GATE CSE 2001 | Question: 2.25

Question

Consider a relation geq which represents "greater than or equal to", that is, $(x,y) \in $ geq only if $y \geq x$.

create table geq
(	
    ib	integer	not null,
    ub	integer	not null,
    primary key ib,	
    foreign key (ub) references geq on delete cascade
);

Which of the following is possible if tuple (x,y) is deleted?

• A. A tuple (z,w) with z > y is deleted
• B. A tuple (z,w) with z > x is deleted
• C. A tuple (z,w) with w < x is deleted
• D. The deletion of (x,y) is prohibited

Comments

@swami_9 I hope this example will make things a lot easier. 

$ib$ [Primary key]	$ub$  [Foreign Key]
1    (z)	2    (w)
2    (z)	3    (w)
3    (x)	4    (y)
4	4
5	5

Let $(x,y) = 3,4$ be deleted. Due to this the tuples : $(2,3)$ will be deleted as $3$ of $ub$ is referencing $3$ of $ib$, and then $(1,2)$ will be deleted because $2$ of $ub$ is referencing $2$ of $ib$. These happen as its set as ON DELETE CASCADE.

Now if we take $(1,2)$ to be $(z,w)$ we can see here $w(2)<x(3)$ . Option C is possible.

---

ib	ub
x	y
w	x
z	w

1. Deletion of tuple (x,y) would only affect another tuple if a tuple exist such that (_,x) exists

    we are given in option that a tuple (z,w) might get affected by (x,y) so let's add a tuple z,w to the table. 
2. Now the tuple (z,w) would only get affected only if w points out to a primary key in the table, so let's say a tuple (w,_) exist which affects/deletes (the z,w) tuple.

now from 1 and 2 points we know a tuple (w,_) exits and (_,x) exists. 
so if we add (w,x) to the relation it satisfies the condition that deletion of x,y leads to deltion of z,w
since these tuple exists, we can conclude from the geq that y>x>q>z
so option (c) satisfies 

 

---

If a tuple (z,w)(z,w) is deleted due to cascade, then its foreign key must point to the deleted tuple (x,y)(x,y). Since the foreign key is:

ub→ib,ub→ib,

we must have

w=x

Therefore the condition

w<x

can never occur.

Another way to say it:

• If w<x, then w≠x.
• Since w≠x, the tuple (z,w)(z,w) does not reference the deleted key x.
• Hence it is not deleted by the cascade.

If only tuples with w<x are deleted, then the tuple having w==x (the actual referencing tuple causing the cascade) still remains, violating the foreign-key constraint.

Answer 1

Answer: C

The table can be depicted as:

$$\begin{array}{|c|c|c|} \hline \textbf{ib(PK)} & \textbf {ub(FK)} \\\hline  \text {z} &  \text{w = u}\\\hline  \text{u} & \text{v = x} \\\hline \text{x} & \text{y}  \\\hline \end{array}$$

If $(x,y)$ is deleted then from the above table:

• $v\leq y$ $($as $v=x)$
• $u<v\leq y, u!=v$ $($as $v=x$ and ib is the Primary Key$)$
• $w<v\leq y$ $($as $w=u)$
• $z<w<v\leq y, z!=w$ $($as $w=u$ and ib is the Primary Key$)$

As, it can be seen that $w<v$ or $w<x$ $($as $v=x)$ so C is the answer.

Comments

Actually, it is a simple query, but asked in a twisted manner.

See, if we write three relation $\left ( x,y \right ),\left ( y,z \right ),\left ( z,w \right )$, then see there is a on delete cascade relationship in it. But in question they have only given $\left ( x,y \right ),\left ( z,w \right )$and asked to find inner one, that is $\left ( y,z \right )$

---

An easy way to solve is:

This shows that there is relation between x & w(Integrity violation could happen there). And, all the tuples where w<x needs to be deleted to hold the relation geq.

Ans: (C)

---

w<=x right? if x,y deleted then for sure the first one to delete from table that contain same x value in y as foreign key so the value is same as x. so it should be w<=x?

Answer 2

tuple (x,y) is to be deleted so let's take it into relation.

Now we have to observe the effect on tuple (z,w) so we will take it also in our table.

So till now, we are having two tuples, (x,y) and (z,w). Now let's consider each option:

a) z>y means (y,z) tuple is there. So deletion of (x,y) doesn't give any cascade relation between (x,y),(z,w) and (y,z) 

b) z>x means (x,z) exists in relation. But since 'ib' is primary key, we can't repeat 'x' .

c) w<x means (w,x) exists. On deleting (x,y), (w,x) should be deleted and then (z,w) to maintain DELETE CASCADE. 

Hence option C is correct choice

Comments

Thanks.

---

Great explanation!!

---

bestt

Answer 3

Consider the below example 

lb	ub
5	8
3	5
8	8
2	3

Deleting first tuple will force us to delete 2 nd tuple

And deleting 2 nd tuple will force us to delete 4 th tuple.

Now because of deletion of first tuple (5,3), 4 th tuple (2,3) should be deleted ... Here z = 2 and x = 5 and z<x

So option C) is the answer ...

Answer 4

ans is C.

here they have mentioned "on delete cascade"

deleting (x,y), the relation having x as a greater value will also b deleted. in (z,w) w<x (x is greater) therefore it will also be deleted.

Comments

Thanks for explanation

---

how w < x, if w =  x, then it will get deleted

Answer 5

the relation schema is ( lb , ub ), where lb is the primary key, and ub is the foreign key which is referencing the primary key of its own relation.
 
Hence the table geq is both the master ( which has the referenced key ) as well as the child table (which has the referencing key).
 
The table has two constraint, one is that if there is a tuple ( x, y ), then y is greater than or equal to x, And the other is referential integrity constraint, which is on-cascade-delete on the foreign key.
 
On-cascade-delete says, that “When the referenced row is deleted from the other table (master table), then delete also from the child table”.
 
Suppose the instance in the given relation is the following:
 
x y
-----
5 6
4 5
3 4
6 6
Now if we delete tuple (5,6) then tuple ( 4,5 ) should also be deleted ( as 5 in the tuple (4, 5) was referencing to 5 in the tuple(5,6) which no longer exist, hence the referencing tuple should also be deleted), and as (4,5) got deleted hence tuple (3,4) should also be deleted for the same reason.
 
Therefore in total 3 rows have to be deleted if tuple ( 5,6 ) is deleted.
 
Now from the above instance we can say that if (x,y), i.e. ( 5,6 ) gets deleted then a tuple ( z, w) i.e, ( 3, 4) is also deleted. And we can see here that w < x. Hence option C.

Answer 6

i want to add just hint here think transitively if (x,y) is deleted then pk x is deleted therefore all the tuple which have x in forein they willl be deleted and because of these all the tuples having those tuples pk as forein key they will be deleted try to visualize you will get answer