Part 1 E-Commerce Project: Design Purpose: To design an E-commerce marketplace using ERD and Logical modeling Skills Used: ERD modeling. Knowledge Goals: ERD modeling, Logical design modeling, and relational design Design the DB for an Online Marketplace for a company you choose the name of. The DB should be able to track products, current inventory, transactions, reviews for products, and users who have signed up for an account on our website. Go through the Analysis (collect and analyze requirements) and Logical modeling (implement ERD of entities, relationships, and attributes), and normalization phases of your DB design (part of Logical modeling). Create the following: The ER diagrams using the notation discussed in class / the book, that can be done in a tool of your choice. I recommend Lucid Chart. Models created in MySQL ER diagram creator will not be accepted. In the future, you will need to answer questions from this DB like: • List which products we currently have in inventory. • Create new products. ● Modify the amount of a particular product that we have in inventory. Delete a product from the inventory Get a list of the most popular products for a given time range Get a list of the least popular products for a given time range Get a list of users who haven't purchased something in a few months to send promotional emails to This should also include products that these users normally purchase Required/Suggested items: • Have a minimum of 6 entities Crows feet notation must be used Cardinality must be defined, including relationship and attribute minimums and maximums. Unique values should be indicated for Attributes. Include documentation explaining which entities Strong and which entities are are Weak. Do not replace your crows feet notation with the diamond notation. Also, if you have any Supertype and Subtype entities, explain these in the documentation. I would like to see at least one supertype/subtype relationship. • Alternative option to documentation is to create a separate ER diagram with the diamond notation and supertype/subtype entities format. Appendix ER Diagram Example: • Imagine a diagram for a simple library system. Entities could include Book, Author, and Member. Book could have attributes like BookID, Title, and Genre. • Author might have AuthorID, Name, and Bio. • Member could include MemberID, Name, and Email. Relationships could show that a Book is written by an Author and borrowed by a Member. Normalization (Boyce-Codd Normal Form): • ● • Entities might be split into Books, Authors, and Members with primary keys. Books entity could have a foreign key linking to the Authors table to establish the relationship without redundancy. Additional entities might be used for many-to-many relationships (e.g., if a book can have multiple authors), such as an AuthorBook junction entity with foreign keys linking to both Authors and Books. Part 2: E-Commerce Project: Implementation Purpose: To implement an E-commerce marketplace using SQL DDL and DML Skills Used: SQL DDL and DML, DBMS operation Knowledge Goals: Translating design into implementation through DDL and DML Summary For this assignment, you will create the Database Schema for the Online Marketplace DB for your company Create the following: You will modify your data model design to normalize your database into Boyce-Codd NF from the Design phase and then implement it. While you are not required to create a new diagram for the database design, it would be helpful if you did, especially with many-to-many relationships. For each normalized relation write a SQL CREATE TABLE statement. Your tables should implement the PRIMARY KEY and FOREIGN KEY constraints of your normalized design. You should also insert some data into each table. The amount of data should be such that the need for the database is clear. In other words, provide enough examples to demonstrate why a database was required in the first place. Be sure your database can be used to complete these tasks: • List which products we currently have in inventory • • Create new products Modify the amount of a particular product that we have in inventory Delete a product from the inventory Get a list of the most popular products for a given time range • Get a list of the least popular products for a given time range Get a list of users who haven't purchased something in a few months to send promotional emails to This should also include products that these users normally purchase Be careful and consider what constraints may need to be put in place to ensure referential integrity. Part 3: E-Commerce Project: Application Creation Purpose: To create an application using Python or Java that will allow API access and manipulation of your database Skills Used: Using Python or Java APIs to access a MySQL database Knowledge Goals: Python, Java, API access, manipulation, Triggers, Stored Procedures/Functions Summary For this assignment, you will create a Java or Python application which can call into the locally running Online Marketplace DB, as well as create stored procedures/functions and triggers in your database Create the following: Use the Java or Python APIs to create an application that can access manipulate and query the database you created in the last project. Create at least 2 Stored Procedures/Functions and 1 Trigger. This application should be able to do the following: • List which products we currently have in inventory Create new products • Modify the amount of a particular product that we have in inventory • Delete a product from inventory • Get a list of the most popular products for a given time range Get a list of the least popular products for a given time range • • Get a list of users who haven't purchased something in a few months to send promotional emails to This should also include products that these users normally purchase For least and most popular, this can be calculated based on the number of items sold over the given range. Include the rating score for these items in your calculation, and weight those two values. This don't have to make any fancy GUI for this application it can just be text menus. But provide a meaningful interface that is robust and helpful to a person wishing to use this database to query the E-Commerce database. Format Requirements Please use Java or Python to complete this project. Assessment Your code will be assessed according to quality and correctness. I will test the functions above and your stored procedures/functions and triggers. Deliverables Please be sure the SQL code that contains your database creation, insertion, stored procedures/functions and triggers are all in the Github repo, along with your Java or Python code for the application. Appendix • ER Diagram Example: Imagine a diagram for a simple library system. Entities could include Book, Author, and Member. • Book could have attributes like BookID, Title, and Genre. • Author might have AuthorID, Name, and Bio. • Member could include MemberID, Name, and Email. Relationships could show that a Book is written by an Author and borrowed by a Member. Non-Normalized Logical Design Diagram: This could be a table representation showing how data is initially laid out. A single table for the library system might include columns for all book, author, and member attributes, leading to redundancy (e.g., the same author details repeated for multiple books). Normalized Logical Design Diagram (Boyce-Codd Normal Form): • • This would refine the non-normalized diagram by eliminating redundancy. Tables might be split into Books, Authors, and Members with primary keys. Books table could have a foreign key linking to the Authors table to establish the relationship without redundancy. Additional tables might be used for many-to-many relationships (e.g., if a book can have multiple authors), such as an AuthorBook junction table with foreign keys linking to both Authors and Books./n first part we have to design, second we will implement and third we will use java or python we just have to create everything by ourself . something creative and interesting Needs code+comment for part 3