Data base design

Assignment #4 (60 Points) – COSC 5360

 

Problem Description

1. (5 Points) Prove or disprove the following statement: A relation with only two attributesisin

BCNF.

2. (10 Points) Consider a database for a hospital that has the following relation called

DoctorPatientsto store information aboutits doctors and their patients.

DoctorID Initials Specialization Office PatientID Symptom Insurance Room Treatment

1 AAA Eyes 100 111 Headache Alpha 10 Aspirin

1 AAA Ears 100 111 Headache Alpha 10 Aspirin

1 AAA Eyes 100 111 Nausea Alpha 10 Rest

1 AAA Ears 100 111 Nausea Alpha 10 Rest

2 BBB Heart 200 220 Fever Beta 20 Cold

2 BBB Heart 200 330 Sore

Throat

Beta 30 Lozenge

3 CCC Lungs 300 220 Fever Gamma 20 Rest

3 CCC Lungs 300 330 Sore

Throat

Gamma 30 Aspirin

4 DDD Feet 400 440 Pain Delta 40 IbuProfin

The following set offunctional dependencies has been identified:

DoctorID {Initials,Office}

PatientID {Insurance, Room}

{DoctorID, Symptom} Treatment

2.1 (3 points) Describe the anomalies that can occur from an insertion, a deletion, and an

update.

2.2 (4 points) Is the following decomposition of DoctorPatients a lossy decomposition? If so,

what has been lost? Show the natural join of R1 and R2 to justify your answer.

R1 = (DoctorID, Initials, Specialization,Office, PatientID, Symptom)

R2 = (PatientID, Symptom, Insurance, Room, Treatment)

2.3 (3 points) Even if we decompose DoctorPatientsso thatitisin BCNF according to the above

functional dependencies, doesredundancy still exist(considerDoctor #1)? Ifso, why?3.(45 Points) For each relation schema R and set offunctional dependencies F, complete the

following tasks:

 Compute (AB)+

 List all ofthe candidate key(s)for R

 Determine a canonical coverfor F

 If R is not in BCNF, find a lossless‐join decomposition or R into a set of BCNF

relations.

 If R is not in 3NF, find a lossless‐join, dependency‐preserving decomposition

of R into a set of 3NF relations.

3.1 R = (A, B, C, X, Y, Z)

F = {A → B, C → XZ, BX → Y, YZ → A}

3.2 R = (A, B, C,G,H, I)

F = {AB → CG, B → G, CH → I, C → G}

3.3 R = (A, B, C,D, E)

F = {A → B, C → DE, B → CD, AD → E}

Submission

Submit your assignment through Blackboard. If your assignment contains multiple files, zip

theminto a single folder before submitting.

Notes

Points can be deducted from your assignment based on the quality of its presentation.

Handwritten assignments will not be accepted.