Previous Year Questions: Integrity Constraints

Q1:Consider the following tables T1 and T2.  (2017 SET 2)

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with ondelete cascade and on-update cascade. In table T2, R is the primary key and S is the foreign key referencing P in table T1 on-delete set NULL and on-update cascade. In order to delete record (3,8) from table T1, the number of additional records that need to be deleted from table T1 is _____.
(a) 0
(b) 1
(c) 2
(d) 3
Ans: (a)
Sol: As Q refers to R so, deleting 8 from Q won't be an issue, however S refers P. But as the relationship given is on delete set NULL, 3 will be deleted from T 1 and the entry in T 2 having 3 in column S will be set to NULL. So, no more deletions. Answer is 0.

Q2: The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.  (2005) 

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:
(a) 3,4) and (6,4)
(b) (5,2) and (7,2)
(c) (5,2), (7,2) and (9,5)
(d) (3,4), (4,3) and (6,4)
Ans: (c)
Sol: Since deleting ( 2 , 4 ) , since 2 is a primary key, you have to delete its foreign key occurrence i.e ( 5 , 2 ) and ( 7 , 2 )
Since we are deleting 5 , and 7 we have delete it foreign key occurrence i.e ( 9 , 5 ) .
There is no foreign key occurrence for 9 .