The database only keeps track of the current team that a coach works for. Team number, team name, ND 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.
. 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 umber 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 relational database. 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. Stadium Name Year Built Size Team Number STADIUM Table There is a frequent and high priority need to quickly retrieve detailed team and stadium data together. Answer - a) This is a case for the Storing Derived Data technique. The data to be totaled is in the Years attribute of the AFFILIATION table. The total for each player will be stored in an additional attribute in the PLAYER table.
You can also store it in AFFILIATION table. B) Build an index over the Batting Average attribute of the AFFILIATION table. C) This is a case for the Creating New Primary Keys technique. The three-attribute primary key of the WORK EXPERIENCE table will be replaced by a new, added, unique serial number attribute (surrogate primary key). D) This implies that frequent Joins will be necessary between the PLAYER and AFFILIATION tables. There are several approaches.
First, make sure that Player Number attributes in both tables are indexed.One option is to use the clustering files technique which will bring each player record and its associated affiliation records physically near each other on the disk. Another option is denationalization in which the PLAYER and AFFILIATION tables will be merged into one table. One can also think of doing both these things. E) This is a case for the Combine Tables in a One-to-One Relationship technique.
The STADIUM and TEAM tables will be merged into one on a one record for one record axis. Example 2 - The Super Baseball League. Consider the Super Baseball League Player file shown below.
