-- ============================================
-- LIBRARY DATABASE SYSTEM
-- SQLite Version for MyCompiler
-- ============================================

-- Enable foreign key support
PRAGMA foreign_keys = ON;


-- ============================================
-- 1. CREATE BOOKS TABLE
-- ============================================

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
);


-- ============================================
-- 2. CREATE MEMBERS TABLE
-- ============================================

CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    Phone VARCHAR(20)
);


-- ============================================
-- 3. CREATE LOANS TABLE
-- ============================================

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)
        ON DELETE CASCADE,

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


-- ============================================
-- 4. INSERT BOOK RECORDS
-- ============================================

INSERT INTO Books
(ISBN, Title, Author, Genre, Quantity)
VALUES
('9780131103627',
 'The C Programming Language',
 'Brian Kernighan',
 'Programming',
 5),

('9780132350884',
 'Clean Code',
 'Robert C. Martin',
 'Programming',
 4),

('9781491950357',
 'Designing Data-Intensive Applications',
 'Martin Kleppmann',
 'Database',
 3),

('9780262033848',
 'Introduction to Algorithms',
 'Thomas H. Cormen',
 'Computer Science',
 6);


-- ============================================
-- 5. INSERT MEMBER RECORDS
-- ============================================

INSERT INTO Members
(MemberID, Name, Email, Phone)
VALUES
(101, 'John Smith', 'john@example.com', '08012345678'),
(102, 'Mary Johnson', 'mary@example.com', '08023456789'),
(103, 'David Williams', 'david@example.com', '08034567890'),
(104, 'Sarah Brown', 'sarah@example.com', '08045678901');


-- ============================================
-- 6. INSERT LOAN RECORDS
-- ============================================

INSERT INTO Loans
(LoanID, MemberID, ISBN, LoanDate, ReturnDate)
VALUES
(1001, 101, '9780132350884', '2026-09-20', NULL),

(1002, 101, '9780131103627', '2026-09-21', '2026-09-27'),

(1003, 102, '9781491950357', '2026-09-22', NULL),

(1004, 103, '9780262033848', '2026-09-23', NULL);


-- ============================================
-- 7. RETRIEVE BOOKS BORROWED BY MEMBER 101
-- ============================================

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


-- ============================================
-- 8. UPDATE BOOK QUANTITY
-- ============================================

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


-- ============================================
-- 9. DELETE MEMBER 104
-- ============================================

DELETE FROM Members
WHERE MemberID = 104;


-- ============================================
-- 10. DISPLAY ALL BOOKS
-- ============================================

SELECT * FROM Books;


-- ============================================
-- 11. DISPLAY ALL MEMBERS
-- ============================================

SELECT * FROM Members;


-- ============================================
-- 12. DISPLAY ALL LOANS
-- ============================================

SELECT * FROM Loans;