First Tutorial Part 5: More Advanced Scenario

Previous	Main	Next
<-- Part 4	Main	Part 6 -->

Student Tests

As you noticed in the Pivots Syntax, we indicated that you could list multiple aggregates. It may not be obvious at first how useful that becomes. Let's change direction for our tutorial and now consider tests taken by students where we record the answers, a flag whether the answer was right, and the time it took in milliseconds for the student to answer the question. The goal is to start accumulating interesting analytics and measures about all the tests taken. To start, we want to know for every test taken, what was the number of correct and incorrect answers, and the total/average/min/max time it took to answer each question.

Let's first review the new table to capture the data for test results:

,{ "name": "TestAnswer"
  ,"description": "Test answers"
  ,"columns":[
      { "name":"formRefnum", "sameas":"Form.refnum"                  , "nullable": false, "invariant": true, "description":"The form's refnum" }
     ,{ "name":"questionId", "sameas":"TestQuestionAnswer.questionId", "nullable":false, "description":"Question id" }
     ,{ "name":"answerId"  , "sameas":"TestQuestionAnswer.answerId"  , "nullable":true , "description":"Answer value" }
     ,{ "name":"timeMillis", "type":"INTEGER"                        , "nullable":false, "description":"Time in milliseconds for the time spent answering the question" }
     ,{ "name":"correct"   , "type":"BOOLEAN"                        , "nullable":false, "description":"Whether the answer is correct or not" }
    ]
  ,"primary": { "autogen": true, "keyBatch": 500 }
  ,"foreign": [
      { "name":"Form" ,  "srcColumns":["formRefnum"], "destObject": "Form" }
    ]
  ,"indices": [ 
      { "name":"FormAnswer", "columns":["formRefnum", "questionId"]}
     ,{ "name":"Form"      , "columns":["formRefnum"]              , "orderBy":["questionId"]}
    ]
 }

It's similar to the FormAnswer table we defined previously, but with 2 additional fields.

Previous	Main	Next
<-- Part 4	Main	Part 6 -->