/********************************************************************************/
/*                                                                              */
/*  Kroenke, Auer, Vandenberg, Yoder - DBConcepts (8th Edition) Chapter 08      */
/*                                                                              */
/*  The QACS-DW Database Create Tables                                          */
/*                                                                              */
/*  These are the Oracle Database XE SQL code solutions                  */
/*                                                                              */
/********************************************************************************/

CREATE TABLE TIMELINE(
		TimeID			Int					NOT NULL,
		DateID			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 CUSTOMER(
		CustomerID			Int				NOT NULL,
		CustomerName		Char(50)		NOT NULL,
		City				Char(35)		NULL,
		State				Char(2)			NULL,
		ZIP					Char(10)		NULL,
		CONSTRAINT 			CUSTOMER_PK 	PRIMARY KEY(CustomerID)
		);

CREATE  TABLE VENDOR(
		VendorID			Int				NOT NULL,
		VendorName			Char(100)		NOT NULL,
		CONSTRAINT 			VENDOR_PK 		PRIMARY KEY(VendorID)
		);

CREATE  TABLE ITEM(
		ItemID				Int	 			NOT NULL,
		ItemDescription		VarChar(255)	NOT NULL,
		CONSTRAINT 			ITEM_PK 		PRIMARY KEY(ItemID)
		);

CREATE  TABLE SALE_ITEM(
		TimeID				Int				NOT NULL,
		CustomerID			Int				NOT NULL,
		ItemID				Int	 			NOT NULL,
		VendorID			Int				NOT NULL,
		ItemSalePrice		Numeric(9,2)	NOT NULL,
		CONSTRAINT 			SALE_ITEM_PK
					 		PRIMARY KEY (TimeID, CustomerID, ItemID, VendorID),
		CONSTRAINT 			SI_TIMELINE_FK FOREIGN KEY (TimeID)
									REFERENCES TIMELINE(TimeID),
		CONSTRAINT 			SI_CUSTOMER_FK FOREIGN KEY (CustomerID)
									REFERENCES CUSTOMER(CustomerID),
		CONSTRAINT 			SI_VENDOR_FK FOREIGN KEY (VendorID)
									REFERENCES VENDOR(VendorID),
		CONSTRAINT 			SI_ITEM_FK FOREIGN KEY (ItemID)
									REFERENCES ITEM(ItemID)
		);

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