This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.
System Architecture: The Online SQL Assessment application is based on three tier architecture. Three-tier is a client-server architecture in which the Presentation layer, application layer and the Data Source Layer are maintained independently. In a Web based Application, the presentation layer is content rendered by the browser. This content could be generated either statically or dynamically. Figure shows the three tier architecture of the System.
The layers present in our system are:
This layer is the user interface of the application. Students and tutors can access the application through the web browser on any computer. The web browser should support the technologies such as XHTML, CSS and Java Script. Through this interface the users can access the information from the database. In this application the user interface is developed very user friendly so that they can be accessed very easily.
The Application Layer:
This layer acts as an interacting layer between the users and the database. When the user makes a request from the user interface, the server receives and processes it and then outputs the required result from the database back to the web browser. This layer is also called Logic tier as it processes the commands and makes logical decisions. In the Online SQL Assessment application, Apache web server acts as the server and PHP, a server side scripting language is used to connect the database.
The Data Source Layer:
This layer consists of a database where all the information that is required by the application is stored and retrieved. This layer keeps the data independent from the application servers. In our application, the data is stored in the mySQL database. It stores the information such as student and tutor login details, database tables and their SQL questions, students' records etc.
Choice of Tools
As per the requirements of the application, it requires a web server which accepts the HTTP requests from the web browser. Apache is the most commonly used web server and it is more efficient. This server is available for free which makes it easier to use. So for this application, Apache is used as web server.
A database engine is required which stores the information related to the application. MySQL is a database engine which is highly compatible with Apache server. The fact that MySQL is free and reliable makes it an appropriate choice.
The programming language which is used to create the dynamic web pages for this application is PHP. As PHP is considered to be open source, easy to use, fast and customizable, it is a good choice for developing the web pages.
XAMPP(X-Cross Platform,Apache,MySQL,PHP,PERL) is a free and open source cross-platform web server package consisting of Apache Server, MySQL database and interpreters for the scripts written in the PHP and Perl programming languages. XAMPP is available for Windows, Linux, Solaris and MAC OS X, hence it is called as the cross platform. The XAMPP Server is to be installed on the University Server which acts as the application server that is set up by the administrator. This allows the users to access this application through web browser from any computer.
Designing Database Structure:
A relational database is required to store the information that is constantly created and updated. All the data related to the system and its functionality should be kept in one main data store. The data will be handled by client/server database (MySQL). And also, the information related to the application, i.e. the SQL questions, tables related to the questions, student and tutor login tables, session tables etc will be stored in one database.
The following tables are required for the application in our database.
Students table: This table maintains the details of all the students who are registered. When a new student is registered, this table is automatically updated. The required information can be retrieved from this table when requested. The schema for this table is
In this table, the StudentId is the primary key.
Tutors table: This table maintains all the information related to the tutors. Once the new tutor is registered, the table gets updated. The schema for this table is
Questions table: This table stores the questions that are created by the tutors for the database tables. The tutor has to enter an answer for the question he/she has created. This table stores those answers for each question and relates them. The schema for this table is
The attribute qdb stores the database name of the particular question id. question field contains the question created by the tutor. The fields selectq,fromq,whereq,groupbyq,havingq,orderbyq stores the respective fields for the answers typed by the tutor. addedby attribute stores the tutor name who has added the particular question.
Sessionresults table: This table stores the record of each student after he logs into the system, such as the number of questions he has attempted, number of questions he has answered correctly etc. The students can view their previous records. The data for the previous records is retrieved from this table. The schema for the table is
The following tables are the database tables for the students to work on. The questions related to these databases are stored in the questions table.
Creating Database tables:
As it is mentioned earlier, XAMPP server is to be set up on a host machine. The XAMPP server includes MySQL database engine which enables us to create new databases and different database tables in a particular database. phpMyAdmin is a feature offered by XAMPP in which we can easily create, update or delete the database tables within a particular database. The phpMyAdmin presents a user friendly interface to create new database tables and insert data values into the fields. By this way, the required tables which are explained above are created using the phpMyAdmin feature in the XAMPP server.
Alternatively, it also offers MySQL command prompt in which the SQL statements have to be written to create and insert values into those tables.
For example, to create the students table the following SQL statement should be written
CREATE TABLE students(
id INT NOT NULL AUTO_INCREMENT,
student_id VARCHAR(10) NOT NULL,
fname VARCHAR(20) NOT NULL,
lname VARCHAR(20) NOT NULL,
dept VARCHAR(10) NOT NULL,
gender VARCHAR(8) NOT NULL,
email VARCHAR(30) NOT NULL,
pwd VARCHAR(42) NOT NULL,
dateOfCreation VARCHAR(40) NOT NULL
PRIMARY KEY (student_id));
Also for inserting values into the fields, INSERT statement should be written with the necessary values.
User Interface Design:
Introduction: The User Interface is designed to make the students and tutors very easy to access the application. Various Online SQL Assessment tools have been referred to produce a good user interface. The interface is designed in such a way that the user can navigate to all other functions of the application very easily. The programming language which is used to create the dynamic web pages is PHP. Although other languages like ASP and JSP are available, it is very easy to develop web pages through PHP and it is more compatible with MySQL and Apache.
Layout and Navigation: The web page presents a very simple layout where the content of the application is presented in the middle section of the web page and the navigation bar is present at the left hand section of the page. A dark background colour is chosen for the web page for a good look. By using the Navigation bar, the user can navigate to the other web pages. The navigation menu contains links like Home, Previous Sessions, User Guide, About Us, Contact Us and Change Password links, which when clicked opens the respective pages. There is a constant display of time on the webpage and logout button.
Here are some of the screen shots of the user interface of the application.
i) This is the home page of the student when he logs into the system, where he can choose the data base from the selected list to work on.
ii)If the Student chooses customers database in the list, the following page with customers table and the questions related to it comes.
Implementation Strategy: The Online SQL Assessment application is developed after investigating the previous Assessment tools like SQLify, SQLTutor, AssesSQL etc. All the requirements for the application are gathered and they are implemented in Incremental Development strategy.
The application was mainly classified into the developing User Interface and the Database. Firstly, the User Interface is developed by creating web pages for the application. Later, Database is also designed in parallel with the User Interface Development.
The strategy is to develop the application incrementally. Initially, a single database table and the student login system are developed. As the application worked according to the requirements, the tutor login system was also developed. In the tutor section, some extra privileges are given to the tutor, like adding a question to the database and editing it. These features are developed after the student section worked properly and additional database tables are added accordingly.
Testing and Validation:
Consider another example, the tutor can create questions to the existing database tables. In the tutor Question Creation Page, the tutor has created a question, but did not provide any answer for the question. So, necessary validations are put in place so that an error message is provided. When the tutor clicks the ADD button, and messages box pops up with message that 'Please provide answer for the question'. This can be seen in the figure.
The application is tested by the users i.e. students and the tutors and the output is delivered as expected. Consider the example of a student logging into the system and selects the databases he chooses to work with. A list of questions will be displayed and when he selects a question, he is expected to answer the query in the fields provided. Then the system is expected to provide the result set for the question. This is shown in the figure
When the user answers the query in the fields provided, the result is displayed as shown. Also the feedback whether the given answer is correct or wrong. If the answer provided is wrong, the reasons for the wrong answer are provided in the feedback.
The System requirements for the application are specified in Chapter 3. The Application is tested to evaluate its compatibility to the computing environment.
-The application is tested on the Windows and Linux operating systems and is compatible. As XAMPP is cross platform server, it is compatible with both the operating systems.
-The database engine used is MySQL and it is compatible with the Apache Server and both the operating systems.
As the application is a web based application, it should be tested on different web browsers and ensure that the users have the same visual experience irrespective of the browsers. Also in terms of the functionality, the application must behave the same way on different browsers. The application is tested on the browsers FireFox, Internet Explorer, Google Chrome and Opera. The application is compatible on all these browsers and the functionality and visual experience is same across these browsers.
The Online SQL Assessment application has to be evaluated on various aspects like Usability of the application, system requirements, easy of access, performance etc. The users for this application are students and tutors. The application has to be checked with them so that it satisfies the user requirements and get feed back from them for any further modifications.
The method that is used to evaluate this application is Questionnaire and Interview. After the application is developed completely, it is demonstrated in front of different students and couple of tutors. Certain questions were asked on the different aspects of the application like navigation of web pages, use of colour, font, functionality in different browsers, usability of the system, performance etc. Their feed back was taken into consideration and few modifications were made based on the feed back. Also an interview is made on the students and tutors who used it. The interview questions include 'Does the application help in learning SQL?', 'What changes should be made to the application to make it more efficient?' 'Is the interface user friendly?' The feed back from the interviews is also considered for further modifications.
Assumptions: The system is reviewed with different students and tutors. It is assumed that the students know the basic concepts of SQL to work on the data base tables and queries. As the system is intended to develop the students' skills in learning SQL, it provides with different database tables and queries. The student is expected to know how to retrieve the data from a table for a given query. He should have knowledge on SQL commands like SELECT, GROUPBY, ORDERBY, HAVING etc. The system provides feedback if the student gives wrong answer to a query.
Analysis of Review:
Students who are questioned and interviewed gave their valuable feedback. The main points of the review of the systems are:
The application is helpful for the students to improve their SQL querying abilities.
Most of the students are comfortable with the navigation of the web pages and the ease of access of the application. Also the interface is user friendly and visually appealing.
Students are satisfied with the feed back that the system gives when an incorrect answer is given. Also the record of previous sessions is helpful for the students so that they can view their progress report of their previous sessions.
In the tutor section, adding the questions to existing database and editing them is helpful for the tutors for better control of the database tables and the questions.
All the students' records can be viewed by tutors, which is very useful to understand the progress of the students.
Recommendations arising from the review:
The students and tutors who reviewed the applications gave their valuable feedback and recommendations to make the application more efficient. The suggestions can be summarised as:
-The application would be better if has queries on connecting two different database tables.
- Nested queries should be included in the application.
- Also in tutor perspective, tutor should be able to add a new database and add questions to it.
Some of the suggestions on user interface are taken into consideration and modification is made according to it. Due to the time factor, some of the other suggestions could not be implemented and they would be helpful for future work of developing the application.
Most of the requirements of the application are satisfied and are working. The following are the functionalities of the system that are working in full potential:
The registration and login functions of the users are easily developed.
A result set is obtained when a student answers a particular query. Also feed back is provided by the system, if the answer given is not correct.
The student can view his previous sessions' records. Each session details are recorded and are shown when a student requests.
The tutor can add the questions to the existing databases and can edit the questions they have created.
The tutor can view all the students' previous records, there by enabling to assess their performance.
The administrator can manage all the users and also the database of the system.
All the forms which have input fields are validated and can be verified.
Though the application is successful in meeting most of the requirements, it has some limitations. These limitations were focussed by the students and tutors in the review of the system. The following aspects are the short comings of the system:
The previous sessions' records of a student give only the summary of their history. It would be better if the students can get a detailed report of their previous history.
The application does not work with the nested queries. In SQL for complex queries, nested queries are essential, so it would make the system complete if the nested queries also work. However, attempts at making it possible seem to be in near future as the idea of implementing it is clear.
In spite of the fact that the system has these little flaws, it is very helpful for the students to improve their SQL querying abilities. It also helps the tutors to track the record of their students and assess them based on their performance. This system could be of great interest to many Universities and educational institutions due to the educative functionalities that it provides.
Recommendations for the future work: