Database Design and Development Tasks 2022

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
)