Exercise 20.1 Consider the following BCNF schema for a portion of a simple corporate datab...

Exercise 20.1 Consider the following BCNF schema for a portion of a simple corporate database (type information is not relevant to this question and is omitted):

 Emp (eid, ename, addr, sal, age, yrs, deptid) Dept (did, dname, floor, budget)

Suppose you know that the following queries are the six most common queries in the workload for this corporation and that all six are roughly equivalent in frequency and importance:

■ List the id, name, and address of employees in a user-specified age range.

■ List the id, name, and address of employees who work in the department with a user-specified department name.

■ List the id and address of employees with a user-specified employee name.

■ List the overall average salary for employees.

■ List the average salary for employees of each age; that is, for each age in the database, list the age and the corresponding average salary.

■ List all the department information, ordered by department floor numbers.

1. Given this information, and assuming that these queries are more important than any updates, design a physical schema for the corporate database that will give good performance for the expected workload. In particular, decide which attributes will be indexed and whether each index will be a clustered index or an unclustered index. Assume that B+ tree indexes are the only index type supported by the DBMS and that both single- and multiple-attribute keys are permitted. Specify your physical design by identifying the attributes you recommend indexing on via clustered or unclustered B+ trees.

2. Redesign the physical schema assuming that the set of important queries is changed to be the following:

■ List the id and address of employees with a user-specified employee name.

■ List the overall maximum salary for employees.

■ List the average salary for employees by department; that is, for each deptid value, list the deptid value and the average salary of employees in that department.

■ List the sum of the budgets of all departments by floor; that is, for each floor, list the floor and the sum.

■ Assume that this workload is to be tuned with an automatic index tuning wizard. Outline the main steps in the execution of the index tuning algorithm and the set of candidate configurations that would be considered.