alter table EMPREGADO_EMBARCACAO drop constraint SYS_C0022521;
alter table EMPREGADO_EMBARCACAO modify(pro_cpf varchar2(14));

alter table EMPREGADO_PORTO drop constraint SYS_C0022523;
alter table EMPREGADO_PORTO modify(pro_cpf varchar2(14));


DROP TABLE CONTRATO CASCADE CONSTRAINTS;

DROP TABLE VINCULO CASCADE CONSTRAINTS;

DROP TABLE INSCRICAO CASCADE CONSTRAINTS;

DROP TABLE AGENDA CASCADE CONSTRAINTS;

DROP TABLE TREINAMENTO CASCADE CONSTRAINTS;



DROP TABLE ITEM_PAGAMENTO CASCADE CONSTRAINTS;

DROP TABLE PAGAMENTO CASCADE CONSTRAINTS;

DROP TABLE PROFISSIONAL_DEPENDENTE CASCADE CONSTRAINTS;



DROP TABLE PROFISSIONAL CASCADE CONSTRAINTS;

DROP TABLE SETOR_RH CASCADE CONSTRAINTS;

DROP TABLE SALARIO_BASE CASCADE CONSTRAINTS;


DROP TABLE PONTO CASCADE CONSTRAINTS;


DROP TABLE EVENTO CASCADE CONSTRAINTS;

DROP TABLE DEPENDENTE CASCADE CONSTRAINTS;


CREATE TABLE SALARIO_BASE

(

     sab_data  DATE  NOT NULL ,

     sab_valor  NUMERIC  NOT NULL ,

CONSTRAINT  XPKSALARIO_BASE PRIMARY KEY (sab_data)

);





CREATE TABLE PONTO

(

     pon_id  INTEGER  NOT NULL ,

     pon_geo  MDSYS.SDO_GEOMETRY  NULL ,

     pon_altitude  NUMERIC  NULL ,

     pon_endereco  VARCHAR2(250)  NULL ,

CONSTRAINT  XPKPONTO PRIMARY KEY (pon_id)

);





CREATE TABLE SETOR_RH

(

     set_codigo  NUMERIC  NOT NULL ,

     set_sigla  VARCHAR2(10)  NOT NULL ,

     set_nome  VARCHAR2(255)  NOT NULL ,

     pon_id  INTEGER  NULL ,

CONSTRAINT  XPKSETOR_RH PRIMARY KEY (set_codigo),

CONSTRAINT  R_8 FOREIGN KEY (pon_id) REFERENCES PONTO(pon_id) ON DELETE SET NULL

);





CREATE TABLE TREINAMENTO

(

     tre_codigo  INTEGER  NOT NULL ,

     tre_nome  VARCHAR2(255)  NOT NULL ,

     tre_custo_aluno  DECIMAL   DEFAULT  0 NOT NULL ,

     tre_carga_horaria  FLOAT  NOT NULL ,

     set_codigo  NUMERIC  NULL ,

CONSTRAINT  XPKTREINAMENTO PRIMARY KEY (tre_codigo),

CONSTRAINT  R_17 FOREIGN KEY (set_codigo) REFERENCES SETOR_RH(set_codigo) ON DELETE SET NULL

);





CREATE TABLE AGENDA

(

     age_codigo  INTEGER  NOT NULL ,

     age_fim  DATE  NOT NULL ,

     age_inicio  DATE  NOT NULL ,

     age_qtde_vagas  INTEGER  NOT NULL ,

     tre_codigo  INTEGER  NULL ,

     pon_id  INTEGER  NULL ,

CONSTRAINT  XPKAGENDA PRIMARY KEY (age_codigo),

CONSTRAINT  R_4 FOREIGN KEY (tre_codigo) REFERENCES TREINAMENTO(tre_codigo) ON DELETE SET NULL,

CONSTRAINT  R_20 FOREIGN KEY (pon_id) REFERENCES PONTO(pon_id) ON DELETE SET NULL

);





CREATE TABLE PROFISSIONAL

(

     pro_cpf  VARCHAR2(14)  NOT NULL ,

     pro_nome  VARCHAR2(255)  NOT NULL ,

     pro_data_nasc  DATE  NOT NULL ,

     pro_cargo  VARCHAR2(255)  NOT NULL ,

     set_codigo  NUMERIC  NOT NULL ,

     pro_salario  NUMERIC  NULL ,

     pon_id  INTEGER  NULL ,

CONSTRAINT  XPKPROFISSIONAL PRIMARY KEY (pro_cpf),

CONSTRAINT  R_5 FOREIGN KEY (set_codigo) REFERENCES SETOR_RH(set_codigo),

CONSTRAINT  R_9 FOREIGN KEY (pon_id) REFERENCES PONTO(pon_id) ON DELETE SET NULL

);





CREATE TABLE INSCRICAO

(

     ins_qualificado  SMALLINT  NULL ,

     ins_realizado  SMALLINT  NULL ,

     age_codigo  INTEGER  NOT NULL ,

     pro_cpf  VARCHAR2(14)  NOT NULL ,

CONSTRAINT  XPKINSCRICAO PRIMARY KEY (age_codigo,pro_cpf),

CONSTRAINT  R_6 FOREIGN KEY (age_codigo) REFERENCES AGENDA(age_codigo),

CONSTRAINT  R_19 FOREIGN KEY (pro_cpf) REFERENCES PROFISSIONAL(pro_cpf)

);





CREATE TABLE PAGAMENTO

(

     pag_id  VARCHAR2(10)  NOT NULL ,

     pag_dt  DATE  NOT NULL ,

     pag_vl_liquido  NUMERIC  NULL ,

     pro_cpf  VARCHAR2(14)  NOT NULL ,

CONSTRAINT  XPKPAGAMENTO PRIMARY KEY (pag_id),

CONSTRAINT  R_10 FOREIGN KEY (pro_cpf) REFERENCES PROFISSIONAL(pro_cpf)

);





CREATE TABLE EVENTO

(

     eve_id  VARCHAR2(10)  NOT NULL ,

     eve_nome  VARCHAR2(255)  NOT NULL ,

     eve_il_adic  INTEGER  NULL ,

     eve_il_vl  INTEGER  NULL ,

     eve_il_hora  INTEGER  NULL ,

     eve_il_percentual  INTEGER  NULL ,

CONSTRAINT  XPKEVENTO PRIMARY KEY (eve_id)

);





CREATE TABLE ITEM_PAGAMENTO

(

     ite_tipo  INTEGER  NOT NULL ,

     ite_vl_adic  NUMERIC  NULL ,

     ite_vl_desc  NUMERIC  NULL ,

     pag_id  VARCHAR2(10) NULL ,

     eve_id  VARCHAR2(10) NULL ,

CONSTRAINT  XPKITEM_PAGAMENTO PRIMARY KEY (ite_tipo),

CONSTRAINT  R_11 FOREIGN KEY (pag_id) REFERENCES PAGAMENTO(pag_id) ON DELETE SET NULL,

CONSTRAINT  R_12 FOREIGN KEY (eve_id) REFERENCES EVENTO(eve_id) ON DELETE SET NULL

);





CREATE TABLE DEPENDENTE

(

     dep_codigo  NUMERIC  NOT NULL ,

     dep_nome  VARCHAR2(255)  NOT NULL ,

     dep_data_nasc  DATE  NOT NULL ,

CONSTRAINT  XPKDEPENDENTE PRIMARY KEY (dep_codigo)

);





CREATE TABLE PROFISSIONAL_DEPENDENTE

(

     p_d_parentesco  VARCHAR2(255)  NOT NULL ,

     pro_cpf  VARCHAR2(14)  NOT NULL ,

     dep_codigo  NUMERIC  NOT NULL ,

CONSTRAINT  XPKPROFISSIONAL_DEPENDENTE PRIMARY KEY (dep_codigo,pro_cpf),

CONSTRAINT  R_6B FOREIGN KEY (pro_cpf) REFERENCES PROFISSIONAL(pro_cpf),

CONSTRAINT  R_7 FOREIGN KEY (dep_codigo) REFERENCES DEPENDENTE(dep_codigo)

);





CREATE TABLE VINCULO

(

     vin_codigo  NUMERIC  NOT NULL ,

     vin_nome  VARCHAR2(255)  NOT NULL ,

     vin_descricao  VARCHAR2(255)  NULL ,

CONSTRAINT  XPKVINCULO PRIMARY KEY (vin_codigo)

);





CREATE TABLE CONTRATO

(

     con_numero  VARCHAR2(10)  NOT NULL ,

     con_inicio  DATE  NOT NULL ,

     con_termino  DATE  NULL ,

     pro_cpf  VARCHAR2(14)  NOT NULL ,

     vin_codigo  NUMERIC  NOT NULL ,

CONSTRAINT  XPKCONTRATO PRIMARY KEY (con_numero),

CONSTRAINT  R_3B FOREIGN KEY (pro_cpf) REFERENCES PROFISSIONAL(pro_cpf),

CONSTRAINT  R_4B FOREIGN KEY (vin_codigo) REFERENCES VINCULO(vin_codigo)

);



ALTER TABLE EMPREGADO_EMBARCACAO
      ADD  ( FOREIGN KEY (pro_cpf)
                            REFERENCES PROFISSIONAL ) ;

ALTER TABLE EMPREGADO_PORTO
      ADD  ( FOREIGN KEY (pro_cpf)
                            REFERENCES PROFISSIONAL ) ;
