Chapter 11 Functional Dependencies Prof YinFu Huang CSIE

Chapter 11 Functional Dependencies Prof. Yin-Fu Huang CSIE, NYUST

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

11. 2 Basic Definitions n 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}→{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” n If relvar R satisfies the FD A →B and A is not a candidate key, then R will necessarily involve some redundancy. n To find some way of reducing the set of FDs to a manageable size. n 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 n An FD is trivial if and only if the right-hand side is a subset of the left-hand side. {S#, P#}→{S#} n The trivial dependencies can be eliminated. Advanced Database System 5

11. 4 Closure of a Set of Dependencies n The set of all FDs that are implied by a given set S of FDs is called the closure of S, written S+. n 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. n 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, Advanced Databasethen System A ∪(C-B)→BD. 6

11. 4 Closure of a Set of Dependencies (Cont. ) n 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 n 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. n 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. ) n Example: R: {A, B, C, D, E, F} and the FDs A →BC E →CF B →E CD →EF n n 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 Advanced if and only if it. System is an irreducible superkey. 9 Database

11. 6 Irreducible Sets of Dependencies (1/3) n If every FD implied by S 1 is implied by S 2, S 2 is a cover for S 1. n Equivalence n 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) n 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) n For every set of FDs, there exists at least one equivalent set that is irreducible. n Example: R: {A, B, C, D} and the FDs A →BC B →C A →B AB →C A →B B →C A →D AC →D n A given set of FDs does not necessarily have a unique irreducible equivalent. Advanced Database System 12

The End. Advanced Database System 13