368
Relations, Functions, and Matrices
tuple must have a primary key value in order to distinguish that tuple and that all
attribute values of the primary key are needed in order to identify a tuple uniquely.
Another business rule of the PLAC enterprise is that all people have unique
names; therefore Name is sufficient to identify each tuple and was chosen as the
primary key in the Person relation. Note that for the Person relation as shown in this
example, State could not serve as a primary key because there are two tuples with
State value “IL.” However, just because Name has unique values in this instance
does not preclude the possibility of duplicate names. It is the business rule that
determines that names will be unique. (There is no business rule that says that addresses or cities are unique, so neither of these attributes can serve as the primary
key, even though there happen to be no duplicates in the Person relation shown.)
The assumption of unique names is a somewhat simplistic business rule. The
primary key in a relation involving people is often an identifying number that is a
unique attribute. This used to be a Social Security number, but due to privacy concerns, institutions now often generate a local unique identifier such as a student ID
number or employee ID number, or they use a driver’s license number. Because
PetName is the primary key in the Pet relation of Example 20, we can surmise
the even more surprising business rule that in the PLAC enterprise, all pets have
unique names. A more realistic scenario would call for creating a unique attribute
for each pet, sort of a pet Social Security number, to be used as the primary key.
This key would have no counterpart in the real enterprise, so the database user
would never need to see it; such a key is called a blind key or surrogate key. Blind
keys are often generated automatically by the database system using a simple sequential numbering scheme.
An attribute in one relation (called the “child” relation) may have the same
domain as the primary key attribute in another relation (called the “parent” relation). Such an attribute is called a foreign key (of the child relation) into the parent
relation. A relation for a relationship (that is, for a diamond in the E-R diagram)
between entities uses foreign keys to establish connections between those entities.
There will be one foreign key in the relationship relation for each entity participating in the relationship.
example 21
The PLAC enterprise has identified the following instance of the Owns relationship. The Name attribute of Owns is a foreign key into the Person relation where
Name is a primary key; PetName of Owns is a foreign key into the Pet relation,
where PetName is a primary key. The first tuple establishes the Owns relationship
between Bob Smith and Spot; that is, it indicates that Bob Smith owns Spot.
owns
Name
petName
Smith, Bob
Spot
Smith, Mary
Twinkles
Jones, Kate
Lad
Jones, Kate
Lassie
Collier, Jon
Tweetie
White, Janet
Tiger
Relations, Functions, and Matrices
tuple must have a primary key value in order to distinguish that tuple and that all
attribute values of the primary key are needed in order to identify a tuple uniquely.
Another business rule of the PLAC enterprise is that all people have unique
names; therefore Name is sufficient to identify each tuple and was chosen as the
primary key in the Person relation. Note that for the Person relation as shown in this
example, State could not serve as a primary key because there are two tuples with
State value “IL.” However, just because Name has unique values in this instance
does not preclude the possibility of duplicate names. It is the business rule that
determines that names will be unique. (There is no business rule that says that addresses or cities are unique, so neither of these attributes can serve as the primary
key, even though there happen to be no duplicates in the Person relation shown.)
The assumption of unique names is a somewhat simplistic business rule. The
primary key in a relation involving people is often an identifying number that is a
unique attribute. This used to be a Social Security number, but due to privacy concerns, institutions now often generate a local unique identifier such as a student ID
number or employee ID number, or they use a driver’s license number. Because
PetName is the primary key in the Pet relation of Example 20, we can surmise
the even more surprising business rule that in the PLAC enterprise, all pets have
unique names. A more realistic scenario would call for creating a unique attribute
for each pet, sort of a pet Social Security number, to be used as the primary key.
This key would have no counterpart in the real enterprise, so the database user
would never need to see it; such a key is called a blind key or surrogate key. Blind
keys are often generated automatically by the database system using a simple sequential numbering scheme.
An attribute in one relation (called the “child” relation) may have the same
domain as the primary key attribute in another relation (called the “parent” relation). Such an attribute is called a foreign key (of the child relation) into the parent
relation. A relation for a relationship (that is, for a diamond in the E-R diagram)
between entities uses foreign keys to establish connections between those entities.
There will be one foreign key in the relationship relation for each entity participating in the relationship.
example 21
The PLAC enterprise has identified the following instance of the Owns relationship. The Name attribute of Owns is a foreign key into the Person relation where
Name is a primary key; PetName of Owns is a foreign key into the Pet relation,
where PetName is a primary key. The first tuple establishes the Owns relationship
between Bob Smith and Spot; that is, it indicates that Bob Smith owns Spot.
owns
Name
petName
Smith, Bob
Spot
Smith, Mary
Twinkles
Jones, Kate
Lad
Jones, Kate
Lassie
Collier, Jon
Tweetie
White, Janet
Tiger