Database Design and Development

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