Data Modeling & Schema Design
Functional Dependencies
A functional dependency
X → Y states that any two legal tuples agreeing on X must agree on Y. Dependencies capture stable business rules, support candidate-key derivation, and reveal where one relation mixes facts with different determinants.- Dependencies are assertions about every legal state, not coincidences in sample data.
- A determinant need not be a declared key.
- Attribute closure tests whether a set determines all attributes.
- A candidate key is a minimal superkey: every attribute in it must be necessary for reaching the full closure.
- Armstrong's reflexivity, augmentation, and transitivity rules derive exactly the dependencies implied by a set.
- A minimal cover simplifies normalization by using singleton right sides and removing extraneous attributes and redundant dependencies.
Closure and candidate keys for an order-line relation
Let R(order_id, line_no, customer_id, placed_at, product_id, sku, quantity, unit_price) obey F = {order_id → customer_id, placed_at; product_id → sku; sku → product_id; (order_id, line_no) → product_id, quantity, unit_price}. The last rule identifies a line within its order; the first two encode header and product facts.
| Step | Dependency applied | Closure after step |
|---|---|---|
| 0 | Seed | {order_id, line_no} |
| 1 | order_id → customer_id, placed_at | Add customer_id, placed_at |
| 2 | (order_id, line_no) → product_id, quantity, unit_price | Add product_id, quantity, unit_price |
| 3 | product_id → sku | Add sku; all attributes reached |
| Starting set | What its closure misses | Conclusion |
|---|---|---|
{order_id} | line_no, product_id, sku, quantity, unit_price | line_no is necessary |
{line_no} | Header identity and all attributes outside the local line number | order_id is necessary |
{order_id, line_no} | Nothing | Candidate key |
{order_id, line_no, sku} | Nothing, but sku is removable | Superkey, not candidate key |