Skip to content
Advertisement

Have I normalized this database to a 3NF level the right way? [closed]

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:

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)

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement