sqldatabasedatabase-designdatabase-normalization3nf

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


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

Solution

  • 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)