Database System Design and Implementation

This report discusses the design and implementation of a database system for ABC Hotel. It covers the background of the organization, conceptual and logical database design, and SQL queries for creating tables and indexes. The report also includes sample data for testing the database.

Running head: DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Database System Design and Implementation
Name of the Student
Name of the University
Author’s note:

1DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Table of Contents
Introduction:....................................................................................................................................2
Background of the organization:.....................................................................................................2
Task 1:.............................................................................................................................................4
Task 2:.............................................................................................................................................6
Task 2.1:......................................................................................................................................6
Task 2.2:..........................................................................................................................................7
Task 2.3:........................................................................................................................................11
Task 2.4:........................................................................................................................................12
Task 2.5:........................................................................................................................................13
Conclusion:....................................................................................................................................15
Bibliography:.................................................................................................................................16

2DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Introduction:
The following report is all about ABC hotel which is a family owned hotel located in
Leicester. Beginning from hotel purely that is for individual customer for booking to overnight
stay. It has ultimately grown into a business which aims to offer different kind of hotel based
services to most of its clients. The manual approach for booking to these services has become
very much difficult in nature. Staff are finding the rooms at much frequent rate which is used in
more than one service and even a single room is being booked twice. It is considered to be as one
of the major issue that is impacting the overall reputation of hotel and its services. Oracle DBMS
comes up with list of features that makes it popular in the whole business world. The current
version of oracle DBMS are being released which comes up with new and enhanced features.
This will merely be used for geared for both business and corporation. In the whole market,
Oracle is found to have leading role as a result of persistence for providing the perfect database.
In the coming pages, an idea has been provided with respect to background of this ABC hotel.
After that, conceptual database and logical database design has been given.
Background of the organization:
ABC Hotel is a family owned hotel that is based in Leicester. Beginning from pure hotel
serving individual customers, the organization has grown into a business which provides hotel
based service to clients. ABC hotel takes up booking for weeding, business events and parties
along with individual stay in the hotel. All the booking is done come up with a particular date
and booking time along with particular booking code. In addition, it has the name of the staff
member that took up the booking and credit card details. The remaining amount needs to be
settled on very last day of the event at the hotel. A record is maintained for the payment method

3DATABASE SYSTEM DESIGN AND IMPLEMENTATION
which is being recorded alongside of paid amount. Booking for weeding reception is mainly
done one day prior to the actual date of booking. All these booking is done on the telephone
where the name of the groom and bride is being recorded with client detail.
The booking is merely inclusive of wedding details requirements like guest and food
menu needed by the reception. It will merely facilities the wedding and special request which is
needed for decorating weeding rooms. Booking of the business event also requires the same kind
of client detail which needs to be stored. Along with this, the name and registered address of the
business organization is taken into account. ABC has five small meetings which is booked as a
part of the business booking event.

4DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Task 1:
Figure 1: Enhanced Entity Relationship Diagram
(Source: Created by Author)

5DATABASE SYSTEM DESIGN AND IMPLEMENTATION
In this conceptual database design, there are three major entities that is client, booking
and credit card. The booking entity comprises of list of attributes like booking_id, booking_date,
staff_number and many other. The client attribute comprises of client_id, address, email and
many other. The credit card entity comprises of card_number, card_cvv and exp_year. The
booking entity has been generalized four aspects that wedding reception, business event, theme
party and individual.

6DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Task 2:
Task 2.1:
Figure 2: Logical ERD of ABC Hotel
(Source: Created by Author)

7DATABASE SYSTEM DESIGN AND IMPLEMENTATION
In this logical diagram, there are mainly three entities that is client, booking and credit
card. In the client entity, client_id is the attribute which is bound to primary key that in the
database there cannot two similar client id. The booking entity has booking attribute that is
bound to primary key. Lastly, credit card entity has a credit card number which is bound to
primary key.
Task 2.2:
create table Client (
client_id number not null constraint client_pk primary key,
client_full_name varchar2(200),
address varchar2(200),
phone varchar2(200),
email varchar2(200)
);
create table CreditCard (
card_number number not null constraint creditcard_pk primary key,
card_cvv number,
exp_month number,
exp_year number,
card_holder_name varchar2(200)
);
create table Booking (
booking_id number not null constraint booking_pk primary key,
client_id number not null,
booking_date date,
booking_time timestamp,
staff_member varchar2(200),
first_payment_method varchar2(200),

8DATABASE SYSTEM DESIGN AND IMPLEMENTATION
paid_amount number,
final_payment_method varchar2(200),
total_amount number,
credit_card number
);
alter table Booking add constraint booking_client_id_uq unique (client_id);
alter table Booking add constraint booking_credit_card_uq unique (credit_card);
create table WeddingReception (
booking_id number not null constraint weddingreception_pk primary key,
bride_name varchar2(200),
groom_name varchar2(200),
reception_date date,
number_of_guests number,
food_menu varchar2(250),
facilities varchar2(250),
special_request varchar2(250),
event_room number not null
);
alter table WeddingReception add constraint weddingreception_event_room_uq unique
(event_room);
create table EventRoom (
event_room_id number not null constraint eventroom_pk primary key,
room_name varchar2(200),
guest_capacity number
);
create table BusinessEvent (
booking_id number not null constraint businessevent_pk primary key,
business_name varchar2(200),
office_address varchar2(200),
event_description varchar2(200),

9DATABASE SYSTEM DESIGN AND IMPLEMENTATION
event_date date,
meeting_room number not null
);
alter table BusinessEvent add constraint businessevent_meeting_room_uq unique
(meeting_room);
create table BookedRoom (
booking_id number not null,
event_room_id number not null
);
alter table BookedRoom add constraint bookedroom_pk primary key (booking_id,
event_room_id);
create table MeetingRoom (
meeting_room_id number not null constraint meetingroom_pk primary key,
room_capacity number,
inbuilt_facilities varchar2(200),
request_facilities varchar2(200)
);
create table ThemedParty (
booking_id number not null constraint themedparty_pk primary key,
special_request varchar2(200),
maximum_participant number,
venue number not null,
food_menu varchar2(200)
);
alter table ThemedParty add constraint themedparty_venue_uq unique (venue);
create table Individual (
booking_id number not null constraint individual_pk primary key,
start_day date,
end_day date,
include_breakfast varchar2(5),

10DATABASE SYSTEM DESIGN AND IMPLEMENTATION
offer varchar2(200)
);
create table Room (
room_id number not null constraint room_pk primary key,
room_type varchar2(200),
inbuilt_facilities varchar2(200),
request_facilities varchar2(200)
);
create table RoomBooking (
booking_id number not null,
room_id number not null
);
create table BusinessEvent (
booking_id number not null constraint businessevent_pk primary key,
business_name varchar2(200),
office_address varchar2(200),
event_description varchar2(200),
event_date date,
meeting_room number
);
create table BookedRoom (
booking_id number,
event_room_id number
);
alter table BookedRoom add constraint bookedroom_pk primary key (booking_id,
event_room_id);
create table ThemedParty (
booking_id number not null constraint themedparty_pk primary key,
special_request varchar2(250),
maximum_participant number,

11DATABASE SYSTEM DESIGN AND IMPLEMENTATION
venue number,
food_menu varchar2(250)
);
alter table RoomBooking add constraint roombooking_pk primary key (booking_id,
room_id);
ALTER TABLE Booking ADD CONSTRAINT FK_client_booking FOREIGN KEY
(client_id) REFERENCES Client(client_id);
ALTER TABLE Booking ADD CONSTRAINT FK_booking_credit_card FOREIGN
KEY (credit_card) REFERENCES CreditCard(card_number);
ALTER TABLE WeddingReception ADD CONSTRAINT
FK_WeddingReception_booking FOREIGN KEY (booking_id) REFERENCES
Booking(booking_id);
ALTER TABLE WeddingReception ADD CONSTRAINT
FK_WeddingReception_event_room FOREIGN KEY (event_room) REFERENCES
EventRoom(event_room_id);
ALTER TABLE BusinessEvent ADD CONSTRAINT FK_BusinessEvent_booking
FOREIGN KEY (booking_id) REFERENCES Booking(booking_id);
ALTER TABLE BusinessEvent ADD CONSTRAINT
FK_BusinessEvent_meeting_room FOREIGN KEY (meeting_room) REFERENCES
MeetingRoom(meeting_room_id);
ALTER TABLE BookedRoom ADD CONSTRAINT FK_BookedRoom_BusinessEvent
FOREIGN KEY (booking_id) REFERENCES BusinessEvent(booking_id);
ALTER TABLE BookedRoom ADD CONSTRAINT FK_BookedRoom_EventRoom
FOREIGN KEY (event_room_id) REFERENCES EventRoom(event_room_id);
ALTER TABLE Individual ADD CONSTRAINT FK_Individual_booking FOREIGN
KEY (booking_id) REFERENCES Booking(booking_id);
ALTER TABLE ThemedParty ADD CONSTRAINT FK_ThemedParty_venue
FOREIGN KEY (venue) REFERENCES EventRoom(event_room_id);
ALTER TABLE RoomBooking ADD CONSTRAINT FK_Individual_room_book
FOREIGN KEY (booking_id) REFERENCES Individual(booking_id);
ALTER TABLE RoomBooking ADD CONSTRAINT FK_Individual_room FOREIGN
KEY (room_id) REFERENCES Room(room_id);
Task 2.3:
create unique index BOOKING_BOOKING_ID_CLIE_UQ_IDX on booking
(booking_id,client_id,credit_card);

12DATABASE SYSTEM DESIGN AND IMPLEMENTATION
create unique index INDIVIDUAL_BOOKING_ID_STAR_IDX on individual
(booking_id,start_day,end_day);
create unique index WEDDINGRECEPTION_BOOKIN_UQ_IDX on weddingreception
(booking_id,event_room);
Task 2.4:
insert into client values(1, 'Isaac Morris', '76 Sale Street SHADFORTH NSW 2800', '(02)
4000 1429', '[e-mail]');
insert into client values(2, 'Isabelle Perrier', '29 Walpole Avenue WINSLOW VIC 3281',
'[phone]', '[e-mail]');
insert into client values(3, 'Grace Haydon', '95 Yangan Drive PINE RIDGE NSW 2343',
'[phone]', '[e-mail]');
insert into client values(4, 'Lauren Rede', '39 Boulter Close STOTERS HILL QLD 4860',
'(07) 4067 06917', '[e-mail]');
insert into client values(5, 'Beau Lower', '76 Bungana Drive MAYFIELD SA 54540',
'[phone]', '[e-mail]');
insert into CreditCard values(4510645983016543, 359, 03, 2021, 'Isaac Morris');
insert into CreditCard values(6380532562905165, 701, 10, 2026, 'Isabelle Perrier');
insert into CreditCard values(6399793927565784, 516, 7, 2024, 'Grace Haydon');
insert into CreditCard values(6227969677902108, 708, 9, 2022, 'Lauren Rede');
insert into CreditCard values(6361673499313421, 552, 3, 2024, 'Beau Lower');
insert into BOOKING values(1, 1, TO_DATE('2019/09/13', 'yyyy/mm/dd'),
TO_DATE('21:02:44', 'hh24:mi:ss'), 'Ella Marie', 'Cash', 58.00, 'Cash', 100.50,
4510645983016543);
insert into BOOKING values(2, 2, TO_DATE('2019/07/23', 'yyyy/mm/dd'),
TO_DATE('11:55:44', 'hh24:mi:ss'), 'Jack Scherk', 'Credit Card', 88.00, 'Credit Card', 130.50,
6380532562905165);
insert into BOOKING values(3, 3, TO_DATE('2019/02/07', 'yyyy/mm/dd'),
TO_DATE('15:35:04', 'hh24:mi:ss'), 'Ella Marie', 'Credit Card', 76.90, 'Credit Card', 123.50,
6399793927565784);
insert into BOOKING values(4, 4, TO_DATE('2019/05/17', 'yyyy/mm/dd'),
TO_DATE('13:15:04', 'hh24:mi:ss'), 'Jack Scherk', 'Credit Card', 82.30, 'Credit Card', 128.70,
6227969677902108);
insert into BOOKING values(5, 5, TO_DATE('2019/11/27', 'yyyy/mm/dd'),
TO_DATE('07:23:48', 'hh24:mi:ss'), 'Jack Scherk', 'Credit Card', 103.50, 'Credit Card', 182.30,
6361673499313421);

13DATABASE SYSTEM DESIGN AND IMPLEMENTATION
insert into EventRoom values(1, 'ABC', 5);
insert into EventRoom values(2, 'DEF', 8);
insert into EventRoom values(3, 'GHI', 4);
insert into EventRoom values(4, 'KLM', 3);
insert into EventRoom values(5, 'NOP', 3);
insert into WeddingReception values(1, 'Alexis Woolcock', 'Charles Kaleski',
TO_DATE('2020/03/21', 'yyyy/mm/dd'), 78, 'Non-Vegeterian', 'Bar', 'Photoshoot wall', 1);
insert into MeetingRoom values(1, 150, 'Speaker, Mic, AC, chairs, ventilation, smoking
area', 'Projector, Stage');
insert into BusinessEvent values(2, 'ABC Retails', '17 Yarra Street NEWLYN NORTH
VIC 3364', 'Annual Office Party', TO_DATE('2019/12/11', 'yyyy/mm/dd'), 1);
insert into ThemedParty values(3, 'Bar', 80, 2, 'Non-vegeterian');
insert into Individual values(4, TO_DATE('2019/10/23', 'yyyy/mm/dd'),
TO_DATE('2019/10/25', 'yyyy/mm/dd'), 'Yes', 'None');
insert into Individual values(5, TO_DATE('2019/10/27', 'yyyy/mm/dd'),
TO_DATE('2019/10/30', 'yyyy/mm/dd'), 'No', 'None');
insert into RoomBooking values(4, 1);
insert into RoomBooking values(5, 3);
insert into Room values(1, 'Double Bed', 'Heater, TV, Sofa', 'Break Fast, Laptop Stand,
Wi-Fi');
insert into Room values(2, 'Deulux Room', 'Heater, TV, Sofa, Wi-Fi, Wahs, Iron', 'Break
Fast, Laptop Stand');
insert into Room values(3, 'Premium Room', 'Heater, TV, Sofa, Wi-Fi, Wahs, Iron,
Laptop, Bathtub', 'Bar');
Task 2.5:
Query 1: Select client_full_name, bride_name, groom_name, card_number from client
inner join booking on client.client_id = booking.client_id inner join CreditCard on
booking.credit_card = CreditCard.card_number inner join WeddingReception on
booking.booking_id = WeddingReception.booking_id;

14DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Query 2: Select client_full_name, count(booking.booking_id) AS TotalBooking from
client inner join booking on client.client_id = booking.client_id Group By client_full_name;
Query 3: select client_full_name, Booking.booking_date from client inner join Booking
on client.client_id = Booking.client_id where Booking.booking_id = (Select
BusinessEvent.booking_id from BusinessEvent);
Query 4: select client_full_name, Booking.booking_date from client inner join Booking
on client.client_id = Booking.client_id inner join Individual on Booking.booking_id =
Individual.booking_id where start_day > TO_DATE('2019/01/01', 'yyyy/mm/dd') Order By
client_full_name;

15DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Query 5: select client_full_name, Booking.booking_date, room_type, start_day, end_day
from client inner join Booking on client.client_id = Booking.client_id inner join Individual on
Booking.booking_id = Individual.booking_id inner join RoomBooking on Individual.booking_id
= RoomBooking.booking_id inner join Room on RoomBooking.room_id = Room.room_id
where staff_member like '%S%' Order By client_full_name;
Conclusion:
The above pages help us in concluding that this report is all ABC hotel which requires
Oracle DBMS for storing customer details. Client can easily book individual bedrooms for one
or more night. It aims to provide a range from single, double and family background. There are
many instances where client is looking to book one or more number of bedrooms. All the client
details are taken into account. Information with respect to booking is merely inclusive of
breakfast that needs to be recorded. The proposed system will work out with total income that is
associated with various kind of booking that is weeding, theme party and business events.

16DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Bibliography:
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Dennis, A., Wixom, B.H. and Roth, R.M., 2018. Systems analysis and design. John wiley &
sons.
Ibrahim, M.S., Kasim, S., Hassan, R., Mahdin, H., Ramli, A.A., Fudzee, M.F.M. and Salamat,
M.A., 2018. Information Technology Club Management System. Acta Electronica
Malaysia, 2(2), pp.01-05.
Laudon, K.C. and Laudon, J.P., 2016. Management information system. Pearson Education
India.
Sarkar, D., Jana, T. and Saha, S., 2015. LMPID: A manually curated database of linear motifs
mediating protein–protein interactions. Database, 2015.
Shi, L., Zhao, L., Wong, A., Wang, D. and Mok, V., 2015. Mapping the relationship of
contributing factors for preclinical Alzheimer’s disease. Scientific reports, 5, p.11259.
Tufail, M., 2018. Online polyclinic and database management system.
Van Aken, D., Pavlo, A., Gordon, G.J. and Zhang, B., 2017, May. Automatic database
management system tuning through large-scale machine learning. In Proceedings of the 2017
ACM International Conference on Management of Data (pp. 1009-1024). ACM.
Zhang, B., Van Aken, D., Wang, J., Dai, T., Jiang, S., Lao, J., Sheng, S., Pavlo, A. and Gordon,
G.J., 2018. A demonstration of the ottertune automatic database management system tuning
service. Proceedings of the VLDB Endowment, 11(12), pp.1910-1913.

17DATABASE SYSTEM DESIGN AND IMPLEMENTATION
Zheng, Y. and Bai, Q., 2018, February. Design and Implementation of Database Centralized
Access Control and Audit System. In 2018 International Conference on Computer Science,
Electronics and Communication Engineering (CSECE 2018). Atlantis Press.