Database Design And System Analysis

Running head: Database Design And System Analysis
DATABASE DESIGN AND SYSTEM ANALYSIS
Name of the Student
Name of the University
Author Note

DATABASE DESIGN AND ANALYSIS
1
Table of Contents
Task 1.1 2
Task 1.2 4
EER Diagram 4
Task 2.1 5
Logical Database Design 5
Task 2.2 6
Table Creation using Oracle DBMS 6
Room Table: 7
Task 2.3 8
Indexes 8
Task 2.4 9
Data Population 9
Task 2.5 12
SQL Query 12

DATABASE DESIGN AND ANALYSIS
2
Task 1.1
A scenario of a Hotel Management System: The Owner of the hotel decided to
develop a digital system for its Hotel instead of the available manual system. The Hotel uses
a manual system for keeping all the necessary details of the hotel, and it is very difficult to
maintain all the records of the hotel, so they wanted to develop a digital system. The new
system mainly performs the following functionality:
 There are mainly two users who are going to use the new system they are admin and
customer. The system maintains the user accounts and stores the relevant pieces of
information such as name, email id, contact number, address and other details of the
user. The system can able to manage the account of both the users.
 Admin can manage all the details of the hotel and the details of the customer, who can
make a room reservation. Only Admin can able to add and modify the rooms of the
hotel and also create a new user account.
 Admin can view, add, delete and modify a new user account. Customer can only
modify their contact details. To perform these activities user of the system have to log
in first to the system, then they can perform such types of task.
 They are managing rooms of the hotel according to their room number, level, room
type and capacity. The hotel has six levels, and every level has more than one rooms.
The cost of the rooms depends on room type and capacity. The hotel has mainly two
types of rooms, AC and Non-Ac. The cost of AC rooms is £105, and the cost of the
non-ac room is £80.
 Both the user of the system can view the details of the rooms, but only admin can add,
delete and modify the details of the room.

DATABASE DESIGN AND ANALYSIS
3
 The admin of the system manages the room reservation process, and the process
should include a particular customer, room details, booking date, start and end time.
Customer can also cancel a reservation. Whenever a booking or cancellation process
takes place, a confirmation message sends by the system to the specific customer for
their respective transaction.
 Every customer will charge 30% of the cost of the room as soon as the customer book
a room and rest of the amount have to pay within 24 hours. If the customer failed to
pay the rest amount within 24 hours, then an automatic cancellation of room booking
occurs, and a 10% penalty fee will be deducted from the advance payment. A
notification message is also sent to the customer with all the details of their
transaction.