1NF, 2NF, 3NF and BCNF

Each normal form removes one specific way a table can contradict itself. They build on each other, so a table in 3NF is already in 2NF and 1NF.

1NF: no repeating groups. Every cell holds one value. A phones column containing "9840012345, 9840099999" breaks it: "who has this number?" becomes a string search, and there is nowhere to record which is the primary number. *Fix:* a separate table.

2NF: no partial dependency. No non-key column may depend on only part of a composite key. In the table above, dept_id depends on roll_no alone while the key is (roll_no, course). *Fix:* split off (roll_no, dept_id).

Only tables with a composite key can break 2NF. A single-column key has no "part" to depend on.

3NF: no transitive dependency. No non-key column may depend on another non-key column. dept_id → dept_name means the department name is stored once per student. *Fix:* a departments table.

BCNF: every determinant is a candidate key. Stricter than 3NF, and the difference only appears with overlapping candidate keys. If A → B and A is not a candidate key, it violates BCNF even if B happens to be part of a key.

After normalizing:

students(roll_no PK, name, dept_id FK)
departments(dept_id PK, dept_name)
marks(roll_no FK, course FK, marks, PRIMARY KEY (roll_no, course))

Each fact lives once. Renaming a department is one update.

Takeaway: 2NF is about part of a key; 3NF is about non-key columns; BCNF is about every determinant being a key.