An OLTP Database Design and Analysis Project for a Restaurant Chain

PROBLEM STATEMENT

An end-to-end task of designing and testing an Online Transaction Processing database system to accommodate the increasing demand for in-person and online transactions across several countries. The restaurant currently stores its data in a spreadsheet for each branch. This adoption is necessary to streamline operations, management and coordination. As part of this project, comprehensive data analysis will be conducted to gain valuable insights from the existing data for each country.
Factors include customer demographics, sales trends, order patterns, and preferences.

DATABASE DESIGN AND TESTING

• Database Schema

  Schema Script

• Data Insertions

  Insert Statements

• Stored Procedures

  Data manipulation using stored procedures and transactions in a database provide a structured and controlled approach to database operations, ensuring data integrity, consistency, and security while promoting code reusability and performance optimisation.

  Data manipulation using stored procedures

  Data Insertion
  Select Maximum Order
  Booking Verification
  Update Booking
  Cancel Orders

  Data validation using stored procedures and transactions

  verify a booking and decline any reservations for tables that are already booked under another name

  Data Validation using Stored Procedures and Transactions

• View, Subquery and Join

  View, Subquery and Join

• Prepared Statement

  prepared statements in MySQL offer performance optimisation, protection against SQL injection attacks, increased security, improved code maintainability, readability, and database portability. They are essential for building secure, efficient, and maintainable database-driven applications.

  Query Data with Prepared Statement

• MySQL/Python Connector

  Query Data Using MySQL/Python Connector
  
  
  

DATA ANALYSIS

Comprehensive data analysis to gain valuable insights from the existing customer data. It involves various aspects, such as sales trends, customer demographics, top-performing customers, order patterns, and preferences.

Concepts Applied In Tableau

• Calculated Fields
• Actions
• Objects
• Filters

View analysis report on tableau public