Normalisation in DBMS

Why normalise?

An unnormalised table repeats data, which leads to three classic problems: insertion anomalies, update anomalies and deletion anomalies.

First normal form (1NF)

Every column holds a single, atomic value and each row is unique. No repeating groups and no multi valued columns.

Second normal form (2NF)

The table is in 1NF and every non key column depends on the whole primary key, not part of it. This only matters for composite keys.

Third normal form (3NF)

The table is in 2NF and no non key column depends on another non key column - no transitive dependencies.

Boyce-Codd normal form (BCNF)

A stricter 3NF: for every functional dependency X to Y, X must be a superkey.

Worked example

-- Unnormalised
-- student(id, name, course1, course2, dept, dept_head)

-- 1NF: remove repeating groups
CREATE TABLE enrolment (
    student_id INT,
    course     VARCHAR(50),
    PRIMARY KEY (student_id, course)
);

-- 3NF: department head depends on department, not on the student
CREATE TABLE departments (
    id        INT PRIMARY KEY,
    name      VARCHAR(50),
    head      VARCHAR(50)
);

Trade offs

Normalisation reduces redundancy but increases the number of joins. Reporting systems often denormalise deliberately for read speed.

Conclusion

Aim for 3NF in transactional systems, then denormalise consciously and only where measurements justify it.