Translate the E-R diagram below into an SQL schema involving three tables: E1, R, and E2. Include all primary and foreign key constraints and assume all attributes have type integer.

(a)

(b) Add the following constraint to the above schema. Don't rewrite the whole schema; just

provide the material to be added and tell me where it goes.

(c) Create a view of this database that provides the values of A3 and B1 for all entities

related by R.

SOLUTION:

(a)



CREATE TABLE E1 (

A1 : INTEGER,

A2 : INTEGER,

A3 : INTEGER,

PRIMARY KEY (A1, A2),

FOREIGN KEY (A1, A2) REFERENCES R (A1, A2))

CREATE TABLE R (

A1 : INTEGER,

A2 : INTEGER,

B1 : INTEGER,

PRIMARY KEY (A1, A2),

FOREIGN KEY (A1, A2) REFERENCES E1 (A1, A2),

FOREIGN KEY (B1) REFERENCES E2 (B1))

CREATE TABLE E2 (

B1 : INTEGER,

B2 : INTEGER,

PRIMARY KEY B1)

Alternate solution uses table E2 as above and replaces E1 and R with:



CREATE TABLE E1-R (

A1: INTEGER,

A2: INTEGER,

A3: INTEGER

B1 : INTEGER,

PRIMARY KEY (A1, A2),

FOREIGN KEY (B1) REFERENCES E2 (B1))

(b) i. In any row of E1, attribute A3 must be less than A2.
Solution:
Add constraint CHECK A3 ¡ A2 in CREATE TABLE E1
ii. The number of rows in R is less than any value of B2
Solution:
Add the following assertion to the schema



CREATE ASSERTION X

CHECK (SELECT COUNT (*) FROM R ) <

( SELECT MIN (B2) FROM E2 )

(c)



CREATE VIEW X (A3, B1) AS

SELECT E1.A3, R.B1

FROM E1, R

WHERE E1.A1 = R.A1 AND E1.A2 = R.A2

Alternatively



CREATE VIEW X (A3, B1) AS

SELECT E1.A3, E2.B1

FROM E1, R, E2

WHERE E1.A1 = R.A1 AND E1.A2 = R.A2 AND

R.B1 = E2.B1