1,164
25
Essay, 3 pages (550 words)

Physical database design

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.

Thank's for Your Vote!
Physical database design. Page 1
Physical database design. Page 2
Physical database design. Page 3

This work, titled "Physical database design" was written and willingly shared by a fellow student. This sample can be utilized as a research and reference resource to aid in the writing of your own work. Any use of the work that does not include an appropriate citation is banned.

If you are the owner of this work and don’t want it to be published on AssignBuster, request its removal.

Request Removal
Cite this Essay

References

AssignBuster. (2021) 'Physical database design'. 19 December.

Reference

AssignBuster. (2021, December 19). Physical database design. Retrieved from https://assignbuster.com/physical-database-design/

References

AssignBuster. 2021. "Physical database design." December 19, 2021. https://assignbuster.com/physical-database-design/.

1. AssignBuster. "Physical database design." December 19, 2021. https://assignbuster.com/physical-database-design/.


Bibliography


AssignBuster. "Physical database design." December 19, 2021. https://assignbuster.com/physical-database-design/.

Work Cited

"Physical database design." AssignBuster, 19 Dec. 2021, assignbuster.com/physical-database-design/.

Get in Touch

Please, let us know if you have any ideas on improving Physical database design, or our service. We will be happy to hear what you think: [email protected]