Physical Database Design Lecture Exampl Essay example

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†¦show more content†¦The Team Number field. ii. The Player Name field. b) Construct a B+-tree index of the type shown in this chapter for the Player file, assuming that now there are many more records than are shown above. The file and the index have the following characteristics: The file is stored on eight cylinders of the disk. The highest key values on the eight cylinders, in order, are: Cylinder 1: 1427 Cylinder 2: 1965 Cylinder 3: 2848 Cylinder 4: 3721 Cylinder 5: 4508 Cylinder 6: 5396 Cylinder 7: 6530 Cylinder 8: 7442 Each index record can hold four key value/pointer pairs. There are three index records at the lowest level of the tree index. c) The same as part b above, but now there are four index records at the lowest level of the tree index. d) The same as part b above, but each index record can hold two key value/pointer pairs and there are four index records at the lowest level of the tree index. Answer - a) Simple linear index. i. (Based on Team Number.) 12 1 12 3 12 8 15 9 18 5 18 6 20 4 20 7 35 2 35 10 ii. (Based on Player Name.) Barry Morton 10 Chico Lopez 8 Chris Vernon 9 Dave Lester 6 Fred Williams 1 Juan Gomez 3 Rod Smith 7 Steve