Normal Forms: 1NF, 2NF, 3NF

You know the big table is broken. Fine. Split it.

But split it how? Into two tables? Five? Which columns go where?

You need a checklist, not a hunch.

Why & what

Normal forms are that checklist. Each one is a rule your table either passes or fails, and each rule kills one specific kind of repetition.

They stack. You reach 2NF only after 1NF, and 3NF only after 2NF. So this is one staircase, not three separate ideas.

1NF: every cell holds a single value. 2NF: no column depends on only part of the key. 3NF: no non-key column depends on another non-key column.

For interviews, 3NF is the finish line. Almost every real database stops there.

How it works

Start from the wide table you met in 4.1, key (roll_no, course).

1. Fail 1NF first. Someone stored Aisha's courses as one cell: DBMS101, OS101. You can't search or join on that. Fix: one value per cell. Aisha now gets two rows, one per course. Now in 1NF.
2. Check 2NF — the partial dependency. The key is (roll_no, course). But name depends on roll_no alone, and title depends on course alone. Each depends on only part of the key. That's a partial dependency. Fix: move them out. Student(roll_no, name), Course(course, title), and Enrollment(roll_no, course, grade) keeps what genuinely needs both. Now in 2NF.
3. Check 3NF — the middle-man. Suppose Student also holds dept_id and dept_name. Here roll_no → dept_id, and dept_id → dept_name. So roll_no decides dept_name only by going through dept_id. That indirect hop is a transitive dependency. Fix: pull the chain apart. Student(roll_no, name, dept_id) and Dept(dept_id, dept_name). Now in 3NF.
4. Read the result. Four tables, and each one describes exactly one subject: a student, a course, an enrollment, a department.
5. Test the fix. Prof. Rao changes his name now — you edit one row in Course. A new course with no students? Insert it into Course; nobody needs to enroll first. Cara drops OS101? Delete one enrollment row; the course stays. All three anomalies gone. Pause here. Before reading on, say which normal form each of these breaks: (a) a phone_numbers cell holding "999, 888", (b) Enrollment(roll_no, course, grade, title).

(a) 1NF — two values in one cell. (b) 2NF — title depends on course alone, which is only part of the key.

Notice: each step removes one kind of dependency, and each step splits a table.

Common confusion

2NF and 3NF sound the same. They're not. Look at what the column depends on:

• 2NF problem — a column depends on part of the key. (title needs only course, not the full (roll_no, course).)
• 3NF problem — a column depends on another normal column. (dept_name needs dept_id, which isn't a key at all.)

Part of the key → 2NF. A non-key column → 3NF.

Useful — shortcut: — if your table's key is a single column, it's automatically in 2NF, — because — a single column has no "part" to partly depend on. So 2NF problems only ever appear in tables with composite keys — like Enrollment.

The old exam line is worth remembering too: every non-key column must depend on the key, the whole key, and nothing but the key. "The key" is 1NF, "the whole key" is 2NF, "nothing but the key" is 3NF.

Interview angle

1. "Explain 1NF, 2NF and 3NF with an example." — Walk one table through all three, like the steps above. A single example carried through beats three definitions.
2. "What's a partial dependency? What's a transitive dependency?" — Partial: depends on part of a composite key. Transitive: depends on a non-key column, so you reach it in two hops. One example each.
3. "A table has a single-column primary key. Can it violate 2NF?" — No. There's no partial key to depend on, so it's automatically in 2NF. This one catches a lot of candidates.
4. "How far do you normalize in practice?" — 3NF for almost everything. Going further is rare, and going backwards is a deliberate performance decision, which is 4.3.

Recap

1NF gives one value per cell, 2NF removes columns that depend on part of the key, 3NF removes columns that depend on other non-key columns — and 3NF is where most real designs stop.