--------------------------------------------------------
--  DDL for Table ADDRESS
--------------------------------------------------------

CREATE TABLE ADDRESS (	
	ID NUMBER(10) NOT NULL, 
	
	STREET VARCHAR2(50), 
	CITY VARCHAR2(50), 
	STATE VARCHAR2(3), 
	ZIP VARCHAR2(5),

	CREATEDAT TIMESTAMP (6), 
	CREATEDFROM VARCHAR2(6), 
	UPDATEDAT TIMESTAMP (6), 
	UPDATEDFROM VARCHAR2(6), 

	CONSTRAINT ADDRESS_PK PRIMARY KEY (ID)
);

--------------------------------------------------------
--  DDL for Table DEPARTMENT
--------------------------------------------------------

CREATE TABLE DEPARTMENT (
	ID NUMBER(10) NOT NULL, 
	
	NAME VARCHAR2(255) NOT NULL,
	
	CREATEDAT TIMESTAMP (6), 
	CREATEDFROM VARCHAR2(6), 
	UPDATEDAT TIMESTAMP (6), 
	UPDATEDFROM VARCHAR2(6), 

	CONSTRAINT DEPARTMENT_PK PRIMARY KEY (ID)
);

--------------------------------------------------------
--  DDL for Table EMPLOYEE
--------------------------------------------------------

CREATE TABLE EMPLOYEE (
	ID NUMBER(10) NOT NULL, 

	NAME VARCHAR2(255) NOT NULL, 
	SALARY NUMBER(7,2), 

	CREATEDAT TIMESTAMP (6), 
	CREATEDFROM VARCHAR2(6), 
	UPDATEDAT TIMESTAMP (6), 
	UPDATEDFROM VARCHAR2(6), 

	EMPLOYEE_ID NUMBER(10), 
	DEPARTMENT_ID NUMBER(10), 
	ADDRESS_ID NUMBER(10) NOT NULL,

	CONSTRAINT EMPLOYEE_PK PRIMARY KEY (ID)
);

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_EMPLOYEE_FK FOREIGN KEY (EMPLOYEE_ID)
	REFERENCES EMPLOYEE (ID);

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_DEPARTMENT_FK FOREIGN KEY (DEPARTMENT_ID)
	REFERENCES DEPARTMENT (ID);

ALTER TABLE EMPLOYEE ADD CONSTRAINT EMPLOYEE_ADDRESS_FK FOREIGN KEY (ADDRESS_ID)
	REFERENCES ADDRESS (ID);

--------------------------------------------------------
--  DDL for Table PHONE
--------------------------------------------------------

CREATE TABLE PHONE (
	ID NUMBER(10) NOT NULL, 

	PHONENUMBER VARCHAR2(10) NOT NULL, 
	TYPE VARCHAR2(1) NOT NULL,
	
	CREATEDAT TIMESTAMP (6), 
	CREATEDFROM VARCHAR2(6), 
	UPDATEDAT TIMESTAMP (6), 
	UPDATEDFROM VARCHAR2(6),
	
	EMPLOYEE_ID NUMBER(10),

	CONSTRAINT PHONE_PK PRIMARY KEY (ID)
);

ALTER TABLE PHONE ADD CONSTRAINT PHONE_EMPLOYEE_FK FOREIGN KEY (EMPLOYEE_ID)
	REFERENCES EMPLOYEE (ID);

--------------------------------------------------------
--  DDL for Table PROJECT
--------------------------------------------------------

CREATE TABLE PROJECT (
	ID NUMBER(10) NOT NULL, 
	
	DTYPE VARCHAR2(50), 
	NAME VARCHAR2(255) NOT NULL, 

	CREATEDAT TIMESTAMP (6), 
	CREATEDFROM VARCHAR2(6), 
	UPDATEDAT TIMESTAMP (6), 
	UPDATEDFROM VARCHAR2(6), 

	CONSTRAINT PROJECT_PK PRIMARY KEY (ID)
);

--------------------------------------------------------
--  DDL for Table PROJECT_EMPLOYEE
--------------------------------------------------------

CREATE TABLE PROJECT_EMPLOYEE (
	PROJECT_ID NUMBER(10) NOT NULL, 
	EMPLOYEE_ID NUMBER(10) NOT NULL,

	CONSTRAINT PROJECT_EMPLOYEE_PK PRIMARY KEY (PROJECT_ID, EMPLOYEE_ID)
);

ALTER TABLE PROJECT_EMPLOYEE ADD CONSTRAINT PROJECT_EMPLOYEE_EMPLOYEE_FK FOREIGN KEY (EMPLOYEE_ID)
	REFERENCES EMPLOYEE (ID);

ALTER TABLE PROJECT_EMPLOYEE ADD CONSTRAINT PROJECT_EMPLOYEE_PROJECT_FK FOREIGN KEY (PROJECT_ID)
	REFERENCES PROJECT (ID);