Room Reservation System

							                      CS 4400 PROJECT (Summer 2008)
                                    (Profs Navathe and Omiecinski)


A Room Reservation System For GT student associations
                    (Version 1.0)
This document contains general description of the requirements for the project. Please note the version
number since we are likely to revise it after student feedback. The deliverables for different phases are
mentioned at the end of document.




1. INTRODUCTION
This system allows the officers of GT student associations to make room reservations for the
association meetings. The system maintains a database that contains the information about student
associations at Georgia Tech, members of the associations, and room reservations. The system has
three different groups of users: 1. Administrators, 2. Faculty Advisors, and 3. Students. All users have
three attributes: gtID#, password, and name. There are several administrators in the system.
Administrators monitor room usage on campus and add/delete student organizations. In this system, a
student can be a member of more than one association. A member can be a regular member or an
officer. Only officers can make or cancel room reservations. The required tasks for each type of user
are specified in the next section.

2. TASKS

FOR ALL USERS

T1. User Login:
Description: The users of this task are administrators, faculty advisors, and students.
Input: gtID# and password.
Output: if the account information is correct, the system will open a new window according to the user
type.
Reference: illustration 1.




                         Illustration 1: Login



Version 1.0                                Page 1
FOR ADMINISTRATORS

T2. View Room Utilization:
Description: this module generates the utilization of all the rooms in terms of hours for a given month.
Input: month and year.
Output: the list of rooms and the total of reserved hours for each room in the given month. The output
will be ordered by building number and then by room number.
Constraints:if the input is the current month, all reservations for that month are also included. Rooms
having no reservation for the month are also displayed. The past used hours made by removed
associations are also counted (refer to task T3c).
Reference: illustration 2.




               Illustration 2: View Room Utilization


T3. List/Add/Remove associations:
Description: the administrators can view the current associations, enter new associations or remove
existing associations for room assignment purposes.
T3a. List:
Output: list of current associations and the faculty advisor of the associations.
Reference: illustration 3.
Version 1.0                                Page 2
T3b. Add:
Input: association name, description (optional), dues, faculty advisor.
Output:a new association with the given name and advisor is added into the system.
Constraints: a faculty advisor can advise more than one association. An association must have one and
only one advisor.
Reference: illustration 3.
T3c. Remove:
Input: association.
Output: the association is logically removed from the system. However, the association is not
physically removed from the database.
Constraints: the future reservations for this association must be removed from the database.
Reference: illustration 3.




Illustration 3: List/Add/Remove Associations


FACULTY ADVISORS
After the user logins into the system as a faculty advisor, the system will show him two options: list of
association and list/add/remove members.



Version 1.0                               Page 3
T4. List of associations
Description: the list of associations that the advisor is advising.
Output: the list of associations that the advisor is advising and the number of members for each
association. If the advisor clicks on an association, the system will forward the user to task T5.




 Illustration 4: List Associations Advised by prof. Edward



-Reference: illustration 4.



T5. List/Add/Remove Members:
Description: after the advisor clicks on an association in task T4. The system shows the user a GUI that
allows him to show the list of members, add new members for that association or remove an existing
member from the association.




Version 1.0                                 Page 4
              Illustration 5: List/Add/Remove Members
T5a. List:
Output: a list of members for the association and the corresponding position for each member. The
entries are ordered by gtID#.
Reference: illustration 5.
T5b. Add:
Input: student gtID#, position. The system should allow the user to choose one of three possible five
options: president, vice president, treasure, officer, and member.
Output:the list in task T5a is updated this new member.
Constraints: the adding is denied if the gtID# does not exist.
Reference: illustration 5.
T5c. Remove:
Input: a member in the list..
Output: the member is removed from the association.
Reference: illustration 5.

STUDENTS

T6. Reserve Room:
Description: the system contains the list of rooms available for reservation by the student associations.
In addition to the room information, the system also has a list of buildings where the rooms belong. A
room must be associated to one building. A building has a unique building number and a name. A room

Version 1.0                                Page 5
is uniquely identified by its room number and its corresponding building number. A room can be
reserved any time from 8am to 5pm in any day after the user’s current time. A day is divided into 30
minute time slots starting from 8am. Thus, there are 18 slots per day. If a user wants to reserve more
than 30 minutes, he needs to make multiple reservations. For example, if a user wants to make
reservation from 8am to 9:30am, he must reserve three consecutive time slots (#1,#2,#3). A reservation
is uniquely identified by building number, room number, date and time slot. The reservation must be
attributed to some association.




    Illustration 6: List/Reserve/Cancel Reservations

T6a. List:
Description: list of available rooms for reservation or the rooms that are reserved by the user's
association of which the user is an officer.
Input: a date (day,month,year).
Output: a list of rooms ordered by building number, room number and then time slot.

Version 1.0                                Page 6
Constraints: the user can not select a date from the past.
Reference: illustration 6.
T6b. Reserve:
Input: (from task T6a), building number, room number and then time slot, and an association of which
the user is an officer.
Output: the system is updated with the new reservation.
Constraints:The system only allows the user to make reservation if he is the officer of at least one
association. He can reserve only available rooms for the associations of which he is an officer. A day
has 18 time slots.
Reference: illustration 6.
T6c. Cancel:
Input: (from task T6a), selected reservation.
Output:the selected reservation is removed.
Constraints: If a room is reserved by an association in which he is an officer, he can cancel that
reservation, otherwise he cannot.
Reference: illustration 6.

T7. View Upcoming Meetings:
Output: the association, location, the date and time of the meetings for the associations of which the
user is a member. The meetings are ordered by association name, by date, and then by time.
Constraints: only the associations of which the user is a member.
Reference: illustration 7.




     Illustration 7: View Upcoming Meetings

3. GENERAL INFORMATION
Version 1.0                                Page 7
GROUPS: Each group must have 3 or 4 members.

As a group, you will decide whether to complete the lightweight or heavyweight project options. The
two options are identical for phases I and II, but differ in the deliverable for phase III. Note that the
option of whether you wish to do heavy or light weight can wait until you get into phase III and as late
as the final submission of Phase III.

Heavyweight Option
Groups choosing this option will demo a working implementation of their project to the TA. The
implementation must include a Java or web-based GUI (Graphical User Interface) that uses JDBC
(Java Database Connectivity) or ODBC (Open Database Connectivity) for database access. The SQL
statements you create in phase II will be embedded inside your GUI.

Lightweight Option
Groups choosing the lightweight option will submit working SQL statements for each of the project
tasks and demo the SQL statements to the TA. This option may be appealing to groups with little or no
experience programming GUIs.

Oracle

We will provide you with access to the Oracle Database Management System on ACME. See the
course webpage for further information on how to access Oracle from the ACME command line or
from a Java program.




Version 1.0                               Page 8
DELIVERABLES
PROJECT PHASES

The three phases of the project cover the following tasks. Specific deliverables are defined for each of
the three phases below.

         PHASE             DESCRIPTION                          DUE DATE
         I                 Analysis & Specification             June 10 (Tu)
         II                Design                               June 26 (Th)
         III               Implementation & Testing             July 22 (Tu)
                           Demonstration                        July 22-25

PHASE I (hard copy)
The deliverables include:
    1. ER-Diagram (ER).
    2. Information Flow Diagram.
    3. A list of logical constraints that will be enforced. (Do not include data-type constraints, but
       rather semantic business logic related constraints).
    4. Any assumptions made. Include explanations.
Notes:
    1. The ER must capture the constraints of the system as much as possible whenever applicable, i.e.
       total participation, super/sub class, weak entities. If there is any drop down list/options in the
       system, it should be modeled by an entity.
    2. The design must satisfy all the constraints. The students are allowed to make up additional
       assumptions and constraints as long as they do not conflict with the specified constraints and
       requirements. Those additional assumptions and constraints should be written in ER-Diagram.
    3. You must turn in your report in hardcopy in class with a title page bearing names of members in
       alphabetical order by last name and each member’s email address. If you have a mixed group
       from multiple sections, identify the section against each member.
     Useful Link for Phase I:
    4. http://www.cc.gatech.edu/classes/AY2008/cs4400_spring/methodologyFall2002.ppt

PHASE II (hard copy)
   List of continuing group members (alphabetical order by Last Name),
   Copy of the E-R Diagram from phase I (with any revisions),
   Copy of the Information Flow Diagram from phase I (with any revisions),
   Relational Schema Diagram (with primary and foreign keys identified, referential integrity is
    shown by arrows),
   Create Table statements, including domain constraints, integrity constraints, primary keys, and foreign keys,
   SQL statements for each task

Notes: A set of SQL statements may be required in order to complete one task. However, in such cases,

Version 1.0                                  Page 9
the last SQL statement should show the output according to the specification. If mentioned, the
returned tuples must be ordered according to the specification. The last SQL should resemble the output
as much as possible. Views and nested query may be used to support the tasks. A nested query can be
broken down into views to make the query readable.

PHASE III
Prior to the demo, the TA will give a dataset that contains:
   1. A list of rooms and their corresponding building for reservation.
   2. A list of users (administrators, faculty advisors, and students).
The database has to be populated with this dataset prior to the demo.
LIGHTWEIGHT: write and test a set of working SQL statements to perform the tasks specified in the
project description.
HEAVYWEIGHT: implements a working application with all functionality including the GUI. Source
code will be mailed to the respective TA grading your project by the deadline.
Deliverables for Phase 3 are:
                 Copy of the Create Table statements from phase II (with any revisions),
                 Contents of each Table in your Database,
                 Source Code (documented) for your System,
                 A set of working SQL statements for all project tasks (Lightweight Option)
                 A functional GUI with embedded SQL statements that accesses your database
                  (Heavyweight Option)
                 A system demo to one of the TAs (you will use SQLPLUS if you choose the light
                  weight option)




Version 1.0                              Page 10