Traffic Lights

1 Disclaimers and outstanding questions
2 Purpose
3 Minimal presence of academic
4 The course site
5 Support code
6 Database information

Disclaimers and outstanding questions

The whole idea of simple objective measures of a course website is hugely problematic and these indicators are of only limited value. The outstanding questions page outlines a number of the concerns and limitations we've identified with this approach which need to be improved upon and considered when looking at the indicators.

Purpose

Misc. thoughts/ponderings in the development of some simple reports to identify potential problems or limitations in blackboard courses.

Initial main aims include:

Is the course available?
Level of academic presence/participation

Any participation in the discussion forum
Any announcements made

Amount and type of content

Any broken links
Any wrong links (e.g. old course profile)
Is the course profile there?
Is there a discussion forum -- that is used?
Are any of the "folders" empty

Minimal presence of academic

This will initially be defined as

The number of times the academic has logged into the course site now.
The number of times they have posted to the discussion forum now.

Implementation

This will have to be implemented as a daily query as it takes a looong time to query the activity accumulator. Also the query should probably not be one per user. i.e. one query to get summary of activity for all staff - coordinators. Also want to get some idea of when the activity is taking place and the type of activity. Maybe do it by week.
Perhaps the idea is

Given a list of courses, get all the coordinators for that course
Do multiple queries which grab from the activity accumulator what they did for each week of term grouped on type e.g. select USER_PK1,timestamp,EVENT_TYPE,INTERNAL_HANDLE,DATA from ACTIVITY_ACCUMULATOR where timestamp > and timestamp < and USER_PK1 in (.....) group by USER_PK1,timestamp -- needs lot more work
Can then manipulate that for display and group by different types of activities

The course site

Want to be able to develop a structure for each course and do checks and comparisons on that structure. A Bb site is essentially hierarchical, so build up an array of hash refs based around the course menu seems to be the way to go.

The hierarchy of a Bb course site, from a database perspective is

COURSE_MAIN - contains detail about the course as a whole

COURSE_TOC - contains detail about the course menu items

COURSE_CONTENTS - contains list of all elements in the site

PARENT_PK1 points to the parent
MAIN_DATA contains the text included with the element
TITLE is the name/title of the element
CONTENT_TYPE is the type - e.g. LINK
CNTHNDLR_HANDLE internal BB description
START|END_DATE - simple adaptive release?

Blackboard::CourseMap, given COURSE/PERIOD/YEAR, does the following

Blackboard::Course to get CRSMAIN_PK1
select * from COURSE_TOC where CRSMAIN_PK1={}

Blackboard::CourseContents, given COURSE/PERIOD/YEAR, does the same but only using COURSE_CONTENTS rather than COURSE_TOC

To do

combine CourseContents into CourseMap
translate the array of hashes into a hierarchical structure

Support code

Need simple way to go from CQU ids into Blackboard ids

Courses - course code, period and year into Blackboard CRSMAIN_PK1

Blackboard::Courses::new( COURSE =>, PERIOD =>, YEAR => ) will do this. Relies on the courses following the standard course id model of SUBJECT_CATALOG_NBR_STRM

People - username into USER_PK1

Blackboard::Users::new( USERNAME => ) will do this. However, there is a problem with CQU's Bb database having multiple entries for a single username. Which means multiple PK1s, which means a chance for problems when getting the courses a user is teaching.

Database information

ANNOUNCEMENTS

Fields

PK1
END_DATE
SUBJECT
TEXT_FORMAT_TYPE H | S
START_DATE
CRSMAIN_PK1
PERMANENT_IND
ANNOUNCEMENT (CLOB)
USERS_PK1
ANNOUNCEMENT_TYPE - only C

ACTIVITY_ACCUMULATOR

Has to come from activity accumulator - relevant fields

PK1
EVENT_TYPE

CONTENT_ACCESS COURSE_ACCESS LOGIN_ATTEMPT LOGOUT MODULE_ACCESS PAGE_ACCESS SESSION_INT TAB_ACCESS

USER_PK1

Need to get this from COURSE_USERS

INTERNAL_HANDLE

Valid values - address_book admin_course_add_users admin_course_list_users admin_course_properties admin_course_remove_users admin_main admin_modify_user_in_course agroup announcements announcements_entry check_grade classic_course_catalog collaboration communication control_panel course_images course_props_areas course_props_defaults course_props_design course_select_icons course_set_course_images course_settings course_tools_area cp_add_group cp_add_users_enroll cp_announcements cp_content cp_copy_course cp_copy_course_content_exists cp_course_images cp_course_images_banner cp_course_images_button cp_course_options cp_course_options_area cp_course_options_avail cp_course_properties cp_course_statistics cp_course_utilities_export cp_digital_dropbox cp_discussion_board cp_gradebook cp_gradebook_chooseItems cp_gradebook_chooseStudents cp_gradebook_displaySettings cp_gradebook_downloadGrades cp_gradebook_gradebookSettings cp_gradebook_item cp_gradebook_itemInfo cp_gradebook_manageCategories cp_gradebook_manageOutcomeDefinitions cp_gradebook_modifyGrade cp_gradebook_uploadGradebook cp_gradebook_user cp_gradebook_viewGradesByItem cp_gradebook_viewGradesByUser cp_gradebook_views cp_gradebookviews_item cp_list_modify_users cp_manage_group cp_manage_groups cp_manage_groups_add cp_manage_groups_modify cp_manage_groups_prop cp_manage_tools cp_modify_user cp_pool_manager cp_remove_users cp_send_email cp_send_email_all_groups cp_send_email_all_instructors cp_send_email_all_ta cp_send_email_all_users cp_send_email_select_groups cp_send_email_select_students cp_staff_information cp_survey_manager cp_test_manager cp_tools_enable_bb cp_tools_enable_content cp_tools_enable_ext create_course cust_login_page discussion_board discussion_board_entry drop_box edit_homepage email_all_groups email_all_instructors email_all_ta email_all_users email_select_groups email_select_students glossary group_email group_file group_forum group_virtual_classroom groups institution_properties jjcd-jjcdg-nav-1 list_courses list_courses_by_user list_users lobj-podcast-nav-1 messages messages_manager my_ab_add_contact my_addressbook my_announcements my_email_courses my_grades my_inst_personal_change_password my_inst_personal_edit my_inst_personal_info my_inst_personal_privacy_options my_inst_personal_textbox_options my_tasks personal_edit personal_info personal_locale personal_privacy_options personal_textbox_options search_courses send_email staff_information student_roster system_settings tasks texteditor user_directory user_management_links

DATA - Message View, Assessment tasks during term, Discussion Board, Reply Message,
TIMESTAMP

This table should give most of these things.

Querying the activity accumulator takes a real long time given its size. May need to think intelligently about this one.

Course content

COURSE_TOC seems to be a high level table about the course. Where does ENTRY_POINT_IND point? The fields are

PK1
CRSMAIN_PK1
COURSE_CONTENTS_PK1 - how to get more detail about what's in it??
LABEL - textual description
HREF - if there's a link
TARGET_TYPE - type of target (no kidding!!)
POSITION - order in the menu
ENTRY_POINT_IND
ALLOW_OBSEVER_IND
INTERNAL_HANDLE
ALLOW_GUEST_IND

This seems to be the table for the course menu - i.e. the ToC

COURSE_CONTENTS

Seems to be the main one with contents. Relevant fields

CNTHNDLR_HANDLE - defines the type of file/handler
CONTENT_TYPE - perhaps - REG|LINK
MAIN_DATA (CLOB)
CRSMAIN_PK1
FOLD_IND??
WEB_URL??
TITLE
PARENT_PK1 - build the hierarchical structure of the site - who's the parent of the top folders - this is NULL indicates top level -- not NULL- has to be some other value