SQL Database Project for Pizzeria: Design and Dashboard Creation

A compelling SQL project enhances a data analyst's portfolio by showcasing database design and query skills.

### Designing and Building a SQL Database for a Pizzeria: A Step-by-Step Guide
* 🍕 The project involves designing and building a relational database for a new pizzeria, focusing on **customer orders, stock levels, and staff data**.
* 🏗 The first step is to design the database schema, starting with the **orders table** and normalizing the data by creating additional tables for **customers, addresses, and items**.
* 📊 The database is created using a tool called Quick DBD, and the SQL code is exported to **set up the database** in a MySQL environment.
* 📈 SQL queries are then written to generate views for business intelligence (BI) dashboards, including **total orders, total sales, and other key metrics**.
* 🍅 For stock control, queries are created to **calculate total quantity by ingredient**, **total cost of ingredients**, and the **cost of each pizza**.
* 💼 Staff data is also incorporated, with queries to **track staff working hours** and **calculate the cost of each pizza** based on staff salaries.
* 🔍 The project demonstrates **essential SQL skills for data analysis and BI**, offering a **practical example for portfolio development**.
* 🛠 The process includes **database design**, **query writing**, and connecting the database to a BI tool to build **interactive dashboards for business monitoring**.

### Building a Comprehensive BI Dashboard for a Restaurant Business
* 📊 The project involves designing and building **three dashboards** using Google Data Studio to visualize data collected in a MySQL database.
* 🍕 The first dashboard focuses on orders, with **10 visualizations** including **total orders**, **total sales**, and **sales by category**.
* 🍅 The second dashboard covers inventory, displaying **total quantity by ingredient**, **total cost of ingredients**, and **percentage stock remaining by ingredient**.
* 👩 The third dashboard is about staff, showing **total staff cost**, **total hours worked**, and **hours worked by staff member**.
* 🔗 The data sources include **tables**, **custom SQL queries**, and **blending data from multiple sources**.
* 🎨 Dashboard design elements include **theme extraction from images**, **adding titles**, and **adjusting grid lines for better readability**.
* 🚀 The project concludes with a **fully functional and visually appealing BI dashboard** for a restaurant business.

Data Analyst Portfolio Project - SQL | Step-by-Step Guide From SQL Database to Interactive Dashboard

Adam Finer - Learn BI Online

The project involves designing and building a SQL database for a new pizzeria, focusing on customer orders, stock levels, and staff.

The database will capture important information, with a tailored approach to normalizing data and creating related tables.

The use of a tool, Quick Database Diagrams, will help visualize the database structure and establish table relationships.

Ben's pizza business will have an item table to streamline menu item changes and reduce data redundancy.

Stock control will be managed through ingredient and recipe tables, with a separate inventory table for stock levels.

Staff data will be organized through staff, shift, and rotor tables, with relationships established to track working hours and calculate pizza costs.

Creating a Dashboard: SQL Queries for Orders and Inventory

The dashboard requires specific columns from the orders, item, and address tables, which are joined using SQL.

The first query calculates the total quantity of ingredients by breaking down pizza orders and joining with the recipe and ingredient tables.

Subqueries are used to handle aggregated fields, allowing for the calculation of total cost and other desired metrics for the dashboard.

The process involves calculating the ordered weight of ingredients, unit cost, and ingredient cost to determine the total cost of making each pizza.

A new view is created from the custom SQL query to aggregate ordered weight, inventory, and remaining stock for each ingredient.

The staff costs are calculated by joining the rotor, staff, and shift tables, and using the time difference to determine hours worked and total staff cost for each row.

Building Dashboards with Google Data Studio and MySQL

The project involves designing and building dashboards using data from a Google Cloud MySQL database.

Google Data Studio is chosen as the BI tool for its free access and necessary functionalities.

The dashboard will include 10 visualizations based on client queries, such as total orders, total sales, and average order value, with a focus on creating the queries first before designing the dashboard.

The total sales figure is calculated differently for average order value, using the sum of total sales divided by the distinct count of order ID.

A donut chart is used to display total sales by item category, while a bar chart is chosen for top-selling items to show values vertically and compare them easily.

Orders by hour and sales by hour are displayed together in a line chart, with total orders on a right y-axis for better scale comparison.

A new page for inventory has been added to the report, featuring a table to visualize over 40 ingredients and products.

The table includes metrics for total quantity and total cost, with a scorecard for the total ingredient cost.

The dashboard also features a blended table for inventory remaining and ingredients to reorder, using conditional formatting to highlight urgent and soon-to-be-reordered items.

The staff dashboard includes metrics for total staff cost and total hours worked, with additional data on hours worked by each staff member and cost per staff member in a table.

The dashboard design incorporates a consistent theme, with a rectangle and date range control added at the report level for uniformity across pages.

The final touches include resizing widgets, adding informative text, and making grid lines lighter to enhance data legibility.