Chapter 11 Functional Dependencies - PowerPoint PPT Presentation

1 / 24
About This Presentation
Title:

Chapter 11 Functional Dependencies

Description:

Trivial and Nontrivial Dependencies. Closure of a Set of Dependencies ... Transitivity:If A B and B C, then A C. More inference rules. Self-determination: A A ... – PowerPoint PPT presentation

Number of Views:324
Avg rating:3.0/5.0
Slides: 25
Provided by: Richar502
Category:

less

Transcript and Presenter's Notes

Title: 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
Write a Comment
User Comments (0)
About PowerShow.com