...
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 /
...
UCD_CM_SECTION (SectionCmImpl)
Primary Source(s) - BANNER Section table (ssbsect)
Secondary Source(s) - Degree Navigator course descriptions (ucd_cm_dn_description_t)
EID(s) - http://ucdavis.edu/course/section/ssbsect.ssbsect_subj_code/ssbsect.ssbsect_crse_numb/ssbsect.ssbsect_seq_numb/ssbsect.ssbsect_term_code
Description - The Section object in the Sakai CourseManagement framework records an instance of a course that is tied to a number of instance objects, such as instructor, meeting times, and academic session.
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.
EnrollmentSet /
...
UCD_CM_ENROLLMENTSET (EnrollmentSetCmImpl)
Primary Source(s) - BANNER Section table (ssbsect)
Secondary Source(s) - None
EID(s) - http://ucdavis.edu/course/enrollmentset/ssbsect.ssbsect_crn/ssbsect.ssbsect_term_code
Description - The EnrollmentSet object is a peer of the Section object. It is used to collect student and instructor associations with Courses/Section.
Derived Values - In the event of blank description or title date, '(Description Unavailable)' is rendered instead.
Mapping Issues - We are currently unaware of the use of the Category field, and have set this value to the literal 'Lecture'.
OfficialInstructors /
...
UCD_CM_INSTRUCTORS
Primary Source(s) - BANNER Instructor Assignment table (sirasgn), BANNER Section table (ssbsect)
Secondary Source(s) - Mothra Person Table (ucd_person_t)
EID(s) - http://ucdavis.edu/course/enrollmentset/sirasgn.sirasgn_crn/sirasgn.sirasgn_term_code/instructor/sirasgn.sirasgn.pidm
Description - The OfficialInstructors object is a simple mapping of person eids to enrollmentsets and sections for the purposes of denoting instruction assignment.
Derived Values - None
Mapping Issues - In order to reproduce the section eid, the ssbsect table must be joined on the mview query. (The unique columns for the section_eid are not stored in the sirasgn table. This table is eligible for mview fast refresh under the simple join condition. This is effected by capturing the rowids of the two source tables.
This object also needs to capture a person eid, which we have identified as the Mothraid of the person. The Banner source data uses pidm to uniquely identify users in its system. Therefore, a lookup from pidm to mothraid must be performed. This is currently done via a view, but other methods are under investigation.
Data Issues: Ideally, the Banner data would only record one instance of an instructor assigned to one course. However, there are isolated cases in which the same instructor is assigned to a section multiple times. Normally, a distinct clause would be able to eliminate the extra assignments, but in this case that would break the fast-refresh of this table. Since we want up to date instructor assignments, we will need to find another way of weeding out the extra assignments.
Enrollment /
...
UCD_CM_ENROLLMENT (EnrollmentCmImpl)
Primary Source(s) - BANNER Student Registration table (sftstcr)
Secondary Source(s) - Mothra Person Table (ucd_person_t)
EID(s) - http://ucdavis.edu/course/enrollmentset/sfrstcr.sfrstcr_crn/sfrstcr.sfrstcr_term_code/sfrstcr.sfrstcr_rsts_code/mothra_person.mothraid
Description - The Enrollment object records all of the data required to notate an student registration to an enrollmentset / section.
Derived Values - The dropped column is derived by classifying all registration codes other than 'RE' and 'WL' as drops.
Mapping Issues - This object needs to capture a person eid, which we have identified as the Mothraid of the person. The Banner source data uses pidm to uniquely identify users in its system. Therefore, a lookup from pidm to mothraid must be performed. This is currently done via a view, but other methods are under investigation.
Data Issues: Ideally, the Banner data would only record one instance of an student assigned to one course. However, there are cases in which the same student is registered to a section multiple times. As opposed to the Instructor assignment case, it is possible to differentiate the enrollments by adding the registration status into the eid. While a student may be registered multiple times, it is always with a different registration code.
Meeting / UCD_CM_MEETING (MeetingCmImpl)
Primary Source(s) - BANNER Section Meeting table, (ssrmeet), BANNER Section table (ssbsect)
Secondary Source(s) - Mothra Person Table (ucd_person_t)
EID(s) - http://ucdavis.edu/course/section/meeting/SSRMEET_TERM_CODE/SSRMEET_CRN/SSRMEET_BLDG_CODE/ SSRMEET_ROOM_CODE/SSRMEET_BEGIN_TIME/SSRMEET_END_TIME/SSRMEET_CATAGORY/SSRMEET_MON_DAY/SSRMEET_TUE_DAY/ SSRMEET_WED_DAY/SSRMEET_THU_DAY/SSRMEET_FRI_DAY/SSRMEET_SAT_DAY/SSRMEET_SUN_DAY
Description - The Enrollment object records all of the data required to notate an student registration to an enrollmentset / section.
Derived Values - The begin and end times are recorded as strings and must be converted to an Oracle timestamp in order for Hibernate to use them.
Mapping Issues - In order to reproduce the section eid, the ssbsect table must be joined on the mview query. (The unique columns for the section_eid are not stored in the ssrmeet table.)
Data Issues: This table is a case study in dirty data, lack of primary keys, and bad enforcement of business rules. In short, almost every column in the table is required in order to resolve a unique eid.
Section Category
...
/ UCD_
...
CM_
...
SECTIONCATEGORY
Primary Source(s) - BANNER Schedule Table (stvschd)
Secondary Source(s) - none
EID(s) - None
Description - The section category mview is used to look up category codes from the section mview for their descriptive information.
Derived Values - None
Mapping Issues - None
Grading Scheme
...
/ UCD_
...
CM_
...
GRADINGSCHEME
Primary Source(s) - BANNER Grade Mode Table (stvgmod)
Secondary Source(s) - None
EID(s) - None
Description - The grading scheme mview is used to look up grade mode codes from the enrollment mview for their descriptive information.
Derived Values - None
Mapping Issues - None
Enrollment Status
...
/ UCD_CM_ENROLLMENTSTATUS
...
Primary Source(s) - BANNER Registration Status Table (stvrsts)
Secondary Source(s) - None
EID(s) - None
Description - The enrollment status mview is used to look up registration codes from the enrollment mview for their descriptive information.
Derived Values - None
Mapping Issues - None