What it shows
A school or a mohalla library often keeps records in a register, and books get lost. A small relational database with books, members and loans tables lets a student issue, return and search books and see overdue lists, which teaches design with primary and foreign keys.
The science
A relational database stores each kind of thing in its own table to avoid repeating data. The loans table links a book id and a member id with issue and return dates through foreign keys. SQL queries such as JOIN and GROUP BY then answer questions like who has book 12 or which books are overdue. Constraints stop errors, such as issuing a book that is already out.
Materials
Cost: free. Many items come from home or from waste; spare parts such as LEDs, motors and wire are in the store.
- A laptop with Python 3 (the sqlite3 module is built in)
- DB Browser for SQLite (free) to inspect the tables
- A text editor or Thonny
- A list of 50 real or sample books, 20 sample members in a CSV
- A notebook for the entity relationship diagram
Safety
For this project: Use only sample or consented member data, not real students’ personal details. Keep a backup of the database file.
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
10 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.
Draw an entity relationship diagram with the tables books, members and loans and mark primary and foreign keys.
Write the CREATE TABLE statements and run them from Python to create library.db.
Load the sample books and members from CSV files with a short import script.
Write functions add_book, add_member, issue_book and return_book that use parameterised queries to avoid SQL injection.
Make issue_book check that the book is available and that the member has fewer than three books out.
Add search by title, author and subject using LIKE and print the results in a neat table.
Write a report query that lists overdue books with the number of days late and a fine at Rs 2 per day.
Build a simple menu in the terminal so that a librarian can choose the actions by number.
Test with at least 20 cases, including wrong input, and record the results in a table.
Write the report with the diagram, table designs, queries, screenshots and ideas for improvement.
What you should see
The program issues and returns books, refuses invalid operations, searches quickly and produces an overdue list with fines. The database file can be opened in DB Browser to check that all records are consistent.
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 that uniquely identifies each row in a table.
2Why are parameterised queries used?
They keep user input separate from the SQL command and stop SQL injection attacks.
3What does JOIN do?
It combines rows from two tables that are related by matching key values.
4Why not keep everything in one table?
It would repeat member and book details in every loan row and cause update errors.
Take it further
Add a Tkinter or Flask front end so that a librarian can use mouse and forms instead of the terminal.
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