I had a lecture at college recently about normalizing databases, but since we are online I can’t really ask a lot of questions, so here I am.
Can someone tell me if I’m doing the conversion right, if not where am I mistaking?
The Task:
My solution:
UNF
# Patient Nos Patient Name Ward Nos Ward Name Bed Name # Drug Number (Drug Name) (Description) (Dosage) (Method) (Units Given) (Start Date) (Finish Date)
1NF
# Patient Nos Patient Name Ward Nos Ward Name Bed Name # Patient Nos # Drug Number Drug Name Description Dosage Method Units Given Start Date Finish Date
2NF
# Patient Nos Patient Name Ward Nos Ward Name Bed Name # Patient Nos # Drug Number Drug Name Description # Patient Nos Dosage Method Units Given Start Date Finish Date
3NF
# Patient Nos Patient Name Ward Nos Ward Name Bed Name # Patient Nos # Drug Number Drug Name Description # Patient Nos # Drug Number Dosage Method Units Given # Patient Nos Start Date Finish Date
Advertisement
Answer
Let’s start with the Patient. The only information we have about a patient is his number and name.
Patient _______ Patient Nos Patient Name
Next, we have a Ward. Based on the report, a ward can have multiple patients, while a patient is in one ward. We model a one to many relationship with a foreign key.
Patient _______ Patient Nos Patient Name Ward Nos (FK) Ward ---- Ward Nos Ward Name
Finally, we have a bed. A ward has multiple beds, while a patient has one bed. Since all we know about a bed is the bed number, we’ll add that to the patient table as another foreign key to a table we don’t have any information to define.
Patient _______ Patient Nos Patient Name Ward Nos (FK) Bed Nos (FK)
What do we know about a drug?
Drug ---- Drug Nos Drug Name Drug Description
What else do we know? A dosage is a relationship between a drug and a patient. So let’s make our first attempt. I’m assuming that the start and end date has to do with the drug, since it’s on the same line as the drug.
Dosage ------ Dosage Nos Dosage Type Dosage Method Dosage Units Given Dosage Start Date Dosage End Date Drug Nos (FK) Patient Nos (FK)
Now, we’ve tied the dosage to the drug and the patient. However, we haven’t tied the patient to the dosages. A patient can have multiple dosages, and dosages can belong to more that one patient. When we have a many to many relationship, we use a junction table to tie them together.
PatientDosage ------------- PatientDosage Nos PatientDosage Units Given PatientDosage Start Date PatientDosage End Date Patient Nos (FK) Dosage Nos (FK)
We moved the units given, start date, and end date to the PatientDosage junction table, since these fields have to do with both the patient and the dosage. So the final Dosage table would look like this.
Dosage ------ Dosage Nos Dosage Type Dosage Method Drug Nos (FK)