Case Study 1: Building a Library Catalog

Overview

A library catalog exercises the full range of database programming concepts: multiple related tables, CRUD operations, search, and reporting. We build a complete catalog application with SQLite, demonstrating proper database design, parameterized queries, and data-aware controls.

Database Schema

CREATE TABLE authors (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  birth_year INTEGER,
  nationality TEXT
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL,
  isbn TEXT UNIQUE,
  year_published INTEGER,
  genre TEXT,
  copies_total INTEGER DEFAULT 1,
  copies_available INTEGER DEFAULT 1,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE borrowings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  book_id INTEGER NOT NULL,
  borrower_name TEXT NOT NULL,
  borrow_date TEXT NOT NULL DEFAULT (date('now')),
  due_date TEXT NOT NULL,
  return_date TEXT,
  FOREIGN KEY (book_id) REFERENCES books(id)
);

Three tables with two foreign key relationships. The borrowings table tracks who has borrowed which book. When return_date is NULL, the book is still checked out.

Key Operations

Search by Title or Author

procedure TfrmLibrary.SearchBooks(const SearchTerm: string);
begin
  FQuery.Close;
  FQuery.SQL.Text :=
    'SELECT b.id, b.title, a.name as author, b.year_published, ' +
    '       b.genre, b.copies_available ' +
    'FROM books b JOIN authors a ON b.author_id = a.id ' +
    'WHERE b.title LIKE :search OR a.name LIKE :search ' +
    'ORDER BY b.title';
  FQuery.Params.ParamByName('search').AsString := '%' + SearchTerm + '%';
  FQuery.Open;
end;

Check Out a Book

procedure TfrmLibrary.CheckOutBook(BookID: Integer; const Borrower: string);
var
  Q: TSQLQuery;
begin
  Q := TSQLQuery.Create(nil);
  try
    Q.Database := FConn;
    Q.Transaction := FTrans;

    { Verify copies are available }
    Q.SQL.Text := 'SELECT copies_available FROM books WHERE id = :id';
    Q.Params.ParamByName('id').AsInteger := BookID;
    Q.Open;
    if Q.FieldByName('copies_available').AsInteger <= 0 then
    begin
      ShowMessage('No copies available for checkout.');
      Q.Close;
      Exit;
    end;
    Q.Close;

    { Create borrowing record }
    Q.SQL.Text :=
      'INSERT INTO borrowings (book_id, borrower_name, due_date) ' +
      'VALUES (:bookid, :name, date(''now'', ''+14 days''))';
    Q.Params.ParamByName('bookid').AsInteger := BookID;
    Q.Params.ParamByName('name').AsString := Borrower;
    Q.ExecSQL;

    { Decrement available copies }
    Q.SQL.Text :=
      'UPDATE books SET copies_available = copies_available - 1 WHERE id = :id';
    Q.Params.ParamByName('id').AsInteger := BookID;
    Q.ExecSQL;

    FTrans.Commit;
    FQuery.Refresh;
    StatusBar1.Panels[0].Text := 'Book checked out to ' + Borrower;
  finally
    Q.Free;
  end;
end;

Overdue Books Report

procedure TfrmLibrary.ShowOverdueBooks;
begin
  FReportQuery.Close;
  FReportQuery.SQL.Text :=
    'SELECT b.title, a.name as author, br.borrower_name, ' +
    '       br.borrow_date, br.due_date, ' +
    '       julianday(''now'') - julianday(br.due_date) as days_overdue ' +
    'FROM borrowings br ' +
    'JOIN books b ON br.book_id = b.id ' +
    'JOIN authors a ON b.author_id = a.id ' +
    'WHERE br.return_date IS NULL AND br.due_date < date(''now'') ' +
    'ORDER BY br.due_date ASC';
  FReportQuery.Open;
end;

Lessons Learned

1. Multiple related tables with foreign keys provide a natural, normalized data model.
2. Parameterized queries are used for every user-facing operation.
3. SQLite date functions (date('now'), julianday()) handle date arithmetic.
4. Transactions ensure that checkout operations (insert borrowing + decrement copies) succeed or fail atomically.
5. JOIN queries combine data from multiple tables for display and reporting.