(1).The Globetrotters Club is organized into chapters. The president of a chapter can never serve as the president of any other chapter, and each chapter gives its president some salary. Chapters keep moving to new locations, and a new president is elected when (and only when) a chapter moves. This data is stored in a relation G(C,S,L,P), where the attributes are chapters (C), salaries (S), locations (L), and presidents (P). Queries of the following form are frequently asked, and you must be able to answer them without computing a join: “Who was the president of chapter X when it was in location Y ?”

1. List the FDs that are given to hold over G.

2. What are the candidate keys for relation G?

3. What normal form is the schema G in?

4. Design a good database schema for the club. (Remember that your design must satisfy the stated query requirement!)

5. What normal form is your good schema in? Give an example of a query that is likely to run slower on this schema than on the relation G.

6. Is there a lossless-join, dependency-preserving decomposition of G into BCNF?

7. Is there ever a good reason to accept something less than 3NF when designing a schema for a relational database? Use this example, if necessary adding further constraints, to illustrate your answer.

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

Prof(ssno, pname, o?ce, age, sex, specialty, dept did) Dept(did, dname, budget, num majors, chair ssno)

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

List the names, ages, and o?ces of professors of a user-speci?ed sex (male or female) who have a user-speci?ed research specialty (e.g., recursive query process-ing). Assume that the university has a diverse set of faculty members, making it very uncommon for more than a few professors to have the same research specialty.

List all the department information for departments with professors in a user-speci?ed age range.

List the department id, department name, and chairperson name for departments with a user-speci?ed number of majors.

List the lowest budget for a department in the university.

List all the information about professors who are department chairpersons.

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 university 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 search keys are per-mitted.

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 that the set of important queries is changed to be the following:

List the number of di?erent specialties covered by professors in each depart-ment, by department.

Find the department with the fewest majors.

Find the youngest professor who is a department chairperson.