-- Create the database
CREATE DATABASE library;
USE library;

-- Create table for students
CREATE TABLE student (
    StudentID VARCHAR(5) PRIMARY KEY,
    LastName VARCHAR(50) NOT NULL,
    FirstName VARCHAR(50) NOT NULL,
    Class VARCHAR(10) NOT NULL
);

-- Create table for books
CREATE TABLE book (
    BookID VARCHAR(5) PRIMARY KEY,
    Title VARCHAR(100) NOT NULL,
    Author VARCHAR(100) NOT NULL,
    Publisher VARCHAR(50) NOT NULL
);

-- Create table for borrow records
CREATE TABLE borrow (
    StudentID VARCHAR(5),
    BookID VARCHAR(5),
    BorrowDate DATE NOT NULL,
    ReturnDate DATE NOT NULL,
    PRIMARY KEY (StudentID, BookID, BorrowDate),
    FOREIGN KEY (StudentID) REFERENCES student(StudentID),
    FOREIGN KEY (BookID) REFERENCES book(BookID)
);

USE library;

-- Insert students
INSERT INTO student (StudentID, LastName, FirstName, Class) VALUES
('S01', 'Smith', 'John', 'B1'),
('S02', 'Johnson', 'Maria', 'A3'),
('S03', 'Brown', 'James', 'B2'),
('S04', 'Davis', 'Emma', 'A1');

-- Insert books
INSERT INTO book (BookID, Title, Author, Publisher) VALUES
('B01', 'The Little Prince', 'Antoine de Saint-Exupéry', 'Gutenberg'),
('B02', '1984', 'George Orwell', 'Penguin'),
('B03', 'The Diary of Anne Frank', 'Anne Frank', 'HarperCollins'),
('B04', 'Around the World in 80 Days', 'Jules Verne', 'Vintage');

-- Insert borrow records
INSERT INTO borrow (StudentID, BookID, BorrowDate, ReturnDate) VALUES
('S01', 'B01', '2024-02-10', '2024-03-10'),
('S02', 'B02', '2024-02-15', '2024-03-15'),
('S03', 'B03', '2024-02-20', '2024-03-20'),
('S04', 'B04', '2024-02-25', '2024-03-25'),
('S01', 'B02', '2024-03-01', '2024-03-30');