/****************************************************************************************/
/*                                                                                      */
/*  Kroenke, Auer, Vandenberg, and Yoder - Database Concepts (8th Edition) Chapter 08   */
/*                                                                                      */
/*  Heather Sweeney Designs Data Warehouse Database Create Tables                       */
/*                                                                                      */
/*  These are the Oracle XE code solutions                              */
/*                                                                                      */
/****************************************************************************************/

CREATE TABLE TIMELINE(
		TimeID			Int					NOT NULL,
		DateID			Date			NOT NULL,
/* "DATE" field renamed "DateID":  DATE is an Oracle reserved word */
		MonthID			Int					NOT NULL,
		MonthText		Char(15)			NOT NULL,
		QuarterID		Int					NOT NULL,
		QuarterText		Char(10)			NOT NULL,
		Year			Char(10)					NOT NULL,
		CONSTRAINT		TIMELINE_PK		PRIMARY KEY(TimeID)
		);

CREATE TABLE CUSTOMER(
		CustomerID		Int					NOT NULL,
		CustomerName	Char(75)			NOT NULL,
		EmailDomain		VarChar(100)		NOT NULL,
		PhoneAreaCode	Char(6)				NOT NULL,
		City			Char(35)			NULL,
		State			Char(2)				NULL,
		ZIP				Char(10)			NULL,
		CONSTRAINT 		CUSTOMER_PK 		PRIMARY KEY(CustomerID)
		);

CREATE TABLE PRODUCT(
		ProductNumber	Char(35)			NOT NULL,
		ProductType		Char(25)			NOT NULL,
		ProductName 	VarChar(75)			NOT NULL,
		CONSTRAINT 		PRODUCT_PK			PRIMARY KEY(ProductNumber)
		);

CREATE TABLE PRODUCT_SALES(
		TimeID			Int					NOT NULL,
		CustomerID		Int					NOT NULL,
		ProductNumber	Char(35) 			NOT NULL,
		Quantity		Int					NOT NULL,
		UnitPrice		Number(9,2)		NOT NULL,
		Total			Number(9,2)		NULL,
		CONSTRAINT		SALES_PK
		PRIMARY KEY	    (TimeID, CustomerID, ProductNumber),
		CONSTRAINT		PS_TIMELINE_FK FOREIGN KEY(TimeID)
								REFERENCES TIMELINE(TimeID),
		CONSTRAINT		PS_CUSTOMER_FK FOREIGN KEY(CustomerID)
								REFERENCES CUSTOMER(CustomerID),
		CONSTRAINT		PS_PRODUCT_FK FOREIGN KEY(ProductNumber)
								REFERENCES PRODUCT(ProductNumber)		
);

/* The following statements are for creating the PAYMENT_TYPE dimension table   */
/* in the Chapter 08 Exercises.													*/


CREATE TABLE PAYMENT_TYPE(
		PaymentTypeID		Int		NOT NULL,
		PaymentType		Char(25)	NOT NULL,
		CONSTRAINT 		PAY_TYPE_PK 	PRIMARY KEY(PaymentTypeID)
	);

ALTER TABLE PRODUCT_SALES
		ADD PaymentTypeID Int NULL;

ALTER TABLE PRODUCT_SALES
		ADD CONSTRAINT  PAY_TYPE_FK
			FOREIGN KEY (PaymentTypeID)
				REFERENCES PAYMENT_TYPE(PaymentTypeID);
					
 