Learning Analytics Solution for Monitoring and Analyzing the Students’

Behavior in SQL Lab Work

Abdelkader Ouared

, Moussa Amrani

and Pierre-Yves Schobbens

Faculty of Computer Science / NaDI, University of Namur, Rue Grandgagnage, Namur 5000, Belgium

Keywords:

SQL Language Learning, Database, Learning Analytics, Learning Analytics Dashboard, Students’ Behavior.

Abstract:

Computer-assisted learning is widely discussed in the literature to aid the comprehension of SQL queries

(Structured Query Language) in higher education. However, it is difﬁcult for educators/instructors to track,

monitor and analyze students’ learning situation due to the higher education massiﬁcation, and institutions with

large classes. Consequently, we need to provide for educators a learning dashboard to monitor and analyze

the digital traces issued from students during the practice learning in SQL course. We propose a system

called LSQL (Learning Analytics for SQL) that is a solution based on the learning analytics’ methodology.

To this end, we propose (i) learning environment dedicated to help students understand the syntax and logic

of SQL and getting data issued from these students during online SQL lab work, (ii) trace model which is

designed to more effectively represent and capture the complex interactions/actions carried by a student during

practice learning activities in virtual/remote laboratories, and (iii) learning analytics dashboard for educators

to visualize the statistics and metrics that represent the students’ behavior, and control the students progress in

SQL skills to enhance the teaching activities. Tool support is fully available.

1 INTRODUCTION

The follow-up of the students during the SQL lab

work in virtual and remote laboratories are an impor-

tant element in evaluation techniques to judge their

learning progress by educators/instructors (Venant

et al., 2016; Pfeiffer et al., 2020). The learning analyt-

ics’ methodology (LA) aims to understand the student

behaviour by collecting the data from digital tools

(e.g. the keyboard/mouse/screen actions or camera),

processed and analyzed this data relating to students

and their environment (Siemens and Gasevic, 2012).

By exploring the literature, seminal work have pro-

posed a computer-assisted learning to teaching SQL

in higher education (e.g. (Mitrovic, 2003) (Ahadi

et al., 2016) (Ahadi et al., 2015) (Brass and Goldberg,

2006) (Taipalus et al., 2018) (Karimzadeh and Jamil,

2022)). In our study, we will focus on the monitor-

ing/analyzing students during the practice learning (or

laboratory work) in SQL course that is a starting point

for several purpose such as, the errors analysis, analy-

sis of students’ results, help to discover which parts of

https://orcid.org/0000-0003-1837-832X

https://orcid.org/0000-0002-6987-1037

https://orcid.org/0000-0001-8677-4485

SQL material students are having trouble with, what

should be improved or explained more clearly, also to

optimize SQL education materials.

1.1 Problem Statement

In SQL practice learning in virtual/remote laborato-

ries, the student tries to formulate a query in SQL

based on a given natural language (NL) queries with

relational database schema. During the SQL lab, the

student performs a set of solution attempts to write

the correct query in SQL. However, monitoring and

analyzing the student’s activities is difﬁcult and an

increasingly harder challenge. First, this due to the

large number of students in the lab room, especially

in the context of Higher Education (HE), with fol-

lowing difﬁculty of each one individually, knowing

who is doing what and when, and difﬁculty in assess-

ing quantitatively and qualitatively the students’ effort

without exploring the solution attempts details. Sec-

ond, analyzing students’ writing SQL style and their

behavior during SQL problem solving requires mon-

itoring them in each solution attempts how they de-

compose the problem into a small and a sequencing

problem. Third, the lack of a tailored trace model to

handle the digital traces of training SQL activities for

184

Ouared, A., Amrani, M. and Schobbens, P.

Learning Analytics Solution for Monitoring and Analyzing the Students’ Behavior in SQL Lab Work.

DOI: 10.5220/0011848200003470

In Proceedings of the 15th International Conference on Computer Supported Education (CSEDU 2023) - Volume 2, pages 184-195

ISBN: 978-989-758-641-5; ISSN: 2184-5026

 2023 by SCITEPRESS – Science and Technology Publications, Lda. Under CC license (CC BY-NC-ND 4.0)

later analysis. Consequently, to analyze and moni-

tor the students’ behavior during SQL problem solv-

ing, we need to capture students’ interactions with the

measurements and metrics related to the learning pro-

cess to understand the academic performance of stu-

dents relative to SQL skills.

1.2 Our Contribution

As we said before, we need to monitor and analyze

data logs, that is the starting point of any learning

analytic solution. Despite event logs capturing be-

havioral information for large numbers of students,

this information must be represented explicitly in a

process model in order to propose an automatic ap-

proach for understanding the progress of students in

SQL lab to enhance the teaching activities. To address

this, in this paper, we propose a system called LSQL

(Learning Analytics for SQL), a learning analytic so-

lution allowing educators to monitor and analyze dig-

ital practices of the practice learning in SQL course.

To support these needs, LSQL provides: (i) learning

environment for helping students learn SQL and as-

sisting them in the laboratory during query formu-

lation in relational DBMSs (Database Management

Systems), (ii) trace model to represent actions carried

by a student on the SQL learning environment, com-

prises objects affected, and the time when the action

was performed, and (iii) learning analytics dashboard

(LAD) to support many visualization of the traces is-

sued from these students which allows educators to

track and understand student progress based on a set

of pedagogical indicators (i.e. learning behavior indi-

cators and content progress indicators) to enhance the

SQL teaching activities.

1.3 Roadmap

We start in Section 2 by motivating example and re-

lated Work. In Section 2.1 we present a motivation

scenario of a SQL learning to highlight the usefulness

to bring educators and support staff back in control of

the students during practical activities through a data-

driven analytics solutions. We also in the Section 2.2

we present the related work. In Section 3, we intro-

duce our system to monitor and analyze students be-

havior in SQL lab work. Then, in Section 4, we detail

our tool support. Finally, we conclude in Section 5.

2 MOTIVATING EXAMPLE AND

RELATED WORK

2.1 Motivating Example

Consider a scenario of a student cognitively engag-

ing during a practical activity, she/he wants to write

the correct query in SQL depending on a database

schema hosted in a relational DBMSs (i.e. translate

NL queries to SQL). Figure 1 shows the different at-

tempts made by the student to come up with the cor-

rect query. In Figure 1, we can see an active win-

dow of history trace that is the complete record of all

changes that have occurred during the query formula-

tion. The trace is composed of four SQL query states

(A,B,C, and D), in which the steps of the query exe-

cution generate one or more errors types. One query

may contain a number of errors comes from various

categories (Ahadi et al., 2016; Taipalus et al., 2018),

it can be (i) syntax errors (i.e. occurs when the SQL

grammar are violated) like specifying an invalid state-

ment order, (ii) semantic errors (i.e. occurs when

statements has no meaning) like table or view does

not exist in the database, (iii) logical errors or analyz-

ing errors, which causes a query to produce incorrect

or undesired output, like the choice of table involved

in the join operation and using the grouping functions,

and (iv) complications like unnecessary join.

The steps from the ﬁrst query Q

to the last query

represents the students’ traceability. Q

contains

two syntax errors (undeﬁned column and missing OR

operator), Q

contains a semantic error (OR instead

of AND), Q

, however, does not contain a semantic

error, but it contains a logical error depends on the

data fetched associated with the Q

(missing join).

contains also a logical error (i.e.exraneous OR-

DER BY clause) that produces incorrect result tables.

While, in practice the execution traces can take

various forms (e.g. logs, list of events, method calls),

we consider in this work a trace all students interac-

tions that is a sequence of SQL subqueries and steps

responsible for the query formulation changes. For

each solution attempt, the student can made an error

which may be syntactical, semantic or logical error.

However, If the instructor only analyzed the last at-

tempt (Q

), in this situation it is difﬁcult to perceive

the history of attempts to check the types of mistakes

made by the student and quantify the students’ effort

during the practical activity sessions. So, we need to

identify trace related to SQL practice learning activ-

ities among exercises and analyze the data related to

the different classes of errors.

Usually, the student solves SQL problems by

breaking them down into smaller a small and a se-

Learning Analytics Solution for Monitoring and Analyzing the Students’ Behavior in SQL Lab Work

185

attempt(a1)

attempt(a2)

attempt(a3

)

Big Change

1 3 4 5

SQL E rr or

So lut ion

at tempt

Actions

un defin ed col umn

OR in stead of AND

mi ssi ng join

exra neous OR DER BY cl ause

Change 1

Change 2

Change 3

mi ssing OR operator

Start

End

SELECT COUNT(*) , firstname

FROM title t, movie_companies mc

WHEREt.id = mc.movie_id AND

t.production_year > 2010

SELECT COUNT(*) , name

FROM title t, movie_companies mc

WHERE t.id = mc.movie_id AND

t.production_year > 2010

AND mc.company_id = 5

SELECT COUNT(*) , name

FROM title t, movie_companies mc

WHERE t.id = mc.movie_id AND

t.production_year > 2010

OR mc.company_id = 5

AND mc.company_years = 2000

SELECT COUNT(*) , name

FROM title t, movie_companies mc

WHERE t.id = mc.movie_id AND

t.production_year > 2010

OR mc.company_id = 5

AND mc.company_years = 2000

GROUP BY name

Figure 1: Scenario student during SQL query formulation, annotated with intermediate steps (student’s attempts to write a

SQL), and errors associated with each attempt to solve a single query.

quencing problem (Qian, 2018). To analyze and mon-

itor the students’ behavior during SQL problem solv-

ing, we need to capture students’ actions with its cor-

responding sub-problems SQL formulation, to under-

stand how before and after a choice of a set of steps,

operation/traces affects the ﬁnal result.

The change on the query state is identiﬁed when

a user tests the input query based on the SQL query

signature (e.g. tables involved in the query, relations

used in the join operation, selection/join predicates

used). For example, in the scenario presented in Fig-

ure 1, the educator could track errors related to query

states (A,B,C, and D) to get a mental image about a

particular student behavior and the overall students.

We believe providing a history of SQL learning ac-

tivities in more granularity as small changes instead

of allowing instructors to more understand and an-

alyze the students’ behavior during SQL lab work.

Furthermore, this analytic evaluation must be based

on a set of indicators to understand the students’ be-

haviors from their digital learning activities. Usu-

ally in LA, they considered measurements and met-

rics related to the learning process that are calculated

from the data captured: learning behavior indicators

(e.g. timing of starting activities, timing of completing

activities) and content progress indicators (e.g. com-

pleted course activities, current course grade, com-

pleted graded assignments) (Jivet et al., 2020).

2.2 Related Work

As we said before, there has been a many efforts to

assess student understanding of SQL in computer sci-

ence education research. Previous SQL research stud-

ies mainly focuses on one of two content areas. First,

on the development and analysis of a particular tool

for facilitating SQL learning, and second, on the study

of student errors in SQL.

In (Brusilovsky et al., 2010; Mitrovic, 2003;

Karimzadeh and Jamil, 2022), the authors provide in-

telligent tutors to help teach SQL without the instruc-

tor’s intervention. In the same direction, authors in

(Xu et al., 2022) propose Intelligent Tutoring System

(ITS) that automatically predicts difﬁculty of SQL

programming problems (Xu et al., 2022). Many re-

cent research papers start studying the common errors

and pitfalls that people run into while trying to learn

to write queries in SQL (Ahadi et al., 2016; Ahadi

et al., 2015; Brass and Goldberg, 2006; Taipalus

et al., 2018). In (Taipalus and Per

a, 2019), the au-

thors explored which errors are persistent, the results

show that syntax and semantic errors are less likely

to persist than logical errors and complications. The

study of (Brass and Goldberg, 2006) attempts to pre-

sented an extensive list of semantic errors to be used

in Database Management System (DBMS) compilers,

and a set of studies (Ahadi et al., 2016), which in-

spired us to this research, explored the frequencies

of syntax and semantic errors students made when

learning SQL. At the same time, several studies pro-

pose solution to enhance the SQL learning such as

ScaffoldSQL for student learning reducing students’

cognitive load towards solving SQL queries, provid-

ing immediate feedback for students, and help educa-

tors of ﬂipped classrooms identify students who are

struggling early (Borchert, 2021; Borchert and Walia,

2022) and gamiﬁcation to improve student motiva-

tion and learning (Deterding et al., 2011; Thom et al.,

2012; Fitz-Walter et al., 2011; Narasareddygari et al.,

2019). Similarly, (Tuparov and Tuparova, 2021) a

pilot study of the implementation of gamiﬁed self-

training and self-assessment in an online SQL course

as solution using Moodle

-based LMS practices. The

authors exploit the generated digital traces expressed

in xAPI

that would be interesting to collected and an-

alyzed to motivate students to attend pure online ac-

tivities in the educational process to improve learning

and help educational stakeholders in their decision-

making. In another line of research, the authors of

(Godinez and Jamil, 2019) addressed the case of ad-

vancing NLP-based voice querying. By having an in-

teractive spoken dialogue interface for querying rela-

tional databases, they were able to build a functional

learning management system for SQL that has the po-

tential to support the classes of queries novice SQL

A learning management system (LMS) is a software

application or web-based technology used to plan, imple-

ment and assess a speciﬁc learning process.

http://experienceapi.com/overview

CSEDU 2023 - 15th International Conference on Computer Supported Education

186

students encounter frequently (Godinez and Jamil,

2019). In a similar trend, the authors in (Tur

ınek

and Farana, 2022) present a database and SQL mi-

crolearning course that offers students the opportunity

to more easily absorb through small chunks that usu-

ally last no longer than a few minutes. In (Ouared and

Chadli, 2021) the authors present the usefulness of

domain-speciﬁc languages in education of SQL query

optimizer.

By exploring the literature, to the best of our

knowledge, there is no work propose a dedicated

model trace in order to capture students’ writing SQL

style and their behavior, this model will be serve as

a starting point for more comprehensive students’ be-

havior using learning analytic methodology. Similar

efforts have been conducted using learning analytics

to analyze the students’ behavior in programming lan-

guage courses (e.g. (Fu et al., 2017; Chaparro et al.,

2021)).

3 OUR SYSTEM

Figure 2 shows an overall view of our proposed solu-

tion. Our system LSQL (Learning Analytics for SQL)

starts by storing data from the students’ interactions

(i.e. event log) during the SQL query formulation for

later analysis. Thus, LSQL offers constructive feed-

back in incremental way to help students to spot the

errors and solving SQL queries.

The trace represents the history of the necessary

data for the calculation of the indicator such as, the

actions related to the SQL learning activities, and user

statistics (i.e. input users, click action sequences, op-

tions[choice], browse SQL education materials). On

the other hand, educators use a learning analytics

dashboard (LAD) to track students from the collected

data to identify and monitor the important indica-

tor related to SQL learning that reﬂects the student’s

behavior and understanding progress from the event

log. Based on these pedagogical indicators, educa-

tors identify error types, and understand the progress

of their students in the knowledge of SQL queries in

order to enhance the teaching activities.

3.1 Trace of SQL Learning Activity

As we said previously, data logs can be extracted from

different student’s actions (i.e. input user, click ac-

tion sequences, options[choice], browse SQL educa-

tion materials). It consists of a set of the required dig-

ital practices of SQL practice learning in virtual/re-

mote laboratories. A digital practice is deﬁned as a

set of events. Each event involves several attributes

Students

(learners)

Instructors

Monitor

SQL digital practices

Hints

SQL Errors

question

Answer

SQL Query

Formulation

Data

Collection

Error

identification

Actions

SQL Lab Work

Database server

Historical results

Dashboard

attempts

progress

errors types

behavior

DB Relational

Schema

NL question & Query

observe

get/set

store

How to optimize SQL

education materials ?

SQL

Activity

Attempt

Digital

traces

Indicator

SQL trace

Model

Input for

Figure 2: High-level view of the system architecture.

such as: the query input, query rewrite, query test,

and report errors generated by DBMS when execute

an SQL query.

SQL query problems solving are very popular in

many application domains, and often tackled by de-

composing them into a small problem and a sequenc-

ing subproblems. Student actions sequencing are not

independent subproblems of SQL formulation, since

ﬁnding the basic building blocks that are introduced

and composed to realise complex queries. To perceive

these digital traces related to the practice learning in

SQL course, we must construct a SQL learning trace

to handle the data log entries during the learning ac-

tivities (Figure 3).

The change is identiﬁed when a user test the in-

put query based on the SQL query signature that is

expressed in the class SQLQuery (Figure 3). This

class describe the number of tables involved in the

query, multiple tables used in the join operation, join

and selection predicates used in the query, number of

equality selection predicates, number of non-equality

selection predicates, number of equijoin predicates,

number of non-equijoin predicates, number of sort

columns (ORDER BY clause), aggregate functions

(GROUP BY with HAVING clause) used , and num-

ber of nested subqueries.

To handle the digital traces of training SQL activ-

ities, we have propose a SQL trace constructor based

on a set of operations. These latter provide the solu-

tion path that reﬂect the students’ writing SQL style

and their behavior aspects during SQL problem solv-

ing. Figure 4 visualizes the main operations required

for students’ trace construction.

• initializeQuery: initialize the ﬁrst SQL query in-

troduced by a student as ﬁrst attempt in the activ-

ity log.

• addStudentAction: add a student action op-

eration in the event trace if query state creat-

ed/deleted or changed by a student.

Learning Analytics Solution for Monitoring and Analyzing the Students’ Behavior in SQL Lab Work

187

Figure 3: Excerpt of SQL trace model.

• addSQLSmallChange: add a small change in the

SQL trace.

• bigSQLChangeStarted: means that a big change

has started on the initial query signature.

• bigSQLChangeEnded: means that a big step has

ended in the last attempt.

We remind you that we want to know the stu-

dents behavior during SQL lab work. As illus-

trated in our motivating example (Figure 2 of Sec-

tion 1), a big step during SQL formulation is sim-

ply a sequence of small steps, we only need to cap-

ture query states with its error type before and af-

ter small steps. However, we also need to capture

when steps occur, hence addSQLSmallChange must

be called at small steps, while bigSQLChangeStarted

and bigSQLChangeEnded must be called before and

after a big step, respectively.

In summary, all the solution attempts required to

construct the trace to represent actions carried by a

student during practice learning activities. Figure 5

shows the construction process as follows: stay tuned

to check if a student do an action (cf.

⃝), if a student

introduce an action (e.g. input query, test, choice),

our system execute the operation initializeQuery to

initialize the ﬁrst attempt in the activity log and and

annotate the bigSQLChangeStarted (cf.

⃝), then, its

analyze change in solution attempt, for that, it start

by identify the traced objects affected (i.e. tables, at-

tributes, functions, predicates) by changing based on

SQL query signature (cf.

⃝), based on the object af-

fected by students’ actions, our system identify the

kind change in terms of SmallChange and BigChange

related to solution attempts (cf.

⃝), after based on

query testing, our system identify the SQL class er-

ror with its category corresponding to the solution at-

tempts (cf.

⃝). At the last attempt, a big step has

ended in the last attempt, i.e. bigSQLChangeEnded

(cf.

⃝).

3.2 Traceability and Constructive

Feedbacks

The collected data through our SQL trace construc-

tor are persisted and exploited graphically to repre-

sent attempts, type of errors, students’ behavior and

progress. We based on the classiﬁcation proposed

by (Taipalus et al., 2018) for identifying various cat-

egories of SQL query such as syntax errors, logical

errors, semantic errors and complications (see Figure

6). This identiﬁcation is based on DBMS error codes

and underlying reasons, for example in PostgresQL

DBMS, the error code 42601 means ”wrong syntax”.

The category of errors gives the possibility to clas-

sify each attempt of the student in a category. In or-

der to help the student to identify the cause of any er-

rors and increase the interactivity with our system, we

https://www.postgresql.org/

CSEDU 2023 - 15th International Conference on Computer Supported Education

188

bigSQLChangeStarted:

bigSQLChangeEnded

Data log

initializeQuery

SELECT .. FROM…

WHERE

addSQLSmallChange

Adding selection predicates

Adding table with its join

predicates

Adding GROUP BY with

HAVING clause

addStudentAction

SELECT .. FROM…

WHERE

Adding selection predicates

Adding table with its join predicates

Adding GROUP BY with HAVING

clause

addSQLSmallChange

Figure 4: Illustration of operations of the SQL trace constructor.

Does a learner do

action ?

Stay tuned

Query

State

Generate

values

sequence

Traced

Object

SQL log

entries

Identify

the change

BigChange

SmallChange

Change

Identify

Error type

Does the query

correct ?

Attempt step

Change analysis in solution attempt

①

②

③

④

⑤

⑥

Figure 5: Process to construct the trace of actions carried by a student during training activities.

Figure 6: Error classes and their error categories, and complications with an exemplary query in SQL query formulation.

have proposed an algorithm to obtain an immediate

answer as hints, or constructive feedbacks (see Figure

7). If a query contains an error, a guidance through

incremental feedback (hint) is dedicated to guide the

student to spot the SQL errors. The hints are applied

based on the error type and the number of solution

attempts. Table 1 shows a constructive feedbacks in

incremental way with its corresponding level.

Learning Analytics Solution for Monitoring and Analyzing the Students’ Behavior in SQL Lab Work

189

• Level 1: provides hints expressed in general state-

ment at high level of abstraction, in which stu-

dents must cognitively engaging with these expla-

nations.

• Level 2: expresses in speciﬁc statement with vo-

cabulary depending on SQL language query, with

more explanation than the level 1, as guidance for

students.

• Level 3: provides hint contains the error type by

comprehensible explanations than the level 2 to

spot the error type in the current query.

We formalized the application of the hints as ECA

type (Event, Condition -Action) using the tables that

are deﬁned as follows:

• ActivityLog (LogID, TracedObject, currentStep, windowSize, de-

scription, location)

• SolutionAttempt (attemptID, eventType, objectAffected, action-

Perf, QueryEvaluation, SQLError, description)

• Action (actionID, actionType, time, event, prevAction, postAction)

• Change (changeID, object, SmallChange, BigChange,

bigSQLChangeStarted, bigSQLChangeEnded)

So, here’s an example of hint application.

Example of hint application: Use hint contains the error type (level 3).

- When: Logical Errors (Event).

- When: Query SQL contain Join error, [not updating](Event).

- Where: [always] Join on multiple relations (Condition).

- What: join on incorrect table (Action).

3.2.1 Analyze Students’ Behavior

Analyzing students’ behavior that makes attempts to

solve the given NL query during the learning activ-

ity can be traced as a path using the tree structure at

a detailed level of granularity according to the edu-

cator’s requirements. When the educator zooming on

the behavior or content progress indicators of a stu-

dent, he/she can see more information about the types

of actions he/she makes and we can ﬁlter actions and

activity timeline for a speciﬁc student. These indi-

cates a status in different aspects of the student’s be-

havior ( e.g. Engagement in the SQL lab work, Tim-

ing of starting activities, Timing of completing activ-

ities) thereby providing a global overview of the stu-

dent.

Analyzing students’ behavior is a process com-

posed by a set of steps that must be applied to the

data sources in order to understand his/her behavior

from the performance of learning activity. With the

aim of guiding educators to analyze students’ behav-

ior, we propose a tree structure, the aspects to be an-

alyzed are represented as nodes to drive user to the

next analysis. The ﬁrst step is selecting the aspect that

will be analyzed, the leaf nodes represent the possi-

ble metrics and measurement that would be useful for

the educator. Figure 8 shows an example of to an-

alyze students’ behavior in SQL lab work, in which

the students’ interactions compose the path leading

to understanding the student behaviour. Thanks to

SQL model traces of learning activities (i.e. opera-

tion, small, and big before and after change with the

corresponding feedback), the educators can track and

analyze students behavior step by step by checking

the logic leading to the ﬁnal query proposed by stu-

dents. This structure indicates attempts made by the

student with their results, and their digital learning ac-

tivities using measurements and metrics of the learn-

ing process such as the learning behavior indicators

(e.g. timing of starting activities, timing of complet-

ing activities, user statistics) and content progress in-

dicators (e.g. completed learning activities, success

rate, error rate of wrong answer, and the type of er-

rors). On the other hand, this trace helps to see how

the student behaves and identify the difﬁculty of SQL

writing problems.

4 TOOL SUPPORT

Our system has been implemented with MVC (Model

View Controller) framework, using Java FX with

PostgreSQL

, available on Github

. A prototype of

our system was developed to be used by the second-

year Bachelor’s program at the computer science de-

partment. Our tool support contains two parts:

• SQL learning environment Side: the ﬁrst part

shows the learning environment dedicated for

SQL self-training in undergraduate database

courses.

• Learning analytics dashboard Side: the second

part provides the learning analytics dashboard for

educators and the overall tendencies of learning

behaviors.

The usage scenario of LSQL is organized as it is

shown in Figure 9. In the following we detail it step

by step.

4.1 SQL Learning Environment Side

Our tool is deployed using three layered components,

including presentation and access, compuation and

storage. A student connects to a database server layer

using a TCP/IP local-area network (LAN) with no or

https://www.postgresql.org/

https://github.com/OUARED-A/LSQL

CSEDU 2023 - 15th International Conference on Computer Supported Education

190

Figure 7: constructive feedbacks in incremental way with its corresponding levels.

Table 1: The three level of SQL hints.

Hint Level Description SQL Syntax Error Example

Level 1 Hint is expressed in general statement Wrong syntax

Level 2 Hint is expressed in speciﬁc statement Invalid pedicat syntax

Level 3 Hint contains the error type Argument of AND must be type Boolean

Analyze a students’ behavior in SQL lab work

by browsing measurements/metrics of the practice learning process

Correct SQL query in the ﬁrst attempt

Timing of completing activities: 10 minutes

End analysis

Showing the summarizing of attempts

Showing content progress indicators ...

....

Showing the summarizing of error

Syntax error: 2

Semantic error: 3

Logical error: 17

End analysis

Filtering logical error

Operator error: 2

Join error: 12

Expression error: 3

End analysis Getting details ”Join error”

Join on incorrect table: 2

Join when join needs to be omitted: 5

Join on incorrect column: 0

Join with incorrect comparison operator: 2

Missing join: 1

End analysis

Number of attempts submitted:20

Number of logged accesses:2

Zooming on time spent by a student

Number of attempts submitted:20

Average time spent between attempts: 5 minutes

Number of attempts that take too long time: 12

Number of attempts that not succeed: 15

End analysis

Timing of starting activities

Number of logged accesses:2

10 Feb 05:45 2022: logged in

10 Feb 06:55 2022: logged out

11 Feb 16:00 2022: logged in

11 Feb 18:37 2022: logged out

End analysis

Figure 8: Example of analyze students’ behavior from to the learning activities history.

internet connections. The choice of LAN network

is motivated by conducting a fairer evaluation of the

groups during SQL lab work to identify students cog-

nitively engaging during a practical activity, and avoid

students that submit reports copied from others.

In Figure 10a, the student uses the module re-

sponsible for gives information about his/here iden-

tity, and the connection establishment with the DBMS

server. Users use this interface to introduce connec-

tion parameters, i.e. server name, DBMS, database

name, destination port, IP address, user, and pass-

word. When connected, our system provides a sim-

ple and and intuitive user interface to build a query

and to aid SQL understanding as shown in the Figure

10b. A student starts by selecting an exercise from

the repository of questions or exercises. Each exer-

cise presented by a relational database with a set of

queries expressed in natural language (NL). Through

Learning Analytics Solution for Monitoring and Analyzing the Students’ Behavior in SQL Lab Work

191

SQL material

SQL learning environment Side

Learning analytics dashboard Side

Students

SQL

reading

Logs

Database Server

Analytics

SQL Queries

Logs

Browse metrics of the

practice learning process

Improve SQL

Education

materials

Practice

learning

Logs

Get a set of pedagogical

indicators (i.e. learning

behavior, content progress )

Educator

Loop

Hints

LAD LSQL

Figure 9: Conceptual processes of LSQL components.

this user interface, the user has ﬁrst to select the

database schema, and NL query to be translated in

SQL language (Figure 10b) and check the correctness

of the solution. Note that every change made to the

user interface is stored using our SQL trace model.

The student introduces the SQL query and executes

his/here query to receive the result table or error mes-

sages with some improvement hints. This interface il-

lustrated in Figure 10b includes the constructive feed-

backs process to help when a student makes differ-

ent errors among incorrect ﬁnal solutions after a num-

ber of attempts. This process sends hints as feedback

without direct instructor intervention when an answer

was considered incorrect.

4.2 Learning Analytics Dashboard Side

For educators, our tool supports provide a learning an-

alytics dashboard to more understand visual percep-

tion and perceive visual changes states to each stu-

dent. Educators use their account to browse the learn-

ing progress in SQL knowledge and behavioral ten-

dencies were shown from charts and events logs. The

detailed functions of the dashboard are described in

the following subsections.

4.2.1 Interface for Reporting the Students’

Activities

Our dashboard shows an overview of a given student

activity on his/her proﬁle (Figure 11). It gives viewers

more detailed information about the digital practices

of the lab work per student and actions types he/she

makes. Figure 11a shows a graphical user dedicated

for educators to track the learning behaviour of digital

practices. This interface offers a wide possibility to

monitor the performance of students relative to SQL

skills, also allow educators to have reports of their ac-

tivity in a timely manner and a human understandable

way. Moreover, our LAD provides a students list pro-

viding correct responses with a good response time

to spot these students since the number of students is

very high.

The errors type of a student during the lab work in-

dicate a status in different aspects of the students’ be-

havior thereby providing a global overview of the stu-

dent. When they select a given student, our tool gives

an overview section to show all actions performed by

this student with its evaluation: true, false (see Figure

11b).

4.2.2 SQL Error Analysis

To monitor the students’ learning activity during the

sessions of practical work, our LAD reports con-

textual information and activities related to a given

course to help educators identify and understand the

common mistakes that students run into while try-

ing to learn to write SQL queries. Identifying the

most common SQL mistakes can also help educator

discover which parts of material students are having

trouble with, what should be improved or explained

more clearly, how to optimize SQL education materi-

als, etc.

The visualization offered by our LAD will facil-

itate instructors analyzing students’ behavior, as it is

shown in Figure 12, our dashboard shows the actual

state of difﬁculties in SQL skills. The dashboard con-

tains two four parts: the top-left part indicates the var-

ious SQL errors for a selected student to better under-

stand each student’s writing SQL style and behavior

(Fig. 12a), this chart allows us to characterize stu-

dents’ weaknesses in their understanding of SQL ma-

terial and reﬂects students’ progression, the top-right

part indicates total number of solution attempts (Fig.

12b), the down-left part indicates the traceability of

SQL questions (Fig. 12c), and the down-right part in-

dicates total number of errors for each question using

a bar chart to show the error distribution by class (i.e.

syntax, semantic and logic) based on the log data pro-

duced by students’ interactions (Fig. 12d).

Using these visualizations, any change in a stu-

dent’s learning behavior reﬂects a change in his/her

reporting state. This change in behavior is caused

by providing constructive feedback for students to re-

duce students’ cognitive load towards solving SQL

queries and reducing instructor intervention.

4.2.3 Timing of Starting Activities

Another factor that must be considered to analyze the

students behavior aspects, is to examine the timing of

starting activities. In the context of SQL lab work,

the repetition and the longest in activity are indicators

to perceive the students’ efforts. A repetition usually

leads to an improvement of exercise results and in-

CSEDU 2023 - 15th International Conference on Computer Supported Education

192

(a) System connection (b) Interactive interface of SQL practice learning

Figure 10: The LSQL learning environment.

(a) Digital practices of the Lab Work per student (b) Detail trace of digital practices of a selected student

Figure 11: Analyzing the digital practices of students in the lab work.

(a) Number of error for each question for a selected student (b) SQL error distribution.

Figure 12: Situation of different students upon trying to run SQL query.

Learning Analytics Solution for Monitoring and Analyzing the Students’ Behavior in SQL Lab Work

193

(a) Logged in/logged out statistics (b) Student’s active time

Figure 13: A student’s detailed timing of starting activities.

creased success rates at the last attempt. Our dash-

board provides information about the timing of start-

ing activities to qualitatively appreciate the students’

activities during a learning session (Figure 13).

This aspect appears in logged in/logged out statis-

tics by student as shown in Figure 13a, and the stu-

dent’s active time as shown in ﬁgure 13b (active, not

active). When educators look at a student active with

a set of actions performed during SQL practicing,

they can appreciate the effort of the students during

the learning activity session. With this chart, we can

easily detect the active and inactive students, and can

help to conduct a fairer evaluation of the groups dur-

ing SQL lab work. For example, the student gener-

ates a lot of log data by keeping trying to test a query

with many solution attempts that can be interpreted

as really engaged during lab work. In contrast, the

student not active can be interpreted as not engaged

with SQL, also, a pause period may be interpreted as

a student taking a large thinking period without inter-

actions. Furthermore, tutors can check if the timing

of starting activities has negative or positive correla-

tion with the performance of students’ progress and

understanding (e.g. completing activities, errors num-

ber). However, the educators can switch between the

visualization, and cross all this visualization to link

students’ behavior aspects aspects with their digital

learning activities to get a state about each student and

a big picture about the overall students.

5 CONCLUSION

In this paper, we have tackled the problem of eliciting

and analyzing students’ behavior during the practice

learning in SQL course in virtual/remote laboratories.

This work is motivated by the difﬁculty of the evalua-

tion due the higher education massiﬁcation, and insti-

tutions with large classes. We have presented a learn-

ing analytics solution for monitoring and analyzing

students behavior in SQL lab work. Behaviour ana-

lytics is driven by an automatic process to capture a

student’s activities from an event log that is purposely

designed for any relational DBMS. The proposed ap-

proach is useful for helping students learn SQL and

assisting them when formulating SQL queries, (ii)

collecting digital traces before and after each small

and big step performed by students, and (iii) learn-

ing analytics dashboard to support reporting the traces

issued from these students which allows educators

to track and understand student progress in the SQL

skills to enhance their teaching activities. The trace

model contains objects with their attributes, which

represent the main entities involved in the process

of solving query SQL, and the relationships between

the small steps performed by the student to achieve

the global query. We have considered the state be-

fore and after each execution step in the event log

and represent them via the learning analytics dash-

board. Two important directions for future works can

be considered. First, we will conducted some form of

exploratory statistical analysis, such as correlational

analysis, regression, or factor analysis. Second, ap-

plied machine learning techniques to obtain predic-

tive model dedicated to to identify students’ behaviors

and trends, also to build adaptive learning in which

the problem difﬁculty or challenge is recommended

according to students’ SQL skills in our system.

ACKNOWLEDGEMENTS

We sincerely thank Mr. Houari ZEGAI (Software En-

gineer at DKV Belgium) for his contributions to the

development of the support tool ”LSQL”.

CSEDU 2023 - 15th International Conference on Computer Supported Education

194

REFERENCES

Ahadi, A., Prior, J., Behbood, V., and Lister, R. (2015). A

quantitative study of the relative difﬁculty for novices

of writing seven different types of sql queries. In

Proceedings of the 2015 ACM Conference on Innova-

tion and Technology in Computer Science Education,

pages 201–206.

Ahadi, A., Prior, J., Behbood, V., and Lister, R. (2016).

Students’ semantic mistakes in writing seven different

types of sql queries. In Proceedings of the 2016 ACM

Conference on Innovation and Technology in Com-

puter Science Education, pages 272–277.

Borchert, O. (2021). Scaffoldsql: Sql test cases+ parson’s

problems. In Proceedings of the 52nd ACM Technical

Symposium on Computer Science Education, pages

1281–1281.

Borchert, O. and Walia, G. (2022). Scaffoldsql: Using par-

son’s problems to support database pedagogy. In Pro-

ceedings of the 55th Hawaii International Conference

on System Sciences.

Brass, S. and Goldberg, C. (2006). Semantic errors in sql

queries: A quite complete list. Journal of Systems and

Software, 79(5):630–644.

Brusilovsky, P., Sosnovsky, S., Yudelson, M. V., Lee, D. H.,

Zadorozhny, V., and Zhou, X. (2010). Learning sql

programming with interactive tools: From integration

to personalization. ACM Transactions on Computing

Education (TOCE), 9(4):1–15.

Chaparro, E., Restrepo-Calle, F., and Ramirez-Echeverry,

J. J. (2021). Learning analytics in computer program-

ming courses. In LALA, pages 78–87.

Deterding, S., Dixon, D., Khaled, R., and Nacke, L. (2011).

From game design elements to gamefulness: deﬁn-

ing” gamiﬁcation”. In Proceedings of the 15th inter-

national academic MindTrek conference: Envisioning

future media environments, pages 9–15.

Fitz-Walter, Z., Tjondronegoro, D., and Wyeth, P. (2011).

Orientation passport: using gamiﬁcation to engage

university students. In Proceedings of the 23rd

Australian computer-human interaction conference,

pages 122–125.

Fu, X., Shimada, A., Ogata, H., Taniguchi, Y., and Suehiro,

D. (2017). Real-time learning analytics for c program-

ming language courses. In Proceedings of the seventh

international learning analytics & knowledge confer-

ence, pages 280–288.

Godinez, J. E. and Jamil, H. M. (2019). Meet cyrus: the

query by voice mobile assistant for the tutoring and

formative assessment of sql learners. In Proceed-

ings of the 34th ACM/SIGAPP Symposium on Applied

Computing, pages 2461–2468.

Jivet, I., Scheffel, M., Schmitz, M., Robbers, S., Specht, M.,

and Drachsler, H. (2020). From students with love:

An empirical study on learner goals, self-regulated

learning and sense-making of learning analytics in

higher education. The Internet and Higher Education,

47:100758.

Karimzadeh, M. and Jamil, H. M. (2022). Visql: An in-

telligent online sql tutoring system. In 2022 Inter-

national Conference on Advanced Learning Technolo-

gies (ICALT), pages 212–213. IEEE.

Mitrovic, A. (2003). An intelligent sql tutor on the web.

International Journal of Artiﬁcial Intelligence in Edu-

cation, 13(2-4):173–197.

Narasareddygari, M. R., Walia, G. S., Duke, D. M., Ra-

masamy, V., Kiper, J., Davis, D. L., Allen, A. A., and

Alomari, H. W. (2019). Evaluating the impact of com-

bination of engagement strategies in sep-cyle on im-

prove student learning of programming concepts. In

Proceedings of the 50th ACM Technical Symposium

on Computer Science Education, pages 1130–1135.

Ouared, A. and Chadli, A. (2021). Using mde for teaching

database query optimizer. In ENASE, pages 529–536.

Pfeiffer, A., Lukarov, V., Romagnoli, G., Uckelmann, D.,

and Schroeder, U. (2020). Experiential learning in

labs and multimodal learning analytics. In Adoption

of Data Analytics in Higher Education Learning and

Teaching, pages 349–373. Springer.

Qian, G. (2018). Teaching sql: A divide-and-conquer

method for writing queries. Journal of Computing Sci-

ences in Colleges, 33(4):37–44.

Siemens, G. and Gasevic, D. (2012). Guest editorial-

learning and knowledge analytics. Journal of Edu-

cational Technology & Society, 15(3):1–2.

Taipalus, T. and Per

a, P. (2019). What to expect and what

to focus on in sql query teaching. In Proceedings of

the 50th ACM Technical Symposium on Computer Sci-

ence Education, pages 198–203.

Taipalus, T., Siponen, M., and Vartiainen, T. (2018).

Errors and complications in sql query formulation.

ACM Transactions on Computing Education (TOCE),

18(3):1–29.

Thom, J., Millen, D., and DiMicco, J. (2012). Removing

gamiﬁcation from an enterprise sns. In Proceedings

of the acm 2012 conference on computer supported

cooperative work, pages 1067–1070.

Tuparov, G. and Tuparova, D. (2021). Gamiﬁcation in

higher education-a pilot study with sql course. In

ERIS, pages 81–90.

Tur

ınek, P. and Farana, R. (2022). Database and sql mi-

crolearning course. In Microlearning, pages 157–169.

Springer.

Venant, R., Vidal, P., and Broisin, J. (2016). Evaluation

of learner performance during practical activities: An

experimentation in computer education. In 2016 IEEE

16th international conference on advanced learning

technologies (ICALT), pages 237–241. IEEE.

Xu, J., Wei, T., and Lv, P. (2022). Sql-dp: A novel difﬁculty

prediction framework for sql programming problems.

In Proceedings of the 15th International Conference

on Educational Data Mining, page 86.

Learning Analytics Solution for Monitoring and Analyzing the Students’ Behavior in SQL Lab Work

195