Database Normalisation: 1NF, 2NF and 3NF Explained

The problem: update anomalies

• Update anomaly. The CSE head changes and you must update every CSE row; miss one and the database now holds two answers.
• Insert anomaly. A new department cannot be recorded until some student enrols in it.
• Delete anomaly. Removing the last ECE student deletes the only record that Dr. Iyer exists.

First normal form — atomic values

1NF: every cell holds a single value, and there are no repeating groups. course_ids holds a comma-separated list, which breaks it — you cannot join on it, index it usefully, or enforce a foreign key against it.

The fix is one row per student-course pair. The table gets longer and immediately more useful.

Second normal form — no partial dependency

2NF: in 1NF, and every non-key column depends on the whole composite key. With the key now (student_id, course_id), student_name depends on student_id alone — a partial dependency, which is why the name repeats on every row for that student.

Split into students(student_id, student_name, dept, dept_head) and enrolments(student_id, course_id).

Third normal form — no transitive dependency

3NF: in 2NF, and no non-key column depends on another non-key column. In students, dept_head depends on dept, which depends on student_id — a transitive dependency, and the original update anomaly still alive.

CREATE TABLE departments (
    dept_code  VARCHAR(8)  PRIMARY KEY,
    dept_head  VARCHAR(80) NOT NULL
);

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(80) NOT NULL,
    dept_code    VARCHAR(8)  NOT NULL REFERENCES departments(dept_code)
);

CREATE TABLE enrolments (
    student_id INT REFERENCES students(student_id),
    course_id  VARCHAR(8),
    PRIMARY KEY (student_id, course_id)
);

Each fact now lives in exactly one row. Changing the CSE head is a single-row update, a department can exist with no students, and deleting a student cannot erase a department.

When to stop, and when to reverse

3NF is the practical target for transactional systems. BCNF, 4NF and 5NF exist and are worth naming, but they address edge cases you will rarely meet in an application schema.