1 / 22

Agenda

Agenda. Functional Dependencies for Relational Databases Normalization for Relational Databases. Normalization for Relational Databases. Normal Forms Based on Primary Keys Second and Third Normal Forms Boyce-Codd Normal Form.

fola
Download Presentation

Agenda

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. Agenda • Functional Dependencies for Relational Databases • Normalization for Relational Databases

  2. Normalization for Relational Databases • Normal Forms Based on Primary Keys • Second and Third Normal Forms • Boyce-Codd Normal Form

  3. Relation Schema that is not in First Normal Form-due to multi-values in location Elmasri, p. 359

  4. First Normal Form Relation with Redundancy Elmasri, p. 315

  5. 3 solutions • 1. Separate table • 2. Expand key (dnumber, location) • 3. For known maximum values - put one column for each • Elmasri p. 360

  6. 1st NF • No multi-valued columns • Includes no nested tables - as shown on next slide

  7. “Nested Relation” PROJS Within EMP_PROJ Elmasri, p. 361

  8. Extension of EMP_PROJ Relation With Nested Relations Within Each Tuple Elmasri, p. 361

  9. Decomposing EMP_PROJ into First Normal Form Relations by Migrating the Primary Key Elmasri, p. 361

  10. Normalization for Relational Databases • Normal Forms Based on Primary Keys • Second and Third Normal Forms • Boyce-Codd Normal Form

  11. Normalizing EMP_PROJ into 2NF Relations Elmasri, p. 363

  12. 2 NF • “Full functional” dependency • Need all of the composite key

  13. Normalizing EMP_DEPT into 3NF Relations Elmasri, p. 363

  14. 3 NF • No transitive dependencies

  15. So - 3 NF • Every column must depend on the key, the whole key, and nothing else

  16. LOTS Relation Schema and Functional Dependencies-next 3 Elmasri, p. 366

  17. Decomposing LOTS into 2NF Relations Elmasri, p. 366

  18. Decomposing LOTS1 into 3NF Relations Elmasri, p. 366

  19. Summary of LOTS Normalization Elmasri, p. 366

  20. Normalization for Relational Databases • Normal Forms Based on Primary Keys • Second and Third Normal Forms • Boyce-Codd Normal Form

  21. Boyce-Codd Normal Form (BCNF) (a) BCNF normalization with the dependency of fd2 being “lost” in the decomposition (b) A relation R in 3NF but not in BCNF Elmasri, p. 369

  22. BCNF • Stronger than 3 NF • Says - any dependency in a table must be explicit (Post p. 86)

More Related