INFO20003 Chap.9 Normalisation and Dependencies
Normalisation and Dependencies
Normalisation stores each fact with the determinant that gives it meaning. Functional dependencies expose repeated facts and explain update, insertion and deletion anomalies.
This chapter starts from candidate keys, identifies partial dependencies that violate second normal form and transitive dependencies that violate third normal form, then checks whether decomposition is lossless and whether the original constraints remain enforceable without recombining relations. A functional dependency X determines Y means that any two legal tuples agreeing on X must also agree on Y.
It is a semantic rule over every permitted instance, not a pattern inferred solely from current data. Attribute closure applies the stated dependencies repeatedly and shows whether a determinant reaches every attribute. A candidate key is a minimal determinant of the whole relation; prime attributes occur in at least one candidate key. First normal form requires scalar values within the course model.
Second normal form removes a non-prime attribute's dependency on a proper subset of a composite candidate key. Third normal form addresses transitive dependence of non-key facts through another non-key determinant, using the formal determinant-or-prime condition when several keys exist. Boyce-Codd normal form applies the stricter requirement that every non-trivial determinant be a superkey.
Anomalies explain why these rules matter. Repeated descriptive facts invite inconsistent updates, a relation may prevent insertion of one subject until another fact exists, and deleting the final combined row may erase an independent fact. Decompose by placing each dependency with its determinant, then retain a relation that carries the candidate key and connections needed to reconstruct the original information.
A good decomposition is lossless: joining the components recreates legal original tuples without spurious combinations. It is also desirable to preserve dependencies so constraints can be checked inside individual components rather than through an expensive join. Those properties are distinct from reaching a named normal form.
Use the binary lossless test or a chase-style argument when required, and explicitly locate where every dependency is enforced. Surrogate keys do not repair dependency problems because they add an identifier without changing which business attributes determine other facts. Denormalisation is a conscious physical or reporting trade-off only after the dependencies and resulting update controls are understood.
The exam method is therefore: write keys and dependencies, identify the first violated form, demonstrate the anomaly, decompose, and verify losslessness plus dependency preservation separately.
What this chapter covers
- 01
Functional dependencies
- 02
Candidate keys
- 03
Update anomalies
- 04
First normal form
- 05
Second normal form
- 06
Third normal form
- 07
Lossless join
- 08
Dependency preservation
Worked example · free
Normalise enrolment facts
- 1Identify the composite candidate key.
- 1Recognise student_name as partially dependent on student_id.
- 1Recognise subject facts as partially dependent on subject_id.
- 1Create Student and Subject relations for those determinants.
- 1Keep grade with the complete Enrolment key and verify the joins.
Key terms
- Functional dependency
- A rule requiring equal determinant values to imply equal dependent values in every legal state.
- Determinant
- The attribute set on the left side of a functional dependency.
- Update anomaly
- A risk that one fact must be changed in several rows and can become inconsistent.
- Partial dependency
- Dependence of a non-key attribute on only part of a composite candidate key.
- Transitive dependency
- Dependence of a non-key attribute on a key through another non-key determinant.
- Lossless decomposition
- A decomposition whose natural join reconstructs exactly the original legal relation.
Normalisation and Dependencies FAQ
Are normal form and lossless decomposition the same property?
No. Normal form constrains dependency patterns inside each resulting relation. Losslessness asks whether joining the components recreates exactly the legal original tuples, and dependency preservation asks whether original rules remain enforceable without a join. Report and test these three properties separately rather than treating a 3NF label as proof of all three.
Can sample rows prove a functional dependency?
No. Matching sample values may be accidental. A counterexample can refute a proposed dependency, but a business rule must establish that agreement on the determinant always forces agreement on the dependent values.
What is the difference between second and third normal form?
Second normal form removes dependence on part of a composite candidate key. Third normal form removes non-key facts determined transitively through another non-key determinant. Both repairs begin by naming the exact dependency.
What should be checked after decomposition?
Verify that joining the relations recreates the original legal facts without spurious tuples and that required dependencies can still be enforced. Reaching a labelled normal form does not replace those checks.
Exam move
Underline candidate-key attributes before moving anything. Write every supplied functional dependency and compute which attributes each determinant controls. Identify the earliest violated normal form, then create relations that correspond to meaningful determinants. Use anomalies as evidence: describe the repeated update, blocked insertion or accidental deletion the decomposition removes.
Afterward, join the relations on a tiny invented instance to check for spurious tuples and list where each dependency will be enforced. Avoid normalising from visual clutter; a wide relation can be sound, while a short relation can still contain a transitive dependency.
Working through Normalisation and Dependencies in INFO20003? Sia is AskSia’s AI Computer Science tutor — ask any INFO20003 Normalisation and Dependencies question and get a clear, step-by-step explanation grounded in how INFO20003 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.