View the step-by-step solution to:

# Executives has attributes ename, title, dname, and address; all are string fields of the same length. The ename attribute is a candidate key. The...

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. 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 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. 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 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. 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 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 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

### Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

### -

Educational Resources
• ### -

Study Documents

Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

Browse Documents