University Database Design and Development Assignment IMAT5103

This document presents a comprehensive solution to a database design and development assignment. The assignment encompasses the creation of a booking database system for the ABC organization, starting with a conceptual database design that outlines the key storage aspects. It includes a relational schema defining entities like Staff, Rooms, Clients, Bookings, and related tables. The solution proceeds to logical database design, detailing the attributes and relationships between tables. The implementation phase involves creating tables using Oracle DBMS, including the necessary SQL queries for table creation, data type specifications, and primary/foreign key constraints. The assignment also focuses on the creation of useful indexes to optimize database performance. Data population is demonstrated through INSERT statements, followed by SQL query writing to retrieve specific information such as client payments, booking types, room bookings, client details, staff information, and booking details. The document concludes with a bibliography and mark sheets for the assignment stages.

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.........................................................................5
Task 2.3: The four most useful indexes on your tables.............................................................14
Task 2.4: Data Population..........................................................................................................16
Task 2.5: SQL Query writing....................................................................................................22
Bibliography..................................................................................................................................27
Appendix........................................................................................................................................28
Stage 1 Mark sheet (50%) – Case Scenario & Conceptual DB Design.....................................28
Stage 2 Marksheet (50%) – Logical Database Design & Oracle SQL implementation/querying
...................................................................................................................................................29

2
DATABASE DESIGN AND DEVELOPEMENT
Task 1: Conceptual database design for ABC’s bookings database system
The conceptual database design for the booking system of the ABC organization is
provided below:
The design includes all the major storage aspects that are to be considered by the
organization while storing the data for their business. In the design the Room_Booking entity has
been included design as there is a many to many relation in the design which has been resolved.
The relational schema for the ABC organization’s booking system is provided below:
Staff (StaffID (pk), StaffName, StaffAddress, StaffContact)
Rooms (RoomID (pk), RoomName, RoomType, Capacity)
Clients (ClinetID (pk), ClientName, ClientAddress, ClientContact, Email, ClientType)

3
DATABASE DESIGN AND DEVELOPEMENT
BookingClient (BookingClientID (pk), ClientID (fk))
WeddingClient (WeddingID (pk), ClientID (fk), Bride, Groom)
FoodMenu (MenuID (pk), MenuDetails)
Booking (BookingID (pk), StaffID (fk), ClientID (fk), MenuID (fk), BookingDate, NoOFRooms,
Event, DecorationRequest)
RoomBooking (ID (pk), BookingID (fk), RoomID (fk))
Payment (PaymentID (pk), BookingID (fk), TotalAmount, CardDetails)
Task 2: Logical Database Design and DB creation/manipulation
Task 2.1: Logical Database Design for ABC’s bookings database system
The logical design for the ABC hotel is provided below
Rooms
Attributes Primary key Foreign key
RoomID, RoomName,
RoomType, Capacity
RoomID
Clients
Attributes Primary key Foreign key
ClientID, ClientName,
ClientAddress, ClientContact,
Email
ClientID
FoodMenu
Attributes Primary key Foreign key

4
DATABASE DESIGN AND DEVELOPEMENT
MenuID, MenuDetails MenuID
Booking
Attributes Primary key Foreign key
BookingID, StaffID ,
ClientID , MenuID ,
BookingDate, NoOFRooms,
Event, DecorationRequest
BookingID StaffID , ClientID , MenuID
Staff
Attributes Primary key Foreign key
StaffID ,StaffName,
StaffAddress, StaffContact
StaffID
BookingClient
Attributes Primary key Foreign key
BookingClientID, ClientID BookingClientID ClientID
WeddingClient
Attributes Primary key Foreign key
WeddingID , Bride, Groom WeddingID, ClientID ClientID
RoomBooking
Attributes Primary key Foreign key
ID, BookingID, RoomID ID BookingID, RoomID
Payment
Attributes Primary key Foreign key

5
DATABASE DESIGN AND DEVELOPEMENT
PaymentID , BookingID ,
TotalAmount, CardDetails
PaymentID BookingID
Task 2.2: Create the tables using Oracle DBMS
The queries for the creation of the tables are provided below:
CREATE table "STAFF" (
"STAFFID" NUMBER(2,10) NOT NULL,
"STAFFNAME" VARCHAR2(30) NOT NULL,
"STAFFADDRESS" VARCHAR2(50) NOT NULL,
"STAFFCONTACT" NUMBER(2,10) NOT NULL,
constraint "STAFF_PK" primary key ("STAFFID")
)
/
Staff Table

6
DATABASE DESIGN AND DEVELOPEMENT
CREATE TABLE "ROOM"
( "ROOMID" NUMBER(2,10) NOT NULL ENABLE,
"ROOMNAME" VARCHAR2(30),
"ROOMTYPE" VARCHAR2(30) NOT NULL ENABLE,
"CAPACITY" NUMBER(2,10) NOT NULL ENABLE,
CONSTRAINT "ROOM_PK" PRIMARY KEY ("ROOMID")
USING INDEX ENABLE
)
/
Room Table
CREATE table "CLIENT" (
"CLIENTID" NUMBER(2,10) NOT NULL,
"CLIENTNAME" VARCHAR2(30) NOT NULL,

7
DATABASE DESIGN AND DEVELOPEMENT
"CLIENTADDRESS" VARCHAR2(30) NOT NULL,
"CLIENTCONTACT" VARCHAR2(30) NOT NULL,
"EMAIL" VARCHAR2(30) NOT NULL,
constraint "CLIENT_PK" primary key ("CLIENTID")
)
/
Client Table
CREATE table "BOOKINGCLIENT" (
"BOOKINGCLIENTID" NUMBER(2,10) NOT NULL,
"CLIENTID" NUMBER(2,10) NOT NULL,
constraint "BOOKINGCLIENT_PK" primary key ("BOOKINGCLIENTID")
)
/

8
DATABASE DESIGN AND DEVELOPEMENT
ALTER TABLE "BOOKINGCLIENT" ADD CONSTRAINT "BOOKINGCLIENT_FK"
FOREIGN KEY ("CLIENTID")
REFERENCES "CLIENT" ("CLIENTID")
/
Booking Client Table
CREATE table "WEDDINGCLIENT" (
"WEDDINGCLIENTID" NUMBER NOT NULL,
"CLIENTID" NUMBER NOT NULL,
"BRIDE" VARCHAR2(30) NOT NULL,
"GROOM" VARCHAR2(30) NOT NULL,
constraint "WEDDINGCLIENT_PK" primary key ("WEDDINGCLIENTID")
)
/

9
DATABASE DESIGN AND DEVELOPEMENT
ALTER TABLE "WEDDINGCLIENT" ADD CONSTRAINT "WEDDINGCLIENT_FK"
FOREIGN KEY ("CLIENTID")
REFERENCES "CLIENT" ("CLIENTID")
/
Wedding Client Table
CREATE table "FOODMENU" (
"MENUID" NUMBER NOT NULL,
"MENUDETAILS" VARCHAR2(100) NOT NULL,
constraint "FOODMENU_PK" primary key ("MENUID")
)
/

10
DATABASE DESIGN AND DEVELOPEMENT
Food Menu Table
CREATE table "BOOKING" (
"BOOKINGID" NUMBER NOT NULL,
"STAFFID" NUMBER NOT NULL,
"CLIENTID" NUMBER NOT NULL,
"MENUID" NUMBER NOT NULL,
"BOOKINGDATE" DATE NOT NULL,
"NOOFROOMS" NUMBER NOT NULL,
"EVENT" VARCHAR2(100) NOT NULL,
"DECORATIONREQUEST" VARCHAR2(100) NOT NULL,
constraint "BOOKING_PK" primary key ("BOOKINGID")
)
/

11
DATABASE DESIGN AND DEVELOPEMENT
ALTER TABLE "BOOKING" ADD CONSTRAINT "BOOKING_FK"
FOREIGN KEY ("STAFFID")
REFERENCES "STAFF" ("STAFFID")
/
ALTER TABLE "BOOKING" ADD CONSTRAINT "BOOKING_FK1"
FOREIGN KEY ("CLIENTID")
REFERENCES "CLIENT" ("CLIENTID")
/
ALTER TABLE "BOOKING" ADD CONSTRAINT "BOOKING_FK2"
FOREIGN KEY ("MENUID")
REFERENCES "FOODMENU" ("MENUID")
/
Booking Table