Question

Multi-part question:

Part 1:

Use MySQL Workbench to create an EER model for a database that stores information about the downloads that users make. (When you create the EER model, it will be given a default name of mydb. For this exercise, it’s not necessary to change this name.) Define the tables that are necessary to implement this data structure:

Each user must have an email address, first name, and last name.

Each user can have one or more downloads.

Each download must have a filename and download date/time.

Each product can be related to one or more downloads.

Each product must have a name.

When you’re done defining the tables, create a diagram for the database. Then, use the diagram to define the required relationships. When you do that, be sure to use the relationship button that uses existing columns.

Part 2:

Create a view named order_item_products that returns columns from the Orders, Order_Items, and Products tables.

This view should return these columns from the Orders table: order_id, order_date, tax_amount, and ship_date.

This view should return the product_name column from the Products table.

This view should return these columns from the Order_Items table: item_price, discount_amount, final_price (the discount amount subtracted from the item price), quantity, and item_total (the calculated total for the item).

Part 3:

Write a SELECT statement that uses the view that you created in part 2 to get total sales for the five best selling products. Sort the result set by the order_total column in descending sequence.

Answer 1

To create the database, you'll need three tables: Users, Downloads, and Products. For the view, use a SELECT statement to combine columns from different tables. To get total sales, use the created view and a SELECT statement with SUM and ORDER BY.

Step-by-step explanation:

Part 1:

To create an EER model for the given database, you'll need three tables: Users, Downloads, and Products. The Users table should have columns for email address, first name, and last name. The Downloads table should have columns for filename, download date/time, and a foreign key referencing the Users table. The Products table should have columns for name and a foreign key referencing the Downloads table.

Part 2:

To create the view named 'order_item_products' that returns columns from Orders, Order_Items, and Products tables, you'll need to write a SELECT statement. The statement should include the desired columns from each table, joined using appropriate JOIN statements.

Part 3:

To get total sales for the five best selling products using the created view, write a SELECT statement. This statement should SELECT the product_name column from the view and SUM the item_total column from the view, grouping by the product_name column. Finally, ORDER BY the total sales in descending sequence.

Answer 2

part 1:

Step-by-step explanation: