Automated SQL Query Evaluations in Massive Database Courses - Brkic, Ljiljana; Mekterovic, Igor; Fertalj, Melita (2024 47th MIPRO ICT and Electronics Convention (MIPRO), 2024)
When assessing submissions in a massive course, using an Automated Programming Assessment System (APAS), can benefit both students and teachers. Students can expect fast and consistent assessment, while teachers benefit from a reduced workload. Acquiring proficiency in SQL is one of the core goals of any introductory or advanced database course. Evaluation of students' SQL queries differs from a general-purpose code evaluation, such as that for C or Java, by requiring a database on which the query will be evaluated and parameterized comparisons of the obtained recordsets. The evaluation using APAS is typically performed in such a way that the system executes two queries: the student query and the correct query provided by the course staff and compares the resulting datasets in terms of accuracy and completeness. When comparing the obtained datasets, there are a number of factors to consider, including the importance of tuple ordering and the relevance of column names. Moreover, some SQL statements create, alter, or delete database objects such as tables and indexes, and their correctness cannot be determined using a predefined output dataset. In order to do so, every SQL question in APAS must reference some test database (populated with data). With hundreds of students enrolled, it becomes technically challenging to execute and evaluate their queries in real time, especially as these databases begin to pile up due to courses development and evolution. In this paper, we comment on a possible solution and present our approach with APAS that uses multiple cloned instances of the test database while supporting the aforementioned specifics of SQL query evaluation.