Home arrow MySQL arrow Page 4 - An Introduction to Database Normalization

The Three Normal Forms - MySQL

A database can be great fun, right? Yes, of course!There are though, a couple things that can ruin allthat hard work and effort you put into your efficient little database. Today we discuss how to keep that beloved bin of data from going bad on you: databasenormalization.

TABLE OF CONTENTS:
  1. An Introduction to Database Normalization
  2. Preliminary Definitions
  3. So Why Normalize?
  4. The Three Normal Forms
  5. What's Next
By: W.J. Gilmore
Rating: starstarstarstarstar / 234
November 27, 2000

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: