You have been assigned to work with a coffee mug shop. They specialize in selling coffee mugs and they are hoping to set up a proper database for it. You’ve had several conversations with the owners of the coffee mug shop and created the ER Diagram below.

Some further requirements you’ve gathers from them are:

• The account ID is a string that the user inputs (such as “adam123”)
• For the order status, there could only be one of two values, “received”, meaning that they’ve received the order, and “completed”, which means that they’ve completed the order.
• For the order date, just the year, month and date will do.
• The mug size will be a number between 8 to 20 (meaning 8-20 ounces).
• The order ID is just a number that’s assigned to each order.
• The mug color and design could be text. It usually will be one word.

First assignment

Create appropriate tables for the ER Diagram above in MySQL.

What assumptions did you have to make?

What rules were you able to enforce through the physical design of the database?

Second assignment

The owners of the coffee mug shop had some data that they were collecting in spreadsheets. You’ve asked them to provide the data that they have so far in CSV files.

Download the CSV files above and import them into your database.

Third assignment

Create a table that shows all the order IDs and order dates for orders that are currently in the “received” status that were made before Sept 2022.

Fourth assignment

Create a table that shows the mug size, color and design for all the completed orders, sorted by when it was ordered.

Fifth assignment

Create a table that shows all the account IDs, mug color, size and design for all the orders that have not yet been completed, meaning still in the “received” status.