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.
Next: What's Next >>
More MySQL Articles
More By W.J. Gilmore
/ 114 
