Database Reference
In-Depth Information
The conversion of an unnormalized table to first normal form requires the removal of repeating groups.
When this was first demonstrated, you merely removed the repeating group symbol and expanded the
primary key. You will recall, for example, that
Orders (OrderNum, OrderDate, (PartNum, NumOrdered) )
became
Orders (OrderNum, OrderDate, PartNum, NumOrdered)
177
The primary key was expanded to include the primary key of the original table together with the key to
the repeating group.
What if there are two or more repeating groups? The method you used earlier is inadequate for such
situations. Instead, you must place each repeating group in a separate table. Each table will contain all the
columns that make up the given repeating group, as well as the primary key of the original unnormalized
table. The primary key to each new table will be the concatenation of the primary key of the original table
and the primary key of the repeating group. For example, consider the following unnormalized table that
contains two repeating groups.
Faculty (FacultyNum, FacultyName, (StudentNum, StudentName), (CommitteeCode, CommitteeName) )
In this example, FacultyName is the name of the faculty member and StudentName is the name of the
student. The columns CommitteeCode and CommitteeName refer to the committee
s code and name. (For
example, one row in this table would have PER in the CommitteeCode column and Personnel Committee in
the CommitteeName column.) Applying this new method to create first normal form tables would produce
the following:
'
Faculty (FacultyNum, FacultyName)
FacStudent (FacultyNum, StudentNum, StudentName)
FacCommittee (FacultyNum, CommitteeCode, CommitteeName)
As you can see, this collection of tables avoids the problems with multivalued dependencies. At this
point, you have a collection of first normal form tables that you still need to convert to third normal form. By
using the above process, however, you can guarantee that the result will also be in fourth normal form.
APPLICATION TO DATABASE DESIGN
The normalization process used to convert a relation or collection of relations to an equivalent collection of
third normal form tables is a crucial part of the database design process. By following a careful and appropri-
ate normalization methodology, you need not worry about normal forms higher than third normal form.
There are three aspects concerning normalization that you need to keep in mind, however.
First, you should carefully convert tables to third normal form. Suppose the following columns exist in a
Coach relation. (The ellipsis (
…
) represents additional columns that exist but are not included in this
example.)
Coach (CoachNum, LastName, FirstName, Street, City, State, Zip, ...)
In addition to the functional dependencies that all the columns have on CoachNum, there are two other
functional dependencies. As originally designed by the United States Postal Service, Zip determines both
State and City.
Does this mean that you should replace the Coach relation with the following?
Coach (CoachNum, LastName, FirstName, Street, Zip, ...)
ZipCode (Zip, City, State)
If you are determined to ensure that every relation is in third normal form, you could replace the Coach
relation with the revised Coach relation and the new ZipCode relation, but this approach is probably unnec-
essary. If you review the list of problems normally associated with relations that are not in third normal form,
you will see that they don
t apply here. Are you likely to need to change the state in which the zip code
49428 is located? Do you need to add the fact that zip code 49401 corresponds to Allendale, Michigan, if you
have no customers who live in Allendale? In this case, the design of the original Coach relation is sufficient.
'
