Porting SQL-Tutor to the Web







Antonija Mitrovic


Phone (64) 3 3642987 extn. 7771


Kurt Hausler


Intelligent Computer Tutors Group

Computer Science Department, University of Canterbury

Private Bag 4800, Christchurch, New Zealand

Fax (64) 3 3642569







Abstract: In this paper, we describe the process of porting SQL-Tutor, an intelligent teaching system for the SQL database language, to the Web. The resulting system, SQLT-Web, observes studentsí actions and adapts to their knowledge and learning abilities. Constraint-Based Modelling is used to model students. We describe the system's architecture in comparison to the architecture of the standalone version of the system. All tutoring functions are performed on the server side, and we explain how SQLT-Web deals with multiple students. Two initial evaluation studies of SQLT-Web have been done in database courses in 1999. The students have enjoyed the systemís adaptability and found it a valuable asset to their learning.



Keywords: porting an existing ITS to the Web, architecture of a Web-enabled teaching system, student modeling, empirical study



1          Introduction

Intelligent Teaching Systems (ITS) offer the advantage of individualized instruction without the expense of one-to-one human tutoring. Although numerous ITSs have been developed to date, they are mostly used in research environments, and only a few have been used by large numbers of students in real classrooms. The main cause of such limited use of existing systems is the complexity of ITS development, and the difficulties with providing robust and flexible systems. The area is young; there are no well established methodologies or development tools. Furthermore, the hardware platforms available in most schools are not the ones developers prefer, and porting systems between platforms is in no way a straightforward task. Fortunately, Web-enabled versions of ITSs have the potential to reach a much wider audience as they face significantly fewer problems with hardware and software requirements.

We have developed SQL-Tutor, a standalone system for teaching SQL (Structured Query Language) [Mitrovic 1998a, 1998b]. The system has been used by senior computer science students at the University of Canterbury and has been found easy to use, effective and enjoyable [Mitrovic and Ohlsson, 1999]. The system has been developed in Allegro Common Lisp [Allegro 1998] and is available on MS Windows and Solaris. Besides local users, in only ten months, close to a thousand people worldwide have downloaded the Windows version of the system[1]. However, we wanted to open the system to a wider audience, and avoid problems with porting between various platforms. In this paper we present SQLT-Web, a Web-enabled version of SQL-Tutor. Web-enabled tutors offer several advantages in comparison to standalone systems. They minimize the problems of distributing software to users and hardware/software compatibility. New releases of tutors are immediately available to everyone. More importantly, students are not constrained to use specific machines in their schools, and can access Web-enabled tutors from any location and at any time.

We present the standalone version of the system firstly. The architecture of the Web-enabled version is discussed next, and is compared to other commonly used architectures. Then, we describe the features of the system that support studentsí learning and discuss how multiple students are handled simultaneously. We present our initial experiences with the system in section 4, and further research directions in the final section.

2          The standalone version

Figure 1 illustrates the architecture of SQL-Tutor. For a detailed discussion of the system, see [Mitrovic 1998] or [Mitrovic and Ohlsson 1999]; here we present only some of its features. SQL-Tutor consists of an interface, a pedagogical module which determines the timing and content of pedagogical actions, and a student modeller (CBM), which analyzes student answers. The system contains definitions of several databases, and a set of problems and the ideal solutions to them. SQL-Tutor contains no domain module. In order to check the correctness of the studentís solution, SQL-Tutor compares it to the correct solution, using domain knowledge represented in the form of constraints. It uses Constraint-Based Modeling [Ohlsson 1994] to model knowledge of its students.

Text Box:   Figure 1: Architecture of SQL-Tutor

At the beginning of a session, SQL-Tutor selects a problem for the student to work on. When the student enters a solution, the pedagogical module sends it to the student modeller, which analyzes the solution, identifies mistakes (if there are any) and updates the student model appropriately. On the basis of the student model, the pedagogical module generates an appropriate pedagogical action (i.e. feedback). When the current problem is solved, or the student requires a new problem to work on, the pedagogical module selects an appropriate problem on the basis of the student model.

3          The architecture of SQLT-Web

Several architectures for Web-enabled tutors have emerged so far. If we consider the location at which the tutoring functions are performed, three types of architectures emerge: centralized, replicated and distributed. In the centralized architecture, the Web and application servers run on the server side, while the student interface is displayed in a Web browser on the clientís machine. The application server performs all tutoring functions. The student interacts with HTML entry forms, and the information is sent to the Web server, which passes the studentís requests and actions to the application server. The server and the interface typically communicate via CGI (Common Gateway Interface) programs. Information sent by a Web browser is processed by an external CGI program, and the results are sent back in the form of new HTML pages. PAT-Online, an algebra tutor [Ritter 1997], is a Web-enabled system base don this architecture. Another option for building centralized teaching systems is to use programmable Web servers, which can be extended with the application code, thus eliminating the need for external CGI programs. This is the architecture that SQLT-Web and ELM-ART [Brusilovsky et al. 1996] are based upon.

In the replicated architecture, the entire tutor resides in a Java applet that needs to be downloaded and is executed on the studentís machine. All tutoring functions are therefore performed on the clientís machine, while the server is only used as a repository of software to be downloaded. An example is a tutor [Vassileva 1997] developed in the DCG authoring tool. In the distributed architecture, tutoring functions are distributed between the client and the server. The exact policy on distributing the functions may vary. Most often, the application server consists of a student modeler, a domain module, and a pedagogical module. The user interface is usually Java-based and may perform some teaching functions. Communication between the interface and the application server does not necessarily involve the Web server; it is possible to establish a direct TCP connection between the applet and the application server in order to speed up the system. AlgeBrain and Belvedere are based on such an architecture. AlgeBrain [Applet et al. 1999] supports students while learning to solve algebraic equations. A downloadable Java applet provides an engaging user interface involving an agent that reacts to a studentís action, and provides immediate feedback on each studentís step. Belvedere [Suthers and Jones 1997] is a system for learning scientific inquiry skills. Java is used to deliver the user interface, while the application server is written in a variety of tools.

Text Box:  
Figure 2. The architecture of SQLT-Web
The amount of effort involved in building a tutor with a replicated architecture is the same as building a standalone system. These systems are very fast, as all processing is done on the clientís machine.However, a significant limitation of this architecture is the fact that the student model is stored on the machine where the tutor has been executed. Therefore, the student always needs to use the system from the same machine if he/she wants to benefit from the summaries of previous sessions stored in the student model. One interesting solution to this problem may be found in [Vassileva 1997], where copies of student models are also kept on the server between sessions for persistent storage. Although this solution removes the requirement that a student always has to use the tutor from the same machine, there is still a problem if a network error occurs before the student completes a session, as the most recent information about studentís performance will then be lost.

In both replicated and distributed architectures, it is necessary for a student to download software in order to start using a system Ė a task that some students may find frustrating. Furthermore, it is necessary to download each new release of a tutor to benefit from the improvements. In the case of a centralized architecture, there are no such problems. A significant advantage of the centralized and distributed architectures is the fact that all student models are kept in one place (on the server) and the student can use the system from any machine. A problem with these two architectures may be the reduced speed, caused by communications between the client and the server. The situation might be better for a system with distributed architecture, as some of the tutoring actions are performed on the client side and hence the number of communications is reduced. However, communicating between the interface and the server in a distributed architecture may require special techniques, which introduces additional complexity to system development.

Starting from the standalone system, we have developed a list of requirements for a Web-enabled tutor. We wanted to maintain a centralized repository of student models and support multiple simultaneous students, thus giving students freedom to access the system at any time and any place. We also wanted to eliminate the need to download software, and therefore decided to use the centralized architecture, which fulfils all requirements. An integrated Web development environment embodied by the Common Lisp Hypermedia Server[2] (CL-HTTP) [Mallery 1994] was selected for implementing the system. We preferred this option to using CGI because of the disadvantages of the latter; when CGI is used to process user requirements, it is necessary to run a separate CGI program in response to each web request. In order to maintain consistency between various requests in a single session, it is necessary to implement a student model in an external database instead of maintaining knowledge structures in the memory. This mechanism would be too complex in a research environment, characterized by frequent changes in requirements, and therefore we decided to use CL-HTTP server, which eliminates the need for CGI.

CL-HTTP is a fully featured HTTP server developed in Common Lisp. Since the original SQL-Tutor was also implemented in Common Lisp, CL-HTTP appears to be an optimal platform. CL-HTTP supports application development by directly extending the server using Common Lisp programming. Developers may define Lisp functions to handle incoming requests, and generate HTML pages as responses. CL-HTTP is based on multi-threaded programming, and creates a separate thread to respond to each client. As several students who use the system concurrently share some components of SQLT-Web, it is necessary to introduce a locking mechanism to ensure non-interference between various sessions. The system also needs to maintain multiple student models and to associate every request to the student model of the corresponding student. We discuss how SQLT-Web supports multiple students in Section 3.2.

Figure 2 presents the architecture of SQLT-Web, which is the extension of the architecture of the standalone system. We have re-implemented the interface, introduced a session manager and extended the domain knowledge structures. At the beginning of an interaction, a student is required to enter his/her name, which is necessary in order to establish a session. The session manager records all student actions and the corresponding feedback in a log. It also requires the student modeler to retrieve the model for the student, if there is one, or to create a new model for a student who interacts with the system for the first time.

Each action a student performs in the interface is first sent to the session manager, as it has to link it to the appropriate session. Then, the action is sent to the pedagogical module, which decides how to respond to it. If the submitted action is a solution to the current problem, the pedagogical module sends it to the student modeler, which diagnoses the solution, updates the student model, and sends the result of the diagnosis back to the pedagogical module. The pedagogical module then generates feedback. If the student has requested a new problem, the pedagogical module consults the student model in order to identify the knowledge elements the student has problems with, and selects one of the predefined problems that feature identified misconceptions. Students may also ask for additional explanations, which are dealt with by the pedagogical module.

3.1         Interface

The interface of SQLT-Web, illustrated in Figure 3, has been designed to be robust, flexible, and easy to use and understand. It reduces the memory load by displaying the database schema and the text of a problem, by providing the basic structure of the query, and also by providing explanations of the elements of SQL. The main page is divided into three areas. The upper part displays the text of the problem being solved and students can remind themselves easily of the elements requested in queries. The middle part contains the clauses of the SQL SELECT statement, thus visualizing the goal structure. Students need not remember the exact keywords used and the relative order of clauses. The lowest part displays the schema of the currently chosen database. Schema visualization is very important; all database users are painfully aware of the constant need to remember table and attribute names and the corresponding semantics as well. Students can get the descriptions of databases, tables or attributes, as well as the descriptions of SQL constructs. The motivation here is to remove from the student some of the cognitive load required for checking the low-level syntax, and to enable the student to focus on higher-level, query definition problems.

When a solution is submitted, the pedagogical module generates feedback on it, offers the possibilities of working on the same problem (if there were mistakes in the studentís solution), logging off, or going on to the next problem, which may be selected by the student or the system.

3.2         Supporting multiple students

SQLT-Web maintains information about a student in his/her student model, which summarizes studentís knowledge and the history of the current and previous sessions. Initially, SQLT-Web acquires information about a student through a login screen. Individual student models are stored permanently on the server, and retrieved for each studentís session. Students who are inactive for a long period of time are automatically logged off (after 120 minutes) and their models are moved back to long term storage.

Text Box:  
Figure 3. The interface of SQLT-Web
A web-based tutor with a central repository of student models must respond to requests of individual students. The system must be able to associate each request to the appropriate student model. Some Web-enabled systems use cookies or IP numbers to identify the student who made a request. Those two approaches were not suitable in our case. It was not possible to use the IP number, as several students might be using the same machine. Also, we did not want to use cookies for identification purposes because various browsers deal with them in different ways. Instead, we identify students by their login name, which is embedded in a hidden tag of HTML forms and sent back to the server. If a student accesses a page by specifying the URL instead of accessing it through a form, then user name is appended to the end of the URL.

It is also necessary to store student-specific data separately from data about other students. All processing is carried out within a single address space, and therefore there must be a uniform mechanism for identifying students and associating requests to corresponding student models. In order to achieve this, we use a hash table that maps the string representing a student name to their student object, which contains all details pertaining to the students, such as a timestamp for automated logout, the history of the current session, the cache of the previous incorrect attempt, the feedback buffer, currently selected database and problem, etc.

Student modeler uses constraint networks [Mitrovic 1998a,b] to diagnose a studentís solution. There may be many students submitting their solutions to the system concurrently, and therefore these knowledge structures must be locked while processing a single studentís solution. Whenever a student submits a solution, the system needs to check whether the constraint networks are available (i.e., to make sure that the processing of a previous solution has been completed and the locks on the networks have been released) before the current solution can be processed.

4          Evaluation

Two studies, consisting of a two-hour lab session each, have been performed with computer science students taking database courses. In May 1999, 33 final year students have used SQLT-Web, while there were 34 second year students in the second study performed in October 1999. The students from both groups had learnt about SQL in 6 lectures and had at least eight hours of hands-on experience of query definition prior to using the system. However, the students who participated in the May study reported having more experience with SQL outside the university (45% of the group) than the students who participated in the October study (23%).

Text Box: Question	Agree	Disagree
	May	Oct	May	Oct
1. Would you recommend SQLT-Web to other students?	84	94	3	0
2. Do you find the display of the schema understandable?	93	88	3	0
Table 1. Responses (in percentages) from the user questionnaire
All students' actions were recorded and the students filled out a questionnaire at the end of the session. We report here on the subjective reponses from the questionnaires; other kinds of analyses performed on the experimental data fall outside of the scope of this paper and are reported elsewhere [Mitrovic & Suraweera, 2000, Mayo & Mitrovic, 2000].

The responses to the user questionnaire revealed that students enjoyed learning with the system and appreciated its adaptive features. The majority of students (77% in May, and 85% in October) reported that they needed less than 10 minutes to start using the system. 9% of students in both studies reported that they needed 30 minutes to learn about the systemís features. Finally, two students reported spending most of the two hours becoming familiar with the system. The students enjoyed the system (questions 1 and 4 in Tables 1 and 2[3]). Consistent with these findings, we observed that the students continued to use the system on their own after the study. The students found the interface easy to use (question 5), and appreciated having the schema of the currently selected database (question 2).

The user questionnaire contained several questions about learning. When asked to rate how much they learned from working with the system (question 3), the average ratings were 3.1 (May) and 4.1 (October). One explanation for the relatively low value obtained for the May study is that many of the students had already encountered the relevant databases and problems in their prior laboratory exercises, and therefore found no unseen problems in SQLT-Web. A new database was added to the system in time for the October study, which may have had challenged the students more.

The majority of students appreciated the exploratory, hands-on approach, learning at their own pace and found learning with SQLT-Web to be more personal than lectures. Other students commented that human input was still necessary at times. The average rating for the helpfulness of feedback (question 6) was 2.9 in May, and 4.2 in October. There were a few suggestions on how to provide additional useful information, such as connecting the system to a DBMS, so that queries can actually be run and results inspected. Text Box: Question	1	2	3	4	5
	May	Oct	May	Oct	May	Oct	May	Oct	May	Oct
3. How much did you learn about SQL from the system?	6	0	15	6	36	20	30	50	6	23
4. Did you enjoy learning with the system?	0	0	9	9	27	23	42	29	21	38
5. Do you find the interface easy to use?	0	0	6	6	24	26	54	41	15	23
6. Do you find feedback useful?	9	0	24	9	33	12	24	26	6	47
Table 2. Responses to questions based on the Likert scale (1 - not at all, 5 - very much)
Some of the suggestions soon to be implemented included requests for more examples of how to generate queries and more SQL-specific help.

5          Conclusions

The Web has introduced a new paradigm for building widely accessible intelligent teaching systems. A very important aspect of Web-based tutors is the ability to use sophisticated tools for knowledge-intensive components of systems, and develop the interfaces in platform-independent ways.

SQLT-Web is a Web-enabled system for teaching SQL. The system is an extension of a standalone system developed in Common Lisp, and we re-used its code for the Web-based extension. SQLT-Web is developed in the CL-HTTP server. It is based on a centralized architecture, where all tutoring functions are performed on the server, and the only functions performed on the clientís side are the user interaction ones. The amount of data that needs to be transferred between the client and the server in SQLT-Web is small due to the nature of the domain, and therefore the centralized architecture is feasible.

SQLT-Web has been used by computer science students in two courses in 1999, and has been found to be effective and easy to use. The majority of students appreciated the exploratory, hands-on approach, learning at their own pace and found learning with SQLT-Web to be more personal than lectures. We are currently improving the system in accordance to the studentsí comments. The plan is to open the system to the general public in the first half of 2000, which would provide a much wider basis for the evaluation of SQLT-Web. In the long term, we plan to introduce support for self-explanation, and allow students to engage in more profound types of learning.


The work presented here was supported by the University of Canterbury research grant U6242 and a Teaching Development grant.


Allegro Common Lisp (1998). Franz Inc.

Alpert, S., Singley, M., & Fairweather, P. (1999) Deploying Intelligent Tutors on the Web: an Architecture and an Example. Int. J. Artificial Intelligence in Education, 10, 183-197.

Brusilovsky, P., Schwarz, E., & Weber, G. (1996) ELM-ART: an Intelligent Tutoring System on Wolrd Wide Web. In C. Frasson, G. Gutier, A. Lesgold (eds), Intelligent Tutoring Systems, ITSí96, 261-269.

Mallery, J.C. (1994) A Common LISP Hypermedia Server. Proc 1st Int. Conf. On the World Wide Web.

Mitrovic, A. (1998a). Learning SQL with a Computerized Tutor, Proc. 29th SIGCSE Technical Symposium, 307-311.

Mitrovic, A. (1998b). A Knowledge-Based Teaching System for SQL, Proc. ED-MEDIAí98, T. Ottmann, I. Tomek (eds.), 1027-1032.

Mayo, M. & Mitrovic, A. (2000) Using a probabilistic student model to control problem difficulty, paper accepted for ITS2000.

Mitrovic, A. & Suraweera, P. (2000) Evaluating an Animated Pedagogical Agent, paper accepted for ITS2000.

Mitrovic, A., & Ohlsson, S. (1999) Evaluation of a constraint-based tutor for a database language, Int. J. Artificial Intelligence in Education, 10, 3-4, 238-256.

Ohlsson, S. (1994). Constraint-based Student Modeling. Student Modeling: the Key to Individualized Knowledge--based Instruction. Berlin: Springer-Verlag, 167-189.

Ritter, S. (1997). PAT-Online: a Model-Tracing Tutor on the World-Wide Web. P. Brusilovsky, K. Nakabayashi, S. Ritter (eds) Workshop on Intelligent Educational Systems on the World Wide Web,AI-EDí97, 11-17.

Suthers, D., & Jones, D. (1997) An Architecture for Intelligent Collaborative Educational Systems. B.de Boulay, R. Mizoguchi (eds) Proc. AI-EDí97, 55-62.

Vassileva, J. (1997) Dynamic Course Generation on the WWW. Proceedings of AIEDí97.

[1] SQL-Tutor is available for downloading from http://www.cosc.canterbury.ac.nz/~tanja/ictg.html

[2] CL-HTTP server is available from http://www.ai.mit.edu/projects/iiip/doc/cl-http/home-page.html

[3] The percentages given in the tables do not add up to 100%, as not all students answered all questions.