DROP TABLE "tbl_Employees" IF EXISTS;

CREATE TABLE "tbl_Employees" 
     (
	 "employee-ID"
  	        INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) 
	  	NOT NULL 
	  	PRIMARY KEY

	,"Last Name"     
	  	VARCHAR(100)
	  	NOT NULL

	,"First Name"     
	  	VARCHAR(100)
	  	NOT NULL

	,"Gender"     
	  	VARCHAR(6)

	,"Start Date"     
	  	DATE

	,"End Date"     
	  	DATE

	,"Address"     
	  	VARCHAR(100)

	,"City"     
	  	VARCHAR(100)

	,"State"     
	  	VARCHAR(100)

	,"Zip"     
	  	VARCHAR(100)

	,"Department-ID"     
	  	INTEGER
                Default 0

	,"Group-ID"     
	  	INTEGER
                Default 0

	,"Hourly Wage"     
	  	NUMERIC

	,"Employee-Pics"     
	  	LONGVARBINARY

        ,CONSTRAINT "CK_GNDR"          CHECK       ( "Gender"  in ('Male', 'Female' ))

        ,CONSTRAINT "CK_STRT-END_DATE" CHECK       ( "End Date" > "Start Date" )

        ,CONSTRAINT "FK_DEPT-ID"       FOREIGN KEY ( "Department-ID")  
                     REFERENCES "tbl_Department" ("Department-ID")
                     ON DELETE SET DEFAULT ON UPDATE CASCADE

        ,CONSTRAINT "FK_GROUP-ID"      FOREIGN KEY ( "Group-ID") 
                     REFERENCES "tbl_Group"      ("Group-ID")
                     ON DELETE SET DEFAULT ON UPDATE CASCADE
     );