People Databases: 

• Intranet site directory (everyone except grad students)

• Admin - Derek
  
• Read - other office staff
• View - everyone

• defines email lists (faculty, staff, etc)
• defines access privileges for various other applications
• defines researchers for public web site
• produces lists of names for: space table, BPS, timesheets

• Critical to site
  
• No personal data
  
• Most info. is on public web site (for researchers only).
• Does contain some CWLs.

• admin form
• 2 files; index, functions
  
• ~750 lines
  

• intranet lookup
• 10 files; ~950 lines
  

• desk assignments in Hennings/Hebb

• Space/Admin- Bridget
• Read - other office staff
  

• desk assignments are visible to other database admins (department & grad student)
  
• locations (bldg, room) are linked to records in department and grad student tables
  

• Useful to Bridget and Paul (and possibly Derek and Oliva)
  
• Not private - doesn't even contain names
  

• admin form
• 1 file; ~1000 lines
  

• committees
• courses
• engphys
• grad_sup
• other
• phys449
  

• faculty teaching and committee assignments
• operates like a giant spreadsheet but takes inputs from various sources
• produces a sign-up sheet for instructors
• produces teaching, committee reports
• faculty can see BPS reports for each other
  

• Admin - Doug, Chris W., Derek
• Read - select office staff and faculty
• View - faculty; everyone (teaching,cmte reports)
  

• uses gradstudents.students to update grad_sup table
• uses directory.department for list of dept. members
• used by program coords. to feed SIS

• Critical for teaching assignments
• Results propagated to SIS
  
• Not private
  

• admin form
• controller index
• BPS class with separate Data and View extensions
• 8 files; ~3000 lines
  

• {yyyy}_groups
• {yyyy}_holidays
• {yyyy}_tsheets
• {yyyy}_users
  

• vacation/sick time tracking for staff
• group features allows for unit planning

• Admin - Derek, Paul 
  
• Report - Martin - can see all 116 reports

• Group admin
• Group member
• Group view
• User edit
• User view only
  

• New version removed look-up in dept. directory to add users.

• Extremely useful for Derek and staff members
• personal data on sick/vacation times
• may include notes on reasons for sick time
• no extraneous personal info. (ie. not a staff admin. tool in general)

• 2013-06-03: total rewrite
• controller index
• class with separate Data and View extensions
• data validation
• 5 files; ~5000 lines

• upload documents to Intranet and provide restricted access
• allows for categorization (eg. Dept. Meetings)
• access groups defined by email lists
• can set expiry dates
  

• Admin - Derek, Doug, Bridget P. primarily
• View - document-specific list of email lists
  

• looks up usernames in directory.department and gradstudents.students to control access
  

• Useful to admin. staff (apparently)
• some documents might contain sensitive data (Dept. Minutes?)
  

• admin tool
• 1 files; ~500 lines

• view tool
• 2 files: ~200 lines
  

• applicant_docs
• applicant_files
• committee_scores
• committee_comments
  

• processing of grad student applications

• Admin - Oliva
  
• Query - select faculty
• Cmte - admissions committee
• View - faculty
  

• info. downloaded from FoGs web site
• when accepted, adds record to student table
  

• Extremely useful tool for admissions committee
• Useful for potential supervisors
• Extremely sensitive:
  - personal data (Employee ID, SIN)
  - letters of reference, transcripts

• admin form
• 2.5 files; ~1200 lines
  
• controller index, shares functions with students code
  

• committee tools
• 6 files; ~750 lines
  

• supervisor view
• 2 files; ~300 lines
  

• administration of current graduate students
• Intranet site directory
  

• Admin - Oliva
• Query - select faculty
• View - everyone

• defines grads@phas email list
• defines access privileges for various other applications
• produces lists of names for TAs
• uses department db for list of potential supervisors

• Critical to site
• Contains much personal data
  

• admin form
• 2.5 files; ~1000 lines
• controller index, shares functions with applicants code
  

• courses
• inst_requests
• ta_applications
• ta_assignments
• ta_requests
• exam_schedule_{sess}{trm}
• exam_duties_{sess}{trm}

• BPS for TAs?
• tool for assigning TAs to courses
• TAs can self-register and request courses
• previous courses and evaluation scores are shown
• weights are assigned to allow best candidates to top of selection list (requests, experience, etc)
• exam duties tool for assigning TAs to invigilating/marker duties

• Admin - Bridget P., Oliva, Doug, Fran
• Read - select faculty and program coords.
• Instructors and TAs - two different reports showing Course and TA name
  

• references gradstudents database for background info. (data only stored in this database if TA not listed in gradstudents)
• uses ta_assignments table to get list of current TAs
• exam_schedule table: Bridget P. provides data which is directly imported via phpMyAdmin

• Extremely useful for TA assignments
• Potentially some sensitive data for nonPHAS TAs (Employee ID, SIN)

• admin form
• controller index
• TA class with separate Data and View extensions
• 5 files; ~3000 lines

• 1 file; 40 lines

• includes self-registration form
  
• 2 files; ~230 lines
  

• 1 file; 250 lines
  

• {session}_students
• {session}_programs

• PHAS courses (class-lists)
  
• PHAS programs

• Read - various applications
  

• daily download from SIS to omega:/opt/sysadmin/sis/regi/
• used by Clab Registration Kiosks
• used to generate pa2-5 email lists
• no longer used by Carchive
  

• Extremely helpful for automating undergrad computer account creation
• Critical for automatic undergrad email list generation
• Personal student data (names and student nos.)
  

• students.pl
• programs.pl

• /clabreg

Financial Databases:

• speedchart administration
• only as required to handle shops/stores/printing billing
  

• Admin - Anilu, Amy, Paul, Martin
  
• View - shops/stores users
  

• referenced for billing of shops, stores (waterjet) and printing charges
• provides valid "Accounts" to addusr script
  

• Critical for billing
• data not personal, but not for public consumption - speedchart/ledger nos.
• 2012-12-10: data validation applied
  

• 3 files; ~500 lines
• config.php contains data validation rules
• db_functions.php contains database access functions
  

• report of all charges against all speedcharts
• web_report.php: report period and content are selectable
• /opt/sysadmin/accounts/email_report.php: sends email for default period

• lists speedcharts for shop/stores users (~150 lines)
• uses admin/config.php for validation
  

• work order administration for Machine Shop, Electronics Lab, STS
  
• billing for above plus HPC
  

• Admin - Martin, Jon N.
• Shop - selected technicians
• Accounting - financial staff
• View - everyone can see status of current work orders
  

• uses accounts database for speedcharts
  
• can import expenses from Centersuite
• can import hours from a spreadsheet
• can export charges for sending to UBC Finance
  

• extremely useful (if not critical) for running of shops
• financial information
  
• no personal info. 
• 2013-01-?: data validation applied
  

• controller index
• SHOPS class with Data and View extensions
• config file
• 8 files; ~3600 lines

• client viewer
• 2 files; ~450 lines
  

• stores inventory maintenance
• kiosks in Stores
  
• used for Waterjet Cutter usage
  

• Admin - Martin, StoresPerson, backup staff
• Accounting - financial staff
  

• uses accounts database for speedcharts
  

• Critical for running Stores
• replacement in the offing?
• financial information
• inventory
  

• 28 files; >5000 lines
• inventory maintenance
  

• 4 files; ~550 lines
• stores billing
  

• printer billing
  

• Admin - financial staff
  

• uses accounts database for speedcharts
  
• monthly script saves non-cash balances from pykota database and zeros them out
  (delta:/opt/sysadmin/printing/monthend.pl)
  

• useful for expediting printing charges
• would be nice to have a "client" viewer (so grantholders could see their print charges)
  

• 1 file; 424 lines
  

Sysadmin Databases:

• maintenance of DNS and DHCP tables
• should be under sysadmin database(?)
  

• Admin - IT staff
  

• builds files on dns server
• uses directory to ensure local contact
  

• Critical for network operation
• data not personal - location and type of computers
  

• 4 files; ~1300 lines

• computer account meta-data
• copy of users.db file (hyper)
• eventually use database instead of file, OR
• use LDAP instead?
  

• Admin - IT staff
  

• used by various user account and email list admin. perl scripts (ie. not on Intranet)

• contains names and student nos.
  

• generic SQL lookup tool
  

Miscellaneous Databases:

• {session}_sections
• {session}_meetings
• {session}_instructors

• current course information for various applications
  

• Read - various applications
  

• daily import from SIS system
  
• used by Carchive
• used to generate public Course Listings pages
  

• Critical for Carchive
• publically available
  

• get_sis.pl (download XML files)
• parse_sis.php (upload to database)
  

• Student Machine Shop Course registration

• 2 files; ~400 lines
  

• Intranet session management
  

• dept. shared calendar
• use for room bookings
  
• otherwise not widely used, but there are loyal fans