Introduction to Database Normalization
Normalization is a set of database design principles that prevents update and deletion anomalies caused by redundancy.
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.
- 1Locate the copies
The value Blue appears in the eye-color column of all 1200 track rows for Frank Sinatra.
- 2Apply the change
The redundant design requires changing the value in 1200 separate rows.
- 3Miss 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.
- 4Assess 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
Common mistake Assuming that repeated values are harmless because each row looks correct
What it looks likeEvery track row shows Frank Sinatra and Blue, so the table appears consistent.
Why it failsThe same artist facts are still stored in multiple places, and a later update can leave different rows with different values.
Do this insteadLook for facts about one entity that are copied into many related rows.
Common mistake Updating only the row currently being viewed
What it looks likeChanging the eye color in one track row from Blue to Light Blue.
Why it failsOther rows for the same artist still contain the old value.
Do this insteadRecognize that a redundant design requires every copy of the fact to be updated.
Common mistake Treating a deletion anomaly as an ordinary missing-track problem
What it looks likeDeleting an artist's only track and then discovering that the artist details are gone too.
Why it failsThe artist information was stored only inside the track row.
Do this insteadSeparate artist information from track information so deleting a track does not delete the only artist record.
Common mistake Thinking normalization means storing no relationships between tables
What it looks likeRemoving artist information from Track without providing a way to connect tracks to artists.
Why it failsNormalization uses references, such as foreign keys, to connect related data across separate tables.
Do this insteadStore 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.
- 1Name change
The name appears five times, so all five copies must be updated. Updating only some rows creates inconsistent student information.
- 2Enrollment deletion
If the student has only one enrollment and that row is deleted, the student's name can be lost from the database.
- 3Normalized 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
- 1Redundancy occurs when the same information is stored repeatedly across rows.
- 2Repeated data makes updates risky because changing only some copies creates inconsistency.
- 3A deletion anomaly occurs when deleting one row also removes the only stored copy of related information.
- 4Normalization stores each piece of information in one place and uses foreign keys to connect related tables.
- 5A table can look correct in a query result while still having a flawed, redundant design.
At a glance
- 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.