CPS 430/542 Lecture notes: Functional Dependences and Rules of FD's


Coverage: [FCDB] §§3.4-3.5 (pp. 82-102)

Keys of relations

  • a set of one or more attributes of a relation forms a key for that relation if they functionally determine every other attribute of the relation
  • key must be minimal, i.e., no proper subset is a key
  • remember there may be more than one key
  • underline all of the attributes of one key, called the primary key
  • difference between
    • minimal, where no attribute can be removed, and
    • minimum, which is the smallest possible
  • E/R model does not require minimal keys
  • superkeys (superset of a key)
    • every key is a superkey
    • not every superkey is a key

Functional dependencies (FD's)

  • concept related to keys; a generalization of the idea of keys
  • a constraint: on schema or instance?
  • critical to reducing redundancy
  • key ingredient in the relational database design theory
  • functional dependency: if two tuples agree on a set of attributes, then they must agree on the other attribute, e.g., A → B
    • says if two tuples agree on attribute A, they must agree on attribute B
    • A and B can also be sets
      A1 A2 ... An → B1
      A1 A2 ... An → B2
      ...
      A1 A2 ... An → Bm

      A1 A2 ... An → B1, B2 ... Bm

[FCDB] example 3.13 on p. 85

  • Movies(title, year, length, filmType, studioName, starName)
  • FD's: title year → length filmType studioName
  • is {title, year, starName} a key?
    • must show they functionally determine all other attributes
    • must show that no proper subset is a key

Combinations

How to determine keys

  • keyness
  • many-one
  • English

Determining keys for relations

  • after a conversion from E/R
  • key for relation derived from entity sets (easy)
  • key for relation derived from relationship
    • depends on the relationship type
    • remember problem from exam
  • under what conditions will the key for a relation derived from a many-many relationship always contain all attributes from both participating entity sets?
  • multiway relationships, what is guaranteed?

Rules of FD's (Armstrong's axioms)

  • splitting/combining
      A1 A2 ... An → B1 B2 ... Bm =
      A1 A2 ... An → B1
      A1 A2 ... An → B2
      A1 A2 ... An → B3
      ...
      A1, A2 ... An → Bm

      can only split/combine rhs, e.g., title year → length ≠ title → length and year → length
  • reflexivity: if B is a subset of A, then A → B
    • these are called trivial FD's
    • an FD is nontrivial if at least one of the attributes on the rhs does not appear on the lhs (assume A, B, C are disjoint set of attributes), e.g., A C → B C
    • an FD is completely nontrivial if none of the attributes on the rhs appear on the lhs, e.g., A → B
  • augmentation: if A → B, then A C → B C
  • transitivity: if A → B and B → C, then A → C
  • these rules are called Armstrong's axioms; see box on [FCDB] p. 99

References

    [FCDB] J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.
Return Home