Reference no: EM13509317

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

Emp (eid, ename, addr, sal, age, yrs, deptid) Dept (did, dname, ?oor, 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-speci?ed age range.

List the id, name, and address of employees who work in the department with a user-speci?ed department name.

List the id and address of employees with a user-speci?ed 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 ?oor 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 at-tributes 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 sup-ported by the DBMS and that both single- and multiple-attribute keys are per-mitted. 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-speci?ed 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 de-partment.

List the sum of the budgets of all departments by ?oor; that is, for each ?oor, list the ?oor 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 con?gurations that would be considered.