Normalization: 1NF through 3NF

What Is Normalization?

Normalization is the process of organizing a relational database so that data is stored efficiently, redundancy is minimized, and data integrity is protected. The core tool is a set of rules called normal forms. Each normal form builds on the previous one. In this lesson you will work through the first three normal forms: 1NF, 2NF, and 3NF. Mastering these three is the practical foundation for every certification exam covered in this program and for real DBA work on the job.

Before starting, note one professional responsibility: poorly normalized schemas are a common source of data corruption and silent data loss. A DBA who introduces or ignores normalization problems can damage production data. Always test decompositions with real data samples before deploying schema changes, and always keep verified backups before any structural alteration.

---

First Normal Form (1NF)

A table is in First Normal Form when:

1. Every column holds atomic (indivisible) values — no lists, no comma-separated strings, no repeating groups stuffed into one cell.

2. Every row is unique — the table has a primary key.

3. There are no repeating column groups (for example, `skill1`, `skill2`, `skill3` columns).

Common violation — repeating groups in columns: An invoice table might have columns `Item1`, `Item2`, `Item3`. If an invoice has four items, the design breaks down. The fix is to move repeating data into its own table.

Common violation — non-atomic values: A single `Address` cell containing `123 Main St, Springfield, IL 62701` violates atomicity if you ever need to filter by city or state. Break it into `StreetAddress`, `City`, `State`, `ZIPCode`.

After applying 1NF, each cell holds exactly one value and each row is uniquely identifiable.

---

Second Normal Form (2NF)

A table is in Second Normal Form when:

1. It is already in 1NF.

2. Every non-key attribute is fully functionally dependent on the entire primary key — not just part of it.

This rule only matters when the primary key is composite (made of two or more columns). If your primary key is a single column, you cannot have a partial dependency.

Partial dependency — the violation: Suppose a table `ProjectAssignment` has the composite key (`EmployeeID`, `ProjectID`). The column `EmployeeName` depends only on `EmployeeID`, not on the combination. That is a partial dependency and violates 2NF.

The fix: Move `EmployeeName` (and any other attribute that depends only on `EmployeeID`) into a separate `Employee` table keyed on `EmployeeID`. Keep only the key columns and attributes that genuinely require both keys in `ProjectAssignment`.

Decomposition must be lossless: you must be able to rejoin the tables on their shared key and recover every original row without inventing rows or losing rows.

---

Third Normal Form (3NF)

A table is in Third Normal Form when:

1. It is already in 2NF.

2. No non-key attribute is transitively dependent on the primary key through another non-key attribute.

Transitive dependency — the violation: In an `Employee` table, suppose `EmployeeID → DepartmentID` and `DepartmentID → DepartmentName`. That means `EmployeeID → DepartmentName` through `DepartmentID`. `DepartmentName` is transitively dependent on the primary key and should not live in the `Employee` table.

The fix: Move `DepartmentName` (and any attribute that depends on `DepartmentID`) into a `Department` table keyed on `DepartmentID`. The `Employee` table retains `DepartmentID` as a foreign key.

Boyce-Codd Normal Form (BCNF) is a slightly stronger version of 3NF and appears on some exam questions. For most practical design work, 3NF is the target.

---

Lossless Join — Why It Matters

Every decomposition step must pass the lossless-join test: when you rejoin the decomposed tables on the shared key column(s), you must get back exactly the original table — no extra (spurious) rows, no missing rows. To verify, manually join your decomposed tables using a small data sample and compare the result to the original. This is a required check in professional practice and is tested on all four credentials in this program.

---

Preservation of Functional Dependencies

Decomposition should also preserve functional dependencies — the logical rules that say one attribute determines another. If a dependency is lost during decomposition, the database can no longer enforce that business rule through the schema alone. In some cases (particularly with BCNF) you must choose between lossless join and dependency preservation; 3NF always allows both.

---

Step-by-Step Summary

| Step | Question to Ask | Action If Violated |

|------|----------------|--------------------|

| 1NF | Are all values atomic? Is there a primary key? Any repeating groups? | Remove repeating groups; add a primary key |

| 2NF | Does every non-key column depend on the whole composite key? | Move partially dependent columns to a new table |

| 3NF | Does any non-key column depend on another non-key column? | Move transitively dependent columns to a new table |

Always work in order: fix 1NF before checking 2NF, fix 2NF before checking 3NF.

---

Professional Notes

Over-normalization can hurt read performance on large data sets. DBAs sometimes intentionally denormalize (accept some redundancy) for reporting tables or data warehouses, but this is a deliberate, documented decision — not an oversight. For OLTP (transaction-processing) systems, 3NF is the standard target. Document every design decision so future DBAs understand the rationale.