1 / 17

Office of Environmental Health and Safety

Office of Environmental Health and Safety. Team 7 Allen Chen Anar Joshi Jiho Tahk Minh Tran Justin Yang. Agenda. Client EER Relational Schema Access Database Queries Normalization Next Steps. Client. Office of Environmental Health & Safety. Organization: EH&S.

gili
Download Presentation

Office of Environmental Health and Safety

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Office of Environmental Health and Safety Team 7 Allen Chen Anar Joshi Jiho Tahk Minh Tran Justin Yang

  2. Agenda Client EER Relational Schema Access Database Queries Normalization Next Steps

  3. Client Office of Environmental Health & Safety

  4. Organization: EH&S Office of Environmental Health and Safety: provide health, safety, and environmental guidance and services for the campus

  5. Organization:Workplace Safety • Responsibilities include: • Biosafety • Food permits • Indoor air quality • Contact: Phil Maynard, Laboratory Safety Specialist • Lead for inspection of over 1,200 fumehoods on campus

  6. Organization:Ventilated Systems Gas Cabinet Vented Sink Exhausted Laminar Flow (ELF) Hood

  7. Problem ContextCurrent State Different ventilation systems documented on different databases Standard forms for all databases Hard to obtain useful information Difficult to compile reports from several separated databases. Inaccurate/Nonspecific data due to use of standard forms

  8. What we have done Created a prototype database using MS Access including all crucial elements Developed queries to get information from the database

  9. EER diagram

  10. Relational Schema 1. People (PID, Fname, Lname, Phone#, email) 1a. Surveyor (PID1, SuperID1b, wage) 1b. Supervisor (PID1, salary,) 1c. Other(PID1) 1d. User (PID1, department, purpose_type) 1e. PPCS_Technician (PPCSID1, Group) 1f. Building_Coordinator(BCID1, building, Rm_num) 2. Complaint (CID, Type, Location5, Date, Time, Complainer_PID1) 3. Inspection_Report (RID, Inspection_ID4, Insp_Rpt_Date_filed, comments, time_spent, velocity, mont_velocity) 4. Inspection_Timeslot (Inspection_ID, Date_filed, Date_required, Location5, Time, SurveyorPID1a, violationID7, ventSysID6) 4a. Emergency_Inspection (Inspection_ID4, SuperID1b) 4b. Routine_Inspection (Inspection_ID4) 4c. Repair_Validation (Inspection4, PPCS_requestor1, repair_successful) 5. Location (LID, Building, Room, BCID1f) 6. Ventilated_System (VSID, LID5, Manufacturer, Model, Construct, Install_date) 6a. ELF_Hood (VSID6, Fire_sprinkler, blower_switch, ) 6b. Gas_Cabinet (VSID6, Gas_type, concentration, gas_exp_date) 6c. Vented_Sink (VSID6, Number_sink_holes, hazards) 7. Violation (VID, date_recorded, date_fixed, reportID3) 7a. Overdue_Violation (VID7, status) 7b. Airflow_Violation (VID7, PPCSID1e) 7c. Accuracy_Violation (VID7, PPCSID1e, monitorID8) 8. Monitor (MID, VSID7, brand, model, type) 8a. Flow_Sensor (MID8, airflow_speed) 8b. Gas_Sensor (MID8, levels, temperature, pressure) 9. Super_WorkOrder_To (SuperID1b, PPCSID1e,Date_issued) 10. Files_Complaint (SuperID1b, complaintID2) 11. User_Has_Room_In (userID1d, locationID5) 12. Send_Report_Copy_To (superID1b, reportID3) 13. Report_CCed_To (BCID1f, reportID3) 14. Vent_Violations (ventSysID6, violationID7)

  11. Access Database:Relationships

  12. Access Database:Tables

  13. Queries

  14. Query: What is the Seasonality of System Evaluations? SELECT t.time_period, COUNT(t.ventSysID) AS NumSysEvals FROM Inspection_Timeslot AS t GROUP BY t.time_period;

  15. Query: Which systems need to be checked? SELECT VS.VSID FROM Ventilated_System AS VS WHERE EXISTS ( SELECT * FROM Violation, Vent_Violations WHERE Vent_Violations.ventSysID = VS.VSID AND Violation.VID = Vent_Violations.violationID AND Violation.date_recorded - Date() < next_days) OR (mean_time_between_failure^(within_how_many_days_do_you_not_want_a_failure)* e^(-mean_time_between_failure)/factorial) > Min_Probability_of_Failure AND NOT EXISTS (SELECT * FROM Inspection_Timeslot AS T WHERE T.ventSysID=VS.VSID And T.Date_filed>(Date()-do_not_count_systems_checked_in_the_past_how_many_days) );

  16. Normalization Multivalued Attributes to Single valued Attibutes 1. People (PID, Fname, Lname, Phone#, email) changed to People (PID, Fname, Lname, Phone#) People_email(PID, email) 2. Vented_Sink (VSID6, Number_sink_holes, hazards) changed to Vented_Sink (VSID6, Number_sink_holes) Vented_Sink_Hazard(VSID6, hazard) Violation of 3NF Accuracy_Violation (VID7, PPCSID1e, monitorID8 ) * Can be accessed using multiple joins * Readily accessible for user to report

  17. Next Steps

More Related