CS2203 Programming Assignment Unit 4.sql

-- BOOKS TABLES
CREATE TABLE Books (
    ISBN VARCHAR(17) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Author_ID VARCHAR(25),
    Genre VARCHAR(50),
    Quantity INT,
    FOREIGN KEY (Author_ID) REFERENCES Authors(Author_ID)
);

-- MEMBERS TABLE
CREATE TABLE Members (
    Member_ID VARCHAR(25) PRIMARY KEY,
    Member_Name VARCHAR(100) NOT NULL,
    Member_Email VARCHAR(50),
    Member_Phone VARCHAR(12),
);

-- BOOK LOANS TABLE
CREATE TABLE Loans (
    Loan_ID VARCHAR(25) PRIMARY KEY,
    Member_ID VARCHAR(25),
    ISBN VARCHAR(17),
    Loan_Date DATE NOT NULL,
    Return_Date DATE NOT NULL,
    FOREIGN KEY (Member_ID) REFERENCES Members(Member_ID),
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN),
);

-- Authors Table
CREATE TABLE Authors (
    Author_ID VARCHAR(25) PRIMARY KEY,
    Author_Name VARCHAR(100) NOT NULL,
    Author_Nationality VARCHAR(50),
    Author_Birth_Year DATE,
);

-- Fetch methods for inserting new values into tables
INSERT INTO Books (ISBN, Title, Author, Genre, Quantity) VALUES
    ('0-380-97365-0', 'American Gods', 'Neil Gaiman', 'Fantasy', 4),
    ('978-0-385-51787-4', 'Rant', 'Chuck Palahniuk', 'Science Fiction, Satire, Horror', 2);

INSERT INTO Members (Member_ID, Member_Name, Member_Email, Member_Phone) VALUES
    ('MX-123', 'John Doe', 'john@proton.com', '787-345-6578'),
    ('MX-124', 'Jane Doe', 'jane@gmail.com', '787-879-3278');

INSERT INTO Loans (Loan_ID, Member_ID, ISBN, Loan_Date, Return_Date) VALUES
    ('LX-36', 'MX-123', '0-380-97365-0', '2024-05-07', '2024-05-21'),
    ('LX-37', 'MX-124', '978-0-385-51787-4', '2024-05-08', '2024-05-22');

-- Retrieving information about books borrowed by a library member
SELECT Books.*
FROM Books
INNER JOIN Loans
    ON Books.ISBN = Loans.ISBN
WHERE Loans.Member_ID = 'MX-123';

-- Update book quantity in 'Books' table
UPDATE Books
SET Quantity = 3
Where ISBN = '0-380-97365-0';

-- Deletion of a member from the 'Members' table
DELETE FROM Members
WHERE Member_ID = 'MX-124';