CIS3306 Project
Report
Design of a Web-based
Database-driven Room Reservation System
John Phillips
bocaweb@hotmail.com
March 22, 2003
Executive Summary
An on-line room reservation system was created to replace the current paper-based
method used in the Computer Information Science department at Mansfield University.
In order for this program to be useful it had to be easy to use. Hence, a
calendar interface was used that closely resembles the current paper-based
method. The primary advantage is that students and instructors can now check
to see if a room or computer lab is available from their own home or dorm
computer. In addition, instructors can make their own reservations without
burdening the department secretary.
A MySQL database was used to store data about the rooms, users, and reservations.
An application was written using the PHP programming language to create a
user friendly web-based calendar interface to the database. The application
uses SQL (Structured Query Language) to interact with the database and then
sends HTML (Hypertext Markup Language) back to the user's browser. A CSS (Cascading
Style Sheet) is used extensively to control the formatting of the output.
Introduction
The Computer Information Science department at Mansfield University had been
receiving complaints from students that the main computer lab was often in
use by classes. The students hated to make the trip to the lab only to find
it already in use by a class and closed for general use. With the current
scheduling system, instructors would tell the department secretary when they
needed that lab and she would record the date and time in her notebook. This
method worked well in keeping two or more instructors from trying to use the
lab at the same time, however, it did nothing to inform students when the
room would be in use and when it would be free.
The solution to this problem was to make the reservation information available
over the Internet. Students can then check the schedule before attempting
to visit the lab. In addition, the department secretary would no longer be
burdened with the scheduling chore. Rather, each individual instructor can
log in and make their own reservations through a simple point and click interface.
The User Interface Design
I wanted to keep the user interface as simple as possible. Therefore a calendar
interface was chosen. Students and faculty will be able to access the reservation
application from the department web page. At a glance one can see the times
the main computer lab is reserved for the current month. The user can click
the arrows on the top right or top left of the calendar to move forward a
month or backward a month. In addition, the user can use the drop down boxes
to choose a different month or year. A drop down box is provided to choose
other rooms, however, currently only Elliott 207, the main computer lab, is
supported. Below is a screen shot of the student's view of the application.
Instructors can log in to the application in order to make or delete a reservation.
To keep the user interface simple, a form is provided for each day of the
month. The instructor simply chooses the time from the drop down boxes and
then enters a note if desired. Upon clicking the reserve
button the database is updated with the new reservation. The instructor is
also presented with delete buttons for each of his/her reservations. Clicking
a delete button will remove that reservation from the database. To help instructors
see their own reservations, they are marked in red as seen below:
The Database Design
The reservation system is built around the MySQL database software package.
MySQL is a popular open source relational database management system that
is included in the RedHat Linux 8.0 standard distribution. Five tables are
used by the reservation application. The tables were normalized to 3rd normal
form. Complete data definitions are listed in this document.
The relationships between the tables are shown in this E-R diagram:
The purpose of each table and some of the key fields are briefly described
below:
The Room Table
The room table is used to store information about each room that can be
reserved. Although the building and room fields can be considered a composite
candidate key, I chose to have the database create a unique roomId primary
key and to treat building name and room number as informational data that
could possibly change over time. Each room has a building secretary who
is responsible for that room.
The User Table
The user table is used to store information about each user who is allowed
to make reservations--typically the instructors and the department secretary.
A type field is included that can be used to distinguish between an instructor,
a building secretary, and the site administrator. Although login is a candidate
key, I chose to have the database create a unique userId primary key and
to treat login as informational data that could possibly change over time.
The Reservation Table
The reservation table is used to store information about each room reservation
transaction. ReservationId is the primary key and is a unique number that
is automatically generated by the database. Reservations that are deleted
are actually just given a status or 'd' whereas active reservations are
given a status of 'a'. This is to help with system security in case of abuse
of the reservation system.
The Session Table
The session table is used by the reservation application for maintaining
the session information when a user logs in. When the user logs out the
session record for that user is deleted. SessionId is the primary key and
is a unique number that is automatically generated by the database.
The Log Table
The log table is used by the reservation application for system security
purposes. It tracks who logs in and out and when. LogId is the primary key
and is a unique number that is automatically generated by the database.
SQL Statements
Various SQL statements are used throughout the application code. For example,
the query to find the reservations for a given day within the current month
is built up in the string variable $q in the following way:
$q = "SELECT r.reservationId, r.startTime, r.endTime, r.note, r.status,
u.userId, u.initials ";
$q.= "FROM ${YSP}reservation r, ${YSP}user u ";
$q.= "WHERE u.userId = r.userId AND date = '$current' AND r.status =
'a' ";
$q.= "ORDER BY r.startTime";
Here we are selecting the starting and ending times of the reservations along
with notes and the user's initials from the reservation and user tables. The
tables are joined together with the clause WHERE u.userId = r.userId. The
reservation records to be retrieved are limited to the current date held in
the PHP variable $current. In addition, we only retrieve reservations that
have an active status; that is, where status = 'a'. Finally, we would like
the reservations sorted in order by time, hence, the ORDER BY r.startTime
clause.
The ${YSP} variable seen in the above query is a prefix that is added onto
each table name. This facilitates creating multiple versions of the application
and database tables over time. By changing the value of this constant in one
place (common.php) a new version of the software and database tables can be
created in the same database space as previous versions. YSP stands for year
semester prefix.
The Code
The application was coded using the PHP programming language. This language
is designed for database-driven web applications. The main.php file is what
is normally executed and displays the reservation calendar to the user. If
the user logs in, control is passed to login.php which validates the user
and then creates a new session. Control is then passed back to main.php along
with the session string. Main.php validates the session string and then displays
the calendar with forms to allow the instructor to make a reservation. When
the instructor logs out, main.php deletes that user's session information
from the session table. Main.php then displays the normal calendar without
the reservation forms.
The code is organized as follows:
main.php is the main program
that generates the room reservation calendar.
common.php contains all of
the functions used by main.php.
login.php contains the code
necessary to validate the user's name and password and to create a new session.
This code is in a separate file as it is only needed to establish a new session.
Keeping it separate from main.php speeds up the application as login.php rarely
gets called.
reservation.css contains
the cascading style sheet for the site. This controls the look and feel of
the calendar. It is derived to some degree from one of Meyer's On CSS
examples [Meyer 2003].
The Implementation
The department has a small Linux server available to support this application.
The RedHat Linux 8.0 server has an Apache web server, the PHP programming
language, and a MySQL database engine installed. An account was created on
this system and the mysql query tool was used to create the database tables.
The PHP code was developed and the application tested. The system will be
tested by faculty during the last two weeks of April and based on that feedback,
further refinements may be made.
The reservation application can be tested here.
Type jphillip
and mu to
login and make a reservation. Note that either Internet Explorer version 6
or above or the latest version of the Mozilla web browser is required to test
the reservation system.
Conclusion
As mentioned, the system will be tested later this semester. Based on feedback
from faculty, further improvements will be made. If the system catches on
then additional work will need to be done to support multiple rooms. It is
envisioned that the building secretary will be able to override any reservation
for the rooms the secretary is responsible for. The database has been designed
with this in mind. However, some additional coding will need to be done to
implement these features. Finally, additional code will need to be added to
create a point and click administrator interface. Currently, administration
must be done using SQL commands and the mysql query tool.
References
Apache HTTP Server Documentation Project (2003). Apache HTTP server version
2.0 Documentation. http://httpd.apache.org/docs-2.0/.
March 22, 2003.
DuBois, Paul (2003). MySQL. Indianapolis: New Riders Publishing.
Meyer, Eric (2003). Eric Meyer on CSS. Indianapolis: New Riders
Publishing.
Phillips, John (2003). Room reservation demo site. http://157.62.24.146/%7Ereserve/reservation/y03b1/main.php.
March 22, 2003.
The PHP Group (2003). PHP manual. The PHP Group. http://www.php.net/manual/en/.
March 22, 2003.
Redhat Corporation (2002). The official Red Hat Linux reference guide.
http://www.redhat.com/docs/manuals/linux/RHL-8.0-Manual/pdf/rhl-rg-en-80.pdf.
March 22, 2003.
Widenius, Michael, Arjen Lentz, and Paul DuBois (2003). MySQL reference
manual. MySQL AB. http://www.mysql.com/documentation/mysql/bychapter/index.html.
March 22, 2003.