eddahviolet / db-capstone-project

Final project in the Meta Database Engineer Professional Certificate

setting up repository for project

Welcome!! This readme is a complete step by step summary of my project from beginning to end along with my thought process. To organise my files and make it easier for you to understand me, I have labelled them in weeks. Since the project was presented in weeks, I have used that to organise the files which are the .sql with the queries and the images with the results. I have labelled the images with the results from the queries chronologically as they were presented in the exercises. For instance, Week 2a Ordersview is the result of the first task in week 2, 2b is the second task in week 2 and so on and so forth till Week 3. Enjoy!!

Week 1: Project Info

Setting up the repository

The very first task was setting up a repository (which is this one) name it db-capstone-project and initialise it in a similarly named folder in your computer

Create a readme.md file and write the message "setting up repository for project"

Commit the changes with a meaningful message

My steps:

• used touch command to create the readme.md file
• nano command to open the file and edit
• cat command to show content of file

Set up the MySQL instance server in MySQL Workbench Scenario

Create a new user in your MySQL database.

• Log in to the MySQL Server using the root user.
• Select Administration tab from the Navigator section.
• Select Add account.
• Provide a meaningful username and a strong password.
• Grant the new user the right to perform all tasks.

My new user is “eddah”

Create a new MySQL connection.

• In the MySQL Workbench home screen, select MySQL connection tab.
• Select the plus icon to open the Setup New Connection form.
• Fill in the form to create a new server instance.
• Click the Test Connection button to check that the settings work as required.

My new connection is called "ColourDB"

Create an ER diagram data model and implement it in MySQL

Little Lemon needs to build a robust relational database system in MySQL in which they can store large amounts of data. They then need to easily manage and find this data as required. This database system should maintain information about the following aspects of the business:

• Bookings
• Orders
• Order delivery status
• Menu
• Customer details
• Staff information

Create a normalized ER diagram (that adheres to 1NF, 2NF and 3NF) with relevant relationships to meet the data requirements of Little Lemon

I first created a conceptual model using draw.io to understand how the tables will relate to each other. Some customers and staff could have multiple addresses, to apply data atomicity I created Addresses tables for both and relate them to the respective tables.

Then created the ER diagram

Implementing the Little Lemon data model inside your MySQL server using the forward engineer method

I used the Database tab to start the forward engineering process, followed the subseqent steps and this is the script that was generated to create the LittleLemon database

its too long to paste it all here, its in the files labelled as "LitleLemonDB Script to forward engineer.sql"

Populating Tables I populated my tables with self generated data, using Kenyan names, Kenyan Foods and Cuisines, the full script is in the files saved as "Popoltaing tables in LittleLemonDB"

Week 2 Database Queries, Procedures and Prepared Statements

Adding Sales Report

Create a virtual table to summarize data

Little Lemon needs to retrieve data from their database, to create some reports on the orders placed in the restaurant.

Task 1: Create a virtual table to summarize data.

Create a virtual table called OrdersView that focuses on OrderID, Quantity and Cost columns within the Orders table for all orders with a quantity greater than 2. Steps:

• Use a CREATE VIEW statement.
• Extract the order id, quantity and cost data from the Orders table.
• Filter data from the orders table based on orders with a quantity greater than 2.
• Query the OrdersView table to display the output

Task 2: Use a JOIN statement to query data from multiple tables.

Little Lemon needs information from four tables on all customers with orders that cost more than $150. Extract the required information from each of the following tables by using the relevant JOIN and the result set should be sorted by the lowest cost amount. • Customers table: The customer id and full name. • Orders table: The order id and cost. • Menus table: The menus name. • MenusItems table: course name and starter name.

I included BookingID because my Customers table connects to the Orders table through the Bookings table

Task 3: Create a SQL statement with a subquery.

Little Lemon needs you to find all menu items for which more than 2 orders have been placed. Carry out this task by creating a subquery that lists the menu names from the menus table for any order quantity with more than 2.

• Use the ANY operator in a subquery
• The outer query should be used to select the menu name from the menus table.
• The inner query should check if any item quantity in the order table is more than 2.

I will target my column ItemNames that is similar to MenuNames and is in the MenuItems table to give the same results required by the task

Create Optimized Queries to Manage and Analyze Data

Task 1: Create Procedure GetMaxQuantity

Little Lemon need you to create a procedure that displays the maximum ordered quantity in the Orders table. This will allow Little Lemon to reuse the logic implemented in the procedure easily without retyping the same code over again and again to check the maximum quantity.

Call the procedure GetMaxQuantity

Task 2: Create Prepared Statement GetOrderDetail

Little Lemon need you to help them to create a prepared statement called GetOrderDetail. This prepared statement will help to reduce the parsing time of queries. It will also help to secure the database from SQL injections.

• The prepared statement should accept one input argument, the CustomerID value, from a variable.
• The statement should return the order id, the quantity and the order cost from the Orders table. 
• Create a variable called id and assign it value of 1.
• Execute the GetOrderDetail prepared statement

I will use BookingID instead of customerID since that is the foreign key in my Orders table

Task 3: Create Procedure CancelOrder

Create a stored procedure called CancelOrder. Little Lemon want to use this stored procedure 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.

Table Booking System

Create SQL queries to check available bookings based on user input

Task 1: Populating Bookings table

Little Lemon wants to populate the Bookings table of their database with some records of data. Your first task is to replicate the list of records in the following table by adding them to the Little Lemon booking table.

• Use simple INSERT statements to complete this task.
• Your output must resemble the following screenshot:

My table has an extra column a StaffID, a foreign key to link it to the Staff table

Task 2: Create Procedure CheckBooking

Little Lemon need you to create a stored procedure called CheckBooking to check whether a table in the restaurant is already booked. The procedure should have two input parameters in the form of booking date and table number. Create a variable in the procedure to check the status of each table.

I decided to alter the table removing the not null constraint on the booking time column, the StaffID and CustomerID being foreign keys, I maintained the not null constraint and use a value of zero for the insert statement.

BookingID column is on auto increment so you don’t have to list it in the columns for the insert statement

Task 3: Create a Procedure called AddValidBooking

Little Lemon need to verify a booking, and decline any reservations for tables that are already booked under another name. To implementing this they require a stored procedure and a transaction. Create a new procedure called AddValidBooking that must use a transaction statement to perform a rollback if a customer reserves a table that’s already booked under another name.

• The procedure should include two input parameters in the form of booking date and table number.
• It also requires at least one variable and should begin with a START TRANSACTION statement.
• Your INSERT statement must add a new booking record using the input parameter's values.
• Use an IF ELSE statement to check if a table is already booked on the given date.
• If the table is already booked, then rollback the transaction. If the table is available, then commit the transaction

I used LIMIT 1 in my select into statements to limit the result set to a single row because I kept getting “Error Code: 1172 results consisted of more than 1 row”

Create SQL queries to add and update bookings

Little Lemon needs help with managing their bookings. They need you to create stored procedures that they can invoke as required to add, update and delete bookings in their database

Task 1: Create a procedure called AddBooking

Create a new procedure called AddBooking to add a new table booking record. The procedure should include four input parameters in the form of the following bookings parameters:

• booking id
• customer id
• booking date
• table number

My booking table has a StaffID column I will not include it in the variables but have it in the insert statement with a value of 0, the booking record can be updated later with StaffID of the assigned staff.

Task 2: Create a Procedure called UpdateBooking

Create a new procedure called UpdateBooking that can be used to update existing bookings in the Bookings table. The procedure should have two input parameters in the form of booking id and booking date. You must also include an UPDATE statement inside the procedure.

I use the booking id variable as the condition in the update statement and updating the BookingDate column

Task 3: Create Procedure called CancelBooking

Little Lemon need you to create a procedure called CancelBooking that they can use to cancel or remove a booking. The procedure should have one input parameter in the form of booking id. Write a DELETE statement inside the procedure.

Week 3: Clients & Visualistaion

Data Visualisation

Set up the Tableau Workspace for data analysis

Little Lemon have an Excel sheet file with thousands of records of data on orders made between 2019 and 2023. They want to analyze the sales data to examine how they can increase their profits. prepare the existing data before analyzing it.

Task 1: Filtering

Connect to Little Lemon data stored in the Excel Sheet called LittleLemonDB. Then filter data in the data source page and select the United States as the country.

Steps:

• In the Connection Pane select Excel, then navigate to the data source which I saved as LittleLemonData
• On the data source page, I selected Add Filter, selected field as “country”, in the general tab of the filter pop up, I selected from list the checked “United states” then Okayed everything

This is how the Country Field looked like before applying the filter

This is how the Country Field looked like after applying the filter

Task 2: Split Column

Create two new data fields called First Name and Last Name. Related values should be extracted from the Full Name field.

Steps

• Locate the spit feature found in the downward arrow at the top of the customer Names column
• Select Split
• Rename the two new columns “Customer First name” and “Customer Last Name”
• Hide the column with the full names

Task 3: Calculated Field

Create a new data field that stores the profits for each sale, or order as shown in the screenshot below.

Steps:

• I select calculated field from down arrow in the Sales column
• Name the calculated field “Profit”
• Write a formula that deducts Cost from Sales.

The Calculated field

The new field "Profit"

Create interactive dashboard for sales and profits

Little Lemon need you to create visual charts to help them understand their business performance, so they can increase their sales and profits.

Task 1: Bar Chart

Create a bar chart that shows customers sales and filter data based on sales with at least $70.

My steps:

• Drag “Customer Name” field to the Columns shelf
• Drag “Sales” field to the Rows shelf
• Drag “Customer Name” to the Colour marker to make the bar differently coloured
• From the down arrow on the “Sales” Filed in the Rows Shelf Filter “Sales” to “At Least 70”
• On the chart, sort sales in descending order
• Drag Customer name and Sales field to the Detail marker
• Name the chart Customers sales

If you roll over a bar, the customer names and sale figures will be displayed

Here is a link to the Sales Bar chart on Tableau Public https://public.tableau.com/views/CustomerSalesLittleLemon/Customerssales?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

Task 2: Line Chart

Create a line chart to show the sales trend from 2019 to 2022.

My Steps:

• Drag the “Order Date” field to the Columns shelf
• Drag the “Profit” field to the Rows shelf
• Drag the “Order Date” field to the colour marker to give the line in the chart colour variations for each year
• From the down arrow on the “Order Date” field in the columns shelf, on the general tab, filter the years to exclude 2023, by unchecking 2023 in the list provided
• Name the chart Profit chart.

Here is a link to the Profit Line chart on Tableau Public https://public.tableau.com/views/ProfitChartLittleLemon/ProfitChart?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

Task 3: Bubble Chart

Create a Bubble chart of sales for all customers. The chart should show the names of all customers. Once you roll over a bubble, the chart should show the name, profit and sale.

My steps:

• Drag the “Customer Name” field to the Column shelf
• Drag “Sales” to the rows shelf
• Select Packed bubbles from the “Show me” drop down
• Drag “Customer Name” to the colour marker to colour the bubbles differently
• Drag “Customer Name” to the label marker so that each bubble bears the name of the customer it represents
• Drag “Profit” field to the detail marker to display profit (along with customer name and Sales) when you hover on a bubble
• Name the chart Sales Bubble Chart.

Here is a link to the Sales Bubble chart on Tableau Public https://public.tableau.com/views/SalesBubbleChartLittleLemon/SalesBubbleChart?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

FYI only reason I can think of why the Customer Names did not appear in all the bubbles is; I am using an old version of Tableau, the 2019 one

Task 4: Task 4: Side-by-side Bars

Compare the sales of the three different cuisines sold at Little Lemon. Create a Bar chart that shows the sales of the Turkish, Italian and Greek cuisines. Display sales data for 2020, 2021, and 2022 only. Each bar should display the profit of each cuisine. Sort data in descending order by the sum of the sale.

My steps:

• Drag “Cuisine Name” field to the Columns shelf
• Drag “Sales” field to the rows shelf
• Drag “Order Date” field on the column shelf next to “Cuisine Name”
• Select side by side bars from Show me drop down menu
• From the down arrow on the “Order Date” field in the columns shelf, on the general tab, filter the years to exclude 2023 and 2019, by unchecking them both in the list provided
• Drag “Profit” field to the label marker so that it appears on top of each bar
• Drag “Order Date” field to the colour marker so that bars are coloured differently according to years
• Sort the Sales axis descending by sum of sales within order date
• Name the worksheet Cuisine Sales and Profits.

Here is a link to the Cuisine Sales and Profits Side-by-side Bar chart on Tableau Public https://public.tableau.com/views/CuisineSalesandProfitsChartLittleLemon/CuisineSalesandProfits?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

Task 5: Interactive Dashboard

Create an interactive dashboard that combines the Bar chart called Customers sales and the Sales Bubble Chart. Once you click a bar, and roll over the related bubble, the name, sales and profit figures should be displayed in the Bubble chart.

My Steps:

• Start a new dashboard by clicking the dashboard icon with the plus sign
• From the sheets pane drag the “Customer sales” sheet to the dashboard
• I used a tiled dashboard because there only two sheets so easy to arrange
• Then drag the “Sales bubble chart’ sheet into the dashboard below the “Customer sales” sheet
• Remove the legends of both sheets from the dash board by clicking the X in the legends
• Check the show dashboard title check box and name the dashboard “Sales DashBoard”
• Use the filter on the “Customer sales bar chart”

Here is a link to the interactive Dashboard on Tableau Public https://public.tableau.com/views/CustomerSalesDashboardLittleLemon/CustomerSalesSalesBubbleDashboard?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

Database Client

Set up the client project

Task 1

Navigate to your terminal and ensure that Python is installed and available on the command path.

Task 2

Install Jupyter

Open a notebook

Create a new notebook for writing your code

Task 3

Establish a connection between Python and your database

Steps

• Ensure that mysql-connector is installed by running the command:
• Import the connector under the alias connector
• Verify that a connection can be made with your database by calling the connection method from the connector class: connection = connector.connect (user = "user_name", password = "password")

Add query functions

An integral part of a well-functioning system is the ability to query data. Little Lemon need you to help them add the search functionality required to query their data.

Task 1: Make connection & create Cursor object

Connect with your database and interact with the data it holds.

Import the connector module, enter your user details and connect with the database such as

connection = connector.connect(user = "your_username", password = "your_password", db = "database_name") Instantiate an instance of cursor to pass queries and return results

Task 2: show tables

Query the database to show all tables within the database. Ordersview is a virtual table, I originally have 9 tables in my database

Task 3: Query with table JOIN

Little Lemon need you to return specific details from your database. They require the full name and contact details for every customer that has placed an order greater than $60 for a promotional campaign.

I will add BookingID to the query because my Customers table connects to the Orders table through the Bookings table

I use the cast() function in my query to convert the output of TotalCost column which is decimal to char datatype