Small College uses the traditional file processing to keep its records. The files used by the Payroll and Personnel Departments are shown on the last page of this handout

a. Carefully study the actual data stored in the files do you see any problems or errors? Give at least three concrete examples (scenarios) of problems that could arise at Small College when these files are used or updated. (Your examples should refer to specific records that are stored in the given files.)

b. Convert the two separate file systems into a unified database

c. Explain why a database would overcome the difficulties that you have listed in part (a).

d. Specify at least four constraints on the data stored in the new database that you have designed in part (b). For each constraint, specify whether it is an integrity constraint, a referential integrity constraint, or a key constraint. You should have at least one example of each type of constraint.

e. Give examples of three informal queries and two updates that could be applied to the database you have designed.

PAYROLL SYSTEM FILES Faculty.file Dpt. N 1 2 ID Nr 119 209 421 462 485 507 564 683 Name Marten, Ann Newton, Jane Johns, Alice Johnson, Tom Sparks, Pam Canton, Rob Ferris, Gina Nils, Frank Address 94 Ridge, Grant, MI 210 Leonard, Ada, MI 121 Calhoun, Hart, MI 604 46th Street, Lowell, MI 201 Olive, Holt, MI 5 State, Cutler, MI 16 W.9th St., Troy, MI 46 Creston, Ada, MI Salary 52,500 59,000 60.000 65,000 43.500 59,500 58,500 76,500 YTD earn 15,425 18,945 11,402 16,952 20,115 11,010 18,450 22,050 1 1 2 3 3 4 Department.file Dept. Nr 1 2 3 4 Dept. Name Computer Science Mathematics Geology Physics Dept. Address Chair 408 Kelly Hall Jones, Ann 253 Watson Hall Sparks, Pam 707 Case Tower Ferris, Gina 118 Font Tower Nils, Frank PERSONNEL SYSTEM FILES Faculty.file Number 119 209 421 462 485 507 564 683 Name Marten, Ann Newton, Jane Jones, Alice Johnson, Tom Sparks, Pam Canton, Rob Ferris, Tina Nils, Frank Address 94 Ridge, Grant, MI 210 Leonard, Ada, MI 121 Calhoun, Hart, MI 462 Mack, Harper, MI 201 Olive, Holt, MI 5 State, Cutler, MI 16 W.9th St., Troy, MI 46 Creston, Ada, MI Degree Rank MA Asst MS Asso PhD Asst MS Asso PhD Prof MS Asst PhD Asso PhD Prof Plan Nr Dependent Coverage 2 N 1 Y 2 N 5 Y 3 Y 1 Y 1 N 2 Y Insurance.file Plan Nr 1 2 3 Description Single Cost Regular $88.18 Deluxe $101.77 Special $115.32 Family Cost $237.87 $274.54 $311.09