Online Assessement

Hey! This is a quick assessement I built for Diligent Corporation It's a complete e-commerce system with synthetic data, proper normalization, and all the SQL joins you'd ever need.

What's Inside

5 tables with proper relationships (categories, customers, products, orders, order_items)
483 records of realistic e-commerce data
Python script that creates and populates the database
SQL queries demonstrating all join types (INNER, LEFT, RIGHT, CROSS, SELF, etc.)
Documentation explaining the design decisions

Stack: Python 3 (stdlib only), SQLite, CSV files, SQL

No fancy dependencies - everything runs with standard Python.

Folder Guide

Here's what each folder contains and why:

📁 `datasets/` - The Raw Data

5 CSV files with synthetic e-commerce data:

categories.csv - 10 product categories (Electronics, Clothing, etc.)
customers.csv - 80 customer records with addresses
products.csv - 100 products with prices and inventory
orders.csv - 100 customer orders
order_items.csv - 193 line items (what's in each order)

All data has proper foreign key relationships - no orphaned records.

📁 `DB/` - The SQLite File

The actual database file (ecommerce.db) gets created here when you run the ingestion script.

This is where all the CSV data ends up - properly organized in relational tables with foreign keys enforced.

📁 `scripts/` - Python Code

Main file: ingest_data.py

This script:

Creates the database schema (all 5 tables with constraints)
Reads the CSV files in correct order (respects foreign keys)
Imports data with validation
Runs integrity checks
Prints business metrics

Run it once to build everything. It's idempotent-ish (will recreate if you run again).

📁 `sql_query/` - SQL Analysis

Main file: analysis_query.sql

Demonstrates 12 different join patterns:

INNER JOIN (basic matching)
LEFT JOIN (all from left + matches)
RIGHT JOIN (simulated in SQLite)
FULL OUTER JOIN (simulated with UNION)
CROSS JOIN (Cartesian product)
SELF JOIN (table joined with itself)
Joins with subqueries, aggregations, CASE statements

Each query is commented with real business questions it answers.

📁 `db_design/` - Documentation

Two markdown files:

DATABASE_SCHEMA.md

Table structures with field descriptions
All foreign key relationships explained
Normalization notes (why 3NF, where we bent the rules)
Cardinality relationships with examples
Quick reference for constraints

ER_Diagram.MD

ASCII art diagrams of table relationships
Visual representation of foreign keys
The 4 main relationships explained
Includes the M:N bridge table pattern

📁 `prompts/` - Prompt Engineering

PROMPT_DESIGN_TEMPLATE.MD

The template I used to generate all this work. Shows:

How to structure prompts for LLMs
Examples of good vs bad prompts
The actual prompts used for data generation, schema design, and SQL queries

Meta, but useful if you want to understand the process.

Getting Started

Step 1: Check Python

python --version

Need Python 3.7 or higher. No pip installs required - uses only stdlib.

Step 2: Build the Database

python scripts\ingest_data.py

You'll see:

[OK] Connected to database: DB\ecommerce.db
[OK] Foreign keys enabled
[OK] Database schema created successfully
[OK] Imported 10 records into categories
[OK] Imported 80 records into customers
[OK] Imported 100 records into products
[OK] Imported 100 records into orders
[OK] Imported 193 records into order_items
[PASS] All integrity checks passed

Business Metrics:
- Total Revenue: $26,123.13
- Average Order Value: $261.23
- Most Popular Category: Home & Kitchen

DATABASE INGESTION COMPLETED SUCCESSFULLY
[OK] Imported 100 records into products
[OK] Imported 100 records into orders
[OK] Imported 193 records into order_items
[PASS] All integrity checks passed

3. Run SQL Queries

sqlite3 DB\ecommerce.db < sql_query\analysis_query.sql

Folder Breakdown

📁 `datasets/`

Contains 5 CSV files with synthetic e-commerce data:

File	Rows	Description
`categories.csv`	10	Product categories (Electronics, Clothing, etc.)
`customers.csv`	80	Customer info (name, email, address)
`products.csv`	100	Product catalog with pricing & inventory
`orders.csv`	100	Order headers (customer, date, total, status)
`order_items.csv`	193	Line items (what's in each order)

Total: 483 records

Features:

Referential integrity maintained
Realistic data (names, prices, dates)
No orphaned records

📁 `scripts/`

Python automation for database creation.

File: ingest_data.py

What it does:

Creates SQLite database schema
Defines all tables with constraints (PKs, FKs, UNIQUE, CHECK)
Imports CSV data in correct order (respects foreign keys)
Validates data integrity
Reports business metrics

Class: EcommerceDBManager

Connection management
Schema creation
CSV import with error handling
Integrity verification

Run it:

python scripts/ingest_data.py

📁 `DB/`

Contains the SQLite database file.

File: ecommerce.db

Schema:

5 tables (categories, customers, products, orders, order_items)
4 foreign key relationships
2 UNIQUE constraints (email, category_name)
3 CHECK constraints (positive prices, quantities)

Key Stats:

Total Revenue: $26,123
Avg Order Value: $261
Most Popular: Home & Kitchen (46 items sold)
Date Range: Jan 15 - Apr 23, 2024

📁 `sql_query/`

SQL queries demonstrating all join types.

File: analysis_query.sql

Contains:

INNER JOIN - Basic matching (customers with orders)
LEFT JOIN - All from left + matches (all customers, even without orders)
RIGHT JOIN - Simulated with LEFT JOIN (SQLite doesn't support)
FULL OUTER JOIN - Simulated with UNION
CROSS JOIN - Cartesian product (all combinations)
SELF JOIN - Table with itself (customers in same city)
NATURAL JOIN - Auto-join on common columns
Subquery JOIN - Joins with derived tables
Aggregate JOIN - GROUP BY with joins
Conditional JOIN - CASE statements in joins
Multiple Outer JOINS - Complex LEFT JOIN chains
Multi-table Complex - 4-5 table joins

Examples:

Customer revenue by category
Top products per category
Geographic sales analysis
Customer segmentation
Product performance

Run queries:

sqlite3 DB\ecommerce.db < sql_query\analysis_query.sql

📁 `db_design/`

Database design documentation.

Files:

`DATABASE_SCHEMA.md`

Complete schema documentation:

Table structures with sample data
Primary & foreign keys
Constraints (UNIQUE, NOT NULL, CHECK)
Normalization (1NF, 2NF, 3NF)
Cardinality relationships
Functional dependencies
CREATE TABLE statements

`ER_Diagram.MD`

Visual database layout:

ASCII ER diagram showing all 5 tables
Relationship diagrams (1:N, M:N)
Foreign key rules
Stats & metrics

Good for: Understanding schema design decisions and table relationships

📁 `prompts/`

Prompt engineering templates used to generate this project.

File: PROMPT_DESIGN_TEMPLATE.MD

Contains:

Reusable prompt structure (9 sections)
Example prompts for data generation, ingestion, SQL queries
Best practices for Chain-of-Thought prompting
Tips & tricks for better LLM outputs

Use case: Template for creating future prompts

Database Schema Overview

Tables & Relationships

CATEGORIES (1) ──► (N) PRODUCTS (1) ──► (N) ORDER_ITEMS
                                              │
                                              │
                                              ▼
CUSTOMERS (1) ──► (N) ORDERS (1) ──────────► (N) ORDER_ITEMS

Foreign Keys

Child Table	FK	Parent	Action
products	category_id	categories	RESTRICT
orders	customer_id	customers	RESTRICT
order_items	order_id	orders	CASCADE
order_items	product_id	products	RESTRICT

RESTRICT: Can't delete parent if children exist
CASCADE: Delete parent = auto-delete children

Key Features

✅ Database Design

3NF Normalization - No redundant data
Referential Integrity - All FKs validated
Constraints - UNIQUE, NOT NULL, CHECK enforced
Star Schema - Fact + dimension tables

✅ Data Quality

No Orphans - All references valid
Realistic Data - Proper names, emails, prices
Historical Accuracy - Prices saved at order time
Status Tracking - Order states (Processing, Shipped, Delivered)

✅ Code Quality

Error Handling - Try/catch blocks
Validation - Integrity checks
Logging - Clear status messages
Documentation - Comments throughout

✅ SQL Coverage

12 Join Types - Every pattern demonstrated
Complex Queries - Multi-table joins, subqueries, CTEs
Aggregations - SUM, COUNT, AVG, GROUP BY
Window Functions - ROW_NUMBER, PARTITION BY

Business Metrics

From the ingested data:

Metric	Value
Total Revenue	$26,123.13
Average Order	$261.23
Total Orders	100
Total Customers	80
Products Sold	193 items
Top Category	Home & Kitchen
Order Date Range	Jan 15 - Apr 23, 2024
Status Breakdown	59% Delivered, 21% Processing, 20% Shipped

Implementation Approach

Step 1: Data Generation

Generated 5 CSV files with realistic e-commerce data maintaining referential integrity.

Step 2: Schema Design

Designed normalized schema (3NF) with proper relationships, constraints, and data types.

Step 3: Ingestion Script

Built Python script to create database and import data with validation.

Step 4: SQL Analysis

Created comprehensive SQL queries demonstrating all join types and patterns.

Step 5: Documentation

Wrote detailed docs explaining schema design, relationships, and usage.

How to Verify Everything Works

1. Check CSV Files

Get-ChildItem datasets/*.csv | ForEach-Object { "$($_.Name): $((Get-Content $_.FullName).Count - 1) rows" }

Expected:

categories.csv: 10 rows
customers.csv: 80 rows
products.csv: 100 rows
orders.csv: 100 rows
order_items.csv: 193 rows

2. Run Ingestion

python scripts/ingest_data.py

Look for:

[OK] messages for each import
[PASS] for integrity checks
Business metrics at end

3. Query Database

sqlite3 DB\ecommerce.db "SELECT COUNT(*) FROM customers;"
# Should return: 80

sqlite3 DB\ecommerce.db "SELECT SUM(total_amount) FROM orders;"
# Should return: 26123.13

4. Test Relationships

# Try to delete a category with products (should fail)
sqlite3 DB\ecommerce.db "DELETE FROM categories WHERE category_id = 1;"
# Error: FOREIGN KEY constraint failed

# Delete an order (items should CASCADE delete)
sqlite3 DB\ecommerce.db "DELETE FROM orders WHERE order_id = 1;"
# Success - and line items auto-deleted

Common Tasks

View All Tables

.tables

See Table Schema

.schema products

Check Data

SELECT * FROM categories;
SELECT name, email FROM customers LIMIT 5;

Run Complex Query

-- Customer spending by category
SELECT 
    c.name,
    cat.category_name,
    SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories cat ON p.category_id = cat.category_id
GROUP BY c.customer_id, cat.category_id
ORDER BY total_spent DESC
LIMIT 10;

Design Decisions

Why SQLite?

No server setup needed
Single file database
Standard SQL support
Good for demos/projects

Why Surrogate Keys?

Emails/names can change (stability)
Integer joins faster than strings
Simpler foreign key relationships

Why Store Subtotal?

Historical accuracy (prices change)
Query performance (no recalc)
Audit compliance

Why CASCADE on order_items?

Line items can't exist without order
Auto-cleanup keeps DB clean
Makes sense logically (receipt lines)

Potential Extensions

Want to expand this? Here are ideas:

Add Indexes - Speed up queries on FKs
Add Views - Common queries as views
Add Triggers - Auto-update timestamps
More Data - Generate 1000+ records
More Tables - Reviews, inventory, shipping
API Layer - REST API with Flask/FastAPI
Analytics - More complex queries, reports
Tests - Unit tests for ingestion script

Troubleshooting

"File not found" error

# Make sure you're in the right directory
Get-Location  # Should be E:\Task

"Module not found" error

# Check Python version
python --version  # Need 3.7+

# Script uses only standard library (csv, sqlite3, os)

Database locked

# Close any open SQLite connections
# Or delete and recreate database
Remove-Item DB\ecommerce.db
python scripts\ingest_data.py

Foreign key constraint failed

# This is expected! It means integrity is working.
# Can't delete parents with children (RESTRICT)
# Can't insert children without parents

Tech Details

Language: Python 3.7+
Database: SQLite 3
Data Format: CSV (UTF-8)
Dependencies: None (standard library only)
OS: Windows (but works on Linux/Mac too)

Files:

5 CSV files (datasets)
1 SQLite DB (Database)
1 Python script (scripts)
1 SQL file (sql_query)
2 MD files (db_design)
1 Template (prompts)

Total Lines of Code: ~800 (Python + SQL)
Total Documentation: ~2000 lines (Markdown)

Learning Resources

From this project, you'll learn:

Database normalization (1NF → 3NF)
Foreign key relationships
Referential integrity
SQL join patterns
Python SQLite integration
CSV data handling
Schema design
Query optimization

Good for:

Database course projects
Interview prep
Portfolio piece
Learning SQL

Approach:

Prompt-driven development
Chain-of-Thought prompting
Iterative refinement

Quick Reference

File Locations

datasets/       → Raw CSV data
Database/       → SQLite .db file
scripts/        → Python ingestion
sql_query/      → SQL queries
db_design/      → Documentation
prompts/        → Templates

Quick Commands

# Run ingestion
python scripts\ingest_data.py

# Open database
sqlite3 DB\ecommerce.db

# Run queries
sqlite3 DB\ecommerce.db < sql_query\analysis_query.sql

# Check files
Get-ChildItem datasets/

Test Results & Verified Outputs

✅ Database Ingestion Works

Ran the ingestion script and got all 483 records imported successfully:

============================================================
E-COMMERCE DATABASE INGESTION SCRIPT
============================================================
Successfully connected to database: DB\ecommerce.db

[OK] Categories table created
[OK] Customers table created
[OK] Products table created
[OK] Orders table created
[OK] Order_items table created

[OK] Successfully imported 10 rows into 'categories'
[OK] Successfully imported 80 rows into 'customers'
[OK] Successfully imported 100 rows into 'products'
[OK] Successfully imported 100 rows into 'orders'
[OK] Successfully imported 193 rows into 'order_items'

Total rows imported: 483

[PASS] All products have valid category references
[PASS] All orders have valid customer references
[PASS] All order items have valid order references
[PASS] All order items have valid product references

Business Insights:
  Total Revenue: $26,123.13
  Average Order Value: $261.23
  Most Popular Category: Home & Kitchen (46 items sold)
============================================================

Query Testing

Top Customers by Spending:

SELECT c.name, COUNT(o.order_id) as orders, SUM(o.total_amount) as spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY orders DESC, spent DESC
LIMIT 5;

Results:

Kevin Campbell    | 2 orders | $949.96
Jason Collins     | 2 orders | $849.97
Raymond Howard    | 2 orders | $809.95
James Taylor      | 2 orders | $799.96
Mark Green        | 2 orders | $779.95

Revenue by Category:

Sports & Outdoors  |  25 orders  | $8,759.74
Home & Kitchen     |  27 orders  | $7,129.54
Electronics        |  21 orders  | $5,273.42
Beauty & Personal  |   9 orders  | $2,354.39

Cross-Platform Compatible

Works on Windows/Mac/Linux because:

Uses only Python standard library (no pip installs)
Uses os.path.join() for cross-platform paths
SQLite is built into Python 3.x everywhere
CSV module handles different line endings automatically

Tested on: Windows 10/11 ✅
Expected to work: Linux, macOS, WSL ✅

Performance

Schema creation: <0.1s
Import 483 rows: ~0.5s
Complex 4-table joins: <0.1s
Total runtime: ~1 second

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
DB		DB
datasets		datasets
db_design		db_design
prompts		prompts
scripts		scripts
sql_query		sql_query
.gitignore		.gitignore
README.md		README.md
requirements.txt		requirements.txt

Folders and files

Latest commit

History

Repository files navigation