Normalize an Excel Spreadsheet into a 3NF Database Case Study

Every normalization concept covered in this module — 1NF, 2NF, 3NF — makes the most sense when applied to something realistically messy, like the flat Excel spreadsheet a hospital's billing department might have used for years before finally moving to a proper database. This lesson works through exactly that transformation, start to finish, applying each normal form in sequence to a single, deliberately unnormalized starting point.

Key Definitions

• Flat file: A single table or spreadsheet storing all data together, typically the unnormalized starting point before a proper relational schema is designed.
• Normalization workflow: The practical, sequential process of applying 1NF, then 2NF, then 3NF (and beyond, if needed) to progressively restructure a flat dataset into a well-designed relational schema.

What You'll Learn

• Analyze a realistic flat spreadsheet for normalization violations.
• Apply 1NF, 2NF, and 3NF sequentially to progressively normalize the data.
• Produce a final, properly structured set of 3NF tables.
• Understand how the individual normal form lessons combine into one complete real-world workflow.

Detailed Explanation

The starting spreadsheet, BillingRecords, has one row per bill and these columns: bill_id, patient_name, patient_city, doctor_name, doctor_phone_numbers (comma-separated), department_name, department_location, treatment_date, amount. This single flat structure violates multiple normal forms simultaneously, and the case study works through fixing each in order.

Step 1 — Achieve 1NF: doctor_phone_numbers contains comma-separated values, violating atomicity. Following Lesson 8.7's approach, this becomes its own doctor_phones table, with one phone number per row, linked back via doctor_name (temporarily, until doctors get a proper ID in the next step).

Step 2 — Achieve 2NF: Since bill_id alone is already the primary key of this flat table (no composite key), there's technically no partial dependency to fix at this stage — 2NF is automatically satisfied once 1NF is achieved, since 2NF only becomes a relevant concern with composite keys, as covered in Lesson 8.8.

Step 3 — Achieve 3NF: Now the real redundancy work begins. patient_city depends only on patient_name, not on the full bill_id — a transitive dependency, so patient details move into their own patients table. Likewise, department_location depends only on department_name, not on bill_id — another transitive dependency, so department details move into their own departments table. doctor_name and its now-properly-linked phone numbers depend on the doctor, not the bill, so doctor details move into their own doctors table, with a foreign key linking to department. What remains in the original table — now genuinely just bill-specific facts like treatment_date and amount, referencing patient_id and doctor_id — becomes the final, properly normalized bills table.

The end result is the now-familiar five-table structure: departments, doctors, doctor_phones, patients, and bills — arrived at not by memorizing a target schema, but by mechanically applying each normal form's rule to a genuinely messy starting point.

Visual Summary

A four-stage horizontal pipeline: [Stage 0: Flat BillingRecords spreadsheet, 9 columns, heavy redundancy] --> [Stage 1: Apply 1NF — extract doctor_phones] --> [Stage 2: Check 2NF — already satisfied, single-column key] --> [Stage 3: Apply 3NF — extract patients, departments, doctors] --> [Final: 5 clean, normalized tables].

Quick Reference

Original Column	Violation Identified	Final Destination Table
doctor_phone_numbers	1NF — comma-separated, not atomic	doctor_phones
patient_city	3NF — transitively dependent via patient_name, not bill_id	patients
department_location	3NF — transitively dependent via department_name, not bill_id	departments
doctor_name	3NF — transitively dependent via doctor, not bill_id	doctors
treatment_date, amount	No violation — genuinely depends on bill_id directly	bills (remains)

SQL Example

-- STAGE 0: the original flat, unnormalized spreadsheet structure
-- bill_id | patient_name | patient_city | doctor_name | doctor_phone_numbers | department_name | department_location | treatment_date | amount

-- FINAL RESULT after applying 1NF, 2NF, and 3NF in sequence:
CREATE TABLE departments (
  department_id       INT PRIMARY KEY AUTO_INCREMENT,
  department_name     VARCHAR(100) NOT NULL,
  department_location VARCHAR(100)
);

CREATE TABLE doctors (
  doctor_id     INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name   VARCHAR(100) NOT NULL,
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE doctor_phones (
  phone_id     INT PRIMARY KEY AUTO_INCREMENT,
  doctor_id    INT NOT NULL,
  phone_number VARCHAR(20) NOT NULL,
  FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

CREATE TABLE patients (
  patient_id   INT PRIMARY KEY AUTO_INCREMENT,
  patient_name VARCHAR(100) NOT NULL,
  patient_city VARCHAR(80)
);

CREATE TABLE bills (
  bill_id        INT PRIMARY KEY AUTO_INCREMENT,
  patient_id     INT,
  doctor_id      INT,
  treatment_date DATE,
  amount         DECIMAL(10,2),
  FOREIGN KEY (patient_id) REFERENCES patients(patient_id),
  FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

Every column from the original flat spreadsheet now has a clear, single home, chosen by mechanically applying each normal form's rule: doctor_phone_numbers was split out for 1NF's atomicity requirement, and patient_city, department_location, and doctor_name were each relocated for 3NF's transitive dependency elimination. The final bills table retains only what genuinely depends on bill_id directly — treatment_date and amount — referencing patients and doctors via clean foreign keys instead of repeating their details on every single billing row.

Real-World Examples

• Small clinics and businesses commonly start with Excel-based record keeping and eventually commission exactly this kind of normalization project as they outgrow spreadsheet-based data management.
• Data migration consultancies frequently perform this exact spreadsheet-to-database normalization process as a standalone paid engagement for growing small businesses.
• Database bootcamps and certification courses use spreadsheet-to-3NF case studies as a standard capstone exercise to test whether students can apply normal forms to messy, realistic data rather than pre-cleaned textbook examples.

Common Mistakes to Avoid

• Trying to apply all normal forms simultaneously instead of working through 1NF, 2NF, then 3NF in sequence.
• Missing a 1NF violation like comma-separated phone numbers because it's easy to overlook in a large, messy real spreadsheet.
• Forgetting to verify whether 2NF actually applies (only relevant for composite keys) before assuming extra work is needed at that stage.

Interview Questions

Q1. How would you approach normalizing a messy, flat spreadsheet into a proper database?

Apply the normal forms sequentially: first achieve 1NF by ensuring every column holds atomic values, then check for and eliminate partial dependencies for 2NF (only relevant if using composite keys), then check for and eliminate transitive dependencies for 3NF, moving each dependent column into its own properly-keyed table at each stage.

Q2. In a flat table with a single-column primary key, is 2NF analysis ever necessary?

No — 2NF specifically concerns partial dependencies on composite keys. If a table already has a single-column primary key after achieving 1NF, it automatically satisfies 2NF, and normalization work can proceed directly to checking for 3NF's transitive dependencies.

Practice MCQs

1. In the case study, why does 2NF require no additional work after achieving 1NF?

1. 2NF is not a real normal form
2. The table's primary key (bill_id) is a single column, not composite
3. 2NF only applies to spreadsheets
4. The data was already perfectly normalized

Answer: B. The table's primary key (bill_id) is a single column, not composite

Explanation: 2NF's partial dependency concept only applies to composite primary keys; a single-column key table automatically satisfies 2NF once it's in 1NF.

2. Why does patient_city get moved to its own patients table during the 3NF step?

1. It contains multiple values
2. It transitively depends on patient_name, not directly on bill_id
3. It has a NULL value in some rows
4. Excel spreadsheets cannot store city names

Answer: B. It transitively depends on patient_name, not directly on bill_id

Explanation: patient_city's value is determined by which patient it belongs to, not directly by the specific bill, making it a transitive dependency that 3NF requires to be relocated.

Quick Revision Points

• Real-world normalization is applied sequentially: 1NF, then 2NF (if composite keys exist), then 3NF.
• A single-column primary key table automatically satisfies 2NF once in 1NF.
• This kind of full worked case study is a common practical exam and interview exercise, testing applied normalization skill rather than just definitions.

Conclusion

• Real-world spreadsheets often violate multiple normal forms simultaneously and require sequential normalization.
• Working through 1NF, then checking 2NF, then applying 3NF produces a properly structured schema step by step.
• This case study demonstrates how the individual normal form lessons combine into one complete, practical workflow.

This case study normalizes a realistic, messy flat spreadsheet — BillingRecords, combining patient, doctor, department, and bill data into one wide table — into a properly structured 3NF database by sequentially applying each normal form covered in this module. First, comma-separated phone numbers are extracted to achieve 1NF. Second, 2NF is confirmed automatically satisfied since the table uses a single-column primary key. Third, transitive dependencies are systematically eliminated to achieve 3NF, relocating patient, doctor, and department details into their own properly-keyed tables. The result is the same five-table structure — departments, doctors, doctor_phones, patients, and bills — arrived at through mechanical, rule-based analysis rather than memorization.