create table employes
(Num int primary key,
 	nom varchar(20),
	prenom varchar(20),
	departement varchar(20),
	salaire float,
	tel varchar(10),
	email varchar(20)
);

create table departement
(DeptId int primary key,
Nom varchar(20),
directeur int
);

create table employedept
(deptid int,
numemploye int,
primary key(deptid,numemploye)
);

create table projet
(projetid int primary key,
responsableprojet int,
nomprojet varchar(20),
status varchar(20)
);

create table membreprojet
(projetid int references projet(projetid),
numemploye int references employes(num),
primary key (projetid,numemploye)
);

alter table projet add foreign key (responsableprojet) references employes(num);
alter table departement add foreign key (directeur) references employes(num);
alter table employedept add foreign key (deptid) references departement(deptid);
alter table employedept add foreign key (numemploye) references employes(num);

insert into employes values (1,'guillet','lois','77000',3000,'0102030405',ll@gmail.com);
insert into employes values (2,'wodling','terence','94000',3000,'0102030405','tw@gmail.com');
insert into employes values (3,'costa','elyas','60000',2000,'0102030405','toto@gmail.com');

insert into departement values (1,"projet1",1);
insert into departement values (2,"projet2",3);
insert into departement values (3,"projet3",3);

insert into projet values (1,3,"projet1","en cours");
insert into projet values (2,3,"projet2","stop");
insert into projet values (3,1,"projet3","en cours");
insert into projet values (4,1,"projet4","stop");

insert into membreprojet values (1,3);
insert into membreprojet values (2,3);
insert into membreprojet values (3,1);
insert into membreprojet values (3,2);
insert into membreprojet values (4,2);

insert into employedept values (1,1);
insert into employedept values (1,2);
insert into employedept values (1,3);