Dark Mode

3 votes

__Consider the following schema:__

```
Create table A(a1 int not null primary key,
a2 int);
Create table B(b1 int not null primary key,
b2 int references A(a1)
on delete cascade on update no action);
Create table C(c1 int not null primary key,
c2 int references B(b1)
on delete cascade on update no action);
insert into A values (1,1),(2,1),(3,1),(4,2),(5,2),(6,3);
insert into B values (1,1),(2,1),(3,1),(4,2),(5,2),(6,4);
insert into C values (1,1),(2,1),(3,2),(4,3),(5,4),(6,6);
```

**The sum of the number of tuples present in the relation B, and C after running the query “Delete from A where a1 = 1;“ will be __________**

3 votes

Best answer

On executing the given query, the tuple that got deleted from relation A is (1, 1).

The tuples in relation to B referring to the value a1 = 1, will also get deleted due to the On delete cascade. Therefore, the tuples (1,1), (2, 1), (3, 1) will get deleted.

Therefore, the remaining tuples in relation to B are 3, which are (4, 2), (5, 2), (6, 4).

On deleting values 1, 2, 3 of b1 form B, every tuple from C referring to these values will get deleted.

Therefore, the tuples that will get deleted from C will be (1,1), (2, 1), (3, 2), (4, 3).

Therefore, the number of tuples present in C are 2 which are (5, 4), (6, 6).

**Therefore, the total number of tuples present in B and C after running this query are 2 + 3 = 5 tuples.**

0 votes

When the given query is run, the tuple that was removed from relation A is returned (1, 1).

The tuples in relation to B referring to the value a1 = 1, will also get deleted due to the on delete cascade. Therefore, the tuples (1,1), (2, 1), (3, 1) will get deleted. So,the remaining tuples in relation to B are 3, which are (4, 2), (5, 2), (6, 4).

On deleting values 1, 2, 3 of b1 form B, every tuple from C referring to these values will get deleted. The tuples that will get deleted from C will be (1,1), (2, 1), (3, 2), (4, 3). So,the number of tuples present in C are 2 which are (5, 4), (6, 6).

**Therefore, the total number of tuples present in B and C after running this query are **

**2 + 3 = 5 tuples.**