1. Logical → Physical.
2. Columns (. 42).
42 -
3. : .. Columns.
4. (. 43).
43 -
5. -- .. : Create Association Table. ( ).
6. .
7. (. 44! .).
44 -
7.1. View table.
7.2. View relationship.
8. SQL, Tools/Forward Engineering (. 45).
45 -
8.1. Table - Create table, Index - (. 46), Referential Integrity . 47, .
46 - Index
47 - Referential Integrity
8.2. Prewiew, - .
8.3. Generate. ERwin , SQL-. - , , .
9. , SQL DDL (*.ers, *.sql, *.ddl), .sql. - .
10. Access SQL-Server.
(. 44)
CREATE TABLE _1 (
_2 varchar(158) NULL,
_1 int IDENTITY(1,1),
_1 money NULL
)
go
ALTER TABLE _1
ADD PRIMARY KEY NONCLUSTERED (_1)
go
CREATE TABLE _1_2 (
_2 int NOT NULL,
_5 varchar(20) NULL,
_1 int NOT NULL
)
go
ALTER TABLE _1_2
ADD PRIMARY KEY NONCLUSTERED (_2, _1)
go
CREATE TABLE _2 (
_4 varchar(180) NULL,
_2 int IDENTITY(1,1),
_3 datetime NULL
)
go
ALTER TABLE _2
ADD PRIMARY KEY NONCLUSTERED (_2)
go
CREATE VIEW AS
SELECT _2._4, _1_2._1, _2._2, _1_2._2, _1._2, _1._1, _1_2._5, _2._3, _1._1
FROM _1_2, _2, _1
go
ALTER TABLE _1_2
ADD FOREIGN KEY (_2)
REFERENCES _2
|
|
go
ALTER TABLE _1_2
ADD FOREIGN KEY (_1)
REFERENCES _1
go
Access:
1. , (. 48).
48 -
1.1. SQL.
1.2. SELECT; (. 49).
1.3. :
CREATE TABLE _1 (
_2 varchar(158) NULL,
_1 int IDENTITY(1,1),
_1 money NULL
)
49 -
a) 1 MS Access .
b) . .
c) 1 .. 1.3,a-1.3,b.
1.4. :
ALTER TABLE _1
ADD PRIMARY KEY NONCLUSTERED (_1)
a) 1 .. 1.4,b-1.4,e.
b) 1 MS Access.
c) NONCLUSTERED.
d) .
e) .
1.5. :
ALTER TABLE _1_2
ADD FOREIGN KEY (_2)
REFERENCES _2
a) 1 .. 1.5,b-1.5,d.
b) 1 MS Access.
c) .
d) , .
2. .
, (. 5)
5 -
int, IDENTITY 1,1 | |||
char (18) | 18 | ||
money | > 100 <10 000 | ||
int, IDENTITY 1,1 | |||
char (18) | 18 | > 0 | |
char (18) | 18 | > 0 | |
char (18) | 18 | ||
char (18) | 18 | ||
int, IDENTITY 1,1 | |||
datatime | |||
int |
: .. Columns (. 50).
(. 51). (. 52, . 53).
50 -
51-
52 - ERD- , 2-
53 - , 1-
SQL (. 54), .
|
|
54 -
SQL:
CREATE TABLE (
char(18) NULL,
int IDENTITY,
char(18) NULL,
char(18) NULL,
char(18) NULL
)
go
ALTER TABLE
ADD PRIMARY KEY NONCLUSTERED ()
go
CREATE TABLE (
int NULL,
int NULL,
int NULL,
datetime NULL,
int NULL
)
go
ALTER TABLE
ADD PRIMARY KEY NONCLUSTERED ()
go
CREATE TABLE (
money NOT NULL,
int IDENTITY,
char(18) NULL
)
go
ALTER TABLE
ADD PRIMARY KEY NONCLUSTERED ()
go
ALTER TABLE
ADD FOREIGN KEY ()
REFERENCES
go
ALTER TABLE
ADD FOREIGN KEY ()
REFERENCES
go
1. ?
2. ?
3. ?
4. .
5. .
6. .
7. .
8. ?
9. ?
10. ?
11. ERwin .
12. --?
13. ?
14. ?
15. SQL?