Physical Plant Campus Services Department Final Project Presentation 12/07/07

1. Physical PlantCampus Services DepartmentFinal Project Presentation12/07/07 Team 3 Audrey F. | Charles K. | Fred Z. | Kenneth L. | Sam L. | Sara S. | Stanley K.

2. Overview • Company Background • Project Purpose • EER Diagram • Relational Schema • Relation Normalization • Database Queries

3. 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

4. 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

5. EER Diagram

6. Relational Schema • Person (SSN, First_Name, Last_Name, Office_Address, Email, Phone, Fax, Position) i. Contractor (SSN4, SuperIntendentName, ProcessInvolvement, CapitalProjects) • Electrical (SSN4, TechnicianLevel, TechnicianType, TestsExpertise) • Elevator (SSN4, AreaSupervised, CertificationLevel) • Utility (SSN4, UtilityType) • Plumbing_Machinary (SSN4, PlumberType, EmergencyCapable) • Lock Shop (SSN4, ServiceType, SpecialAssistance) • Structural (SSN4­­, ServiceGroups, Licensure) • Ventilation (SSN4, ModelExpertise, ModelExperienced, DateofLastMaintainence, MechanicType, Scale) • Stationary (SSN4, CertificationLevel)

7. Normalization Analysis 2NF FD1 FD2 Partial Dependency violates 2NF Normalization ProcessForm1 FD3 ProcessForm2 FD4

8. Normalization Analysis 3NF FD1 FD2 Transitive Dependency violates 3NF Normalization Location1 FD3 Location2 FD4

9. MS Access Queries • Shutdown Statistics • Craftspeople Contacted • Building Age Correlation • Process Time of Forms

10. 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.

11. 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_Recorded HAVING ((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;

12. Monthly Shutdown Statistics 1 2 Type the month Type the year

13. 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.

14. Craftspeople Contacted SQL> 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];

15. Craftspeople Contacted 1 Type shutdown name

16. 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.

17. 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 Correlation FROM Shutdown S, Location L WHERE EXISTS (SELECT COUNT(*) AS Shutdown_per_LID FROM Shutdown S, Location L WHERE S.LID = L.LID GROUP BY S.LID);

18. Building Age Correlation 1 Type full or partial location name OR

19. 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.

20. Process time of forms SQL> 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;

21. Process Time of Forms 1 2 Type the month Type the year

22. Questions? Special thanks to Keith Muller and Shaylah Rigmaiden!