Library Management System — PostgreSQL Database

A relational database design and implementation for a library management system, covering schema design, data integrity constraints, reusable query functions, and CRUD stored procedures for all entities.

Entity-Relationship Diagram

See library_erd.pdf for the full ERD, including all primary keys, foreign keys, and relationships.

Entities

The system is built around 8 core entities:

Entity	Description
genre	Book categories (fiction, sci-fi, biography, etc.)
book	The abstract concept of a book — title, genre. Not a physical object.
author	Authors who write books
author_book	Junction table resolving the many-to-many relationship between authors and books (co-authored books, authors with multiple books)
copy	A physical copy of a book — has its own barcode and condition. A single book can have multiple copies on the shelf.
membership_type	Membership tiers (student/regular/premium) that determine how many books a member can borrow at once
library_members	Registered library members
borrow	A transaction record — which copy, which member, borrow date, due date, and return date (null until returned)
fine	Generated only for late returns; linked to the specific borrow record that caused it, not directly to the member

Relationships

• Genre → Book: one genre has many books (1:M)
• Author ↔ Book: many-to-many, resolved via author_book junction table
• Book → Copy: one book has many physical copies (1:M) — copy is modeled as a separate entity (not an attribute of book) so each physical item can be tracked and borrowed independently
• Membership Type → Member: one membership type applies to many members (1:M)
• Member ↔ Copy: resolved via borrow, which tracks which specific copy is with which member and when
• Borrow → Fine: one borrow generates at most one fine (1:0..1) — only late returns produce a fine, and the fine is tied to the borrow event, not the member directly

File Structure

Run the files in this order — each depends on the tables/objects created before it:

File	Contents
01_schema.sql	Table definitions — all 9 tables with primary and foreign keys
02_constraints.sql	CHECK constraints (date validity, non-negative fines, controlled status values) and indexes on foreign key columns
03_functions.sql	show_available_copies(), calculate_fine(), and a BEFORE INSERT trigger that enforces each member's borrowing limit
04_procedures.sql	Add / update / delete procedures for every table, grouped by entity
library_erd.pdf	Entity-relationship diagram

Setup

psql -U your_user -d your_database -f 01_schema.sql
psql -U your_user -d your_database -f 02_constraints.sql
psql -U your_user -d your_database -f 03_functions.sql
psql -U your_user -d your_database -f 04_procedures.sql

Key Design Decisions

• copy is a separate entity from book, not an attribute — this is what allows the system to know exactly which physical copy is with which member at any time, rather than just tracking a count.
• fine references borrow, not library_members directly — a fine exists because of a specific late borrow event, so linking it to the borrow record (rather than the member) preserves that context.
• Foreign key columns are explicitly indexed — Postgres does not auto-index FK columns (only primary keys), and this schema joins across FKs frequently (borrow history, member lookups, availability checks).
• Borrowing limits are enforced at the database level via a trigger, not left to the application layer — membership_type.max_books_allowed is checked on every INSERT into borrow.