Library Management Database (PostgreSQL)

A relational database project built with PostgreSQL to model a realistic library system.
This project demonstrates strong fundamentals in schema design, relational modeling, constraints, views, triggers, and analytical SQL queries, with an emphasis on data integrity and real-world use cases.

---

Overview

This database models a library where:

• Members borrow books
• Books can have one or more authors
• Loans track borrowing activity over time
• Late returns automatically generate fines

The project was designed and tested in PostgreSQL and organized for reproducibility and clarity, following standard database engineering practices.

---

Core Concepts Demonstrated

• Relational schema design
• Primary keys & foreign keys
• Many-to-many relationships
• Data integrity via constraints
• Indexes for performance
• Views for reusable query logic
• Triggers for automatic business rules
• Analytical SQL queries (joins, filters)

---

Database Structure

Main Tables

• members – library members
• books – books and availability status
• authors – book authors
• book_authors – join table (many-to-many relationship)
• loans – borrowing events over time
• fines – penalties for late returns

Key Design Decisions

• Many-to-many relationships are modeled using a composite primary key (book_authors)
• Triggers enforce business rules directly in the database
• Indexes are added only where query patterns justify them
• Seed data uses explicit IDs for deterministic rebuilds

---

Automatic Business Logic

• When a loan’s return_date is updated:
  • If the return is late, a fine is automatically created
• This logic is enforced at the database level, not the application layer

---

Repository Structure

library-database/
├── schema/
│   └── schema.sql          # Table definitions, PKs, FKs, constraints
├── seed/
│   └── seed.sql            # Deterministic sample data with explicit IDs
├── views/
│   └── available_books.sql # Reusable view for available books
├── functions/
│   └── apply_overdue_fine.sql
├── triggers/
│   └── overdue_fine.sql
├── indexes/
│   └── indexes.sql
├── queries/
│   └── analytics.sql       # Example analytical queries
└── README.md

How to Rebuild the Database

Run the SQL files in this order:

1. schema/schema.sql
2. seed/seed.sql
3. indexes/indexes.sql
4. views/available_books.sql
5. functions/functions.sql
6. triggers/overdue_fine.sql

After running these files, the database will be fully recreated with sample data.

---

Example Analytical Questions Answered

The queries/analytics.sql file includes example queries such as:

• Which books are currently available?
• Which loans are overdue?

These queries are read-only and are intended to demonstrate SQL reasoning and analysis.

---

Notes on Sample Data

• All data is fake and for demonstration purposes only
• Primary keys are explicitly set for reproducibility
• Sequences are reset after seeding to allow normal inserts
• Seed data intentionally includes edge cases (late returns, multi-author books)

---

Technologies Used

• PostgreSQL
• DBeaver (development & testing)
• SQL

---

Why This Project

This project was built to demonstrate practical SQL skills beyond basic CRUD operations, including:

• Designing schemas that reflect real-world systems
• Enforcing rules with database-level logic
• Writing queries that answer meaningful questions
• Organizing database code in a professional, reusable way