Normal Forms: 1NF, 2NF & 3NF

By the end of this lesson you will be able to spot insertion, update and deletion anomalies, state the exact definitions of 1NF, 2NF and 3NF, find the highest of these normal forms for a relation, and decompose a table step by step until it is in 3NF. You will rejoin the pieces with SQL to prove nothing was lost.

Why normalise?

Normalisation is the process of splitting a badly designed table into smaller, well-designed tables, guided by functional dependencies. The goal is to store each fact once. Think of a family WhatsApp group where everyone keeps their own copy of the wedding date. When the date changes, some copies are never updated. One shared calendar is the normalised design.

A badly designed table suffers three anomalies (problems when changing data):

1. Insertion anomaly: you cannot add a fact without inventing another one. You cannot record a new course until some student enrols in it.
2. Update anomaly: one fact is stored many times, so changing it means changing many rows. Miss one, and the data contradicts itself.
3. Deletion anomaly: deleting one fact accidentally deletes another. If the only student in a course drops it, the course details vanish too.

The starting table

The exam cell of a college in Kochi keeps this table:

RESULT_SHEET(roll_no, name, dept_id, dept_name, hod, course_code, course_title, marks)

FDs:
  roll_no → name, dept_id
  dept_id → dept_name, hod
  course_code → course_title
  roll_no, course_code → marks

Candidate key: {roll_no, course_code}

Step 1: build it and see the update anomaly happen.

CREATE TABLE result_sheet (
  roll_no INTEGER, name VARCHAR(30), dept_id INTEGER, dept_name VARCHAR(20), hod VARCHAR(30),
  course_code VARCHAR(8), course_title VARCHAR(30), marks INTEGER
);
INSERT INTO result_sheet VALUES
  (2401, 'Farhan', 10, 'CSE', 'Dr. Nair',  'CS301', 'DBMS', 78),
  (2401, 'Farhan', 10, 'CSE', 'Dr. Nair',  'CS302', 'OS', 66),
  (2402, 'Isha',   10, 'CSE', 'Dr. Nair',  'CS301', 'DBMS', 91),
  (2403, 'Kabir',  20, 'ECE', 'Dr. Reddy', 'EC201', 'Signals', 72);

-- CSE gets a new HoD, but the clerk updates only one row
UPDATE result_sheet SET hod = 'Dr. Iyer' WHERE roll_no = 2402;

SELECT DISTINCT dept_name, hod FROM result_sheet ORDER BY dept_name, hod;

dept_name | hod
----------+----------
CSE       | Dr. Iyer
CSE       | Dr. Nair
ECE       | Dr. Reddy
(3 rows)

You should see two different HoDs for CSE. The table now contradicts itself, because the HoD of CSE was stored three times.

First normal form (1NF)

A relation is in 1NF if every attribute holds only atomic (single, indivisible) values, with no lists or repeating groups. A cell like 'DBMS, OS' breaks 1NF.

Not 1NF:  2401 | Farhan | CS301, CS302 | 78, 66
1NF:      2401 | Farhan | CS301 | 78
          2401 | Farhan | CS302 | 66

The fix is one row per value (or a separate table, as with multivalued attributes in ER Model: Entities & Attributes). Our result_sheet already has one course per row, so it is in 1NF. 1NF is the minimum requirement for a relational table.

Second normal form (2NF)

Two terms first. A prime attribute is part of some candidate key. A non-prime attribute is not part of any candidate key.

A relation is in 2NF if it is in 1NF and no non-prime attribute is partially dependent on a candidate key: every non-prime attribute depends on the whole of every candidate key, not just a part of it.

Check result_sheet. The key is {roll_no, course_code}. But roll_no → name, dept_id uses only part of the key, and so does course_code → course_title. These are partial dependencies, so the table is in 1NF but not 2NF.

Note: a relation whose candidate keys are all single attributes is automatically in 2NF, because a single attribute has no "part".

Step 2: remove partial dependencies by moving each one into its own table, with the part of the key as the new key.

STUDENT(roll_no, name, dept_id, dept_name, hod)       key: roll_no
COURSE(course_code, course_title)                     key: course_code
RESULT(roll_no, course_code, marks)                   key: {roll_no, course_code}

Now every non-prime attribute depends on the whole key of its table. But STUDENT still repeats the HoD for every CSE student.

Third normal form (3NF)

A relation is in 3NF if it is in 2NF and no non-prime attribute is transitively dependent on a candidate key.

The precise test that exams use: R is in 3NF if, for every non-trivial FD X → A that holds in R, either X is a super key, or A is a prime attribute.

In STUDENT, dept_id → dept_name, hod. Is dept_id a super key of STUDENT? No. Are dept_name and hod prime? No. So STUDENT breaks 3NF. The chain roll_no → dept_id → hod is a transitive dependency.

Step 3: move the transitive dependency into its own table.

STUDENT(roll_no, name, dept_id)                       key: roll_no
DEPARTMENT(dept_id, dept_name, hod)                   key: dept_id
COURSE(course_code, course_title)                     key: course_code
RESULT(roll_no, course_code, marks)                   key: {roll_no, course_code}

All four tables are in 3NF. Each fact now lives in exactly one place.

Proving nothing was lost

Step 4: create the 3NF tables, load the same facts, change the HoD once, and rejoin everything.

CREATE TABLE department (dept_id INTEGER PRIMARY KEY, dept_name VARCHAR(20), hod VARCHAR(30));
CREATE TABLE student (roll_no INTEGER PRIMARY KEY, name VARCHAR(30), dept_id INTEGER);
CREATE TABLE course (course_code VARCHAR(8) PRIMARY KEY, course_title VARCHAR(30));
CREATE TABLE result (roll_no INTEGER, course_code VARCHAR(8), marks INTEGER,
  PRIMARY KEY (roll_no, course_code));

INSERT INTO department VALUES (10, 'CSE', 'Dr. Nair'), (20, 'ECE', 'Dr. Reddy');
INSERT INTO student VALUES (2401, 'Farhan', 10), (2402, 'Isha', 10), (2403, 'Kabir', 20);
INSERT INTO course VALUES ('CS301', 'DBMS'), ('CS302', 'OS'), ('EC201', 'Signals'), ('CS401', 'Compilers');
INSERT INTO result VALUES (2401, 'CS301', 78), (2401, 'CS302', 66), (2402, 'CS301', 91), (2403, 'EC201', 72);

UPDATE department SET hod = 'Dr. Iyer' WHERE dept_id = 10;

SELECT s.roll_no, s.name, d.dept_name, d.hod, c.course_title, r.marks
FROM result r
JOIN student s ON s.roll_no = r.roll_no
JOIN department d ON d.dept_id = s.dept_id
JOIN course c ON c.course_code = r.course_code
ORDER BY s.roll_no, c.course_title;

roll_no | name   | dept_name | hod       | course_title | marks
--------+--------+-----------+-----------+--------------+------
2401    | Farhan | CSE       | Dr. Iyer  | DBMS         | 78
2401    | Farhan | CSE       | Dr. Iyer  | OS           | 66
2402    | Isha   | CSE       | Dr. Iyer  | DBMS         | 91
2403    | Kabir  | ECE       | Dr. Reddy | Signals      | 72
(4 rows)

You should see the original four rows, with Dr. Iyer on every CSE row after a single one-row update. The update anomaly is gone. The insertion anomaly is gone too: CS401 Compilers exists in course even though nobody has enrolled yet. Deleting Kabir's result would no longer delete the Signals course.

Finding the highest normal form

Use this checklist on any relation R with FD set F:

1. Find all candidate keys (with attribute closure, as in Attribute Closure & Canonical Cover), and list prime attributes.
2. For each non-trivial FD X → A with A non-prime: if X is a proper subset of a candidate key, R is not in 2NF.
3. For each non-trivial FD X → A: if X is not a super key and A is not prime, R is not in 3NF.

Quick example: R(A, B, C, D) with F = { AB → C, C → D }. Key: AB. C → D: C is not a super key and D is not prime, so R is not in 3NF. Is there a partial dependency? No FD has just A or just B on the left, so R is in 2NF. Highest normal form: 2NF.

Common mistakes

• Checking 2NF against only one candidate key. 2NF and 3NF must hold for every candidate key.
• Calling a dependency on a prime attribute a violation. 3NF allows X → A when A is prime, even if X is not a super key.
• Splitting tables without keeping a link. Each new table must keep the attribute it depends on (such as dept_id in STUDENT), or you cannot join back.
• Over-normalising read-heavy reports. Reporting tables are sometimes denormalised on purpose; see Normalisation & Table Design.

Key points

• Normalisation removes insertion, update and deletion anomalies by storing each fact once.
• 1NF: every value is atomic, no repeating groups.
• 2NF: 1NF and no non-prime attribute depends on part of a candidate key.
• 3NF: for every non-trivial X → A, X is a super key or A is prime.
• Fix 2NF and 3NF violations by moving the offending FD into its own table, keeping the determinant as a link.
• Rejoining the decomposed tables must give back the original data.

Key: {invoice_no, product_id}  (neither appears on any right side; together they determine all)

invoice_no → customer_phone       partial dependency  → not 2NF
product_id → product_name, price  partial dependency
Highest normal form: 1NF

3NF decomposition:
  INVOICE(invoice_no, customer_phone)
  CUSTOMER(customer_phone, customer_name)
  PRODUCT(product_id, product_name, price)
  INVOICE_LINE(invoice_no, product_id, qty)