/********************************************************************************/
/*																				*/
/*	Kroenke, Auer, Vandenberg & Yoder											*/
/*  Database Concepts (8th Edition) Chapter 08									*/
/*																				*/
/*	Marcia's Dry Cleaning Data Warehouse [MDC-DW] CReate Tables					*/
/*																				*/
/*	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 CUSTOMER(
		CustomerID		Int					NOT NULL IDENTITY (100,5),
		CustomerName	Char(50)			NOT NULL,
		EmailDomain		Char(50)			NULL,
		CONSTRAINT 		CUSTOMER_PK 		PRIMARY KEY(CustomerID)
		);

CREATE  TABLE INVOICE(
		InvoiceID		Int					NOT NULL,
		DateIn			Date				NOT NULL,
		DateOut			Date				NULL,
		CONSTRAINT 		INVOICE_PK 		PRIMARY KEY(InvoiceID)
		);

CREATE  TABLE SERVICE(
		ServiceID			Int	 			NOT NULL,
		ServiceDescription	Char(50)		NOT NULL,
		CONSTRAINT 			SERVICE_PK 	PRIMARY KEY(ServiceID)
		);

CREATE  TABLE INVOICE_ITEM(
		TimeID				Int					NOT NULL,
		CustomerID			Int					NOT NULL,
		InvoiceID			Int	 				NOT NULL,
		ServiceID			Int					NOT NULL,
		ExtendedPrice		Numeric(8,2)		NOT NULL,
		CONSTRAINT 			INVOICE_ITEM_PK
					 		PRIMARY KEY (TimeID, CustomerID, InvoiceID, ServiceID),
		CONSTRAINT 			II_TIMELINE_FK FOREIGN KEY (TimeID)
									REFERENCES TIMELINE(TimeID)
											ON UPDATE NO ACTION
											ON DELETE NO ACTION,
		CONSTRAINT 			II_CUSTOMER_FK FOREIGN KEY (CustomerID)
									REFERENCES CUSTOMER(CustomerID)
											ON UPDATE NO ACTION
											ON DELETE NO ACTION,
		CONSTRAINT 			II_INVOICE_FK FOREIGN KEY (InvoiceID)
									REFERENCES INVOICE(InvoiceID)
											ON UPDATE NO ACTION
											ON DELETE NO ACTION,
		CONSTRAINT 			II_SERVICE_FK FOREIGN KEY (ServiceID)
									REFERENCES SERVICE(ServiceID)
											ON UPDATE NO ACTION
											ON DELETE NO ACTION
		);
