Due Date June 27th (Tuesday) at 9:00am

Assignment 1 is due on June 27th (Tuesday) at 9:00am. Each team will

•   upload the assignment to Canvas by June 27th (Tuesday) at 9:00am,

•   demonstrate the assignment to the tutor in the lab session in week 8

Weighting

30% of course mark

Assignment Team

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 19th. Students who have not enrolled by May 19th will be randomly assigned to a group.

This assignment has 4 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).

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.

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) need 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 used 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 are 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 has 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.

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 have 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 (15 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 them during lab classes to clarify any questions regarding the requirements.

Your EER model must use UML notation as per lecture slides.

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

The EER diagram designed in section 1 needs to be mapped to a relational schema and normalized  to  BCNF.  If  a  mapped  relation  is  not  normalized,  specify  the  functional dependencies that cause the relation to be in non-BCNF form and show the normalised schema.

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 CASCADE

DBDL format is provided in your text – Database Systems – A Practical Approach to Design, Implementation, and Management – 5th Edition.

Section 3: Database Implementation – Database Script (3 marks)

Create a T-SQL script for the database design in section 2. You will create a database with all the necessary tables and constraints: primary key, foreign key, not null, unique and check constraints.   The database  must  be  populated with sufficient and  meaningful  records for evaluation purpose.

Save this SQL script as CreateDB.sql.

Section 4: Stored Procedures (10 marks)

Implement the following stored procedures. Ensure that each stored procedure is tested with appropriate sample data. Error handling needs to be implemented and appropriate warnings and error messages are generated. Test cases should be saved in a separate test script. Note that marks are allocated for testing and well-documented code.