Capstone — Build a Real Database-Backed System Capstone

2. The Four Tracks

3. Track A — Mini SQL Engine (Spec)

Goal. Write a working, single-file SQL engine that supports a useful subset of the language.

Required features

1. CREATE TABLE name(col TYPE, col TYPE PRIMARY KEY, ...) — TYPE in {INT, TEXT}.
2. INSERT INTO name VALUES (...) — single-row at minimum.
3. SELECT col, ... FROM name [WHERE cond] [ORDER BY col [DESC]] [LIMIT n]
4. Persistence: each table backed by a file on disk (e.g. JSON-lines or a custom binary format).
5. One B-tree index per primary key, used to speed up WHERE pk = ?.
6. REPL with a read-eval-print loop; multi-line statements terminated by ;.

Stretch goals

• UPDATE and DELETE.
• Support a single inner JOIN (nested-loop is fine).
• Print an EXPLAIN for any query before executing it.
• Add BEGIN/COMMIT/ROLLBACK with an in-memory transaction layer.

4. Track B — Full-Stack Postgres App (Spec)

Goal. Ship a small but real database-backed web application — properly modeled, properly queried, properly secured.

Required features

1. Pick a domain (mini Daraz, library, gym, freelance marketplace, anything you can describe in two sentences).
2. ER diagram → 3NF Postgres schema, as a numbered set of migration files.
3. At least 5 user-facing pages; every read/write goes through parameterized SQL.
4. Transactions wrap any multi-statement workflow (e.g. checkout, refund, return).
5. Indexes designed deliberately, not by accident — document each one.
6. Authentication with hashed passwords (bcrypt or argon2) and a least-privilege DB user.
7. Daily backup + a one-page runbook for restore.

Stretch goals

• Add a read-replica and route reports to it.
• Add full-text search on one entity (Postgres tsvector).
• Deploy to a free tier (Railway, Render, Fly.io, Supabase).

-- A starter library schema you can build on. Runs in our in-browser SQLite.
CREATE TABLE member(
    id            INTEGER PRIMARY KEY,
    name          TEXT NOT NULL,
    email         TEXT NOT NULL UNIQUE,
    joined_at     TEXT NOT NULL
);
CREATE TABLE book(
    id            INTEGER PRIMARY KEY,
    isbn          TEXT NOT NULL UNIQUE,
    title         TEXT NOT NULL,
    author        TEXT NOT NULL,
    total_copies  INTEGER NOT NULL CHECK(total_copies >= 0)
);
CREATE TABLE loan(
    id          INTEGER PRIMARY KEY,
    member_id   INTEGER NOT NULL REFERENCES member(id),
    book_id     INTEGER NOT NULL REFERENCES book(id),
    borrowed_at TEXT NOT NULL,
    due_at      TEXT NOT NULL,
    returned_at TEXT
);
CREATE INDEX idx_loan_member ON loan(member_id);
CREATE INDEX idx_loan_open   ON loan(returned_at) WHERE returned_at IS NULL;

INSERT INTO member VALUES
 (1,'Arif','arif@univ.bd','2025-08-01'),
 (2,'Mim','mim@univ.bd','2025-09-12');
INSERT INTO book VALUES
 (1,'978-0','Database Internals','A. Petrov',2),
 (2,'978-1','Designing Data-Intensive Applications','M. Kleppmann',3);
INSERT INTO loan(id,member_id,book_id,borrowed_at,due_at) VALUES
 (1,1,1,'2026-04-25','2026-05-09'),
 (2,2,2,'2026-04-30','2026-05-14');

-- Example query: every book currently on loan, with the borrower.
SELECT b.title, m.name AS borrower, l.due_at
FROM   loan l
JOIN   book   b ON b.id = l.book_id
JOIN   member m ON m.id = l.member_id
WHERE  l.returned_at IS NULL;

5. Track C — Analytics Dashboard (Star Schema)

Goal. Take a real public dataset and turn it into an analytics-ready warehouse with dashboards.

Suggested datasets

• Bangladesh Open Data — population, education, weather.
• NYC Taxi trips (subset).
• Olist Brazilian e-commerce orders (Kaggle).
• Your own university's exam results (with permission & PII removed).

Required features

1. Design a star schema: 1 fact table + 3–5 dimension tables.
2. Loader script that ingests raw CSV / JSON into the schema (idempotent).
3. 5 analytical queries — each using at least one of: window function, CTE, GROUP BY, multi-join.
4. Dashboard: Metabase / Superset / Looker Studio with at least 5 widgets.
5. Document each query: what it answers, how long it takes, what index it uses.

6. Track D — Tiny Key-Value Store

Goal. Build a tiny but durable KV store, see how RocksDB and Cassandra think.

Required features

1. API: set(key, value), get(key), delete(key), scan(prefix).
2. In-memory memtable (sorted map). Periodically flushed to disk as an SSTable.
3. Write-ahead log so crashes do not lose acknowledged sets.
4. Background compaction that merges multiple SSTables and drops tombstoned keys.
5. Benchmark: 1 M random writes/sec target on commodity hardware (achievable with batching).

7. Deliverables — Same For Every Track

1. README.md — what, why, how to run, screenshots.
2. Schema — version-controlled migration files / formal schema docs.
3. Seed data — at least one realistic seed dataset and a script that loads it.
4. Tests — at minimum a few integration tests of the critical paths.
5. Benchmarks / EXPLAIN — for any query or operation in the hot path.
6. One-page architecture write-up — what you chose, what you didn't, why.
7. Public repo — push to GitHub. This is the artifact you show off.

8. Self-Review Rubric