A relational database application for supporting a chain of car repair and service centers by a company. Using a Java based CLI with Oracle SQL based backend. The application handles role-authorized actions. The user roles are customer, receptionist, and manager. This is accomplished through the combination of menus that present only appropriate actions for the given role and context, and advanced features like Views, Procedures or Triggers and Authorization.
A detailed description of the project is given below:
Every state in the United States has three service centers, each center independently managed by a manager, a receptionist and at least five mechanics, and operates 5 days a week (M-F) from 8 AM to 7 PM. Each service center is identified by a unique ID, a name, an address, and a telephone number . The employees at these locations fit into one of the three roles: manager, receptionist, and mechanic . For each employee, we store their 9-digit employee ID, name, address, email address, phone number, the service center he works at, their role at the service center . An employee can work at only one service center. Additionally, we want to store payroll details about the wages paid to the employees (start date, wages, frequency, etc.). The location anager and receptionist are paid a monthly salary while the mechanic is paid hourly based solely on the number of hours that he works servicing/repairing a vehicle (a maximum of 11 hours a day). Assume that a paycheck is generated on the 1st and 15th each month.
A customer in our system is identified using a unique customer id . Additionally, the system records the following information about the customer: name, email address (unique), address, phone number, and the vehicles he gets serviced/repaired at service centers owned by Acme. For each car, the system records the license plate number (which uniquely identifies the car), the type of the car (make, model and year), date of purchase, the last recorded mileage, the type of most recent service/repair, and the date of most recent service/repair . For sake of simplicity, we shall assume that car ownership does not change and also assume that the license plate number of the car always remains the same.
For each service center, we want to keep track of its parts inventory. For each part in the inventory, that is uniquely identified by a partid , we want to record its name, current quantity, unit price, a threshold for the minimum quantity that must be present in the inventory and a minimum order threshold specifying the minimum quantity that must be ordered for the part whenever an order is placed (see next). If the quantity for a part falls below the given threshold for any reason, the system must automatically create an order (see next) for that part to maintain the quantity above its threshold. A part can be supplied by a distributor (uniquely identified by distributor Id ) and each distributor has a delivery window for each part e.g. 4 days, 7 days etc.
The part ordering process follows these steps:
-
Calculate the desired quantity to be ordered. One way to do this is to calculate the value max(required quantity, minimum order threshold) .
-
Check if any of the other Acme centers has the part in sufficient quantity such that ordering the part from that center does not cause its inventory to fall below the minimum threshold.
-
If one or more such centers are found, an order should be placed to obtain the part from the center with the largest available quantity for that part.
-
If no such center is found, the part should be ordered from its corresponding distributor. Placing an order creates an order record with at least the following details: order id, date, part id, quantity, center/distributor id where the part is coming from, the center where the part is going to, order status (complete/pending) . A separate order is created for each part that needs to be ordered. An order status always starts as “pending” and is changed to “complete” when the order is received by an employee.
Services offered by Acme are either maintenance or repair. For each type of car that is serviced by Acme service centers, there is a predetermined maintenance service schedule based on the number of miles traveled since the last service. Three types of services are provided for each car type: Service A, Service B, and Service C. The service type is determined by the given number of miles on the vehicle e.g. Service A-10K miles, Service B-25K miles, etc. In general, service schedules rotate, first Service A, then B, then C then back to A and so on. Each service includes a number of basic services e.g. oil and filter change, and each basic service is associated with a set of parts in specific quantities. Each basic service is associated with an estimate on hours to complete and a labor charge rate in dollars per hour. There are basically two labor charge rates used by Acme, a low charge rate and a high charge rate. It is anticipated that some services like changing tires would have a cheaper charge rate than work involving an engine. Acme also provides labor as a complimentary service. That is, any service that is provided for the first time will be charged only for the parts. If it is provided again then the customer will be charged for both parts as well as labor. Also, certain basic services come with a warranty. Each service type structure is inclusive, i.e., Service B provides everything that Service A does plus some additional services, and Service C provides everything that Service B does plus some additional services.
Repair services include a specific problem reported by the customer, a diagnostic service and fee, and then the actual repair and fees (parts and labor). The diagnostic service produces a report which includes the identified faults and list of recommended repair services. If the repair includes a warrantied service, the cost of the repair is done at no charge. However, there is still a diagnostic fee included. The system should maintain records of all service appointments for each customer and the invoices associated with each service. The daily schedule at Acme is broken into 30 minutes time slots from 8 AM to 7 PM. A customer may request an appointment for either a repair or a maintenance service. Each request always includes the car type, model, license plate number and current mileage on vehicle and preference for a mechanic (optional). In response to a maintenance request, the two earliest, “possible” time slots are offered to the customer. By “possible” we mean, the time slots whose duration are sufficient for the estimated time for the required service. The required service is determined by the current mileage of car given by customer and information about the last service. A repair service request is done similarly but also includes the problem that led to the request. Scheduling of appointments tries to ensure that no more than half the day is allocated to maintenance requests in order to allow enough daily openings for repairs which tend to be more urgent. The customer then selects one of the two appointments offered. A customer may also later request a rescheduling of an existing appointment. Besides the availability of a time slot, the scheduling needs to check the availability of parts needed for the service. All parts needed should be either available within the local inventory of the shop, the local inventory of one of the other Acme shops, or the supplier’s inventory with a supplier delivery window that is at least 1 day prior to appointment date. It is important that the system not allow over commitment of parts or double counting. For example, the fact that a part exists in an inventory should not be used to confirm multiple services that will use the same part. In other words, confirming an appointment should capture the fact that though a part is in the inventory it has been committed for a service and will be used by a particular date. When an appointment is rescheduled, the commitment dates for parts will change. In summary, the appointment scheduler should only offer those slots to a customer that meet both the inventory and time slot constraints.
At the beginning of every day, three inventory updating tasks are run:
-
Update the counts of parts to be used that day, basically adjusted (decrementing them) to reflect the fact the parts will be removed and actually used that day
-
Update the status of any pending orders whose items have arrived to “complete” and update their counts
-
For any pending orders whose orders have not arrived but is past the delivery window for the distributor, a notification is generated.