Introduction to Database Normalization
Redundancy occurs when the same information is stored multiple times across different rows in a table, such as repeating an artist's name in every track row.
The Intuitive Track Design
When you first add artist information to a table of tracks, the most natural choice is to add an artist column directly to the Track table. The design appears simple: each row contains a track title, its play count, and the artist who recorded it. With only a few rows, the table can look clean and correct.
| Track title | Play count | Artist | Artist eye color |
|---|---|---|---|
| Track A | 120 | Frank Sinatra | Blue |
| Track B | 85 | Frank Sinatra | Blue |
| Track C | 210 | Frank Sinatra | Blue |
The same artist information is repeated in every track row.
Recognizing Redundancy
Redundancy occurs when the same information is stored multiple times across different rows in a table. In the track design, the artist name and artist eye color are stored once for every track by that artist.
The repeated values are not separate facts merely because they appear in separate rows. They describe the same artist. If one artist has many tracks, the design copies the artist's information into many track records. The more tracks the artist has, the more copies exist. This may look harmless in a small test table, but it makes one artist fact depend on many separate rows.
Tracing an Update Anomaly
Changing an Artist Attribute
Frank Sinatra's eye color must change from Blue to Light Blue in a table containing 1200 of his tracks.
Locate the copies: The value Blue appears in the eye-color column of all 1200 track rows for Frank Sinatra.
Apply the change: The redundant design requires changing the value in 1200 separate rows.
Miss some rows: If the update stops partway through or application logic updates only some rows, some records continue to say Blue while others say Light Blue.
Assess the result: The database is now inconsistent, and the rows themselves do not reveal which version is correct.
One change to one artist fact becomes a maintenance operation across 1200 track rows. A partial update creates conflicting values.
This is an update anomaly. It arises because one fact has been copied into multiple places. Updating only some instances creates inconsistency, and the database provides no reliable way to tell which repeated version is correct. The same problem applies whether the changed value is an artist's eye color, name, or another artist attribute.
Tracing a Delete Anomaly
Deletion creates a different problem. Suppose a track is removed because it is no longer available. If that track is the artist's only track in the table, deleting the track row also removes the only stored copy of the artist's information. The database loses artist details as an unintended side effect of deleting a track.
The same pattern appears in enrollment data. If a student takes five courses, the student's name can appear five times. If the student's enrollment in their only course is deleted, the student's name can disappear from the database entirely. For an instructor teaching 20 courses, the instructor's name and email can appear 20 times, so an email change requires changing 20 rows.
Normalization and References
Normalization is a set of database design principles that prevents update and deletion anomalies caused by redundancy. Its core idea is that each piece of information should be stored in exactly one place. Instead of copying artist information into every Track row, a separate Artist table stores the artist information once. The Track table then connects to that artist using a foreign key.
| Design choice | Where artist information is stored | Maintenance consequence |
|---|---|---|
| Redundant design | Repeated in every related track row | An update may require changing many rows |
| Normalized design | Stored once in a separate Artist table | Tracks use a foreign key to reference the artist |
Normalization separates artist information from track information and connects related data with references.
Mistakes in Identifying Redundancy
Assuming that repeated values are harmless because each row looks correct
The same artist facts are still stored in multiple places, and a later update can leave different rows with different values.
Fix:
Look for facts about one entity that are copied into many related rows.Updating only the row currently being viewed
Other rows for the same artist still contain the old value.
Fix:
Recognize that a redundant design requires every copy of the fact to be updated.Treating a deletion anomaly as an ordinary missing-track problem
The artist information was stored only inside the track row.
Fix:
Separate artist information from track information so deleting a track does not delete the only artist record.Thinking normalization means storing no relationships between tables
Normalization uses references, such as foreign keys, to connect related data across separate tables.
Fix:
Store the artist once and let Track reference that artist.
Practice: Diagnose the Design
A table stores a student's name in every enrollment row. The student is enrolled in five courses. Predict what can happen if the student's name changes, and predict what can happen if the student's only enrollment row is deleted.
Hints
- Count how many copies of the student's name must be changed.
- Ask where the student's information is stored after the only enrollment row disappears.
- Connect each problem to either an update anomaly or a deletion anomaly.
Practice Answer
A student's name appears in five enrollment rows.
Name change: The name appears five times, so all five copies must be updated. Updating only some rows creates inconsistent student information.
Enrollment deletion: If the student has only one enrollment and that row is deleted, the student's name can be lost from the database.
Normalized direction: Store the student's information once and use references from enrollment data to connect the student with courses.
Repeated student information creates the same update and deletion risks as repeated artist information.
Key Takeaways
- Redundancy occurs when the same information is stored repeatedly across rows.
- Repeated data makes updates risky because changing only some copies creates inconsistency.
- A deletion anomaly occurs when deleting one row also removes the only stored copy of related information.
- Normalization stores each piece of information in one place and uses foreign keys to connect related tables.
- A table can look correct in a query result while still having a flawed, redundant design.
Key Takeaways
- Repeated artist, student, or instructor information across related rows is data redundancy.
- Redundancy creates update anomalies because one fact must be changed in multiple places.
- Redundancy creates deletion anomalies because removing a row can remove the only copy of related information.
- Normalization separates different kinds of information into separate tables and connects them with foreign keys.
- The central design goal is to store each piece of information in exactly one place.