recategorized by
3,632 views
4 votes
4 votes

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

  1. $(4,3)$ and $(6,4)$
  2. $(2,4)$ and $(7,2)$
  3. $(3,2)$ and $(9,5)$
  4. $(3,4), (4,5)$ and $(6,4)$
recategorized by

3 Answers

Best answer
5 votes
5 votes
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.
selected by
2 votes
2 votes

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.

 

 

1 votes
1 votes
→ 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:

Related questions

1 votes
1 votes
2 answers
1
gatecse asked Dec 9, 2020
508 views
Domain constraints, functional dependency and referential integrity are special forms of _______Foreign keyPrimary keyAssertionReferential constraint
1 votes
1 votes
2 answers
2
Arjun asked Dec 7, 2018
1,973 views
Identify the true statement from the given statements:Number of child pointers in a B/B+ tree node is always equal to number of keys in it plus oneB/B+ tree is defined by...
2 votes
2 votes
3 answers
3
Arjun asked Dec 7, 2018
944 views
______ is NOT a part of the ACID propertiesInconsistencyConsistencyAtomicityIsolation
1 votes
1 votes
2 answers
4
Arjun asked Dec 7, 2018
1,349 views
____ symbol is used to denote derived attributes in ER modelDashed ellipseSquared ellipseEllipse with attribute name underlinedRectangular Box