The objective of this assignment is to construct and deploy a database management

system for an online shopping market such as Hepsiburada or Amazon, utilizing MySQL

as the database. The system will provide the capability to manage products, customers,

orders, and inventory for an online store. The objective is to create a reliable and

efficient system that allows for the manipulation and retrieval of data for an online

shopping platform.

This assignment is made up of two sub$-$steps:

$1.$ Design the ER diagram and the relation schemas for the assignment$$s scenario

and create tables in your MySQL server.

$2.$ Add data into these relations, update and retrieve data from these relations in

your Java program via JDBC$.$ Implement a console interface to run the queries

which you will provided in the second part of this assignment.

When writing your program, you should create a new schema$/$database in your

MySQL server just for this assignment. You can$$t create the tables in Workbench, you

must write the corresponding DDL and DML files by hand Data Definition Language $($DDL$)$ SQL Code

The steps involved in writing the DDL SQL code:

$1.$ DDL SQL Code Writing: Based on the ER diagram, write DDL SQL code to

create tables for each identified entity.

$2.$ Include Specifications: Include primary keys, foreign keys, data types, constraints, and relationships in the SQL code.

Once you completed the database design, you should be able to do the followings in the

second part of this project $($By considering these queries you can modify your

design$)$:

Note: Please note that not all of this will be asked for Part II$,$ but your DB design

should at least allow these operations.

$$ Add a new seller

$$ Create a new category

$$ Add a new product

$$ Create a new Listing

$$ Add a new customer

$$ Buy a Product

$$ Update a listings$$ price

$$ Modify a customer$$s address

$$ Delete Product

$$ Delete Seller

$$ Delete Listing

$$ Delete Category

$$ Delete Customer

$$ Remove an order from the records

$$ Customer can add$/$remove a payment method

$$ Retrieve all products available in the specific category.

$$ List all customers who have placed orders on the last day$/$week$/$month$/$year$.$

$$ Display all orders placed by a specific customer.

$$ Show details of a particular product by its ID or name.

$$ List all sellers and their contact information.

$4$

$$ Display the total number of orders placed.

$$ Calculate the total revenue generated by a specific product.

$$ List top$-$selling products based on the quantity sold.

$$ Identify products that are out of stock or have low stock quantities.

$$ Find product listings supplied by a specific seller.

$$ Show the average price of products in each category. PLEASE WRITE DDL DML BOTH