The purpose of this project is to help Little Lemon manage its database by adding sales reports and creating a table booking system using mySQL. Little Lemon needs to build a robust relational database system in MySQL in which they can store large amounts of data. They then need to easily manage and find this data as required. This database system should maintain information about the following aspects of the business:
- Bookings - To store information about booked tables in the restaurant including booking id, date, and table number.
- Orders - To store information about booked tables in the restaurant including booking id, date, and table number.
- Order delivery status - To store information about the delivery status of each order such as delivery date and status.
- Menu - To store information about cuisines, starters, courses, drinks, and desserts.
- Customer details - To store information about the customer's names and contact details.
- Staff information - Including role and salary.
A normalized ER diagram (that adheres to 1NF, 2NF, and 3NF) with relevant relationships to meet the data requirements of Little Lemon developed using MySQL Workbench.
To set up the database, do the following:
-
Install MySQL: Download and install MySQL on your machine if you haven't done so.
-
Download SQL File: Obtain the LittleLemonDB.sql file from this repository.
-
Import and Execute in MySQL Workbench:
- Open MySQL Workbench.
- Navigate to
Server
>Data Import
. - Choose
Import from Self-Contained File
and load theLittleLemonDB.sql
file. - Click
Start Import
to both import and execute the SQL commands from the file.
Your database should now be set up and populated with tables and stored procedures.
This stored procedure retrieves the maximum quantity of a specific item that has been ordered. It's useful for inventory management.
CREATE PROCEDURE GetMaxQuantity()
BEGIN
DECLARE maxQty INT;
SELECT MAX(Quantity) INTO maxQty FROM `LittleLemonDB`.`Orders`;
SELECT maxQty AS 'Maximum Ordered Quantity';
END;
CALL GetMaxQuantity()
The CheckBooking stored procedure validates whether a table is already booked on a specified date. It will output a status message indicating whether the table is available or already booked.
CREATE PROCEDURE `LittleLemonDB`.`CheckBooking`(IN booking_date DATE, IN table_number INT)
BEGIN
DECLARE table_status VARCHAR(50);
SELECT COUNT(*) INTO @table_count
FROM `LittleLemonDB`.`Bookings`
WHERE `Date` = booking_date AND `TableNumber` = table_number;
IF (@table_count > 0) THEN
SET table_status = 'Table is already booked.';
ELSE
SET table_status = 'Table is available.';
END IF;
SELECT table_status AS 'Table Status';
END;
CALL CheckBooking('2022-11-12', 3);
This stored procedure updates the booking details in the database. It takes the booking ID and new booking date as parameters, making sure the changes are reflected in the system.
CREATE PROCEDURE `LittleLemonDB`.`UpdateBooking`(
IN booking_id_to_update INT,
IN new_booking_date DATE)
BEGIN
UPDATE `LittleLemonDB`.`Bookings`
SET `Date` = new_booking_date
WHERE `BookingID` = booking_id_to_update;
SELECT CONCAT('Booking ', booking_id_to_update, ' updated') AS 'Confirmation';
END;
CALL `LittleLemonDB`.`UpdateBooking`(9, '2022-11-15');
This procedure adds a new booking to the system. It accepts multiple parameters like booking ID, customer ID, booking date, and table number to complete the process.
CREATE PROCEDURE `LittleLemonDB`.`AddBooking`(
IN new_booking_id INT,
IN new_customer_id INT,
IN new_booking_date DATE,
IN new_table_number INT,
IN new_staff_id INT)
BEGIN
INSERT INTO `LittleLemonDB`.`Bookings`(
`BookingID`,
`CustomerID`,
`Date`,
`TableNumber`,
`StaffID`)
VALUES(
new_booking_id,
new_customer_id,
new_booking_date,
new_table_number,
new_staff_id
);
SELECT 'New booking added' AS 'Confirmation';
END;
CALL `LittleLemonDB`.`AddBooking`(17, 1, '2022-10-10', 5, 2);
This stored procedure deletes a specific booking from the database, allowing for better management and freeing up resources.
CREATE PROCEDURE `LittleLemonDB`.`CancelBooking`(IN booking_id_to_cancel INT)
BEGIN
DELETE FROM `LittleLemonDB`.`Bookings`
WHERE `BookingID` = booking_id_to_cancel;
SELECT CONCAT('Booking ', booking_id_to_cancel, ' cancelled') AS 'Confirmation';
END;
CALL `LittleLemonDB`.`CancelBooking`(9);
The AddValidBooking stored procedure aims to securely add a new table booking record. It starts a transaction and attempts to insert a new booking record, checking the table's availability.
CREATE PROCEDURE `LittleLemonDB`.`AddValidBooking`(IN new_booking_date DATE, IN new_table_number INT, IN new_customer_id INT, IN new_staff_id INT)
BEGIN
DECLARE table_status INT;
START TRANSACTION;
SELECT COUNT(*) INTO table_status
FROM `LittleLemonDB`.`Bookings`
WHERE `Date` = new_booking_date AND `TableNumber` = new_table_number;
IF (table_status > 0) THEN
ROLLBACK;
SELECT 'Booking could not be completed. Table is already booked on the specified date.' AS 'Status';
ELSE
INSERT INTO `LittleLemonDB`.`Bookings`(`Date`, `TableNumber`, `CustomerID`, `StaffID`)
VALUES(new_booking_date, new_table_number, new_customer_id, new_staff_id);
COMMIT;
SELECT 'Booking completed successfully.' AS 'Status';
END IF;
END;
CALL AddValidBooking('2022-10-10', 5, 1, 1);
The CancelOrder stored procedure cancels or removes a specific order by its Order ID. It executes a DELETE statement to remove the order record from the Orders table.
CREATE PROCEDURE CancelOrder(IN orderIDToDelete INT)
BEGIN
DECLARE orderExistence INT;
SELECT COUNT(*) INTO orderExistence FROM `LittleLemonDB`.`Orders` WHERE OrderID = orderIDToDelete;
IF orderExistence > 0 THEN
DELETE FROM `LittleLemonDB`.`OrderDeliveryStatuses` WHERE OrderID = orderIDToDelete;
DELETE FROM `LittleLemonDB`.`Orders` WHERE OrderID = orderIDToDelete;
SELECT CONCAT('Order ', orderIDToDelete, ' is cancelled') AS 'Confirmation';
ELSE
SELECT CONCAT('Order ', orderIDToDelete, ' does not exist') AS 'Confirmation';
END IF;
END;
CALL CancelOrder(5);
- Create an entity relationship diagram using MySQL Workbench
- Use MySQL Workbench to forward engineer the database and tables and populate data
- Perform CRUD operations with SQL and with a Python client
- Use the Python connector class to access the database
- Create a dashboard using Tableau software to analyse business KPIs
- Tableau software
- MySQL / MySQL Workbench
- Python / Pandas / MySQL Connector
- Jupyter Notebook