--Savana Hughes & David Abushlaih

Savana and I created a 3NF SQL design framework for an ecommerce business modeled off of Shopify.

The design process started off with an attribute catalog, where we turned it into 1NF, 2NF, and 3NF. We generated our mock data using a python script found in generate_data.py

Our raw_table is found in create_raw_table.sql Our create tables for each individual table is found in ddl.sql Our insertions and table modifications are found in dml.sql

Our queries for finding the subsequent results are found in queries.sql

Using our 3NF tables we queried different business metrics:

--AVG_Business_Order_Value:
SQL query:

Results from Query:

--Total Business Revenue
SQL Query:

Results from Query:

--AVG Monthly Revenue
SQL Query:

Results from Query:

--Revenue By Year
SQL Query:

Results from Query:

How to Run

Install:

https://tableplus.com/blog/2018/09/getting-started-with-postgresql-on-mac.html Tableplus and DBngin

If you want to query directly from database:

cmd: psql -U postgres simplify_db

where postgres = username and simplify_db = database_name

To use Python Code:

Create a Virtual Environment

cmd: python3 -m venv venv

Activate a Virtual Environment

cmd: source venv/bin/activate

Install Requirements in virtual Environemnt

cmd: python3 -m pip install -r requirements.txt

Run code

cmd: python3 -m file_name_here