/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Appendix E 	       		*/
/*																				*/
/*	Wedgewood Pacific (WP) Create Tables:  New Appendix E tables only									*/
/*																				*/
/*	These are the Oracle Database XE SQL code solutions				*/
/*																				*/
/********************************************************************************/

CREATE TABLE PRODUCTION_ITEM( 
	SKU 			Int 		NOT NULL, 
	SKU_Description 	Char(35) 	NOT NULL, 
	ProductionStartDate 	Date 		NULL, 
	ProductionEndDate 	Date 		NULL, 
	QuantityOnHand 		Int 		NULL, 
	QuantityInProduction 	Int 		NULL, 
	CONSTRAINT 		PRODUCTION_ITEM_PK PRIMARY KEY(SKU) 
);

CREATE TABLE CATALOG_SKU_2015( 
	CatalogID 		Int 		NOT NULL, 
	SKU 			Int 		NOT NULL, 
	CatalogDescription 	Varchar(255) 	NOT NULL, 
	CatalogPage 		Int		NULL, 
	DateOnWebSite 		Date 		NULL, 
	CONSTRAINT 		CATALOG_SKU_2015_PK PRIMARY KEY(CatalogID), 
	CONSTRAINT 		CAT15_PROD_ITEM_FK FOREIGN KEY(SKU) 
					REFERENCES PRODUCTION_ITEM(SKU)  
);

CREATE SEQUENCE seqCat15ID INCREMENT BY 1 START WITH 20150001;

CREATE TABLE CATALOG_SKU_2016( 
	CatalogID 		Int 		NOT NULL, 
	SKU 			Int 		NOT NULL, 
	CatalogDescription 	Varchar(255) 	NOT NULL, 
	CatalogPage 		Int 		NULL, 
	DateOnWebSite 		Date 		NULL, 
	CONSTRAINT 		CATALOG_SKU_2016_PK PRIMARY KEY(CatalogID) 
);

CREATE SEQUENCE seqCat16ID INCREMENT BY 1 START WITH 20160001;

CREATE TABLE PRODUCTION_ITEM_DATA( 
	SKU 			Int 		NOT NULL, 
	SKU_Description 	Char(35) 	NULL, 
	ProductionStartDate 	Date 		NULL, 
	ProductionEndDate 	Date 		NULL, 
	QuantityOnHand 		Int 		NULL, 
	QuantityInProduction 	Int 		NULL, 
	ApprovalDate 		Date 		NULL, 
	CONSTRAINT 		PROD_ITEM_PK PRIMARY KEY(SKU) 
);

/******  CREATE TABLE FOR EXERCISE E.56  ******/

CREATE TABLE CATALOG_SKU_2017( 
	CatalogID 		Int 		NOT NULL, 
	SKU 			Int 		NOT NULL, 
	CatalogDescription 	Varchar(255) 	NOT NULL, 
	CatalogPage 		Int		NULL, 
	DateOnWebSite 		Date 		NULL, 
	CONSTRAINT 		CATALOG_SKU_2017_PK PRIMARY KEY(CatalogID), 
	CONSTRAINT 		CAT17_PROD_ITEM_FK FOREIGN KEY(SKU) 
					REFERENCES PRODUCTION_ITEM(SKU) 
);

CREATE SEQUENCE seqCat17ID INCREMENT BY 1 START WITH 20170001;

/********************************************************************************/