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.