DBMS End Term: 24 December 2023, Set FDD1 (September 2023 term)

Consider the tables Students, Departments and Courses_Taken as shown below:

Table 1: Students

Table 2: Departments

Table 3: Courses_Taken

Consider $dept\_ID$ to be the foreign key in table Students that references $dept\_ID$ in table Departments with on-delete cascade and $SID$ be the foreign key in table Courses_Taken that references $SID$ in table Students with on-delete cascade. If tuples (C001, Comp. Sci.) and (C002, Maths) are deleted from table Departments then how many tuples will be deleted from table Courses_Taken?

Consider a nested loop join for the two relations, instructor and department. Assuming the worst-case memory availability and instructor as the outer relation, the details are as follows:

• Total number of block transfers: 140660
• Total number of seeks required: 2600
• Number of records in the outer relation: 2000

What is the number of blocks in the inner relations?