CourseManagement Institutional Data Mapping
The objects in the CourseManagement framework will be filled almost entirely with data from institutional systems. This page will outline each CourseManagement Object, the institutional data that will be referenced, and any implementation issues. Additionally, each CourseManagement object requires an EID to uniquely identify rows. The algorithm for generating each eid will be captured here.
EID Structure
Common Fields
All of the CourseManagement Objects share a set of fields in common. These fields seem to primarily be concerned with determining the actor that created or updated the data in the object, as well as when that happened. Luckily, the source systems all record activity dates for rows in source tables. So, we will be generically mapping the following columns thusly:
createdBy: The name of the source system for the majority of columns. (This will almost always be 'BANNER')
createdDate: The activity date from the source system's row.
lastModifiedBy: The name of the source system for the majority of columns. (This will almost always be 'BANNER')
lastModifiedDate: The activity date from the source system's row.
AcademicSession / UCD_CM_ACADEMICSESSION (AcademicSessionCmImpl)
Primary Source - BANNER Academic Terms (stvterm)
Secondary Sources - None
EID - http://ucdavis.edu/course/academicsession/stvterm.stvterm_code
Description - The AcademicSession object closely models the BANNER concept of Academic Terms. This object will largely be a direct mapping from BANNER's STVTERM term information lookup table.
Derived Values - The AcademicSession Description field is filled out with a special hash of the term code value instead of the actual description. The description is instead encoded in the title field. The hash generates a short-form description of the term by taking the year from the term code and combining it with a lookup of the term code against a set of <4 character abbreviations, resulting in values of the type: FQ 2007, SS1 2005, etc...
Mapping Issues - None
CourseSet / BANNER_COURSESET
College Listings
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER |
http://ucdavis.edu/course/courseset/college/stvcoll.stvcoll_code |
title |
BANNER |
stvcoll.stvcoll_desc |
description |
BANNER |
stvcoll.stvcoll_desc |
Category |
|
'COLLEGE' |
Parent |
|
N/A |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
stvcoll.stvcoll_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
stvcoll.stvcoll_activity_date |
CanonicalCourses |
BANNER |
BANNER_CANON_COURSESETS |
CourseOfferings |
|
BANNER_OFFERING_COURSESETS |
Department Listings
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER |
http://ucdavis.edu/course/courseset/department/stvdept.stvdept_code |
title |
BANNER |
stvdept.stvdept_desc |
description |
BANNER |
stvdept.stvdept_desc |
Category |
|
'DEPARTMENT' |
Parent |
|
N/A |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
stvdept.stvdept_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
stvdept.stvdept_activity_date |
CanonicalCourses |
BANNER |
BANNER_CANON_COURSESETS |
CourseOfferings |
|
BANNER_OFFERING_COURSESETS |
Subject Listings
Field |
Datasource |
Reference |
|
---|---|---|---|
eid |
BANNER |
http://ucdavis.edu/course/courseset/subject/stvsubj.stvsubj_code | |
title |
BANNER |
stvsubj.stvsubj_desc |
|
description |
BANNER |
stvsubj.stvsubj_desc |
|
Category |
|
'SUBJECT' |
|
Parent |
|
|
|
createdBy |
|
'BANNER' |
|
createdDate |
BANNER |
stvsubj.stvsubj_activity_date |
|
lastModifiedBy |
|
'BANNER' |
|
lastModifiedDate |
BANNER |
stvsubj.stvsubj_activity_date |
|
CanonicalCourses |
BANNER |
BANNER_CANON_COURSESETS |
|
CourseOfferings |
|
BANNER_OFFERING_COURSESETS |
|
CanonicalCourse / BANNER_CANONICALCOURSE (CanonicalCourseCmImpl)
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER |
http://ucdavis.edu/course/canonical/scbcrse.scbcrse_subj_code/scbcrse.scbcrse_crse_numb |
title |
BANNER |
scbcrse.scbcrse_subj_code scbcrse.scbcrse_crse_numb |
description |
BANNER |
scbcrse.scbcrse_subj_code scbcrse.scbcrse_crse_numb |
courseSets |
BANNER |
BANNER_CANON_COURSESETS |
crossListing |
BANNER |
No usable mapping exists. The scbcrse table records subj_code/crse_numb pairs for cross listings, but in some cases those values have changed over time (ANT 101). |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
scbcrse.scbcrse_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
scbcrse.scbcrse_activity_date |
Canonical Course CourseSets / BANNER_CANON_COURSESETS
Field |
Datasource |
Reference |
---|---|---|
canonicalcourse_eid |
BANNER |
http://ucdavis.edu/course/canonical/scbcrse.scbcrse_subj_code/scbcrse.scbcrse_crse_numb |
courseset_eid |
BANNER |
http://ucdavis.edu/course/courseset/
<college,department,subject> / <scbcrse.scbcrse_coll_code,scbcrse.scbcrse_dept_code,scbcrse.scbcrse_subj_code> |
CourseOffering / COURSEMANAGEMENT_OFFERING (CourseOfferingCmImpl)
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER |
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 |
http://ucdavis.edu/course/academicsession/stvterm.stvterm_code |
canonicalcourse_eid |
BANNER |
http://ucdavis.edu/course/canonical/scbcrse.scbcrse_subj_code/scbcrse.scbcrse_crse_numb |
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 |
http://ucdavis.edu/course/offering/scbcrse.scbcrse_subj_code/scbcrse.scbcrse_crse_numb/stvterm.stvterm_code |
courseset_eid |
BANNER |
http://ucdavis.edu/course/courseset/
<college,department,subject> / <scbcrse.scbcrse_coll_code,scbcrse.scbcrse_dept_code,scbcrse.scbcrse_subj_code> |
Section / COURSEMANAGEMENT_SECTION (SectionCmImpl)
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER |
http://ucdavis.edu/course/section/ssbsect.ssbsect_subj_code/ssbsect.ssbsect_crse_numb/ssbsect.ssbsect_seq_numb/ssbsect.ssbsect_term_code |
title |
BANNER |
ssbsect.ssbsect_subj_code ssbsect.ssbsect_crse_numb - ssbsect.ssbsect_seq_numb ssbsect.ssbsect_crse_title Lookup(ssbsect.ssbsect_term_code to two character code for term and year) |
description |
BANNER |
degree_navigator.description /ssbsect.ssbsect_crse_title |
Category |
BANNER |
ssbsect.ssbsect_schd_code |
courseoffering_eid |
BANNER |
http://ucdavis.edu/course/offering/banner_section.subj_code/banner_section.crse_numb/banner_section.term_code |
enrollmentset_eid |
BANNER |
http://ucdavis.edu/course/enrollmentset/ssbsect.ssbsect_crn/ssbsect.ssbsect_term_code |
MaxSize |
BANNER |
ssbsect.ssbsect_max_enrl |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
ssbsect.ssbsect_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
ssbsect.ssbsect_activity_date |
Meetings |
BANNER |
select banner_schedule.term_code, banner_schedule.crn |
term_code |
BANNER |
ssbsect.ssbsect_term_code |
crn |
BANNER |
ssbsect.ssbsect_crn |
coursecode |
BANNER |
ssbsect.ssbsect_subj_codessbsect.ssbsect_crse_numb |
EnrollmentSet / BANNER_ENROLLMENTSET (EnrollmentSetCmImpl)
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER |
http://ucdavis.edu/course/enrollmentset/ssbsect.ssbsect_crn/ssbsect.ssbsect_term_code |
title |
BANNER |
ssbsect.ssbsect_crse_title |
description |
BANNER |
ssbsect.ssbsect_crse_title |
courseoffering_eid |
BANNER |
http://ucdavis.edu/course/offering/ssbsect.ssbsect_subj_code/ssbsect.ssbsect_crse_numb/ssbsect.ssbsect_term_code |
DefaultEnrollmentCredits |
ssbsect.ssbsectr_credit_hrs |
|
Category |
|
'Lecture' |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
ssbsect.ssbsect_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
ssbsect.ssbsect_activity_date |
OfficialInstructors |
BANNER |
select user_id from coursemanagement_instructors where enrollmentset = <eid> |
term_code |
BANNER |
ssbsect.ssbsect_term_code |
crn |
BANNER |
ssbsect.ssbsect_crn |
OfficialInstructors / COURSEMANAGEMENT_INSTRUCTORS
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER/MOTHRA |
http://ucdavis.edu/course/enrollmentset/sirasgn.sirasgn_crn/sirasgn.sirasgn_term_code/instructor/sirasgn.sirasgn.pidm |
enrollment_set_id |
BANNER |
http://ucdavis.edu/course/enrollmentset/sirasgn.sirasgn_crn/sirasgn.sirasgn_term_code |
section_id |
BANNER |
http://ucdavis.edu/course/section/ssbsect.ssbsect_subj_code/ssbsect.ssbsect_crse_numb/ssbsect.ssbsect_seq_numb/ssbsect.ssbsect_term_code |
person_eid |
MOTHRA |
mothra_person.mothraid (sirasgn.sirasgn_pidm) |
Enrollment / COURSEMANAGEMENT_ENROLLMENT (EnrollmentCmImpl)
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER |
http://ucdavis.edu/course/enrollmentset/sfrstcr.sfrstcr_crn/sfrstcr.sfrstcr_term_code/sfrstcr.sfrstcr_rsts_code/mothra_person.mothraid |
enrollmentset_eid |
BANNER |
http://ucdavis.edu/course/enrollmentset/sfrstcr.sfrstcr_crn/sfrstcr.sfrstcr_term_code |
person_eid |
BANNER |
mothra_person.mothraid (sfrstcr.sfrstcr_pidm) |
EnrollmentStatus |
BANNER |
sfrstcr.sfrstcr_rsts_code |
Dropped |
BANNER |
case when sfrstcr.sfrstcr_rsts_code IN ('RE','WL') then 0 else 1 end |
Credits |
BANNER |
sfrstcr.sfrstcr_credit_hr |
GradingScheme |
BANNER |
sfrstcr.sfrstcr_gmod_code |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
sfrstcr.sfrstcr_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
sfrstcr.sfrstcr_activity_date |
Meeting (MeetingCmImpl)
Field |
Datasource |
Reference |
---|---|---|
eid |
BANNER |
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 |
section_eid |
BANNER |
http://ucdavis.edu/course/section/ssbsect.ssbsect_subj_code/ssbsect.ssbsect_crse_numb/ssbsect.ssbsect_seq_numb/ssbsect.ssbsect_term_code |
StartTime |
BANNER |
ssrmeet.ssrmeet_begin_time |
EndTime |
BANNER |
ssrmeet.ssrmeet_end_time |
Location |
BANNER |
ssrmeet.ssrmeet_bldg_code ssrmeet.ssrmeet_room_code |
Sunday |
BANNER |
ssrmeet.ssrmeet_sun_day |
Monday |
BANNER |
ssrmeet.ssrmeet_mon_day |
Tuesday |
BANNER |
ssrmeet.ssrmeet_tue_day |
Wendesday |
BANNER |
ssrmeet.ssrmeet_wed_day |
Thursday |
BANNER |
ssrmeet.ssrmeet_thu_day |
Friday |
BANNER |
ssrmeet.ssrmeet_fir_day |
Saturday |
BANNER |
ssrmeet.ssrmeet_sat_day |
Notes |
|
|
createdBy |
|
'BANNER' |
createdDate |
BANNER |
ssrmeet.ssrmeet_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
ssrmeet.ssrmeet_activity_date |
Section Category (BANNER_SECTION_CATEGORY)
Field |
Datasource |
Reference |
---|---|---|
code |
BANNER |
stvschd.stvschd_code |
label |
BANNER |
stvschd.stvschd_desc |
description |
BANNER |
stvschd.stvschd_desc_2 |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
stvschd.stvschd_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
stvschd.stvschd_activity_date |
Grading Scheme (BANNER_GRADING_SCHEME)
Field |
Datasource |
Reference |
---|---|---|
code |
BANNER |
stvgmod.stvgmod_code |
description |
BANNER |
stvgmod.stvgmod_desc |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
stvgmod.stvgmod_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
stvgmod.stvgmod_activity_date |
Enrollment Status (BANNER_ENROLLMENTSTATUS)
Field |
Datasource |
Reference |
---|---|---|
code |
BANNER |
stvrsts.stvrsts_code |
description |
BANNER |
stvrsts.stvrsts_desc |
createdBy |
|
'BANNER' |
createdDate |
BANNER |
stvrsts.stvrsts_activity_date |
lastModifiedBy |
|
'BANNER' |
lastModifiedDate |
BANNER |
stvrsts.stvrsts_activity_date |