MYUCDAVIS QUIZ BUILDER ARCHITECTURE SUMMARY

The purpose of this document is to identify the architecture of MyUCDavis Quiz Builder on the basis of database tables and table relationships. The intent is to use this documentation in support of migrating quiz content from MyUCDavis to the Davis implementation of Sakai.

RELATIONSHIP BETWEEN A QUIZ AND A COURSE

Quizzes exist independent of a course until the instructor or proxy user adds the quiz to a course via Website Builder. When the quiz is added to the course, a record is generated in page_metadata which generates a quiz link in the code based plus detail information like the order the quiz link appears on the page, release information, and whether the link is currently visible.

The sole association between the two tables is that the quizID of qz_quiz is entered in the file_name field of page_metatdata.
Note - relationships between a quiz taken, the course, and the student are defined in the qzt_top table.

The relationship between a quiz and a course are all defined on the course side within the page_metadata table. Quiz records in qz_quiz are independent of courses and the fields 'term' and 'crn' in qz_quiz are irrelevent - i.e., every record has nulls in these two fields.
The primary keys in page_metatdata include term_code, crn, and pid, where pid refers to a page id which is defined by the order in which items are added to the course website beginning with '1'. The combination of these non-unique keys form a unique primary key.
Other important fields include:

• page_name which is generated by wrapping the following text around the quiz name: "Take the " + qz_quiz.quizname + " Quiz"
• file_name which is a varchar field that stores the numeric quiz id
• user_account which stores the course instructor's kerberos name - NOT the name of the quiz owner or person who added the quiz (which could be the instructor or someone with proxy access to the course"
• activity_date which is either the add and last edit date
• is_quiz which is a single varchar field where 'q' indicates a quiz, 'a' indicates and answer key, and null exists for all other types of (non-quiz) records.

Additional fields designate to whom and when course information, in this case, quizzes, are released.

• secure: if secure is 'Y', only enrolled students, instructor and proxies may view
• hidden: if hidden is 'Y', quiz does not appear in course for anyone
• release_flag: if release_flag is 'Y', this indicates a record in the selective_release table is set for this quiz.

Selective releases are set by date, by person (e.g., to a particular student), by course (if using joined sections - specify particular sections for release), or by page (i.e., uses users stats to see if the person has viewed a particular page first.) Of the data to date, instructors chose to release quizzes by course, by date and by person. No instructors have used the release by page previously viewed function.

RELATIONSHIPS BETWEEN QUIZZES, CATEGORIES, SECTIONS AND THE QUESTION CATALOG

(Note: Graphic data valid on 1/13/06)

A top down review of the existing tables and data shows the following hierarchical structure for quizzes in the MyUCDavis cms. The hierarchy of Quiz Builder revolves around quizzes, quiz sections, questions, answers, and finally student response data associated with quiz attempts. In addition, the original design suggests a grouping mechanism for quizzes in a categories table, but these tables are so little used as to be negligible.

DATA ANALYSIS OF TABLE USAGE - QZ_QUIZ & QZ_SECTIONS

The key table here is qz_quiz which contains the primary key identifying the quiz (quizID), the quiz name, the kerberos_name of the owner, and quiz type information.

The two category tables on the left side of the graphic allow the user to create a category and then to associate quizzes with that category. This feature is virtually unused (the majority of the records were created by IET personnel as test data.) These records do not need to be ported to sakai.

The quiz sections relationship is the key to associating questions with a quiz. The process flow is that the user first creates a quiz (record) and in the next step makes additional decisions which populate a section record. There is a minimum of a one-to-one relationship when a quiz is created, i.e., for every quiz created (quizzes w/o this association are not complete thus irrelevent) there is at least one section automatically associated with it. (As of 1/30/06, there are 72 'orphan' quizzes.)

The default name for auto-generated sections is 'Section 1' with the number increasing with each section. The same section relationship exists in Samigo with 'Default' as the auto-generated name, which will not be displayed unless it is changed. Some quizzes have more than the one auto-generated section.

No quizzes with 9 sections

DATA ANALYSIS OF TABLE USAGE - QZ_QUES_CATALOG & QZ_SECTION_QUESTIONS

The qz_ques_catalog table contains all questions. Questionid is the primary key, with question name, question type, and kerberos_name as required fields. All relationships to answers and feedback are associated with this primary key, questionID.
(As of 1/30/06, there was a total of 313 unique question owners and 72 of these had more than 25 questions in their pool.)

There are 6 question types possible in this pool, with the following distribution on a count of 9535 total as of 1/30/06 (no nulls allowed):

The association of quizzes with questions occurs in qz_section_questions. The unique secid (from qz_section) is matched with a question key (qcid) - the two foreign keys are the primary key with additional information for the order/sequence of appearance.

RELATIONSHIPS BETWEEN QUESTIONS AND COLLECTIONS

(Note: Graphic data valid on 8/14/06)

Collections is an optional feature of Quiz Builder which allows faculty and staff to organize their large generic pool of questions into subpools of optionally sequenced questions.

Breakdown as of 8/14/06:

• Total noumber of collections: 319
• Total number of collection owners: 74
• Number of valid owners: 60
• Number of collections owned by valid users: 206
• Number of valid collections containing questions: 197
• Number of distinct questions contained in valid collections: 1777