Consider the following BCNF relations, which describe employees and departments that they work in:
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.
1. Describe the physical design that you would choose for this relation. That is, what kind of a file 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 file organization that you chose for the relations in response to the previous question). Since you cannot afford 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 relation(s) that you would use and your choice of file organizations and indexes on these relations.
3. Suppose that your database system has very inefficient implementations of index structures. What kind of a design would you try in this case?