UGC NET CSE | June 2019 | Part 2 | Question: 39

Question

Following table has two attributes Employee_id and Manager_id, where Employee_id is a primary key and manager_id is a foreign key referencing Employee_id with on-delete cascade:

$$\begin{array}{|c|c|} \hline \text{Employee_id} & \text{Manager_id} \\ \hline 20 & 40 \\ \hline 25 & 40 \\ \hline 30 & 35 \\ \hline 35 & 20 \\ \hline 40 & 45 \\ \hline 45 & 25 \\ \hline \end{array}$$

On deleting the table $(20,40)$, the set of other tuples that must be deleted to maintain the referential integrity of table is

• A. $(30, 35)$ only
• B. $(30, 35)$ and $(35, 20)$ only
• C. $(35, 20)$ only
• D. $(40, 45)$ and $(25, 40)$ only

Answer 1

To delete tuple (20,40) in which key attribute is 20. so there should not be any tuple 20 present in foreign key columns. so after delete tuple (20,40) 2 more tuple (35,20) and (30,35) also deleted.

option b is correct.

For Reference:https://gateoverflow.in/1399/gate2005-76

Answer 2

Yes option B IS CORRECT as to maintain the referential integrity of the table after deleting 20,40 where 20 being the PK,ANY VALUE 20 in the foreign key to be deleted and son on.

Hence (35,20 ) to be deleted.

further deleting (35,20 ) where 35 being PK and in order to maintain referential integrity any VALUE 35 in the foreign key must be deleted i.e (30,35 )

 further no value eaqual to 30 in foreign key column is found,

hence the option B is correct.

Answer 3

(30,35) and (35,20) must be deleted as values in a foreign key column must match either all the values, or a subset of the values in the referenced Primary Key.