1 / 27

Independent Auto Collision Repair Team #2

Independent Auto Collision Repair Team #2. Overview. Client EER Diagram Relational Schema Database Normalization Analysis Queries Q&A. Client: Independent Auto Collision Repair. About Auto Body & Paint Shop Location: Reseda, CA Insurance and Walk In Customers 5 Employees

iona
Download Presentation

Independent Auto Collision Repair Team #2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Independent Auto Collision Repair Team #2

  2. Overview • Client • EER Diagram • Relational Schema • Database • Normalization Analysis • Queries • Q&A

  3. Client: Independent Auto Collision Repair • About • Auto Body & Paint Shop • Location: Reseda, CA • Insurance and Walk In Customers • 5 Employees • Suppliers: Part Dealers • Currently… • No software for data collection • Paper records and invoices • No metrics • Difficult to track costs and work flow

  4. EER Diagram

  5. Relational Schema 1) Insurance Company (Insurance_id, Insurance_name, Claim_id, Adjuster_name, Address, City, State, Phone_no, e-mail) 2) Customer(DL_no, Fname, Lname, Address, City, State, Phone_no, Feedback_no, Total_payment, Referred_by, DL_no2) 2.a) Insurance Customer(DL_no2, Insurance_id1, Claim_no, Insurance_name, Adjuster_name, Deductible, Insurance_coverage, Insurance_estimate) 2.b) Non-Insurance(DL_no2) 3) Returned_Vehicle(RID, Defect, Date,DL_no2) 4) Feedback(Feedback_no, Feedback_received, Feedback_reviewed, Work _Quality, feedback_accuracy, Service_length, Customer_service, customer_id2, repair_id6.d) 5) Vehicle (VIN, License_plate_no, Make, Model, Year, Mileage_count, Primary_color, Other_colors, Process_id6) 6) Process (Process_id, SSN) 6.a) Estimation(Process_id6, Estimated_labor_hrs, Estimated_labor_cost, Parts_cost, Total_estimate, Insurance_estimate 6.b) Approval(Process_id6) 6.c) Disassembly(Process_id6) 6.d) Repair(Process_id6, Repair_id, Car_part, IID13, Order_id9.13) 6.d.i) Bodywork(Process_id6.d, working_hour) 6.d.ii) Painting(Process_id6.d, working_hour) 6.d.iii) Other(Process_id6.d, working_hour) 6.e)Assembly(Process_id6)

  6. …Relational Schema 6.f)Payment(Process_id6, SSN7.b) Employee(SSN, Hourly_wage, Address, City, State, Phone_number, e-mail, Position, Process_type) 7.a) Technical(SSN7, expertise) 7.b) Clerical(SSN7, Order_id9) 7.c) Other(SNN7) 8) Timeslot(Start_Time,End_Time, Month, Day, Year, TS_id) 9) Part_Order(Order_id, Item_name, Revision Order_quantity, Brand, Tax_amount, Price, Invoice_no11, Dealer_id10, Barcode) 10) Part_Dealer(Dealer_id, Dealer_location) 11) Invoice(Invoice_no,Process_id6, DL_no2, b_cost, b_part_cost, p_cost, p_part_cost, o_cost, o_part_cost) 12) Equipment(Equipment_id, Date_acquired, price, color) 12.a) Frame_Rack(Equipment_id12) 12.b) Valve_Seal_Toolkit(Equipment_id12) 12.c) Puller_Chain(Equipment_id12) 12.d) Auto_Lift(Equipment_id12) 12.e) Welding_Station(Equipment_id12) 12.f) Hand_Toolkit(Equipment_id12) 12.g) Compressor(Equipment_id12) 12.h) Paint_Booth(Equipment_id12)

  7. …Relational Schema 12.i) Other(Equipment_id12) 13) Part_Inventory(item_name,revision,Order_id9, weight, price, brand, tax, location_of_supplier, barcode) 14) Gives(DL_no2, Feedback_no4, Gives_date, VIN) 15) Owns(DL_no2, VIN5) 16) Moves_through(Process_id6, VIN5) 17) Needs1(RID3, Process_id6) 18) Perform_start(TS_id8, Process_id6.d) 19) Peformed_end(TS_id8, Process_id6.d) 20) Start_work(TS_id8, SSN7) 21) End_work(TS_id8, SSN7) 22) Analyzes(RID3, SSN7.a) 23) Uses(Process_id6.d.iii, Equipment_id12.i) 24) Scheduled_for(Equipment_id12.i, TS_id8) 25) Requires(Equipment_id12.i, Process_id6.d) 26) Stores_into(IID13, Order_id9) 27) Start_Process(TS_id8, Process_id6) 28) End_Process(TS_id8, Process_id6) 29) Start_Equiment(TS_id8, Equiment_id12) 30) End_Equiment(TS_id8, Equiment_id12)

  8. Database - Relationships

  9. Database - Forms Form allows client to easily add new customers and search through existing Form allows client to input estimates

  10. Database - Report

  11. Database - Switchboard Main Switchboard for easy navigation and data inputting

  12. Normalization Analysis Example: Part_Order(Order_id, Item_name,Revision Order_quantity, Brand, Tax_amount, Price, Invoice_no11, Dealer_id10, Barcode) 1 INF: Part_Order(Order_id, Item_name,Revision Order_quantity, Brand, Tax_amount, Price, Invoice_no11, Dealer_id10, Barcode) 2 INF: R1(Order_id, Invoice_no11, Dealer_id10) R2(Item_name, Brand) R3(Item_name,Revision, Barcode, Order_quanity, Price, Tax_amount)

  13. …Normalization Analysis Continued Example: Part_Order(Order_id, Item_name,Revision Order_quantity, Brand, Tax_amount, Price, Invoice_no11, Dealer_id10, Barcode) 3 INF: R1(Order_id, Invoice_no11, Dealer_id10) R2(Item_name, Brand) R3(Price, Tax_amount) R4(Item_name,Revision, Barcode, Order_quanity) BCNF R1(Order_id, Invoice_no11, Dealer_id10) R2(Item_name, Brand) R3(Price, Tax_amount) R4(Item_name,Revision, Order_quanity) R5(Barcode, Item_name)

  14. 1. Query: Service Level What is our average customer service level? • Customer service level • Average values in FEEDBACK entity to determine customer satisfaction • Lead time, estimate accuracy, quality,repairid, customerid • Justification • To understand current standing with customers and identify areas of improvement

  15. 1. Query SQL SELECT E.Process_id, F.Feedback_accuracy*(F.Customer_Service+F.Work_quality)/2+(E.Estimated_Service_Length-F.Service_Length)/E.Estimated_Service_Length*100+(E.Total_estimate-(I.Overall_part_cost+I.Overall_labor_cost))/E.Total_estimate*100 AS Average_service_level_percent FROM Feedback AS F, Invoice AS I, Estimation AS E WHERE (((F.Process_id)=I.Process_id) And ((E.Process_id)=F.Process_id))UNION SELECT AVG_FEEDBACK_LEVEL, AVG(F.Feedback_accuracy*(F.Customer_Service+F.Work_quality)/2+(E.Estimated_Service_Length-F.Service_Length)/E.Estimated_Service_Length*100+(E.Total_estimate-(I.Overall_part_cost+I.Overall_labor_cost))/E.Total_estimate*100) AS Average_service_level_percent FROM Feedback AS F, Invoice AS I, Estimation AS E WHERE (((F.Process_id)=I.Process_id) And ((E.Process_id)=F.Process_id));

  16. Query 1 Execution Query Input: Query Output:

  17. 2. Query: Equipment Life Cycle What is the optimal preventive or breakdown maintenance schedule? • Determine optimal maintenance schedules and failure frequencies • Installation date, recorded failures and maintenance, manufacturer information • Justification • Determine optimal maintenance procedures • Breakdown vs Preventative maintenance

  18. Maintenance Modeling • C1 = cost of preventive maintenance (PM) by repairing after breakdown • C2 = PM cost by repairing before breakdown • F(t) = total probability of breakdown with repair every t periods • k = period # • pk = probability of breakdown at period k if repairs done every period

  19. 2. Query SQL SELECT E.Equipment_id, (E.cost_break*(E.Probfail1 + E.Probfail2 + E.Probfail3 + E.Probfail4) + E.cost_repair*(1-(E.Probfail1 + E.Probfail2 + E.Probfail3 + E.Probfail4))) / ((1*E.Probfail1 + 2*E.Probfail2 + 3*E.Probfail3 + 4*E.Probfail4)  + 4*(1-(E.Probfail1 + E.Probfail2 + E.Probfail3 + E.Probfail4))) as Cost_for_maintenance_for_every_fourth_period FROM Equipment as E UNION SELECT E.Equipment_id, (E.cost_break*(E.Probfail1 + E.Probfail2 + E.Probfail3) + E.cost_repair*(1-(E.Probfail1 + E.Probfail2 + E.Probfail3))) / ((1*E.Probfail1 + 2*E.Probfail2 + 3*E.Probfail3)  + 3*(1-(E.Probfail1 + E.Probfail2 + E.Probfail3))) as Cost_for_maintenance_for_every_third_period FROM Equipment as E UNION SELECT E.Equipment_id, (E.cost_break*(E.Probfail1 + E.Probfail2) + E.cost_repair*(1-(E.Probfail1 + E.Probfail2))) / ((1*E.Probfail1 + 2*E.Probfail2)  + 2*(1-(E.Probfail1 + E.Probfail2))) as Cost_for_maintenance_for_every_second_period FROM Equipment as E    UNION SELECT E.Equipment_id, (E.cost_break*E.Probfail1 + E.cost_repair*(1-E.Probfail1)) / ((1*E.Probfail1) + 1*(1-E.Probfail1)) as Cost_for_maintenance_for_each_period FROM Equipment as E;

  20. Query 2 Execution Query Input: Query Output:

  21. 3. Query: Productivity Level Who are the most efficiently productive employees? • Employee productivity level • Use customer feedback • Returns, complaints, returning customers • Compare estimates to actuals • Justification • To identify employees who might need additional training • Who to fire

  22. 3. Query SQL SELECT E.SSN, T.Month, T.Year, a*((PE.True_time – PS.Estimated_time)/PE.Estimated_time)*100 + b*F.Feedback_accuracy +c*F.Work_quality FROM Feedback F, Employee E, Timeslot T, Perform_end PE, Perform_start PS WHERE F.Repair_ID = R.Repair_ID AND R.Process_ID = PS.Process_ID AND R.Process_ID = PE.Process_ID AND PE.SSN = E.SSN AND T.TSID = PS.TSID AND T.TSID = PE.TSID AND T.Year = ‘2008’ GROUP BY E.SSN, T.MonthORDER BY a*(PE.true_time – PS.estimated_time) +b*(F.feedback_accuracy * F.work_quality) DESC;

  23. 4. Query: Demand Trends What are the busiest months and for what job type? • Forecasting demand and seasonality • Determine average number of jobs per type and per month • Bodywork, painting, other, … • Justification • To better predict labor needs during specific seasons • To determine profitability of physical expansion • To determine safety stock and decrease customer lead time

  24. 4. Query SQL SELECT T.year, T.month, count(B.Process_id) FROM Timeslot T, Bodywork B, Invoice I GROUP BY T.year, T.month WHERE T.process_id = I.process_id and B.process_id = I.process_id ORDER BY T.year, T.month, DESC UNION SELECT T.year, T.month, count(P.process_id) FROM Timeslot T, Painting P, Invoice I GROUP BY T.year, T.month WHERE T.process_id = I.process_id and P.process_id = I.process_id UNION SELECT T.year, T.month, count(O.Process_id) FROM Timeslot T, Order O, Invoice I GROUP BY T.year, T.month WHERE T.process_id = I.process_id and O.process_id = I.process_id;

  25. 5. Query: Profits What is the repair job that yields the most monthly profit? • Invoices, labor hours, lead times • Job type frequencies (demand)… • Justification • Determine profitability of specializing • Better prioritize high marginal profit jobs

  26. 5. Query SQL SELECT (sum(I.B_cost) – sum(I.B_part_cost) – Hourly_wage * sum(B.working_hours)) / sum(B.working_hours) FROM Invoice I, Bodywork B WHERE I.process_id = B.process_id UNION SELECT (sum(I.P_cost) – sum(I.P_part_cost) – Hourly_wage * sum(P.working_hours)) / sum(P.working_hours) FROM Invoice I, Painting P WHERE I.process_id = P.process_id UNION SELECT (sum(I.O_cost) – sum(I.O_part_cost) – Hourly_wage * sum(O.working_hours)) / sum(O.working_hours) FROM Invoice I, Other O WHERE I.process_id = O.process_id;

  27. Questions ? ? ? Any questions???

More Related