THE DATABASE MANAGEMENT SYSTEM CONCEPT - Fundamentals of Database Management Systems - page 53

to be answered regarding the Salesperson Number field appearing in this file. First,
why is it there? After all, it seems already to have a good home as the unique
identifying field of the Salesperson file. The Salesperson Number field appears in
the Customer file to record which salesperson is responsible for a given customer
account. In fact, there is a one-to-many relationship between salespersons and
customers. A salesperson can and generally does have several customer accounts,
while each customer is serviced by only one General Hardware salesperson. The
second question involves the data in the Salesperson Number field in the Customer
file. For example, salesperson number 137 appears in four of the records (plus once
in the first record of the Salesperson file!). Does this constitute data redundancy?
The answer is no. For data to be redundant (and examples of data redundancy will be
coming up shortly), the same fact about the business environment must be recorded
more than once. The appearance of salesperson number 137 in the first record of
the Salesperson file establishes 137 as the identifier of one of the salespersons.
The appearance of salesperson number 137 in the first record of the Customer file
indicates that salesperson number 137 is responsible for customer number 0121. This
is a different fact about the business environment. The appearance of salesperson
number 137 in the third record of the Customer file indicates that salesperson
number 137 is responsible for customer number 0933. This is yet another distinct
fact about the business environment. And so on through the other appearances of
salesperson number 137 in the Customer file.
Retrieving data from each of the files of Figure 3.7 individually is
straightforward and can be done on a direct basis if the files are set-up for direct
access. Thus, if there is a requirement to find the name or commission percentage
or year of hire of salesperson number 204, it can be satisfied by retrieving the
record for salesperson number 204 in the Salesperson file. Similarly, if there is a
requirement to find the name or responsible salesperson (by salesperson number!)
or headquarters city of customer number 1525, we simply retrieve the record for
customer number 1525 in the Customer file.
But, what if there is a requirement to find the name of the salesperson
responsible for a particular customer account, say for customer number 1525? Can
this requirement be satisfied by retrieving data from only one of the two files of
Figure 3.7? No, it cannot! The information about which salesperson is responsible
for which customers is recorded only in the Customer file and the salesperson
names are recorded only in the Salesperson file. Thus, finding the salesperson
name will be an exercise in data integration. In order to find the name of the
salesperson responsible for a particular customer, first the record for the customer
in the Customer file would have to be retrieved. Then, using the salesperson number
found in that record, the correct salesperson record can be retrieved from the
Salesperson file to find the salesperson name. For example, if there is a need to
find the name of the salesperson responsible for customer number 1525, the first
operation would be to retrieve the record for customer number 1525 in the Customer
file. As shown in Figure 3.7b, this would yield salesperson number 361 as the
number of the responsible salesperson. Then, accessing the record for salesperson
361 in the Salesperson file in Figure 3.7a determines that the name of the salesperson
responsible for customer 1525 is Carlyle. While it's true that the data in the record
in the Salesperson file and the data in the record in the Customer file have been
integrated, the data integration process has been awfully laborious.
This kind of custom-made, multicommand, multifile access (which, by the
way, could easily require more than two files, depending on the query and the files