Assignment: Database Principles
Do the following tasks.
1. What is sequential access? What is direct access? Which of the two is more important in today's business environment? Why?
2. Answer the following questions based on the following two tables from the Super Baseball League's relational database.
|
Team number
|
Team Name
|
City
|
Manager
|
|
137
|
Eagles
|
Orlando
|
Smith
|
|
275
|
Cowboys
|
San Jose
|
Jones
|
|
294
|
Statesmen
|
Springfield
|
Edwards
|
|
368
|
Pandas
|
El Paso
|
Adams
|
|
422
|
Sharks
|
Jackson
|
Vega
|
(a) TEAM TABLE
Player Number Player Name Age Position Team Number
|
Player Number
|
Player Name
|
Age
|
Position
|
Team Number
|
|
1209
|
Steve Marks
|
24
|
Catcher
|
294
|
|
1254
|
Roscoe Gomez
|
19
|
Pitcher
|
422
|
|
1536
|
Mark Norton
|
32
|
First Baseman
|
368
|
|
1953
|
Alan Randall
|
24
|
Pitcher
|
137
|
|
2753
|
John Harbor
|
22
|
Shortstop
|
294
|
|
2843
|
John Yancy
|
27
|
Center Fielder
|
137
|
|
3002
|
Stuart Clark
|
20
|
Catcher
|
422
|
|
3274
|
Lefty Smith
|
31
|
Third Baseman
|
137
|
|
3388
|
Kevin Taylor
|
25
|
Shortstop
|
294
|
|
3740
|
Juan Vidora
|
25
|
Catcher
|
368
|
(b) PLAYER table.
a. Regarding the Super Baseball League Player file:i.Describe the file's record type.ii.Describe the set or range of values that the Player Number field can take.
b. Assume that the records of the Player file are physically stored in the order shown.
i.Retrieve all of the records of the file physically sequentially.
ii.Retrieve all of the records of the file logically sequentially based on the Player Name field.
iii.Retrieve all of the records of the file logically sequentially based on the Player Number field.
iv.Retrieve all of the records of the file logically sequentially based on the Team Number field.
v.Perform a direct retrieval of the records with a Player Number field value of 3388.
vi.Perform a direct retrieval of the records with a Team Number field value of 275.
vii.Perform a direct retrieval of the records with an Age field value of 24.
c. The value 294 appears as a team number once in the TEAM table and three times in the PLAYER table. Does this constitute data redundancy? Explain.
d. Merge the TEAM and PLAYER tables based on the common Team Number field (in a manner similar to Figure 3.8 for the General Hardware database.) Is the merged table an improvement over the two separate tables in terms of: i.Data redundancy? Explain.ii.Data integration? Explain.
e. Explain why the Team Number field is in the PLAYER table.
f. Explain why team number 422 appears twice in the PLAYER table.
g. How many tables must be accessed to find:i.The age of player number 1953?ii.The name of the team on which player number 3388 plays?iii.The number of the team on which player number 3388 plays?
h. Describe the procedure for finding the name of the city in which player number 3002 is based.
i. What is the mechanism for recording the one-to-many relationship between players and teams in the Super Baseball League database, above?