1 / 13

Multivalued Dependencies

Multivalued Dependencies. by Asmerom Tekeste. Normal Forms. BCNF. 5NF. 2NF. 4NF. 3NF. 1NF. Functional dependencies. Multivalued dependencies. Join dependencies. Recall Normal Forms. Example :. R (A, B, C, D, E). FD: { B, C, D, E}. A. Normal Form of R (A,B,C,D,E).

mariah
Download Presentation

Multivalued Dependencies

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Multivalued Dependencies by Asmerom Tekeste

  2. Normal Forms BCNF 5NF 2NF 4NF 3NF 1NF Functional dependencies Multivalued dependencies Join dependencies

  3. Recall Normal Forms Example: R (A, B, C, D, E) FD: { B, C, D, E} A

  4. Normal Form of R (A,B,C,D,E) 1NF: no multivalues 2NF: no FDs where a subset of the key to the relation is on the left 3NF: no non-trivial FDs, either the determinant is a superkey or the RHS of the FD is a member of some key BCNF: the determinant of any non-trivial FD is a superkey for the relation

  5. Normal Form (cont) Stars

  6. Normal Form (cont) • R T x U x V(R is similar to the Cartesian product of relations T, U, and V) ~ ~ Relation U Relation V Relation T - The relation Stars is in BCNF

  7. Problems with relation Stars The relation Stars doesn’t seem to be sufficiently normalized Reasons: • No reason to associate address with one movie and not • another • When we repeat address and movie facts in all combinations, • there is obvious redundancy • - Relation R contains unnecessary duplication of data

  8. Introducing MVD Definitions: • A MVD: A1A2…An B1B2…Bn for a Relation R is • “non- trival” if • 1. none of the Bs are among the As • 2. not all of the attributes of R are among the As and Bs • A MVD is “trivial” if • A1A2…An  B1B2…Bmwhere B1B2…Bm is a subset of A1A2…Anor (A1A2…AnU B1B2…Bn ) contains all attributes of R • A relation cannot be decomposed any further (under 4NF • rules) if it has a trivial MVD

  9. More on MVD Definitions(cont): A1A2…AnB1B2…Bm holds for relation R if: For all tuples t, u in R If t[A1A2...An] = u[A1A2...An], then there exists a v in R such that: (1) v[A1A2...An] = t[A1A2...An] = u[A1A2...An] (2) v[B1B2…Bm] = t[B1B2…Bm] (3) v[C1C2…Ck] = u[C1C2…Ck], where C1C2…Ck is all attributes in R except (A1A2...An  B1B2…Bm)

  10. More on MVD • Intuitively, A1A2…An  B1B2…Bm says that the relationship • between A1A2…An and B1B2…Bm is independent of the relationship • between A1A2…An and R -{B1B2…Bm} • - MVD's uncover situations where independent facts related to a certain object are being squished together in one relation • Functional dependenciesrule out certain tuples from being in a • relation • - if A  B, then we can’t have two tuples with the same A values but different B • - a.k.a. equality-generating dependencies • Multivalued dependenciesrequire that other tuples of a certain form be present in the relation • - a.k.a. tuple-generating dependencies

  11. MVD Example: is A  C

  12. Theories of MVD REPLICATION FD is an MVD If A1A2…An  B1B2…Bm then A1A2…An  B1B2…Bm holds COMPLEMENTATION If A1A2…An  B1B2…Bm then A1A2…An  C1C2…Ck where C1C2…Ck is all attributes in R except (A1A2…An  B1B2…Bm) AUGMENTATION If XY and WZ then WX YZ

  13. Theories of MVD (cont) TRANSITIVITY If XY and YZ then X  (Z-Y) REFLEXIVITY ifAis a set of attributes andA B, then ABholds. REPLICATION if A  Bholds, then AB.

More Related