Question 1

Consider the tables **Students**, **Departments** and **Courses_Taken** as shown below: | SID | name | dept_ID | |---|---|---| | 001 | Harry | C001 | | 002 | Louis | C002 | | 003 | Liam | C003 | | 004 | Niall | C001 | | 005 | Zayn | C003 | | 006 | Luke | C004 | | 007 | Ashton | C002 | | 008 | Bradley | C005 | | 009 | Connor | C006 | | 010 | Alex | C005 | Table 1: **Students** | dept_ID | dept_name | |---|---| | C001 | Comp. Sci. | | C002 | Maths | | C003 | History | | C004 | Geography | | C005 | Music | | C006 | Biology | Table 2: **Departments** | SID | course_name | |---|---| | 001 | DBMS | | 002 | Calculus | | 003 | Modern History | | 001 | Operating Systems | | 002 | Algebra | | 004 | DBMS | | 005 | Modern History | | 006 | Oceanography | | 007 | Algebra | | 006 | Climatology | | 008 | Classical | | 009 | Zoology | | 010 | Post Rock | 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? Figure from the original question paper