The table taken(StNo, CourseID, Year, Quarter, Sec, Grade, Remarks) contains the grades for the courses completed by university students during the last 20 years. If 10,000 new students enter the university every year, we can assume that in taken there are 200,000 different students, each identi ed by a StudentID. Thus will assume there are 40,000 students enrolled each quarter, and that each student takes four classes per quarter (160,000 classes taken by students each quarter) and that there are three quarters in each year (480,000 classes taken every year). Thus we get a total of 9,600,000 tuples recording the grades of all students over the last 20 years. Also assume that the average number of students per class is 100; this implies that 4,800 classes are o ered each year. On this table, we have a sparse index on StNo and a dense index on the combination: (CourseID,Year,Quarter,Sec,StNo). Both indexes are implemented as B+ trees where CourseNo, Year, Quarter, Sec, and StNo take 8 bytes each, and the pointers used in the B+ trees take 10 bytes.

B1 If the le blocks have 4096 bytes and each tuple in taken requires 100 bytes, how many blocks will be needed to store the unspanned tuples of this relation ?

B2 Compute the levels and the number of blocks at each level of the B+ tree, assuming a worst-case scenario.

B3 How many blocks of B+ tree and le will the DBMS retrieve from disk to answer the following query: Find the average grade in a given class (e.g. nd the average grade for: CS143, 2010, Fall, sec. 1). Assume the worst-case scenario, and that all the buffers are initially empty.

B4 We now want to compute the average grade over the (480,000 or so) classes taken in year 2011 (assume that they all have the same credit). Explain how the DBMS will go about searching and retrieving blocks from disk for this query, and estimate the number of blocks the system will have to fetch if those 200,000 students each took 48 classes on the average. Assume the worst-case scenario, and that all the buffers are initially empty.