The Meta Database Engineer Capstone Project

My Approach To Tackling The Tasks Involved

Photo by Steve Johnson on Unsplash

Little Lemon is a family-owned Mediterranean restaurant, primarily with the aim of satisfying its customers with good food.

Project’s Objectives

• Set up the database
• Setup MYSQL server
• Create and implement an ER diagram
• Populate the entities
• Creation of a virtual table called OrdersView
• Create a prepared statement, GetMaxQuantity() to check the maximum quantity to avoid repeated usage of codes
• Create a prepared statement, GetOrderDetail() to help reduce the parsing time of queries.
• A stored procedure, CancelOrder() to delete an order record based on the user input of the order ID
• To create a stored procedure, ()CheckBooking to check whether a table in the restaurant was already booked
• Create a stored procedure, AddValidBooking() to verify a booking and decline any reservations for tables that were already booked under another name
• Create a procedure UpdateBooking() to update existing bookings in the Booking table
• Create a new procedure, CancelBooking() to cancel or remove a booking
• Creating a Python environment to connect with the database and interact with the data it holds
• Create appropriate visualizations
• Creation of a virtual table called OrdersView

In order to easily manage and find the necessary data, there is a need to build a relational database system in MySQL in which large amounts of data can be stored. The database system maintains information about the following aspects: Bookings, Orders, Order delivery status, Menu, Customer details, and Staff information. The model is given below:

DDL FOR THE DATABASE DESIGN:

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema LittleLemonDB
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema LittleLemonDB
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `LittleLemonDB` DEFAULT CHARACTER SET utf8 ;
USE `LittleLemonDB` ;

-- -----------------------------------------------------
-- Table `LittleLemonDB`.`Bookings`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `LittleLemonDB`.`Bookings` (
  `BookingID` INT NOT NULL,
  `Date` DATE NOT NULL,
  `TableNumber` INT NOT NULL,
  PRIMARY KEY (`BookingID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `LittleLemonDB`.`CustomerDetails`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `LittleLemonDB`.`CustomerDetails` (
  `CustomerID` INT NOT NULL,
  `Contacts` VARCHAR(255) NOT NULL,
  `Names` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`CustomerID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `LittleLemonDB`.`Order_Delivery_Status`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `LittleLemonDB`.`Order_Delivery_Status` (
  `StatusID` INT NOT NULL,
  `DeliveryDate` DATE NOT NULL,
  `DeliveryStatus` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`StatusID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `LittleLemonDB`.`Orders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `LittleLemonDB`.`Orders` (
  `OrderID` INT NOT NULL,
  `OrderDate` INT NOT NULL,
  `Quantity` INT NOT NULL,
  `TotalCost` INT NOT NULL,
  `StatusID` INT NOT NULL,
  `CustomerID` INT NOT NULL,
  PRIMARY KEY (`OrderID`),
  INDEX `Customer_fk_idx` (`CustomerID` ASC) VISIBLE,
  INDEX `Order_fk_idx` (`StatusID` ASC) VISIBLE,
  CONSTRAINT `Customer_fk`
    FOREIGN KEY (`CustomerID`)
    REFERENCES `LittleLemonDB`.`CustomerDetails` (`CustomerID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Order_fk`
    FOREIGN KEY (`StatusID`)
    REFERENCES `LittleLemonDB`.`Order_Delivery_Status` (`StatusID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `LittleLemonDB`.`Staff_Information`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `LittleLemonDB`.`Staff_Information` (
  `StaffID` INT NOT NULL,
  `Role` VARCHAR(255) NOT NULL,
  `Salary` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`StaffID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `LittleLemonDB`.`Menu`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `LittleLemonDB`.`Menu` (
  `MenuID` INT NOT NULL,
  `Starters` VARCHAR(255) NOT NULL,
  `Cuisines` VARCHAR(255) NOT NULL,
  `Courses` VARCHAR(255) NOT NULL,
  `Drinks` VARCHAR(255) NOT NULL,
  `Desserts` VARCHAR(255) NOT NULL,
  `OrderID` INT NOT NULL,
  `BookingID` INT NOT NULL,
  `StaffID` INT NOT NULL,
  PRIMARY KEY (`MenuID`),
  INDEX `Menu_fk_idx` (`OrderID` ASC) VISIBLE,
  INDEX `Bookings_fk_idx` (`BookingID` ASC) VISIBLE,
  INDEX `Staff_fk_idx` (`StaffID` ASC) VISIBLE,
  CONSTRAINT `Menu_fk`
    FOREIGN KEY (`OrderID`)
    REFERENCES `LittleLemonDB`.`Orders` (`OrderID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Bookings_fk`
    FOREIGN KEY (`BookingID`)
    REFERENCES `LittleLemonDB`.`Bookings` (`BookingID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Staff_fk`
    FOREIGN KEY (`StaffID`)
    REFERENCES `LittleLemonDB`.`Staff_Information` (`StaffID`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Inserting data into the various entities:

-- Insert data into CustomerDetails
INSERT INTO `LittleLemonDB`.`CustomerDetails` (`ID`, `Names`, `Contacts`)
VALUES
(1, 'John Doe', 'john.doe@example.com'),
(2, 'Jane Doe', 'jane.doe@example.com'),
(3, 'Alice', 'alice@example.com'),
(4, 'Bob', 'bob@example.com'),
(5, 'Charlie', 'charlie@example.com'),
(6, 'David', 'david@example.com'),
(7, 'Emily', 'emily@example.com'),
(8, 'Frank', 'frank@example.com'),
(9, 'Grace', 'grace@example.com'),
(10, 'Hannah', 'hannah@example.com');

-- Insert data into StaffInformation
INSERT INTO `LittleLemonDB`.`StaffInformation` (`StaffID`, `Name`, `Role`, `Salary`)
VALUES
(1,'Sarah', 'Manager', 55000),
(2,'Tom', 'Waiter', 30000),
(3,'Linda', 'Chef', 40000),
(4,'Robert', 'Cashier', 31000),
(5,'Daniel', 'Waiter', 32000),
(6,'Susan', 'Hostess', 28000),
(7,'Chris', 'Manager', 60000),
(8,'Jessica', 'Chef', 38000),
(9,'Brian', 'Waiter', 29000),
(10,'Kim', 'Hostess', 27000);

-- Insert data into Menus
INSERT INTO `LittleLemonDB`.`Menus` (MenuID, Starters, Cuisines, CourseName, Drinks, Desserts, OrderID, BookingID, StaffID)
VALUES
(1, 'Garlic Butter Shrimp', 'Italian', 'Appetizer', 'White Wine', 'Tiramisu', 1, 1, 1),
(2, 'Spring Rolls', 'Chinese', 'Appetizer', 'Green Tea', 'Fruit Tart', 2, 2, 2),
(3, 'Caprese Salad', 'Italian', 'Salad', 'Iced Tea', 'Cheesecake', 3, 3, 3),
(4, 'Chicken Wings', 'American', 'Appetizer', 'Soda', 'Chocolate Cake', 4, 4, 4),
(5, 'Tomato Soup', 'French', 'Soup', 'Red Wine', 'Creme Brulee', 5, 5, 5),
(6, 'Sushi Rolls', 'Japanese', 'Appetizer', 'Sake', 'Mochi Ice Cream', 6, 6, 6),
(7, 'Hummus with Pita', 'Lebanese', 'Appetizer', 'Mint Tea', 'Baklava', 7, 7, 7),
(8, 'Tandoori Chicken', 'Indian', 'Main Course', 'Lassi', 'Gulab Jamun', 8, 8, 8),
(9, 'Greek Salad', 'Greek', 'Salad', 'Ouzo', 'Baklava', 9, 9, 9),
(10, 'Steak au Poivre', 'French', 'Main Course', 'Red Wine', 'Creme Brulee', 10, 10, 10);

-- Insert data into Bookings
INSERT INTO `LittleLemonDB`.`Bookings` (`BookingID`, `Date`, `TableNumber`)
VALUES
(1, '2023-09-01 12:00:00', 10),
(2, '2023-09-01 12:30:00', 12),
(3, '2023-09-02 13:00:00', 14),
(4, '2023-09-02 14:00:00', 16),
(5, '2023-09-03 15:00:00', 18),
(6, '2023-09-03 16:00:00', 20),
(7, '2023-09-04 17:00:00', 22),
(8, '2023-09-04 18:00:00', 24),
(9, '2023-09-05 19:00:00', 26),
(10, '2023-09-05 20:00:00', 28);


-- Insert data into Orders
INSERT INTO `LittleLemonDB`.`Orders` (OrderID, OrderDate, Quantity, TotalCost, StatusID, CustomerID)
VALUES
(1, '2023-09-01 12:00:00', 3, 499, 1, 5),
(2, '2023-09-01 12:30:00', 2, 295, 2, 8),
(3, '2023-09-02 13:00:00', 4, 599, 3, 4),
(4, '2023-09-02 14:00:00', 1, 199, 4, 7),
(5, '2023-09-03 15:00:00', 5, 795, 5, 1),
(6, '2023-09-03 16:00:00', 2, 295, 6, 9),
(7, '2023-09-04 17:00:00', 3, 499, 7, 2),
(8, '2023-09-04 18:00:00', 4, 599, 8, 6),
(9, '2023-09-05 20:00:00', 1, 199, 9, 3),
(10, '2023-09-05 20:00:00', 5, 795, 10, 10);

-- Insert data into OrderDeliveryStatuses
INSERT INTO `LittleLemonDB`.`OrderDeliveryStatuses` (`StatusID`, `DeliveryDate`, `DeliveryStatus`)
VALUES
(1, '2023-09-01 12:15:00', 'Delivered'),
(2, '2023-09-01 12:45:00', 'Preparing'),
(3, '2023-09-02 13:15:00', 'Preparing'),
(4, '2023-09-02 14:15:00', 'Out for delivery'),
(5, '2023-09-03 15:15:00', 'Out for delivery'),
(6, '2023-09-03 16:15:00', 'Delivered'),
(7, '2023-09-04 17:15:00', 'Preparing'),
(8, '2023-09-04 18:15:00', 'Delivered'),
(9, '2023-09-05 19:15:00', 'Delivered'),
(10, '2023-09-05 20:15:00', 'Delivered');

Creation of a virtual table called OrdersView that focuses on OrderID, Quantity, and Cost with orders quantity of more than 2.

CREATE VIEW OrdersView AS
SELECT OrderID, Quantity, TotalCost
FROM orders
WHERE Quantity > 2;

Little Lemon needed information from four tables on all customers with orders that cost more than $150. In order to do this, I extracted the required information from each of the following tables by using the relevant JOIN clause

SELECT customers.CustomerID, customers.Names, orders.OrderID, orders.TotalCost, menus.MenuName, menus.CourseName
FROM customersDetails customers INNER JOIN orders orders
ON customers.CustomerID = orders.customerID
INNER JOIN menus ON orders.MenuID = menus.MenuID 
WHERE Cost > 150  ORDER BY Cost;

Little Lemon needed to find all menu items for which more than 2 orders had been placed. In order to do this, the task was carried out by creating a subquery that lists the menu names from the menus table for any order quantity with more than 2.

SELECT Menus.MenuName
FROM Menus
WHERE Menus.MenuID = ANY (
    SELECT Orders.MenuID
    FROM Orders
    GROUP BY Orders.MenuID
    HAVING COUNT(*) > 2
);

Little Lemon needed to create a prepared statement called GetMaxQuantity. This procedure will allow Little Lemon to reuse the logic implemented in the procedure easily without retyping the same code again and again to check the maximum quantity.

CREATE PROCEDURE 'GetMaxQuantity'()
SELECT max(quantity) AS "Max Quantity in Order" FROM orders

Little Lemon needed to create a prepared statement called GetOrderDetail to help reduce the parsing time of queries.

-- Create the prepared statement
PREPARE GetOrderDetail FROM 
'SELECT OrderID, Quantity, TotalCost FROM Orders WHERE CustomerID = ?';

Little Lemon needed a stored procedure called CancelOrder to delete an order record based on the user input of the order ID. Creating this procedure will allow Little Lemon to cancel any order by specifying the order ID value in the procedure parameter without typing the entire SQL delete statement.

DELIMITER //

CREATE PROCEDURE CancelOrder(IN orderId INT)
BEGIN
    DELETE FROM Orders WHERE OrderID = orderId;
END //

DELIMITER ;

In order to minimize the effort involved in repeatedly coding the same SQL statements, Little Lemon needed to create a stored procedure called CheckBooking to check whether a table in the restaurant was already booked. This was carried out with the following query:

CREATE PROCEDURE 'CheckBooking'(booking_date DATE, table_number INT)
BEGIN
DECLARE bookedTable INT DEFAULT 0;
 SELECT COUNT(bookedTable)
    INTO bookedTable
    FROM Bookings WHERE BookingDate = booking_date and TableNumber = table_number;
    IF bookedTable > 0 THEN
      SELECT CONCAT( "Table", table_number, "is already booked") AS "Booking status";
      ELSE 
      SELECT CONCAT( "Table", table_number, "is not booked") AS "Booking status";
    END IF;
END

Little Lemon needed to verify a booking and decline any reservations for tables that were already booked under another name. This requires a stored procedure and a transaction and was carried out with the following query:

DELIMITER //

CREATE PROCEDURE AddValidBooking(
    IN bookingDate DATE,
    IN tableNumber INT,
    IN customerName VARCHAR(255)
)
BEGIN
    DECLARE @existingBookingCount INT;

    -- Check if the table is already booked
    SELECT COUNT(*) INTO @existingBookingCount
    FROM Bookings
    WHERE BookingDate = bookingDate AND TableNumber = tableNumber;

      -- Start a transaction
    START TRANSACTION;

    IF @existingBookingCount > 0 THEN
        -- The table is already booked, so rollback the transaction
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Table is already booked for this date.';
    ELSE
        -- The table is available, so insert the new booking
        INSERT INTO Bookings (BookingDate, TableNumber, CustomerName)
        VALUES (bookingDate, tableNumber, customerName);

        -- Commit the transaction
        COMMIT;
    END IF;
END //

DELIMITER ;

In order to update existing bookings in the booking table, Little Lemon needed to create a new procedure called UpdateBooking that they could use. This was carried out with the following query:

CREATE DEFINER='admin1'@'%' PROCEDURE 'UpdateBooking'(booking_id INT, booking_date DATE)
BEGIN
UPDATE bookings SET BookingDate = booking_date WHERE BookingID = booking_id; 
SELECT CONCAT("Booking", booking_id, "updated") AS "Confirmation";
END

Little Lemon needed to create a new procedure called CancelBooking that they could use to cancel or remove a booking. This was carried this out with the following query:

DELIMITER //
CREATE PROCEDURE `LittleLemonDB`.`CancelBooking`(IN booking_id_to_cancel INT)
BEGIN
    -- Delete the booking record
    DELETE FROM `LittleLemonDB`.`Bookings`
    WHERE `BookingID` = booking_id_to_cancel;

    SELECT CONCAT('Booking ', booking_id_to_cancel, ' cancelled') AS 'Confirmation';
END;
//
DELIMITER ;

VISUALIZATIONS

Customers' sales for data based on sales with at least $70

Sales trend from 2019 to 2022.

Names of all customers and their respective sales

Sales of the Turkish, Italian, and Greek cuisines.

An interactive dashboard combining the Bar chart called Customers Sales and the Sales Bubble Chart

Creating a Python environment to connect with the database and interact with the data it holds

import mysql.connector as connector
connection = connector.connect(user="root", password="Atdimpley$10")
cursor = connection.cursor()
cursor.execute("USE LittleLemonDB")

creating_joins =
"""
SELECT customers.FullName, 
customers.contactDetails, 
orders.TotalCost
FROM customers
INNER JOIN orders ON customers.customerID = orders.customerID
WHERE orders.TotalCost > 60;

"""

cursor.execute(creating_joins)
results =cursor.fetchall()
print(cursor.column_names)
print(results)

Thank you for reading! If you found this interesting, please consider following and subscribing to my latest articles. Don’t forget to reach out to me on LinkedIn and follow me on Twitter