Does X → Y mean X causes Y?

No. It means equal X values require equal Y values in valid rows.

Can X contain several columns?

Yes. A combination can determine another attribute even when no component alone does.

Can a sample prove a dependency?

A sample can disprove it by showing a violation, but apparent agreement does not establish that the rule holds for every valid future state.

AI Glossary / Data engineering and architecture / Intermediate

Functional dependency

Definition

A functional dependency is a rule in relational data stating that one set of attributes uniquely determines another set. Written X → Y, it means that any two rows agreeing on X must also agree on Y. It is a statement about valid data under the model, not a claim that X causes Y.

What it means

For example, if an EmployeeID identifies one employee record, it may determine that employee's recorded name in a current-state table. Several employees can share the same name, so the reverse dependency need not hold. The word functional refers to a single determined value for each determinant value, not to a software function that must be executed. The intended scope matters: a table containing historical names may require a date or version as part of the determinant.

How it works

Designers derive dependencies from the meaning and constraints of the domain. They use them to reason about keys and normal forms, and to identify redundant facts that can create update problems. Dependencies can involve several attributes on either side. A dependency observed in today's rows is not automatically a rule for all future valid rows. Database profiling can find candidate patterns or counterexamples, but business meaning is needed to establish which dependencies should actually hold.

Three practical examples

In a product catalog with stable product identifiers, ProductID determines the recorded product description within the chosen versioning scope.
In an order-line table, OrderID and LineNumber together determine the quantity recorded for that line.
An AI experiment registry uses ModelVersionID to determine its immutable configuration record, helping detect inconsistent metadata when results are combined.

Think of it this way

Think of looking up a value in a well-defined directory: one identifier leads to one corresponding entry. The analogy is limited because functional dependencies can involve combinations of attributes and do not require a physical lookup table. They also express a constraint on all valid records, not merely a convenient pattern in a small list.

Worked example

Suppose a relation has rows (E1, D7, Sales), (E2, D7, Sales) and (E3, D9, Support), representing EmployeeID, DepartmentID and DepartmentName. If the organization's rule is that each department identifier has one current name, then DepartmentID → DepartmentName holds. EmployeeID → DepartmentID may also hold if each employee has one current department. From those rules, EmployeeID determines DepartmentName. Storing the name repeatedly makes it possible to record D7 as both Sales and Service by mistake. Separating the department's name into its own relation can avoid that repeated fact, provided the decomposition and constraints are designed correctly.

Illustrative case study

Fictional case: Acorn Data finds that every postal code in a small sample happens to have one customer. An analyst proposes PostalCode → CustomerID, but the team rejects it because several customers can legitimately share a postal code. They distinguish an accidental sample pattern from a domain rule before using dependencies to redesign the data. This fictional case illustrates why profiling alone cannot establish a valid schema constraint.

Common misconceptions and mistakes

Do not confuse functional dependency with statistical correlation or causation. It is an exact rule under a specified relational model. Do not infer the reverse dependency automatically. If A determines B, B may still correspond to many A values. Avoid assuming that a unique column in a sample is a permanent key. Also, practical SQL nulls and temporal records require care when translating theoretical reasoning into constraints; a dependency must be interpreted against the actual representation and intended valid states.

Business use

Businesses use dependencies to clarify identity, reduce inconsistent duplication and improve integration rules. They are useful when explaining why a join should produce one match or why a field belongs in a separate entity. For AI datasets, dependency checks can expose contradictory metadata or unexpected row multiplication. However, breaking a dependency does not identify the correct replacement value by itself. Investigate whether the data are wrong, the rule is wrong or the table now represents a broader scope than originally intended.

Check your understanding

Two different customers have the same surname. Does this violate CustomerID → Surname?

Answer: No. The rule permits different identifiers to share a surname. It would be violated if the same customer identifier had conflicting surnames within the defined current-state model.

Related terms

Determinant
Candidate key
Database normalization
Transitive Dependency
Update Anomalies
attribute closure
update anomaly
lossless decomposition

Learn next

Write three proposed dependencies for a familiar table and state the business assumptions behind each. Construct a counterexample where possible. Then distinguish rules that follow from identifiers from patterns that merely happen to appear in the current data.

Sources

Primary teaching reference: Carnegie Mellon University database course, Functional Dependencies and Normalization, https://www.cs.cmu.edu/~natassa/courses/15-415/S03/notes/17Norm2up.pdf . University teaching material opened and extracted text consulted. Original examples emphasize domain rules and scope; full theoretical and editorial review remain pending.

Previous: Function word Next: Functional presence engine