Functional Dependencies

1
Functional Dependencies

• Alternative Data Modeling Approach
• Based on Formal Logic
• ER Diagrams can be mapped into FDs
• (sans some cardinality information)
• Algorithms to automatically generate 3rd Normal
  form.
• FDs (alone with MVDs and JDs) are used to
  formally define the various relational normal
  forms (e.g., 3rd normal form).

2
Functional Dependencies

• Attribute(s) B are said to be functionally
  dependent on attribute(s) A iff (if and only if)
  for all valid instance(s) of A, those values of A
  uniquely determine the value(s) for B.
• P ? Color P,S ? Qty P ?gt PN

3
Employee(EmpID,Name,Dept,Salary,Course,Date
Completed)

• FDs
• EMPID ? Name,Dept,Salary
• Course ? date completed
• Note A key is a set of non-redundant attributes
  that functionally determines all the attributes
  in the relation schema.
• empid,course ? name,dept,salary,date completed

4
Functional Dependency Rules

• Augmentation if x? Y, then ZX? Y
• Student ? StudentName then Student course
  ? student name
• Transitivityif X ? Y Y ? Z then X? Z
• Student ? major and major ? advisor
  then student ? advisor
• Pseudo Transitivity if X? Y YZ ? W then XZ ?
  W Thus if student ? major and major, class ?
  advisor then student ,Class ? advisor

5
If X,Y,Z, and W are attributes

• X ? X (reflexive)
• If X ? y then XZ ? Y (augmentation)
• If X ? Y X ? Z then X ? YZ (union)
• If X ? Y then X ? Z where Z subset of Y
  (Decomposition)
• IF X ? Y Y ? Z then X ? Z (transitivity)
  
• IF X ? Y YZ ? W, then XZ ? W (pseudotrans
  itivity)

6
Suppose relation (A B C D) with A ? BC, B ? D,
and DB ? A

• Are These Valid Derivations?
• A ? B A ? D A ? BD
• A ? A A ? C B ? A
• Is this a Minimal equivalent Set?
• B ? A
• A ? CD