Constructing and animating SQL queries
Tulin T.Kilinc,
Steven Setzer,
Kennesaw State University
Problem and Motivation
Teaching Database is a subject of great importance. Besides being a core course in any CS curriculum, most projects assigned in CS courses include a significant database component and a large number of all computer science graduates find jobs that are database related. The most popular query language for most commercially available Database Management Systems is SQL. Learning SQL queries is time consuming and difficult for many computer science students. By having a tool that students can assimilate and consolidate their SQL knowledge, more database topics can be taught during a semester course.
Background and Related Literature
This project required us to study several database textbooks [1-4]. Not only to update us on some of the more complex queries, but to compare the pedagogy and obtain examples of tables that were complex enough to illustrate the SQL operation being explained and at the same time were simple enough for the student to understand and to fit nicely on the screen.
It also required to study several animation softwares that aid in teaching programming concepts. Although there were a lot of animation tools that we studied, most of them were developed for teaching Data Structures and Programming and very little has been done to aid in teaching Databases [5-14].
This project also required to study several database textbooks [1-4]. Not only to update us on some of the more complex queries, but to compare the pedagogy and obtain examples of tables that were complex enough to illustrate the SQL operation being explained and at the same time were simple enough for the student to understand and to fit nicely on the screen.
Approach and Uniqueness
Our first prototypes were developed following a rapid application development (RAD) methodology using Visual Basic 6. Weekly meetings were held with a team of faculty and students at Kennesaw State University. Once a significant amount of queries were implemented and reviewed by our team, we experimented them with student in the Introduction to Database Management Systems class and met with Dr.Navathe and his team at Georgia Tech. Currently we have elaborated a student evaluation form and we are testing it in several database classes at Kennesaw State University. Our set of prototypes allows for the students to view animations of all SQL operations that implement all Relational Algebra operations (Project, Restrict, Union, Intersection, Minus, Multiplication, Join and Division) and queries with sub-selects (not in and not equal). Currently we are implementing animations that view queries that use the set function. We are also allowing beginning to develop options for the students to construct their own animations in a limited scenario. As far as we researched, there are no other tools available that offer this functionality.
Animations of these exercises are composed of (1) a description window that describes the problem and the objectives of the program; (2) an SQL query window containing the sql query that solves the problem; (3) one or more data windows containg the tables; (4) a procedure window containing the simplest form of algorithm that implements the SQL query; (5) and an output window containing the results of the query. They also contain at least two additional buttons: a “clear” button that returns the query to the initial stage, and a “step” button that executes the highlighted line of the procedure window and highlights the next line
every time that it is clicked. Depending on the query, there will also be a Relational Algebra window, containing the corresponding Relational Algebra solution to the exercise.
Results and Contributions
Although our Visual Basic prototypes allows for the end-user to select resolution and color, the options are limited. Besides the examples are downloadable as opposed to being executable under a web-browser.
Visual Basic was chosen due to being an easy language to generate prototypes. Preliminary feedback has indicated that our prototypes are currently useful to aid an instructor in presenting SQL. Based on feedback, we have started a second version that is being developed in Java (java applets). Among the enhancements from the feedback included the addition of a problem statement window, a help screen and an option for student to construct the query (in a limited scenario). More than half of the student and faculty that tested the system has stated that the we have a helpful tool as is. All the students and faculty have agreed that the system will greatly enhance learning if the enhancements proposed are implemented. Our Visual Basic prototypes can be downloaded from To execute it, one must have Visual Basic or the Visual Basic Run-time that can be freely downloadable from the web. Our Java applets can be executed under a web-browser from
References
[1] Date, C.J. “Introduction to Database Systems”, 7th ed., Addison_Wesley, 2000.
[2] Elmasri, R. and Navathe, S. “Fundamentals of Database Systems”, 3rd ed., BC Cummings, 1999.
[3] O’Neil, P. Database: Principles, Programming, Performance, Morgan-Haufman, 1995.
[4] McFadden, Fred R.; Hoffer, Jeffrey A.; Prescott, Mary B. “Modern Database Management.”. 5th edition, McGraw-Hill, 1998.
[5] The Complete Collection of Algorithm Animations,
[6] Algorithm Animation,
[7]
[8] Interactive Data Structure Visualizations,
[9] Computer Science at SUNY Brockport - Sorting Algothims,
[10] Algorithm Animation.
[11] Sorting Algorithms,
[12] The Sorting Algorithm Demo (1.1),
[13] Sorting Algorithms,