Practice: Design a Library Database

Put the whole design process together. A library lends books to members. Each book has a title and an author; a book can have several copies; a member can borrow many copies over time, and a copy is borrowed by one member at a time.

Entities: Author, Book, Copy, Member, Loan. Notice that Loan is the link between Member and Copy, and carries the dates. Build it with foreign keys, load some data, and ask questions with joins.

Examplepython

import sqlite3

db = sqlite3.connect(':memory:')

db.execute('PRAGMA foreign_keys = ON')
db.executescript('''
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER NOT NULL REFERENCES authors(id));
CREATE TABLE copies (id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES books(id));
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE loans (id INTEGER PRIMARY KEY, copy_id INTEGER NOT NULL REFERENCES copies(id),
                    member_id INTEGER NOT NULL REFERENCES members(id), out_on TEXT NOT NULL, back_on TEXT);
INSERT INTO authors VALUES (1, 'Achebe'), (2, 'Adichie');
INSERT INTO books VALUES (1, 'Things Fall Apart', 1), (2, 'Americanah', 2);
INSERT INTO copies VALUES (1, 1), (2, 1), (3, 2);
INSERT INTO members VALUES (1, 'Ama'), (2, 'Kofi');
INSERT INTO loans VALUES (1, 1, 1, '2026-10-01', '2026-10-15'), (2, 3, 1, '2026-10-20', NULL), (3, 2, 2, '2026-10-22', NULL);
''')
print(db.execute('''SELECT m.name, b.title FROM loans l
                    JOIN members m ON m.id = l.member_id
                    JOIN copies c ON c.id = l.copy_id
                    JOIN books b ON b.id = c.book_id
                    WHERE l.back_on IS NULL ORDER BY m.name''').fetchall())

Open in editor »

Quick check

Which table links members and copies, carrying the dates?

loansauthorsbooks

Your turn: write the code

Count the copies of each book title (join books and copies) and print a list of (title, copies) ordered by title.

Finish the quick check (and any code exercise) to complete this lesson, or mark it done yourself.

Tip: use the left and right arrow keys to move between lessons.