CREATE DATABASE BOOKS;

USE BOOKS;

CREATE TABLE BOOK_INFO
(TITLE VARCHAR(100) NOT NULL,
EDITION VARCHAR(4) NOT NULL,
DESCRIPTION TEXT(500) NOT NULL, 
CATEGORY VARCHAR(50) NOT NULL, 
FICTION ENUM ('FICTION', 'NON-FICTION') NOT NULL,
PUBLISHER_ID CHAR(5) NOT NULL,
AUTHOR_ID CHAR(4) NOT NULL,
YEAR_PUBLISHED VARCHAR(4),
PAGES VARCHAR(4) NOT NULL,
BOOK_PRICE DECIMAL(10,2) NOT NULL, 
ISBN CHAR(13) NOT NULL PRIMARY KEY);



CREATE TABLE PUBLISHER_INFO
(PUBLISHER_NAME TEXT(100) NOT NULL,
PUBLISHER_ADDRESS TEXT(50) NOT NULL,
PUBLISHER_COUNTRY VARCHAR(50) NOT NULL,
PUBLISHER_STATE CHAR(2),
PUBLISHER_ID CHAR(5) NOT NULL PRIMARY KEY);


CREATE TABLE AUTHOR_INFO
(FIRST_NAME VARCHAR (50) NOT NULL, 
LAST_NAME VARCHAR (50) NOT NULL, 
BIRTH_DATE DATE NOT NULL,
COUNTRY VARCHAR(50) NOT NULL,
AUTHOR_ID CHAR(4) NOT NULL PRIMARY KEY);