Database Design and Development

This document provides detailed information about database design and development for ABC's bookings database system. It includes conceptual and logical database design, table creation using Oracle DBMS, indexes, and data population. The document also covers the four most useful indexes on the tables.

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

12
DATABASE DESIGN AND DEVELOPEMENT
CREATE table "ROOMBOOKING" (
"ID" NUMBER,
"ROOMID" NUMBER,
"BOOKINGID" NUMBER,
constraint "ROOMBOOKING_PK" primary key ("ID")
)
/
ALTER TABLE "ROOMBOOKING" ADD CONSTRAINT "ROOMBOOKING_FK"
FOREIGN KEY ("ROOMID")
REFERENCES "ROOM" ("ROOMID")

13
DATABASE DESIGN AND DEVELOPEMENT
/
ALTER TABLE "ROOMBOOKING" ADD CONSTRAINT "ROOMBOOKING_FK1"
FOREIGN KEY ("BOOKINGID")
REFERENCES "BOOKING" ("BOOKINGID")
/
Room Booking Table
CREATE table "PAYMENT" (
"PAYMENTID" NUMBER NOT NULL,
"BOOKINGID" NUMBER NOT NULL,
"TOTALAMOUNT" NUMBER NOT NULL,
"CARDDETAILS" VARCHAR2(150) NOT NULL,
constraint "PAYMENT_PK" primary key ("PAYMENTID")
)

14
DATABASE DESIGN AND DEVELOPEMENT
/
ALTER TABLE "PAYMENT" ADD CONSTRAINT "PAYMENT_FK"
FOREIGN KEY ("BOOKINGID")
REFERENCES "BOOKING" ("BOOKINGID")
/
Payment Table
Task 2.3: The four most useful indexes on your tables
The four most useful indexes created in the database are described below:
Room Name:
CREATE INDEX idx_rname ON Room (RoomName);

15
DATABASE DESIGN AND DEVELOPEMENT
The index has been created as the room names are very important and the room names
would be helpful and are descriptive in providing the info about the type of room.
Bride and Groom:
CREATE INDEX idx_wed ON WEDDINGCLIENT (Bride,Groom);
The bride and the groom are very important for the wedding and the wedding client have
the names of the brides and the grooms as the most important information on his list.
Client Type:
CREATE INDEX idx_cl ON CLIENT (ClientType);

16
DATABASE DESIGN AND DEVELOPEMENT
The client type is an index in the database as the index would be helpful in differentiating
the type of clients for the ABC organization.
Room Type:
CREATE INDEX idx_rm ON ROOM (RoomType);
The indexes on the room would be created on the room types that would be helpful in
distinguishing the different type of rooms made available by ABSC hotels.
Task 2.4: Data Population
Staff table
Insert into staff values (1,'Harry', '42 bt rd', 789925);

17
DATABASE DESIGN AND DEVELOPEMENT
Insert into staff values (2,'Garry', '42 new rd', 789990);
Insert into staff values (3,'Terry', 'Southend Road', 789019);
Insert into staff values (4,'Lewis', 'Down Street', 788890);
Insert into staff values (5,'Kevin', 'Down Street', 788890);
Room Table
Insert into room values (1,'Chatsworth Suite', 'Wedding Suite', 60);
Insert into room values (2,'Fleetwood Suite', 'Wedding Suite', 110);
Insert into room values (3,'Hall 1', 'Meeting Room', 30);
Insert into room values (4,'Hall 2', 'Meeting Room', 30);
Insert into room values (5,'Hall 3', 'Meeting Room', 25);

18
DATABASE DESIGN AND DEVELOPEMENT
Insert into client values (1,'Devon', '[phone]' ,'89 well street', '[e-mail]','Wedding
Client');
Insert into client values (2,'Wenton', '[phone]' ,'8 Benson road', '[e-mail]','Wedding
Client');
Insert into client values (3,'Jellinton', '[phone]' ,'7 Benson road', '[e-mail]','Wedding
Client');
Insert into client values (4,'Winston', '[phone]' ,'10 pincel street', '[e-mail]','Booking
Client');
Insert into client values (5,'David', '[phone]' ,'11 henry street', '[e-mail]','Wedding
Client');
Insert into client values (6,'Willy', '[phone]' ,'21 gt road', '[e-mail]','Booking
Client');

19
DATABASE DESIGN AND DEVELOPEMENT
Booking Client
Insert into Bookingclient values (1,6);
Insert into Bookingclient values (2,4);
Wedding Client
Insert into Weddingclient values (1,1,'Andrew', 'Becky');
Insert into Weddingclient values (2,2,'Gary', 'Rosa');
Insert into Weddingclient values (3,3,'Peter', 'Kirster');

20
DATABASE DESIGN AND DEVELOPEMENT
Insert into Weddingclient values (4,5,'Kristopher', 'Rosita');
Food Menu Table
Insert into FoodMenu values (1,'T1');
Insert into FoodMenu values (2,'T2');
Insert into FoodMenu values (3,'T3');
Insert into FoodMenu values (4,'M1');
Insert into FoodMenu values (5,'M2');

21
DATABASE DESIGN AND DEVELOPEMENT
Booking
Insert into Booking values (1,1,1,4,'05/03/2018',55,'Marriage', 'No Request');
Insert into Booking values (2,2,2,4,'09/17/2018',55,'Marriage', 'No Request');
Insert into Booking values (3,3,3,5,'12/19/2018',95,'Marriage', 'No Request');
Insert into Booking values (4,4,4,5,'12/29/2018',15,'Meeting', 'No Request');
Insert into Booking values (5,5,6,5,'12/29/2018',35,'Meeting', 'No Request');
Room Booking table
Insert into RoomBooking values (1, 1, 1);
Insert into RoomBooking values (2, 2, 1);
Insert into RoomBooking values (3, 3, 2);
Insert into RoomBooking values (4, 4, 5);
Insert into RoomBooking values (5, 5, 4);

22
DATABASE DESIGN AND DEVELOPEMENT
Insert into RoomBooking values (6, 5, 3);
Payment
Insert into Payment values (1, 1, 45, 'Credit card Number: 5684739904845');
Insert into Payment values (2, 2, 55, 'Credit card Number: 5684739123445');
Insert into Payment values (3, 3, 50, 'Credit card Number: 5684739120987');
Insert into Payment values (4, 4, 60, 'Credit card Number: 5684739110986');
Insert into Payment values (5, 5, 40, 'Cash');
Task 2.5: SQL Query writing
Query 1:
Work out automatically the amount that a client will pay for a particular booking
Select Client.ClientName, Payment.TotalAmount
From Booking

23
DATABASE DESIGN AND DEVELOPEMENT
Inner Join Client on Booking.ClientID = Client.ClientID
Inner Join Payment on Payment.BookingID = Booking.BookingID;
Query 2:
Work out the total income associated with different types of booking
Select Booking.Event, SUM(Payment.TotalAmount)
From Booking
Inner Join Payment on Payment.BookingID = Booking.BookingID
Group By Booking.Event;

24
DATABASE DESIGN AND DEVELOPEMENT
Query 3:
The count of the number of times a room has been booked
Select Room.RoomName, COUNT(ID)
From Room
Inner Join RoomBooking On Room.RoomID = RoomBooking.RoomID
Group By Room.RoomName;
Query 4:
List of all the clients in the system
Select * from Client;

25
DATABASE DESIGN AND DEVELOPEMENT
Query 5:
List of staffs in the organization and their details
Select * from Staff;
Query 6:
The details of the Booking
Select Client.ClientName, Staff.StaffName, Booking.*
From Booking
Inner Join Client on Booking.ClientID = Client.ClientID

26
DATABASE DESIGN AND DEVELOPEMENT
Inner Join Staff On Staff.StaffID = Booking.StaffID;

27
DATABASE DESIGN AND DEVELOPEMENT
Bibliography
Hoffer, J., Venkataraman, R. and Topi, H., 2015. Modern database management. Prentice Hall
Press.
Parks, R.F. and Hall, C., 2016. Front-End and Back-End Database Design and Development:
Scholar’s Academy Case Study. Information Systems Education Journal, 14(2), p.58.

28
DATABASE DESIGN AND DEVELOPEMENT
Appendix
Stage 1 Mark sheet (50%) – Case Scenario & Conceptual DB Design
Criteria Student’s self-
evaluation %
Assessor’s
evaluation %
Any
Explanation/Additional
Comments to support
assessor’s difference in
evaluation
EERD –
presentation,
including the use of
consistent and
appropriate
notation
95%
EERD – content
quality, contains
minimum standard
of complexity and
matches scenario
95%
EERD –
decompositions
appropriate and
traps eliminated
95%

29
DATABASE DESIGN AND DEVELOPEMENT
Associated
attribute lists
appropriate and
complete
95%
Enterprise rules
complete and
correct relative to
EERD
90%
Assumptions
complete and
appropriate to
scenario
90%
OVERALL
GRADE
93.3%
Stage 2 Marksheet (50%) – Logical Database Design & Oracle SQL
implementation/querying
Criteria Student’s self-
evaluation %
Assessor’s
evaluation %
Any
Explanation/Additional
Comments to support
assessor’s difference in
evaluation
Tables – quantity
correct and in
100%

30
DATABASE DESIGN AND DEVELOPEMENT
appropriate format,
with
primary/foreign
keys indicated and
appropriate
Tables – Well
normalised tables,
with all appropriate
non-key attributes.
100%
Creation of Oracle
tables with
sufficient and
suitable integrity
constraints
(including data
types and lengths)
implemented
95%
Population of
suitable and
sufficient data into
the Oracle tables
using appropriate
90%

31
DATABASE DESIGN AND DEVELOPEMENT
SQL statements
Creation of the four
most appropriate
indexes with
written
justifications as to
why the indexes are
important to
implement
100%
Development of the
six required
syntactically
correct queries;
that together cover
the required SQL
SELECT statement
elements as defined,
with reason(s) for
each query choice.
95%
OVERALL
GRADE
96.7%