High School Outreach Database

1. High School Outreach Database Kamil Cygan, Bryan Quach, Alyssa Kuschel, Steven Tran

2. Requirements • Maintenance of data relating to a university-based program of visiting high schools for computer science outreach • Goal of IL Computes Consortium is to provide students with info about computing careers • Main goal is to keep track of all the major contacts between Consortium Personnel and high schools, with each visit being recorded • Also have to keep track of contacts at schools where we have attempted to arrange a visit and have yet to visit but it is planned

3. Desired Functionality • Create an automated mechanism to load data from an excel spreadsheet • Edit existing webform and connect this to database • Updating visits when a future visit is completed • Visited attribute is Y/N that corresponds to whether the visit has been completed or it is planned for the future • Update personnel when a consortium person is added through the webform that was not already part of the database

6. Create Tables, Populating Database http://code.google.com/p/comp353hsdb/downloads/list

7. Sample Queries 2) Same as 1, but for a specific person's upcoming visits select pfname, plname, vdate from visits, personnel where personnel.pemail = visits.pemail and cvisited = 'N‘; 1) Get a list of upcoming scheduled visit dates select vdate from visits where cvisited = 'N';

8. Sample Queries 3a) Get a report on visits that have occurred during a specified period, including at least summary measures like number of schools visited and number or students reached. Also desirable at times to list off the schools and who made the visit select count(DISTINCT scode) SCHOOLS_VISITED, sum(nstudents) NUM_STUDENTS from visits where vdate >= to_date('01-jan-2000') and vdate < to_date('01-jan-2005');

9. Sample Queries • 3b)Also desirable at times to list off the schools and who made the visit select sname, pfname, plname from visits, schools, personnel where schools.scode = visits.scode and personnel.pemail = visits.pemail and vdate >= to_date('1-jan-2000') and vdate < to_date('1-jan-2005');

10. Sample Queries 4) Which schools were contacted during a specified period of time but have not been visited during the latest school year, and get information on the people contacted at those schools. select sname, cname from schools, contacts, visits, attempted where schools.scode = visits.scode and schools.scode = attempted.scode and visits.scode = attempted.scode and cvisited = 'N' and contacts.cemail = visits.cemail and adate >= to_date ('1-jan-2000') and adate < to_date ('1-jan-2005') and vdate >= to_date ('1-jan-2001') and vdate < to_date ('12-dec-2001');

11. Web Based Form Demo • http://comp353hsdb.googlecode.com/files/hsvisitsurvey.html • http://comp353hsdb.googlecode.com/files/hscontactsurvey.html

12. Future Work • Currently data about visits and school contacts is stored using a crude spreadsheet. • This spreadsheet has ~400 schools that do not match the school names that are in the other school databases that were given to us (~5520) • Need to create a script that pattern matches this to our allschools data and then matches school code to these schools • Need a webform for updating attempted when it becomes a planned visit • Need a webform for future visits, right now have to manually input it through SQL

13. ANY QUESTIONS?