Databases Reference
In-Depth Information
• Serial Number (for bats) is unique only within a team.
• In the AFFILIATION relation, the Years attribute
indicates that number of years that a player played
on a team; the Batting Average is for the years that a
player played on a team.
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. Assume that we add the following STADIUM
relation to the Super Baseball League relational
database. Each team has one home stadium, which
is what is represented in this relation. Assume that a
stadium can serve as the home stadium for only one
team. Stadium Name is unique across the league.
TEAM Relation
Team
Team
Number
Name
City
Manager
COACH Relation
Team
Coach
Coach
Number
Name
Telephone
WORK EXPERIENCE Relation
STADIUM Relation
Team
Coach
Experience
Years Of
Stadium
Year
Team
Number
Name
Type
Experience
Name
Built
Size
Team Number
What kind of binary relationship exists between the
STADIUM relation and the TEAM relation? Could
the data from the two relations be combined into one
without introducing data redundancy? If so, how?
h. Using the informal relational command language
described in this chapter, write commands to:
i. Retrieve the record for team number 12.
ii. Retrieve the record for coach Adams on team
number 12.
iii. List the player number and age of every player.
iv. List the work experience of every coach.
v. List the work experience of every coach on team
number 25.
vi. Find the age of player number 42459.
vii. List the serial numbers and manufacturers of all
of the Vultures' (the name of a team) bats.
viii. Find the number of years of college coaching
experience that coach Taylor of the Vultures has.
BATS Relation
Team
Serial
Number
Number
Manufacturer
PLAYER Relation
Number
Name
Player
Player
Age
AFFILIATION Relation
Player
Team
Batting
Number
Number
Years
Average
Search WWH ::
Custom Search