Datasheet

Second normal form
The rule for the second normal form dictates that you must create separate tables for sets of values that
apply to multiple records and in multiple tables, and relate these tables with foreign keys.
Starting with the Employee table, you can see that multiple employees can work at the same location, as
well as have the same job title. Therefore, following the rules of the second normal form, you need to
create a separate table for Location and relate this new table to the Employee table with a foreign key.
This provides a one-to-many relationship whereby you have one row in the Location table relating to
multiple rows in the Employee table.
You also need to create a Job Title table and relate this table to the Employee table with a foreign key.
This also provides a one-to-many relationship whereby you have one row in the Job Title table relating
to multiple rows in the Employee table.
The next table that you want to examine for the rules of the second normal form is the Manager table.
Again, you have a Location field, and multiple managers could work at the same location. Because you
have already defined a Location table, you merely need to create a foreign key field in the Manager table
pointing to the Location table.
With the addition of these new tables, your database design would now look like the one shown in
Figure 1-5.
Figure 1-5
Third normal form
The rules of the third normal form dictate that you eliminate columns that do not depend on the
primary key. Given the database design shown in Figure 1-5, you have no columns that match this
description. All columns in all tables depend on the primary key to identify each row uniquely.
Employee Table
EmployeeID
FirstName
LastName
PhoneNumber
LocationID
JobTitleID
Job Title Table
JobTitleID
JobTitle
Location Table
LocationID
Location
Manager Table
ManagerID
FirstName
LastName
PhoneNumber
LocationID
Manager Employees Table
ManagerEmployeeID
ManagerID
EmployeeID
14
Chapter 1
04_58894x ch01.qxd 10/13/05 5:54 PM Page 14