raw an Entity Relationship Diagram (ERD) representing the following business scenario and database using MS Visio. Located in Kailua, on the windward side of Oahu, Elamo Automobile Repair (EAR) repairs automobiles. EAR has 12 employees, and completes about 150 automobile repairs per year. The company is generally successful, but poor record keeping and information management have limited its growth and profitability. In particular, EAR believes that improved information on automobile repair estimation and resource management related to
Draw an Entity Relationship Diagram (ERD) representing the following business scenario and
Located in Kailua, on the windward side of Oahu, Elamo Automobile Repair (EAR) repairs automobiles. EAR has 12 employees, and completes about 150 automobile repairs per year. The company is generally successful, but poor record keeping and information management have limited its growth and profitability. In particular, EAR believes that improved information on automobile repair estimation and resource management related to their repairs would allow them to analyze and improve their operations, and generate repair estimates that are more accurate and competitive. Therefore, they have tasked you to develop a database to support their automobile repair estimation and repair resource management.
Specifically, an automobile repair refers to the repair of an automobile for a customer. Before EAR can repair an automobile for a customer, they must first provide an estimate for the repair. If a customer approves of the estimated amount for the repair, then the repair will be initiated. The database will contain information on both automobile repair estimates and automobile repairs. To support the operational analysis desired by EAR management, the database must record the resources that are estimated to be required to complete a repair (for repair estimates), and the resources consumed throughout the completion of a repair (for repairs). The resources consumed in an automobile repair include various quantities of parts and the utilization of specialized equipment and skilled technicians (measured in time).
The database will contain information on the customers of EAR including their name, phone number, and address. Automobiles belong to a single customer, and are described by a year, make, model, and VIN. Repairs and repair estimates pertain to repairing a single car for a single customer. A description of the proposed repair, estimated amount, and date of the estimate are recorded for each repair estimate. For repairs, a description of the repair, initiation date, deposit amount, completion date, and total amount are stored. Each repair estimate includes various parts and time required from specialized equipment and skilled technicians, entered into the database when the estimate is determined completely and approved by EAR management. Repairs are entered into the database when the repair is initiated, with no resources yet consumed, but with managers assigned.
There are two types of employees that work for EAR, managers and technicians. The database will contain the name, phone number, and date of hire for each employee. Employees will be entered into the database when hired, before working on any automobiles. Managers have oversight responsibilities at EAR. Each repair estimate is reviewed and approved by a manager, and one or more managers are assigned to supervise each repair. A manager can supervise many repairs over time, and approve many estimates. Technicians have one or more technical skills, which are required to repair automobiles. These skills are for example engine
Information regarding the parts utilized by EAR to repair automobiles includes a description, unit cost, and quantity on hand. The specialized equipment required by technicians to perform their skilled repairs are described by a description, manufacturer, and model. Parts and equipment acquired by EAR are entered into the database before being utilized in a repair. When a repair estimate is calculated, the quantities of various parts and estimates of time using each piece of specialized equipment required to complete the repair are recorded. So for example, a collision repair estimate may include 1 fender, 2 liters of metallic paint, 4 hours using the welder, and 7 hours using the paint booth.
The database will also contain information on the resource consumption of repairs, allowing EAR to manage repairs on an ongoing basis. When technicians work a shift on a repair, their starting date and time and ending date and time are recorded. When specific parts are required for a repair, the date that the parts are retrieved to be utilized in the repair and the quantities consumed are also recorded. Finally, when a specific piece of specialized equipment is needed for a task in a repair, it is checked out by a technician. The check-out and check-in dates and times are recorded, as well as the specific technician checking out the equipment.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 1 images
