er, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget. • Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.). • Each project is managed by one professor (known as the project’s principal investigator). • Each project is worked on by one or more professors (known as the project’s co-investigators). • Professors can manage and/or work on multiple projects. • Each project is worked on by one or more graduat

Suppose we have a database for an university.

The requirements are as follows:

• Professors have an SSN, a name, an age, a rank, and a research specialty.

• Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.

• Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.).

• Each project is managed by one professor (known as the project’s principal investigator). • Each project is worked on by one or more professors (known as the project’s co-investigators).

• Professors can manage and/or work on multiple projects.

• Each project is worked on by one or more graduate students (known as the project’s research assistants).

• When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a (potentially different) supervisor for each one.

The FDs for the above requirements are given as,

• PSSN → Name, Age, Rank, Specialty

• Proj → Sponsor, Start, End, Budget

• GSSN → Name, Age, Program • Proj → PI

• GSSN, Proj → Supervisor • PI → PSSN

• Supervisor → PSSN

Answer the following questions:

(a) Create one relational schema (R) with all the attributes.

(b) How many candidate keys does R have? Justify your answer.

(c) Do a BCNF decomposition. Is it lossless? Is it dependency preserving?

(d) Do a 3NF decomposition. Is it lossless? Is it dependency preserving?

(e) Are there any FDs that this example has which are not in F+?