Coursera Meta Database Engineering Capstone Project: A Comprehensive Booking System for Little Lemon Company

GitHub - drowaisqayyum/db-capstone-project

As part of the Coursera Meta Database Engineering Professional Certificate program, I recently completed the capstone project, which focused on creating a comprehensive booking system for the Little Lemon Company, a renowned restaurant chain. This project allowed me to apply the knowledge and skills acquired throughout the course, including data modeling, database design, query optimization, data visualization, and Python integration.

Project Overview

The primary objective of this capstone project was to develop a robust relational database in MySQL capable of storing and managing large amounts of data efficiently. The system would maintain information pertaining to various aspects of the business, including bookings, orders, delivery statuses, menus, customer details, and staff information.

The project was divided into several distinct tasks, each focusing on a specific aspect of the database development process:

1. Data Modeling
2. Adding Sales Report
3. Creating Optimized Queries
4. Setting up Tableau Workspace for Data Analysis
5. Python Client

Data Modeling

The data modeling phase was crucial in ensuring the database structure accurately represented the data requirements of Little Lemon. It involved the following steps:

Task 1: Creating an ER Diagram

In this task, I created a normalized Entity-Relationship (ER) diagram adhering to the 1st, 2nd, and 3rd Normal Forms (1NF, 2NF, and 3NF) using MySQL Workbench Model Editor. The diagram accurately represented the entities, attributes, primary keys, foreign keys, data types, and constraints.

Capstone Data Model

Task 2: Creating Database Schema

Using the forward engineering feature in MySQL Workbench, I generated the SQL schema for the Little Lemon Database based on the ER diagram.

Tables in Little Lemon Database

Task 3: Populating with Data

To ensure the database was populated with relevant information for testing and analysis purposes, I executed SQL queries to insert data into the newly created tables.

USE LittleLemonDB;

-- Populate Address table
INSERT INTO LittleLemonDB.Address (Country, City, PostalCode)
VALUES
    ('United States', 'New York', '10001'),
    ('United Kingdom', 'London', 'SW1A 1AA'),
    ('Canada', 'Toronto', 'M5V 2H1'),
    ('Australia', 'Sydney', '2000'),
    ('France', 'Paris', '75001');

-- Populate Customers table
INSERT INTO LittleLemonDB.Customers (Name, Email, PhoneNumber, AddressID)
VALUES
    ('John Doe', 'john.doe@email.com', '1234567890', 1),
    ('Jane Smith', 'jane.smith@email.com', '9876543210', 2),
    ('Michael Johnson', 'michael.johnson@email.com', '5555555555', 3),
    ('Emily Davis', 'emily.davis@email.com', '1112223333', 4),
    ('David Wilson', 'david.wilson@email.com', '4445556666', 5);

-- Populate Staff table
INSERT INTO LittleLemonDB.Staff (Role, Salary)
VALUES
    ('Manager', 600.00),
    ('Chef', 500.00),
    ('Waiter', 350.00),
    ('Bartender', 400.00),
    ('Hostess', 300.00);

-- Populate Bookings table
INSERT INTO LittleLemonDB.Bookings (BookingDate, TableNumber, CustomerID, StaffID)
VALUES
    ('2023-05-01 19:00:00', 1, 1, 5),
    ('2023-05-02 20:30:00', 2, 2, 3),
    ('2023-05-03 18:00:00', 3, 3, 5),
    ('2023-05-04 21:00:00', 4, 4, 3),
    ('2023-05-05 19:30:00', 5, 5, 5);

-- Populate Orders table
INSERT INTO LittleLemonDB.Orders (OrderDate, Quantity, TotalCost, CustomerID)
VALUES
    ('2023-05-01 19:30:00', 2, 50.00, 1),
    ('2023-05-02 21:00:00', 3, 75.00, 2),
    ('2023-05-03 18:30:00', 1, 25.00, 3),
    ('2023-05-04 21:30:00', 4, 100.00, 4),
    ('2023-05-05 20:00:00', 2, 60.00, 5);

-- Populate Delivery_status table
INSERT INTO LittleLemonDB.Delivery_status (DeliveryDate, Status, OrderID)
VALUES
    ('2023-05-01 20:00:00', 'Delivered', 1),
    ('2023-05-02 21:30:00', 'Delivered', 2),
    ('2023-05-03 19:00:00', 'Delivered', 3),
    ('2023-05-04 22:00:00', 'Delivered', 4),
    ('2023-05-05 20:30:00', 'Delivered', 5);

-- Populate Menu table
INSERT INTO LittleLemonDB.Menu (ItemName, Price, Category)
VALUES
    ('Margherita Pizza', 12.99, 'Main Course'),
    ('Caesar Salad', 8.99, 'Starter'),
    ('Spaghetti Bolognese', 14.99, 'Main Course'),
    ('Tiramisu', 6.99, 'Dessert'),
    ('Mojito', 9.99, 'Drink');

-- Populate OrdersMenu table
INSERT INTO LittleLemonDB.OrdersMenu (OrderID, MenuID, Quantity)
VALUES
    (1, 1, 1),
    (1, 2, 1),
    (2, 3, 2),
    (2, 4, 1),
    (3, 1, 1),
    (4, 2, 2),
    (4, 3, 1),
    (4, 4, 1),
    (5, 5, 2);

DESC Staff;

Adding Sales Report

This phase focused on creating views, implementing joins, and utilizing special operators to extract meaningful information from the database.

Task 1: Views

I created a virtual table named OrdersView that focused on the OrderID, Quantity, and `TotalCost` columns from the `Orders` table for all orders with a quantity greater than 2.

CREATE VIEW OrdersView AS
 SELECT O.OrderID, O.Quantity, O.TotalCost
 FROM LittleLemonDB.Orders AS O
 WHERE O.Quantity > 2;
SELECT * FROM OrdersView;

Task 2: Joins

This task involved extracting information from multiple tables using the appropriate JOIN clauses. Specifically, it retrieved customer details and order information for all customers with orders costing more than $50.

SELECT C.CustomerID, C.Name, O.OrderID, O.TotalCost
 FROM Orders AS O
 INNER JOIN Customers AS C ON O.CustomerID = C.CustomerID
 WHERE O.TotalCost > 50;

Task 3: Special Operator (ANY)

The final task in this section involved finding all menu items for which more than two orders had been placed. This was achieved by creating a subquery that listed the menu names from the `Menu` table for any order quantity with a sum greater than 2.

SELECT m.ItemName
 FROM Menu AS m
 WHERE m.MenuID = ANY (
 SELECT MenuID FROM OrdersMenu
 GROUP BY MenuID
 HAVING SUM(Quantity) > 2
);

Creating Optimized Queries

Little Lemon needed to query the data in their database efficiently. To achieve this, I created optimized queries using stored procedures and prepared statements.

Task 1: Stored Procedures

A stored procedure named `GetMaxQuantity()` was created to display the maximum ordered quantity in the `Orders` table. This procedure allows Little Lemon to reuse the logic without retyping the same code repeatedly.

CREATE PROCEDURE GetMaxQuantity()
BEGIN
 SELECT MAX(Quantity) AS 'Get Max Quantity'
 FROM Orders;
end;

CALL GetMaxQuantity();

Task 2: Prepared Statements

A prepared statement called `GetOrderDetail` was implemented to reduce query parsing time and enhance database security against SQL injections.

PREPARE GetOrderDetail FROM
 'SELECT OrderID, Quantity, TotalCost
 FROM Orders
 WHERE CustomerID = ?';

SET @id = 1;
EXECUTE GetOrderDetail USING @id;

Task 3: Stored Procedure for Order Cancellation

A stored procedure named `CancelOrder` was developed to delete an order record based on the provided order ID through user input.

CREATE PROCEDURE CancelOrder(IN order_id INT)
BEGIN
 DELETE FROM Orders
 WHERE CancelOrder.order_id = Orders.OrderID;
end;

CALL CancelOrder(3);

Setting up Tableau Workspace for Data Analysis

Little Lemon needed to analyze their sales data visually. To achieve this, I set up a Tableau workspace and created various data visualizations.

Task 1: Data Preparation

• Connected to Little Lemon’s data stored in an Excel sheet file.
• Filtered data in the data source page to select “United States” as the country.
• Created two new data fields, “First Name” and “Last Name,” by extracting values from the “Full Name” field.
• Calculated a new data field to store the profits for each sale or order.

Data Preparation

Task 2: Data Visualization

• Created a bar chart titled “Customers Sales” to display customers’ sales, filtered for sales with at least $70.

Customer Sales

• Developed a line chart named “Profit Chart” to show the sales trend from 2019 to 2022.

Profit Chart

• Generated a bubble chart titled “Sales Bubble Chart” to visualize sales for all customers.

Sales Bubble Chart

• Compared the sales of three different cuisines (Turkish, Italian, and Greek) sold at Little Lemon using a bar chart for the years 2020, 2021, and 2022.

Cuisine Sales

• Created an interactive dashboard combining the “Customer Sales” bar chart and the “Sales Bubble Chart” for comprehensive data analysis.

Dashboard

GitHub - drowaisqayyum/db-capstone-project

Python Client

A Python client was developed to interact with the MySQL database, allowing Little Lemon to query and manage their data programmatically.

Task 1: Establishing a Connection

• Installed the necessary Python libraries, including `mysql-connector`.
• Established a connection between Python and the MySQL database using the `connector` API.
• Created a cursor object to communicate with the entire MySQL database.
• Set the `LittleLemonDB` database for use.

# Importing MySQL Connector/Python 
import mysql.connector as connector
from mysql.connector import Error

# Establishing connection between Python and MySQL database via connector API
try:
 connection=connector.connect(user="root",password="")
except Error as er:
 print(er.msg)
print("Connection between MySQL and Python is established.\n")

# Creating a Cursor
cursor = connection.cursor()
print("Cursor is created to communicate with the MySQL using Python.")

# Setting the Database for use
cursor.execute("USE LittleLemonDB")
connection.database
print("The database LittleLemonDB is set for use.")

Task 2: Querying the Database

A query was executed to display all tables within the `LittleLemonDB` database.

show_tables_query = "SHOW TABLES;"

cursor.execute(show_tables_query)

results=cursor.fetchall()

columns=cursor.column_names
print(columns)

for result in results:
  print(result)

Task 3: Query with Table JOIN

In this task, a specific query was implemented to retrieve the full name, contact details, and order information for every customer who placed an order greater than $60. This information will be used for a promotional campaign by Little Lemon.

join_query = """ SELECT Customer.FullName,
Customer.ContactNumber,
Customer.Email,
Orders.TotalCost
FROM Customer
LEFT JOIN Orders
ON Customer.CustomerID = Orders.CustomerID
WHERE Orders.TotalCost > 60;"""

cursor.execute(join_query)

# Fetching the results in a variable called "results".
results = cursor.fetchall()

# Extracting the names of the columns.
columns=cursor.column_names

# Printing the names of the columns and the stored data using the "for" loop.
print(columns)
for result in results:
 print(result)

# Closing the Connection between Python and MySQL database.
if connection.is_connected():
 cursor.close()
 print("The cursor is closed.")
 connection.close()
 print("MySQL connection is closed.")
else:
 print("Connection is already closed")

Conclusion

This capstone project successfully delivered a comprehensive booking system for Little Lemon Company, complete with a robust relational database, optimized queries, data analysis capabilities, and a Python client for seamless data management. The system is designed to streamline operations, enhance customer experience, and enable data-driven decision-making for the restaurant chain.

By completing this project, I gained practical experience in data modeling, database design, query optimization, data visualization, and Python integration. The skills and knowledge acquired throughout this capstone project have prepared me for real-world database engineering challenges and have solidified my understanding of the concepts learned in the Coursera Meta Database Engineering Professional Certificate program.

References

1. Github Repository — https://github.com/drowaisqayyum/db-capstone-project
2. Coursera Meta Database Engineering — https://www.coursera.org/professional-certificates/meta-database-engineer