The ABC Hotel plans to create a database system to keep track of the information about their room types, guests, and reservations.

ABC Hotel has several room types. Each room type has a unique name (such as Standard, Duplex, Queen, and King), a rental price, and number of rooms. ABC assigns each guest who has made a reservation at ABC Hotel a unique guest number. ABC also wants to record the guests name (first name, last name) and email address. Some old guests are granted a VIP title. ABC Hotel keeps track of the special preference (e.g., close to elevator, breakfast delivery, etc.) of a VIP guest and offers her a little gift upon her future arrivals.

Guests may reserve room types over phone, via travel agencies, or on the Internet. ABC is very interested in analyzing the reservations by different origins (phone, travel agency, and online).

Each reservation is allocated a unique confirmation number. The total rental fee of a reservation is computed based on the rental price of the room type and the duration (consisting of arrival date and departure date) of the reservation. Each reservation is made by exactly one guest for exactly one room type. But a guest may make many reservations for many room types.

ABC Hotel allows at most a 5% over-reservation. The number of reservations for each room type can be at most 5% more than the number of rooms of that type at any time. After the limit of a room type has been reached, new reservations for that type will be denied.

After performing conceptual design for the given case, we can generate both an ER diagram and a specification that includes additional constraints not shown on the diagram. The specification is written in English.

a) Translate the conceptual schema (ER diagram) into a logical schema. Do not make any assumptions about the real world except those encoded in the E-R diagram. Be sure to specify keys and constraints for all relations.

b) What if the requirement has the following change: Each reservation is made by exactly one guest and can be for one or more room type, what changes would you make to the logical schema you already obtained in (1)? You only need to show the changes.