Woodys Automotive The following specifications are intended as a starting point; they are not the complete specifications. These are intended to be a basis for you to get started in the right direction in designing your system. You as the designer must analyze and decide what other details or features should be specified for your system. Thus, individual group implementations will differ in terms of design and implementation styles. Every group has to describe clearly in its report what other specifications are assumed. However: (a) they should not contradict the assumptions described below, and (b) they have to be clearly stated in your report. DATABASE REQUIREMENTS (Specifications for the design of the database) Woody Randolph owns a chain of 5 auto repair shops in Northern New Jersey and has plans to expand across the state. Woody wants an on-line system to help him manage his growing company. The company wants to build a database system for customers, employees, and service transactions. You have been approached to help Woodys Automotive with the database design and application development. 1. Each customer has an ID, name, home address, a telephone number, an email address and a credit card for payment. 2. Each customer has one or more vehicles. Each vehicle has a type (sedan, SUV, sports car, truck), manufacturer, model, color, and year. 3. Woodys Automotive offers a list of maintenance and repair services: a. Oil changes b. Front-end alignments c. Brakes d. Tire repairs and replacements e. Engine tune-ups f. Vehicle computer diagnostics g. State vehicle inspections 4. All services have a price for labor, which are based on the service and the vehicle type, and additional charges for parts. 5. Woodys wishes to maintain an inventory of parts at each location. Each part has a name and a retail price. 6. Prices for service labor and parts can be changed by management at any time using the on-line application. However, the price for labor and parts is fixed at the time a service appointment is made and cannot be changed. Those prices are recorded in the invoices and used for management reports 7. Customers will be able to self-register and make service appointments at the repair shop of their choice using the on-line system. 8. Customers will be able to track the current status of their service appointments waiting, in progress, waiting for parts, complete, etc. 9. Once service is complete, an invoice is generated and paid for via customer credit card and recorded in the database. Transactions include a complete record of the service provided, including labor and parts charges, the total charge, the shop location, the technician, the customer and vehicle, and the date of service. 10. Each Woodys Automotive repair shop location has a manager and between 4 and 6 repair technicians. The database will record the name, location, role, and start date for each manager and technician. 11. Each technician has the skills to perform one or more of the repair services that the company offers. The database needs to record the skills for each technician. 12. Each morning the location manager must print out a report of the services scheduled to be performed that day. FUNCTIONAL REQUIREMENTS (Specifications for the development of the application programs) The Woodys Automotive is interested in an online application program interface that is easy to use so that they do not have to spend time unnecessarily to train their employees. Woody would also like the program interface to be easy for the customers. In particular, Woodys Automotive is interested in the next three applications: Customer registration and service appointments, Shop Management (workload reporting and monitoring, service transaction processing), and Management and Reporting. The last application is crucial in decision making by the owner and Accounting Department of the company. For instance, it will allow the workload and profitability at each location. As database and application program designers you are expected to write a menu-based main program which consists of the following three application programs for the daily transactions of the company and is open to the inclusion of additional functionality in the future. Note that many functions are left out in order to reduce the size and the complexity of the project. 1. Customer Registration and Service Appointments Every customer has to register before being able to place a service order. This interface will help the customers register (themselves and their automobiles) and manage/edit their registration information. Customers will be able to initiate service orders for their vehicles using a simple menu-driven system. 2. Shop Management and Transaction Processing Once a vehicle has been dropped off for service, the customer should be able to check and inquire about the status of his/her order. The status of an order is updated as the shop processes it. The customer should be able to view his service history and pull up specific transactions based on different criteria (for instance, based on the vehicle serviced or the type of service). The location manager should be able to view all service appointments for a selected date. Service appointments are closed out by recording and processing an invoice and its payment. 3. Management and Reporting Management uses this application to review and/or change prices for services (by vehicle type) and parts. This program provides statistics used by Woodys Automotive to help management to analyze its locations, sales and customers. In particular, the program should be able to make the following computations: 1. For a given day and location, a report of the pending service appointments. 2. For a given time period (begin date and end date) compute revenue by location and service type. 3. For a given time period (begin date and end date) compute the top 3 locations (in terms of total service revenue) in descending order. 4. For a given time period (begin date and end date) compute the 5 best customers (in terms of total service revenue). 5. For a given time period (begin date and end date) compute the average service revenue for each vehicle type at each location.