Remove the partial dependencies and show the 2NF relations. Part II: Appendix B of your textbook describes

Remove the partial dependencies and show the 2NF relations.

 

Transcribed Image Text:

Part II: Appendix B of your textbook describes three case studies for database design. The third case study describes a small hospital in Edinburgh that specializes in the provision of health care for the elderly people. The Wellmeadows Hospital has 17 wards with a total of 240 beds available for the short- and log-term patients, and an outpatient clinic. Each ward is uniquely identified by a number (for example, ward 11) and also a ward name (for example, Orthopedic), location (for example, E Block) and total number of beds. When a patient is first referred to the hospital, he or she is allocated a unique patient number. When a patient is prescribed medication, the details are recorded. This includes the patient's name and number, drug number and name, units per day, method of administration (for example, oral or intravenous (IV)), start and finish date. An example of a Wellmeadows Hospital report used to record the details of medication given to a patient called Robert MacDonald is shown below: Wellmeadows Hospital Patient Medication Form Patient Number: P10034 Full Name: Robert MacDonald Ward Number: Ward 11 Bed Number: 84 Ward Name: Orthopaedic 10223 Drug Number 10223 Morphine 10mg/ml 10334 Tetracyclene Antibiotic 0.5mg/ml Morphine Pain Killer 10mg/ml Name Description Dosage Method of Units per Start Date Finish Date Admin Day Pain Killer Oral 50 IV 10 Oral 10 24/03/13 24/04/14 24/03/13 17/04/13 25/04/14 02/05/15 1NF relation with a combined primary key: (patient No, drugNo, startDate) fullName, ward No, ward Name, bed No, drugName, description, dosage, methodOfAdmin, unitsPerDay, finishDate) a) List the business rules that you will use to put this data into 3NE (10 points) b) Considering the 1NE list the partial dependencies and the transitive dependencies, use the notation explained above. (5 points) c) Remove the partial dependencies and show the 2NF relations. (5 points) Part II: Appendix B of your textbook describes three case studies for database design. The third case study describes a small hospital in Edinburgh that specializes in the provision of health care for the elderly people. The Wellmeadows Hospital has 17 wards with a total of 240 beds available for the short- and log-term patients, and an outpatient clinic. Each ward is uniquely identified by a number (for example, ward 11) and also a ward name (for example, Orthopedic), location (for example, E Block) and total number of beds. When a patient is first referred to the hospital, he or she is allocated a unique patient number. When a patient is prescribed medication, the details are recorded. This includes the patient's name and number, drug number and name, units per day, method of administration (for example, oral or intravenous (IV)), start and finish date. An example of a Wellmeadows Hospital report used to record the details of medication given to a patient called Robert MacDonald is shown below: Wellmeadows Hospital Patient Medication Form Patient Number: P10034 Full Name: Robert MacDonald Ward Number: Ward 11 Bed Number: 84 Ward Name: Orthopaedic 10223 Drug Number 10223 Morphine 10mg/ml 10334 Tetracyclene Antibiotic 0.5mg/ml Morphine Pain Killer 10mg/ml Name Description Dosage Method of Units per Start Date Finish Date Admin Day Pain Killer Oral 50 IV 10 Oral 10 24/03/13 24/04/14 24/03/13 17/04/13 25/04/14 02/05/15 1NF relation with a combined primary key: (patient No, drugNo, startDate) fullName, ward No, ward Name, bed No, drugName, description, dosage, methodOfAdmin, unitsPerDay, finishDate) a) List the business rules that you will use to put this data into 3NE (10 points) b) Considering the 1NE list the partial dependencies and the transitive dependencies, use the notation explained above. (5 points) c) Remove the partial dependencies and show the 2NF relations. (5 points)