create table lavoratore(
	codice numeric primary key,
	nome varchar(20) not null,
	cognome varchar(20) not null,
	inprogetto numeric not null,
	insindacato varchar(20)
);

create domain Tipo as varchar(2) check(value in ('S','D','C','ES','ED','B'));

create table sindacato(
	nome varchar(20) primary key,
	tipo Tipo,
	capo numeric not null
);

create table progetto(
	codice numeric primary key,
	usura int not null default 1,
	check(usura > 0)
);

alter table lavoratore
	add constraint tmp1 foreign key(inprogetto) references progetto on delete restrict deferrable;

alter table lavoratore
	add constraint tmp2 foreign key(insindacato) references sindacato on delete set null deferrable;

alter table sindacato
	add constraint tmp3 foreign key(capo) references lavoratore on delete cascade deferrable;


//-----------------------------------------------------------------------------------------------------------------------

begin;

set constraints tmp1 deferred;
set constraints tmp2 deferred;
set constraints tmp3 deferred;

insert into sindacato values('CISL','C',100);
insert into sindacato values('CGIL','S',130);
insert into sindacato values('UIL','D',180);
insert into sindacato values('COBAS','B',190);
insert into sindacato values('CFAS',null,120);

insert into progetto values(1000,1);
insert into progetto values(2000,2);
insert into progetto values(3000,2);
insert into progetto values(4000,4);
insert into progetto values(5000,5);
insert into progetto values(6000,3);
insert into progetto values(7000,2);

insert into lavoratore values(100,'Mario','Rossi',1000,'CISL');
insert into lavoratore values(110,'Paolo','Verdi',2000,'CISL');
insert into lavoratore values(120,'Giorgio','Gialli',2000,'CISL');
insert into lavoratore values(130,'Carlo','Neri',2000,'CGIL');
insert into lavoratore values(140,'Dario','Grigi',3000,'CGIL');
insert into lavoratore values(150,'Riccardo','Marroni',3000,'CGIL');
insert into lavoratore values(160,'Stefano','Violi',4000,'CGIL');
insert into lavoratore values(170,'Marco','Fabbri',4000,'UIL');
insert into lavoratore values(180,'Guido','Maestri',5000,'UIL');
insert into lavoratore values(190,'Alberto','Virgili',5000,'COBAS');
insert into lavoratore values(200,'Silvio','Salti',5000,'COBAS');

end;


//-----------------------------------------------------------------------------------------------------------------------


create view db(codice,nome,cognome,sindacato,tipo,capo,progetto,usura) as
select l.codice,l.nome,l.cognome,s.nome,tipo,capo,p.codice,usura
from lavoratore l full outer join sindacato s on insindacato = s.nome
			full outer join progetto p on l.inprogetto = p.codice


//-----------------------------------------------------------------------------------------------------------------------


delete from progetto p where codice not in (select p.codice
					    from progetto p,lavoratore
					    where p.codice = inprogetto);

delete from lavoratore where codice = 180;


//-----------------------------------------------------------------------------------------------------------------------