Hotel Project (MySQL)

The final project for CSC 365: Introduction to Databases. This is a Java backend for a hotel application connected to a MySQL server. This application code can be found in InnReservations.java.

There are two tables in this database:

1. rooms
2. reservations

A command line UI allows queries to be run on the database. Queries are generated by connecting user input information to query templates, such as in the example code below. The query string is then executed using the JDBC library.

   // OR-1. Occupancy overview
   private static void displayOneOcc(String date)
   {
      String query = "SELECT DISTINCT RoomId, RoomName, 'occupied' AS Status"
                   + " FROM myReservations re JOIN myRooms ro ON (re.Room = ro.RoomId)"
                   + " WHERE DATEDIFF(CheckIn,  DATE(" + date + ")) <= 0"
                   + "   AND DATEDIFF(CheckOut, DATE(" + date + ")) > 0"
                   + " UNION" 
                   + " SELECT DISTINCT RoomId, RoomName, 'empty' AS Status"  
                   + " FROM myRooms"
                   + " WHERE RoomId NOT IN ("
                     + " SELECT DISTINCT RoomId"
                     + " FROM myReservations re JOIN myRooms ro ON (re.Room = ro.RoomId)"
                     + " WHERE DATEDIFF(CheckIn,  DATE(" + date + ")) <= 0"
                     + "   AND DATEDIFF(CheckOut, DATE(" + date + ")) > 0"
                  + " ) ORDER BY RoomId";
   ...

Notes:

• this system considers CheckOut as unoccupied
• a room code is assumed to be RoomId (OR-2)
• OR-5 includes RoomName, MaxOcc and BasePrice

compilation

• Note: the MYSQL server used for this server is no longer active; server information can be found in the server.txt file

javac *.java
java -cp .:mysql-connector-java-5.1.18-bin.jar InnReservations

Alternatively:

• the Makefile compiles InnReservations when 'make' is run in the directory
• the Makefile runs InnReservations when 'make run' is run in the directory

features

There are three users for this Hotel application:

1. Admin: person maintaining the MYSQL database
2. Owner: person in charge of managing reservations
3. Guest: person making a reservation for the hotel

Admin

• AR-1 Current Status Display

  • Database Status: either full, empty or no database
  • Reservations: total number of rows in reservations table
  • Rooms: total number of rows in rooms table

• AR-2 Table display

  • view Rooms table
  • view Reservations table

• AR-3 Clear database

  • removes rows from each table
  • keeps table schema

• AR-4 Load/Reload DB

  • creates new database if one does not exists
  • populates

• AR-5 Database Removal

  • removes rows and table schema

Owner

• OR-1 Occupancy overview
  • ability to check occupancy of rooms in a date range
  • check occupancy history of a particular room
• OR-2 Revenue
  • combined revenue
  • revenue on a per-room basis
• OR-3 Reservations
  • check reservation information in a date range
• OR-4 Rooms
  • general information about room(s); i.e. general features of the room (number of beds, floor, cost, etc.)
  • total number of nights of occupancy for the room in 2010
  • percent of time the room is occupied
  • total revenue the room has generated in 2010
  • percent of the overall 2010 revenue generated by the room
• OR-5 Detailed reservation information
  • select information about a specific reservation

Guest

• R-1 Rooms and Rates

  • query information about each room to the user

• R-2 Checking Room Availability

  • query information about room availability
  • ability to check rooms within a date range
  • ability to check availability of a specific room(s)

• R-4 Reservations

  • ability to place a reservation given a room and start / end date (adds new row to reservations table)
  • checks if room is occupied before adding reservation to database

---

Note: This if for demonstration purposes only. Do not plagiarize this source code.