BUILD A RELATIONAL DATABASE SYSTEM IN MYSQL WITH A PYTHON CLIENT

Capstone project for the META database engineer specialization on Coursera

Project summary

The goal of the project is to build a database system for Little Lemon restaurant, allowing them to store data regarding:

• bookings;
• orders;
• order delivery status;
• menu;
• customer details; and
• staff information.

Project guidelines

• The data model should be created and deployed in MySQL server using the Forward Engineer method in MySQL Workbench;
• SQL queries should be created to manage and summarize data for Little Lemon;
• a Python project should be set up for the database client; and
• Tableau software will be used to perform different types of data analytics to provide Little Lemon with relevant insights into their business performance on a dashboard.

List of files and folders

• folders :

  • raw-data : project source data (.xlsx and .csv formats)
  • data-clean : clean & formatted data, ready to populate database tables (.csv format)
  • queries : scripts performing the CRUD operations requested in the task list (.sql format)

• files :

  • task_list.pdf : list of project requirements for SQL queries, Python client project and Tableau dashboard
  • creation_script.sql : database and table creation script from MySQL Workbench Forward Engineer method
  • schema.mwb : entity relationship diagram for the MySQL database
  • query_results.xlsx : results of CRUD scripts
  • orders_above_60.ipynb : Python client in Jupyter Notebook (also as a .py file)

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