slides01-1 - Background: Functional Dependencies We are...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Background: Functional Dependencies We are always talking about a relation R, with a xed schema set of attributes and a varying instance set of tuples. Conventions: A; B; : : : are attributes; : : :; Y; Z are sets of attributes. Concatenation means union. FD is X ! Y , where X and Y are sets of attributes. Two tuples that agree in all attributes of X must agree in all attributes of Y . Implication: FD X ! Y follows from F i all relation instances that satisfy F also satisfy X !Y. Three Ways to Reason About FD's 1. Semantic : FD X ! Y is the set of relation instances that satisfy it. 3 Say F j= X ! Y if every instance that satis es all FD's in F also satisfy X ! Y . 3 All approaches assume there is a xed relation scheme R to which the FD's pertain. 2. Algorithmic : Give an algorithm that tells us, given F and X ! Y , whether F j= X ! Y . 3. Logical : Give a reasoning system that lets us deduce an FD like X ! Y exactly when F j= X !Y. 3 Deduction indicated by F ` X ! Y . Closure Test for Implication Algorithmic Approach Start with X + = X . Adjoin V to X + if U ! V is in F , and U X + . At end, test if Y X + . Proof that if Y X + , then F j= X ! Y : Easy induction on the number of additions to X + that X ! A for all A in X + . Proof that if F implies X ! Y , then Y X + : Prove the contrapositive; assume Y is not a subset of X + and prove F does not imply X ! Y . Construct relation R with two tuples that agree on X + and disagree elsewhere. 1 Armstrong's Axioms Logical Approach A sound what may be deduced is correct in the j= sense and complete what is true in the j= sense can be deduced axiomatization of FD's. A1: Trivial FD's or Re exivity. X ! Y always holds if Y X . A2: Augmentation. If X ! Y , then XZ ! Y Z for any set of attributes Z . A3: Transitivity. If X ! Y and Y ! Z , then X ! Z. Deductive Proofs A series of lines." Each line is either: 1. A given statement FD in the given set F for deductions about FD's, or 2. A statement that follows from previous lines by applying an axiom. Example Given fAB ! C; CD ! E g, deduce ABD ! E . AB ! C Given ABD ! CD A2 CD ! E Given ABD ! E A3 Proof of Soundness Easy observations about relations. Proof of Completeness 1. Given F , show that if A is in X + , then X ! A follows from F by AA's. 2. Show that if X ! A1 ; : : :; X ! An follow from AA's, then so does X ! A1    An . 3. Complete the proof by observing that X ! A1    An follows from F i all of A1 ; : : :; An are in X + , and therefore i X ! A1    An follows by AA's. Proof 2 Induction on i that X ! A1    Ai follows. Basis: i = 1, given. 2 Induction: Assume X ! A1    Ai,1 . 3 X ! XA1    Ai,1 A2 by X applied to X ! A1    Ai,1 . 3 XA1    Ai,1 ! Ai A2 by A1    Ai,1 applied to X ! Ai . 3 X ! A1    Ai A3 applied to previous two FD's. Proof 1 Induction on the number of steps used to add A to X+. Basis: 0 steps. Then A is in X , and X ! A by A1. Induction: Assume B1    Bk ! A in F used to add A to X + . 3 By the inductive hypothesis, X ! Bj follows from F for all 1  j  k. 3 By part 2 of the theorem, X ! B1    Bk follows from F . 3 By A3, X ! A follows. Background: Normal Forms A key for R is a minimal set of attributes such that X ! R note: I use R as both the instance and schema of a relation | shame on me. A superkey is any superset of a key. BCNF: If X ! Y holds for R and is nontrivial, then X is a superkey. 3NF: If X ! Y holds for R and is nontrivial, then either X is a superkey or Y contains a prime attribute member of some key. Decomposition : We can decompose R into schemas S1 ; : : :; Sn if S1    Sn = R. The instance for Si is S R. The FD's that hold for Si are those X ! Y such that XY Si and X ! Y follows from the given FD's for R. i Covers of Sets of FD's Sets F 1 and F 2 of FD's are equivalent if each implies the other. 3 I.e., exactly the same relation instances satisfy each. 3 Any set of FD's equivalent to F is a cover for F. A cover is minimal if: 1. No right side has more than one attribute. 2. We cannot delete any FD from the cover and have an equivalent set of FD's. 3. We cannot delete any attribute from any left side and have an equivalent set of FD's. Example Relation CTHRSG represents courses, teachers, hours, rooms, students, and grades. The FD's: C ! T ; HR ! C ; HT ! R; HS ! R; CS ! G; CH ! R. We can eliminate CH ! R. 3 Proof: Using the other 5 FD's, CH + = CHTR. Having done so, we cannot eliminate any attribute from any left side. 3 Sample proof: Suppose we tried to eliminate T from HT ! R. We would need that C ! T , HT ! R, HR ! C , HS ! R, and CS ! G imply H ! R. But H + = H with respect to these 5 FD's. Thus the remaining ve are a minimal cover of the original six. Note: minimal cover need not be unique, or even have the same number of FD's. Lossless Join The decomposition of R into S1 ; : : :; Sn has a lossless join with respect to some constraints on R if for any instance r of R that satis es the constraints: S1 r .    . S r = r n Motivation: We can replace R by S and T , knowing that the instance of R can be recovered from the instances of S and T . Theorem A decomposition of R into S and T has a lossless join wrt FD's F if and only if S T ! S or S T ! T. 4 Proof First note that r S r . T r always. Assume S T ! S and show that S r . T r r Suppose s is in S r and t is in T r, and s and t join i.e., they agree in S T . 3 Then t is the projection of a tuple t0 of r that agrees with s on S . 3 So t0 agrees with t on T and with s on S , so t0 is s . t; i.e., s . t is in r. Similarly if S T ! T . 3 Now assume neither FD follows from F . 3 Then there is an instance r consisting of two tuples that agree on S T + and disagree elsewhere. This instance satis es F. 3 Neither S nor T is contained in S T + or else one of the FD's in question would follow. 3 Thus, r projected and rejoined yields four distinct tuples, and cannot be r. Theorem We can always decompose a relation R with FD's F into BCNF relations with a lossless join. Proof We decompose when we nd a BCNF violation X ! Y , into X Y and R , Y  X. ,  But R , Y  X X Y  = X . Thus the intersection of the schemas functionally determines one of them, X Y . To complete the proof, we need to show that when we decompose further, the resulting n relations have a lossless join, but that is an easy induction on n. 5 Dependency Preservation When we decompose R with FD's F , will F be equivalent to the union of its projections onto the decomposed relations? One way to guarantee dependency preservation is to use a minimal cover, and convert each FD in the cover X ! A into the schema XA. 3 But if there are some attributes not mentioned in any FD, make them a schema by themselves. Theorem A minimal cover F yields 3NF relations. Proof Suppose XA the relation from X ! A is not in 3NF, because Y ! B is a 3NF violation. We know Y is not a superkey, and B is not prime. Case 1: A = B . Then Y X . Since Y ! B follows from F , and X ! A surely follows from Y ! B , we know F is equivalent to F , fX ! Ag fY ! B g. 3 Then F was not a minimal cover. Case 2: A 6= B . Then B is in X . 3 X is surely a superkey for XA, and since B is not prime, B must not be in any key Z X. 3 Then X , B  ! A can replace X ! A in F , showing F is again not minimal. Decomposition With 3NF, Dependency Preservation and Lossless Join To the schema from a minimal cover, add a key for the original relation if there is not already some relation schema that is a superkey. Example R = ABCD; F = fA ! B; C ! Dg. Start with AB and CD from the FD's. Only key for R is AC . Thus, DP, LJ, 3NF decomposition is fAB; AC; CDg. 6 Proof that the decomposition is always LJ will have to wait for the theory of generalized dependencies." 3 This decomposition is obviously LJ, since AB . AC is lossless because A ! B , and then ABC . CD is lossless because of C ! D. 7 ...
View Full Document

This document was uploaded on 01/06/2012.

Ask a homework question - tutors are online