Scribd
Upload
179 views4 pages

Hospital Normalization Example

The document outlines the process of normalizing a hospital management system's data table, identifying functional dependencies and addressing issues related to multivalued and partial dependencies. It details the steps taken to achieve First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF), resulting in a structured schema with separate tables for Patient, Doctor, Hospital, and Prescription. Additionally, it includes a bonus exercise for students to practice normalization.

Uploaded by

Pratit Raj Giri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
179 views4 pages

Hospital Normalization Example

The document outlines the process of normalizing a hospital management system's data table, identifying functional dependencies and addressing issues related to multivalued and partial dependencies. It details the steps taken to achieve First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce-Codd Normal Form (BCNF), resulting in a structured schema with separate tables for Patient, Doctor, Hospital, and Prescription. Additionally, it includes a bonus exercise for students to practice normalization.

Uploaded by

Pratit Raj Giri
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Real-Life Example: Functional Dependencies and Normalization in a Hospital Management System

🏥 Hospital Data Table (Unnormalized)

PatientTreatment(
patient_id, patient_name, doctor_id, doctor_name,
hospital_id, hospital_name, admitted_date, room_no,
medicine_1, medicine_2, dosage_1, dosage_2
)

Assume this sample data:

(1, 'Rita', 101, 'Dr. Shah', 201, 'Koshi Hospital', '2025-06-10', 'A203',
'Paracetamol', 'Amoxicillin', '500mg', '250mg')
(2, 'Ram', 101, 'Dr. Shah', 201, 'Koshi Hospital', '2025-06-11', 'A203',
'Paracetamol', NULL, '500mg', NULL)

🔍 Task 1: Functional Dependencies

Identified FDs:

1. patient_id → patient_name, admitted_date, room_no


2. doctor_id → doctor_name
3. hospital_id → hospital_name
4. patient_id → doctor_id, hospital_id
5. (patient_id, medicine_1) → dosage_1
6. (patient_id, medicine_2) → dosage_2

🔄 Task 2: Trivial and Non-Trivial Dependencies

✔ Trivial:

• patient_id, doctor_id → patient_id (RHS is subset of LHS)

❌ Non-Trivial:

• patient_id → doctor_id (RHS is not a subset of LHS)


• doctor_id → doctor_name

1
⟳ Task 3: Attribute Closure

Find closure of {patient_id}:

Start: {patient_id} - From FD1: → patient_name, admitted_date, room_no - From FD4: → doctor_id,
hospital_id - From FD2: doctor_id → doctor_name - From FD3: hospital_id → hospital_name - From FD5/FD6:
→ medicine_1, dosage_1, medicine_2, dosage_2

Result:

{patient_id, patient_name, admitted_date, room_no, doctor_id, doctor_name,


hospital_id, hospital_name, medicine_1, medicine_2, dosage_1, dosage_2}

Conclusion: patient_id is a superkey.

📊 Task 4: Step-by-Step Normalization

🔝 First Normal Form (1NF)

Issue: medicine_1, medicine_2 are multivalued

Fix: Convert to atomic rows:

(patient_id, medicine, dosage)

🔝 Second Normal Form (2NF)

• Partial dependencies exist: patient_id → patient_name, room_no, etc.


• Decompose:

Tables:

• Patient(patient_id, patient_name, admitted_date, room_no, doctor_id, hospital_id)


• Doctor(doctor_id, doctor_name)
• Hospital(hospital_id, hospital_name)
• Prescription(patient_id, medicine, dosage)

🔝 Third Normal Form (3NF)

Check for transitive dependencies: - doctor_id → doctor_name - hospital_id → hospital_name

These are already separated ✔

2
🔝 Boyce-Codd Normal Form (BCNF)

• All LHS of FDs are superkeys in respective tables ✔

📃 Final BCNF Schema

CREATE TABLE Patient (


patient_id INT PRIMARY KEY,
patient_name VARCHAR(100),
admitted_date DATE,
room_no VARCHAR(10),
doctor_id INT,
hospital_id INT,
FOREIGN KEY (doctor_id) REFERENCES Doctor(doctor_id),
FOREIGN KEY (hospital_id) REFERENCES Hospital(hospital_id)
);

CREATE TABLE Doctor (


doctor_id INT PRIMARY KEY,
doctor_name VARCHAR(100)
);

CREATE TABLE Hospital (


hospital_id INT PRIMARY KEY,
hospital_name VARCHAR(100)
);

CREATE TABLE Prescription (


patient_id INT,
medicine VARCHAR(100),
dosage VARCHAR(50),
PRIMARY KEY (patient_id, medicine),
FOREIGN KEY (patient_id) REFERENCES Patient(patient_id)
);

📄 Summary Table

Step Issue Action

1NF Multivalued Separate into atomic tuples

2NF Partial dependencies Split Patient from Prescription

3
Step Issue Action

3NF Transitive Moved Doctor and Hospital to new tables

BCNF Superkey check All tables verified

🎓 Bonus Exercise for Students


Use the following relation and bring it to BCNF:

Staff(patient_id, doctor_id, department, doctor_name, department_head)

FDs: - doctor_id → doctor_name, department - department → department_head

You might also like