Functional dependency in DBMS is a constraint between two set of attributes. In a relational database schema having n attributes, there exists a universal relation R = {A1, A2, A3, ..., An}. A functional dependency is denoted by X→Y, where X and Y are subset of R. In relation R, attribute (or set of attributes) Y is functionally dependent to attribute X, means value of X can uniquely determine the value of Y.
The abbreviation of functional dependency is FD or f.d.
Consider the below relation, in this each value of X uniquely determines value of Y
Now consider the below table, here there are two 1s in X attribute but both determines two different value of Y, this violates functional dependency X->Y.
Inference Rules for Functional Dependencies
In a database schema, F is a set of functional dependency. But are some other dependency that can be inferred or deduced from functional dependencies FD in F.
The set of all dependencies including F as well as other dependencies inferred from F are known as closure of F and it is denoted by F*
For example: {Emp_id → {Emp_name, Emp_salary, Emp_mobile}}
Emp_mobile → {Emp_name, Emp_salary}
Following dependencies can be inferred from above two FD
Emp_mobile → Emp_name
Emp_id → Emp_id
The following rules are inference rules for functional dependency.
- Reflexive rule
If Y ⊆ X, then X →Y
- Augmentation rule
If {X →Y}, then XZ →YZ
- Transitive rule
If {X →Y, Y →Z}, then X →Z
- Decomposition, or projective, rule
If {X →YZ}, then X →Y
- Union, or additive, rule
If {X →Y, X →Z}, then X →YZ
- Pseudotransitive rule
If {X →Y, WY →Z}, then WX →Z