Data systems Discussion

The Relational Database Model: Additional Concepts

BLCN-534: Fundamentals of Database Systems

*

*

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-*

*

*

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.

6-*

Salesperson (also a sales manager)

Salesperson

*

*

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-*

*

*

General Hardware Co. Salesperson Reporting Hierarchy

6-*

*

*

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.

6-*

Product

Product

*

*

Ternary Relationships

  • Involves three different entity types.

6-*

*

*

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-*

*

*

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-*

*

*

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-*

*

*

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-*

*

*

Referential Integrity – Record Deletion

6-*

  • 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

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.

6-*

*

*

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-*

*

*

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-*

*

*

Three Delete Rules

  • Restrict:
  • Cascade
  • Set-to-Null

6-*

*

*

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

6-*

*

*

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-*

*

*

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-*

*

*

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-*

*

*

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-*

*

*

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-*

*

*

Chapter 6 Use Case

General Hardware

6-*

*

*

General Hardware Company’s Product Set

Tools and sets of tools are sold.

Many-to-many nature of products.

6-*

Wrench Model A (#11)

Deluxe Wrench Set (#43)

Wrench Model B (#14)

Supreme Tool

Wrench Model C (#17) Set (#53)

Master Wrench Set (#44)

Wrench Model D (#19)

Hammer Model A (#22)

Grand Tool

Hammer Model B (#24) Deluxe Hammer Set (#48) Set (#56)

Hammer Model C (#28)

Drill Model A (#31)

Drill Model B (#35)

Figure 6.5 General Hardware Co. product bill of materials.

*

*

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-*

*

*

Ternary Relationship

6-*

Did salesperson 137 sell product 19440 to customer 0839?

Salesperson 137 Customer 0839

Salesperson 204 Customer 1826

(a) Salespersons and customers.

Customer 0839 Product 19440

Customer 1826 Product 24013

(b) Customers and products.

Salesperson 137 Product 19440

Salesperson 204 Product 24013

(c) Salespersons and products.

*

*

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
find the cost of your paper

Asian American 3

I need support with this Asian Studies question so I can learn better. Write a review of the reading Marcus and Chen Inside Outside Chinatown Requirements: 250+   |   .doc fileATTACHMENTSmarcus_and_chen_inside_outside_chinatown.pdf

Environmental Science Question

m trying to learn for my Environmental Science class and I’m stuck. Can you help? Helpful Video on a shark field study: Turks & Caicos Islands: Field Research on Sharks (Links….

What is the command for it?

I’m working on a linux question and need a sample draft to help me understand better. What is the command for this, one line is all I need to solve….