Reference no: EM13509295

Consider the following BCNF relational schema for a portion of a company database (type information is not relevant to this question and is omitted):

Project(pno, proj name, proj base dept, proj mgr, topic, budget) Manager(mid, mgr name, mgr dept, salary, age, sex)

Note that each project is based in some department, each manager is employed in some department, and the manager of a project need not be employed in the same department (in which the project is based). Suppose you know that the following queries are the ?ve most common queries in the workload for this university and all ?ve are roughly equivalent in frequency and importance:

List the names, ages, and salaries of managers of a user-speci?ed sex (male or female) working in a given department. You can assume that, while there are many departments, each department contains very few project managers.

List the names of all projects with managers whose ages are in a user-speci?ed range (e.g., younger than 30).

List the names of all departments such that a manager in this department manages a project based in this department.

List the name of the project with the lowest budget.

List the names of all managers in the same department as a given project.

These queries occur much more frequently than updates, so you should build whatever indexes you need to speed up these queries. However, you should not build any un-necessary indexes, as updates will occur (and would be slowed down by unnecessary indexes). Given this information, design a physical schema for the company database that will give good performance for the expected workload. In particular, decide which attributes should be indexed and whether each index should be a clustered index or an unclustered index. Assume that both B+ trees and hashed indexes are supported by the DBMS, and that both single- and multiple-attribute index keys are permitted.

1. Specify your physical design by identifying the attributes you recommend indexing on, indicating whether each index should be clustered or unclustered and whether it should be a B+ tree or a hashed index.

2. Assume that this workload is to be tuned with an automatic index tuning wizard. Outline the main steps in the algorithm and the set of candidate con?gurations considered.

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

Find the total of the budgets for projects managed by each manager; that is, list proj mgr and the total of the budgets of projects managed by that manager, for all values of proj mgr.

Find the total of the budgets for projects managed by each manager but only for managers who are in a user-speci?ed age range.

Find the number of male managers. Find the average age of managers.