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 data 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

The following ERD and corresponding data table violate first normal form, because there are lists in the tracks column. The lists in the tracks column are problematic 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 next ERD and corresponding data table also violate first normal form because there is a list 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

The next ERD and corresponding data table are normalized to first normal form because there are no lists within cells and no lists of columns and each table has a primary key. However, they violate second normal form because the track table contains sub-themes. Album and artist are sub-themes within the track table and should be separated.

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
album
albumKey albumTitle yearReleased label trackTitle duration artist artistCountry
ABRD Abbey Road 1969 Apple Here Comes the Sun 3:06 Beatles UK
ABRD Abbey Road 1969 Apple Octopus’s Garden 2:48 Beatles UK
ABRD Abbey Road 1969 Apple Something 2:59 Beatles UK
FRLS Fearless 2008 Big Machine Records Love Story 3:57 Taylor Swift US
FRLS Fearless 2008 Big Machine Records White Horse 3:55 Taylor Swift US
FRLS Fearless 2008 Big Machine Records You Belong with Me 3:52 Taylor Swift US
FRLS Fearless 2008 Big Machine Records Fifteen 4:55 Taylor Swift US

Violates Third Normal Form

In the next design, the album table is normalized to third normal form, meaning it doesn’t violate the rules for first, second, or third normal forms. However, the track table violates the rules for third normal form. The track table doesn’t violate the rules for first or second normal form but does violate the rules for third normal form because the artistCountry column is not determined by the primary key (trackKey column) but is determined by the artist column.

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
albumKey albumTitle yearReleased label
ABRD Abbey Road 1969 Apple
FRLS Fearless 2008 Big Machine Records
track
trackKey trackTitle duration artist artistCountry albumKey
1 Here Comes the Sun 3:06 Beatles UK ABRD
2 Something 2:48 Beatles UK ABRD
3 Octopus’s Garden 2:59 Beatles UK ABRD
4 Love Story 3:57 Taylor Swift US FRLS
5 White Horse 3:55 Taylor Swift US FRLS
6 You Belong with Me 3:52 Taylor Swift US FRLS
7 Fifteen 4:55 Taylor Swift US FRLS

Normalized to Third Normal Form

The entities in the next ERD and corresponding data 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 data 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 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 first normal form 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 first normal form 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 first normal form because all multi-valued cells and repeated columns have been removed but does violate second normal form because the subscription table contains sub-themes that should be separated. Magazine, subscription, and subscriber are each sub-themes 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 data 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