Caucus 5.0 Quiz Schema

Last revised 04/22/2006

I. Introduction

"Quizzes" are a new feature in Caucus 5.0, intended to round out the e-Learning side of Caucus discussions.  The basic use-case model goes like this:

  1. Quizzes are just another type of item.  They belong to a conference, and have the same kind of access control as a regular ("discussion") item.
  2. Quizzes are created and edited by organizers (or instructors).
  3. Quizzes have a list of editable properties, such as title, when the quiz is available to be taken, etc.
  4. Generally it is assumed that the quizzes are meant to be "taken" by the regular ("include" level) members, although the organizers may also "take" their own quizzes.
  5. Quizzes in turn contain a list of "Problems".  Each Problem is a single question, with an optional list of answers.  A problem may be "in" one or more Quizzes, but every Problem must appear in at least one Quiz.  Problems can be moved around, or copied, to different Quizzes.
  6. Quiz takers have a record of their "Score(s)", identified by Quiz and Problem.  Takers can only see their own Scores, organizers can see all the scores for all quizzes in the conference.
  7. Quizzes do not have discussion, although one can add a "breakout" discussion item to a quiz item (or vice versa).
  8. Quizzes have 4 states, which appear as icons in the "new" column of the conference home page item list:
    • "New" -- never seen.
    • Open circle -- seen but no answers saved.
    • Half-filled circle -- some or all answers saved.
    • Filled-in circle -- quiz scored.

II. Table Summaries

This section briefly summarizes the database tables used to represent the data model, and the relationships between them.  Field-by-field details follow in the next section.

  1. Quiz.  Each quiz has a unique key (qkey); each row represents a single quiz.
  2. QuizProblems.  Each problem has a unique key (pkey); each row represents a single problem.
  3. QuizAnswers.  Each problem may have multiple "rows" of answers, e.g. a three-choice multiple-choice problem has 3 rows which define the possible answers.
  4. Quizmap.  Maps sets of problems to a quiz.  This tells us which quizzes "own" or "contain" which problems.  Each row relates (connects) one problem to one quiz.
  5. Quizscore.  Records Takers' scores on problems.  One row per Taker/problem/quiz/session combination.  (Allows for a single problem to be shared across multiple quizzes, and also for taking a quiz more than once.)
  6. Grouprules, Groups -- we use this pair of tables to handle access controls, exactly as we do for conferences, filesafes, etc.  (Described elsewhere.)

III. Table Details

quiz
    qkey   int   (primary key)
userid varchar   Quiz owner (Writer)
title varchar Description/name of quiz.
onepage int   Should all problems in this quiz be shown on one page?
visible0 date Starting date when quiz is visible
visible1 date Ending date when quiz is visible
deleted tinyint Is quiz deleted?
seesummary  varchar Who can see the summary? (editors/takers/anyone)
seeAnswer tinyint May takers see the correct answers after scoring?
 
quizproblems
pkey int (primary key)
owner varchar userid of original creator
category varchar general category of problem (arbitrary text)
title varchar Name or short description of problem
text text Actual text of problem. May include uploaded files, as per Caucus response (text contains macros which link to files)
texttype varchar type of answer, drives type of form element needed to input answer: choose 1 (radio), choose N (checkboxes), menu (pulldown), number, short text, long text
totalpoints int
deleted tinyint problem deleted?
floating int (default 0) 0=>tabular layout (columns); 1=>"floating" (flowing)
 
quizanswers
akey int primary key
pkey int foreign key to quizproblems table, i.e. which problem "owns" these answers
text1 varchar text before form element before this answer row
type varchar type of text in text1
text2 varchar text after form element
answer varchar correct answer (if needed)
sortorder int sorting order for listing problems
points int ??
newrow tinyint Start a new row when displaying this answer element?
 
quizmap
qkey int foreign key to quiz table
pkey int foreign key to quizproblems table
sortorder int sort order of this problem in this quiz
score int score for this problem in this quiz (overrides quizproblems.score)
skippable   int Is this problem skippable?
editable int Can Taker come back and edit their answer?
 
quizscore
userid varchar Taker
qkey int foreign key to quiz table
pkey int foreign key to quizproblems table
akey int foreign key to quizanswers table
session int ignored for now
answer text Taker's answer set
points int Points scored for this answer
status varchar Status of scoring: unscored=1, waiting for human scoring=2, scored=3.
submitted time When the answers were submitted/scored