Functional Dependencies (Part 3)

1. All page numbers are in reference to Database System Concepts (5th Edition) Functional Dependencies(Part 3) Presented by Nash Raghavan

2. 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

3. Quick Review • What is a functional dependency? • SSN → Name “Name is functionally dependent on SSN” • Given a relation R, if   , then  must be the same whenever  is the same for all tuples in R • First Normal Form • Domains of all attributes in relation R are atomic • Page 269 • Boyce-Codd Normal Form • For all functional dependencies   ,  is a superkey • Page 272

4. 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

5. 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

6. The Closure of a Set • Why do we care about the axioms? • Given a set of functional dependencies denoted by F • { A  B, A  C, CG  H, CG  I, B  H } • We can now determine logically implied functional dependencies such as A  H • The set of all functional dependencies implied by F is denoted by F+and is called the closure of F • See page 279 for more details

7. 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

8. 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 )

9. Decomposition • branch relation is now in BCNF but loan_info is not because loan_number is not a superkey given the following dependency: loan_number amount branch_name • Solution is to iteratively decompose relations until all relations are in desired form. • To complete solution, decompose loan_info to: loanb = ( loan_number, branch_name, amount ) borrower = ( customer_name, loan_number )

10. 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!

11. 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 } Multivalued dependencies result in duplicate data and are considered “tuple-generating dependencies”. Formal definition – see page 295

12. The End • This information will become relevant … eventually. • Makes more sense when we study: • Database design/creation • Normalization • BCNF, 1NF, 2NF, 3NF, 4NF • Decomposition • Denormalization