Database Implementation Project: Hotel Booking System

Desklib provides solved assignments and past papers to help students succeed.

Table of Contents
Introduction................................................................................................................................3
Task 1.........................................................................................................................................4
Task 1.1 ER Diagram.............................................................................................................4
Task 1.2 Conceptual Diagram................................................................................................5
Task 2.........................................................................................................................................6
Task2.1 Logical Diagram.......................................................................................................6
Task 2.2 Create the Tables.....................................................................................................7
Task 2.3 Indexes.....................................................................................................................9
Task 2.4 Data Population.....................................................................................................10
Task 2.5 SQL Query Writing...............................................................................................14
Conclusion................................................................................................................................17
References................................................................................................................................18
List of Figures
Figure 1: ER Diagram................................................................................................................2
Figure 2: Conceptual Diagram...................................................................................................3
Figure 3: Logical Diagram.........................................................................................................4
Figure 4: Booking Table............................................................................................................5
Figure 5: Business Table............................................................................................................5
Figure 6: ThemedParty Table.....................................................................................................5
Figure 7: Wedding Table...........................................................................................................5
Figure 8: Client Table................................................................................................................5
Figure 9: Payment Table............................................................................................................6
Figure 10: BusinessDetails Table...............................................................................................6
Figure 11: WeddingDetails Table..............................................................................................6
Figure 12: ThemedPartyDetails Table.......................................................................................6

Figure 13: Index 1......................................................................................................................7
Figure 14: Index 2......................................................................................................................7
Figure 15: Index 3......................................................................................................................7
Figure 16: Index 4......................................................................................................................7
Figure 17: Booking Data............................................................................................................8
Figure 18: Business Data...........................................................................................................8
Figure 19: Wedding Data...........................................................................................................8
Figure 20: ThemedParty Data....................................................................................................9
Figure 21: ThemedPartyDetails Data.........................................................................................9
Figure 22: BusinessDetails Data..............................................................................................10
Figure 23: WeddingDetails Data..............................................................................................10
Figure 24: Client Data..............................................................................................................10
Figure 25: Payment Data..........................................................................................................11
Figure 26: Query 1...................................................................................................................12
Figure 27: Query 2...................................................................................................................12
Figure 28: Query 3...................................................................................................................12
Figure 29: Query 4...................................................................................................................13
Figure 30: Query 5...................................................................................................................13
Figure 31: Query 6...................................................................................................................13
Figure 32: Query 7...................................................................................................................14
Figure 33: Query 8...................................................................................................................14

Introduction
The below-created report is purely based on the scenario of a hotel named as ABC who gives
the booking of the hotel for various purposes such as for business meetings, for weddings as
well as for themed parties. The entire database is created and implemented below using the
Oracle database software and the designs are created using draw.io. The entire database is
created along with the proper indexes and also 8 different are being created. The report is
purely based on practical work.

Task 1
Task 1.1 ER Diagram
Assumptions:
 Each Client can be uniquely identified by Primary Key named as “Client_ID”.
 Each Booking can be uniquely identified by Primary Key named as “Booking_Code”.
 Each WeddingDetails can be uniquely identified by Primary Key named as “W_ID”.
 Each BusinessDetails can be uniquely identified by Primary Key named as “B_ID”.
 Each ThemedPartyDetails can be uniquely identified by Primary Key named as “TP_ID”.
 Each Payment can be uniquely identified by Primary Key named as “P_ID”.
 One Client can make multiple Bookings.
 One Payment can have multiple Bookings.
 Booking is specialized as Wedding, Business and ThemedParty.
 Each Wedding contains single WeddingDetails.
 Each Business contains a single BusinessDetails.
 Each ThemedParty contains single ThemedPartyDetails.
 Client_ID and P_ID are the two Foreign Keys present in Booking entity.

Figure 1: ER Diagram
Task 1.2 Conceptual Diagram

Figure 2: Conceptual Diagram

Task 2
Task2.1 Logical Diagram
Figure 3: Logical Diagram

Task 2.2 Create the Tables
Figure 4: Booking Table
Figure 5: Business Table
Figure 6: ThemedParty Table
Figure 7: Wedding Table
Figure 8: Client Table

Figure 9: Payment Table
Figure 10: BusinessDetails Table
Figure 11: WeddingDetails Table
Figure 12: ThemedPartyDetails Table

Task 2.3 Indexes
Figure 13: Index 1
Figure 14: Index 2
Figure 15: Index 3
Figure 16: Index 4
The index is referred as a scheme object which contains a proper entry for each and every
value that is appeared in the indexed columns of the particular table or we could say the
cluster and helps in providing the direct access to the rows (Gallas et al., 2017). There are
various indexes present in the oracle database and that are mentioned as Normal Indexes, By-
default indexes and also B-tree indexes (Nanda et al., 2017). Here, four different tables are
being provided the four different indexes for different columns.

Task 2.4 Data Population
Figure 17: Booking Data
Figure 18: Business Data

Figure 19: Wedding Data
Figure 20: ThemedParty Data