376
Relations, Functions, and Matrices
For instance, we could not add a tuple to PetOwner with a non-NULL Name value
that does not exist in the Person relation, because this would violate the Owns
relation as a binary relation on Person × Pet. Also, if the Bob Smith tuple is deleted from the Person relation, then the Bob Smith tuple must be deleted from the
PetOwner relation or the Name value “Bob Smith” changed to NULL (a business
rule must specify which is to occur) so that PetOwner’s foreign key Name does not
violate referential integrity. This prevents the inconsistent state of a reference to
Bob Smith in PetOwner when Bob Smith no longer exists as a “Person.”
S e c t I o n 5 . 3 Review
technIQueS
• Carry out restrict, project, and join operations in a
relational database.
• Formulate relational database queries using relational algebra, SQL, and relational calculus.
maIn IDeaS
• A relational database uses mathematical relations,
described by tables, to model objects and relationships in an enterprise.
• The database operations of restrict, project, and
join are operations on relations (sets of tuples).
• Queries on relational databases can be formulated
using the restrict, project, and join operations, SQL
statements, or notations borrowed from set theory
and predicate logic.
exeRcISeS 5.3
Exercises 1–4 refer to the Person, Pet, and PetOwner relations of Examples 20 and 22.
1. Consider the following operation:
restrict Pet where PetType = “Cat” giving Kitties
a. Write a query in English that would result in the information contained in Kitties.
b. What is the cardinality of the relation obtained by performing this operation?
c. Write an SQL query to obtain this information.
2. Consider the following operation:
project Person over (Name, City, State) giving Census
a. Write a query in English that would result in the information contained in Census.
b. What is the degree of the relation obtained by performing this operation?
c. Write an SQL query to obtain this information.
3. Write the results of the following operation:
project Pet over (PetName, Breed ) giving What Am I
4. Write the results of the following operation:
restrict PetOwner where PetType = “Bird” Or PetType = “Cat” giving SomeOwners
W
W
Relations, Functions, and Matrices
For instance, we could not add a tuple to PetOwner with a non-NULL Name value
that does not exist in the Person relation, because this would violate the Owns
relation as a binary relation on Person × Pet. Also, if the Bob Smith tuple is deleted from the Person relation, then the Bob Smith tuple must be deleted from the
PetOwner relation or the Name value “Bob Smith” changed to NULL (a business
rule must specify which is to occur) so that PetOwner’s foreign key Name does not
violate referential integrity. This prevents the inconsistent state of a reference to
Bob Smith in PetOwner when Bob Smith no longer exists as a “Person.”
S e c t I o n 5 . 3 Review
technIQueS
• Carry out restrict, project, and join operations in a
relational database.
• Formulate relational database queries using relational algebra, SQL, and relational calculus.
maIn IDeaS
• A relational database uses mathematical relations,
described by tables, to model objects and relationships in an enterprise.
• The database operations of restrict, project, and
join are operations on relations (sets of tuples).
• Queries on relational databases can be formulated
using the restrict, project, and join operations, SQL
statements, or notations borrowed from set theory
and predicate logic.
exeRcISeS 5.3
Exercises 1–4 refer to the Person, Pet, and PetOwner relations of Examples 20 and 22.
1. Consider the following operation:
restrict Pet where PetType = “Cat” giving Kitties
a. Write a query in English that would result in the information contained in Kitties.
b. What is the cardinality of the relation obtained by performing this operation?
c. Write an SQL query to obtain this information.
2. Consider the following operation:
project Person over (Name, City, State) giving Census
a. Write a query in English that would result in the information contained in Census.
b. What is the degree of the relation obtained by performing this operation?
c. Write an SQL query to obtain this information.
3. Write the results of the following operation:
project Pet over (PetName, Breed ) giving What Am I
4. Write the results of the following operation:
restrict PetOwner where PetType = “Bird” Or PetType = “Cat” giving SomeOwners
W
W