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. A | C ------ 2 | 4 3 | 4 4 | 3 5 | 2 7 | 2 9 | 5 6 | 4 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) and (9,5) C. (5,2) and (7,2) D. (3,4), (4,3), (6,4), (5,2), (7,2), and (9,5)

GATE 2005 · Databases · Referential Integrity · medium

Answer: B. (5,2) and (7,2) and (9,5)

1. Delete initial tuple (2,4); A=2 is removed: Deleting (2,4) means A=2 is gone. Find all rows where C=2: rows (5,2) and (7,2). These are cascade-deleted.
2. Cascade from deletion of (5,2): A=5 is removed: Row (5,2) is deleted (A=5 gone). Find all rows where C=5: row (9,5). Row (9,5) is cascade-deleted.
3. Cascade from deletion of (7,2): A=7 is removed: Row (7,2) is deleted (A=7 gone). Find all rows where C=7: none. No further cascade.
4. Cascade from deletion of (9,5): A=9 is removed: Row (9,5) is deleted (A=9 gone). Find all rows where C=9: none. No further cascade.
5. Collect all additionally deleted tuples: Additionally deleted (beyond the original (2,4)): (5,2), (7,2), (9,5). This matches option B.