Details of this Paper

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.


Paper#72860 | Written in 18-Jul-2015

Price : $22