Project — Library Management System

• Design a normalised three-table schema with foreign keys.
• Enforce foreign keys (off by default in SQLite — turn them on).
• Wrap every read/write in a function.
• Build a clear CLI that uses those functions.

-- books are independent
CREATE TABLE books (
    id     INTEGER PRIMARY KEY AUTOINCREMENT,
    title  TEXT NOT NULL,
    author TEXT NOT NULL,
    year   INTEGER
);

-- members are independent
CREATE TABLE members (
    id    INTEGER PRIMARY KEY AUTOINCREMENT,
    name  TEXT NOT NULL,
    joined TEXT DEFAULT CURRENT_TIMESTAMP
);

-- loans bridge them
CREATE TABLE loans (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    book_id   INTEGER NOT NULL REFERENCES books(id),
    member_id INTEGER NOT NULL REFERENCES members(id),
    borrowed_at TEXT DEFAULT CURRENT_TIMESTAMP,
    returned_at TEXT
);

Connection + foreign-key pragma

import sqlite3
from pathlib import Path

DB = Path("library.db")

def get_conn():
    con = sqlite3.connect(DB)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA foreign_keys = ON")   # critical!
    return con

SQLite disables foreign-key checks by default. The pragma must be set per-connection. Without it, you can insert book_id = 9999 into loans with no error.

Functions per verb

def add_book(title, author, year):
    with get_conn() as con:
        return con.execute(
            "INSERT INTO books (title, author, year) VALUES (?,?,?)",
            (title, author, year),
        ).lastrowid

def add_member(name):
    with get_conn() as con:
        return con.execute(
            "INSERT INTO members (name) VALUES (?)", (name,)
        ).lastrowid

def borrow(book_id, member_id):
    with get_conn() as con:
        # Reject if the book is already on loan
        row = con.execute(
            "SELECT id FROM loans WHERE book_id = ? AND returned_at IS NULL",
            (book_id,),
        ).fetchone()
        if row:
            return None
        return con.execute(
            "INSERT INTO loans (book_id, member_id) VALUES (?,?)",
            (book_id, member_id),
        ).lastrowid

def return_book(loan_id):
    with get_conn() as con:
        return con.execute(
            "UPDATE loans SET returned_at = CURRENT_TIMESTAMP "
            "WHERE id = ? AND returned_at IS NULL",
            (loan_id,),
        ).rowcount

Reporting queries

def books_on_loan():
    with get_conn() as con:
        return [dict(r) for r in con.execute("""
            SELECT b.title, b.author, m.name AS borrower, l.borrowed_at
            FROM loans l
            JOIN books   b ON b.id = l.book_id
            JOIN members m ON m.id = l.member_id
            WHERE l.returned_at IS NULL
            ORDER BY l.borrowed_at
        """)]

def member_history(member_id):
    with get_conn() as con:
        return [dict(r) for r in con.execute("""
            SELECT b.title, l.borrowed_at, l.returned_at
            FROM loans l
            JOIN books b ON b.id = l.book_id
            WHERE l.member_id = ?
            ORDER BY l.borrowed_at DESC
        """, (member_id,))]

Schema init

def init():
    with get_conn() as con:
        con.executescript("""
            CREATE TABLE IF NOT EXISTS books (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              title TEXT NOT NULL, author TEXT NOT NULL, year INTEGER
            );
            CREATE TABLE IF NOT EXISTS members (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name TEXT NOT NULL,
              joined TEXT DEFAULT CURRENT_TIMESTAMP
            );
            CREATE TABLE IF NOT EXISTS loans (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              book_id INTEGER NOT NULL REFERENCES books(id),
              member_id INTEGER NOT NULL REFERENCES members(id),
              borrowed_at TEXT DEFAULT CURRENT_TIMESTAMP,
              returned_at TEXT
            );
        """)

# library_cli.py
from library_db import (init, add_book, add_member, borrow, return_book,
                        books_on_loan, member_history)

init()

MENU = """
1) add book
2) add member
3) borrow
4) return
5) books on loan
6) member history
7) quit
"""

def ask_int(prompt):
    try: return int(input(prompt).strip())
    except ValueError: return None

while True:
    print(MENU)
    c = input("> ").strip()
    if c == "1":
        bid = add_book(input("  title : "), input("  author: "),
                       ask_int("  year  : "))
        print(f"  📚 added book #{bid}")
    elif c == "2":
        mid = add_member(input("  name: "))
        print(f"  👤 added member #{mid}")
    elif c == "3":
        bid = ask_int("  book id  : ")
        mid = ask_int("  member id: ")
        loan = borrow(bid, mid)
        print(f"  ✅ loan #{loan}" if loan else "  ❌ already on loan")
    elif c == "4":
        lid = ask_int("  loan id: ")
        print("  ✅ returned" if return_book(lid) else "  ❌ no open loan")
    elif c == "5":
        for r in books_on_loan():
            print(f"  · {r['title']} → {r['borrower']} ({r['borrowed_at']})")
    elif c == "6":
        for r in member_history(ask_int("  member id: ")):
            mark = "✓" if r["returned_at"] else "·"
            print(f"  {mark} {r['title']}  borrowed {r['borrowed_at']}")
    elif c == "7":
        break

You built a complete three-table application with foreign keys, a bridge table, and pure-SQL reporting queries. The CLI is a thin shell over the data module. PRAGMA foreign_keys = ON is the single line that turns SQLite from a toy into a database that enforces your invariants. Next week we shift gears to Pandas.