COMP3350 – Advanced Database
Assignment 1 – Database Design & Implementation
Trimester 2, 2023
Due Date June 19th (Monday) at 5pm
Assignment 1 is due on June 19th (Monday) at 5pm. Each team will
• upload the assignment to Canvas by June 19th (Monday) at 5pm,
• demonstrate the assignment to the tutor in the lab session on June 23rd.
Weighting
25% 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 15th. Student who have not
enrolled by May 15th 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 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 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.
Page 4 of 6
(1) Create a Package
Procedure name usp_createPackage
Functionality Creates a service package
Input Parameters
• Package name – Name of the package
• Service Item List – A table-valued parameter consisting of list of service items and
quantity of each
• Description – Description of each package
• Valid period start date – The start date of the package
• Valid period end date – The end date of the package
• Advertised price – Price of package
• Advertised currency – Currency for the advertised price
• Employee – Employee authorizing the package
Output
Parameters
• Advertised package id – Id of the newly created package
SQL script createPackage.sql
Test script test_createPackage.sql
(2) Make a Reservation
Procedure name usp_makeReservation
Functionality
Make a reservation.
Ensure that a reservation does not exceed available capacity at a hotel at any given time.
If there aren’t sufficient capacity available for the reservation an error is raised with an
appropriate error message and the entire reservation is cancelled.
Appropriate bookings of facilities for the specified period needs to be saved in the
database at the time of saving a valid reservation. The total amount due and deposit due
needs to be calculated for the reservation. The deposit amount due is 25% of total amount
due.
Input Parameters
• Customer details – Details of customer making the reservation
▪ customer name – name of customer
▪ address – address of customer
▪ phone number – phone number of customer
▪ email – email of the customer
• List of services/packages reserved – A table valued parameter with each row consisting
of service or package id, quantity, start date and end date of each service/package.
• Guest list – A list of guests if provided (name, address, contact number and email)
Output
Parameters
Reservation id – id of the created reservation
SQL script makeReservation.sql
Test script test_makeReservation.sql
Page 5 of 6
Section 5: ORM (2 bonus marks)
Implement a Form that allows for a hotel employee to make a phone reservation for a customer.
students to register to courses.
Your form needs interact with the database using an ORM framework (e.g. Java Hibernate,
Microsoft Entity Framework, etc.). You should call usp_makeReservation stored procedure
implemented in Section 4.
You need to test your form with valid and invalid data and ensure appropriate error messages
are generated for incorrect input.
Submission Requirements
Soft copy submission is via Canvas.
1. Each group needs to submit a zip file which contains the following items:
o Database documentation.
o SQL Script files
o ORM files (if any)
See link here.
2. Each group needs to submit sections 1-2 as a PDF file to TurnItIn via Canvas. See link here.
3. Each group needs to submit sections 3-5 as a PDF file to TurnItIn via Canvas. See link here.
4. Each group member needs to fill the Assignment 1: Self and Peer Review quiz.
The database documentation must contain the following sections (in the order specified):
1. Assumptions: Assumptions made by your group with respect to the business requirements
2. ERD: The ERD must show all the entities, attributes and relationships.
3. Database Schema: The logical model must show all the tables, columns, column types,
null/not null constraints, primary keys and foreign keys
The SQL script files to be included are listed below.
Section SQL scripts
Database createDB.sql
Stored
Procedures
createPackage.sql
test_createPackage.sql
makeReservation.sql
test_makeReservation.sql
Progress Monitoring
Each groups need to show progress of their assignment during the tutorial sessions. Each
member of group needs to attend these tutorial sessions.
Section Progress
Sections 1 May 22nd
Sections 2 and 3 June 1st
Sections 4 and 5 June 9th
Page 6 of 6
Demonstration
All groups need to demonstrate their database implementation. Group demonstration for
Assignment 1 will be held during June 23rd tutorial sessions. Each member’s contribution
will be evaluated individually during demonstrations. Failure to attend the demonstration can
result in a zero grade for the assignment by the member of the group.