Library Database – SQL Project for Beginners

Project Overview

The library system needs to track:

Books — title, author, ISBN, genre, publication year, and how many copies the library owns.
Members — people who have library cards, with contact details and join date.
Loans — which member borrowed which book, when, and whether it was returned.

Schema Design

Three tables, two foreign key relationships:

Table	Primary Key	Key Columns	Foreign Keys
books	id	title, author, isbn, genre, published_year, copies_available	—
members	id	name, email, join_date	—
loans	id	loan_date, return_date, returned	book_id → books, member_id → members

Create Tables

SQL – DDL

CREATE TABLE books (
  id                INT PRIMARY KEY,
  title             VARCHAR(200)    NOT NULL,
  author            VARCHAR(150)    NOT NULL,
  isbn              VARCHAR(20)     UNIQUE NOT NULL,
  genre             VARCHAR(50),
  published_year    INT,
  copies_available  INT             NOT NULL DEFAULT 1,
  CHECK (copies_available >= 0)
);

CREATE TABLE members (
  id        INT PRIMARY KEY,
  name      VARCHAR(100)  NOT NULL,
  email     VARCHAR(150)  UNIQUE NOT NULL,
  join_date DATE          NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE loans (
  id          INT PRIMARY KEY,
  book_id     INT     NOT NULL,
  member_id   INT     NOT NULL,
  loan_date   DATE    NOT NULL DEFAULT CURRENT_DATE,
  return_date DATE,           -- expected return date
  returned    BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY (book_id)   REFERENCES books(id)   ON DELETE RESTRICT,
  FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE RESTRICT
);

-- Index the FK columns for faster JOIN performance
CREATE INDEX idx_loans_book_id   ON loans(book_id);
CREATE INDEX idx_loans_member_id ON loans(member_id);

Insert Sample Data

SQL – Sample Data

-- 10 Books
INSERT INTO books (id, title, author, isbn, genre, published_year, copies_available) VALUES
  (1, 'The Pragmatic Programmer', 'David Thomas', '9780135957059', 'Technology',    2019, 3),
  (2, 'Clean Code',               'Robert Martin','9780132350884', 'Technology',    2008, 2),
  (3, 'Dune',                     'Frank Herbert', '9780441013593', 'Science Fiction',1965,4),
  (4, 'To Kill a Mockingbird',    'Harper Lee',    '9780061935466', 'Fiction',       1960, 2),
  (5, 'Sapiens',                  'Yuval Harari',  '9780062316097', 'Non-Fiction',   2011, 3),
  (6, 'The Great Gatsby',         'F. Scott Fitzgerald','9780743273565','Fiction',   1925, 1),
  (7, 'Designing Data-Intensive Applications','Martin Kleppmann','9781449373320','Technology',2017,2),
  (8, 'Atomic Habits',            'James Clear',   '9780735211292', 'Non-Fiction',   2018, 4),
  (9, 'Foundation',               'Isaac Asimov',  '9780553293357', 'Science Fiction',1951,2),
  (10,'1984',                     'George Orwell', '9780451524935', 'Fiction',       1949, 3);

-- 5 Members
INSERT INTO members (id, name, email, join_date) VALUES
  (1, 'Alice Johnson', 'alice@example.com',  '2022-01-15'),
  (2, 'Bob Smith',     'bob@example.com',    '2022-06-20'),
  (3, 'Carol White',   'carol@example.com',  '2023-03-10'),
  (4, 'Dave Brown',    'dave@example.com',   '2023-07-05'),
  (5, 'Eve Davis',     'eve@example.com',    '2024-01-22');

-- 8 Loans
INSERT INTO loans (id, book_id, member_id, loan_date, return_date, returned) VALUES
  (1,  1, 1, '2024-11-01', '2024-11-15', TRUE),
  (2,  3, 1, '2024-11-20', '2024-12-04', FALSE),  -- Alice still has Dune
  (3,  5, 2, '2024-10-10', '2024-10-24', TRUE),
  (4,  7, 2, '2024-11-15', '2024-11-29', FALSE),  -- Bob still has DDIA
  (5,  2, 3, '2024-09-01', '2024-09-15', TRUE),
  (6,  8, 3, '2024-11-25', '2024-12-09', FALSE),  -- Carol still has Atomic Habits
  (7,  1, 4, '2024-11-05', '2024-11-19', TRUE),
  (8, 10, 5, '2024-10-01', '2024-10-15', TRUE);

Operational Queries

Find all available books (with copies remaining)

SQL

SELECT id, title, author, genre, copies_available
FROM books
WHERE copies_available > 0
ORDER BY genre, title;

Currently borrowed books (not yet returned)

SQL

SELECT
  b.title,
  b.author,
  m.name          AS borrowed_by,
  l.loan_date,
  l.return_date   AS due_date,
  CURRENT_DATE - l.return_date AS days_overdue
FROM loans l
JOIN books   b ON b.id = l.book_id
JOIN members m ON m.id = l.member_id
WHERE l.returned = FALSE
ORDER BY l.return_date;

Overdue loans (past return date, not yet returned)

SQL

SELECT
  m.name        AS member,
  m.email,
  b.title,
  l.return_date AS due_date,
  CURRENT_DATE - l.return_date AS days_overdue
FROM loans l
JOIN books   b ON b.id = l.book_id
JOIN members m ON m.id = l.member_id
WHERE l.returned = FALSE
  AND l.return_date < CURRENT_DATE
ORDER BY days_overdue DESC;

Member borrow history

SQL

SELECT
  m.name          AS member,
  b.title,
  b.genre,
  l.loan_date,
  l.return_date,
  CASE WHEN l.returned THEN 'Returned' ELSE 'On Loan' END AS status
FROM loans l
JOIN books   b ON b.id = l.book_id
JOIN members m ON m.id = l.member_id
WHERE m.id = 1   -- change to the member_id you want
ORDER BY l.loan_date DESC;

Reports

Most popular books (by loan count)

SQL

SELECT
  b.title,
  b.author,
  COUNT(l.id)  AS total_loans,
  SUM(CASE WHEN l.returned = FALSE THEN 1 ELSE 0 END) AS currently_on_loan
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.id, b.title, b.author
ORDER BY total_loans DESC;

Most active members (by books borrowed)

SQL

SELECT
  m.name,
  m.email,
  COUNT(l.id)    AS total_loans,
  MAX(l.loan_date) AS last_loan_date
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name, m.email
ORDER BY total_loans DESC;

Summary

📋 Summary

Built a 3-table library schema: books, members, loans with proper FK constraints.
Added CHECK constraint to prevent negative copies_available; indexed FK columns.
Inserted 10 books, 5 members, and 8 loan records with mixed returned/outstanding status.
Wrote queries to list available books, find currently borrowed items, and identify overdue loans.
Built reports for most popular books and most active members using LEFT JOIN + COUNT + GROUP BY.
Key SQL used: CREATE TABLE with FK + CHECK, INSERT, SELECT + JOIN + WHERE, COUNT + GROUP BY + ORDER BY.

FAQ

How do I update copies_available when a book is loaned or returned? +

You have two options. Option A: update copies_available in your application code every time you INSERT into loans or UPDATE loans SET returned = TRUE. Option B: use a TRIGGER that fires AFTER INSERT on loans (decrement) and AFTER UPDATE on loans (increment when returned = TRUE). For a learning project, Option A is more explicit and easier to understand. In production, Option B enforces consistency regardless of how the data is modified.

What does ON DELETE RESTRICT do? +

ON DELETE RESTRICT prevents deletion of a referenced row if child rows exist. So you cannot delete a book from the books table if that book has loan records. This protects data integrity. Alternatives: ON DELETE CASCADE (automatically delete child rows), ON DELETE SET NULL (set FK column to NULL). For this library system, RESTRICT makes sense — you don't want to accidentally delete book records that have loan history.

How would I add a feature to reserve books? +

Add a reservations table: id, book_id, member_id, reserved_at, expires_at, fulfilled. When a member reserves a book, insert a row. When they collect it, INSERT into loans and mark the reservation as fulfilled. Add a query to show pending reservations per book so staff can notify members when their book is available. This is a good extension exercise.

What's a good next project after this? +

The E-Commerce Schema project is a natural next step — it introduces more tables, multi-level foreign key relationships, and more complex analytical queries. After that, the Analytics Queries project covers advanced SQL patterns like cohort analysis, retention, moving averages, and funnel analysis using window functions.