Lecture 17 4th Normal Form.ppt

Lecture 17 4th Normal Form Prof. Sin-Min Lee Department of Computer Science Functional Dependencies Dependencies for this relation: A ? B A ? D BC ? EF Do they all hold in this instance of the relation R? Armstrong’s Axioms Armstrong’s Axioms: Let X, Y be sets of attributes from a relation T. [1] Inclusion rule: If Y ? X, then X ? Y. [2] Transitivity rule: If X ? Y, and Y ? Z, then X ? Z. [3] Augmentation rule: If X ? Y, then XZ ? YZ. Other derived rules: [1] Union rule: If X ? Y and X ? Z, then X ? YZ [2] Decomposition rule: If X ? YZ, then X ? Y and X ? Z [3] Pseudotransitivity: If X ? Y and WY ? Z, then XW ? Z [4] Accumulation rule: If X ? YZ and Z ? BW, then X ? YZB Closure Let F be a set of functional dependencies. We say F implies a functional dependency g if g can be derived from the FDs in F using the axioms. The closure of F, written as F+, is the set of all FDs that are implied by F. Example: Given FDs { A ? BC, C ? D, AD ? B }, what is the closure? The closure is a potentially exponential set: Trivial dependencies: {A ?A, B ?B,…,ABC ?ABC,…}, other dependencies obtained by augmentation {AB ?ABC, BC ?BD,…}, dependencies obtained by other rules (or multiple rules), {A ? BC, C ? D, AD ? B, A ? D } Closure Given a set F of functional dependencies. A functional dependency X ? Y is said to be entailed (implied) by F, if X ? Y is in F+. To sets of functional dependencies F1, F2 are said to be equivalent, if their closures are equivalent, I.e. F1+ = F2+. Checking Entailment To find whether a functional dependency X ? Y is implied by a set of functional dependencies F We can apply all the rules in Armstrong’s Axioms to find whether we can obtain this dependency from the dependencies in F OR We can determine the closure of attributes X, denoted by X+ with respect to F, and check whether Y ? X+ Closure of a set of attributes Given a set of F of functional dependencies, the closure of a set of attributes X, denoted by X+ i