SED University Extension

Sakai External Database University Extension Data

The University Extension runs a program known as Open Enrollment which allows extension students to attend official campus courses. However, their enrollments are tracked through an independent system and are not accounted for in BANNER in any fashion. Seeing this problem, MyUCDavis implemented a system to collect extension enrollments from the UNEX system and add them to the list of enrolled students in the Course Management application.

Therefore, in order to meet expectations, Sakai will need to do a similar feed of Open Campus students and add them to the enrollments for a course.

University Extension Data Architecture

The University Extension system stores its enrollment data in an Oracle-based application. In order to insulate us from their underlying table architecture, they have created a view that contains all of the information we require.

OPEN_CAMPUS_ENROLLMENTS

Column

Description

campus_course_record_number

The BANNER CRN of the enrolled course.

term_code

The BANNER term code of the enrolled course.

external_id

The external id of the student. This external id is a synced value between the University Extension database and MOTHRA.

External IDs

Open Campus Students are issued unique ids by University Extension, which are then matched with their Campus computing account information. This is done for several reasons:

  1. Most Open Campus students have no other affiliation with the University, therefore University Extension must sponsor their campus computing account. In order to keep track of these students, the shared external id is used.
  2. In some cases, ex-students will take Open Campus courses. In order to avoid confusion, these students must be referenced by an ID other than their student id.

Requirements

  • Availability: The University Extension data used by Sakai must be available on a 24x7 basis.
  • Refresh: MyUCDavis currently refreshes Extension data nightly, so the minimum refresh interval will be nightly. A long term goal would be to increase the refresh frequency of Extension data.

Design

The University Extension database does not maintain a 24X7 service level. Therefore, in order to avoid periods of downtime due to database maintenance, a combination of a local table and a pl/sql feed procedure will be created to retrieve any and all updated data each time it is run.

One of the largest issues with the addition of Open Campus students is with the matching of person information and encoding of student ids:

  • New Students: New extension students do not start out with any computing account infortmation and therefore do not have mothra ids or kerberos names. Until those students do register a computing account, they will not be successfully fed.
  • Existing Students: Existing students will be provided into courses using their Mothraid, which avoids the problem MyUCDavis had. MyUCDavis used StudentID to refer to students in courses, which means their feed needed to issue fake student ids for each extension student.

The current view-based structure of the Course Management schema lends itself well to integrating multiple sources of data. The extension students can be added to the coursemanagement_enrollment records simply by performing a sql UNION of the Banner and Extension data.

pseudo-sql
SELECT *
   FROM banner_enrollment be
UNION
SELECT *
   FROM unex_enrollment ue

Implementation

The implementation of Open Campus enrollments will have two artifacts:

  • UNEX_ENROLLMENT Table: This table will be a column-for-column copy of the Banner-source mview (with the exception of using mothraid instead of pidm). The table will be filled nightly via the feed procedure.
  • UNEX Package: The UNEX package is a pl/sql package that contains all of the code necessary to update the unex_enrollment table with information from the Extension database. The functions of the package are as follows:

PKG_UNEX

Function

Parameters

Description

cm_unex_feed

none

The primary procedure to run the feed for the current term.

cm_unex_main

term code, crn, external id

Invokes all sub-procedures to add a student to a course.

cm_unex_feed_get_user_info

external id

Looks up mothra information for an external id.

cm_unex_feed_check_course

term code, crn

Checks the course information to ensure it is valid.

cm_unex_feed_add_enrollment

term code, crn, external id

Adds the student to the unex_enrollment table.