slides01-2 - Multivalued Dependencies In relation R, we say...

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: Multivalued Dependencies In relation R, we say MVD X ! Y holds if ! whenever there are tuples s and t in R such that X s = X t, then there is a tuple r in r such that: 1. XY r = XY s. 2. R,Y  X r = R,Y  X t. I.e., r agrees with s on the attributes mentioned, and with t on X and all the attributes not mentioned. Example Consider CTHRSG. In addition to the FD's C ! T ; HR ! C ; HT ! R; HS ! R; CS ! G; CH ! R, we might reasonably expect the MVD C ! HR. ! That is, given a course, the hour-room pairs are independent of the teacher-student-grade triples. 3 There will be a unique teacher for the course, but student-grade information should appear in combination with each hour-room pair, since there is no logical reason to assign di erent grades for di erent hours and rooms. Axiomatization of FD's + MVD's A4: A5: A6: A7: A8: Start with Armstrong's Axioms. Then add: Complementation. If X ! Y holds in R, then ! X ! R , X , Y . ! MVD augmentation. If X ! Y , then ! XZ ! Y Z for any set of attributes Z . ! MVD transitivity. If X ! Y and Y ! Z , then ! ! X ! Z , Y . ! Promotion. If X ! Y , then X ! Y . ! If X ! Y and U ! V , where: ! 1. U is disjoint from Y , and 2. V Y , then X ! V . 3 Example: AB ! CDE and FG ! C ! imply AB ! C . 1 Decomposition Rules For FD's, we know that if X ! A1    An , then we can decompose" into X ! A1 ; : : :; X ! An . MVD's do not always allow right sides of size 1. However, X ! R , X  always holds by A1 ! trivial FD's, A7, and A4. We can break R , X into some disjoint partition, say X ! Y1 Y2    Yk ! such that X ! Z i Z is a union of some of ! the Yi's. Example For CTHRSG, we have C ! T HR SG ! i.e., a course has a teacher only one because of the FD C ! T , a set of hour-room pairs, and a set of student-grade pairs. Generalized Dependencies Unify FD's, MVD's, lossless joins, and inferences about dependencies. A generalized dependency consists of: 1. One or more hypothesis rows consisting of abstract symbols, one for each attribute of the relation in question. 2. A conclusion that is either another row or an equality between two symbols. 3 If the conclusion is a row, then the GD is a tuple-generating dependency TGD; if an equality it is an equality-generating dependency EGD. We shall usually talk about typed dependencies, which means that a symbol may not appear in two di erent columns, and we may not equate in the conclusion symbols from di erent columns. Usually, we talk about full dependencies, meaning that the conclusion row of a TGD uses only symbols that have appeared elsewhere. 2 But sometimes we like to have partial dependencies, where the conclusion introduces new symbols. Semantics of GD's If there is a mapping from the symbols of the hypothesis rows that turns each hypothesis into a tuple of R, then the conclusion must hold. If it is an EGD, then R must be such that the equated symbols are mapped to the same value. If a full TGD, then the conclusion row, with the same mapping applied, must also be a row of R. If a partial TGD, then there is a way to map the new symbols in the conclusion so the conclusion row becomes a tuple of R. Examples In R = ABCD the FD AB ! C : ABCD a1 b1 c1 d1 a1 b1 c2 d2 c1 = c2 The MVD A! BC : ! AB a1 b1 a1 b2 a1 b1 C c1 c2 c1 D d1 d2 d2 The join dependency JD . AB; BC; CD, which says that AB , BC , and CD are a lossless-join decomposition of ABCD: ABCD a b c1 d1 a2 b c d2 a3 b3 c d abcd The Chase Infers Full GD's Given a set of full GD's G , does another GD G follow? I.e., does every relation instance that satis es G also satisfy G? 3 Start with a relation R consisting of the hypothesis rows of G. Repeatedly apply GD's H of G to the current relation: a If H is a TGD, map its hypothesis rows to tuples of R in any way, and insert the mapped conclusion row of H into R. b If H is an EGD, map its hypothesis rows to tuples of R in any way and equate the symbols of R that H 's conclusion says are equal. 3 Remember to equate all occurrences of the symbols that the EGD says are equal. 3 Subtle point: if one or both of the equated symbols appear in the conclusion, change these occurrences as well; i.e., the desired conclusion must change as well as the tuples in the constructed relation. Since no new symbols are ever generated because of the full" assumption, eventually this process stalls. At that time, if the conclusion row of G has been added to R in the case G is a TGD or the symbols that G says must be equal have been equated in case G is an EGD, then conclude that G follows from G. If not, then not. Proof the Chase Works Soundness: Each inference made is sound, since it is a direct application of a given GD. Completeness: Suppose the conclusion of G is not obtained. Then the nal R is a counterexample: 3 It satis es every GD in G , but does not satisfy G. Proof That a Key Plus Relations From a Minimal Cover Have a Lossless Join Suppose we have a minimal cover F consisting of FD's Xi ! Ai for i = 1; 2; : : :; n, and we choose database schema fX1 A1; : : :; Xn An; K g where K is a key for the entire relation R. 4 We need to infer from F written as EGD's, if you like the JD that says the join is lossless. This JD has: 1. A conclusion row that has unsubscripted" symbols corresponding to each attribute. 2. For each relation schema, a hypothesis row that has the unsubscripted symbol for an attribute if that attribute is in the schema, and a unique symbol bj if b is the symbol for the attribute and j is the row number. Example Let F = fA ! B; C ! Dg. Recall the generated decomposition is fAB; AC; CDg. The JD is: A a a a3 a B b b2 b3 b C c1 c c c D d1 d2 d d Proof That This JD Follows From the FD's We know K + = R. An induction on the order in which attributes are added to K + says that in the row for K , each subscripted symbol is equated to its unsubscripted version. 3 Key idea: if we use X ! A to add A to K + , then in the row for K , all symbols for the attributes in X have lost their subscripts. Therefore, we may apply X ! A to the rows for K and XA to infer that aj = a, where j is the row number for XA. As a result, the row for K eventually loses all its subscripts, and becomes the conclusion row of the JD. 5 ...
View Full Document

This document was uploaded on 01/06/2012.

Ask a homework question - tutors are online