The Three Normal Forms - MySQL

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.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus