Course Management Object Relational Mapping
- devtools@ucdavis.edu
- Anonymous
- Jon Gorrono (Unlicensed)
- Brian Donnelly
Course Management Object Relational Mapping
The Smartsite Course Management Object Relational Mapping effort will attempt to reconcile the new Sakai Course Management framework with the data present in our institional stores of course and person data.
Sakai Course Management Framework
The Sakai Course Management framework attempts to create a generic model from which any university may specify a model of their curriculumn of courses and attendant entities. The Course Management API documentation has a number of diagrams and definitions that would be useful to review: http://bugs.sakaiproject.org/confluence/display/CM/Course+Management+API
UC Davis Course Management Mapping
The mapping of UC Davis specific course data to the Sakai Course Management framework is as follows:
Course Management Object -> Database Map
Course Management Object |
DB Object |
DB Object Type |
DB Refresh Frequency |
Proposed Value |
Refresh Time |
|---|---|---|---|---|---|
AcademicSession |
BANNER_ACADEMICSESSION |
Materialized View |
24 Hours |
 |
4:00am |
CourseSet |
BANNER_COURSESET |
Materialized View |
30 Days |
 |
4:15am |
CanonicalCourse |
BANNER_CANONICALCOURSE |
Materialized View |
30 Days |
 |
4:15am + 24 Hours |
CanonicalCourse/CourseSet Mapping |
BANNER_CANON_COURSESETS |
Materialized View |
30 Days |
 |
4:15am + 48 Hours |
CourseOffering |
COURSEMANAGEMENT_OFFERING (BANNER_COURSEOFFERING) |
View |
7 Days |
5 Hours* |
4:30am |
CourseOffering/CourseSet Mapping |
BANNER_OFFERING_COURSESETS |
Materialized View |
7 Days |
5 Hours* |
4:30am + 24 Hours |
Section |
COURSEMANAGEMENT_SECTION (BANNER_CM_SECTION) |
View |
30 Minutes |
 |
Immediate |
EnrollmentSet |
BANNER_ENROLLMENTSET |
Materialized View |
30 Minutes |
 |
+5 Minutes |
Instructor List |
COURSEMANAGEMENT_INSTRUCTORS (BANNER_INSTRUCTORS) |
View |
10 Minutes |
 |
+10 Minutes |
Enrollment |
COURSEMANAGEMENT_ENROLLMENT (BANNER_ENROLLMENT) |
View |
10 Minutes |
 |
+15 Minutes |
Meeting |
BANNER_MEETING |
Materialized View |
24 Hours |
 |
4:30am + 48 Hours |
SectionCategory |
BANNER_SECTION_CATEGORY |
Materialized View |
24 Hours |
 |
4:45am |
GradingScheme |
BANNER_GRADING_SCHEME |
Materialized View |
24 Hours |
 |
4:45am + 48 Hours |
EnrollmentStatus |
BANNER_ENROLLMENTSTATUS |
Materialized View |
24 Hours |
 |
4:45am + 24 Hours |
* Weekend of 12/20/07
Design and Development
- CourseManagement Institutional Data Mapping - Each CourseManagement Object and the institutional data that will be mapped to it.
- DDL Structure and Design - Structural documentation on the DDL code artifacts.
Testing
- Object Query Tests
- Results:
Out of 35 distinct queries, all but 4 returned in under 0.09 seconds (90 milliseconds).
Of the 4 remaining queries, most returned in under a second with the exception of two code lookup queries that returned in 1.7 seconds. Those queries cannot be optimized any further in the database. - Integration test results
Under one hibernate transaction on hanley, all queries (with some duplication: 313 in all) queries run in about 10 seconds.
- Results:
- API Query Tests
- Results:
Out of 45 distinct queries, all but 7 returned in under 0.09 seconds (90 milliseconds).
Of the 7 remaining queries, most returned in under a second with the exception of two code lookup queries that returned in 1.7 seconds. Those queries cannot be optimized any further in the database.
- Results:
