1 / 23

Multi-valued Dependencies Salman Azhar

Multi-valued Dependencies Salman Azhar. Multi-valued Dependencies Fourth Normal Form. These slides use some figures, definitions, and explanations from Elmasri-Navathe’s Fundamentals of Database Systems and Molina-Ullman-Widom’s Database Systems. A New Form of Redundancy.

cleary
Télécharger la présentation

Multi-valued Dependencies Salman Azhar

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. Multi-valued Dependencies Salman Azhar Multi-valued Dependencies Fourth Normal Form These slides use some figures, definitions, and explanations from Elmasri-Navathe’s Fundamentals of Database Systemsand Molina-Ullman-Widom’s Database Systems

  2. A New Form of Redundancy • Multi-valued dependencies (MVDs) express a condition among tuples of a relation that exists when the table (relation) is trying to represent • more than one many-many relationship. • then certain columns (attributes) become independent of one another • and their values must appear in all combinations.

  3. Example Customers(name, addr, phones, sodasLiked) • A customer’s phones are independent of the sodas they like. • Many-many relations: • Customers  Phones Customers  Sodas • Each phone appear with each soda in all combinations. • E.g., For 3 phones (Home, Work, Cell) and 10 sodas, we need 30 tuples • Repetition is unlike FD redundancy. • There is only one FD: name  addr

  4. sue a p2 s1 sue a p1 s2 Tuples Implied by Independence If we have pink tuples, then Then the blue tuples must also be in the relation name addr phones sodasLiked sue a p1 s1 sue a p2 s2 name-phone and name-soda relations are independent:The green relationship implies existence of blue relations

  5. Definition of MVD • A multi-valued dependency (MVD) X Y : • if an assertion that if two rows of a table agree on all the attributes of X, • then their components may be swapped in the set of attributes Y, • and the result will be two tuples that are also in the relation.

  6. Example • The name-addr-phones-sodasLiked example illustrated two MVDs • name  phones • name  sodasLiked.

  7. Picture of MVD X Y XY others equal exchange Other attributes get copied remain the same We must exchange all components of Ys (not just some) If there is an FD X  Y, then swapping Ys components doesn’t change anything. Every FD is an MVD.

  8. MVD Rules • Every FD is an MVD. • If XY, then swapping Y ’s between two tuples that agree on X doesn’t change the tuples. • Therefore, the “new” tuples are surely in the relation, and we know X Y. • Complementation: • If XY, and Z is all the other attributes (complement of X and Y) then XZ. • Reason: Swapping Ys that agree on X is the same effect as swapping Zs. 

  9. Splitting Doesn’t Hold • Recall in FDs we could split right side, but could NOT split left side • Recall FD A, B  C does not imply AC and BC • Recall FD A B, C does imply AB and AC • MVDs, we cannot generally split the left side • BUT, we cannot split the right side EITHER!!! • E.g., name  areaCode phone • in (408) 555-9999 and (812)-555-1111, you can’t swap area codes • sometimes you have to deal with several attributes on the right side.

  10. Example • Consider phone has been split into area code and number and adding manf giving the following relation: Customers(name, areaCode, phone, sodasLiked, manf) • We can claim: • A customer can have several phones, • the number divided between areaCode and phone • A customer can like several sodas, • each with its own manufacturer. • Phones are sodasLiked are independent, so we get • all possible combinations of areaCode-phone pairs & sodasLiked-manf pairs

  11. Example, Continued • Since the areaCode-phone pairs for a customer are independent of the sodasLiked-manf pairs, we expect the following MVDs: name  areaCode phone name  sodasLiked manf • Observe that we can’t split the right hand sides: • areaCode and phone are linked • sodasLiked and manf are linked • in (408) 555-9999 and (812)-555-1111, you can’t swap area codes!!!

  12. Example Data Here is possible data satisfying these MVDs: name areaCode phone sodasLiked manf Sue 812 555-1111 Pepsi PepsiCo Sue 812 555-1111 Sprite CocaCola Sue 408 555-9999 Pepsi PepsiCo Sue 408 555-9999 Sprite CocaCola Now all possible combinations are represented. Swapping any pairs does not yield new rows. We cannot swap area codes or phones by themselves. Swapping (812) And (408) gives us incorrect phone numbers Note: neither name  areaCode nor name  phone holds for this relation.

  13. Fourth Normal Form • The redundancy caused by MVDs can’t be removed by transforming the database schema to BCNF. • There is a stronger normal form, called 4th Normal for (4NF), that (intuitively): • treats MVDs as FDs when it comes to decomposition • but not when determining keys of the relation.

  14. 4NF Definition • A relation R is in 4NF if whenever X Y is a nontrivial MVD, then X is a superkey. • Nontrivial means that: • Y is not a subset of X (swapping components does not change tuples) • X and Y are not, together, all the attributes. (swapping components yields the same tuples) • Note that the definition of “superkey” still depends on FDs only.

  15. BCNF Versus 4NF • Remember that • Every FD X Y is also an MVD, XY. • Thus, if R is in 4NF, it is certainly in BCNF. • Because any BCNF violation is a 4NF violation. • However, R could be in BCNF and not 4NF, because MVDs are “invisible” to BCNF.

  16. Decomposition and 4NF • If XY is a 4NF violation for relation R, we can decompose R using the same technique as for BCNF. • XY is one of the decomposed relations. • All but (Y – X) is the other.

  17. Example Customers(name, addr, phones, sodasLiked) FD: name  addr MVDs: name  phones name  sodasLiked • Key is {name, phones,sodasLiked}. • All dependencies violate 4NF because name is not a superkey.

  18. Example, Continued • Decompose using name  addr: • Customers1(name, addr) • In 4NF, only dependency is name  addr. • Customers2(name, phones, sodasLiked) • Not in 4NF because MVDs • name  phones and name  sodasLiked apply. • No FD’s, so all three attributes form the key.

  19. Example: Decompose Customers2 • Recall • Customers2(name, phones, sodasLiked) • Either MVD • name  phones • name  sodasLiked • tells us to decompose to: • Customers3(name, phones) • Customers4(name, sodasLiked)

  20. Example: Decompose Customers2 • 4th Normal Form Consists of: • Customers1(name, addr) • Contact • Customers3(name, phones) • Phone • Customers4(name, sodasLiked) • Likes • The following FD and MVDs are satisfied: FD: name  addr MVDs: name  phones name  sodasLiked

  21. Continue Normal Forms • 5th Normal Form • 6th Normal Form • 7th Normal Form

  22. Salman Teaching 4th Normal Form • How would I look teaching 937th Normal Form?

  23. Salman Teaching 957th Normal Form

More Related