Pizzeria Management

Project Description

The project revolves around supporting Ben, the owner of a newly established Pizzeria, in managing and optimizing his business operations. As a Data Analyst, my role consists of two phases.

Phase 1: Designing and Building a Custom Relational Database In this phase, the focus is on designing and constructing a bespoke relational database tailored to the specific needs of Ben's Pizzeria. The database will serve as a centralized repository to effectively capture and store all critical information and data generated by the business. This includes customer orders, inventory levels, and staff management details. By implementing this database, Ben will have a reliable and efficient system to organize and manage his business data.

Phase 2: Interactive Dashboard Development The second phase involves the creation and design of an interactive dashboard that will be connected to the Pizzeria Database. The dashboard will provide Ben with a comprehensive visual representation of key business metrics and insights. By leveraging this dashboard, Ben will be able to monitor and analyze customer orders, track inventory levels, and evaluate staff performance. The interactive nature of the dashboard will empower Ben to make data-driven decisions, identify areas for improvement, and optimize his business operations for better efficiency and profitability.

Shop Menu

Project Brief

1. Building a relational database to collect and store data

2. Writing SQL queries to extract required data for further analysis

3. For building dashboards, pulling data into Power BI

4. Requirements Stock inventory

What ingredients go into each pizza
Their quantity is based on the size of the pizza
The existing stock level

5. Staff data requirements

Wants to know which staff members are working when
Based on the staff salary information, how much does pizza cost (ingredient + chefs + delivery)

Project Steps

Step 1: Created a Relational database using QuickDBD considering all the Ben’s requirements.

Below is the snapshot of the same:

relation schema

Step 2: Data Manipulation
Wrote SQL queries in MySQL to get the required data for building a dashboard.
Below are the SQL queries used in the Project:

Orders Table

Inventory Table

Staff Table

Step 3: Data Visualisation
These SQL queries retrieve specific information from the respective tables, which can then be used to build Power BI dashboards

Click the picture to open the dashboard and try it out!