# 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.

**Other questions 10**

- For Excellentassignments Fix Broken problems..
- business 101
- As an economist, you have been asked to address a meeting of a group of international professionals to explain the...
- What does the word properties mean?
- describe the cause and effects of fame, fortune, and revenge in the work "beowulf"
- goods brout into into the country
- BMI
- X + y = 5 X - y = -1
- A city in the Pacific Northwest recorded its highest temperature at 74 degrees Fahrenheit and its lowest temperature at 23...
- Can some good pro* help me with my questions. Money is waiting for you.