Exercise 15.4 You are given the following information:
Executives has attributes ename, title, dname, and address; all are stringfields of the same length.
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.
(a) 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.)
(b) Suppose that an unclustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
(c) Suppose that a clustered B+ tree index on ename is (the only index) available. What is the cost of the best plan?
(d) Suppose that a clustered B+ tree index on address is (the only index) available. What is the cost of the best plan?
(e) 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.
(a) Suppose that a clustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
(b) Suppose that a clustered B+ tree index on dname is (the only index) available. What is the cost of the best plan?
(c) Suppose that a clustered B+ tree index on 〈title, dname〉 is (the only index) available. What is the cost of the best plan?
(d) Suppose that a clustered B+ tree index on 〈title, ename〉 is (the only index) available. What is the cost of the best plan?
(e) Suppose that a clustered B+ tree index on 〈dname, title, ename〉 is (the only index) available. What is the cost of the best plan?
(f) 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
(a) Suppose that a clustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
(b) Suppose that an unclustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
(c) Suppose that a clustered B+ tree index on ename is (the only index) available. What is the cost of the best plan?
(d) Suppose that a clustered B+ tree index on 〈ename, title〉 is (the only index) available. What is the cost of the best plan?
(e) 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.
(a) 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?
(b) Suppose that an unclustered B+ tree index on title is (the only index) available. What is the cost of the best plan?
(c) 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?
(d) Suppose that a clustered B+ tree index on 〈dname, title〉 is (the only index) available. What is the cost of the best plan?
(e) Suppose that a clustered B+ tree index on 〈title, dname〉 is (the only index) available. What is the cost of the best plan?
We need at least 10 more requests to produce the solution.
0 / 10 have requested this problem solution
The more requests, the faster the answer.