1 / 26

Multivalued Dependencies

Multivalued Dependencies. By David Wortham. Problem Introduction. Carrie Fisher = Princess Leia Organa. Assume a relation R (from the book): (credit Ullman and Widom). Problem Introduction. What is the highest Normal Form R complies with?. Recall 1NF.

kalb
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 David Wortham

  2. Problem Introduction Carrie Fisher = Princess Leia Organa • Assume a relation R (from the book):(credit Ullman and Widom)

  3. Problem Introduction • What is the highest Normal Form R complies with?

  4. Recall 1NF • 1NF eliminates wasted space due to duplicate attributes (columns) • Example (before 1NF normalization):

  5. Recall 1NF (con’t) • After 1NF normalization:

  6. Identify the FDs • Functional Dependencies in R: • Since every tuple in R is unique, the

  7. Identify the FDs • Functional Dependencies in R: • The only FD is: {attrB, attrC, attrD, attrE } ® attrA

  8. Normal Form of R • 1NF (no multivalues) [check] • 2NF (no FDs where a subset of the key to the relation is on the left) [check] • 3NF (no non-trivial FDs: either the determinant is a superkey or the RHS of the FD is a member of some key) [check] • BCNF (the determnant of any non-trivial FD is a superkey for the relation) [check]

  9. Problem Intro. (con’t) • Notice:

  10. Problem Intro. (con’t) • Also Notice:

  11. Observe the Pattern • R ~= TxUxV(R is similar to the Cartesian product of relations T, U, and V) Relation T Relation U Relation V

  12. Problem Definition • The Relation R contains unnecessary duplication of data • R is valid 1NF, 2NF, 3NF, and BCNF (and there are no exact duplicate tuples) • R has common data on AddrStreet and AddrCity of all tuples • R has common data on FilmName and FilmLocat of all tuples

  13. Solution • Introduction of a 4NF • Eliminate “non-trivial” MDs • Eliminate additional FDs that violate BCNF

  14. Definitions • Fourth Normal Form - if R is valid BCNF and… - given the “non-trivial” MVD: A1A2…AnÞ B1B2…Bn {A1A2…An} is a superkey • A MVD: A1A2…AnÞ B1B2…Bn for a Relation R is “non-trivial” 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 it contains all the variations of A1A2…Anx B1B2…Bn. • A relation cannot be decomposed any further (under 4NF rules) if it has a trivial MVD

  15. Note about FDs and MVDs • Every FD is a MVD (if A1A2…An® B1B2…Bn , then A1A2…AnÞ B1B2…Bn ) • The converse is not always true • FDs rule out certain tuples (i.e. if A ® B then two tuples will not have the same value for A and different values for B) • MVDs do not rule out tuples. They guarantee that certain tuples must exist. (we will see this later)

  16. Another Example • Another MVD example(credit www.cs.jcu.edu.au)

  17. Another Example (con’t) • Our second example 4NF decomposed: Relation M Relation L Relation O

  18. Explanation of second example • Unnecessary tuples are eliminated (those with NULL values)… saving space • Note: for this example, MxO is not similar to L: this is because the MVD in L is non-trivial

  19. Second example (modified) • If we were to modify the second example L to be 4NF, we would need to combine every possible value of M with every one of O, changing the MVD from non-trivial to trivial • This relation would look similar to the product of M and O

  20. Second example (modified) • If we were to modify the second example L, we would need to combine every possible value of M with every one of O Relation L

  21. Second example (modified) • If we were to modify the second example L, we would need to combine every possible value of M with every one of O • This relation is 4NF b/cthe only MVD is trivial(the original L was not4NF) Relation L w/ Trivial MVD

  22. Formal Definition of a MVD • Using the following relation,(credit Ullman & Widom) • Note that A, B, and “others” are not actual attributes, but rather sets of attributes

  23. Formal Definition of a MVD (con’t) • Suppose A Þ B holds, then: • t[A] = u[A] = v[A] • t[B] = u[B] • u[C] = v[C] • If a MVD is to exist, u must exist. • For the MVD to be non-trivial,every tuple must be in the formof a u tuple

  24. Formal Definition of a MVD (con’t) • For the MVD A Þ B to be trivial, either: • B Í A or • B È A = R Must be true

  25. Armstrong’s Axioms WRT MVDs • Many of Armstrong’s Axioms work with MVDs including: • Reflexivity rule • Augmentation rule • Transitivity rule • Complementation rule • Multivalued augmentation rule • Multivalued transitivity rule • Replication rule • Coalescence rule • Multivalued union rule • Intersection rule • Difference rule • See below for specifics on these ruleshttp://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node15.html

  26. References • Course textbook:a First Course in Database Systems (Jeffrey D. Ullman and Jennifer Widom) • Normalizing your database: First Normal Form (1NF):http://webc.nicc.edu/~weedd/sysanaly/Chapter%206%20Database%20normalization/1NF.html • Multivalued Dependencies (Ozmar Zaine):http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter7/node13.html • Multivalued Dependencies:http://www.cs.jcu.edu.au/Subjects/cp1500/1998/Lecture_Notes/normalisation/mvd.html

More Related