CSI2132: Databases IAssignment

QQ: 942371102

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.