Exercise 15.2 Consider a relation with this schema: Employees(eid: integer, ename: string,...

Exercise 15.2 Consider a relation with this schema:

 Employees(eid: integer, ename: string, sal: integer, title: string, age: integer)

Suppose that the following indexes, all using Alternative (2) for data entries, exist: a hash index on eid, a B+ tree index on sal, a hash index on age, and a clustered B+ tree index on 〈age, sal〉. Each Employees record is 100 bytes long, and you can assume that each index data entry is 20 bytes long. The Employees relation contains 10,000 pages.

1. Consider each of the following selection conditions and, assuming that the reduction factor (RF) for each term that matches an index is 0.1, compute the cost of the most selective access path for retrieving all Employees tuples that satisfy the condition:

(a) sal > 100

(b) age = 25

(c) age > 20

(d) eid = 1, 000

(e) sal > 200 ∧ age > 30

(f) sal > 200 ∧ age = 20

(g) sal > 200 ∧ title =‘CFO’

(h) sal > 200 ∧ age > 30 ∧ title =‘CFO’

2. Suppose that, for each of the preceding selection conditions, you want to retrieve the average salary of qualifying tuples. For each selection condition, describe the least expensive evaluation method and state its cost.

3. Suppose that, for each of the preceding selection conditions, you want to compute the average salary for each age group. For each selection condition, describe the least expensive evaluation method and state its cost.

4. Suppose that, for each of the preceding selection conditions, you want to compute the average age for each sal level (i.e., group by sal). For each selection condition, describe the least expensive evaluation method and state its cost.

5. For each of the following selection conditions, describe the best evaluation method:

(a) sal > 200 ∨ age = 20

(b) sal > 200 ∨ title =‘CFO’

(c) title =‘CFO’ ∧ ename =‘Joe’