slide1
Download
Skip this Video
Download Presentation
Independent Auto Collision Repair Team #2

Loading in 2 Seconds...

play fullscreen
1 / 27

Independent Auto Collision Repair - PowerPoint PPT Presentation


  • 342 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

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
Overview
  • Client
  • EER Diagram
  • Relational Schema
  • Database
  • Normalization Analysis
  • Queries
  • Q&A
client independent auto collision repair
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
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
…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
…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 forms
Database - Forms

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

Form allows client to input estimates

database switchboard
Database - Switchboard

Main Switchboard for easy navigation and data inputting

normalization analysis
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
…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
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
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
Query 1 Execution

Query Input:

Query Output:

2 query equipment life cycle
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
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
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
Query 2 Execution

Query Input:

Query Output:

3 query productivity level
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
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
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
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
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
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
Questions ? ? ?

Any questions???

ad