Question

Suppose we have a database for an university.

The requirements are as follows:
1) Professors have an SSN, a name, an age, a rank, and a research specialty.
2) Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget.
3)Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.).
4) 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).
5) Professors can manage and/or work on multiple projects.
6) Each project is worked on by one or more graduate students (known as the project’s research assistants).
7) 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,
i) PSSN → Name, Age, Rank, Specialty
ii) Proj → Sponsor, Start, End, Budget
iii) GSSN → Name, Age, Program • Proj → PI
iv) GSSN, Proj → Supervisor • PI → PSSN
v) 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+?

Answer 1

(a) One possible relational schema (R) is:

Professors(SSN, Name, Age, Rank, Specialty)

Projects(ProjectNumber, Sponsor, StartDate, EndDate, Budget, PI_SSN)

GraduateStudents(SSN, Name, Age, Program)

WorksOn(PI_SSN, ProjectNumber)

Supervises(PI_SSN, GSSN)

(b) R has two candidate keys: {Professors.SSN} and {Projects.ProjectNumber}.

(c) The BCNF decomposition includes tables: Professors, Projects, GraduateStudents, WorksOn, and Supervises. It is lossless and dependency preserving.

(d) The 3NF decomposition includes tables: Professors, Projects, GraduateStudents, and WorksOn. It is lossless and dependency preserving.

(e) No, all functional dependencies (FDs) are covered in F+.

Step-by-step explanation:

(a) The relational schema captures entities and relationships: Professors, Projects, GraduateStudents, WorksOn, and Supervises.

(b) The candidate keys are derived from the functional dependencies: {Professors.SSN} and {Projects.ProjectNumber}.

(c) The BCNF decomposition ensures each table is in Boyce-Codd Normal Form and retains dependencies without loss.

(d) The 3NF decomposition further normalizes tables while preserving dependencies and ensuring lossless join.

(e) All FDs are captured in F+, indicating no additional dependencies beyond those specified.