Projects Schema

create database projects;
use projects;

CREATE TABLE E_LEGAL_FOUNDATIONS
(
    LABEL       VARCHAR(4)   NOT NULL,
    DESCRIPTION VARCHAR(255) NOT NULL,
    PRIMARY KEY (LABEL)
);

INSERT INTO E_LEGAL_FOUNDATIONS (LABEL, DESCRIPTION)
VALUES ('P_26', 'Requestfundingprojects that are Smallprojects');
INSERT INTO E_LEGAL_FOUNDATIONS (LABEL, DESCRIPTION)
values ('P_27', 'Researchfundingprojects not supported by the EU, Stipendiums ');
INSERT INTO E_LEGAL_FOUNDATIONS (LABEL, DESCRIPTION)
values ('P_28', 'Researchfundingprojects supported by the EU');
INSERT INTO E_LEGAL_FOUNDATIONS (LABEL, DESCRIPTION)
values ('P_29', 'Managementprojects, Requestfundingprojects that are not Smallprojects');

CREATE TABLE E_PROJECT_STATES
(
    LABEL VARCHAR(12) NOT NULL,
    PRIMARY KEY (LABEL)
);

INSERT INTO E_PROJECT_STATES
VALUES ('DRAFT');
INSERT INTO E_PROJECT_STATES
VALUES ('RE_DRAFT');
INSERT INTO E_PROJECT_STATES
VALUES ('CANCLED');
INSERT INTO E_PROJECT_STATES
VALUES ('RUNNING');
INSERT INTO E_PROJECT_STATES
VALUES ('FINISHED');
INSERT INTO E_PROJECT_STATES
VALUES ('IN_APPROVAL');
INSERT INTO E_PROJECT_STATES
VALUES ('APPROVED');
INSERT INTO E_PROJECT_STATES
VALUES ('REJECTED');


CREATE TABLE PROJECTS_BT
(
    PROJECT_ID       INT NOT NULL AUTO_INCREMENT,
    TITLE            VARCHAR(100)  NOT NULL UNIQUE,
    DESCRIPTION      TEXT,
    CREATED_AT       DATE          NOT NULL,
    LEGAL_FOUNDATION VARCHAR(4)    NOT NULL,
    PRIMARY KEY (PROJECT_ID),
    CONSTRAINT FK_PROJECTS_PROJECT_ID FOREIGN KEY (LEGAL_FOUNDATION)
        REFERENCES E_LEGAL_FOUNDATIONS (LABEL)
);


INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (1,
        'Finite Elemente in der Simulation. Zur Simulation der Kraftstoffverbrennung in Motoren sollen Finite Elemente Methoden eingesetzt werden.',
        'P_27', 'Finite Elemente in der Simulation', STR_TO_DATE('21.12.1997', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (2,
        'Schnellhärteverfahren in der Betontechnik. Für den Einsatz unter extremen Bedingungen (Brückenbau) sollen Schnellhärteverfahren in der Betontechnik gefunden werdne.',
        'P_29', 'Schnellhärteverfahren in der Betontechnik', STR_TO_DATE('01.01.2010', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (3, 'AI in der Robotertechnik. Entwickeln einer leistungsstarken AI für die Robotersteuerung im Roboterfussball',
        'P_28', 'AI in der Robotortechnik', STR_TO_DATE('04.02.2010', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (4, 'Brückenbau im Meer. Es sollen Methoden zum Brückenbau zwischen Inseln erdacht und perfektioniert werden',
        'P_29', 'Brückenbau im Meer', STR_TO_DATE('12.03.2010', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (5, 'Aufschüttung von künstlichen Inseln', 'P_26', 'Aufschüttung von künstlichen Inseln',
        STR_TO_DATE('30.03.2010', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (6, 'Bau von industriellen Flughäfen auf künstlichen Inseln.', 'P_29',
        'Bau von industriellen Flughäfen auf künstlichen Inseln', STR_TO_DATE('05.05.2012', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (7, 'Numerische Methoden in der Differentiellen Analysis', 'P_27',
        'Numerische Methoden in der Differentiellen Analysis', STR_TO_DATE('07.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (8, 'Numerische Methoden in der Geometrischen Algebra', 'P_27',
        'Numerische Methoden in der Geometrischen Algebra', STR_TO_DATE('01.08.2015', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (9, 'Computeralgorithmen auf Graphen', 'P_27', 'Computeralgorithmen auf Graphen',
        STR_TO_DATE('02.08.2013', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (10, 'Computeralgorithmen in der Diskreten Mathematik', 'P_27',
        'Computeralgorithmen in der Diskreten Mathmatik', STR_TO_DATE('10.09.2020', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (11, 'Statistische Prognosen in der Wahlanalyse', 'P_29', 'Statistische Prognosen in der Wahlanalyse',
        STR_TO_DATE('21.12.2019', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (12, 'Algorithmen in der Mustererkennung', 'P_27', 'Algorithmen in der Mustererkennung',
        STR_TO_DATE('04.08.2019', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (13, 'Gesichtsmerkmale in der Mustererkennung', 'P_27', 'Gesichtmerkmale in der Mustererkennung',
        STR_TO_DATE('05.07.2019', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (14, 'Generierung von Zufallszahlen', 'P_28', 'Generierung von Zufallszahlen',
        STR_TO_DATE('05.01.2017', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (15, 'Gleichverteilte Zufallszahlen in statistischen Algorithmen', 'P_27',
        'Gleichverteilte Zufallszahlen in statistischen Algorithmen', STR_TO_DATE('06.04.2016', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (16, 'Codierungsverfahren', 'P_27', 'Codierungsverfahren', STR_TO_DATE('07.09.2017', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (17, 'Codes in der Gruppenalgebra', 'P_28', 'Codes in der Gruppenalgebra', STR_TO_DATE('04.03.2013', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (18, 'Kürzeste Wege in Graphen berechnen', 'P_27', 'Kürzeste Wege in Graphen berechnen',
        STR_TO_DATE('12.03.2019', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (19, 'Numerische Methoden für die Fourier Transformation', 'P_26',
        'Numerische Methoden für die Fourier Transformation', STR_TO_DATE('14.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (20, 'Infrastruktur im alpinen Gelände', 'P_29', 'Infrastruktur im alpinen Gelände',
        STR_TO_DATE('21.05.2016', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (21, 'Tunnelbau im alpienen Gelände', 'P_26', 'Tunnelbau im alpinen Gelände',
        STR_TO_DATE('25.02.2017', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (22, 'Entwicklung künstlicher Intelligenz', 'P_29', 'Entwicklung künstlicher Intelligenz',
        STR_TO_DATE('24.12.2019', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (23, 'Dekalrative Informationssysteme', 'P_29', 'Dekalrative Informationssysteme',
        STR_TO_DATE('04.04.2015', '%d.%m.%Y'));
INSERT INTO PROJECTS_BT (PROJECT_ID, description, legal_foundation, TITLE, CREATED_AT)
VALUES (24, 'Design Pattern in der OOP', 'P_29', 'Design Pattern in der OOP', STR_TO_DATE('22.12.2015', '%d.%m.%Y'));


CREATE TABLE PROJECT_HAS_STATES_JT
(
    PROJECT_ID       INT NOT NULL,
    PROJECT_STATE    VARCHAR(12)   NOT NULL,
    STATE_CHANGED_AT DATE          NOT NULL,
    PRIMARY KEY (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT),
    CONSTRAINT FK_PHS_PROJECT_ID FOREIGN KEY (PROJECT_ID)
        REFERENCES PROJECTS_BT (PROJECT_ID),
    CONSTRAINT FK_PHS_PROJECT_STATE FOREIGN KEY (PROJECT_STATE)
        REFERENCES E_PROJECT_STATES (LABEL)
);

INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (1, 'DRAFT', STR_TO_DATE('21.12.1997', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (1, 'IN_APPROVAL', STR_TO_DATE('01.01.1998', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (1, 'APPROVED', STR_TO_DATE('01.03.1998', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (1, 'RUNNING', STR_TO_DATE('02.03.1998', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (1, 'FINISHED', STR_TO_DATE('01.01.1999', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (2, 'DRAFT', STR_TO_DATE('01.01.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (2, 'CANCLED', STR_TO_DATE('01.02.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (3, 'DRAFT', STR_TO_DATE('04.02.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (3, 'IN_APPROVAL', STR_TO_DATE('05.02.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (3, 'APPROVED', STR_TO_DATE('21.02.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (3, 'RUNNING', STR_TO_DATE('22.02.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (3, 'FINISHED', STR_TO_DATE('01.02.2012', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (4, 'DRAFT', STR_TO_DATE('12.03.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (4, 'IN_APPROVAL', STR_TO_DATE('20.03.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (4, 'APPROVED', STR_TO_DATE('01.04.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (4, 'RUNNING', STR_TO_DATE('02.04.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (4, 'FINISHED', STR_TO_DATE('20.12.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (5, 'DRAFT', STR_TO_DATE('30.03.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (5, 'IN_APPROVAL', STR_TO_DATE('1.04.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (5, 'CANCLED', STR_TO_DATE('10.04.2010', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (6, 'DRAFT', STR_TO_DATE('05.05.2012', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (7, 'DRAFT', STR_TO_DATE('07.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (7, 'IN_APPROVAL', STR_TO_DATE('10.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (7, 'APPROVED', STR_TO_DATE('21.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (7, 'RUNNING', STR_TO_DATE('21.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (7, 'FINISHED', STR_TO_DATE('12.12.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (8, 'DRAFT', STR_TO_DATE('01.08.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (8, 'IN_APPROVAL', STR_TO_DATE('02.08.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (8, 'REJECTED', STR_TO_DATE('04.08.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (8, 'RE_DRAFT', STR_TO_DATE('05.08.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (8, 'IN_APPROVAL', STR_TO_DATE('07.08.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (8, 'APPROVED', STR_TO_DATE('10.08.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (8, 'RUNNING', STR_TO_DATE('12.08.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (8, 'FINISHED', STR_TO_DATE('01.01.2017', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (9, 'DRAFT', STR_TO_DATE('02.08.2013', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (9, 'IN_APPROVAL', STR_TO_DATE('10.08.2013', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (9, 'APPROVED', STR_TO_DATE('30.08.2013', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (9, 'RUNNING', STR_TO_DATE('01.09.2013', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (9, 'FINISHED', STR_TO_DATE('10.09.2014', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (10, 'DRAFT', STR_TO_DATE('10.09.2020', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (11, 'DRAFT', STR_TO_DATE('21.12.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (12, 'DRAFT', STR_TO_DATE('01.01.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (12, 'IN_APPROVAL', STR_TO_DATE('10.01.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (12, 'APPROVED', STR_TO_DATE('12.01.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (12, 'RUNNING', STR_TO_DATE('21.01.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (12, 'FINISHED', STR_TO_DATE('12.12.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (13, 'DRAFT', STR_TO_DATE('05.07.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (13, 'IN_APPROVAL', STR_TO_DATE('08.09.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (13, 'APPROVED', STR_TO_DATE('10.09.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (13, 'RUNNING', STR_TO_DATE('11.09.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (14, 'DRAFT', STR_TO_DATE('05.01.2017', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (14, 'CANCLED', STR_TO_DATE('11.01.2017', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (15, 'DRAFT', STR_TO_DATE('06.04.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (15, 'IN_APPROVAL', STR_TO_DATE('05.05.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (15, 'APPROVED', STR_TO_DATE('06.05.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (15, 'RUNNING', STR_TO_DATE('06.05.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (15, 'FINISHED', STR_TO_DATE('14.10.2018', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (16, 'DRAFT', STR_TO_DATE('07.09.2017', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (16, 'CANCLED', STR_TO_DATE('09.09.2017', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (17, 'DRAFT', STR_TO_DATE('04.03.2013', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (17, 'CANCLED', STR_TO_DATE('05.03.2013', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (18, 'DRAFT',
        STR_TO_DATE('12.12.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (18, 'IN_APPROVAL',
        STR_TO_DATE('14.04.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (19, 'DRAFT', STR_TO_DATE('14.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (19, 'IN_APPROVAL', STR_TO_DATE('20.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (19, 'REJECTED', STR_TO_DATE('22.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (19, 'CANCLED', STR_TO_DATE('23.07.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (20, 'DRAFT',
        STR_TO_DATE('21.05.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (20, 'CANCLED',
        STR_TO_DATE('01.06.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (21, 'DRAFT',
        STR_TO_DATE('25.08.2020', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (21, 'IN_APPROVAL',
        STR_TO_DATE('01.10.2020', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (21, 'APPROVED',
        STR_TO_DATE('20.10.2020', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (22, 'DRAFT',
        STR_TO_DATE('24.12.2019', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (22, 'CANCLED',
        STR_TO_DATE('01.01.2020', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (23, 'DRAFT',
        STR_TO_DATE('04.04.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (23, 'CANCLED',
        STR_TO_DATE('05.05.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'DRAFT', STR_TO_DATE('22.12.2015', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'IN_APPROVAL', STR_TO_DATE('01.01.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'REJECTED', STR_TO_DATE('02.01.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'RE_DRAFT', STR_TO_DATE('12.01.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'IN_APPROVAL', STR_TO_DATE('13.01.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'REJECTED', STR_TO_DATE('20.01.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'RE_DRAFT', STR_TO_DATE('01.02.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'IN_APPROVAL', STR_TO_DATE('02.02.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'APPROVED', STR_TO_DATE('10.02.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'RUNNING', STR_TO_DATE('12.02.2016', '%d.%m.%Y'));
INSERT INTO PROJECT_HAS_STATES_JT (PROJECT_ID, PROJECT_STATE, STATE_CHANGED_AT)
VALUES (24, 'FINISHED', STR_TO_DATE('01.01.2018', '%d.%m.%Y'));

CREATE TABLE PROJECT_FORERUNNERS_JT
(
    PROJECT_ID INT NOT NULL,
    PARENT_ID  INT NOT NULL,
    PRIMARY KEY (PROJECT_ID),
    CONSTRAINT FK_PF_PROJECT_ID FOREIGN KEY (PROJECT_ID)
        REFERENCES PROJECTS_BT (PROJECT_ID),
    CONSTRAINT FK_PF_PARENT_ID FOREIGN KEY (PARENT_ID)
        REFERENCES PROJECTS_BT (PROJECT_ID)
);

INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (7, 1);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (8, 7);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (9, 8);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (10, 1);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (13, 12);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (18, 9);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (4, 2);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (5, 2);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (6, 5);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (14, 7);
INSERT INTO PROJECT_FORERUNNERS_JT (PROJECT_ID, PARENT_ID)
values (21, 20);


CREATE TABLE RESEARCH_FUNDING_PROJECTS
(
    PROJECT_ID       INT NOT NULL,
    IS_EU_SPONSORED  TINYINT     NOT NULL,
    IS_FFG_SPONSORED TINYINT     NOT NULL,
    IS_FWF_SPONSORED TINYINT     NOT NULL,
    PRIMARY KEY (PROJECT_ID),
    CONSTRAINT FK_RFP_PROJECT_ID FOREIGN KEY (PROJECT_ID)
        REFERENCES PROJECTS_BT (PROJECT_ID)
            ON DELETE CASCADE,
    CONSTRAINT CK_RFP_IS_EU_SPONSORED CHECK  ( IS_EU_SPONSORED IN  (0, 1) ),
    CONSTRAINT CK_RFP_IS_FWF_SPONSORED CHECK ( IS_FWF_SPONSORED IN (0, 1) ),
    CONSTRAINT CK_RFP_IS_FFG_SPONSORED CHECK ( IS_FFG_SPONSORED IN (0, 1) )
);

INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 0, 0, 1);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (1, 1, 0, 3);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 1, 1, 7);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 1, 1, 8);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 0, 1, 9);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 0, 1, 10);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 0, 0, 12);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 0, 0, 13);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (1, 0, 0, 14);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 0, 0, 15);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 0, 0, 16);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (1, 1, 0, 17);
INSERT INTO RESEARCH_FUNDING_PROJECTS (IS_EU_SPONSORED, IS_FFG_SPONSORED, IS_FWF_SPONSORED, project_id)
VALUES (0, 0, 1, 18);

CREATE TABLE REQUEST_FUNDING_PROJECTS
(
    PROJECT_ID       INT NOT NULL,
    IS_SMALL_PROJECT TINYINT     NOT NULL,
    PRIMARY KEY (PROJECT_ID),
    CONSTRAINT FK_RESEARCHFP_PROJECT_ID FOREIGN KEY (PROJECT_ID)
        REFERENCES PROJECTS_BT (PROJECT_ID)
            ON DELETE CASCADE,
    CONSTRAINT CK_RESEARCHP_SMALL_PROJECT CHECK ( IS_SMALL_PROJECT IN (0, 1) )
);

-- REQUEST_FUNDING_PROJECT
INSERT INTO REQUEST_FUNDING_PROJECTS (IS_SMALL_PROJECT, PROJECT_ID)
VALUES (0, 2);
INSERT INTO REQUEST_FUNDING_PROJECTS (IS_SMALL_PROJECT, PROJECT_ID)
VALUES (0, 4);
INSERT INTO REQUEST_FUNDING_PROJECTS (IS_SMALL_PROJECT, PROJECT_ID)
VALUES (1, 5);
INSERT INTO REQUEST_FUNDING_PROJECTS (IS_SMALL_PROJECT, PROJECT_ID)
VALUES (0, 6);
INSERT INTO REQUEST_FUNDING_PROJECTS (IS_SMALL_PROJECT, PROJECT_ID)
VALUES (0, 11);
INSERT INTO REQUEST_FUNDING_PROJECTS (IS_SMALL_PROJECT, PROJECT_ID)
VALUES (1, 19);
INSERT INTO REQUEST_FUNDING_PROJECTS (IS_SMALL_PROJECT, PROJECT_ID)
VALUES (0, 20);
INSERT INTO REQUEST_FUNDING_PROJECTS (IS_SMALL_PROJECT, PROJECT_ID)
VALUES (1, 21);

CREATE TABLE E_MANAGEMENT_DUTY
(
    LABEL VARCHAR(50) NOT NULL,
    PRIMARY KEY (LABEL)
);

INSERT INTO E_MANAGEMENT_DUTY
VALUES ('ADMINISTRATION_STAFF');
INSERT INTO E_MANAGEMENT_DUTY
VALUES ('ADMINISTRATION_STUDENTS');
INSERT INTO E_MANAGEMENT_DUTY
VALUES ('ADMINISTRATION_TEACHERS');
INSERT INTO E_MANAGEMENT_DUTY
VALUES ('FACILITIES');
INSERT INTO E_MANAGEMENT_DUTY
VALUES ('MAINTENANCE_REPAIR');
INSERT INTO E_MANAGEMENT_DUTY
VALUES ('DEVICES');
INSERT INTO E_MANAGEMENT_DUTY
VALUES ('LABOR');


CREATE TABLE MANAGEMENT_PROJECTS
(
    PROJECT_ID      INT NOT NULL,
    PROJECT_END     DATE          NOT NULL,
    MANAGEMENT_DUTY VARCHAR(50)   NOT NULL,
    PRIMARY KEY (PROJECT_ID),
    CONSTRAINT FK_MP_PROJECT_ID FOREIGN KEY (PROJECT_ID)
        REFERENCES PROJECTS_BT (PROJECT_ID),
    CONSTRAINT FK_MP_MANAGEMENT_DUTY FOREIGN KEY (MANAGEMENT_DUTY)
        REFERENCES E_MANAGEMENT_DUTY (LABEL)
);

INSERT INTO MANAGEMENT_PROJECTS (project_id, project_end, management_duty)
VALUES (22, STR_TO_DATE('05.01.2020', '%d.%m.%Y'), 'ADMINISTRATION_STUDENTS');
INSERT INTO MANAGEMENT_PROJECTS (project_id, project_end, management_duty)
VALUES (23, STR_TO_DATE('01.01.2016', '%d.%m.%Y'), 'LABOR');
INSERT INTO MANAGEMENT_PROJECTS (project_id, project_end, management_duty)
VALUES (24, STR_TO_DATE('01.03.2016', '%d.%m.%Y'), 'FACILITIES');



-- ------------------------------------------------------------------------------------------------------------------ --
-- TABLE: FACILITIES                                                                                                  --
-- ------------------------------------------------------------------------------------------------------------------ --
CREATE TABLE E_FACILITY_TYPE
(
    LABEL VARCHAR(10) NOT NULL,
    PRIMARY KEY (LABEL)
);

INSERT INTO E_FACILITY_TYPE
VALUES ('FACULTY');
INSERT INTO E_FACILITY_TYPE
VALUES ('INSTITUTE');

CREATE TABLE FACILITIES_ST
(
    FACILITY_ID    INT NOT NULL AUTO_INCREMENT,
    FACILITY_TYPE  VARCHAR(10)   NOT NULL,
    DESCRIPTION    TEXT,
    FACILITY_CODE  VARCHAR(7)    NOT NULL UNIQUE,
    FACILITY_TITLE VARCHAR(100)  NOT NULL UNIQUE,
    FACULTY_ID     INT,
    PRIMARY KEY (FACILITY_ID),
    CONSTRAINT FK_FACILITIES_TYPE FOREIGN KEY (FACILITY_TYPE)
        REFERENCES E_FACILITY_TYPE (LABEL),
    CONSTRAINT FK_FACILITIES_FACULTY_ID FOREIGN KEY (FACULTY_ID)
        REFERENCES FACILITIES_ST (FACILITY_ID)
);

INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (1, 'FACULTY', 'Fakultät für Architektur und Raumplanung', '401.000', 'Fakultät für Architektur und Raumplanung',
        null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (2, 'FACULTY', 'Fakultät für Bauingenierwesen', '402.000', 'Fakultät für Bauingenierwesen', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (3, 'FACULTY', 'Fakultät für Elektronik und Informationstechnik', '403.000',
        'Fakultät für Elektronik und Informationstechnik', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (4, 'FACULTY', 'Fakultät für Informatik', '404.000', 'Fakultät für Informatik', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (5, 'FACULTY', 'Fakultät für Maschinenwesen und Betriebswissenschaften', '405.000',
        'Fakultät für Maschinenwesen und Betriebswissenschaften', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (6, 'FACULTY', 'Fakultät für Mathematik und Geoinformation', '406.000',
        'Fakultät für Mathematik und Geoinformation', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (7, 'FACULTY', 'Fakultät für Physik', '407.000', 'Fakultät für Physik', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (8, 'FACULTY', 'Fakultät für Technische Chemie', '408.000', 'Fakultät für Technische Chemie', null);

INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (9, 'FACULTY', 'Fakultät für Materialwissenschaften', '309.012', 'Fakultät für Materialwissenschaften', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (10, 'FACULTY', 'Fakultät für angewandte Naturwissenschaften', '310.013',
        'Fakultät für angewandte Naturwissenschaften', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (11, 'FACULTY', 'Fakultät für Festkörper', '408.111', 'Fakultät für Festkörper', null);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (12, 'FACULTY', 'Fakultät für Automationstechnologie', '408.562', 'Fakultät für Automatinstechnologie', null);

INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (13, 'INSTITUTE', 'Institut für Bauforschung', '401.251', 'Institut für Bauforschung', 5);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (14, 'INSTITUTE', 'Institut für Architektur', '401.253', 'Institut für Architektur', 5);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (15, 'INSTITUTE', 'Institut für Architekurwissenschaften', '401.259', 'Institut für Architekurwissenschaften',
        5);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (16, 'INSTITUTE', 'Institut für Städtebau', '401.260', 'Institut für Städtebau', 5);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (17, 'INSTITUTE', 'Institut für Kunst und Gestaltung', '401.264', 'Institut für Kunst und Gestaltung', 5);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (18, 'INSTITUTE', 'Institut für Raumplanung', '401.265', 'Institut für Raumplanung', 5);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (19, 'INSTITUTE', 'Institut für Mechanik', '402.202', 'Institut für Mechanik', 6);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (20, 'INSTITUTE', 'Institut für Hochbau und Technologie', '402.206', 'Institut für Hochbau und Technologie', 6);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (21, 'INSTITUTE', 'Institut für Tragkonstruktion', '402.212', 'Institut für Tragkonstruktion', 6);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (22, 'INSTITUTE', 'Institut für Geotechnik', '402.220', 'Institut für Geotechnik', 6);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (23, 'INSTITUTE', 'Institut für Wasserbau', '402.221', 'Institut für Wasserbau', 6);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (24, 'INSTITUTE', 'Institut für Verkehrswissenschaften', '402.254', 'Institut für Verkehrswissenschaften', 6);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (25, 'INSTITUTE', 'Institut für Elektrodynamik', '403.354', 'Institut für Elektrodynamik', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (26, 'INSTITUTE', 'Institut für Mikroelektronik', '403.360', 'Institut für Mikroelektronik', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (27, 'INSTITUTE', 'Institut für Festkörperelektronik', '403.362', 'Institut für Festkörperelektronik', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (28, 'INSTITUTE', 'Institut für Sensorsysteme', '403.366', 'Institut für Sensorsysteme', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (29, 'INSTITUTE', 'Institut für Energiesysteme', '403.370', 'Institut für Energiesysteme', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (30, 'INSTITUTE', 'Institut für Computertechnik', '403.376', 'Institut für Computertechnik', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (31, 'INSTITUTE', 'Institut für Photonik', '403.387', 'Institut für Photonik', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (32, 'INSTITUTE', 'Institut für Telekommunikation', '403.389', 'Institut für Telekommunikation', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (33, 'INSTITUTE', 'Institut für Nanostrukturen', '403.350', 'Institut für Nanostrukturen', 7);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (34, 'INSTITUTE', 'Institut für Technische Informatik', '404.182', 'Institut für Technische Informatik', 8);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (35, 'INSTITUTE', 'Institut für Rechnergestütze Automation', '404.183',
        'Institut für Rechnergestütze Automation', 8);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (36, 'INSTITUTE', 'Institut für Informationssysteme', '404.184', 'Institut für Informationssysteme', 8);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (37, 'INSTITUTE', 'Institut für Computersprachen', '404.185', 'Institut für Computersprachen', 8);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (38, 'INSTITUTE', 'Institut für Computergraphik', '404.186', 'Institut für Computergraphik', 8);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (39, 'INSTITUTE', 'Institut für Algorithmen', '404.187', 'Institut für Algorithmen', 8);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (40, 'INSTITUTE', 'Institut für Softwaretechnik', '404.188', 'Institut für Softwaretechnik', 8);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (41, 'INSTITUTE', 'Institut für Thermodynamik', '405.302', 'Institut für Thermodynamik', 9);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (42, 'INSTITUTE', 'Institut für Technische Logistik', '405.307', 'Institut für Technische Logistik', 9);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (43, 'INSTITUTE', 'Institut für Werkstoffe', '405.308', 'Institut für Werkstoffe', 9);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (44, 'INSTITUTE', 'Institut für Lasertechnik', '405.311', 'Institut für Lasertechnik', 9);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (45, 'INSTITUTE', 'Institut für Automobiltechnik', '405.317', 'Institut für Automobiltechnik', 9);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (46, 'INSTITUTE', 'Institut für Biomechanik', '405.322', 'Institut für Biomechanik', 9);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (47, 'INSTITUTE', 'Institut für Strömungsmechanik', '405.325', 'Institut für Strömungsmechanik', 9);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (48, 'INSTITUTE', 'Institut für Mechatronik', '405.340', 'Institut für Mechatronik', 9);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (49, 'INSTITUTE', 'Institut für Analysis', '406.101', 'Institut für Analysis', 10);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (50, 'INSTITUTE', 'Institut für numerische Mathematik', '406.104', 'Institut für numerische Mathematik', 10);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (51, 'INSTITUTE', 'Institut für Diskrete Mathematik', '406.105', 'Institut für Diskrete Mathematik', 10);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (52, 'INSTITUTE', 'Institut für Geometrie', '406.120', 'Institut für Geometrie', 10);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (53, 'INSTITUTE', 'Institut für Stochastik', '406.131', 'Institut für Stochastik', 10);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (54, 'INSTITUTE', 'Institut für Wirtschaftsmathematik', '406.140', 'Institut für Wirtschaftsmathematik', 10);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (55, 'INSTITUTE', 'Institut für Geoinformation', '406.189', 'Institut für Geoinformation', 10);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (56, 'INSTITUTE', 'Institut für Angewandte Physik', '407.134', 'Institut für Angewandte Physik', 11);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (57, 'INSTITUTE', 'Institut für Theoretische Physik', '407.136', 'Institut für Theoretische Physik', 11);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (58, 'INSTITUTE', 'Institut für Festkörperphysik', '407.138', 'Institut für Festkörperphysik', 11);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (59, 'INSTITUTE', 'Atominstitut', '407.141', 'Atominstitut', 11);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (60, 'INSTITUTE', 'Institut für Angewandte Synthesechemie', '408.163', 'Institut für Angewandte Synthesechemie',
        12);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (61, 'INSTITUTE', 'Institut für Chemische Analytik', '408.164', 'Institut für Chemische Analytik', 12);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (62, 'INSTITUTE', 'Institut für Materialchemie', '408.165', 'Institut für Materialchemie', 12);
INSERT INTO FACILITIES_ST (FACILITY_ID, FACILITY_TYPE, DESCRIPTION, FACILITY_CODE, FACILITY_TITLE, FACULTY_ID)
VALUES (63, 'INSTITUTE', 'Institut für Verfahrenstechnik', '408.174', 'Institut für Verfahrenstechnik', 12);

-- ------------------------------------------------------------------------------------------------------------------ --
-- TABLE: SUBPROJECTS                                                                                                 --
-- ------------------------------------------------------------------------------------------------------------------ --

CREATE TABLE E_SUBPROJECT_STATES
(
    LABEL VARCHAR(12) NOT NULL,
    PRIMARY KEY (LABEL)
);

INSERT INTO E_SUBPROJECT_STATES
VALUES ('CANCLED');
INSERT INTO E_SUBPROJECT_STATES
VALUES ('RUNNING');
INSERT INTO E_SUBPROJECT_STATES
VALUES ('FINISHED');

CREATE TABLE SUBPROJECTS
(
    SUBPROJECT_ID        INT NOT NULL AUTO_INCREMENT,
    DESCRIPTION          TEXT,
    APPLIED_RESEARCH     INTEGER       NOT NULL,
    FOCUS_RESEARCH       INTEGER       NOT NULL,
    THEORETICAL_RESEARCH INTEGER       NOT NULL,
    RESEARCH_FOCUS       INTEGER       GENERATED ALWAYS AS (APPLIED_RESEARCH + FOCUS_RESEARCH + THEORETICAL_RESEARCH) VIRTUAL,
    INSTITUTE_ID         INT NOT NULL,
    PROJECT_ID           INT NOT NULL,
    PRIMARY KEY (SUBPROJECT_ID),
    CONSTRAINT FK_SUBPROJECTS_INSTITUTE_ID FOREIGN KEY (INSTITUTE_ID)
        REFERENCES FACILITIES_ST (FACILITY_ID),
    CONSTRAINT FK_SUBPROJECTS_PROJECT_ID FOREIGN KEY (PROJECT_ID)
        REFERENCES PROJECTS_BT (PROJECT_ID),
    CONSTRAINT CK_SUBPROJECTS_RESEARCH CHECK ( APPLIED_RESEARCH + THEORETICAL_RESEARCH + FOCUS_RESEARCH = 100 ),
    CONSTRAINT CK_SUB_APPLIED_RESEARCH CHECK ( APPLIED_RESEARCH >= 0 AND APPLIED_RESEARCH <= 100 ),
    CONSTRAINT CK_SUB_FOCUS_RESEARCH CHECK ( FOCUS_RESEARCH >= 0 AND APPLIED_RESEARCH <= 100 ),
    CONSTRAINT CK_SUB_THEORETICAL_RES CHECK (THEORETICAL_RESEARCH >= 0 AND THEORETICAL_RESEARCH <= 100)
);


INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (1, 'Mathematische Grundlagen der Finiten Elemente', 0, 70, 30, 50, 1);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (2, 'Softwaretechnische Algorithmen für Finite Elemente', 60, 20, 20, 39, 1);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (4, 'Algorithmen in der AI', 70, 30, 0, 39, 3);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (5, 'Logische Grundlagen der AI', 10, 40, 50, 37, 3);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (6, 'Theoretische Grundlagen des komplexen Brückenbaus', 0, 0, 100, 21, 4);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (7, 'Theoretische Grundlagen des komplexen Brückenbaus im Wasserbeet', 0, 0, 100, 23, 4);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (11, 'Numerische Methoden ', 0, 40, 60, 50, 7);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (12, 'Numerische Methoden in der Differentiellen Analysis', 0, 60, 40, 49, 7);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (13, 'Numerische Methoden', 0, 40, 60, 50, 8);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (14, 'Numerische Methoden in der Geometrischen Algebra', 0, 60, 40, 49, 8);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (15, 'Computeralgorithmen auf Graphen', 100, 0, 0, 39, 9);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (16, 'Graphentheorie', 10, 50, 40, 51, 9);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (19, 'Algorithmen', 30, 30, 40, 39, 12);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (20, 'Algorithmen in der Mustererkennung', 100, 0, 0, 38, 12);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (21, 'Gesichtsmerkmale in der Mustererkennung', 100, 0, 0, 38, 13);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (23, 'Gleichverteilte Zufallszahlen', 10, 30, 60, 53, 15);
INSERT INTO SUBPROJECTS (SUBPROJECT_ID, DESCRIPTION, APPLIED_RESEARCH, FOCUS_RESEARCH, THEORETICAL_RESEARCH,
                         INSTITUTE_ID, PROJECT_ID)
VALUES (30, 'Design Pattern in der OOP21', 100, 0, 0, 13, 24);


CREATE TABLE SUBPROJECT_HAS_STATES_JT
(
    SUBPROJECT_ID    INT NOT NULL,
    SUBPROJECT_STATE VARCHAR(12)   NOT NULL,
    STATE_CHANGED_AT DATE          NOT NULL,
    PRIMARY KEY (SUBPROJECT_ID, SUBPROJECT_STATE, STATE_CHANGED_AT),
    CONSTRAINT FK_SHS_SUBPROJECT_ID FOREIGN KEY (SUBPROJECT_ID)
        REFERENCES SUBPROJECTS (SUBPROJECT_ID),
    CONSTRAINT FK_SHS_SUBPROJECT_STATE FOREIGN KEY (SUBPROJECT_STATE)
        REFERENCES E_SUBPROJECT_STATES (LABEL)
);

INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (1, 'RUNNING', STR_TO_DATE('02.03.1998','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (1, 'FINISHED', STR_TO_DATE('20.12.1998','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (2, 'RUNNING', STR_TO_DATE('05.03.1998','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (2, 'FINISHED', STR_TO_DATE('11.11.1998','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (4, 'RUNNING', STR_TO_DATE('25.02.2010','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (4, 'FINISHED', STR_TO_DATE('20.12.2011','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (5, 'RUNNING', STR_TO_DATE('25.02.2010','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (5, 'RUNNING', STR_TO_DATE('01.12.2011','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (6, 'RUNNING', STR_TO_DATE('04.04.2010','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (6, 'FINISHED', STR_TO_DATE('20.12.2010','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (7, 'RUNNING', STR_TO_DATE('10.04.2010','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (7, 'FINISHED', STR_TO_DATE('01.12.2010','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (11, 'RUNNING', STR_TO_DATE('21.07.2015','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (11, 'FINISHED', STR_TO_DATE('01.12.2015','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (12, 'RUNNING', STR_TO_DATE('25.07.2015','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (12, 'FINISHED', STR_TO_DATE('07.12.2015','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (13, 'RUNNING', STR_TO_DATE('12.08.2015','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (13, 'FINISHED', STR_TO_DATE('01.01.2017','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (14, 'RUNNING', STR_TO_DATE('21.08.2015','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (14, 'FINISHED', STR_TO_DATE('20.12.2016','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (15, 'RUNNING', STR_TO_DATE('10.09.2013','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (15, 'FINISHED', STR_TO_DATE('01.09.2014','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (16, 'RUNNING', STR_TO_DATE('12.09.2013','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (16, 'FINISHED', STR_TO_DATE('03.09.2014','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (19, 'RUNNING', STR_TO_DATE('21.01.2019','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (19, 'FINISHED', STR_TO_DATE('12.12.2019','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (20, 'RUNNING', STR_TO_DATE('02.02.2019','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (20, 'FINISHED', STR_TO_DATE('10.12.2019','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (21, 'RUNNING', STR_TO_DATE('10.09.2019','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (23, 'RUNNING', STR_TO_DATE('10.05.2016','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (23, 'FINISHED', STR_TO_DATE('10.10.2018','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (30, 'RUNNING', STR_TO_DATE('14.02.2016','%d.%m.%Y'));
INSERT INTO SUBPROJECT_HAS_STATES_JT (SUBPROJECT_ID,
                                      SUBPROJECT_STATE, STATE_CHANGED_AT)
VALUES (30, 'FINISHED', STR_TO_DATE('20.12.2017','%d.%m.%Y'));

-- ------------------------------------------------------------------------------------------------------------------ --
-- TABLE: DEBITORS                                                                                                    --
-- ------------------------------------------------------------------------------------------------------------------ --

CREATE TABLE DEBITORS
(
    DEBITOR_ID  INT NOT NULL AUTO_INCREMENT,
    DESCRIPTION VARCHAR(100) NOT NULL,
    NAME        VARCHAR(20)   NOT NULL UNIQUE,
    PRIMARY KEY (DEBITOR_ID)
);

INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (1, 'Forschungsförderungsgesellschaft', 'FFG');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (2, 'Fonds für Wissenschaftliche Förderung', 'FWF');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (3, 'EU Forschungsfond', 'EUFA');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (4, 'EU Wissenschaftsfond', 'EUWF');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (5, 'Siemens Forschungsabteilung', 'Siemens FA');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (6, 'ÖBB Forschungsabteilung', 'ÖBB FA');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (7, 'VÖST Forschungsabteilung', 'Vöst FA');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (8, 'Strabag', 'Strabag FA');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (9, 'Deutsche Bahn Forschungsabteilung', 'DB FA');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (10, 'SAP', 'SAP FA');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (11, 'Uni Regensburg', 'Uni Regensburg');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (12, 'Uni Berlin', 'Uni Berlin');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (13, 'Uni Linz', 'Uni Linz');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (14, 'Uni Wien', 'Uni Wien');
INSERT INTO DEBITORS (debitor_id, description, name)
VALUES (15, 'Uni Hamburg', 'Uni Hamburg');


CREATE TABLE PROJECT_DEBITORS_JT
(
    DEBITOR_ID INT NOT NULL,
    PROJECT_ID INT NOT NULL,
    AMOUNT     DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (DEBITOR_ID, PROJECT_ID),
    CONSTRAINT FK_PD_DEBITOR_ID FOREIGN KEY (DEBITOR_ID)
        REFERENCES DEBITORS (DEBITOR_ID),
    CONSTRAINT FK_PD_PROJECT_ID FOREIGN KEY (PROJECT_ID)
        REFERENCES PROJECTS_BT (PROJECT_ID),
    CONSTRAINT CK_PD_AMOUNT CHECK ( AMOUNT >= 0 )
);

INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (10000.00, 12, 1);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (30000.00, 9, 1);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (15000.00, 13, 2);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (30000.00, 1, 3);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (35000.00, 13, 4);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (100000.00, 7, 4);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (150000.00, 8, 5);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (10000.00, 13, 6);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (5000.00, 5, 6);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (5000.00, 1, 7);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (10000.00, 3, 7);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (50000.00, 1, 8);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (15000.00, 2, 9);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (14000.00, 2, 10);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (12000.00, 14, 11);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (25000.00, 9, 12);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (10000.00, 9, 13);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (12000.00, 2, 14);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (50000.00, 12, 15);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (10000.00, 11, 16);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (5000.00, 3, 17);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (12000.00, 1, 18);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (23000.00, 5, 19);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (30000.00, 13, 20);
INSERT INTO PROJECT_DEBITORS_JT (amount, debitor_id, project_id)
VALUES (25000.00, 13, 21);

commit;

-- DROP TABLE MANAGEMENT_PROJECTS;
-- DROP TABLE RESEARCH_FUNDING_PROJECTS;
-- DROP TABLE REQUEST_FUNDING_PROJECTS;
-- DROP TABLE PROJECT_FORERUNNERS;
-- DROP TABLE PROJECT_DEBITORS;
-- DROP TABLE DEBITORS;
-- DROP TABLE SUBPROJECT_HAS_STATES_JT;
-- DROP TABLE SUBPROJECTS;
-- DROP TABLE FACILITIES;
-- DROP TABLE E_FACILITY_TYPE;
-- DROP TABLE PROJECT_HAS_STATES_JT;
-- DROP TABLE PROJECTS;
-- DROP TABLE E_LEGAL_FOUNDATIONS;
-- DROP TABLE E_PROJECT_STATES;
-- DROP TABLE E_MANAGEMENT_DUTY;
-- DROP TABLE E_SUBPROJECT_STATES;
--
-- DROP SEQUENCE SEQ_PROJECTS_PROJECT_ID;
-- DROP SEQUENCE SEQ_FACILITIES_FACILITY_ID;
-- DROP SEQUENCE SEQ_SUBPROJECTS_SUBPROJECT_ID;
-- DROP SEQUENCE SEQ_DEBITORS_DEBITOR_ID;