Requirements Gathering, Conceptual Database Design And Implementation Assignment

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 LeisureAustralasias 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?posit is charged at time of reservation to the customers 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.

You may implement the stored procedures either as T-SQL or CLR stored procedures.

(1) Create a Package

(2) Make a Reservation