Databases Project
Library Management System
A library system is the classic database project because the rules are familiar and the constraints are genuinely interesting: a copy cannot be lent twice at once, a member has a borrowing limit, and a return must be recorded against the right loan. Getting the schema right makes the application code almost trivial.
The brief
Model books, copies, members and loans. Enforce the rules in the database rather than in application code, write the reporting queries a librarian would ask for, and index them so they stay fast.
What it demonstrates
That you can model a domain and use the database to enforce correctness instead of hoping the application does.
What "done" looks like
Build all of these and the project is finished. Anything past that is in the stretch goals.
- Books, copies, members and loans, correctly separated
- Constraints preventing a copy being lent twice at once
- A borrowing limit enforced by the database
- Returns recorded against the correct loan
- Reporting queries — overdue, most borrowed, member history
- Indexes making those queries fast
How to build it
- 1
Separate the book from the copy
A title is not a physical item. Loans reference a copy. Getting this right is most of the project.
- 2
Normalise the schema
Authors, publishers and categories as their own tables. Then check each is earning its place.
- 3
Add the constraints
Foreign keys, NOT NULL, and a unique partial index stopping two open loans on one copy.
- 4
Model the loan lifecycle
Borrowed, returned, overdue. Decide whether a return is a new row or an update, and be consistent.
- 5
Write the reporting queries
Overdue loans, most-borrowed titles, a member history. Joins and aggregation, which is the SQL that matters.
- 6
Index for those queries
Read the query plan first, add indexes second. Guessing at indexes is how tables get slow to write.
- 7
Try to break it
Attempt the illegal states — double loan, over limit, return of a copy never borrowed. The database should refuse.
Once it works
Only after the definition of done is met. Half-finished ambition reads worse than a small finished thing.
- Add reservations with a queue per title
- Add fine calculation with a configurable daily rate
- Put a thin web UI or REST API in front of it
Frequently Asked Questions
Why enforce rules in the database?
Because application code is not the only thing that writes to it — scripts, migrations, a second service and a person with a SQL client all bypass your validation. A constraint holds regardless of who is writing, which is the entire argument for a relational database.
What is the difference between a book and a copy?
A book is the title; a copy is the physical item on the shelf. Conflating them is the classic modelling mistake here — you cannot lend a title, only a copy, and the loan must reference the copy. Spotting this is most of the project.
Do I need a UI?
Not for the database to be the point. A CLI or a handful of scripts is enough to demonstrate the schema. If you add a UI, keep it thin — the interesting work is in the schema and the queries.