Design of an E/R Diagram, view integration, and mapping to the relational data model.

Deadline (Report): Th., Feb. 24 in class

The objective of the project are as follows:

1) Individual Task: Each student designs an E/R schema with respect to his subset of the information requirements; there are 3 subsets A, B, C. Students with group numbers 1, 6, 7 use group A, students with numbers 2, 4, 8 use group B, and students with group numbers 3 and 5 use information requirements group C.

2) Group Task: The members of each group meet and design an E/R schema that is suitable to satisfy the information requirements of all three usergroups.

3) Group Task: The E/R Diagram is mapped to a relational schema; moreover, a small sample database has to be provided that describes one state of the hotel world (3 to 13 tuples per relation are sufficient).

4) Individual Task: Select 3 information requirements of your requirement group and construct SQL-queries that implement the chosen information requirements.

A database for the well-known Bilton company that owns hotels all over the US has to be designed. The database to be designed has to satisfy the following information requirements:

Group A Requirements:

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 May 3, 1995?

2) Does the Bilton Waco have a single room for seven days starting the night of May 4, 1995, 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 May 3, 1995.

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

Group B Requirements:

5) How often did Bill Clinton stay in the Bilton in Waco in 1994, which days did he stay in Waco, and how much did pay for each stay, and what was the form of her payment.

6) What total did Madonna pay, when she checked out on March 4, 1995 from the Bilton Paris(TX), and how long did she stay?

7) How much did Mr. Elvin Jones pay for a suite in the Bilton in Houston on March 8, 1995?

8) Did Madonna and Elvis ever spend a night in the same Bilton hotel? If yes, when and where did this happen, and in which room(s) did they stay?

Group C Requirements:

9) 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.

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

11) What were the total charges of the Bilton in Waco for the following items on April 22, 1995: movies, double rooms, single rooms, suites, telephone, restaurant, and bar charges.

12) List all guests that spend more than $200 in the hotel restaurant of the Bilton Dallas during the last 10 days.

13) How much did guest Elvin Jones already pay for his current stay in the Bilton in Dallas.

Moreover, you can assume the following:

a) today is May 1, 1995

b) The following changes occur in the hotel world.

b1) Reservations are made for n day 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 are deleted from the database

b7) Billing information including individual charges will be kept in the database for 2 years.

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. * assume when you create data that today is May 1, 1995

For other missing information make "realistic assumptions" about the hotel world of your own.

Results of the project have to be described in a project report that consists of the four parts of the project. There will be individual solutions for part1 and part4 and group solutions for part2 and part3.

Notes: Make reasonable assumptions and state them in your report. Your schema should be consistent with the assumptions that you make. Just one report for the group tasks is enough.