Relational Design Theory Multivalued Dependencies 4 th Normal

Relational Design Theory Multivalued Dependencies & 4 th Normal Form Jennifer Widom

Relational design by decomposition MVDs & 4 NF § “Mega” relations + properties of the data § System decomposes based on properties § Final set of relations satisfies normal form – No anomalies, no lost information § Functional dependencies Boyce-Codd Normal Form § Multivalued dependences Fourth Normal Form Jennifer Widom

Example: College application info. MVDs & 4 NF Apply(SSN, c. Name, hobby) FDs? Keys? BCNF? Good design? Jennifer Widom

Multivalued Dependency MVDs & 4 NF § Based on knowledge of real world § All instances of relation must adhere Jennifer Widom

MVDs & 4 NF Apply(SSN, c. Name, hobby) Jennifer Widom

Modified example MVDs & 4 NF Apply(SSN, c. Name, hobby) Reveal hobbies to colleges selectively MVDs? Good design? Jennifer Widom

Expanded example MVDs & 4 NF Apply(SSN, c. Name, date, major, hobby) Reveal hobbies to colleges selectively Apply once to each college May apply to multiple majors Jennifer Widom

Trivial Multivalued Dependency MVDs & 4 NF Nontrivial MVD Jennifer Widom

Rules for Multivalued Dependencies FD-is-an-MVD rule MVDs & 4 NF Jennifer Widom

Rules for Multivalued Dependencies Intersection rule MVDs & 4 NF Transitive rule Jennifer Widom

Fourth Normal Form MVDs & 4 NF Relation R with MVDs is in 4 NF if: For each nontrivial A B, A is a key Jennifer Widom

Fourth Normal Form MVDs & 4 NF Relation R with MVDs is in 4 NF if: For each nontrivial A B, A is a key Jennifer Widom

4 NF decomposition algorithm MVDs & 4 NF Input: relation R + FDs for R + MVDs for R Output: decomposition of R into 4 NF relations with “lossless join” Compute keys for R Repeat until all relations are in 4 NF: Pick any R’ with nontrivial A B that violates 4 NF Decompose R’ into R 1(A, B) and R 2(A, rest) Compute FDs and MVDs for R 1 and R 2 Compute keys for R 1 and R 2 Jennifer Widom

BCNF Decomposition Example #1 MVDs & 4 NF Apply(SSN, c. Name, hobby) Jennifer Widom

BCNF Decomposition Example #2 MVDs & 4 NF Apply(SSN, c. Name, date, major, hobby) Jennifer Widom

Relational design MVDs & 4 NF § Functional dependencies & Boyce-Codd Normal Form § Multivalued dependences & Fourth Normal Form Jennifer Widom