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

Consider the following relational database for the Super Baseball League

Subject:Computer SciencePrice:4.89 Bought3

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 the teams that each player has played on and all 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 table, the years attribute indicates the number of years that a player played on a team; the batting average is for the years that a player played on a team.

Analyze each of the following situations and, using the physical database design techniques discussed in this chapter, state how you would modify the logical design shown to improve performance or otherwise accommodate it.

a. There is a frequent need to quickly find the total number of years that any particular player has played in the league (i.e., the total number of years played for all of the teams a player played for).

b. There is a need to retrieve AFFILIATION table records directly based on batting averages.

c. The three-attribute primary key of the WORK EXPERIENCE table has been found to be cumbersome to use in queries and awkward to index.

d. There is a frequent very high-priority need to quickly retrieve player name and age data together with the teams (identified by team number) they have played on, the number of years they played on the teams, and the batting averages they compiled.

Each team has one home stadium, which is what is represented in this table. Assume that a stadium can serve as the home stadium for only one team.

Stadium name is unique across the league.

There is a frequent high-priority need to quickly retrieve detailed team and stadium data together.

pur-new-sol

Purchase A New Answer

Custom new solution created by our subject matter experts

GET A QUOTE

Answer Preview

Candidate Key:

•           It is a set of fields from which primary key can be selected it is set of attributes that act as a primary keys.

 

Candidate 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:

•           It is same as candidate key that is most appropriate to become main key of the table.

•           It uniquely identifies records in a table.

 

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

 

Foreign Key:

•           It is used to link two tables together.

•           Column or combination of it, whose values match a Primary Key in a different table.

 

d.         Foreign Key of:

 

EAMRelation: Team Number

COACHRelation: Team number

WORKEXPERIENCERelation: Team number

BATSRelation: Team Number

PLAYERRelation: none

AFFILIATIONRelation: Team Nmuber