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.
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.
Use the files below and upload the data into the database.
Display an output as below that shows the top accounts based on total number of miles each account has ran with the company.
For the driver “Amani David”, create a table that shows how many miles the driver ran per month. It would look something like below.
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.
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.
Create a procedure that takes in an integer and string and creates an entry for a new account.
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.