[60 marks] Question 1. A small online mobile video creation and sharing platform called 'VideoBook" has the following three relations in its database. The relevant catalog information is given: " "userld" is the candidate key for relation user; "videolD" is the candidate key for relation video; the two attributes in relation creates are the foreign keys referencing user and video, respectively. In all relations, no records span over one block. - user (userID name, email, city) - creates (userID, videolD) - video (videolD, title, category, creationDate, format, URL.) Number of records in user; nuser=7,000; each disk block can hold up to 300 tuples; Number of records in user, nuser=90,000; each disk block can hold up to 650 tuples; Number of records in video, nvideo=50,500; each disk block can hold up to 70 tuples; Index: a primary B+ tree index of height 6 on the userID attribute of the creates relation; The number of distinct values, V (city, user) =10. Answer the following questions. [30 marks] a) Assume that in the worst case, the memory can only hold one block for any of the relations. Using the nested loop join algorithm, and the smaller relation as the outer relation, how many block transfers and seeks would be needed to evaluate "user respectively? [4/30] b) Assume that in the worst case, the memory can only hold one block for any of the relations. Using the blocked nested loop join algorithm and the small relation as the outer relation, how many block transfers and seeks would be needed to evaluate "user respectively? [4/30] c) Assume that the indexed nested loop join algorithm is used based on the available B+ tree index on creates. How many block transfers and seeks at minimum would be needed to evaluate "user creates", respectively? [4/30] d) Assume that both relations user and creates are already sorted on disk. With the merge join algorithm and buffer size bb=1, how many block transfers and seeks would be needed to evaluate "user creates", respectively? [4/30] e) Based on the results obtained from a), b), c) and d), discuss which join algorithm is the most efficient in evaluating "user D creates". [4/30] f) Assume that the technician at 'VideoBook' created an optimised evaluation plan for a query, which is shown in the following diagram. Assume that linear scan is used to evaluate all the selection operations. The use of pipelining is also shown in the diagram. What is the total number of block transfers for the whole evaluation plan? Justify your