Normalisation

Database normalization is the process of organizing data in a relational database to reduce redundancy and improve data integrity. It involves breaking a large, flat table into smaller, related tables and defining relationships between them.

The progression from 1st Normal Form (1NF) to 5th Normal Form (5NF) represents a step-by-step refinement. To reach any given normal form, the database must first satisfy all the requirements of the preceding normal forms (e.g., to be in 3NF, a table must already be in 2NF and 1NF).

Here is a breakdown of the normal forms, from 1st to 5th.

Before We Begin: Important Keys

• Superkey: Any column (or set of columns) that uniquely identifies a row.
• Candidate Key: A minimal superkey (no unnecessary columns). A table can have multiple candidate keys.
• Primary Key: The candidate key selected by the database designer to uniquely identify rows.
• Prime Attribute: A column that is part of any candidate key.
• Non-prime Attribute: A column that is not part of any candidate key.

1. First Normal Form (1NF)

The Rule: A table is in 1NF if every column contains only atomic (indivisible) values, and there are no repeating groups of columns.

• Problem: If a table stores multiple values in a single cell (like a comma-separated list of phone numbers) or has repeating columns (like Phone1, Phone2), it is hard to query, update, or index the data.
• Solution: Split multi-valued attributes into separate rows, or move repeating groups into a related table.

Non-1NF Example:

1NF Solution:

2. Second Normal Form (2NF)

The Rule: A table is in 2NF if it is in 1NF and no non-prime attribute is dependent on a proper subset of any candidate key. This is known as eliminating partial dependencies.

This rule only applies when the primary/candidate key is composite (consists of more than one column).

• Problem: If a table has a composite key, and a column depends on only part of that key, data redundancy occurs.
• Solution: Move the partially dependent columns and the part of the key they depend on into a new table.

1NF (but not 2NF) Example:

• Composite Primary Key: (StudentID, Course)
• Instructor depends on the entire key (who teaches Math to Student 101).
• StudentEmail depends only on StudentID (a subset of the composite key). This is a partial dependency.

2NF Solution:

Split into two tables so StudentEmail is no longer partially dependent.

Table A (Student Details): Primary Key is StudentID

Table B (Student Courses): Composite Primary Key is (StudentID, Course)

3. Third Normal Form (3NF)

The Rule: A table is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on the primary key.

Essentially, this means non-prime attributes must depend only on the primary key, and not on other non-prime attributes (i.e., “no transitivities”). A common way to remember this is: Every attribute must depend on the key, the whole key, and nothing but the key (so help me Codd).

• Problem: If column A determines column B, and column B determines column C, then column A transitively determines column C. If you change column B, you must update column C in multiple places.
• Solution: Break the transitive relationship out into a separate table.

2NF (but not 3NF) Example:

• Primary Key: StudentID
• Department depends on StudentID.
• DeptHead depends on Department, which is a non-prime attribute. This is a transitive dependency: StudentID (\rightarrow) Department (\rightarrow) DeptHead.

3NF Solution:

Split the table to isolate the department hierarchy.

Table A (Student Majors): Primary Key is StudentID

Table B (Departments): Primary Key is Department

(Note: There is also an intermediate normal form called Boyce-Codd Normal Form (BCNF), which is a slightly stronger version of 3NF. BCNF addresses anomalies that can occur when a table has multiple overlapping candidate keys.)

4. Fourth Normal Form (4NF)

The Rule: A table is in 4NF if it is in BCNF (or 3NF) and has no multi-valued dependencies (MVDs).

A multi-valued dependency occurs when the presence of one or more rows in a table implies the presence of certain other rows. This typically happens when a single entity has two or more independent, multi-valued relationships.

• Problem: Suppose a teacher can teach multiple subjects AND has multiple hobbies. If we try to store both independent lists in one table, we are forced to represent every combination of subject and hobby to keep the data consistent, leading to massive redundancy.
• Solution: Separate the independent multi-valued facts into their own tables.

3NF (but not 4NF) Example:

Because hobbies and subjects are completely independent of each other, we have to duplicate rows to represent all combinations.

4NF Solution:

Split into two separate tables to isolate the independent many-to-many relationships.

Table A (Teacher Subjects):

Table B (Teacher Hobbies):

5. Fifth Normal Form (5NF)

The Rule: A table is in 5NF (also known as Project-Join Normal Form) if it is in 4NF and cannot be decomposed into any number of smaller tables without introducing redundancy or losing information.

A table is in 5NF if every join dependency in the table is implied by the candidate keys.

• Problem: Sometimes, a three-way relationship exists where facts are ternary (involving three attributes), but there are physical/logical rules stating that if pair (A,B) and pair (B,C) and pair (A,C) exist, then the triple (A,B,C) must exist. If we do not design for this, we get logical update anomalies where we might insert an invalid combination, or fail to insert a required combination.
• Solution: Decompose the table into three separate tables representing the pairwise relationships. Rejoining them will reconstruct the original table exactly (hence “Project-Join”).

4NF (but not 5NF) Example:

Let’s trace Agents, Companies they represent, and Products they sell. Rule: If Agent Green represents Company X, and Company X makes Laptops, and Agent Green sells Laptops, then Agent Green MUST sell Laptops for Company X.

If we keep this in one table, we have redundant combinations:

5NF Solution:

To handle this strictly, we decompose the ternary relationship into three binary (2-column) tables:

Table 1 (Agent-Company):

Table 2 (Company-Product):

Table 3 (Agent-Product):

If we perform a join on all three of these tables, we will reconstruct the exact correct relationships of the original table without any risk of partial, mismatched, or impossible combinations being inserted key-by-key in a single unified table.

Summary Checklist

• 1NF: Atomic values only. No repeating columns.
• 2NF: 1NF + No partial dependencies (every non-key column depends on the whole primary key).
• 3NF: 2NF + No transitive dependencies (non-key columns do not depend on other non-key columns).
• 4NF: 3NF + No independent multi-valued dependencies.
• 5NF: 4NF + No join dependencies (reconstructing data from split tables doesn’t create spurious rows).