Chapter 11

1. Chapter 11 Functional Dependencies

2. Topics in this Chapter • Basic Definitions • Trivial and Nontrivial Dependencies • Closure of a Set of Dependencies • Closure of a Set of Attributes • Irreducible Sets of Dependencies

3. Functional Dependence • Functional dependence is a many to one relationship from one set of attributes to another within a given relvar • Let r be a relation, and let X and Y be subsets of the attributes of r • X  Y says • “Y is functionally dependent on X”, or • “X functionally determines Y” • X is the determinant; Y the dependent

4. Functional Dependence X Y means: attribute X (of relation R) functionally determines attribute Y or: attribute Y is functionally dependent on attribute X iff: each X value has associated with it one Y value (at any one time) i.e., a given X value must always occur with the same Y value

5. Functional Dependence So, in the relation S: S.S# S.CITY means that (at any given time) a particular supplier number is associated with only one city in any tuple with S# and CITY, if S# is S1, then CITY must be London

6. Functional Dependence In the relation S: S.S# S.SNAME S.S# S.STATUS S.S# S.CITY and: S.S# S.S# but: S.STATUS S.CITY

7. Functional Dependence X Y the determinant the dependent the determinant and dependent can actually be sets of attributes: {X, A} {Y, B}

8. The table “SCP” +------+--------+------+------+ | snum | city | pnum | qty | +------+--------+------+------+ | S1 | London | P1 | 300 | | S1 | London | P2 | 200 | | S2 | Paris | P1 | 300 | | S2 | Paris | P2 | 400 | | S3 | Paris | P2 | 200 | | S4 | London | P2 | 200 | | S4 | London | P4 | 300 | | S4 | London | P5 | 400 | +------+--------+------+------+

9. {S#, P#} {QTY} {S#, P#} {CITY} {S#, P#} {CITY, QTY} {S#, P#} {S#} {S#, P#} {S#, P#, CITY, QTY} {S#} {QTY} {S#, P#} {QTY}

10. Functional Dependence X Y Whenever two tuples agree in their “X” value(s) they must also agree in their “Y” value(s). Thus, it represents an integrity constraint.

11. Trivial and Nontrivial Dependencies • An FD is trivial if and only if the right side is a subset of the left side • In SCP, { S#, P# }  S# trivial • In S, S#  S# trivial • All other dependencies are called nontrivial

12. 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 • Armstrong’s 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

13. Closure of a Set of Dependencies –Armstrong’s Axioms • Reflexivity: If B is a subset of A then A  B • Augmentation: If A  B, then AC  BC • Transitivity:If A  B and B  C, then A  C • 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

14. Closure Example Given: A BC B E CD EF Then: A BC Given A C Decomposition AD CD Augmentation CD EF Given AD EF Transitivity AD F Decomposition all members of the closure

15. Closure of a Set of Attributes • The set of all attributes that are implied by a given set of attributes S is called the closure of S • 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

16. 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 • 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 FD in S can be discarded without changing the closure of S