Workstation & Microcomputer Facilities Team 5 Asmita Karandikar,

Workstation & Microcomputer Facilities Team 5 Asmita Karandikar,

Workstation & Microcomputer Facilities Team 5 Asmita Karandikar, Zach Levine, Michelle Lew, Brian Loo, Mike Lovejoy, Yue Tu Friday, December 7th, 2007 Overview 1. 2. 3.

4. 5. 6. 7. Organization summary Simplified EER diagram Relational Design (Schema) Normalization Queries in SQL and Access Query snapshot Conclusion

2 Organization Summary UC Berkeley Microcomputer Facilities 12 locations, 7 open to public Approximately 300 computers total Approximately 100 full- and parttime staff Dwinelle MF Evans MF

Moffitt MF 3 UC Berkeley Microcomputer Facilities Database Relation Design (Schema) Microcomputer Facilities Database 5

Relation Design (Continued) 6 Relation Design (Continued) 19. Teaches (FID2b, C_S_Y_ID9) 20. Uses (UID2, Accessory_ID4, Location_ID4) 21. Enrolls_In (SID2a, C_S_Y_ID9) 22. Placed_At (Location_ID5,C_S_Y_ID9)

23. Unit(Unit_Number) 24. Maintains_Comp (WMF_Staff_ ID2d, Computer_ID8, Location_ID8) 25. Reserves (Timeslot_ID6, C_S_Y_ID9) 26. Includes (Timeslot_ID6, Shift_ID7, Location_ID7) 27. Logs_Into (Computer_ID5, Location_ID5, UID2, Date, Time) 28. Logs_Out_Of (Computer_ID5, Location_ID5, UID2, Date, Time) 29. Works_T(WMF_Staff_ ID 2d, Timeslot_ID6, Location_ID5) 7 Access Relationship View

8 Access Interface Example 1 (Edit/Add Computer) 9 Access Interface Example II (Request and Assign Repair) 10

Functional Dependencies Normalization #1: The following relation, used to provide information about each class, is in 2NF: Course (C_ S_ Y_ ID, Course_ ID, Semester, Year, Department, Course_ Number, Course_ Name, Location_ ID5, WMF_ Staff_ ID_ Super 2di2, Temp_ Course_ ID) This is in 2NF because all attributes can be derived from the primary key, C_ S_ Y_ ID, but some of them can also be derived from other attributes that are not the primary key. Therefore, to normalize this relationship as 3NF, the relationship could be changed into the following relationships: R1(C_ S_ Y_ ID, Course_ ID, Semester, Year)

R2 (C_ S_ Y_ ID, Location_ ID5, WMF_ Staff_ ID_ Super 2di2, Temp_ Course_ ID) R3(Course_ ID, Department, Course_ Number, Course_ Name) 11 Functional Dependencies (contd) However, R1 is not in BCNF form because the Course_ ID, Semester, and Year could be used to determine the C_ S_ Y_ ID. So in order for this to be in BCNF form, R1should be changed into the following three relations: R11(C_ S_ Y_ ID, Course_ ID) R12(C_ S_ Y_ ID, Semester)

R13(C_ S_ Y_ ID, Year) Functional Dependencies (contd) Normalization #2: The following relation, which provides information about each computer, is in 3NF: Computer (Location_ ID5, Computer_ ID, Computer_ Type, Model, Operating_ System, Service_ Tag, Express_ Service_ Code, Installation_ Date) However, this relation is not in BCNF because the Service_ Tag and the Express_ Service_ Code are also unique to each. To make BCNF, modify as

follows: R1(Location_ ID5, Computer_ ID, Computer_ Type, Model, Operating_ System, Installation_ Date) R2(Location_ ID5, Computer_ ID, Service_ Tag) R3(Location_ ID5, Computer_ ID, Express_ Service_ Code) Functional Dependencies (contd) Normalization #3: The below relation, which gives information about each waitlist, is in BCNF: Waitlist (Waitlist_ ID, Location_ ID5, Operating_ System)

Query- Budget Analysis Budget: How much money is spent on employees in the previous month compared to the previous 3 months and the same month the previous year? For these same five months, what is the ratio of total employee cost per student user? For the last month, what would be the additional cost of increasing wages by $0.50, and of extending lab hours by 1, 2, 3, or 4 hours? What is the three-month moving average of total money spent on employees each month over the previous year? - This combines a client-requested query with a ratio that can analyze the productivity of the lab. - The additional hours are only part-time employees. - The additional hours simply extend the part-time employees who worked the last shift. - Full-time employees simply receive Salary/ 2000 per hour worked.

- Full-time employees work only one shift per day. - This query meets the needs of our client very closely. Budget decisions come down to simple number comparisons. When evaluating facility costs, labor represents an enormous percentage (along with electricity and maintenance). Forecasting overall changes in the budget by increasing (or hypothetically decreasing) wages helps the management make decisions quickly. Additionally, the parameter, ($ of wages)/ (student served), gives a general perspective of the dollar value of providing computing services to the student. 15 Query Budget Analysis (contd)

SQL> SELECT (COUNT(i.TimeSlot_ID)*p.Wage + COUNT(w.Shift_ID)*(f.Salary/ 240)) as Total_Cost, Total_Cost/ (COUNT(l.Date)), (COUNT(i.TimeSlot_ID))*(0.25) as Wage_Increase_of_25, (COUNT(i.TimeSlot_ID))*(0.5) as Wage_Increase_of_50, MONTH(t.Date) as Month, YEAR(t.Date) as Year FROM Part_Time p, Includes i, Works w, TimeSlot t, Staffs, Logs_Into l, Full_Time f WHERE i.Shift_ID = w.Shift_ID AND (w.WMF_Staff_ID = p.WMF_Staff_ID

OR w.WMF_Staff_ID = f.WMF_Staff_ID) AND MONTH(t.Date) = (SELECT MONTH(t.Date) FROM Timeslot t WHERE ((MONTH(t.Date) = MONTH(Date()) 1 OR MONTH(t.Date) = MONTH(Date()) 2

OR MONTH(t.Date) = MONTH(Date()) 3 OR MONTH(t.Date) = MONTH(Date()) 4) AND YEAR(t.Date) = YEAR(Date())) OR YEAR(t.Date) = (SELECT (YEAR(t.Date) FROM Timeslot t

WHERE YEAR(t.Date) = YEAR(Date()) 1 AND MONTH(t.Date) = MONTH(Date()) 1)) AND MONTH(l.Date) = 16 Query Budget Analysis (contd) (SELECT

MONTH(l.Date) FROM Logs_Into l WHERE (MONTH(l.Date) = MONTH(Date()) 1 OR MONTH(l.Date) = MONTH(Date()) 2 OR MONTH(l.Date) = MONTH(Date()) 3 OR MONTH(l.Date) = MONTH(Date()) 4) AND

YEAR(l.Date) = YEAR(Date()) OR YEAR(l.Date) = (SELECT YEAR(l.Date) FROM Logs_Into l WHERE YEAR(l.Date) = YEAR(Date()) 1 AND MONTH(l.Date) = MONTH(Date()) 1)) ORDER BY Month DESC, Year DESC;

Query Budget Analysis (contd) SQL> SELECT COUNT(i.TimeSlot_ID)*p.Wage as Additional_Cost1, 2*Additional_Cost1 as Additional_Cost2, 3*Additional_Cost1 as Additional_Cost3, 4*Additional_Cost1 as Additional_Cost4 FROM Includes i, Part_Time p, TimeSlot t WHERE

AND AND AND NOT EXISTS HOUR(t.Date) + 1 t.TimeSlot_ID = i.TimeSlot_ID i.Location_ID = w.Location_ID w.WMF_Staff_ID = p.WMF_Staff_ID ; Query Budget Analysis (contd)

5 separate queries: SELECT (Count(t.TimeSlot_ID)*p.Wage+Count(w.TimeSlot_ID)*(f.Salary/ 2000)) AS Total_Cost, (Count(t.TimeSlot_ID)*(p.Wage+0.5)+Count(w.TimeSlot_ID)*(f.Salary/ 2000)) AS Total_Cost1, count(l.date) AS mycount0 FROM Part_Time AS p, Works_T AS w, TimeSlot AS t, StaffAS s, Log s_Into AS l, Full_Time AS f WHERE (((w.WMF_Staff_ID)=p.WMF_Staff_ID Or (w.WMF_Staff_ID)=f.WMF_Staff_ID) And ((w.Timeslot_ID)=t.Timeslot_ID) And ((Month(t.Date))=Month(Date()))) GROUP BY w.Timeslot_ID, p.Wage, f.Salary; One query to bring them all together: SELECT Sum([Query 2a].Total_Cost) AS Total_Employee_Cost, Sum([Query

2b].Total_Cost_1Mo) AS TEC1Mo, Sum([Query 2c].Total_Cost_2Mo) AS TEC_2Mo, Sum([Query 2d].Total_Cost_3Mo) AS TEC3Mo, Sum([Query 2e].Total_Cost_1Yr) AS TEC1Yr, Max([Query 2a].mycount0) AS Count0, Max([Query 2b].mycount1) AS Count1, Max([Query 2c].mycount2) AS Count2, Max([Query 2d].mycount3) AS Count3, Max([Query 2e].mycount1yr) AS Count_1Yr, Sum([Query 2a].Total_Cost1) AS Total_Employee_Cost_50, Sum([Query 2b].Total_Cost_1Mo1) AS TEC1Mo50, Sum([Query 2c].Total_Cost_2Mo1) AS TEC_2Mo50, Sum([Query 2d].Total_Cost_3Mo1) AS TEC3Mo50, Sum([Query 2e].Total_Cost_1Yr1) AS TEC1Y50 FROM [Query 2a], [Query 2b], [Query 2c], [Query 2d], [Query 2e]; Query Budget Analysis (Prt Sc)

Query Budget Analysis Report Query Maintenance Mgmt Maintenance: Rank the computers by number of total maintenance issues by location, then by Operating System, then by Model. What is the average (and variance of) number of repairs per computer for each location and operating system? What is the mean time to failure for each computer, sorted by location, then Operating System (Mac or PC)? - This query will ignore any computers that have not experienced any maintenance issues.

- We will enter the resulting data into Excel, which will determine the distribution of the TTF. - Date includes a time, and a computer cannot have simultaneous repair requests. Query Maintenance Mgmt Query 4 1. Rank the computers by number of total maintenance issues. SELECT c.Computer_ID as Computer_ID, COUNT(r.Date) as Repairs, c.Location_ID as Location, c.Operating_System as Operating_System, c.Model as Model FROM Computer c, Requsts_Repairs_From r WHERE c.Computer_ID = r.Computer_ID AND c.Location_ID = r.Location_ID

GROUP BY c.Operating_System, c.Location_ID, c.Computer_ID, c.Model; Query Maintenance Mgmt (contd) 2a. What is the average number of repairs per computer for each location? SELECT c.Location_ID as Location, COUNT(r.Date)/ COUNT(c.Computer_ID) as Repair_Rate_L FROM Requsts_Repairs_From r, Computer c WHERE c.Computer_ID = r.Computer_ID AND c.Location_ID = r.Location_ID

GROUP BY Location; 2b. What is the average number of repairs per computer for each operating system? SELECT c.Operating_System as Operating_System, COUNT(r.Date)/ COUNT(c.Computer_ID) as Repair_Rate_OS FROM Requsts_Repairs_From r, Computer c WHERE c.Computer_ID = r.Computer_ID AND c.Location_ID = r.Location_ID GROUP BY Operating_System; Query Maintenance Mgmt (contd) 3. What is the mean time to failure for each computer, sorted by location, then Operating

System (Mac or PC)? SELECT c.Computer_ID AS Computer_ID, c.Location_ID AS Location, (MAX(r.Date)c.Installation_Date)/ COUNT(r.Date) AS Mean_TTF FROM Requsts_Repairs_From r, Computer c WHERE c.Computer_ID = r.Computer_ID AND c.Location_ID = r.Location_ID GROUP BY Operating_System, c.Computer_ID, c.Location_ID, c.Installation_Date; Query Maintenance Mgmt(Prt Sc) Query Maintenance Mgmt (Prt Sc)

Query Maintenance Mgmt (Prt Sc) Query Maintenance Mgmt (Prt Sc) Query Maintenance Mgmt Report Query Human Resources 1. Human Resources: What is the average length of employment grouped by location by

position for all staffposition , with variance? For each position, what is the average total length of employment with the MCF, with variance? For each position, what is the average and variance of the number of previous positions held by employees, and what is the most common previous position? For each position, what is the average student level (part-time staff) ? SQL> Select AVG(s.termination_date-s.hiring_date), VARIANCE(s.termination_dates.hiring_date), From Staff s, Group By WMF_Staff_ID SQL> Select AVG(SUM(Termination_Date-Hiring_Date)) , VARIANCE(SUM(Termination_Date-Hiring_Date)), AVG(Count(Distinct WMF_Staff_ID), VARIANCE(Count(Distinct WMF_Staff_ID) FROM Staff s, Maintenance m, Supervisor sup, Scheduler sch, Other oth, Part_Time pt

WHERE s.SSN=m.SSN AND s.SSN=sup.SSN AND s.SSN=sch.SSN AND s.SSN=oth.SSN AND s.SSN=pt.SSN SQL> Select AVG(pt.rank) FROM Part_Time pt GROUP BY WMF_Staff_ID Query Scheduling 1. Give a ranked list of part-time employees who have worked a timeslot the most number of times over the last month, for each timeslot. SELECT pt.WMF_Staff_ID AS WMF_Staff_ID, HOUR(t.Hour) AS Timeslot_Start_Hour, COUNT(t.Hour) AS Number_of_Times_Worked FROM Part_Time AS pt, Timeslot AS t, Works_T AS w

WHERE MONTH(t.Date) = MONTH(Date()) AND w.Timeslot_ID = t.Timeslot_ID AND w.WMF_Staff_ID = pt.WMF_Staff_ID GROUP BY pt.WMF_Staff_ID, t.Hour, t.Timeslot_ID; Query Scheduling 2. Who has never worked a timeslot that had a waitlist? SELECT pt.WMF_Staff_ID AS WMF_Staff_ID FROM Part_Time AS pt WHERE NOT EXISTS (SELECT * FROM Works_T w, Enters e, Timeslot t WHERE pt.WMF_Staff_ID = w.WMF_Staff_ID

AND Hour(e.Time) = t.Hour AND w.Timeslot_ID = t.Timeslot_ID AND t.Date = e.Date); Query Scheduling 3. Who has worked a beginning or ending shift (first or last of the open period) during the month? -Timeslot_IDs are consecutive SELECT pt.WMF_Staff_ID AS WMF_Staff_ID, w.Location_ID AS Location FROM Part_Time AS pt, Works_T AS w, Timeslot AS t WHERE pt.WMF_Staff_ID = w.WMF_Staff_ID AND w.Timeslot_ID = t.Timeslot_ID

AND (EXISTS SELECT * FROM Works_T AS w WHERE t.Timeslot_ID-1 = w.Timeslot_ID AND w.Location_ID = Location AND NOT EXISTS w.WMF_Staff_ID) OR (EXISTS SELECT * FROM Works_T AS w WHERE t.Timeslot_ID+1 = w.Timeslot_ID AND w.Location_ID = Location AND NOT EXISTS w.WMF_Staff_ID);

Query Service Levels 1. Service Levels: What is the approximate current wait time for each location? What is the average wait time and variance for a given day? What is the utilization rate over a one-week period? Rank each parameter by location. - Helping to create a wait time board, this query helps distribute students among the available resources. - Service level, and specifically wait time, is arguably the most important parameter to which the management can gauge each facility's effectiveness. Query Service Levels SQL> IF COUNT(w.waitlist_ id)>2 FROM Waitlist w, Enters e, Logs_ Into li, Computer c

THEN Select AVG(li.time-e.time)*COUNT(Waiting) WHERE e.UID=li.UID AND Year(e.date)=Year(current_ timestamp) AND Year(e.month)=Year(current_ timestamp) AND GROUP BY Location, Operating_ System DESC ELSE Print Wait time is Count(w.waitlist_ id)*4 SQL> SELECT AVG(li.time-e.time), VARIANCE(li.time-e.time) FROM Enters e, Logs_ Into li WHERE e.UID=li.UID GROUP BY Date, Location, Operating_ System SQL> Select (COUNT(li.computer_ id)/ COUNT(c.computer_ id)), From Logs_ Into li, Computer c, Timeslot t Where li.time BETWEEN MIN(t.time) AND MAX(t.time) AND t.date=3/ 17/ 06

GROUP BY Location_ ID IN DESC, Operating_ System SQL> Select SUM(lo.time-li.time)/ 1080 From Logs_ Out_ Of lo, Logs_ Into li, Computer c Where lo.date=li.date AND lo.UID = li.UID AND lo.Computer_ ID = li.Computer_ ID GROUP by Questions? 37

Recently Viewed Presentations

  • Sports Nutrition - Western Oregon University

    Sports Nutrition - Western Oregon University

    Sports Nutrition ... recovery Are believed to stimulate and maintain muscle growth Nutrition Supplements and Ergogenic Aids Amino acids "Andro" and DHEA Caffeine Carnitine Chromium Coenzyme Q10 Creatine Nutrition Supplements and Ergogenic Aids Ephedrine Ginseng Glutamine Medium-chain ...
  • 9.17.19 GOFO! - Edl

    9.17.19 GOFO! - Edl

    Bingo Create 16 equations & graph them: 5 in Standard form. 5 in slope-int form. 4 in point-slope form. 1 vertical line. 1 horizontal line. The range for all must be {-10<y<10} Domain for all must be {-10<x<10} 8 slopes...
  • Optics I

    Optics I

    Phase shifts with coated optics Total Internal Reflection occurs when sin(qt) > 1, and no transmitted beam can occur. Applications of Total Internal Reflection Three bounces: The Corner Cube Fiber Optics Design of optical fibers Propagation of light in an...
  • L 10 Torque and Angular Momentun - Physics &amp; Astronomy

    L 10 Torque and Angular Momentun - Physics & Astronomy

    L-10 Torque and Rotational Motion Torque makes things spin! What makes something rotate in the first place? TORQUE To make an object rotate, a force must be applied in the right place. the combination of force and point of application...
  • Golf Cart: Rules - plu.edu

    Golf Cart: Rules - plu.edu

    Golf Cart: Rules Do not drive on grass Do not drive on Red Square Do not tamper with any controls Do not speed around corners Keep the cart clean Do not throw or leave trash in the dash, back of...
  • Diversifying Music Recommendations

    Diversifying Music Recommendations

    Diversifying Music Recommendations. Houssam Nassif, Kemal Oral Cansizlar, Mitchell Goodman, S.V.N. Vishwanathan. [email protected] Thank you all for coming. I will be presenting today an experiment of diversifying music recommendation that my colleagues Oral, Mitchell,Vishy and I did at amazon
  • Sikhism - mcsd.org

    Sikhism - mcsd.org

    Sikhism Sikhism is a monotheistic religion that shares features with Islam and Hinduism Sikhs call their religious teachers Gurus Sikhism was founded in the 16th century by Guru Nanak There have been 9 gurus since and the holy scripture of...
  • How To Prepare Wild Game - Meetup

    How To Prepare Wild Game - Meetup

    Rigor mortis is not a disqualifier, especially if the other signs are not present. ... thus the slang "rabbit fever." Other animals can catch the disease but it is very rare. ... How To Prepare Wild Game