Consider the following relational schema and SQL query. The schema captures information about employees, departments, and...

 
 

Transcribed Image Text:

Consider the following relational schema and SQL query. The schema captures information about employees, departments, and company finances (organized on a per department basis). Emp (eid: integer, did: integer, sal: integer, hobby: char (20)) Dept (did: integer, dname: char (20), floor: integer, phone: char (10)) Finance (did: integer, budget: real, sales: real, expenses: real) Consider the following query: SELECT D.dname, F.budget FROM Emp E, Dept D, Finance F WHERE E.did = D.did AND D.did = F.did AND E.sal > 100,000 AND E.hobby IN ('diving', 'soccer'); The system's statistics indicate that employee salaries range from 50,000 to 150,000, and employees enjoy 50 different hobbies. There is a total of 25,000 employees and 1,200 departments (each with corresponding financial record in the Finance relation) in the database. Each relation fits 100 tuples in a page. Suppose there exists a clustered B+ tree index on (Dept.did) and a clustered B+ tree index on (Emp.salary), both of size 50 pages. a) Compute the reduction factors and the estimated result size in number of tuples. (2 marks) b) Compute the cost in number of disk I/O's of the plans shown below. Assume that sorting of any relation (if required) can be done in 2 passes. NLJ is a Page-oriented Nested Loops Join. Assume that did is the candidate key, and that 50 tuples of a resulting join between Emp and Dept fit in a page. Similarly, 50 tuples of a resulting join between Finance and Dept fit in a page. Any selections/projections not indicated on the plan are performed "on the fly" after all joins have been completed. (8 marks, 2 marks per plan) NLJ Dept NLJ 1) SMJ 2) HJ D Emp Emp (Heap scan) (Heap scan) Finance Dept Finance (Heap scan) (Heap scan) 3) HJA (Heap scan) (Heap scan) 4) NLJ SMJ HJ Finance (Heap scan) Finance (Heap scan) Emp Dept OB.sal>100,000 Dept (Heap scan) (Heap scan) (Heap scan) Emp (Index scan (Emp.salary)) Consider the following relational schema and SQL query. The schema captures information about employees, departments, and company finances (organized on a per department basis). Emp (eid: integer, did: integer, sal: integer, hobby: char (20)) Dept (did: integer, dname: char (20), floor: integer, phone: char (10)) Finance (did: integer, budget: real, sales: real, expenses: real) Consider the following query: SELECT D.dname, F.budget FROM Emp E, Dept D, Finance F WHERE E.did = D.did AND D.did = F.did AND E.sal > 100,000 AND E.hobby IN ('diving', 'soccer'); The system's statistics indicate that employee salaries range from 50,000 to 150,000, and employees enjoy 50 different hobbies. There is a total of 25,000 employees and 1,200 departments (each with corresponding financial record in the Finance relation) in the database. Each relation fits 100 tuples in a page. Suppose there exists a clustered B+ tree index on (Dept.did) and a clustered B+ tree index on (Emp.salary), both of size 50 pages. a) Compute the reduction factors and the estimated result size in number of tuples. (2 marks) b) Compute the cost in number of disk I/O's of the plans shown below. Assume that sorting of any relation (if required) can be done in 2 passes. NLJ is a Page-oriented Nested Loops Join. Assume that did is the candidate key, and that 50 tuples of a resulting join between Emp and Dept fit in a page. Similarly, 50 tuples of a resulting join between Finance and Dept fit in a page. Any selections/projections not indicated on the plan are performed "on the fly" after all joins have been completed. (8 marks, 2 marks per plan) NLJ Dept NLJ 1) SMJ 2) HJ D Emp Emp (Heap scan) (Heap scan) Finance Dept Finance (Heap scan) (Heap scan) 3) HJA (Heap scan) (Heap scan) 4) NLJ SMJ HJ Finance (Heap scan) Finance (Heap scan) Emp Dept OB.sal>100,000 Dept (Heap scan) (Heap scan) (Heap scan) Emp (Index scan (Emp.salary))