Web Based Sql Tutorial System Computer Science Essay

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

To identify the more proper technologies that suits my project, it is important to make research on how the system is to be employ and other approaches to the problem development. In this section, I will analyze the previous work done to evaluate the positive and negative aspect points and keep away from problems of the current system. Hence, doing that will allow implementing effective system that can be actuality useful.

Code The name of this project is web based SQL tutorial system which will help students to practicing their SQL commands, helping them to correct their usually mistake of SQL commands so that they can improve their knowledge gained from lectures. The system will support frequently used SQL commands. It will be not instead the academic stuff in laboratories but to enable students to understanding where it will be frequent mistakes through the real time practice.

This project will enable students to use the client side system from a pc which is connect to the internet independently if they are within Bradford school's intranet or not. As a result it will give the option to study in their environment and still have an assistant helping them indicating what is wrong and what is correct'

The system will not rely only in simulation but will rely in interaction with database and more specifically postrgreSQl. It should work exactly like a dumb UNIX terminal but with extended features.

Main problem

The main purpose of this project is to design and implement a web based tutorial system. This system used to helping students those who are study the course of database system. In order that student can practice their SQL command at home as they are in the lab. Also help students to correct their mistake in the SQL command.

So that students will have the ability to test their knowledge in SQL commands.

Current problem

There are many students study at department of computing current$. And all second year students have the course of "Database Systems". But during the lab time there is not enough supervisors, to answer every question of each student. So this time we need the web based database tutorial system to solve this problem and help student get rid of some easy questions. Also this system can be used at every place if it can connect to the internet. Student can through the internet connect to the school database space to practice their knowledge. At last student can easily gasp everything that is taught in the lectures and also implemented them as they are in the lab.

Normally student can connect to the school database space through the telnet. As the step of start -> run -> cmd-> telnet ->open -> url-> username-> password-> operation system.

2.1 Key terms

Firstly, I will start my research by identifying some major definition related to this project.

Web Applications are described as "web programs or real programs designed to be used on the web site using a browser. Example of web application would be e-commerce web site, web banking, stock exchange on the web, web games and many others."[2]

Interactive, this term is defined as "providing ouput based on input from the user. This output feeds back into the user's decision process for subsequent interaction. Interactive website, for instance, allow for more dynamic information browsing and applications such as shopping, banking, etc." [3]

Tutorial, this term is defined as " A computer-assisted instruction technique in which new information is introduced on a step-by-step basis with frequent quizzes given to ascertain if the information is being leaned."[4]

I should to keep those terms in mind to emphasise that the project is designed to be learning system which allows insert data by the user and output information from the system, In order to create an effective system.

2.1.1 Database Technologies

SQL stands for Structured Query Language which is used as a tool for getting information into and out of a database. It is the standard language for relation database management systems. "Although most database systems use SQL, most of them also have their own additional proprietary extensions that are usually only used on their system. However, the standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database."[5]

DBMS stands for Database Management System is a set of computer software and program environment which enables users to organize and facilities the management, creation, manipulation, maintenance, controlling, accessing, structuring and retrieving the data stored in the databases system such as MySQL and Microsoft Access. The DBMS identify the database during the utilizing of the Data Definition Language (DDL) and provide process such as insert and retrieve data from the database during the Data manipulation language (DML).

DDL or Data Definition Language is a programming language for specifying the type and the various structure of the data that stored in the database and can be connected with operations such as Create, Alter, Drop.

Useful DDL Commands:

Creating a table

CREATE TABLE <table name> (<column name> <data type>,<column name> <data type>..);

For example CREATE TABLE users (first_name varchar (30), last_name varchar (30)...);

Create a database

CREATE DATABASE <database name>;


Deleting a table

DROP <table name>;

For example DROP users;

DML or Data Manipulation Language involves the manipulation of data in the database table or adding information to the tables, so this means it is focused on the manipulation of data in clearly defined structures. DML commands include insert, Select, Update and Delete etc.

2.1.2 Database Management System

You can see many web site connected with a Database Management Systems linked via SQL. For that reason, I will go through some of those in order to choose most appropriate option which suits this project.

Useful DML Commands:

Querying Data in a database

Select named columns from a particular table

Select <column name(s)> from <table name>

For example SELECT firstname, lastname from users;

Select all columns from a particular table

SELECT * from <table name>

For example SELECT * from users;

Manipulating the database

Insert a new record into the table

INSERT INTO <table name> VALUES ('<value1>','<value2>',....);

For example INSERT INTO Users VALUES ('Perepiczka','James');

Delete a record from a table

DELETE FROM <table name> where <column name>='<value>';

For example DELETE FROM Users where Iname='Perepiczka';

Updating a record

UPDATE <table name> SET <column name> = '<new value>' WHERE <column name> = '<value>';

For example UPDATE Users SET Iname='Perepiczka' WHERE Iname='Smith';

MySQL is the most popular Open Source database management around today. MySQL is a relational database management system for SQL that can be hosted on many different operating systems. It is Open Source which means it is free and that it can be freely manipulated. MySQL is "a second generation Open Source company that unites Open Source values and methodology with a successful business model."[6]

MySQL, was derived from MSQL as MSQL was not fast or flexible enough for the needs of the user, resulting in a new interface being developed using a similar API interface. MySQL was written in C and C++ and tested with many different compilers. MySQL is adaptive to today's technologies in particular the use of dual core processors because it is "multi threaded using kernel threads" [7]. In fact, MySQL and PostgreSQL are almost the same, and both are sustain by the server's of the school of computing. The advantages of MySQL indicate the efficiency of memory management and the capability of using it with multiple scripting environments such as JDBC and ODBC and some of other server scripting technologies.

PostgreSQL is an Object-Relational Database Management System. "Postgres is the potential successor to the INGERES RDBMS" [8]. Postgres used widely depending on the concept of a Relational Database Management System by inserting abstract data types, rules and data of type 'procedure' holding more semantic and object-oriented characteristics. PostgreSQL is the most powerful Open Source DBMS available and is also supported by the school of computing server. Postgres seeks to perform more to support composite objects, provide user extending for data types, operators and access methods, make active database manages such as alerts and prompts and also to make a few adjustments as possible to the relational model.

Oracle is a Relational Database Management System and was the first to support Structured Query Language (SQL). With Oracle, data is stored logically in the form of tablespaces and physically in the form of data files. "PL/SQL is the procedural language extension to SQL.. PL/SQL is a programming language like C, java or Pascal. In the Oracle world, there is no better way to access your data from inside a program. SQL can be natively embedded in PL/SQL programs"[9]

2.1.3 Web Languages

You can use several possible technologies of the web to organize this project of online tutorial. Because of this, it is essential to investigate the language influence "statically" the component of the project.

HTML is an acronym for Hyper-text make-up language, it is the primmer mark-up language used by web pages designers that can be interpreted and displayed in any internet browser. HTML can be used to provides the client brewers with how the formation of a web document should looks like. However, the final look of the document is assigned to the client. It able you to insert different media like picture, music and videos and also enable the inserting of script languages such as JavaScript. HTML documents use a couple of tags to identify various operations in the document which are indicated between angle brackets. HTML tags can be functional which means it can translate the aim of a piece of text. In addition, it can be presentational which realise how the content is to be appearing and hyper-text able for the making of links to different pages and content. Because HTML display is up to the client, it used for more appropriate appearance for different client browsers. For example, various browsers which may vary in operation would allow greater compatibility and usability between various browsers. The advantages of HTML are that it has been utilise for long time compatible with browsers and is designed to use built-in elements which will not overwrite the presentation of a document. For instance, showing a picture, half the size of off screen as the way CSS may do. The disadvantages of HTML are that it needs some of the 'cleverness' language like XML which has feature to modify several web documents at the same time. Also as long as the designing of HTML has ended, it will no longer be adjustable to new medium.

CSS stands for Cascading Style Sheets. The capability to differentiate document 'body' and document presentation is one of the main aspects of CSS. CSS purposed to inform the browser by the way the document should display, although also purposed to customise for wide range of users. For Example, large font size for optically weaken people. Due to the Cascading feature of a CSS, you can update many web documents and their style by change particular file which will lend to make enormous quantity of modifications easily. CSS can be exterior documents forming the structures of several pages or they can be included within HTML as an embedded layout attribute. The advantages of CSS represent in that it decrease code complication in the web documents since the presentational script is separate. Also, document layouts can be personalized for many users in order to carry out their desires and as stated above, it composes document presentation through multiple documents simpler. The disadvantages of CSS are that the user can disable CSS option on the net browser which would devastate the accomplished displaying effects provided by the programmer of the document and the effects of the CSS differ throughout browsers, particularly when treating with the location of elements which may appear in different positions or may go beyond other page aspects.

XHTML stands for Extensible Hyper-text Mark Up Language which is reformulation of HTML 4.0 to conform to the rules of XML application specification. This means that XHTML is almost the same compared to HTML, except for minor variation in the mark-up, but it is currently a sub-set of XML.XHTML merges the suppleness of HTML and the extensibility of XML. XHTML pages can interpret on all XML enabled devices/browsers and get advantage of the well designed layout of XML. As with XML, XHTML is rearward well-matched with HTML4 enabled browsers by using an internalised slash method inside the tags. The advantages of XTML are that XHTML documents are typed with a well organized layout and doing it clearly where tags finish, XHTML is a dominant language for the newer web browsers and in the near future it will swap HTML 4.0 as the main format for browsers as nobody gone to use HTML frequently. The disadvantage of XTML is whether there is or isn't such a programs or applications which can 'statically' like Dreamweaver, Front page or 'dynamically' use PHP, Perl to create XHTML document files.

Java is referring to a programming language which can be used as a web technology in term of java Applets. Java is an Object Oriented Language which can be inserted within a web pages code as an applet which beneficial from having its individual internal data layout, and oppositely to the HTTP protocol it does store status. Java applets can also be dealt with to store data across many web browsers for quick reference afterwards. Although, the observable limitation of using a java applet is that the user can disable it by the client web browser options.

JavaScript "is a programming language that is mostly used in web pages, usually to add features that the web page more interactive. When JavaScript is included is an

HTML file it relies upon the browser to interpret the JavaScript. When JavaScript is combined with Cascading Style Sheets (CSS), and later versions of HTML (4.0 and later) the result is often called DHTML" [10]. JavaScript is a 'client-based scripting language' allocates a familiar syntactical tradition with 'server-based language' Perl. JavaScript is commonly used as a mean of validation on the client, which ensures that every data pass is in the suitable layout prior to progress it by server technologies. Although JavaScript is useful, it always depends on where it being enabled by the client web browser.

Flash is another technology which can be deployed on the web, it was initially used to create animations and then embed them in HTML. Some businesses like Macromedia believe that Flash can be a replacement for HTML for the following reasons. "Flash movies load faster and save on download time because Flash is vector based whereas HTML is not, Flash intelligently 'caches' it's movies so they don't have to be reloaded and Flash gives the user (the person viewing/using the Flash movie) a more responsive 'rich-client' like experience" [11]. However browsers nowadays do not support flash natively, they may need to install plug-ins which they may not be prompt for also as with new versions of Internet Explorer, flash only has the ability to run when the users click on the flash application and not automatically the way that HTML is used.

2.1.4 Web-Database Connectivity Languages

You can find many technologies available used for connecting web based system to a database system. So, it is important to discuss those languages in which "dynamically" affect the element of the system.

PHP is an acronym for hyper-text pre-processor which can be embedded in HTML. As PHP is on the server side of the web-client to web-server model, when a PHP file is executes the only thing that can be seen from source code is the resulting HTML output. PHP can perform any operations that a CGI program can do but in the case of database connectivity it isn't as flexible in the way that some CGI program are. PHP doesn't use a standard DBI connection for different types of database and therefore some customisation iis needed when connecting to different types of database. Although PHP is seen as the "easy" way of connecting to server side resources, it lacks the complexity and flexibility that CGI programs such as Perl have. This is not to say that they are without flaws, PHP has the advantage that there is no need to load up an external interpreter every time the program is executed. Due to the ability to enable PHP in HTML it allows programmers to create a template design, also content mangers and designers can work separately without both users needing to know what the order has done. PHP is open source and therefore is viable from a cost perspective, and it is also compatible across different platforms.

ASP stands for Active Server Pages, originating in Microsoft's Internet Information Service (IIS). ASP allows for the creation of interactive and dynamic web pages which get around the problems being faced by using multiple browsers. The default scripting language for using with ASP is VBScript although Jscript can be used which is Microsoft's version of JavaScript. ASP pages have the extension ".asp". ASP is fast and easy to implement optimised for multiple threads and multiple users. ASP allows for the separation of the design and content allowing the implementers to focus on what they do best.

JSP is an acronym for Java Server Pages which as with java, it was introduced by Sun Microsystems. It is an extension of Servlets allowing Java to be combined with HTML on the same page." The Java providers the processing and the HTML provides the page layout that will be rendered in the Web browser" [12]. The main advantage of JSP is that it can interpret in many operating system and also it can be useful with any kind of server systems.

Perl is an acronym for Practical Extension and Reporting Language. Perl is a commonly used programming language that includes a mixture of syntax from many Unix facilities and codes. Perl is a server side programming language which fit little tasks, but a problem occurs when it deals with several jobs. Whenever a Perl program execute, it moves to install the external interpreter that used to translate the Perl script into a meaningful thing. Perl is a CGI program language that can connect to external devices; however the limitations of CGI program represent in that they exceed the number of resources than they really need to make function. Even though, some programmers prefer the supplying of Perl as it can be used for several various functions, beginning from updating user accounts automatically towards creating reports. Also, Perl has been adjusted to leave UNIX based world by using mod_perl.

2.1.5 State Saving Technologies

Due to the natural "stateless" of the HTTP protocol, additional technologies is needed to keep the data interim to be manipulated by the database later on.

A Session is "a sequence of pages that a user visits in a website, from the moment they log on to the site until they logs out" [13]. A Session can be preserved in a database with a distinctive identifier and then restored later on. Perhaps it isn't preserved in a database, so in this case, the Session will devastate at any time the user quit the system. Sessions is aim to check whether a user access a website previously, modify context if the user accessed the page previously. For instance, tailoring context derived from some people's answer and there may be want to follow financial commercial information in a transaction. There is an advantage that a server side Session following system because it maintains all progression to the server, Even though, there may be troubles if the user is loss the connection from the web, if the Session id is not transmitted in the URL addressee.

Hidden Form Fields is useful to pass data from one internet page to another possibly to store session recognition. Data frame can pass by a 'GET' method which will transmit the hidden data in the URL. Those happen due to once the data pass, the web browser does not know the source of information in order to identify the URL. However this is not appropriate method for responsive data such as usernames and passwords because The 'GET' method shows the information that stored in the database on the address bar of the browser when it generated. The suitable method in this case is by using the 'POST'' method. The 'POST' method enables data to be passed from user's input via forms rather than transmitted via URL. Thus, a username and password will not be shown in a web page address bar on the browser. Other pros of the 'Post' method contain the unlimited length of world in a query which in the case of transmitting data in the URL could be crucial as browsers can shorten the data. Also as outcome of the 'Post' method, data values are passed through more securely and reliably.

Cookies are described as "pieces of information sent to a browser by a Web Server. The browser then returns that information to the Web server". [14] Cookies is helpful from the angle of security point of view because it only contains specific amount of data. For instance, Cookies will unable to preserve all the data related with signing into an internet banking account. Cookies can be unset by the web browser by posting a cookie with the similar name of the available one but with old expiration date. While, from a client's point of view, when cookies store data such as session identifiers, it is regularly harder to connection to more significant data server side. In addition, there is also a unconvinced issue that if the user has their internet security settings up as high level, they are not enabling cookies to be maintain on the computer. For this reason, any utilities that work based on cookies will not execute successfully.

2.1.6 SQL Tutorials

To reach a successful tutorial system and to come out with new ideas, I have to review the techniques and work done by the current interactive e-learning websites on the internet which aimed to achieve the target of this project to understand and get an idea of how those sites was implemented. Moreover, to discover some part that my web based tutorial may develop over others implementations and to avoid the problems done by them. After investigation, the advantage and disadvantages areas of the current tutorial will be provided in order to carry out some improvements for my project if that is possible.

SQL Zoo.net

URL: http://sqlzoo.net/

This website created by using XHTML language as a base code of the pages and tables for the structure and the result. The outcomes are shown on the same pages as the query statement is created and typed into a text area. Dissimilar to some SQL Tutorials, SQL Zoo enables the using of the WHERE condition statement. The DELETE operation on the website does not really remove the data from the database. It just prevents it from appearing when the user uses the DELETE operation in relation with the SELECT query, except if the SELECT query is done individually then the full outcomes are appear. The CREATE TABLE function does not make a table as they are typically created; otherwise it creates a display of a table which creates a momentary table from data stored in different table. Any fault queries will show an error syntax message about asking the SQL guide for the command user used. The main weakest point to this SQL tutorial website is the truth that it enables a number of RDMS choices being used to employ queries on, outcomes are shortened and it enables for operation such as the Average function and concatenation function. It is indistinguishable which server side method is being used for linking of this website to the database.

SQL Course.com

URL: http://sqlcourse.com/

In comparison with SQL Zoo.net, this website began by introducing SQL meaning. This tutorial website uses XHTML language similar to SQL Zoo and has side navigation menu as well. There is more context on the pages with SQL Course as it more detailed explanation on how to deal with the SQL queries and some various data kinds while not talking about the various query functions such as those talked about at SQL Zoo. The SQL command examination is at the bottom of each query associated page, although there is a page just committed for the command explanations. The DELETE statement does not able removing all the data of a table but shows a message even though the command not works. This website is limit to definite SQL commands to be done on their current page which may be obvious to authenticate from a programming point of view. Further advance queries are enabled to be use but on SQL.Course2.com which is a branch from the original web site.

The technique this web based tutorial queries data and shows it onscreen is by using a CGI script that uses data passed via the 'Get' Method. This looks one of the most exciting utilise of database associated tutorials until now but utilizing a CGI script is more difficult than utilizing technology like PHP. The problem of this website is that it lacks the capability to show any results within the respective page enabling the user to test their query against the outcomes.

Web Cheat Sheet.com

URL: http://webcheatsheet.com/sql/interactive_sql_tutorial/

This website also begins similar to SQL course by defining SQL but without much detailed information. This website provides a search box to look for particular information indicating information on the way of using SQL queries. SQL data kinds are provided on this website. Not like SQL Course, this site does not distinguish between basic SQL queries and advanced SQL queries. Web Cheat Sheet uses an automated query to show table's data for every table on one of the pages as the 'Data Structure' link but on the page there are several links for every table. Although it works ineffectually because it pop-up a new window to show the same data and only moves the attention to the table of concern rather than to custom the context for it. The outcomes are displayed on a separate page to that of the query form but inside the same page window, which also indicates the query used to display the outcomes again and the number of rows as well. The queries on Web Cheat Sheet do not able the use of the semicolon ';' character at the last of the query which should influence the command being used to execute each query. This tutorial website is using the 'POST' method in order to transfer all the data, which means that the query length would be with no limit. For this web site, PHP is being used as a server side technology to allow connection to the database.

2.2 Summary

Upon the investigating multiple existing methodologies to design my system, I have reach a good idea of the methodologies that I am going to use to create the system. First of all, I am going to use HTML for the basic language of the web site without the need of extra function related to XHTML, so HTML is quit enough. Also, I will use CSS technology for the purpose of designing the main structure of the web page. The usage of CSS will be as external style sheets instead of inserting some style codes inside my HTML attributes. This technique purposed to update multiple pages at the same time and that is useful to work dynamically within the context of the source code. In this case, the server side methodology which will handle whole of my database connection tasks is PHP. PHP fit my project as it is capable to embed its scripts within HTML. PHP is straightforward and simple to execute and the main advantage of it is that it is free open source. That's means it is more beneficial compared to expensive and non-free technologies such as ASP or JSP. I have decide to use the MYSQL database which is supported by the universities server as it is being the most powerful Open source database system exist in order to enable to provide the flexibility for the desires of the project. The information would be pass via the hidden form fields. I am aiming to use JavaScript for the purpose of client authentication.