For this assignment. Let’s imagine that you’ve been hired to create a simple database for a logistics company. You’ve talked with the stakeholders several times and came up with the ER diagram below.

First Assignment

Create a database in MySQL with four tables. For all ids, we will use integers, the run distance (miles) will also be an integer. For the run date, we will use DATE, and for the truck year, we could use an integer.

Second Assignment

Use the files below and upload the data into the database.

Third Assignment

Display an output as below that shows the top accounts based on total number of miles each account has ran with the company.

Fourth Assignment

For the driver “Amani David”, create a table that shows how many miles the driver ran per month. It would look something like below.

Fifth Assignment

Create a table for the total number of miles ran by each truck. Show the truck id, the year and total number of miles and sort by oldest truck to newest.

Sixth Assignment

For the account Mattress Experts, create a table that shows the ranking of the most used driver to the least used driver with the driver names. It could look something similar to the results below.

Seventh Assignment

Create a procedure that takes in an integer and string and creates an entry for a new account.

Eighth Assignment

Create a procedure that takes a driver name and if that driver exists in the database, remove the driver and all the driver’s runs from the database. Utilize transactions and implement a mechanism so that changes are reversed if the whole procedure does not execute properly.