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

This article is a conceptual guide to database normalization – complete with practical examples and exam-style 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.

Core Concept

Normalization is a formal approach to eliminating redundancy and avoiding anomalies in relational databases. By applying normal forms step by step, you improve data structure systematically.

Anomalies without normalization:

• Insertion anomalies: Data cannot be inserted without additional information
• Update anomalies: Changes require updates in multiple places
• Deletion anomalies: Deleting data inadvertently removes 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 stronger rules

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

Exam-Critical Topics

• 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 problems in denormalized data
• Industry certification relevance: Important for data modeling and database design
• Practice: Trade-off between normalization and performance

Core Components

1. Functional dependency: X → Y (X uniquely determines Y)
2. Primary key: Unique identification of records
3. Candidate key: Potential primary keys
4. Partial dependency: Dependency on part of a composite key
5. Transitive dependency: Indirect dependency through non-key attributes
6. Determinant: An attribute that determines other attributes
7. Normalization process: Stepwise application of normal forms
8. Denormalization: Deliberate redundancy for performance optimization

Practical Examples

Unnormalized Table (0NF)

-- Problematic structure with redundancy and anomalies
CREATE TABLE Orders (
    order_nr INT,
    order_date DATE,
    customer_name VARCHAR(100),
    customer_address VARCHAR(200),
    item_nr 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', 'Main Street 1', 101, 'Laptop', 999.99, 2, 1999.98),
(1, '2024-01-15', 'Meier', 'Main Street 1', 102, 'Mouse', 29.99, 1, 29.99),
(2, '2024-01-16', 'Schmidt', 'Side Street 2', 101, 'Laptop', 999.99, 1, 999.99);

First Normal Form (1NF)

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

-- Functional dependencies:
-- order_nr, item_nr → quantity, total_price
-- order_nr → order_date, customer_name, customer_address
-- item_nr → item_name, price

Second Normal Form (2NF)

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

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

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

Third Normal Form (3NF)

-- Elimination of transitive dependencies
CREATE TABLE Orders_3NF (
    order_nr 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_nr INT,
    item_nr INT,
    quantity INT,
    PRIMARY KEY (order_nr, item_nr),
    FOREIGN KEY (order_nr) REFERENCES Orders_3NF(order_nr),
    FOREIGN KEY (item_nr) REFERENCES Items(item_nr)
);

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

Anomalies and Solutions

Insertion 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', 'Third Way 3');

Update Anomaly (without normalization)

-- Problem: Customer address must be updated in multiple places
-- Solution: Centralized customer table
UPDATE Customers 
SET customer_address = 'Main Street 1a' 
WHERE customer_id = 1;

Deletion Anomaly (without normalization)

-- Problem: Deleting the last order removes customer data
-- Solution: Separate tables prevent unintended deletion
DELETE FROM OrderItems WHERE order_nr = 1;
-- Customer data remains in 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,
    employee_id INT,
    role VARCHAR(50),
    PRIMARY KEY (project_id, employee_id)
);

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

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

CREATE TABLE EmployeeProjects (
    project_id INT,
    employee_id INT,
    PRIMARY KEY (project_id, employee_id)
);

Advantages and Disadvantages

Advantages of Normalization

• Data integrity: Avoids redundancy and inconsistencies
• Maintainability: Changes required in only one place
• Storage efficiency: Reduces redundancy
• Consistency: Unified data representation

Disadvantages

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

Common Exam Questions

1. What is the difference between 2NF and 3NF? 2NF eliminates partial dependencies; 3NF eliminates transitive dependencies.

2. When is a table in BCNF? When every determinant is a candidate key (stricter than 3NF).

3. Explain functional dependencies! X → Y means the value of X uniquely determines the value of Y.

4. What are anomalies and how are they prevented? Insertion, update, and deletion anomalies are prevented through normalization.

Key References

1. https://de.wikipedia.org/wiki/Normalisierung_(Datenbank)
2. https://www.gatech.edu/coe/cse/normalization
3. https://www.sql-tutorial.ru/sql-normalization.html

---

Recommended Reading: Databases