Integrated CaseGary Cooper (yes - that is his real name, and his nickname is Coop) loves classic cars, but he knows he will never be able to afford most of them. He also knows that most classics are unreliable and expensive to own. Plus, they do not have the features and comforts of modern cars. So, he created a company that constructs custom replicas of classic cars. His replicas have modern mechanical parts and many of the features that can be found in todays' cars. More importantly, they meet modern safety and emission requirements. This case describes Coop's sales and production processes.Coop's Cars: Coop developed a list of replicas for the most sought-after classic cars. He sells replica European Sports Cars, such as Porsche, Ferrari, and Jaguar, as well as some American muscle

Question

cars. Coop tracks each car, engine, and color combination with a custom car product number. The customer gets to select among the various engine and color options for his/her car. Customers typically order one car (one product number), but some customers love the cars so much that they order several at once. Customers must provide at least a 25% deposit with the order, but they can pay the balance after they take delivery of the car.Car chassis: Coop maintains an inventory of car chassis with body shells, interior, seats, etc. Consequently, he can deliver a custom car in just a few days. When the inventory of car chassis gets low, an employee issues a chassis production order. The chassis assembly plan describes the specific parts that go into each chassis as well as the specific chassis assembly operations required. Coop's employees then conduct the assembly operations to assemble the new chassis.Customizing: After the customer places an order and selects the chassis, engine, and color combination, Coop's employees start the customizing operations. The engine is placed in the car and the car is painted. Then, after some initial testing, the car is placed in inventory, ready for delivery to the customer. Although several employees work on the customizing operations, Coop also designates one employee to supervise the process and assure a high quality finished car.Miscellaneous: Coop keeps all his employee records in one table, but he also categorizes employees according to their specialty. He records information about employees, customers, employee types, custom cars, and classic parts before they are linked to other classes.REQUIRED: 1) use the information above and the list of attributes below to draw a UML class diagram showing the classes, associations, and multiplicities, 2) prepare a listing of the tables necessary to support Coop's processes using all the attributes. Name each table and clearly identify primary keys with PK and foreign keys with FK. List your tables in the following order: RESOURCES, EVENTS, AGENTS, TYPE IMAGES, and LINKING. Use only the following list of attributes (remember that there should be no classes without attributes).AttributesActual cost of this chassis partActual qty of this part in this chassisCash account #Cash account balanceCash receipt #Cash receipt dateChassis #Chassis actual costChassis assembly operation #Chassis assembly operation dateChassis assembly order #Chassis assembly order dateChassis assembly plan #Chassis assembly planned sequenceChassis descriptionChassis quantity on handChassis standard costChassis part descriptionChassis part #Chassis part quantity on hand (QOH)Custom car actual costCustom car priceCustom car product #Custom car standard costCustomer #Customer addressCustomer nameCustomer order #Customer order dateCustomer order delivery dateCustomer order total $ amountCustomizing operation #Customizing operation dateEmployee #Employee Hire DateEmployee NameEmployee Pay RateEmployee typeEngine #Engine cylindersEngine horsepowerNumber of employees of this typeNumber of hours this employee worked on this assembly operationNumber of hours this employee worked on this customizing operationQuantity of this custom car ordered on this orderStandard cost of this part in this chassisStandard qty of this part in this chassisTotal actual cost of this assembly operationTotal actual cost of this chassis assembled on this orderTotal standard cost of this assembly operationTotal standard cost of this chassis assembled on this order

What will be an ideal response?

Answer 1

Diagram:


Table listing:

Resources
Chassis Parts = Chassis Part # (PK), Chassis part description, Chassis part QOH, Actual cost of this chassis part
Car Chassis = Chassis # (PK), Chassis description, Chassis QOH, Chassis actual cost, Chassis standard cost
Engines = Engine # (PK), Engine cylinders, Engine horsepower
Custom Cars = Custom Car Product # (PK), Chassis # (FK), Engine# (FK), Custom car price, Custom car actual cost, Custom car standard cost
Cash = Cash Account # (PK), Cash account balance

Events
Assembly Operations = Chassis Assembly Operation # (PK), Chassis assembly operation date, Chassis Assembly Plan# (FK), Chassis Assembly Order# (FK), Total actual cost of this assembly operation, Total standard cost of this assembly operation
Chassis Order = Chassis Assembly Order # (PK), Chassis assembly order date, Total actual cost of this chassis assembled on this order, Total standard cost of this chassis assembled on this order, Chassis# (FK), Employee # (FK)
Customizing Operations = Customizing Operation # (PK), Customizing operation date, Chassis # (FK), Engine # (FK), Employee # (FK), Customer Order # (FK)
Customer Order = Customer Order # (PK), Customer order date, Customer order delivery date, Customer order total $ amount, Employee # (FK), Customer # (FK)
Cash Receipt = C/R # (PK), Cash receipt date, Cash Acct # (FK), Customer Order # (FK), Customer # (FK), Employee # (FK)

Agents
Employee = Employee # (PK), Employee Type (FK), Employee hire date, Employee name, Employee pay rate,
Customer = Customer # (PK), Customer address, Customer name

Type Images
Chassis Assembly Plan = Chassis Assembly Plan # (PK), Chassis# (FK), Chassis assembly planned sequence,
Employee Type = Employee Type (PK), Number of employee of this type

Linking Tables
Chassis Assembly Plan-Chassis Part = Chassis Assembly Plan # + Chassis Part # (PK), Standard cost of this part in this chassis, Standard qty of this part in this chassis
Chassis Parts-Assembly Operations = Chassis Part # + Chassis Assembly Operation # (PK), Actual qty of this part in this chassis
Assembly Operations-Employee = Chassis Assembly Operation # + Employee # (PK), number of hours this employee worked on this assembly operation
Customizing Operations-Employee = Customizing Operation # + Employee # (PK), number of hours this employee worked on this customizing operation
Custom Cars-Customer Order = Custom Car Product # + Customer Order# (PK), Qty of this custom car ordered on this order