CMSC 424 Section 401
Database Design Project

• Customer Data: The name of every customer who ever purchased goods or services from the dealership. This includes name, address, phone #, email address.
• Vehicle Data: Pertinent information on every vehicle the dealership ever worked on. This includes make, model, year, vehicle identification number.
• Sales Data: Data on every vehicle sold through the dealership. In addition to the vehicle data above, the date sold, the customer it was sold to, the temporary tag number, any customization work done to the vehicle, a link to the billing data for that sale, ...
• Service Data: Data covering each service visit at the dealership. The vehicle and customer involved, the mechanic(s) who worked on the vehicle, the vehicle mileage when it arrived at the dealership and when it left the dealership, preliminary estimates, what was actually done to the car, and final billing information, the date the vehicle entered the dealership, the time estimate for the work on the vehicle, the date the work was completed, the date the customer picked up the vehicle...
• Lot Data: It is useful for the dealership to be able to track which vehicles are present in their lot. Shipments of new vehicles arrive, vehicles are sold, various customer vehicles arrive for service and leave. This can be confusing, but it is important (in cases of theft, or to avoid costly mistakes) that the dealership be able to track this information. Some dealerships will have other parking lots at remote sites as well as their main dealership lot.
• Billing Data: Relation connecting customers and billing events -- the date, payment method, customer information, and what dealership goods or service are being purchased. The project is not going to focus on billing information as such -- there are many accounting databases available for handling small-business billing issues. Our database will just store sufficient information to interact with some undefined external billing system.
• Contract Data: Dealerships are involved with a number of contracts that require database management -- car leases, service contracts with vehicle fleets for small companies, service contracts with other customers, promotional contracts, and the like. Representing these appropriately is also important for the dealership database.
• Web Access: Companies like CarMax are expanding into selling cars on the web. Even though this usually involves a physical visit to the dealership at some point, the idea is to get potential customers interested in vehicles by supporting a dealership web presence. Supporting website queries from strangers regarding vehicles available on the lot, or promotional deals available, would be nice. Aside from that, having a web interface to the database to allow uniform access for employees, owners, and database managers, would be a significant plus. Such a web interface could have at least four levels of authorization: one for strangers, one for customers who wanted to check up on their own vehicle status (the status of their vehicle order, or of the service on their vehicle), one for normal dealership employees (mechanics, sales people, accountants), and one for the database administrator.
• Different Views: The dealership has a number of different employee types who require access to the database. Mechanics need to look at vehicle and customer data, and update service data. Sales people need a different view of the database, focussed on vehicle sales. Billing personnel would have a third set of requirements and responsibilities. It would be useful to be able to support giving each type of access their own view of the database.

• Sell a vehicle: vehicles sold might be off the lot, in which case the lot inventory needs to be modified, or they might be sold in expectation of delivery from the factory.
• Lease a vehicle: this is much as with selling vehicles, except that a lease contract is involved, and leased vehicles are treated differently with regard to regular maintenance and service.
• Order a vehicle from the factory: describe the vehicle desired, including all options (leather seats, four-wheel drive, moon roof, whatever), and request that the factory build one like that and send it to the dealership.
• Transfer inventory: it might be useful to support sending and receiving new vehicles from other nearby dealerships, for example if our dealership had a surplus of Rav4s and a Bowie dealership was out of that model.
• Customize a new vehicle: add post-factory options, like roofracks, stereo systems, rust-resistant coating, and the like.
• Service a vehicle: Add a new service record when a vehicle enters the dealership for service; keep track of the ongoing status of the vehicle during service; update the service record after the service is completed and again when the vehicle is picked up by the customer and leaves the dealership.
• Query the database: possible queries include a survey of the billing record for a customer, a survey of the service history for a vehicle, and a variety of other searches Item query. Criteria can include up to one make, model, year, customer, service technician, billing date ... It would probably be useful to set up separate queries for vehicles, customers, and contracts within the database. Salesmen will be interested in making queries regarding vehicles on the lot with certain options, colours, etc. Leaving a field blank indicates that any value for that field is acceptable.
• Insert new customer: All relevant info should be recorded.
• Insert new vehicle: All relevant info should be recorded, whether this is a new vehicle arriving on the lot or an older vehicle being serviced for the first time.
• Release a vehicle: When a vehicle is given to a customer, whether it is a new purchase or a vehicle that is here for service, all relevant data should be checked to ensure that the customer data is OK, the service is finished, title has been transferred if it is a new vehicle, and so on.
• Daily Reports: every day the dealership will run a process to identify ongoing issues. Service issues would include finished vehicles (triggering a call or email to the customer informing them their vehicle is ready), vehicles in service that have spent too much time there (the service took longer than the estimate), and so on. Sales issues would track vehicles ordered from the factory that have not appeared yet, vehicles in the lot that are ready for customer pickup, etc. A daily lot inventory showing how full the lot is, and what vehicles of what makes and models are in the lot, would be useful for salespeople to make orders from the factory when inventory falls -- it wouldn't do for all the vehicles of a given make to be gone from the lot, for example. Daily billing reports would fall under the purview of some external accounting database system that we are not concerned with.
• Weekly Reports: some reports should be done weekly, rather than daily. Sending out service reminders to regular customers and leased vehicles so they can come in and have their car serviced, for example. Statistics on how many cars were serviced, general types of service (oil change, tire rotation, regular 15000 mile overhaul, etc.) are useful to the dealership management for making business decisions. Similar weekly statistic overviews of sales and lot inventory would be useful as well.
• Bulk Load: the data to be imported will be provided.

• Groups: the project is to be done in groups of 2 students. A roster for each group must be submitted to the professor by the date specified. Each group is responsible for its own division of labour. If an irreconcilable problem arises it is your responsibility to contact the professor as soon as possible. After the project is due is too late.
• Assumptions: in cases where the above description of the application is incomplete, it is acceptable to make assumptions about the application providing that: 1) they are explicitly stated in the report, and 2) they do not conflict with any of the requirements specified above, and 3) they are "reasonable" (e.g. it is NOT reasonable to assume that the dealership has only one customer, and the customer never comes in for service) If you have a questions about the acceptability of any of your assumptions, check with the professor or TA.
• Project Reports: a report should be handed in for grading at the end of each phase. The report must be formatted in a reasonable manner (i.e. using a text processor and a decent printer). Reports are due during class on the date specified in the "Due Dates" section of this assignment.
• Implementation: the final phase of the project requires a working implementation of the system to be built, tested, and demonstrated. A large part of the project grade depends upon the quality of this implementation. The implementation will be done using the ORACLE database system. Computer accounts with access to ORACLE will be provided for this purpose. A portion of the project grade will be based on the user interface. A portion of the grade will also depend on the quality of error detection and management, i.e. your system should be robust to data entry errors, etc.

• 1) a short description of the purpose of the project and the purpose of this phase of the project
• 2) a description of the technical/conceptual problems encountered in this phase and justification for the solutions
• 3) the assumptions that you have made about the enterprise
• 4) a description of the procedures in the enterprise, as you imagine they happen
• 5) all the documentation produced in this phase, i.e.
  • the top-level information flow diagram, (very important also, do not forget the system boundary)
  • the list of tasks and subtasks
  • the task forms
  • the list of documents
  • the document forms (very important)
  • the detailed information flow diagrams

The phase II report must contain:

• 1) a short description of the purpose of the project and the purpose of this phase of the project
• 2) the top level information flow diagram from phase I, as well as a list of any revisions that were made to the specification described in the phase I report
• 3) a description of the problems encountered in this phase and justification for the solutions
• 4) the documentation produced in this phase, i.e.,
  • the graphical schema using the E-R model,
  • a list of the attributes for each entity and relationship,
  • explanations of the non-obvious entities and relationships,
  • the DDL statements to create the relational schema in some appropriate Normal Form
  • explanations (e.g., primary keys, etc.),
  • the code for each task: pascal-like pseudo-code and the embedded DML code