

CREATE TABLE ENTREPRISE (
	idEntreprise INT GENERATED ALWAYS AS IDENTITY,
	nomEntreprise varchar(25),
	constraint pkEntreprise primary key(idEntreprise));

CREATE TABLE PROFESSEUR (
	noProfesseur INT GENERATED ALWAYS AS IDENTITY,
	nomProfesseur varchar(25),
	constraint pkProfesseur primary key(noProfesseur)
);

CREATE TABLE SALLE (
	idSalle INT GENERATED ALWAYS AS IDENTITY,
	nomSalle varchar(25),
	constraint pkSalle primary key(idSalle)
);

CREATE TABLE JURY (
	idJury INT GENERATED ALWAYS AS IDENTITY,
	nomJury varchar(25),
	idSalle int,
	constraint pkJury primary key(idJury),
	constraint fkJurySalle foreign key (idSalle) REFERENCES SALLE(idSalle)
	);

CREATE TABLE ETUDIANT (
	noEtudiant INT GENERATED ALWAYS AS IDENTITY,
	nomEtudiant varchar(25),
	tuteurPresent int,
	idEntreprise int,
	noProfesseur int,
	constraint pkEtudiant primary key(noEtudiant),
	constraint fkEtudiantEntreprise foreign key (idEntreprise) REFERENCES ENTREPRISE(idEntreprise),
	constraint fkEtudiantProfesseur foreign key (noProfesseur) REFERENCES PROFESSEUR(noProfesseur)
);


CREATE TABLE SECOMPOSE (
	idJury int,
	noProfesseur int,
	constraint pkSeCompose primary key(idJury, noProfesseur),
	constraint fkSeComposeJury foreign key (idJury) REFERENCES JURY(idJury),
	constraint fkSeComposeProfesseur foreign key (noProfesseur) REFERENCES PROFESSEUR(noProfesseur)
);

CREATE TABLE SOUTIENT (
	noEtudiant int,
	idJury int,
	dateSout date,
	note float,
	constraint pkSoutient primary key(noEtudiant, idJury),
	constraint fkSoutientJury foreign key (idJury) REFERENCES JURY(idJury),
	constraint fkSoutientEtudiant foreign key (noEtudiant) REFERENCES ETUDIANT(noEtudiant)
);

insert into entreprise values(default, 'Google');
insert into entreprise values(default, 'PSA');
insert into entreprise values(default, 'Apple');
insert into entreprise values(default, 'IBM');


insert into professeur values(default, 'Ambert');
insert into professeur values(default, 'Couchot');
insert into professeur values(default, 'Couturier');
insert into professeur values(default, 'Domas');



insert into salle values(default, '101');
insert into salle values(default, '102');
insert into salle values(default, '105');
insert into salle values(default, '108');


insert into jury values(default, 'jury1', 1);
insert into jury values(default, 'jury2', 2);
insert into jury values(default, 'jury3', 3);
insert into jury values(default, 'jury4', 1);


insert into etudiant values(default, 'toto', 1, 1, 1);
insert into etudiant values(default, 'etudiant2', 1, 1, 1);
insert into etudiant values(default, 'etudiant3', 0, 2, 2);
insert into etudiant values(default, 'etudiant4', 0, 3, 2);
insert into etudiant values(default, 'etudiant5', 1, 4, 2);
insert into etudiant values(default, 'etudiant6', 0, 2, 3);
insert into etudiant values(default, 'etudiant7', 1, 4, 4);
insert into etudiant values(default, 'etudiant8', 1, 1, 3);	


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

insert into soutient values(1,1,'06/06/2020', 12.0);
insert into soutient values(2,2,'06/06/2020', 16.0);
insert into soutient values(3,2,'06/06/2020', 12.0);
insert into soutient values(4,1,'06/06/2020', 8.0);
insert into soutient values(5,3,'07/06/2020', 13.0);
insert into soutient values(6,4,'07/06/2020', 10.0);
insert into soutient values(7,3,'07/06/2020', 12.0);
insert into soutient values(8,3,'07/06/2020', 15.0);