First Normal Form: Eliminating Repeating Groups
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 Attractive Shortcut
When a Track table already stores song information such as a title and play count, adding an artist column seems like the most direct solution. Each track row can contain the artist's name and other artist details. With only a few rows, the table can look clean and perfectly reasonable. The structural problem appears when the same artist information is copied into every track row.
| Track | Artist | Eye color |
|---|---|---|
| My Way | Frank Sinatra | Blue |
| Fly Me to the Moon | Frank Sinatra | Blue |
| New York, New York | Frank Sinatra | Blue |
The artist details are repeated once for every track.
Tracing the Redundancy
Redundancy occurs when the same information is stored multiple times across different rows in a table. In this design, the track is different from row to row, but the artist name and eye color describe the same artist each time. If Frank Sinatra has 1200 songs in the table, the value Frank Sinatra appears in 1200 artist cells, and the same eye-color value is repeated across those rows. The database is storing one artist fact in many places.
Counting Copies of One Artist Fact
A table contains 1200 tracks by Frank Sinatra. Each row stores the artist name and the artist's eye color. How many rows must be considered when the eye-color fact changes?
Locate the repeated fact: The eye color describes the artist, not an individual track, but it is stored in every track row.
Count the copies: Because Frank Sinatra has 1200 tracks in the table, the same eye-color fact is present in 1200 rows.
Identify the maintenance task: Changing the fact requires updating all 1200 copies rather than changing one stored artist record.
One artist fact has become 1200 separate values that must be kept consistent.
A query can return a reasonable-looking result even when the table design is poorly organized. Correct-looking output does not prove that each fact is stored in the right place.
The Update Anomaly
An update anomaly appears when redundant data needs to change. Suppose Frank Sinatra's eye color should be recorded as Light Blue instead of Blue. In the redundant design, the change must be applied to every row containing his tracks. If only some rows are updated, the table contains two conflicting versions of the same fact. Some rows say Blue and others say Light Blue, and the database itself cannot identify which version is correct.
The Deletion Anomaly
A deletion anomaly occurs when deleting one row removes information that was stored only in that row. Suppose a track is no longer available and is deleted. If it was the only track by a particular artist in the table, the deletion also removes the only record of that artist's existence. A track deletion has accidentally caused artist information to be lost.
The deletion anomaly reveals that the table has coupled two different kinds of information: facts about a track and facts about an artist. Removing one should not automatically erase the other.
Separating Facts and References
Normalization prevents these anomalies by storing each piece of information in exactly one place. Instead of repeating artist details in the Track table, create a separate Artist table that stores those details once. The Track table can then refer to the artist using a foreign key. The relationship between tracks and artists remains available, but the artist's details no longer have to be copied into every track row.
| Design | Where artist details are stored | Effect of changing an artist detail |
|---|---|---|
| Redundant table | Repeated in each track row | Many rows must be updated |
| Normalized design | Stored once in the Artist table | One artist record is updated |
The normalized design keeps the relationship through references instead of copied details.
Design Review Practice
A course-enrollment table stores the student's name in every course row. A student is enrolled in five courses. The student's name needs to change because it was entered incorrectly. What redundancy exists, what update anomaly could occur, and what information could be lost if the student drops their only course?
Hints
- Count how many rows contain the same student fact.
- Consider what happens if only some copies are changed.
- Ask whether the enrollment row is also the only place where the student's name is stored.
Checking the Enrollment Design
Analyze the student-name repetition in the enrollment table.
Identify the redundancy: The student's name is repeated in all five course rows even though it describes one student.
Predict the update problem: A name correction must be applied to five rows. If some rows are missed, different rows can contain different versions.
Predict the deletion problem: If the student drops their only course, deleting that enrollment row can also remove the student's name from the database.
Apply the design principle: Store the student information once and connect enrollment records to it with a reference.
Repeated student information creates the same update and deletion anomalies as repeated artist information.
Common Design Mistakes
Assuming a table is well designed because its query results look correct.
The same result can hide the fact that one artist fact is stored repeatedly.
Fix:
Check whether each piece of information is stored once or copied across many rows.Updating only one occurrence of a repeated value.
The table now contains conflicting versions of the same artist fact.
Fix:
Avoid repeated storage by placing the artist detail in one Artist record.Treating a deletion anomaly as an ordinary consequence of removing a track.
Track information and artist information have been coupled in one row.
Fix:
Store artist information separately and connect tracks through a foreign key.Thinking redundancy matters only in large databases.
The same design problem becomes a major maintenance issue as the number of rows grows.
Fix:
Evaluate where each fact belongs before the table grows.
Key Takeaways
- Redundancy occurs when the same information is stored repeatedly across different rows.
- Repeated artist details make updates fragile because partial updates can create inconsistent values.
- Deleting the last related row can accidentally remove the only copy of an artist's information.
- Normalization stores each piece of information in one place and uses foreign keys to connect related tables.
- Separating track facts from artist facts prevents unrelated information from being coupled to the same row.
Key Takeaways
- Repeated values across rows are a form of data redundancy.
- Redundancy causes update anomalies when copies are not changed consistently.
- Redundancy causes deletion anomalies when deleting one row removes the only copy of related information.
- A normalized design stores artist information once and connects tracks to it with a foreign key.