Part I

1. Given the file structure shown in Figure P1.1, answer Problems a through f.

Figure P1.1 The File Structure for Problems a-f

a) How many records does the file contain, and how many fields are there per record?

b) What problem would you encounter if you wanted to produce a listing by city? How would you solve this problem by altering the file structure?

c) If you wanted to produce a listing of the file contents by last name, area code, city, state, or zip code, how would you alter the file structure?

d) What data redundancies do you detect, and how could these redundancies lead to anomalies?

e) Using two relational database tables, PROJECT and MANAGER, eliminate the redundancies discovered in Problem d. Make sure you use the naming conventions discussed in Section 1.3.3 and connect the two tables through the appropriate link. (Hint: Use Figure 1.11 as an example.)

f) Create the relational schema to show how the two database tables in Problem e are linked.

2. Given the file structure shown in Figure P1.7, answer Problems a through g.

Figure P1.7 The File Structure for Problems a-g.

a) Identify and discuss the serious data redundancy problems exhibited by the file structure shown in Figure P1.7.

b) How many different data sources are likely to be used by the file you examined in problem a?

c) Given your findings in Problems a and b, how would a relational database environment help eliminate the data redundancy problems?

d) Given your answer to Problem c, how many tables would you use to substantially eliminate the data redundancy problems? What table structures would you recommend?

e) Given your answers to problem d, show the table contents for each table.

f) Identify the types of relationships (1:1, 1:M, or M:N) between the tables you defined in problems d and e.

g)