drop table vente;
drop table temps;
drop table produit;
drop table etat;

create table etat (
  eid varchar2(2), 
  nom varchar2(50),
  constraint pk_etat primary key(eid));

insert into etat values ('E1', 'Californie');
insert into etat values ('E2', 'Floride');
 
create table produit (
  pid varchar2(2), 
  nom varchar2(50),
  constraint pk_produit primary key(pid));

insert into produit values ('P1', 'Voiture');
insert into produit values ('P2', 'Moto');
  
create table temps (
  tid varchar2(2), 
  semestre varchar2(2) check(semestre in ('S1','S2')),
  annee number(4),
  constraint pk_temps primary key(tid));
  
insert into temps values ('T1', 'S1', 2018);
insert into temps values ('T2', 'S2', 2018);

 create table vente(
   eid varchar2(2), 
   pid varchar2(2),
   tid varchar2(2),
   montant integer,
   constraint pk_vente primary key(eid, pid, tid),
   constraint fk_vente_etat foreign key (eid) references etat(eid),
   constraint fk_vente_produit foreign key (pid) references produit(pid),
   constraint fk_vente_temps foreign key (tid) references temps(tid));

insert into vente values ('E1', 'P1', 'T1', 1);
insert into vente values ('E1', 'P1', 'T2', 1);  
insert into vente values ('E1', 'P2', 'T1', 1); 
insert into vente values ('E1', 'P2', 'T2', 1);
insert into vente values ('E2', 'P1', 'T1', 1);
insert into vente values ('E2', 'P1', 'T2', 1);  
insert into vente values ('E2', 'P2', 'T1', 1); 
insert into vente values ('E2', 'P2', 'T2', 1);  