Interview Questions

Database normalisation: 1NF, 2NF, 3NF and BCNF with a worked example

Himanshu Sharma · 12 September 2023 · 10 min read

Imagine a school keeps its enrolment records in one big spreadsheet: student, course, teacher, teacher's phone number, one row per enrolment. It works until the teacher changes their phone number, and you must fix it in forty rows. Miss one, and the data now disagrees with itself. Normalisation is the process of organising tables so that each fact is stored in exactly one place, which removes this whole class of problems. This guide explains the problems it solves, then walks one example through first, second and third normal form and Boyce-Codd normal form, with MySQL 8.4 tests of each step.

The three anomalies

A badly designed table causes three kinds of trouble.

Update anomaly: a fact stored in many rows must be changed in all of them. Miss one and the data is inconsistent.
Insert anomaly: you cannot record one fact without another. You cannot add a new course until a student enrols in it.
Delete anomaly: deleting one fact accidentally destroys another. Remove the last student of a course and you lose the information about the course and its teacher.

Normalisation is the cure. It is a set of rules called normal forms, each stricter than the last. In practice you aim for third normal form.

Key ideas you need first

A primary key uniquely identifies a row. A composite key is a primary key made of several columns.
A functional dependency A → B means that if you know A, you know exactly one B. student_id → student_name: one student id has one name.
A partial dependency is when a column depends on only part of a composite key.
A transitive dependency is when a non-key column depends on another non-key column (A → B → C).

Step 0: the unnormalised table

CREATE TABLE enrolment_flat (
  student_id INT, student_name VARCHAR(30), courses VARCHAR(100));
INSERT INTO enrolment_flat VALUES
  (1,'Alice','Math, Physics'), (2,'Bob','Chemistry, Biology'), (3,'Carol','Math');

student_id	student_name	courses
1	Alice	Math, Physics
2	Bob	Chemistry, Biology
3	Carol	Math

The courses column holds several values in one cell. Even a simple question is awkward: WHERE courses LIKE '%Math%' works here, but it would also match a course named "Mathematics" or "Discrete Math", and counting courses per student needs string tricks.

First normal form (1NF)

Rule: every column holds a single, indivisible (atomic) value, there are no repeating groups, and each row is unique (has a primary key).

Split the courses into separate rows, and add the teacher information that we now want to record:

CREATE TABLE enrolment_1nf (
  student_id INT, student_name VARCHAR(30), course VARCHAR(30),
  instructor VARCHAR(30), instructor_phone VARCHAR(15),
  PRIMARY KEY (student_id, course));
INSERT INTO enrolment_1nf VALUES
  (1,'Alice','Math','Dr. Rao','111'),   (1,'Alice','Physics','Dr. Sen','222'),
  (2,'Bob','Chemistry','Dr. Das','333'),(2,'Bob','Biology','Dr. Sen','222'),
  (3,'Carol','Math','Dr. Rao','111');

student_id	student_name	course	instructor	instructor_phone
1	Alice	Math	Dr. Rao	111
1	Alice	Physics	Dr. Sen	222
2	Bob	Chemistry	Dr. Das	333
2	Bob	Biology	Dr. Sen	222
3	Carol	Math	Dr. Rao	111

WHERE course = 'Math' is now exact. The table is in 1NF, with the composite key (student_id, course). But it has all three anomalies. Update Dr. Sen's phone in one row only (as a hurried clerk might):

UPDATE enrolment_1nf SET instructor_phone = '999' WHERE student_id = 1 AND course = 'Physics';
SELECT instructor, instructor_phone FROM enrolment_1nf WHERE instructor = 'Dr. Sen';

+------------+------------------+
| instructor | instructor_phone |
+------------+------------------+
| Dr. Sen    | 999              |
| Dr. Sen    | 222              |
+------------+------------------+

Dr. Sen now has two phone numbers: an update anomaly. Try to add a new course nobody has joined yet, and MySQL refuses, because student_id is part of the key:

ERROR 1364 (HY000): Field 'student_id' doesn't have a default value

That is an insert anomaly. And if you delete Bob's only enrolment in Chemistry, Dr. Das and his phone number vanish from the database: a delete anomaly.

Second normal form (2NF)

Rule: the table is in 1NF and has no partial dependencies, meaning every non-key column depends on the whole composite key.

Look at the dependencies in enrolment_1nf:

student_id → student_name depends on only part of the key.
course → instructor, instructor_phone depends on only the other part.

Both are partial dependencies. Fix them by giving each fact its own table, keyed by what it really depends on:

Table	Key	Columns
`students`	`student_id`	`student_name`
`courses`	`course_id`	`title`, `instructor`, `instructor_phone`
`enrolments`	`(student_id, course_id)`	(nothing else)

Now a student's name is stored once, and a course can exist without students. The insert and delete anomalies for courses and students are gone. (A table whose primary key is a single column is automatically free of partial dependencies, so 2NF only matters for composite keys.)

Third normal form (3NF)

Rule: the table is in 2NF and has no transitive dependencies. Every non-key column depends on the key, "the whole key, and nothing but the key".

In courses, the instructor's phone does not describe the course; it describes the instructor: course_id → instructor → instructor_phone. That is a transitive dependency, and it is the cause of Dr. Sen's two phone numbers, since Dr. Sen teaches two courses. Move instructors into their own table and refer to them by id:

CREATE TABLE students (
  student_id INT PRIMARY KEY, student_name VARCHAR(30) NOT NULL);
CREATE TABLE instructors (
  instructor_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, phone VARCHAR(15));
CREATE TABLE courses (
  course_id INT PRIMARY KEY, title VARCHAR(30) NOT NULL, instructor_id INT NOT NULL,
  FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id));
CREATE TABLE enrolments (
  student_id INT, course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id));

INSERT INTO students    VALUES (1,'Alice'),(2,'Bob'),(3,'Carol');
INSERT INTO instructors VALUES (1,'Dr. Rao','111'),(2,'Dr. Sen','222'),(3,'Dr. Das','333');
INSERT INTO courses     VALUES (10,'Math',1),(11,'Physics',2),(12,'Chemistry',3),(13,'Biology',2);
INSERT INTO enrolments  VALUES (1,10),(1,11),(2,12),(2,13),(3,10);

Changing Dr. Sen's number is now a single-row update, and every report sees it:

UPDATE instructors SET phone = '999' WHERE name = 'Dr. Sen';
SELECT s.student_name, c.title, i.name AS instructor, i.phone
FROM students s
JOIN enrolments e USING (student_id)
JOIN courses c USING (course_id)
JOIN instructors i USING (instructor_id)
ORDER BY s.student_id, c.course_id;

+--------------+-----------+------------+-------+
| student_name | title     | instructor | phone |
+--------------+-----------+------------+-------+
| Alice        | Math      | Dr. Rao    | 111   |
| Alice        | Physics   | Dr. Sen    | 999   |
| Bob          | Chemistry | Dr. Das    | 333   |
| Bob          | Biology   | Dr. Sen    | 999   |
| Carol        | Math      | Dr. Rao    | 111   |
+--------------+-----------+------------+-------+

The flat table's view of the data is reproduced with joins, but each fact now lives in one place. Foreign keys keep the tables honest: the database rejects an enrolment for a student that does not exist.

INSERT INTO enrolments VALUES (9, 10);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`nm`.`enrolments`, CONSTRAINT `enrolments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`))

And deleting the last enrolment in Biology no longer destroys the course: the row in courses is untouched.

Boyce-Codd normal form (BCNF)

Rule: for every functional dependency A → B, A must be a candidate key (something that could serve as a key). BCNF is a slightly stricter 3NF. The two differ only in unusual tables where a non-key column determines part of a key.

A standard example: a table (student, subject, teacher) where each teacher teaches only one subject, and a student has one teacher per subject. The key is (student, subject), but teacher → subject, and teacher is not a key. It is in 3NF (subject is part of a candidate key) but not BCNF. Fix it by splitting into (teacher, subject) and (student, teacher). For most business tables, reaching 3NF also reaches BCNF.

Higher forms: 4NF and 5NF

Fourth normal form removes multi-valued dependencies: a table recording a person's hobbies and languages in the same rows forces every hobby to pair with every language. Split them into two tables. Fifth normal form deals with tables that can only be reconstructed by joining three or more pieces. They are rarely needed, but they are named in interviews, so know the one-line meanings.

Summary of the normal forms

Form	Requirement	Problem it removes
1NF	atomic values, no repeating groups, a primary key	multi-value cells
2NF	1NF + no partial dependency on a composite key	facts about part of the key repeated in many rows
3NF	2NF + no transitive dependency	facts about a non-key column repeated
BCNF	every determinant is a candidate key	leftover anomalies when keys overlap
4NF	no independent multi-valued dependencies	combinatorial row explosions
5NF	no join dependencies that are not implied by keys	rare decomposition anomalies

Denormalisation: when to break the rules on purpose

Normalised designs need joins to answer questions, and joins cost time on very large data. Denormalisation deliberately stores some data twice to make reads faster. Reasonable cases include reporting tables and data warehouses, a stored order_total that saves summing millions of line items, and a copy of a product's name on an invoice line (which is actually correct, because the invoice must keep the name as it was on the day). The price is that you must keep the copies consistent, with application logic, triggers or scheduled jobs. Normalise first, measure, and denormalise only where you have a measured problem. Good indexes solve many join performance issues without duplicating data.

A practical design checklist

List the things (entities) your system records: students, courses, instructors.
Give each a table and a primary key, preferably a simple integer id.
Put each fact in the table of the thing it describes.
Connect tables with foreign keys; use a separate table for many-to-many relationships (like enrolments).
Choose data types and NOT NULL carefully.
Re-read the design and ask of every column: "does this depend on the key, the whole key and nothing but the key?"

Common interview questions

What is normalisation? Organising data to reduce redundancy and avoid anomalies.
Explain 1NF, 2NF and 3NF. Atomic values; no partial dependencies; no transitive dependencies.
What is the difference between 3NF and BCNF? BCNF requires every determinant to be a candidate key; 3NF allows determinants that are part of a key.
What are the disadvantages of normalisation? More tables and joins, so some queries get more complex or slower.
When would you denormalise? For measured read performance needs, such as reports and warehouses.

Common mistakes

Storing lists in one column ('Math, Physics') instead of using rows.
Repeating the same name or address in many tables instead of referencing an id.
Using a person's name or phone as a primary key.
Over-normalising tiny lookup values into dozens of tables.
Skipping foreign keys and then finding orphan rows years later.

Next steps

After design come queries and speed: read the joins guide, subqueries, indexes and SQL performance optimisation. The MySQL course designs a real shop database, and the MySQL interview questions include more design questions.