BookVerse Library Merger Emergency - Activity 04 📚

docker-compose up -d
docker exec -it w4-postgres psql -U postgres bookverse_db

-- Authors: Shared across both campuses
authors (id, name, nationality, birth_year)

-- Books: Unified catalog
books (id, title, author_id, isbn, publication_year)

-- Campuses: Physical locations
campuses (id, name, location, capacity)

-- Inventory: Book distribution across campuses
book_inventory (id, book_id, campus_id, copies_available, shelf_location)

-- Members: Library cardholders
members (id, name, email, campus_id, membership_type)
  └── membership_type: 'student', 'faculty', 'staff'

-- Loans: Borrowing records
loans (id, book_id, member_id, loan_date, due_date, return_date, campus_id)

-- Find books with author info (INNER JOIN)
SELECT b.title, a.name as author, b.publication_year
FROM books b
INNER JOIN authors a ON b.author_id = a.id;

-- Members who NEVER borrowed anything (LEFT JOIN)
SELECT m.name, m.email, m.membership_type
FROM members m
LEFT JOIN loans l ON m.id = l.member_id
WHERE l.id IS NULL;

-- Book availability across BOTH campuses
SELECT b.title,
    SUM(CASE WHEN c.name = 'East Campus' THEN bi.copies_available ELSE 0 END) as east,
    SUM(CASE WHEN c.name = 'West Campus' THEN bi.copies_available ELSE 0 END) as west
FROM books b
JOIN book_inventory bi ON b.id = bi.book_id
JOIN campuses c ON bi.campus_id = c.id
GROUP BY b.id, b.title;

-- Multi-table JOIN: Loan history with all details
SELECT m.name as member, b.title, a.name as author,
       c.name as campus, l.loan_date, l.due_date
FROM loans l
JOIN members m ON l.member_id = m.id
JOIN books b ON l.book_id = b.id
JOIN authors a ON b.author_id = a.id
JOIN campuses c ON l.campus_id = c.id;