chapter 6 the relational database model additional concepts l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 6 The Relational Database Model: Additional Concepts PowerPoint Presentation
Download Presentation
Chapter 6 The Relational Database Model: Additional Concepts

Loading in 2 Seconds...

play fullscreen
1 / 31

Chapter 6 The Relational Database Model: Additional Concepts - PowerPoint PPT Presentation


  • 189 Views
  • Uploaded on

Chapter 6 The Relational Database Model: Additional Concepts . Fundamentals of Database Management Systems by Mark L. Gillenson, Ph.D. University of Memphis Presentation by: Amita Goyal Chin, Ph.D. Virginia Commonwealth University John Wiley & Sons, Inc. Chapter Objectives.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Chapter 6 The Relational Database Model: Additional Concepts' - johana


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
chapter 6 the relational database model additional concepts

Chapter 6The Relational Database Model: Additional Concepts

Fundamentals of Database Management Systems

by

Mark L. Gillenson, Ph.D.

University of Memphis

Presentation by: Amita Goyal Chin, Ph.D.

Virginia Commonwealth University

John Wiley & Sons, Inc.

chapter objectives
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.
unary one to many relationships
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

unary one to many relationships6
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.
one to many unary relationship
One-to-Many Unary Relationship
  • Requires the addition of one column to the relation representing the single entity involved in the unary relationship.
unary many to many relationships
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

Product

general hardware company s product set
General Hardware Company’s Product Set
  • Tools and sets of tools are sold.
  • Many-to-many nature of products.
modified product relation
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.
unary many to many relationship new relation
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.
ternary relationships
Ternary Relationships
  • Involves three different entity types.
ternary relationship
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.
ternary relationship16
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.
ternary relationship17
Ternary Relationship

Did salesperson 137 sell product 19440 to customer 0839?

database operations
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
referential integrity
Referential Integrity
  • Revolves around the circumstance of trying to refer to data in one relation in the database, based on values in another relation.
referential integrity record deletion
Referential Integrity - Record Deletion
  • A problem arises, e.g., because a deleted record, a salesperson record, is on the “one side” of a one-to-many relationship.
referential integrity insertion
Referential Integrity - Insertion
  • Insertion - if a new record is inserted into the “one side” (SALESPERSON relation) of the one-to-many relationship, there is no problem.
  • If a new customer record is inserted into the “many side” (CUSTOMER relation) 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.
referential integrity update
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.
dbms referential integrity
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
three delete rules
Three Delete Rules
  • Restrict
  • Cascade
  • Set-to-Null
delete rule restrict
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 are any matching foreign key values in the relation on the “many side.”
delete rule restrict26
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.
delete rule cascade
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.
delete rule cascade28
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.
delete rule set to null
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.
delete rule set to null30
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.
slide31

“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.”