Second Normal Form: Removing Partial Dependencies

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 One-Table Design

When you first design a database, the most direct solution often feels like the best one. A Track table already contains song information such as a title and play count, so adding artist information to that same table seems convenient. Each track row can contain the track details, the artist name, and other artist attributes. The table looks organized, and a query can return everything in one result. The hidden problem is that the same artist information is copied into every row for that artist.

A table can look correct while still having a poor structure. Repeated artist information is a sign that one row is mixing facts about two different things: a track and an artist.

Tracing a Partial Dependency

A useful way to inspect this design is to ask what each attribute describes. Track title and play count describe a track. Artist name and eye color describe an artist. If a row is identified using a combination of track-related and artist-related information, the artist attributes do not need the complete combination to determine which artist they describe. They depend on the artist part rather than on the whole track-and-artist combination. That is the partial dependency this design needs to remove.

Which attributes describe the entire track-and-artist combination, and which describe only the artist?

A partial dependency occurs when an attribute associated with a row depends on only part of a combined identifier instead of the complete identifier. In the track example, artist attributes belong to the artist portion of the design, so repeating them in every track row creates redundancy.

Counting the Repeated Facts

Frank Sinatra's repeated artist details

Suppose 1200 Frank Sinatra songs are stored in one table, with the artist name and eye color repeated in every track row. What redundancy does this create?

Locate the repeated values: Every one of the 1200 track rows contains Frank Sinatra in the artist column and Blue in the eyes column.

Separate track facts from artist facts: The track rows may each describe different songs, but the artist name and eye color describe the same artist.

Measure the maintenance burden: Changing the eye color from Blue to Light Blue requires changing 1200 stored instances in this redundant design.

The table stores the same artist facts repeatedly instead of storing them once and connecting tracks to that single artist record.

Where does the same artist information appear across track rows, and how does separating the artist record reduce duplication?

Splitting Artist and Track Facts

Second Normal Form addresses this design problem by separating information that describes different entities. An Artist table stores each artist's information once. A Track table stores track information and contains a reference to the related artist rather than repeating the artist's name and other artist attributes in every track row. The reference is a foreign key, which connects the related tables.

How does the original table split so artist information is stored once while track information remains connected?

Normalization does not remove the relationship between a track and an artist. It removes the unnecessary repetition by storing the artist facts once and preserving the relationship through a foreign key.

Following the Anomalies

Redundancy creates maintenance problems because one fact has multiple stored copies. An update anomaly occurs when only some copies are changed. A deletion anomaly occurs when deleting a track also removes the only remaining copy of an artist's information. These anomalies couple track maintenance to artist maintenance, even though they describe different kinds of information.

What happens when an artist's repeated value is changed in one row but not in the other rows?

Assuming that a correct-looking query result proves the table is well designed.
The visible result does not reveal the maintenance burden or the possibility of inconsistent copies.
Fix: Inspect whether an attribute describes the track or the artist, then store it with the appropriate entity.
Updating only the row currently being viewed.
The table now contains conflicting versions of the same artist fact.
Fix: Avoid repeated artist facts by storing the artist information once and referencing it from tracks.
Treating deletion of a track as unrelated to artist information.
The deletion can also remove the only stored record of that artist.
Fix: Keep artist information in its own table so deleting a track does not automatically delete the artist record.

A Practical Inspection Routine

List the attributes in the table and state what each attribute describes.
Mark attributes that describe a track separately from attributes that describe an artist.
Look for artist values repeated across multiple track rows.
Ask whether an artist attribute depends on the complete row identifier or only on the artist portion.
Check what would happen if the artist attribute changed.
Check whether deleting the last track for an artist would also remove the artist's information.
Separate artist information into an Artist table and connect tracks with a foreign key.

MEDIUM

A design stores a student's name in every enrollment row. The student takes five courses. Identify the repeated information, describe one update anomaly, and describe what could happen if the student's enrollment in their only course is deleted.

Hints

Count how many enrollment rows contain the student's name.
Imagine the student's name changes and only some rows are updated.
Consider whether the enrollment row is the only place where the student appears.

Your attempt (not saved)

Design	Where artist information is stored	Maintenance consequence
Combined table	Repeated in every track row	Updates must change many copies
Separated tables	Stored once in Artist	Tracks reference the artist through a foreign key

The Normalized Design

Second Normal Form is about removing information that depends on only part of a combined row identifier. In the example, artist attributes are not properties of each individual track, so repeating them in the Track table creates redundancy. Storing artist information once in an Artist table and linking tracks with a foreign key prevents the same fact from being copied across many rows.

Redundancy means storing the same information in multiple rows.
Repeated artist attributes create a partial dependency when those attributes belong to only the artist portion of a combined identifier.
An update anomaly occurs when some repeated copies change and others do not.
A deletion anomaly occurs when deleting a track also deletes the only stored copy of an artist's information.
Normalization stores each piece of information once and uses foreign keys to connect related tables.

Key Takeaways

Repeated artist information across track rows is data redundancy.
Artist attributes that depend on only the artist portion of a combined identifier are partial dependencies.
Redundancy can produce inconsistent updates and accidental loss of artist information during deletion.
Second Normal Form separates artist and track facts into related tables.
A foreign key preserves the track-to-artist relationship without copying artist details into every track row.