IMAT5103: Database Design and Development Assignment - ABC Hotel

This assignment focuses on the design and development of a database system for ABC Hotel. It begins with a conceptual database design, outlining the major storage facilities required by the organization, and then progresses to a logical database design, detailing the attributes, primary keys, and foreign keys for various entities such as Rooms, Clients, Bookings, and Staff. The solution includes the creation of tables using Oracle DBMS, along with SQL queries for table creation, data population, and retrieval. Furthermore, the assignment highlights the implementation of four useful indexes to optimize database performance. SQL queries are provided to retrieve information about rooms, staff, clients, bookings, and to perform data analysis, such as counting bookings per customer and staff member. The assignment demonstrates a comprehensive approach to database design and implementation, covering all aspects from conceptualization to practical application using SQL.

Running head: DATABASE DESIGN AND DEVELOPEMENT
Database Design and Development
Name of the Student:
Name of the University:
Author Note

1
DATABASE DESIGN AND DEVELOPEMENT
Table of Contents
Task 1: Conceptual database design for ABC’s bookings database system....................................2
Task 2: Logical Database Design and DB creation/manipulation...................................................3
Task 2.1: Logical Database Design for ABC’s bookings database system.................................3
Task 2.2: Create the tables using Oracle DBMS.........................................................................4
Task 2.3: The four most useful indexes on your tables.............................................................12
Task 2.4: Data Population..........................................................................................................13
Task 2.5: SQL Query writing....................................................................................................19
Bibliography..................................................................................................................................23

2
DATABASE DESIGN AND DEVELOPEMENT
Task 1: Conceptual database design for ABC’s bookings database system
The ABC hotel would like to develop a database for their organization. The conceptual
design has been developed here for the creating of the database for the organization:
All the details of the major storage facilities required by the organization has been plotted
in the conceptual design used for the development. The meeting clients and the Wedding clients
have used for further specialization in the design and the along with this assumption it has also
been assumed that the room_booking entity would be useful in the database as this would be

3
DATABASE DESIGN AND DEVELOPEMENT
used for the resolution of the many to many relationship taking place in between the Rooms and
Booking.
Task 2: Logical Database Design and DB creation/manipulation
Task 2.1: Logical Database Design for ABC’s bookings database system
The logical database design for the ABC hotel has been described in the
Rooms
Attributes Primary key Foreign key
Room_ID, Room_Name,
Room_Type, Capacity
Room_ID
Clients
Attributes Primary key Foreign key
Client_ID, Client_Name,
Client_Address,
Client_Contact, Email
Client_ID
Booking
Attributes Primary key Foreign key
Booking_ID, Staff_ID ,
Client_ID , Menu ,
Booking_Date,
No_Of_Guests, Event,
Decoration_Request,
Booking_ID Staff_ID , Client_ID

4
DATABASE DESIGN AND DEVELOPEMENT
Total_Amount, Card_details
Staff
Attributes Primary key Foreign key
Staff_ID ,Staff_Name,
Staff_Address, Staff_Contact
Staff_ID
Booking_Client
Attributes Primary key Foreign key
Booking_ClientID, Client_ID Booking_Client_ID Client_ID
Wedding_Client
Attributes Primary key Foreign key
Wedding_ID , Bride, Groom Wedding_ID, Client_ID Client_ID
Room_Booking
Attributes Primary key Foreign key
ID, Booking_ID, Room_ID ID Booking_ID, Room_ID
Task 2.2: Create the tables using Oracle DBMS
The queries for the creation of the tables are provided below:
CREATE table "STAFF" (
"STAFF_ID" NUMBER NOT NULL,
"STAFF_NAME" VARCHAR2(30) NOT NULL,
"STAFF_ADDRESS" VARCHAR2(50) NOT NULL,

5
DATABASE DESIGN AND DEVELOPEMENT
"STAFF_CONTACT" NUMBER NOT NULL,
constraint "STAFF_PK" primary key ("STAFF_ID")
)
/
Staff Table
CREATE TABLE "ROOM"
( "ROOM_ID" NUMBER NOT NULL ENABLE,
"ROOM_NAME" VARCHAR2(30),
"ROOM_TYPE" VARCHAR2(30) NOT NULL ENABLE,
"CAPACITY" NUMBER NOT NULL ENABLE,
CONSTRAINT "ROOM_PK" PRIMARY KEY ("ROOM_ID")
USING INDEX ENABLE
)

6
DATABASE DESIGN AND DEVELOPEMENT
/
Room Table
CREATE table "CLIENT" (
"CLIENT_ID" NUMBER NOT NULL,
"CLIENT_NAME" VARCHAR2(30) NOT NULL,
"CLIENT_ADDRESS" VARCHAR2(30) NOT NULL,
"CLIENT_CONTACT" VARCHAR2(30) NOT NULL,
"EMAIL" VARCHAR2(30) NOT NULL,
constraint "CLIENT_PK" primary key ("CLIENT_ID")
)
/
Client Table

7
DATABASE DESIGN AND DEVELOPEMENT
CREATE table "BOOKING_CLIENT" (
"BOOKING_CLIENT_ID" NUMBER NOT NULL,
"CLIENT_ID" NUMBER NOT NULL,
constraint "BOOKING_CLIENT_PK" primary key ("BOOKING_CLIENT_ID")
)
/
ALTER TABLE "BOOKING_CLIENT" ADD CONSTRAINT "BOOKING_CLIENT_FK"
FOREIGN KEY ("CLIENT_ID")
REFERENCES "CLIENT" ("CLIENT_ID")
/
Booking Client table

8
DATABASE DESIGN AND DEVELOPEMENT
CREATE table "WEDDING_CLIENT" (
"WEDDING_CLIENT_ID" NUMBER NOT NULL,
"CLIENT_ID" NUMBER NOT NULL,
"BRIDE" VARCHAR2(30) NOT NULL,
"GROOM" VARCHAR2(30) NOT NULL,
constraint "WEDDING_CLIENT_PK" primary key ("WEDDING_CLIENT_ID")
)
/
ALTER TABLE "WEDDING_CLIENT" ADD CONSTRAINT "WEDDING_CLIENT_FK"
FOREIGN KEY ("CLIENT_ID")
REFERENCES "CLIENT" ("CLIENT_ID")
/

9
DATABASE DESIGN AND DEVELOPEMENT
Wedding Client Table
CREATE table "BOOKING" (
"BOOKING_ID" NUMBER NOT NULL,
"STAFF_ID" NUMBER NOT NULL,
"CLIENT_ID" NUMBER NOT NULL,
"MENU" VARCHAR2(30) NOT NULL,
"BOOKING_DATE" DATE NOT NULL,
"NO_OF_GUEST" VARCHAR2(30) NOT NULL,
"EVENT" VARCHAR2(30) NOT NULL,
"TOTAL_AMOUNT" NUMBER NOT NULL,
"CARD_DETAILS" VARCHAR2(30) NOT NULL,
"DECORATION_REQUEST" VARCHAR2(50) NOT NULL,
constraint "BOOKING_PK" primary key ("BOOKING_ID")

10
DATABASE DESIGN AND DEVELOPEMENT
)
/
ALTER TABLE "BOOKING" ADD CONSTRAINT "BOOKING_FK"
FOREIGN KEY ("STAFF_ID")
REFERENCES "STAFF" ("STAFF_ID")
/
ALTER TABLE "BOOKING" ADD CONSTRAINT "BOOKING_FK1"
FOREIGN KEY ("CLIENT_ID")
REFERENCES "CLIENT" ("CLIENT_ID")
/
Booking Table

11
DATABASE DESIGN AND DEVELOPEMENT
CREATE table "ROOM_BOOKING" (
"ID" NUMBER NOT NULL,
"ROOM_ID" NUMBER NOT NULL,
"BOOKING_ID" NUMBER NOT NULL,
constraint "ROOM_BOOKING_PK" primary key ("ID")
)
/
ALTER TABLE "ROOM_BOOKING" ADD CONSTRAINT "ROOM_BOOKING_FK"
FOREIGN KEY ("ROOM_ID")
REFERENCES "ROOM" ("ROOM_ID")
/

12
DATABASE DESIGN AND DEVELOPEMENT
ALTER TABLE "ROOM_BOOKING" ADD CONSTRAINT "ROOM_BOOKING_FK1"
FOREIGN KEY ("BOOKING_ID")
REFERENCES "BOOKING" ("BOOKING_ID")
/
Room Booking Table
Task 2.3: The four most useful indexes on your tables
The four most useful indexes created in the database are described below:
Client name:
CREATE INDEX idx_cname ON CLIENT (Client_Name);
The name of the client is created an index as it would be helpful to often search lists with
the client names.
Bride and Groom:
CREATE INDEX idx_wed ON WEDDING_CLIENT (Bride,Groom);

13
DATABASE DESIGN AND DEVELOPEMENT
The names of the bride and the groom are important for fetching the information related
to the wedding informations.
Room Name:
CREATE INDEX idx_rname ON Room (Room_Name);
The room names are important for selecting a room for the booking process.
Room Type:
CREATE INDEX idx_rtype ON ROOM (RoomType);
The type of rooms selected for the booking are indexed here as it is often very important
to select a room as per the requirements and the room types have been set as per the
requirements.
Task 2.4: Data Population
Staff table
Insert into staff values (1,'Robert', '42 New valley road', 784902);
Insert into staff values (2,'Henry', 'Chicago', 789670);
Insert into staff values (3,'Lara', 'Northern Road', 784992);
Insert into staff values (4,'Suzan', 'lands down Street', 748920);
Insert into staff values (5,'Ben', 'northern Street', 774899);

14
DATABASE DESIGN AND DEVELOPEMENT
Room Table
Insert into room values (5,'Chatsworth Suite', 'Wedding Suite', 60);
Insert into room values (4,'Fleetwood Suite', 'Wedding Suite', 110);
Insert into room values (1,'Hall 1', 'Meeting Room', 30);
Insert into room values (2,'Hall 2', 'Meeting Room', 30);
Insert into room values (3,'Hall 3', 'Meeting Room', 25);
Insert into room values (6,'Bedroom 1', 'Bedroom', 2);
Insert into room values (7,'Bedroom 2', 'Bedroom', 2);

15
DATABASE DESIGN AND DEVELOPEMENT
Client Table
Insert into client values (1,'Rodger', '[phone]' ,'32 Chicagoo', '[e-mail]','Wedding
Client');
Insert into client values (2,'Charles', '[phone]' ,'Louis Street', '[e-mail]','Wedding
Client');
Insert into client values (3,'Albert', '[phone]' ,'Rose henry street', '[e-mail]','Party
Client');
Insert into client values (4,'Ritcher', '[phone]' ,'Chicago', '[e-mail]','Party Client');
Insert into client values (5,'Smith', '[phone]' ,'11 bentrick street',
'[e-mail]','Booking Client');
Insert into client values (6,'Johnes', '[phone]' ,'21 Highway', '[e-mail]','Booking
Client');

16
DATABASE DESIGN AND DEVELOPEMENT
Booking Client
Insert into Booking_client values (1,6);
Insert into Booking_client values (2,5);
Wedding Client
Insert into Wedding_client values (1,1,'Kevin', 'Robin');
Insert into Wedding_client values (2,2,'Harry', 'Lisa');

17
DATABASE DESIGN AND DEVELOPEMENT
Booking
Insert into Booking values (1,1,1,4,'05/03/2018',55,'Marriage', 55, 'Credit card Number:
5684739123445', 'No Request');
Insert into Booking values (2,2,2,4,'09/17/2018',55,'Marriage', 50, 'Credit card Number:
5684739120987', 'No Request');
Insert into Booking values (3,3,3,5,'12/19/2018',95,'Marriage', 45, 'Credit card Number:
5684739904845', 'No Request');
Insert into Booking values (4,4,4,5,'12/29/2018',15,'Meeting', 60, 'Credit card Number:
5684739110986', 'No Request');
Insert into Booking values (5,5,6,5,'12/29/2018',35,'Meeting', 40, 'Cash', 'No Request');

18
DATABASE DESIGN AND DEVELOPEMENT
Room Booking table
Insert into Room_Booking values (1, 1, 1);
Insert into Room_Booking values (2, 2, 1);
Insert into Room_Booking values (3, 3, 2);
Insert into Room_Booking values (4, 4, 5);
Insert into Room_Booking values (5, 5, 4);
Insert into Room_Booking values (6, 5, 3);

19
DATABASE DESIGN AND DEVELOPEMENT
Task 2.5: SQL Query writing
Query 1:
The list of rooms available for booking
Select * from Room;
Query 2:
List of all the staffs
Select * from staff;

20
DATABASE DESIGN AND DEVELOPEMENT
Query 3:
Select * from Client;
Query 4:
Select * from Booking, Client, Staff, Room, Room_Booking
where Booking.Client_ID = Client.Client_ID and Booking.Staff_ID = Staff.Staff_ID and
Room_Booking.Booking_ID = Booking.Booking_ID and Room_Booking.Room_ID =
Room.Room_ID;

21
DATABASE DESIGN AND DEVELOPEMENT
Query 5:
Number of booking made by a customer
Select Client.Client_Name, COUNT(Booking_ID) from Booking, Client
where Booking.Client_ID = Client.Client_ID
Group by Client.Client_Name;
Query 6:
Number of booking Handled by each Staff:
Select Staff.Staff_Name, COUNT(Booking_ID) from Booking, Staff

22
DATABASE DESIGN AND DEVELOPEMENT
where Booking.Staff_ID = Staff.Staff_ID
Group by Staff.Staff_Name;

23
DATABASE DESIGN AND DEVELOPEMENT
Bibliography
Harrington, J.L., 2016. Relational database design and implementation. Morgan Kaufmann.