Guide for Icodeon SCORM Player Version 2 Database
Icodeon Ltd
St Johns Innovation Centre
Cowley Road
Cambridge
Cambs
CB4 0WS
United Kingdom
+44 (0)870 950 6582
<sales@icodeon.com>
Guide for Icodeon SCORM Player Version 2 Database (.NET Edition)
February 2008
Table of Contents
List of Tables
- 1.1. Dtaa Model Mapping
- 2.1. Tables for Icodeon Player Database Schema
- 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
This guide aims to help system integrators leverage the tracking information persisted by the Icodeon SCORM Player.
In SCORM 1.2 there was a single data model (the SCORM Run Time Environment Data Model) that was inherited by SCORM 2004. The SCORM Run-Time Environment Data Model is based on the 1484.11.1 Standard for Learning Technology - Data Model for Content Object Communication standard produced by the IEEE LTSC CMI. 1484.11.1 is a standard that defines a set of data model elements that can be used to communicate information from a content object (i.e. SCOs in SCORM) to an LMS.
The SCORM Run-Time Environment Data Model suffers from a major limitation: that only individual communicative content objects (i.e. SCOs in SCORM) have state. Other entities, such as non-communicative content objects (i.e. ASSETs in SCORM) or collections of content objects (such as an entire "course") have no state associated with them.
The lack of a state model for other entities (assets, collections of content objects, whole courses etc) was addressed in SCORM 2004 with the addition of the Sequencing Tracking Model.
A state model for other entities (assets, collections of content objects, whole courses etc) was added in SCORM 2004 as the Sequencing Tracking Model. This Sequencing Tracking Model added:
Default values for non-communicative content objects (i.e. ASSETs in SCORM)
Rollup (aggregation) of the state of individual content objects to give the overall state of collections of content objects, whole courses etc.
Mapping of SCORM Run-Time Environment Data Model element values to Sequencing Tracking Model
The tracking model defines the following sets of tracking status information:
Activity Progress Information: Describes a learner's progress on a content object. This information describes the cumulative learner progress across all attempts on a content objects.
Attempt Progress Information: Describes a learner's progress on an a content object and is broadly equivalent to the "completion" status of a content object or a group of content objects.
Objective Progress Information: Describes the learner's progress related to a learning objective and is broadly equivalent to the "score" or "satisfied" status of a content object or a group of content objects.
The Icodeon SCORM Player implements the Sequencing Tracking Model for all content packages (SCORM 2004, SCORM 1.2 and IMS Content Packages) so that a consistent set of data is available for reporting, irrespective of the content package type or version. An overview of the mapping of SCORM Run-Time Environment Data Model elements to Sequencing Tracking Model is given below:
| Comment | Run-Time Environment Data Model | Sequencing Tracking Model |
|---|---|---|
| Indicatation that the learner has completed the content object | cmi.completion_status | Attempt Progress Information: Completion Status |
| Indicatation that the learner has mastered the content object | cmi.success_status | Objective Progress Information: Primary Objective Satisfied Status |
| Indication of the performance of the learner. The value is scaled to fit the range -1.0 to 1.0 inclusive | cmi.score.scaled | Objective Progress Information: Primary Objective Normalized Measure |
Table 1.1. Dtaa Model Mapping
For more detail on these mappings, please see the SCORM 2004 Run Time Environment book.
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].
The following table provides a short description of each database table:
| Table Name | Comment | Reference |
|---|---|---|
| LEARNER | Fields include an identifier (LEARNER_ID) and label (LEARNER_NAME) for the learner on behalf of whom the SCO instance was launched. The learnerID and learnerName parameter values provided by the host LMS will be used for these fields and within corresponding fields in the SCORM run time environment data model. | See the SCORM Run Time Environment Book. The SCORM run time environment data model includes elements for cmi.learner_id and cmi.learner_name. |
| COURSE | Fields include an identifier (COURSE_ID) that can be used to uniquely identify the directory containing the imsmanifest.xml for the SCORM package. The courseID parameter value provided by the host LMS will be used for this field. How the courseID is resolved to the network location of the directory containing the imsmanifest.xml is implementation specific. | Implementation specific. The default implementation is for the courseID to be the same as the the directory name containing the imsmanifest.xml for the SCORM package. |
| ORGANIZATION | Fields include an identifier (ORG_ID) that can be used to uniquely identify an organization within the imsmanifest.xml for the SCORM package. The orgID parameter value provided by the host LMS will be used for this field. | See the IMS Content Packaging specification. The specification includes scope for a default organization and multiple organizations within an imsmanifest.xml file. |
| ACTIVITY_TREE | Fields include a large object field (XML) for an XML representation of the hierarchical relationship of a learning experience. A Content Structure diagram is a common tool used by the instructional design community to describe the hierarchical relationship of a learning experience. IMS Simple Sequencing specification defines and utilizes a similar concept called an Activity Tree to describe a structure of learning activities. The Activity Tree is derived from an organization within an imsmanifest.xml, where the organization element is the root of the Activity Tree and each of its item elements correspond to a learning activity | See the IMS Simple Sequencing Specification and the SCORM Sequencing and Navigation Book. |
| ACTIVITY | Fields include ACTVIVTY_ID and TITLE that are derived from the identifier attribute and title element of an item element within an organization from an imsmanifest.xml file. | See the IMS Content Packaging Specification and the SCORM Sequencing and Navigation Book. |
| COCD | Fields include a large object field (XML) for an XML representation of the SCORM run time environment data model committed by the SCO. Other fields (such as CMI_SUCCESS_STATUS, CMI_TOTAL_TIME) include those most commonly used individual elements from the SCORM run time environment data model. There is one COCD record per activity per learner. | See the IEEE 1484.11.3 Schema for Content Object Communication Data Model (COCD) for the XML binding of SCORM run time environment data model. |
| ACTIVITY_PROGRESS_INFO | Fields include ATTEMPT_COUNT for a cummulative summary of an attempts on aparticular activity by a particular learner. There is one activity progress record per activity per learner. Activity progress information is used by SCORM sequencing during the evaluation of sequencing rules and linit conditions. | See the IMS Simple Sequencing Specification and the SCORM Sequencing and Navigation Book. |
| ATTEMPT_PROGRESS_INFO | Fields include START_DATE_TIME and END_DATE_TIME for a per session record of a unique attempt on a particular activity by a particular learner. There can br one to many attempt progress records per activity per learner. Attempt progress information is used by SCORM sequencing during the evaluation of sequencing rules and linit conditions. | See the IMS Simple Sequencing Specification and the SCORM Sequencing and Navigation Book. |
| OBJECTIVE_PROGRESS_INFO | Fields include SATISFIED_STAUS (yes/no) and NORMALIZED_MEASURE (decimal score) for a per session record of a unique attempt on a particular objective of an activity by a particular learner. There can be one to many objective progress records per activity per learner. Objective progress information is used by SCORM sequencing during the evaluation of sequencing rules and linit conditions. | See the IMS Simple Sequencing Specification and the SCORM Sequencing and Navigation Book. |
| GLOBAL_OBJECTIVE | Fields include GLOBAL_OBJECTIVE_ID to uniquely identify a global objective. If the global objective is narrowly scoped to a particular organization (GLOBAL_TO_SYSTEM is false), the record is related to the organization. | See the IMS Simple Sequencing Specification and the SCORM Sequencing and Navigation Book. |
| GLOBAL_OBJECTIVE_PROGRESS_INFO | Fields include SATISFIED_STAUS (yes/no) and NORMALIZED_MEASURE (decimal score) for a record of a particular global objective state of by a particular learner. Global objective progress information is used by SCORM sequencing during the evaluation of sequencing rules and linit conditions. | See the IMS Simple Sequencing Specification and the SCORM Sequencing and Navigation Book. |
Table 2.1. Tables for Icodeon Player Database Schema
A number of the tables and their relationships are required for the internal maintenance 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" overall completion status or "whole course" overall score.
Integrators are free to develop any concept of "whole course" status that is meaningful within their learning application.
However, SCORM 2004 introduced a behaviour called "rollup" where the 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 snapshot aggregation 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. The root activity can be determined in an SQL query because a activity will have the same identifier (ACTIVITY_ID) as the organization identifier (ORG_ID)within a content package:
...
WHERE (icn_course.COURSE_ID ='Reload Test Content Package'
AND icn_organization.ORG_ID = icn_activity.ACTIVITY_ID
AND icn_learner.LEARNER_ID ='alphanumeric255')
...
To determine the overall, rolled up, satisfied status, use the objective progress information of the root activity: