1 / 15

4N and the Normalization of Multivalue Dependency

4N and the Normalization of Multivalue Dependency. By: Laith Alnagem. Rules. Fourth Normal Form - if R is 3N and BCNF… - given the “non-trivial” MVD: A 1 A 2 …A n Þ B 1 B 2 …B n { A 1 A 2 …A n } is a superkey

martine
Download Presentation

4N and the Normalization of Multivalue Dependency

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. 4N and the Normalization of Multivalue Dependency By: Laith Alnagem

  2. Rules Fourth Normal Form - if R is 3N and BCNF… - given the “non-trivial” MVD: A1A2…AnÞ B1B2…Bn {A1A2…An} is a superkey A MVD: A1A2…AnÞ B1B2…Bn for a Relation R is “non-trivial” if: 1. none of the Bs are among the As 2. Not all of the attributes of R are among the As and Bs A MVD is “trivial” if it contains all the variations of A1A2…AnxB1B2…Bn. orαß is ß Íαor ß Èα

  3. Formal Definition Let R be a relation schema and let αÍ Rand ß Í R . The multivalued dependencyα  βholds on R if, in any legal relation r(R), for all pairs of tuples t1 and t2 in r such that t1[α] = t2[α], there exist tuples t3 and t4 in r such thatt1[α] = t2[α] = t3[α] = t4[α]t3[β] = t1[β]t3[R − β] = t2[R − β]t4[β] = t2[β]t4[R − β] = t1[R − β][1]

  4. Multivalued Dependency in 4N • A table with a multivalued dependency is one where the existence of more than one independent many-to-many relationships in a table causes redundancy; and it is this redundancy which is removed by fourth normal form.

  5. Example

  6. Example (cont’) • It is 1N, 2N, 3N and BCNF • But because the varieties of pizza a restaurant offers are independent from the areas to which the restaurant delivers, there is redundancy in the table: • for example, we are told three times that A1 Pizza offers Stuffed Crust, and if A1 Pizza start producing Cheese Crust pizzas then we will need to add multiple records, one for each of A1 Pizza's delivery areas.

  7. The Fix

  8. A little thought Q: What if the pizza varieties offered by a restaurant sometimes did vary from one delivery area to another?

  9. A: Then the original three-column table would satisfy 4NF

  10. A. • {course}  {book} • {course}  {lecturer}

  11. Conclusions • Databases with multivalued dependencies exhibit redundancy. • In database normalization, fourth normal form requires that databases have no multivalued dependencies.

  12. Extra • Transitivity, Reflexivity, Complementation, Replication, Augmentation and a few more are all properties of 4th normal form

  13. References • Multivalued dependency From Wikipedia, the free encyclopedia Revision as of 11:59, 24 October 2006 by GurchBot (Talk | contribs)(diff) ← Older revision | Current revision | Newer revision → (diff) • DataBase: System Concepts (5th Edition) Ch-7 Silberschatz, Korth, Sudarshan NY, 2006

More Related