Drawing Initial and Optimized Data Tree

Question

Consider the database schema

emp(eid, did, sal, hobby)

dept(did, dname, floor, phone)

finance(did, budget, sales, expenses)

and 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 D.floor=1

and E.sal>=49000 and E.hobby=’tennis’

(a) Draw the initial query tree and an optimized query tree produced by a decent query

(b) Assume that the optimizer will not consider any evaluation plan involving a cross product, list join orders that the optimizer will consider.

(c) Consider the following scenario. The attributes that have unclustered B+ tree indexes are emp.did, emp.sal, dept.floor, dept.did, and finance.did. The data in emp.sal ranges between 10,000 and 60,000. Employees have 200 different hobbies. The building of the company has 5 floors. The company has 50,000 employees and 2,500 departments, each of which has some financial information. The DBMS uses only the indexed nested loop join methods including possible pipelining. Assume that all B+ tree indexes have 3 levels.

i. For each base relation (i.e., emp, dept, and finance), estimate the number of tuples that will satisfy all non-join conditions relevant to that relation.

ii. Which join order considered by the optimizer has the lowest estimated cost?

Summary

This question belongs to computer science and discusses about query tree for a database schema.

Word count: NA