physical plant campus services department final project presentation 12/07/07
DESCRIPTION
Physical Plant Campus Services Department Final Project Presentation 12/07/07. Team 3 Audrey F. | Charles K. | Fred Z. | Kenneth L. | Sam L. | Sara S. | Stanley K. Overview. Company Background Project Purpose EER Diagram Relational Schema Relation Normalization Database Queries. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/1.jpg)
Physical PlantCampus Services Department
Final Project Presentation12/07/07
Team 3Audrey F. | Charles K. | Fred Z. | Kenneth L. | Sam L. | Sara S. | Stanley K.
![Page 2: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/2.jpg)
Overview
• Company Background• Project Purpose• EER Diagram• Relational Schema• Relation Normalization• Database Queries
![Page 3: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/3.jpg)
Company Background• The University of California Physical Plant Campus Services Department
(PPCS) maintains the University of California Berkeley’s campus facilities, utilities, and resources
• PPCS is responsible for taking maintenance, repair, and emergency service calls from UC Building Coordinators and Capital Projects Managers
• A Customer Service Representative at PPCS receives maintenance requests and coordinates workers to inspect and resolve reported issues
• Before any maintenance work can be done, PPCS workers must disable any affected utilities such as water, steam, or electricity at the location through a process known as a shutdown
![Page 4: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/4.jpg)
Project Purpose
• Create a database that will accurately represent the PPCS shutdown process
• Coordinate information used by all parties involved in the shutdown process
• Explore data relationships to identify areas of inefficiency in the shutdown process
![Page 5: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/5.jpg)
EER Diagram
![Page 6: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/6.jpg)
Relational Schema1. Person (SSN, First_Name, Last_Name, Office_Address,
Email, Phone, Fax, Position)i. Contractor (SSN4, SuperIntendentName, ProcessInvolvement,
CapitalProjects)a) Electrical (SSN4, TechnicianLevel, TechnicianType, TestsExpertise)b) Elevator (SSN4, AreaSupervised, CertificationLevel)c) Utility (SSN4, UtilityType)d) Plumbing_Machinary (SSN4, PlumberType, EmergencyCapable)e) Lock Shop (SSN4, ServiceType, SpecialAssistance)f) Structural (SSN4 , ServiceGroups, Licensure)g) Ventilation (SSN4, ModelExpertise, ModelExperienced,
DateofLastMaintainence, MechanicType, Scale)h) Stationary (SSN4, CertificationLevel)
![Page 7: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/7.jpg)
WO IsEmergencySD
CSR Date_Recorded Comments
FD1
FD2
Partial Dependency violates 2NF
Normalization
ProcessForm1
ProcessForm2
FD3
FD4
WO IsEmergency SD CSR Date_Recorded
IsEmergencySD Comments
Normalization Analysis 2NF
![Page 8: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/8.jpg)
FD1FD2
Transitive Dependency violates 3NF
Normalization
Location1
Location2
FD3
FD4
LID Contact_No RoomNo Name AddressLocation_Type
Zip-code Type3
LID Contact_No Room No Name Address Zip Code
Location_Type Type3
Normalization Analysis 3NF
![Page 9: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/9.jpg)
MS Access Queries
1) Shutdown Statistics2) Craftspeople Contacted3) Building Age Correlation4) Process Time of Forms
![Page 10: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/10.jpg)
Monthly Shutdown Statistics
Query:Find the frequency and proportion of each type of shutdown occurring over a given month.
Purpose:PPCS can better allocate its workers and resources to efficiently complete the most frequently requested shutdown types.
![Page 11: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/11.jpg)
Monthly Shutdown Statistics SQL>SELECT [Shutdown Request Subclass].Type, [Shutdown Request
Subclass].Date_Recorded, Count([Shutdown Request Subclass].WO_No) AS CountOfWO_No
FROM [Shutdown Request Subclass]GROUP BY [Shutdown Request Subclass].Type, [Shutdown
Request Subclass].Date_RecordedHAVING ((Shutdown Request Subclass].Date_Recorded)=[Month]);
SELECT [Query 1A].Type, [Query 1A].Date_Recorded AS [Month], [Query 1A].CountOfWO_No AS Frequency,
[CountOfWO_No]/[SumOfCountOfWO_No] AS Proportion
FROM [Query 1A], [Query 1B]GROUP BY [Query 1A].Type, [Query 1A].Date_Recorded, [Query
1A].CountOfWO_No, [CountOfWO_No]/[SumOfCountOfWO_No], [Query 1B].SumOfCountOfWO_No;
![Page 12: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/12.jpg)
Monthly Shutdown Statistics 1 Type the year 2 Type the month
![Page 13: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/13.jpg)
Craftspeople Contacted
Query:List the names of all craftspeople contacted previously for a specified shutdown.
Purpose:PPCS can identify the most experienced workers for each type of shutdown.
![Page 14: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/14.jpg)
Craftspeople ContactedSQL>SELECT [Verification Subclass].[Lead Craft EID], [Verification
Subclass].[Assist Craft], [Verification Subclass].[PhoneNumber], Count([Verification Subclass].[Assist Craft]) AS [CountOfAssist Craft]
FROM [Verification Subclass]GROUP BY [Verification Subclass].[Assist Craft];
![Page 15: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/15.jpg)
Craftspeople Contacted1 Type shutdown name
![Page 16: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/16.jpg)
Building Age Correlation
Query:Calculate the correlation coefficient between the age of a specified building and the number of shutdowns.
Purpose:PPCS can identify buildings that are particularly problematic due to building age and may require extra maintenance.
![Page 17: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/17.jpg)
Building Age Correlation
SQL>SELECT DISTINCT S.LID, L.Age, Shutdown_per_LID,
(COUNT(DISTINCT S.LID)*SUM(Shutdown_per_LID * L.Age)-SUM(Shutdown_per_LID)*SUM(L.Age))/(SQRT(COUNT(DISTINCT
S.LID)*SUM(SQUARE(Shutdown_per_LID))-SQUARE(SUM(Shutdown_per_LID)))*
SQRT(COUNT(DISTINCT S.LID)*SUM(SQUARE(L.Age))-SQUARE(SUM(L.Age))))
AS DISTINCT CorrelationFROM Shutdown S, Location LWHERE EXISTS
(SELECT COUNT(*) AS Shutdown_per_LIDFROM Shutdown S, Location LWHERE S.LID = L.LIDGROUP BY S.LID);
![Page 18: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/18.jpg)
Building Age Correlation
1 Type full or partial location name
OR
![Page 19: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/19.jpg)
Process Time of Forms
Query:Calculate the mean and variance for the time elapsed between a shutdown request and a shutdown completion over a specified month.
Purpose:PPCS can identify bottlenecks in the shutdown process.
![Page 20: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/20.jpg)
Process time of formsSQL>SELECT ProcessForm.WO_No, ProcessForm.CSR,
ProcessForm.Date_Recorded, ProcessForm.Comments, ProcessForm.Is_Emergency_SD, ShutdownRequest.Request_Date, Completion.Completion_Date, [Completion_Date]-[Request_Date] AS Date_Difference, ShutdownRequest.Type
FROM (ProcessForm INNER JOIN Completion ON ProcessForm.WO_No = Completion.WO) INNER JOIN
ShutdownRequest ON ProcessForm.WO_No = ShutdownRequest.WO;
SELECT Avg([Query 4A].Date_Difference) AS Mean, Var([Query 4A].Date_Difference) AS Variance, [Query 4A].Type
FROM [Query 4A]GROUP BY [Query 4A].Type;
![Page 21: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/21.jpg)
Process Time of Forms1 Type the year 2 Type the month
![Page 22: Physical Plant Campus Services Department Final Project Presentation 12/07/07](https://reader036.vdocuments.us/reader036/viewer/2022070502/568144d8550346895db1a4e3/html5/thumbnails/22.jpg)
Questions?
Special thanks to Keith Muller and Shaylah Rigmaiden!