Building my first database with SQL: a library project

Derick Lopes — Thu, 20 Aug 2026 12:22:44 +0000

As a Computer Science student, one thing I've noticed is that learning a concept in theory is very different from actually using it to solve a problem.

That's what led me to build a small project to practice relational databases and SQL: a management system for a university library.

The idea behind the project

The goal was to build a database capable of storing information about books, authors, categories, students, and loans.

At first glance, it seems simple. But as soon as you start thinking about how that data relates to itself, more interesting questions come up: can a book have more than one author? Can an author write several books? Can a student borrow more than one book at a time? How do you know which books are currently on loan?

These questions turned an apparently simple problem into a small data-modeling exercise.

Modeling the data

The main entities were Authors, Categories, Books, Students, and Loans. On top of that, I needed an intermediate table, Book_Author, since a book can have several authors and an author can be involved in several books — a many-to-many relationship.

AUTHOR → BOOK_AUTHOR → BOOK ↔ CATEGORY → LOAN → STUDENT

That was one of the first lessons of this project: before writing a single line of SQL, you need to think about the data you want to represent and how it connects.

_Creating the database
_
I chose PostgreSQL. First, I created the database:
CREATE DATABASE library;

Authors

CREATE TABLE authors (
    id             SERIAL PRIMARY KEY,
    name           VARCHAR(150) NOT NULL,
    nationality    VARCHAR(100)
);

PRIMARY KEY uniquely identifies each record; NOT NULL makes a field mandatory.

Categories

CREATE TABLE categories (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL UNIQUE
);

UNIQUE prevents the same category from being registered more than once.

Books

CREATE TABLE books (
    id               SERIAL PRIMARY KEY,
    title            VARCHAR(200) NOT NULL,
    isbn             VARCHAR(20) UNIQUE,
    publication_year INTEGER,
    category_id      INTEGER NOT NULL,

    CONSTRAINT fk_book_category
        FOREIGN KEY (category_id)
        REFERENCES categories(id)
);

Here comes the foreign key: category_id links books and categories. Instead of repeating the text "Programming" in every book, the table only stores the id of the matching category, which avoids unnecessary repetition.

Linking books and authors

CREATE TABLE book_author (
    book_id    INTEGER NOT NULL,
    author_id  INTEGER NOT NULL,

    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id)    REFERENCES books(id),
    FOREIGN KEY (author_id)  REFERENCES authors(id)
);

With a composite key made of book_id + author_id, the same book-author pair can't be registered twice — the database enforces that on its own.

Students

CREATE TABLE students (
    id             SERIAL PRIMARY KEY,
    name           VARCHAR(150) NOT NULL,
    email          VARCHAR(150) NOT NULL UNIQUE,
    registered_at  DATE NOT NULL DEFAULT CURRENT_DATE
);

DEFAULT CURRENT_DATE automatically fills in the registration date when none is provided.

Loans

CREATE TABLE loans (
    id           SERIAL PRIMARY KEY,
    student_id   INTEGER NOT NULL,
    book_id      INTEGER NOT NULL,
    loaned_at    DATE NOT NULL DEFAULT CURRENT_DATE,
    returned_at  DATE,

    FOREIGN KEY (student_id)  REFERENCES students(id),
    FOREIGN KEY (book_id)     REFERENCES books(id)
);

The logic here is simple: when returned_at has a value, the book has already been returned; when it's NULL, the loan is still open.

Inserting the first records

INSERT INTO authors (name, nationality) VALUES
    ('Robert C. Martin', 'American'),
    ('Martin Fowler', 'British'),
    ('Andrew S. Tanenbaum', 'Dutch'),
    ('Erich Gamma', 'Swiss');

INSERT INTO categories (name) VALUES
    ('Programming'), ('Databases'),
    ('Software Engineering'), ('Operating Systems');

INSERT INTO books (title, isbn, publication_year, category_id) VALUES
    ('Clean Code', '9780132350884', 2008, 1),
    ('Refactoring', '9780134757599', 2018, 3),
    ('Computer Networks', '9780132126953', 2010, 4),
    ('Design Patterns', '9780201633610', 1994, 3);

INSERT INTO book_author (book_id, author_id) VALUES
    (1, 1), (2, 2), (3, 3), (4, 4);

INSERT INTO students (name, email) VALUES
    ('Ana Silva', 'ana@example.com'),
    ('Carlos Souza', 'carlos@example.com'),
    ('Mariana Oliveira', 'mariana@example.com');

INSERT INTO loans (student_id, book_id, loaned_at) VALUES
    (1, 1, '2026-08-10'),
    (2, 2, '2026-08-12'),
    (3, 3, '2026-08-15');

Starting to query the data
Once the data was in place, SQL became a lot more interesting — because now there were real questions to answer.

Which books are registered?

SELECT * FROM books;

Which books were published after 2010?

SELECT title, publication_year
FROM books
WHERE publication_year > 2010;

What category does each book belong to?

SELECT
    books.title,
    categories.name AS category
FROM books
JOIN categories
    ON books.category_id = categories.id;

That query was a turning point. It made me understand, in practice, why relationships between tables matter so much. A database doesn't need to store everything in a single table — data can be split up and then combined on demand, exactly when the right question comes along.

What I learned from this project

The main takeaway was that SQL isn't just about memorizing commands. At first, I assumed learning SQL would mostly mean memorizing SELECT, INSERT, UPDATE, and DELETE. Building an actual database showed me there's a more important step that comes first: understanding and modeling the problem.

Before writing CREATE TABLE, you need to ask yourself:

What information needs to be stored?
What entities exist?
How do they relate to one another?
What data might repeat — and what shouldn't?
What's required?
What uniquely identifies each record?

Another important lesson was seeing the real role of primary and foreign keys. I already knew these concepts in theory, but actually using them made it much clearer how they hold the relationships between data together. I also started to see JOIN differently — not just as another command, but as a way to ask questions that span different parts of the database.

Next steps
This project is still far from being a complete system. Some topics I plan to study next:

Different types of JOIN
GROUP BY and aggregate functions
Subqueries
Indexes
Normalization
Transactions
Rules to prevent invalid loans

It would be interesting, for example, to be able to answer questions like: which books are currently on loan? Which student has the most loans? Which category has the most books? Which books have never been borrowed? Which authors have the most books registered?

Conclusion
This project started as a simple way to practice SQL, but it ended up showing me that working with databases involves much more than writing queries. The most interesting part was seeing the connection between modeling, relationships, and queries.

There's still a lot to learn, but building small projects like this seems like a far more effective way to consolidate knowledge than studying commands in isolation. Maybe that's exactly the point of learning to program: starting without knowing exactly how to do something, building it anyway, running into problems, researching, making mistakes, fixing them, and gradually understanding what's actually going on.

If you're also getting started with SQL, I hope this small project can serve as a starting point for your own experiments. The next step is turning questions into queries.

DEV Community: Derick Lopes

Building my first database with SQL: a library project