1 / 39

Asbestos and Preventive Measures Database Project

Group #5: Ahmed Osman , Min Suk Kim, Miranda Ortiz, Moises Coronado, Paul Kim, Rhonda Nassar , Bong Su Jang, and Will Drevno. Asbestos and Preventive Measures Database Project. Overview. Client Background Client Needs EER Diagram Relationship Schema Access Database

marietta
Download Presentation

Asbestos and Preventive Measures Database Project

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. Group #5: Ahmed Osman, Min Suk Kim, Miranda Ortiz, Moises Coronado, Paul Kim, Rhonda Nassar, Bong Su Jang, and Will Drevno Asbestos and Preventive Measures Database Project

  2. Overview • Client Background • Client Needs • EER Diagram • Relationship Schema • Access Database • Normalization Analysis • Queries • Q/A

  3. Client Description • Maintains service facilities for all buildings on the UC Berkeley campus • Tests buildings for asbestos and lead • Provides maintenance for: • Electrical power distribution system • Cogeneration plant • Asbestos abatement • Fire drills • Paint

  4. Project Summary • Part 1: Asbestos • Record results of asbestos and lead samples • Keep track of locations previously visited • Show locations with the highest concentrations of asbestos • Provide tool for analysis of inspection reports • Part 2: Preventative Maintenance • Keep track of work orders • Ensure that preventative measures management is not performed more frequently than necessary • Calculate percentage of work orders completed within required date • Monitor equipment and inventory

  5. Benefits • Increased safety and improved health due to quicker realization of hazardous material contamination • Decreased response time to work-orders • Ease in performing analysis of asbestos data • Reduce costs • Prevent equipment from breaking down • SAVE LIVES! 

  6. EER Diagram

  7. Relational Schema • CLIENT(CID, FNAME, MI, LNAME, ADDRESS, PHONE, E-MAIL) • CLIENT_WORK_ORDER(CWID, Submitted_by_CID1, Sent_to_MID6a, For­_Room13 , For­_Building13, Date_Submitted) • EXTERNAL_AUDITOR(EAID, Fname, Lname, Organization, Contact_Number, E-mail, Address) • LAB (LAB_ID, Lab_Name, Address, Contact_Person, Phone_Number, E-mail) • SAMPLE(SID, Quantity, Collected_by_FID6b, Required_by_Audit_Record_ID11, Room13, Building13,Area_of_Room, Direction, Description, Image) • EMPLOYEE(EID, Supervised_by_EID6, Fname, MI, Lname, E-mail, Phone_Number, D.O.B.) a. MANAGER(MID6, Salary) b. FIELDWORKER(FID6, Wage) c. MECHANIC (MECHID6, Wage) • BUILDING(BNAME, Address, Number_of_Floors, Number_of_Rooms, Floor_Plan, Safety) • PM_WORK_ORDER(PMWID, Sent_to_Manager6a, Worked_by_MECHID6c, Equip_Inv18, Equip_ID18, Issue_Date, Generation_Time, Status, Task, Asset, Equipment_Note, Remark, Condition) • ORDER(OID, Placed_by_MID6a, Sent_to_Sup_ID10) • SUPPLIER(SUP_ID, Company_Name, Contact_Person, E-mail, Address, Phone_Number • AUDIT_RECORD(ARID, CWID2, Audited_By3, Sampled_by4, Received_by_CID1, Requested_by_Manager_ID6a, Date, Time, No_Samples, Turnaround, ROOM13, Floor, Area, Location, Material)

  8. Relational Schema 12. SAMPLE_RESULT(SRID, SID5, TurnAround (ASAP/24 Hours), Provided_by_Lab_ID4, Description, Asb_Type, Asb_TypeA, Asb_Type2, Asb_TypeB, Other_Fibers, NonFibrous, Analysis Date) 13. ROOM(NUMBER, BNAME7, Size, Floor, Max_Capacity) 14. NOTE(NID, MECHID6c, PMWID8, Written_by_MECHID6c, Date, Equip_Inv_ID18, Equip_ID18, Action_Taken) 15. SAMPLE_ANALYSIS_TYPE(SID5, Analysis_Type) 16. CHILD_TAG_WO(CT_ID, Submitted_by_CID1, Sent_to6a, Equip_Inv_ID18,Equip_ID18, Date, Description) 17. EQUIPMENT(EQ_ID, Type, Manufacturer, Equipment_Function, Model) 18. EQUIPMENT_INVENTORY(EISN, EQ_ID17, Installed, Room13,Building13, Warehouse, Supplied_by10, Condition, Service_Expiration_Date, Warranty_Expiration_Date) 19. MATERIAL(MAT_ID, Type, Manufacturer, Model, Fixed_Cost, Cost) 20. MATERIAL_INVENTORY(MISN, MAT_ID19, Supplied_by10) a. PERMANENT(PM_ID20, Used_by_Mechanic6c) b. TEMPORARY(TM_ID20, Rented_by_Mechanic6c, Last_Date_In, Last_Date_Out) 21. TIME_SLOT(TSID, Start_time, End_time, Day, Month, Year, Worked_by_Mechanic6c, Referring_to_Client_WO2, Referring_to_PM_WO8) 22. MATERIAL_EQUIPMENT(MAT_ID19, EQ_ID17) 23. ORDER_MATERIAL(OID9, MAT_ID19, Quantity, Price, Month, Year) 24. ORDER_EQUIPMENT(OID9, EQ­_ID17, Quantity, Price, Month, Year)

  9. Database: Relations

  10. Queries

  11. Query #1 • Finding the fastest mechanic for a specific job • For urgent fixes, the most efficient mechanic can be allocated to the job. • Query finds the mechanic with the lowest average time spent on fixing specified equipment. • IEOR Method: The productivity metric to rank the mechanics based on their efficiency.

  12. Query #1 SELECT s.Worked_By as EID, e.first_name as First_Name, e.last_name as Last_Name, avg(s.total_time) as Average_Time FROM Efficiency_STEP1 s, employee e where e.eid=s.Worked_By group by s.Worked_By, e.first_name, e.last_name order by avg(s.total_time); Select t.Referring_To_PMWO, sum(DateDiff("n",t.Start_Time, t.End_Time)) as Total_Time, t.Worked_By From PM_WORK_ORDER p, Time_Slot t, equipment eq Where eq.type=INPUT AND EQ.EQ_ID=P.EQUIP_ID AND t.Referring_To_PMWO=p.PMWID Group by t.Referring_To_PMWO, t.Worked_By;

  13. Query #2 • Mean Time Between Failure • Mean time between failure for each equipment • Help managers determine which equipment fails the most and which equipment fails the least • Allows the user to determine the reliability of each type of inventory

  14. Query #2 SELECTChild_Tag.Equip_ID, Avg(Child_Tag.TTF) AS TTF_of_Avg FROM ( SELECTEquip_ID, Equip_Inv_ID, DATEDIFF("d",( SELECT TOP 1 tmp.Date_Submitted FROM CHILD_TAG_WO AS tmp WHEREtmp.Equip_Inv_ID = wo.Equip_Inv_ID AND tmp.Date_Submitted < wo.Date_Submitted ORDER BYtmp.Date_Submitted DESC),wo.Date_Submitted) AS TTF FROM CHILD_TAG_WO AS wo ORDER BYwo.Date_Submitted DESC ) AS Child_Tag GROUP BYChild_Tag.Equip_ID;

  15. Query #3 • Economic Order Quantity • helps managers keep track of materials inventory and place orders as necessaryto minimize lead time SELECTQuantity_by_Month.Mat_ID, Quantity_by_Month.Model, round(Sqr(Avg(Quantity_by_Month.QuantityOfSum)*2*Quantity_by_Month.Fixed_Cost/(0.12*Quantity_by_Month.Cost)),0) AS EOQ FROM ( SELECTM.Mat_ID, M.Model, O.Month, Sum(O.Quantity) AS QuantityOfSum, M.Cost, M.Fixed_CostFROMOrder_Material AS O, Material AS M WHERE ((O.Year=Year(Now())-1) And ((O.Mat_ID)=M.MAT_ID)) GROUP BYM.Mat_ID, M.Model, O.Month, M.Cost, M.Fixed_Cost) AS Quantity_by_Month GROUP BYQuantity_by_Month.Mat_ID, Quantity_by_Month.Model, Quantity_by_Month.Fixed_Cost, Quantity_by_Month.Cost;

  16. Query #4 • Forecasting arrival of work order calls • allows management to make an informed decision of how many calls to expect and how to staff accordingly to satisfy demand. • IEOR Method: Queuing theory CREATE FUNCTION Factorial (p_MyNum INTEGER) RETURN NUMBER AS BEGIN IF p_MyNum = 1 THEN RETURN 1; ELSE RETURN (p_MyNum * Factorial (p_MyNum-1)); END IF; END; SELECT exp(-(count(*)/60)*[ ENTER PROJECTED TIME])*((count(*)/60)*[ENTER PROJECTED TIME])^[ ENTER NUMBER OF CALLS] / (Factorial ([ENTER NUMBER OF CALLS)) FROM CLIENT_WORK_ORDER WHERE CLIENT_WORK_ORDER.date >= [Now()] – 1800;

  17. Query #4: Future Improvements • Issue: output of this query is limited to one number, which is “the probability that k client work orders will arrive in time t.” • Due to 2 user inputs, “Projected Time” (t) and “Number of Calls” (k). • May be improved, for example, by taking only time period as input, automatically generating values for the number of work order arrival, and calculating probabilities accordingly.

  18. Query #5 • Emergency Response • Prioritize rooms and buildings that should be responded to first when an action is needed • Calculate “vulnerability” scores by using a weighted sum of: • # of equipment in room • maximum capacity of room • avg. number of people in building/room

  19. Query #5 • Rooms with Equipment SELECTRoom.Bname, Room.RID, Room.[Max Capacity], Count(Room.RID) AS Equipment_Count FROM Room INNER JOIN Equip_Inventory ON (Room.Bname=Equip_Inventory.Building) AND (Room.RID=Equip_Inventory.Room) GROUP BYRoom.Bname, Room.RID, Room.[Max Capacity]; • Building Capacity SELECTBuilding.Building_Name, Sum(Room.[Max Capacity]) AS [SumOfMax Capacity] FROM Building INNER JOIN Room ON Building.Building_Name=Room.Bname GROUP BYBuilding.Building_Name;

  20. Query #5 • Rooms with Samples SELECTRoom.Bname, Room.RID FROM (Room INNER JOIN Sample ON (Room.Bname=Sample.[For Building]) AND (Room.RID=Sample.From_Room)) INNER JOIN [Sample Result] ON Sample.SID=[Sample Result].Sample_ID WHERE (((Room.Bname)=[Sample].[For Building]) AND ((Room.RID)=[Sample].[From_Room]) AND ((Sample.SID) In (SELECT [Sample Result].Sample_ID FROM [Sample Result] WHERE ((([Sample Result].Asb_type)<>'NULL')))));

  21. Query #5 • Vulnerability SELECTRooms_With_Samples.Bname, Rooms_With_Samples.RID, Rooms_with_equipment.Equipment_Count, Rooms_with_equipment.[Max Capacity] AS Room_MAX_Capacity, Building_Capacity.[SumOfMax Capacity] AS Building_MAX_Capacity FROMBuilding_Capacity INNER JOIN (Rooms_With_Samples INNER JOIN Rooms_with_equipment ON (Rooms_With_Samples.RID=Rooms_with_equipment.RID) AND (Rooms_With_Samples.Bname=Rooms_with_equipment.Bname)) ON Building_Capacity.Building_Name=Rooms_with_equipment.Bname;

  22. Query #5 • Final Query: Emergency Response SELECT (([V].[Equipment_Count]*5)+([V].[Room_MAX_Capacity]*10)+([V].[Building_MAX_Capacity]*0.1*0.5)) AS Score, V.Bname, V.RID FROM vulnerability AS V ORDER BY (([V].[Equipment_Count]*5)+([V].[Room_MAX_Capacity]*10)+([V].[Building_MAX_Capacity]*0.1*0.5)) DESC;

  23. Forms

  24. Form #1: Switchboard • The switchboard provides links to 3 different forms (Child Tag Work Order, Client Work Order, Audit Record)

  25. Form #2: Audit Report • Legal document, which is signed by the manager and the lab representative who are in charge of the specific case

  26. Form #3: Child Tag Work Order • Needed for an order to be placed to restock that particular piece of equipment.

  27. Form #4: Client Work Order • When a client submits a request online, manager receives this request and fills out this form to keep record of all requests submitted by clients

  28. Reports

  29. Report #1: EOQ • Economic Order Quantity for each material (result from Query 3) + Graph to visualize

  30. Report #2: Emergency Response • “Vulnerability” score for each room, grouped by building

  31. Normalization Analysis

  32. Normalization Analysis: 1NF • Example of relations normalized to 1NF: MATERIAL_EQUIPMENT(MAT_ID19, EQ_ID17) EQUIPMENT(EQ_ID, Type, Manufacturer, Equipment_Function, Model, MAT_ID) MATERIAL(MAT_ID, Type, Manufacturer, Model, Fixed_Cost, Cost, EQ_ID) • Each type of material belongs to multiple types of equipment, and each type of equipment takes multiple types of material • A new relation called MATERIAL_EQUIPMENT is created to account for this many-to-many relationship and to have all attribute domains include only atomic, single-valued variables.

  33. Normalization Analysis: 2NF A relation NOT in 2NF: • If NOTE were a weak entity to PM_Work_Order,: • NOTE(NID, PMWID8, MECHID6c, Equip_Inv_ID18, Equip_ID18, Date, Action_Taken) • PMWID determines MECHID, Equip_Inv_ID and Equip_ID A relation in 2NF: • NOTE(NID, PMWID8,MECHID6c, Equip_Inv_ID18, Equip_ID18, Date, Action_Taken) • PM_WORK_ORDER(PMWID, Sent_to_Manager6a, Worked_by_MECHID6c, Equip_Inv18, Equip_ID18, Issue_Date, Generation_Time, Status, Task, Asset, Equipment_Note, Remark, Condition) • Mechanic who wrote the note can be found by natural-joining NOTE with PM_WORK_ORDER and MECHANIC.

  34. Normalization Analysis: 3NF • Example of relation in 2NF, but not 3NF: AUDIT_RECORD(ARID, CWID2, Audited_By3, Sampled_by4, Received_by_CID1, Requested_by_Manager_ID6a, Date, Time, No_Samples, Turnaround, ROOM13, Floor, Area, Location, Material) • Example in 3NF: AUDIT_RECORD(ARID, CWID2, Audited_By3, Sampled_by4, Received_by_CID1, Requested_by_Manager_ID6a, Date, Time, No_Samples, Turnaround, ROOM13, Floor, Area, Location, Material) ROOM(NUMBER, BNAME7, Size, Floor, Area, Location, Max_Capacity)

  35. Normalization Analysis: 3NF • CLIENT_WORK_ORDER NOT in3NF: • CLIENT_WORK_ORDER(CWID, Submitted_by_CID1, Client_Fname, Client_Lname, Client_Address, Client_Phone, Client_Email,Sent_to_MID6a, Manager_Fname, Manager_MI, Manager_Lname, Manager_Email, For­_Room13 , For­_Building13, Date_Submitted) • CLIENT_WORK_ORDER IN 3NF • CLIENT(CID, FNAME, MI, LNAME, ADDRESS, PHONE, E-MAIL) • CLIENT_WORK_ORDER(CWID, Submitted_by_CID1, Sent_to_MID6a, For­_Room13 , For­_Building13, Date_Submitted) • EMPLOYEE(EID, Supervised_by_EID6, Fname, MI, Lname, E-mail, Phone_Number, D.O.B.) • MANAGER(MID6, Salary) • R is in BCNF if whenever a nontrivial functional dependency XA holds in R, then X is a superkey of R.

  36. Normalization: BCNF • A relation in 2NF but not in 3NF: • TIME_SLOT(TSID, Start_time, End_time, Day, Month, Year, Worked_by_Mechanic6c, Referring_to_Client_WO2, Referring_to_PM_WO8) • Issue: PM_Work_Order includes which mechanic works on each specific PM work order. Hence, the above relation can be normalized into 3NF as the following: • A relation in 3NF and BCNF: • TIME_SLOT(TSID, Start_time, End_time, Day, Month, Year, Worked_by_Mechanic6c, Referring_to_Client_WO2, Referring_to_PM_WO8) • PM_WORK_ORDER(PMWID, Sent_to_Manager6a, Worked_by_MECHID6c, Equip_Inv18, Equip_ID18, Issue_Date, Generation_Time, Status, Task, Asset, Equipment_Note, Remark, Condition) • These relations are in BCNF since PKs for all the relations above are super keys.

  37. Questions?

  38. Thank you115group5@googlegroups.com

More Related