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