Unnormalized Database Designs

It is important to recognize when a database design violates the rules for the normal forms.

Albums, Tracks, and Artists

Below are five different relational database designs shown as Entity Relationship Diagrams (ERDs) and corresponding tables for a database that stores data about albums, songs, and musical artists. The first four designs violate first, second, or third normal form. The last design does not violate any of the rules for first, second, or third normal form and is therefore normalized to third normal form.

Violates First Normal Form

A table is in first normal form (1NF) if all cells contain only atomic values (not multi-valued), there are no repeating columns, and the table has a primary key. The entity in the following ERD and the corresponding table violate 1NF, because there are lists in the cells in the tracks column. One reason the lists in the tracks column are problematic is because some of the track titles, such as “Something, etc.”, include commas. How will the computer distinguish between a comma that is part of a track title and a comma that separates track titles?

An entity relationship diagram containing a non-normalized design for artists, albums, and tracks that violates first normal form
album
albumTitle yearReleased label tracks artist artistCountry
Abbey Road 1969 Apple Here Comes the Sun, Octopus’s Garden, Something, etc. Beatles UK
Fearless 2008 Big Machine Records Love Story, White Horse, You Belong with Me, Fifteen Taylor Swift US

The entity in the next ERD and the corresponding table also violate first normal form because there are lists of columns: track1, track2, … With such a design, how will the designer know how many track columns to add to the table? How many of those track columns will be empty most of the time? When the computer searches for a particular track, it will have to search in all the track columns which is inefficient.

An entity relationship diagram containing a non-normalized design for artists, albums, and tracks that violates first normal form
album
albumTitle yearReleased label track1 track2 track3 track4 track5 artist artistCountry
Abbey Road 1969 Apple Here Comes the Sun Octopus’s Garden Something, etc. Beatles UK
Fearless 2008 Big Machine Records Love Story White Horse You Belong with Me Fifteen Taylor Swift US

Violates Second Normal Form

A table is in second normal form (2NF) if it meets all the requirements for 1NF and all non-key attributes are functionally dependent on the entire primary key. The definition of 2NF and the definition of primary key imply that if a table is in 1NF and its primary key is a single column (not composite), then it’s also in 2NF. In other words, only a table with a composite primary key can violate 2NF.

The entity in the next ERD and the corresponding table are normalized to first normal form (1NF) because there are no lists within cells and no repeated columns and the table has a primary key. However, they violate second normal form (2NF) because the yearReleased depends on only the albumTitle which is only one column of the composite primary key. Similarly, the duration depends on only the trackTitle.

An entity relationship diagram containing a non-normalized design for artists, albums, and tracks that doesn’t violate first normal form but does violate second normal form
track
albumTitle yearReleased label trackTitle duration artist artistCountry
Abbey Road 1969 Apple Here Comes the Sun 3:06 Beatles UK
Abbey Road 1969 Apple Octopus’s Garden 2:48 Beatles UK
Abbey Road 1969 Apple Something 2:59 Beatles UK
Fearless 2008 Big Machine Records Love Story 3:57 Taylor Swift US
Fearless 2008 Big Machine Records White Horse 3:55 Taylor Swift US
Fearless 2008 Big Machine Records You Belong with Me 3:52 Taylor Swift US
Fearless 2008 Big Machine Records Fifteen 4:55 Taylor Swift US

In practical terms, a table that violates 2NF contains multiple entities and must be split into multiple tables. The track table above contains at least three entities, album, track, and artist.

Violates Third Normal Form

A table is in third normal form (3NF) if it meets all the requirements for 2NF and no non-key attributes is transitively dependent on the primary key. In other words, no non-key attribute depends on another non-key attribute.

In the next design, the album table is normalized to 3NF, meaning it doesn’t violate the rules for 1NF, 2NF, or 3NF. However, the track table is normalized to 2NF but violates the rules for 3NF. It violates the rules for 3NF because the artistCountry is transitively dependent on the primary key (trackTitle). The artistCountry depends on the artist which depends on the trackTitle. In other words, artistCountry is determined by the artist and not the trackTitle.

An entity relationship diagram containing a non-normalized design for artists, albums, and tracks that doesn’t violate first normal form because all multi-valued columns and repeated columns have been removed but does violate third normal form because artistCountry doesn’t depend on trackKey
album
albumTitle yearReleased label
Abbey Road 1969 Apple
Fearless 2008 Big Machine Records
track
trackTitle duration artist artistCountry albumTitle
Here Comes the Sun 3:06 Beatles UK Abbey Road
Something 2:48 Beatles UK Abbey Road
Octopus’s Garden 2:59 Beatles UK Abbey Road
Love Story 3:57 Taylor Swift US Fearless
White Horse 3:55 Taylor Swift US Fearless
You Belong with Me 3:52 Taylor Swift US Fearless
Fifteen 4:55 Taylor Swift US Fearless

Normalized to Third Normal Form

The entities in the next ERD and corresponding tables do not violate any of the rules for first, second, or third normal form, so we say the design is normalized to third normal form.

An entity relationship diagram for artists, albums, and tracks that is normalized to third normal form
album
albumKey albumTitle yearReleased label
ABRD Abbey Road 1969 Apple
FRLS Fearless 2008 Big Machine Records
track
trackKey trackTitle duration albumKey artistKey
1 Here Comes the Sun 3:06 ABRD BTLS
2 Something 2:48 ABRD BTLS
3 Octopus’s Garden 2:59 ABRD BTLS
4 Love Story 3:57 FRLS TSWF
5 White Horse 3:55 FRLS TSWF
6 You Belong with Me 3:52 FRLS TSWF
7 Fifteen 4:55 FRLS TSWF
artist
artistKey artistName artistCountry
BTLS Beatles UK
TSWF Taylor Swift US

Magazines, Subscriptions, and Subscribers

Below are five different relational database designs shown as Entity Relationship Diagrams (ERDs) and corresponding tables for a database that stores data about magazines, subscriptions, and subscribers. The first four designs violate first, second, or third normal form. The last design does not violate any of the rules for first, second, or third normal form and is therefore normalized to third normal form.

Violates First Normal Form

This first design violates first normal form (1NF) because some of the cells contain lists of values.

An entity relationship diagram containing a non-normalized design for subscribers, magazines, and subscriptions that violates first normal form
subscriber
Subscriber Magazine Subscription
First Name Last Name Address City State ZIP Code Name Price Start Date Length
Albert Anderson 220 K Street Southeast Auburn WA 98002 Car Racing Made Easy $15.45 2011-3-1 14
Jose Jimenez 187 27th Ave Seattle WA 98122 Fishing in the Mojave,
Corn Shucking for Fun and Profit		 $13.95,
$15.05		 2011-9-1,
2012-8-1		 12,
12
Julie Johnston 10336 NE 187th St Bothell WA 98012 Fishing in the Mojave $13.95 2011-3-1 12
Lucy Lamont 175 Smokey Point Dr Lakewood WA 98409 Beautiful Birds,
Corn Shucking for Fun and Profit		 $12.45,
$15.05		 2012-2-1,
2012-7-1		 12,
24
Samantha Sanders 316 Union Ave Snohomish WA 98290 Fishing in the Mojave,
Pine Cone Computing,
Cooking Like Mad,
If Only I Could Sing,
Beautiful Birds		 $13.95,
$17.50,
$18.00,
$12.45,
$12.45		 2011-5-1,
2011-5-1,
2011-9-1,
2011-12-1,
2012-2-1		 12,
18,
12,
12,
12
Walter Wong 1073 South 323rd Street Federal Way WA 98003 If Only I Could Sing $12.45 2012-7-1 24

The next design violates 1NF because some of the columns are repeated (consider Magazine 1 Name, Magazine 2 Name, …).

An entity relationship diagram containing a non-normalized design for subscribers, magazines, and subscriptions that violates first normal form
subscriber
Subscriber Magazine 1 Magazine 2 Magazine 3 Magazine 4 Magazine 5
First Name Last Name Address City State ZIP Code Name Price Start Date Length Name Price Start Date Length Name Price Start Date Length Name Price Start Date Length Name Price Start Date Length
Albert Anderson 220 K Street Southeast Auburn WA 98002 Car Racing Made Easy $15.45 2011-3-1 14
Jose Jimenez 187 27th Ave Seattle WA 98122 Fishing in the Mojave $13.95 2011-9-1 12 Corn Shucking for Fun and Profit $15.05 2012-8-1 12
Julie Johnston 10336 NE 187th St Bothell WA 98012 Fishing in the Mojave $13.95 2011-3-1 12
Samantha Sanders 316 Union Ave Snohomish WA 98290 Fishing in the Mojave $13.95 2011-5-1 12 Pine Cone Computing $17.50 2011-5-1 18 Cooking Like Mad $18.00 2011-9-1 12 If Only I Could Sing $12.45 2011-12-1 12 Beautiful Birds $12.45 2012-2-1 12
Lucy Lamont 175 Smokey Point Dr Lakewood WA 98409 Beautiful Birds $12.45 2012-2-1 12 Corn Shucking for Fun and Profit $15.05 2012-7-1 24
Walter Wong 1073 South 323rd Street Federal Way WA 98003 If Only I Could Sing $12.45 2012-7-1 24

The following design violates 1NF because some of the columns are repeated (consider Subscriber 1 Last Name, Subscriber 2 Last Name, …).

An entity relationship diagram containing a non-normalized design for subscribers, magazines, and subscriptions that violates first normal form
magazine
Magazine Subscriber 1 Subscriber 2 Subscriber 3
Name Price Start Date Length First Name Last Name Address City State ZIP Code Start Date Length First Name Last Name Address City State ZIP Code Start Date Length First Name Last Name Address City State ZIP Code
Car Racing Made Easy $15.45 2011-3-1 14 Albert Anderson 220 K Street Southeast Auburn WA 98002
Fishing in the Mojave $13.95 2011-3-1 12 Julie Johnston 10336 NE 187th St Bothell WA 98012 2011-5-1 12 Samantha Sanders 316 Union Ave Snohomish WA 98290 2011-9-1 12 Jose Jimenez 187 27th Ave Seattle WA 98122
Beautiful Birds $12.45 2012-2-1 12 Samantha Sanders 316 Union Ave Snohomish WA 98290 2012-2-1 12 Lucy Lamont 175 Smokey Point Dr Lakewood WA 98409
Pine Cone Computing $17.50 2011-5-1 18 Samantha Sanders 316 Union Ave Snohomish WA 98290
Corn Shucking for Fun and Profit $15.05 2012-7-1 24 Lucy Lamont 175 Smokey Point Dr Lakewood WA 98409 2012-8-1 12 Jose Jimenez 187 27th Ave Seattle WA 98122
If Only I Could Sing $12.45 2012-7-1 24 Walter Wong 1073 South 323rd Street Federal Way WA 98003 2011-12-1 12 Samantha Sanders 316 Union Ave Snohomish WA 98290
Cooking Like Mad $18.00 2011-9-1 12 Samantha Sanders 316 Union Ave Snohomish WA 98290

Violates Second Normal Form

The next design doesn’t violate 1NF because all multi-valued cells and repeated columns have been removed but does violate 2NF because the subscription table contains multiple entities that should be separated. Magazine, subscription, and subscriber are each an entity and should be in separate tables.

An entity relationship diagram containing a non-normalized design for magazines, subscriptions, and subscribers that doesn’t violate first normal form but does violate second normal
subscription
Magazine Subscription Subscriber
Name Price Start Date Length First Name Last Name Address City State ZIP Code
Car Racing Made Easy $15.45 2011-3-1 14 Albert Anderson 220 K Street Southeast Auburn WA 98002
Fishing in the Mojave $13.95 2011-3-1 12 Julie Johnston 10336 NE 187th St Bothell WA 98012
Fishing in the Mojave $13.95 2011-5-1 12 Samantha Sanders 316 Union Ave Snohomish WA 98290
Pine Cone Computing $17.50 2011-5-1 18 Samantha Sanders 316 Union Ave Snohomish WA 98290
Cooking Like Mad $18.00 2011-9-1 12 Samantha Sanders 316 Union Ave Snohomish WA 98290
Fishing in the Mojave $13.95 2011-9-1 12 Jose Jimenez 187 27th Ave Seattle WA 98122
If Only I Could Sing $12.45 2011-12-1 12 Samantha Sanders 316 Union Ave Snohomish WA 98290
Beautiful Birds $12.45 2012-2-1 12 Lucy Lamont 175 Smokey Point Dr Lakewood WA 98409
Beautiful Birds $12.45 2012-2-1 12 Samantha Sanders 316 Union Ave Snohomish WA 98290
Corn Shucking for Fun and Profit $15.05 2012-7-1 24 Lucy Lamont 175 Smokey Point Dr Lakewood WA 98409
If Only I Could Sing $12.45 2012-7-1 24 Walter Wong 1073 South 323rd Street Federal Way WA 98003
Corn Shucking for Fun and Profit $15.05 2012-8-1 12 Jose Jimenez 187 27th Ave Seattle WA 98122

Normalized to Third Normal Form

The entities in the next ERD and corresponding tables do not violate any of the rules for first, second, or third normal form, so we say the design is normalized to third normal form.

An entity relationship diagram for magazines, subscriptions, and subscribers normalized to third normal form
magazine
magazineKey magazineName price
1 Fishing in the Mojave $13.95
2 Car Racing Made Easy $15.45
3 Pine Cone Computing $17.50
4 Cooking Like Mad $18.00
5 If Only I Could Sing $12.45
6 Beautiful Birds $12.45
7 Corn Shucking for Fun and Profit $15.05
subscription
subscriptionKey magazineKey subscriberKey startDate duration
1 1 1 2011-03-01 12
2 2 2 2011-03-01 14
3 6 3 2012-02-01 12
4 6 5 2012-02-01 12
5 4 3 2011-09-01 12
6 7 5 2012-07-01 24
7 7 4 2012-08-01 12
8 1 3 2011-05-01 12
9 1 4 2011-09-01 12
10 5 3 2011-12-01 12
11 5 6 2012-07-01 24
12 3 3 2011-05-01 18
subscriber
subscriberKey firstName lastName address city state ZIP
1 Julie Johnston 10336 NE 187th St Bothell WA 98012
2 Albert Anderson 220 K Street Southeast Auburn WA 98002
3 Samantha Sanders 316 Union Ave Snohomish WA 98290
4 Jose Jimenez 187 27th Ave Seattle WA 98122
5 Lucy Lamont 175 Smokey Point Dr Lakewood WA 98409
6 Walter Wong 1073 South 323rd Street Federal Way WA 98003