Database Normalization (1NF–BCNF)

Normalization is the process of organising a database to reduce redundancy and prevent update anomalies. You do it by splitting big, messy tables into smaller, well-focused ones connected by keys. The goal: store each fact in exactly one place. The result is a series of normal forms — 1NF, 2NF, 3NF, and BCNF — each stricter than the last.

The short version: a table is normalized when every non-key column depends on the key, the whole key, and nothing but the key.

Why bother? The three anomalies

Redundant data causes three problems:

• Update anomaly — the same fact stored in many rows; change it in one place and the others go stale.
• Insertion anomaly — you cannot add a fact without also inventing unrelated data.
• Deletion anomaly — deleting one row accidentally erases a fact you wanted to keep.

Normalization is the cure. Let us refactor one bad table through each form.

The starting point: an unnormalized table

Imagine a single table tracking enrolments at a coaching institute:

This table is a mess: courses holds a list, the department head repeats, and so on. Let us fix it.

First Normal Form (1NF): atomic values

A table is in 1NF if every cell holds a single, atomic value — no lists, no repeating groups.

The courses column violates 1NF. We split each course into its own row:

Now it is in 1NF — but full of redundancy.

Functional dependencies: the key concept

A functional dependency A → B means "if you know A, you know B." For example, student_id → student_name (each student ID maps to one name). Normalization is really about removing dependencies that do not belong with the table's key.

Second Normal Form (2NF): no partial dependency

A table is in 2NF if it is in 1NF and every non-key column depends on the whole primary key — not just part of it. This only matters when the key is composite.

Our composite key is (student_id, course). But student_name depends only on student_id — a partial dependency. We split it out:

-- PostgreSQL / MySQL compatible

-- Students: name depends on the whole (single) key
CREATE TABLE student (
    student_id    INTEGER PRIMARY KEY,
    student_name  VARCHAR(120) NOT NULL,
    dept          VARCHAR(50)  NOT NULL
);

-- Enrolment: the M:N fact, keyed on the full composite
CREATE TABLE enrolment (
    student_id INTEGER NOT NULL REFERENCES student(student_id),
    course     VARCHAR(100) NOT NULL,
    PRIMARY KEY (student_id, course)   -- whole key; no partial dependency remains
);

Third Normal Form (3NF): no transitive dependency

A table is in 3NF if it is in 2NF and no non-key column depends on another non-key column (a transitive dependency).

In our student table, dept → dept_head. So dept_head depends on dept, which is not a key — a transitive dependency. We split departments into their own table:

-- Department head depends on department, not on student. Separate it.
CREATE TABLE department (
    dept       VARCHAR(50) PRIMARY KEY,
    dept_head  VARCHAR(120) NOT NULL
);

-- Student now references department instead of repeating the head's name
CREATE TABLE student (
    student_id    INTEGER PRIMARY KEY,
    student_name  VARCHAR(120) NOT NULL,
    dept          VARCHAR(50) NOT NULL REFERENCES department(dept)
);

Now Dr. Kale is stored once. Update his name in one row and every student reflects it instantly — the update anomaly is gone.

Boyce-Codd Normal Form (BCNF): the stricter 3NF

BCNF tightens 3NF: for every functional dependency A → B, A must be a superkey (a key or a superset of one). Most tables that are in 3NF are already in BCNF. The difference appears only in tables with multiple overlapping candidate keys — an uncommon but real edge case. If you have a dependency whose left side is not a candidate key, you are in 3NF but not BCNF, and you split again.

How far should you normalize?

For most applications, 3NF is the practical target. It eliminates the everyday anomalies without over-fragmenting your schema. BCNF is worth reaching when overlapping candidate keys create lingering redundancy.

Denormalization — deliberately reintroducing redundancy — is sometimes justified for read-heavy reporting where joins are too slow. But treat it as a tuning decision made after measuring, never as a starting design. A normalized schema is the correct default.

Common mistakes

• Stopping at 1NF. Atomic values alone do not stop redundancy. Push to at least 3NF.
• Over-normalizing tiny tables. Splitting a two-column lookup into three tables adds joins for no benefit. Normalize to remove real anomalies, not for points.
• Confusing 2NF with 3NF. 2NF is about partial dependencies (needs a composite key to matter); 3NF is about transitive dependencies between non-key columns.
• Denormalizing first, measuring never. If you add redundancy before you have a measured performance problem, you are creating the very anomalies normalization exists to prevent.

FAQ

Do I need to memorize all the normal forms? Understand 1NF, 2NF, and 3NF deeply; recognise BCNF. Higher forms (4NF, 5NF) handle rare multivalued-dependency cases you will seldom meet early on.

Is a normalized database always faster? Not always for reads — more tables can mean more joins. But it is almost always safer, and modern databases join efficiently with proper indexes.

Keep learning

Normalization rests on understanding keys in DBMS and the relational model. Once your schema is clean, learn to query it with SQL joins.

Want guided practice normalizing real schemas? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.