Database Reference
In-Depth Information
Adding columns for the properties of each of these entities would yield this:
Rep (RepNum, LastName, FirstName
Customer (CustomerNum, CustomerName, Street, City, State,
Zip, Balance, CreditLimit
Finally, you deal with the relationship: one sales rep is related to many customers. To implement this
one-to-many relationship, include the key of the
table. In this
case, you would include the RepNum column in the Customer table. Thus, you would have the following:
“
one
”
table as a foreign key in the
“
many
”
192
Rep (RepNum, LastName, FirstName)
Customer (CustomerNum, CustomerName, Street, City, State,
Zip, Balance, CreditLimit, RepNum)
Both tables are in third normal form, so you can move on to representing the keys. Before doing that,
however, consider another approach that you could have used to determine the tables.
Suppose you did not realize that there were really two entities, and you created only a single table for
customers. You would begin by listing the table as follows:
Customer (
Adding the unique identifier as the primary key would give this table:
Customer (CustomerNum,
Finally, adding the other properties as additional columns would yield the following:
Customer (CustomerNum, CustomerName, Street, City, State,
Zip, Balance, CreditLimit, RepNum, LastName, FirstName)
A problem occurs, however, when you examine the functional dependencies that exist in the Customer
entity. The CustomerNum column determines all the other columns, as it should. However, the RepNum col-
umn determines the LastName and FirstName columns, but RepNum is not an alternate key. This table is in
second normal form because no column depends on a portion of the primary key, but it is not in third nor-
mal form. Converting the table to third normal form produces the following two tables:
Customer (CustomerNum, CustomerName, Street, City, State,
Zip, Balance, CreditLimit, RepNum)
Rep (RepNum, LastName, FirstName)
Notice that these are the same tables you determined with the first approach
—
it just took a little longer
to get there.
Besides the obvious primary keys, CustomerNum for Customer and RepNum for Rep, the Customer table
now contains a foreign key, RepNum. There are no alternate keys, nor did the requirements state anything
that would require a secondary key. If there were a requirement to retrieve the customer based on the cus-
tomer
s name, for example, you would probably choose to make CustomerName a secondary key.
The next step is to merge these two tables into the cumulative design. You could now represent the Rep
table in DBDL in preparation for merging these two tables into the existing cumulative design. Looking ahead,
however, you see that because this table has the same primary key as the Rep table from the first user view,
you can merge the two tables to form a single table that has the common column RepNum as its primary key
and that contains all the other columns from both tables without duplication. For this second user view, the
only columns in the Rep table other than the primary key are LastName and FirstName. These columns were
already in the Rep table from the first user view that you added to the cumulative design. Thus, you do not
need to add anything to the Rep table that already appears in the cumulative design. The cumulative design
now contains the Rep and Customer tables shown in Figure 6-5.
'
