数据库管理系统概述英文版课件：tutorial5 Functional Dependencies.pptVIP

HKUST * Database Management Systems The Hong Kong University of Science and Technology COMP231 Tutorial 5 Functional Dependencies, 3NF Superkey K ? R Candidate Key K ? R no K’ ? K, s.t. K’ ? R (minimal) Primary Key The candidate key chosen to uniquely identify tuples in a relation superkey candidate key primary key Review: Key For a set of functional dependencies F, we can get the closure, F+, by applying Armstrong’s Axioms. Armstrong’s Axioms: Reflexivity If X ? Y, then X ? Y Augmentation If X ?Y, then XZ ? YZ Transitivity If X ? Y, Y ? Z, then X ? Z Derived rules: Decomposition If X ? YZ, then X ? Y and X ? Z Union If X ? Y and X ? Z, then X ? YZ Pseudo-transitivity If X ? Y and WY ? Z, then WX ? Z Review: The Closure of FD If XY?Z then X?Z and Y?Z Definition: X, Y are attributes of a relation R: X ? Y is in F+ ? Y ? X+ Example: Given R = (loan_no, amount, branch_name, customer_name) If loan_no ? amount then loan_no+ = {loan_no, amount} If we also have loan_no ? branch_name then loan_no+ = {loan_no, amount, branch_name} If we also have loan_no ? customer_name then loan_no+ = {loan_no, amount, branch_name, customer_name} Algorithm: X(0) := X Repeat X(i+1) := X(i) ? Z, where Z is the set of attributes such that there exists Y?Z in F, and Y ? X(i) Until X(i+1) := X(i) Return X(i+1) Review: The Closure of Attributes Review: Canonical Cover of FD Definition: A canonical cover for F is a set of dependencies Fc such that F and Fc are equivalent Fc contains no redundancy Each left hand side of functional dependency in Fc is unique Review: Normalization Decomposition of a relation R with the following goals Lossless (necessary) Information lost? Dependency preservation (desirable) (?i Fi)+ = F+ ? Good form 1NF, 2NF, 3NF, BCNF 2NF: R is in 2NF if and only if for each FD: X ? {A} in F+ Then A ? X (the FD is trivial), OR X is not a proper subset of a candidate key for R, OR A is a prime attribute 3NF: R is in 3NF if and only if for each FD: X ? {A}