Databases Reference
In-Depth Information
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 cumula-
tive 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)
184
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 are satisfied, the information-
level design is considered to be complete.
User view
Step 1.
Represent the user view as a collection of tables (relations).
Collection of tables
Step 2.
Normalize these tables.
Collection of
normalized tables
Step 3.
Represent all keys.
Collection of
normalized tables with
keys represented
Step 4.
Merge the result of the previous steps into the
cumulative design.
New cumulative design
Old cumulative design
FIGURE 6-3
Information-level design method
