Download
1 / 13
130 likes | 268 Views
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.
E N D
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
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
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
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
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
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
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
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
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
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.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
The End. Advanced Database System
