This is the end of the preview. Sign up
to
access the rest of the document.

Unformatted text preview: Physical Database Design and Tuning 211 2. The problem could be that the optimizer may not be considering the index only plans that could be obtained using the previously described schema. So we can instead create clustered indexes on h pid, cost i and h pname, num avail i . To do this we have to vertically partition the relation into two relations like Parts1( pid, cost ) and Parts2( pid, pname, num avail). (If the indexes themselves have not been implemented properly, then we can instead use sorted file organizations for these two split relations). 3. If the multi attribute keys are not allowed then we can have a clustered B+ Tree indexes on cost and on pname on the two relations. Exercise 20.6 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-specified employee works. Check whether the budget of a department is greater than the salary of each employee in that department....
View Full
Document