Guide for Icodeon SCORM Player Version 2.00 Database
Icodeon Ltd
St Johns Innovation Centre
Cowley Road
Cambridge
Cambs
CB4 0WS
United Kingdom
+44 (0)870 950 6582
<sales@icodeon.com>
February 2008
Table of Contents
List of Tables
- 3.1. Learner Table Data Model Names and Types
- 3.2. Course Table Data Model Names and Types
- 3.3. Organization Table Data Model Names and Types
- 3.4. Activity Table Data Model Names and Types
- 3.5. Activity Progress Information Table Data Model Names and Types
- 3.6. Attempt Progress Information Table Data Model Names and Types
- 3.7. Objective Progress Information Table Data Model Names and Types
Table of Contents
The database schema inherits semantics from the specifications used within the SCORM 2004 specification.
In particular, the table names and field names are based on the following specifications:
IMS Simple Sequencing 1.0
IMS Content Packaging 1.1
Computer Managed Instruction (CMI) Data Model
IEEE 1484.11.3 Content Object Communication Data Model
The full database schema is illustrated below:
Complete Database Schema [click for full size].
A number of the tables and their relationships are required for the internal maitenance of state by the application and are not normally used for creating reports. The section below outlines the important tables normally used for reporting.
Table of Contents
A number of the tables and their relationships are required for the internal maitenance of state by the application and are not normally used for creating reports. This section focusses on some selected tables and thier relationships that are important for generating reports for host applications.
Selected Tables from the Database Schema [click for full size].
The Learner table has fields that relate to the identity of a user of the application.
The host LMS is responsible for setting a unique, mandatory learnerID parameter during the launch request for the Icodeon SCORM Player. The value of the learnerID parameter is inserted into the LEARNER table as the LEARNER_ID field. The host LMS may also optionally add a learnerName parameter, which is inserted into the LEARNER table as the LEARNER_NAME field.
The Course table has fields that relate to the identity of a (SCORM) content package.
The host LMS is responsible for setting a unique, mandatory courseID parameter during the launch request for the Icodeon SCORM Player. The courseID corresponds to a unique identifier for the decompressed SCORM Package Interchange File (PIF), usually distributed as a .zip file. The value of the courseID parameter is inserted into the COURSE table as the COURSE_ID field.
The Organization table has fields that relate to the identity of one group of activities within a(SCORM) content package.
The SCORM Package Interchange File (PIF) will contain an imsmanifest.xml file that will contain one or more organization elements:
<organizations
default="ORG-D6257D46-F4BF-B282-732C-8412E21AC988">
<organization
identifier="ORG-D6257D46-F4BF-B282-732C-8412E21AC988"
structure="hierarchical">
<title>Summer Pictures</title>
...
</organization>
</organizations>
The value of the identifier attribute is inserted into the
ORGANIZATION table as the ORG_ID field. The value of the title child element is inserted
into the ORGANIZATION table as the TITLE field. The organization element is used to build
the root activity in the table of contents in the Player user interface, and one item in the
multiple organizations drop down box:
Organization title and the root activity
| Launch Parameter | Mandatory | Database Field | SCORM Mapping | Data Type |
|---|---|---|---|---|
| PK | bigint(20) | |||
| COURSE_PK | bigint(20) | |||
| orgID | Optional | ORG_ID | Root Activity Identifier | varchar(255) |
| TITLE | Root Activity Title | varchar(255) | ||
| DEFAULT_ORGANIZATION | tinyint(1) | |||
| SCHEMA_VERSION | Version token | varchar(255) |
Table 3.3. Organization Table Data Model Names and Types
The Activity table has fields that relate to the identity of an activity within a(SCORM) content package.
The SCORM Package Interchange File (PIF) will contain an imsmanifest.xml file that will contain one or more item elements as descendents of an organization element:
<organization
identifier="ORG-D6257D46-F4BF-B282-732C-8412E21AC988"
structure="hierarchical">
<title>Summer Pictures</title>
<item
identifier="ITEM-BEBD6D04-5C98-58C8-A967-907D01BF9D08"
isvisible="true"
identifierref="RES-42D808C8-431A-D55E-E941-211BDDE5DF02">
<title>Bridges Sunset</title>
</item>
<item
identifier="ITEM-A69B52E2-C70D-9099-6E63-EDB81BC81E80"
isvisible="true"
identifierref="RES-99863A93-02C8-8996-2FD1-6042124014FD">
<title>Castle Sunset</title>
</item>
...
</organization>
The value of the identifier attribute is inserted into the
ACTIVITY table as the ACTIVITY_ID field. The value of the title child element is inserted
into the ACTIVITY table as the TITLE field. The activity element is used to build the an
activity in the table of contents in the Player user interface. Leaf activities are related
to web pages that are displayed in the Player:
Activity title and an activity
Normally we are only interested in the leaf activities, as these represent the web pages seen by the learner in the Icodeon SCORM Player.
The Activity Progress Information table has fields that relate to visits of a learner to an activity.
Each activity has one set of activity progress information per learner that is a record of all visits of a learner to an activity. The activity progress information record is inserted into the ACTIVITY_PROGRESS_INFO table.
| Database Field | SCORM Mapping | Data Type |
|---|---|---|
| PK | bigint(20) | |
| LEARNER_PK | bigint(20) | |
| ACTIVITY_PK | bigint(20) | |
| START_DATE_TIME | datetime | |
| ACTIVE | Sequencing Activity State Information: Activity Is Active | tinyint(1) |
| SUSPENDED | Sequencing Activity State Information: Activity Is Suspended | tinyint(1) |
| ATTEMPT_COUNT | Sequencing Tracking Model Activity Progress Information: Activity Attempt Count | int(11) |
| PROGRESS_STATUS | Sequencing Tracking Model Activity Progress Information: Activity Progress Status | tinyint(1) |
Table 3.5. Activity Progress Information Table Data Model Names and Types
The attempt progress information table has fields that relate to the "completion" status following a unique visit of a learner to an activity.
Each activity progress information record has one to many sets of attempt progress information that is a record of each visit of a learner to an activity. Each attempt progress information record is inserted into the ATTEMPT_PROGRESS_INFO table.
NOTE Attempt progress information recording is optional. To enable recoding of attempt progress information, the tracking level configuration parameter must be set to 2 (default) or higher:
<context-param>
<param-name>trackingLevel</param-name>
<param-value>2</param-value>
</context-param>
| Database Field | SCORM Mapping | Data Type |
|---|---|---|
| PK | bigint(20) | |
| ACTIVITY_PROGRESS_INFO_PK | bigint(20) | |
| ATTEMPT_NUMBER | int(11) | |
| DATE_TIME | datetime | |
| END_DATE_TIME | datetime | |
| ENDED | tinyint(1) | |
| PROGRESS_STATUS | Sequencing Tracking Model Attempt Progress Information: Attempt Progress Status | tinyint(1) |
| COMPLETION_AMOUNT | Sequencing Tracking Model Attempt Progress Information: Attempt Completion Amount | decimal(19,5) |
| COMPLETION_STATUS | Sequencing Tracking Model Attempt Progress Information: Attempt Completion Status | tinyint(1) |
Table 3.6. Attempt Progress Information Table Data Model Names and Types
The objective progress information table has fields that relate to the "satisfied" status and "score" following a unique visit of a learner to an activity.
Each activity progress information record has one to many sets of objective progress information that is a record of each visit of a learner to an activity. Each objective progress information record is inserted into the OBJECTIVE_PROGRESS_INFO table.
NOTE Objective progress information recording is optional. To enable recoding of objective progress information, the tracking level configuration parameter must be set to 3 or higher:
<context-param>
<param-name>trackingLevel</param-name>
<param-value>3</param-value>
</context-param>
| Database Field | SCORM Mapping | Data Type |
|---|---|---|
| PK | bigint(20) | |
| ACTIVITY_PROGRESS_INFO_PK | bigint(20) | |
| DATE_TIME | datetime | |
| OBJECTIVE_ID | varchar(255) | |
| PRIMARY_OBJECTIVE | tinyint(1) | |
| PROGRESS_STATUS | Sequencing Tracking Model Objective Progress Information: Progress Status | tinyint(1) |
| SATISFIED_STATUS | Sequencing Tracking Model Objective Progress Information: Satisfied Status | tinyint(1) |
| MEASURE_STATUS | Sequencing Tracking Model Objective Progress Information: Measure Status | tinyint(1) |
| NORMALIZED_MEASURE | Sequencing Tracking Model Objective Progress Information: Normalized Measure | decimal(19,2) |
Table 3.7. Objective Progress Information Table Data Model Names and Types
Table of Contents
The SCORM specification has emphasised the individual "content objects" rather than the whole "course". Currently SCORM does not support any API that returns a measure of whole course "completion" status or "score".
Integrators are free to develop any concept of whole "course" status that is meaningful within thier learning application.
However, SCORM 2004 introduced a behaviour called "rollup" where status of individual activities is aggregated and applied to the status of a parent activity. If the aggregation is followed all the way up to the "root" activity, then the status of the "root" is a summary of the status of all descedent activities:
Rollup of status from descendent activities to the root activity
The Icodeon SCORM Player applies this "rollup" behaviour to all packages - SCORM 2004, SCORM 1.2 and even non-SCORM IMS Content Packages. This allows "whole course" report queries based on "rollup" to be constructed irrepective of SCORM version or package type.
In SCORM 2004, ADL SCORM introduced a behaviour called "rollup" where status of individual activities is aggregated and applied to the status of a parent activity.
Rollup behaviour is managed by "rollup rules" that may be added to the XML in the imsmanifest.xml file. If no rollup rules are present in the imsmanifest.xml, the default rules are applied:
If all completed, Then completed
If all (attempted or incomplete), Then incomplete
If all satisfied, Then satisfied
If all (attempted or not satisfied), Then not satisfied
The rollup behaviour is useful because, the status of all activities is rolled up on to the status of the root activity.
To determine the overall, rolled up, completion status, use the attempt progress information of the root activity:
To determine the overall, rolled up, satisfied status, use the objective progress information of the root activity: