Training database Library

This repository contains a sample PostgreSQL library database with schema, a Python data generator, and example analytical queries.

---

Repository structure

library-db-demo/
├── create_schema.sql      # Database schema
├── generate_data.py       # Data generator (with Faker)
├── requirements.txt       # Python dependencies
├── sample_queries.sql     # Example analytical queries
└── README.md              # Instructions

---

Requirements

• PostgreSQL 14+
• Python 3.8+
• Packages listed in requirements.txt

---

Quick Start

Create database

createdb library

Create schema

psql -d library -f create_schema.sql

Database Schema

The database models a simple library system with the following tables:

Table	Description
authors	List of authors (id, name, country)
members	Library members (id, name, email, joined_at)
books	Books with title, author, genre, year, ISBN
copies	Physical copies of books, shelf location, and status
loans	Loans of specific copies to members with due and return dates
reservations	Book reservations by members with status
reviews	Member reviews for books with rating and comment

---

Setup Python environment

python3 -m venv venv
source venv/bin/activate
pip install -r requirements.txt

Generate data

Default values:

python generate_data.py

With custom parameters:

python generate_data.py --authors 20 --members 50 --books 30 --reservations 10 --reviews 10

Run example queries

psql -d library -f sample_queries.sql