IMAT5103: Database System Design and Implementation for ABC Hotel

This report details the database system design and implementation for ABC Hotel, a family-owned business in Leicester. The report begins with an introduction to the hotel's background and its need for a database to manage bookings and services efficiently. It then presents the conceptual and logical database designs, including Entity-Relationship Diagrams (ERDs) for clients, bookings, and credit cards. The report also includes SQL code for table creation, including constraints and relationships, along with sample data insertion and unique index creation. Furthermore, the report demonstrates the use of SQL queries to retrieve specific information, such as client details, booking information, and room bookings. Finally, the report concludes by summarizing the key findings and the benefits of the proposed database system for ABC Hotel.

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);