Combining dynamic and static analysis for automated grading SQL statements

<strong>Introduction</strong> Our experiment was conducted in an undergraduate Relational Database course at the Australian National University. The experiment was conducted on August 10th 2018 when students enrolled in the Relational Database course started to learn relational data model and SQL. The experiment was carried out fully online for three weeks and a total of 393 students were enrolled. The students were asked to login in an online assessment platform and complete 15 exercises. This platform provided an SQLite environment in students browsers by compiling the SQLite C code with Emscripten. Students were allowed to submit and execute their answers in the form of SQL statements. If the execution result of the statement submitted by the student is the same as that of the reference statement, the online assessment platforms will return a feedback message indicating that the execution result is correct. During the interaction with the assessment platform, statements submitted by students were recorded and archived. Overall, our experiment had collected 12,899 statements submitted by students. To create a benchmark dataset that can be used to evaluate different grading approaches, we randomly selected 45 SQL statements submitted by students for each exercise, and asked three teaching assistants to grade them manually. Finally, we average the scores provided by the three assistants and take it as the final score of each statement. The dataset collected in this experiment is ready for public release. All experimental data are stored in Submission.sqlite, which is an SQLite database file. It is recommended to use software such as DB browser or SQLite expert to explore the database. <strong>Datatable description</strong> <em><strong>exercises_result</strong></em> This datatable stores the statements submitted by students. Based on the execution result of statement, statements were divided into three categories. noninterpretable: the statement is non-executable. partially correct: the execution result of statement is different from the expected result. correct: the execution result of the SQL statement is the same as the expected result. After analyzing the correct statements, we found that the correct set contains some statements carefully constructed by students to deceive the examination system. Take exercise 1 as an example, the task is to answer the following questions using SQL statements. Question: Assume persons who were born in the same year are the same age and there is only one youngest person (with no ties/draws) in this database, who is/are the second youngest person(s) in the database? List the id(s) of the person(s). The reference statement to this exercise is: <pre><code class="language-sql">SELECT p.id FROM person p WHERE p.year_born = (SELECT MAX(year_born) FROM person WHERE year_born &lt; (SELECT MAX(year_born) FROM person)); </code></pre> By exploring the database or trying to execute different statements, some students found that the ID of the person who met the conditions was '00000842', so the following statement was submitted. <pre><code class="language-sql">select id from person where id ='00000842'; </code></pre> The execution result of the above code was correct, but it was obviously not what the tutor expected. Therefore, we identified such statements as 'cheating'. Table 1 Description of exercises_result table. <strong>field</strong> <strong>desc</strong> <strong>datatype</strong> submission_id Submission ID INT submitted_answer statement submitted by student TEXT submission_time Submission time NUM exercise_id Exercise ID INT is_correct Mark whether the statement is correct INT student_id Student ID INT category categories of statement TEXT <em><strong>exercises_benchmark</strong></em> This datatable stores the scores provided by different assistants. We randomly selected 45 SQL statements submitted by students for each exercise, and asked three teaching assistants to grade them manually. Finally, we averaged the scores provided by the three assistants as the final score of each statement. Table 2 Description of exercises_benchmark table. <strong>Field</strong> <strong>comment</strong> <strong>datatype</strong> Submission_id Submission ID INT grade grade provided by tutor REAL tutor tutor TEXT <em><strong>exercises_exercise</strong></em> This datatable stores the exercises provided by tutor. Table 3 Description of exercises_exercise table. <strong>Field</strong> <strong>comment</strong> <strong>datatype</strong> id Exercise ID INT title Title of exercise TEXT preamble Description of exercise TEXT difficulty Coefficient of difficulty integer ref Reference statement integer <em><strong>database schema</strong></em> Please refer to db_schema.pdf for the database schema used in the experiment. <strong>BibTex</strong> if you want to cite our paper: <pre>@article{wang2020combining, title={Combining dynamic and static analysis for automated grading SQL statements}, author={Wang, Jinshui and Zhao, Yunpeng and Tang, Zhengyi and Xing, Zhenchang}, journal={J Netw Intell}, volume={5}, number={4}, pages={179--190}, year={2020} }</pre>