CREATE TABLE departments
    ( department_id    NUMBER( 4 )
    , department_name  VARCHAR2( 30 )
    , manager_id       NUMBER( 6 )
    ) ;

ALTER TABLE departments
ADD ( CONSTRAINT dept_id_pk
       		 PRIMARY KEY (department_id)) ;

CREATE TABLE employees
    ( employee_id    NUMBER( 6 )
    , first_name     VARCHAR2( 20 )
    , last_name      VARCHAR2( 25 )
    , email          VARCHAR2( 25 )
    , phone_number   VARCHAR2( 20 )
    , hire_date      DATE
    , job_id         VARCHAR2( 10 )
    , salary         NUMBER( 8 , 2 )
    , commission_pct NUMBER( 2 , 2 )
    , manager_id     NUMBER( 6 )
    , department_id  NUMBER( 4 )
    ) ;

CREATE UNIQUE INDEX emp_emp_id_pk
ON employees (employee_id) ;

ALTER TABLE employees
ADD ( CONSTRAINT     emp_emp_id_pk
                     PRIMARY KEY (employee_id)
    , CONSTRAINT     emp_dept_fk
                     FOREIGN KEY (department_id)
                      REFERENCES departments
    , CONSTRAINT     emp_manager_fk
                     FOREIGN KEY (manager_id)
                      REFERENCES employees
    ) ;