Normalization in DBMS (1NF to 5NF with Examples)

What is normalization in DBMS?

Normalization is the step-by-step process of organising the columns and tables of a relational database so that every fact is stored in exactly one place. You start with one wide, messy table, study how its columns depend on each other, and split it into smaller tables that are linked by keys. Each split follows a rule called a normal form — 1NF, 2NF, 3NF, BCNF, 4NF and 5NF — and each rule removes one specific kind of repetition.

The idea comes from Edgar F. Codd, the IBM researcher who created the relational model. He defined First Normal Form in 1970 and the second and third forms in 1971. Boyce–Codd Normal Form followed in 1974, and Ronald Fagin added the fourth (1977) and fifth (1979) forms. Half a century later, the same rules still shape every well-designed MySQL schema.

Think of a school office cupboard. If every student file contains a photocopy of the principal's address, then the day that address changes, someone has to find and correct hundreds of copies — and will surely miss a few. Normalization says: keep the address in one file, and let every other file simply point to it.

To keep things easy, this lesson follows one running example — a college register of students, their courses, marks and departments — from the messy first draft all the way to 3NF. BCNF, 4NF and 5NF then get their own small examples, because they deal with situations the register does not have. Every diagram is drawn like a page from a notebook, so you can copy it into your own notes.

Why normalize? The three anomalies

A badly designed table does not fail on day one. It fails slowly, as data is added, changed and deleted. Database books call these failures anomalies, and there are exactly three of them.

• Update anomaly — the same fact is stored in several rows, so one change has to be made in all of them. Miss a single row and the table now contradicts itself.
• Insert anomaly — you cannot record one fact without inventing another. A new department cannot be added until at least one student joins it, because the student's roll number is part of every row.
• Delete anomaly — removing one fact accidentally removes another. When the last Science student leaves, the name of the Science head disappears with him.

Normalization removes all three at the root. Along the way you also get smaller storage, simpler UPDATE statements, and reports you can actually trust.

Keys and dependencies in five minutes

Every normal form is defined with a handful of terms. Learn these once and the rules that follow become almost obvious.

If functional dependencies are new to you, the lesson on functional dependency explains closures and how to find candidate keys step by step.

The starting point: an unnormalized table (UNF)

Here is how a beginner might record everything in a single sheet — one row per student, with all their phones, courses and marks squeezed in:

At first glance it looks compact. Look closer and three problems appear:

1. Lists inside cells. Aman's row holds two phone numbers, two courses and two marks. SQL cannot cleanly answer "who studies Maths?", and nothing in the table says that 78 belongs to DBMS rather than to Maths.
2. Repeated facts. "Computer → Dr. Rao" is typed into every Computer student's row.
3. Too many subjects in one table. The table tries to describe four different things at once: students, courses, results and departments.

A table like this is in UNF (unnormalized form). We will now fix it one normal form at a time.

First Normal Form (1NF)

Rule: a table is in 1NF when every cell holds a single, indivisible (atomic) value, every column holds one kind of data, there are no repeating groups, and every row can be identified uniquely by a key.

How to convert:

1. Break every list into separate rows — one row per student per course.
2. Give each course a code (c_code) so that it can be part of a key.
3. Move a repeating group that has nothing to do with courses — the phone numbers — into its own table, student_phone.
4. Choose a primary key. Since one student takes many courses, the key is the pair (roll, c_code).

Why not simply repeat the phone numbers on every row as well? Aman has 2 phones and 2 courses, so that would create 2 × 2 = 4 rows for him, and every new phone would multiply the rows again. Keeping independent lists apart is exactly what 4NF formalises later in this lesson.

The table is now in 1NF and SQL can query it properly. But notice that "Aman" and "Dr. Rao" still repeat. 1NF fixes the shape of the data, not the repetition.

Second Normal Form (2NF)

Rule: the table is in 1NF, and every non-key column depends on the whole primary key, not on just a part of it. In other words, there is no partial dependency.

To check, write down the functional dependencies of the 1NF table:

Only marks needs both halves of the key — a mark belongs to one student in one course. name and dept depend on roll alone, and c_name depends on c_code alone. Those are partial dependencies, and they are the reason "Aman" and "DBMS" are written again and again.

How to convert: move each partial dependency into a table of its own, keyed by the part of the key it depends on.

Course names are now stored once, and the enrolment table holds only what truly belongs to a student–course pair. A useful shortcut: if a 1NF table has a single-column primary key, it is automatically in 2NF, because there is no "part" of the key for anything to depend on.

The students table, however, still repeats Dr. Rao for every Computer student. That is the next problem to solve.

Third Normal Form (3NF)

Rule: the table is in 2NF, and no non-key column depends on another non-key column — there is no transitive dependency. Formally: for every dependency X → A, either X is a super key or A is a prime attribute.

In students the chain is roll → dept → dept_head. The head of a department is a fact about the department, not about the student; it reaches the student only through dept.

How to convert: move the dependent column out, together with the column it really depends on.

Now check the three anomalies again:

A popular way to remember the first three forms is William Kent's summary: every non-key column must depend on "the key, the whole key, and nothing but the key". 1NF gives you a key, 2NF makes it the whole key, and 3NF makes it nothing but the key.

Boyce–Codd Normal Form (BCNF)

Rule: for every non-trivial functional dependency X → Y, X must be a super key. BCNF is a slightly stricter 3NF: it drops the "or A is a prime attribute" escape clause.

The gap only shows up when a table has overlapping candidate keys. Consider a coaching centre where:

• each teacher teaches exactly one subject (teacher → subject), and
• for a given subject, a student studies with just one teacher ((student, subject) → teacher).

The candidate keys are (student, subject) and (student, teacher). Every column is part of some key, so the table passes 3NF. But in teacher → subject the left side is not a super key, so it fails BCNF — and the anomalies are real. "Mr. Sharma teaches DBMS" is stored twice, and if Rahul drops out we lose the fact that Ms. Khan teaches DBMS at all.

How to convert: split on the offending dependency into teacher_subject(teacher, subject) and student_teacher(student, teacher). Because teacher is the key of the first table, joining the two gives back the original rows exactly — this is called a lossless decomposition.

The trade-off: the rule "(student, subject) → teacher" no longer lives inside one table, so a primary key cannot enforce it any more. You would check it in a trigger or in application code. This is why some designers stay at 3NF when moving to BCNF would lose an important dependency.

Fourth Normal Form (4NF)

Rule: the table is in BCNF and has no non-trivial multi-valued dependency except on a super key. A multi-valued dependency X ↠ Y means X decides a whole set of Y values, and that set has nothing to do with the other columns.

Suppose the college records the clubs each student joins and the languages each student speaks. Clubs and languages are unrelated, but if both live in one table you are forced to write every combination:

With 2 clubs and 2 languages Aman needs 4 rows. Add German and you must add 2 more rows — one for each club — or the table starts implying something false. Interestingly, the table is already in BCNF (its only key is all three columns and there are no functional dependencies), yet it is full of redundancy. That is the gap 4NF closes.

How to convert: store each independent multi-valued fact in its own table: student_club(roll, club) and student_language(roll, language). Adding a language is now a single row.

Fifth Normal Form (5NF)

Rule: a table is in 5NF (also called Project-Join Normal Form) when it cannot be split into smaller tables without losing information, except in ways already implied by its candidate keys. The problem it targets is a join dependency: a table that equals the join of three or more of its projections, but not of any two.

Take a school timetable with this business rule: if a teacher can teach a subject, the teacher is assigned to a class, and that class studies the subject, then the teacher teaches that subject in that class.

Store the three facts separately — teacher_subject, teacher_class and class_subject — and the full timetable is rebuilt by joining all three. Joining only two of them produces a spurious row (Sharma teaching Python in XII-A, a class that has no Python), which is why a two-way split is not safe here. Once the three pairwise tables exist, adding a subject to a class is one insert instead of several timetable rows.

Honestly, 5NF cases are rare in everyday projects. Know it for exams and interviews, and keep an eye out for three-way relationships that are really driven by a rule.

All normal forms at a glance

The final design in MySQL

Here is the 3NF version of the college register as real MySQL code. Run it in the editor, then look at the UPDATE near the end: the head of department changes in exactly one row, and every report picks it up.

CREATE TABLE departments (
  dept      VARCHAR(30) PRIMARY KEY,
  dept_head VARCHAR(50) NOT NULL
);

CREATE TABLE students (
  roll INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  dept VARCHAR(30) NOT NULL,
  FOREIGN KEY (dept) REFERENCES departments(dept)
);

CREATE TABLE student_phone (
  roll  INT,
  phone VARCHAR(15),
  PRIMARY KEY (roll, phone),
  FOREIGN KEY (roll) REFERENCES students(roll)
);

CREATE TABLE courses (
  c_code CHAR(5) PRIMARY KEY,
  c_name VARCHAR(40) NOT NULL
);

CREATE TABLE enrolments (
  roll   INT,
  c_code CHAR(5),
  marks  TINYINT UNSIGNED,
  PRIMARY KEY (roll, c_code),
  FOREIGN KEY (roll)   REFERENCES students(roll),
  FOREIGN KEY (c_code) REFERENCES courses(c_code)
);

INSERT INTO departments VALUES
  ('Computer', 'Dr. Rao'), ('Science', 'Dr. Mehta'), ('Commerce', 'Dr. Khan');
INSERT INTO students VALUES
  (101, 'Aman', 'Computer'), (102, 'Priya', 'Computer'), (103, 'Rahul', 'Science');
INSERT INTO student_phone VALUES
  (101, '98765-43210'), (101, '91234-56780'), (102, '99887-76655'), (103, '90011-22334');
INSERT INTO courses VALUES
  ('CS101', 'DBMS'), ('MA101', 'Maths'), ('PH101', 'Physics');
INSERT INTO enrolments VALUES
  (101, 'CS101', 78), (101, 'MA101', 85), (102, 'CS101', 91), (103, 'PH101', 66);

-- Rebuild the old wide report with JOINs
SELECT s.roll, s.name, c.c_name, e.marks, d.dept, d.dept_head
FROM enrolments e
JOIN students    s ON s.roll   = e.roll
JOIN courses     c ON c.c_code = e.c_code
JOIN departments d ON d.dept   = s.dept
ORDER BY s.roll, c.c_code;

-- A one-row update: no update anomaly any more
UPDATE departments SET dept_head = 'Dr. R. Rao' WHERE dept = 'Computer';
SELECT s.name, d.dept_head FROM students s JOIN departments d ON d.dept = s.dept;

The first SELECT rebuilds the original wide report from the clean tables. Its output is:

After the UPDATE, both Aman and Priya show "Dr. R. Rao" — because the name lives in one place only. The price we pay is three JOINs in the report, but every value now comes from a single source of truth. For more solved exercises of this kind, see normalization examples.

When to denormalize (on purpose)

Normalization is the right default, but it is not a religion. In read-heavy places — dashboards, reports, search pages, analytics — the cost of many joins can matter more than a little repetition. Denormalization means deliberately adding back a redundant column or summary table to make reads faster.

• Keep a student_count on each class row for a busy dashboard, updated by a trigger or by the code that enrols students.
• Build a flat reporting table from the normalized tables every night for the principal's monthly report.
• Store an invoice's grand total on the invoice row so that listing a thousand invoices does not sum a thousand item lists.

One thing that looks like denormalization but is not: copying the product price into an order line. That is a different fact — the price at the time of sale — and it must not change when today's price changes.

The golden rule: normalize first, then denormalize only where you have measured a real need, and write down which copy is the source of truth.

Common mistakes

• Storing comma-separated lists. A column like subjects = 'DBMS,Maths' breaks 1NF. Use a child table with one row per value.
• Hunting for partial dependencies in a single-column key. Partial dependency is only possible with a composite key; such a table in 1NF is already in 2NF.
• Mixing up 2NF and 3NF. Ask two separate questions: "Does this column depend on only part of the key?" (2NF) and "Does it depend on another non-key column?" (3NF).
• Thinking an AUTO_INCREMENT id solves everything. Adding a surrogate id does not remove dept → dept_head; the repetition is still there.
• Splitting in a way that loses data. When you decompose, the common column must be a key of at least one of the new tables, otherwise joins create fake rows.
• Forgetting foreign keys after splitting. Without them, an enrolment can point to a student who no longer exists.
• Normalizing for its own sake. Splitting every column into its own table makes simple queries painful. Stop at 3NF or BCNF unless a real anomaly remains.

Practice questions (with answers)

Q1. order_items(order_id, book_id, book_title, qty, customer_name) has primary key (order_id, book_id). What is its highest normal form, and how would you fix it?

Q2. employees(emp_id, name, pin_code, city) has primary key emp_id. Assume each PIN code belongs to one city. What is its highest normal form?

Q3. Relation R(A, B, C) has the dependencies AB → C and C → B. Find the candidate keys and the highest normal form.

Q4. Is every BCNF table in 3NF? Is every 3NF table in BCNF?

Q5. A table stores (roll, club, language) for students who join several clubs and speak several languages. Which normal form does it violate, and what is the fix?

Summary

• Normalization stores each fact once and links tables with keys, removing update, insert and delete anomalies.
• 1NF: atomic values, no repeating groups, a key for every row.
• 2NF: no partial dependency — every non-key column needs the whole composite key.
• 3NF: no transitive dependency — non-key columns depend on the key and nothing else.
• BCNF: the left side of every dependency is a super key.
• 4NF separates independent multi-valued facts; 5NF removes join dependencies.
• In real projects aim for 3NF or BCNF, then denormalize only for a measured reason.

Next, practise with worked problems in normalization examples, or go deeper into the theory in functional dependency.