Chapter 11 Functional Dependencies

1
Chapter 11 Functional Dependencies
2
Outline

• Basic Definitions
• Trivial and Nontrivial Dependencies
• Closure of a Set of Dependencies
• Closure of a Set of Attributes
• Irreducible Sets of Dependencies

3
Basic Definitions
4
Informal Definition

• Functional dependence is a many to one
  relationship from one set of attributes to
  another within a given relvar
• Example S, P ? QTY in relvar SP
• For any given value for the pair of attributes S
  and P, there is just one corresponding value of
  attribute QTY
• However, any number of distinct values of the
  pair of attributes S and P can have the same
  corresponding value for attribute QTY

5
Sample Value for relvar SCP
6
Definition (Case a)

• Let r be a relation, and let X and Y be subsets
  of the attributes of r. Then we say that Y is
  functionally dependent (FD) on X, or X
  functionally determines Y in symbols X ? Y
  if and only if each X value in r has associated
  with it precisely one Y value in r. In other
  words, whenever two tuples of r agree on their X
  value, they also agree on their Y value
• X is the determinant Y the dependent
• Focus on the value of a given relvar at a given
  point in time

7
FD in SCP (Case a)

• S ? CITY
• S, P ? QTY
• S, P ? CITY
• S, P ? CITY, QTY
• S, P ? S // S, P ? S
• S, P ? P
• S, P ? S, P, CITY, QTY
• S ? QTY
• QTY ? S

8
Definition (Case b)

• Let R be a relation variable, and let X and Y be
  subsets of the attributes of r. Then we say that
  Y is functionally dependent (FD) on X, or X
  functionally determines Y in symbols X ? Y
  if and only if, in every possible legal value of
  R, each X value in r has associated with it
  precisely one Y value in r. In other words, in
  every possible legal value of R, whenever two
  tuples of r agree on their X value, they also
  agree on their Y value
• Focus on the set of all possible values that the
  given relvar might assume at different times

9
FD in SCP (Case a)

• S ? CITY
• S, P ? QTY
• S, P ? CITY
• S, P ? CITY, QTY
• S, P ? S
• S, P ? P
• S, P ? S, P, CITY, QTY
• S ? QTY
• QTY ? S

10
Notes (Case b)

• An FD holds for all time is an integrity
  constraint
• Example S ? CITY
• CONSTRAINT S_CITY_FD FORALL SCPX FORALL SCPY
  ( IF SCPX.S SCPY.S THEN SCPX.CITY
  SCPY.CITY END IF)
• If X is a candidate key for relvar R, then all
  attributes Y of relvar R must be functionally
  dependent on X
• P ? P, PNAME, COLOR, WEIGHT, CITY
• If relvar R satisfies the FD A?B, and A is not a
  candidate key, then R will necessarily involve
  some redundancy
• Means that A, B will appear many times in that
  relvar
• Example S ? CITY

11
Notes (Case b) (Cont.)

• The complete set of FDs for a given relvar can be
  very large
• What we would like is to find some way of
  reducing that set to a manageable size
• Given a particular set S of FDs, it is desirable
  to find some other set T that is (ideally) much
  smaller than S and has the property that every FD
  in S is implied by the FDs in T
• If such a set T can be found, it is sufficient
  that the DBMS enforce just the FDs in T, and the
  FDs in S will then be enforced automatically

12
Trivial and Nontrivial Dependencies

• An FD is trivial if and only if the right side is
  a subset of the left side
• S, P ? S
• All other dependencies are called nontrivial

13
Closure of a Set of Dependencies

• Some FDs might imply others
• Example S, P ? CITY, QTY implies the
  following two FDs
• S, P ? CITY
• S, P ? QTY
• The set of all FDs that are implied by a given
  set S of FDs is called the closure of S, denoted
  by S
• Armstrongs axioms (see next slide) are used to
  infer FDs from others
• Let A, B, and C be subsets of relvar R, and let
  AB signify the union of A and B

14
Armstrongs Axioms

• Reflexivity If B is a subset of A the A ? B
• Augmentation If A ? B, then AC ? BC
• TransitivityIf A ? B and B ? C, then A ? C
• More inference rules
• Self-determination A ? A
• Decomposition If A ? BC, then A ? B and A ? C
• Union If A ? B and A ? C, then A ? BC
• Composition If A ? B and C? D, then AC ? BD
• General Unification Theorem If A ? B and C? D,
  then A?(C-B) ? BD

15
Closure of a Set of Attributes

• In principle, we can compute the closure S of a
  given set S of FDs by means of an algorithm that
  says Repeatedly apply the rules from the
  previous section until they stop producing new
  FDs
• In practice, there is little need to compute the
  closure, and the algorithm just mentioned is
  hardly very efficient
• Here we show how to compute a certain subset of
  the closure namely, that subset consisting of
  all FDs with a certain (specified) set Z of
  attributes as the left side

16
Computing the Closure Z of Z under S

• Given a relvar R, a set Z of attributes of R, and
  a set S of FDs that hold for R, the set of all
  attributes of R that are functionally dependent
  on Z the closure Z of Z under S can be
  computed as

17
Computing the Closure Z of Z under S Example

• Given a relvar with attributes A, B, C, D, E, F
  and FDs
• A?BC E?CF B?E CD?EF
• We can compute the closure A, B under S as
  follows
• Initialize CLOSUREA, S to A, B
• A?BC ? CLOSUREA, S ? A, B, C
• E?CF ? NO CHANGE
• B ? E ? CLOSUREA, S ? A, B, C, E
• CD ? EF ? NO CHANGE
• A? BC ? NO CHANGE
• E ? CF ? CLOSUREA, S ? A, B, C, E, F
• B ? E CD ? EF ? NO CHANGE
• Iterative for the four FDs again ? NO CHANGE

CLOSUREA, S ? A, B, C, E, F
18
Closure of a Set of Attributes (Cont.)

• If Z is a set of attributes of relvar R and S is
  a set of FDs that hold for R, then the set of FDs
  hold for R with Z as the left side is the set
  consisting of all FDs of the form Z?Z, where Z
  us some subset of the closure Z of Z under S
• The closure S of the original set of FDs is the
  union of all such sets of FDs, taken over all
  possible attribute sets Z
• Given a set S of FDs, a specific FD X?Y follows
  from S if and only if Y is a subset of the
  closure X of X under S

19
Closure of a Set of Attributes

• A superkey for a relvar R is a set of attributes
  of R that includes some candidate key of R as a
  subset
• The superkeys for a given relvar R are precisely
  those subsets K of the attributes of R such that
  the FD K?A holds true for every attribute A of R
• A superkey implies all the other attributes of a
  relvar
• The nonkey attributes of a relvar represent a
  closure of the superkey, but not necessarily an
  irreducible one
• Any group of attributes for which all the other
  attributes represent a closure is a superkey

20
Irreducible Sets of Dependencies

• Let S1 and S2 be two sets of FDs. If every FD
  implied by S1 is implied by S2, then S2 is a
  cover of S1
• If S2 is a cover for S1 and S1 is a cover for S2,
  then S1 and S2 are equivalent
• A set S of FDs is irreducible iff
• The right side of every FD in S involves one
  attribute (a singleton set)
• The left side is irreducible (no attribute can be
  discarded from the determinant without changing
  the closure S)
• No FD in S can be discarded from S without
  changing the closure of S
• For every set of FDs there exists at least one
  equivalent set that is irreducible

21
Irreducible Sets of Dependencies Example (P)

• Irreducible set of FDs
• P ? PNAME
• P ? COLOR
• P ? WEIGHT
• P ? CITY
• Not irreducible set of FDs
• P ? PNAME, COLOR
• P ? WEIGHT
• P ? CITY

• Not irreducible set of FDs
• P, PNAME ? COLOR
• P ? PNAME
• P ? WEIGHT
• P ? CITY
• Not irreducible set of FDs
• P ? P
• P ? PNAME
• P ? COLOR
• P ? WEIGHT
• P ? CITY

22
Irreducible Sets of Dependencies (Cont.)

• For every set S of FDs there exists at least one
  equivalent set that is irreducible
• Step 1 Thanks to the decomposition rule, we can
  assume without loss of generality that every FD
  in S has a singleton right side
• Step 2 For each FD f in S we examine each
  attribute A in the left side of f if deleting A
  from the left side of f has no effect on S, we
  delete A from the left side of f
• Step 3 For each FD f remaining in S, if deleting
  f from S has no effect on S, we delete f from S
• The final set S is irreducible and is equivalent
  to the original set S

23
Irreducible Sets of Dependencies (Cont.)

• Given a relvar R with attributes A, B, C, D, and
  FDs
• A ? BC
• B ? C
• A ? B
• AB ? C
• AC ?D
• Step 1
• A ? B, A?C
• B ? C
• A ? B
• AB ? C
• AC ?D

• Step 2 AC?D ? A?D
• A?C ? A ?AC (Augmentation)
• A ? AC, AC?D ? A ? D
• Step 3 eliminate AB ? C
• A?C ? AB ? CB ? AB ? C
• Step 4 A ?C is implied by A?B and B?C
• The final irreducible set
• A ? B
• B ? C
• A ? D

24
Irreducible Sets of Dependencies (Cont.)

• A set I of FDs that is irreducible and equivalent
  to some other set S of FDs is said to be an
  irreducible equivalent of S
• Given some particular set S of FDs that need to
  be enforced, it is sufficient for the system to
  find and enforce the FDs in an irreducible
  equivalent I instead
• A given set of FDs does not necessarily have a
  unique irreducible equivalent