Stuff iThink that will be on the Quiz/Midterm. - PowerPoint PPT Presentation

paul2
stuff ithink that will be on the quiz midterm l.
Skip this Video
Loading SlideShow in 5 Seconds..
Stuff iThink that will be on the Quiz/Midterm. PowerPoint Presentation
Download Presentation
Stuff iThink that will be on the Quiz/Midterm.

play fullscreen
1 / 21
Download Presentation
Stuff iThink that will be on the Quiz/Midterm.
363 Views
Download Presentation

Stuff iThink that will be on the Quiz/Midterm.

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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