- Slides: 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
Overview � Client Background � Client Needs � EER Diagram � Relationship Schema � Access Database � Normalization Analysis � Queries � Q/A
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
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 ○ ○
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!
Relational Schema 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. CLIENT(CID, FNAME, MI, LNAME, ADDRESS, PHONE, E MAIL) CLIENT_WORK_ORDER(CWID, Submitted_by_CID 1, Sent_to_MID 6 a, For _Room 13 , For _Building 13, 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_FID 6 b, Required_by_Audit_Record_ID 11, Room 13, Building 13, Area_of_Room, Direction, Description, Image) EMPLOYEE(EID, Supervised_by_EID 6, Fname, MI, Lname, E mail, Phone_Number, D. O. B. ) a. MANAGER(MID 6, Salary) b. FIELDWORKER(FID 6, Wage) c. MECHANIC (MECHID 6, Wage) BUILDING(BNAME, Address, Number_of_Floors, Number_of_Rooms, Floor_Plan, Safety) PM_WORK_ORDER(PMWID, Sent_to_Manager 6 a, Worked_by_MECHID 6 c, Equip_Inv 18, Equip_ID 18, Issue_Date, Generation_Time, Status, Task, Asset, Equipment_Note, Remark, Condition) ORDER(OID, Placed_by_MID 6 a, Sent_to_Sup_ID 10) SUPPLIER(SUP_ID, Company_Name, Contact_Person, E mail, Address, Phone_Number AUDIT_RECORD(ARID, CWID 2, Audited_By 3, Sampled_by 4, Received_by_CID 1, Requested_by_Manager_ID 6 a, Date, Time, No_Samples, Turnaround, ROOM 13, Floor, Area, Location, Material)
Relational Schema 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. SAMPLE_RESULT(SRID, SID 5, Turn. Around (ASAP/24 Hours), Provided_by_Lab_ID 4, Description, Asb_Type. A, Asb_Type 2, Asb_Type. B, Other_Fibers, Non. Fibrous, Analysis Date) ROOM(NUMBER, BNAME 7, Size, Floor, Max_Capacity) NOTE(NID, MECHID 6 c, PMWID 8, Written_by_MECHID 6 c, Date, Equip_Inv_ID 18, Equip_ID 18, Action_Taken) SAMPLE_ANALYSIS_TYPE(SID 5, Analysis_Type) CHILD_TAG_WO(CT_ID, Submitted_by_CID 1, Sent_to 6 a, Equip_Inv_ID 18, Equip_ID 18, Date, Description) EQUIPMENT(EQ_ID, Type, Manufacturer, Equipment_Function, Model) EQUIPMENT_INVENTORY(EISN, EQ_ID 17, Installed, Room 13, Building 13, Warehouse, Supplied_by 10, Condition, Service_Expiration_Date, Warranty_Expiration_Date) MATERIAL(MAT_ID, Type, Manufacturer, Model, Fixed_Cost, Cost) MATERIAL_INVENTORY(MISN, MAT_ID 19, Supplied_by 10) a. PERMANENT(PM_ID 20, Used_by_Mechanic 6 c) b. TEMPORARY(TM_ID 20, Rented_by_Mechanic 6 c, Last_Date_In, Last_Date_Out) TIME_SLOT(TSID, Start_time, End_time, Day, Month, Year, Worked_by_Mechanic 6 c, Referring_to_Client_WO 2, Referring_to_PM_WO 8) MATERIAL_EQUIPMENT(MAT_ID 19, EQ_ID 17) ORDER_MATERIAL(OID 9, MAT_ID 19, Quantity, Price, Month, Year) ORDER_EQUIPMENT(OID 9, EQ _ID 17, Quantity, Price, Month, Year)
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.
Query #1 Select t. Referring_To_PMWO, sum(Date. Diff("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; 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_STEP 1 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);
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
Query #2 SELECT Child_Tag. Equip_ID, Avg(Child_Tag. TTF) AS TTF_of_Avg FROM ( SELECT Equip_ID, Equip_Inv_ID, DATEDIFF("d", ( SELECT TOP 1 tmp. Date_Submitted FROM CHILD_TAG_WO AS tmp WHERE tmp. Equip_Inv_ID = wo. Equip_Inv_ID AND tmp. Date_Submitted < wo. Date_Submitted ORDER BY tmp. Date_Submitted DESC), wo. Date_Submitted) AS TTF FROM CHILD_TAG_WO AS wo ORDER BY wo. Date_Submitted DESC ) AS Child_Tag GROUP BY Child_Tag. Equip_ID;
Query #3 � Economic Order Quantity �helps managers keep track of materials inventory and place orders as necessary to minimize lead time SELECT Quantity_by_Month. Mat_ID, Quantity_by_Month. Model, round(Sqr(Avg(Quantity_by_Month. Quantity. Of. Sum)*2*Q uantity_by_Month. Fixed_Cost/(0. 12*Quantity_by_Month. Cost)), 0) AS EOQ FROM ( SELECT M. Mat_ID, M. Model, O. Month, Sum(O. Quantity) AS Quantity. Of. Sum, M. Cost, M. Fixed_Cost FROM Order_Material AS O, Material AS M WHERE ((O. Year=Year(Now()) 1) And ((O. Mat_ID)=M. MAT_ID)) GROUP BY M. Mat_ID, M. Model, O. Month, M. Cost, M. Fixed_Cost) AS Quantity_by_Month GROUP BY Quantity_by_Month. Mat_ID, Quantity_by_Month. Model, Quantity_by_Month. Fixed_Cost, Quantity_by_Month. Cost;
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_My. Num INTEGER) RETURN NUMBER AS BEGIN IF p_My. Num = 1 THEN RETURN 1; ELSE RETURN (p_My. Num * Factorial (p_My. Num 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;
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. � Work Order Arrival Probabilities (Poisson) 0. 4 0. 35 0. 3 0. 25 0. 2 0. 15 0. 1 0. 05 0 1 2 3 4 5 6 7 8 9
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
Query #5 Building Capacity SELECT Building_Name, Sum(Room. [Max Capacity]) AS [Sum. Of. Max Capacity] FROM Building INNER JOIN Room ON Building_Name=Room. B name GROUP BY Building_Name; � • Rooms with Equipment SELECT Room. Bname, Room. RID, Room. [Max Capacity], Count(Room. RID) AS Equipment_Count FROM Room INNER JOIN Equip_Inventory ON (Room. Bname=Equip_Inventory. Buildi ng) AND (Room. RID=Equip_Inventory. Room) GROUP BY Room. Bname, Room. RID, Room. [Max Capacity];
Query #5 Rooms with Samples SELECT Room. 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'))))); �
Query #5 � Vulnerability SELECT Rooms_With_Samples. Bname, Rooms_With_Samples. RID, Rooms_with_equipment. Equipment_ Count, Rooms_with_equipment. [Max Capacity] AS Room_MAX_Capacity, Building_Capacity. [Sum. Of. Max Capacity] AS Building_MAX_Capacity FROM Building_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=Roo ms_with_equipment. Bname)) ON Building_Capacity. Building_Name=R ooms_with_equipment. Bname;
Query #5 • Final Query: Emergency Response SELECT (([V]. [Equipment_Count]*5)+([V]. [R oom_MAX_Capacity]*10)+([V]. [Buil ding_MAX_Capacity]*0. 1*0. 5)) AS Score, V. Bname, V. RID FROM vulnerability AS V ORDER BY (([V]. [Equipment_Count]*5)+([V]. [R oom_MAX_Capacity]*10)+([V]. [Buil ding_MAX_Capacity]*0. 1*0. 5)) DESC;
Form #1: Switchboard � The switchboard provides links to 3 different forms (Child Tag Work Order, Client Work Order, Audit Record)
Form #2: Audit Report � Legal document, which is signed by the manager and the lab representative who are in charge of the specific case
Form #3: Child Tag Work Order � Needed for an order to be placed to restock that particular piece of equipment.
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
Report #1: EOQ � Economic Order Quantity for each material (result from Query 3) + Graph to visualize
Report #2: Emergency Response � “Vulnerability” score for each room, grouped by building
Normalization Analysis: 1 NF � Example of relations normalized to 1 NF: MATERIAL_EQUIPMENT(MAT_ID 19, EQ_ID 17) EQUIPMENT(EQ_ID, Type, Manufacturer, Equipment_Function, Model, MAT_ID) MATERIAL(MAT_ID, Type, Manufacturer, Model, Fixed_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. �
Normalization Analysis: 2 NF A relation NOT in 2 NF: � If NOTE were a weak entity to PM_Work_Order, : � NOTE(NID, PMWID 8, MECHID 6 c, Equip_Inv_ID 18, Equip_ID 18, Date, Action_Taken) � PMWID determines MECHID, Equip_Inv_ID and Equip_ID A relation in 2 NF: � NOTE(NID, PMWID 8, MECHID 6 c, Equip_Inv_ID 18, Equip_ID 18, Date, Action_Taken) � PM_WORK_ORDER(PMWID, Sent_to_Manager 6 a, Worked_by_MECHID 6 c, Equip_Inv 18, Equip_ID 18, 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.
Normalization Analysis: 3 NF Example of relation in 2 NF, but not 3 NF: AUDIT_RECORD(ARID, CWID 2, Audited_By 3, Sampled_by 4, Received_by_CID 1, Requested_by_Manager_ID 6 a, Date, Time, No_Samples, Turnaround, ROOM 13, Floor, Area, Location, Material) � Example in 3 NF: AUDIT_RECORD(ARID, CWID 2, Audited_By 3, Sampled_by 4, Received_by_CID 1, Requested_by_Manager_ID 6 a, Date, Time, No_Samples, Turnaround, ROOM 13, Floor, Area, Location, Material) ROOM(NUMBER, BNAME 7, Size, Floor, Area, Location, Max_Capacity) �
Normalization Analysis: 3 NF � CLIENT_WORK_ORDER NOT in 3 NF: � CLIENT_WORK_ORDER(CWID, Submitted_by_CID 1, Client_Fname, Client_Lname, Client_Address, Client_Phone, Client_Email, Sent_to_MID 6 a, Manager_Fname, Manager_MI, Manager_Lname, Manager_Email, For _Room 13 , For _Building 13, Date_Submitted) � CLIENT_WORK_ORDER IN 3 NF � CLIENT(CID, FNAME, MI, LNAME, ADDRESS, PHONE, E-MAIL) � CLIENT_WORK_ORDER(CWID, Submitted_by_CID 1, Sent_to_MID 6 a, For _Room 13 , For _Building 13, Date_Submitted) � EMPLOYEE(EID, Supervised_by_EID 6, Fname, MI, Lname, Email, Phone_Number, D. O. B. ) ○ MANAGER(MID 6, Salary) � R is in BCNF if whenever a nontrivial functional dependency X A holds in R, then X is a superkey of R.
Normalization: BCNF � A relation in 2 NF but not in 3 NF: � TIME_SLOT(TSID, Start_time, End_time, Day, Month, Year, Worked_by_Mechanic 6 c, Referring_to_Client_WO 2, Referring_to_PM_WO 8) � Issue: PM_Work_Order includes which mechanic works on each specific PM work order. Hence, the above relation can be normalized into 3 NF as the following: � A relation in 3 NF and BCNF: � TIME_SLOT(TSID, Start_time, End_time, Day, Month, Year, Worked_by_Mechanic 6 c, Referring_to_Client_WO 2, Referring_to_PM_WO 8) � PM_WORK_ORDER(PMWID, Sent_to_Manager 6 a, Worked_by_MECHID 6 c, Equip_Inv 18, Equip_ID 18, 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.
Thank you 115 group [email protected] com