Multivalued Dependencies

Multivalued Dependencies

Intuition • 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 An instance of the Stars_in relation with address (street, city)

Intuition Can we reduce Stars_in to the following relation? What are the addresses of C. Fisher when he stars in Star Wars? How to avoid redundancy, yet still get the correct answer? DECOMPOSITION 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: A1A2…An B1B2…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: • With both t and u on the A’s • With t on the B’s • With u on the attributes that are not A’s or B’s

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

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}) t  u  Because of t and u, the relation needs to contain the following tuple v  Switch t and u, we can conclude that the relation also needs to contain the following tuple: v’  We can then reconstruct the first relation!

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

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

Reasoning about MVD • If A1…An B1…Bm then A1…AnB1…Bm • If A1…An B1…Bm then A1…An C1…Ckwhere 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 (4NF) • A relation R is in fourth normal form (4NF) if whenever there is a nontrivial MVD A1…An B1…Bm, then {A1,…,An} is a superkey.

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

Example DECOMPOSITION WHAT IS THE GROUND FOR THIS DECOMPOSITION? ANSWER: Because the MVD name  street city

Relationship among Normal Forms • 4NF  BCNF  3NF • Properties of NF and their decomposition

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: • Indicate all the BCNF violations. Decompose the relations, as necessary, into collections of BCNF relations. (10pt) • Indicate all the 3NF violations. Decompose the relations , as necessary, into collections of 3NF relations. (10pt)

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: • Indicate all the 4NF violations. • Decompose the relations, as necessary, into collections of 4NF relations. (10pt)

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)

Multivalued Dependencies