686 views
3 votes
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 __________

2 Answers

Best answer
4 votes
4 votes

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.

 

selected by
0 votes
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.

Related questions

1 votes
1 votes
2 answers
3
gatecse asked Dec 9, 2020
507 views
Domain constraints, functional dependency and referential integrity are special forms of _______Foreign keyPrimary keyAssertionReferential constraint