Mastering Database Normalization: From 1NF to 3NF with Real Examples

Fundamentals of Normal Forms

When designing relational databases, you must follow normalization rules called normal forms . There are six normal forms: 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF (perfect form). Higher forms reduce redundancy; satisfying a higher form automatically satisfies lower ones.

Basic Concepts

Entity : an object that exists in the real world, e.g., company, project, employee.

Attribute : a property of an entity, e.g., company name, address.

Key : attribute or set of attributes that uniquely identifies a row, such as employee number or (employee number, performance month).

Prime attribute : any attribute that is part of a candidate key.

Non‑prime attribute : attributes not appearing in any candidate key, e.g., employee name, department, department size, performance.

First Normal Form (1NF)

Overview

1NF requires that every attribute in a relation be indivisible.

Example

The original table mixes department and department size in one column, violating 1NF. Splitting the column into separate department and department size columns satisfies 1NF.

Problems of 1NF

Data redundancy : repeated employee and department information.

Insertion anomaly : cannot insert a department without an employee.

Deletion anomaly : deleting employee rows also removes department data.

Update anomaly : changing an employee’s department requires updating multiple rows.

Second Normal Form (2NF)

Overview

2NF eliminates partial functional dependencies of non‑prime attributes on a part of a composite key. Every non‑prime attribute must depend on the whole key.

Key concepts

Functional dependency X → Y means Y is uniquely determined by X.

Full functional dependency X >> Y means Y depends on X and on no proper subset of X.

Partial functional dependency X > Y means Y depends on a subset of X.

Transitive functional dependency X >>> Z means X → Y and Y → Z with Y not part of X.

Analysis of the example

The composite key is (employee number, performance month). Non‑prime attributes such as employee name and department depend only on employee number, creating partial dependencies, so the table is not in 2NF.

To achieve 2NF we decompose the original table into:

A table with employee number, performance month, and performance (fully dependent).

A table with employee number, employee name, department, and department size (each attribute fully dependent on employee number).

A table with department and department size.

Third Normal Form (3NF)

Overview

3NF removes transitive dependencies of non‑prime attributes on the key. After 2NF, any remaining dependency where a non‑prime attribute depends on another non‑prime attribute must be eliminated.

Example

Because employee number → department and department → department size, department size is transitively dependent on employee number. Splitting the department‑size table resolves this, resulting in three tables that satisfy 3NF.

Other Normal Forms (brief)

BCNF : every determinant is a candidate key.

4NF : eliminates multi‑valued dependencies.

5NF (projection‑join normal form): all join dependencies are implied by candidate keys.

Conclusion

Normalization progressively reduces data redundancy and anomalies. In practice, achieving 3NF is usually sufficient for most relational database designs.