CREATE TABLE OWNER(
	OwnerID	        Int		       NOT NULL		 IDENTITY (1, 1) PRIMARY KEY,
	OwnerName	    Char(35)       NOT NULL,
    OwnerEmail      Char(50)       NULL,
    OwnerType       Char(25)       NOT NULL,

);

CREATE TABLE PROPERTY(
	PropertyID	        Int		   NOT NULL		 IDENTITY (1, 1) PRIMARY KEY,
	PropertyName	    Char(25)   NOT NULL,
    Street              Char(25)   NOT NULL,
    City                Char(25)   NOT NULL,
    State               Char(2)    NOT NULL,
    Zip                 Int        NOT NULL,
    OwnerID             Int        NOT NULL,

    CONSTRAINT  PROPERTY_FK    FOREIGN KEY(OwnerID)      REFERENCES OWNER(OwnerID)
    ON UPDATE CASCADE
);

CREATE TABLE GG_EMPLOYEE (
    EmployeeID          Int         NOT NULL   IDENTITY (1, 1) PRIMARY KEY,
    LastName            Char(25)    NOT NULL,
    FirstName           Char(25)    NOT NULL,
    CellPhone           Char(12)    NOT NULL,
    ExperienceLevel     Char(15)    NOT NULL,
);

CREATE TABLE SERVICE (
    PropertyID          Int         NOT NULL,
    EmployeeID          Int         NOT NULL,
    ServiceDate         DateTime    NOT NULL,
    HoursWorked         Numeric(6,2)NOT NULL,
    ExperienceLevel     Char(15)    NOT NULL,

    CONSTRAINT  SERVICE_PROPERTY_FK    FOREIGN KEY(PropertyID)      REFERENCES PROPERTY(PropertyID)
    ON UPDATE CASCADE,

    CONSTRAINT  SERVICE_EMPLOYEE_FK    FOREIGN KEY(EmployeeID)      REFERENCES GG_EMPLOYEE(EmployeeID)
    ON UPDATE CASCADE
);

INSERT INTO OWNER(OwnerName, OwnerEmail, OwnerType) VALUES("Mary Jones", "MaryJones@somewhere.com", "Individual");
INSERT INTO OWNER(OwnerName, OwnerEmail, OwnerType) VALUES("DT Enterprises", "DTE@dte.com", "Corporation");
INSERT INTO OWNER(OwnerName, OwnerEmail, OwnerType) VALUES("Sam Douglas", NULL, "Individual");
INSERT INTO OWNER(OwnerName, OwnerEmail, OwnerType) VALUES("UNY Enterprises", "UNYE@unye.com", "Corporation");
INSERT INTO OWNER(OwnerName, OwnerEmail, OwnerType) VALUES("Doug Samuels", "DougSamuels@somewhere.com", "Individual")

INSERT INTO PROPERTY(PropertyName, Street, City, State, Zip, OwnerID) VALUES("Eastlake Building", "123 Eastlake", "Seattle", WA, 98119, 2);
INSERT INTO PROPERTY(PropertyName, Street, City, State, Zip, OwnerID) VALUES("Elm St Apts", "4 East Elm", "Lynwood", WA, 9823, 1);
INSERT INTO PROPERTY(PropertyName, Street, City, State, Zip, OwnerID) VALUES("Jefferson Hill", "42 West 7th St", "Bellevue", WA, 98007, 2);
INSERT INTO PROPERTY(PropertyName, Street, City, State, Zip, OwnerID) VALUES("Lake View Apts", "1265 32nd Avenue", "Redmond", NY, 14604, 3);
INSERT INTO PROPERTY(PropertyName, Street, City, State, Zip, OwnerID) VALUES("Kodak Heights Apts", "65 32nd Avenue", "Rochester", WA, 14604, 4);
INSERT INTO PROPERTY(PropertyName, Street, City, State, Zip, OwnerID) VALUES("Private Residence", "1456 48th St", "Bellevue", WA, 98007, 1);
INSERT INTO PROPERTY(PropertyName, Street, City, State, Zip, OwnerID) VALUES("Private Residence", "1567 51st St", "Bellevue", WA, 98007, 3;
INSERT INTO PROPERTY(PropertyName, Street, City, State, Zip, OwnerID) VALUES("Private Residence", "567 151st St", "Rochester", WA, 98119, 5);

INSERT INTO GG_EMPLOYEE(LastName, FirstName, CellPhone, ExperienceLevel) VALUES("Smith", "Sam", 206-254-1234, "Master");
INSERT INTO GG_EMPLOYEE(LastName, FirstName, CellPhone, ExperienceLevel) VALUES("Evanston", "John", 206-254-2345, "Senior");
INSERT INTO GG_EMPLOYEE(LastName, FirstName, CellPhone, ExperienceLevel) VALUES("Murray", "Dale", 206-254-3456, "Junior");
INSERT INTO GG_EMPLOYEE(LastName, FirstName, CellPhone, ExperienceLevel) VALUES("Murphy", "Jerry", 585-545-8765, "Master");
INSERT INTO GG_EMPLOYEE(LastName, FirstName, CellPhone, ExperienceLevel) VALUES("Fontaine", "Joan", 206-254-4567, "Senior");

INSERT INTO SERVICE(ServiceDate, HoursWorked) VALUES(2012-05-05 00:00:00:000, 4.50);
INSERT INTO SERVICE(ServiceDate, HoursWorked) VALUES(2012-05-08 00:00:00:000, 2.75);
INSERT INTO SERVICE(ServiceDate, HoursWorked) VALUES(2012-05-08 00:00:00:000, 4.50);
INSERT INTO SERVICE(ServiceDate, HoursWorked) VALUES(2012-05-19 00:00:00:000, 3.00);
INSERT INTO SERVICE(ServiceDate, HoursWorked) VALUES(2012-05-12 00:00:00:000, 7.50);
INSERT INTO SERVICE(ServiceDate, HoursWorked) VALUES(2012-05-10 00:00:00:000, 2.50);
INSERT INTO SERVICE(ServiceDate, HoursWorked) VALUES(2012-05-19 00:00:00:000, 2.50);
INSERT INTO SERVICE(ServiceDate, HoursWorked) VALUES(2012-05-15 00:00:00:000, 2.75);