#Création des tables

DROP TABLE salarie;
CREATE TABLE salarie(
matricule INTEGER NOT NULL PRIMARY KEY,
nomsalarie VARCHAR(50),
prenomsalarie VARCHAR(50),
adr1salarie VARCHAR(50),
adr2salarie VARCHAR(50),
cpsalarie VARCHAR(5),
villesalarie VARCHAR(50),
dateembauche DATE);

DROP TABLE produit;
CREATE TABLE produit(
codeproduit VARCHAR(10) NOT NULL PRIMARY KEY,
nomproduit VARCHAR(50));

DROP TABLE specialiser;
CREATE TABLE specialiser(
matricule INTEGER NOT NULL REFERENCES salarie(matricule),
codeproduit VARCHAR(10) NOT NULL REFERENCES produit(codeproduit),
PRIMARY KEY (matricule, codeproduit));

DROP TABLE formation;
CREATE TABLE formation(
codeform VARCHAR(10) NOT NULL PRIMARY KEY,
libelleform VARCHAR(50),
nbmaxstagiaires INTEGER,
codeproduit VARCHAR(10) REFERENCES produit(codeproduit));

DROP TABLE session;
CREATE TABLE session(
codeform VARCHAR(10) NOT NULL REFERENCES formation(codeform),
nosession INTEGER NOT NULL,
datesession DATE,
matricule INTEGER REFERENCES salarie(matricule),
PRIMARY KEY (codeform,nosession));

DROP TABLE client;
CREATE TABLE client(
noclient INTEGER NOT NULL PRIMARY KEY,
nomclient VARCHAR(50),
adr1client VARCHAR(50),
adr2client VARCHAR(50),
cpclient VARCHAR(5),
villeclient VARCHAR(50));

DROP TABLE stagiaire;
CREATE TABLE stagiaire(
nostagiaire INTEGER NOT NULL PRIMARY KEY,
nomstagiaire VARCHAR(50),
prenomstagiaire VARCHAR(50),
noclient INTEGER REFERENCES client(noclient));

DROP TABLE participer;
CREATE TABLE participer(
codeform VARCHAR(10) NOT NULL REFERENCES formation(codeform),
nosession INTEGER NOT NULL REFERENCES session(nosession),
nostagiaire INTEGER NOT NULL REFERENCES stagiaire(nostagiaire),
PRIMARY KEY (codeform, nosession, nostagiaire));

#Insertion des données
INSERT INTO client values(1,'TZIG Travaux Publics','87 rue Carnot','','05000','Gap');
INSERT INTO client values(2,'KEL & BEL Cie','54 bd Georges Pompidou','','05000','Gap');
INSERT INTO client values(3,'JC++ Corporation','5 place jules ferry','','05000','Gap');

INSERT INTO stagiaire values(1,'OPILON','Marthe',1);
INSERT INTO stagiaire values(2,'ENFAILLITE','Mélusine',1);
INSERT INTO stagiaire values(3,'ZETOFRAIS','Mélanie',1);
INSERT INTO stagiaire values(4,'DOUCHE','Urbain',2);
INSERT INTO stagiaire values(5,'MARTEAU','Philippe',2);
INSERT INTO stagiaire values(6,'HERISSON','JC',3);
INSERT INTO stagiaire values(7,'FOUINE','Nicolas',3);
INSERT INTO stagiaire values(8,'LE CHAT','David',3);
INSERT INTO stagiaire values(9,'PIKATCHU','Diane',3);
INSERT INTO stagiaire values(10,'VIPERE','Laurie',3);
INSERT INTO stagiaire values(11,'ALCOOL','Renaud',3);
INSERT INTO stagiaire values(12,'51','Johan',3);
INSERT INTO stagiaire values(13,'CHOUBI','JB',3);
INSERT INTO stagiaire values(14,'SPEEDY','Jérome',3);
INSERT INTO stagiaire values(15,'BRAIN','Laurent',3);
INSERT INTO stagiaire values(16,'CHAMEAU','taha',3);

INSERT INTO salarie values(1,'Thomassin','Gilles','lycée dominique villars','','05000','Gap','1995-08-01');
INSERT INTO salarie values(2,'Bourges','Claude','lycée dominique villars','','05000','Gap','2000-05-01');
INSERT INTO salarie values(3,'Lamoureux','Bernard','lycée dominique villars','','05000','Gap','1998-06-01');

INSERT INTO produit values('prod_QT','Qt');

INSERT INTO formation values('GPAIE-101','Formation des IG1',12,'prod_QT');
INSERT INTO formation values('GPAIE-102','Formation des IG1',23,'prod_QT');
INSERT INTO formation values('GPAIE-103','Formation des IG2',19,'prod_QT');
INSERT INTO formation values('GPAIE-104','Formation des IG2',13,'prod_QT');
INSERT INTO formation values('GPAIE-105','Formation des IG2',17,'prod_QT');

INSERT INTO session values('GPAIE-101',1,'2002-09-05',1);
INSERT INTO session values('GPAIE-101',2,'2002-10-05',1);
INSERT INTO session values('GPAIE-105',1,'2002-08-01',2);
INSERT INTO session values('GPAIE-105',2,'2002-11-01',3);
INSERT INTO session values('GPAIE-105',3,'2003-01-24',3);

INSERT INTO participer values('GPAIE-101',1,1);
INSERT INTO participer values('GPAIE-101',1,2);
INSERT INTO participer values('GPAIE-101',1,3);
INSERT INTO participer values('GPAIE-101',1,4);
INSERT INTO participer values('GPAIE-101',2,3);
INSERT INTO participer values('GPAIE-101',2,4);
INSERT INTO participer values('GPAIE-101',2,5);
INSERT INTO participer values('GPAIE-105',1,6);
INSERT INTO participer values('GPAIE-105',1,7);
INSERT INTO participer values('GPAIE-105',1,8);
INSERT INTO participer values('GPAIE-105',1,9);
INSERT INTO participer values('GPAIE-105',1,10);
INSERT INTO participer values('GPAIE-105',1,11);
INSERT INTO participer values('GPAIE-105',1,12);
INSERT INTO participer values('GPAIE-105',1,13);
INSERT INTO participer values('GPAIE-105',1,14);
INSERT INTO participer values('GPAIE-105',2,11);
INSERT INTO participer values('GPAIE-105',2,12);
INSERT INTO participer values('GPAIE-105',2,13);
INSERT INTO participer values('GPAIE-105',3,6);
INSERT INTO participer values('GPAIE-105',3,8);
INSERT INTO participer values('GPAIE-105',3,9);
INSERT INTO participer values('GPAIE-105',3,10);
INSERT INTO participer values('GPAIE-105',3,14);
INSERT INTO participer values('GPAIE-105',3,15);
INSERT INTO participer values('GPAIE-105',3,16);