Library Book Management System

Relational database for end-to-end library operations: catalog, members, lending, reservations, fines.
Built for SQL Server in SSMS with a 3NF schema, T-SQL stored procedures, and reporting views.

---

📚 Quick start

1. Install Docker Desktop & SSMS (Windows).
2. Clone:

   git clone https://github.com/florasteve/library-book-management-system.git

3. Start DB:

   cd docker
   docker compose up -d

4. Connect in SSMS:
   • Server: localhost,1433
   • Auth: SQL Server Authentication
   • Login: SA
   • Password: YourStrong!Passw0rd
   • Trust server certificate: True
5. Validate: run scripts/ssms_smoke_test.sql in SSMS.
6. Reset clean (optional): run scripts/reset-db.bat.

🚀 Features

• Catalog management: Books ↔ Authors (many-to-many)
• Circulation: Checkout / Return with due dates, renewals, grace period
• Reservations/holds queue with first-come fulfillment
• Overdue tracking & fine calculation (optional)
• Reporting views: availability, overdues, active reservations

🧱 Schema (Core Tables)

• Authors(AuthorId, FirstName, LastName, ... )
• Books(BookId, ISBN, Title, PublishedYear, CopiesTotal, CopiesAvailable, ...)
• Book_Authors(BookId, AuthorId) (M:N)
• Members(MemberId, FirstName, LastName, Email, Status, ...)
• Loans(LoanId, BookId, MemberId, LoanDate, DueDate, ReturnDate, FineAccrued)
• Reservations(ReservationId, BookId, MemberId, ReservedAt, FulfilledAt, CanceledAt, Position)

🔧 Troubleshooting

• Container unhealthy: ensure Docker has ≥ 4 GB RAM; healthcheck uses /opt/mssql-tools18/bin/sqlcmd -C (TLS trusted).
  • Recreate: docker compose down -v && docker compose up -d.
• Port 1433 in use: change mapping to "11433:1433" and connect to localhost,11433.
• LibraryDB missing: check docker logs library-db-init.
  • Ensure sql\ exists as a sibling to docker\ so the ../sql:/sql mount is valid.
• Duplicate seed errors: the seed is idempotent; if you edited it, re-run scripts\seed.bat or reset with scripts\reset-db.bat.

🗺️ ER Diagram (Mermaid)

erDiagram
  AUTHORS ||--o{ BOOK_AUTHORS : writes
  BOOKS }o--o{ BOOK_AUTHORS : includes
  MEMBERS ||--o{ LOANS : borrows
  BOOKS ||--o{ LOANS : loaned
  MEMBERS ||--o{ RESERVATIONS : requests
  BOOKS ||--o{ RESERVATIONS : reserved

AUTHORS {
    INT AuthorId PK
    NVARCHAR FirstName
    NVARCHAR LastName
  }
  BOOKS {
    INT BookId PK
    VARCHAR ISBN UK
    NVARCHAR Title
    INT PublishedYear
    INT CopiesTotal
    INT CopiesAvailable
  }
  BOOK_AUTHORS {
    INT BookId FK
    INT AuthorId FK
  }
  MEMBERS {
    INT MemberId PK
    NVARCHAR FirstName
    NVARCHAR LastName
    NVARCHAR Email UK
    VARCHAR Status
  }
  LOANS {
    INT LoanId PK
    INT BookId FK
    INT MemberId FK
    DATE LoanDate
    DATE DueDate
    DATE ReturnDate
    DECIMAL FineAccrued
  }
  RESERVATIONS {
    INT ReservationId PK
    INT BookId FK
    INT MemberId FK
    DATETIME2 ReservedAt
    DATETIME2 FulfilledAt
    DATETIME2 CanceledAt
    INT Position
  }

Loading