Q2. [62 marks] You are given the following three relations in a university database as below: COURSE (Cid, Name) STUDENT

[answerhappygod]

Q2 62 Marks You Are Given The Following Three Relations In A University Database As Below Course Cid Name Student 1 (43.19 KiB) Viewed 21 times

Q2. [62 marks] You are given the following three relations in a university database as below: COURSE (Cid, Name) STUDENT (Id, Name, DeptName, GPA) TAKES (Id, Cid, Semester, Year) The relation STUDENT has 500 tuples, and 25 tuples fit into one block; the relation COURSE has 200 tuples, and 20 tuples fit into one block; the relation TAKES has 4,000 tuples and 200 tuples fit into one block. The records of STUDENT are sorted in ascending order of Id on disk, and the records of COURSE are sorted in ascending order of Name on disk. Assume a relation is stored on disk consecutively.
i. [5 marks] Consider the following SQL query: select * from COURSE where Cid=100 Assume COURSE has a B+-tree index of height 3 on the Cid attribute. Further assume that the Cid of COURSE are integers between 1 to 200. What is the number of disk seeks and the number of block transfers associated with the SQL query in the best case and worst case respectively? ii. [5 marks] Consider the following SQL query: select * from STUDENT where Id=100 Assume that the Id of STUDENT are integers that are >1. What is the number of disk seeks and the number of block transfers associated with the SQL query in the best case and worst case respectively? iii. [5 marks] Consider the following SQL query: select * from STUDENT where Id>100 Assume that the Id of STUDENT are integers between 1 to 500. What is the number of disk seeks and the number of block transfers associated with the SQL query in the best case and worst case respectively? iv. [5 marks] Consider the following SQL query: select * from STUDENT where Id>100 Assume that the Id of STUDENT are integers between 1 to 500. Further assume that STUDENT has a B+-tree index of height 5 on the Id attribute. If we use the B+-tree for the query, what is the number of disk seeks and the number of block transfers associated with the SQL query in the best case and worst case respectively?