Task for the access database "Passenger autoenterprise" (Transportation on inner city routes)
The municipal auto enterprise carries out passenger transportations on inner city routes. The motor company has a fleet of buses that operate on certain routes. The work of the auto enterprise is provided by the personnel of the enterprise, which can be divided into the categories of posts occupied by the administration, engineering personnel and personnel servicing the routes (drivers, conductors). On the route, the bus driver receives a route sheet (or a waybill) containing information about the bus, route, mode of operation, driver, conductor.
An auto enterprise is described by data that can be grouped as follows:
- buses (bus number, state number of the bus, brand, year of manufacture, mileage);
- Routes (route number, route, length of the route in km, average time of one flight, planned number of flights per shift);
- personal data of the personnel (personnel number, full name of the employee, date of birth, home address, home phone, work phone);
- personnel records (personnel number, category, position, date of hiring, bus number (for drivers and conductors));
- Route sheets (route number, bus number, date, number of performed flights, driver, conductor).
It is possible that in a real auto enterprise a greater amount of data is taken into account, however, the assignee of the assignment can be limited to those listed. If desired, you can enter data for labor compensation in the personnel records if you want to automate the calculation of wages. In routing sheets, you can enter plan and actual revenue per shift, respectively.
The created information system, first of all, should provide input and editing of data in a user-friendly form. In addition, it is possible to formulate a variety of requests for both the staffing of the enterprise, and the characteristics and technical support of the routes, for example:
- give out full or partial information on personnel;
- give out full or partial information on the bus fleet;
- give out full or partial information on the routes.
In particular, requests can be:
- issue a list of the administration´s employees with the indication of the position;
- on a certain date for all numbers of routes to give information on the number of buses serving each route;
- for each route number and date (request parameters) to give information about buses serving the route: the onboard number, the brand, the state. bus number.
Other tasks can be solved. For example, based on the work results for a month, calculate the number of flights performed by each bus or on each route. Based on the work results for the month, calculate the number of shifts worked by each driver and conductor.