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

0 record will be additional deleted.

Since, table T1 has On delete cascade constraint, referenced record <3,8> of table T1 will be deleted and the referencing record <8,3> of table T2 will be deleted.

Since, Table T2 has on delete set Null constraint, when referenced record <8,3> of T2 will be deleted, the referencing records, <5,8> and <9,8> get updated to <5,null> and <9, null>. 

So, no additional record will be deleted from table T1.

Answer 2

When <3 8> is deleted from table 1 .it alerts s
Bcz s is referencing p and 3 is deleted from p so tuple having s value 3 will be deleted from table 2. As T2 uses cascade delete approach .now as tuple <8 3>will be deleted from T2 ..q will get alert about 8 deleted from R ( as q references R) .As T1 uses on delete null policy so it sets null in q row where q value is 8....as tuple <5 8>in T1 has q value 8 so it get updated to ,<5 null>...so no tuple is deleted from T1 and 1 tuple deleted from T2.