CSI2132: Databases I
Assignment
Winter 2022
Submission Deadline: March 25, 2022
Every student must submit the assignment individually through Brightspace.
Exercise 1 (30%)
Consider the table below showing a sample dentist/patient appointment data. A patient is given an appointment
at a specific time and date with a dentist located at a particular surgery. On each day of patient appointments, a
dentist is allocated to a specific surgery for that day.
a) (10%) Provide examples of insertion, deletion, and update anomalies
b) (10%) Identify the functional dependencies represented by the attributes shown in the table. State any
assumptions you make about the data and the attributes shown in this table
c) (10%) Describe and illustrate the process of normalizing the table to 3NF relations. Identify the primary,
alternate, and foreign keys in your 3NF relations
Exercise 2 (30%)
Consider the schema R = (B, O, I, S, Q, D) and the set F of functional dependencies: F = {S → D, I →B, IS → Q, B → O}
a) (5%) Provide a list of all candidate keys.
b) (10%) Find the canonical cover for F, along with an explanation of the steps you took to generate it.
c) (10%) Use the 3NF decomposition algorithm to generate a 3NF decomposition of R, and show all the
necessary steps.
d) (5%) Are any of the relations in BCNF or not?
Exercise 3 (40%)
Consider the schema R = (A, B, C, D, E, G) and the set of FDs: F = {A → BC, BD → E, AD → E, CD → AB}
a) (10%) Find a nontrivial functional dependency containing no extraneous attributes that is logically implied
by the above dependencies and explain how you found it.
b) (10%) Find all candidate keys of R.
c) (10%) Use the BCNF decomposition algorithm to find a BCNF decomposition of R. Start with A → BC.
Explain your steps.
d) (5%) For your decomposition, state whether it is lossless and explain why.
e) (5%) For your decomposition, state whether it is dependency preserving and explain why.