FIT2094 Chap.3 Normalisation from UNF to 3NF
Normalisation from UNF to 3NF
Normalisation restructures data around functional dependencies so that each fact is stored under the key that determines it. The required method begins with an unnormalised relation that faithfully represents the source document. First normal form removes repeating groups and uses atomic values. Second normal form removes dependencies on only part of a composite candidate key.
Third normal form removes dependencies in which a non-key attribute determines another non-key attribute.
The process is evidence, not ceremony. Candidate keys must be stated at 1NF, dependencies must be shown at the stage where they matter, and every source attribute must remain accounted for. A decomposition should be lossless and should preserve the important business dependencies.
Adding a surrogate key too early can hide the natural candidate key and bypass the analysis. Implementation choices belong after the formal transformation.
Audit the transformation with an attribute ledger. Mark where every original attribute appears after each split and identify the determinant that justifies its destination.
Reconstruct one original business event by joining the decomposed relations on their shared keys; missing rows expose a lossy split, while unexplained duplicates expose a key mistake. Then simulate an update to a descriptive fact and verify that it changes in one place. A relation is not in a higher normal form merely because it is smaller.
The reason must be stated as a dependency, and the resulting keys must still represent the events present in the original document.
What this chapter covers
- 01
Functional dependencies and determinants
- 02
Repeating groups and unnormalised form
- 03
Atomic rows and candidate keys in first normal form
- 04
Partial dependencies and second normal form
- 05
Transitive dependencies and third normal form
- 06
Dependency diagrams and relation decomposition
- 07
Lossless reconstruction and naming consistency
Worked example · free
Normalise a service-order document
- +1Move the repeating part group into rows to form SERVICE_1NF with candidate key (order_no, part_no). Keep every source attribute.
- +2State partial dependencies: order_no determines order_date and customer_no, while part_no determines part_name. The full pair determines quantity.
- +2Create SERVICE_ORDER(order_no, order_date, customer_no), PART(part_no, part_name) and ORDER_PART(order_no, part_no, quantity) for 2NF.
- +2Remove the transitive dependency customer_no to customer_name by creating CUSTOMER(customer_no, customer_name) and retaining customer_no in SERVICE_ORDER.
- +1Join the four 3NF relations and verify that the original document facts return without spurious order-part combinations.
Key terms
- Functional dependency
- A business-rule constraint in which one attribute set determines another for every valid state of the relation.
- Unnormalised form
- A faithful source representation that may contain repeating groups or non-atomic structures before the formal transformation begins.
- First normal form
- A relation state with atomic values and no repeating groups, where candidate keys can be identified explicitly.
- Partial dependency
- A dependency in which a non-key attribute depends on only part of a composite candidate key.
- Transitive dependency
- A dependency in which a non-key attribute determines another non-key attribute under the relation's key.
- Lossless decomposition
- A split into relations that can be joined to reconstruct the original valid facts without information loss or spurious tuples.
Normalisation from UNF to 3NF FAQ
Why must I show every normal form?
The transformation demonstrates which dependency caused each decomposition. Jumping directly to final relations hides candidate-key analysis and makes it impossible to verify whether attributes were lost, added or assigned to the wrong determinant.
How do I find a partial dependency?
Start with a composite candidate key and test whether a non-key attribute is determined by only one proper subset. If order_no alone determines order_date inside an order-line relation keyed by order_no and item_no, the dependency is partial.
Why should I avoid surrogate keys during normalisation?
A generated identifier can make every row look uniquely keyed without revealing the dependencies in the source facts. Complete the natural-key and dependency analysis first. A later logical design may add a justified surrogate while preserving natural uniqueness.
How can I check that a decomposition is correct?
Trace every source attribute, identify the key of every final relation, join the relations through shared keys and confirm the original facts return without spurious combinations. Also test whether insert, update and delete anomalies have been removed.
Assessment move
Practise from documents rather than starting with clean tables. Write one UNF relation, mark the repeating group and list all source attributes. Convert to atomic 1NF rows and state every candidate key. Write functional dependencies with determinants on the left, then label them full, partial or transitive. Create 2NF relations only for partial dependencies and 3NF relations only for transitive dependencies.
Keep a trace table showing where each attribute moved. Rejoin the final relations and test the three anomaly types. Use the assignment checklist to confirm that UNF, 1NF, 2NF and 3NF headings, candidate keys and dependencies are all visible and names match the logical model.
After each decomposition, explain which update, insert or delete anomaly is removed and which shared key makes reconstruction possible without inventing rows.
