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.

Assignment #4 (60 Points) – COSC 5360 – Dr. Leonard Brown 
Due:  April 15, 2013 (at the beginning of class) 

 
 
Problem Description 
1.  (5 Points) Prove or disprove the following statement:  A relation with only two attributes is in 
BCNF. 
 
 
2.    (10  Points)  Consider  a  database  for  a  hospital  that  has  the  following  relation  called 
DoctorPatients to store information about its 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 of functional 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 DoctorPatients so that it is in BCNF according to the above 
functional dependencies, does redundancy still exist (consider Doctor #1)?  If so, why? 
 
 
 
 

3. (45 Points) For each relation schema R and set of functional dependencies F, complete the 
following tasks: 

 Compute (AB)+ 
 List all of the candidate key(s) for R 
 Determine a canonical cover for 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 
them into 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.