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.

Programming

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.

Which values are duplicated across rows, and how does each track row repeat the same artist information?

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.

Worked example

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?

1
Locate the repeated fact
The eye color describes the artist, not an individual track, but it is stored in every track row.
2
Count the copies
Because Frank Sinatra has 1200 tracks in the table, the same eye-color fact is present in 1200 rows.
3
Identify the maintenance task
Changing the fact requires updating all 1200 copies rather than changing one stored artist record.

Result

One artist fact has become 1200 separate values that must be kept consistent.

Key point

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.

What happens when an artist's name or detail changes in one row but remains unchanged in the other rows?

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.

What information is accidentally lost when deleting the last track associated with an artist?

Key point

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.

How does separating artist information from track information eliminate repeated values while preserving their relationship?

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

MEDIUM

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.

Your attempt (not saved)

Worked example

Checking the Enrollment Design

Analyze the student-name repetition in the enrollment table.

1
Identify the redundancy
The student's name is repeated in all five course rows even though it describes one student.
2
Predict the update problem
A name correction must be applied to five rows. If some rows are missed, different rows can contain different versions.
3
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.
4
Apply the design principle
Store the student information once and connect enrollment records to it with a reference.

Result

Repeated student information creates the same update and deletion anomalies as repeated artist information.

Common Design Mistakes

Common mistake
Assuming a table is well designed because its query results look correct.
What it looks like
A track query displays the expected artist name on every row.
Why it fails
The same result can hide the fact that one artist fact is stored repeatedly.
Do this instead
Check whether each piece of information is stored once or copied across many rows.
Common mistake
Updating only one occurrence of a repeated value.
What it looks like
Changing one row from Blue to Light Blue while leaving other rows unchanged.
Why it fails
The table now contains conflicting versions of the same artist fact.
Do this instead
Avoid repeated storage by placing the artist detail in one Artist record.
Common mistake
Treating a deletion anomaly as an ordinary consequence of removing a track.
What it looks like
Deleting an artist's last track also removes the only stored details about that artist.
Why it fails
Track information and artist information have been coupled in one row.
Do this instead
Store artist information separately and connect tracks through a foreign key.
Common mistake
Thinking redundancy matters only in large databases.
What it looks like
Ignoring repeated values because a test table contains only a few tracks.
Why it fails
The same design problem becomes a major maintenance issue as the number of rows grows.
Do this instead
Evaluate where each fact belongs before the table grows.

Key Takeaways

1
Redundancy occurs when the same information is stored repeatedly across different rows.
2
Repeated artist details make updates fragile because partial updates can create inconsistent values.
3
Deleting the last related row can accidentally remove the only copy of an artist's information.
4
Normalization stores each piece of information in one place and uses foreign keys to connect related tables.
5
Separating track facts from artist facts prevents unrelated information from being coupled to the same row.

At a glance

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.

Related concepts

More concepts starting with F