Functional Dependencies in Database Systems: Understanding Normal Forms and Decomposition, Slides of Database Management Systems (DBMS)

Download Functional Dependencies in Database Systems: Understanding Normal Forms and Decomposition and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Functional Dependencies

(Part 3)

Agenda

• Quick Review

• What is a functional dependency
• Different normal forms

• Functional Dependency Theory

• Closure of a Set of Dependencies

• Decomposition using F.D.

• Multivalued dependencies

Functional Dependency Theory

• Armstrong’s axioms (page 279)
  • Reflexivity rule: If β ⊆ α, then α→β holds
    • Example: A → A , BCD → BC
  • Augmentation rule: If α → β , then γα → γβ
    • Example: A → B, therefore AC → BC
  • Transitivity: If α → β and β → γ then α → γ
    • Example: A → B and B → C then A → C

Functional Dependency Theory

• Additional rules (page 280)
  • Union rule: If α → β and α → γ then α → γβ
    • Example: If A → B and A → C then A → BC
  • Decomposition rule: If α → βγ , then α → β and α → γ
    • Example: A → BC, then A → B and A → C
  • Pseudo-transitivity: If α → β and γβ → δ then αγ → δ
    • Example: A → B and BD → C then AD → C

Decomposition

• The ability to compute F +^ enables us to convert a

relation R into any normal form

• Example: BCNF Decomposition
  • See pages 289 – 290

lending = ( branch_name, branch_city, assets, customer_name, loan_number, amount )

Candidate Key: { loan_number, customer_name } Functional Dependencies: branch_name → assets branch_city loan_number → amount branch_name

Decomposition

• The problem

branch_name → assets branch_city Valid but branch_name is not a superkey, therefore it is not in BCNF!

• The solution – decomposition!

lending = ( branch_name, branch_city, assets, customer_name, loan_number, amount ) Decomposes to multiple relations: branch = ( branch_name, branch_city, assets ) loan_info = ( branch_name, customer_name, loan_number, amount )

Decomposition

• The beginning:

lending = ( branch_name, branch_city, assets, customer_name, loan_number, amount )

• The end result:

branch = ( branch_name, branch_city, assets ) loanb = ( loan_number, branch_name, amount ) borrower = ( customer_name, loan_number )

The Original Functional Dependencies: branch_name → assets branch_city loan_number → amount branch_name

Everything is now in BCNF!

Multivalued Dependencies

• A multivalued dependency, denoted:

α →→ β Means a tuple must exist for every value in β

• Example: class →→ books

Class = { CS 157, CS 46 }

Books = { Manual, Solution }

CID class books 10 CS 46 Manual 10 CS 46 Solution 20 CS 157 Manual 20 CS 157 Solution

Multivalued dependencies result in duplicate data and are considered “tuple-generating dependencies”.

Formal definition – see page 295