You need to attempt the assignment in a group of $2$ members. Find a fellow student in your

tutorial group and enroll into a group via Canvas on or before May $15$th$.$ Student who have not

enrolled by May $15$th will be randomly assigned to a group.

This assignment has $5$ sections to it$.$ Progress in each part for the group is monitored during

tutorials. Note that all SQL scripts and code created in this assignment needs to be commented

for readability $($Marks will be deducted for uncommented, illegible code$).$ You need to use

Github classroom repositories for Assignment $1.$

Assignment Background

LeisureAustralasia group operates a number of hotels and resorts throughout Australia

$($Brisbane$,$ Cairns, Newcastle, Broome and Darwin$)$ and Asia $($Vietnam$,$ Singapore, Thailand,

Sri Lanka and India$).$ They have had a number of IT systems that each hotel managed. Recently,

the group has decided to invest in an integrated web$-$enabled central IT system to service all

LeisureAustralasia$$s hotels and will be hosted on the cloud.

You are working for an IT company that has won the contract to supply this system. Your job is

to design and implement the central database for hotel bookings, reservations and billing.

LeisureAustralasia group has a number of hotels and resorts that it manages. Each hotel has

a hotel id $($which is unique$),$ name, address, country, phone number and a description.

Each hotel has facilities such as rooms, conference venues, swimming pools, gyms,

restaurants, etc. Each facility has a unique facility id$,$ name, description and status. The facilities

are categorized to Facility types $($e$.$g$.$ standard room, family room, conference hall, swimming

pool etc.$).$ Each facility type has an id $($unique$),$ name, description and capacity.

Page $2$ of $6$

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