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.