Multi-Value Dependency & 4 Normal Form

1. By Omar Jaime Multi-Value Dependency & 4 Normal Form

2. Definition: The multi-valued dependency X->>Y holds in a relation R if wherever we have two tuples of R that agree in all the attributes of X, then we can swap their Y components and get two new tuples that are also in R. Multi-Value Dependency

3. Example Jim ->-> phone#? No Jim ->-> #Beer Drinked? No

4. Example Cont. Jim ->-> Phone# #Beer Drinked? Yes! Jim: {(203)655-7474, 5}, {(415)432-1987, 3}

5. Definition: A1A2…An  B1B2…Bm where B1B2…Bm is a subset of A1A2…An or (A1A2…An B1B2…Bm ) contains all attributes of R Trivial MVD • X ->> Y is trivial if • Y  X or • Y U X = R

6. Complementation if X->->Y, then X->->attr(R) – X - Y Augmentation if X->->Y and V  W, then XW->->YV Transitivity if X->->Y and Y->->Z, then X->->Z-Y MVD Rules

7. Coalescence If X->->Y and ZY and there is some W disjoint from Y such that W->, then X->Z Mvd Rules Cont. X  Y Then:X  Z If:   W:W  Z

9. Definition: A relation R is in 4NF if: • For every non-trivial MVD X->->Y in R, X is superkey. • That is, all FD’s and MVD’s follow from "key-> other attributes" (i.e., no MVD's and no FD's besides key functional dependencies. 4 Normal Form

10. Find a 4NF violation: • A non-trivial MVD X->->Y in R where X is not a superkey. • Decompose R into R1 and R2, where • R1 has attributes X U Y. • R2 has attributes XUZ(Z contains attributes not in X or Y) Repeat until all relations are in 4NF 4NF Decomposition Algorithm

11. R =(A, B, C, G, H, I) F ={ A B BHI CG H } • R is not in 4NF since AB and A is not a superkey for R • Decomposition a) R1 = (A, B) (R1 is in 4NF) b) R2 = (A, C, G, H, I) (R2 is not in 4NF) c) R3 = (C, G, H) (R3 is in 4NF) d) R4 = (A, C, G, I) (R4 is not in 4NF) Example

12. Since AB and BHI, AHI, so AI e) R5 = (A, I) (R5 is in 4NF) f)R6 = (A, C, G) (R6 is in 4NF) Example Cont.

13. The End