9.1 Functional dependency theory

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.

Armstrong's axioms 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.

Functional Dependencies

Key Concepts

Top images from around the web for Key Concepts

• 

  Chapter 11 Functional Dependencies – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Chapter 11 Functional Dependencies – Database Design – 2nd Edition View original

  Is this image relevant?

1 of 1

Top images from around the web for Key Concepts

• 

  Chapter 11 Functional Dependencies – Database Design – 2nd Edition View original

  Is this image relevant?

• 

  Chapter 11 Functional Dependencies – Database Design – 2nd Edition View original

  Is this image relevant?

1 of 1

• Functional dependency (FD) denotes a relationship between attributes where the value of one attribute determines the value of another
• Given relation R, attribute Y is functionally dependent on attribute X (X → Y) if each X value is associated with precisely one Y value
• Determinant 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

• Full functional dependency 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
• Attribute closure 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
  • Reflexivity rule: If Y ⊆ X, then X → Y
  • Augmentation rule: If X → Y, then XZ → YZ
  • Transitivity rule: If X → Y and Y → Z, then X → Z
• Additional inference rules derived from Armstrong's axioms include:
  • Union rule: If X → Y and X → Z, then X → YZ
  • Decomposition rule: If X → YZ, then X → Y and X → Z
  • Pseudotransitivity rule: If X → Y and WY → Z, then WX → Z

Transitive Dependencies

Concept and Implications

• Transitive dependency 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