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
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+?
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
