Exam 6: The Relational Database Model: Additional Concepts

If the referential integrity delete rule between two relations is restrict, an attempt to delete a record on the "many side" of the one-to-many will be rejected if of one or more records in the relation on the "one side" of the relationship has the corresponding key value.

A ternary relationship ____.

If the referential integrity delete rule between two relations is restrict, the deletion of a record in the relation on the "one side" of the one-to-many relationship may result in the deletion of one or more records in the relation on the "many side" of the relationship.

Referential integrity is an issue in record ____.

If the referential integrity delete rule between two relations is cascade, the deletion of a record in the relation on the "many side" of the one-to-many relationship may result in the deletion of one or more records in the relation on the "one side" of the relationship.

If the referential integrity delete rule between two relations is cascade, the deletion of a record in the relation on the "one side" of the one-to-many relationship may result in the deletion of one or more records in the relation on the "many side" of the relationship.

A ternary relationship may have intersection data associated with it.

The following scenario applies to the next several questions: Consider relations A and B. Relation A represents the entity on the "one side" of a one-to-many relationship; Relation B represents the entity on the "many side" of the one-to-many relationship. -The delete rule between relations A and B is cascade. If an attempt is made to delete a record in relation A and its primary key value appears as a foreign key value in one or more records of relation B ____.

If the referential integrity delete rule between two relations is set-to-null, the deletion of a record in the relation on the "one side" of the one-to-many relationship may result in the deletion of one or more records in the relation on the "many side" of the relationship.

A ternary relationship involves relations for two different entity types plus an additional relation.

A ternary relationship is the equivalent of ____ binary many-to-many relationship(s).

A ternary relationship is the functional equivalent of three different binary many-to-many relationships.

Consider the following ANIMAL and ENCLOSURE relations from the Central Zoo relational database. The left-hand column of relative record numbers is there to facilitate answering some of the questions. Enclosure Number Type Size 1 2 3 4 5 0347 Glass Cage 400 0636 Fenced Yard 1500 0912 Natural Area 2000 1483 Fenced Yard 1650 1660 Steel Cage 700 Enclosure relation Animal Animal Enclosure Number SpeciesNameNumber 1 2 3 4 5 6 7 8 9 10 13648 Elephant Jumbo 1483 15273 Giraffe Stretch 0912 17543 Elephant Shirley 0636 20165 Giraffe Necky 0912 23743 Giraffe High Top 0912 27579 Panda Fluffy 0347 32565 Elephant Large Louie 1483 33837 Elephant Charley 0636 36340 Panda Huggy 0347 40436 Giraffe Sarah 0912 Animal relation a. What would happen if the delete rule between the ENCLOSURE and ANIMAL relations is restrict and an attempt is made to delete the record for enclosure number 0912 in the ENCLOSURE relation? b. What would happen if the delete rule between the ENCLOSURE and ANIMAL relations is restrict and an attempt is made to delete the record for enclosure number 1660 in the ENCLOSURE relation? c. What would happen if the delete rule between the ENCLOSURE and ANIMAL relations is restrict and an attempt is made to delete the record for animal number 40436 in the ANIMAL relation? d. What would happen if the delete rule between the ENCLOSURE and ANIMAL relations is cascade and an attempt is made to delete the record for enclosure number 0912 in the ENCLOSURE relation? e. What would happen if the delete rule between the ENCLOSURE and ANIMAL relations is cascade and an attempt is made to delete the record for enclosure number 1660 in the ENCLOSURE relation? f. What would happen if the delete rule between the ENCLOSURE and ANIMAL relations is set-to-null and an attempt is made to delete the record for enclosure number 0912 in the ENCLOSURE relation? g. What would happen if the delete rule between the ENCLOSURE and ANIMAL relations is set-to-null and an attempt is made to delete the record for enclosure number 1660 in the ENCLOSURE relation?

Consider the following relational database for the Central Zoo. Central Zoo wants to maintain information about its animals, the enclosures in which they live, and its zookeepers and the services they perform for the animals. In addition, Central Zoo has a program by which people can be sponsor of animals. Central Zoo wants to track its sponsors, their dependents, and associated data. Each animal has a unique animal number and each enclosure has a unique enclosure number. An animal can live in only one enclosure. An enclosure can have several animals in it or it can be currently empty. A zookeeper has a unique employee number. Some zookeepers supervise other zookeepers. Every animal has been cared for by at least one and generally many zookeepers; each zookeeper has cared for at least one and generally many animals. Each time a zookeeper performs a specific, significant service for an animal the service type, date, and time are recorded. A zookeeper may perform a particular service on a particular animal more than once on a given day. A sponsor, who has a unique sponsor number and a unique social security number, sponsors at least one and possibly several animals. An animal may have several sponsors or none. For each animal that a particular sponsor sponsors, the zoo wants to track the annual sponsorship contribution and renewal date. In addition, Central Zoo wants to keep track of each sponsor's dependents. A sponsor may have several dependents or none. A dependent is associated with exactly one sponsor. Enclosure Date Number Type Location Size Built ENCLOSURE Relation Animal Animal Country Enclosure Number Species Name Gender Of Birth Weight Number ANIMAL Relation Employee Employee Year Supervisor Number Narne Title Hired Number ZOOKEEPER Relation Animal Employee Service Number Number Type Date Time CARES FOR Relation Sponsor Social Security Sponsor Number Number Name Address Telephone SPONSOR Relation Animal Sponsor Annual Renewal Number Number Contribution Date CONTRIBUTION Relation Sponsor Dependent Date of Number Name Relationship Birth DEPENDENT Relation a. Identify the candidate keys of each relation. b. Identify the primary key and any alternate keys of each relation. c. How many foreign keys does each relation have? d. Identify the foreign keys of each relation. e. Indicate any instances in which a foreign key serves as part of the primary key of the relation in which it is a foreign key. Why does each of those relations require a multi-attribute primary key? f. Identify the relations that support many-to-many relationships, the primary keys of those relations, and any intersection data. g. Using the informal relational command language described in this chapter, write commands to: i. Retrieve the record for animal number 58560. ii. Retrieve the record for the tiger named Stripes. iii. List all of the tigers born in India. iv. List the name and animal number of every animal born in China that weighs less than 100 pounds . v. List the name and number of every tiger. vi. What is the country of birth of animal number 74371? vii. What is the type and size of the enclosure used for animal number 74371? viii. List the name and address of every sponsor of animal number 74371.

Storing a ternary relationship in a relational database requires the creation of an additional relation.

Representing a ternary relationship in a relational database ____.

Consider the following AIRPLANE and FLIGHT relations from the Grand Travel Airlines relational database. The left-hand column of relative record numbers is there to facilitate answering some of the questions: Airplane Passenger Year NumberManufacturerModelCapacityBuilt 1 2 3 4 04653 Boeing 767 280 1988 10582 Airbus A320 256 1997 16420 Canadair CRJ 54 2001 22663 Boeing 767 265 1999 Airplane relation Flight Departure Arrival Airplane Number Date Time Time Number 1 2 3 4 5 6 7 8 9 005 11/15/2003 2:14 4:20 22663 018 01/24/2004 11:00 1:32 10582 032 11/15/2003 9:03 10:30 16420 032 11/16/2003 8:57: 10:23 16420 032 11/17/2003 9:00 10:33 16420 120 01/24/2004 7:52 9:34 16420 120 01/28/2004 7:51 9:57 16420 154 11/15/2003 12:58 3:30 10582 197 02//12/2004 5:03 7:22 22663 Flight relation a. What would happen if the delete rule between the AIRPLANE and FLIGHT relations is restrict and an attempt is made to delete the record for airplane number 16420 in the AIRPLANE relation? b. What would happen if the delete rule between the AIRPLANE and FLIGHT relations is restrict and an attempt is made to delete the record for airplane number 04653 in the AIRPLANE relation? c. What would happen if the delete rule between the AIRPLANE and FLIGHT relations is restrict and an attempt is made to delete the record for flight number 120 on 01/24/2004 in the FLIGHT relation? d. What would happen if the delete rule between the AIRPLANE and FLIGHT relations is cascade and an attempt is made to delete the record for airplane number 16420 in the AIRPLANE relation? e. What would happen if the delete rule between the AIRPLANE and FLIGHT relations is cascade and an attempt is made to delete the record for airplane number 04653 in the AIRPLANE relation? f. Record 3 in the AIRPLANE relation would be deleted and the Airplane Number values of records 3, 4, 5, 6, and 7 in the FLIGHT relation would be updated to null. g. Record 1 in the AIRPLANE relation would be deleted.

Representing a unary one-to-many relationship in a relational database requires the addition of one column to the relation that represents the single entity involved in the relationship.

The following scenario applies to the next several questions: Consider relations A and B. Relation A represents the entity on the "one side" of a one-to-many relationship; Relation B represents the entity on the "many side" of the one-to-many relationship. -The delete rule between relations A and B is restrict. If an attempt is made to delete a record in relation A and its primary key value appears as a foreign key value in one or more records of relation B ____.