Create table passageiro (cod number(10),nome varchar2(90));
Create table assento (cod number(10),tipo char);
Create table bilhete (cod_passageiro number(10),cod_assento number(10),numero_voo number(10),valor_bilhete number(12,2));
Create table voo (numero number(10),aviao number(10),data date,valor_padrao number(12,2));
Create table aviao (cod_aviao number(10),qtd_voos number(5));

alter table passageiro add constraint pk_passageiro primary key (cod);
alter table assento add constraint pk_assento primary key (cod);
alter table bilhete add constraint pk_bilhete primary key (cod_passageiro,cod_assento,numero_voo);
alter table aviao add constraint pk_aviao primary key (cod_aviao);
alter table voo add constraint pk_voo primary key (numero);
alter table assento add constraint ch_assento check (tipo in (‘A’,’B‘));


alter table bilhete add constraint fk_bilhete1 foreign key (cod_passageiro) references passageiro (cod);
alter table bilhete add constraint fk_bilhete2 foreign key (cod_assento) references assento (cod);
alter table bilhete add constraint fk_bilhete3 foreign key (numero_voo) references voo (numero);
alter table voo add constraint fk_voo foreign key (aviao) references aviao (cod_aviao);



create sequence seq_pk_cod start with 1;

create or replace trigger INSERT_COD
  before insert on passageiro  
  for each row
begin
  begin
select seq_pk_cod.nextval into :new.cod from dual;
end insert_cod;
end INSERT_COD;

create or replace trigger ATUALIZA_VOO
  after INSERT OR DELETE on voo  
  for each row
declare
  begin
     if inserting then
      update aviao set qtd_voos=qtd_voos + 1 where AVIAO.COD_AVIAO= :NEW.AVIAO;
     ELSE
      update aviao set qtd_voos=qtd_voos - 1 where AVIAO.COD_AVIAO= :OLD.AVIAO;     
     END IF;
end ATUALIZA_VOO;


create or replace function calcula_val(tipo_p in char, data_p in date) return number is
  Result number(12,2);
begin
  select sum(valor_bilhete) into result from assento a,bilhete b, voo v where
  v.data=data_p and a.tipo=tipo_p and a.cod=b.cod_assento and b.numero_voo=v.numero;
  if result < 200 then
     return(-1);
  else
     return(Result);
  end if;
end calcula_val;

create or replace procedure ATUALIZA_B is
begin
  FOR P IN (SELECT NUMERO,AVIAO,DATA,VALOR_PADRAO FROM VOO) LOOP
  IF TO_CHAR(P.DATA,'D') IN (1,7) AND TO_CHAR(P.DATA,'HHMM') >= '1800' AND
  TO_CHAR(P.DATA,'HHMM') <= '2359' THEN
     UPDATE BILHETE SET BILHETE.VLOR_BILHETE=P.VALOR_PADRAO*0.5 WHERE BILHETE.NUMERO_VOO=P.NUMERO;
  END IF;
  END LOOP;
  COMMIT;
end ATUALIZA_B;