PHYSICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 229

CUSTOMER EMPLOYEE

Customer
Employee

F I G U R E 8.26
Creating a new primary key attribute to
replace a multiattribute primary key

Customer Employee Employee

Number Number Number Name Title

numbers for these people that will be unique across all of the customer companies .
Then, the current two-attribute primary key of the CUSTOMER EMPLOYEE table
can be replaced by this one new attribute, as shown in Figure 8.26. If the Customer
Number, Employee Number combination had been placed in other tables in the
database as a foreign key (it wasn't), then the two-attribute combination would be
replaced by this new single attribute, too. Notice that Customer Number is still
necessary as a foreign key because that's how we know which customer company
a person works for. Arguably, the old Employee Number attribute may still be
required because that is still their employer's internal identifier for them.

Storing Derived Data Some queries require performing calculations on the data in
the database and returning the calculated values as the answers. If these same values
have to be calculated over and over again, perhaps by one person or perhaps by
many people, then it might make sense to calculate them once and store them in the
database. Technically, this is a form of data redundancy, although a rather subtle
form. If the ''raw'' data is ever updated without the stored, calculated values being
updated as well, the accuracy or integrity of the database will be compromised.
To illustrate this point, let's add another attribute to General Hardware's
CUSTOMER table. This attribute, called Annual Purchases in Figure 8.27a, is the
expected amount of merchandise, in dollars, that a customer will purchase from
General Hardware in a year. Remember that there is a one-to-many relationship
from salespersons to customers, with each salesperson being responsible for several

CUSTOMER

Customer Customer Salesperson Annual

Number Name Numbe r HQ City Purchases

a. Annual Purchases attribute added to the CUSTOMER table.

SALESPERSON

Salesperson Salesperson Commission Year Office Total Annual

Number Name Percentage of Hire Number Customer Purchases

CUSTOMER

Customer Customer Salesperson Annual

Number Name Numbe r HQ City Purchases

b. Total Annual Customer Purchases attribute added to the SALESPERSON table as
derived data.

F I G U R E 8.27
Adding derived data