(1).For each of the following queries, identify one possible reason why an optimizer might not ?nd a good plan. Rewrite the query so that a good plan is likely to be found. Any available indexes or known constraints are listed before each query; assume that the relation schemas are consistent with the attributes referred to in the query.
1. An index is available on the age attribute:
SELECT E.dno
FROM Employee E
WHERE E.age=20 OR E.age=10
2. A B+ tree index is available on the age attribute:
SELECT E.dno
FROM Employee E
WHERE E.age<20 AND E.age>10
3. An index is available on the age attribute:
SELECT E.dno
FROM Employee E WHERE 2*E.age<20
4. No index is available:
SELECT DISTINCT * FROM Employee E
5. No index is available:
SELECT AVG (E.sal) FROM Employee E GROUP BY E.dno HAVING E.dno=22
6. The sid in Reserves is a foreign key that refers to Sailors:
SELECT S.sid
FROM Sailors S, Reserves R WHERE S.sid=R.sid
(2).Consider the following BCNF relations, which describe employees and the departments they work in:
Emp (eid, sal, did)
Dept (did, location, budget)
You are told that the following queries are extremely important:
Find the location where a user-speci?ed employee works.
Check whether the budget of a department is greater than the salary of each employee in that department.
1. Describe the physical design you would choose for this relation. That is, what kind of a ?le structure would you choose for these relations, and what indexes would you create?
2. Suppose that your customers subsequently complain that performance is still not satisfactory (given the indexes and ?le organization that you chose for the rela-tions in response to the previous question). Since you cannot a?ord to buy new hardware or software, you have to consider a schema redesign. Explain how you would try to obtain better performance by describing the schema for the rela-tion(s) that you would use and your choice of ?le organizations and indexes on these relations.
3. Suppose that your database system has very ine?cient implementations of index structures. What kind of a design would you try in this case?