Database Application for FastBurgers Inc. - RDBMS Solution, HND

This document presents a comprehensive database solution for FastBurgers Inc., a fast-food franchise. The solution encompasses the design and implementation of a relational database management system (RDBMS) to manage the company's operations. It begins with an abstract that provides a concise overview of the project. The report details the entities and attributes of the database, including the `Order`, `Customer`, `Staff`, `Menu`, `Shift`, `FoodItem`, `RegularMenu`, `SaversMenu`, and `Stock` entities. An Entity-Relationship Diagram (ERD) visually represents the relationships between these entities. The design adheres to the principles of normalization, specifically 3NF, to ensure data integrity and minimize redundancy. The report includes the complete SQL code for creating the database tables, along with sample data insertion scripts. Assumptions made during the design process, such as customer registration and shift management, are explicitly stated. Furthermore, the document outlines the business rules that govern the database, such as the breakfast menu time restriction, stock management protocols, and staff responsibilities. Finally, the report provides a series of SQL queries for data selection and projection, demonstrating how to retrieve and manipulate data from the database. The conclusion summarizes the successful completion of the project, highlighting the creation of the database, sample data population, and query execution.

Assessment Title Database Application for FastBurgers
Qualification Module Code and title
HND in Computer Science H16W 35
Relational Database Management Systems
Student ID and Name Assessor’s Name
S VIJEPRATHAP
01000152
Kanchana Senadheera
Cohort Date Issued Submitted on
30-09-2019
No. Learning Outcome Task no
2 Design an RDBMS from a given scenario.
3 Map the design model to the physical model.
4 Create and run SQL statements/queries on a RDBMS.
I certify that the work submitted for this Assessment is my own and research sources are
fully acknowledged.
S Vijeprathap.
Student Signature:……………………………… Date: 30-09-2019

Database Solution for FastBurgers Inc.
S VIJE PRATHAP
01000152
30th September 2019
Abstract
“FastBurgers” is a fast food franchise with over 100 outlets in the UK. We are requested by
the company to create a database solution according to the company’s business rules.
This report includes with Entity Relational Diagram, Entity and Attribute dictionary,
Normalization schema and the Source code of the program.
This report will be the blue print of the program and it included the assumptions which are
taken by me and the business rules of the company create the database solution.
Entities and Attributes
Entity Name: Order
Attribute Datatype
OrderId INT (10)
CustomerId INT (10)
PaymentMethod VARCHAR (50)
MemberId INT (10)
Date DATE
Entity Name: Customer
Attribute Datatype
CustomerId INT (10)
LastName VARCHAR (50)
FirstName VARCHAR (50)
Telephone VARCHAR (10)
Email VARCHAR (50)

Entity Name: Staff
Attribute Datatype
StaffId INT (10)
FirstName VARCHAR (50)
LastName VARCHAR (50)
Role VARCHAR (50)
Entity Name: Menu
Attribute Datatype
MenuId INT (10)
Entity Name: Shift
Attribute Datatype
StaffMemberId INT (10)
StartShift DATETIME
EndShift DATETIME
Entity Name: FoodItem
Attribute Datatype
FoodId INT (10)
Description VARCHAR (50)
Name VARCHAR (50)
Price DECIMAL (10,2)
Entity Name: RegularMenu
Attribute Datatype
MenuId INT (10)
IsBreakFast BIT

Entity Name: SaversMenu
Attribute Datatype
MenuId INT (10)
StartDate DATETIME
EndDate DATETIME
Entity Name: Stock
Attribute Datatype
StockId INT (10)
Name VARCHAR (50)
Unit VARCHAR (50)
Quantity INT (10)

Entity Relationships
Normalized Design
Tables are in 3NF.
Customer
CustomerId FirstName LastName Telephone Email
TheOrder
OrderId CustomerId StaffMemberId PaymentMethod Date
StaffMember
StaffId FirstName LastName Role
Shift
StaffId StartShift EndShift
RegularMenu
MenuId IsBreakfast
SaversMenu
MenuId StartDate EndDate
Menu
MenuId
Food
FoodId Description FoodName Price
StockItem
StockId Name UnitOfMeasuremet Quantity

MenuFoodItem
FoodItemId MenuId
OrderMenu
OrderId MenuId
Implementation
ALTER TABLE customer MODIFY COLUMN CustomerId INT AUTO_INCREMENT;
CREATE TABLE Customer
(
CustomerId INT (10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR (50) NOT NULL,
LastName VARCHAR (50) NOT NULL,
Telephone VARCHAR (10) NOT NULL,
Email VARCHAR (50)
);
CREATE TABLE Staff
(
StaffId INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
FirstName VARCHAR(50) NOT NULL,

LastName VARCHAR(50) NOT NULL,
Role VARCHAR(50) NOT NULL
);
CREATE TABLE TheOrder
(
OrderId INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
CustomerId INT(10) NOT NULL,
StaffId INT (10) NOT NULL,
Paymentmethod VARCHAR(50) NOT NULL,
Date DATE NOT NULL,
CONSTRAINT fk_customerorder FOREIGN KEY (CustomerId) REFERENCES custome
r(CustomerId),
CONSTRAINT fk_stafforder FOREIGN KEY (StaffId) REFERENCES
Staff(StaffId)
);
CREATE TABLE Shift
(
StaffId INT(10) NOT NULL ,
StartShift DATETIME NOT NULL,
EndShift DATETIME NOT NULL,
PRIMARY KEY (StaffId, StartShift, EndShift),
CONSTRAINT fk_StaffShift FOREIGN KEY (StaffId) REFERENCES
Staff(StaffId)
);
CREATE TABLE Menu
(
MenuId INT NOT NULL PRIMARY KEY AUTO_INCREMENT );
CREATE TABLE RegularMenu
(
RegularMenuId INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
IsBreakFast BIT NOT NULL,
CONSTRAINT fk_regularmenumenu FOREIGN KEY (MenuId) REFERENCES Menu(
MenuId)
);
CREATE TABLE SaversMenu
(
SaversMenuId INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
StartDate DATE NOT NULL,

EndDate DATE NOT NULL,
CONSTRAINT fk_saversmenumenu FOREIGN KEY (MenuId) REFERENCES Menu(M
enuId)
);
CREATE TABLE Food
(
FoodItemId INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
FoodName VARCHAR(50),
FoodDescription VARCHAR(50),
Price DECIMAL(10,2)
);
CREATE TABLE MenuFoodItem
(
FoodItemId INT(10) NOT NULL,
MenuId INT(10) NOT NULL,
PRIMARY KEY (FoodItemId, MenuId),
CONSTRAINT fk_menufooditemfooditem FOREIGN KEY (FoodItemId) REFERENCE
S
FoodItem(FoodItemId),
CONSTRAINT fk_menufooditemmenu FOREIGN KEY (MenuId) REFERENCES Menu
(MenuId)
);
CREATE TABLE OrderMenu
(
OrderId INT(10) NOT NULL,
MenuId INT(10) NOT NULL,
PRIMARY KEY (OrderId, MenuId),
CONSTRAINT fk_ordermenu FOREIGN KEY (OrderId) REFERENCES TheOrder(Orde
rId),
CONSTRAINT fk_ordermenumenu FOREIGN KEY (MenuId) REFERENCES Menu(Me
nuId)
);
CREATE TABLE Stock
(
StockId INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT ,
StockName VARCHAR(50) NOT NULL,
Unit VARCHAR(50) NOT NULL,

Quantity INT(10) NOT NULL
);
Assumptions
 Assumed that customers can place order without being registered.
 Assumed that every customer has to provide a telephone number to be
registered.
 Assumed shifts are added as records weekly for each member.
 Assumed automatic request to restock is done by the system.

Business Rules
 Breakfast menu in the regular menu can’t be severed after 11 AM: - As said in
the scenario the restaurant only focuses on making food that is for breakfast for
time period where people take breakfast.
 Manager should check the stock at least once a day: - Managers should check
the stocks to see if it is necessary to restock, not checking frequently can lead
to not having enough stock.
 Servers menu should be changed monthly: - Server menu should be changed
monthly to give something new for the regular customers.
 Cooks can’t take order directly from customers: - Cooks should focus on their
duty on cooking and not interact with the customer directly as proper produces
should be followed.
 All of the products sold should either be on the regular menu or savers menu: -
Custom menus can’t be made per customer request to stick to proper business
operation.
Sample Data
INSERT INTO Customer (FirstName, LastName, Telephone, Email)
VALUES ('Herrod', 'Marshall', '07572882656', '[e-mail]'), ;
INSERT INTO Staff (FirstName, LastName, Role)
VALUES ('Abel', 'Wiggins', 'Manager'),
INSERT INTO TheOrder (CustomerId, StaffId, Paymentmethod, Date)
VALUES (27, 9, 'Cash', '2019/05/12'),
INSERT INTO Shift (StaffId, StartShift, EndShift)
VALUES (1, '2019-06-17T09:00:00', '2019-06-17T18:00:00'),

INSERT INTO menu DEFAULT VALUES
INSERT INTO Food(FoodName, FoodDescription, Price)
VALUES ('Kiwi fruit and daikon salad', 'Fresh kiwi fruit and daikon served on a bed of let
uce', 600.00),
INSERT INTO MenuFoodItem (FoodItemId, MenuId)
VALUES (9, 7),
INSERT INTO OrderMenu (OrderId, MenuId)
VALUES (2, 5)
INSERT INTO Stock (StockName, Unit, Quantity)
VALUES ('Shrimps', '1 KG', 100),
INSERT INTO RegularMenu (RegularMenu, isbreakfast)
VALUES (1, 1)
INSERT INTO SaversMenu (SaversMenu , StartDate, EndDate)
VALUES (11, '2019/05/01', '2019/05/12'),
Data Selection and Projection
SELECT *
FROM customer

SELECT Concat(firstname, ' ', lastname, ' ') AS NAME,
ROLE AS Designation
FROM customer