ISYS1055 Chap.11 Functional Dependencies and Update Anomalies
Functional Dependencies and Update Anomalies
Week 9 opens the design half of ISYS1055 with the diagnostic half of normalisation: observe why good database design matters, identify the indicators of poor design - insertion, deletion and modification anomalies - and define the functional dependencies that drive every decomposition. The course states the point plainly: a database may stay with a business for its entire life, so a designer is not designing for the next year or two. This chapter is the direct payload for Assessment Task 2 Part A Q1, listing the functional dependencies, and for the primary-key derivation that Q2 depends on, and it runs alongside quiz 4 in the Week 9 practical.
What this chapter covers
- 01The four qualities of a well-designed database: minimal attributes, attributes with a close logical relationship in the same relation, minimal redundancy (foreign keys excepted), and update anomalies minimised or eliminated
- 02The three defects to name on sight - a repeating group spread across numbered columns, one entity's details embedded inside another entity's relation, and a non-key attribute determined by another non-key attribute
- 03Insertion anomaly: a fact about one entity cannot be recorded without a fact about another, and redundant copies can disagree
- 04Deletion anomaly: removing one fact silently destroys the only copy of another
- 05Modification anomaly: one logical change requires many physical row updates, and missing one leaves the database asserting two different truths
- 06Functional dependency A → B: each value of A is associated with exactly one value of B, A the determinant and B the dependent - taken from semantics that must hold for all time, never from the sample rows, and holding among attributes rather than between entities as ER relationships do
- 07Deriving the primary key from an FD set: list all FDs, strike out every attribute appearing on a right-hand side, and what remains is the key
- 08Full versus partial dependency (only meaningful with a composite left-hand side) and transitive dependency, the two defects that 2NF and 3NF remove
Derive the primary key from an FD set, then demonstrate all three anomalies
- +1(a) Read the FDs off the business rules. F1: staffNo → staffName, payGrade. F2: payGrade → hourlyRate. F3: siteCode → siteName, siteSuburb. F4: staffNo, siteCode, shiftDate → hoursWorked. Do not also list staffNo → hourlyRate: it is derivable through F1 and F2, and a derivable FD is struck out of the list rather than recorded twice.
- +1(b) Apply right-hand-side elimination. The attributes appearing on the right of some FD are staffName, payGrade, hourlyRate, siteName, siteSuburb and hoursWorked; strike them all out. The attributes never determined by anything are staffNo, siteCode and shiftDate, so PK = {staffNo, siteCode, shiftDate}. Do the procedure rather than guessing: common sense would probably have proposed staffNo alone, which does not identify a row at all.
- +1(c1) Insertion anomaly. A newly contracted site cannot be recorded until somebody has actually worked a shift there, because siteName and siteSuburb only exist on a roster row, and a row needs the whole composite key. Worse, because the site details are repeated on every shift row, two rows can disagree about the same site's suburb.
- +1(c2) Deletion anomaly. Delete the last shift worked at a site - the roster is being tidied at the end of a contract - and the site's name and suburb disappear with it, even though the business still needs the site on its books. One fact was deleted; a second, unrelated fact was destroyed as collateral.
- +1(c3) Modification anomaly. A pay grade's hourly rate changes. Because hourlyRate is stored on every shift row for every staff member on that grade, one logical change becomes many physical row updates, and missing a single row leaves the database asserting two different rates for one grade. The common cause of all three is that facts about staff, pay grades and sites are stored inside a relation whose key is about shifts.
Key terms
- Functional dependency
- A one-way constraint between attributes of a relation, written A → B and read as "B is functionally dependent on A" or "A determines B": each value of A is associated with exactly one value of B. A is the determinant and B the dependent. The mental prompt is "given I know A, what else do I know?"
- Semantics, not sample data
- The rule that decides whether an FD holds. A dependency must hold for all time, on the basis of what the business allows, not on what today's rows happen to show - which is why a name never determines an identifier even when every name in the sample is unique.
- Insertion anomaly
- A fact about one entity cannot be recorded without also having a fact about another - a new site with no shifts yet cannot be stored at all - and, because details are duplicated across rows, two rows may end up disagreeing about the same thing.
- Deletion anomaly
- Deleting a fact about one entity silently destroys the only stored copy of a fact about another, as when removing the last shift row for a site also removes the site's name and suburb.
- Modification (update) anomaly
- One logical change requires many physical row updates because the value is stored redundantly; missing one leaves the database asserting two different values for the same fact.
- Full, partial and transitive dependency
- A dependency is full when every attribute on a composite left-hand side is needed for it to hold, and partial when a proper subset of that left-hand side already determines the right-hand side. It is transitive when A → B and B → C, so C depends on A only through B. Partial dependencies are what 2NF removes; transitive dependencies are what 3NF removes.
Functional Dependencies and Update Anomalies FAQ
How do I find the functional dependencies in the first place?
From the stated business rules, one rule at a time, asking "given I know this, what else do I know for certain, for all time?" Then prune the list: strike out any FD that is derivable from others (if an identifier determines a supplier and the supplier determines its address, do not also list the identifier determining the address), and strike out any FD that does not really hold. An attribute that turns out to be determined by nothing is a signal that it belongs in the key or in a relation of its own.
Why can I not just use common sense to pick the primary key?
Because common sense reads the relation name rather than the dependencies. In a relation whose FDs are staffNo → staffName and siteCode → siteName, neither identifier alone determines a shift, and the key is composite. The course states twice that you must run the procedure: list the FDs, strike out every attribute that appears on any right-hand side, and take what remains. The answer is often a key nobody would have guessed.
Are functional dependencies the same as ER relationships?
No, and confusing them is a common error. ER relationships hold between entities and are drawn as associations with multiplicity; functional dependencies hold among attributes inside one relation and are written with arrows. A model can have perfectly good relationships and still contain a relation whose attributes carry a dependency the ER diagram never captured - which is exactly why a mapped schema still has to be validated in Week 10.
What is the difference between a partial and a transitive dependency?
A partial dependency exists only when the primary key is composite: some attribute depends on part of the key rather than on the whole of it. A transitive dependency exists when a non-key attribute is determined by another non-key attribute, so it depends on the key only indirectly. Each has its own normal form - partial dependencies are removed at 2NF, transitive ones at 3NF - and naming the wrong one is the standard way to lose the justification mark.
Assessment move
Practise on relations you did not build, because in the assignment the relation and its business rules arrive together and the work is entirely in reading them correctly. Take any wide spreadsheet you can find, write out the FDs it implies, then run right-hand-side elimination and see whether the key you derive matches the one whoever built it assumed. Then force yourself to produce all three anomalies on that same relation with concrete operations - a row you cannot insert, a row whose deletion loses something else, and a change that needs updating in several places - because naming the anomaly is easy and demonstrating it on an instance is what earns the mark. Keep every FD list you write: Part A of Assessment Task 2 opens with exactly this task, its draft is due at the end of Week 9, and the draft gate carries a stated deduction if it is missing or incomplete.
Working through Functional Dependencies and Update Anomalies in ISYS1055? Sia is AskSia’s AI Information Technology tutor — ask any ISYS1055 Functional Dependencies and Update Anomalies 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.
