Database Fundamentals Workshop

A complete, hands-on demo repository to teach Database Fundamentals for Software Engineering.

Repository purpose

This repo demonstrates:

• Basic SQL (SQLite) schema + queries
• A minimal backend (Flask) exposing GET/POST endpoints
• ORM usage with SQLAlchemy
• Raw SQL examples showing safe parameterization vs vulnerable string concatenation
• How to view and export the database
• A hands-on workshop challenge with a rubric
• Optional perf & indexing demo

Repository structure

soda-db-workshop/
├── README.txt                      # (this file) workshop guide & instructions
├── requirements.txt                # Python dependencies
├── create_students.sql             # SQL schema + seed data
├── init_db.py                      # Initialize students.db from SQL file
├── students.db                     # (optional) the SQLite DB - typically .gitignored
├── orm_models.py                   # SQLAlchemy models and DB wrapper class
├── server_with_db.py               # Flask server demonstrating GET/POST and raw SQL
├── raw_sql_demo.py                 # Demo: parameterized vs vulnerable SQL
├── view_db.py                      # Pretty-print Students table (pandas fallback)
├── client_examples.sh              # curl examples (for Bash / Git Bash / WSL)
├── client_examples.ps1             # PowerShell examples (for Windows)
├── workshop_challenge.md           # Challenge prompt + rubric for attendees
├── .gitignore
└── README.txt      

Quick setup (Linux / macOS / WSL)

clone + virtual environment

git clone <repo-url>
cd soda-db-workshop
python -m venv .venv

Activate the venv:

Windows (PowerShell)	.\.venv\Scripts\Activate.ps1
Windows (Command Prompt)	.venv\Scripts\activate
Linux / macOS / WSL (bash/zsh)	source .venv/bin/activate

install deps

pip install -r requirements.txt

create DB

python init_db.py

run server (Flask)

python server_with_db.py

Server will run at: http://127.0.0.1:8000

Quick setup (Windows PowerShell)

Open PowerShell in repo folder:

# create & activate venv
python -m venv .venv
# activate
.\.venv\Scripts\Activate.ps1   # or: . .venv\Scripts\Activate.ps1

# install
pip install -r requirements.txt

# initialize DB
python init_db.py

# run server
python server_with_db.py

PowerShell: Using client examples

We included a PowerShell helper: client_examples.ps1. Run it after starting the server:

.\client_examples.ps1

This will:

• POST /students with a sample student
• GET /students?gpa_min=3.6
• Show /raw-query demonstrations (safe and unsafe)

Files and purpose

• create_students.sql
  • Schema for Students table and seed data.
• init_db.py
  • Reads create_students.sql and creates students.db in the repo.
• orm_models.py
  • SQLAlchemy declarative models (Student) and DB wrapper class (DB).
  • Contains methods: add_students(list), get_students_with_min_gpa(gpa)
• server_with_db.py
  • Flask app exposing:
    • GET / -> index
    • POST /students -> insert JSON student
    • GET /students -> list students (filter by gpa_min)
    • GET /raw-query -> demo parameterized vs unsafe SQL (unsafe only for demo)
• raw_sql_demo.py
  • Demonstrates sqlite3 parameterized queries vs vulnerable concatenation
  • Shows a simulated malicious input and why concatenation is unsafe
• view_db.py
  • Prints Students table in a readable tabular format (pandas if available, fallback to sqlite)
• client_examples.sh
  • curl examples for Bash / WSL
• client_examples.ps1
  • PowerShell examples for Windows
• populate_db.py & measure_perf.py
  • Tools to generate many records and measure indexing performance
• workshop_challenge.md
  • Challenge prompt & rubric for participants
• .gitignore
  • excludes .venv and students.db (recommended to create DB at runtime)

How to use the server

.:Examples:.

Insert a student (Bash / Git Bash)

curl -X POST http://127.0.0.1:8000/students \
  -H "Content-Type: application/json" \
  -d '{"name":"Lina Park","major":"AI","gpa":3.7}'

Query students (Bash)

curl "http://127.0.0.1:8000/students?gpa_min=3.6"

Parameterized raw query (safe)

curl "http://127.0.0.1:8000/raw-query?gpa_min=3.6"

Unsafe concatenation demo (do not use in prod)

curl "http://127.0.0.1:8000/raw-query?gpa_min=3.6&unsafe=1"

PowerShell equivalents are in client_examples.ps1

How to view the DB table

Option A — view_db.py (recommended)

python view_db.py

Option B — using sqlite3 CLI (Git Bash / WSL)

sqlite3 students.db
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM Students;

Option C — GUI

• DB Browser for SQLite: https://sqlitebrowser.org
• VS Code: "SQLite" extension (open students.db and browse tables)

Troubleshooting (common issues)

• "sqlite3.OperationalError: database is locked": close other processes, restart server, or use a smaller dataset.
• Windows PowerShell: if script execution is blocked, run: Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned
• If pandas read_sql_query fails, ensure you pass a connection or use sqlite:///students.db as URL.
• If Flask not found: ensure venv activated and pip install -r requirements.txt ran successfully.