NIELIT 2018-49

Question

The following table has two attributes $X$ and $Y$ where $X$ is the primary key and $Y$ is the foreign key referencing $X$ with on-delete cascade.

$\begin{array}{|c|c|} \hline X & Y \\ \hline 2 & 4 \\ \hline 3 & 4 \\ \hline 4 & 3 \\ \hline 5 & 2  \\ \hline 7 & 2 \\ \hline 9 & 5 \\ \hline 6 & 4 \\ \hline\end{array}$

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple $(3,4)$ is deleted is

• A. $(4,3)$ and $(6,4)$
• B. $(2,4)$ and $(7,2)$
• C. $(3,2)$ and $(9,5)$
• D. $(3,4), (4,5)$ and $(6,4)$

Comments

all rows from that instance are deleted

---

even i think all should be deleted

---

All options are wrong so please don't spend your time on this type of questions

Answer 1

Since $X$ is a primary key, on deleting $(3, 4)$, all the foreign key occurrences of $3$ will also be deleted: $(4, 3)$.
We've deleted $4$, which was also primary key in $(4, 3)$, its foreign key occurrences will also be deleted: $(2, 4), (6, 4)$.
$2$ and $6$ are also deleted, so their foreign key occurrences will also be deleted: $(5, 2), (7, 2)$.
Likewise, $5$ and $7$'s key foreign key occurrences will also be deleted: $(9, 5)$.

And that deletes whole of the tuples of the table.

Comments

Logically whole table will be deleted. But from the given options (A) is correct. (4,3), (2,4), (6,4) are deleted first. Might be question is trying to say that which cascade are deleted first. Option (C) is incorrect because there is no (3,2) cascade is present in the table. Option (D) is also incorrect because (3,4) is already deleted, we are asked for the additional deleted cascade.

---

This is the official qouestion from nielit and yet we can still see its. kind of dumb question! here option 1 and option 2 relates most but we can't say that option 1 or option 2 or both are correct answers neither option 4 is correct so this is a dumb question and yes it would be removed at time of paper checking and we will get full marks.

Answer 2

A FOREIGN KEY with on-delete cascade states that if A RECORD IS DELETED FROM PARENT TABLE THEN THAT CORRESPONDING RECORD MUST ALSO BE DELETED FROM THE CHILD TABLE.

But here we have single table so we delete foreign keys corresponding to DELETED primary keys

here X IS PARENT AS PK (PRIMARY KEY)and Y IS CHILD AS FK(FOREIGN KEY)

(3,4) deleted 3 is PK and 3 is FK in tuple (4,3) so deleted.

now again 4 is FK in (6,4) & (2,4) so these will also be deleted.

again 2 is FK in (5,2) & (7,2) so deleted

similarly 5 is FK in (9,5) so deleted.

 

so BY DELETING ONLY (3,4) all entries are deleted thus none of the option matches.

but official key says option A is correct only.

 

 

Answer 3

→ On removal of row (3,4), row (4,3) must also be deleted as they depend on value 3.
→ On removal of row (4,3), row (2,4) and (6,4) must also be deleted as it depends on value 4.
→ As there is no option with row(2,4) and also the question says additional tuples should be deleted. So, option D is eliminated.

Answer 4

To preserve referential integrity all tuples will be deleted. So, non of these should be the answer.

But since it's not in the option, so we can eliminate options one by one.

Option C and D are wrong options because it have wrong tuples which are not in the table.

So now we have two options left A and B, both options are right but we have to selesct one we can delect option A because if we go step by step then tuples (4,3) and (6,4) will be deleted first.

so logically my answer is Option A.