CREATE TABLE REGION (
    id         INTEGER NOT NULL,
    nombre     VARCHAR2(30) NOT NULL,
    id_padre   INTEGER,
    CONSTRAINT region_pk PRIMARY KEY ( id ),
    CONSTRAINT fk_region FOREIGN KEY (id_padre) REFERENCES REGION (id)
);

CREATE TABLE PAIS (
    id          INTEGER NOT NULL,
    nombre      VARCHAR2(30) NOT NULL,
    area        VARCHAR2(30),
    poblacion   INTEGER NOT NULL,
    id_region   INTEGER NOT NULL,
    CONSTRAINT pais_pk PRIMARY KEY ( id ),
    CONSTRAINT fk_pais FOREIGN KEY (id_region) REFERENCES REGION (id)
);

CREATE TABLE FRONTERA (
    id_pais    INTEGER NOT NULL,
    id_pais2   INTEGER NOT NULL,
    CONSTRAINT frontera_pk PRIMARY KEY (id_pais,id_pais2),
    CONSTRAINT fk_frontera1 FOREIGN KEY (id_pais) REFERENCES PAIS (id),
    CONSTRAINT fk_frontera2 FOREIGN KEY (id_pais2) REFERENCES PAIS (id)
);

CREATE TABLE INVENTO (
    id        INTEGER NOT NULL,
    nombre    VARCHAR2(30) NOT NULL,
    id_pais   INTEGER NOT NULL,
    CONSTRAINT invento_pk PRIMARY KEY (id),
    CONSTRAINT fk_invento FOREIGN KEY (id_pais) REFERENCES PAIS (id)
);

CREATE TABLE INVENTOR (
    id             INTEGER NOT NULL,
    nombre         VARCHAR2(30) NOT NULL,
    salario        NUMBER(10, 2),
    comision       NUMBER(5, 2),
    fecha          DATE NOT NULL,
    nacionalidad   INTEGER NOT NULL,
    CONSTRAINT inventor_pk PRIMARY KEY (id),
    CONSTRAINT fk_inventor FOREIGN KEY (nacionalidad) REFERENCES PAIS (id)
);

CREATE TABLE INVENCION (
    id_invento    INTEGER NOT NULL,
    id_inventor   INTEGER NOT NULL,
    CONSTRAINT invencion_pk PRIMARY KEY (id_invento,id_inventor),
    CONSTRAINT fk_invencion1 FOREIGN KEY (id_invento) REFERENCES INVENTO (id),
    CONSTRAINT fk_invencion2 FOREIGN KEY (id_inventor) REFERENCES INVENTOR (id)
);

CREATE TABLE CPM (
    id        INTEGER NOT NULL,
    id_jefe   INTEGER NOT NULL,
    CONSTRAINT cpm_pk PRIMARY KEY (id),
    CONSTRAINT fk_cpm FOREIGN KEY (id_jefe) REFERENCES INVENTOR (id)
);

CREATE TABLE AREA_INVESTIGACION (
    id            INTEGER NOT NULL,
    nombre        VARCHAR2(30) NOT NULL,
    descripcion   CLOB NOT NULL,
    ranking       INTEGER,
    cpm           INTEGER NOT NULL,
    id_jefe       INTEGER NOT NULL,
    CONSTRAINT check_a CHECK(ranking BETWEEN 0 and 10),
    CONSTRAINT area_pk PRIMARY KEY (id),
    CONSTRAINT fk_area FOREIGN KEY (cpm) REFERENCES CPM (id),
    CONSTRAINT fk_area2 FOREIGN KEY (id_jefe) REFERENCES INVENTOR (id)
);

CREATE TABLE ASIGNACION (
    id_inventor   INTEGER NOT NULL,
    id_area       INTEGER NOT NULL,
    CONSTRAINT asignacion_pk PRIMARY KEY (id_area,id_inventor),
    CONSTRAINT fk_asignacion1 FOREIGN KEY (id_inventor) REFERENCES INVENTOR (id),
    CONSTRAINT fk_asignacion2 FOREIGN KEY (id_area) REFERENCES AREA_INVESTIGACION (id)
);

CREATE TABLE TEMA (
    id       INTEGER NOT NULL,
    nombre   VARCHAR2(30) NOT NULL,
    CONSTRAINT tema_pk PRIMARY KEY (id)
);

CREATE TABLE ENCUESTA (
    id        INTEGER NOT NULL,
    id_tema   INTEGER NOT NULL,
    fecha     DATE,
    CONSTRAINT encuesta_pk PRIMARY KEY (id),
    CONSTRAINT fk_encuesta FOREIGN KEY (id_tema) REFERENCES TEMA (id)
);

CREATE TABLE CONTESTACION (
    id_pais       INTEGER NOT NULL,
    id_encuesta   INTEGER NOT NULL,
    CONSTRAINT contestacion_pk PRIMARY KEY (id_pais,id_encuesta),
    CONSTRAINT fk_cntestacion1 FOREIGN KEY (id_pais) REFERENCES PAIS (id),
    CONSTRAINT fk_cntestacion2 FOREIGN KEY (id_encuesta) REFERENCES ENCUESTA (id)
);

CREATE TABLE PREGUNTA (
    id            INTEGER NOT NULL,
    pregunta      CLOB NOT NULL,
    id_encuesta   INTEGER NOT NULL,
    CONSTRAINT pregunta_pk PRIMARY KEY (id),
    CONSTRAINT fk_pregunta FOREIGN KEY (id_encuesta) REFERENCES ENCUESTA (id)
);

CREATE TABLE RESPUESTA (
    respuesta     CLOB NOT NULL,
    id_pregunta   INTEGER NOT NULL,
    id            INTEGER NOT NULL,
    CONSTRAINT respuesta_pk PRIMARY KEY (id),
    CONSTRAINT fk_respuesta FOREIGN KEY (id_pregunta) REFERENCES PREGUNTA (id)
);

CREATE TABLE RESPUESTA_SEL (
    id_contestacion   INTEGER NOT NULL,
    id_respuesta      INTEGER NOT NULL,
    CONSTRAINT respuestas_pk PRIMARY KEY (id_contestacion,id_respuesta),
    CONSTRAINT fk_respuestas3 FOREIGN KEY (id_respuesta) REFERENCES RESPUESTA (id)
);