CREATE TABLE area (
    id        INTEGER generated always as identity (start with 1 increment by 1),
    nombre    VARCHAR2(70),
    ranking   INTEGER,
    id_jefe   INTEGER NOT NULL
);

ALTER TABLE area ADD CONSTRAINT area_pk PRIMARY KEY ( id );



CREATE TABLE asignacion (
    id               INTEGER generated always as identity (start with 1 increment by 1),
    id_profesional   INTEGER NOT NULL,
    id_area          INTEGER NOT NULL
);

ALTER TABLE asignacion ADD CONSTRAINT asignacion_pk PRIMARY KEY ( id );

CREATE TABLE contestacion (
    id             INTEGER generated always as identity (start with 1 increment by 1),
    id_pais        INTEGER NOT NULL,
    id_respuesta   INTEGER NOT NULL
);

ALTER TABLE contestacion ADD CONSTRAINT contestacion_pk PRIMARY KEY ( id );

CREATE TABLE encuesta (
    id       INTEGER generated always as identity (start with 1 increment by 1),
    nombre   VARCHAR2(70) NOT NULL
);

ALTER TABLE encuesta ADD CONSTRAINT encuesta_pk PRIMARY KEY ( id );

CREATE TABLE frontera (
    id         INTEGER generated always as identity (start with 1 increment by 1),
    id_pais    INTEGER NOT NULL,
    id_pais2   INTEGER NOT NULL
);

ALTER TABLE frontera ADD CONSTRAINT frontera_pk PRIMARY KEY ( id );

CREATE TABLE invension (
    id            INTEGER generated always as identity (start with 1 increment by 1),
    id_invento    INTEGER NOT NULL,
    id_inventor   INTEGER NOT NULL
);

ALTER TABLE invension ADD CONSTRAINT invension_pk PRIMARY KEY ( id );

CREATE TABLE invento (
    id               INTEGER generated always as identity (start with 1 increment by 1),
    nombre           VARCHAR2(70) NOT NULL,
    ano              INTEGER NOT NULL,
    id_pais          INTEGER NOT NULL,
    id_profesional   INTEGER NOT NULL
);

ALTER TABLE invento ADD CONSTRAINT invento_pk PRIMARY KEY ( id );

CREATE TABLE inventor (
    id        INTEGER generated always as identity (start with 1 increment by 1),
    nombre    VARCHAR2(70),
    id_pais   INTEGER NOT NULL
);

ALTER TABLE inventor ADD CONSTRAINT inventor_pk PRIMARY KEY ( id );

CREATE TABLE paiss (
    id          INTEGER generated always as identity (start with 1 increment by 1),
    nombre      VARCHAR2(70) NOT NULL,
    area        INTEGER NOT NULL,
    poblacion   INTEGER NOT NULL,
    capital     VARCHAR2(70),
    id_region   INTEGER NOT NULL
);

ALTER TABLE paiss ADD CONSTRAINT pais_pk PRIMARY KEY ( id );

CREATE TABLE pregunta (
    id            INTEGER generated always as identity (start with 1 increment by 1),
    pregunta      VARCHAR2(70) NOT NULL,
    id_encuesta   INTEGER NOT NULL
);

ALTER TABLE pregunta ADD CONSTRAINT pregunta_pk PRIMARY KEY ( id );

CREATE TABLE profesional (
    id         INTEGER generated always as identity (start with 1 increment by 1),
    nombre     VARCHAR2(70) NOT NULL,
    fecha      DATE,
    salario    INTEGER,
    comision   INTEGER
);

ALTER TABLE profesional ADD CONSTRAINT profesional_pk PRIMARY KEY ( id );

CREATE TABLE regionn (
    id         INTEGER generated always as identity (start with 1 increment by 1),
    nombre     VARCHAR2(70) NOT NULL,
    id_padre   INTEGER
);

ALTER TABLE regionn ADD CONSTRAINT region_pk PRIMARY KEY ( id );

CREATE TABLE respuesta (
    id            INTEGER generated always as identity (start with 1 increment by 1),
    respuesta     VARCHAR2(20) NOT NULL,
    letra         VARCHAR2(2) NOT NULL,
    correcta      VARCHAR2(5) NOT NULL,
    id_pregunta   INTEGER NOT NULL
);

ALTER TABLE respuesta ADD CONSTRAINT respuesta_pk PRIMARY KEY ( id );

ALTER TABLE area
    ADD CONSTRAINT area_profesional_fk FOREIGN KEY ( id_jefe )
        REFERENCES profesional ( id );

ALTER TABLE asignacion
    ADD CONSTRAINT asignacion_area_fk FOREIGN KEY ( id_area )
        REFERENCES area ( id );

ALTER TABLE asignacion
    ADD CONSTRAINT asignacion_profesional_fk FOREIGN KEY ( id_profesional )
        REFERENCES profesional ( id );

ALTER TABLE contestacion
    ADD CONSTRAINT contestacion_pais_fk FOREIGN KEY ( id_pais )
        REFERENCES paiss ( id );

ALTER TABLE contestacion
    ADD CONSTRAINT contestacion_respuesta_fk FOREIGN KEY ( id_respuesta )
        REFERENCES respuesta ( id );

ALTER TABLE frontera
    ADD CONSTRAINT frontera_pais_fk FOREIGN KEY ( id_pais )
        REFERENCES paiss ( id );

ALTER TABLE frontera
    ADD CONSTRAINT frontera_pais_fkv2 FOREIGN KEY ( id_pais2 )
        REFERENCES paiss ( id );

ALTER TABLE invension
    ADD CONSTRAINT invension_invento_fk FOREIGN KEY ( id_invento )
        REFERENCES invento ( id );

ALTER TABLE invension
    ADD CONSTRAINT invension_inventor_fk FOREIGN KEY ( id_inventor )
        REFERENCES inventor ( id );

ALTER TABLE invento
    ADD CONSTRAINT invento_pais_fk FOREIGN KEY ( id_pais )
        REFERENCES paiss ( id );

ALTER TABLE invento
    ADD CONSTRAINT invento_profesional_fk FOREIGN KEY ( id_profesional )
        REFERENCES profesional ( id );

ALTER TABLE inventor
    ADD CONSTRAINT inventor_pais_fk FOREIGN KEY ( id_pais )
        REFERENCES paiss ( id );

ALTER TABLE paiss
    ADD CONSTRAINT pais_region_fk FOREIGN KEY ( id_region )
        REFERENCES regionn ( id );

ALTER TABLE pregunta
    ADD CONSTRAINT pregunta_encuesta_fk FOREIGN KEY ( id_encuesta )
        REFERENCES encuesta ( id );

ALTER TABLE regionn
    ADD CONSTRAINT region_region_fk FOREIGN KEY ( id_padre )
        REFERENCES regionn ( id );

ALTER TABLE respuesta
    ADD CONSTRAINT respuesta_pregunta_fk FOREIGN KEY ( id_pregunta )
        REFERENCES pregunta ( id );