CSI2132代写、R编程语言代写

日期：2022-03-26 08:32

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.