Database Normalization Guide

Introduction

Database normalization is the process of organizing data to reduce redundancy and improve data integrity. Understanding normal forms is essential for designing efficient, maintainable database schemas.

Table of Contents

• Why Normalize?
• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Boyce-Codd Normal Form (BCNF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)
• Denormalization
• Practical Guidelines
• Interview Questions

Why Normalize?

Problems with Unnormalized Data

┌─────────────────────────────────────────────────────────────────────────┐
│ Orders (Unnormalized)                                                    │
├──────────┬────────────┬─────────────────┬───────────┬──────────────────┤
│ OrderId  │ Customer   │ CustomerAddress │ Products  │ ProductPrices    │
├──────────┼────────────┼─────────────────┼───────────┼──────────────────┤
│ 1        │ John Doe   │ 123 Main St     │ A, B, C   │ 10, 20, 30       │
│ 2        │ John Doe   │ 123 Main St     │ B, D      │ 20, 40           │
│ 3        │ Jane Smith │ 456 Oak Ave     │ A         │ 10               │
└──────────┴────────────┴─────────────────┴───────────┴──────────────────┘

Problems:

1. Update Anomaly: Changing John’s address requires updating multiple rows
2. Insert Anomaly: Can’t add a new customer without an order
3. Delete Anomaly: Deleting Jane’s only order loses her information
4. Data Redundancy: Customer info repeated for each order
5. Complex Queries: Parsing comma-separated values

Benefits of Normalization

First Normal Form (1NF)

Rules

1. Each column contains only atomic (indivisible) values
2. Each column contains values of a single type
3. Each row is unique (has a primary key)
4. No repeating groups or arrays

Violation Example

-- ❌ NOT in 1NF: Multiple values in single column
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    Products VARCHAR(500)  -- "Laptop, Mouse, Keyboard"
);

INSERT INTO Orders VALUES (1, 'John', 'Laptop, Mouse, Keyboard');

1NF Solution

-- ✅ 1NF: Atomic values, separate rows
CREATE TABLE Orders (
    OrderId INT,
    CustomerName VARCHAR(100),
    ProductName VARCHAR(100),
    PRIMARY KEY (OrderId, ProductName)
);

INSERT INTO Orders VALUES (1, 'John', 'Laptop');
INSERT INTO Orders VALUES (1, 'John', 'Mouse');
INSERT INTO Orders VALUES (1, 'John', 'Keyboard');

Visual Transformation

BEFORE (Not 1NF):
┌─────────┬──────────────┬─────────────────────────┐
│ OrderId │ CustomerName │ Products                │
├─────────┼──────────────┼─────────────────────────┤
│ 1       │ John         │ Laptop, Mouse, Keyboard │
└─────────┴──────────────┴─────────────────────────┘

AFTER (1NF):
┌─────────┬──────────────┬─────────────┐
│ OrderId │ CustomerName │ ProductName │
├─────────┼──────────────┼─────────────┤
│ 1       │ John         │ Laptop      │
│ 1       │ John         │ Mouse       │
│ 1       │ John         │ Keyboard    │
└─────────┴──────────────┴─────────────┘

Second Normal Form (2NF)

Rules

1. Must be in 1NF
2. All non-key columns must depend on the entire primary key
3. No partial dependencies (applies to composite keys)

Violation Example

-- ❌ NOT in 2NF: ProductPrice depends only on ProductName, not full key
CREATE TABLE OrderItems (
    OrderId INT,
    ProductName VARCHAR(100),
    Quantity INT,
    ProductPrice DECIMAL(10,2),  -- Depends only on ProductName!
    PRIMARY KEY (OrderId, ProductName)
);

Partial Dependency: ProductPrice depends on ProductName alone, not on (OrderId, ProductName)

2NF Solution

-- ✅ 2NF: Separate tables for different dependencies

-- Products table (ProductPrice depends on ProductName)
CREATE TABLE Products (
    ProductId INT PRIMARY KEY,
    ProductName VARCHAR(100),
    ProductPrice DECIMAL(10,2)
);

-- OrderItems table (Quantity depends on full key)
CREATE TABLE OrderItems (
    OrderId INT,
    ProductId INT,
    Quantity INT,
    PRIMARY KEY (OrderId, ProductId),
    FOREIGN KEY (ProductId) REFERENCES Products(ProductId)
);

Visual Transformation

BEFORE (1NF, Not 2NF):
┌─────────┬─────────────┬──────────┬──────────────┐
│ OrderId │ ProductName │ Quantity │ ProductPrice │
├─────────┼─────────────┼──────────┼──────────────┤
│ 1       │ Laptop      │ 2        │ 1000         │ ← Price depends only on Product
│ 1       │ Mouse       │ 5        │ 25           │
│ 2       │ Laptop      │ 1        │ 1000         │ ← Same price, redundant!
└─────────┴─────────────┴──────────┴──────────────┘

AFTER (2NF):
Products:                          OrderItems:
┌───────────┬─────────────┬───────┐   ┌─────────┬───────────┬──────────┐
│ ProductId │ ProductName │ Price │   │ OrderId │ ProductId │ Quantity │
├───────────┼─────────────┼───────┤   ├─────────┼───────────┼──────────┤
│ 1         │ Laptop      │ 1000  │   │ 1       │ 1         │ 2        │
│ 2         │ Mouse       │ 25    │   │ 1       │ 2         │ 5        │
└───────────┴─────────────┴───────┘   │ 2       │ 1         │ 1        │
                                      └─────────┴───────────┴──────────┘

Third Normal Form (3NF)

Rules

1. Must be in 2NF
2. No transitive dependencies
3. Non-key columns must depend only on the primary key, not on other non-key columns

Violation Example

-- ❌ NOT in 3NF: CustomerCity depends on CustomerZip, not OrderId
CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    CustomerName VARCHAR(100),
    CustomerZip VARCHAR(10),
    CustomerCity VARCHAR(100)  -- City depends on Zip, not OrderId!
);

Transitive Dependency: OrderId → CustomerZip → CustomerCity

3NF Solution

-- ✅ 3NF: Eliminate transitive dependencies

CREATE TABLE ZipCodes (
    ZipCode VARCHAR(10) PRIMARY KEY,
    City VARCHAR(100),
    State VARCHAR(50)
);

CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ZipCode VARCHAR(10),
    FOREIGN KEY (ZipCode) REFERENCES ZipCodes(ZipCode)
);

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

Visual Transformation

BEFORE (2NF, Not 3NF):
┌─────────┬────────────┬─────────────┬─────────┬──────────────┐
│ OrderId │ CustomerId │ CustomerZip │ City    │ State        │
├─────────┼────────────┼─────────────┼─────────┼──────────────┤
│ 1       │ 100        │ 10001       │ New York│ NY           │
│ 2       │ 101        │ 10001       │ New York│ NY           │ ← Redundant!
│ 3       │ 102        │ 90210       │ Beverly │ CA           │
└─────────┴────────────┴─────────────┴─────────┴──────────────┘

AFTER (3NF):
ZipCodes:                    Customers:                   Orders:
┌─────────┬──────────┬─────┐ ┌────────┬───────┬─────────┐ ┌─────────┬────────┐
│ ZipCode │ City     │State│ │CustId  │ Name  │ ZipCode │ │ OrderId │ CustId │
├─────────┼──────────┼─────┤ ├────────┼───────┼─────────┤ ├─────────┼────────┤
│ 10001   │ New York │ NY  │ │ 100    │ John  │ 10001   │ │ 1       │ 100    │
│ 90210   │ Beverly  │ CA  │ │ 101    │ Jane  │ 10001   │ │ 2       │ 101    │
└─────────┴──────────┴─────┘ │ 102    │ Bob   │ 90210   │ │ 3       │ 102    │
                             └────────┴───────┴─────────┘ └─────────┴────────┘

Boyce-Codd Normal Form (BCNF)

Rules

1. Must be in 3NF
2. For every functional dependency X → Y, X must be a superkey
3. Every determinant must be a candidate key

When BCNF Differs from 3NF

BCNF is stricter than 3NF. The difference appears when:

• A table has multiple overlapping candidate keys
• A non-prime attribute determines part of a candidate key

Violation Example

-- Student course enrollment with instructor assignment
-- Constraints:
--   Each student takes each course from one instructor
--   Each instructor teaches only one course

CREATE TABLE Enrollments (
    StudentId INT,
    Course VARCHAR(50),
    Instructor VARCHAR(50),
    PRIMARY KEY (StudentId, Course)
);

-- Functional dependencies:
-- (StudentId, Course) → Instructor  ✓ Key determines non-key
-- Instructor → Course               ✗ Non-key determines part of key!

BCNF Solution

-- ✅ BCNF: Split to ensure determinants are superkeys

CREATE TABLE Instructors (
    InstructorId INT PRIMARY KEY,
    InstructorName VARCHAR(50),
    Course VARCHAR(50)  -- Instructor determines Course
);

CREATE TABLE Enrollments (
    StudentId INT,
    InstructorId INT,
    PRIMARY KEY (StudentId, InstructorId),
    FOREIGN KEY (InstructorId) REFERENCES Instructors(InstructorId)
);

Fourth Normal Form (4NF)

Rules

1. Must be in BCNF
2. No multi-valued dependencies (unless dependent on a superkey)
3. Independent multi-valued facts should be stored separately

Violation Example

-- ❌ NOT in 4NF: Independent multi-valued facts in one table
-- Employee can have multiple skills AND multiple languages (independent)

CREATE TABLE EmployeeSkillsLanguages (
    EmployeeId INT,
    Skill VARCHAR(50),
    Language VARCHAR(50),
    PRIMARY KEY (EmployeeId, Skill, Language)
);

-- If John knows 3 skills and 2 languages, we need 3 × 2 = 6 rows!
INSERT INTO EmployeeSkillsLanguages VALUES
(1, 'C#', 'English'),
(1, 'C#', 'Spanish'),
(1, 'SQL', 'English'),
(1, 'SQL', 'Spanish'),
(1, 'Azure', 'English'),
(1, 'Azure', 'Spanish');

4NF Solution

-- ✅ 4NF: Separate independent multi-valued dependencies

CREATE TABLE EmployeeSkills (
    EmployeeId INT,
    Skill VARCHAR(50),
    PRIMARY KEY (EmployeeId, Skill)
);

CREATE TABLE EmployeeLanguages (
    EmployeeId INT,
    Language VARCHAR(50),
    PRIMARY KEY (EmployeeId, Language)
);

-- Now: 3 skills + 2 languages = 5 rows instead of 6
INSERT INTO EmployeeSkills VALUES (1, 'C#'), (1, 'SQL'), (1, 'Azure');
INSERT INTO EmployeeLanguages VALUES (1, 'English'), (1, 'Spanish');

Fifth Normal Form (5NF)

Rules

1. Must be in 4NF
2. Cannot be decomposed further without losing information
3. No join dependencies that aren’t implied by candidate keys

When to Consider 5NF

5NF addresses complex scenarios with three or more related entities where the relationship between any two depends on the third.

-- Agents sell Products in certain Regions
-- An agent might sell a product in one region but not another
-- A region might have a product through one agent but not another

-- ✅ 5NF: Decompose into three binary relationships if they're independent
CREATE TABLE AgentProducts (AgentId, ProductId);
CREATE TABLE AgentRegions (AgentId, RegionId);
CREATE TABLE ProductRegions (ProductId, RegionId);

-- ❌ But if the ternary relationship is NOT decomposable (agent-product-region
--    combinations are specific), keep as single table:
CREATE TABLE AgentProductRegions (
    AgentId INT,
    ProductId INT,
    RegionId INT,
    PRIMARY KEY (AgentId, ProductId, RegionId)
);

Denormalization

When to Denormalize

Denormalization intentionally introduces redundancy for performance reasons.

Common Denormalization Techniques

-- 1. Store calculated values
ALTER TABLE Orders ADD TotalAmount DECIMAL(10,2);
-- Update via trigger or application logic

-- 2. Store redundant data
ALTER TABLE OrderItems ADD ProductName VARCHAR(100);
-- Avoids JOIN to Products table for display

-- 3. Summary tables
CREATE TABLE DailySalesSummary (
    Date DATE PRIMARY KEY,
    TotalOrders INT,
    TotalRevenue DECIMAL(10,2)
);
-- Updated by scheduled job

-- 4. Materialized views (SQL Server indexed views)
CREATE VIEW vw_OrderSummary WITH SCHEMABINDING AS
SELECT
    o.CustomerId,
    COUNT_BIG(*) AS OrderCount,
    SUM(o.TotalAmount) AS TotalSpent
FROM dbo.Orders o
GROUP BY o.CustomerId;

CREATE UNIQUE CLUSTERED INDEX IX_OrderSummary
ON vw_OrderSummary(CustomerId);

Trade-offs

Practical Guidelines

Normal Form Recommendations

Normalization Decision Process

1. Start in 1NF
   └── Eliminate repeating groups and multi-valued columns

2. Check for 2NF
   └── Do you have composite keys with partial dependencies?
       └── Yes → Split into separate tables

3. Check for 3NF
   └── Do non-key columns depend on other non-key columns?
       └── Yes → Create lookup/reference tables

4. Consider BCNF
   └── Do you have overlapping candidate keys?
       └── Yes → Further decomposition may be needed

5. Evaluate Denormalization
   └── Are JOINs causing performance issues?
       └── Yes → Strategically add redundancy

Quick Reference

┌─────────────────────────────────────────────────────────────┐
│                    NORMAL FORMS SUMMARY                      │
├─────────┬───────────────────────────────────────────────────┤
│   1NF   │ Atomic values, no repeating groups                │
├─────────┼───────────────────────────────────────────────────┤
│   2NF   │ 1NF + No partial dependencies on composite key    │
├─────────┼───────────────────────────────────────────────────┤
│   3NF   │ 2NF + No transitive dependencies                  │
├─────────┼───────────────────────────────────────────────────┤
│  BCNF   │ 3NF + Every determinant is a candidate key        │
├─────────┼───────────────────────────────────────────────────┤
│   4NF   │ BCNF + No multi-valued dependencies               │
├─────────┼───────────────────────────────────────────────────┤
│   5NF   │ 4NF + No join dependencies                        │
└─────────┴───────────────────────────────────────────────────┘

Interview Questions

1. What is database normalization and why is it important?

Answer: Normalization is the process of organizing database tables to minimize data redundancy and dependency. It’s important because it:

• Prevents update anomalies (changing data in one place)
• Ensures data consistency
• Reduces storage waste
• Makes maintenance easier
• Enforces data integrity through proper relationships

2. Explain the first three normal forms (1NF, 2NF, 3NF).

Answer:

• 1NF: Each column contains atomic (indivisible) values. No repeating groups or arrays. Each row is unique.
• 2NF: Must be in 1NF, plus no partial dependencies. All non-key columns must depend on the entire primary key (relevant for composite keys).
• 3NF: Must be in 2NF, plus no transitive dependencies. Non-key columns cannot depend on other non-key columns—they must depend only on the primary key.

3. What is the difference between 3NF and BCNF?

Answer: BCNF is stricter than 3NF. In BCNF, every determinant must be a candidate key. 3NF allows a non-key attribute to determine part of a composite key, while BCNF does not.

The difference only matters when you have:

• Multiple candidate keys
• Overlapping composite keys
• A non-prime attribute determining part of a key

4. When would you choose to denormalize?

Answer: Denormalize when:

• Read performance is critical and JOINs are expensive
• Reporting queries need pre-aggregated data
• The data is relatively static (fewer update anomaly concerns)
• You have a read-heavy workload (OLAP, data warehouses)

Common techniques: calculated columns, summary tables, redundant foreign key attributes, materialized views.

5. What are update, insert, and delete anomalies?

Answer:

• Update Anomaly: Same data stored in multiple places requires updating all copies. Missing one creates inconsistency.
• Insert Anomaly: Cannot insert data without other unrelated data. Example: Can’t add a customer without an order.
• Delete Anomaly: Deleting data unintentionally removes other needed data. Example: Deleting the last order loses customer info.

Normalization eliminates these by ensuring each fact is stored once.

6. What is a transitive dependency?

Answer: A transitive dependency occurs when a non-key column depends on another non-key column, which in turn depends on the primary key.

Example: In an Orders table with CustomerId (PK), CustomerZip, and CustomerCity:

• CustomerId → CustomerZip (OK: key → non-key)
• CustomerZip → CustomerCity (Transitive: non-key → non-key)
• Therefore: CustomerId → CustomerZip → CustomerCity

To achieve 3NF, extract ZipCode and City into a separate table.

Sources

• Database Normalization Basics
• Normal Forms in DBMS
• Codd’s 12 Rules
• Database Design for Mere Mortals