...
Primary Source(s) - BANNER Course table (scbcrse)
Secondary Source(s) - Degree Navigator course descriptions (ucd_cm_dn_description_t)None
EID(s) - http://ucdavis.edu/course/canonical/scbcrse.scbcrse_subj_code/scbcrse.scbcrse_crse_numb
Description - The CanonicalCourse object in the Sakai CourseManagement framework records a non-temporal definition of a course. Unfortunately, BANNER has no such concept of a course throughout time, at least through its tables. While we were able to create CanonicalCourse objects, their usefulness is limited as they do not have descriptions and may actually be different courses through time.
Derived Values - The title and description fields take the value of the concatenation of the subject code and course number.
Mapping Issues - The scbcrse table, which has the closest correlation to this object, is referenced by academic term. Therefore the generating query needs to use a distinct clause to compress the data and remove the temporally-specific data. Given that the data is pulled via a materialized view, this form of transformation is not especially problematic, although it does render the resuting mview incapable of fast refresh. In addition, the create and last update times use max and min of the banner activity dates respectively.
Canonical Course CourseSets /
...
UCD_CM_
...
CANONICAL_COURSESETS
Primary Source(s) - BANNER Course table (scbcrse)
Secondary Source(s) - None
EID(s) - None, lookup between canonicalcourse and courseset EIDs
Description - This object relates CourseSets to Canonical Courses. The BANNER scbcrse table has columns for college, department, and subject, so this table takes those values, expands them to eids and creates one row for each object type mapping to one canonicalcourse EID.
Derived Values - TNoneNone
Mapping Issues - The subject and department data present several challenges in this mapping. As mentioned in the CourseSet description, there are a large number of subject codes and departments that are not in general use. This is especially the case for the location-specific subjects, which have 4 characters rather than three. The inclusion of the extra codes was confusing the Sakai interface, so the decision was made to supress the four character subject and department codes. However, there are actually some classes taught in the four-character subjects, and we cannot afford to lost those classes. So, the solution is to drop the fourth character of the subject for any classes in those subjects. This has the effect of adding the course to the three character subject. In general this is fine although there is a risk that two courses may overlap, in which case the only way to differentiate would be by instructor or description.
CourseOffering / COURSEMANAGEMENT_OFFERING (CourseOfferingCmImpl)
...
Field
...
Datasource
...
Reference
...
eid
...
BANNER
...
Primary Source(s) - BANNER Course table (scbcrse), Banner Section table (ssbsect)
Secondary Source(s) - Degree Navigator course descriptions (ucd_cm_dn_description_t)
EID(s) - http://ucdavis.edu/course/offering/scbcrse.scbcrse_subj_code/scbcrse.scbcrse_crse_numb/stvterm.stvterm_code
...
title
...
BANNER
...
scbcrse.scbcrse_subj_code scbcrse.scbcrse_crse_numb : scbcrse.scbcrse_title
...
status
...
...
'active'
...
description
...
BANNER
...
degree_navigator.description / scbcrse.scbcrse_title
...
academicsession_eid
...
BANNER
...
canonicalcourse_eid
...
BANNER
...
StartDate
...
BANNER
...
stvterm.stvterm_start_date
...
EndDate
...
BANNER
...
stvterm.stvterm_end_date
...
createdBy
...
...
'BANNER'
...
createdDate
...
BANNER
...
scbcrse.scbcrse_activity_date
...
lastModifiedBy
...
...
'BANNER'
...
lastModifiedDate
...
BANNER
...
scbcrse.scbcrse_activity_date
...
CourseSets
...
BANNER
...
BANNER_OFFERING_COURSESETS
Course Offering CourseSets / BANNER_OFFERING_COURSESETS
...
Field
...
Datasource
...
Reference
...
courseoffering_eid
...
BANNER
...
courseset_eid
...
BANNER
http://ucdavis.edu/course/courseset/
...
Description - The CourseOffering object in the Sakai CourseManagement framework records a temporal definition of a course. While the scbcrse table does indeed record temporal data about courses, it only records changes to courses over time. Therefore, in order to expand that data to record a row per-term this data is joined with BANNER's section data.
Derived Values - In the event of blank description or title date, '(Description Unavailable)' is rendered instead.
Mapping Issues - This object needs to retrieve its description fields from the ucd_cm_dn_description table which is fed from the Degree Navigator system. This system also stores only changes to data, and so needs to be joined in specially to ensure the appropriate course description is returned. Unfortunately, the Degree Navigator data is inconsistent in its use of start and end terms, and so must be joined specially on max date to ensure duplicate records are not generated.
In addition, the resulting query logic from the multiple subqueries eliminates the fast-refresh capability of this Materialized View.
Course Offering CourseSets / UCD_CM_OFFERING_COURSESETS
Primary Source(s) - BANNER Course table (scbcrse)
Secondary Source(s) - None
EID(s) - None, lookup between courseoffering and courseset EIDs
Description - This object relates CourseSets to CourseOffering. The BANNER scbcrse table has columns for college, department, and subject, so this table takes those values, expands them to eids and creates one row for each object type mapping to one canonicalcourse EID.
Derived Values - None
Mapping Issues - The subject and department data present several challenges in this mapping. As mentioned in the CourseSet description, there are a large number of subject codes and departments that are not in general use. This is especially the case for the location-specific subjects, which have 4 characters rather than three. The inclusion of the extra codes was confusing the Sakai interface, so the decision was made to supress the four character subject and department codes. However, there are actually some classes taught in the four-character subjects, and we cannot afford to lost those classes. So, the solution is to drop the fourth character of the subject for any classes in those subjects. This has the effect of adding the course to the three character subject. In general this is fine although there is a risk that two courses may overlap, in which case the only way to differentiate would be by instructor or description.
Section / COURSEMANAGEMENT_SECTION (SectionCmImpl)
...