<<eJournal

SQL E-LEARNING SYSTEM

Ph.D.Dragica Radosav
University of Novi Sad, Technical Faculty “Mihajlo Pupin” Zrenjanin, Serbia&Montenegro
University of Mostar, Faculty of Information Technology, Bosnia and Herzegovina

Zoltan Kazi
University of Novi Sad, Technical Faculty “Mihajlo Pupin” Zrenjanin, Serbia&Montenegro

Ljubica Kazi
University of Novi Sad, Technical Faculty “Mihajlo Pupin” Zrenjanin, Serbia&Montenegro

Abstract

In this study is described e-Learning system assigned to distance learning SQL, as the part of course Data Base Management System (DBMS). It is possible for students to test their queries within three different problems  for about 200 hundred solved tasks.They also have the opportunity to create their own queries and check the result of their query (report) within the given database. Consequently, this e-Learning system allows independent, interactive studying in WEB environment. 

Key terms
SQL, DBMS, WEB, e-Learning, practice


E-Learning System

E-Learning system is a system, which offers support to studying by development or application of information and communication technology (Information & Communication Technology - ICT). One of the basic benefits of e-Learning system at universities is that students are allowed to access, explore, analyze, construct and evaluate materials within the subjects. Surveys of the leading universities in the world in this area have stated that, e-Learning systems are usually used as a supplement and improvement and not as a substitute for traditional, frontal classes.

E-Learning system usually consists of some of the following components

·          Presentation of syllabus using multimedia and hypertext

·          Testing section

·          Simulations

·          Example section

·          Practice section

·          Multi-user forms

·          Group discussions

·          Video conference

·          Discussion groups

·          Mailing lists

·          Index

·          Download and upload of materials

·          Server search

·          Agents

Creation Technology of E-Learning Systems

Technology used for creation of e-Learning system is shown on Picture 1, and consists of the following components:

1.       WEB is multimedia service Internet-a, I.E. network of documents connected to each other, pack of protocols, which define how the system works and transmit data, and software, which allows operation of this network.

2.       HTTP (Hyper Text Transfer Protocol) is a protocol, I.E. collection of rules, which define exchange of files by using Internet, and allows user to load and see the desired page. HTTP client at one side and HTTP server on the other side are needed for functioning and usage of this protocol

3.       HTML (Hypertext Markup Language) is a script language used to write and create hypertext documents, pages and Internet presentations.

4.       ASP (Active Server Page) is not a programming language, but an active page on a server or more precisely a mechanism which connects scripts, components, objects and interaction on a web server

5.       RDBMS (Relational Database Management System) – Relational Database Management System is used to operate databases and allows manipulation and control of data in a database. It consists of DDL (Data Definition Language – language for describing data), DML-a (Data Mainpulation Language – langauge for manipulation of data vocabulary, query language, report generator, and mechanism for protection of integrity, recovery and exchange of data.

6.       SCRIPT LANGUAGE is a programming language used for control, modification, automats, expanding, and improvement of the current system of HTML pages on WEB. Code script language is an addition to HTML page and already existing tags.

Picture 1. Web technology for creation of e-Learning system

7.       JAVA SCRIPT is a sort of script developed by Netscape Communications because of improved interaction of Web pages on Internet

8.       VB SCRIPT (Visual Basic Script) is a sort of script language – addition to Web pages, developed by Microsoft, and it is also the creator of programming languages Basic and Visual Basic.

9.       ADO (ActiveX Data Objects) is component, I.E. objects, which allow direct access to databases and data resources.

Functioning of e-Learning system (Picture 2) starts when a user (Client), using Web Browser- activates hypertext link and precedes request for HTML or ASP page to server (Host), which executes HTML page with Java script and / or ASP page with VB script. Within ASP page, execution is possible only if program called Internet Information Services is installed on a server, which by using ADO connection communicates with RDBMS and database. In the end as a result of request, user gets HTML page, which can consist of text and multimedia elements and also data, which are the result of execution of scripts or SQL query in a database.

 


Picture 2. Functioning of technology needed for creation of e-Learning system, (Bardic (2002))

Description E-Learning System for DBMS

E-Learning system for SQL can be used after the installation of MS Internet Information Services-a, created virtual directory of web site and copying of e-learning files to virtual directory. The system starts when Web Browse starter (I.E. MS Internet Explorer-a) and typing the following into Address field: http://nazivserverracunara/elearningsql (I.E.:  http://protos/elearningsql or  http://protos/elearningsql/index.htm)

All the pages in the system are divided on two sides: menu on the left side, and portal on the right. (Picture 3). All the items on the page, such as pictures, hyperlinks etc have additional explanation: in a status line or as a tool tip when they are focused with the pointer. Below the menu there is a link to homepage of the faculty, and menu itself contains buttons, which lead to following:

·          About SQL

·          Database

·          Examples

·          Query editor

·          Index

·          Links

·          Info

When you click on the first item in the menu “O SQL-u” page is opened  (Picture 3) which contains short theoretical description of history, use, standard and syntax of SQL, but only those commands, operators and clause that are found in the examples at the web site of the Technical Faculty “Mihajlo Pupin”. This part of e-Learning system can be used for getting basic knowledge of the SQL. Hyperlinks below the title lead to thematic parts of system’s theory section. Thematic section “SINTAKSA”, which describes basic commands, SELECT, INSERT, UPDATE and DELETE is also the most important for students who haven’t got enough knowledge of the SQL to understand examples, so they could later, after viewing these, create their own queries.

Return to the top of pages is possible by clicking on the arrow, which is located on the line thematic section, next to Scroll Bar.



Picture 3. Section “O SQL-u” of e-learning SQL system

Next screen (Picture 4) shows section database “BAZA PODATAKA”, which contains relation schemes. Relation schemes were taken from the following web page: http://www.tf.zr.ac.yu/predmeti/bazepodataka



Page, which shows database, has several sections. Relation scheme is the first one, where each relation scheme link leads to new pages, which describe database physical structure. Second section is relational limits, on which are defined indexes in tables, I.E. external keys (indexes) and relation setting between tables. Third section (Picture 4) displays graphically database, tables, names of columns in tables, primary and external keys of tables and relations between tables with cardinal.  Maintenance of referential integrity of data is setup the same for all relations, cascade for operations of data changing and restrictive for delete operation.


Picture 4. Graphical view of a database in e-learning SQL system


The last, fourth page called databases “BAZA PODATAKA” allows download of databases with tests and examples of SQL queries. Three types of problems are available: faculty, company and commercial department.

Return to the top of page is the same as in the section “About SQL”. If link data in table “PODACI U TABELI” or by clicking on the graphical view of a table is chosen next page of the system is opened, which contains random test data for each table (Picture 5). Names of columns in a table, I.E. are fields in headings of table view and rows contain actual data. This page also has two links: first one which opens page with physical structure view and second to return to homepage with view of a database, so user would be able to go some other tables in the base.


Picture 5. Section “BAZA PODATAKA” in e-learning SQL system – Table structure


If you click on relation scheme name page will be opened giving the detailed view of physical structure in each base. View is in the form of table which contains names of fields in table in columns, type of data in fields, information on necessity of fields, and if the field is index and short description of the field. In rows of a table are names of fields and their properties.  Two links are available at this page: first one, which leads to a page with test data and the second leading to homepage with a view of a database.


Picture 6. Examples Section “PRIMERI” in e-learning SQL system


Third section contains examples and it is shown on picture 6. It contains around 200 examples of SQL queries taken from the site of the faculty. Some of the examples were modified, in order to adjust to test data, and up to a point queries were modified too, because of typing mistakes and because of adjustment to Microsoft Access system for operating databases.



The user of e-learning system, by accessing field with query text, changing query and then again pressing the button for execution of query to get the new result, can change every query from the examples. If the result is not shown after the execution it means that new or modified query has not been properly written, and e-Learning system does not execute syntax check of the query, so web browser shows page-containing message about mistake.

Section Index - “INDEKS POJMOVA” associates to theoretical part of the SQL system and contains all relevant terms needed to user, so that user is able to find them quickly and easily. The last two pages of e-Learning system links and info, “LINKOVI” and “INFO”, contain various information about authors, their  e-mail addresses, use of the system, literature and interesting links about this area.

Query Editor

Query editor is the place where everyone can practice writing queries after they have dealt with theory, get to know more about databases by executing examples using test examples.

Editor page is divided into two sections:

·          Making simple queries with commands: SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY. User needs to enter the rest of the query to appropriate fields next to clauses. It is not needed to enter the whole query.

·          Writing random, more complicated and simple queries, which can be any ANSI compatible query for access database, needs to be done to a bigger field  (Text Box) in the lower part of the page (Picture 7).


Picture 7. QUERY EDITOR “EDITOR UPITA” of e-learning SQL system – Entering query


Below the field for entering queries, there are two buttons: “IZVRŠI UPIT” – execute query and “PONIŠTI” - cancel. The first one is used for executing query and its functioning has already been described in the section “EXAMPLES” - “PRIMERI”, and the other is used to cancel content of a field, if a query is incorrect or if a new one needs to be written. If the page is shown, with the message, after the entry of the query, it means that either query is not correctly written or the execution of the query was successful, but it cannot display the columns, because of the type of query, which does not have to show data. Then it is necessary to go back to query editor and with simple command SELECT see what has the system done. Page with the show results (Picture 8) has only one link, and that is to go back to the query editor, where fields are empty and the page is ready for writing of the new query. If you do not want delete content of the fields you can use navigational buttons of your browser “Back” and  “Next”.




Picture 8. QUERY EDITOR “EDITOR UPITA” of e-learning SQL system – result of query execution


System Requirements for Setup and Usage

In order to setup e-Learning system for SQL it is needed to have Microsoft Windows 2000, Windows XP or Windows Server 2003 operating system installed with IIS (Internet Information Services). For older versions of Windows it is possible to use  program Personal Web Server, which is not included in the initial package of Windows but needs to be installed separately.

For usage of e-Learning SQL system it is enough to have any version  32-bit operating system Microsoft Windows (Windows 95 and newer) and any of browsers installed I.E. Microsoft Internet Explorer.

Hardware platform for a computer which would be a server must be at least PC compatible computer , Pentium III or IV with 256 DDR RAM memory and as much as possible big Hard disk and quality network adapter. Work stations, which would access server for opening and loading  e-learning system, must be computers with at least Pentium I.

Installation of Internet Information Services

Internet Information Services is a program from a group of web servers. It can be installed in two ways: by turning on appropriate option during the installation of operating system, which is already initially included or by adding it later after the installation of windows. Here, we will describe only the second way.

In Control Panel-u open option Add/Remove Programs (Picture 9) and choose last item Add/Remove Windows Components. From the list of offered components of Windows-which can be installed, choose Internet Information Services (IIS) and after command Next, operational system will copy files from installation disc.

Picture 9. Installation of IIS-a

In order to check if IIS is correctly installed and started you can check by opening window for Web Server administration. (Picture 10). If IIS is correctly installed, in the Column State for Default Web Site will be status running. In case that there is not a connection with server or Web Site is stopped, status will be stopped. Then it is necessary to connect again to server and start Web Site by option Start.

Picture 10. Administration window of Internet Information Services

Settings of IIS. Creating virtual directory

Virtual directory is a directory that does not exist root directory of web site (c:\inetpub\wwwroot), but browse can show it. It is in fact an alias for another directory, somewhere on server. The advantage of using virtual directory is that users cannot know the destination of critical files on server. Virtual Directory Creation Manager, can be used for creation of virtual directory, which is started from administration window IIS-a (picture 10), by opening pop-up menu and choosing on server or web site option New–Virtual Directory. After choosing option next, comes another step where alias needs to be entered I.E., logical name of the virtual directory. This does not have to be the name of the directory where site is located. Write: ELearningSQL. After choosing option next a new window opens, where physical directory has to be entered on hard drive. Using option Browse or by entering it yourself you have to choose the following path:

c:\inetpub\wwwroot\elearningsql.

If the directory has not been created previously on hard drive of server, it must be created. If you click on button next you move to a window in Wizard, which determines which actions and operations will be allowed in virtual directory. Then after pressing next again, final window appears containing message about successful creation of virtual directory. By clicking button Finish the process of creating is finished. Directory will appear on initial web site (Default Web Site) in which it is created as shown on picture 11.

Picture 11. Created virtual directory for eLearning SQL system

Last step in setting IIS, in order to be able to execute ASP pages, is, opening page Documents in the window of ELearning SQL Properties. Button Add has to be added on a list of startup pages of the web site that has just been created Index.html, since that is the startup page of  e-learning system. The other pages from the list can be deleted. In the end of installation process it is just required to copy software for learning SQL is located to the previously created directory.

Database adovbs.inc, needed for functioning ADO connection to the database in ASP pages has to be copied to the following path:

c:\Inetpub\wwwroot\adovbs.inc

Realization of E-Learning

Realization of e-Learning SQL system is not given completely because HTML language is not the aim of this study, and only explanation and characteristic situations are described in writing of HTML and ASP pages, as an illustration of applying Web technology and programming.

Entering the following tag to the document does enabling JavaScript code in HTML directory:

<SCRIPT language=JavaScript>

document.write(“Pozdrav iz JavaScripta.”)

</SCRIPT>

In HTML pages are used JavaScript functions for showing messages and additional explanations in status line of a Browser:

function MM_displayStatusMsg(msgStr)

{

  status=msgStr;

  document.MM_returnValue = true;

}

Then, functions for changing of the picture in GIF format, when mouse is in focus over menu:

function MM_swapImage()

{

var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)

if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}

}

function MM_swapImgRestore()

{

var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;

}

Beginnings of these functions are within HTML tags for links to pages, which open when we click on item in menu, or picture which represents a link:

<a

onmouseover="MM_swapImage('layered_toolbar_r02_c1','','layered_toolbar_files/layered_toolbar_r02_c1_f2.gif',1);MM_displayStatusMsg('Osnovne informacije o SQL-u');return document.MM_returnValue;"

onmouseout="MM_swapImgRestore();MM_displayStatusMsg(' ');return document.MM_returnValue;"

href="portal_osqlu.htm"

Calling pages of APS are “simple” HTML pages with added POST form method on previously created HTML page and contains for example Submit type of Button:

form method="POST" action=http://localhost/elearningsql/esql/primeri_rezultat.asp

Interactive query execution

Interactive query execution is achieved with ASP technology, which available to user as soon as IIS is installed. Simplicity of VB Script with which are ASP pages written by is seen in when after writing HTML code, where programmer wants it or where it is needed, it is added to ASP code between the following tags: <% %>

<HTML>

<HEAD>

</HEAD>

<BODY>

<p>Ovo je tekst nekog pasusa.</p>

<% Response.write(“Ovo je tekst nekog pasusa.”) %>

</BODY>

</HTML>

To define standard ADO named constants, start of file ADOVBS.INC needs to be added to <head> section of HTML page:

<!-- #include file="../adovbs.inc" -->

This file contains all declarations of constants needed for operating ADO objects. It needs to be copied to root directory of web site.

Example of ASP code for creation and opening ADO connection to Access database:

<%Set Con = Server.CreateObject("ADODB.Connection")

OpenStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\wwwroot\elearningsql\esql\baza.mdb;Persist Security Info=False"

Con.Open OpenStr%>

VB script for creation Recordset objects which contains records from the tables created by executing queries:

<% str = "SELECT * FROM Student;"

Set RS = Server.CreateObject("ADODB.Recordset")

RS.Open str, Con, adOpenDynamic, adLockoptimistic%>

VB script in combination with tags of HTML language, which with  WHILE ... WEND loop shows records, I.E. rows of table from table Recordset with name RS using command  response.write(RS("BR_IND")) and shows value of field on a HTML page:

<%WHILE NOT RS.EOF%>

<tr>

<TD bgcolor="#ECFBFD"><font face="Verdana" size="1">&nbsp<%response.write(RS("BR_IND"))%></font></TD>

<TD bgcolor="#ECFBFD"><font face="Verdana" size="1">&nbsp<%response.write(RS("IME"))%></font></TD>

<TD bgcolor="#ECFBFD"><font face="Verdana" size="1">&nbsp<%response.write(RS("PREZIME"))%></font></TD>

<TD bgcolor="#ECFBFD"><font face="Verdana" size="1">&nbsp<%response.write(RS("DATUM_ROD"))%></font></TD>

<TD bgcolor="#ECFBFD"><font face="Verdana" size="1">&nbsp<%response.write(RS("PTT_STAN"))%></font></TD>

<TD bgcolor="#ECFBFD"><font face="Verdana" size="1">&nbsp<%response.write(RS("SMER"))%></font></TD>

<%RS.MoveNext%>

</TR>

<%WEND%>

In the end of ASP page, commands for deleting  Recordset  objects and closing ADO connection to database are written:

<%RS.Close %>

<%Con.Close%>

Execution of examples of manipulation query is realized by the following VB script which opens connection for database by using appropriate driver (Microsoft.Jet.OLEDB.4.0), executes SQL query and closes ADO connection:

<%

SqlString=Request.Form("txtPrimer1")

Set Con = Server.CreateObject("ADODB.Connection")

OpenStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\wwwroot\elearningsql\esql\baza.mdb;Persist Security Info=False"

Con.Open OpenStr

Con.Execute SqlString

Con.Close

%>

Dinamic creation of tables with data on ASP pages

Within query editor and window for showing results of query execution is dinamical creation of tables, with data on ASP pages, acheived in <BODY> of the page tag, with the following block of HTML and VB script commands:

<BODY><%SqlString=""

if Request.Form("txtSQL")<>"" then SqlString=Request.Form("txtSQL")

if Request.Form("txtSelect")<>"" then SqlString=SqlString & "SELECT " & Request.Form("txtSelect")

if Request.Form("txtFrom")<>"" then SqlString=SqlString & " FROM " & Request.Form("txtFrom")

if Request.Form("txtWhere")<>"" then SqlString=SqlString & " WHERE " & Request.Form("txtWhere")

if Request.Form("txtGroupBy")<>"" then SqlString=SqlString & " GROUP BY " & Request.Form("txtGroupBy")

if Request.Form("txtHaving")<>"" then SqlString=SqlString & " HAVING " & Request.Form("txtHaving")

if Request.Form("txtOrderBy")<>"" then SqlString=SqlString & " ORDER BY " & Request.Form("txtOrderBy")%>

<TABLE DIR=LTR BORDER="2" width="522">

<CAPTION>

<p align="left" style="word-spacing: 0; line-height: 150%; margin-left: 0; margin-right: 0; margin-top: 0; margin-bottom: 6"><font face="Verdana" size="2"><b>EDITOR UPITA</b> - Rezultat izvršavanja upita</font>

  <div align="left"></div>

</CAPTION>

<%Set Con = Server.CreateObject("ADODB.Connection")

OpenStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\wwwroot\elearningsql\esql\baza.mdb;Persist Security Info=False"

Con.Open OpenStr

Set RS = Server.CreateObject("ADODB.Recordset")

RS.Open SqlString, Con, adOpenDynamic, adLockoptimistic

brojac=RS.Fields.Count%>

<tr>

<%for i=1 to brojac%>

<TD DIR=LTR ALIGN=LEFT width="510" bgcolor="#4069A8"><b><font face="Verdana" color="#FFFFFF" size="1"><%response.write(RS.Fields(i-1).Name)%></font></b></TD>

<%next%>

</tr>

<%WHILE NOT RS.EOF%>

<tr>

<%for i=1 to brojac%>

<TD bgcolor="#ECFBFD" width="510"><font face="Verdana" size="1">&nbsp<%response.write(RS.fields(i-1))%></font></TD>

<%next%>

</tr>

<%RS.MoveNext%>

<%WEND%>

<%RS.Close %>

<%Con.Close%>

The main idea is that after opening ADO connection to database, Recordset object is created which is packed with data from SQL query acquired from the Text Box in query editor, and from Fields collection of Recordset, in variables are located number of columns Recordset with Count property. This number is used as a maximum limit of the counter in FOR ... NEXT loop, which in  <TD> tag lists values of columns with the following VB script:

<%response.write(RS.fields(i-1))%>


Conclusion


E-Learning SQL system allows students to experience basics structures of query language, to experiment with examples of queries in database with test examples and to independently practice writing of queries with query editor. System has presentational and interactive, where interactive part is more interesting and significant to students because it stimulates independence in learning system and independent work and practice.

Syllabus shown in the study is suitable for e-learning system because SQL is easy to ilustrate, execute and integrate into Internet technology and ASP web pages. System realization with Internet technology, allows user to learn and practice without installing system for handling databases, either from local network - Intranet or from home, using modem communication (Internet). Technically, for this kind of  e-Learning system it is needed to have it set up on faculty site, and existence of communication server that is Windows oriented.

Possibilities for improvement and development of e-Learning SQL system are numerous. It is needed to form tests for testing of the knowledge of SQL, forms for multi-user operations, authenticity and signup of users, list of examples, tutorial for SQL with buildup model, forming of discussion groups for SQL, normalization of database and expand it with a large number of examples etc.

By adding part of the system which refers to query editor, with return information about result of query and characteristic mistakes which can happen because of typing mistakes, system would be much more interactive and improved.

Described SQL E-learning system is available at www.tf.zr.ac.yu.

References

Bardic, I. (2002), Project of IS, Internal material on Technical Faculty, Zrenjanin, Serbia and Montenegro

Goodman, D. (2000). The Bible of JavaScript, Mikro-knjiga, Belgrade, Serbia and Montenegro

http://www.unimondo.org/balcani/news/news-2001101202.html - “Educational system in Serbia: lack of funding and resistance to reform”

Ivković, M.,Radenković, B. (1998). Internet and e-business, Technical Faculty  “Mihajlo Pupin”, Zrenjanin, Serbia and Montenegro

Jenings, R. (2000). Guide-book -  Microsoft Access 2000, CET, Belgrade, Serbia and Montenegro

Mogin, P.,Luković, I. (2003).A Methodology of a  Database Schema Design Using the Subschemas. In Proceedings of IEEE International Conference on Computational Cybernetics, Siofok, Hungary

Mogin, P,Luković, I.,Govedarica, M. (2000).The Principles of Database Design,  University of Novi Sad and MP Stylos, Novi Sad, Serbia and Montenegro

Mogin, P.,Luković, I.,Karadžić, Ž. (1994).Relational Database Schema Design and Application Generating Using IIS*CASE Tool, In Proceedings of International Conference on Technical Informatics, Timisoara, Romania, Vol. 5, 49-58.

Mercer, D. (2001). The bases of  ASP 3.0, Komjuter biblioteka, Čačak, Serbia and Montenegro

Radosav, D. (2003).System of DL based on Internet technology by used multimedia educational software, IEEE conference «EE Education in South-eastern Europe», /CD, pages 35-37/, Sarajevo

Radosav, D., Barbarić, M. (2003).  Distance learning – curriculum module, YU INFO 2003, Kopaonik, Serbia

Radosav, D., Naumov, D. (2002). Multimedia DB and Software for Education, Symposium INFO-2002., Kopaonik, Serbia and Montenegro

Stephens, R.K.,Plew, R.R., Morgan, B., Perkins, J., Teach Yourself SQL in 21 Days, Second Edition, SAMS.

Sotirović, V.,Radosav, D. (2003).  Distance Learning projects for the Education of the XXI Century, IEEE conference «EE Education in South-eastern Europe», /CD, pages 56-60/, Sarajevo, BiH

 

^top

<<eJournal