Important note: The data files attached to this assignment to be used in part II are randomly generated.

PartIU$(10$ points$)$ Consider the following logical relations for simple ecommerce:

Products $($PID: integer, Name: varchar$($x$),$ Vendor: varchar$($x$),$ AvailableQuantity: integer$)$

Customers $($CID: integer, FullName: varchar$($x$),$ Email: varchar$($x$),$ Age: integer, Address: varchar$($x$),$ State: varchar$($x$))$

Orders $($OID: integer, PID: integer, CID: integer, OrderQuantity: integer, OrderDate: DATE, TotalCost: decimal $(x,x))$

The "PID" and "CID" fields in Orders relation are foreign keys referencing the "PID" field of the Products relation and "CID"

field of the Customers relation.

Attached with this assignment are $3$ CSV files with data for the three relations described above: $100$ Products, $1000$ Customers,

and $2000$ Orders. Scan the data records stored in these files to decide on the size $($the value of $x)$ of the varchars and decimals in

these logical relations.

Through MySQL workbench or shell, use SQL to create a new database $($named eCommerceApp$),$ build the relations $($tables$)$

mentioned above, and place the constraints $($e$.$g$.,$ default values$)$ you find appropriate.

In this assignment, as detailed below, you will create a simple Python or Java terminal$-$based application that connects to the

database you created and interacts with it$.$ Revise the Python$-$SQ$1$ tutorial or the Java$-$SQL tutorial $($the material is on Canvas$)$ to

do the following tasks.

$*$Note: You can create a simple python or java terminal$/$console$-$based application.

$*$Note: Sometimes, when you create a java project over Eclipse Java IDE, Eclipse includes a file named "module$-$info.java" to

your project. Make sure you delete this file to avoid compilation errors.

Task $1(4$ points$)$: Attached with this assignment are $3$ CSV files with data for the three relations described above. Through your

Python or Java application, perform the following steps:

Connect with the your MyCompany database

Read the files line by line

Build SQL queries to insert the data records in the corresponding relation.

$*$Note: After you run these insert queries successfully, you may check the content of the tables using MySQL workbench$/$shell

$($e$.$g$.,$ display the content of the tables$)$

$*$Note: To avoid inserting duplicate records and receiving errors, comment out $($e$.$g$.,\frac{?}{?}*dots..\frac{*}{?})$ this part in your Python or Java

code before you move to the next task.

Task $2(6$ points $-2/$query$)-$ Through your Python or Java application, use SQL to develop and execute the following queries:

For each available product vendor, display the vendor as well as the number of unique products and the total quantity

available. Only display vendors with more than $5$ unique products.

Display the customers $-$by full name, state, and age$-$ who made orders with more than $1000$$ $($included$).$

Display any order made in $2023$ with total cost greater than the average of all orders made so far. Display the orders in

descending order of their total costs.