Project1 (Individual Project)
Logical Database Design for a Hotel Management Application
What will be learnt in the project: E/R schema design, mapping E/R to the relational data model, creating relational schemas, writing SQL-queries.
Deadline (Report): Th., Feb. 28 in class
The objective of the project is the 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. The following information requirements have to be satisfied by the database to be designed:
0) List the rooms (category, number, today's price) of all Biltons in the US.
1) 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?
2) 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.
3) How many double-bed rooms does the Bilton in Dallas have, and how many of those rooms are reserved for March 30, 2001.
4) Give all open reservations in any Bilton Hotel for John Smith; what is his rate for each reservation.
5) 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.
6) What would be the room charges, if Mr. Tom Jones(ssn=123456678), guest in the Bilton Waco would checkout today?
7) 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.
8) List all current guests that spend more than $200 in the hotel restaurant of the Bilton Dallas.
9) Cancel Madonna's reservation for the Bilton Paris for March 22, 2001.
Moreover, you can assume the following:
a) Today is March 3, 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 dayly;
b6) Reservations that refer to the past and
information concerning completed stays are deleted from the database
c) Other important information includes:
Project1 consists of the following 4 subtasks:
A) E/R Diagram Design: Design a valid E/R Diagram that is suitable to satisfy
the information requirement 0-9, and which expresses a large percentage
of the constraints in the hotel management world.
B) Mapping E/R to Relational: The E/R Diagram is mapped to a relational schema.
C) Create a Relational Database Database:
Define ORACLE tables for the relational database.
Express key constraints and referential integrity constraints in the
your relational schema.
D) Write SQL queries for information requirements 0, 1, 2, 6 and a give a sequence of SQL
operations that implements Madonna's cancellation.
Results of the project have to be summarized in a project report.
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. Details on what the report should exactly contain will be discussed in a
separate document.
Initial Thoughts what the Report Should
Contain: (very preliminary):
1) the assumptions the students make
2) the E/R diagram
3) the relational schema
4) SQL statements to create the tables
5) execution result to instantiate the tables and
6) execution result of answering the queries
Ying Lin will post the information on my web page at:
http://www.cs.uh.edu/~linying/cs6340.html.