Theory
The cure, one step at a time
Meera's messy table has all three anomalies. The cure, normalization, is not one leap but a staircase of stages called normal forms: 1NF, then 2NF, then 3NF, then BCNF. Each step removes one specific kind of bad design you now recognise.
You climb them in order, a table must be in 1NF before it can be in 2NF, and so on. By the top, the anomalies are gone and each fact lives in exactly one place. This is the grand finale of Unit 3, and a certain exam question.
Theory
Cleaning a room in passes
You do not clean a messy room in one motion. First pass: put every loose item in its own spot, nothing piled together (1NF). Second pass: move things that only half-belong here to where they fully belong (2NF). Third pass: remove things that are really about something else in the room (3NF). Each pass targets a specific kind of mess, and you always do them in order.
At a glance
The normal-form staircase
| Form | Removes | Rule |
|---|---|---|
| 1NF | Multi-valued / composite cells | Every cell holds ONE atomic value |
| 2NF | Partial dependency | 1NF + no non-key depends on part of a composite key |
| 3NF | Transitive dependency | 2NF + no non-key determines another non-key |
| BCNF | Remaining determinant anomalies | Every determinant is a super key |
Theory
Climbing the stairs
- 1NF: every cell holds a single atomic value. Meera's 'three phone numbers in one cell' violates it; fix by splitting phones into their own rows/table. No repeating groups.
- 2NF: in 1NF and no partial dependency, no non-key attribute depending on only part of a composite key. Only matters when the key is composite. Fix: move the partial attribute to its own table.
- 3NF: in 2NF and no transitive dependency, no non-key attribute determined by another non-key (customer_city via customer_id). Fix: split the chain out.
- BCNF: a stricter 3NF, for every dependency X to Y, X must be a super key (every determinant is a key).
Quiz
Meera's table stores an item's supplier_city, which depends on supplier_id (a non-key column), not on the table's key. Which normal form fixes this, and how?
- 3NF, by removing the transitive dependency into a separate Supplier table
- 1NF, by making cells atomic
- 2NF, by removing a partial dependency
- It is already fully normalized
Show the answer
3NF, by removing the transitive dependency into a separate Supplier table
supplier_city depends on supplier_id, a non-key attribute, so it is a transitive dependency, exactly what 3NF removes. The fix: move supplier_id and supplier_city into their own Supplier table, linked back by a foreign key. 1NF is about atomic cells and 2NF about partial dependencies on a composite key, neither matches a non-key-determines-non-key chain.
Think first
Why 2NF needs a composite key
A partial dependency means a non-key attribute depends on only PART of the key. Think about it: if a table's primary key is a SINGLE column, can a partial dependency even exist? What does that tell you about when 2NF matters?
Show the answer
No, a single-column key has no 'parts' to partially depend on, so a table with a single-column primary key that is already in 1NF is automatically in 2NF. Partial dependencies can only arise with a composite key (like order_id + item_id), where a column might depend on just order_id. So 2NF only does real work when the key is composite, a subtle point examiners love to probe.
Watch out
Where marks leak
Muddling which form fixes what: 1NF = atomic values, 2NF = no partial dependency (composite key only), 3NF = no transitive dependency, BCNF = every determinant is a super key. Forgetting the forms are cumulative (2NF requires 1NF, etc.). And giving the rule without the fix (decompose into a linked table). The staircase order and the specific dependency each form removes are the exam's core targets.
Formula
Exam recipe: normalize this table
Work upward, showing each step: check 1NF (atomic cells, split any multi-valued), then 2NF (remove partial dependencies, only if composite key), then 3NF (remove transitive dependencies via non-key columns), naming the dependency you eliminate and the new table you create at each step. BCNF if a determinant is not a key. Show the decomposition at every stage, that worked progression is what earns full marks.
Theory
Unit 3 complete, Meera is ready
Meera's chaotic sheet is now a clean set of tables, Customers, Items, Suppliers, Orders, each fact in one place, anomalies gone. That is a real relational database design. Unit 4 finally lets her (and you) talk to it in SQL: creating these tables, inserting data, and asking questions. Everything you designed here becomes something you type next. Next: SQL data types and the CREATE statement.
Summary
Key takeaways
- The normal forms are a cumulative staircase: each requires the previous.
- 1NF: every cell holds a single atomic value (no multi-valued or repeating cells).
- 2NF: 1NF + no partial dependency (non-key depending on part of a composite key); matters only with composite keys.
- 3NF: 2NF + no transitive dependency (non-key determined by another non-key).
- BCNF: stricter 3NF, every determinant must be a super key.
- Fix each violation by decomposing into a separate table linked by a key.
- Memory hook: clean the room in passes, atomic, part-belongs, about-something-else.