s
Section 5.3 Relations and Databases
369
Persons who do not own pets are not represented in Owns, nor are pets with
no owners. The primary key of Owns is PetName. Recall the business rule that
no pet has multiple owners. If any pet could have multiple owners, the composite
primary key Name/PetName would have to be used. Name alone cannot serve as
the primary key because people can have more than one pet (for example, Jones,
Kate, does not identify a unique tuple.)
In a one-to-one or one-to-many relationship such as our example, a separate
relationship table (like Owns), while not incorrect, is also not necessary.
example 22
Because PetName in the Owns relation is a foreign key into the Pet relation,
the two relations can be combined (using an operation called outer join over
PetName) to form the PetOwner relation.
petowner
Name
petName
petType
Breed
Smith, Bob
Spot
Dog
Hound
Smith, Mary
Twinkles
Cat
Siamese
Jones, Kate
Lad
Dog
Collie
Jones, Kate
Lassie
Dog
Collie
NULL
Mohawk
Fish
Moorish idol
Collier, Jon
Tweetie
Bird
Canary
White, Janet
Tiger
Cat
Shorthair
This PetOwner relation can replace both the Owns relation and the Pet relation
with no loss of information. PetOwner contains a tuple with a NULL value for
Name. This tuple does not violate entity integrity because Name is not a component of the primary key but instead is still a foreign key into Person.
operations on Relations
Two unary operations that can be performed on relations are restrict and project.
The restrict operation creates a new relation made up of those tuples of the original relation that satisfy certain conditions. The project operation creates a new
relation made up of certain attributes from the original relation, eliminating any
duplicate tuples. The restrict and project operations can be thought of in terms
of subsets. The restrict operation creates a subset of the rows that satisfy certain
conditions; the project operation creates a subset of the columns that represent
certain attributes.