Mansfield University CIS3306 Project Report

Design of a Server-Side Database-based Quiz Program Using JSP

and a

Quiz Results Analysis Tool Using Perl and XML

by

John Phillips

bocaweb@hotmail.com

September 21, 2003

Summary

An online quiz program was created for use in the computer courses that I teach. This program allows a student to log in and select from the available quizzes. After completing the quiz, the quiz is automatically graded and the results shown to the student. The student's start / stop time and answers are stored in a database. An analysis program reviews the results and produces an XML report for review by the instructor or for further processing by a grade book program.

The demo programs and source code can be viewed by visiting the project page.

Introduction

A few years ago I developed a discussion board program named coursenotebook that I use in teaching both online and standard courses. However, coursenotebook is lacking an online quiz program. This project is part of the effort to fill that void.

A main quiz program allows a student to login, select a quiz, take the quiz, and see his or her grade. In addition, a variety of utility programs were created to help the instructor administer the quiz database tables. These programs include one to automate the creation of user accounts, one to help the instructor to process the grades, and one to help the instructor create new quizzes. The program to create new quizzes is under development. Code to create and manage student accounts is already part of coursenotebook. This report will focus on the main quiz program and the grade processing program; both developed in partial fulfillment of the requirements for CIS3306.

The Database Design

The quiz application and related utility programs make use of three SQL Server 2000 database tables: JPuser, JPquizzes, and JPresults. The user table is a scaled down version of the one that Coursenotebook uses and contains a bare minimum of information to validate a user logging in. The quizzes table contains all of the quizzes that are available for the student to take. Future versions of this program will allow beginning and ending dates to be set for each quiz so as to control when the student is able to take a quiz. However, for now, the student can take any quiz currently in the table. The results table contains the results from a student taking a quiz. It marks when the student first begins a quiz and when the quiz is finished. It also keeps track of the students answers and score.

Currently students can take a quiz more than once. However, each occurrence is marked by the addition of a new record to the results table. Even if a quiz is not finished, it is still recorded in the results table. In some cases the instructor might want the student to take a quiz over and over for practice or until they achieve a certain score. In other cases, the instructor can simply tell the student that they may only take a quiz one time. If a student takes a quiz more than once then the instructor can simply ignore the later scores and only keep the earliest score.

The database was kept simple and the application currently only supports a single instructor and class. The tables were created using the Microsoft Query program and demo data was inserted. Below is the entity-relationship diagram for the quiz database.

The Quiz Program

The main quiz program is made up of three JSP (Java Server Pages) files and one HTML file. The program has the following sequence: p2form.html -> p2.jsp -> showquiz.jsp -> gradeit.jsp.

p2form.html is a simple HTML form that lets the user enter his or her user ID and password. The standard coursenotebook user id is automatically generated from a class roster and consists of the student's school-assigned email name which is unique for every student. Typically it consists of 7 letters of the last name followed by the student's first initial. The password is chosen by the student using the coursenotebook program. However, for this demo, user ID's and passwords were hand-generated using the Microsoft Query tool. Below is a screen shot of p2form.html.

p2.jsp validates the user's login information and then presents a list of quizzes that the student can take. The student can choose a quiz by clicking on the appropriate radio button and clicking the "Take Quiz" button. Below is a screen shot of p2.jsp.

showquiz.jsp inserts a new record in the JPresults table with the student's id, quiz id, and start time. This is so the instructor has proof of when a student first viewed a quiz. Then the program reads the appropriate quiz record from the JPquizzes table. Each quiz is actually HTML code defining the questions and answers and providing radio buttons for the student to select the best answer. This code is stored in the JPquizzes table's field named quiz. The program embeds the quiz inside the appropriate html, head, body, and form tags and then displays the quiz as an HTML page. Two quizzes were created by hand using the Microsoft Query tool for this demo. However, a Java application is under development to allow the easy "fill-in-the-blanks" creation of multiple choice quizzes in the appropriate format. Below is a screen shot of Quiz 1 in action.

gradeit.jsp takes the answers submitted by the student and then grades the quiz. The quiz's actual answers that are stored in the JPquizzes answers field are compared to the student's answers. The program will process any number of multiple choice questions. Any unanswered questions are counted wrong. The student's answers are stored as a simple string of characters. For example, the string 'abz' would indicate that the student answered question 1 with an 'A', question 2 with a 'B', and question 3 went unanswered (indicated by the 'z'). The student's most recent JPresult record is updated with the stop time, the student's answers, and the score. Then the student is shown the score. Below is a screen shot of what the student might see after taking a quiz.

The Analyze Program

The analyze program answers the question, now what do I do after all of the quizzes have been taken. Perhaps the instructor wants to see how the students are doing on the quizzes or perhaps the quizzes will count as a grade in the course. The analyze program lets the instructor select a quiz and then produces a nicely formatted table displaying the information ordered by user name and then by start date.

This program is written in Perl / CGI. It produces HTML output and XML output. Below are screen shots of the program running.

After choosing Quiz 1 we see the following screen:

It would be useful to have the data in another format that could be easily processed by another program, for example, a grade book program could automatically import the latest quiz grades. XML was selected as the format to use for this purpose. To see the XML data generated choose View Source in the browser. The following screen shot is the XML data from the above example:

The analyze.pl program was originally coded to write the XML data directly to a file name results.xml. However, the Capella server would not allow the file to be written and generated a permission's error. This issue was not pursued as this will not be a problem on the Linux server that host's coursenotebook. The quick fix was to simply display the XML data that is generated by the program as shown above.

Conclusion

The programs discussed have been tested on the MU server. They are not completely bulletproof. For example, security is very lax and not selecting a radio button can give an error in some cases. Of course, the errors are not really critical and one almost has to force the error. There are many additions that can be made to improve the usability and functionality. For example, support for multiple courses will be a priority for future upgrades. Additional improvements can be made to the code itself. For example, I did not make use of the sessions capability of JSP. Nor did I explore the use of JavaBeans. This was entirely due to time constraints. Over the next year I hope to pursue these topics in more details. As I am both the coder and designer of this application, separating the look from the code is not really an issue. However, it would be interesting to actually do it so as to better understand the process.

References

Deitel & Deitel (2003) Java How To Program, 5th ed. Prentice Hall, NJ

DuBois, P. (2000) MySQL. New Riders, Indianapolis, IN

Fields, D. & Kolb M. (2000). Web Development with Java Server Pages. Manning, Greenwich, CT.

LeMay, L. (1999) Sams Teach Yourself Perl in 21 Days. Sams, USA

Microsoft, Inc. (2003) SQL Server Transact-SQL and Utilities Reference. Available at: http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/Default.asp. Last accessed: September 14, 2003.

Phillips, J. (2003) Mansfield University CIS3306 Project: Design of a Database-based Quiz Creator Application in Java. Available at: http://www.programbetter.com/capella/ts5513/report.html. Last accessed: September 21, 2003.