THE RELATIONAL DATABASE MODEL: ADDITIONAL CONCEPTS - Fundamentals of Database Management Systems - page 151

(a) SALESPERSON relation

Salesperson Salesperson Commission Year

Number Name Percentage of Hire

137 Baker 10 1995

186 Adams 15 2001

204 Dickens 10 1998

361 Carlyle 20 2001

(b) CUSTOMER relation

Customer Customer Salesperson

Number Name Numbe r HQ City

0121 Main St. Hardware 137 New York

0839 Jane's Stores 186 Chicago

0933 ABC Home Stores 137 Los Angeles

1047 Acme Hardware Store 137 Los Angeles

1525 Fred's Tool Stores 361 Atlanta

1700 XYZ Stores 361 Washington

1826 City Hardware 137 New York

F IGURE 6.11
General Hardware Company
SALESPERSON and CUSTOMER relations

2198 Western Hardware 204 New York

2267 Central Stores 186 New York

Similar referential integrity arguments can be made for the record insertion
and update operations, but the issue of whether the exposure is on the '' one side''
or the '' many side'' of the one-to-many relationship changes! Again, in the case
of deletion, the problem occurred when a record was deleted on the '' one side''
of the one-to-many relationship. But, for insertion, if a new salesperson record is
inserted into the Salesperson relation, i.e. a new record is inserted into the '' one
side'' of the one-to-many relationship, there is no problem. All it means is that a
new salesperson has joined the company but, as yet, has no customer responsibility.
On the other hand, if a new customer record is inserted into the CUSTOMER
relation, i.e. a new record is inserted into the '' many side'' of the one-to-many
relationship, and it happens to include a salesperson number that does not have a
match in the SALESPERSON relation, that would cause the same kind of problem
as the deletion example above. Similarly, the update issue would concern updating
a foreign key value, i.e. a salesperson number in the CUSTOMER relation with a
new salesperson number that has no match in the SALESPERSON relation.
The early relational DBMSs did not provide any control mechanisms for
referential integrity. Programmers and users were on their own to keep track of
it and this upset many people. This was particularly the case because referential
integrity issues in the older hierarchical and network DBMSs were more naturally
controlled by the nature of the hierarchical and network data structures on which they
were based, at the expense of some flexibility in database design. Modern relational
DBMS's provide sophisticated control mechanisms for referential integrity with
so-called '' delete rules ,'' '' insert rules ,'' and '' update rules .'' These rules are
specified between pairs of relations. We will take a look at the three most common
delete rules, ''restrict,'' ''cascade,'' and ''set-to-null,'' to illustrate the problem.