Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

to understand smaller, more tangible objects and the simple relationships they share with all the other

smaller objects. Needless to say, a better understanding of how a database works leads to a better

utilization of your assets.

Second Normal Form

The rule of Second Normal Form states that all partial dependencies must be eliminated and separated into
their own tables. A partial dependency is a term to describe data that doesn't rely on the table key to
uniquely identify it. In the sample database, the order information is in every record. It would be simpler to
use just the order number. The rest of the information could reside in its own table. After breaking out the
order information, your schema would resemble Table 5.4.
Table 5.4 Eliminating Partial Dependencies—Second Normal Form

Customers Products Orders

Customer_ID Product_ID Order_Number

Product_ID Order_Date Product_Name

Order_Number Product_Cost Order_Quantity

Last_Name Product_Picture (^)
First_Name
Address (^)
Shipper_Name
Again, by arranging the schema in this way, you have reflected the real world in your database. You
would have to make some changes for your business rules to be applicable, but for illustrating
normalization, this is okay.
By now you should be noticing some things. The table that was once hard to read and understand is
now making more sense. Relationships between the information that is going to be stored is clearer and
easier to understand. Things appear to be more logical. These are some of the advantages to
normalizing a database.
One of the major disadvantages of normalization is the time it takes to do. Most people are busy
enough, and to spend time making sure their data is normalized when it works just fine is perceived as a
waste of time. This is not so. You will spend way more time fixing a broken, non-normalized database
than you would a normalized, well-designed database.
By achieving the Second Normal Form, you enjoy some of the advantages of a relational database. For
example, you can now add new columns to the Customers table without affecting the Products or the
Orders tables. The same applies to the other tables. Getting to this level of normalcy allows data to fall
naturally into the bounds for which it was intended.
After you have reached the level of Second Normal Form, most of the logic problems are taken care of.
You can insert a record without excess data in most tables. Looking closer at the Customers table,
there is a Shipper_Name column. This column is not dependant on the customer. The next level of
normalization will explain how to clear this up.

Third Normal Form

The rule of Third Normal Form is to eliminate and separate any data that is not a key. This column must
depend on the key for its value. All values must be uniquely identified by the key. In the sample database,
the Customers table contains the Shipper_Name column. The Shipper_Name is not uniquely identified
by the key. You could separate this data from the current table and put it into its own table. Table 5.5 shows
the resulting database schema:
Table 5.5 Eliminating Non-Key Data for Third Normal Form

Customers Products OrderMaster OrderDetail Shippe

Customer_ID Product_ID Order_Number Order_Detail_ID Shipp

Product_ID Product_Name Order_Date Order_Number Shipp

Order_Number Product_Cost Order_Quantity Order_Date (^)
Shipper_ID Product_Picture Order_Quantity (^)
Last_Name (^)
First_Name (^)