State Patrol

The purpose of the State Patrol ticket processing system is to record driver violations, to keep records of the fines paid by drivers when they plead guilty or are found guilty of moving violations by the courts, and to notify the court that a warrant for arrest should be issued when such fines are not paid in a timely manner. A separate State Patrol system records accidents and verification of financial responsibility (insurance). Yet a third system produces driving record reports from the ticket and accident records for insurance companies. Finally, a fourth system issues, renews, or suspends driver’s licenses. These four systems are obviously integrated in that they share access to the same database, but otherwise, they are operated separately by different departments of the State Patrol. State Patrol operations (what the officers do) are entirely separate.

The portion of the database used with the ticket processing system involves driver data, ticket data, officer data, and court data. Driver data, officer data, and court data are used by the system. The system creates and maintains ticket data. Driver attributes include license number, name, address, date of birth, date licensed, and so on. Ticket attributes include ticket number (each is unique and preprinted on each sheet of the officer’s ticket book), location, ticket type, ticket date, ticket time, plea, trial date, verdict, fine amount, and date paid. Court and officer data include the name and address of each, respectively. Each driver may have zero or more tickets, and each ticket applies to only one driver. Officers write quite a few tickets.

When an officer gives a ticket to a driver, a copy of the ticket is turned in and entered into the system. A new ticket record is created, and relationships to the correct driver, officer, and court are established in the database. If the driver pleads guilty, he or she mails in the fine in a preprinted envelope with the ticket number on it. In some cases, the driver claims innocence and wants a court date. When the envelope is returned without a check and the trial request box has an “X” in it, the system notes the plea on the ticket record, looks up driver, ticket, and officer information, and sends a ticket details report to the appropriate court. A trial date questionnaire form is also produced at the same time and is mailed to the driver. The instructions on the questionnaire tell the driver to fill in convenient dates and mail the questionnaire directly to the court. Upon receiving this information, the court schedules a trial date and notifies the driver of the date and time.

When the trial is completed, the court sends the verdict to the ticketing system. The verdict and trial date are recorded for the ticket. If the verdict is innocent, the system that produces driving record reports for insurance companies will ignore the ticket. If the verdict is guilty, the court gives the driver another envelope with the ticket number on it for mailing in the fine.

If the driver fails to pay the fine within the required period, the ticket processing system produces a warrant request notice and sends it to the court. This happens if the driver does not return the original envelope within two weeks or does not return the court-supplied envelope within two weeks of the trial date. What happens then is in the hands of the court. Sometimes the court requests that the driver’s license be suspended, and the system that processes drivers’ licenses handles the suspension.

1. To what events must the ticket processing system respond? Create a complete event table listing the event, trigger, source, activity, response, and destination for each event.

2. Draw an entity-relationship diagram to represent the data storage requirements for the ticket processing system, including the attributes mentioned.