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

(a) Salesperson file

Salesperson Salesperson Commission Year Customer

Number Name Percentage of Hire Numbers

137 Baker 10 1995 0121, 0933, 1047, 1826

186 Adams 15 2001 0839, 2267

204 Dickens 10 1998 2198

361 Carlyle 20 2001 1525, 1700

(b) Customer file

Customer Customer

Number Name HQ City

0121 Main St. Hardware New York

0839 Jane's Stores Chicago

0933 ABC Home Stores Los Angeles

1047 Acme Hardware Store Los Angeles

1525 Fred's Tool Stores Atlanta

1700 XYZ Stores Washington

FIGURE3.9
General Hardware Company combined
files: One-to-many relationship horizontal
variation

1826 City Hardware New York

2198 Western Hardware New York

2267 Central Stores New York

fields. While this arrangement does represent the one-to-many relationship, it is
unacceptable for two reasons. One is that the record length could be highly variable
depending on how many customers a particular salesperson is responsible for. This
can be tricky from a space management point of view. If a new customer is added
to a salesperson's record, the new larger size of the record may preclude its being
stored in the same place on the disk as it came from, but putting it somewhere else
may cause performance problems in future retrievals. The other reason is that once
a given salesperson record is retrieved, the person or program that retrieved it would
have a difficult time going through all the associated customer numbers looking for
the one desired. With simple files like these, the normal expectation is that there
will be one value of each field type in each record (e.g. one salesperson number,
one salesperson name, and so on). In the arrangement in Figure 3.9, the end-user
or supporting software would have to deal with a list of values, i.e. of customer
numbers, upon retrieving a salesperson record. This would be an unacceptably
complex process.
Figure 3.10 shows a ''vertical'' solution to the problem. In a single file, each
salesperson record is immediately followed by the records for all of the customers
for which the salesperson is responsible. While this does preserve the one-to-many
relationship, the complexities involved in a system that has to manage multiple
record types in a single file make this solution unacceptable, too.
A database management system must be able to handle all of the various
unary, binary, and ternary relationships in a logical and efficient way that does
not introduce data redundancy or interfere with data integration. The database
management system approaches that are in use today all satisfy this requirement. In