Independent Auto Collision Repair
Download
1 / 27

Independent Auto Collision Repair - PowerPoint PPT Presentation


  • 335 Views
  • Updated 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

PowerPoint Slideshow about 'Independent Auto Collision Repair' - Anita


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

Overview l.jpg
Overview

  • Client

  • EER Diagram

  • Relational Schema

  • Database

  • Normalization Analysis

  • Queries

  • Q&A


Client independent auto collision repair l.jpg
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



Relational schema l.jpg
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)


Relational schema6 l.jpg
…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)


Relational schema7 l.jpg
…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)


Database relationships l.jpg
Database - Relationships


Database forms l.jpg
Database - Forms

Form allows client to easily add new customers and search through existing

Form allows client to input estimates



Database switchboard l.jpg
Database - Switchboard

Main Switchboard for easy navigation and data inputting


Normalization analysis l.jpg
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)


Normalization analysis13 l.jpg
…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)


1 query service level l.jpg
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


1 query sql l.jpg
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));


Query 1 execution l.jpg
Query 1 Execution

Query Input:

Query Output:


2 query equipment life cycle l.jpg
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


Maintenance modeling l.jpg
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


2 query sql l.jpg
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;


Query 2 execution l.jpg
Query 2 Execution

Query Input:

Query Output:


3 query productivity level l.jpg
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


3 query sql l.jpg
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;


4 query demand trends l.jpg
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


4 query sql l.jpg
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;


5 query profits l.jpg
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


  • 5 query sql l.jpg
    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;


    Questions l.jpg
    Questions ? ? ?

    Any questions???


    ad