A3.2.5 · Database design

The difference between normal forms

If fixing one instructor's typo means editing the same name in five different rows, the table is breaking a specific, nameable rule, and normal forms are how you name which one.

Core for SL & HL Command term: explain Builds on A3.2.4

Explainer

A workshop signup sheet lists every participant alongside the workshop they joined, its title, and the instructor teaching it, all crammed into one table. Rename the instructor, and that new name has to be typed into every single row they appear in. Miss one, and the table now silently disagrees with itself about who's teaching what. Normalization is the fix, and it comes in graded stages, each one closing off a specific way this kind of table can go wrong.

Normalization is the discipline of restructuring a table's fields so every fact lives in exactly one place, rather than copied wherever it happens to be needed. Fewer duplicate copies also means a physically smaller table, and a smaller table simply has less for the disk to read on every query, so this pays off in raw speed too, not just tidiness.

What normalization is actually fixing

Real problems in an unnormalized table

Data duplication: the same fact (an instructor's name, a workshop's title) stored in many rows instead of one.
Missing data: a fact can't be recorded at all until it happens to appear alongside something else, e.g. a new workshop with no participants yet has nowhere to live.
Dependency concerns: a field that looks like it belongs to the row actually depends on something narrower than the whole key, or on a different field entirely. Several distinct flavours of this exist, covered below.

The vocabulary normal forms are built from

Try to split a field's content into two meaningful pieces, name and surname out of a full name, say, and if that split is possible, the field was never atomic to begin with: atomic means one cell, one fact, nothing hiding inside it that could be pulled apart further. Unique identification is the simpler of the two: some field, or some combination of fields, has to pin down one row and one row only, with zero ambiguity about which row is meant.

Whenever knowing one field's value pins down another field's value automatically, that second field is said to have a functional dependency on the first, the first is its determinant, the second is its dependent. The relationship only runs one way: the determinant fixes the dependent, but the dependent tells you nothing about the determinant. Three specific kinds matter here:

Three kinds of dependency

Full functional dependency: the dependent attribute relies on the WHOLE primary key. Healthy, this is what every non-key attribute should have.
Partial dependency: the primary key is composite, made of two or more fields together, and a non-key attribute leans on only one piece of that combination rather than needing the whole thing.
Transitive dependency: a non-key attribute's real determinant is a DIFFERENT non-key attribute sitting in the same table, not the primary key at all, the primary key only reaches it secondhand.

1NF, 2NF, 3NF: what each one requires, and what it fixes

1NF

A working primary key exists, no attribute is repeated, no group of attributes recurs, and every field genuinely holds one indivisible value.

Fixes: combined, non-atomic fields and repeating groups.

Example: see Step 2 of the stepper below.

2NF

Everything 1NF already requires, PLUS every non-key field genuinely needs the complete primary key to determine it, not just a slice of it, so no partial dependency survives.

Fixes: partial-key dependencies (only relevant when the key is composite).

Example: see Step 3 of the stepper below.

3NF

Everything 2NF already requires, PLUS tracing any non-key field back to its determinant leads straight to the primary key, with no detour through some other non-key field along the way.

Fixes: transitive (non-key) dependencies.

Example: see Step 4 of the stepper below.

Worked example

One unnormalized table, several real violations

WORKSHOP_SIGNUP tracks who's attending which workshop. Click any column to see exactly which rule it breaks, and why.


P01	Noor Aziz	W1	Digital Photography	I1	Marcus Webb
P02	Tariq Idris	W1	Digital Photography	I1	Marcus Webb
P01	Noor Aziz	W2	Pottery Basics	I2	Sana Farouk

Click a column header to see the rule it breaks (or satisfies).

Click a column above to see its dependency status.

Now watch it actually get fixed, one normal form at a time

Same table, same violations, click through and see exactly what each normal form changes.

Step 1 of 5 Unnormalized

Check your understanding

InstructorName depends on InstructorID, which is itself just a non-key attribute in the table, not part of the primary key. What kind of dependency is this?

Common mistake

Markers reported this pattern for A3.2.5

RecurringPartial dependency and transitive dependency get confused constantly, since both are "a non-key attribute depending on the wrong thing." The difference is WHAT the wrong thing is: partial dependency is about depending on only part of a composite primary key, transitive dependency is about depending on another non-key attribute instead of the primary key at all.

What a weak answer looks like: "InstructorName is a partial dependency because it doesn't depend on the whole key." Half right, it doesn't depend on the key, but not because of a composite-key split, InstructorName depends on a DIFFERENT non-key field entirely.

✓

What it should say: "InstructorName is a transitive dependency, it depends on InstructorID, a non-key attribute, rather than depending directly on the primary key." Names the correct dependency type by identifying exactly what it depends on.

Revision questions

Past-paper style. Write an answer, then reveal the marking points and mark yourself.

Question 1 · 2 marks

[2]Explain what is meant by atomicity, and give an example of a field that is not atomic.

Question 2 · 3 marks

[3]Explain the difference between a partial dependency and a transitive dependency.

Question 3 · 4 marks

[4]A table ORDER_ITEM(OrderID, ProductID, ProductName, Quantity) uses a composite primary key of OrderID and ProductID. ProductName depends only on ProductID. Identify the type of dependency this creates, and explain which normal form it violates.

Flashcards

Active recall for this statement's key terms.