Database Design: Normalization 1NF, 2NF, 3NF & BCNF

This article is a glossary entry on database normalization – including practical examples and exam questions.

In a Nutshell

Normalization is the process of structuring relational databases to eliminate redundancy and prevent data anomalies. The goal is to ensure data integrity and consistency.

Compact Technical Definition

Normalization is a formal approach to eliminating redundancy and preventing anomalies in relational databases. By progressively applying normal forms, you improve data structure.

Anomalies Without Normalization:

Insert anomalies: Data cannot be inserted without other information
Update anomalies: Changes require updates in multiple places
Delete anomalies: Removing data inadvertently deletes other information

Normal Forms:

1NF: Atomic values, no repeating groups
2NF: 1NF + no partial dependencies
3NF: 2NF + no transitive dependencies
BCNF: Stricter form of 3NF with more rigorous rules

Functional dependencies form the mathematical foundation: X → Y means the value of X uniquely determines the value of Y.

Exam-Relevant Key Points

1NF: Atomic values, no repeating groups, unique primary keys
2NF: 1NF satisfied, no partial dependencies, complete dependency on primary key
3NF: 2NF satisfied, no transitive dependencies, non-key attributes depend only on primary key
BCNF: Every determinant is a candidate key
Functional dependencies: Mathematical basis of normalization
Anomalies: Insert, update, delete issues with denormalized data
Exam-relevant: Important for data modeling and database design
Practice: Trade-off between normalization and performance

Core Components

Functional dependency: X → Y (X uniquely determines Y)
Primary key: Unique identification of records
Candidate key: Possible primary keys
Partial dependency: Dependency on part of a composite key
Transitive dependency: Indirect dependency through non-key attributes
Determinant: Attribute that determines other attributes
Normalization process: Progressive application of normal forms
Denormalization: Intentional redundancy for performance optimization

Practical Examples

Unnormalized Table (0NF)

-- Problematic structure with redundancy and anomalies
CREATE TABLE Orders (
    order_id INT,
    order_date DATE,
    customer_name VARCHAR(100),
    customer_address VARCHAR(200),
    item_id INT,
    item_name VARCHAR(100),
    price DECIMAL(10,2),
    quantity INT,
    total_price DECIMAL(10,2)
);

-- Data with issues
INSERT INTO Orders VALUES 
(1, '2024-01-15', 'Meier', 'Hauptstraße 1', 101, 'Laptop', 999.99, 2, 1999.98),
(1, '2024-01-15', 'Meier', 'Hauptstraße 1', 102, 'Mouse', 29.99, 1, 29.99),
(2, '2024-01-16', 'Schmidt', 'Nebenstraße 2', 101, 'Laptop', 999.99, 1, 999.99);

First Normal Form (1NF)

-- Atomic values, no repeating groups
CREATE TABLE Orders_1NF (
    order_id INT,
    order_date DATE,
    customer_name VARCHAR(100),
    customer_address VARCHAR(200),
    item_id INT,
    item_name VARCHAR(100),
    price DECIMAL(10,2),
    quantity INT,
    total_price DECIMAL(10,2),
    PRIMARY KEY (order_id, item_id)
);

-- Functional dependencies:
-- order_id, item_id → quantity, total_price
-- order_id → order_date, customer_name, customer_address
-- item_id → item_name, price

Second Normal Form (2NF)

-- Elimination of partial dependencies
CREATE TABLE Orders_2NF (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_name VARCHAR(100),
    customer_address VARCHAR(200)
);

CREATE TABLE OrderItems (
    order_id INT,
    item_id INT,
    quantity INT,
    total_price DECIMAL(10,2),
    PRIMARY KEY (order_id, item_id),
    FOREIGN KEY (order_id) REFERENCES Orders_2NF(order_id)
);

CREATE TABLE Items (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100),
    price DECIMAL(10,2)
);

Third Normal Form (3NF)

-- Elimination of transitive dependencies
CREATE TABLE Orders_3NF (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_address VARCHAR(200)
);

CREATE TABLE OrderItems (
    order_id INT,
    item_id INT,
    quantity INT,
    PRIMARY KEY (order_id, item_id),
    FOREIGN KEY (order_id) REFERENCES Orders_3NF(order_id),
    FOREIGN KEY (item_id) REFERENCES Items(item_id)
);

CREATE TABLE Items (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100),
    price DECIMAL(10,2)
);

Anomalies and Their Solutions

Insert Anomaly (Without Normalization)

-- Problem: Customer cannot be added without an order
-- Solution: Separate customer table
INSERT INTO Customers (customer_id, customer_name, customer_address) 
VALUES (3, 'Mueller', 'Dritter Weg 3');

Update Anomaly (Without Normalization)

-- Problem: Customer address must be updated in multiple places
-- Solution: Central customer table
UPDATE Customers 
SET customer_address = 'Hauptstraße 1a' 
WHERE customer_id = 1;

Delete Anomaly (Without Normalization)

-- Problem: Deleting the last order removes customer data
-- Solution: Separate tables prevent accidental deletion
DELETE FROM OrderItems WHERE order_id = 1;
-- Customer data remains intact in the customer table

BCNF (Boyce-Codd Normal Form)

BCNF Rule

Every determinant must be a candidate key.

-- Example that satisfies 3NF but not BCNF
CREATE TABLE ProjectStaff (
    project_id INT,
    staff_id INT,
    role VARCHAR(50),
    PRIMARY KEY (project_id, staff_id)
);

-- Functional dependencies:
-- project_id, staff_id → role
-- project_id, role → staff_id  (Determinant is not a candidate key!)

-- BCNF solution: Decomposition
CREATE TABLE ProjectRoles (
    project_id INT,
    role VARCHAR(50),
    staff_id INT,
    PRIMARY KEY (project_id, role)
);

CREATE TABLE StaffProjects (
    project_id INT,
    staff_id INT,
    PRIMARY KEY (project_id, staff_id)
);

Advantages and Disadvantages

Advantages of Normalization

Data integrity: Prevention of redundancy and inconsistencies
Maintainability: Changes required in one place only
Storage efficiency: Reduction of redundancy
Consistency: Uniform data representation

Disadvantages

Performance: More joins required
Complexity: More complex data structure
Write performance: Distributed updates across multiple tables
Learning curve: Requires understanding of functional dependencies

Frequently Asked Exam Questions

What is the difference between 2NF and 3NF? 2NF eliminates partial dependencies, 3NF eliminates transitive dependencies.
When is a table in BCNF? When every determinant is a candidate key (stricter than 3NF).
Explain functional dependencies! X → Y means the value of X uniquely determines the value of Y.
What are anomalies and how are they prevented? Insert, update, and delete anomalies are prevented through normalization.

Täglicher Artikel

Database Normalization: 1NF, 2NF, 3NF & BCNF