IDB Assignment Question

CT042-3-1-IDB Group Assignment Page 1 of 4

Module Learning Outcomes:

Related Post

CS5071NPProfessionalandEthicalIssuesSpring25MainSitCWQP_08f31e25-c3ca-45ec-8c05-46e300dc52fe_89407_

April 10, 2025

TNE_MGT101 Assessment 2024_5_NEW

April 9, 2025

BM3506 AFS assignment 24_25

April 9, 2025

CC5067NISmartDataDiscoveryY25SpringMainSitCWQP_f89a9efd-1e5c-4f71-a941-825b5d75b874_93472_

April 9, 2025

• Apply redundancy control in designing a database.

• Demonstrate a database solution using an appropriate tool based on a case study.

Case Study:

Hotel Booking Management System

X Hotel is a growing hotel business who owns multiple branches throughout various states in Malaysia. As their business expands, X Hotel wants to implement an online booking system to facilitate and manage customer bookings. The system should handle various aspects of hotel operations including room availability, customer information, room reservation, and billing. Your team is assigned to design and implement a database system for the Hotel Booking Management System.

Scenario:

• Given that X Hotel has multiple branches, each branch is managed by its own manager. The details of each branch manager need to be recorded in the database, as it is valuable for internal communication and enhancing customer service.

• Each branch offers different number of rooms, which can be summarized into three types: standard double, standard twin, and deluxe double. The database should store details for each room such as its capacity, room rate, etc.

• Customers must first register as members before making room reservation. During the registration process, they are required to provide their first name, last name, contact number, email address and mailing address. The system should prevent customers from registering with the same email address more than once.

• Customer can reserve multiple rooms at a single branch with one booking provided the rooms are available. Reservations for different branches must be made separately. Each reservation must include details like booking date, check-in and check-out date, and emergency contact. Besides, the system allows customer to add special requests and preferences for each booking such as late check-in, non-smoking room, etc.

• Invoice will be sent once customer made the payment. Every invoice has an invoice identification number and total amount paid. To ensure high level of customer satisfaction, customers can leave rating as a score (1=Very Dissatisfied, 2=Dissatisfied, 3=Neutral, 4=Satisfied, 5=Very Satisfied) along with short text review after their stay.

*Note: you may make any other assumptions after discussing them with module lecturer

Level 1 Asia Pacific University of Information & Technology 072024

CT042-3-1-IDB Group Assignment Page 2 of 4

Coursework Details:

1. In this assignment, you are required to design, implement, and document a database system for a hotel booking management system.

2. Create the following queries using Data Manipulation Language (DML) – student must be able to explain the queries.

i. Display the customers who have not make any reservation since the beginning of 2023. The result should include first name, last name, email address, and contact number.

ii. Display the monthly revenue for the year 2023 and sort the results in descending order by monthly revenue.

iii. Display all customers have make more than 3 reservations in the year of 2023. iv. Display the average rating and total number of reviews for each room type at the Kuala Lumpur branch.

v. Display the total revenue generated by each branch for the year 2023. vi. Display the names of customers and the total amount they spent in the year of 2023.

vii. Display the average and total spending by gender for the year 2023. viii. Display the branch id, address, manager’s name, and the total number of rooms for each branch.

ix. Display the customer with highest number of reservations and show their average amount spent for the year 2023.

x. Display the customer who have stayed more than one branch in 2023.

Level 1 Asia Pacific University of Information & Technology 072024

CT042-3-1-IDB Group Assignment Page 3 of 4

3. Deliverables – Minimum requirement of your documentation:

Level 1 Asia Pacific University of Information & Technology 072024

CT042-3-1-IDB Group Assignment Page 4 of 4

General Requirements:

In this assignment you are required to:

• Work a group of 3-4 members.

• Design and implement a solution to a business problem.

• Implement the solution using any Enterprise DBMS.

• Document the solution as set out in the assignment requirements.

• Submit the document online according to the date and time given below. • Each group member is required to participate in all tasks / discussions together. • Presentation schedules will be published at a later date through MS TEAMS.

Note: It is acceptable for discrete activities of this assignment to be undertaken by individual group members. However, it is essential that all group members understand the presentation in its entirety. At the end of the demonstration your group will be asked a series of questions to explore your understanding and analysis of the given problem. Responses to these questions such as “I don’t know because I didn’t work on that part of the assignment” are not acceptable and will result in a penalty for either the entire group or specific individual(s).

Level 1 Asia Pacific University of Information & Technology 072024