-- Library Management System - Full SQL Script
-- Drop tables if they already exist (for re-runs)
DROP TABLE IF EXISTS BookAuthors;
DROP TABLE IF EXISTS Loans;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;
DROP TABLE IF EXISTS Members;
-- 1. Table Definitions
CREATE TABLE Authors (
author_id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE Books (
book_id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
isbn TEXT UNIQUE,
publication_year INT
);
CREATE TABLE BookAuthors (
book_id INT REFERENCES Books(book_id) ON DELETE CASCADE,
author_id INT REFERENCES Authors(author_id) ON DELETE CASCADE,
PRIMARY KEY (book_id, author_id)
);
CREATE TABLE Members (
member_id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
membership_date DATE DEFAULT CURRENT_DATE
);
CREATE TABLE Loans (
loan_id SERIAL PRIMARY KEY,
book_id INT REFERENCES Books(book_id) ON DELETE CASCADE,
member_id INT REFERENCES Members(member_id) ON DELETE CASCADE,
loan_date DATE DEFAULT CURRENT_DATE,
due_date DATE,
return_date DATE
);
-- 2. Sample Data
INSERT INTO Authors (name) VALUES ('J.K. Rowling'), ('George Orwell'), ('Jane
Austen');
INSERT INTO Books (title, isbn, publication_year) VALUES
('Harry Potter', '9780747532743', 1997),
('1984', '9780451524935', 1949),
('Pride and Prejudice', '9780141439518', 1813);
INSERT INTO BookAuthors (book_id, author_id) VALUES
(1, 1), -- Harry Potter by J.K. Rowling
(2, 2), -- 1984 by George Orwell
(3, 3); -- Pride and Prejudice by Jane Austen
INSERT INTO Members (name, email) VALUES
('Alice Smith', '[email protected]'),
('Bob Johnson', '[email protected]');
INSERT INTO Loans (book_id, member_id, loan_date, due_date, return_date) VALUES
(1, 1, '2025-07-01', '2025-07-10', NULL),
(2, 2, '2025-07-02', '2025-07-12', '2025-07-10');
-- 3. Views for borrowed and overdue books
CREATE VIEW BorrowedBooks AS
SELECT m.name AS member_name, b.title AS book_title, l.due_date, l.return_date
FROM Loans l
JOIN Members m ON l.member_id = m.member_id
JOIN Books b ON l.book_id = b.book_id
WHERE l.return_date IS NULL;
CREATE VIEW OverdueBooks AS
SELECT * FROM BorrowedBooks
WHERE due_date < CURRENT_DATE;
-- 4. Trigger to notify on due date approach (example only prints message)
CREATE OR REPLACE FUNCTION notify_due_date()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.due_date <= CURRENT_DATE + INTERVAL '2 days' THEN
RAISE NOTICE 'Reminder: Book is due soon!';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS due_date_trigger ON Loans;
CREATE TRIGGER due_date_trigger
BEFORE INSERT OR UPDATE ON Loans
FOR EACH ROW
EXECUTE FUNCTION notify_due_date();
-- 5. Aggregated Report (example: number of loans per member)
SELECT m.name, COUNT(*) AS total_loans
FROM Loans l
JOIN Members m ON l.member_id = m.member_id
GROUP BY m.name;
-- 6. JOIN example: Book title, author, borrower
SELECT b.title, a.name AS author, m.name AS borrower
FROM Loans l
JOIN Books b ON l.book_id = b.book_id
JOIN BookAuthors ba ON b.book_id = ba.book_id
JOIN Authors a ON ba.author_id = a.author_id
JOIN Members m ON l.member_id = m.member_id
WHERE l.return_date IS NULL;
