Database Normalization for Hospital Management System

Initial Unnormalized Table

HOSPITAL_MANAGEMENT (
    DoctorID, DoctorName, DoctorPhone, DoctorEmail,
    Specialization1, Certification1, CertDate1,
    Specialization2, Certification2, CertDate2,
    DepartmentID, DepartmentName, DepartmentLocation, DepartmentHead,
    ClinicID, ClinicName, ClinicAddress, ClinicPhone,
    ConsultationDays, ConsultationHours,
    PatientID, PatientName, PatientDOB, PatientPhone, PatientEmail,
    PatientAddress, EmergencyContact, EmergencyPhone,
    Diagnosis, TreatmentDate,
    MedicationID, MedicationName, Dosage, Frequency,
    PrescriptionDate, PrescriptionEndDate
)

Sample Data

D101, "Dr. Sarah Smith", "555-0101", "[email protected]",
"Cardiology", "ABIM-001", "2020-01-15",
"Internal Medicine", "ABIM-002", "2018-06-20",
DEPT01, "Cardiology Department", "Building A, Floor 3", "Dr. James Wilson",
CL01, "Downtown Clinic", "123 Main St", "555-9999",
"Mon,Wed,Fri", "9:00-17:00",
P1001, "John Doe", "1980-05-15", "555-1234", "[email protected]",
"456 Oak St", "Jane Doe", "555-5678",
"Hypertension", "2024-01-10",
MED101, "Lisinopril", "10mg", "Once daily",
"2024-01-10", "2024-07-10"

Assignment Tasks

1. Identify all the problems with the current table structure in terms of normalization.

The initial table HOSPITAL_MANAGEMENT contains information about doctors, departments, clinics, patients, diagnoses, medications, and prescriptions in a single relation. This design violates several normalization principles and causes multiple structural problems.

Repeating Groups

The table contains repeating attributes such as:

These attributes represent multiple values for a single doctor. This violates normalization rules because a relational table should not contain repeated columns representing the same type of data.

Non-Atomic Attributes

Some attributes contain multiple values in a single field. For example:

ConsultationDays = "Mon,Wed,Fri"

This is not an atomic value because it contains multiple entries in one attribute.