/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg, Yoder DBC (8th Edition) Chapter 08				*/
/*																				*/
/*	James River Jewelry Data Warehouse Database Create Tables					*/
/*																				*/
/*	These are the Microsoft SQL Server 2016 code solutions						*/
/*																				*/
/********************************************************************************/


CREATE TABLE TIMELINE(
		TimeID			Int					NOT NULL,
		[Date]			Date				NOT NULL,
		MonthID			Int					NOT NULL,
		MonthText		Char(15)			NOT NULL,
		QuarterID		Int					NOT NULL,
		QuarterText		Char(10)			NOT NULL,
		[Year]			Int					NOT NULL,
		CONSTRAINT		TIMELINE_PK		PRIMARY KEY(TimeID)
		);

CREATE TABLE ITEM(
		ItemID			Int					NOT NULL,
		ItemType		Char(35)			NOT NULL,
		ItemMaterial	Char(35)			NOT NULL,
		CONSTRAINT		ITEM_PK				PRIMARY KEY(ItemID),
		);

CREATE TABLE ARTIST(
		ArtistID		Int					NOT NULL,
		ArtistName		Char(25)			NOT NULL,
		CONSTRAINT		ARTIST_PK			PRIMARY KEY(ArtistID)
		);

CREATE TABLE PURCHASE_PRICE(
		TimeID			Int					NOT NULL,
		ItemID			Int					NOT NULL,
		ArtistID		Int					NOT NULL,
		PurchasePrice	Numeric(9,2)		NULL,
		CONSTRAINT		PURCHASE_PRICE_PK   PRIMARY KEY(TimeID, ItemID, ArtistID),
		CONSTRAINT		PP_TIMELINE_FK FOREIGN KEY(TimeID)
								REFERENCES TIMELINE(TimeID)
										ON UPDATE NO ACTION
										ON DELETE NO ACTION,
		CONSTRAINT		PP_ITEM_FK FOREIGN KEY(ItemID)
								REFERENCES ITEM(ItemID)
										ON UPDATE NO ACTION
										ON DELETE NO ACTION,
		CONSTRAINT		SH_EARTIST_FK FOREIGN KEY(ArtistID)
								REFERENCES ARTIST(ArtistID)
										ON UPDATE NO ACTION
										ON DELETE NO ACTION
		);

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

 