1 / 12

Database Systems: Design, Implementation, and Management

Database Systems: Design, Implementation, and Management. Chapter 6 Normalization of Database Tables. Problems with unnormalized tables. Needless redundancy, hence insert, update and delete anomalies (inconsistencies) Data updates are less efficient because tables are larger

marah-casey
Download Presentation

Database Systems: Design, Implementation, and Management

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. Database Systems: Design, Implementation, and Management Chapter 6 Normalization of Database Tables

  2. Problems with unnormalized tables • Needless redundancy, hence insert, update and delete anomalies (inconsistencies) • Data updates are less efficient because tables are larger • Indexing is more cumbersome • No simple strategies for creating views (virtual tables)

  3. Dependencies for normalization:Functional dependency • Functional dependency: A→B or (A,B)→(C,D) • B is functionally dependent on A means A can automatically give you the correct value of B • E.g. Project.ID → Project.Name • Also called determination: “A determines B” • Full functional dependency: (A,B)→C where A↛C and B↛C • When all the attributes in a key are required for the determination (none is optional) • E.g. (Project.ID, Project.Manager) → Project.NameProject.Manager is optional—this is not a full functional dependency • E.g. (Project.Manager, Project.StartDate) → Project.CostThis is a full functional dependency, assuming a manager can launch no more than one project on a given date

  4. Dependencies for normalization:Partial and transitive dependencies • Partial dependency: (A,B)→(C,D) and B→C • (A,B)is a PK • C doesn’t need both A and B to determine it; it only needs B • E.g. (Project.ID,Project.ManagerID) → Project.Nameand Project.ID→ Project.Name • Transitive dependency: A→(B,C) and B→C • Ais a PK • Technically speaking, a transitive dependency requires that B and C not be part of the PK. However, if you expand the meaning to include even if they are part of the PK, then you will avoid BCNF automatically • A determines C, but so does B, even though B is not a PK • E.g. Project.ID → (Project.Client,Project.Location)and Project.Client → Project.Location

  5. Summary of normal forms • 1NF: table format with PK • 2NF: 1NF minus partial dependencies • All dependencies are fully functional • (A,B)→C where A↛C and B↛C • 3NF/BCNF: 2NF minus transitive dependencies • All determinants are PKs • If A→(B,C), then B ↛ C • There is a technical distinction between 3NF and BCNF, but if you keep this rule, then you take care of both 3NF and BCNF • 4NF: BCNF minus multivalued dependencies • Each row strictly describes just one entity • DKNF, 5NF, 6NF • relatively rare and often not worth the trouble normalizing

  6. Dependency diagram:Primary tool for normalization • Depicts all dependencies found within given table structure • Helpful in getting bird’s-eye view of all relationships among table’s attributes • Makes it less likely that you will overlook an important dependency

  7. 3NF vs BCNF

  8. Denormalization • Although normalization is important, processing speed and efficiency is also important in database design

  9. Sources • Most of the slides are adapted from Database Systems: Design, Implementation and Management by Carlos Coronel and Steven Morris. 11th edition (2015) published by Cengage Learning. ISBN 13: 978-1-285-19614-5 • Other sources are noted on the slides themselves

More Related