Homework #4: Relational Databases and NoSQL Data Modeling

EE 547: Fall 2026

Assignment Details

Assigned: 6 October
Due: Monday, 19 October at 23:59

Gradescope: Homework 4 | How to Submit

Requirements

Docker Desktop must be installed and running on your machine
Python 3.11+ required
AWS CLI configured with valid credentials
Use only Python standard library modules unless explicitly permitted

Overview

This assignment covers relational databases (PostgreSQL) and NoSQL systems (DynamoDB). You will design schemas, load data, and implement queries against both.

Getting Started

Download the starter code: hw4-starter.zip

unzip hw4-starter.zip
cd hw4-starter

Problem 1: Metro Transit Database (Relational SQL)

Design and query a relational database for a city metro transit system tracking lines, stops, trips, and ridership.

Use only psycopg2-binary (PostgreSQL adapter for Python) and Python standard library modules (json, sys, os, csv, datetime, argparse). Do not use ORMs (SQLAlchemy, Django ORM), query builders, or pandas. Write raw SQL.

Part A: Schema Design

Design a database schema from these entity descriptions.

Entities:

Lines - Transit routes (e.g., Route 2, Route 20)
- Has: name, vehicle type
Stops - Stop locations
- Has: name, latitude, longitude
Line Stops - Which stops each line serves, in order
- Links line to stops with sequence number and time offset from start
- A stop can be on multiple lines (transfer stations)
Trips - Scheduled vehicle runs
- Has: line, departure time, vehicle ID
Stop Events - Actual arrivals during trips
- Has: trip, stop, scheduled time, actual time, passengers on/off

Relationships:

“A line has many stops (ordered)” becomes the line_stops table
“A trip runs on one line” becomes a foreign key
“A trip has many stop events” becomes a foreign key

Create schema.sql defining all five tables. You must decide:

Primary keys (natural vs surrogate keys)
Foreign key constraints
CHECK constraints (e.g., passengers_on >= 0)
UNIQUE constraints where appropriate

Starter for schema.sql:

CREATE TABLE lines (
    line_id SERIAL PRIMARY KEY,  -- or use line_name as natural key?
    line_name VARCHAR(50) NOT NULL UNIQUE,
    vehicle_type VARCHAR(10) CHECK (vehicle_type IN ('rail', 'bus'))
);

CREATE TABLE stops (
    -- Your design here
);

CREATE TABLE line_stops (
    -- Your design here
    -- Must handle: line_id, stop_id, sequence_number, time_offset_minutes
);

-- Continue for trips and stop_events

Part B: Data Loading

Provided CSV files (in data/):

lines.csv - 11 routes
stops.csv - 227 stops
line_stops.csv - 231 line-stop pairs
trips.csv - 1,155 trips, one week
stop_events.csv - 24,255 events

CSV Formats:

lines.csv:

line_name,vehicle_type
Route 2,bus
Route 20,bus

stops.csv:

stop_name,latitude,longitude
Wilshire / Veteran,34.057616,-118.447888
Le Conte / Broxton,34.063594,-118.446732

line_stops.csv:

line_name,stop_name,sequence,time_offset
Route 20,Wilshire / Veteran,1,0
Route 20,Le Conte / Broxton,2,5

trips.csv:

trip_id,line_name,scheduled_departure,vehicle_id
T0001,Route 2,2026-10-01 06:00:00,V101

stop_events.csv:

trip_id,stop_name,scheduled,actual,passengers_on,passengers_off
T0001,Le Conte / Broxton,2026-10-01 06:00:00,2026-10-01 06:00:00,29,0
T0001,Le Conte / Westwood,2026-10-01 06:02:00,2026-10-01 06:02:00,25,29

Create load_data.py that:

Connects to PostgreSQL
Runs schema.sql to create tables
Loads CSVs in correct order (handle foreign keys)
Reports statistics

Your script must accept the data directory as an argument (default: /app/data):

python load_data.py --datadir data/

Connection parameters default to the values in docker-compose.yaml (same as queries.py).

Example output:

Connected to transit@db
Creating schema...
Tables created: lines, stops, line_stops, trips, stop_events

Loading data/lines.csv... 11 rows
Loading data/stops.csv... 227 rows
Loading data/line_stops.csv... 231 rows
Loading data/trips.csv... 1,155 rows
Loading data/stop_events.csv... 24,255 rows

Total: 25,879 rows loaded

Part C: Query Implementation

Create queries.py that implements 10 SQL queries.

Your script must support:

python queries.py Q1 --format json
python queries.py all

Connection parameters default to the values in docker-compose.yaml (--host db, --dbname transit, --user transit, --password transit123). Override with flags when running outside compose:

python queries.py Q1 --host localhost --dbname transit --user transit --password transit123

Required Queries:

Q1: List all stops on Route 20 in order

-- Output: stop_name, sequence, time_offset
SELECT ...
FROM line_stops ls
JOIN lines l ON ...
JOIN stops s ON ...
WHERE l.line_name = 'Route 20'
ORDER BY ls.sequence;

Q2: Trips during morning rush (scheduled departure at or after 07:00 and before 09:00)

-- Output: trip_id, line_name, scheduled_departure

Q3: Transfer stops (stops on 2+ routes)

-- Output: stop_name, line_count
-- Uses: GROUP BY, HAVING

Q4: Complete route for trip T0001

-- Output: All stops for specific trip in order
-- Multi-table JOIN

Q5: Routes serving both Wilshire / Veteran and Le Conte / Broxton

-- Output: line_name

Q6: Average ridership by line

-- Output: line_name, avg_passengers
-- avg_passengers = AVG(passengers_on) over the line's stop_events

Q7: Top 10 busiest stops

-- Output: stop_name, total_activity
-- total_activity = SUM(passengers_on + passengers_off)

Q8: Count delays by line (more than 10 minutes late)

-- Output: line_name, delay_count
-- WHERE actual > scheduled + interval '10 minutes'

Q9: Trips with 3+ delayed stops (delayed as in Q8)

-- Output: trip_id, delayed_stop_count
-- Uses: Subquery or HAVING

Q10: Stops with above-average ridership

-- Output: stop_name, total_boardings
-- total_boardings = SUM(passengers_on); above average = above the mean over all stops

Output format (--format json):

{
  "query": "Q1",
  "description": "Route 20 stops in order",
  "results": [
    {"stop_name": "Wilshire / Veteran", "sequence": 1, "time_offset": 0},
    {"stop_name": "Le Conte / Broxton", "sequence": 2, "time_offset": 5}
  ],
  "count": 2
}

Requirements:

description is a one-line summary of the query, in your words
all prints a JSON array of these objects, one per query, in order
Without --format json, print the rows in any readable form

Part D: Docker Configuration

A Dockerfile, docker-compose.yaml, and requirements.txt are provided in the starter code:

Code: Dockerfile

FROM python:3.11-slim

WORKDIR /app

COPY requirements.txt .
RUN pip install -r requirements.txt

COPY schema.sql load_data.py queries.py ./

CMD ["python", "load_data.py"]

Code: docker-compose.yaml

services:
  db:
    image: postgres:15-alpine
    environment:
      POSTGRES_DB: transit
      POSTGRES_USER: transit
      POSTGRES_PASSWORD: transit123
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U transit -d transit"]
      interval: 2s
      timeout: 3s
      retries: 15

  app:
    build: .
    depends_on:
      db:
        condition: service_healthy
    volumes:
      - ./data:/app/data:ro

  adminer:
    image: adminer:latest
    ports:
      - "8080:8080"
    depends_on:
      - db

volumes:
  pgdata:

Code: requirements.txt

psycopg2-binary>=2.9.0

The compose file defines three services:

db - PostgreSQL 15 database (user: transit, password: transit123, database: transit), with a healthcheck that passes once it accepts connections
app - Your Python scripts, built from the Dockerfile; starts only after the database is healthy
adminer - Web UI at http://localhost:8080 for browsing tables and running queries

Part E: Building and Running

Build the application image and start the database:

docker compose build
docker compose up -d db

The build copies schema.sql, load_data.py and queries.py into the image; create all three before building.

Load the data, then run queries, inside the app container; each run waits until the database is healthy before starting:

docker compose run --rm app python load_data.py --datadir /app/data
docker compose run --rm app python queries.py Q1 --format json

The app service mounts ./data read-only at /app/data and reaches the database at host db on the compose network. Stop everything with docker compose down; add -v to discard the database volume as well.

Part F: Testing

Run every query and inspect the output:

docker compose run --rm app python queries.py all --format json

Browse the tables and try statements interactively at http://localhost:8080 (Adminer; system PostgreSQL, server db, user transit, password transit123, database transit).

Re-run load_data.py after changing schema.sql; it must recreate the tables from scratch.

Deliverables

See Submission. Your README should explain:

Your choice of natural or surrogate keys, and why
The CHECK and UNIQUE constraints you added
Which query was hardest, and why
One example of invalid data your foreign keys prevent
Why the relational model fits this domain

Grading Commands

We will validate your submission by running the following commands from your q1/ directory:

docker compose build
docker compose up -d db
docker compose run --rm app python load_data.py --datadir /app/data
docker compose run --rm app python queries.py all --format json
docker compose down

These commands must complete without errors. We will then verify:

Schema declares foreign key, CHECK and UNIQUE constraints
All 10 queries return correct results
Loading inserts the CSVs in an order the foreign keys accept
Queries execute within 500ms on the provided dataset
README answers all five questions

Problem 2: arXiv Paper Discovery with DynamoDB

Build a paper discovery system on AWS DynamoDB whose table design serves five access patterns, each with one Query.

This problem requires boto3 (AWS SDK for Python) and Python standard library modules (json, sys, os, datetime, re, collections). Do not use other AWS libraries, NoSQL ORMs, or database abstraction layers beyond boto3. AWS CLI must be configured with valid credentials.

Part A: Schema Design for Access Patterns

Design a DynamoDB table schema for these five access patterns:

Browse recent papers by category (the latest cs.LG papers)
Find all papers by a specific author
Get full paper details by arxiv_id
List papers published in a date range within a category
Search papers by keyword (extracted from abstract)

Design Requirements:

Define partition key and sort key for main table
Design Global Secondary Indexes (GSIs) for the access patterns the main table does not serve
Denormalize so that each access pattern is answered by one Query
Document trade-offs in your schema design

Example Schema Structure:

# Main Table Item
{
  "PK": "CATEGORY#cs.LG",
  "SK": "2020-12-21#2012.11510v1",
  "arxiv_id": "2012.11510v1",
  "title": "Paper Title",
  "authors": ["Luis Francisco", "Tanmay Lagare"],
  "abstract": "Full abstract text...",
  "categories": ["cs.LG"],
  "keywords": ["keyword1", "keyword2"],
  "published": "2020-12-21T17:26:31Z"
}

# GSI1: Author access
{
  "GSI1PK": "AUTHOR#Luis Francisco",
  "GSI1SK": "2020-12-21",
  # ... rest of paper data
}

# Additional GSIs as needed for other access patterns

Part B: Data Loading

Create load_data.py that loads arXiv papers from your HW#2 Problem 2 output (papers.json) into DynamoDB.

The starter includes a reference copy of papers.json (ten papers, cat:cs.LG) for testing. Load your own output for the submission; the reference copy is a fallback if yours is unavailable. The file you load is the file you submit under q2/, and the grading commands run against it.

Your script must accept these command line arguments:

python load_data.py <papers_json_path> <table_name> [--region REGION]

Required Operations:

Create DynamoDB table with appropriate partition/sort keys
Create GSIs for alternate access patterns
Transform paper data from HW#2 format to DynamoDB items
Extract keywords from abstracts (top 10 most frequent words, excluding stopwords)
Implement denormalization:
- A paper in multiple categories becomes one item per category
- Multiple authors become one item per author (a GSI key is a single scalar attribute)
- Multiple keywords become one item per keyword
Batch write items to DynamoDB (use batch_write_item)
Report statistics:
- Number of papers loaded
- Total DynamoDB items created
- Denormalization factor (items/paper ratio)

Requirements:

Create the table with on-demand billing (PAY_PER_REQUEST) and wait until it is ACTIVE before writing
A second run against an existing table must not fail: reuse it, or delete and recreate it
batch_write_item takes at most 25 items per call; resend UnprocessedItems until none remain
For keyword counting, a word is any maximal sequence of alphanumeric characters, lowercased; count within each abstract

Example Output:

Creating DynamoDB table: arxiv-papers
Creating GSIs: AuthorIndex, PaperIdIndex, KeywordIndex
Loading papers from papers.json...
Extracting keywords from abstracts...
Loaded 10 papers
Created 185 DynamoDB items (denormalized)
Denormalization factor: 18.5x

Storage breakdown:
  - Category items: 18 (1.8 per paper avg)
  - Author items: 57 (5.7 per paper avg)
  - Keyword items: 100 (10.0 per paper avg)
  - Paper ID items: 10 (1.0 per paper)

Keyword Extraction:

A stopwords.py is provided in the starter code (the base list from HW#2, plus domain-specific terms for academic papers). Use STOPWORDS from this module when filtering keywords.

Code: stopwords.py

"""
Stopwords for keyword extraction.

Base stopwords from HW#2, plus domain-specific terms for academic papers.
"""

STOPWORDS = {'the', 'a', 'an', 'and', 'or', 'but', 'in', 'on', 'at', 'to', 'for',
             'of', 'with', 'by', 'from', 'up', 'about', 'into', 'through', 'during',
             'is', 'are', 'was', 'were', 'be', 'been', 'being', 'have', 'has', 'had',
             'do', 'does', 'did', 'will', 'would', 'could', 'should', 'may', 'might',
             'can', 'this', 'that', 'these', 'those', 'i', 'you', 'he', 'she', 'it',
             'we', 'they', 'what', 'which', 'who', 'when', 'where', 'why', 'how',
             'all', 'each', 'every', 'both', 'few', 'more', 'most', 'other', 'some',
             'such', 'as', 'also', 'very', 'too', 'only', 'so', 'than', 'not'}

DOMAIN_STOPWORDS = {'use', 'using', 'based', 'approach', 'method',
                    'paper', 'propose', 'proposed', 'show', 'our'}

STOPWORDS = STOPWORDS | DOMAIN_STOPWORDS

Part C: Query Implementation

Create query_papers.py that implements queries for all five access patterns.

Your script must support these commands:

# Query 1: Recent papers in category
python query_papers.py recent <category> [--limit 20] [--table TABLE]

# Query 2: Papers by author
python query_papers.py author <author_name> [--table TABLE]

# Query 3: Get paper by ID
python query_papers.py get <arxiv_id> [--table TABLE]

# Query 4: Papers in date range
python query_papers.py daterange <category> <start_date> <end_date> [--table TABLE]

# Query 5: Papers by keyword
python query_papers.py keyword <keyword> [--limit 20] [--table TABLE]

Each query function should use the appropriate DynamoDB operation:

Query 1: Main table partition key query with sort key descending
Query 2: GSI (AuthorIndex) partition key query
Query 3: GSI (PaperIdIndex) for direct lookup
Query 4: Main table with composite sort key range query (between), both dates inclusive
Query 5: GSI (KeywordIndex) partition key query

Output Format:

All queries must output JSON to stdout:

{
  "query_type": "recent_in_category",
  "parameters": {
    "category": "cs.LG",
    "limit": 5
  },
  "results": [
    {
      "arxiv_id": "2012.11510v1",
      "title": "Paper Title",
      "authors": ["Luis Francisco", "Tanmay Lagare"],
      "published": "2020-12-21T17:26:31Z",
      "categories": ["cs.LG"]
    },
    ...
  ],
  "count": 5,
  "execution_time_ms": 12
}

Part D: Testing

A requirements.txt is provided in the starter code:

Code: requirements.txt

boto3>=1.28.0

Install it, load your papers, and run each query:

pip install -r requirements.txt
python load_data.py papers.json arxiv-papers --region us-west-2

python query_papers.py recent cs.LG --limit 5 --table arxiv-papers
python query_papers.py author "Yassien Shaalan" --table arxiv-papers
python query_papers.py get 2012.11510v1 --table arxiv-papers
python query_papers.py daterange cs.LG 2020-12-15 2020-12-22 --table arxiv-papers
python query_papers.py keyword model --limit 10 --table arxiv-papers

# Verify JSON is valid
python query_papers.py recent cs.LG --table arxiv-papers | python -m json.tool > /dev/null

Delete the table when you are done; a table you leave behind is billed for its storage:

aws dynamodb delete-table --table-name arxiv-papers

Deliverables

See Submission. Your README should explain:

Your partition key structure, the GSIs you created, and the trade-offs each denormalization makes
The average number of items per paper, the storage multiplication factor, and which access pattern caused the most duplication
Queries your schema cannot answer with one Query (for example, counting a given author’s papers, or ranking papers globally), and why they are difficult in DynamoDB
When you would choose DynamoDB over PostgreSQL, from this exercise

Grading Commands

We will validate your submission by running the following commands from your q2/ directory:

pip install -r requirements.txt
python load_data.py papers.json arxiv-papers --region us-west-2
python query_papers.py recent cs.LG --limit 5 --table arxiv-papers
python query_papers.py author "Yassien Shaalan" --table arxiv-papers
python query_papers.py get 2012.11510v1 --table arxiv-papers
python query_papers.py daterange cs.LG 2020-12-15 2020-12-22 --table arxiv-papers
python query_papers.py keyword model --limit 10 --table arxiv-papers

These commands must complete without errors. We will then verify:

DynamoDB table and GSIs exist with the key schema the README describes
All five query patterns return correct results
Denormalization is implemented (multiple items per paper)
README answers all four questions

Submission

README.md
q1/
├── schema.sql
├── load_data.py
├── queries.py
├── Dockerfile
├── docker-compose.yaml
├── requirements.txt
└── README.md
q2/
├── load_data.py
├── query_papers.py
├── stopwords.py
├── papers.json
├── requirements.txt
└── README.md