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

USE WP3
GO

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 IDENTITY (20150001, 1), 
	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 
);

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

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 IDENTITY (20170001, 1), 
	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 
);

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