What it shows
Three linked tables for books, members and loans answer questions such as who has which book, using a JOIN. It shows how real databases avoid repeating data.
The science
A relational database stores data in tables. Each table has a primary key, a column whose values are unique, and other tables refer to it with a foreign key. Keeping members and books in their own tables means a name is stored once, and the loans table holds only their ids. A JOIN brings the rows back together by matching the ids.
Materials
Cost: free. Many items come from home or from waste; spare parts such as LEDs, motors and wire are in the store.
- A computer, laptop or phone with a browser (free)
- DB Browser for SQLite (free and open source) or a free online SQLite page that runs in the browser
- Made-up data: 8 books, 6 members and 8 loans
- A notebook and a pencil
Safety
This project uses safe, everyday materials. Still, read the rules below with a parent before you start.
Never use mains electricity: power models with cells, batteries or a low-voltage adapter. Sharp and hot tools, flames and lab chemicals need an adult or your teacher beside you. Keep magnets, button cells and small parts away from young children, and wash your hands after making.
All the safety rulesStep by step
8 steps. Do them in order, and test each part before you go on to the next. Write what you notice in a notebook as you go.
Open DB Browser for SQLite, choose New Database, and open the Execute SQL tab.
Create the tables by running CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT); and CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT, class TEXT);.
Create the third table with CREATE TABLE loans (id INTEGER PRIMARY KEY, book_id INTEGER, member_id INTEGER, due TEXT, returned INTEGER);.
Insert data with INSERT INTO books (title, author) VALUES ("Panchatantra Tales", "Various"); and add seven more, then six members and eight loans with returned set to 0 or 1.
Run SELECT * FROM books; to check each table and fix mistakes with UPDATE.
List who has which book: SELECT members.name, books.title, loans.due FROM loans JOIN members ON loans.member_id = members.id JOIN books ON loans.book_id = books.id WHERE loans.returned = 0;.
Count loans per member with SELECT members.name, COUNT(*) FROM loans JOIN members ON loans.member_id = members.id GROUP BY members.name;.
Find overdue loans with WHERE returned = 0 AND due < "2026-10-01" and note why dates are written year first.
What you should see
The queries show who holds which book with the due date, how many loans each member has, and which are overdue, all from three linked tables.
Viva questions
A teacher or a judge will ask how it works. Try to answer each question before you open it, then compare.
1What is a primary key?
A column whose values uniquely identify each row.
2What is a foreign key?
A column that holds the primary key of a row in another table.
3What does JOIN do?
It combines rows of two tables where the matching columns are equal.
4Why write dates as year first?
So that alphabetical order matches date order.
Take it further
Add a fine column and a query that calculates fines of Rs 2 per day using julianday for the dates.
Change one thing, measure what happens, and add a table or chart of your readings: that is what turns a model into your own investigation.
Free ebooks for this project
Programming
Databases and SQL Study Book
The complete study notes, practice questions and glossary for Databases and SQL