1 / 16

Chapter 11

Chapter 11. Functional Dependencies. 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. Functional Dependence.

dima
Télécharger la présentation

Chapter 11

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. 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

More Related