Week 6 Assignment

📝 Overview

Remember the Bank Account System from Week 5? Every time you quit the program, all your accounts vanished. This week you'll fix that.

You'll add a SQLite database layer to the existing codebase so that accounts and every transaction are stored permanently in a file called bank.db. When the program starts, it reads the database and picks up exactly where it left off.

• SQLite: a lightweight, file-based database that ships built into Python — no installation required
• SQL basics: CREATE TABLE, INSERT, UPDATE, SELECT, and WHERE clauses
• Parameterized queries: the safe way to pass Python values into SQL statements
• Refactoring: adding a new capability to existing, working code without breaking anything

The Week 5 OOP layer (Account classes, Bank class, menu) is already written in the scaffolding file. Your only job is to fill in the database pieces.

🎯 Learning Objectives

By completing this assignment, you'll be able to:

1. Connect to a SQLite database file using Python's built-in sqlite3 module
2. Create tables with CREATE TABLE IF NOT EXISTS
3. Insert and update rows using parameterized ? placeholders
4. Query rows with SELECT, WHERE, ORDER BY, and LIMIT
5. Reconstruct Python objects from raw database rows
6. Add persistence to an existing application without changing its interface

🗄️ Database Design

You will create two tables in bank.db:

accounts

Column	Type	Notes
account_number	TEXT	PRIMARY KEY (e.g. "ACC-4821")
owner_name	TEXT	NOT NULL
account_type	TEXT	"checking" or "savings"
balance	REAL	DEFAULT 0.0
withdrawal_count	INTEGER	DEFAULT 0 (used by SavingsAccount)
created_at	TEXT	Timestamp string

transactions

Column	Type	Notes
id	INTEGER	PRIMARY KEY AUTOINCREMENT
account_number	TEXT	FOREIGN KEY → accounts
type	TEXT	"deposit", "withdrawal", "fee", "interest"
amount	REAL	NOT NULL
balance_after	REAL	Balance immediately after this transaction
timestamp	TEXT	Timestamp string

📋 Requirements

Building on the Week 5 program, your updated system must:

• Create bank.db automatically on first run
• Save every new account to the database when it is created
• Update the balance in the database after every deposit, withdrawal, fee, or interest event
• Record every transaction in the transactions table
• Load all accounts from the database when the program starts
• Display the last 10 transactions for any account in reverse-chronological order
• Persist the savings withdrawal count so the fee applies correctly even after a restart

🚀 Suggested Implementation Order

The scaffolding file has 17 TODO markers. Work through them in this order:

1. TODO 1 — Add the DATABASE_FILE constant
2. TODOs 2–7 — Build the Database class method by method; after each one, add a quick test at the bottom of the file to confirm it works
3. TODOs 8–11 — Update Bank.__init__ and create_account() to use the database; restart the program and verify accounts reload
4. TODOs 12–16 — Hook up update_balance() and save_transaction() in the menu; implement the transaction history view
5. TODO 17 — Add the entry point and run the full test checklist

📁 Files in Your Assignment

• bank_v2.py — your starting point with the Week 5 OOP layer complete and 17 database TODO markers
• cheat_sheet.py — SQLite reference patterns: connecting, creating tables, inserting, querying, updating, and more

💡 Tips for Success

1. SQLite is built into Python — no install needed. import sqlite3 and you're ready to go.
2. Inspect your database visually. Download DB Browser for SQLite (free) to open bank.db and browse your tables like a spreadsheet while you develop. This makes debugging much faster.
3. Always commit() after writes. Without a commit, inserts and updates are rolled back when the connection closes and nothing is saved.
4. Use ? placeholders, never f-strings in SQL. Formatting user input directly into a SQL string is a security vulnerability called SQL injection.
5. Delete bank.db to start fresh. If your table schema gets into a bad state during development, just delete the file and let the program recreate it.
6. Verify persistence is working. Open an account, deposit some money, quit the program, reopen it — if the account and balance are still there, it's working.

🎮 Example Session

=== Welcome to First National Bank ===
Loaded 2 account(s) from database.

1. Open new account
2. Deposit funds
3. Withdraw funds
4. Check balance
5. View transaction history
6. List all accounts
7. Apply monthly fee / interest
8. Quit

Choose an option: 5
Account number: ACC-4821
Transaction history for ACC-4821:
  Timestamp             Type         Amount     Balance
  ----------------------------------------------------------
  2026-02-28 14:01:00   interest     $10.00     $510.00
  2026-02-28 14:00:00   deposit      $500.00    $500.00