Reservation SQL

CREATE TABLE User ( user_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30) NOT NULL, phone VARCHAR(30) NOT NULL UNIQUE, email VARCHAR(30) NOT NULL UNIQUE, registered_at DATETIME NOT NULL, password VARCHAR(30) NOT NULL );

CREATE TABLE Book ( book_id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(30) NOT NULL, author VARCHAR(30) NOT NULL, publication_year DATE NOT NULL, isbn VARCHAR(30) NOT NULL UNIQUE, is_available TINYINT(1) DEFAULT 1, -- 1: true publisher VARCHAR(30),

category ENUM('UNKNOWN', 'GENERAL_WORK', 'PHILOSOPHY', 'RELIGION', 'SOCIAL_SCIENCE', 'NATURAL_SCIENCE', 'TECHNOLOGY_SCIENCE', 'ARTS', 'LANGUAGE', 'LITERATURE', 'HISTORY') );

CREATE TABLE Loan ( loan_id INT PRIMARY KEY AUTO_INCREMENT, book_id INT NOT NULL, user_id INT NOT NULL, start_date DATETIME NOT NULL, end_date DATETIME NOT NULL, return_date DATETIME, FOREIGN KEY (book_id) REFERENCES Book(book_id), FOREIGN KEY (user_id) REFERENCES User(user_id) );

CREATE TABLE Reservation ( reservation_id INT PRIMARY KEY AUTO_INCREMENT, book_id INT NOT NULL, user_id INT NOT NULL, register_date DATETIME NOT NULL, FOREIGN KEY (book_id) REFERENCES Book(book_id), FOREIGN KEY (user_id) REFERENCES User(user_id) );