Each hotel provides a number of services. For instance, Food & Meals, Accommodation, Event

venues, Gym, Laundry, Entertainment, Site$-$Seeing Tours, Taxis, etc. Each service category

has a unique code, name, description and type of service.

All services offered by the hotel and resort $($called service items$)$ needs to be maintained. Each

service has an id $($unique$),$ name, description, restrictions, notes, comments, status, available

times, base cost, base currency and capacity. Service items may use certain facility types in

the hotel to offer the service. For instance, a buffet lunch is a service item offered at a restaurant

$($i$.$e$.$ facility$)$ in the hotel. The base cost is cost to the hotel to provide the service item and is

determine to calculate profit$/$loss$.$ The base currency is the currency used for the base cost. For

instance, a room in Australian hotel will have a base currency is AUD while a room in an Indian

hotel has a base currency in INR.

The services of the hotel is offered to guests as individual services or may be packaged as

needed. There are a number of standard packages. For instance, a standard half$-$board

package consists of a standard room for a single person per night with breakfast meal included.

The marketing department of the group or front$-$office manager of the hotel can create

customized promotions or holiday packages. For instance, a package deal may consist of $$a $7$

night stay at Hotel Paradise for two in a standard room with breakfast and dinner meals provided

for the cost of $$999/-.$

Services and packages offered by the hotel are advertised. Advertised service$/$packages has

an advertised price. The advertised price for the service may be seasonal. Therefore, each

advertised service$/$package has an advertised price with a start date and an end date when the

advertised price is valid and an employee that authorizes the advertised service$/$package$.$

Each advertised service$/$package an id $($unique$),$ a name, description, one or more service items,

a start date and end date when the package is offered, advertised price, advertised currency,

inclusions, exclusions, status, grace period and employee authorizing the advertised

service$/$package$.$ Grace period in days is the time until which a service$/$package can be

cancelled without any charge.

Customers can make a reservation for services$/$packages advertised by the hotel via online,

phone or in person. A reservation has a reservation number $($unique$),$ a customer making the

reservation, a list of all advertised services$/$packages booked under the reservation and

payment information for the reservation. A $25\%$ deposit is charged at time of reservation to the

customer$$s credit or debit card.

For each package$/$service booked under the reservation, you need to keep track of the

advertised service$/$package which the booking is coming under, quantity booked, start date,

end date of the booking, facilities reserved for the booking $($such as rooms etc.$)$ and their start

date$/$time and end date$/$time and guest details $($name$,$ address, contact number$).$

Guests once checked in may use other services $($not under the booking$)$ and charge them under

the booking. For example, guests may order a bar drink under the booking and pay it at

checkout time. The booking needs to keep track of all charges made under it$.$ If the guest arrives

on a package, services which are not included in the package are billed at the advertised rate

for the period.

Page $3$ of $6$

When checking out, the reservation needs to be fully paid. All payment information in settling

the bill needs to be maintained. The manager and authorized personnel has the ability to

provide a discount on the final bill. If a discount is provided, the discount amount and employee

authorizing the discount is recorded. A discount of more than $25\%$ needs the authorization from

head office. The final bill for a reservation needs to include all charges under each booking,

deposit information and all customer payments. The database should be able to generate this

final bill.

Customer and guest information such as name, address, contact number and email needs to

be maintained. If a reservation or a booking is cancelled after the grace period $25\%$ of the

amount due is charged and all facility bookings for the reservation is removed.

Section $1$: Conceptual DB Design $(10$ marks$)$

Based on the business requirements, develop a conceptual database design using EER model.

Your lecturer and tutor will act as the client and you can speak to him to clarify any questions

regarding the requirements.

Section $2$: EER $$ Relational Mapping & Normalisation $(2$ marks$)$

The EER diagram needs designed in section $1$ needs to be mapped to a relational schema and

normalised.

The relational model needs to be described in DBDL format. Sample DBDL format is given in

the below:

ISBN $($id$,$ number, itemNo$)$

Primary Key id

Alternate Key number

Foreign Key itemNo references Book$($itemNo$)$

ON UPDATE CASCADE, ON DELETE C