codeseed
Sign in
Explore Projects
SQLbeginnerbackendAI generated

Library Lending System with Overdue Fines

Design a relational schema for a library that tracks books, members, and loans, then write queries to compute overdue fines based on due dates and daily rates. Learners practice schema design, foreign keys, date arithmetic, and conditional aggregation.

Estimate
~7.5h
Steps
5
Completed by
0
Proposed by
codeseed.app

PostgreSQL · pgAdmin

Sign in to claim this project

Project roadmap

  1. 01

    Design the schema

    ~1.5h

    Create tables for books, members, and loans with appropriate primary and foreign keys.

  2. 02

    Insert sample data

    ~1h

    Populate the tables with realistic sample books, members, and loan records.

  3. 03

    Query currently overdue loans

    ~1.5h

    Write a query that lists all loans past their due date and not yet returned.

  4. 04

    Compute overdue fines

    ~2h

    Use date arithmetic and CASE expressions to calculate fines based on days overdue and a daily rate.

  5. 05

    Report most-borrowed books

    ~1.5h

    Write a GROUP BY query ranking books by number of times borrowed.

Resources

Ready to build this?

Get a GitHub repo and start building. Your AI reviewer checks each step as you go.

~7.5h · 5 steps

Tech stack

PostgreSQLpgAdmin