Little Lemon Management System

Database Project Description

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.

Entity-Relationship Diagram

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.

Installation and Setup

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 the LittleLemonDB.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.

Stored Procedures

GetMaxQuantity()

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()

CheckBooking()

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);

UpdateBooking()

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');

AddBooking()

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);

CancelBooking()

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);

AddValidBooking()

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);

CancelOrder()

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);

Skills acquired

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

Languages & software

Tableau software
MySQL / MySQL Workbench
Python / Pandas / MySQL Connector
Jupyter Notebook

daz005 / little-lemon-database Goto Github PK

little-lemon-database's Introduction