Stuff iThink that will be on the Quiz/Midterm.

# Stuff iThink that will be on the Quiz/Midterm.

Télécharger la présentation

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