/************************************************************************/
/*																		*/
/*	Kroenke, Auer, Vandenberg, and Yoder 					*/
/*	Database Concepts (8th Edition) Chapter 03 	       		*/
/*																		*/
/*	Garden Glory Database Create Tables									*/
/*																		*/
/*	These are the Oracle Express 11GR2 SQL code solutions		*/
/*																		*/
/************************************************************************/

CREATE TABLE OWNER(
	OwnerID			Int			NOT NULL,
	OwnerName			Char(50)		NOT NULL,
	OwnerEmailAddress			VarChar(100)	NULL,
	OwnerType			Char(12)		NULL,
	CONSTRAINT			OWNER_PK		PRIMARY KEY(OwnerID),
	CONSTRAINT			OWNER_TYPE_CHECK CHECK (OwnerType IN 
						('Individual','Corporation'))
	);

CREATE SEQUENCE seqOID INCREMENT BY 1 START WITH 1;

CREATE TABLE OWNED_PROPERTY(
	PropertyID			Int				NOT NULL,
	PropertyName		VarChar(50)		NOT NULL,
	PropertyType		VarChar(50)		NOT NULL,
	Street			Char(35)		NOT NULL,
	City				Char(35)		NOT NULL,
	State				Char(2)			NOT NULL,
	ZIP				Char(10)		NOT NULL,
	OwnerID				Int				NOT NULL,
	CONSTRAINT			OWNED_PROPERTY_PK	PRIMARY KEY(PropertyID),
	CONSTRAINT			PROPERTY_OWNER_FK	FOREIGN KEY (OwnerID)
							REFERENCES OWNER(OwnerID),
	CONSTRAINT			PROPERTY_TYPE_CHECK CHECK (PropertyType IN
					 ('Office','Apartments','Private Residence'))
	);

CREATE SEQUENCE seqOPID INCREMENT BY 1 START WITH 1;

CREATE TABLE EMPLOYEE(
	EmployeeID			Int				NOT NULL,
	LastName			Char(25)		NOT NULL,
	FirstName			Char(25)		NOT NULL,
	CellPhone			Char(12)		NOT NULL,
	ExperienceLevel		Char(15)		NOT NULL,
	CONSTRAINT			EMPLOYEE_PK		PRIMARY KEY(EmployeeID),
	CONSTRAINT			EXPERIENCE_LEVEL_CHECK CHECK (ExperienceLevel IN
					 ('Junior','Senior','Master'))
	);

CREATE SEQUENCE seqEID INCREMENT BY 1 START WITH 1;

CREATE TABLE GG_SERVICE(
	ServiceID			Int				NOT NULL,
	ServiceDescription	VarChar(100)	NOT NULL,
	CostPerHour			Number (6,2)	NULL,
	CONSTRAINT			GG_SERVICE_PK 		
							PRIMARY KEY(ServiceID)
	);

CREATE SEQUENCE seqGGSID INCREMENT BY 1 START WITH 1;

CREATE TABLE PROPERTY_SERVICE(
	PropertyServiceID	Int				NOT NULL,
	PropertyID			Int				NOT NULL,
	ServiceID			Int				NOT NULL,
	ServiceDate			Date			NOT NULL,
	EmployeeID			Int				NOT NULL,
	HoursWorked			Number (4,2)	NULL,
	CONSTRAINT			PROP_SERVICE_PK 		
							PRIMARY KEY(PropertyServiceID),
	CONSTRAINT			PROP_SERVICE_PROP_FK FOREIGN KEY (PropertyID)
							REFERENCES OWNED_PROPERTY(PropertyID),
	CONSTRAINT			PROP_SERVICE_SERVICE_FK FOREIGN KEY (ServiceID)
							REFERENCES GG_SERVICE(ServiceID),
	CONSTRAINT			SERVICE_EMP_FK FOREIGN KEY (EmployeeID)
							REFERENCES EMPLOYEE(EmployeeID)
	);

CREATE SEQUENCE seqPSID INCREMENT BY 1 START WITH 1;

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