Question

Consider the following relations:

- Employees (eno, ename, age, phone)

- Works (eno, dno, hours)

- Departments (dno, dname, location)

Assume that Employees relation consists of 300 pages with 100 tuples per page, Works relation contains 900 pages with 100 tuples per page, and Departments relation contains 300 pages with 100 tuples per page. Assume equal-size fields in each relation and uniform distribution of employees for departments in Works relation.

Consider the following query:

Select e.eno, e.ename, w.hours From Employees e, Works w Where e.eno = w.eno AND w.hours >75

Assume that \(3 \%\) of the tuples in Works satisfy the selection condition of hours \(>75\) and 50 buffer pages are available.

Assume that following indexes exit and a hash index takes \(1.21 / \mathrm{Os}\) and a \(\mathrm{B}+\) tree index takes 3 I/Os to find the rid of a tuple when the key is given.

- Index 1: Unclustered B+ tree index on Workschours, eno>

- Index 2: Unclustered hash index on Employees

- Index 3: Unclustered B+ tree index on Employees

Consider Index Nested Loop Join and Sort Merge Join in choosing an efficient plan for the above query. You may use only the given indexes in considering the best plan.

1. Describe two plans based on each of the above join methods.

2. Show estimations of cost (in disk \(1 / \mathrm{O} \mathrm{s}\) ) for both plans.

Note: For simplicity, you may ignore the cost for storing rids in data entries of indexes in your calculations. Write equations/formulas and sub formulas whenever possible. Marks will be given for each step that is shown in estimation.