第9讲-逻辑模型设计(更高范式和数据查询操作).ppt

第4部分:逻辑模型设计   逻辑模型设计(更高范式和数据查询操作) 教 师：朱征宇 单 位：重庆大学计算机学院 Multivalued Dependencies Suppose we record names of children, and phone numbers for instructors We might design the schema inst_info(ID, child_name, phone_number) Example data:(99999, David, 512-555-1234)(99999, David, 512-555-4321)(99999, William, 512-555-1234)(99999, William, 512-555-4321) Is this relation in BCNF? Is there any problem？ 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: t3 [?] = t4 [?] = t1[?] = t2 [?]  t3[?] = t1 [?]  t3[R – ?] = t2[R – ?]  t4 [?] = t2[?]  t4[R – ?] = t1[R – ?] MVD (Cont.) Tabular representation of ? ?? ? MVD (Cont.) 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 (性质1-互补律) Y ?? Z if Y ?? W Example 1 In our example: ID ?? child_name  ID ?? phone_number The above formal definition is supposed to formalize the notion that given a particular value of Y (ID) it has associated with it a set of values of Z (child_name) and a set of values of W (phone_number), and these two sets are in some sense independent of each other. Note: (性质2) If Y ? Z then Y ?? Z 证明：Indeed事实上 we have (in above notation) Z1 = Z2The claim follows. (根据定义)# Theory of MVDs From the definition of multivalued dependency, we can derive the following rule: If ? ? ?, then ? ?? ? (即前面的性质2) That is, every functional dependency is also a multivalued dependency Y ?? Z if Y ?? W (即前面的性质1-互补律) The closure D+ of D (函数依赖和多