When decisions are increasingly driven by data, data analytics skills have become an indispensable part of any education seeking to prepare students for the modern workforce. Essential in this skill set is the ability to work with structured data using the venerable and ubiquitous SQL language as well as popular libraries heavily influenced by relational query languages, e.g., dplyr for R, DataFrame for pandas and Spark. Learning and debugging relational queries, however, pose challenges to novices: even if they have a programming background, they are often not used to thinking in terms of relational logic or operators.

This project, named HNRQ (Helping Novices Learn and Debug Relational Queries), has built a suite of powerful software tools for database educators and students alike. In an educational setting, we are often given a reference query defined by the teacher and a potentially incorrect query written by a student. First, if the two queries return different results on some test database, the RATest tool automatically constructs a small instance that illustrates the difference between the queries but is much simpler for the student to understand. Second, CInsGen finds “conditional instances,” which are abstract instances that illustrate all possible ways of how to satisfy a complex query or to differentiate two queries. Compared with concrete instances, conditional instances hide unnecessary details and articulate general conditions, making it easier to spot logical differences between queries. Third, Qr-Hint provides actionable hints to fix a working query so that it becomes semantically equivalent to the reference query. These hints purposefully guide the student through a sequence of steps that incrementally transform the working query such that it becomes correct in the end. Together, these three tools offer help that are specifically tailored to students’ individual mistakes, but do so automatically without revealing the reference query or requiring extensive personal tutoring. Finally, in settings where no reference query is known, i-Rex is a novel debugger that helps students understand SQL query evaluation and debug SQL queries. It allows students to trace query evaluation and study the lineage among input, output, and intermediate result rows. It has a “pinning” feature that focuses on relevant parts of executions to examine, as well as pagination and “teleporting” features that allow the system to reproduce relevant parts of execution without starting from the beginning, significantly improving scalability of debugging on massive databases. This project has also started to investigate challenges and opportunities posed by the rise of Generative AI to database education. Specifically, it has produced preliminary results on how to leverage large language models to help students decompose complex queries into simpler steps and describe them, and how to verify the correctness of automatically generated SQL code.

The HNRQ suite of tools have been deployed in undergraduate and graduate database courses at Duke University, benefiting more than 1,800 students during the project period, and will be continued in the future. The project has provided research experiences for learners at many levels, including one postdoctoral fellow, 4 PhD students, 6 MS students, 12 undergraduate students, and one high school student. Two alumni of the project are now Assistant Professors. In addition to the educational impact, the research carried out under the HNRQ project has also deepened the understanding of many fundamental problems in databases, resulting in numerous research papers and system demonstrations at top publication venues, 3 keynote speeches at international workshops and conferences, as well as 5 invited talks at research lab/universities.

Last Modified: 01/15/2025
Modified by: Jun Yang