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")
/