/********************************************************************************/
/*                                                                              */
/*  Kroenke, Auer, Vandenberg, Yoder - DBC (8th Edition) Chapter 08             */
/*                                                                              */
/*  Garden Glory Data Warehouse Database Create Tables                          */
/*                                                                              */
/*  These are the Oracle XE 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 PROPERTY(
		PropertyID		Int					NOT NULL,
		PropertyName	Char(50)			NOT NULL,
		City			Char(35)			NOT NULL,
		State			Char(2)				NOT NULL,
		Zip				Char(10)			NOT NULL,
		CONSTRAINT		PROPERTY_PK	PRIMARY KEY(PropertyID)
		);

CREATE TABLE EMPLOYEE(
		EmployeeID		Int					NOT NULL,
		LastName		Char(25)			NOT NULL,
		FirstName		Char(25)			NOT NULL,
		ExperienceLevel	Char(15)			NOT NULL,
		CONSTRAINT		EMPLOYEE_PK			PRIMARY KEY(EmployeeID)
		);

CREATE TABLE SERVICE_HOURS(
		TimeID			Int					NOT NULL,
		PropertyID		Int					NOT NULL,
		EmployeeID		Int					NOT NULL,
		HoursWorked		Number (4,2)		NULL,
		CONSTRAINT		SERVICE_HRS_PK      PRIMARY KEY(TimeID, PropertyID, EmployeeID),
		CONSTRAINT		SH_TIMELINE_FK FOREIGN KEY(TimeID)
								REFERENCES TIMELINE(TimeID),
		CONSTRAINT		SH_PROPERTY_FK FOREIGN KEY(PropertyID)
								REFERENCES PROPERTY(PropertyID),
		CONSTRAINT		SH_EMPLOYEE_FK FOREIGN KEY(EmployeeID)
								REFERENCES EMPLOYEE(EmployeeID)
		);

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

 