Multiple choiceQuestion 1 · Recall
Loading…
By the end you can spot a transitive dependency, explain whether a given set of tables is in Third Normal Form (3NF), and normalise a scenario all the way from an unnormalised list to a 3NF design.
1NF removed lists. 2NF removed fields that depended on only part of a composite key. 3NF removes the last common problem: a non-key field that depends on another non-key field.
Suppose a Student table stores StudentID, StudentName, TutorID and TutorName.
StudentID determines TutorID: each student has one tutor. And TutorID determines TutorName: each tutor has one name.
So StudentID determines TutorName through TutorID. That chain is a transitive dependency. The tutor's name does not really describe the student; it describes the tutor.
The consequence is familiar: if a tutor changes their name, every student row for that tutor must be updated, and a missed row makes the data inconsistent.
Third Normal Form (3NF)
A table is in 3NF when it is in 2NF and no non-key field depends on another non-key field. In other words, every non-key field depends directly on the primary key, and on nothing else.
Worked example: Splitting out the tutor
Normalise Student(StudentID, StudentName, TutorID, TutorName) into 3NF.
Some exam questions give you tables and ask whether they are in 3NF, and why. Work through the forms in order and say what you find.
A good answer names the dependency and the field that causes it: "It is not in 3NF because TutorName depends on TutorID, which is not a key." Then say the fix: "Move TutorName into a Tutor table keyed by TutorID."
Beware of one subtlety. A field that looks transitive may be fine if the field it depends on is itself a candidate key. The rule is about depending on a non-key field, so if the determinant uniquely identifies the row, the dependency is not transitive in the harmful sense.
Worked example: A full normalisation from scratch
A shop records each sale in one table:
Sale(SaleID, SaleDate, CustomerID, CustomerName, CustomerCity, ProductID, ProductName, Quantity)
The primary key is (SaleID, ProductID). Normalise it to 3NF.
3NF means every field depends on the key.
That is closer to 2NF. 3NF adds a restriction: no non-key field may depend on another non-key field. A field can depend on the key and still fail 3NF if it also depends on something else.
Normalising to 3NF always improves performance.
It removes redundancy and anomalies, but it creates more tables, so queries may need more joins. Performance is a separate design trade-off; correctness comes first.
You can skip 2NF if you fix 3NF.
The forms are cumulative. A table that is not in 2NF is not in 3NF either, and the exam expects you to identify the earlier problem.
A foreign key is a transitive dependency.
TutorName depends on TutorID, not on StudentID". Vague answers such as "the data is repeated" rarely earn the dependency mark.A foreign key is a field that refers to another table's primary key, which is normal and desirable. The problem is storing a non-key fact that depends on another non-key fact in the same table.