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 from banner_schedule where banner_schedule.term_code = <eid,2> and banner_schedule.crn = <eid,3>
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