Library Lending System with Overdue Fines
Design a PostgreSQL database for a small library and write the queries librarians use every day: which books are out, which loans are overdue, how much each member owes in late fines, and which books are the most popular. You'll create tables for books, members and loans with the right keys and rules (for example a copy can't be lent twice at once), fill them with sample data, and use date arithmetic and CASE expressions to calculate fines, with a daily rate and a maximum fine per loan. It's a friendly introduction to SQL: you'll learn table design with primary and foreign keys, constraints, joins, working with dates, conditional logic in queries, and grouping.
- Estimate
- ~7h
- Steps
- 6
- Completed by
- 0
- Proposed by
- codeseed.app
PostgreSQL · pgAdmin
Project roadmap
- 01
Design the tables
~1.5hInstall PostgreSQL and pgAdmin. Write schema.sql with books (title, author, ISBN and year), copies (a book can have several physical copies), members (name, email unique, joined date) and loans (copy, member, loaned date, due date and returned date, empty while out). Add foreign keys, a check that the due date is after the loan date, and a partial unique index so a copy can have only one open loan. Done when: the schema loads, and lending the same copy twice fails.
- 02
Add sample data
~1hWrite seed.sql with about 30 books, 50 copies, 20 members and 100 loans spread over the last few months: some returned on time, some returned late, some still out and overdue, and some still out but not yet due. Done when: the data loads, and every kind of loan is present.
- 03
List overdue loans
~1hWrite a query listing loans that are not returned and past their due date, with the member's name and email, the book title, the due date and the number of days overdue (CURRENT_DATE minus the due date), most overdue first. Done when: the query lists exactly the overdue open loans.
- 04
Calculate fines
~1.5hCalculate each loan's fine: days late (using the returned date, or today for open loans) times a daily rate, capped at a maximum, and 0 for loans returned on time, using CASE and LEAST. Then total the unpaid fines per member. Store the rate and maximum in a small settings table instead of hard-coding them. Done when: the fines match hand calculations for a few loans.
- 05
Report popular books
~1hWrite a query ranking books by how many times they've been borrowed (counting across all their copies), with their current availability (copies not out). Save the overdue list, member fines and popular books as views. Done when: the views return sensible results for the sample data.
- 06
Test the queries
~1hWrite test.sql that, inside a transaction, adds loans with known dates (on time, 3 days late, and far past the maximum), checks the fines and overdue list, raising an error with a DO block if they're wrong, then rolls back. Done when: psql -f test.sql runs without errors.
Ready to build this?
Get a GitHub repo and start building. Your AI reviewer checks each step as you go.
Tech stack