Global Harvest Imports — Enterprise Relational Database · 3NF · MySQL

type

Database Design

status

Completed

year

2024

role

Database Architect

01 —

System Architecture · 3D View

02 —

Architecture Diagram

Requirements

5 Departments

↓

DFD

Data Flow Diagram

↓

ERD

7 Entities · FK Map

↓

Normalization

1NF → 2NF → 3NF

↓

schema.sql

Tables · Indexes

seed_data.sql

Realistic Test Data

↓

queries.sql

5 Analytical Queries

MySQL Database

Deployed · Indexed

03 —

Screenshots & Output

terminal

$ mysql -u root -p global_harvest < schema.sql

Query OK — Suppliers · Categories · Products ✓

Query OK — Customers · Orders · Order_Items · Shipments ✓

Indexes created: idx_products_supplier, idx_orders_date...

$ mysql -u root -p global_harvest < seed_data.sql

5 suppliers · 7 products · 5 customers · 5 orders ✓

$ mysql > SOURCE queries.sql

Top revenue: Basmati Rice $60,000 · Cardamom $54,000 ✓

SQL Schema Output

CREATE TABLE statements with constraints

Table Normalization

3NF Compliance100%

FK Integrity100%

Index Coverage90%

Query Perf.85%

Normalization100%

Table Normalization

3NF compliance per table

Data Output

{
# schema.sql — 7 tables in 3NF
tables: [Suppliers, Categories, Products, Customers, Orders...],
normalization: 3NF,
generated_col: Order_Items.subtotal,
indexes: [supplier_id, order_date, product_id]
}

Schema Definition

Table + FK structure JSON

Project Structure

📁 global-harvest-imports/

├─ schema.sql 7 tables · FK · indexes

├─ seed_data.sql Sample data

├─ queries.sql 5 analytical queries

└─ erd_notes.md ERD · 3NF rationale

Project Files

schema · seed · queries · erd docs

04 —

What I Built

›

Designed a 7-table 3NF MySQL schema: Suppliers, Categories, Products, Customers, Orders, Order_Items, Shipments — with proper FK constraints and indexes.

›

Built Order_Items as a junction table with a GENERATED subtotal column (MySQL 5.7+) to avoid update anomalies and enforce data integrity.

›

Applied full normalization pipeline: 1NF (atomic attributes) → 2NF (full functional dependency) → 3NF (no transitive dependencies).

›

Wrote 5 analytical SQL queries: revenue by product, customer order history, supplier performance, monthly trend, and low-stock alerts.

›

Executed ERD and DFD design phases to map 5-department requirements (procurement, sales, logistics, finance, operations) into a logical data model.

›

Created seed data with realistic international suppliers (India, Brazil, Australia, Sri Lanka, Canada) and customers across 5 countries.

05 —

Project Insights

✎Personal Notes & Learnings

Markdown Editor

### Design Process

Full **requirements → production-ready schema** pipeline for a multi-department international import company.

### Files Delivered

- `schema.sql` — 7 CREATE TABLE statements, FK constraints, performance indexes
- `seed_data.sql` — realistic data: 5 suppliers (4 countries), 7 products, 5 customers, 5 orders
- `queries.sql` — 5 analytical queries: revenue ranking, supplier performance, monthly trends, low-stock alerts, customer history
- `erd_notes.md` — entity list, relationships, normalization rationale

### Key Design Decisions

- `subtotal` in Order_Items is a **GENERATED column** — computed automatically, no update anomaly possible
- **ENUM for order status** (`pending → confirmed → shipped → delivered → cancelled`) enforces valid state machine
- Separate Categories table prevents transitive dependency in Products — satisfies 3NF strictly
- **Composite UNIQUE** on `(order_id, product_id)` prevents duplicate line items

### What I Learned

- Stakeholder communication is 50% of database design — requirements gathering drives everything
- ERD-first approach prevents costly schema refactors downstream

Live Preview

Design Process

Full requirements → production-ready schema pipeline for a multi-department international import company.

Files Delivered

schema.sql — 7 CREATE TABLE statements, FK constraints, performance indexes
seed_data.sql — realistic data: 5 suppliers (4 countries), 7 products, 5 customers, 5 orders
queries.sql — 5 analytical queries: revenue ranking, supplier performance, monthly trends, low-stock alerts, customer history
erd_notes.md — entity list, relationships, normalization rationale

Key Design Decisions

subtotal in Order_Items is a GENERATED column — computed automatically, no update anomaly possible
ENUM for order status (pending → confirmed → shipped → delivered → cancelled) enforces valid state machine
Separate Categories table prevents transitive dependency in Products — satisfies 3NF strictly
Composite UNIQUE on (order_id, product_id) prevents duplicate line items

What I Learned

Stakeholder communication is 50% of database design — requirements gathering drives everything
ERD-first approach prevents costly schema refactors downstream

✓ Insights saved locally

← PreviousFormal Language Typing Game

All Projects

Next →Indoor Scene Recognition