Data base design

Assignment #4 (60 Points) – COSC 5360


Problem Description

Save your time - order a paper!

Get your paper written from scratch within the tight deadline. Our service is a reliable solution to all your troubles. Place an order on any task and we will take care of it. You won’t have to worry about the quality and deadlines

Order Paper Now

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


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


Beta 30 Lozenge

3 CCC Lungs 300 220 Fever Gamma 20 Rest

3 CCC Lungs 300 330 Sore


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


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


 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}


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

theminto a single folder before submitting.


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

Handwritten assignments will not be accepted.


Computer Science homework help