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: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.
- 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.
- Quizzes are created and edited by organizers (or instructors).
- Quizzes have a list of editable properties, such as title, when the quiz is available to be taken, etc.
- 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.
- 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.
- 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.
- Quizzes do not have discussion, although one can add a "breakout" discussion item to a quiz item (or vice versa).
- 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.
III. Table Details
- Quiz. Each quiz has a unique key (qkey); each row represents a single quiz.
- QuizProblems. Each problem has a unique key (pkey); each row represents a single problem.
- 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.
- 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.
- 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.)
- Grouprules, Groups -- we use this pair of tables to handle access controls, exactly as we do for conferences, filesafes, etc. (Described elsewhere.)
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