Quiz database structure

Revision as of 08:47, 5 February 2006 by Gustav Delius (talk | contribs) (quiz_states)

Jump to: navigation, search

The quiz data model has a fairly large pool of database tables, so the first step in explaining them is to provide some order. Conceptually it is possible to distinguish between the tables holding the teacher-supplied data, defining quizzes and questions, and the tables storing all the data that is generated when students interact with the quizzes and questions.

A further simplification is possible by separating out the questiontype specific tables. They are logical extensions to other tables and therefore are not necessary for understanding the general basic model. However, some information is provided for each questiontype specific table, namely which questiontype it belongs to (although that should be clear from the name), which table it extends and what the additional data is needed for.

The diagram below shows how the most important tables are linked to one another.

Quiz database tables.gif

Teacher-supplied data

quiz

The quiz table contains the definition for all the quizzes. Each quiz belongs to a course, reflected by the course id, has a name and a short descriptive text (intro), an opening and a closing time and several fields that store the settings of various quiz options, each of which is explained in the quiz help that is linked to from the quiz settings page.

The only field that requires additional information is the optionsflag, which... I will put an explanation here eventually --Gustav Delius 06:02, 5 February 2006 (WST)

quiz_questions

This table constitutes the item or question bank, i.e. the repository of defined questions. The quiz_questions table defines the data that is common to questions of all types.

id
int(10) NOT NULL auto_increment,
Primary key. This is used as a foreign key in many other tables. for example the quiz_answers table allows to define an arbitrary number of answers that are part of the question. And many questiontypes have their own tables that hold more information about the question.
category
int(10) NOT NULL default '0',
Foreign key: refers to an id in the table quiz_categories
parent
int(10) unsigned NOT NULL default '0',
Foreign key: refers to an id in the table quiz_questions. This field is set to zero except in the case of wrapped questions as they are used for example in the multianswer questiontype. When a question wraps around any number of subquestions the subquestions will have their parent id field set to the id of the main question, thus allowing the question to find all its sub-questions (or wrapped questions). If parent is not '0' the question is never shown in a list of questions, because it is not a valid question by itself. This is also used for hiding random questions from the question list. Their parent field is simply set to their own id.
name
varchar(255) NOT NULL default ,
Question name that is used in question lists on the quiz editing pages
questiontext
text NOT NULL,
Main text of the question
questiontextformat
tinyint(2) NOT NULL default '0',
image
varchar(255) NOT NULL default ,
defaultgrade
int(10) unsigned NOT NULL default '1',
Default grade for a question, usually '1'. In the wrapped parts of cloze questions defaultgrade represents the weight of the part.
penalty
float NOT NULL default '0.1',
The penalty that is applied (if the respective quiz option is set) for an incorrect attempt at the question.
qtype
smallint(6) NOT NULL default '0',
The questiontype of the question. (Constants are defined in locallib.php)
length
int(10) unsigned NOT NULL default '1',
This defines how many question numbers are required for this question. It is generally set to "1", but the description questiontype, for example, sets it to "0", reflecting the fact that it doesn't have a question number.
stamp
varchar(255) NOT NULL default ,
Unique identifier
version
int(10) NOT NULL default '1',
A number representing how often the question was edited.
hidden
int(1) unsigned NOT NULL default '0',
Controls whether to display a question in the question bank list in edit.php. Hiding questions is a mechanism to "delete" questions without removing them from the database and thus to allow them to be restored or "unhidden" at a later stage. Also the (unfinished and disabled) versioning feature uses the hidden field to prevent older versions of a question from cluttering the user interface.

In addition to these static fields, which are part of the generic question definitions, there are some additional fields that are only added to the object at runtime. There are two different kinds of fields: On the one hand there are questiontype specific fields, which are also part of the static question definition, but only apply to some questions. On the other hand there are fields that refer to the question instance, i.e. the question in the context of a particular quiz. The values for these later fields can differ when a question is used in different quizzes.

The runtime fields are added to question objects with the function
quiz_get_questions_options()
is called. This in turn calls the questiontype specific
get_question_options()
method for to add the options field.
maxgrade
The maximum grade available for this question in this quiz, i.e. the grade for a correct response.
instance
Foreign key: refers to an id in the table quiz_instances
options
Optional field. It provides a namespace for any questiontype specific information that may be stored in this field. It is generally set by the
get_question_options
method.
name_prefix
The prefix to be used on all interactions printed as part of the question.

We now deal in alphabetical order with the remaining tables for the teacher-provided data.

quiz_answers

This table allows a common way to store one or more teacher-defined answers for each question. It is not mandatory for a questiontype to make use of this table however. A questiontype may choose to store it's teacher-defined answers in an entirely different way, or even to do away with teacher-defined answers and use some other method to mark the student-supplied answer. For example it could calculate the correct answer on the fly.

id
int(10) unsigned NOT NULL auto_increment,
Primary key
question
int(10) unsigned NOT NULL default '0',
Foreign key to quiz_questions table.
answer
text NOT NULL,
The string that represents the teacher-defined answer that will be compared to the student responses for grading and feedback purposes. The format of this field is entirely up to the question type.
fraction
varchar(10) NOT NULL default '0.0',
The fraction of the total mark that the student should earn for giving this particular answer. This could be a negative number for wrong answers. Note that it is stored in a varchar(10) field.
feedback
text NOT NULL,
Text that can be displayed to student as feedback when the student's responses match this particular answer.

In the past there was also a sequence number field was sometimes used to store the order of the different answers and was set to '0' if the order was of no importance.

quiz_categories

Categories are provided as a way to organize questions. Each category has a name and a descriptive text (info) and the sortorder as metadata. Categories allow hierarchical nesting via the parent id and can be private or published, i.e. they can be made available to teachers in other courses.

Since categories are simply a means for organising questions they are not vital for understanding how the quiz module works.

id
int(10) unsigned NOT NULL auto_increment,
Primary key
course
int(10) unsigned NOT NULL default '0',
Foreign key to the course table
name

varchar(255) NOT NULL default ,

info
text NOT NULL,
publish
tinyint(4) NOT NULL default '0',
stamp
varchar(255) NOT NULL default ,
parent int(10)
unsigned NOT NULL default '0',
sortorder int(10)
unsigned NOT NULL default '999',

quiz_question_instances

Questions can be assigned different grades in different quizzes. These are stored in this table.

While, after a small extension, this table could also fulfill the purpose of storing the order of the questions in a quiz, this is currently still done in the questions field in the quiz table.

id
int(10) unsigned NOT NULL auto_increment,
Primary key
quiz
int(10) unsigned NOT NULL default '0',
Foreign key to the id of the quiz table.
question
int(10) unsigned NOT NULL default '0',
Foreign key to the id of the quiz_questions table.
grade
smallint(6) NOT NULL default '0',
The maximum grade assigned to this question in this quiz. This grade was set by the teacher on the quiz editing page.

quiz_question_versions

This feature is not finished and disabled. The table structure may still change. See Quiz developer docs#Question versioning for a discussion.

id
int(10) unsigned NOT NULL auto_increment,
quiz
int(10) unsigned NOT NULL default '0',
oldquestion
int(10) unsigned NOT NULL default '0',
newquestion
int(10) unsigned NOT NULL default '0',
userid
int(10) unsigned NOT NULL default '0',
timestamp
int(10) unsigned NOT NULL default '0',

Student-created data

quiz_attempts

In the quiz_attempts table a record is created each time a user starts an attempt at a quiz. This is one of the important tables and the corresponding $attempt object is passed between many of the quiz module functions and methods.

quiz
int(10) unsigned NOT NULL default '0',
The id of the quiz that is attempted
userid
int(10) unsigned NOT NULL default '0',
The id of the user who is attempting the quiz
attempt
smallint(6) NOT NULL default '0',
It is possible for a user to attempt a quiz several times, therefore the number of the attempt is stored in this field.
sumgrades
varchar(10) NOT NULL default '0.0',
The (unscaled) grade for the attempt, i.e. if the grades assigned to the questions add up to 8, but the maximum grade for the quiz is set to 10, then the sumgrades field can contain 8 at maximum.
timestart
int(10) unsigned NOT NULL default '0',
The timestart field is set to the current time when an attempt is started and is never changed afterwards.
timefinish
int(10) unsigned NOT NULL default '0',
The timefinish field is set to "0" initially and to the current time when the attempt is closed. This is exploited at several places in the code to determine whether an attempt has been closed or not (i.e. closed = timefinish > 0). For all other modifications of an attempt record the timemodified field should be changed as well.
timemodified
int(10) unsigned NOT NULL default '0',
This should be changed each time data in the record is modified.
layout
text NOT NULL,
a comma separated list of question ids, with a "0" denoting a page break. Usually the comma separated list ends with ",0".
preview
tinyint(3) unsigned NOT NULL default '0',
A flag that marks a teacher preview (i.e. an attempt by a user with teacher privileges) that may be automatically deleted when the quiz is previewed again, and which is not taken into account when viewing statistics.

quiz_states

States are saved for each interaction with a question. This allows a review of the complete history of a user's interactions with individual questions. The current state is the most important object used by the quiz module runtime code. This $state runtime object has a few additional fields not in the database that will be explained further down. The database fields are:

id
int(10) unsigned NOT NULL auto_increment,
Primary key
attempt
int(10) unsigned NOT NULL default '0',
Foreign key: refers to an id in the table quiz_attempts
question
int(10) unsigned NOT NULL default '0',
Foreign key: refers to an id in the table quiz_questions. The attempt id and the question id together sufficiently identify a question instance that a state belongs to.
originalquestion
int(10) unsigned NOT NULL default '0',
Foreign key: refers to an id in the table quiz_questions. It identifies which question was in use when the student attempted the question. This is part of the question versioning code.
seq_number
int(6) unsigned NOT NULL default '0',
A counter that provides information about the sequence in which the states were created.
answer
text NOT NULL,
This field is deleted during runtime and replaced with the responses field. For legacy reasons it is still called answer in the database. Questiontypes can store students' responses (usually in a serialized format) in this field using the method save_session_and_responses. Questiontypes are allowed to deviate from this and handle their response storage in any desired way. Some questiontypes do not use this field but instead store the student response in their own table extending this table.
timestamp
int(10) unsigned NOT NULL default '0',
A timestamp to record when the state was created. This could mainly be useful for audit purposes.
event
int(4) unsigned NOT NULL default '0',
Records the event or interaction type that lead from the previouse state to the current one. The field can take the value of any of the following constants (defined in locallib.php):
  • EVENTOPEN: The attempt has just been opened and this is the initial state of a question, i.e. the user has seen the question did not interact with it yet.
  • EVENTSAVE: The responses are just being saved, either because the student requested this explicitly or because the student navigated to another quiz page.
  • EVENTVALIDATE: The student requested a validation of the responses. (This is not supported by all questiontypes.)
  • EVENTGRADE: The responses are being graded but the question session is not closed. This is generally the case for adaptive questions.
  • EVENTCLOSE: The responses are being graded and the question session is closed. Usually this happens because the whole attempt closes, either because the student requests it or because the time is up or because we are beyond the due date.
  • EVENTDUPLICATEGRADE: This is a strange one. It indicates that the responses would have been graded had they not been found to be identical to previous responses.
grade
varchar(10) NOT NULL default '0.0',
Calculated from the raw grade by deducting the penalty and rescaling to the defaultgrade value of the question. Note that this is a varchar(10) field like most grade-related fields in the quiz module.
raw_grade
varchar(10) NOT NULL default ,
The grade that was achieved for the question scaled to the question's weight or grade as assigned in the quiz_question_instances table
penalty
varchar(10) NOT NULL default '0.0',
The penalty incurred during the transition from the previous state to this one. This is different to the cumulative penalty, which can be calculated by adding up all the penalties from previous marking events.
In addition to the database fields a few fields are added to the states at runtime. They are dealt with by the questiontypes with the methods
create_session_and_responses
,
restore_session_and_responses
and
save_session_and_responses
and are defined as follows.
sumpenalty
The cumulative penalty, which can be calculated by adding up all the penalties from previous marking events. For efficiency these cumulative penalties are stored in the table quiz_newest_states so that they do not need to be re-calculated each time.
changed
This records whether a change has taken place to the runtime state. This is set to false when the state is restored from the database. Any code which changes the question state must set this field to true amd must increment seq_number. The
quiz_save_question_session()
function will save the new state object to the database if the field is set to true.
responses
This is automatically set to the value of the database field
answer
before that one is removed. The responses field contains an array of responses. In the default case of a single response the value can be found in ->responses[]. For questiontypes using several HTML form elements for their responses the array contains a value for each of the interaction elements. The indicies are determined by the name of the elements after the name_prefix is stripped.
last_graded
This field contains another state object representing the most recent graded state in the history of this question attempt. This is necessary, because if a state merely represents a save interaction, it should not affect the session in any way. Therefore another grade interaction has to proceed from the last graded state.
options
Optional field. Similarly to the options field in the question object, this field provides a namespace for any questiontype specific information. The difference is that the information in the state->options field should be state specific, whereas the question->options field should be static.

We now deal in alphabetical order with the remaining tables holding the student-created data.

quiz_grades

The quiz_grades table merely stores a student's awarded grade for a quiz. Since it is possible to allow several attempts on a quiz, the grade stored is calculated depending on the quiz setting grademethod. This table exists mainly for convenience, because the values of its fields can be recalculated.

quiz_newest_states

This table exists only for efficiency reasons:

  1. Via its 'newest' and 'newgraded' fields it gives attempt.php a way to quickly find the newest state and the newest graded state for an attempt. It allows the construction of SQL to select all the states that need to be loaded on attempt.php or review.php.
  2. Via its 'sumpenalty' field it gives quiz_apply_penalty() a quick way for getting at the accumulated penalty that needs to be applied. Without this field the penalties from all previous graded states would have to be added up each time. Not a big deal actually because this could be achieved with a single SQL query (using SUM) but this field was introduced when we still had the multiplicative penalty scheme around which would have been more difficult to recompute.

This table was introduced in Moodle 1.5 and is not populated for all states during the upgrade because on sites with a lot of existing states that could take too long. Rather it is done whenever needed by quiz_upgrade_states().

Questiontype specific tables

quiz_calculated

The quiz_calculated table is an extension to the quiz_questions table by the calculated questiontype. However, it would be more suitable to change that to be an extension of the quiz_answers table, which, from a data perspective, is already possible, since an answer id is stored in the answer field. The questiontype code would need some changes to take this into account, however.

quiz_dataset_definitions

The quiz_dataset_definitions table belongs to the abstract datasetdependent questiontype, which is currently only used by the calculated questiontype. It is an indirect extension to the quiz_questions table, because the quiz_question_datasets table can link a question to one or more datasets. Each dataset represents a variable, that is used either in the questiontext or in the answer to a dataset dependent question.

quiz_dataset_items

Dataset items can be created for each dataset. The quiz_dataset_items table stores these possible values for the variables defined in the quiz_dataset_definitions table.

quiz_match

The quiz_match table belongs to the match questiontype and extends the quiz_questions table. It is only used in the code for saving matching questions and can therefore be considered redundant.

quiz_match_sub

The quiz_match_sub table belongs to the match questiontype and extends the quiz_questions table. It stores the pairs of questions and answers (as strings) that need to be matched for a correct solution.

quiz_multianswers

The quiz_multianswers table belongs to the multianswer questiontype and is an extension of the quiz_questions table. It merely stores a comma separated list of question ids in the sequence field, which is important, because that's the only way to know which sub question belongs to which position in the questiontext.

quiz_multichoice

The quiz_multichoice table belongs to the multichoice questiontype and is an extension of the quiz_questions table. The layout field does not seem to be used, the answers field stores the order of the answers (should be superseded by the seq_number field in the quiz_answers table) and the single field is a flag signaling, whether only one option or multiple options can be chosen.

quiz_numerical

The quiz_numerical table belongs to the numerical questiontype and is an extension of the quiz_answers table, defining a tolerance value for each answer.

quiz_numerical_units

The quiz_numerical_units table is used by the numerical questiontype and the calculated questionype. It extends the quiz_questions table, defining an arbitrary number of units that can be used in the responses.

quiz_question_datasets

The quiz_question_datasets table is used by dataset dependent questionypes (i.e. calculated) to link datasets to questions.

quiz_randomsamatch

This extension to the quiz_questions table simply stores how many shortanswer questions should be randomly chosen to build this randomsamatch question.

quiz_rqp

No information.

quiz_rqp_states

No information.

quiz_rqp_types

No information.

quiz_shortanswer

The quiz_shortanswer table belongs to the shortanswer questiontype and is an extension of the quiz_questions table. The answers field stores a comma separated list of answer ids, which is redundant. The only valuable piece of information contained in this table is the usecase field, which is used to decide whether to do a case sensitive or case insensitive comparison for grading.

quiz_truefalse

An extension of the quiz_questions table the quiz_truefalse table stores the answer ids for the true and for the false answers.