CREATE TABLE Books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50),
    publication_year INT,
    availiable_quantity INT DEFAULT 0
);

CREATE TABLE Borrowers (
    borrower_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    grade VARCHAR(10),
    UNIQUE (name,grade)
);

CREATE TABLE BorrowingRecords (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    borrower_id INT,
    borrow_date DATE,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id),
    FOREIGN KEY (borrower_id) REFERENCES Borrowers(borrower_id)
    
);

INSERT INTO Books (title, author, publication_year, availiable_quantity) VALUES
('Dark history', 'Jane Doe', 2025, 5),
('Child Physcology', 'Holly Jackson', 2013, 2),
('Algebra 1', 'Shaivi Ramyad', 2020, 7);

INSERT INTO Borrowers (name,grade) VALUES
('Emmy Green', '7th Grade'),
('Mason Turner', '10th Grade'),
('Rayva Ramyad', '9th Grade');

UPDATE Books SET available_quantity = available_quantity - 1 WHERE title = 'Introduction to SQL';

select *from Books;

INSERT INTO BorrowingRecords (book_id, borrower_id, borrow_date, return_date) VALUES
(1, 1, '2023-01-01', NULL);

select *from BorrowingRecords;

UPDATE Books SET available_quantity = available_quantity + 1 WHERE title = 'Introduction to SQL';

select *from Books;

UPDATE BorrowingRecords SET return_date = '2023-01-10' WHERE book_id = 1 AND borrower_id = 1;

select *from BorrowingRecords;

SELECT * FROM Books WHERE available_quantity >= 5;

DELETE FROM BorrowingRecords WHERE book_id = 1 AND borrower_id = 1;

SELECT * FROM BorrowingRecords;