--AUTHORS ARE THE AUTHORS OF THE BOOKS
--CREATE UNIQUE SEQUENCE AUTHOR_ID_SEQ;

CREATE TABLE AUTHORS(
	AUTHOR_ID INT NOT NULL AUTO_INCREMENT, 
	NAME VARCHAR(50),
	SURNAME VARCHAR(50),
	PRIMARY KEY(AUTHOR_ID)
);

--BOOKS CONTAINS ALL BOOKS IN THE SHOP
--CREATE UNIQUE SEQUENCE BOOK_ID_SEQ;

CREATE TABLE BOOKS(
	BOOK_ID INT  NOT NULL AUTO_INCREMENT, 
	AUTHOR_ID INT, 
	TITLE VARCHAR(200), 
	DESCRIPTION BLOB, 
	PRICE NUMERIC(5,2), 
	PAGES INT, 
	PRIMARY KEY(BOOK_ID), 
	FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(AUTHOR_ID)
);

--ONE ORDER MAY CONTAIN A LOT OF BOOKS
--CREATE UNIQUE SEQUENCE ORDER_ID_SEQ;

CREATE TABLE ORDERS(
	ORDER_ID INT NOT NULL AUTO_INCREMENT,
	BYER_NAME VARCHAR(100),
	SHIPPING_ADRESS VARCHAR(100),
	SHIPPING_ZIPCODE VARCHAR(10),
	SHIPPING_CITY VARCHAR(30),
	PRIMARY KEY(ORDER_ID)
);

--EACH DISTINCT ORDER ITEMS	
--CREATE UNIQUE SEQUENCE ORDER_ITEM_ID_SEQ;

CREATE TABLE ORDER_ITEMS(
	ORDER_ITEM_ID INT NOT NULL AUTO_INCREMENT,
	ORDER_ID INT,
	BOOK_ID INT,
	QUANTITY INT,
	PRIMARY KEY(ORDER_ITEM_ID),
	FOREIGN KEY(ORDER_ID) REFERENCES ORDERS(ORDER_ID),
	FOREIGN KEY(BOOK_ID) REFERENCES BOOKS(BOOK_ID)
);

--SOME AUTHORS. THE AUTHOR_ID WILL BE GENERATED BY THE SEQUENCE

INSERT INTO AUTHORS(NAME, SURNAME) VALUES('GREG', 'BARISH');
INSERT INTO AUTHORS(NAME,SURNAME) VALUES('TIMOTHY', 'BUDD');
INSERT INTO AUTHORS(NAME,SURNAME) VALUES('MARK', 'WUTKA');
INSERT INTO AUTHORS(NAME,SURNAME) VALUES('H.M', 'DIETEL');

INSERT INTO BOOKS(AUTHOR_ID, TITLE, DESCRIPTION, PRICE, PAGES) 
	VALUES(1, 'BUILDNING SCALABLE AND HIGH-PERFORMANCE JAVA WEB APPLICATIONS USING J2EE TECHNOLOGY', 'A BOOK ABOUT BUILDNING SCALABLE AND HIGH-PERFORMANCE JAVA WEB APPLICATIONS USING J2EE TECHNOLOGY. THE BOOKS DESCRIBES HOW TO USE THE DIFFERENT PARTS OF J2EE TO BUILD A WEB APPLICATION', 600, 392);

INSERT INTO BOOKS(AUTHOR_ID, TITLE, DESCRIPTION, PRICE, PAGES) VALUES(2, 'CLASSIC DATA STRUCTURES IN C++', 'BUDD EXPLAINS SEVERAL DATA STRUCTURES THAT ARE COMMIN IN C++', 550, 537);

INSERT INTO BOOKS(AUTHOR_ID, TITLE, DESCRIPTION, PRICE, PAGES) VALUES(2, 'CLASSIC DATA STRUCTURES IN JAVA', 'BUDD EXPLAINS SEVERAL DATA STRUCTURES THAT ARE COMMIN IN JAVA', 650, 437);

INSERT INTO BOOKS(AUTHOR_ID, TITLE, DESCRIPTION, PRICE, PAGES) VALUES(3, 'SPECIAL EDITION USING JAVA SERVER PAGES', 'A QUITE EXTENSIVE BOOK ABOUT WEB DEVELOPMENT USING JAVA SERVER PAGES AND JAVA SERVLETS', 400, 351);

INSERT INTO BOOKS(AUTHOR_ID, TITLE, DESCRIPTION, PRICE, PAGES) VALUES(4, 'C: HOW TO PROGRAM', 'AN INTRODUCTION TO C-PROGRAMMING.', 430, 800);