Database Reference
In-Depth Information
a many-to-many relationship. The one-to-many relationship between each of the original tables with the new
table creates the many-to-many relationship between the two original tables.
In some situations, no other columns will be required in the new table. The other columns in the
WorksIn table would be those columns that depend on both the employee and the department, if such
columns existed. One possibility, for example, would be the date the department hired the employee
because it depends on both the employee and the department.
If each employee works in a single department and each department has only one employee, the relation-
ship between employees and departments is one-to-one. (In practice, such relationships are rare.) The sim-
plest way to implement a one-to-one relationship is to treat it as a one-to-many relationship. Which table is
the
186
part? Sometimes looking ahead helps. For
example, you might ask this question: If the relationship changes in the future, is it more likely that one
employee will work in many departments or that one department will hire several employees rather than just
one? If your research determines that it is more likely that a department will hire more than one employee,
you would make the Employee table the
“
one
”
part of the relationship, and which table is the
“
many
”
part of the relationship. If both situations might happen, you
could treat the relationship as many-to-many. If neither situation is likely to occur, you could arbitrarily
choose the
“
many
”
“
many
”
part of the relationship.
Normalize the Tables
After establishing the relationships between the entities, the next task is to normalize each table, with the
target being third normal form. (The target is actually fourth normal form, but careful planning in the early
phases of the normalization process usually rules out the need to consider fourth normal form.)
Identify All Keys
For each table, you must identify the primary key and any alternate keys, secondary keys, and foreign keys.
In the database containing information about employees and departments, you already determined the pri-
mary keys for each table in an earlier step.
Recall that an alternate key is a column or collection of columns that could have been chosen as a pri-
mary key but was not. It is not common to have alternate keys; if they do exist and the system must enforce
their uniqueness, however, you should note them. You usually implement this restriction by creating a unique
index on the field. If there are any secondary keys (columns that are of interest strictly for the purpose of
retrieval), you should represent them at this point. If a user were to indicate, for example, that rapidly
retrieving an employee record based on his or her last name was important, you would designate the
LastName column as a secondary key. You usually create a nonunique index for each secondary key.
In many ways, the foreign key is the most important key because it is through foreign keys that you cre-
ate relationships between tables and enforce certain types of integrity constraints in a database. Remember
that a foreign key is a column (or collection of columns) in one table that is required to match the value of
the primary key for some row in another table or is required to be null. (This property is called referential
integrity.) Consider, for example, the following tables:
Department (DepartmentNum, Name, Location)
Employee (EmployeeNum, LastName, FirstName, Street, City,
State, Zip, WageRate, SocSecNum, DepartmentNum)
As before, the DepartmentNum column in the Employee table indicates the department in which the
employee works. In this case, you say that the DepartmentNum column in the Employee table is a foreign key
that identifies Department. Thus, the number in this column on any row in the Employee table must be a
department number that is already in the database or the value must be set to null. (Null indicates that, for
whatever reason, the employee is not assigned to a department.)
Types of Primary Keys
There are three types of primary keys that you can use in your database design. A natural key (also called a
logical key or an intelligent key) is a primary key that consists of a column that uniquely identifies an entity,
such as a person
'
s Social Security number, a book
'
s ISBN (International Standard Book Number), a product
'
s
UPC (Universal Product Code), or a vehicle
s VIN (Vehicle Identification Number). These characteristics are
inherent to the entity and visible to users. If a natural key exists for an entity, you usually can select it as the
primary key.
'
