Restaurant Management System: Database Design, Queries, and Analysis

This project details the design and implementation of a restaurant management system database. It begins with defining business rules that govern the system's functionality, followed by conceptual and logical database models represented through an Enhanced Entity-Relationship Diagram (EERD) and a logical schema. The database schema includes tables for customers, categories, employees, products, payments, orders, deliveries, and the association between orders and products. The document provides table descriptions, including data types and constraints, and creates unique indexes for efficient data retrieval. Furthermore, it presents a set of SQL queries to retrieve specific data, such as customer details, order information, product counts, employee salaries, and product searches, demonstrating the system's data retrieval capabilities. The project concludes with a bibliography of relevant sources used in the design and development of the restaurant management system. This comprehensive project demonstrates a practical application of database design principles and SQL querying techniques in a real-world scenario.

Running head: RESTAURANT MANAGEMENT SYSTEM
RESTAURANT MANAGEMENT SYSTEM
Name of the Student
Name of the University
Author note

1RESTAURANT MANAGEMENT SYSTEM
Table of Contents
Business Rules.................................................................................................................................2
Conceptual EERD (Enhanced entity relationship diagram)............................................................3
Logical Schema...............................................................................................................................4
Table’s Description..........................................................................................................................5
Index create (Unique)......................................................................................................................9
Queries...........................................................................................................................................10
Query 1......................................................................................................................................10
Query 2......................................................................................................................................10
Query 3......................................................................................................................................11
Query 4......................................................................................................................................12
Query 5......................................................................................................................................12
Bibliography..................................................................................................................................14

2RESTAURANT MANAGEMENT SYSTEM
Business Rules
Business Rules of an organization can represents the business process and functionality
of the company and how it works. In developing the, System design for the organization it also
helps in the modelling. Here the Entity relationship diagram is developed using the business
rules and assumptions. The business rules for the restaurant management system can be
described as follows:
i. The restaurant has many products which it servers and delivers to their customers.
ii. The restaurant has multiple type and cuisines in their menu.
iii. The restaurant has multiple employees for receiving orders and serving food to their
customers.
iv. Each Food product is associated with one and only one category.
v. Each category can have multiple food products.
vi. Each customer can order one or more orders selecting products from the menu.
vii. Each order can have one or more than one product items.
viii. Each product can also be associated with one or more than one orders.
ix. One order can only be associated with only one customer. Customer can select the mode
of delivery or eat on the location.
x. The delivery types are either home delivery or pickup.
xi. Each employee can receive one or more than one order requests.
xii. All the basic details of the employees and customers are stored in the system. For
example name, address, city, contact for both and salary details for the employees.
xiii. Customers can pay for their order by cash, debit card, credit card or using PayPal.
xiv. Each payment details is associated with one on only one order records.

3RESTAURANT MANAGEMENT SYSTEM
Conceptual EERD (Enhanced entity relationship diagram)
Figure 1: Conceptual Diagram
Source: created by author

4RESTAURANT MANAGEMENT SYSTEM
Logical Schema
Figure 2: Logical Diagram
Source: created by author

5RESTAURANT MANAGEMENT SYSTEM
Table’s Description
P2558806Customer
create table P2558806customer (CustomerID varchar(5) primary key, Name varchar(30),
Address varchar (50), City varchar(30),
Preferences varchar(100), Email varchar(30), PhoneNo number(10));
P2558806Category
create table P2558806category (CategoryID number(5) primary key, Name varchar(30),
Description varchar(100));
P2558806Employee
create table P2558806Employee (EmployeeID varchar(5) primary key, EmpName varchar(30),
Address varchar (50), City varchar(30),

6RESTAURANT MANAGEMENT SYSTEM
Contact number(10), Salary decimal(8,2));
P2558806Product
create table P2558806Product (ProductID varchar(5) primary key, Name varchar(30),
Rate decimal(6,2), Description varchar(100), CategoryID number(10));
alter table P2558806Product
add constraint fk_cat_prod
foreign key(CategoryID) references P2558806Category(CategoryID);
P2558806Payment
create table P2558806Payment (PaymentID number(10) primary key,

7RESTAURANT MANAGEMENT SYSTEM
PaymentMethod varchar(30), PaymentDate Date);
P2558806Order
create table P2558806Order (OrderID varchar(5) primary key, OrderDate date,
Rating decimal(2,1), EmployeeID varchar(5), PaymentID number(10), CustomerID varchar(5));
alter table P2558806Order
add constraint fk_ord_emp
foreign key (EmployeeID) references
P2558806Employee(EmployeeID);
alter table P2558806Order
add constraint fk_ord_cust
foreign key (CustomerID) references
P2558806Customer(CustomerID);

8RESTAURANT MANAGEMENT SYSTEM
alter table P2558806Order
add constraint fk_ord_pay
foreign key (PaymentID) references
P2558806Payment(PaymentID);
P2558806Delivery
create table P2558806Delivery (OrderID varchar(5) not null, DeliveryMethod varchar(30),
DeliveryDate date,
DeliveryAddress varchar(50), Rating decimal(2,1), Comments varchar(100), constraint
fk_del_ord
foreign key(OrderID) references P2558806Order(OrderID));

9RESTAURANT MANAGEMENT SYSTEM
P2558806Order_has_Products
create table P2558806Order_has_Products (OrderID varchar(5), ProductID varchar(5),
Quantity number(10), amount decimal(8,2), primary key(OrderID,ProductID),Constraint
fk_ohp_ord
foreign key(OrderID) references P2558806Order(OrderID), constraint fk_ohp_pro
foreign key(ProductID) references P2558806Product(ProductID));
Index create (Unique)
create unique index cust_phone on P2558806Customer(PhoneNo);
create unique index cust_email on P2558806Customer(Email);
create unique index prod_name on P2558806Product(Name);
create unique index emp_con on P2558806Employee(Contact);

10RESTAURANT MANAGEMENT SYSTEM
create unique index cat_name on P2558806Category(Name);
Queries
Query 1
select name, address, city, phoneno from P2558806customer;
Description: The query shows the some selected columns from a table. The name, address, city
and phone numbers of the customers have been displayed.
Query 2
select c.name,o.orderid,o.orderdate,d.deliverymethod, d.deliveryaddress from
P2558806customer c inner join P2558806order o on
o.customerid=c.customerid inner join P2558806delivery d
on d.orderid=o.orderid;

11RESTAURANT MANAGEMENT SYSTEM
Description: The following query has been implemented using inner join. The three tables,
order, customer and delivery have been joined to get the order details and their delivery type.
Query 3
select orderid, count(productid) from P2558806Order_has_products group by orderid;

12RESTAURANT MANAGEMENT SYSTEM
Description: The query uses mathematical function count for returning the count of the products
ordered for each order ID.
Query 4
select empname, address, city, contact, salary from P2558806Employee order by salary desc;
Description: The query returns the salaries of the employees in descending order of the
salaries.
Query 5
select productid, name, rate from P2558806Product where name like '%Chicken%';

13RESTAURANT MANAGEMENT SYSTEM
Description: The query uses the Like keyword for string matching up. The dishes and products
which have chicken is retrieved from the query.
Query 6
select empname, address, contact from P2558806Employee where
employeeid not in (Select distinct employeeid from P2558806Order);
Description: Sub query is used to get the names of the employee who has not been provided any
services associated with any order.

14RESTAURANT MANAGEMENT SYSTEM
Bibliography
Patel, M., 2015. Online Food Order System for Restaurants.
Jakhete, M.D. and Mankar, P.C., 2015. Implementation of Smart Restaurant with e-menu
Card. International Journal of Computer Applications, 119(21).
Thalheim, B., 2013. Entity-relationship modeling: foundations of database technology. Springer
Science & Business Media.
Cleve, A., Gobert, M., Meurice, L., Maes, J. and Weber, J., 2015. Understanding database
schema evolution: A case study. Science of Computer Programming, 97, pp.113-121.
Cadle, J., Paul, D. and Turner, P., 2014. Business analysis techniques. Chartered Institute for IT.
Hoffer, J.A., Ramesh, V. and Topi, H., 2013. Modern database management. Boston: Pearson,.