    -- creates some test-tables and data
    -- DROP TABLE EMPLOYEE;
    -- DROP TABLE DEPARTMENT;
    -- DROP TABLE SALARYGRADE;
    -- DROP TABLE BONUS;
    -- DROP TABLE PROJECT;
    -- DROP TABLE PROJECT_PARTICIPATION;
    -- DROP TABLE ROLE;

    CREATE TABLE EMPLOYEE(
       empno      INTEGER NOT NULL,
       name       VARCHAR(10),
       job        VARCHAR(9),
       boss       INTEGER,
       hiredate   VARCHAR(12),
       salary     DECIMAL(7, 2),
       comm       DECIMAL(7, 2),
       deptno     INTEGER
    );

    CREATE TABLE DEPARTMENT(
       deptno     INTEGER NOT NULL,
       name       VARCHAR(14),
       location   VARCHAR(13)
    );

    CREATE TABLE SALARYGRADE(
       grade      INTEGER NOT NULL,
       losal      INTEGER NOT NULL,
       hisal      INTEGER NOT NULL
    );

    CREATE TABLE BONUS (
       ename      VARCHAR(10) NOT NULL,
       job        VARCHAR(9) NOT NULL,
       sal        DECIMAL(7, 2),
       comm       DECIMAL(7, 2)
    );

    CREATE TABLE PROJECT(
       projectno    INTEGER NOT NULL,
       description  VARCHAR(100),
       start_date   VARCHAR(12),
       end_date     VARCHAR(12)
    );

    CREATE TABLE PROJECT_PARTICIPATION(
       projectno    INTEGER NOT NULL,
       empno        INTEGER NOT NULL,
       start_date   VARCHAR(12) NOT NULL,
       end_date     VARCHAR(12),
       role_id      INTEGER
    );

    CREATE TABLE ROLE(
       role_id      INTEGER NOT NULL,
       description  VARCHAR(100)
    );

    -- Primary Keys
    ALTER TABLE EMPLOYEE
       ADD CONSTRAINT emp_pk
       PRIMARY KEY (empno);

    ALTER TABLE DEPARTMENT
       ADD CONSTRAINT dept_pk
       PRIMARY KEY (deptno);

    ALTER TABLE SALARYGRADE
       ADD CONSTRAINT salgrade_pk
       PRIMARY KEY (grade);

    ALTER TABLE BONUS
       ADD CONSTRAINT bonus_pk
       PRIMARY KEY (ename, job);

    ALTER TABLE PROJECT
       ADD CONSTRAINT project_pk
       PRIMARY KEY (projectno);

    ALTER TABLE PROJECT_PARTICIPATION
       ADD CONSTRAINT participation_pk
       PRIMARY KEY (projectno, empno, start_date);

    ALTER TABLE ROLE
       ADD CONSTRAINT role_pk
       PRIMARY KEY (role_id);

    -- EMPLOYEE to DEPARTMENT
    ALTER TABLE EMPLOYEE
       ADD CONSTRAINT department
       FOREIGN KEY (deptno)
       REFERENCES DEPARTMENT (deptno);

    -- EMPLOYEE to EMPLOYEE
    ALTER TABLE EMPLOYEE
       ADD CONSTRAINT boss
       FOREIGN KEY (boss)
       REFERENCES EMPLOYEE (empno);

    -- EMPLOYEE to PROJECT_PARTICIPATION
    ALTER TABLE PROJECT_PARTICIPATION
       ADD CONSTRAINT employee
       FOREIGN KEY (empno)
       REFERENCES EMPLOYEE (empno);

    -- PROJECT to PROJECT_PARTICIPATION
    ALTER TABLE PROJECT_PARTICIPATION
       ADD CONSTRAINT project
       FOREIGN KEY (projectno)
       REFERENCES PROJECT (projectno);

    -- ROLE to PROJECT_PARTICIPATION
    ALTER TABLE PROJECT_PARTICIPATION
       ADD CONSTRAINT role
       FOREIGN KEY (role_id)
       REFERENCES ROLE (role_id);

    -- data
    INSERT INTO DEPARTMENT VALUES (10, 'ACCOUNTING', 'NEW YORK');
    INSERT INTO DEPARTMENT VALUES (20, 'RESEARCH',   'DALLAS');
    INSERT INTO DEPARTMENT VALUES (30, 'SALES',      'CHICAGO');
    INSERT INTO DEPARTMENT VALUES (40, 'OPERATIONS', 'BOSTON');

    INSERT INTO EMPLOYEE VALUES (7839, 'KING',   'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
        INSERT INTO EMPLOYEE VALUES (7566, 'JONES',  'MANAGER',   7839, '1981-04-02',  2975, NULL, 20);
           INSERT INTO EMPLOYEE VALUES(7788, 'SCOTT',  'ANALYST',   7566, '1982-12-09', 3000, NULL, 20);
              INSERT INTO EMPLOYEE VALUES(7876, 'ADAMS',  'CLERK',     7788, '1983-01-12', 1100, NULL, 20);
           INSERT INTO EMPLOYEE VALUES(7902, 'FORD',   'ANALYST',   7566, '1981-12-03',  3000, NULL, 20);
              INSERT INTO EMPLOYEE VALUES(7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800, NULL, 20);
        INSERT INTO EMPLOYEE VALUES (7698, 'BLAKE',  'MANAGER',   7839, '1981-05-01',  2850, NULL, 30);
           INSERT INTO EMPLOYEE VALUES(7499, 'ALLEN',  'SALESMAN',  7698, '1981-02-20', 1600,  300, 30);
           INSERT INTO EMPLOYEE VALUES(7521, 'WARD',   'SALESMAN',  7698, '1981-02-22', 1250,  500, 30);
           INSERT INTO EMPLOYEE VALUES(7654, 'MARTIN', 'SALESMAN',  7698, '1981-09-28', 1250, 1400, 30);
           INSERT INTO EMPLOYEE VALUES(7844, 'TURNER', 'SALESMAN',  7698, '1981-09-08',  1500,    0, 30);
           INSERT INTO EMPLOYEE VALUES(7900, 'JAMES',  'CLERK',     7698, '1981-12-03',   950, NULL, 30);
        INSERT INTO EMPLOYEE VALUES(7782, 'CLARK',  'MANAGER',   7839, '1981-06-09',  2450, NULL, 10);
           INSERT INTO EMPLOYEE VALUES(7934, 'MILLER', 'CLERK',     7782, '1982-01-23', 1300, NULL, 10);

    INSERT INTO SALARYGRADE VALUES (1,  700, 1200);
    INSERT INTO SALARYGRADE VALUES (2, 1201, 1400);
    INSERT INTO SALARYGRADE VALUES (3, 1401, 2000);
    INSERT INTO SALARYGRADE VALUES (4, 2001, 3000);
    INSERT INTO SALARYGRADE VALUES (5, 3001, 9999);

    INSERT INTO ROLE VALUES (100, 'Developer');
    INSERT INTO ROLE VALUES (101, 'Researcher');
    INSERT INTO ROLE VALUES (102, 'Project manager');

    INSERT INTO PROJECT VALUES (1001, 'Development of Novel Magnetic Suspension System', '2006-01-01', '2007-08-13');
    INSERT INTO PROJECT VALUES (1002, 'Research on thermofluid dynamics in Microdroplets', '2006-08-22', '2007-03-20');
    INSERT INTO PROJECT VALUES (1003, 'Foundation of Quantum Technology', '2007-02-24', '2008-07-31');
    INSERT INTO PROJECT VALUES (1004, 'High capacity optical network', '2008-01-01', null);

    INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7902, '2006-01-01', '2006-12-30', 102);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7369, '2006-01-01', '2007-08-13', 100);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1001, 7788, '2006-05-15', '2006-11-01', 100);

    INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7876, '2006-08-22', '2007-03-20', 102);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7782, '2006-08-22', '2007-03-20', 101);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1002, 7934, '2007-01-01', '2007-03-20', 101);

    INSERT INTO PROJECT_PARTICIPATION VALUES (1003, 7566, '2007-02-24', '2008-07-31', 102);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1003, 7900, '2007-02-24', '2007-01-31', 101);

    INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7499, '2008-01-01', null, 102);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7521, '2008-05-01', null, 101);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7654, '2008-04-15', null, 101);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7844, '2008-02-01', null, 101);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7900, '2008-03-01', '2008-04-01', 101);
    INSERT INTO PROJECT_PARTICIPATION VALUES (1004, 7900, '2008-05-20', null, 101);