Requirements Deliverable 1 must contain an analysis of the intended database system, and a conceptual schema. It must describe the problems encountered in this phase and justify the solutions. What is expected: 1. Outline the goals of this phase of the project. 2. Analyze the entire application for Woodys Automotive and compose with an Extended ER diagram. Use the notation for E/R model constructs and additional notation for specialization/generalization hierarchies we learned in class. Show entity types, relationship types (including class/subclass relationship types), and attributes. Show different types of attributes (simple/composite, single-valued/multi- valued, stored/derived) if needed. Show roles on recursive relationship types. Show key attributes (only one key per entity type should be shown on the ER diagram. Include structural constraints (cardinalities ratio and participation constraints). Use both notations for the constraints of relationship types on the diagram as an exercise (the traditional one and the (min,max) notation). Use some diagram editor to draw a clean diagram. Hand-written diagrams will not be accepted. 3. Mention any assumptions you made in doing the above that go beyond what is given in the project description. In particular, mention all assumptions that led you in determining structural constraints. 4. Make a list of constraints that apply over and above what you can show in the diagram. In particular, make a list of additional keys for entity types (if there are any). 5. Besides the above, comment on the difficulties you faced in doing this conceptual design task. NOTE: The above specification will drive your subsequent phases. As in any actual design and implementation, you will be revising what you do here as you proceed through the phases. Please be explicit and detailed when writing your deliverable. This will help you in the long run! The Automotive Repair Shop Application 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.