slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Independent Auto Collision Repair Team #2 PowerPoint Presentation
Download Presentation
Independent Auto Collision Repair Team #2

Loading in 2 Seconds...

play fullscreen
1 / 27

Independent Auto Collision Repair Team #2 - PowerPoint PPT Presentation


  • 375 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - 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???