Chapter 07 · Functional Dependencies and Data Anomalies
Functional Dependencies Explained Visually
Learn functional dependencies as business rules about attribute determination, read X → Y notation correctly, and use dependencies to reveal hidden structure in relations.
Learning outcomes
A functional dependency is a formal way to express a business rule about determination between attributes. It tells us that whenever two valid rows agree on some attributes, they must also agree on other attributes. Functional dependencies are the foundation for normalization because they reveal which facts depend on which identifiers.
Read and interpret the notation \(X \rightarrow Y\).
Distinguish dependencies from mere correlations in current sample data.
Identify determinants, dependent attributes, trivial dependencies, and key dependencies.
Extract functional dependencies from WorkshopHub business rules.
The core idea
For a relation \(R\), the functional dependency:
\[ X \rightarrow Y \]
means: if two valid rows have the same values for every attribute in \(X\), they must also have the same values for every attribute in \(Y\).
We say X functionally determines Y, X is the determinant, and Y is functionally dependent on X.
WorkshopHub example
Suppose Part has:
Part( part_id, sku, description, current_unit_price)If part_id uniquely identifies a part, then:
\[ part\_id \rightarrow sku, description, current\_unit\_price \]
If SKU is also guaranteed unique, then:
\[ sku \rightarrow part\_id, description, current\_unit\_price \]
Both attributes are candidate determinants under the stated business rules.
Dependencies are rules, not observations
Imagine a dataset where every technician currently has a different last name. From the data alone you might observe:
last_name -> technician_idBut this is not a valid functional dependency unless the business guarantees that no two technicians can share a last name. Functional dependencies describe all valid database states, not accidental uniqueness in today's rows.
Ask whether the dependency must always hold according to the domain—not whether it happens to hold in a sample.
Determinants can contain multiple attributes
WorkshopHub may guarantee that serial numbers are unique only within manufacturer:
\[ (manufacturer\_id, serial\_number) \rightarrow asset\_id \]
Neither component may determine the asset alone:
manufacturer_id -/-> asset_idserial_number -/-> asset_idThe combination is the determinant.
Functional dependency versus foreign key
A foreign key says a referenced value must exist in another relation. A functional dependency says one set of attributes determines another. They are different concepts.
For example:
WorkOrder.asset_id -> Asset.asset_id [foreign-key relationship]and within WorkOrder:
\[ work\_order\_id \rightarrow asset\_id, status\_code, opened\_at \]
are different statements.
Trivial functional dependencies
A dependency \(X \rightarrow Y\) is trivial when \(Y \subseteq X\). For example:
\[ (work\_order\_id, technician\_id) \rightarrow technician\_id \]
This is always true because the right-hand attribute is already part of the left-hand set. Trivial dependencies do not reveal useful normalization structure.
Non-trivial dependencies
More interesting:
\[ employee\_number \rightarrow technician\_id \]
or:
\[ status\_code \rightarrow status\_display\_name \]
These say something substantive about how values determine other values.
Keys create dependencies
If K is a candidate key of relation R, then:
\[ K \rightarrow \text{every attribute in }R \]
This is why candidate-key reasoning and functional-dependency reasoning are tightly connected.
A visual example
The diagram reveals that part_description depends on part_id, not directly on the whole WorkOrderLine key. That hidden dependency is a clue that Part information has been copied into the wrong relation.
Transitivity appears naturally
If:
\[ work\_order\_line\_key \rightarrow part\_id \]
and:
\[ part\_id \rightarrow part\_description \]
then:
\[ work\_order\_line\_key \rightarrow part\_description \]
by transitivity. But the path matters for normalization, which we explore in Lesson 2.
Dependencies from business rules
| Business rule | Functional dependency |
|---|---|
| Each SKU identifies one Part. | sku -> part_id, description |
| Each employee number identifies one Technician. | employee_number -> technician_id, full_name |
| Status code has one canonical display name. | status_code -> status_display_name |
| One work-order line number is unique within one work order. | (work_order_id, line_number) -> ... |
| Serial number is unique only within manufacturer. | (manufacturer_id, serial_number) -> asset_id |
Dependencies that do not hold
For WorkOrderAssignment:
technician_id -/-> work_order_idbecause one technician can work on many work orders.
work_order_id -/-> technician_idbecause one work order can have many technicians.
This is a many-to-many relationship.
Minimal left sides
Suppose:
\[ (tenant\_id, username) \rightarrow user\_id \]
If username is not globally unique and tenant_id alone is not unique, both attributes are necessary. The determinant is minimal for that dependency.
Why functional dependencies matter
They help answer:
- What are the candidate keys?
- Which attributes depend on only part of a composite key?
- Which non-key attributes determine other non-key attributes?
- Where is information duplicated?
- Which decompositions preserve semantics?
Practice: write the dependencies
Training platform
Assume:
- Each
student_ididentifies one student and one email. - Each
course_codeidentifies one course title. - Each student can enroll in a course at most once per term.
Write functional dependencies for these rules.
Review answer
student_id -> student_email; course_code -> course_title; and (student_id, course_code, term_id) -> enrollment-specific attributes such as grade or status. Whether term_id itself determines dates or name depends on additional business rules.
Summary and next lesson
Functional dependencies express which attributes determine which others across all valid database states. They formalize business identity and expose hidden redundancy. The next lesson studies two dependency patterns that are especially important for normalization: partial and transitive dependencies.
References
- E. F. Codd, “Further Normalization of the Data Base Relational Model,” 1971.
- C. J. Date, Database Design and Relational Theory.
- Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.