Welcome, database detective! You've been called in to fix a critical problem. MediCare Hospital's database is a complete mess-patient data is duplicated everywhere, doctor schedules are inconsistent, and appointment records don't match up.
Your mission: Transform this chaotic spreadsheet-style database into a properly normalized, efficient system using ER diagrams and normalization techniques.
By solving this database crisis, you will:
The hospital's current "database" is just one giant table that looks like a spreadsheet gone wrong:
-- The nightmare table: HospitalData
CREATE TABLE HospitalData (
record_id INT,
patient_name VARCHAR(100),
patient_phone VARCHAR(20),
patient_address TEXT,
doctor_name VARCHAR(100),
doctor_specialty VARCHAR(50),
doctor_phone VARCHAR(20),
appointment_date DATE,
appointment_time TIME,
symptoms TEXT,
prescription TEXT,
medication_list TEXT,
insurance_company VARCHAR(100),
insurance_policy VARCHAR(50)
);
-- Sample of the mess
INSERT INTO HospitalData VALUES
(1, 'John Smith', '555-1234', '123 Main St, Apt 4B', 'Dr. Sarah Lee', 'Cardiology', '555-9876',
'2024-03-15', '10:00', 'Chest pain', 'Rest, Aspirin', 'Aspirin 81mg', 'HealthCo', 'HC123456'),
(2, 'John Smith', '555-1234', '123 Main St, Apt 4B', 'Dr. Mike Chen', 'General', '555-5432',
'2024-03-20', '14:00', 'Flu symptoms', 'Antibiotics', 'Amoxicillin 500mg', 'HealthCo', 'HC123456'),
(3, 'Jane Doe', '555-5678', '456 Oak Ave', 'Dr. Sarah Lee', 'Cardiology', '555-9876',
'2024-03-15', '11:00', 'High BP', 'Lifestyle changes', 'None', 'MediSure', 'MS789012');
As you examine this table, alarm bells start ringing:
Before we fix this mess, we need a blueprint. Entity-Relationship diagrams are our design tools:
Components of ER Diagrams:
┌─────────────┐ ┌─────────────┐
│ ENTITY │◇────────│ ATTRIBUTE │
│ (Patient) │ │ (Name) │
└─────────────┘ └─────────────┘
│
│ RELATIONSHIP
│ (has appointment with)
▼
┌─────────────┐
│ ENTITY │
│ (Doctor) │
└─────────────┘
Let's identify the entities hiding in our messy table:
Entities Found:
- PATIENT (patient_name, patient_phone, patient_address)
- DOCTOR (doctor_name, doctor_specialty, doctor_phone)
- APPOINTMENT (appointment_date, appointment_time, symptoms, prescription)
- INSURANCE (insurance_company, insurance_policy)
- MEDICATION (medication_list)
Our medication_list field violates 1NF-it contains multiple values in one field!
-- Problem: medication_list = "Aspirin 81mg, Ibuprofen 200mg"
-- This is a repeating group!
-- Solution: Create a separate medications table
CREATE TABLE Medications (
medication_id INT PRIMARY KEY,
appointment_id INT,
medication_name VARCHAR(100),
dosage VARCHAR(50)
);
-- Step 1: Remove multi-valued attributes
-- Before: medication_list TEXT with "Aspirin 81mg, Ibuprofen 200mg"
-- After: Separate rows for each medication
INSERT INTO Medications VALUES
(1, 1, 'Aspirin', '81mg'),
(2, 2, 'Amoxicillin', '500mg');
In 2NF, every non-key attribute must depend on the ENTIRE primary key, not just part of it.
-- Our current mess has partial dependencies
-- patient_phone depends only on patient_name, not on the full record
-- Solution: Separate entities
CREATE TABLE Patients (
patient_id INT PRIMARY KEY AUTO_INCREMENT,
patient_name VARCHAR(100),
patient_phone VARCHAR(20),
patient_address TEXT
);
CREATE TABLE Doctors (
doctor_id INT PRIMARY KEY AUTO_INCREMENT,
doctor_name VARCHAR(100),
doctor_specialty VARCHAR(50),
doctor_phone VARCHAR(20)
);
-- Transform the data
INSERT INTO Patients (patient_name, patient_phone, patient_address)
SELECT DISTINCT patient_name, patient_phone, patient_address
FROM HospitalData;
INSERT INTO Doctors (doctor_name, doctor_specialty, doctor_phone)
SELECT DISTINCT doctor_name, doctor_specialty, doctor_phone
FROM HospitalData;
In 3NF, no non-key field should depend on another non-key field.
-- Problem: insurance_policy depends on insurance_company
-- This is a transitive dependency!
-- Solution: Separate insurance information
CREATE TABLE Insurance (
insurance_id INT PRIMARY KEY AUTO_INCREMENT,
company_name VARCHAR(100),
policy_number VARCHAR(50)
);
CREATE TABLE Appointments (
appointment_id INT PRIMARY KEY AUTO_INCREMENT,
patient_id INT,
doctor_id INT,
appointment_date DATE,
appointment_time TIME,
symptoms TEXT,
prescription TEXT,
insurance_id INT,
FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id),
FOREIGN KEY (insurance_id) REFERENCES Insurance(insurance_id)
);
-- The clean, normalized structure
-- 1. Patients table
CREATE TABLE Patients (
patient_id INT PRIMARY KEY AUTO_INCREMENT,
patient_name VARCHAR(100) NOT NULL,
patient_phone VARCHAR(20),
patient_address TEXT
);
-- 2. Doctors table
CREATE TABLE Doctors (
doctor_id INT PRIMARY KEY AUTO_INCREMENT,
doctor_name VARCHAR(100) NOT NULL,
doctor_specialty VARCHAR(50),
doctor_phone VARCHAR(20)
);
-- 3. Insurance table
CREATE TABLE Insurance (
insurance_id INT PRIMARY KEY AUTO_INCREMENT,
company_name VARCHAR(100),
policy_number VARCHAR(50) UNIQUE
);
-- 4. Appointments table (junction table)
CREATE TABLE Appointments (
appointment_id INT PRIMARY KEY AUTO_INCREMENT,
patient_id INT NOT NULL,
doctor_id INT NOT NULL,
appointment_date DATE NOT NULL,
appointment_time TIME NOT NULL,
symptoms TEXT,
prescription TEXT,
insurance_id INT,
FOREIGN KEY (patient_id) REFERENCES Patients(patient_id),
FOREIGN KEY (doctor_id) REFERENCES Doctors(doctor_id),
FOREIGN KEY (insurance_id) REFERENCES Insurance(insurance_id)
);
-- 5. Medications table
CREATE TABLE Medications (
medication_id INT PRIMARY KEY AUTO_INCREMENT,
appointment_id INT NOT NULL,
medication_name VARCHAR(100),
dosage VARCHAR(50),
FOREIGN KEY (appointment_id) REFERENCES Appointments(appointment_id)
);
┌─────────────┐
│ PATIENTS │
├─────────────┤
│ patient_id │◆────┐
│ name │ │
│ phone │ │ has
│ address │ │
└─────────────┘ │
▼
┌──────────────┐ ┌─────────────┐
│ APPOINTMENTS │◆────────│ DOCTORS │
├──────────────┤ with ├─────────────┤
│ appt_id │ │ doctor_id │
│ patient_id │ │ name │
│ doctor_id │ │ specialty │
│ date/time │ │ phone │
│ symptoms │ └─────────────┘
│ prescription │
│ insurance_id │◆────────┐
└──────────────┘ │ covered by
│ ▼
│ ┌─────────────┐
includes│ │ INSURANCE │
▼ ├─────────────┤
┌──────────────┐ │ insurance_id│
│ MEDICATIONS │ │ company │
├──────────────┤ │ policy_num │
│ med_id │ └─────────────┘
│ appt_id │
│ name │
│ dosage │
└──────────────┘
You've just received another messy database from a library system:
CREATE TABLE LibraryMess (
record_id INT,
book_title VARCHAR(200),
book_isbn VARCHAR(20),
author_names TEXT, -- "J.K. Rowling, Mary GrandPré"
publisher_name VARCHAR(100),
publisher_address TEXT,
member_name VARCHAR(100),
member_email VARCHAR(100),
member_phone VARCHAR(20),
checkout_date DATE,
due_date DATE,
fine_amount DECIMAL(10,2)
);
Your tasks:
By normalizing the hospital database, we've achieved:
Now that you've mastered normalization:
Remember: Good database design is like good architecture-it's invisible when done right, but painfully obvious when done wrong!