An Introduction to Database Normalization - The Three Normal Forms (
Page 4 of 5 )
The process towards
database normalization progressing through a series of steps, typically known as
Normal Forms. For purposes of illustration, assume that a school system used a
table containing these attributes to store its information. As you can see,
employing this strategy results in a lookup mechanism that essentially defeats
the purpose of using a database; it’s just a group of records. In short, this
table is in dire need of a normalization overhaul. In this section, I’ll
implement the rules specified by the first three Normal Form rules to reorganize
this school’s table structure.
First Normal Form
Converting a database to the first normal form is
rather simple. This first rule calls for the elimination of repeating groups of
data through the creation of separate tables of related data. Obviously, the
original table contains several sets of repeating groups of data, namely
classID, className, classTime, classLocation, professorID, professorName. Each
attribute is repeated three times, allowing for each student to take three
classes. However, what if the student takes more than three classes? This, and
other restrictions on this table should be obvious.
Therefore, let’s
break this mammoth table down into several smaller tables. The first table
contains solely student information (Student):
|
studentID |
studentName |
Major
|
college |
collegeLocation |
The second table
contains solely class information (Class):
|
studentID |
classID |
className |
The third
table contains solely professor information (Professor):
|
professorID |
professorName |
Second Normal Form
Once you have separated the data into their
respective tables, you can begin concentrating upon the rule of Second Normal
Form; that is, the elimination of redundant data. Referring back to the Class
table, typical data stored within might look like:
|
studentID |
classID |
className |
|
134-56-7890 |
M148 |
Math 148 |
|
123-45-7894 |
P113 |
Physics 113 |
|
534-98-9009 |
H151 |
History 151 |
|
134-56-7890 |
H151 |
History 151 |
While this table
structure is certainly improved over the original, notice that there is still
room for improvement. In this case, the className attribute is being repeated.
With 60,000 students stored in this table, performing an update to reflect a
recent change in a course name could be somewhat of a problem. Therefore, I’ll
create a separate table that contains classID to className mappings
(ClassIdentity):
|
classID
|
className
|
|
M148 |
Math 148 |
|
P113 |
Physics 113 |
|
H151 |
History 151 |
The updated Class
table would then be simply:
|
studentID |
classID |
|
134-56-7890 |
M148 |
|
123-45-7894 |
P113 |
|
534-98-9009 |
H151 |
|
134-56-7890 |
H151 |
Revisiting the need to
update a recently changed course name, all that it would take is the simple
update of one row in the ClassIdentity table! Of course, substantial savings in
disk space would also result, due to this elimination of redundancy.
Third Normal Form
Continuing on the quest for complete normalization of
the school system database, the next step in the process would be to satisfy the
rule of the Third Normal Form. This rule seeks to eliminate all attributes from
a table that are not directly dependent upon the primary key. In the case of the
Student table, the college and collegeLocation attributes are less dependent
upon the studentID than they are on the major attribute. Therefore, I’ll create
a new table that relates the major, college and collegeLocation
information:
|
major |
college |
collegeLocation |
The revised
Student table would then look like:
|
studentID |
studentName |
Major |
Although for most cases
these three Normal Forms sufficiently satisfy the requirements set for proper
database normalization, there are still other Forms that go beyond what rules
have been set thus far. However, these are out of the scope of this article. If
you would be interested in learning more about these Forms, there have been a
number of books written on the subject. Check out your local bookstore for more
information.