0ch07-bygyb.ppt

Figure 7.16: An Illegal R2 Relation Figure 7.18: Relation of Practice Exercise 7.2 * Hold 成立 * 结束 * 结束 * Design Goals Interestingly, SQL does not provide a direct way of specifying functional dependencies other than superkeys. Can specify FDs using assertions, but they are expensive to test Even if we had a dependency preserving decomposition, using SQL we would not be able to efficiently test a functional dependency whose left hand side is not a key. Multivalued Dependencies (MVDs) Let R be a relation schema and let ? ? R and ? ? R. The multivalued dependency ? ?? ? holds on R if in any legal relation r(R), for all pairs for tuples t1 and t2 in r such that t1[?] = t2 [?], there exist tuples t3 and t4 in r such that: t1[?] = t2 [?] = t3 [?] = t4 [?]  t3[?] = t1 [?]  t3[R – ?] = t2[R – ?]  t4 [?] = t2[?]  t4[R – ?] = t1[R – ?]  MVD (Cont.) Tabular representation of ? ?? ? Example Let R be a relation schema with a set of attributes that are partitioned into 3 nonempty subsets. Y, Z, W We say that Y ?? Z (Y multidetermines Z )if and only if for all possible relations r (R ) y1, z1, w1 ? r and y1, z2, w2 ? r then y1, z1, w2 ? r and y1, z2, w1 ? r Note that since the behavior of Z and W are identical it follows that Y ?? Z if Y ?? W Example (Cont.) In our example: course ?? teacher  course ?? book The above formal definition is supposed to formalize the notion that given a particular value of Y (course) it has associated with it a set of values of Z (teacher) and a set of values of W (book), and these two sets are in some sense independent of each other. Note: If Y ? Z then Y ?? Z Indeed we have (in above notation) Z1 = Z2The claim follows. Use of Multivalued Dependencies We use multivalued dependencies in two ways: 1. To test relations to determine whether they are legal under a given set of functional and multivalued dependencies 2. To specify constraints on the set of legal relations. We shall