Blackboard Database Investigation
Interpreting the Blackboard Database
I found this discussion that confirms what we thought was the case. The Blackboard database isn't documented anywhere and its a matter of trial and error to figure things out.
Below are some basic SQL commands that work using the BB_BB60 schema.
To list all of the term 2 2007 courses select * from BB_BB60.COURSE_MAIN WHERE COURSE_ID like '%2072%';
A list of all the courses that are using safe assignment. SELECT distinct b.course_id from bb_bb60.course_contents a, bb_bb60.course_main b WHERE a.cnthndlr_handle = 'resource/x-mdb-assignment' and a.crsmain_pk1 = b.pk1 and b.course_id like '%2072%'
A list of all the course that user pk1 63292 select * from bb_bb60.course_main cm, bb_bb60.course_users cu where cu.users_pk1 like '63292' and cm.pk1 = cu.CRSMAIN_PK1;
An activity list for user 63292 (beerc) in the course eded21007 select * from bb_bb60.activity_accumulator where USER_PK1 = '63292' and COURSE_PK1 = '5669'
lists all of the assignments for nutr_19001_2072 that had end dates after 1/oct select * from bb_bb60.course_contents where crsmain_pk1 = '5376' and cnthndlr_handle like '%assign%' and END_DATE >= '01-OCT-07'
A list of all courses that had assignments due between the 2 dates. select * from bb_bb60.COURSE_CONTENTS where CNTHNDLR_HANDLE like '%resource/x-bb-assignment%' and end_date >= '01-OCT-07' and END_DATE <= '18-OCT-07'
Investigate //select attempt.*,course_main.course_id, gradebook_main.description from bb_bb60.COURSE_MAIN, bb_bb60.attempt, bb_bb60.gradebook_main, bb_bb60.GRADEBOOK_GRADE where // GRADEBOOK_main.pk1 = gradebook_grade.gradebook_main_pk1 and // gradebook_grade.pk1 = attempt.gradebook_grade_pk1 and gradebook_main.crsmain_pk1 = course_main.pk1 //and gradebook_main.due_date between '1-OCT-07' and '23-OCT-07' //order by ATTEMPT_DATE
To see a list of courses with quizzes that have quiz locks.
SELECT "ATTEMPT".* FROM
"BB_BB60"."GRADEBOOK_GRADE" "GRADEBOOK_GRADE"
INNER JOIN "BB_BB60"."GRADEBOOK_MAIN" "GRADEBOOK_MAIN"
ON "GRADEBOOK_GRADE"."GRADEBOOK_MAIN_PK1" = "GRADEBOOK_MAIN"."PK1"
INNER JOIN "BB_BB60"."ATTEMPT" "ATTEMPT"
ON "ATTEMPT"."GRADEBOOK_GRADE_PK1" = "GRADEBOOK_GRADE"."PK1"
INNER JOIN "BB_BB60"."COURSE_MAIN" "COURSE_MAIN"
ON "GRADEBOOK_MAIN"."CRSMAIN_PK1" = "COURSE_MAIN"."PK1"
WHERE course_main.pk1='6438' and attempt.score = '0' and attempt.status = '3
This query lists all of the term 1 2008 courses that don't have a course coordinator assigned. SELECT "COURSE_MAIN"."COURSE_ID", //"COURSE_USERS"."ROLE" FROM "BB_BB60"."COURSE_USERS" "COURSE_USERS", "BB_BB60"."COURSE_MAIN" "COURSE_MAIN" WHERE "COURSE_USERS"."CRSMAIN_PK1" = "COURSE_MAIN"."PK1" AND ("COURSE_MAIN"."COURSE_ID" LIKE '%2081%') and "COURSE_USERS"."ROLE" = 'P'
SELECT distinct COURSE_MAIN.COURSE_ID FROM "BB_BB60"."COURSE_MAIN" "COURSE_MAIN" WHERE ("COURSE_MAIN"."COURSE_ID" LIKE '%2081%') minus SELECT distinct "COURSE_MAIN"."COURSE_ID" FROM "BB_BB60"."COURSE_USERS" "COURSE_USERS","BB_BB60"."COURSE_MAIN" "COURSE_MAIN" WHERE "COURSE_USERS"."CRSMAIN_PK1" = "COURSE_MAIN"."PK1" AND ("COURSE_MAIN"."COURSE_ID" LIKE '%2081%') and "COURSE_USERS"."ROLE" = 'P
