Super Baseball League Consider the following relational database for the Super B

Question

Super Baseball League Consider the following relational database for the Super Baseball

League. It keeps track of teams in the league, coaches and players on the teams, work

experience of the coaches, bats belonging to each team, and which players have played on

which teams. Note the following facts about this environment:

• The database keeps track of the

history of all of the teams that each player has played on and all of the players who have played

on each team.

• The database keeps track of only the current team that a coach works for.

•Team Number, Team Name, and Player Number are each unique attributes across the league.

•Coach Name is unique only within a team (and we assume that a team cannot have two

coaches of the same name).

• 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.

These are the relations...

TEAMRelation
(attributes) Team number, Team Name, City, Manager

COACHRelation
Team number, Coach Name, coach Telephone

WORKEXPERIENCERelation
Team number, Coach name , Experience type, Years of experience

BATSRelation

Team number , Serial Number, Manufacturer

PLAYERRelation
player Number, player Name , player Age

AFFILIATIONRelation
Player number, Team number, Batting average, Years

Using the description in the main question, and the relations with their attributes in this comment above answer the following:
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.

Explanation / Answer

a. Condidate Key of :
   TEAMRelation : Team number , Team Name
   COACHRelation : Team number , coach Telephone
   WORKEXPERIENCERelation : Team number
   BATSRelation : Team number, Serial Number
   PLAYERRelation : player Number
   AFFILIATIONRelation : Player number, Team number

b. Primary Key of:
   EAMRelation : Team Number
   COACHRelation : coach Telephone
   WORKEXPERIENCERelation : Team number
   BATSRelation : Serial Number
   PLAYERRelation : Player name
   AFFILIATIONRelation : Player Nmuber

c. All relations have one foreign key i.e Team number
   except one PlayerRelation

d. Foreign Key of:
   EAMRelation : Team Number
   COACHRelation : Team number
   WORKEXPERIENCERelation : Team number
   BATSRelation : Team Number
   PLAYERRelation : none
   AFFILIATIONRelation : Team Nmuber

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.