Flexible Curriculum User’s Guide<>

Changes to the data model

Overview of changes to the data model

The red links illustrates key changes to the database structure required to support the flexible curriculum functionality.

Impact of the changes

If you decide not to use the flexible curriculum features the data model still ‘looks’ the same to any existing reporting applications or external interfaces. You do not have to modify your current SQL queries; they will still work.

If you decide to use the flexible curriculum features you can roll-out ‘flexible-curriculum aware’ reports and queries at a pace that suits your organisation; all your existing reports and interfaces will continue to function. Be aware, however, that the new cross-sessional flexible courses will NOT appear in your existing reports — only existing single-session courses will appear unless you modify the query logic to include cross-sessional courses.

How are changes made invisible to existing programs?​

Many existing Quercus modules access the tables (named COURSE_INSTANCE, MODULE_INSTANCE and STUDENT_COURSE_DETAIL in pre-Flexible Curriculum versions of Quercus) that have been changed as a result of the Flexible Curriculum changes.

Rather than modify all the programs that access these tables (which would involve the updating of many thousands of lines of code) the tables have been renamed and views created in their place. A view is a re-usable SQL statement which can be referenced in exactly the same way as table. So:

Select COURSE, LOCATION from COURSE_INSTANCE where ACADEMIC_YEAR='2011' and INSTANCE_CODE like '%BIO%'

could be referencing a table named COURSE_INSTANCE OR a view named COURSE_INSTANCE.

We have made use of this feature to create views in Quercus 8.0 which act as replacements for the equivalent tables in previous versions of Quercus. The tables listed above have been renamed (by appending a ‘_TABLE’ suffix) and views with the original names of the tables have been created. These views are subsets of the new, extended, tables and have exactly the same structure as the original tables. This means that any SQL query calling (say) COURSE_INSTANCE will call the COURSE_INSTANCE view rather than table. Assuming that your data had not changed between the upgrades this would return exactly the same result set as the same SQL query run against the COURSE_INSTANCE table in the previous Quercus release.

Note, however, that if you make use of the flexible curriculum features and are running a query which makes use of the new table fields and their associated joins, you MUST reference the new table and not the view.

For example running the queries

Select * from COURSE_INSTANCE where INSTANCE_CODE like '%FLEX%'

and

Select * from COURSE_INSTANCE_TABLE where INSTANCE_CODE like '%FLEX%’

gives different results on the data set shown in the example below:

Select * from COURSE_INSTANCE

Select * from COURSE_INSTANCE_TABLE

This is because the COURSE_INSTANCE view does not include course instances with a SESSIONAL_TYPE indicating a cross-sessional course.

We can see this clearly by examining the statement that generates the COURSE_INSTANCE view. The important line filtering out the cross-sessional course instances is highlighted:

CREATE OR REPLACE FORCE VIEW  "COURSE_INSTANCE" ("OBJECT_ID", "TIMESTAMP", "COURSE", "COURSE_YEAR", "LOCATION", "START_DATE", "END_DATE", "CREDITS", "ACADEMIC_YEAR", "MODE_OF_STUDY", "OPTIONAL_SUBJECT_COUNT", "SEMESTERISED", "COURSE_STREAM", "QUALIFICATION", "INSTANCE_CODE", "QUOTA", "STATUS", "STRUCTURE_LEVELS", "RESULTS_ENTRY_LEVEL", "AWARD") AS 
select
cit.object_id,
cit.timestamp,
cit.course,
cit.course_year,
cit.location,
cit.start_date,
cit.end_date,
cit.credits,
cit.academic_year,
cit.mode_of_study,
cit.optional_subject_count,
cit.semesterised,
cit.course_stream,
cit.qualification,
cit.instance_code,
cit.quota,
cit.status,
cit.structure_levels,
cit.results_entry_level,
cit.award
from
course_instance_table cit,
sessional_type st
where
cit.sessional_type = st.object_id and
st.sessional_type = 'SINGLE-SESSION'
/

In other words the COURSE_INSTANCE (and the MODULE_INSTANCE and STUDENT_COURSE_DETAIL) views are blind to courses and modules that make use of the flexible curriculum features. To query and maintain these records you must reference the new data tables (ending in ‘_TABLE’) NOT the views.

Note also that you can reference a view in standard SQL table manipulation statements such as INSERT and UPDATE.

For example:

INSERT INTO COURSE_INSTANCE VALUES (96378731,'21/02/2011', '16378726', '1', '2660061', '10/01/2010', '10/01/2011', '75', '2010', '2654023','','','','','FLEXY01','','2654694','1','','')

inserts a record in the COURSE_INSTANCE_TABLE table. The ‘missing’ values for SESSIONAL_TYPE, PACKAGED and VAT_RATE are defaulted to single-session, ‘0’ and null respectively.

The rest of this chapter documents in detail the changes to the data model.

Changes to the data tables — overview

The following tables have been changed to support the flexible curriculum features:

COURSE_PROGRAM_DETAIL

COURSE_PROGRAM_UNIT

COURSE_UNIT_DETAIL

COURSE_INSTANCE

MODULE_INSTANCE

STUDENT_COURSE_DETAIL

Changes to the COURSE_PROGRAM_DETAIL table

Before Flexible Curriculum

Flexible Curriculum

Change: Addition of mandatory MODULE_INSTANCE foreign key.

Changes to the COURSE_PROGRAM_UNIT table

Before Flexible Curriculum

Flexible Curriculum

Change: Addition of mandatory MODULE_INSTANCE foreign key.

Changes to the COURSE_UNIT_DETAIL table

Before Flexible Curriculum

Flexible Curriculum

Change: Addition of mandatory MODULE_INSTANCE foreign key.

COURSE_INSTANCE view now a substitute for the old COURSE_INSTANCE table

Before Flexible Curriculum — COURSE_INSTANCE table

Flexible Curriculum — COURSE_INSTANCE view

Changes: SQL operations referencing COURSE_INSTANCE will now be directed to a COURSE_INSTANCE view rather than a COURSE_INSTANCE table. The new COURSE_INSTANCE view and the old COURSE_INSTANCE table are structurally identical (see the comparison, above).

The COURSE_INSTANCE table has been renamed COURSE_INSTANCE_TABLE so that existing code does not not need to be modified. New code and SQL statements designed to exploit flexible curriculum functionality will reference the COURSE_INSTANCE_TABLE table.

The COURSE_INSTANCE view shows only COURSE_INSTANCE_TABLE records where SESSIONAL_TYPE = 'SINGLE-SESSION'.

COURSE_INSTANCE table has been renamed the COURSE_INSTANCE_TABLE table

See note above.

Before Flexible Curriculum (COURSE_INSTANCE table)

Flexible Curriculum (COURSE_INSTANCE_TABLE table)

Changes: SESSIONAL_TYPE, PACKAGED and VAT_RATE columns have been added to support flexible curriculum features.

SESSIONAL_TYPE is a foreign key linking to the SESSIONAL_TYPE table (below). It identifies whether the course is single-session or cross-sessional.

PACKAGED supports future course customisation functionality.

VAT_RATE supports future payment-related functionality.

MODULE_INSTANCE view now a substitute for the old MODULE_INSTANCE table

Before Flexible Curriculum — MODULE_INSTANCE table

Flexible Curriculum — MODULE_INSTANCE view

Changes: SQL operations referencing MODULE_INSTANCE will now be directed to a MODULE_INSTANCE view rather than a MODULE_INSTANCE table. The new MODULE_INSTANCE view and the old MODULE_INSTANCE table are structurally identical (see the comparison, above).

The MODULE_INSTANCE table has been renamed MODULE_INSTANCE_TABLE so that existing code does not not need to be modified. New code and SQL statements designed to exploit flexible curriculum functionality will need to reference the MODULE_INSTANCE_TABLE table.

The MODULE_INSTANCE view shows only MODULE_INSTANCE_TABLE records where DEFAULTS = ‘1’ (i.e. only the default module is retrieved) and SESSIONAL_TYPE = 'SINGLE-SESSION'.

MODULE_INSTANCE table has been renamed the MODULE_INSTANCE_TABLE table

See note above.

Before Flexible Curriculum (MODULE_INSTANCE table)

Flexible Curriculum (MODULE_INSTANCE_TABLE table)

Changes: MODULE_INSTANCE, DEFAULTS, SESSIONAL_TYPE, PACKAGED and VAT_RATE columns have been added to support flexible curriculum features.

MODULE_INSTANCE is a mandatory, column. It stores a unique identifying code for module instance.

The DEFAULTS column indicates whether a record represents the default module instance for an academic session. Values can be ‘0’ or ‘1’ with ‘1’ indicating the default.

SESSIONAL_TYPE is a foreign key linking to the SESSIONAL_TYPE table (below). It identifies whether the course is single-session or cross-sessional.

PACKAGED supports future course customisation functionality.

VAT_RATE supports future payment-related functionality.

MODULE, ACADEMIC_SESSION and STATUS columns in MODULE_INSTANCE are now mandatory (Nullable = ‘No’). This is to improve data quality.

STUDENT_COURSE_DETAIL view substituted for the old STUDENT_COURSE_DETAIL table

Before Flexible Curriculum — STUDENT_COURSE_DETAIL table

Flexible Curriculum — STUDENT_COURSE_DETAIL view

Changes: SQL operations referencing STUDENT_COURSE_DETAIL will now be directed to a STUDENT_COURSE_DETAIL view rather than a STUDENT_COURSE_DETAIL table. The new STUDENT_COURSE_DETAIL view and the old STUDENT_COURSE_DETAIL table are structurally identical (see the comparison, below).

The STUDENT_COURSE_DETAIL table has been renamed STUDENT_COURSE_DETAIL_TABLE so that existing code does not not need to be modified. New code and SQL statements designed to exploit flexible curriculum functionality will need to reference the STUDENT_COURSE_DETAIL_TABLE table.

Note: students who are linked with cross-sessional courses will not appear in the STUDENT_COURSE_DETAIL view (see How are changes made invisible to existing programs?). They will, however, appear in the STUDENT_COURSE_DETAIL_TABLE (see below).

STUDENT_COURSE_DETAIL table renamed STUDENT_COURSE_DETAIL_TABLE table

See note above.

Before Flexible Curriculum (STUDENT_COURSE_DETAIL table)

Flexible Curriculum (STUDENT_COURSE_DETAIL_TABLE table)

Changes: The STATUS column is now mandatory (Nullable = ‘No’). Required to track active students across sessions.

Change to unique index

COURSE_INSTANCE & MODULE_INSTANCE combination replaces COURSE_INSTANCE & MODULE as a unique index.

New system parameters

The following system parameters will be created:

System parameter

Default value

Purpose

CROSS_SESSION_CI_FLAG

N

Determines whether users can create cross-sessional course instances.

CROSS_SESSION_MI_FLAG

N

Determines whether users can create cross-sessional module instances.

New mode of study

A new mode of study of ‘Packaged Course’ will be defined to identify ‘Packaged Courses’.

SESSIONAL_TYPE table

Flexible Curriculum

This is a new static data table with values ‘SINGLE-SESSION’ and ‘CROSS-SESSIONAL’.