Design of Industrial Information Systems- Spring 2018 Recommended problems for t

Question

Design of Industrial Information Systems- Spring 2018 Recommended problems for the Final Exam Group 1 -SQL and RELATIONAL ALGEBRA Proble Consider the following table declarations: m 1 CREATE TABLE R1 ( a INT PRIMARY KEY, b INT CREATE TABLE R2 ( C INT PRIMARY KEY d INT REFERENCES R1(a) CREATE TABLE R3 e INT PRIMARY KEY f INT CHECK(f IN (SELECT a FROM R1)) Assume R1 contains the tuples (1,10), (2,10), and (3,20), while R2 and R3 are empty. Which of the following sequences of statements would not be allowed by SQL? 23 P I a ) (a) INSERT INTO R3 VALUES(5,2); DELETE FROM RI WHERE a-2, INSERT INTO R2 VALUES(1,1); -5 2 IO (b) INSERT INTO R2 VALUES(1,1); DELETE FROM R1 WHERE a-2; INSERT INTO R3 VALUES(6,1); 3 20 b) (c) INSERT INTO R3 VALUES(6,1); UPDATE R1 SET a-4 WHERE a-3; INSERT INTO R2 VALUES(10,2); Lo Delete a2y2 (0 ) (d) DELETE FROM RI WHERE a=2; INSERT INTO R2 VALUES(1,1); INSERT INTO R3 VALUES(5,2);

Explanation / Answer

R1(a,b) { (1,10),(2,10),(3,20)}

R2(c,d)

R3(e,f)

1.INSERT INTO R3 VALUES(5,2)

f = 2 and a = 2 in R1 , so insertion can be done after check constraint

DELETE FROM R1 WHERE a = 2

Deletion can also be done as there is no referential integrity constraint between R1 and R3

INSERT INTO R2 VALUES(1,1)

Insertion can also be done in R2 as a = 1 is present in R1 for referenntial constraint with d= 1

2.

INSERT INTO R2 VALUES(1,1)

d = 1 and a = 1 is in R1 , so referential integrity constraint is satisfied, insertion can be done.

DELETE FROM R1 WHERE a = 2

Deletion can also be done as there is no referential integrity constraint between R1 and R2

INSERT INTO R3 VALUES(6,1)

Insertion can also be done in R3 as a = 1 is present in R1 and Check constraint is satisfied so f = 1 is valid.

3.

INSERT INTO R3 VALUES(6,1)

f = 1 can be inserted in R3 as a = 1 is present in R1, so Check constraint is satisfied

UPDATE R1 SET a = 4 WHERE a = 3

Update is valid

INSERT INTO R2 VALUES (10,2)

d = 2 has refential constraint a = 2, so insertion can be done

4.

DELETE FROM R1 WHERE a = 2

deletetion can be done as there are no matching values in R2 and R3

INSERT INTO R2 VALUES(1,1)

Insertion can be done in R2 as referential constraint is satisfied(a=1 is present in R1)

INSERT INTO R3 VALUES(5,2)

Insertion cannot be done in R3 as f=2 is not satisfied by check constraint , the value a = 2 is deleted already. So insertion will not be executed

So option 4 of set of sql statements is incorrect as Check constraint in R3

.

?Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.