-- =========================================================
-- LIBRARY DATABASE SYSTEM
-- Assignment 4
-- =========================================================

-- Create the database
-- CREATE DATABASE LibraryDB;

-- Select the database for use
-- USE LibraryDB;

-- =========================================================
-- 1. CREATE TABLES
-- =========================================================

-- Books table stores information about library books.
CREATE TABLE Books (
    ISBN VARCHAR(20) PRIMARY KEY,
    Title VARCHAR(150) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    Genre VARCHAR(50),
    Quantity INT NOT NULL
);

-- Members table stores information about library members.
CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(20)
);

-- Loans table records books borrowed by members.
-- MemberID references Members.
-- ISBN references Books.
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    MemberID INT NOT NULL,
    ISBN VARCHAR(20) NOT NULL,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,

    FOREIGN KEY (MemberID)
        REFERENCES Members(MemberID),

    FOREIGN KEY (ISBN)
        REFERENCES Books(ISBN)
);

-- =========================================================
-- 2. INSERT SAMPLE RECORDS
-- =========================================================

-- Insert books into the Books table.
INSERT INTO Books (ISBN, Title, Author, Genre, Quantity)
VALUES
('9780132350884', 'Clean Code', 'Robert C. Martin', 'Programming', 5),
('9781491950357', 'Designing Data-Intensive Applications', 'Martin Kleppmann', 'Database', 3),
('9780134685991', 'Effective Java', 'Joshua Bloch', 'Programming', 4),
('9780262033848', 'Introduction to Algorithms', 'Thomas H. Cormen', 'Algorithms', 6);

-- Insert members into the Members table.
INSERT INTO Members (MemberID, Name, Email, Phone)
VALUES
(101, 'Destiny Peter', 'destiny@example.com', '08012345678'),
(102, 'David James', 'david@example.com', '08023456789'),
(103, 'Amina Bello', 'amina@example.com', '08034567890');

-- Insert loan records.
INSERT INTO Loans (LoanID, MemberID, ISBN, LoanDate, ReturnDate)
VALUES
(1001, 101, '9780132350884', '2026-09-10', NULL),
(1002, 101, '9781491950357', '2026-09-12', NULL),
(1003, 102, '9780134685991', '2026-09-13', '2026-09-20'),
(1004, 103, '9780262033848', '2026-09-15', NULL);

-- =========================================================
-- 3. DISPLAY ALL RECORDS
-- These queries can be used to produce the required
-- screenshots showing the contents of each relation.
-- =========================================================

SELECT * FROM Books;

SELECT * FROM Members;

SELECT * FROM Loans;

-- =========================================================
-- 4. RETRIEVE BOOKS BORROWED BY A SPECIFIC MEMBER
-- Example: retrieve all books borrowed by MemberID 101.
-- =========================================================

SELECT
    m.MemberID,
    m.Name AS MemberName,
    b.ISBN,
    b.Title,
    b.Author,
    b.Genre,
    l.LoanDate,
    l.ReturnDate
FROM Members m
JOIN Loans l
    ON m.MemberID = l.MemberID
JOIN Books b
    ON l.ISBN = b.ISBN
WHERE m.MemberID = 101;

-- =========================================================
-- 5. UPDATE BOOK QUANTITY
-- Example: increase the quantity of Clean Code to 7.
-- =========================================================

UPDATE Books
SET Quantity = 7
WHERE ISBN = '9780132350884';

-- Verify the updated record.
SELECT * FROM Books
WHERE ISBN = '9780132350884';

-- =========================================================
-- 6. DELETE A MEMBER
-- =========================================================

-- First, delete the member's loan records because Loans
-- contains a foreign key referencing Members.
DELETE FROM Loans
WHERE MemberID = 103;

-- Now delete the member from Members.
DELETE FROM Members
WHERE MemberID = 103;

-- Verify that the member has been removed.
SELECT * FROM Members;