Database Design and Implementation: Hotel Management System Project

This assignment showcases a student's solution for a database systems and design project focused on a hotel management system. The solution begins with a scenario outlining the requirements of the system, including guest and room information, booking processes, service records, and bill generation. It then progresses to conceptual database design, followed by logical database design, where tables are created in an Oracle database. The assignment details the creation of tables such as Family, Family_Members, Company, Company_Members, Guest, Bill, Rooms, and Alloted, along with specifying primary and foreign key constraints. Indexes are created for efficient data retrieval. The solution also includes data population with insert statements, populating the created tables with sample data. Finally, the assignment demonstrates SQL querying, including examples of selecting data from multiple tables, using aggregate functions, and filtering results based on conditions. The assignment concludes with a bibliography of the resources used.

Running head: DATABASE SYSTEMS AND DESIGN
Database Systems and Design
Name of the Student
Name of the University
Author’s note:

1DATABASE SYSTEMS AND DESIGN
Table of Contents
Stage 1: Scenario and Conceptual Database Design.......................................................................2
Task 1.1: Scenario.......................................................................................................................2
Task 1.2: Conceptual Database Design.......................................................................................2
Stage 2: Logical Database Design and Oracle SQL Implementation/Querying..............................3
Task 2.1: Logical Database Design:............................................................................................3
Task 2.2: Tables in Oracle Database...........................................................................................3
Task 2.3: Indexes.........................................................................................................................7
Task 2.4: Data Population............................................................................................................7
Task 2.5: Queries.......................................................................................................................10
Bibliography:.................................................................................................................................12

2DATABASE SYSTEMS AND DESIGN
Stage 1: Scenario and Conceptual Database Design
Task 1.1: Scenario
The requirements from the hotel management system are as following. The database must
store the information about the guests as well as the rooms allotted to them. Customer must have
information about the room availability. Guests must book the available rooms online. The
record of services and food availed by the consumer should be stored. Bill must be generated for
the customer.
Task 1.2: Conceptual Database Design

3DATABASE SYSTEMS AND DESIGN
Figure 1: Conceptual Database Design of Hotel Management System
(Source: Created by Author)
Stage 2: Logical Database Design and Oracle SQL Implementation/Querying
Task 2.1: Logical Database Design:
Figure 2: Logical Database Design of Hotel Management System
(Source: Created by Author)
Task 2.2: Tables in Oracle Database
Create Table Family (
Family_Head_SSN varchar2(20) not null constraint family_pk primary key,
Family_Head_Name varchar2(100) not null,

4DATABASE SYSTEMS AND DESIGN
Address varchar2(240) not null,
Phone_Number varchar2(50),
No_Of_Adults number,
No_Of_Children number
);
Create Table Family_Members (
Family_Head_SSN varchar2(20) not null,
Member_Name varchar2(100) not null,
Age number,
constraint family_member_pk primary key (Family_Head_SSN, Member_Name),
constraint fk_family_head foreign key (Family_Head_SSN) references
Family(Family_Head_SSN)
);
Create Table Company (
Company_Name varchar2(100) not null constraint company_pk primary key,
Member_Location varchar2(240) not null
);

5DATABASE SYSTEMS AND DESIGN
Create Table Company_Members (
Company_Name varchar2(100) not null,
Member_Name varchar2(100) not null,
Age number,
Designation varchar2(100) not null,
constraint company_member_pk primary key (Company_Name, Member_Name),
constraint fk_company_name foreign key (Company_Name) references
Company(Company_Name)
);
Create Table Guest (
Guest_ID varchar2(20) not null constraint guest_pk primary key,
Booked_By_Company varchar2(20),
Booked_By_Family varchar2(20),
constraint fk_company foreign key (Booked_By_Company) references
Company(Company_Name),
constraint fk_companyfamily foreign key (Booked_By_Family) references
Family(Family_Head_SSN)

6DATABASE SYSTEMS AND DESIGN
);
Create Table Bill (
Bill_No varchar2(20) not null constraint bill_pk primary key,
Amount number(9,2) not null,
Bill_Date Date not null,
Paying_Method varchar2(100) not null,
Guest_ID varchar2(20) not null,
constraint fk_bill_payment foreign key (Guest_ID) references Guest(Guest_ID)
);
Create Table Rooms (
Room_No varchar2(100) not null constraint room_pk primary key,
Room_Type varchar2(100) not null,
Rate number(9,2) not null,
Status varchar2(100) not null
);

7DATABASE SYSTEMS AND DESIGN
Create Table Alloted (
Room_No varchar2(100) not null,
Guest_ID varchar2(20) not null,
Check_In_Date Date not null,
Check_Out_Date Date not null,
constraint room_alloted_pk primary key (Room_No, Guest_ID),
constraint fk_alloted_room foreign key (Room_No) references
Rooms(Room_No),
constraint fk_guest_room foreign key (Guest_ID) references Guest(Guest_ID)
);
Task 2.3: Indexes
CREATE INDEX family_head_name_i ON Family(Family_Head_Name);
CREATE INDEX bill_amount_i ON Bill(Amount);
CREATE INDEX company_member_name_i ON Company_Members(Member_Name);
CREATE INDEX family_member_name_i ON Family_Members(Member_Name);
Task 2.4: Data Population
insert into family values('003-94-3842', 'William Ward', '1 Prince Consort Road
KENNAVAY HS4 4JF', '070 4898 1251', 2, 2);

8DATABASE SYSTEMS AND DESIGN
insert into family values('017-92-7770', 'Archie Cooper', '17 Walden Road
GREENLOANING FK15 0UE', '077 0542 5137', 3, 1);
insert into family values('086-70-3279', 'Rebecca Craig', '33 Simone Weil Avenue
WATTON IP25 6QH', '070 3174 2182', 2, 1);
insert into family values('442-14-9339', 'Daniel Jordan', '87 Holgate Rd RAMSEY IM8
0JP', '078 7628 6556', 2, 0);
insert into family values('541-82-1191', 'Ben Dean', '74 Worthy Lane MARYPORT DG9
8EJ', '077 6007 3670', 2, 1);
insert into Family_Members values('003-94-3842', 'Sophie Ward', 28);
insert into Family_Members values('003-94-3842', 'Abigail Ward', 3);
insert into Family_Members values('017-92-7770', 'Jennifer Cooper', 78);
insert into Family_Members values('017-92-7770', 'Elise Cooper', 32);
insert into Family_Members values('017-92-7770', 'Zak Cooper', 7);
insert into Company values('Royal Dutch Shell', '32 Trinity Crescent WHATCOTE
CV36 8AD');
insert into Company values('Dolomite Bio', '65 St Andrews Lane DALCHALM KW9
3RN');

9DATABASE SYSTEMS AND DESIGN
insert into Company values('Film Producers Guild', '7 Dunmow Road GRIMSTON PE32
9ZE');
insert into Company values('Galpharm International', '82 Broomfield Place STONES
GREEN CO12 9XF');
insert into Company values('Grosvenor Casinos', '82 Helland Bridge UPCHURCH ME9
8BE');
insert into Company_Members values('Royal Dutch Shell', 'Noah Shepherd', 48,
'Corporate Technical Officer');
insert into Company_Members values('Dolomite Bio', 'Oscar Houghton', 34, 'Pharmacist
Head');
insert into Company_Members values('Film Producers Guild', 'Maddison Kirby', 67,
'Managing Director');
insert into Company_Members values('Galpharm International', 'Ellis Bartlett', 27,
'Researcher');
insert into Company_Members values('Grosvenor Casinos', 'Ruby Patterson', 41,
'Manager');
insert into Rooms values('G101', 'Normal', 100, 'Available');
insert into Rooms values('G102', 'Delux', 250, 'Available');

10DATABASE SYSTEMS AND DESIGN
insert into Rooms values('First101', 'Normal', 130, 'Available');
insert into Rooms values('First102', 'Delux', 320, 'Available');
insert into Rooms values('Second101', 'Delux', 430, 'Unavailable');
insert into Guest values('101', '', '003-94-3842');
insert into Guest values('102', 'Dolomite Bio', '');
insert into Alloted values('G101', '101', TO_DATE('2019/05/03', 'yyyy/mm/dd'),
TO_DATE('2019/05/13', 'yyyy/mm/dd'));
insert into Alloted values('Second101', '102', TO_DATE('2019/01/11', 'yyyy/mm/dd'),
TO_DATE('2019/01/14', 'yyyy/mm/dd'));
insert into Bill values('Bill101', 1500, TO_DATE('2019/05/13', 'yyyy/mm/dd'), 'Cash',
'101');
insert into Bill values('Bill102', 2000, TO_DATE('2019/01/14', 'yyyy/mm/dd'), 'Credit
Card', '102');
Task 2.5: Queries
Select Family_Head_Name, Member_Name from Family_Members inner join Family on
Family_Members.Family_Head_SSN = Family.Family_Head_SSN order by
Family_Members.Family_Head_SSN;

11DATABASE SYSTEMS AND DESIGN
Select Family_Head_Name, Count(Member_Name) as Number_of_Members from
Family_Members inner join Family on Family_Members.Family_Head_SSN =
Family.Family_Head_SSN group by Family_Head_Name;
Select Family_Head_Name, Count(Booked_By_Family) as Number_of_Booking from
Family full outer join guest on Family.Family_Head_SSN = Guest.Booked_By_Family where
Booked_By_Family IS NULL group by Family_Head_Name;
Select Company.Company_Name from Company where Company_Name Like '%a%'
order by Company_Name;
select Guest.Guest_ID from Guest inner join Bill B1 on Guest.Guest_ID = B1.Guest_ID
where B1.Amount = (Select MAX(B2.Amount) from Bill B2);
select Guest.Guest_ID, Count(Alloted.Guest_ID) as Number_of_Room from Guest inner
join Alloted on Guest.Guest_ID = Alloted.Guest_ID Group By Guest.Guest_ID;

12DATABASE SYSTEMS AND DESIGN
Bibliography:
Gould, H., 2015. Database design and implementation: A practical introduction using Oracle
SQL.
Hossain, M., Mahmud, S. and Santa, T.D., 2019. Oracle, MySQL, PostgreSQL, SQLite, SQL
Server: Performance based.