GATE CSE 2017 Set 2 | Question: 19

Question

Consider the following tables $T1$ and $T2.$

$$\overset{T1}{\begin{array}{|c|c|c|} \hline \textbf {P} & \textbf {Q} \\\hline  \text {2} &  \text{2 }\\\hline \text{3} & \text{8} \\\hline \text{7} & \text{3} \\\hline \text{5} & \text{8} \\\hline  \text{6 } &\text{9} \\\hline \text{8} & \text{5} \\\hline \text{9} & \text{8} \\\hline \end{array}}
\qquad \overset{T2}{\begin{array}{|c|c|c|} \hline \textbf {R} & \textbf {S} \\\hline  \text {2} &  \text{2 }\\\hline \text{8} & \text{3} \\\hline \text{3} & \text{2} \\\hline \text{9} & \text{7} \\\hline  \text{5 } &\text{7} \\\hline \text{7} & \text{2}  \\\hline \end{array}}$$

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 $\langle 3, 8 \rangle$ from the table $T1,$ the number of additional records that need to be deleted from table $T1$ is _______

Comments

If you analyze clearly NO tuple will be deleted from T2 in any case untill we want to delete.[ since t2 has on delete set null]

---

Only thing to be known is On Delete Cascade / On Delete Set Null  apply to tables i.e here On-Delete Cascade is on T1 and On Delete Set Null is on T2. 

that means tuple wont be deleted from T2 ,instead the corresponding value of attribute which violate referential integrity will be set to Null ​​​​​​.

Rest is simple if concept of ODC and ODSN is clear.

---

consider the table

Answer 1

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 $T1$ and the entry in $T2$ having $3$ in column S will be set to NULL. So, no more deletions. Answer is 0.

Comments

@prateekkmr60 @just_bhavana

What if question is delete < 3,2 > from Table T2, as it is not mention for table T2 on-delete set NULL .

It takes additional 1 delete in T1 i.e. <  7,3>   and

0 delete in T2 but  2 cell to  set Null in Column S.

Am I right ?

---

In case of on delete cascade,i believe yes.

on deletion of 3,8 no effect on Q-> R reference. though for S->P , now all entries in S which have entry 3 should be removed. this leads to removal of 8 from R which affects the Q->R reference. Hence all entries in R which have 8 would be deleted. which leads to removal of 5 and 9 from P. This will have impact on S via S->R reference. However there are no 5 and 9 entries in S. So over all 5,8 and 9,8 get deleted from T1 as a result of the 3,8 deletion.

---

given that on DELETE, set Null to dependents of T1, cascade to dependents of T2.

Answer 2

Answer is 0. 

Comments

Good explanation

I miss the term On delete set null

and I get 2 answer

but I read the question again and  I'm done the silly mistake to read a question

I get 0 is answer

---

Can anyone explain why the answer would be 2 if S is referencing P with on delete cascade instead of on delete set null???? any records from T2 would be deleted???

Is it because when deleting (5,8) record 5 is being searched in T2's S but since there's no 5 available no record would be deleted in T2.

same for record (9,8).

---

Answer correct, but on delete set null is applicable to T1 and on delete cascade on T2 not vice versa

Answer 3

On-delete set NULL would set 3 in table T2 under S column as NULL. That's it. No record needs to be deleted off of T1.
Ans: 0

---

However, if On-delete-cascade was the situation, < 8, 3> would be deleted from T2, which would in turn result in the deletion of  < 5, 8 > and < 9,8 > from T1.

Answer would have been 2 in this case.

---

PS: 'On delete set null' would make the row < 8,3 > as < 8, NULL> and not <NULL, NULL>

---

 

Answer 4

For T1 table if any violation occurred then we need to that Field as NULL. So we no need to delete any thing from T1 table.

So  Number of Deletions required in T1 Table = 0