-- Create Books table
CREATE TABLE Books (
    ISBN VARCHAR(13) PRIMARY KEY,  -- ISBN as primary key, ensuring unique identification
    Title VARCHAR(255) NOT NULL,    -- Book title, cannot be null
    Author VARCHAR(255) NOT NULL,   -- Author name, cannot be null
    Genre VARCHAR(100),              -- Genre of the book
    Quantity INT NOT NULL            -- Number of copies available
);

-- Create Members table
    CREATE TABLE Members (
    MemberID INT PRIMARY KEY AUTO_INCREMENT,  -- MemberID as primary key, auto-incremented
    Name VARCHAR(100) NOT NULL,                -- Member name, cannot be null
    Email VARCHAR(100) NOT NULL UNIQUE,        -- Member email, must be unique
    Phone VARCHAR(15)                         -- Member phone number
);

-- Create Loans table
    CREATE TABLE Loans (
    LoanID INT PRIMARY KEY AUTO_INCREMENT,    -- LoanID as primary key, auto-incremented
    MemberID INT NOT NULL,                    -- Foreign key referencing Members
    ISBN VARCHAR(13) NOT NULL,                -- Foreign key referencing Books
    LoanDate DATE NOT NULL,                   -- Date when the book was loaned
    ReturnDate DATE,                          -- Date when the book is returned
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),  -- Ensures data integrity
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)              -- Ensures data integrity
);

-- Insert new records into the Books table
INSERT INTO Books (ISBN, Title, Author, Genre, Quantity) VALUES 
('978-3-16-148410-0', 'The Great Gatsby', 'F. Scott Fitzgerald', 'Fiction', 5),
('978-0-06-112008-4', 'To Kill a Mockingbird', 'Harper Lee', 'Fiction', 3);

-- Insert new records into the Members table
INSERT INTO Members (Name, Email, Phone) VALUES 
('Alice Smith', 'alice@example.com', '123-456-7890'),
('Bob Johnson', 'bob@example.com', '098-765-4321');

-- Insert new records into the Loans table
INSERT INTO Loans (MemberID, ISBN, LoanDate) VALUES 
(1, '978-3-16-148410-0', '2024-12-01'),
(2, '978-0-06-112008-4', '2024-12-02');

-- Retrieve all information about books borrowed by a specific member
SELECT Books.Title, Books.Author, Loans.LoanDate, Loans.ReturnDate
FROM Loans
JOIN Books ON Loans.ISBN = Books.ISBN
WHERE Loans.MemberID = 1;  -- Replace with desired MemberID

-- Update the quantity of a particular book in the Books table
UPDATE Books
SET Quantity = Quantity - 1
WHERE ISBN = '978-3-16-148410-0';  -- Assume one copy has been loaned out

-- Delete a member record from the Members table
DELETE FROM Members
WHERE MemberID = 2;  -- Replace with the MemberID to delete