Lecture: Normalization
Hospital Management System
Unnormalized Table
PatientID PatientName DoctorID DoctorName VisitDate Symptoms Medication DoctorSpecialization
2024-01- Fever, Paracetamol,
P001 John Doe D101 Dr. Smith General Medicine
10 Cough Cough Syrup
2024-02- Headache, Aspirin,
P002 Jane Roe D102 Dr. Allen Neurology
15 Nausea Antacid
2024-03-
P001 John Doe D103 Dr. White Chest Pain Painkillers Cardiology
18
2024-04-
P003 Mary Lee D102 Dr. Allen Dizziness Antivert Neurology
05
Issues in Unnormalized Table
The above table has multiple issues:
1. Repeating Groups: Symptoms and Medications contain multiple values.
2. Data Redundancy: Doctor and patient details are repeated.
3. Partial Dependencies: Some attributes depend on only part of the composite
key.
4. Transitive Dependencies: Non-key attributes depend on other non-key
attributes.
Step 1: Convert to 1NF (Eliminate Repeating Groups)
To achieve 1NF, we need to remove repeating groups, ensuring each attribute contains
atomic values.
PatientID PatientName DoctorID DoctorName VisitDate Symptom Medication DoctorSpecialization
2024-01-
P001 John Doe D101 Dr. Smith Fever Paracetamol General Medicine
10
2024-01-
P001 John Doe D101 Dr. Smith Cough Cough Syrup General Medicine
10
2024-02-
P002 Jane Roe D102 Dr. Allen Headache Aspirin Neurology
15
2024-02-
P002 Jane Roe D102 Dr. Allen Nausea Antacid Neurology
15
2024-03-
P001 John Doe D103 Dr. White Chest Pain Painkillers Cardiology
18
2024-04-
P003 Mary Lee D102 Dr. Allen Dizziness Antivert Neurology
05
Step 2: Convert to 2NF (Eliminate Partial Dependencies)
In 2NF, we remove partial dependencies, ensuring all non-key attributes depend on the
entire primary key. Here, we have a composite key of PatientID, DoctorID, and
VisitDate.
1. Separate Patient Information:
o Patient Table: Contains unique PatientID and PatientName.
2. Separate Doctor Information:
o Doctor Table: Contains unique DoctorID, DoctorName, and
DoctorSpecialization.
3. Separate Visit Information:
o Visit Table: Contains PatientID, DoctorID, VisitDate, Symptom, and
Medication.
New Tables in 2NF
Patient Table
PatientID PatientName
P001 John Doe
P002 Jane Roe
P003 Mary Lee
Doctor Table
DoctorID DoctorName DoctorSpecialization
D101 Dr. Smith General Medicine
D102 Dr. Allen Neurology
D103 Dr. White Cardiology
Visit Table
PatientID DoctorID VisitDate Symptom Medication
P001 D101 2024-01-10 Fever Paracetamol
P001 D101 2024-01-10 Cough Cough Syrup
P002 D102 2024-02-15 Headache Aspirin
P002 D102 2024-02-15 Nausea Antacid
P001 D103 2024-03-18 Chest Pain Painkillers
P003 D102 2024-04-05 Dizziness Antivert
Step 3: Convert to 3NF (Eliminate Transitive Dependencies)
In 3NF, we eliminate transitive dependencies, ensuring that non-key attributes do not
depend on other non-key attributes.
We already separated DoctorSpecialization from the Visit Table, so it depends
only on the primary key in Doctor Table.
Final Tables in 3NF
The tables remain as defined in 2NF since they are already in 3NF:
1. Patient Table
PatientID PatientName
P001 John Doe
P002 Jane Roe
P003 Mary Lee
2. Doctor Table
DoctorID DoctorName DoctorSpecialization
D101 Dr. Smith General Medicine
D102 Dr. Allen Neurology
D103 Dr. White Cardiology
3. Visit Table
PatientID DoctorID VisitDate Symptom Medication
P001 D101 2024-01-10 Fever Paracetamol
P001 D101 2024-01-10 Cough Cough Syrup
P002 D102 2024-02-15 Headache Aspirin
P002 D102 2024-02-15 Nausea Antacid
P001 D103 2024-03-18 Chest Pain Painkillers
P003 D102 2024-04-05 Dizziness Antivert
This structure now adheres to 3NF with minimized redundancy and enhanced data
integrity.