Database Reference
In-Depth Information
The primary key for each entity appears above the line in the rectangle for each entity.
DepartmentNum is the primary key of the Department entity, and EmployeeNum is the
primary key of the Employee entity.
The other columns in each entity appear below the line within each rectangle.
The letters AK, SK, and FK appear in parentheses following the alternate key, secondary key,
and foreign key, respectively, in the Employee entity. (The Department entity does not have an
alternate, secondary, or foreign key.)
For each foreign key, there is a line leading from the rectangle that corresponds to the table
being identified to the rectangle that corresponds to the table containing the foreign key. The
dot at the end of the line indicates the
189
part of the one-to-many relationship between the
Department and Employee entities. (In Figure 6-2, one department is related to many employ-
ees, so the dot is at the end of the line connected to the Employee entity.)
“
many
”
When you use an E-R diagram to represent a database, it visually illustrates all the information listed in
the DBDL. Thus, you would not also need to include the DBDL version of the design. There are other styles,
however, that do not include such information within the diagram. In that case, you should represent the
design with both the diagram and the DBDL.
Merge the Result into the Design
As soon as you have completed Steps 1 through 3 for a given user view, you can merge the results into the
cumulative design. If the view on which you have been working is the first user view, the cumulative design
will be identical to the design for the first user. Otherwise, you merge all the tables for this user with those
tables that are currently in the cumulative design.
Next, you combine tables that have the same primary key to form a new table. The new table has the
same primary key as those tables you have combined. The new table also contains all the columns from both
tables. In the case of duplicate columns, you remove all but one copy of the column. For example, if the
cumulative design already contains the following table:
Employee (EmployeeNum, LastName, FirstName, WageRate, SocSecNum, DepartmentNum)
and the user view you just completed contains the following table:
Employee (EmployeeNum, LastName, FirstName, Street, City, State, Zip)
you would combine the two tables because they have the same primary key. All the columns from both
tables are in the new table, but without any duplicate columns. Thus, LastName and FirstName appear only
once, even though they are in each table. The end result is as follows:
Employee (EmployeeNum, LastName, FirstName, WageRate, SocSecNum, DepartmentNum, Street, City,
State, Zip)
If necessary, you could reorder the columns at this point. For example, you might move the Street, City,
State, and Zip columns to follow the FirstName column, which is the more traditional arrangement of this
type of data. This change would give the following:
Employee (EmployeeNum, LastName, FirstName, Street, City, State, Zip, WageRate, SocSecNum,
DepartmentNum)
At this point, you need to check the new design to ensure that it is still in third normal form. If it is not,
you should convert it to third normal form before proceeding.
Figure 6-3 summarizes the process that is repeated for each user view until all user views have been
examined. At that point, the design is reviewed to resolve any problems that may remain and to ensure that it
can meet the needs of all individual users. After all user view requirements have been satisfied, the
information-level design is considered to be complete.
