Document a small database project for a portfolio

A database project becomes easier to assess when another learner can rebuild it and explain the same results. A screenshot of a populated table shows that something ran. It does not explain the relationships, the rules that reject bad data or why a query returns the rows it does.

This guide uses a fictional book-loan database as a documentation exercise. The titles and records are invented for practice. It is not a student success story or a production lending system. The deliverable is a small evidence packet that connects a question, a schema, a repeatable setup and the result you expected.

State the question and the limits

Begin with one sentence: “Which books currently have an open loan, including books with no open loans?” That question gives the reviewer something specific to check. Keep reservations, fines, member accounts and notifications outside this exercise. Naming those limits is more useful than presenting an unfinished collection of features as a complete library application.

Choose a database engine and record the version you actually used. The example below uses SQLite for a small local exercise; it is not a claim about the software taught in a particular course. If you adapt it to MySQL or another engine, check the syntax and behaviour again instead of describing the change as a simple file rename.

Make a map of the evidence

GitHub's README guidance explains the role of a repository introduction in showing what a project does and how to get started. For this project, let the introduction point to evidence rather than repeat every line of SQL.

Keep file names consistent with the instructions. A reviewer should not need to guess whether “final-query-new.sql” replaced the file mentioned in the README. If you use an application around the database, explain which checks belong to the application and which the database itself enforces.

Explain the relationship before showing the query

Use a book table with a book identifier and title. Use a separate loan table with a loan identifier, a book identifier and a returned flag. For this exercise, zero means open and one means returned. These are deliberately small design choices, not a recommended data model for every lending service.

The loan's book identifier references an existing book. SQLite's foreign-key documentation describes how this relationship can be enforced and why applications should explicitly enable enforcement for each connection. Set PRAGMA foreign_keys = ON; before starting the exercise transaction, and check that it is enabled. A diagram alone does not enforce a relationship.

Explain the other rules too: titles cannot be null, a loan must identify a book, and the returned flag accepts only the values selected for the exercise. Keep a separate note for rules you have not implemented, such as preventing more than one simultaneous open loan for a book. Do not claim that a foreign key enforces that different rule.

Choose data that exposes the important case

Create two fictional books, “Gate” and “Sky”. Give Gate one open loan and leave Sky without a loan. The expected answer must include both books. This small dataset exposes a useful mistake: a query that only joins matching loan rows can hide the book with no loan.

SELECT b.book_id, b.title,
       COUNT(l.loan_id)
         AS open_loans
FROM book AS b
LEFT JOIN loan AS l
  ON l.book_id = b.book_id
 AND l.returned = 0
GROUP BY b.book_id, b.title
ORDER BY b.book_id;

With book identifiers one and two respectively, the expected open-loan counts are one for Gate and zero for Sky. The join condition limits matching loans to open ones while retaining each book. Counting the loan identifier counts those matches; explain that choice alongside the expected rows.

Record useful failures

Add an attempted loan for a book identifier that is absent from the seed data. With foreign-key enforcement enabled, that insert should be rejected. Also try a returned flag outside the allowed values. Run these checks in a disposable local database and record the observed outcome without leaving the failed examples mixed into the successful setup script.

Next, mark Gate's loan as returned and rerun the query. Both books should now show no open loans. Record the setup, action and expected result for that case. Avoid claiming that a test passed merely because the SQL editor showed no error: compare the returned rows and confirm the stored state.

Rebuild from a clean starting point

Ask a second reader to follow the README, or follow it yourself in a fresh disposable database. Record the engine version and the order in which files run. The packet should work without a private file, an unexplained manual edit or a password copied from your machine.

Use synthetic data throughout. A portfolio does not need real customer names, borrowing histories or connection secrets. If an AI tool suggests a query or explanation, verify it against your chosen engine and the expected rows. Keep the final explanation in words you can defend during a review.

Common questions

Do I need a large dataset?

No. Start with a small dataset that exposes a meaningful relationship and an edge case. Add volume only when you have a separate question about performance or scale.

Can I include screenshots?

Yes, as supporting evidence. Keep the executable setup and query files alongside them so another reader can reproduce the result rather than rely on an image.

What should I explain in a portfolio discussion?

Explain the question, one schema choice, one rejected input and one limitation. Distinguish your own work from any library, tutorial or assistance used.

Explore the database course and Data Structures and Algorithm course. Bring your evidence packet to a free demo discussion and ask how database reasoning receives feedback.