Module 07 Projects - Databases

These projects are engineering specifications, not tutorials. Each spec defines exactly what the finished system must do - it is your job to figure out how to build it. Read every requirement carefully before writing a single line of code.

By the end of this module you will have built three independent, production-flavored database-backed systems, each targeting a distinct layer of the database engineering stack: raw SQL with sqlite3, ACID transactions with PostgreSQL, and a full ORM-backed REST API with SQLAlchemy.

Project Summary

#	Project	Database	Key Skills	Difficulty
01	Library Management System	SQLite (sqlite3)	Schema design, parameterized queries, foreign keys, transactions, row factories, CLI	Intermediate
02	Transaction-Safe Payment Service	PostgreSQL (psycopg2) / SQLite fallback	ACID transfers, idempotency keys, deadlock retry, audit logging, thread safety	Intermediate–Advanced

What These Projects Test

Project 01 - Library Management System

A command-line application backed by SQLite. You will design and enforce a relational schema without an ORM, write every query by hand, and handle the full lifecycle of books, members, and loans.

Skills assessed:

• Designing normalized schemas with constraints (NOT NULL, UNIQUE, FOREIGN KEY)
• Writing parameterized queries - no string interpolation in SQL, ever
• Using row_factory to access columns by name instead of index
• Wrapping multi-step operations (loan, return) in explicit transactions
• Surfacing meaningful errors when database constraints are violated
• Building a clean Data Access Layer (DAL) that separates SQL from application logic

Project 02 - Transaction-Safe Payment Service

A payment processing back-end that enforces ACID guarantees across concurrent transfers. You will implement idempotency so that retried requests never double-charge, add exponential-backoff deadlock handling, and maintain a complete audit trail.

Skills assessed:

• Full ACID transaction composition (BEGIN → lock → debit → credit → COMMIT)
• Idempotency key pattern - detecting duplicate requests at the database level
• Deadlock detection and retry with exponential backoff
• Audit logging - recording every transfer attempt regardless of outcome
• Check constraints to enforce business invariants (non-negative balance) at the database layer
• Thread-safe connection management (connection-per-thread or connection pool)

How to Approach Each Project

1. Read the entire spec before writing any code.
2. Sketch the schema on paper (or in a diagram tool).
3. Create the database and verify your schema with a REPL or DB browser.
4. Build the Data Access Layer functions one at a time, testing each in isolation.
5. Wire up the CLI or service layer only after the DAL is solid.
6. Run the acceptance criteria as a checklist - every item must pass.
7. Attempt at least one extension challenge.

Ground Rules

• No raw f-string SQL. Every value that comes from outside your code must travel through a query parameter (? for SQLite, %s for psycopg2).
• No ORM in Projects 01 and 02. The point is to understand what an ORM does before you use one.
• Errors must be catchable. Callers of your DAL functions should receive meaningful Python exceptions, not raw database driver errors.
• Schema changes go in migration scripts, not in application startup code (after the initial CREATE TABLE phase).

Folder Structure (Recommended)

project/
├── db/
│   ├── schema.sql          # DDL - CREATE TABLE statements
│   └── migrations/         # future schema changes
├── dal/
│   ├── __init__.py
│   ├── books.py            # one file per entity (Project 01)
│   ├── members.py
│   └── loans.py
├── services/
│   └── loan_service.py     # business logic that spans multiple DAL calls
├── cli.py                  # entry point (Project 01)
├── payment_service.py      # entry point (Project 02)
└── tests/
    └── test_dal.py

This structure is a recommendation, not a hard requirement. What is required is that SQL never leaks into your CLI or service layer.