6.     Exercise 15.2 Consider a relation with this schema:

Ernployees(eid:  integer, ename:  string,   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)   sol> 100

(b)          =  25

(c)   20

(d)   eid =   1, 000

(e)   sal>  200     age> 30

(f)                age = 20

(g)  sal>                     ='CFO'

(h)  sal> 200        age>                   ='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 ex• pensive   method and state its cost.

Suppose that, for each of the preceding selection conditions, you want to compute the av-• salary for each  age group.  For each selection condition, describe the least   expensive

evaluation  method  and  state  its cost.

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

 

 

                                                                                                                          

 

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

(a)    sal> 200 V age = 20

(b)   sal>  200 V title ='CFO'

(c)   title ='CFO' 1\  ename  ='Joe'