What You’ll Learn
This section of notes covers the process of database normalization in which relations (tables) created from the conversion of the 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 in a relational database management system.
What is Normalization?
- 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.
- Normalization is an important part of the database development process: Often during normalization, the database designers get their first real look into how the data are going to interact in the database.
- Finding problems with the database structure at this stage is strongly preferred to finding problems further along in the development process because at this point it is fairly easy to cycle back to the conceptual model (Entity Relationship model) and make changes.
- Normalization can also be thought of as a trade-off between data redundancy and performance. Normalizing a relation reduces data redundancy but introduces the need for joins when all of the data is required by an application such as a report query.
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.