Database Normalization: 1NF, 2NF, 3NF, BCNF, 4NF & 5NF Explained

This guide covers all database normalization forms — from 1NF through 5NF — with practical examples and explanations.

In a Nutshell

Normalization provides formal rules for structuring relational databases, progressively eliminating redundancy and ensuring data integrity. Each higher normal form builds on the previous one.

Core Concept

Normalization is the process of systematically improving database structures by applying formal rules. Each normal form addresses specific anomalies and dependency patterns.

Overview of Normal Forms:

1NF: Atomic values, no repeating groups
2NF: 1NF + no partial dependencies
3NF: 2NF + no transitive dependencies
BCNF: Stricter 3NF with stronger rules
4NF: 3NF + no multi-valued dependencies
5NF: 4NF + no join dependencies

Mathematical Foundations:

Functional Dependencies: X → Y
Multi-Valued Dependencies: X →→ Y
Join Dependencies: (A, B, C)

Practical Relevance: The first three normal forms (1NF–3NF) suffice for most applications. Higher forms (4NF–5NF) become important for complex data structures and academic contexts.

Key Exam Topics

1NF: Atomic values, no repeating groups, unique primary keys
2NF: No partial dependencies on composite primary keys
3NF: No transitive dependencies through non-key attributes
BCNF: Every determinant must be a candidate key
4NF: No non-trivial multi-valued dependencies
5NF: No join dependencies except through candidate keys
Functional Dependencies: Foundation of normalization
Exam Focus: Especially 1NF–3NF for database design

Core Components

Functional Dependency (FD): X → Y (X uniquely determines Y)
Multi-Valued Dependency (MVD): X →→ Y (X determines multiple Y values)
Join Dependency (JD): (A, B, C) (table can be reconstructed from joins)
Determinant: An attribute that determines other attributes
Candidate Key: Minimal unique identifier
Primary Key: Selected candidate key
Partial Dependency: Dependency on part of a composite key
Transitive Dependency: Indirect dependency through non-key attributes

Practical Examples

First Normal Form (1NF)

-- Problem: Non-atomic values and repeating groups
CREATE TABLE Bestellungen_Schlecht (
    bestell_id INT,
    kunde VARCHAR(100),
    artikel_liste VARCHAR(500), -- Non-atomic
    telefonnummern VARCHAR(200) -- Repeating group
);

-- 1NF Solution: Atomic values
CREATE TABLE Bestellungen_1NF (
    bestell_id INT PRIMARY KEY,
    kunden_id INT,
    bestelldatum DATE
);

CREATE TABLE Bestellpositionen (
    bestell_id INT,
    positionsnummer INT,
    artikel_id INT,
    menge INT,
    PRIMARY KEY (bestell_id, positionsnummer)
);

CREATE TABLE Kunden_Telefone (
    kunden_id INT,
    telefonnummer VARCHAR(20),
    PRIMARY KEY (kunden_id, telefonnummer)
);

Second Normal Form (2NF)

-- Problem: Partial dependencies
CREATE TABLE Bestellpositionen_2NF_Problem (
    bestell_id INT,
    artikel_id INT,
    menge INT,
    artikel_name VARCHAR(100), -- Depends only on artikel_id
    preis DECIMAL(10,2),       -- Depends only on artikel_id
    PRIMARY KEY (bestell_id, artikel_id)
);

-- 2NF Solution: Remove partial dependencies
CREATE TABLE Bestellpositionen_2NF (
    bestell_id INT,
    artikel_id INT,
    menge INT,
    PRIMARY KEY (bestell_id, artikel_id)
);

CREATE TABLE Artikel (
    artikel_id INT PRIMARY KEY,
    artikel_name VARCHAR(100),
    preis DECIMAL(10,2)
);

Third Normal Form (3NF)

-- Problem: Transitive dependencies
CREATE TABLE Mitarbeiter_3NF_Problem (
    mitarbeiter_id INT PRIMARY KEY,
    name VARCHAR(100),
    abteilungs_id INT,
    abteilungs_name VARCHAR(100), -- Transitive: abteilungs_id → abteilungs_name
    standort VARCHAR(50)           -- Transitive: abteilungs_id → standort
);

-- 3NF Solution: Remove transitive dependencies
CREATE TABLE Mitarbeiter_3NF (
    mitarbeiter_id INT PRIMARY KEY,
    name VARCHAR(100),
    abteilungs_id INT
);

CREATE TABLE Abteilungen (
    abteilungs_id INT PRIMARY KEY,
    abteilungs_name VARCHAR(100),
    standort VARCHAR(50)
);

Boyce-Codd Normal Form (BCNF)

-- Problem: 3NF but not BCNF
CREATE TABLE Projektmitarbeiter_BCNF_Problem (
    projekt_id INT,
    mitarbeiter_id INT,
    rolle VARCHAR(50),
    PRIMARY KEY (projekt_id, mitarbeiter_id)
);

-- Functional dependencies:
-- projekt_id, mitarbeiter_id → rolle
-- projekt_id, rolle → mitarbeiter_id (Determinant not a candidate key!)

-- BCNF Solution
CREATE TABLE Projektrollen (
    projekt_id INT,
    rolle VARCHAR(50),
    mitarbeiter_id INT,
    PRIMARY KEY (projekt_id, rolle)
);

Fourth Normal Form (4NF)

-- Problem: Multi-valued dependencies
CREATE TABLE Mitarbeiter_Kenntnisse_4NF_Problem (
    mitarbeiter_id INT,
    programmiersprache VARCHAR(50),
    projekt_id INT,
    PRIMARY KEY (mitarbeiter_id, programmiersprache, projekt_id)
);

-- MVDs: mitarbeiter_id →→ programmiersprache, mitarbeiter_id →→ projekt_id

-- 4NF Solution: Separate MVDs
CREATE TABLE Mitarbeiter_Sprachen (
    mitarbeiter_id INT,
    programmiersprache VARCHAR(50),
    PRIMARY KEY (mitarbeiter_id, programmiersprache)
);

CREATE TABLE Mitarbeiter_Projekte (
    mitarbeiter_id INT,
    projekt_id INT,
    PRIMARY KEY (mitarbeiter_id, projekt_id)
);

Fifth Normal Form (5NF)

-- Problem: Join dependencies
CREATE TABLE Lieferanten_Produkte_Kunden_5NF_Problem (
    lieferant_id INT,
    produkt_id INT,
    kunden_id INT,
    PRIMARY KEY (lieferant_id, produkt_id, kunden_id)
);

-- JD: *(Lieferanten_Produkte, Produkte_Kunden, Lieferanten_Kunden)*

-- 5NF Solution: Decompose into projections
CREATE TABLE Lieferanten_Produkte (
    lieferant_id INT,
    produkt_id INT,
    PRIMARY KEY (lieferant_id, produkt_id)
);

CREATE TABLE Produkte_Kunden (
    produkt_id INT,
    kunden_id INT,
    PRIMARY KEY (produkt_id, kunden_id)
);

CREATE TABLE Lieferanten_Kunden (
    lieferant_id INT,
    kunden_id INT,
    PRIMARY KEY (lieferant_id, kunden_id)
);

Normalization Process

Step 1: Identify Dependencies

-- Analyze functional dependencies
-- Example: Order system
-- bestell_id → bestelldatum, kunden_id
-- kunden_id → kundenname, adresse
-- bestell_id, artikel_id → menge, einzelpreis
-- artikel_id → artikelname, lagerbestand

Step 2: Apply Normal Forms

-- 1NF: Ensure atomic values
-- 2NF: Remove partial dependencies
-- 3NF: Remove transitive dependencies
-- BCNF: Make determinants candidate keys
-- 4NF: Remove multi-valued dependencies
-- 5NF: Remove join dependencies

Step 3: Verify

-- Test for anomalies
INSERT INTO ... -- Insert anomalies?
UPDATE ...     -- Update anomalies?
DELETE ...     -- Delete anomalies?

Strengths and Limitations

Benefits of Normalization

Data Integrity: Prevents redundancy and inconsistencies
Maintainability: Changes required in one place only
Storage Efficiency: Reduces redundant data
Consistency: Unified data representation
Scalability: Better performance with large datasets

Drawbacks

Query Performance: More joins required
Complexity: More intricate data structures
Write Performance: Distributed updates
Over-Engineering: Excessive normalization can backfire

Denormalization Strategies

Targeted Redundancy for Performance

-- Denormalized version for reporting
CREATE TABLE Bestellungen_Report (
    bestell_id INT PRIMARY KEY,
    bestelldatum DATE,
    kundenname VARCHAR(100), -- Redundant from customer table
    artikelname VARCHAR(100), -- Redundant from article table
    menge INT,
    gesamtpreis DECIMAL(10,2)
);

Common Exam Questions

When is a table in 3NF but not BCNF? When a determinant is not a candidate key (example: project staff with roles).
What’s the difference between 3NF and BCNF? BCNF is stricter: every determinant must be a candidate key.
Explain multi-valued dependencies. X →→ Y means that for each X value, the set of Y values is independent of other attributes.
When are higher normal forms (4NF, 5NF) practically relevant? With very complex data structures involving many relationships, typically in academic or specialized systems.

Täglicher Artikel

Database Normal Forms: 1NF to 5NF Explained