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

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_hotelRate, 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). Include at least 3 future reservation of Mr. Marks in the table. 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. Be also aware of the fact that inserting data to the new relation might make it necessary to add additional data to other relations (e.g. if there a future reservation is made on a particular day there must be a rate for the day the reservation is made in another table).

3.      Do not change the basic relations we provided to you.  For data consistency and answering queries purposes, you may want to add more data to make your answer reasonable.  If you find any problems, report to TA.

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_hotelRate;

@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) 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!

5) Give all the reservations (rate, startdate, enddate, hotel id, location) of Mr. Mark as of today.

6) Miss Lin reserved a single room in Waco Bilton on Feb 5^th, 2001, if she reserves the same room in Houston Bilton on Feb 19^th, 2001, how the price change in percentage?  Remember hotel rates depend on the day the reservation occurred.

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 starting and 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 constraints.

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. Otherwise you lose most points.  Name these views as view_01.sql and view_02.sql. 

Task 5: Writing PL/SQL functions and procedures
Define a num-single-available operator (using PL/SQL stored functions and procedures) that takes a day, and hotel-id as its input, and returns the number of single 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-hotel that takes a person SSN and a date 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 should return the string 'wrong date' if called with incorrect dates or dates that are 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-20days (hid,day,#res) that stores the number of reservations of a particular hotel for the coming 20 days starting from tomorrow. Write a PL/SQL program (named procedure_03.sql) that fills this table with data (using the reservation information that is already in the database).

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

Task 7: Writing triggers
Write an ORACLE trigger (named trigger.sql) that updates the contents of the Res-next-20days 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 Five database modifications. Three modifications should activate the trigger, and the remaining two 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 two cases.

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 zhu@cs.uh.edu clearly indicate 6340 Project01 in the topic line. Please only send them, 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, steps necessary to run your programs and for running each step and any explanations if necessary.  You will lose a lot of credit, if you submit solutions in inproper format or if solutions are not clearly documented and explained or the explanation is missing, cannot be understood, or is incorrect.

Academic Honesty:

*Please 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.