The client is establishing a new pizzeria that centres around delivery and pick up, without dine-in services.
The business task is to develop a relational database to capture and store all relevant business information and data efficiently, and to provide the client with a systematic means to monitor and assess business performance through interactive dashboards. The client would like three primary areas of focus: orders, stock inventory and staff management.
In each dashboard, the following questions are addressed:
Dashboard 1: Orders
- What is the total number of orders?
- What is the total sales?
- What is the total number of items?
- What is the average order value?
- What proportion of total sales does each category represent?
- What are the top selling items?
- What are the total orders by hour?
- What are the total sales by hour?
- What proportion of orders are via delivery versus pick up?
Dashboard 2: Inventory
- What is the total quantity for each ingredient?
- What is the total cost for each ingredient?
- What is the remaining inventory percentage for each ingredient?
- What is the list of ingredients to reorder based on the remaining inventory?
- What is the total ingredient cost?
- What is the cost to make each pizza?
Dashboard 3: Staff
- What is the total staff cost?
- What is the total number of hours worked?
- How many hours does each staff member work during each shift?
- What is the hourly rate for each staff member?
- What is the cost for each staff member?
The dataset is proprietary and not accessible to the public.
There are a total of 10 CSV files: address, customers, ingredient, inventory, item, orders, recipe, rota, shift and staff.