Question

Part 32 Tramdlatian Madl eal es Point) Question 3.2.1 (35 Points) Take the following ER-model and translate it into a relational schema using the rules presented in class. Present the relational schema as an SQL script (assume that all attributes are of data type INT). Present the results of the following intermediate steps in this order: 1. Translate strong entities + unnest composite attributes 2. Translate weak entities 3. Translated multi-valued attributes 4. Translate relationships as 011 A. 0:N 012 0:N 1:N C11 C12 C1 0:N 21 0:N d2 2

Answer 1

If you have any doubts, please give me comment...

CREATE TABLE B(

b1 INT,

b2 INT,

PRIMARY KEY(b1, b2)

);

CREATE TABLE B_B3(

b1 INT,

b2 INT,

b3 INT,

PRIMARY KEY(b1, b2, b3),

FOREIGN KEY(b1, b2) REFERENCES B(b1, b2)

);

CREATE TABLE C(

c2 INT PRIMARY KEY,

c11 INT,

c12 INT

);

CREATE TABLE E(

e1 INT PRIMARY KEY,

e2 INT

);

CREATE TABLE D(

c2 INT,

d1 INT,

PRIMARY KEY(c2, d1)

);

CREATE TABLE A(

a11 INT,

a12 INT,

a2 INT,

a3 INT,

c2 INT,

d1 INT,

b1 INT,

b2 INT,

PRIMARY KEY(a11, a12),

FOREIGN KEY(c2, d1) REFERENCES D(c2, d1),

FOREIGN KEY(b1, b2) REFERENCES B(b1, b2)

);

CREATE TABLE D_D2(

c2 INT,

d1 INT,

d2 INT,

PRIMARY KEY(c2, d1, d2),

FOREIGN KEY(c2, d1) REFERENCES D(c2, d1)

);

CREATE TABLE Z(

b1 INT,

b2 INT,

c2 INT,

e1 INT,

z1 INT,

PRIMARY KEY(b1, b2, c2, e1, z1),

FOREIGN KEY(b1, b2) REFERENCES B(b1, b2),

FOREIGN KEY(c2) REFERENCES C(c2),

FOREIGN KEY(e1) REFERENCES E(e1)

);