Chapter 6 The Relational Database Model Additional Concepts

Chapter 6 The Relational Database Model: Additional Concepts Fundamentals of Database Management Systems, 2 nd ed. by Mark L. Gillenson, Ph. D. University of Memphis John Wiley & Sons, Inc.

Chapter Objectives ¨ Describe how unary and ternary relationships are implemented in a relational database. ¨ Explain the concept of referential integrity. ¨ Describe how the referential integrity restrict, cascade, and set-to-null delete rules operate in a relational database. 6 -2

General Hardware Co. including OFFICE 6 -3

General Hardware Co. including OFFICE 6 -4

Unary One-to-Many Relationships ¨ A salesperson reports to exactly one sales manager, but each salesperson who does serve as a sales manager typically has several salespersons reporting to him. ¨ There is a one-to-many relationship within salespersons. Salesperson (also a sales manager) Salesperson 6 -5

Unary One-to-Many Relationships ¨ A unary relationship because there is only one entity type involved. ¨ A one-to-many because among the individual entity occurrences, that is, among the salespersons, a particular salesperson reports to one salesperson who is his sales manager, while a salesperson who is a sales manager may have several salespersons reporting to her. 6 -6

General Hardware Co. Salesperson Reporting Hierarchy 6 -7

One-to-Many Unary Relationship ¨ Requires the addition of one column to the relation representing the single entity involved in the unary relationship. 6 -8

Unary Many-to-Many Relationships ¨ A special case, an example of which has come to be known as the bill of materials problem. ¨ Every entity occurrence can be related to many other occurrences. Product 6 -9

General Hardware Company’s Product Set ¨ Tools and sets of tools are sold. ¨ Many-to-many nature of products. 6 -10

Modified Product Relation ¨ Product Numbers have been reduced to 2 digits for simplicity. ¨ Every individual unit item and every set of tools has its own row in the relation because every item and set is available for sale. 6 -11

Unary Many-to-Many Relationship: New Relation ¨ Just as a binary many-to-many relationship requires the creation of an additional relation in a relational database, so does a unary many-to-many relationship. ¨ The domain of values of each column is that of the Product Number column of the PRODUCT relation. 6 -12

Ternary Relationships ¨ Involves three different entity types. 6 -13

General Hardware Co. : Ternary Relationship 6 -14

Ternary Relationship ¨ These new General Hardware Co. relations are all independent with no foreign keys in any of them. ¨ The SALES relation shows how this ternary relationship is represented in a relational database. 6 -15

Ternary Relationship ¨ The primary key of the additional relation (SALES) will be (at least) the combination of the primary keys of the entities involved in the relationship. 6 -16

Ternary Relationship Did salesperson 137 sell product 19440 to customer 0839? 6 -17

Database Operations ¨ In addition to retrieving data we must be prepared to perform data maintenance operations, including: ª inserting new records ª deleting existing records ª updating existing records 6 -18

Referential Integrity ¨ Revolves around the circumstance of trying to refer to data in one relation in the database, based on values in another relation. 6 -19

Referential Integrity - Record Deletion ¨ A problem arises, e. g. , because a deleted record, a salesperson record, is on the “one side” of a one-tomany relationship. 6 -20

Referential Integrity Insertion ¨ Insertion - if a new record is inserted into the “one side” (SALESPERSON relation) of the oneto-many relationship, there is no problem. ¨ If a new customer record is inserted into the “many side” (CUSTOMER relation) of the one-tomany 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. 6 -21

Referential Integrity - Update ¨ Updating a foreign key value. ¨ For example, a salesperson number in the CUSTOMER relation with a new salesperson number that has no match in the SALESPERSON relation. 6 -22

DBMS & Referential Integrity ¨ Early relational DBMSs did not provide any control mechanisms for referential integrity. ¨ Modern relational DBMSs provide sophisticated control mechanisms for referential integrity: Delete rules ª Insert rules ª Update rules ª 6 -23

Three Delete Rules ¨ Restrict ¨ Cascade ¨ Set-to-Null 6 -24

Delete Rule: Restrict ¨ If an attempt is made to delete a record on the “one side” of the one-to-many relationship, the system will forbid the delete to take place if there any matching foreign key values in the relation on the “many side. ” 6 -25

Delete Rule: Restrict ¨ If an attempt is made to delete the record for salesperson 361 in the SALESPERSON relation, the system will not permit the deletion to take place because the CUSTOMER relation records for customers 1525 and 1700 include salesperson number 361 as a foreign key value. 6 -26

Delete Rule: Cascade ¨ If an attempt is made to delete a record on the “one side” of the relationship, not only will that record be deleted but all of the records on the “many side” of the relationship that have a matching foreign key value will also be deleted. ¨ The deletion will cascade from one relation to the other. 6 -27

Delete Rule: Cascade ¨ If an attempt is made to delete the record for salesperson 361 in the SALESPERSON relation, that salesperson record will be deleted and so too, automatically, will the records for customers 1525 and 1700 in the CUSTOMER relation because they have 361 as a foreign key value. 6 -28

Delete Rule: Set-to-Null ¨ If an attempt is made to delete a record on the “one side” of the one-to-many relationship, that record will be deleted and the matching foreign key values in the records on the “many side” of the relationship will be changed to null. 6 -29

Delete Rule: Set-to-Null ¨ If an attempt is made to delete the record for salesperson 361 in the SALESPERSON relation, that record will be deleted, and the Salesperson Number attribute values in the records for customers 1525 and 1700 in the CUSTOMER relation will have their Salesperson Number attribute values changed from 361 to null. 6 -30

“Copyright 2004 John Wiley & Sons, Inc. All rights reserved. Reproduction or translation of this work beyond that permitted in Section 117 of the 1976 United States Copyright Act without express permission of the copyright owner is unlawful. Request for further information should be addressed to the Permissions Department, John Wiley & Sons, Inc. The purchaser may make back-up copies for his/her own use only and not for distribution or resale. The Publisher assumes no responsibility for errors, omissions, or damages caused by the use of these programs or from the use of the information contained herein. ” 6 -31