CPS 430/542 Lecture notes: Functional
Dependences and Rules of FD's
Coverage: [FCDB] §§3.43.5 (pp. 82102)
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
A_{1} A_{2} ... A_{n} → B_{1}
A_{1} A_{2} ... A_{n} → B_{2}
...
A_{1} A_{2} ... A_{n} → B_{m}
A_{1} A_{2} ... A_{n} → B_{1},
B_{2} ... B_{m}
[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
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 manymany relationship
always contain all attributes from both participating
entity sets?
 multiway relationships, what is guaranteed?
Rules of FD's (Armstrong's axioms)
 splitting/combining
A_{1} A_{2} ... A_{n} →
B_{1} B_{2} ... B_{m} =
A_{1} A_{2} ... A_{n} → B_{1}
A_{1} A_{2} ... A_{n} → B_{2}
A_{1} A_{2} ... A_{n} → B_{3}
...
A_{1}, A_{2} ... A_{n} → B_{m}
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.

