edited by
18,239 views
62 votes
62 votes

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 _______

edited by

6 Answers

Best answer
94 votes
94 votes

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.

edited by
53 votes
53 votes

Answer is 0. 

16 votes
16 votes

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>


 

0 votes
0 votes

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                  

Answer:

Related questions

31 votes
31 votes
5 answers
1
Madhav asked Feb 14, 2017
10,827 views
In a B+ Tree , if the search-key value is $8$ bytes long , the block size is $512$ bytes and the pointer size is $2\;\text{B}$ , then the maximum order of the B+ Tree is ...
44 votes
44 votes
3 answers
3
Arjun asked Feb 14, 2017
16,032 views
Consider the following database table named $\text{top_scorer}$.$$\overset{\text{top_scorer}}{\begin{array}{|c|c|c|}\hline\\\textbf{player}& \textbf{country}& \textbf...