Greenspot Grocer is a rapidly growing family-owned online grocery store. This project involves transitioning their inventory and sales data from a flat-file spreadsheet format to a robust, scalable MySQL Relational Database.
The goal is to eliminate data redundancy, ensure data integrity through normalization (3NF), and provide a foundation for business expansion.
- 3NF Normalization: Minimized data redundancy by separating Vendors, Products, Units, and Categories into dedicated tables.
- Storage Optimization: Strategic use of data types (
TINYINT,SMALLINT,UNSIGNED) to reduce disk space and improve query performance. - Relational Integrity: Implementation of Primary Keys and Foreign Keys to prevent orphan records.
- Business Intelligence: Advanced SQL JOIN queries to track inventory levels, sales history, and profitability.
data/: Contains the original source dataset (.csv).reports/: Documentation on database design, anomalies analysis, and normalization steps.sql_scripts/:create_database.sql: Schema definition (DDL).load_data.sql: Data population script (DML).verify_queries.sql: Initial verification tests.check_relationships.sql: Relationship validation.insert_new_product.sql: Example of maintaining referential integrity during insertions.business_report_query.sql: Complex query for profitability analysis.
- Clone this repository.
- Open MySQL Workbench.
- Execute the scripts in the following order:
sql_scripts/create_database.sqlsql_scripts/load_data.sql
- Use the remaining scripts in
sql_scripts/to verify and test the database functionality.
The database allows the owner to answer critical questions such as:
- Which products are low on stock and who is the preferred vendor?
- What is the total profit generated per sale after calculating purchase costs?
- Which categories are performing best in terms of sales volume?
📄 License This project is distributed under the MIT license. Its purpose is strictly educational and research-based.
Author: JUAN S.
Contact: https://github.com/johnyse99