--	use master
--	drop database prjcodetest
--	create database prjcodetest
--	use prjcodetest
--- a project
create table prj(
	id int identity(1,1)
	not null primary key
)

create table act(
	id int identity(1,1)
	not null primary key
)

----
create table act_coding(
	act_id int not null	
	, prj_id int not null		
)

alter table act_coding 
add constraint PK_act_coding
primary key(act_id)
go


alter table act_coding
add constraint FK_act_coding@2@act
foreign key(act_id) 
references act(id)


alter table act_coding
add constraint 
UQ@act_coding
unique(prj_id, act_id)
go


--
----
create table act_testing(
	act_id int not null	
	, prj_id int not null		
)

alter table act_testing 
add constraint PK_act_testing
primary key(act_id)
go


alter table act_testing
add constraint FK_act_testing@2@az
foreign key(act_id) 
references act(id)


alter table act_testing
add constraint 
UQ@act_testing
unique(prj_id, act_id)
go


---ora la tripletta

create table triple(
	prj_id int not null
	, act_coding_id int not null
	, act_testing_id int not null	
)
go


alter table triple
add constraint PK_triple
primary key(prj_id, act_coding_id, act_testing_id)
go



alter table triple 
add constraint FK_triple@2@act_testing
foreign key (prj_id,act_testing_id)
references act_testing(prj_id,act_id)
go

alter table triple
add constraint FK_triple@act_coding
foreign key(prj_id, act_coding_id)
references act_coding(prj_id, act_id)
go