1 / 21

Normalization of Database Tables

Normalization of Database Tables. Topics. Role of normalization in the database design process Normal forms 1NF, 2NF, 3NF, BCNF, 4NF Normalization & ER modeling used together for good database design Denormalization. Normalization.

tbieber
Download Presentation

Normalization of Database Tables

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. Normalization of Database Tables

  2. Topics • Role of normalization in the database design process • Normal forms 1NF, 2NF, 3NF, BCNF, 4NF • Normalization & ER modeling used together for good database design • Denormalization

  3. Normalization • Process for evaluating/correcting table structures to minimize data redundancies • Reduces data anomalies • Series of stages called normal forms • 1NF, 2NF, 3NF, BCNF, 4NF • For many purposes, 3NF is good enough • Sometimes denormalization (to a lower normal form) is advised

  4. Example • A company manages building projects • Charges clients by hours spent on each contract • Hourly billing rate depends on employee’s position • Report: Table 5.1

  5. Report converted to table

  6. Goals of Normalization Process • Each table represents a single subject • No data item will be unnecessarily stored in more than one table • All attributes in a table are dependent only on the primary key (or a candidate key) • Each table void of insertion, update, deletion anomalies • All a product of data redundancy

  7. Conversion to First Normal Form • Eliminate any repeating groups • They represent multi-valued attributes – bad!!! • Eliminate nulls: each repeating group attribute contains an appropriate data value • Identify the primary key (PK) • Must uniquely identify all attribute values • Identify all dependencies • Dependencies depicted with a diagram

  8. Dependency Diagram

  9. What is the PK?

  10. Conversion to 1NF • First normal form describes tabular format in which: • All key attributes are defined • There are no repeating groups in the table • All attributes are dependent on primary key

  11. Conversion to 2NF • Eliminate partial dependencies • Only relevant if PK is composite

  12. Conversion to 3NF • Eliminate transitive dependencies

  13. Improving the Design • Issues to address beyond normalization • Evaluate PK assignments • Evaluate naming conventions • Refine attribute atomicity • Identify new attributes • Identify new relationships • Refine primary keys for granularity • Maintain historical accuracy • Evaluate derived attributes

  14. Higher-Level Normal Forms • Tables in 3NF perform suitably in business transactional databases • Higher order normal forms useful on occasion • Two special cases of 3NF: • Boyce-Codd normal form (BCNF) • Fourth normal form (4NF)

  15. Boyce-Codd Normal Form (BCNF) • Every determinant in table is a candidate key • Has same characteristics as PK, but was not chosen to be primary key • When table contains only one candidate key, 3NF and BCNF are equivalent • Table can be in 3NF and fail to meet BCNF • Happens when a non-key attribute is the determinant of a key attribute

  16. In 3NF but not BCNF

  17. In 3NF but not BCNF PK is STU_ID, STAFF_ID CLASS_CODE STAFF_ID

  18. Fourth Normal Form (4NF) • Table is in 4NF when • It is in 3NF • No multiple sets of multi-valued dependencies • Not an issue if tables conform to these rules: • All attributes are dependent on PK, and are independent of each other • No row contains two or more multi-valued facts about an entity • Example – see Fig. 6.10 & 6.11, pp. 196-197

  19. Normalization and Database Design • Normalization is part of the design process • Proposed entities must meet required normal form before table structures are created • Many real-world databases have been improperly designed or burdened with anomalies • Normalization is necessary if you are asked to redesign and modify existing databases

  20. Denormalization • Creation of normalized relations is important database design goal • Processing requirements should also be a goal • With normalization, the number of tables expands • Joining the larger number of tables reduces system speed • Conflicts often resolved through compromises that may include denormalization`

More Related