StoreLogic DB

General Overview:
StoreLogic DB is a comprehensive project focused on the design and development of a relational database tailored to the intelligent management of an e-commerce business. The main objective is to structure, store, and analyze key data from the ecommerce environment to support strategic decision-making through advanced SQL queries and business analysis.

This project simulates the real-world environment of an online store operating across multiple sales channels (web, app, and marketplaces), offering products in various categories and managing orders, payments, shipments, returns, and marketing campaigns. The database is built following best practices in normalization, referential integrity, and scalability.

Project Objectives:
* Design a robust Entity-Relationship Model (ERD) for an ecommerce business.
* Build the database in SQL using primary keys, foreign keys, and one-to-many/many-to-many relationships.
* Simulate realistic data using Python and libraries such as Faker and Pandas.
* Develop advanced SQL queries to extract valuable business insights (sales, customers, most profitable products, etc.).
* Prepare the database for potential integration with data visualization tools like Power BI, Tableau, or Streamlit.

Core Database Structure:
* clients: personal information and purchasing behavior.
* products: inventory data, prices, and product categories.
* orders: purchase records including channel and status.
* order_details: products included in each order (many-to-many relationship).
* payments: payment methods and transaction status.
* shipments: logistics and delivery tracking.
* returns: record of returned products with reasons.
* campaigns: promotional campaigns by channel and product.

Key Metrics and Analysis (via SQL):
* Total sales by month, product, and sales channel.
* Most profitable and frequent customers.
* Return rates by product category.
* Profitability per product considering cost and price.
* Impact of marketing campaigns on sales volume.

Skills Demonstrated:
* Design of relational databases from scratch
* Logical modeling with a business-oriented approach
* Writing efficient and complex SQL queries
* Data simulation and cleansing using Python
* Analytical thinking focused on data-driven business decisions