Given the file structure shown in Figure P1.7, answer Problems a through g.
Figure P1.7: The File Structure for Problems a-g
PROJ_NUM | PROJ_NAME | EMP_NUM | EMP_NAME | JOB_CODE | JOB_CHG_HOUR | PROJ_HOURS | EMP_PHONE
---------|-----------|---------|----------|----------|--------------|------------|-----------
1 | Hurricane | 101 | John D. Newson | EE | $85.00 | 13.3 | 653-234-3245
1 | Hurricane | 105 | David F. Schwann | CT | $60.00 | 16.2 | 653-234-1123
1 | Hurricane | 110 | Anne R. Ramoras | CT | $60.00 | 14.3 | 615-233-5568
2 | Coast | 101 | John D. Newson | EE | $85.00 | 19.8 | 653-234-3254
2 | Coast | 108 | June H. Sattlemeir | EE | $85.00 | 17.5 | 905-554-7812
3 | Satellite | 110 | Anne R. Ramoras | CT | $62.00 | 11.6 | 615-233-5568
3 | Satellite | 105 | David F. Schwann | CT | $26.00 | 23.4 | 653-234-1123
3 | Satellite | 123 | Mary D. Chen | EE | $85.00 | 19.1 | 615-233-5432
3 | Satellite | 112 | Allecia R. Smith | BE | $85.00 | 20.7 | 615-678-6879
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?
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?
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) Create a relational schema for Problem f.
