Hotel Management Database Project

This was a final project for my Database Management class
It was a group project; there were two other students involved. We used Oracle 8i Personal Edition
Shown here are several components of the project

1. Hotel Management System Requirements Document
   This is basically a verbal description of what kind of information will be required.
2. Domain Constraints
   This defines the type and size of the data. For example, the Hotel Id would be a Character-string made up of 9 characters
3. Database Schema
   A description of the database.
4. Entity-Relationship Diagram
   A graphical description of the database schema.
5. Sample updates
   Shows data being updated using SQL.

Hotel Management System Requirements Document

A hotel chain consists of several hotels. Each hotel is uniquely identified by its Hotel ID. Each hotel has a name, address, main phone number, toll free phone number, fax number and sales tax rate. The directory of locations is sorted by hotel name and also by zip code.

Each hotel consists of several rooms. Each room is uniquely identified by combining the hotel ID that it is in with the room number Each room has a room type, room rate, smoking/no smoking indicator, phone number, list of amenities, available/unavailable indicator, start and end date of the current reservation (if unavailable) and a miscellaneous notes record. The room rate value refers to a specified list of allowed rates.

There are a fixed number of rates allowed for the rooms. Each rate is uniquely identified by its rate type. The daily rate consists of a flat dollar amount.

The hotel location maintains a list of guests. Each guest is given a guest ID each time they register with the hotel. The first and last name, address, phone number, pager number, mobile phone number, e-mail address of the guest and a miscellaneous notes record are recorded when each registers. A listing of guests by home state is available to the hotel management.

Reservations are maintained by the hotel. Each reservation has a unique id. The guest ID, last name, room number, start date and duration of the reservation are stored. When the guest checks into the hotel, his payment information is recorded and the room to which he is assigned is marked as unavailable.

Payments by credit card or check are accepted by the hotel. Credit card payments include the credit card number, expiration date and card type. An indicator that specifies whether the credit card used as payment is the guest's card or that of a business is stored. Check payments include the routing number, bank name and check number. The payment amount, date of the payment, authorization code, if any, and a miscellaneous notes record about the payment are also maintained.

Hotel Database schema

Domain Constraints

HOTEL
HotelId: Character-string of 9 characters (fixed length)
HotelName: Character-string of up to 20 characters (varying-length)
Street: Character-string of up to 25 characters (varying-length)
City: Character-string of up to 15 characters (varying-length)
State: Character-string of 2 characters (fixed length)
ZipCode: 5 digit integer
Phone: 10 digit integer
Tollfree: 10 digit integer
MainFax: 10 digit integer
SalesTax: floating point number

ROOM
RoomId: Character-string of 9 characters (fixed length)
HotelNum: Character-string of 9 characters (fixed length)
RoomNo: Character-string of up to 5 characters (varying-length)
RoomType: Character-string of 9 characters (varying-length)
RateId: Character-string of 5 characters (varying-length)
Smoking: Character, either Y, or N
PhoneNo: 10 digit integer
Available: Character, either Y, or N
StDate: Date -YYYY-MM-DD
EndDate: Date -YYYY-MM-DD
Notes: Character-string of up to 256 characters (varying-length)

RATE
RateId: Character-string of 3 characters (fixed length)
RateType: Character-string of up to 15 characters (varying-length)
DayRate: Decimal value, precision=5, scale=2

GUEST
GuestId: Character-string of 9 characters (fixed length)
Fname: Character-string of up to 15 characters (varying-length)
Lname: Character-string of up to 15 characters (varying-length)
Gstreet: Character-string of up to 25 characters (varying-length)
GState: Character-string of 2 characters (fixed length)
GState: Character-string of 2 characters (fixed length)
GZipCode: 5 digit integer
Notes: Character-string of up to 256 characters (varying-length)

RESERVATION
ResId: Character-string of 9 characters (fixed length)
GuestId: Character-string of 9 characters (fixed length)
GFname: Character-string of up to 15 characters (varying-length)

RESERVATION (Continued)
GLname: Character-string of up to 15 characters (varying-length)
RoomNo: Character-string of up to 5 characters (varying-length)
ResStDate: Date -YYYY-MM-DD
Duration: 3 digit integer
PaymentId: Character-string of 12 characters (fixed length)

PAYMENT
PayId: Character-string of 12 characters (fixed length)
ResNum: Character-string of 9 characters (fixed length)
CCardNo: Character-string of 19 characters (fixed length)
CCardExp: Date, MM-YY
CCardType: Character-string of up to 15 characters (varying-length)
CCardOwn: Character-string of up to 30 characters (varying-length)
RouteNo: 9 digit integer
BankNo: 9 digit integer
ChkNo: 9 digit integer
DatePd: Date YYY-MM-DD
Amt: Decimal value, precision=5, scale=2
Auth: Character-string of 9 characters (fixed length)
Notes: Character-string of 256 characters (varying-length)

AMENITY
RoomId: Character-string of 9 characters (fixed length)
Amenity: Character-string of 256 characters (varying-length)

Hotel Database Entity-Relationship Diagram

Sample updates (insert, delete, and update).

1. Insert into HOTEL values: HNJ000011, Sleep Well, 23 Sleepy Way Ave., Wayne, NJ, 07470, 973-222-4444, 800-123-1212, 973-223-4445, and 6.00%.
There are no violations of constraints.

2. Insert into ROOM values: RNJ000031, HNJ000013, 205B, Single, SQS, Yes, 973-333-0205, No, 11-2-2000, 11-6-2000, Has VCR and cable TV.
There are no violations of constraints.

3. Insert into RATE values: SQS, Single, and $55.50.
There are no violations of constraints.

4. Insert into GUEST values: HG0000001, Tambi, Nakhjo, 32 Sleepy Drive, Wayne, NJ, 07470, USA, 973-555-1515, 973-555-1222, null, nakhjo@email.address, Repeat customer. Treat with great care.
There are no violations of constraints.

5. Insert into RESERVATION values: HR0000001, HG0000001, Tambi, Nakhjo, RNJ000031, 11-28-00, 4, CCPMC0000001.
There are no violations of constraints.

6. Insert into PAYMENT values: CCPMC0000001, HR0000001, 5421-1111-2222-3333, 11-00, MasterCard, Tambi Nakhjo, null, null, null, 11-27-2000, $150.00, PA0000001, Repeat customer. There are no violations of constraints.

7. Update GUEST set GMobile to 973-555-1232 where FName is Tambi and LName is Nakhjo. There are no violations of constraints.

8. Update PAYMENT set Amt to $200.00 where FName is Tambi and LName is Nakhjo. There are no violations of constraints.

9. Update AMENITY set Amenity to VCR and Jacuzzi where RoomId is RNJ000031. There are no violations of constraints.

10. Update RESERVATION set Duration to 6 where FName is Tambi and LName is Nakhjo. There are no violations of constraints.

11. Update HOTEL set SalesTax to 6.25%, HotelId to HNJ000012 where HotelName is Sleepy Way. There are no violations of constraints.

12. Delete from HOTEL where: HotelName is Sleep Well. There are no violations of constraints.

13. Delete from GUEST where: Address is 32 Sleepy Way. There are no violations of constraints.

14. Delete from RESERVATION where: GFName is Tambi and GLName is Nakhjo. There are no violations of constraints.