This is a database project for an e-commerce scenario. The database was created to store information about customers, stocks, products, orders, suppliers and sellers (authorized stories).
The implementation of this project involved the transition from the Conceptual Model to the Logical Model, incorporating the use of the Enhanced Entity-Relationship Diagram (ERR).
I used the SQL language to create the database in the DBMS, MySQL, defining the entities and restrictions necessary for the model. Additionally, I populated the database with dummy data to allow queries using Data Query Language (DQL).
The project was a prerequisite for approval in the "Database Modeling and Logical Projects" module of the "Bootcamp Power Tech powered by Ifood | Data Science" from the educational institution DIO.
Here are the business questions that can be answered using SQL queries:
- Where is a product located in stock?
- Which products are low in stock?
- What is the total sales revenue to customers in a given period?
- Which customers placed the most orders?
- Which suppliers have a specific product?
- Which authorized stores sell a specific product from our e-commerce?
- What are the most rated products?
- Which types of payments have the biggest requests?
- What is the total value in sales by payment type?
- Which orders are still in progress?
- Which customers have not placed any orders?
- Requirements Analysis: This is the initial phase, where system requirements were collected and analyzed. This involves understanding what information the database should store, how the data will be used, and what operations should be supported.
- Conceptual Model: the conceptual model was created that describes the entities, relationships and attributes of the system. The Enhanced Entity-Relationship Diagram (EER) was used for this purpose.
- Logical Design: Based on the conceptual model, the database schema was designed. This includes defining tables, columns, primary and foreign keys, and other constraints.
- Implementation: Here, the logical design has been mapped to a database management system (DBMS). The tables, relationships and constraints were created in the chosen DBMS (in this case, MySQL).
- Data Loading: After mapping the schema to the DBMS, some fictitious data was loaded, in order to perform some queries and obtain some business insights.
- Tests and Queries: some tests were carried out to observe the operation of the database and some queries were also carried out to answer some business questions.
- MySQL Server
- SQL
- MySQL Workbench
- Lucidchart
- Run the creating_data_ecommerce_database.sql script against a supported database (e.g. MySQL, PostgreSQL) to create the database schema;
- Run the inserting_data_ecommerce_database.sql script to insert data into the database;
- Run the queries_data_ecommerce_database.sql script to perform the queries discussed.
Contributions are welcome! Feel free to suggest improvements or corrections to the project.
Jadeson Bruno Albuquerque da Silva
