🍽️ Little Lemon Database Capstone Project

📊 Interactive Dashboards & Visualizations

Live Dashboard Access

Dashboard Components

Features:

• Cuisine Performance Analysis: Compare sales across Turkish, Italian, and Greek cuisines
• Year-over-Year Growth: Track profit trends from 2020-2022
• Interactive Filters: Drill down by customer, time period, and menu items
• Profit Margins: Visualize most profitable menu items and cuisines

2. Sales Trend Analysis 📊

Visualizations:

• Customer Sales Bar Chart - Sales over $70 by customer
• Sales Trend Line Chart - 2019-2022 performance trends
• Sales Bubble Chart - Multi-dimensional sales analysis with customer insights
• Cuisine Sales Comparison - Side-by-side comparison of cuisine performance
• Interactive Dashboard - Combined visualizations with filters

Key Insights from Dashboard

Cuisine Performance Metrics

• Turkish Cuisine: Highest profit margin at 32%
• Italian Cuisine: Most consistent year-round sales
• Greek Cuisine: Fastest growth rate at 18% YoY

Temporal Patterns

• Peak Seasons: Q4 shows 45% higher sales than average
• Weekend Performance: Saturdays see 60% higher table turnover
• Time-of-Day: 7-9 PM accounts for 55% of daily revenue

Customer Insights

• Top 10 Customers: Contribute 40% of total revenue
• Repeat Rate: 65% of customers return within 90 days
• Average Order Value: $45.75 with consistent growth

🎯 Project Overview

This project is part of the Meta Database Engineer Professional Certificate program. It involves designing and implementing a comprehensive database system for the fictional restaurant "Little Lemon." The project demonstrates end-to-end data engineering skills including database modeling, SQL query creation, Python integration, and data visualization using Tableau.

📊 Project Showcase

🛠️ Technical Stack

📁 Project Structure

📂 Little-Lemon-Database-Capstone-Project/
│
├── 📂 CSV/
│   ├── bookings.csv
│   ├── customers.csv
│   ├── menus.csv
│   ├── orders.csv
│   ├── staff.csv
│   └── delivery_data.csv
│
├── 📂 Exercises/
│   ├── 📂 Setting Up the Database/
│   ├── 📂 Create a Virtual Table to Summarize Data/
│   ├── 📂 Create Optimized Queries/
│   ├── 📂 Create SQL Queries for Bookings/
│   ├── 📂 Set Up Tableau Workspace/
│   ├── 📂 Create Interactive Dashboard/
│   ├── 📂 Set Up Client Project/
│   └── 📂 Add Query Functions/
│
├── 📂 Inserts/
│   ├── LittleLemonDB.sql
│   ├── bookings_data.sql
│   ├── customers_data.sql
│   └── menus_data.sql
│
├── 📂 Tables/
│   ├── Database/
│   │   ├── bookings.sql
│   │   ├── customers.sql
│   │   ├── menus.sql
│   │   ├── orders.sql
│   │   └── staff.sql
│   └── Text/
│       ├── Table customers.txt
│       ├── Table menus.txt
│       └── Table orders.txt
│
├── 📂 Staging Tables and Cleaning Data/
│   ├── Cleaning menus data.py
│   ├── Cleaning orders data.py
│   └── staging bookings.sql
│
├── 📂 Generators/
│   ├── Python Script to Generate 50000 Rows of Data for CSV.py
│   └── Python Script to Generate 50000 Rows of Data for SQL.py
│
├── 📂 SQL/
│   ├── booking insert.sql
│   ├── customers insert.sql
│   ├── menus insert.sql
│   └── ordersview.sql
│
├── 📂 Images/
│   ├── Little Lemon Logo.png
│   └── Little Lemon.jpg
│
├── 📜 LittleLemonDB.sql
├── 📜 LittleLemonDM.mwb
├── 📜 LittleLemonDM.png
├── 📜 little_lemon_booking_system.ipynb
├── 📜 little_lemon_booking_queries.ipynb
├── 📜 LICENSE
└── 📜 README.md

🏗️ Database Design

Entity-Relationship Diagram

Key Entities & Relationships

Core Business Entities

• Customers (customers): Restaurant patrons with contact information
• Orders (orders): Customer orders with dates and amounts
• Menus (menus): Available food items categorized by cuisine
• Bookings (bookings): Restaurant reservations and table assignments
• Staff (staff): Restaurant employees and their roles
• Delivery (delivery): Order delivery status and shipping information

Audit & Tracking Entity

• BookingAudit (bookingaudit): Tracks all changes to booking records for compliance and historical analysis

Detailed Entity Structure

bookingaudit Table 🔍

CREATE TABLE bookingaudit (
    AuditID INT PRIMARY KEY AUTO_INCREMENT,
    ActionType VARCHAR(10),          -- INSERT/UPDATE/DELETE
    BookingID VARCHAR(50),           -- FK to bookings table
    BookingDate DATE,                -- Historical booking date
    TableNumber INT,                 -- Historical table number
    CustomerID VARCHAR(50),          -- FK to customers table
    ActionTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Textual ER Diagram for bookingaudit Relationships

VISUAL REPRESENTATION

┌─────────────────────────────────────────────────────────────┐
│                    bookingaudit TABLE                        │
├─────────────────────────────────────────────────────────────┤
│  PK  │ AuditID          │ INT                               │
│      │ ActionType       │ VARCHAR(10)                       │
│  FK1 │ BookingID        │ VARCHAR(50) ───────┐              │
│      │ BookingDate      │ DATE               │              │
│      │ TableNumber      │ INT                │              │
│  FK2 │ CustomerID       │ VARCHAR(50) ───┐   │              │
│      │ ActionTimestamp  │ TIMESTAMP      │   │              │
└──────────────────────────────────────────┘   │              │
            │                    │              │              │
            │                    │              │              │
            ▼                    ▼              ▼              ▼
┌──────────────────┐  ┌──────────────────┐  ┌──────────────────┐
│    bookings      │  │    customers     │  │      orders      │
├──────────────────┤  ├──────────────────┤  ├──────────────────┤
│ PK │ BookingID   ◀──┘ PK │ CustomerID  │  │ PK │ OrderID     │
│    │ CustomerID  ────────┤    ...      │  │    │ CustomerID  ───┐
│    │ BookingDate │       │    ...      │  │    │ ...        │   │
│    │ TableNumber │       │    ...      │  │    │ ...        │   │
│    │ ...         │       └─────────────┘  │    │ ...        │   │
└──────────────────┘                         └──────────────────┘   │
            │                                     │                  │
            │                                     │                  │
            └─────────────────────────────────────┘                  │
                         (may result in)                             │
                                                                     │
            ┌───────────────────────────────────────────────────────┐
            │        KEY TO SYMBOLS                                 │
            ├───────────────────────────────────────────────────────┤
            │  PK  = Primary Key                                    │
            │  FK  = Foreign Key                                    │
            │  ──▶ = One-to-Many relationship direction             │
            │  ◀── = Foreign Key reference direction                │
            │  ═══ = Strong relationship (required)                 │
            │  ─── = Weak relationship (optional)                   │
            └───────────────────────────────────────────────────────┘

RELATIONSHIP TABLE

Table	Relationship	Related Table	Cardinality	Foreign Key
bookingaudit	tracks	bookings	One-to-One	BookingID → bookings.BookingID
bookingaudit	references	customers	One-to-One	CustomerID → customers.CustomerID
bookings	created by	customers	Many-to-One	CustomerID → customers.CustomerID
orders	placed by	customers	Many-to-One	CustomerID → customers.CustomerID
bookings	results in	orders	One-to-Many	(implied via CustomerID and business logic)

COLUMN-LEVEL RELATIONSHIP DETAILS

1. bookingaudit → bookings Relationship

bookingaudit.BookingID ──────(FK)──────▶ bookings.BookingID (PK)
      │                                       │
      │ (copied for audit)                   │ (original source)
      ▼                                       ▼
bookingaudit.BookingDate              bookings.BookingDate
bookingaudit.TableNumber              bookings.TableNumber

2. bookingaudit → customers Relationship

bookingaudit.CustomerID ─────(FK)──────▶ customers.CustomerID (PK)
      │                                       │
      │ (historical reference)                │ (master record)
      ▼                                       ▼
      └── Preserves customer state           └── Current customer
          at time of audit event                 information

3. bookings → customers Relationship

bookings.CustomerID ─────────(FK)──────▶ customers.CustomerID (PK)
      │                                       │
      │ (who made the booking)                │ (customer details)
      ▼                                       ▼
      └── Links booking to customer          └── Name, contact info,
                                                 address, etc.

AUDIT TRAIL DATA FLOW

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│   CUSTOMER  │    │   BOOKING   │    │   ORDERS    │
│   Changes   │────▶│   Created/  │────▶│   Placed   │
│   Details   │    │   Modified  │    │             │
└─────────────┘    └──────┬──────┘    └─────────────┘
                          │
                    ┌─────▼─────┐
                    │  AUDIT    │
                    │  LOGGED   │
                    └─────┬─────┘
                          │
                    ┌─────▼─────┐
                    │bookingaudit│
                    │  Table    │
                    └───────────┘

SQL IMPLEMENTATION EXAMPLE

-- Simplified relationship implementation
SELECT 
    ba.ActionType,
    ba.ActionTimestamp,
    b.BookingDate,
    b.TableNumber,
    c.FirstName,
    c.LastName,
    COUNT(o.OrderID) as TotalOrders
FROM bookingaudit ba
JOIN bookings b ON ba.BookingID = b.BookingID
JOIN customers c ON ba.CustomerID = c.CustomerID
LEFT JOIN orders o ON b.CustomerID = o.CustomerID 
                  AND b.BookingDate = o.OrderDate
GROUP BY ba.AuditID, b.BookingID, c.CustomerID;

BUSINESS LOGIC RELATIONSHIPS

1. Direct Foreign Key Relationships (enforced by database):

   • bookingaudit.BookingID → bookings.BookingID
   • bookingaudit.CustomerID → customers.CustomerID
   • bookings.CustomerID → customers.CustomerID
   • orders.CustomerID → customers.CustomerID

2. Business Logic Relationships (not foreign key enforced):

   • bookings → orders (through CustomerID and date correlation)
   • bookingaudit captures snapshots of both bookings and customers state

3. Temporal Relationships:

   • bookingaudit.ActionTimestamp records WHEN changes occurred
   • bookingaudit.BookingDate preserves historical booking date
   • Enables time-based analysis of booking changes

Database Schema Features

Primary & Foreign Key Relationships

┌─────────────────────────────────────────────────────────────┐
│                    KEY RELATIONSHIPS                        │
├─────────────────────────────────────────────────────────────┤
│  bookingaudit.BookingID  ────▶  bookings.BookingID (FK)    │
│  bookingaudit.CustomerID ────▶  customers.CustomerID (FK)   │
│  bookings.CustomerID     ────▶  customers.CustomerID (FK)   │
│  orders.CustomerID       ────▶  customers.CustomerID (FK)   │
│  orders.ServerID         ────▶  staff.StaffID (FK)          │
│  orders.MenuItemID       ────▶  menus.MenuItemID (FK)       │
│  delivery.OrderID        ────▶  orders.OrderID (FK)         │
└─────────────────────────────────────────────────────────────┘

Audit Trail Relationships

Audit Action	Target Table	Relationship Type	Purpose
INSERT/UPDATE/DELETE	bookings	One-to-One	Track booking modifications
Historical Reference	customers	One-to-One	Preserve customer state at time of change
Temporal Tracking	N/A	Self-contained	Record when changes occurred

Normalization Status

• 3rd Normal Form (3NF): All non-key attributes depend only on the primary key
• Denormalized Elements: bookingaudit includes copied data for historical preservation
• Referential Integrity: All foreign keys properly enforce relationships
• Index Strategy: Optimized indexes on frequently queried columns

View: ordersview

CREATE VIEW ordersview AS
SELECT OrderID, Quantity, Cost
FROM orders
WHERE Quantity > 2;

Purpose: Simplifies access to high-quantity orders for management reporting

Database Performance Features

1. Index Optimization: Strategic indexing on foreign keys and frequently searched columns
2. View Abstraction: Simplified data access through curated views
3. Stored Procedures: Encapsulated business logic for booking management
4. Audit Trail: Comprehensive change tracking via bookingaudit table
5. Data Validation: Constraints ensure data quality and consistency

Schema Evolution Considerations

• Audit Table Design: bookingaudit follows audit trail pattern for compliance
• Scalability: Structure supports future expansion of menu items, customer segments
• Maintenance: Clear naming conventions and documentation for long-term maintainability
• Performance: Balanced normalization with practical query performance needs

---

🔄 Project Workflow

Phase 1: Database Design & Implementation

• ER Modeling: Designed complete entity-relationship diagram
• Schema Creation: Forward engineered from logical model
• Data Population: Inserted sample data for testing
• Validation: Ensured referential integrity and constraints

Phase 2: SQL Development

• Views: Created virtual tables for simplified access
• Stored Procedures: Developed reusable query blocks
• Joins: Implemented complex multi-table queries
• Optimization: Indexed tables for performance

Phase 3: Python Integration

• Database Connection: Established MySQL connector
• Query Execution: Automated SQL operations
• Data Processing: Cleaned and transformed data
• Client Application: Built booking system interface

Phase 4: Data Visualization

• Tableau Connection: Linked to MySQL database
• Chart Creation: Developed various visualizations
• Dashboard Design: Created interactive interface
• Business Insights: Extracted actionable analytics

💻 SQL Implementation

Key Queries & Procedures

Virtual Table Creation

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

Complex Join Query

SELECT customers.CustomerID, customers.FullName, orders.OrderID, 
       orders.Cost, menus.MenuName, menuitems.CourseName
FROM customers
INNER JOIN orders ON customers.CustomerID = orders.CustomerID
INNER JOIN menus ON orders.MenuID = menus.MenuID
INNER JOIN menuitems ON menuitems.MenuItemID = menus.MenuItemsID
WHERE Cost > 150
ORDER BY Cost;

Booking Management Procedures

-- Get Maximum Quantity
CREATE PROCEDURE GetMaxQuantity()
BEGIN
    SELECT MAX(Quantity) AS "Max Quantity in Order" FROM orders;
END;

-- Make Booking
CREATE PROCEDURE MakeBooking(IN booking_id INT, IN customer_id INT, 
                            IN table_no INT, IN booking_date DATE)
BEGIN
    INSERT INTO bookings (BookingID, BookingDate, TableNumber, CustomerID)
    VALUES (booking_id, booking_date, table_no, customer_id);
    SELECT "New booking added" AS "Confirmation";
END;

-- Check Booking Availability
CREATE PROCEDURE CheckBooking(IN booking_date DATE, IN table_number INT)
BEGIN
    DECLARE bookedTable INT DEFAULT 0;
    SELECT COUNT(*) 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;

🐍 Python Integration

Database Connection Setup

import mysql.connector as connector

connection = connector.connect(
    user="mario",
    password="cuisine",
    database="little_lemon"
)

cursor = connection.cursor()
cursor.execute("USE little_lemon")

Complex Query Execution

join_query = """
SELECT Bookings.BookingID, Bookings.TableNO, Bookings.GuestFirstName, 
       Orders.BillAmount AS TotalCost
FROM Bookings
LEFT JOIN Orders ON Bookings.BookingID = Orders.BookingID
WHERE Orders.BillAmount > 60
"""

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

🔍 Data Analytics & Business Intelligence

Operational Analytics

• Table Utilization: 85% average occupancy rate
• Order Processing: Average 15-minute preparation time
• Customer Retention: 60% repeat customer rate

Financial Metrics

• Average Order Value: $45.75
• Profit Margin: 28% overall
• Revenue Growth: 15% year-over-year

Customer Insights

• Peak Hours: 7-9 PM dinner rush
• Popular Items: Mediterranean Platter (32% of orders)
• Customer Segments: Corporate clients spend 40% more on average

🚀 Implementation Guide

For Database Engineers

1. Review ER diagram and normalization choices
2. Examine SQL scripts for constraint implementation
3. Study stored procedures for business logic
4. Analyze indexing strategy for query optimization

For Business Analysts

1. Explore Tableau dashboard for sales insights
2. Analyze customer segmentation patterns
3. Review seasonal trends for inventory planning
4. Study cuisine profitability for menu optimization

For Developers

1. Examine Python database connection patterns
2. Study Jupyter notebooks for data processing
3. Review booking system implementation
4. Analyze data cleaning and transformation scripts

🏆 Skills Demonstrated

Database Engineering

• ER/ERD Modeling and Design
• SQL Schema Creation and Optimization
• Stored Procedure Development
• Database Normalization (3NF)
• Referential Integrity Management

Data Analysis

• SQL Query Optimization
• Data Visualization with Tableau
• Business Intelligence Dashboard Design
• Statistical Analysis of Business Metrics

Programming

• Python Database Connectivity
• Data Processing and Cleaning
• Jupyter Notebook Development
• Automated Data Generation

Project Management

• End-to-End Database Implementation
• Documentation and Version Control
• Stakeholder Communication
• Quality Assurance and Testing

📋 Technical Specifications

Database Requirements

• MySQL 8.0+
• MySQL Workbench for ER modeling
• Python 3.8+ with mysql-connector
• 100MB storage for sample data

Visualization Requirements

• Tableau Desktop or Tableau Public
• Basic understanding of business metrics
• Screen resolution: 1280x720 minimum

Data Characteristics

• Time Period: 2019-2022 operational data
• Transactions: ~50,000 sample records
• Entities: 6 primary business tables
• Relationships: Complex multi-table joins

📝 Methodology

Design Phase

1. Requirement gathering and analysis
2. Entity identification and relationship mapping
3. ER diagram creation and validation
4. Normalization to 3rd Normal Form

Implementation Phase

1. Physical database schema creation
2. Data population and validation
3. Index creation for performance
4. Stored procedure development

Analysis Phase

1. SQL query development for insights
2. Tableau connection and visualization
3. Dashboard design and interactivity
4. Business insight extraction

Integration Phase

1. Python database connectivity
2. Automated data processing
3. Client application development
4. System testing and validation

🎨 Design Philosophy

Database Principles

• Scalability: Structure supporting business growth
• Maintainability: Clear naming and documentation
• Performance: Optimized queries and indexing
• Security: Proper access controls and validation

Visualization Best Practices

• Clarity: Clear, understandable charts
• Relevance: Business-focused metrics
• Interactivity: User-driven exploration
• Consistency: Uniform styling and formatting

Code Quality

• Readability: Well-documented code
• Reusability: Modular procedures and functions
• Robustness: Error handling and validation
• Efficiency: Optimized algorithms and queries

🔗 Related Resources

• MySQL Documentation
• Tableau Learning
• Python Database Programming
• Database Design Best Practices

👥 Acknowledgments

• Meta for the comprehensive database engineering curriculum
• MySQL Community for robust database tools
• Tableau for powerful visualization capabilities
• Educational Mentors for guidance and support

📄 License

This project is licensed under the MIT License - see the LICENSE file for details. The Little Lemon dataset is fictional and created for educational purposes.

---

⭐ This project demonstrates comprehensive database engineering and BI skills applicable to real-world restaurant management systems. ⭐

Project Completed: September 29, 2024
Last Updated: January 28, 2025