aim of this project is to work with purchased data, build a derived table, and calculate KPIs
based on the datasets provided
Create and build an database, to capture and store all important data.
Create a table to be able to know when its time to order new stock.
Main areas of focus:
-orders
-stock control
-Staff
Stock control requirements
client wants to be able to know when its time to order new stock
To do this we need more information about:
- what ingredients go into each pizza
- their quantity based on the size of pizza
- the existing stock level
We will assume the lead time for delivery by suppliers is the same for all ingredients
Order Data Required:
• Item name
• Item price
• Quantity
• Customer name
• Delivery address
Inventory management:
- total quantity by ingredient
- total costs of ingredients
- calculated cost if pizza
- percentage stock remaining by ingredients
- Install MySQLWorkbench or Xampp
- Any Editor (Preferably VS Code or Sublime Text)
- XAMPP or MySQL Workbench
- MySQL
- Tableau public
Row ID int pk
Order ID varchar(10)
Created at datetime
Quantity int
Delivery boolean
Cust_id int fk
Add_id int fk
Item-id int fk
Cust_id int pk
Cust_ firstname varchar(50)
Cust_lastname varchar(50)
Add_id int pk
Delivery address 1 varchar(200)
Delivery address 2 varchar(200) NULL
Delivery city varchar(50)
Delivery zip code varchar(20)
Item_id varchar(10) pk
Item name varchar(50)
Item category varchar(50)
Item size varchar(20)
Item price decimal(5,2)
Ing_id varchar(10) pk
Ing_name varchar(20)
Ing_weight int
Ing_meas varchar(20)
Ing_price decimal (5,2)
Row_id int pk
Recipe_id varchar(20) fk
Ing_id varchar(10) fk
Quantity int
Inv_id int pk
Item_id varchar(10) fk
Quantity
Row_id intpk
Rota_id int
Date date fk
Shift_id int fk
Staff_id int fk
Staff_id varchar(20) pk
First_name varchar(20)
Last_name varchar(20)
Position varchar(20)
Hourly_rate decimal (5,2)
Shift_id pk
Day_of_week varchar(10)
Start_time time
End_time time
