AWS Cloud Architect & Developer · Databases
Functional Dependencies and First Normal Form
FDs, attribute closure, candidate keys and atomic cells — the rules that make the campus-shop tables split later.
Still the kiosk. A functional dependency is a rule "this column decides that column". Closure walks the rules. Keys are the sets whose walk reaches every column. 1NF is the boring-looking gate: one value per cell, so those rules have a place to stand. 2NF, 3NF and BCNF are the next lesson.
- AWS Cloud Architect & Developer
- Hard level
- 4 concepts
1A functional dependency is a rule
A functional dependency X \to Y says: if two rows agree on X, they must agree on Y. On Item, item_id → price_rs: every row with item_id 11 has price 12. If we wrote a second Tea row with price 15, we would have broken the rule — the same item would have two prices.
The left side is the determinant. It does not have to be a key of the whole table. On a badly designed "one big shop sheet" that repeats item_name on every sale, item_id → item_name still holds, even though item_id does not identify a sale. That leftover rule is what later normal forms will make us split out.
Figure. item_id 11 determines Tea and Rs 12. A second 11 with a different price would break the FD.
Read an FD off the shop
- Pick Xitem_id — the thing we claim is enough.
- Check YEvery 11 is Tea at Rs 12. The FD holds.
- Counter-rowA second 11 at Rs 15 would falsify item_id → price_rs.
| FD | In words |
|---|---|
| item_id → item_name, price_rs | an item has one name and price |
| student_id → name, hostel | a student has one name and hostel |
| sale_id → student_id, item_id, qty | a sale names one buyer, item, qty |
Two Sale rows both have item_id 11. item_id → price_rs requires that
- They must have the same sale_id
- They must agree on the Tea price; qty may still differ
- They must have the same student_id
The FD only constrains price (and name) for that item. qty is a fact about the sale, not about item_id.
2Attribute closure
The closure X^+ is every attribute we can reach from X by firing FDs. Start with X. Whenever the left side of an FD is already inside the set, add its right side. Stop when nothing new appears.
From student_id on the shop FDs we get name and hostel, then we stop — we do not get sale_id or qty, because no FD says a student determines a sale. From sale_id we get student_id, item_id, qty, and then (by firing the other FDs) name, hostel, item_name and price_rs. That is why sale_id can identify a whole joined line and student_id cannot.
Figure. student_id reaches name and hostel. qty stays out: no FD fires that far.
Walk student_id⁺
- Start{student_id}
- Fire student_id → name, hostel{student_id, name, hostel}
- StopNo remaining FD has its left side inside the set.
Closures on the shop FDs
Using student_id → name, hostel; item_id → item_name, price_rs; sale_id → student_id, item_id, qty. What is student_id⁺ and what is sale_id⁺?
- student_id⁺ start{student_id}
- + name, hostel{student_id, name, hostel}
- sale_id⁺ after its own FD{sale_id, student_id, item_id, qty}
- then fire the other two FDs{sale_id, student_id, item_id, qty, name, hostel, item_name, price_rs}
Pro tip. sale_id reaches every shop column. student_id does not reach qty.
item_id⁺ on the shop FDs is
- {item_id, item_name, price_rs}
- {item_id, sale_id, qty}
- every column of Sale
item_id determines the item columns only. It does not determine who bought it or how many.
3Candidate keys from closures
A superkey is a set of attributes whose closure is every attribute of the relation — it can identify a row. A candidate key is a superkey that is minimal: drop any column and the closure is no longer everything. The primary key is the candidate key we chose to enforce.
On Sale with only the shop FDs, sale_id⁺ is everything, and no proper subset of {sale_id} exists, so {sale_id} is the one candidate key. {student_id, item_id} is not a key of Sale: Asha can buy Tea twice on two different days and those rows would agree on student_id and item_id but not on sale_id or qty. We would need an extra FD we do not have.
Figure. sale_id⁺ is every Sale attribute, so {sale_id} is a candidate key. student_id⁺ is only student_id, name and hostel — sale_id, item_id and qty stay out — so {student_id} is not a key of Sale.
Test a set K
- CloseCompute K⁺ with the FDs.
- All attributes?If yes, K is a superkey.
- Minimal?If dropping any column of K breaks the closure, K is a candidate key.
Is {student_id} a key of Sale?
Sale attributes are sale_id, student_id, item_id, qty plus (if denormalised) name, hostel, item_name, price_rs. Using only student_id → name, hostel.
- student_id⁺{student_id, name, hostel}
- has sale_id, item_id, qty?no
- {student_id} is a key of Sale?no
Pro tip. A key of Student is not automatically a key of Sale.
A candidate key is
- Any set of columns the user likes
- A minimal superkey: its closure is all attributes, and no subset works
- Always exactly one column
Superkey = closure is everything. Candidate = minimal such set. Primary = the one we picked.
4First normal form: atomic values
First normal form says every cell holds one value, not a list. A cell "Samosa, Tea" is two items pretending to be one. You cannot say the price of "Samosa, Tea", you cannot COUNT items, and you cannot foreign-key to Item. 1NF is the promise that each cell is atomic — one item_id, one qty, one hostel.
The fix is more rows, not a cleverer cell. Asha buying a samosa and a tea becomes two Sale rows (100 and, if she also bought tea, another sale), each pointing at one item. That is why our running Sale table was already in 1NF: qty is a number, item_id is one id.
Figure. One stuffed cell on the left. Two atomic rows on the right. That is the 1NF rewrite.
Split a stuffed cell
- Bad rowAsha | "Samosa, Tea" | "20, 12" — two items and two prices in one row.
- 1NFTwo rows: Asha / Samosa / 20 and Asha / Tea / 12.
- Then keysEach row can now point at Item with a single item_id.
| name | items cell |
|---|---|
| Asha (bad) | Samosa, Tea |
| Asha row 1 | Samosa |
| Asha row 2 | Tea |
Coding lab. Walk a stuffed cell into 1NF runs in the app, with checks on your output.
A row with items = "Samosa, Tea" fails 1NF because
- Tea is cheaper than Samosa
- One cell holds two values, so we cannot treat item as a single attribute
- Asha bought more than one thing, which 1NF forbids
1NF forbids lists-in-a-cell, not multiple purchases. Multiple purchases become multiple rows.
Notes
- Normal forms: 1NF requires atomic values; 2NF removes partial dependency on part of a candidate key; 3NF removes transitive dependency of non-prime attributes.
- BCNF is stricter than 3NF: for every functional dependency X->Y, X must be a superkey; BCNF may not be dependency-preserving.
- A functional dependency X->Y means X uniquely determines Y; the closure X+ is the set of all attributes determined by X.
- A candidate key is a minimal attribute set whose closure yields all attributes; prime attributes belong to some candidate key.
- Integrity constraints: entity integrity (primary key not NULL), referential integrity (foreign key matches a key or is NULL), and domain constraints.
Formulas
- 2NF condition: no non-prime attribute is partially dependent on any candidate key.
- 3NF: for each FD X->Y, either X is a superkey OR Y is a prime attribute.
- BCNF: for each non-trivial FD X->Y, X must be a superkey.
- Attribute closure X+ is computed by repeatedly adding attributes reachable via FDs from X.
- Number of candidate keys is found by testing which attribute sets have closure = all attributes.
Exam traps & shortcuts
- Order of strictness: 1NF < 2NF < 3NF < BCNF < 4NF; each higher form implies all lower ones.
- Partial dependency needs a composite key — a table with a single-attribute key is automatically at least 2NF.
- 3NF is always achievable and dependency-preserving; BCNF sometimes sacrifices dependency preservation.
Reference tables
| Determinant | Determines |
|---|---|
| item_id | item_name, price_rs |
| student_id | name, hostel |
| sale_id | student_id, item_id, qty |
Recap
Rules, then a walk, then a key, then atomic cells.
- FD
- X \to Y: rows that agree on X must agree on Y.
- Closure
- student_id⁺ = {student_id, name, hostel}. qty is not in it.
- Key
- Minimal superkey. sale_id is the candidate key of Sale.
- 1NF
- One value per cell. "Samosa, Tea" becomes two rows.
Practise Functional Dependencies and First Normal Form
Reading is free and needs no account. Practice, mocks and progress live in the app.
- A 2-question practice set that ends the chapter
- 4 quick checks with worked explanations
- Timed mocks scored with the real marking scheme
- Readiness tracked per topic, kept on your device