Library Management System (PostgreSQL)

Project Overview

This project implements a Library Management System using PostgreSQL to manage books, authors, and patrons. It allows users to add, view, update, and delete records, demonstrating core SQL concepts like tables, relationships, foreign keys, and advanced queries.

Core Features

• Add a book
• View all books
• View a single book by title or ID
• View books by a specific author
• Update book availability (borrow/return)
• Update patron borrowed books
• Delete a book
• Delete an author
• Run advanced queries (filtering, searching, updating in bulk)

Database Schema & Sample Data

Instructions to Run SQL Commands:

1. Install PostgreSQL: Ensure PostgreSQL is installed and running on your system.
2. Install pgAdmin (Optional): For a GUI, install pgAdmin. Otherwise, you can use psql.
3. Connect to PostgreSQL:
   • Using pgAdmin: Open pgAdmin, connect to your PostgreSQL server, and open a query tool.
   • Using psql: Open your terminal or command prompt and type psql -U your_username (replace your_username with your PostgreSQL username).
4. Create Database: Execute the CREATE DATABASE LibraryDB; command. You might need to connect to a default database like postgres first.
5. Connect to LibraryDB: After creating, connect to LibraryDB. In psql, use \c LibraryDB;. In pgAdmin, select LibraryDB from the database dropdown.
6. Execute library_management.sql: Copy and paste the contents of the library_management.sql file into your pgAdmin query tool or psql prompt and execute it. Alternatively, in psql, you can run \i /path/to/your/library_management.sql (replace /path/to/your/ with the actual path to the file).

Sprint 1: Project Setup

---

-- Create a new database LibraryDB (This command is typically run outside the script or by connecting to a default database first) -- CREATE DATABASE LibraryDB; -- \c LibraryDB; -- Connect to your newly created database if running in psql

-- Creating the Authors' table
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    nationality VARCHAR(100),
    birth_year INT,
    death_year INT
);

-- Create Books table
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author_id INT REFERENCES authors(id),
    genres TEXT[],
    published_year INT,
    available BOOLEAN DEFAULT TRUE
);

-- Create Patrons table
CREATE TABLE patrons (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    borrowed_books INT[] DEFAULT ARRAY[]::INT[]
);

Sprint 2: Insert Data

---

--  sample for authors
INSERT INTO authors (id, name, nationality, birth_year, death_year) VALUES
(1, 'George Orwell', 'British', 1903, 1950),
(2, 'Harper Lee', 'American', 1926, 2016),
(3, 'F. Scott Fitzgerald', 'American', 1896, 1940),
(4, 'Aldous Huxley', 'British', 1894, 1963),
(5, 'J.D. Salinger', 'American', 1919, 2010),
(6, 'Herman Melville', 'American', 1819, 1891),
(7, 'Jane Austen', 'British', 1775, 1817),
(8, 'Leo Tolstoy', 'Russian', 1828, 1910),
(9, 'Fyodor Dostoevsky', 'Russian', 1821, 1881),
(10, 'J.R.R. Tolkien', 'British', 1892, 1973);

-- sample for books
INSERT INTO books (id, title, author_id, genres, published_year, available) VALUES
(1, '1984', 1, ARRAY['Dystopian', 'Political Fiction'], 1949, TRUE),
(2, 'To Kill a Mockingbird', 2, ARRAY['Southern Gothic', 'Bildungsroman'], 1960, TRUE),
(3, 'The Great Gatsby', 3, ARRAY['Tragedy'], 1925, TRUE),
(4, 'Brave New World', 4, ARRAY['Dystopian', 'Science Fiction'], 1932, TRUE),
(5, 'The Catcher in the Rye', 5, ARRAY['Realist Novel', 'Bildungsroman'], 1951, TRUE),
(6, 'Moby-Dick', 6, ARRAY['Adventure Fiction'], 1851, TRUE),
(7, 'Pride and Prejudice', 7, ARRAY['Romantic Novel'], 1813, TRUE),
(8, 'War and Peace', 8, ARRAY['Historical Novel'], 1869, TRUE),
(9, 'Crime and Punishment', 9, ARRAY['Philosophical Novel'], 1866, TRUE),
(10, 'The Hobbit', 10, ARRAY['Fantasy'], 1937, TRUE);

-- sample for patrons
INSERT INTO patrons (id, name, email, borrowed_books) VALUES
(1, 'Alice Johnson', 'alice@example.com', ARRAY[]::INT[]),
(2, 'Bob Smith', 'bob@example.com', ARRAY[1, 2]),
(3, 'Carol White', 'carol@example.com', ARRAY[]::INT[]),
(4, 'David Brown', 'david@example.com', ARRAY[3]),
(5, 'Eve Davis', 'eve@example.com', ARRAY[]::INT[]),
(6, 'Frank Moore', 'frank@example.com', ARRAY[4, 5]),
(7, 'Grace Miller', 'grace@example.com', ARRAY[]::INT[]),
(8, 'Hank Wilson', 'hank@example.com', ARRAY[6]),
(9, 'Ivy Taylor', 'ivy@example.com', ARRAY[]::INT[]),
(10, 'Jack Anderson', 'jack@example.com', ARRAY[7, 8]);

Sprint 3: Read Operations (Queries)

---

-- Get all the books
SELECT * FROM books;

-- Get a book by title (e.g., '1984')
SELECT * FROM books WHERE title = '1984';

-- Get all books by author 
SELECT b.* FROM books b JOIN authors a ON b.author_id = a.id WHERE a.name = 'George Orwell';

-- Get all available books
SELECT * FROM books WHERE available = TRUE;

Sprint 4: Update Operations

---

-- Mark a book as borrowed (set available = false) (e.g., book with id 1)
UPDATE books SET available = FALSE WHERE id = 1;

-- Add a new genre to an existing book (e.g., book with id 1, add 'Classic')
UPDATE books SET genres = array_append(genres, 'Classic') WHERE id = 1;

-- Add a borrowed book to a patron’s record (e.g., patron with id 1 borrows book with id 10)
UPDATE patrons SET borrowed_books = array_append(borrowed_books, 10) WHERE id = 1;

Sprint 5: Delete Operations

---

-- Delete a book by title (e.g., '1984')
DELETE FROM books WHERE title = '1984';

-- Delete an author by ID (e.g., author with id 1)
DELETE FROM authors WHERE id = 1;

Sprint 6: Advanced Queries

---

-- Find books published after 1950
SELECT * FROM books WHERE published_year > 1950;

-- Find all American authors
SELECT * FROM authors WHERE nationality = 'American';

-- Set all books as available
UPDATE books SET available = TRUE;

-- Find all books that are available AND published after 1950
SELECT * FROM books WHERE available = TRUE AND published_year > 1950;

-- Find authors whose names contain "George"
SELECT * FROM authors WHERE name LIKE '%George%';

-- Increment the published year 1869 by 1 (e.g., for book with id 8, War and Peace)
UPDATE books SET published_year = published_year + 1 WHERE published_year = 1869;