Notes on The Relational Model and Normalization

Objectives

• 3.1 Understand basic relational terminology

• 3.2 Understand characteristics of relations

• 3.3 Identify alternative terminology in relational model

• 3.4 Identify functional dependencies, determinants, and dependent attributes

• 3.5 Identify primary, candidate, and composite keys

• 3.6 Identify insertion, deletion, and update anomalies in a relation

• 3.7 Normalize a relation to BCNF normal form

• 3.8 Understand domain key normal form significance

• 3.9 Identify multivalued dependencies

• 3.10 Normalize a relation to fourth normal form (4NF)

Understanding Relations and Normal Forms

• Categorization: Process relations based on normal forms to address anomalies and ensure good database design.

• Normalization: Analyze relations using primary keys and functional dependencies to ensure compliance with normalization standards.

The Process of Normalization

1. Transformation: Move from Unnormalized Form (UNF) to:

   • 1NF: Define primary keys, remove repeating groups.

   • 2NF: Eliminate partial dependencies from non-key attributes.

   • 3NF: Remove transitive dependencies where non-key attributes depend on other non-key attributes.

2. BCNF: Ensure all determinants are candidate keys.

3. Higher Normal Forms:

   • 4NF: Address multivalued dependencies.

   • 5NF: Ensure lossless-join dependencies.

Identifying Anomalies

• Insertion Anomaly: Complications in adding data if dependencies reside on other attributes.

• Update Anomaly: Inconsistencies may arise when updating one tuple while others remain unchanged.

• Deletion Anomaly: Removing one entry could inadvertently eliminate necessary data.

Multivalued Dependencies (MVD)

• Definition: Occurs when a determinant is tied to multiple value sets, risking anomalies.

• Handling MVDs: Restructure relations to avoid unwanted MVDs for compliance with 4NF.

Conclusion

• Normalizing database design ensures data integrity and mitigates anomalies systematically from UNF to 5NF.