Database: gradstudents

Date: 2012-09-06

Purpose:

• original purpose was to provide graduate coordinator with a database of graduate program applicants and students (old data was imported but is spotty)
• current graduate student information feeds on-line directory and email list
• current graduate applicant information available on-line to potential supervisors
  
• more recently evolving into a candidate evaluation tool for the admisisons committee
  
• program coordinator imports applicant information from FoGS (Faculty of Graduate Studies)
• accepted candidate data is transferred to student record table
  

Contact Person(s): (name, email, phone#)

• technical, Mary Ann, map@phas.ubc.ca, 2-6913
• end-user, Oliva, gradcoord@phas.ubc.ca, 2-4245
• Graduate Program Director and Admissions Chair have also been involved
  

Maintained by: (URLs, users, scripts, cronjobs)

• student/applicant information maintained by:
• https://secure.phas.ubc.ca/forms/graddb/index.php
• users: Graduate Program Co-ordinator (and any other staff who are assisting)
• director and committee have read access
  
• committee comments and scores:
  • https://secure.phas.ubc.ca/forms/graddb/committee/index.php
  • users: Graduate Admissions Committee members
• applicants table yearly purge:
  
  • https://secure.phas.ubc.ca/forms/graddb/purge.php
  • users: technical staff running by hand in September each year
    

Data Accessed by: (URLs, users, scripts)

• Graduate Program Director/Advisors/Committee:
  
  • have read access to student and applicant maintenance forms
  • can make SQL queries against data via student/applicant_report.php scripts
• potential supervisors:
  
  • can view applicant forms, references, transcripts via: https://secure.phas.ubc.ca/forms/graddb/supervisors/index.php
  • users: faculty, postdocs and adj-assoc members (and "GradSups" flagged as such in the dept. directory)
    
• those designated as "current_student"s in this database can enable/modify their public web presence on our server
  
  • url: https://secure.phas.ubc.ca/forms/grads/index.php
  • users: current grad students
  • database: directory.grads
    

Dependencies/Effects: (databases, files, cronjobs)

• directory.grads database - public web information for grad students
• used by Intranet site in general to determine access (current_student field = 'y')
• used to populate grads@phas.ubc.ca (mail:/opt/sysadmin/passwd/check_grads.pl)
  

Changelog:

• 2010-04 - Added two new tables: cmte_comments and cmte_scores
  - for Admissions Committee to rate applicants and share comments
• 2010-04 - Added new form "Change Applicant Status"
  - for graduate coordinator to make bulk changes in applicant status (incl. bulk transfers to student table)
• 2012-03 - Developed Purge script
  - run (by hand) every September to purge sensitive data from Applicant records
  

Wish List:

• screen layout dates to orginal implementation - could benefit from a rework, eg. more compact; easier to modify without breaking it
  

Code Notes:

• this is one of our first web-based database applications
• original web version written by two co-op students in 200x (~3?)
• has good "constructor" interface (index.php is one big switch)
• at least some separation of concerns - ie. code<->file<->table match (eg. applicants.php, students.php)
• database and display interfaces hopelessly interwined
• way too much dependency on global variables

Tables:

1. applicants - record for each graduate program application
   
2. applicant_docs - meta-data for scanned applicant documents
   
   • FoGS applications, transcripts, letters of reference
3. applicant_files - scanned applicant documents (see above)
   
4. cmte_comments - per-applicant comment log - used by Admissions Committee
   
5. cmte_scores - per-applicant score - used by Admissions Committee
   
6. queries - saved queries made against applicants and students tables
   
7. students - record for each PHAS graduate student
   
8. users - allowed users list
   
   • access levels: admin, query, read
   • cmte field: y/n

Files:

• main directory (/wwws/forms/graddb):
  
  • functions.php - collection of general-purpose functions
    
    
  • applicants.php - main applicants table interface
  • edit_applicant.php - functions supporting applicants table interface
    
  • applicant_report.php - implementation of general-purpose database query tool (/usr/local/lib/php/db_report.php) on applicants table
  • upload_file.php - code to upload scanned documents into database
  • view_file.php - code to retrieve scanned documents
  • purge.php - run this in September to delete purge applicants table of sensitive information
    
    
  • students.php - main students table interface
  • edit_student.php - functions supporting students table interface
  • student_report.php - implementation of general-purpose database query tool (/usr/local/lib/php/db_report.php) on students table
  
  
• committee sub-directory:
  
  • rateform.php - form to insert comments or rate applicant
  • summary.php - current applicant view with score added
  
  
• supervisors sub-directory:
  
  • index.php - list of current applicants with option to view documents for a particular applicant
  • show_doc.php - code to actually retrieve and display a document