1. RELATIONAL TABLE NORMALIZATION

2. Key Concepts • Guidelines for Primary Keys • Deletion anomaly • Update anomaly • Insertion anomaly • Functional dependency • Transitive dependency

3. Key Concepts (cont’d) • Multivalued dependency • First normal form (1NF) • Second normal form (2NF) • Third normal form (3NF) • Fourth normal form (4NF) • Domain key normal form (DKNF)

4. Guidelines for Primary Keys • Guideline 1 • The domain of the primary key should be large enough to accommodate the identification of unique rows for the next 100 years • Guideline 2 • Primary keys should be a unique random collection of alphabetic, numeric or alphanumeric characters

5. Guidelines for Primary Keys (cont’d) • Guideline 3 • Avoid using smart keys. Primary keys should not contain “fact giving” data. If these facts are necessary, they should be entity attributes • Guideline 4 • Use the suffix ID in constructing primary key names (CUST_ID, Vendor_ID, etc.)

6. Data Anomalies • Definition of “anomaly”: • Deviation or departure from the normal or common order, form, or rule. • An item that is peculiar, irregular, abnormal, or difficult to classify

7. Deletion Anomaly • Occurs when the removal of a record results in a lost of important information • For example, if all the information about a customer is contained in the ORDER table, deleting an order also deletes customer information • See Recycled Tractor problem for example

8. Update Anomaly • Occurs when multiple record changes for a single attribute are necessary when a change to only one record in a database should be necessary. • Example: an evaluator at Recycled Tractor changes his/her cell phone number

9. Insertion Anomaly • Occurs when there does not appear to be any reasonable place to assign attributes and attribute values to records in a database • Two types of insertion anomalies: • Type 1: Adding new attributes to a record • Type 2: Updating only part of a record

10. Insertion Anomaly (cont’d) • Type 1 example: • Adding Recycled Tractor evaluator’s home address and phone number to the database

11. Insertion Anomaly (cont’d) • Type 2 example: • Essence of the Insertion Anomaly problem: when to enter values into the database • Assign the new Recycled Tractor evaluator to a new dummy lead • Or, add new evaluator to all records in LEAD database – can result in lots of null values

12. Eliminating Data Anomalies • Normalization facilitates the removal of data anomalies • Basic rule of normalization: • The attribute values in a relational table should be functionally dependent on the primary key value

13. Eliminating Data Anomalies (cont’d) • Corollaries to the basic rule: • No repeating groups are allowed in relational tables • A relational table cannot have attributes involved in a transitive dependency with the primary key

14. Eliminating Data Anomalies (cont’d) • The different types of dependencies are critical to understanding and executing the normalization process • One of the primary responsibilities of the database designer is to formalize data relationships by identifying the dependencies among the attributes

15. Functional Dependency • A functionally dependent relationship exists between two attributes when one attribute value implies or determines the value for the other attribute • Example: the value LEAD_NAME determines value of LEAD_BANK in the Recycled Tractor problem • A functional dependency can be reciprocal • Social Security # and Name of person

16. Transitive Dependency (TD) • Occurs when a nonkey attribute value is functionally dependent on another nonkey attribute value that is not a candidate key

17. Transitive Dependency (cont’d) • Example: • EMPLOYEE (EMPLOYEE_ID, CATEGORY, HOURLY_RATE) • If JOB_CATEGORY = SUPERVISOR • Then HOURLY_RATE is $25.00 per hour • if JOB_CATEGORY = WELDER • Then HOURLY_RATE is $18.00 per hour • HOURLY_RATE is dependent on JOB_CATEGORY

18. Multi-Valued Dependency (MVD) • Results from having multiple values for a particular attribute • Three types of MVD’s • Simple • Independent • Transitive

19. Simple MVD • Similar to 1:N cardinality (one to many) • Most common type of MVD • Examples • A student can register for many courses • LEAD_ID functionally determines many values for TRACTOR_ID

20. Independent and Transitive MVD’s • Both types involve three or more attributes • Usually eliminated by first three normal forms

21. First Normal Form (1NF) • A relational table is in first normal form if no attributes form repeating groups • Repeating group attributes are removed by creating another table • In the Recycled Tractor problem, tractor attributes are removed from LEAD and placed in the TRACTOR table, and EVALUATOR attributes are placed in the EAVLUATOR table

22. Second Normal Form (2NF) • A relational table is in second normal form when all nonkey attributes are functionally dependent on the primary key • Only tables with concatenated (composite) keys will a problem in meeting the 2NF requirement • Does our new EVALUATOR table meet 2NF requirements?

23. Third Normal Form (3NF) • A relational table is in third normal form when • it is in second normal form • no attribute has a transitive dependency involving nonkey attributes • In the Recycled Tractor problem, TRACKER_PHONE# is functionally dependent on TRACKER_NAME, which is functionally dependent on LEAD_ID • Boyce-Codd normal form adds requirement that all attribute determinants are also candidate keys

24. Fourth Normal Form (4NF) • A relational table is in fourth normal form when all multivalued dependencies have been removed • In most situations, normalizing tables to third normal form removes multivalued dependencies

25. Domain-Key Normal Form ( DKNF) • DKNF is a philosophy that focuses on developing themes for tables • A student table contains attributes describing students • A relational table is in DKNF if every constraint on the table or file is the result of defining primary keys for a relational table and defining domains for the attributes • Examples of data constraints: • Edit rules for attributes • Relationships of attributes • Functional and multi-valued dependencies

26. Comments on Normalization • The benefits of additional levels of normalization decrease rapidly after tables have been put in 3NF • The instances where higher-level normalization strategies are necessary are considered rare and theoretical