Imagine that Pennsylvania, New Jersey, New York and Delaware have “privatized” a bunch of services. MidAtlantic Travel Corp is now responsible for handling Department of Motor Vehicles (drivers license and car registration) Traffic Enforcement (traffic tickets) and Tolls (EZ Pass). They want you to develop a prototype that demonstrates potential functionality (using Microsoft Access – not more large scale technology – that’s in the advanced DB class). We are to develop an integrated database application that will keep track of drivers, cars, tickets, tolls, etc, and info about them that might be of interest for any of the above services. You will need to enter sufficient data to demonstrate the capabilities of your prototype.
Functionality provided should include:
· User friendly interface(s) for data input for each kind of data. Note that since we don’t have scanners, a car going through a toll booth has to be treated as data input.
· User friendly interfaces for update/delete for any kinds of data subject to frequent updates
· Menus leading users to the capabilities that they can make use of
· Support for some pre-planned reports and queries. An initial specification of desirable queries/ reports is given below. This list is tentative and subject to change.
We will talk more about this project in upcoming weeks. Your first task is to arrange yourselves into compatible groups of 2-3 people. This should be done ASAP. Note that the DB design is due Oct 18, a first installment is due Nov 15, and the completed project is due Dec 6. The will be a handout later on project deliverables.
NOTE: Some (NOT ALL) business rules include (even some obvious rules are left out):
You will probably have to ask me about other business rules as you get into design. Some of the queries are tricky too. Don’t wait until the last minute to get going.
Tentative Queries / Reports:
For Department of Motor Vehicles:
1. Show all drivers with too many “points” (12?) – due for license suspension.
2. Show all drivers whose license needs to be renewed (expires between 30-60 days from today)
3. Show all car owners whose car registration needs to be renewed (expires between 30-60 days from today). Use a report so that co-owners can be grouped together instead of generating separate records.
4. Show average age of registered vehicles, and year of oldest vehicle.
5. Show total number of registered vehicles by year (manufactured)
For Police Patrol:
6. Show info on drivers who own the car with Plate X from State Y.
7. Show car and driver info for all cars with Make X, Model Y, and main body color Z.
8. Show all offenses for driver with license number X
9. Show all offenses involving car with license plate X
For Traffic Enforcement Headquarters:
10. Show all tickets issued by officer X on Date Y, with offense, location.
11. Report: Show by Police Officer total tickets issued by type of offense.
12. Show by Area/Division of Police Officer, total tickets issued
13. Show by offense, total tickets issued in time period from date X to date Y
For EZ Pass Toll Authorities:
14. Show by toll booth location, total tolls and total traffic volume (number of registered vehicles going through).
15. Show by Zip Code of EZ Pass account holder, revenues
16. Show frequent customers (cars – those going through more than X times in last Y days)
17. Show for EZ Pass account for Last Name X and First Name Y, the payments between date Z and date W.
18. Show for EZ Pass account for Last Name X and First Name Y, the tolls between date Z and date W.
19. Show the total number of EZ Pass accounts by Zip code.
20. Show EZ Pass account and driver info for EZ Pass accounts with balance greater than $X.
21. Show all bridges with a toll greater than or equal to $3.00, with name and location, ordered by toll amount (descending) with ties broken by name (ascending).
22. For turnpike exit X, show cost for each possible entry point to this exit
NOTES:
· Access provides capabilities for people to quickly sort and filter data. In this project our queries are “in addition to” those capabilities – provided either because the query is frequently done (so we save effort by pre-packaging it) or because the users are not expected to be able to do it on their own.
· Always remember when designing output that these are designed to help humans. Thus, for example, if the primary key value is not meaningful to humans, other identifying info should be displayed in addition/instead. E.g. In addition to or instead of driver license number, display driver name.
· Many of these should be “parameterized” in order to be flexible. This means that the user should be able to specify a value that constrains or personalizes the query. For example, “Show info on drivers who own the car with Plate X from State Y.” Some, but not necessarily all, of the queries that should be parameterized are indicated with X, Y, Z etc.
· If the description includes the word “total”, I’m looking for some summarization, not just a list of raw data. The “by” clause is an attempt to indicate what results are to be summarized “by”.
· It probably makes sense to organize your application by what kinds of users need what capabilities. E.g. Detailed info about tickets will be entered by Traffic Enforcement staff, while EZ Pass enrollment will be handled by EZ Pass staff. If you have any questions about who might do what, please ask.
· All of the above will be queries. Do at least 5 as reports based on their query. Reports are particularly valuable if subtotals or totals or grouping of data is of interest in addition to details.
· Somehow (at least in documentation) let me know which query is which (by number)
· These are tentative. If you find any of these that are problematic, check with me. Also, if you think of any great additional canned queries/reports, feel free to include them.