Relational Databases: Introduction to Normalization
Database schema normalization is a database design technique. Normalization is a step-by-step procedure
for examining a database design and detecting any entity types (which are modelled as tables) that we may
have missed.
Consider the following purchase order document:
PO#: 12356
Date: 05/30/91
Vendor#: 1245
Item# Qty. Description Color# Color Price
123 12 Hammers 3 Black 4.34
236 2 Squeegees 4 Blue 1.21
238 4 Squeegees 3 Black 1.21
We can model the document directly as a single table:
PO ( PO#, Vendor#, Date, Item1#, Qty1, Desc1, Color#1, Color1, Price1,
Item2#, Qty2, Desc2, Color#2, Color2, Price2,
Item3#, Qty3, Desc3, Color#3, Color3, Price3 ,
...
ItemN#, QtyN, DescN, Color#N, ColorN, PriceN )
This database (comprised of the single table above) is considered to be in unnormalized form, because it
includes a repeating group.
This table specification has undesirable characteristics in several respects. First, we do not know how
many items to allow for. Second, it is difficult to devise queries to manipulate item information as
information on a particular item might appear in any one of N different sets of information. A query to
search for the quantity of all of a particular item which we have on order might look like:
SELECT FROM PO ITEM1#,QTY1,ITEM2#,QTY2,...,ITEMN#,QTYN
WHERE ITEM1#=234 OR ITEM2#=234...OR ITEMN#=234;
This is unwieldy, and it leads us to consider an alternative design for the table.
An alternative design removes the repeating group :
PO ( PO#, Item#, Vendor#, Date, Qty, Desc, Color#, Color, Price )
Now it is easy to devise queries which involve the selection of lines from the invoice. This table is
considered to be in First Normal Form because it contains no repeating groups.
Examine the database (which has only one table) as it looks with data:
ISM 4212 Relational Databases: Introduction to Normalization 12/02/20 1
