EXPERIMENTAL PROCEDURE Patients visit the hospital and their visit history is maintained by the hospital staff. Different physicians may be available on different dates. They diagnose and treat the patients of all categories. Some of treatments are free while others are to be paid by the patients. Sample data of the case is shown in the following chart. Patient History Report PatientID Name Address Visit Date Physician Diagnosis Treatment P-100809 A City: X 12-02-2007 20-02-2007 29-02-2007 15-03-2007 Dr. Z Dr. F Dr. R Dr. L Chest Infection Cold Hepatitis-A Eyes Infection Free Free Paid Paid P-200145 N City: Y 10-01-2007 15-02-2007 25-03-2007 Dr. L Dr. K Dr. A Bone Fracture Cough Flu Paid Free Free Task 1 Draw a dependency diagram and transform the above data to first normal form by eliminating repeating groups such that each row in the relation is atomic. Be sure to create an appropriate name for the relation and identify primary key/s.
EXPERIMENTAL PROCEDURE
Patients visit the hospital and their visit history is maintained by the hospital staff. Different physicians may be available on different dates. They diagnose and treat the patients of all categories. Some of treatments are free while others are to be paid by the patients. Sample data of the case is shown in the following chart.
Patient History Report
|
PatientID |
Name |
Address |
Visit Date |
Physician |
Diagnosis |
Treatment |
|
P-100809 |
A |
City: X |
12-02-2007 20-02-2007 29-02-2007 15-03-2007 |
Dr. Z Dr. F Dr. R Dr. L |
Chest Infection Cold Hepatitis-A Eyes Infection |
Free Free Paid Paid |
|
P-200145 |
N |
City: Y |
10-01-2007 15-02-2007 25-03-2007 |
Dr. L Dr. K Dr. A |
Bone Fracture Cough Flu |
Paid Free Free |
Task 1
Draw a dependency diagram and transform the above data to first normal form by eliminating repeating groups such that each row in the relation is atomic. Be sure to create an appropriate name for the relation and identify primary key/s.
|
|
First Normal Form
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Step by step
Solved in 3 steps with 1 images
