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.
Hi there! Click one of our representatives below and we will get back to you as soon as possible.