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

Computer Science & Information Technology

You might also like to view...

Showthat the schedule shown in Figure 21.13 for incorrect execution at SNAP- SHOT isolation can also occur when executing at OPTIMISTIC READ COMMITTED (Section 21.2.1) and will also be incorrect.

What will be an ideal response?

Computer Science & Information Technology

If you want to make your linked list a class, you must also include the _____.

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology

SQL Server can generate a self-signed ______________ to encrypt data.

Fill in the blank(s) with the appropriate word(s).

Computer Science & Information Technology

________ is an example of the General Date format

A) 11/9/2015 10:10:10 PM B) Wednesday, November 9, 2015 C) 9-Nov-15 10:10:10 PM D) Wednesday, November 9, 2015 10:10 PM

Computer Science & Information Technology