DATABASE

PLEASE SHOW YOUR WORK!!!

3. Consider SQL queries below.

SELECT Fname, Lname, Address

FROM (EMPLOYEE JOIN DEPARTMENT ON Dno=Dnumber)

WHERE Dname=Research;

SELECT E.Lname, S.Lname

FROM EMPLOYEE as E

LEFT JOIN EMPLOYEE as S on E.Super_ssn = S.Ssn;

Question: Draw the initial query tree for each of these queries, and then show how the query tree is optimized by the algorithm outlined in Heuristic method.

4. A file of 4096 blocks is to be sorted with an available buffer space of 64 blocks. How many passes will be needed in the merge phase of the external sort-merge algorithm?