SED BANNER
- devtools@ucdavis.edu
- Anonymous
- Brian Donnelly
Sakai External Database BANNER Data
The BANNER Student Information System is an Oracle Forms Application that has been in existence for over 15 years. It is the system of record for course, instructor, and student information. Therefore, it represents most of the external information required by Sakai for the purposes of provisioning and maintaining course sites.
BANNER Data Architecture
The BANNER system consists of thousands of tables, views, sequences, and pl/sql procedures. Luckily, only a handful of those objects are of interest to the Sakai project. The following diagram and table will describe each of the objects Sakai will be collecting data from.
Name |
Object Type |
Description |
Example |
|---|---|---|---|
STVTERM |
Table |
Academic Term Infomation |
Spring Quarter 2007 |
STVCOLL |
Table |
Academic Colleges |
AE - Agricultural and Environmental Sciences |
STVDEPT |
Table |
Academic Departments |
ENH - Environmental Horticulture |
STVSUBJ |
Table |
Academic Subjects |
LDA - Landscape Architecture |
SCBCRSE |
Table |
Course Information |
LDA 001 - Spring Quarter 2007 |
SSBSECT |
Table |
Section Information |
LDA 001 - Section A01 - Spring Quarter 2007 |
SSRMEET |
Table |
Section Meeting Times |
MWF 9:00-10:15 - LDA 001 - Section A01 - Spring Quarter 2007 |
SIRASGN |
Table |
Instructor Assignments |
John Smith - LDA 001 - Section A01 - Spring Quarter 2007 |
SFRSTCR |
Table |
Student Enrollments |
Jane Doe - Waitlisted - LDA 001 - Section A01 - Spring Quarter 2007 |
Requirements
- Availability: The BANNER data used by Sakai must be available on a 24x7 basis.
- Fast Refresh: Updated Student Registration information must be available as soon as possible. In general, the other BANNER data sources do not need to be updated as frequently.
- Flexibility: The resultant data store must be felxible enough to adapt to changes in Sakai's data requirements, as well as to include and augment data with data from external sources.
Design
BANNER, while a core campus system, is not yet a 24x7 application (24x7 database availablility is expected in Summer 2006). Therefore, in order to avoid periods of downtime due to BANNER database maintenance, Materialized Views have been chosen as the method to pull data from BANNER to the External Database.
Oracle Materialized Views work similarly to normal views, but rather than storing the query results in memory, the query results are stored in the local oracle database. Subsequent queries using the view then use the local data rather than accessing the view source every time. Materialized Views include refresh information indicating when and how the information in the view is updated from the source system.
In the ideal design, BANNER data will be extracted in a normalized format similar to the structure of course information in Sakai. Additionally, data in the Materialized Views will be updated automatically when data is changed on the source side, resulting in near-realtime data refresh.
Implementation
For the 2.4.x Course Management implementation of Sakai, the Materialized Views have been re-designed to closer meet the original design goals. Each MView maps closely to a single object in the Course Management schema, and converts most ids to a Course Management compatible format during the refresh process. However, this approach required sacrificing the fast-refresh capability of most of the materialized views. Fast refresh has been preserved on a few tables that contain time sensitive data such as enrollments and instructor assignments. The documentation for the Course Management External Database implementation is located at Course Management Object Relational Mapping.