=Paper=
{{Paper
|id=Vol-2341/paper-04
|storemode=property
|title=Extension of Electronic Testing Systems on the Example of Testing SQL-Queries
|pdfUrl=https://ceur-ws.org/Vol-2341/paper-04.pdf
|volume=Vol-2341
|authors=Tatyana S. Karpova,Svetlana Yu. Malysheva,Nikolay N. Teslya
}}
==Extension of Electronic Testing Systems on the Example of Testing SQL-Queries==
https://ceur-ws.org/Vol-2341/paper-04.pdf
Extension of Electronic Testing Systems on the Example of Testing
SQL-Queries
Tatyana S. Karpova Svetlana Yu. Malysheva Nikolay N. Teslya
Department of Department Mathematics Laboratory of computer
Mathematics and and modeling, Emperor aided integrated systems,
modeling, Emperor Alexander I St. Petersburg St.Petersburg Institute for
Alexander I St. Petersburg State Transport University Informatics and
State Transport University St. Petersburg, Russia Automation of the RAS
St. Petersburg, Russia SvetlanaMalisheva315@y Saint Petersburg, Russia
t.s.karpova@gmail.com andex.ru teslya@iias.spb.su
Most foreign researchers perceive e-learning as
an educational paradigm. They define e-learning as
Abstract "an innovative learning approach applied to provide
a well-designed interactive learning environment to
The presence of a system of distance any learner, anywhere and at any time, using the
learning (education), abbreviated as SDO, is resources of various digital technologies along with
currently a mandatory component of the other forms of learning materials suitable for an
educational environment of any University open learning environment. E-learning is making
of the Russian Federation, accredited for the the transition from a data management system to a
right to conduct educational activities in knowledge management system" [Ser12].
accordance with State standards. The transition to a new stage of economic
development – to the digital economy is also
consonant with the active use of e-learning systems
1 Introduction in the educational process. However, a simple
transfer of printed materials, textbooks and teaching
On the recommendation of UNESCO in most AIDS in the electronic educational environment
publications remains the international name of such will not give a clear effect of the assimilation of
information environments, distance learning. knowledge, which are set out in this electronic
without translation [Ser12]. Today's generation content. Yes information is provided, the
lives next door in a computer environment almost knowledge stored in textbooks is publicly available,
around the clock, so the use of information and but there is no guarantee that this knowledge,
communication technologies for learning is natural expressed in electronic form, will be perceived and
and necessary for him. Russian Universities have to assimilated by students better than in boring
urgently structure, move away from outdated lectures. But the time for presentation of the
technologies used in the educational process and do material is limited by modern standards, the volume
it as quickly as possible. It is no secret that most of classroom work of teachers at the same time only
modern students prefer not to attend lectures, increased and the load on the teaching staff
because their material can be easily read from a increased. Feedback components are used to assess
smartphone or laptop in a much more comfortable the quality of the acquired knowledge in modern
environment than a lecture hall of the University. electronic learning environments. In most
Our state adequately responds to the changed environments, these components include the ability
conditions and in the new educational standards to provide any reports, abstracts, solved specific
everywhere demand to reduce the volume of lecture tasks as well for verification in electronic form.
material to a minimum, leaving the discipline of However, such a decision only aggregates the
practical training and laboratory work.1 situation, the amount of materials that require
individual verification by the teacher increases and
this puts the teacher before a choice: either to
Copyright © by the papers’ authors. Copying reduce the number of practical tasks, or to abandon
permitted for private and academic purposes. their thorough verification. Both options reduce the
In: B. V. Sokolov, A. D. Khomonenko, A. A. quality of learning-without constant, regular and
Bliudov (eds.): Selected Papers of the Workshop very careful feedback there can be no guarantee of
Computer Science and Engineering in the
framework of the 5 th International Scientific-
Methodical Conference "Problems of Mathematical Education", St.-Petersburg, Russia, 8–9 November,
and Natural-Scientific Training in Engineering 2018, published at http://ceur-ws.org
20
mastering the theoretical material that is presented of students of 25 people almost 300 full hours of
in the content. study time, which, of course, almost impossible. In
The only way out in this case is to build an modern information systems in the analysis of
adaptive testing system that will automatically information skills correct formation of arbitrary
check everything that is possible and free the SQL-queries are required constantly. Therefore,
teacher from the routine work of checking the almost all interviews are checked for the presence
initial basic concepts of the taght field of of this skill.
knowledge. A set of essential question Bank and
the preparation of these questions themselves also 2 The Skills
requires a significant amount of time and effort
from teachers, but there is a real prospect: once
Figure 1 shows the order of formation of skills
typed test questions can be used repeatedly. In
in any problem domain.
addition, the completion and expansion of the Bank
of questions and competitive construction of test
models will eliminate empty guessing and provide
an effective process of self-education for students,
which we strike for.
However, with all the variety of types of test
questions in most areas of knowledge, there are
professional problems or tasks that can not be
implemented available tests. These tasks can have
many possible solutions and it is often impossible
to foresee and list them all. In this case, a different
concept of feedback is required. The authors
propose to use the concept, in which there are no
Figure 1: Hierarchy of professional
attempts to simulate the process of solution, and the
competences
student is given the opportunity to solve the
problem-the problem in any way and check the Knowledge is formed in lectures and reading
result. For this purpose, it is assumed that the e- textbooks, practical skills-in the process of practical
learning system has a mechanism for the and laboratory work, and skills are formed with
implementation of the solution proposed by the experience, ie, with multiple assignments.
student on the given source data and the mechanism In order for students to be able to apply the
for the implementation of the solution proposed by theoretical knowledge and practical skills acquired
the teacher on the same data, and the evaluation of during their studies at the University, they must
the solution is carried out by comparing the results. move into skills. The problem-solving skills will
If the results are the same, you can consider the make students competitive.
solution correct, otherwise – no. The authors set a goal-to develop a system of
Of course, for different classes of problems in automatic check for SQL-queries. In this case,
different disciples should be supported by different students can independently develop the skills of
"performers" developed algorithms for solving correct formation and SQL-queries and confirm it
problems. during the control testing. However, to do this, the
To implement this task, the authors choose the system must be able to replenish the test databases,
task of getting the skills of writing semantically fill them with specially verified data, allowing in
correct SQL queries. From many years of teaching any situation to identify a semantic error.
experience, the following result was experimentally
obtained: if a student on 10 different databases 3 The Analysis Of Systems Performing
makes no more than 3 errors when executing 100
SQL queries, then it can be guaranteed that he will
The Verification By SQL Queries
make no more than 3% of incorrect queries on any In the public domain, there are ready-made
other databases. The authors do not take into solutions that allow you to check the syntax written
account the syntax – it is checked by all translators in the SQL-query [Jew11]. Also, the check can be
and mastered quickly enough. Here, the checks are performed by comparing with the text of the correct
performed exactly within the meaning of the SQL SQL query stored in the database (DB), without its
query. execution. This approach has the disadvantage of
The teacher spends from 5 to 10 minutes to not taking into account the sequence of data from
check one SQL query of average complexity. And multiple tables.
if, as defined above, for a steady skill of correct From the point of view of the system analyst,
writing on SQL-queries is required to check at least they are not suitable for the task, as they are aimed
100 on SQL-queries of each student, the elementary solely at checking the syntax compiled by the SQL
calculation shows that the teacher requires a group query, which is necessary, but does not contribute
21
to training. Therefore, it was decided to develop its The development of a methodology for
own testing methodology. comparing and checking the answers of the student
and the teacher included the implementation of
4 Description Of The Proposed some operations of relative algebra in a limited
version of the language with SQL and the formation
Mechanism of the algorithm shown in the form of a flowchart in
The complexity of automatic verification of the figure 3.
student's response and the correct SQL-query When building a system of automatic testing of
teacher is the existence of different ways of writing query checking in SQL, the authors faced the
the correct SQL-query to the given task, that is, the problem of the absence in the free version of the
translation of the semantic question in the standard database management system (DBMS) MySQL the
SQL-query is not formalized and is creative. For ability to execute a query directly corresponding to
example, a semantically correct SQL query can the difference in relations. This limitation applies to
have multiple ways of writing because of the the considered DBMS, which is the basic in the
different order of column or table names used in the system of support of the educational process e-
query. leaning Moodle. Moodle [Moo19] – open-source
During the analysis of possible methods of system implemented in PHP, it was decided to
semantic analysis of SQL-queries, the procedure of develop a mechanism for reflecting the difference
comparing the results of the correct SQL-query operation of relative algebra into the difference
prepared by the teacher and the results of the SQL- operation over associative arrays of PHP language
query written by the student was proposed [Каr18]. to implementation this functionality [Каr18].
Figure 2 illustrates the process of comparing the During the request processing SQL queries on
results of a teacher's and a student's SQL queries. the DBMS server MySQL using PHP language, you
The architecture of checking the results of SQL- are working with the library of functions that allow
query allows you to check different versions of its you to perform queries of the SQL language in
writing. database. The result of the query is an associative
array that actually models a two – dimensional table
where column area attributes and its rows are the
values of the result tuples.
However, among the common operations on
associative arrays there is no subtraction operation
for multi-dimensional arrays. This operation is
available only for one-dimensional linear array.
The General mechanism for checking the
correctness of SQL queries prepared by students is
shown in figure 3.
Figure 2: Algorithm of check of conformity of Figure 3: The developed algorithm for comparing
test results the difference with the implementation of the
22
relative algebra operation, used in the training
system
As can be seen from figure 3, this algorithm
uses the relative algebra operation – the difference
of relations.
The difference between the relations P1 and P2
is the set, which includes a set of tables belonging
to R1 and not belonging to R2, and: Figure 5: Types of database
𝑅3 = 𝑅1 \ 𝑅2 = {𝑟 | 𝑟 є 𝑅1 ∧ 𝑟 ∉ 𝑅2 }. (1) Based on the description of the subject area, in
the process of communication with experts in this
To implement the algorithm in PHP, you first field, the need for the following entities was
need to check whether the schemes are equivalent formulated:
and only then proceed to the execution of the
db,
difference operation. This is initially done by
tables,
comparing the ranks of relationship names and
attributes. In this case, the algorithm for performing stolb,
the operation of the relative algebra of difference is zapros.
as follows, as shown in figure 4. An important stage of the project was the design
of the database using CASE
(Computer Aided Software Engineering)-system.
Modern CASE-systems — means of development
of not only software systems, but also
organizational and management. The purpose
CASE-средств – to separate processes from the
design of programming processes.
To ensure the independence of the design of the
logical structure of the database was used modern
free-distributable CASE-система
OpenSystemArchitekt. The choice of this product is
justified by its correctness in the formation of the
classical infological model EntityRelationShip –
ER, shown in figure 6, with the possibility of
obtaining a database generation script in accordance
with the standard SQL92.
Figure 4: Checking the equivalence of the
compared operation schemes
5 Design And Creation Database
To implement the above algorithm in the
process, it was decided to create a metadata
database at the first step, then create a set of
training databases [Nev06] (figure 5).
23
Figure 6:ER model database
Then, based on the logical model, a physical model was created, as shown in figure 7.
Figure 7: Physical database model
Then the script of database generation was In the table "db" the data is stored on the created
received, imported to Denwer-local server with training databases:
PHP, Apache and MySQL support, allowing to serial number assigned to automatically,
work with the site code without connecting to the the name of the database,
Internet.
24
brief description of the subject area of the 13.
database.
Table «tables», the structure of which is shown
in figure 9, contains data on the list of all tables
included in all training databases, namely:
serial number assigned to automatically,
the name of the table,
database number to which the table belongs,
number of columns in the table,
name of the table in English.
In table «stolb» data is stored on the columns of
all database tables, indicating the data type:
serial number assigned to automatically,
column name,
data type, Figure 13: Source code comparison of the ranks of
the table number refers to the column names of relations and attributes
Table «zapros» stores information about
the SQL query environment: If, when checking the algorithm shown in figure
4, the scheme of operations of the equivalents to be
serial number assigned to automatically,
compared, it is necessary to proceed to the
query text,
subtraction operation, the implementation of which
the correct answer in the form on a SQL is shown in figure 3.
query, In this case, in the absence of the possibility to
database number to which the request is carry out the subtraction operation directly on the
written. multidimensional associative arrays, a special
To create a training database "Products" function srav (a1, a2), was developed, presented in
developed at the initial stage of the database tables, figure 14.
which includes the following tables:
db,
tables,
stolb,
zapros,
new will be added :
tovar,
zakaz,
klient,
postav.
6 Implementation Of The Methodology,
Functional Development
The implemented technique assumes storage in
the database of the list:
• training database,
• table of the training database with the description,
Figure 14: Code to perform the subtraction
• fields with data type and meaning,
operation
that was implemented and described above. When
implementing the algorithm illustrated in figure 4 in
PHP, it is sufficient to use the mysql_num_fields ()
function to compare ranks, which returns the 7 Development And Writing Interfaces
number of elements in the associative array of the
For the formation of training databases, for
result, and to perform the schema comparison
further testing of students, it was necessary to
operation, you need to perform the function of
develop an interface of the teacher, with which he
comparing keys of associative arrays using the
will fill the database metadata and training
array_diff_key () function [Kar18]. The use of
databases for the execution of SQL-queries.
functions is shown in the program code in figure
Using the PHP programming language and the
HTML hypertext markup language, the interface
was developed for the teacher.
25
participation "science of present and
8 Further Development future" for students, postgraduates and
young scientists. Proceedings of the
In the development of the issue raised, the conference. SPb.: Publishing house Etu
authors plan to integrate the mechanism in the "LETI", 2018. 144 - 146 p.
open-source wrapper for the class systems e-leaning
Moodle. According to the statistics collected at the [Moo19] Moodle Docs. [Electronic resource]. –
summit of the developers of e-courses, we can say URL:
that Moodle is the most used in the educational https://docs.moodle.org/36/en/Main_page
environment. . – (Date of appeal: 17.02.2019).
References [Kar18] T. S. Karpov, S. Yu. Malysheva.
Implementation of some relational
[Ser12] A. G. Sergeev. Introduction to e-learning: algebra operations in a limited version of
monograph of Sergeyev, I. E. Zhigalov, SQL // Problems of mathematical and
V. V. Balandina; Vladimir. state natural science training in engineering
University named after Alexander education. Sat. proceedings of the 5th
Grigorievich and Nikolai Grigorievich international scientific-methodical
Stoletovs. - Vladimir: publishing house of conference. 8 - November 9, 2018, St.
VlSU, 2012. - 182 p. ISBN 978-5-9984- Petersburg/ ed. – St. Petersburg: PGUPS,
0268-5. 2018. P. 141– 148.
[Jew11] B. Nevarez. Inside the SQL Server Query [Nev06] T. Jewett Database Design With UML
Optimizer, 2011. — 265 p. and SQL. –Department of Computer
Engineering and Computer Science
[Kar18] T. S. Karpov, S. Yu. Malysheva. System California State University, Long Beach,
approach to the development of training 2006.
system for obtaining skills // VI scientific
- practical conference with international
26