Physical Database Design
PHYSICAL DATABASE DESIGN EXAMPLES
Example 1 – 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 only keeps track of 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 only unique within a team (and we assume that a team cannot have two coaches of the same name.)
Serial number (for bats) is only unique within a team.
In the Affiliation table, 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.
Analyze each of the following situations and, using the physical database design techniques discussed in the class, 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 and 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.
e. Assume that we add the following Stadium table to the Super Baseball League
Example 1 – 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 only keeps track of 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 only unique within a team (and we assume that a team cannot have two coaches of the same name.)
Serial number (for bats) is only unique within a team.
In the Affiliation table, 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.
Analyze each of the following situations and, using the physical database design techniques discussed in the class, 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 and 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.
e. Assume that we add the following Stadium table to the Super Baseball League