Multivalued Dependencies Intuition name street city title year

Multivalued Dependencies

Intuition name street city title year C. Fisher 123 Map St. Hollywood Star Wars 1977 C. Fisher 5 Locust Ln. Malibu Star Wars 1977 C. Fisher 123 Map St. Hollywood Empire Strikes Back 1980 C. Fisher 5 Locust Ln. Malibu Empire Strikes Back 1980 C. Fisher 123 Map St. Hollywood Return of the Jedi 1983 C. Fisher 5 Locust Ln. Malibu Return of the Jedi 1983 An instance of the Stars_in relation with address (street, city) • Redundancy: addresses, title repeated several times – because a star might have several addresses and stars in several movies – yet, the relation is in BCNF (no nontrivial functional dependency) • Reason: address and name are independent on each other

Intuition Can we reduce Stars_in to the following relation? name street city title year C. Fisher 123 Map St. Hollywood Star Wars 1977 C. Fisher 5 Locust Ln. Malibu Empire Strikes Back 1980 What are the addresses of C. Fisher when he stars in Star Wars? How to avoid redundancy, yet still get the correct answer? DECOMPOSITION name street city name title year C. Fisher 123 Map St. Hollywood C. Fisher Star Wars 1977 C. Fisher 5 Locust Ln. Malibu C. Fisher Empire Strikes Back 1980 WHAT IS THE GROUND FOR THIS DECOMPOSITION?

Multivalued Dependencies (MVD) A multivalued dependency is a statement about the fact that a set of attributes is independent from another set of attributes. Notation: A 1 A 2…An B 1 B 2…Bm Meaning: for each pair of tuples t and u of R that agree on the A’s, we can find in R some tuple v that agrees: 1. With both t and u on the A’s 2. With t on the B’s 3. With u on the attributes that are not A’s or B’s

Illustration If A 1 A 2…An B 1 B 2…Bm then for each pair t and u that agree on A’s we will find v such that v agrees with 1. t and u on A’s (green) 2. t on B’s(blue) 3. u on all others (red) A’s B’s t u v MVD in picture Others

Example name street city is a MVD of ‘Stars_in’ with address (street, city) (the A’s: name, the B’s {street, city}, others {title, year}) name t C. Fisher u C. Fisher street city title year 123 Map St. Hollywood Star Wars 1977 5 Locust Ln. Malibu Empire Strikes Back 1980 Because of t and u, the relation needs to contain the following tuple v C. Fisher 123 Map St. Hollywood Empire Strikes Back 1980 Switch t and u, we can conclude that the relation also needs to contain the following tuple: v’ C. Fisher 5 Locust Ln. Malibu We can then reconstruct the first relation! Star Wars 1977

Nontrivial MVD • A MVD of relation R A 1…An B 1…Bm is nontrivial if – None of the B’s is among the A’s – Not all attributes of R belong to {A 1, …, An} {B 1, …, Bm}

Reasoning about MVD • Trivial dependencies rule: – if A 1…An B 1…Bm then A 1…An C 1…Ck where the C’s are the B’s plus some of the A’s – if A 1…An B 1…Bm then A 1…An D 1…Dr where the D’s are those B’s that are not among the A’s • Transitive rule: if A 1…An B 1…Bm and B 1…Bm C 1…Ck then A 1…An D 1…Dl where {D 1, …, Dl} = {C 1, …, Ck } {B 1, …, Bm}

Reasoning about MVD • If A 1…An B 1…Bm then A 1…An B 1…Bm • If A 1…An B 1…Bm then A 1…An C 1…Ck where the C’s are all other attributes other than A’s and B’s (Complementation rule) • NOTE: splitting/combining rules do not hold.

Fourth Normal Form (4 NF) • A relation R is in fourth normal form (4 NF) if whenever there is a nontrivial MVD A 1…An B 1…Bm, then {A 1, …, An} is a superkey.

Decomposition into 4 NF • If R is not in 4 NF then there exists a MVD A 1…An B 1…Bm that violates the 4 NF condition, we decompose R into 2 relations with the following schemas: – {A 1, …, An, B 1, …, Bm} – {A 1, …, An, C 1, …, Ck} where {C 1, …, Ck} are those attributes of R that are not among the A’s and the B’s.

Example name street city title year C. Fisher 123 Map St. Hollywood Star Wars 1977 C. Fisher 5 Locust Ln. Malibu Empire Strikes Back 1980 DECOMPOSITION name street city name title year C. Fisher 123 Map St. Hollywood C. Fisher Star Wars 1977 C. Fisher 5 Locust Ln. Malibu C. Fisher Empire Strikes Back 1980 WHAT IS THE GROUND FOR THIS DECOMPOSITION? ANSWER: Because the MVD name street city

Relationship among Normal Forms • 4 NF BCNF 3 NF • Properties of NF and their decomposition Property 3 NF 4 NF BCNF Eliminates redundancy due to FD’s Most Yes Eliminates redundancy due to MVD’s No Yes No Preserves FD’s Yes Maybe Preserves MVD’s Maybe

Homework 4 (Due Oct. 8) 3. 6. 1 For each of the following relation schemas and sets of FDs: a. R(A, B, C, D) with FDs AB C, C D, D A b. R(A, B, C, D) with FDs B C, B D c. R(A, B, C, D) with FDs AB C, BC D, CD A, AD B do the following: i. Indicate all the BCNF violations. Decompose the relations, as necessary, into collections of BCNF relations. (10 pt) ii. Indicate all the 3 NF violations. Decompose the relations , as necessary, into collections of 3 NF relations. (10 pt)

Homework 4 (Due Oct. 8) 3. 7. 2 For each of the following relation schema and dependencies: a. R(A, B, C, D) with MVDs A C, A B b. R(A, B, C, D) with MVDs A B, B CD c. R(A, B, C, D) with MVDs AB C and FD B D do the following: i. Indicate all the 4 NF violations. ii. Decompose the relations, as necessary, into collections of 4 NF relations. (10 pt)

Text for next class • New edition (DBS the complete book): Chapter 4 • Old edition (First course in DBS): – Section 2. 1 – Section 2. 3. 4 – Section 2. 4. 1, 2. 4. 2 – Section 2. 5. 2 – Section 3. 2 (ALL)