Database Management

1
Functional Dependencies(Chapter 14)
2
What are functional dependencies

• A FD is a constraint between two sets of
  attributes from the database..
• A FD, denoted by X Y, between two sets of
  attributes X and Y forms a constraint that
  states
• For any two tuples t1 and t2 such that t1X
  t2X, we much also have t1Y t2Y.
• The values of the Y component of a tuple depend
  on, or are determined by, the values of X
  component.

3
Example

• Employee(SSN, EName)
• Project(ProjectNo, ProjectName, DeptName)
• WorksOn(SSN, ProjectNo, Hours)
• Functional dependencies
• SSN Ename
• ProjectNo ProjectName DeptName
• SSN ProjectNo Hour

4
Additional Notes

• A functional dependency is a property of the
  semantics or meaning of the attributes.
• A functional dependency is a property of the
  relation schema R, not of a particular legal
  relation state r of R.
• FD cannot be inferred from a given relation
  state.
• It must be defined explicitly by someone who
  knows the semantics of the attributes of R.

5
Keys and FDs

• A key is a determinant, while a determinant may
  or may not be a key.
• A key is always unique to the relation, while a
  determinant may or may not be unique to the
  relation.
• The most significant difference between keys and
  FDs is UNIQUENESS and MINIMALITY.
• Several terms
• Superkey
• Candidate Key
• Primary Key

6
Example

• Student(SSN, Name, Phone, City, Street, Zip)
• SSN Phone Name City Street Zip
• SSN Name City Street Zip
• City Street Zip

7
Example
8
Computing the Closure of Attributes

• What is the closure of attributes?
• Given a set of attributes A A1, A2, , An and
  a set of FDs S, the closure of A under the FDs in
  S is the set of attributes B such that every
  relation that satisfies all the FDs in S also
  satisfies A1, A2, , An B.
• B is denoted by A A1, A2, , An
• How to compute a closure?
• Algorithm Determining X, the closure of X under
  F
• X X
• repeat
• oldX X
• for each FD Y Z in F do
• if Y ? X then X X ? Z
• until ( oldX X )

9
Example
Let us consider a relation with attribute A, B,
C, D, E and F. Suppose that this relation has the
functional dependencies AB C, BC AD. D
E, and CF B. 1. What is the closure of A,
B, that is A,B ? 2. How to test whether AB
D follows from these dependencies? How about
D A? 3. What can be the primary key of this
relation?
10
Splitting/Combing Rule

• Splitting rule
• A FD A1 A2 An B1 B2 Bm can be
  replaced by a set of FDs A1 A2 An Bi for i
  1, 2, , m.
• Combining rule
• A set of FDs A1 A2 An Bi for i 1, 2, ,
  m can be replaced by a single FD A1 A2 An
  B1 B2 Bm

11
Trivial-dependency Rule

• A FD A1 A2 An B1 B2 Bm is
• Trivial if the Bs are a subset of the As.
• Nontrivial if at least one of the Bs is not
  among the As.
• Completely nontrivial if none of the Bs is also
  one of the As.
• Trivial-dependency rule
• The FD A1 A2 An B1 B2 Bm is
  equivalent to A1 A2 An C1 C2 Ck, where
  the Cs are all those Bs that are not also As

12
The Transition Rule

• If two FDs A1, A2, , An B1, B2, , Bm and B1,
  B2, , Bm C1, C2, , Ck hold in relation R,
  then A1, A2, , An C1, C2, , Ck also holds in
  R.
• This rule can be proved by computing the closure
  of A1, A2, , An under A1, A2, , An B1,
  B2, , Bm B1, B2, , Bm C1, C2, , Ck .

13
A complete Set of Inference Rules

• Armstrongs axioms (1974)
• Reflexivity
• if X ? Y then X Y
• Augmentation
• X Y XZ YZ
• Transitivity
• X Y, Y Z X Z

14
Other rules

• Decomposition ( or projective) rule
• X YZ X Y
• Union ( or additive) rule
• X Y, X Z X YZ
• Pseudotransitive rule
• X Y, WY Z WX Z

15
Proof

• 1. X---gtYZ (given)
• YZ ---gt Y ( using Reflexivity)
• X ---gt Y ( applying transitivity to the
  above two FDs)
• 2. X ---gt Y (given)
• X----gt Z (given)
• X ----gt XY (applying augmentation to rule 1)
• XY --gt YZ (applying augmentation to rule 2)
• X ---gt YZ ( applying transitivity to the above
  two rules)
• 3. X ---gt Y (given)
• WY ---gt Z (given)
• WX ---gt WY ( using augmentation to rule 1)
• WX ---gt Z (applying transitivity to rule 2
  and 3)

16
Equivalence of Sets Dependencies

• Given two sets of dependencies E and F, the
  closures of these two sets are E and F
  respectively, We say
• E is covered by F, if E ? F.
• E and F are equivalent if E F.
• How to determine the equivalence?
• calculating X under F for each FD X Y in E,
  and then checking whether this X includes the
  attributes in Y. (This is to determine whether E
  is covered by F)
• determining whether F is covered by E by the same
  approach above.

17
Example

• E A B, B A, B C, C A
• F A B, B C, C A

18
Minimal Sets of FDs

• A set of FDs F is minimal if it satisfies the
  following condition
• Every FD in F has a single attribute for its
  right-hand side.
• We cannot replace any FD X A with a FD Y A,
  where Y is a proper subset of X, and still have a
  set of dependencies that is equivalent to F.
• We cannot remove any dependency from F and still
  have a set of FDs that is equivalent to F.
• A minimal basis of F is denoted by Fmin, and
  there may be several minimal bases for one
  particular F.