GATE CS 2017 Set 2 Q.19 (CS Q.19): ER Model and Relational Schema Design

Consider the following tables T1 and T2.

Table T1

P	Q
2	2
3	8
7	3
5	8
6	9
8	5
9	8

Table T2

R	S
2	2
8	3
3	2
9	7
5	7
7	2

In table T1, P is the primary key and Q is the foreign key referencing R in table T2 with on-delete cascade and on-update cascade. In table T2, R is the primary key and S is the foreign key referencing P in table T1 with 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

Your answer

Official answer and solution

Official answer: 0
From: Official GATE 2017 answer key

Solution

Trace the referential integrity constraints and cascading actions triggered by deleting record $(3,8)$ from table T1.

Understand the schema and constraints:

Table T1: Primary key $P$ , foreign key $Q$ referencing $T2(R)$ with ON DELETE CASCADE and ON UPDATE CASCADE.
Table T2: Primary key $R$ , foreign key $S$ referencing $T1(P)$ with ON DELETE SET NULL and ON UPDATE CASCADE.

Now trace the deletion of record $(P=3, Q=8)$ from table T1:

We execute: DELETE FROM T1 WHERE P = 3;
Since $P=3$ is deleted from T1, we check foreign key references in table T2 ( $S$ references $P$ ).
In table T2, record $(R=8, S=3)$ references $P=3$ .
Because the foreign key rule on $S$ is ON DELETE SET NULL, the attribute $S$ in record $(8,3)$ of table T2 is set to NULL, turning it into $(8, \text{NULL})$ .
Crucially, the row $(8, \text{NULL})$ in table T2 is NOT deleted!
Since no record is deleted from table T2, no cascading deletion is triggered back on table T1 (where $Q$ references $R$ with ON DELETE CASCADE).

Therefore, no additional records in table T1 are deleted. The number of additional records deleted from table T1 is 0.

Final answer

Common trap

Assuming ON DELETE SET NULL deletes the row in T2 instead of modifying its foreign key attribute to NULL.

The answer is the one in the official GATE answer key. The solution is this site's own explanation.

Source

Paper: GATE 2017 Computer Science and Information Technology (CS) Session 2
Question: 19 of 65, printed as CS Q.19
Section: Computer Science and Information Technology

Report an error in this question

GATE questions are the work of the organising institutes. This site is independent and is not affiliated with them.

Question

Solution

Source