-- Create the library database
CREATE DATABASE LibraryDB;

-- Select the database
USE LibraryDB;

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

-- Create the Members table
CREATE TABLE Members (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(100) NOT NULL,
    Phone VARCHAR(20)
);

-- Create the Loans table
CREATE TABLE Loans (
    LoanID INT PRIMARY KEY,
    MemberID INT NOT NULL,
    ISBN VARCHAR(20) NOT NULL,
    LoanDate DATE NOT NULL,
    ReturnDate DATE,

    -- Establish relationship with Members
    FOREIGN KEY (MemberID) REFERENCES Members(MemberID),

    -- Establish relationship with Books
    FOREIGN KEY (ISBN) REFERENCES Books(ISBN)
);