Database Normalization: Forms, Rules, and Worked Examples

TAKEAWAYS:

• Normalization is the process of organizing data to minimize redundancy and dependency through structured decomposition into tables.
• 1NF requires atomic values and a primary key; 2NF removes partial dependencies; 3NF eliminates transitive dependencies; BCNF ensures every determinant is a candidate key.
• Anomalies (insert, update, delete) arise from poor normalization and can corrupt data integrity in multi-user systems like NTC’s call records.
• Worked examples show how to normalize a messy schema (e.g., student-course enrollment) into 3NF, reducing storage and improving query efficiency.
• Trade-offs exist: over-normalization can slow joins, while under-normalization wastes space and risks inconsistencies.
• Tools like SQL CREATE TABLE and ALTER TABLE implement normalization rules in practice (e.g., splitting a single table into Students, Courses, and Enrollments).

1. Why Normalize? The Problem of Redundancy

Databases store data in tables, but poorly designed tables suffer from redundancy (duplicate data) and anomalies (errors when updating or deleting records). For example:

erDiagram
    Student ||--o{ Enrollment : "takes"
    Enrollment ||--o{ Course : "enrolled in"
    Student {
        int SID PK
        string SName
        string City
    }
    Course {
        int CID PK
        string CName
    }
    Enrollment {
        int SID PK, FK
        int CID PK, FK
        char Grade
    }

If a student’s name is stored in both the Student and Enrollment tables, updating it in one table but not the other creates inconsistency. Normalization fixes this by:

• Eliminating redundant data (storing each fact once).
• Ensuring data integrity (no anomalies on updates/deletes).
• Improving query performance (fewer joins, smaller tables).

Real-world impact:

• eSewa: User accounts, transactions, and balances are stored in normalized tables to avoid duplicate payment records.
• NTC call records: Storing customer names in both Calls and Billing tables would cause anomalies if a customer’s address changes.

2. The Normalization Process: Step-by-Step Rules

Normalization follows a hierarchy of forms (1NF → 2NF → 3NF → BCNF). Each form removes specific types of redundancy.

2.1 First Normal Form (1NF)

Definition: A table is in 1NF if:

1. Every column contains atomic (indivisible) values (no lists or repeating groups).
2. There is a primary key to uniquely identify rows.

Example: A table storing student grades as a comma-separated list violates 1NF.

-- ❌ Violates 1NF (non-atomic grades)
Student (SID, SName, Grades)  -- Grades: "A,B,C" (not atomic)

Fix: Split into separate rows.

-- ✅ 1NF-compliant
Student (SID, SName)
Grade (SID, CourseID, Grade)

Visual: → Split into two tables:

2.2 Second Normal Form (2NF)

Definition: A table is in 2NF if:

1. It is in 1NF.
2. No partial dependencies: All non-key attributes depend on the entire primary key (not just part of it).

Partial dependency example:

-- ❌ Violates 2NF (partial dependency)
Enrollment (SID, CID, SName, CName, Grade)

Here, SName depends only on SID (not the full key (SID, CID)), and CName depends only on CID.

Fix: Split into two tables.

-- ✅ 2NF-compliant
Enrollment (SID, CID, Grade)  -- Full key: (SID, CID)
Student (SID, SName)
Course (CID, CName)

Visual:

erDiagram
    Student ||--o{ Enrollment : "enrolled in"
    Course ||--o{ Enrollment : "takes"
    Enrollment {
        int SID PK
        int CID PK
        char Grade
    }

2.3 Third Normal Form (3NF)

Definition: A table is in 3NF if:

1. It is in 2NF.
2. No transitive dependencies: Non-key attributes depend only on the primary key (not other non-key attributes).

Transitive dependency example:

-- ❌ Violates 3NF (transitive dependency)
Department (DID, DName, HeadName, HeadSalary)

Here, HeadSalary depends on HeadName, which depends on DID. This creates redundancy if a department head changes.

Fix: Split into two tables.

-- ✅ 3NF-compliant
Department (DID, DName, HeadID)
Employee (EID, EName, Salary)

Visual: → Split into:

2.4 Boyce-Codd Normal Form (BCNF)

Definition: A stricter form of 3NF where:

• Every determinant (attribute that determines another) must be a candidate key.

BCNF example:

-- ❌ Violates BCNF (CID determines CName, but CName is not a key)
Course (CID, CName, Prerequisite)

Here, Prerequisite depends on CID, but CID is not a candidate key (since CName alone could also uniquely identify a course).

Fix: Split into two tables.

-- ✅ BCNF-compliant
Course (CID, CName)
Prerequisite (CID, RequiredCourse)

Comparison Table:

3. Worked Example: Normalizing a Messy Schema

Problem: Normalize the following schema for a university’s student-course system:

StudentCourse (SID, SName, City, Faculty, CID, CName, Credits, Grade)

Step 1: Check 1NF

• All attributes are atomic (no lists or repeating groups).
• Primary key: (SID, CID) (since a student can enroll in multiple courses).

Step 2: Check 2NF

• SName, City, Faculty depend only on SID (partial dependency).
• CName, Credits depend only on CID (partial dependency). → Split into:

Student (SID, SName, City, Faculty)
Course (CID, CName, Credits)
Enrollment (SID, CID, Grade)  -- Full key: (SID, CID)

Step 3: Check 3NF

• In Enrollment, Grade depends on (SID, CID) (no transitive dependency).
• In Student, no transitive dependencies.
• In Course, no transitive dependencies. → Already in 3NF.

Final Schema:

erDiagram
    Student ||--o{ Enrollment : "enrolled in"
    Course ||--o{ Enrollment : "takes"
    Student {
        int SID PK
        string SName
        string City
        string Faculty
    }
    Course {
        int CID PK
        string CName
        int Credits
    }
    Enrollment {
        int SID PK, FK
        int CID PK, FK
        char Grade
    }

Real-world tie:

• Daraz order tracking: Orders, customers, and products are stored in normalized tables to avoid duplicate product descriptions or customer addresses.

4. Anomalies and Their Fixes

Anomalies occur when normalization rules are violated. Here’s how they manifest:

Example:

-- ❌ Insert anomaly: Cannot add a course without a student.
INSERT INTO StudentCourse (CID, CName, Credits) VALUES (101, "DBMS", 3);
-- Error: No student enrolled in this course.

Solution: Separate Course and Enrollment tables.

5. Trade-offs of Normalization

Example:

• Ncell call records: Storing caller details in both Calls and Billing tables (denormalized) speeds up billing queries but risks inconsistencies.
• NEPSE stock data: Normalized tables ensure accurate trade histories but require joins to fetch a stock’s price and volume.

6. Practical Implementation in SQL

To implement normalization in SQL:

1. Create tables for each normalized entity.
2. Define relationships using foreign keys.
3. Use constraints (PRIMARY KEY, FOREIGN KEY) to enforce integrity.

Example:

-- Create normalized tables
CREATE TABLE Student (
    SID INT PRIMARY KEY,
    SName VARCHAR(100),
    City VARCHAR(50),
    Faculty VARCHAR(50)
);

CREATE TABLE Course (
    CID INT PRIMARY KEY,
    CName VARCHAR(100),
    Credits INT
);

CREATE TABLE Enrollment (
    SID INT,
    CID INT,
    Grade CHAR(2),
    PRIMARY KEY (SID, CID),
    FOREIGN KEY (SID) REFERENCES Student(SID),
    FOREIGN KEY (CID) REFERENCES Course(CID)
);

In the Real World

1. eSewa Transactions:

   • Idea: Normalization ensures each transaction (e.g., payment to a merchant) is stored with a unique TXN_ID, linked to User and Merchant tables.
   • Why: Prevents duplicate transactions or orphaned records when a user updates their phone number.

2. Pathao Ride Tracking:

   • Idea: Rides, drivers, and passengers are stored in separate tables with foreign keys. A ride’s status (e.g., "in progress") is stored in a RideStatus table to avoid redundancy.
   • Why: If a driver’s phone number changes, only the Driver table is updated, not every ride record.

3. NTC Call Billing:

   • Idea: Call records (CallID, CallerID, Duration) are normalized from a single table storing caller details, reducing storage and ensuring consistency when a customer’s plan changes.

Worked Example: Daraz Order Queue Problem: Daraz’s order system initially stored orders as:

Order (OrderID, CustomerID, ProductID, ProductName, Price, Quantity, Status)

Issue: If a product’s price changes, all orders for that product must be updated (update anomaly). Also, ProductName is redundant.

Normalized Solution:

erDiagram
    Customer ||--o{ Order : "places"
    Product ||--o{ Order : "contains"
    Order {
        int OrderID PK
        int CustomerID FK
        int ProductID FK
        int Quantity
        char Status
    }
    Product {
        int ProductID PK
        string ProductName
        float Price
    }

Benefit: Updating a product’s price only requires changing the Product table. Orders reference the ProductID, not the name.

Exam Tip

1. Recognize anomalies: Always check for insert/update/delete anomalies in messy schemas. Normalization eliminates them.
2. Apply rules step-by-step: Start with 1NF (atomicity + primary key), then 2NF (no partial dependencies), then 3NF (no transitive dependencies).
3. Draw ER diagrams: Use Mermaid or textbook-style diagrams to show how tables relate. Examiners love clear visuals.
4. Compare forms: Know when to stop (e.g., 3NF is often sufficient, but BCNF is needed for complex dependencies).
5. SQL practice: Write CREATE TABLE and ALTER TABLE statements to implement normalization. Past exams test this directly.
6. Real-world link: Tie examples to apps like eSewa or Daraz. Explain why normalization matters (e.g., "to avoid duplicate merchant data").

Common Pitfalls:

• Forgetting that 1NF requires atomicity (e.g., storing grades as a list).
• Stopping at 2NF when transitive dependencies exist (check 3NF).
• Over-normalizing (e.g., splitting every possible table). Use judgment based on query patterns.

Sample Question: Given the schema Employee (EID, EName, DeptID, DeptName, Salary), normalize it to 3NF and explain why the original schema has anomalies. Answer Structure:

1. Identify anomalies (transitive dependency: DeptName → DeptID → EID).
2. Split into Employee (EID, EName, DeptID, Salary) and Department (DeptID, DeptName).
3. Show how updates to DeptName now only require changing one table.