Datasheet

❑ The first normal form eliminates repeating groups of data in a table. You create a separate table
for each set of related data and identify each table with a primary key, which uniquely identifies
each row of data.
❑ The second normal form creates separate tables for sets of values that apply to multiple records,
and relates these tables with foreign keys.
❑ The third normal form eliminates columns that do not depend on the primary key.
First normal form
You want to apply the rules of normalization to your sample database design, shown previously. In the
first normal form, you need to eliminate repeating groups of data, and create separate tables for each set
of related data. You must also identify a primary key for each table.
The Manager Employees table contains repeating groups of data so this table is a prime candidate for
the first normal form. This table already provides a relationship between a manager and employees, but
you need to eliminate the repeating groups of data (for example Employee1, Employee2). You’ll remove
the four individual Employee fields in this table and replace them with a single Employee field. This
table will then provide a one-to-many relationship — one manager to many employees.
All tables must have a primary key assigned, as shown in Figure 1-4. A primary key will uniquely
identify each record contained in a table. Notice that the primary keys shown in Figure 1-4 contain a
prefix of the table name and contain a suffix of ID. This naming convention will help identify all primary
and foreign keys and which table they belong to. Of course, you can use any naming convention that
you like, but find one that works well for you and use it consistently in your database design.
Notice that the Manager Employees table contains a primary key for itself, which will uniquely identify
each record contained in this table. It also contains the primary keys from the Manager and Employee
tables. These keys, as used in this table, are known as foreign keys, as a table may contain only one
primary key.
Let’s look at how you can identify the primary and foreign keys in a table using the naming convention
that has been incorporated here. The Manager Employees table contains a field called ManagerEmployeeID.
Because this key contains the name of the table and a suffix of ID, you know that this is the primary key
for this table. Likewise, you see two other fields in this table containing a suffix of ID. By looking at the
names of these fields you can surmise that the field ManagerID is a foreign key to the Manager table, and
that the field EmployeeID is a foreign key to the Employee table.
Figure 1-4
Employee Table
EmployeeID
FirstName
LastName
PhoneNumber
Location
JobTitle
Manager Table
ManagerID
FirstName
LastName
PhoneNumber
Location
Manager Employees Table
ManagerEmployeeID
ManagerID
EmployeeID
13
Databases
04_58894x ch01.qxd 10/13/05 5:54 PM Page 13