Functional Dependencies Part 3

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

• 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

• Armstrongs 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