1 / 13

11.1 Introduction

11.1 Introduction. A functional dependency (FD) is basically a many-to-one relationship from one set of attributes to another within a given relvar. 11.2 Basic Definitions.

ting
Télécharger la présentation

11.1 Introduction

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 11.1 Introduction • A functional dependency (FD) is basically a many-to-one relationship from one set of attributes to another within a given relvar. Advanced Database System

  2. 11.2 Basic Definitions • To distinguish between (a) the value of a given relvar at a given point in time and (b) the set of all possible values that the given relvar might assume at different times Case (a): • Let r be a relation, and let X and Y be arbitrary subsets of the set of attributes of r. Y is functionally dependent on X, X →Y, if and only if each X value in r has associated with it precisely one Y value in r. • Sample value (See Fig. 11.1) {S#}→{City} {S#,P#}→{S#,P#,City,Qty} {S#}→{Qty} {Qty}→{S#} Advanced Database System

  3. 11.2 Basic Definitions (Cont.) Case (b): • Let R be a relation variable, and let X and Y be arbitrary subsets of the set of attributes of R. Y is functionally dependent on X, X →Y, if and only if in every possible legal value of R, each X value has associated with it precisely one Y value. {S#}→{Qty} and {Qty}→{S#} do not hold “for all time” • If relvar R satisfies the FD A →B and A is not a candidate key, then R will necessarily involve some redundancy. • To find some way of reducing the set of FDs to a manageable size. • The reason is that FDs represent certain integrity constraints, and we would thus like the DBMS to enforce them. Advanced Database System

  4. 11.3 Trivial and Nontrivial Dependencies • An FD is trivial if and only if the right-hand side is a subset of the left-hand side. {S#,P#}→{S#} • The trivial dependencies can be eliminated. Advanced Database System

  5. 11.4 Closure of a Set of Dependencies • The set of all FDs that are implied by a given set S of FDs is called the closure of S, written S+ . • Armstrong‘s axioms: 1. Reflexivity: If B is a subset of A, A →B. 2. Augmentation: If A →B, then AC →BC. 3. Transitivity: If A →B and B →C, then A →C. • The rules are complete and sound. 4. Self-determination: A →A. 5. Decomposition: If A →BC, then A →B and A →C. 6. Union: If A →B and A →C, then A →BC. 7. Composition: If A →B and C →D, then AC →BD. 8. General Unification Theorem: If A →B and C →D, then A ∪(C-B)→BD. Advanced Database System

  6. 11.4 Closure of a Set of Dependencies (Cont.) • Example: R:{A,B,C,D,E,F} and the FDs A →BC B →E CD →EF AD →F is a member of the closure of the given set. 1. A →BC (given) 2. A →C (1, decomposition) 3. AD →CD (2, augmentation) 4. CD →EF (given) 5. AD →EF (3 and 4, transitivity) 6. AD →F (5, decomposition) Advanced Database System

  7. 11.5 Closure of a Set of Attributes • Given a relvar R, a set Z of attributes of R, and a set S of FDs that hold for R, we can determine the set of attributes of R that are functionally dependent on Z-the closure Z+ of Z under S. • A simple algorithm for computing the closure Z+ (See Fig. 11.2) Advanced Database System

  8. 11.5 Closure of a Set of Attributes (Cont.) • Example: R:{A,B,C,D,E,F} and the FDs A →BC E →CF B →E CD →EF Computing the closure {A,B} + ={A,B,C,E,F} • Given a set S of FDs, we can easily tell whether a specific FD X →Y follows from S, because that FD will follow if and only if Y is a subset of the closure X+ of X under S. • A superkey for a relvar R is a set of attributes of R that includes some candidate key of R as a subset. • K is a superkey if and only if the closure K+ of K under the given set of FDs is precisely the set of all attributes of R. • K is a candidate key if and only if it is an irreducible superkey. Advanced Database System

  9. 11.6 Irreducible Sets of Dependencies (1/3) • If every FD implied by S1 is implied by S2, S2 is a cover for S1. • Equivalence • A set S of FDs to be irreducible if and only if it satisfies the following three properties: 1. The right side of every FD in S involves just one attribute. 2. The left side of every FD in S is irreducible, meaning that no attribute can be discarded from the determinant without changing the closure S+ . 3. No FD in S can be discarded from S without changing the closure S+ . Advanced Database System

  10. 11.6 Irreducible Sets of Dependencies (2/3) • Example: 1. P#→{Pname,Color} P#→Weight P#→City 2. {P#,Pname}→Color P#→Pname P#→Weight P#→City 3. P#→P# P#→Pname P#→Color P#→Weight P#→City Advanced Database System

  11. 11.6 Irreducible Sets of Dependencies (3/3) • For every set of FDs, there exists at least one equivalent set that is irreducible. • Example: R:{A,B,C,D} and the FDs A →BC B →C A →B A →B  B →C AB →C A →D AC →D • A given set of FDs does not necessarily have a unique irreducible equivalent. Advanced Database System

  12. The End. Advanced Database System

More Related