Sakai External Database Degree Navigator Data

Degree Navigator is an application that allows students to track and plan their path toward degree achievement within the University. It also has the property of being populated with up-to-date course description information from the CAF (Course Approval Form) application. Access to CAF is not possible, so the next best source for course description data is Degree Navigator.

Degree Navigator Data Architecture

The Degree Navigator system stores all of its course description data in a single table called COURSEDESCRIPTION_DAG.

Data Inconsistencies

The Degree Navigator data is plagued by an inconsistency of the start and end term data. Specifically, there are some cases in which there are multiple coursecodes for which the endterm_dag field is null. Functionally, this makes it harder to distinguish which is the active course description. Our code will perform a max on the starttterm field combined with the check for a null endterm field, this will have the effect of isolating out the newest course description.

Requirements

• Availability: The Degree Navigator data used by Sakai must be available on a 24x7 basis.
• Refresh: While course descriptions are rarely updated in Degree Navigator, Sakai will need to have any updates as soon as they are available. In practice, a nightly feed will be sufficient.

Design

Degree Navigator is not yet a 24x7 application. 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.

Implementation

The Degree Navigator pl/sql procedure and table have been copied following the model used by MyUCDavis CourseManagement. The feed procedure performs a simple insert of all records from degree navigator and then deletes all records created before the last insert.

create or replace PROCEDURE                DN4_EXTRACT
IS
BEGIN
declare
    v_timestamp_current date;

begin
  select sysdate into v_timestamp_current from dual;

  begin
    insert into sakai_external.degree_nav_desc(
            coursecode,
            description,
            start_term,
            end_term,
            subj_code,
            crse_numb,
            activity_date)
    select  coursecode_dag,
            description_dag,
            startterm_dag,
            endterm_dag,
            substr(coursecode_dag,1,3),
            substr(coursecode_dag,4,6),
            v_timestamp_current
    from navigate.coursedescription_dag@DN4.UCDAVIS.EDU;
  end;

  commit;

  begin
    delete from degree_nav_desc where activity_date <> v_timestamp_current;
  end;

  commit;
end;

END; -- Procedure DN4_EXTRACT