Exercise 15.4 You are given the following information:Executives has attributes ename, tit...

Exercise 15.4 You are given the following information:

Executives has attributes ename, title, dname, and address; all are string fields of the same length.
The ename attribute is a candidate key.
The relation contains 10,000 pages.
There are 10 buffer pages.

1. Consider the following query:
   SELECT E.title, E.ename FROM Executives E WHERE E.title=‘CFO’
   Assume that only 10% of Executives tuples meet the selection condition.
   1. Suppose that a clustered B+ tree index on title is (the only index) available. What is the cost of the best plan? (In this and subsequent questions, be sure to describe the plan you have in mind.)
   2. Suppose that an unclustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
   3. Suppose that a clustered B+ tree index on ename is (the only index) available. What is the cost of the best plan?
   4. Suppose that a clustered B+ tree index on address is (the only index) available. What is the cost of the best plan?
   5. Suppose that a clustered B+ tree index on (ename, title) is (the only index) available. What is the cost of the best plan?
2. Suppose that the query is as follows:
   SELECT E.ename FROM Executives E WHERE E.title=‘CFO’ AND E.dname=‘Toy’
   Assume that only 10% of Executives tuples meet the condition E.title =’CFO’, only 10% meet E.dname =′Toy′, and that only 5% meet both conditions.
   1. Suppose that a clustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
   2. Suppose that a clustered B+ tree index on dname is (the only index) available. What is the cost of the best plan?
   3. Suppose that a clustered B+ tree index on (title, dname) is (the only index) available. What is the cost of the best plan?
   4. Suppose that a clustered B+ tree index on (title, ename) is (the only index) available. What is the cost of the best plan?
   5. Suppose that a clustered B+ tree index on (dname, title, ename) is (the only index) available. What is the cost of the best plan?
   6. Suppose that a clustered B+ tree index on 〈ename, title, dname〉 is (the only index) available. What is the cost of the best plan?
3. Suppose that the query is as follows:
   SELECT E.title, COUNT(*) FROM Executives E GROUP BY E.title
   1. Suppose that a clustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
   2. Suppose that an unclustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
   3. Suppose that a clustered B+ tree index on ename is (the only index) available. What is the cost of the best plan?
   4. Suppose that a clustered B+ tree index on (ename, title) is (the only index) available. What is the cost of the best plan?
   5. Suppose that a clustered B+ tree index on (title, ename) is (the only index) available. What is the cost of the best plan?
4. Suppose that the query is as follows:

   SELECT E.title, COUNT(*) FROM Executives EWHERE E.dname > ‘W%’ GROUP BY E.title

   
   Assume that only 10% of Executives tuples meet the selection condition.
   1. Suppose that a clustered B+ tree index on title is (the only index) available. What is the cost of the best plan? If an additional index (on any search key you want) is available, would it help produce a better plan?
   2. Suppose that an unclustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
   3. Suppose that a clustered B+ tree index on dname is (the only index) available. What is the cost of the best plan? If an additional index (on any search key you want) is available, would it help to produce a better plan?
   4. Suppose that a clustered B+ tree index on (dname, title) is (the only index) available. What is the cost of the best plan?
   5. Suppose that a clustered B+ tree index on (title, dname) is (the only index) available. What is the cost of the best plan?