Automated Grading and Tutoring of SQL Statements to

Automated Grading and Tutoring of SQL Statements to Improve Student Learning Koli Calling ’ 13, November 14 -17 2013, Koli, Finland Carsten Kleiner, Christopher Tebbe, Felix Heine 2016/7/7 yusuke 1

Author �Carsten Kleiner a professor in the Department of computer science University of Hannover Database management systems, Software for mobile devices, Information Systems… 2

Abstract � a. SQLg software system (a. SQLg) to automatically assess SQL statements used in any introductory database class that teaches students the use of SQL v increases the efficiency of grading v improve student learning of SQL statements 3

Introduction � introductory database system course the most important goal is learning to use the SQL query language in a correct and efficient manner. number of students enrolled is rather large and on tend to be low and proficient students to perform grading and support lab sessions are difficult to find. � SQL statements appropriate to automated or computer-supported grading ⇔ schema modeling 4

Introduction � automated assignment grader + electronic tutors improves the attractiveness of the particular institution for students. improves performance of students in distance learning classes pemploy a single system that may be used in several different courses • not sufficient to develop and use a tool for a database system course alone pplugged into existing CMS • a plug-in to Web. CAT as well as our university’s proprietary CMS 5

Introduction �a. SQLg perform an automated assessment of student submitted SQL queries vtutoring tool to improve the quality of the student results • improve their scores with repeated submission of hopefully better solutions based on the feedback vimproving efficiency in assignment (or even exam) assessment • delivering a fair assessment of the quality of each solution without the tutoring capabilities 6

Automated sql grading concept 7

Automated sql grading concept � Grading process 1. Load all SQL-statements 2. Check one statement forbidden elements (optional) 3. Check if the statement equals reference solution 4. 5. 6. 7. 8. 9. 10. (optional) Check for syntactical correctness (points) Check statement cost (points) Check correctness of result of the statement (points) Check statement style (points) After all statements processed: add points & generate report Additional manual grading of statements (optional) Update total grade 8

Automated sql grading concept 1. Load all SQL-statements • • all statements of an assignment are loaded into the plugin possible to load a reference solution 2. Check one statement forbidden elements (optional) • • a statement contains for- bidden elements or not whitelists or blacklists v JSql. Paser • o get all elements of a statement 9

Automated sql grading concept 3. Check if the statement equals reference solution (optional) • compared to the reference solution 4. Check for syntactical correctness (points) • • performed using the database Syntactical correctness is determined by using the query cost of a statement 10

Automated sql grading concept 5. Check statement cost (points) • • can choose a limit of the maximum allowed cost The number of points awarded for efficiency depends on how close the query cost of the students statement is to the reference solution or to a given value from the configuration file 11

Automated sql grading concept 5. Check statement cost (points) • • turned out that such a strict correctness check led to a lot of frustration. two additional steps v before executing the statement in listing 1 we checked if the datatypes of the student and the reference solution match and are in the correct order v check if the sorting of the student had been the same as in the reference solution 12

Automated sql grading concept 6. Check correctness of result of the statement (points) • the result sets of the student statement and the reference solution are compared to each other 7. Check statement style (points) • • • In general it is very difficult to assess the style of a SQL statement as there are no generally accepted rules on SQL style in contrast to e. g. Java. a. SQLg is delivered with a small list of rules de- scribing a general style a complete list of rules has to be generated by the instructor. 13

Automated sql grading concept 8. After all statements processed: add points & generate report • • Additionally reports about the single grading steps are generated, which contain errors that may have occurred and other information including the number of points received by each statement in every step. The number of available points for a statement in each step may be set in a configuration file 9. Additional manual grading of statements (optional) • • After the report generation and point calculation the statements can be optionally graded manually by an instructor. The instructor may award additional points to the student. 14

Automated sql grading concept n no semantic analysis and not mentioned n no semantic analysis of the provided solutions in comparison to the reference solution n This feature is on the list of open issues for a future version of the system and would be an addition to the correctness check n the identification and prevention of plagiarism n figured out to be to complex to implement during the project 15

Some emaples � about 40 SQL exercises of increasing difficulty uses the well-known Oracle example table employee v valid solution v. Student solution üerror: ORA-00904: "YEAR": invalid identifier. 16

Some emaples � about 40 SQL exercises of increasing difficulty uses the well-known Oracle example table employee v. Student solution üerror: ORA-00904: "YEAR": invalid identifier. 17

Some emaples � about 40 SQL exercises of increasing difficulty uses the well-known Oracle example table employee v. Student solution 18

Some emaples � about 40 SQL exercises of increasing difficulty uses the well-known Oracle example table employee v. Student solution 19

Implementation overview � a. SQLg Core uses the well-known Oracle example table employee v. Student solution 20

Implementation overview � a. SQLg Core embeds the plug-in into the hosting environment system configures the other components and implements the overall flow control for SQL grading � Reporter collects information, that is generated by the other components, and creates report files � Statement. View. Builder builds a structured object view of a statement in form of a tree parsing the statement 21

Implementation overview � Statement. Filter component analyzes statements sub- mitted in order to prevent insider attacks The filter can be configured by either using a whitelist of allowed or a blacklist of forbidden elements � Statement. Tester statement loading and execution of the statement implements the general flow of the check as explained in section 3. � Check. Style The instructor may define a style rule file Each rule defines to which syntax and/or semantic type it is applicable 22

Evaluation They show that about 60% of the students felt supported a lot or more by a. SQLg in finding a solution (and only just over 10% did not feel supported at all). 23

Evaluation 24

Evaluation 25

Conclusion � � � presented a concept for automated assessment of SQL statements. The proposed algorithm uses the following aspects of the statement submitted in order to determine the score: syntactical correctness, efficiency of statement, correctness of results and style. in order to be able to practically use the software and prevent harm from the system by students submitting malicious solutions use black- or white-listing for allowed parts of the statement In future work, plug the software into the new university-wide CMS lon. CAPA the correctness check could be significantly improved by performing a semantic comparison of student and reference solution 26