Database Design and Implementation: Restaurant Management System

This assignment presents a database design and implementation project for a restaurant management system, named Sweet Pot. The project begins with a case scenario outlining the restaurant's operations, departments, and data requirements. It then moves on to the conceptual database design, including enterprise rules, entities, and their attributes, visualized with an enhanced entity-relationship diagram. The logical database design phase details the table structures and keys. The implementation uses Oracle DBMS, including table creation with SQL scripts, creation of indexes for optimization, and data population for all tables. The project concludes with SQL query writing to retrieve and manipulate data. The database encompasses information on employees, products, suppliers, customers, and their respective orders, providing a comprehensive solution for managing restaurant operations.

Course Code and ID:
Student Number and Name:
Lecturer Name:
Submission Date:

Table of Contents
INTRODUCTION.......................................................................................................................................3
Stage 1: Scenario and Conceptual Database Design................................................................................4
Task 1.1: Case Scenario upon which the database design and implementation is to be based...........4
Task 1.2: Conceptual database design for the case scenario & the list of enterprise rules being
modelled..............................................................................................................................................4
Stage 2: Logical Database Design and Oracle SQL Implementation/querying........................................7
Task 2.1: Logical Database Design.......................................................................................................7
Task 2.2: Creation of the tables using Oracle DBMS............................................................................7
Task 2.3: Four indexes on tables..........................................................................................................8
Task 2.4: Data Population....................................................................................................................9
TASK 2.5: SQL Query writing..............................................................................................................17
Conclusion.............................................................................................................................................19
References.............................................................................................................................................20

INTRODUCTION
Databases play a very important role in maintaining organization’s data. Among the few
advantages that databases offer to the organizations include easy storage and retrieval of data,
reduction of data redundancy, improved data consistency, data security among others. In this
context, database design and implementation focus on a restaurant management system. We
shall be implementing the database using Oracle database management system for Sweet Pot
restaurant. The database will maintain the restaurants information about the employees,
products sold, suppliers, products the suppliers deliver, customers and their respective orders.
An entity relationship diagram has also been supplied to provide a visual understanding of the
database structure. Sample data has also been populated to test the database.

Stage 1: Scenario and Conceptual Database Design
Task 1.1: Case Scenario upon which the database design and
implementation is to be based
(a) Background information on the organisation
In this case scenario, a restaurant management system has been selected. Sweet pot is a
fictious restaurant which offers food and beverage services to its customers. The restaurant is
structured into 5 departments, the Management department, Accounting and finance
department, Sanitation department, Kitchen department and Reception and service department.
The management department is responsible for managing the day to day activities of the
restaurant and includes the Manager, Assistant manager, Marketing director and other
employees employed in that department. The accounting and finance department is responsible
for managing all the financial resources of the restaurant. This includes ordering and paying for
items brought in by suppliers. The sanitation department is responsible for maintaining the
overall cleanliness of the restaurant. The kitchen department is responsible for preparing meals
according to the customer orders. The reception and service department offer services to the
customers including receiving customers and serving them with their orders.
Basically, the restaurant gets most of its goods from suppliers who deliver them upon an order
made by the restaurant through the Accounting and finance department. Upon the arrival of a
customer, employees from the Reception and service department receives them and the
customer proceeds to the Accounting and finance department to make an order and make
payments. A customer is then issued with a receipt indicating the meals ordered. Employees
from the reception and service department liaises with the kitchen department to deliver the
customer order.
(b) An overview of what operations a database would need to
support
The database is expected to maintain the following information:
i. Departments details – Department ID, Department name
ii. Employees Details – Employee ID, First Name, Last Name, Date employed,
Department, Address, Street, City and Phone number.
iii. Product details – Product ID, Product Description, Price
iv. Supplier details – Supplier ID, Name, Address, Street, City and Phone number.
v. Supplier deliverables – Delivery ID, Supplier ID, Delivery Date.
vi. Supplier Products – ID, Product ID, Price and Quantity.
vii. Customer details – Customer ID, First Name, Last Name, Gender, Phone number.
viii. Customer Orders – Order ID, Order date, Customer ID.
ix. Customer Ordelines – Line ID, Order ID, Product ID, Price and Quantity.

Task 1.2: Conceptual database design for the case scenario & the
list of enterprise rules being modelled.
Enterprise rules
The following are the business rules to be modelled.
i. A department has many employees and an employee belongs to one and only one
department.
ii. A supplier can supply many products and a single product can be supplied by
different suppliers.
iii. A supplier can have many deliverables and each deliverable can have many
products.
iv. A customer can make many orders and each order can have many customer
ordelines i.e. many products. (Mancas, 2016)
Entities and their attributes
i. Department – DepartmentID, DepartmentName
ii. Employee – EmployeeID, FirstName, LastName, DateEmployed, DepartmentID,
Address, Street, City, PhoneNo, Position.
iii. Product – ProductID, ProductDescription, Price
iv. Supplier – SupplierID, Name, Address, Street, City and PhoneNo.
v. Supplier_deliverable – DeliveryID, SupplierID, DeliveryDate.
vi. Supplier_Product – ID, DeliveryID, ProductDescription, Price and Quantity.
vii. Customer – CustomerID, FirstName, LastName, Gender, PhoneNo.
viii. Customer_Order – OrderID, OrderDate, CustomerID.
ix. Customer_Ordeline – LineID, OrderID, ProductID, Price and Quantity.
Enhanced Entity Relationship Diagram
The enhanced entity relationship diagram below illustrates the relationship between the
entities. The EER Diagram further shows the specialization and generalization structures of
the database.

(Bagui & Earp, 2011)

Stage 2: Logical Database Design and Oracle SQL
Implementation/querying
Task 2.1: Logical Database Design
Table Name Field names and Keys
Department_Details DepartmentID (PK), DepartmentName
Employee_Details EmployeeID (PK), FirstName, LastName, DateEmployed,
DepartmentID (FK), Address, Street, City, PhoneNo, Position.
Product_Details ProductID (PK), ProductDescription, Price
Supplier_Details SupplierID (PK), Name, Address, Street, City, PhoneNo.
Supplier_Deliverables DeliveryID (PK), SupplierID (FK), DeliveryDate, EmployeeID (FK).
Supplier_Product_Details ID (PK), DeliveryID (FK), ProductDescription, Price, Quantity.
Customer_Details_Tbl CustomerID (PK), FirstName, LastName, Gender, PhoneNo.
Customer_Order_Details OrderID (PK), OrderDate, CustomerID (FK)
Customer_Ordelines LineID (PK), OrderID (FK), ProductID (FK), Price, Quantity.
Task 2.2: Creation of the tables using Oracle DBMS
i. Department_Details
- This tables stores information about the departments.
CREATE TABLE P2512083Department_Details (DepartmentID Varchar2(12) PRIMARY KEY,
DepartmentName Varchar2(36));
ii. Employee_Details
- This tables stores information about the employees.
CREATE TABLE P2512083Employee_Details (EmployeeID Varchar2(12) PRIMARY KEY,
FirstName Varchar2(20), LastName Varchar2(20), DateEmployed Date, DepartmentID
Varchar2(12) REFERENCES Department_Details (DepartmentID) , Address Varchar2(50),
Street Varchar2(24), City Varchar2(24), PhoneNo Varchar2(24), Position Varchar2(24));
iii. Product_Details
- This tables stores information about the products.
CREATE TABLE P2512083Product_Details (ProductID Varchar2(12) PRIMARY KEY,
ProductDescription Varchar2(80), Price float);
iv. Supplier_Details

- This tables stores information about the suppliers.
CREATE TABLE P2512083Supplier_Details (SupplierID Varchar2(12) PRIMARY KEY, Name
Varchar2(24), Address Varchar2(50), Street Varchar2(24), City Varchar2(24), PhoneNo
Varchar2(24));
v. Supplier_Deliverables
- This tables stores information about the supplier deliverables per turn of delivering
products to the restaurant.
CREATE TABLE P2512083Supplier_Deliverables (DeliveryID Varchar2(12) PRIMARY KEY,
SupplierID Varchar2(12) REFERENCES Supplier_Details (SupplierID), DeliveryDate Date,
EmployeeID Varchar2(12) REFERENCES Employee_Details (EmployeeID));
vi. Supplier_Product_Details
- This tables stores information about the products the suppliers deliver to the restaurant.
CREATE TABLE P2512083Supplier_Product_Details (ID Number (3) PRIMARY KEY,
DeliveryID Varchar2(12) REFERENCES Supplier_Deliverables (DeliveryID), ProductDescription
Varchar2(50), Price float, Quantity Number (2));
vii. Customer_Details_Tbl
- This tables stores information about the customers.
CREATE TABLE P2512083Customer_Details_Tbl (CustomerID Varchar2(12) PRIMARY KEY,
FirstName Varchar2(24), LastName Varchar2(24), Gender Varchar2(8), PhoneNo
Varchar2(18));
viii. Customer_Order_Details
- This tables stores information about the customer orders.
CREATE TABLE P2512083Customer_Order_Details (OrderID Varchar2(12) PRIMARY KEY,
OrderDate Date, CustomerID Varchar2(12) REFERENCES Customer_Details_Tbl
(CustomerID));
ix. Customer_Ordelines
- This tables stores information about the products customers order in their respective
orders.
CREATE TABLE P2512083Customer_Ordelines (LineID Number (3) PRIMARY KEY, OrderID
Varchar2(12) REFERENCES Customer_Order_Details (OrderID), ProductID Varchar2(12)
REFERENCES Product_Details (ProductID), Price float, Quantity Number(3));
Task 2.3: Four indexes on tables
i. Index on Customer Details Table

Create unique index cust_index on P2512083Customer_Details_Tbl (PhoneNo);
Explanation: The index is created to ensure that the Phone number of each customer
is unique.
ii. Index on Employees Details
Create unique index Emp_index on P2512083Employee_Details (EmployeeID,
DepartmentID);
Explanation: This unique index ensures that every employee belongs to strictly one
Department.
iii. Index on Supplier Details
Create unique index Sup_index on P2512083Supplier_Details (PhoneNo);
Explanation: The index has been created to ensure that every supplier has a unique phone
number.
iv. Index on Product details
Create unique index Prod_index on P2512083Product_Details (ProductDescription);
Explanation: This index is created to maintain the uniqueness of every product description in
the products details table.
Task 2.4: Data Population
i. Department_Details
SQL Codes for data population
INSERT INTO P2512083DEPARTMENT_DETAILS (DEPARTMENTID,
DEPARTMENTNAME) VALUES ('001','Management');
INSERT INTO P2512083DEPARTMENT_DETAILS (DEPARTMENTID,
DEPARTMENTNAME) VALUES ('002','Accounting and finance');
INSERT INTO P2512083DEPARTMENT_DETAILS (DEPARTMENTID,
DEPARTMENTNAME) VALUES ('003','Sanitation');

INSERT INTO P2512083DEPARTMENT_DETAILS (DEPARTMENTID,
DEPARTMENTNAME) VALUES ('004','Kitchen');
INSERT INTO P2512083DEPARTMENT_DETAILS (DEPARTMENTID,
DEPARTMENTNAME) VALUES ('005','Reception and service');
SELECT * FROM P2512083DEPARTMENT_DETAILS;
ii. Employee_Details
SQL Codes for data population
INSERT INTO P2512083EMPLOYEE_DETAILS (EMPLOYEEID, FIRSTNAME,
LASTNAME, DATEEMPLOYED, DEPARTMENTID, ADDRESS, STREET, CITY,
PHONENO, POSITION) VALUES ('EM001', 'Hedda', 'Goodman', '15-JAN-2010','001', '9212
Ultricies Road', 'Second St', 'Chicago','[phone]','Manager');
INSERT INTO P2512083EMPLOYEE_DETAILS (EMPLOYEEID, FIRSTNAME,
LASTNAME, DATEEMPLOYED, DEPARTMENTID, ADDRESS, STREET, CITY,
PHONENO, POSITION) VALUES ('EM002', 'Gillian', 'Woodward', '11-FEB-2010','002', 'P.O.
Box 842, 8685 Amet, St.', 'First St', 'Chicago','[phone]','Chief Accountant');
INSERT INTO P2512083EMPLOYEE_DETAILS (EMPLOYEEID, FIRSTNAME,
LASTNAME, DATEEMPLOYED, DEPARTMENTID, ADDRESS, STREET, CITY,
PHONENO, POSITION) VALUES ('EM003', 'Brandon', 'Barnes', '18-FEB-2010','002', '7487
Malesuada Rd.', 'Main St', 'Wilmette','[phone]','Accountant');

INSERT INTO P2512083EMPLOYEE_DETAILS (EMPLOYEEID, FIRSTNAME,
LASTNAME, DATEEMPLOYED, DEPARTMENTID, ADDRESS, STREET, CITY,
PHONENO, POSITION) VALUES ('EM004', 'Nissim', 'Travis', '18-FEB-2010','003', '849-5802
Libero Rd.', 'Main St', 'Wilmette','[phone]','Sanitary Assistant');
INSERT INTO P2512083EMPLOYEE_DETAILS (EMPLOYEEID, FIRSTNAME,
LASTNAME, DATEEMPLOYED, DEPARTMENTID, ADDRESS, STREET, CITY,
PHONENO, POSITION) VALUES ('EM005', 'Leah', 'Travis', '21-FEB-2010','004', 'P.O. Box
814, 3690 Libero. Road', 'River Ave', 'Kenosha','[phone]','Chef');
INSERT INTO P2512083EMPLOYEE_DETAILS (EMPLOYEEID, FIRSTNAME,
LASTNAME, DATEEMPLOYED, DEPARTMENTID, ADDRESS, STREET, CITY,
PHONENO, POSITION) VALUES ('EM006', 'Cassidy', 'Mccarty', '25-FEB-2010','005', '907-
2981 Vel Av.', 'River Ave', 'Kenosha','[phone]','Receiptionist');
SELECT * FROM P2512083EMPLOYEE_DETAILS;
iii. Product_Details
SQL Codes for data population
INSERT INTO P2512083PRODUCT_DETAILS (PRODUCTID, PRODUCTDESCRIPTION,
PRICE) VALUES ('P001','Fried Chicken', 10.5);
INSERT INTO P2512083PRODUCT_DETAILS (PRODUCTID, PRODUCTDESCRIPTION,
PRICE) VALUES ('P002','Poke', 9.5);
INSERT INTO P2512083PRODUCT_DETAILS (PRODUCTID, PRODUCTDESCRIPTION,
PRICE) VALUES ('P003','500 Ml Cocacola Drink', 2.5);

INSERT INTO P2512083PRODUCT_DETAILS (PRODUCTID, PRODUCTDESCRIPTION,
PRICE) VALUES ('P004','Pizza', 8.5);
INSERT INTO P2512083PRODUCT_DETAILS (PRODUCTID, PRODUCTDESCRIPTION,
PRICE) VALUES ('P005','Coffee', 3.6);
INSERT INTO P2512083PRODUCT_DETAILS (PRODUCTID, PRODUCTDESCRIPTION,
PRICE) VALUES ('P006','Rice Bowls', 2.6);
INSERT INTO P2512083PRODUCT_DETAILS (PRODUCTID, PRODUCTDESCRIPTION,
PRICE) VALUES ('P007','Tea', 1.4);
SELECT * FROM P2512083PRODUCT_DETAILS;
iv. Supplier_Details
SQL Codes for data population
INSERT INTO P2512083SUPPLIER_DETAILS (SUPPLIERID, NAME, ADDRESS,
STREET, CITY, PHONENO) VALUES ('SP001','David Hills', '189-438 Porttitor St.', 'North
Ave', 'Gary','[phone]')
INSERT INTO P2512083SUPPLIER_DETAILS (SUPPLIERID, NAME, ADDRESS,
STREET, CITY, PHONENO) VALUES ('SP002','Peter Davis', 'P.O. Box 352, 3571 Integer
Ave','North Ave', 'Gary','[phone]');
INSERT INTO P2512083SUPPLIER_DETAILS (SUPPLIERID, NAME, ADDRESS,
STREET, CITY, PHONENO) VALUES ('SP003','Mazx Miller', '4052 Vel, Road','North Ave',
'Gary','[phone]');