6340 Spring 2003
Project1 (Individual Project)

Using Oracle for a Hotel Management Application
Draft

What will be learnt in the project: understanding E/R diagrams and relational databases, creating relations, writing SQL-queries, creating views, using PL/SQL, and writing triggers.

Deadline: Sa., March 15, 9:00pm(electronic submission)

Project Description: The project is based on the given design of an E/R schema and the definition of a relational hotel management database that stores information concerning hotels belonging to the "famous" Bilton hotel chain.

Main Assumptions:

a) Today is Feb 10, 2001.

b) The following changes occur in the hotel world.

b1) Reservations are made for n consecutive days for a particular type of Room (given yes/no as an answer and returning the room price to the costumer, if the reservation was successful).

b2) Reservations are cancelled.

b3) Guests check in (with and without reservations)

b4) Guests check out, also paying their bills.

b5) Room prices change daily;

b6) Reservations that refer to the past and information concerning completed stays are deleted from the database

c) Other important information includes:

¡¤ prices are the same for the same room category for the same hotel on the same day of reservation; however, if you make a reservation on different days you may get a different price.

¡¤ guests can reserve / stay in multiple rooms, and have to pay for all of those, when they checkout.

¡¤ reservations are performed for n consecutive days.

The database you will use was created to satisfy the following information requirements:

a) List the rooms (category, number, today's price) of all Biltons in the US.

b) How many single rooms, double rooms 1-bed, double-room 2-beds, suites, conference rooms of the Bilton Paris(TX) have been reserved for March 7, 2001?

c) Does the Bilton Waco have a single room for seven days starting the night of March 5, 2001 and what would be the rate for the 7-day stay.

d) How many double-bed rooms does the Bilton in Dallas have, and how many of those rooms are reserved for March 30, 2001.

e) Give all open reservations in any Bilton Hotel for John Smith; what is his rate for each reservation.

f) Print the bill (the bill should contain the total amount as well as a listing of individual charges) for Mr. Tom Jones (ssn=123456678) --- he is checking out from the Bilton Waco.

g) What would be the room charges, if Mr. Tom Jones(ssn=123456678), guest in the Bilton Waco would checkout today?

h) What were the total charges of the Bilton in Waco for the following items for today (March 3, 2001): movies, double rooms, single rooms, suites, telephone, restaurant, and bar charges.

i) List all current guests that spend more than $200 in the hotel restaurant of the Bilton Dallas.

j) Cancel Madonna's reservation for the Bilton Paris for March 22, 2001(starting date is March 22, 2001).

E/R Diagram

The Relational Schema of the Hotel Management Database:

Category (cname)

Hotel (hid, city, state, country)

Hotel_Menu (hid, cname, day, rate)

//cname: category name, hid: hotel id,

//rate: unit price, only good for room rate, not for service. The charge for service is provided //in Service_to

Hotel_Room (hid, no, cat)

//no: room no

//cat: category

Person (ssn, name)

Room_Stay (hid, no, ssn, from_d, to_d)

//from_d: starting date; to_d: end date

Room_Reserve (hid, no, ssn, from_d, to_d)

Service_To (ssn, hid, stype, day, charge)

//stype: type of services

//charge: one time charge for this service to this person at that day

 

Task 0: Try to understand the E/R diagram and the relational schema of the hotel management application

Task 1: Create a new table and fill a given database with data

In the Table Definition File, all tables of the above hotel management are created, except the Room_Reserve table. The task of this subproblem is to create the table and fill it with data that are consistent with the data that are already in the database. More specifically, follow the following approach when creating the Room_reserve table:

1.    copy Table Definition File and save it as an create.sql file

2.    Add one more table definition for the missing relation Room_Reserve to this sql file that is consistent with the definition of the other tables.

3.    Write a separate sql file to drop all tables named drop.sql.

4.    Run the create and drop file to practice table creation and table removal

5.    Include the create and the drop file in your project report.

Fill the relations of the hotel management database using the SQL commands that can be found in the following files (one file for each relation of the relational schema): ins_category, ins_hotel, ins_hotelMenu, ins_room, ins_person, ins_stay, ins_service. Follow the steps listed below to perform this task:

1.      copy all above files and save as an sql file (named ins_<>.sql)

2.      create an sql file named: ins_reserve that fills the Room_Reserve table with "realistic data (at least 20 tuples should be inserted into the file). Your tuples should be consistent with the other data in the database and should be suitable to become answers (or part of answers) for those queries that you will write in Task2.

3.      Do not change the "insertion" files for the relations we provided to you (if there is a problem using these files unchanged, please let us know).

4.      create another file named insert.sql. in this file, you write sql command to insert all data into 8 tables above by simply saying @insert.sql. for example: if you won¡¯t change the name style I used, the insert.sql should look like this:

@ins_category;

@ins_hotel;

@ins_hotelMenu;

@ins_room;

@ins_person;

@ins_stay;

@ins_reserve;

@ins_service;

5.      Run all insert files by type command: @insert to make sure all tuples are inserted successfully

6.      print the data of each table. Attach the final version of all these data into your report.

Task 2: Write SQL queries to satisfy the following information requirements:

1) How many single and double-one bed rooms does the Bilton in Houston have, and how many of those rooms are reserved for Feb 11, 2001.

2) Print the bill (the bill should contain the total amount as well as a listing of individual charges) for Mr. John --- when he checks out from the Bilton Kingsville.

3) What were the total charges of the Bilton in Houston for the following items for Feb 10, 2001: double-1 rooms, single rooms, telephone, restaurant, movie, and bar charges.

4) List all current guests that spend more than $100 in the hotel restaurant of the Bilton Houston.

5) Mr. Mark's buisiness meeting has been moved from Houston to Kingsville. Therefore, cancel Mr. Mark's reservation for the Bilton Houston for March 22, 2001(starting date) and reserve the same kind of room in the Bilton Kingsville instead!

Follow the following steps for Task2:

1.    Modify your ins_reserve file if necessary.

2.    Create a single sql file for each query

3.    Run each query

4.    Print the running result of each query

Task 3: Check-based constraints in Oracle:
Add check constraints to the Oracle Schema that do the following: when customer wants to make a reservation, check if the desired starting/end dates are consistent (the end date has to be after the start date, both dates cannot be in the past).

Show the revised table creation/its successful declaration, and the response of Oracle for 2 inserts that violate one of the above constaints.

Task 4: Using Views
Define two useful views for the hotel management database that facilitate querying the database. Do not only submit the definition of your views, but also discuss (in a paragraph or two) the benefits that each view definition provides for the hotel management application. Name these views as view_01.sql and view_02.sql

Task 5: Writing PL/SQL functions and procedures
Define a num-double-available operator (using PL/SQL stored functions and procedures) that takes a day, and hotel-id as its input, and returns the number of double rooms that the hotel has available for this particular day. You can assume that persons that use this operator will only use the present date or future dates when using this operator.

Write another PL/SQL function find-stay that takes a persons ssn and a day as an input and returns the hid of the hotel the person stays at this particular day. If the person does not stay in any Bilton hotel that day the function returns the string "notinhotel". You can assume that this function only refers to current and past stays, and not to stays that will occurr in the future

Hand in listings of your code(named procedure_01.sql and procedure_02.sql) and scripts showing each of them called at least once each. Also, show in the script the results of queries that demonstrate the functions have had their intended effect.

Task 6: Using PL/SQL statements, cursor, and loops
Add a new table to your relational schema Res-next-28days(hid,day,#res) that stores the number of reservations of a particular hotel for tomorrow and the following 28 days. Write a PL/SQL program (named procedure_02.sql) that fills this table with data (using the reservation information that is already in the database).

Print the content of the Res-next-28days table after running your program.

Task 7: Writing triggers
Write an ORACLE trigger (named trigger.sql) that updates the contents of the Res-next-28days table, in the case that a new reservation is made, or an existing reservation is cancelled.
For the trigger program hand in your code and a script showing how the trigger is declared. Also, the script should show, for each trigger, the effect of four database modifications. Three modifications should activate the trigger, and the fourth one not. Execute in the script queries that demonstrate that the trigger had the intended effect in the first three cases, but did not do anything in the last case.

Submission List:

1. Create and instantiate tables (both hard copy and electronic submission)
2. Problems1, 2, 3, 4, 5, 6, 7(both hard copy and electronic submission; for the explanation parts, submit hard copy only)

For electronic submission, following these steps:

1.    save all code files into a folder named YourLastName_FirstName (if your name is too long, FirstName can use initial)

2.    zip this folder to a zip file: LastName_FirstName.zip

3.    send this zip file as an attachment to htu@bay.uh.edu clearly indicate 6340 Project01 in the topic line. Please only send them once, else I will only use the last one you send.

The report should include source code, execution results and explanations if applicable.  Your report should be well organized, summarized and clearly formatted to show me your solution to each tasks, the whole steps to run your programs ,running results of each step and any explanations if necessary.

If information to solve a particular problem is missing, make "realistic assumptions" about the hotel world of your own and state those in your report. Your submitted solutions should be consistent with the assumptions that you make.

*note: when you save your file, put in **.sql in the file name field, and in the next field, please be sure to select ALL File as the file type. Otherwise, you file will be named **.sql.txt. it is still a text file.