Skip to main content
Continuing Online Operations
Loyola is continuing online operations while we prepare for reopening safely in the fall. Coronavirus resources

Selected Blackboard DDA Queries: Usage Notes

Selected Blackboard Data Direct Access (DDA) Queries.

Notes on the Blackboard Database

USER ACCOUNT PRIVILEGES

Blackboard users automatically have access to some data and functions just by being enrolled in a course. Exactly which data and functions is determined in large part by the user's role in a given course (Instructor, Teaching Assistant, Student, etc.). This level of access is considered "basic," and requires no special settings in a user's account.

More extensive privileges in Blackboard require that a user's account be associated with one ore more "system roles."  Each system role references a set of privileges listed in a table called system_roles_entitlement. At login, a user receives all of the entitlements referenced by all of the system roles with which the user's account is associated.

Each user can have one and only one Primary System role, recorded in the "users" table. For most users, without any extended privileges, the Primary System Role is "None." Each user can also have zero, one, or more than one, Secondary System roles. These are listed in the domain_admin table, with the domain name of 'default,' which applies to the entire database. 

The domain_admin table can also list system roles for specific users within domains other than the 'default' domain. Such other domains, if they exist, are specified subsets of courses, users, and other database objects. Domains other than 'default' can be used to set up user accounts that have special privileges with regard only to the courses, users, and other details within that domain.

WORKING WITH GRADE DATA

Grade information in Blackboard is stored in a complex set of tables, the most important of which are:

gradebook_setttings

The gradebook_settings table contains information that applies to the whole grade book within a single course. The most important column is public_item_key, which hold the primary key of the row in gradebook_main that identifies the one column in a course's grade book that is marked as the "external grade" (usually intended to be the final course grade that gets reported to the Student Information System).

gradebook_main

One row per grade book column in a given course, giving the name, possible points, due date, and how submissions (if any) are to be graded. An important distinction made here in the calculated_ind column is whether the corresponding column in the grade book of a course contains a calculated value or represents a unit of gradable work from a student. Aggregation_model can be set to Last, Highest, Lowest, First, or Average. Scorable_ind defines whether or not the grade book column participates in calculations. Deleted_ind indicates whether or not the row has been "soft" deleted. 

gradebook_grade

One row per student per column in a course's grade book. This is the focal point for determining a specific student's grade in a specific unit of gradable work. Manually entered grades, the average of multiple submissions (if aggregation_model is set to "average") are stored here. If the aggregation_model (gradebook_main) is set to First, Last, Highest, or Lowest, it is necessary to use the key pointers in the gradebook_grade to find the right row in the attempt table to find the student's grade. Note that since manual/override grades can be entered and then reversed, the manual_score and manual_grade columns might contain values that no longer apply. If the last_override_date column is NULL, then the manual_grade and manual_score values can be ignored. 

gradebook_grade_calc

One row per student per column in a course's grade book. This contains the last calculated value of a grade book column that is of the any of the calculated types, such as the default Total or Weighted Total columns. 

attempt

One row for each submission a student makes within Blackboard for a given assignment in a single course. Contains the grade assigned by the instructor, if any, for one single submission. Since an instructor might allow some or all students to submit multiple attempts for a single assignment, it is necessary to consider the aggregation_model in the gradebook_main table and the primary key pointers in the gradebook_grade table to identify the one row in attempt that has the score that "counts" (is the one that is displayed in the course grade book and is used in any grade book calculations).

Some data issues:

In general, reporting on student grade data requires that instructors take proper steps to ensure that the grade book in each of their courses is "clean." Unused columns can be misleading. Accurate reporting on past-due assignments requires that meaningful due dates be entered for each assignment. Accurate reporting on grades for individual units of work requires that each assignment have a meaningful value for "total points possible."  Reporting on students' course standings requires that the the "external grade" column be accurately set in each course. Be aware that retrieving the values for calculated columns and retrieving values for columns representing gradable work may require different query strategies, as the values for calculated columns and the values for graded submissions are stored in different tables.