CREATE TABLE Authors(
    AuthorID INT PRIMARY KEY,
    AuthorName VARCHAR(100),
    BirthYear INT,
    Nationality VARCHAR(50)
);
CREATE TABLE Publishers(
  PublisherID INT PRIMARY KEY,
  PublisherName VARCHAR(100),
  Address VARCHAR(200),
  ContactInformation VARCHAR(100)
);
CREATE TABLE Books(
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    AuthorID INT,
    ISBN VARCHAR(13),
    PublisherID INT,
    PublicationYear INT,
    Genre VARCHAR(50),
    Available BOOLEAN
);
CREATE TABLE Borrowers(
    BorrowerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Grade INT,
    ContactInformation VARCHAR(100) 
);
CREATE TABLE BorrowedBooks(
    BorrowedID INT PRIMARY KEY,
    BookID INT,
    BorrowerID INT,
    BorrowDate DATE,
    DueDate DATE,
    ReturnDate DATE
);
INSERT INTO Authors(AuthorID,AuthorName,BirthYear,Nationality)
VALUES
(1,'J.K Rowling',1965,'British'),
(2,'George Orwell',1903,'British'),
(3,'Mark Twain',1835,'American'),
(4,'Jane Austin',1775,'British'),
(5,'F.Scott Fitzgeralad',1896,'American');
-- SELECT*FROM Authors;

INSERT INTO Publishers(PublisherID,PublisherName,Address,ContactInformation)
VALUES
(1,'Scholastic','123 Book St. , New York','contact@scholastic.com'),
(2,'Penguin Books','456 Publish Ave. , London','contact@penguinbooks.co.uk'),
(3,'HarperCollins','789 Print Dr. , New York','contact@harpercollins.com'),
(4,'Random House','101 Publisher Ln. , London','contact@randomhouse.co.uk'),
(5,'Scribner','555 Literary Rd. , New York','contact@scribner.com');
-- SELECT*FROM Publishers;
INSERT INTO Books(BookID,Title,AuthorID,ISBN,PublisherID,PublicationYear,Genre,Available)
VALUES
(1,'Harry Potter and the Philosophers Stone',1,'975',1,1997,'Fantasy',true),
(2,'1984',2,'976',2,1949,'Dystopian',true),
(3,'The Adventures of Huckleberry Finn',3,'977',3,1884,'Adventure',true),
(4,'Pride and Prejudice',4,'978',4,1813,'Romance',true),
(5,'The Great Gatsby',5,'979',5,1925,'Drama',true);
-- SELECT*FROM Books;
INSERT INTO Borrowers(BorrowerID,Name,Grade,ContactInformation)
VALUES
(1, 'Emma Johnson', 7, 'emma@email.com'),
(2, 'Michael Smith', 7, 'michael@email.com'),
(3, 'Olivia Brown', 7, 'olivia@email.com'),
(4, 'William Jones', 7, 'william@email.com'),
(5, 'Sophia Davis', 7, 'sophia@email.com');
-- SELECT*FROM Borrowers;
INSERT INTO BorrowedBooks (BorrowedID, BookID, BorrowerID, BorrowDate, DueDate, ReturnDate)
VALUES
(1, 1, 1, '2023-10-10', '2023-10-24', '2023-10-15'),
(2, 2, 2, '2023-10-12', '2023-10-26', NULL),
(3, 3, 3, '2023-10-14', '2023-10-28', NULL),
(4, 4, 4, '2023-10-15', '2023-10-29', NULL),
(5, 5, 5, '2023-10-17', '2023-10-31', NULL);
-- SELECT*FROM BorrowedBooks;
SELECT* FROM Borrowers WHERE BorrowerID=1;
SELECT* FROM Books WHERE BookID=3;
select * from Borrowers ;
select * from Books ; 
select * from Publishers ;
Select * from BorrowedBooks ;
Select * from Authors ;