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.
(a) Suppose that a clustered B+ tree index on title is (the only index) available. Whatis the cost of the best plan? (In this and subsequent questions, be sure to describethe 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 enarne is (the only index) available.What is the cost of the best plan?
(d) Suppo$e that a clustered B+ tree index on address is (the only index) available.What is the cost of the best pian?
(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'
A T..7Jpical Query Opl'irnizer 51J
Assume that only 10% of Executives tuples IIleet the condition E.title ='C FO', 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. Whatis 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 t'ltle 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 E
WHERE 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. Whatis the cost of the best plan? If an additional index (on any search key you want) isavailable, would it help produce a better plan?
(b) Suppose that an unclustered B+ tree index OIl 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 youwant) 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