Database Reference
In-Depth Information
Step 2. Determine the primary key for each table. In this step, you can add one or more columns
depending on how many columns are required for the primary key. You will add additional columns later.
Even though you have yet to determine the columns in the table, you can usually determine the primary key.
For example, the primary key in an Employee table will probably be EmployeeNum, and the primary key in a
Department table will probably be DepartmentNum.
The primary key is the unique identifier, so the essential question is this: What does it take to uniquely
identify an employee or a department? Even if you are trying to automate a previously designed manual sys-
tem, you usually can find a unique identifier in that system. If no unique identifier is available, you will need
to assign one. For example, in a manual system, customers may not have been assigned numbers because the
customer base was small and the organization did not require or use customer numbers. Because the organi-
zation is computerizing its records, however, now is a good time to assign customer numbers to become the
unique identifiers you are seeking.
After creating unique identifiers, you add these primary keys to what you have written already. At this
point, you will have something like the following:
185
Department (DepartmentNum,
Employee (EmployeeNum,
Now you have the name of the table and the primary key, but that is all. In later steps, you will add the
other columns.
Step 3. Determine the properties for each entity. You can look at the user requirements and then deter-
mine the other required properties of each entity. These properties, along with the primary key identified in
Step 2, will become columns in the appropriate tables. For example, an Employee entity may require columns
for LastName, FirstName, Street, City, State, Zip, WageRate, and SocSecNum (Social Security number). The
Department entity may require columns for Name (department name) and Location (department location).
Adding these columns to what is already in place produces the following:
Department (DepartmentNum, Name, Location
Employee (EmployeeNum, LastName, FirstName, Street, City,
State, Zip, WageRate, SocSecNum
Step 4. Determine relationships between the entities. The basic relationships are one-to-many, many-
to-many, and one-to-one. You will see how to handle each type of relationship next.
To create a one-to-many relationship, include the primary key of the
“
one
”
table as a foreign key in the
“
table. For example, assume each employee works in a single department but a department can have
many employees. Thus, one department is related to many employees. In this case, you would include the
primary key of the Department table (the
many
”
“
one
”
part) as a foreign key in the Employee table (the
“
many
”
part). The tables would now look like this:
Department (DepartmentNum, Name, Location)
Employee (EmployeeNum, LastName, FirstName, Street, City,
State, Zip, WageRate, SocSecNum, DepartmentNum)
You create a many-to-many relationship by creating a new table whose primary key is the combination of
the primary keys of the original tables. Assume each employee can work in multiple departments and each
department can have many employees. In this case, you would create a new table whose primary key is the
combination of EmployeeNum and DepartmentNum. Because the new table represents the fact that an
employee works in a department, you might choose to call it WorksIn. Another method is to use a name that
combines the names of the two tables being related. Using the second approach, the new table
s name could
be DepartmentEmployee or EmployeeDepartment. After creating the new table, the collection of tables is as
follows:
'
Department (DepartmentNum, Name, Location)
Employee (EmployeeNum, LastName, FirstName, Street, City,
State, Zip, WageRate, SocSecNum)
WorksIn (EmployeeNum, DepartmentNum)
In this design, there is a one-to-many relationship between the Department and WorksIn tables and a
one-to-many relationship between the Employee and WorksIn tables. By creating the WorksIn table, which
includes foreign keys from the Department and Employee tables, you have created a new table to implement
