Functional Dependencies

• Let R=(A,B, ... Z) be a relation
• A Functional Dependency is a relation between attributes.
  • Let A and B be attributes of R.
  • B is functionally dependent on A (A → B) if each value of A is associated with exactly one value of B.
  • A is the determinant of B.
  • Both A and B may be compound.
• If we know that B is dependent on A then
  • Each value of A maps to a single value of B.
  • So if two Tuples have the same value of A, they also have the same value of B.
  • However, if two Tuples have the same value of B, they may have different values of A.
• Consider Buyer and Department in the SKU_DATA
  • In this case, the relationship between Buyer and Department is one to one.
  • But the relationship between Department and Buyer is one to many.
  • 
• "When identifying functional dependencies between attributes in a relation, it is important to distinguish clearly between the values held by an attribute at a given point in time and the set of all possible values that an attribute may hold at different times. "
• In other words, we need to consider what values the data may assume, not the current values.
• For example:
  • My position at EUP has a position number.
  • It may appear that PositionNumber → FacultyName
  • But this is not true, when I no longer work here, they will hire someone else into that position (assuming Computer Science exists at that point)
• We are generally not concerned with trivial dependencies.
  • (Buyer, Department) → Department
  • (Buyer, Department) → Buyer
• We are interested in nontrivial functional dependencies because they represent integrity constraints for the relation.
• To use functional dependencies to normalize tables they must:
  • Be a one to one relationship between the left hand side attribute and the right hand side attribute.
  • Be nontrivial
  • Hold for all time
• A particular database may contain many dependencies described above.
• We will need to limit these to the "important" ones
• There is some Theory that helps.
  • Dependencies are Transitive: If A → B and B → C , then A → C
  • Closed under Augmentation: If A → B then (A,C) → (B,C)
  • Self Deterministic: A → A
  • Closed under Decomposition: A → (B,C) then A → B and A → C
  • Closed under Union: If A → B and A→ C, then A → (B,C)
  • Closed Under Composition: If A → B and C → D then (A,C) → (B,D)
• We will most likely not be using these algebraic properties this semester, but I wanted to throw them in for completeness and to demonstrate that a body of theory exists for this task.
• These rules are used to reduce the total number of functional dependencies to a usable set for normalization.
• Connolly notes "Normally a database designer starts by specifying functional decencies that are semantically obvious; however, there are usually numerous other functional dependencies. In fact, the task of specifying all the possible functional dependencies for 'real' database projects is more often than not, impractical."