1 / 21

Understanding Fourth Normal Form in Database Design

Learn about 4NF, its benefits, eliminating data anomalies, reducing need for inserts/updates/deletes, MVD comparison, no duplications in tables for 4NF, examples, and how to achieve 4NF properly. References included.

ulema
Télécharger la présentation

Understanding Fourth Normal Form in Database Design

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. Stuff iThinkthat will be on the Quiz/Midterm. Fourth Normal Form by Andrew Yee

  2. Why 4NF? • Performance • We want database to take up less space.

  3. Why 4NF? • To eliminate the possibility for data anomalies.

  4. Why 4NF? • To reduce the need for: • inserts, • updates • deletes

  5. Normal Forms Comparison

  6. Non-Trival Multi-Valued Dependencies (MVD) • Assume X is the Superkey • “Superkey” still depends on FD’s only. • Y is not a subset of X • X and Y are not, together, all the attributes.

  7. Recap of 4NF Definition • No non-trivial multi-valued dependencies - Except on superset of a candidate key - Multi-valued dependencies are functional dependencies. • Table must be in BCNF

  8. Recap of 4NF Definition • No Duplications • Table must be in BCNF

  9. Example to get 4NF • Assume the table is in BCNF • The town, Region, and District together form the candidate key. From Reference #1

  10. Example to get 4NF • Decomposed into two tables From Reference #1

  11. Example 2: Is it in 3NF or BCNF? Pizza Delivery From Reference #2

  12. Example 2: Is it in 3NF or BCNF? • The table has no non-key attributes because its only key is {Restaurant, Pizza Variety, Delivery Area}. • The answer is BCNF. From Reference #2

  13. Example 2: Is it in 4NF? Pizza Delivery From Reference #2

  14. Example 2: Is it in 4NF? • If we assume, that pizza varieties offered by a restaurant are not affected by delivery area, then it does not meet 4NF. • The answer is NO. From Reference #2

  15. Why not in 4NF? Two Non-trivial: MVD Pizza Delivery From Reference #2

  16. Why not in 4NF? • The table has two non-trivial multivalued dependencies on the {Restaurant} attribute (which is not a superkey). • The dependencies are: • {Restaurant} →→ {Pizza Variety} • {Restaurant} →→ {Delivery Area} From Reference #2

  17. How to “fix it” (4NF)? Two Non-trivial: MVD Pizza Delivery From Reference #2

  18. How to “fix it” (4NF)? Varieties By Restaurant Delivery Areas By Restaurant No Duplications among rows! Yay! From Reference #2

  19. Practice Example • Is this 3NF or BCNF? • Is this 4NF? • If not, what are the decomposed tables in 4NF? Pizza Delivery

  20. Practice Example (Answer) • BCNF 2. NO 3. See Below Delivery Areas By Restaurant Varieties By Restaurant No Duplications among rows! Yay!

  21. References • Website: Database Design: 4th and 5th Normal Forms « Tod means Fox URL:http://blog.todmeansfox.com/2007/12/04/database-design-4th-and-5th-normal-forms/ 2. Website: Fourth normal form - Wikipedia, the free encyclopedia URL: http://en.wikipedia.org/wiki/Fourth_normal_form

More Related