Transcript 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 workorders
Ease in performing analysis of asbestos
data
Reduce costs
Prevent equipment from breaking down
SAVE LIVES!
EER Diagram
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_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)
Relational Schema
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
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)
ROOM(NUMBER, BNAME7, Size, Floor, Max_Capacity)
NOTE(NID, MECHID6c, PMWID8, Written_by_MECHID6c, Date, Equip_Inv_ID18, Equip_ID18,
Action_Taken)
SAMPLE_ANALYSIS_TYPE(SID5, Analysis_Type)
CHILD_TAG_WO(CT_ID, Submitted_by_CID1, Sent_to6a, Equip_Inv_ID18,Equip_ID18, Date,
Description)
EQUIPMENT(EQ_ID, Type, Manufacturer, Equipment_Function, Model)
EQUIPMENT_INVENTORY(EISN, EQ_ID17, Installed, Room13,Building13, Warehouse,
Supplied_by10, Condition, Service_Expiration_Date, Warranty_Expiration_Date)
MATERIAL(MAT_ID, Type, Manufacturer, Model, Fixed_Cost, Cost)
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)
TIME_SLOT(TSID, Start_time, End_time, Day, Month, Year, Worked_by_Mechanic6c,
Referring_to_Client_WO2, Referring_to_PM_WO8)
MATERIAL_EQUIPMENT(MAT_ID19, EQ_ID17)
ORDER_MATERIAL(OID9, MAT_ID19, Quantity, Price, Month, Year)
ORDER_EQUIPMENT(OID9, EQ_ID17, Quantity, Price, Month, Year)
Database: Relations
Queries
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(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;
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);
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.QuantityOfSum)*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
QuantityOfSum, 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_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;
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.Building_Name,
Sum(Room.[Max Capacity]) AS
[SumOfMax Capacity]
FROM Building INNER JOIN Room
ON
Building.Building_Name=Room.B
name
GROUP BY Building.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.[SumOfMax
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=Ro
oms_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;
Forms
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
Reports
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
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.
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.
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)
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, Email, 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.
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.
Questions?
Thank you
[email protected]