What You’ll Learn
This section of notes covers the process of database normalization in which relations (tables) created from the conversion from E-R model are analyzed for potential flaws and these flaws are corrected. The following specific topics are covered:
- The Relational Model
- Functional Dependencies
- Keys and Uniqueness
- Modification Anomalies
- Normalization Process
- All-In-One Example of normalization.
- More Normalization Exercises to try
|Connolly, Begg, Holowczak||Pratt/Adamski||Rob/Coronel (5th ed)||Elmasri/Navathe (3rd) ed.||Kroenke (7th ed.)||Hoffer, Prescott & McFadden (6th ed)|
|Chapter 8||Chapter 5||Chapter 4||Chapter 14 and 15||Chapter 5||Chapter 5 and Appendix B|
The Relational Model
As a reminder, the database development process we are following has the these steps:
- Gather user/business requirements.
- Develop the conceptual E-R Model (shown as an E-R Diagram) based on the user/business requirements.
- Convert the E-R Model to a set of relations in the (logical) relational model
- Normalize the relations to remove any anomalies.
- Implement the database by creating a table for each normalized relation.
Normalization is a process in which we systematically examine relations for anomalies and, when detected, remove those anomalies by splitting up the relation into two new, related, relations.
Recall, the Relational Model consists of the elements: relations, which are made up of attributes.
- A relation is a set of attributes with values for each attribute such that:
- Each attribute (column) value must be a single value only.
- All values for a given attribute (column ) must be of the same data type.
- Each attribute (column) name must be unique.
- The order of attributes (columns) is insignificant
- No two tuples (rows) in a relation can be identical.
- The order of the tuples (rows) is insignificant.
- From our discussion of E-R Modeling, we know that an Entity typically corresponds to a relation and that the Entity’s attributes become attributes of the relation.
- We also discussed how, depending on the relationships between entities, copies of attributes (the identifiers ) were placed in related relations as foreign keys.
The next step is to identify functional dependencies within each relation.