SQLify

1
SQLify

• Stijn Dekeyser
• Michael de Raadt
• Tien Yu Lee

2
Cross-disciplinary Research

• Two perspectives
• Educational (pedagogical)
• Database (technical)
• Previous SQL trainers
• Poorly described technically
• Binary assessment
• Heuristic evaluation only

3
Motivation

• Overcome difficulties
• Students produce errors
• Simple SQL misleading
• Trouble memorising schema
• Improve student outcomes
• Reduce marking
• Increase consistency

4
Requirements

• Rich feedback
• Peer-review
• Wider range marks
• Judge accuracy of student reviews
• Special treatment of DISTINCT and ORDER BY
• SQL-injection attack countermeasures

5
Query Testing

• Heuristic
• Comparing results
• Can be fooled by specific queries
• Conjunctive Queries equivalence testing
• CQ gt70 Intro Curriculum
• Query rewriting then tableaux
• SELECTFROMWHERE

6
SQLify Process

• Trial
• Only automated assessment
• No peer reviewing
• Assignment Submission
• Reviewing peers work
• Receive feedback and marks

7
Trial and Submission

• Interface

8
Reviewing

• Single step submit-review

Accuracy comparison
ReviewingStudent
select ...
Instructor
Studentsbeing reviewed
select ...
Accuracy comparison
9
Reviewing (cont.)

• L0. Syntax, semantics, output schema wrong
• L1. Syntax correct, rest wrong
• L2. Syntax and schema correct
• L3. Syntax correct, semantics mostly incorrect
• L4. (Student not sure)
• L5. Syntax correct, semantics just adequate
• L6. (Student not sure)
• L7. Syntax and semantics correct

10
Suggesting a Mark

• SQLify Aids Instructor
• Incorrect
• sysL1
• Largely incorrect
• sysL2 ? L2std1L4 ? L2std2L4
• Conflict (probably incorrect)
• sysL2 ? (L2std1L4 ? L2std2L4)
• Conflict (probably correct)
• sysL6 ? (std1L4 ? std2L4)
• Heuristically Correct
• sysL6 ? std1L5 ? std2L5
• Definitely Correct
• sysL7

11
Example

• Schema
• employee(eNo, fname, lname, wage, dNo, eloc)
• department(dNo, dname, dlocation)
• CQ Problem
• Give the first and last names of all employees in
  the Sales department earning more than 300
  dollars.

12
Example

• Solution
• SELECT fname, lname FROM employee E, department D
  WHERE E.dNo D.dNo AND dname Sales AND wage
  gt 300

13
Example
14
Example

• Schema
• employee(eNo, fname, lname, wage, dNo, eloc)
• department(dNo, dname, dlocation)
• Non-CQ Problem
• List all locations where there is either an
  employee or a department.

15
Example

• Solution
• (SELECT eloc FROM employee) UNION (SELECT
  dlocation FROM department)

16
Example

• Correctness marks for submitted query answers

17
Example

• Accuracy marks for reviews

18
Example

• Final Mark

19
Experiences

• Intention was to use fully in S1 2007
• Problems with interface
• Problems with security
• Used by a few students in controlled environment
• Only practice part, not assessment
• Interest from third parties
• Cummulative assessment can be too harsh
• If output schema is wrong, logic can still be
  very good

20
Questions

• dekeyser_at_usq.edu.au
• deraadt_at_usq.edu.au
• www.sci.usq.edu.au/projects/sqlify