The Learning Standard - Course Objective star schema provides information about learning standards, course objective, and alignments.
ODS_CONTENT_ALIGNMENT_FACT contains a row for each alignment of a learning standard or course objective to any content item.
|sog_pk1||Identifies the clp_sog record, by sog_pk1 join with ods_learning_standard.sog_pk1 to get all of attributes of one standard.|
|course_content_pk1||Identifies the content within a learning system course, by course_content_pk1, join with ods_course_content to get the detail information of one content aligned.|
|content_alignment_pk1||Not used. It is present to facilitate delta processing.|
ODS_COURSE_SOG_FACT contains a row for each association between a standard (learning standard or course objective) and a learning system course that should address that standard.
|sog_pk1||Identifies the clp_sog record, by sog_pk1 join with ods_k12_standard.sog_pk1 to get all of attributes of one standard.|
|ls_course_pk1||Identifies the learning system course, by ls_course_pk1, join with ods_ls_course to get all of detailed information of each course.|
ODS_COURSE_CONTENT contains a row for each piece of alignable content within a course.
|pk1||Uniquely identifies the piece of alignable content.|
|content_name||Name of the item.|
|content_type||B=blog M=message thread F=forum G=gradebook C=course content T= test Q=question S=Survey J=Journal|
|content_owner||Display the parent of the item as defined below:
|content_description||The description of this piece of content. If the piece is a question this will be the first 100 characters of the question text. If the question text contains html, the html will be stripped out and the first 100 non-html characters will be shown|
|gradable_ind||Y if there there is a grade column associated with this item, N if not.|
|ls_course_pk1||Identifies the learning system course that this piece of content belongs to.|
|type_label||The label for this content item's type.|
|question_type||If this is a question, this column is the label for the question type. If it's not a question, this column's value will be null.|
ODS_LS_COURSE contains a row for each learning system course.
|course_name||Name of the learning system course.|
|batch_uid||Batch_uid of the learning system course.|
Indicates if the course is available or not.
|term_pk1||The term that this course is created in. Can be blank.|
|node_pk1||The course's primary node.|
|organization_ind||Indicates if this record is for an organization.|
ODS_LEARNING_STANDARD contains a row for each learning standard.
|sog_pk1||Identifies the goal.|
|standard_doc_number||The number that the provider normally uses to identify this standard, such as 1.1.a. Normally only unique within a sub-document.|
|sub_doc_name||The name given by the provider of the sub-doc that this standard belongs to, such as First Grade or Algebra I. Also known as grade level.|
|sub_doc_pk1||Uniquely identifies the sub doc.|
|standard_type||A string that the provider uses to identify the type of standard, such as Indicator or Benchmark.|
|stem||Provides context to the clp_sog.description for this standard. Consider a case where you have this standards sub-tree.
1. The child shall be able to do simple arithmetic
1.a Add numbers between 1 and 100.
1.b Subtract numbers between 1 and 100.
The stem for 1.a and 1.b would be "The child shall be able to do simple arithmetic."
|grade_hi||The upper grade range that this sub doc applies to. We use age based grade ranges so that we can numerically represent kindergarten as 5. Thus 1st grade corresponds to 6 and so on.|
|grade_lo||The lower grade range that this sub doc applies to. We use age based grade ranges so that we can numerically represent kindergarten as 5. Thus 1st grade corresponds to 6 and so on.|
|document_type||The type of of the standards document that this standard belong to, such as Assessment Standards.|
|document_type_pk1||Uniquely identifies the type of of the standards document that this standard belongs to.|
|discipline_code||Code that identifies the discipline, such as MATH.|
|discipline_name||Discipline name, such as Mathematics or Language Arts.|
|discipline_pk1||Uniquely identifies the document, such as California Math Assessment Standards for 1997, that this standard belongs to.|
|provider_name||The name of the entity which owns the standard, normally a state or standards body, such as California.|
|provider_code||A code which identifies the provider, such as CA.|
|provider_country||The country of the provider, such as USA.|
|provider_pk1||Uniquely identifies the provider.|
|parent_sog_pk1||Identifies the owning clp_sog record if the goal is part of a hierarchical set of goals.|
|display_order||Indicates the display order of standards within a sub document. The actual value has no significance. A java counter is used to assign these numbers in increments of 100 to standards as they are imported from the XML files. This allows them to display in the same order represented by the XML file. An increment is used because we may have to insert new standards during in-place updates.|
|description||Contains the text of the standard.|
|title||The learning system's title.|
|layer||Identifies the depth of this goal within its hierarchical set. If it isn't part of such a set, will be null or 1. This is information which could be determined by walking the parent_sog_pk1 tree but was denormalized to make it conveniently display goals.|
|doc_status||A=active, I=Inactive, R=In Review|
|doc_publication_date||The date on which the provider published this document. Normally, only a year is provided, so it defaults to January 1 of the publication year.|
|doc_last_update_date||The date of the last "in-place" update to this standards document. If there has been no "in-place" udpate, it should be same as the publication_date.|
|alignable_ind||Indicates if this learning standard can be aligned to or not. Can be Y or N.|
|owner||No longer used.|
|standard_set||The name of the standard set that this standard belongs to. For K-12 standards, this is a concatenation of the provider code, discipline name, and publication date. For non K-12, it's just the discipline name.|
This query shows each standard associated with each course and the contents aligned with each standard.
select c.course_name, ls.standard_doc_number, ls.description, cc.content_name
from ods_content_alignment_fact caf, ods_learning_standard ls, ods_course_content cc, ods_ls_course c
and c.pk1 = cc.ls_course_pk1