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

"foreign key (ub) references geq" Means referring to primary key of table geq(if foreign key references to same table then referenced key must be primary key of same table with same data type). In this case Parent table(Foreign key column) and child table (referenced column) both denoting same table.

Referenced column by Foreign key in same table or in child table must be a primary key.

NOTE-If more than 1 table involved in foreign key then you can replace the word primary key with candidate key above.

---

Why can’t just anyone explain using some example or example table, it just becomes easier for everyone, it is so confusing. Please explain  anyone.

---

@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.

Answer 1

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.