Normalization of Database Tables

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

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

15. 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)

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

17. In 3NF but not BCNF

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

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

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

21. 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`