/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, and Yoder 										*/
/*	Database Concepts (8th Edition) Appendix E 						       		*/
/*																				*/
/*	Wedgewood Pacific (WP) Create Tables:  New Appendix E tables only			*/
/*																				*/
/*	These are the MySQL 5.7 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) 
);

/* we set start value of auto_increments after creating the tables */

CREATE TABLE CATALOG_SKU_2015( 
	CatalogID 		Int 		NOT NULL AUTO_INCREMENT, 
	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) 
						ON UPDATE NO ACTION 
						ON DELETE NO ACTION 
);

ALTER TABLE CATALOG_SKU_2015 AUTO_INCREMENT=20150001;

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

ALTER TABLE CATALOG_SKU_2016 AUTO_INCREMENT=20160001;

/* run this at appropriate time in exercise instructions */
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) 
);

/* run this at appropriate time in exercise instructions */
/******  CREATE TABLE FOR EXERCISE E.56  ******/

CREATE TABLE CATALOG_SKU_2017( 
	CatalogID 		Int 		NOT NULL AUTO_INCREMENT, 
	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) 
						ON UPDATE NO ACTION 
						ON DELETE NO ACTION 
);

ALTER TABLE CATALOG_SKU_2017 AUTO_INCREMENT=20170001;


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