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. 
 
 
 

Still stressed with your coursework?
Get quality coursework help from an expert!