Functional dependencies are the backbone of database design, defining relationships between attributes. They're crucial for understanding how data is connected and organized within a database. Knowing these dependencies helps create efficient, normalized database structures.
and inference rules are your toolset for working with functional dependencies. These rules allow you to deduce new dependencies from existing ones, helping you uncover hidden relationships in your data and optimize your database schema.
(FD) denotes a relationship between attributes where the value of one attribute determines the value of another
Given relation R, attribute Y is functionally on attribute X () if each X value is associated with precisely one Y value
refers to the attribute on the left-hand side of a functional dependency (X in X → Y)
Dependent represents the attribute on the right-hand side of a functional dependency (Y in X → Y)
Types of Functional Dependencies
occurs when an attribute depends on a composite key but not on any proper subset of that key
If (X, Y) → Z, but neither X → Z nor Y → Z hold, then Z is fully functionally dependent on (X, Y)
Partial functional dependency arises when an attribute depends on only part of a composite key
If (X, Y) → Z and either X → Z or Y → Z hold, then Z is partially functionally dependent on (X, Y)
Partial dependencies can lead to data redundancy and update anomalies in a relation
Attribute Closure and Inference Rules
Attribute Closure
Closure of attributes refers to the set of all attributes functionally determined by a given set of attributes
To find the closure of attribute set X (denoted as X+), start with X and repeatedly add attributes based on given functional dependencies until no more attributes can be added
helps determine all functional dependencies that can be inferred from a given set of dependencies
Inference Rules
Armstrong's axioms are a set of inference rules used to infer all functional dependencies on a relational schema
: If Y ⊆ X, then X → Y
: If X → Y, then XZ → YZ
: If X → Y and Y → Z, then X → Z
Additional inference rules derived from Armstrong's axioms include:
: If X → Y and X → Z, then X → YZ
: If X → YZ, then X → Y and X → Z
: If X → Y and WY → Z, then WX → Z
Transitive Dependencies
Concept and Implications
occurs when a non-prime attribute depends on another non-prime attribute
If X → Y and Y → Z, but X is not functionally dependent on Z, then Z transitively depends on X via Y
Transitive dependencies can lead to data redundancy and update anomalies in a relation
Removing transitive dependencies is a key step in normalizing relations to higher normal forms (e.g., 3NF)
Example Scenario
Consider a relation R(A, B, C) with functional dependencies A → B and B → C
In this case, C transitively depends on A via B
To eliminate the transitive dependency, decompose R into two relations:
R1(A, B) with FD A → B
R2(B, C) with FD B → C
The resulting relations are free of transitive dependencies and in a higher normal form