ISYS1055 Chap.12 Normalisation to 3NF: Top-Down and Bottom-Up
Normalisation to 3NF: Top-Down and Bottom-Up
This is the payload chapter for Assessment Task 2 Part A. Week 9's outcome is to define normalisation and apply it both top-down and bottom-up, and Week 10 is pure practice on it - the course says outright that the week is all about practice and that no new content will be presented - with the stated skills being to use functional dependencies to determine a relation's primary key, to determine which normal form it conforms to, and to apply 1NF, 2NF and 3NF. Normalisation is a stepwise refinement guided entirely by the functional dependencies: 1NF for atomic values, 2NF for partial dependencies on a composite key, 3NF for transitive dependencies. The course stops at 3NF and says so - BCNF, 4NF and 5NF are named as mostly academic, with 3NF sufficient for almost all practical purposes.
What this chapter covers
- 01Normalisation defined: examine the functional dependencies, then rearrange attributes into relations - decomposing or merging - to reach a better design
- 02Two ways to use it - top-down as a validation technique after mapping an ER model (because not every functional dependency is captured in an ER model, so a mapped schema is occasionally not in 3NF) and bottom-up as a decomposition tool from an initial schema, which is not suited to large, complex designs
- 03The mnemonic - the key, the whole key, and nothing but the key - and what each form eliminates
- 041NF: every tuple component holds one and only one value; no repeating groups, no repeating values; and the subtler failure where an isolated attribute is really multi-valued
- 052NF: 1NF plus every non-key attribute fully dependent on the whole primary key; automatically satisfied when the key is a single attribute
- 063NF: 2NF plus no non-key attribute determined by another non-key attribute; practical test - any FD with a non-key attribute on the left means the relation is not in 3NF
- 07The single decomposition recipe used at both 2NF and 3NF: remove the offending attributes with a copy of their determinant, make the determinant the new relation's primary key, leave it behind as a foreign key
- 08The complete procedure, including merging relations that share a primary key and renaming relations meaningfully; and denormalisation as a deliberate trade-off of integrity for retrieval speed
Take one wide relation from 1NF to 3NF and present the final schema
- +1Re-derive the key before anything else, because every later test depends on it. Striking out every attribute that appears on a right-hand side leaves staffNo, siteCode and shiftDate, so PK = {staffNo, siteCode, shiftDate} - a composite key, which means 2NF is a live question rather than automatic.
- +11NF. Every component holds a single atomic value, there are no repeating groups and no numbered columns, and no attribute is isolated or implicitly multi-valued, so the relation is in 1NF. That is also the highest normal form it reaches, and the justification is the failure at the next rung rather than the success at this one.
- +12NF fails, so decompose. F1 shows staffName and payGrade depending on staffNo alone, and hourlyRate depends on staffNo indirectly through payGrade, so it too depends on only part of the key; F3 shows siteName and siteSuburb depending on siteCode alone. Only hoursWorked is fully dependent on the whole key. Apply the recipe - remove each partially dependent group with a copy of its determinant, make the determinant the new key, leave it behind as a foreign key: Shift(staffNo*, siteCode*, shiftDate, hoursWorked) · Staff(staffNo, staffName, payGrade, hourlyRate) · Site(siteCode, siteName, siteSuburb).
- +13NF. Test each new relation with the practical rule - is there any FD with a non-key attribute on the left? Shift and Site are clean. Staff is not: payGrade → hourlyRate has a non-key attribute determining another non-key attribute, which is a transitive dependency, and it is the reason every staff member on the same grade repeats the same rate. Apply the identical recipe: Staff(staffNo, staffName, payGrade*) · PayScale(payGrade, hourlyRate).
- +1Clean up and name. Check whether any two relations share a primary key and could prudently be merged - none do here. Give every relation a name that says what it represents, and present the final schema: Shift(staffNo*, siteCode*, shiftDate, hoursWorked) with composite key (staffNo, siteCode, shiftDate) · Staff(staffNo, staffName, payGrade*) · PayScale(payGrade, hourlyRate) · Site(siteCode, siteName, siteSuburb). Every anomaly from the wide version is now gone: a new site can be recorded with no shifts, deleting a shift destroys nothing else, and a rate change is one row.
Key terms
- Normalisation
- A design technique that begins by examining the functional dependencies between attributes and then rearranges the attributes into relations - decomposing one into several, or merging several into one - to reach a better design. It is used top-down as a validation technique after ER mapping, and bottom-up as a decomposition tool from an initial schema.
- First normal form (1NF)
- A relation is in 1NF when every tuple component contains one and only one value - all values atomic, with no repeating groups and no repeating values. The subtle failure is an isolated attribute that is really multi-valued, which has to be split into its own relation with a copy of the key.
- Second normal form (2NF)
- 1NF plus every non-primary-key attribute fully functionally dependent on the entire primary key, so no attribute depends on only part of a composite key. It only matters when the key is composite: a 1NF relation with a single-attribute key is automatically in 2NF.
- Third normal form (3NF)
- 2NF plus no non-primary-key attribute dependent on another non-primary-key attribute, i.e. no transitive dependency on the key. The practical test the course gives: if any FD has a non-key attribute on its left-hand side, the relation is not in 3NF.
- The decomposition recipe
- The same three moves at both 2NF and 3NF: remove the offending attributes together with a copy of their determinant; make the determinant the primary key of the new relation; leave the determinant in the original relation as a foreign key referencing it.
- Denormalisation
- Deliberately storing data in less-than-normalised relations to avoid expensive joins, trading data integrity for retrieval performance. It is a legitimate choice for time-critical applications provided the integrity is then managed by other means - validation on load, application-level checks, or triggers.
Normalisation to 3NF: Top-Down and Bottom-Up FAQ
If I mapped my schema properly from an ER model, why normalise it at all?
Because not every functional dependency is captured in an ER model. The mapping steps faithfully reproduce the entities, relationships and attributes you drew, but a dependency between two attributes inside one entity - a suburb determining a postcode, a grade determining a rate - was never part of the diagram, so it survives the mapping intact. Validating the mapped relations against 1NF, 2NF and 3NF is cheap insurance, and the course calls this out as the muddiest point worth answering.
How far should I normalise?
This course stops at 3NF and says so explicitly: BCNF, 4NF and 5NF are named but treated as mostly academic, with 3NF sufficient for almost all practical purposes. Going further is not rewarded, and stopping early is: a relation left at 2NF still repeats a value on every row that shares a determinant, which is exactly the modification anomaly you started out trying to remove.
Do I have to check 2NF if my primary key is a single attribute?
No. Partial dependency means dependency on part of the key, and a single-attribute key has no proper parts, so a 1NF relation with a simple key is automatically in 2NF. Say that in your working rather than silently skipping the step, then go straight to the transitive test for 3NF. Similarly, a relation with at most one non-key attribute is trivially in 3NF.
What is the difference between the top-down and bottom-up approaches?
Top-down starts from requirements, draws an ER model, maps it to relations and then uses normalisation to validate them; it works on designs of any size and is preferred when the design is substantially complex. Bottom-up starts from an initial schema, often one wide relation, and uses normalisation to decompose it; its suitability depends on the size and complexity of the database, and it is not suited to large or complex ones. Both converge on the same final schema, and Week 10 practises them in both directions.
Assessment move
Week 10 tells you what to do: practice, and more practice. Work the whole procedure end to end on a fresh relation every day of that week rather than re-reading a worked solution - determine all the FDs, derive the key by right-hand-side elimination, state the highest normal form with the dependency that breaks the next one, decompose to 3NF, merge anything that shares a key, and rename the relations meaningfully. Take the express path first, which is just the how, then repeat the same relations with the why, which is the anomaly and dependency reasoning from the previous chapter; the lecture is deliberately built to be run twice. Vary the shape of the practice too: sometimes validate a schema you mapped from an ER model (top-down), sometimes decompose one wide relation (bottom-up). Assessment Task 2's final submission falls at the end of Week 11 with its draft two weeks earlier, and quiz 5, in the Week 11 practical, is the cumulative one - the course states it covers the previous ten weeks - so this material is assessed twice over.
Working through Normalisation to 3NF: Top-Down and Bottom-Up in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 Normalisation to 3NF: Top-Down and Bottom-Up question and get a clear, step-by-step explanation grounded in how ISYS1055 is taught and assessed. Read this chapter free, then take your hardest questions to Sia.
