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

involved) is clumsy, potentially error prone, and expensive in terms of performance.
While the two files have the benefit of holding data non-redundantly, what is lacking
is a good level of data integration. That is, it is overly difficult to find and retrieve
pieces of data in the two files that are related to each other. For example, customer
number 1525 and salesperson name Carlyle in the two files in Figure 3.7 are related
to each other by virtue of the fact that the two records they are in both include
a reference to salesperson number 361. Yet, as shown above, ultimately finding
the salesperson name Carlyle by starting with the customer number 1525 is an
unacceptably laborious process.
A fair question to ask is, if we knew that data integration was important in
this application environment and if we knew that there would be a frequent need to
find the name of the salesperson responsible for a particular customer, why were
the files structured as in Figure 3.7 in the first place? An alternative arrangement is
shown in Figure 3.8. The single file in Figure 3.8 combines the data in the two files
of Figure 3.7. Also, the Customer Number field values of both are identical.
The file in Figure 3.8 was created by merging the salesperson data from
Figure 3.7a into the records of Figure 3.7b, based on corresponding salesperson
numbers. As a result, notice that the number of records in the file in Figure 3.8
is identical to the number of records in the Customer file of Figure 3.7b. This is
actually a result of the ''direction'' of the one-to-many relationship in which each
salesperson can be associated with several customers. The data was ''integrated''
in this merge operation. Notice, for example, that in Figure 3.7b, the record
for customer number 1525 is associated with salesperson number 361. In turn,
in Figure 3.7a, the record for salesperson number 361 is shown to have the name
Carlyle. Those two records were merged, based on the common salesperson number,
into the record for customer number 1525 in Figure 3.8. (Notice, by the way, that the
Salesperson Number field appears twice in Figure 3.8 because it appeared in each
of the files of Figure 3.7. The field values in each of those two fields are identical
in each record in the file in Figure 3.8, which must be the case since it was on those
identical values that the record merge that created the file in Figure 3.8 was based.
That being the case, certainly one of the two Salesperson Number fields in the file
in Figure 3.8 could be deleted without any loss of information.)
The file in Figure 3.8 is certainly well integrated . Finding the name of
the salesperson who is responsible for customer number 1525 now requires a
single record access of the record for customer number 1525. The salesperson
name, Carlyle, is right there in that record. This appears to be the solution to the

Customer Customer Salesperson Salesperson Salesperson Commission Year

Number Name Number HQ City Number Name Percentage of Hire

0121 Main St. Hardware 137 New York 137 Baker 10 1995

0839 Jane's Stores 186 Chicago 186 Adams 15 2001

0933 ABC Home Stores 137 Los Angeles 137 Baker 10 1995

1047 Acme Hardware Store 137 Los Angeles 137 Baker 10 1995

1525 Fred's Tool Stores 361 Atlanta 361 Carlyle 20 2001

1700 XYZ Stores 361 Washington 361 Carlyle 20 2001

1826 City Hardware 137 New York 137 Baker 10 1995

2198 Western Hardware 204 New York 204 Dickens 10 1998

FIGURE 3.8
General Hardware Company combined file

2267 Central Stores 186 New York 186 Adams 15 2001