Demo Mode

Lesson 9 of 19

Concept 9: Data Modeling, ER Diagrams, and Normalization

The Database Disaster: A Normalization Adventure

🚨 Your Mission: Database Detective

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.

Learning Objectives

By solving this database crisis, you will:

Diagnose data redundancy problems in denormalized databases
Design Entity-Relationship (ER) diagrams to model real systems
Apply normalization rules (1NF, 2NF, 3NF) step by step
Transform messy data into clean, efficient database structures

The Crisis Unfolds

📊 The Messy Database

The hospital's current "database" is just one giant table that looks like a spreadsheet gone wrong:

sql

-- 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');

🔍 Discovering the Problems

As you examine this table, alarm bells start ringing:

Duplicate Data Everywhere: John Smith's address and phone appear twice
Update Anomalies: If Dr. Lee changes her phone, you need to update multiple rows
Insertion Problems: Can't add a new doctor without a patient appointment
Deletion Issues: Deleting the last appointment removes doctor information entirely

Step One: Understanding ER Diagrams

📐 What Are ER Diagrams?

Before we fix this mess, we need a blueprint. Entity-Relationship diagrams are our design tools:

csharp

Components of ER Diagrams:
┌─────────────┐         ┌─────────────┐
│   ENTITY    │◇────────│  ATTRIBUTE  │
│  (Patient)  │         │   (Name)    │
└─────────────┘         └─────────────┘
       │
       │ RELATIONSHIP
       │ (has appointment with)
       ▼
┌─────────────┐
│   ENTITY    │
│  (Doctor)   │
└─────────────┘

🎨 Drawing Our First ER Diagram

Let's identify the entities hiding in our messy table:

diff

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)

Step 2: First Normal Form (1NF)

🎯 Rule: Eliminate Repeating Groups

Our medication_list field violates 1NF-it contains multiple values in one field!

sql

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

🔨 Applying 1NF

sql

-- 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');

Step 3: Second Normal Form (2NF)

🎯 Rule: Remove Partial Dependencies

In 2NF, every non-key attribute must depend on the ENTIRE primary key, not just part of it.

sql

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

🔨 Applying 2NF

sql

-- 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;

Step 4: Third Normal Form (3NF)

🎯 Rule: Remove Transitive Dependencies

In 3NF, no non-key field should depend on another non-key field.

sql

-- 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 Final Solution

🎉 Our Normalized Database

sql

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

📊 The Complete ER Diagram

sql

         ┌─────────────┐
         │  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       │
                      └──────────────┘

Practical Exercise: Test Your Skills

🏋️ Challenge: Normalize This!

You've just received another messy database from a library system:

sql

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:

Identify all the problems with this design
Draw an ER diagram for the normalized version
Write CREATE TABLE statements for the normalized structure
Explain which normal form rules you applied

Real-World Impact

💡 Why This Matters

By normalizing the hospital database, we've achieved:

50% less storage space required
Zero data duplication (no more update anomalies)
Faster queries with proper indexes
Data integrity through foreign key constraints
Easier maintenance with logical structure

🚀 Next Steps

Now that you've mastered normalization:

Practice with more complex scenarios
Learn about denormalization for performance
Explore advanced normal forms (BCNF, 4NF, 5NF)
Study database design patterns

Remember: Good database design is like good architecture-it's invisible when done right, but painfully obvious when done wrong!