Clifford Cohen University was founded as a small, liberal arts school just three years ago.
Since that time, the institution has grown to the point where parking on campus is difficult and parking in illegal areas is common. Accordingly, the Board of Directors has reluctantly approved a policy requiring campus police to issue parking tickets. Currently, the university requires students and faculty to register their cars with the parking office, which issues them parking decals that registrants must display inside the front windshield of their cars. At present, all record keeping at the parking office is done manually, severely limiting the ability of office personnel to create reports or perform meaningful statistical analyses about parking on campus. For example, it is currently not known how many students of each class (freshman, sophomore, etc.) register their cars or how many full-time faculty, part-time faculty, or clerical staff register their cars. The new policy of writing parking tickets will only add to this problem because it will require office staff to match parking tickets to student or faculty names. In addition, the Board of Directors would like an end-of-semester report indicating how many parking violations of each type (meter violation, invalid parking sticker, etc.) are issued by the campus police. To help solve these problems, the University Board of Directors has hired you to create a computerized system for them. You realize that a database system might work for this, and accordingly propose a database of tables with record structures similar to those in Figure. The Board of Directors approves your plan, but asks that you create a small system to demonstrate its features before creating a full-blown system.

Requirements:
1. Use Microsoft Access (or an alternate DBMS designated by your instructor) to create the three tables illustrated in Figure. What data type did you specify for each data field in each table?
2. Create at least three records in the car registration table. Be sure to use your own name as one of the registrants. Also, create at least three records for the Parking Violations Code File. Make up your own fine amounts instead of using the ones shown in the figure.
3. For each record you create in the car registration file in step 2 above, create at least three parking tickets and input this information to the Tickets File. Thus, you should have at least nine records in this file. Be sure that at least one record in the Tickets File contains a reference to each of the records in the Parking Violations Code File (i.e., at least one person breaks every possible parking violation). Print copies of the records in each table for your instructor.
4. Attempt to create a record in the Ticket File that contains a nonexistent ticket code in the parking Violations Code File. Were you successful?
5. Link the tables together, following the directions in Chapter 4. (Be sure to check ‘‘enforce referential integrity’’ when you see the dialog box illustrated in Figure 4-19.) When you finish, your relationships window should resemble the one shown in Figure 5-10. What are the relationships among the records in the three tables? Print a copy of this window as documentation for your project.
6. Now return to the Tables portion of Access and display the Car Registration table. You should now see the plus symbols illustrated in Figure. Click on one of these symbols. Are you able to view the linked records?
7. Now again attempt to create a record in the ticket file that contains a nonexistent ticket code in the parking Violations Code File. Were you successful this time?
8. Finally, attempt to delete a record in the Parking Violations Code File. Why can’t you do it?
9. If required by your instructor, create an example of the parking-violations-by-type report desired by the Board of Directors using the database you just created.