Bonsoir supras31,
Envoyé par
supras31
je vois pas comment ça se fait l'affectation d'un employé à un projet
j'avais plus de temps en attente de votre aide , je dois commencer le développement le plutôt possible.
L’affectation d’un employé à un projet n’est possible que si cet employé a les compétences requises pour ce projet (table PROJET_COMPETENCE_EMPLOYE) .
Reprenons donc la série des INSERT en tenant compte de la modélisation où figure la classe EQUIPE. A noter que j’ai ajouté l’attribut code_projet dans l’en-tête de la table PROJET :
CREATE TABLE PROJET
(
id_equipe Int Not null,
id_projet Int Not null,
code_projet char(3) Not Null,
id_employe_chef Int Not null,
nom_projet Varchar(64) Not null,
date_debut_projet Date Not null,
duree_projet Int Not null,
CONSTRAINT PROJET_PK PRIMARY KEY (id_equipe, id_projet),
CONSTRAINT PROJET_AK UNIQUE (code_projet),
CONSTRAINT PROJET_CHEF_DE_PROJET_FK FOREIGN KEY (id_equipe, id_employe_chef)
REFERENCES CHEF_DE_PROJET (id_equipe, id_employe),
CONSTRAINT PROJET_EQUIPE_FK FOREIGN KEY (id_equipe)
REFERENCES EQUIPE (id_equipe)
) ;
(1) - On sait créer des compétences :
INSERT INTO COMPETENCE (id_competence, libelle_competence) VALUES (1, 'compétence 1') ;
INSERT INTO COMPETENCE (id_competence, libelle_competence) VALUES (2, 'compétence 2') ;
INSERT INTO COMPETENCE (id_competence, libelle_competence) VALUES (3, 'compétence 3') ;
INSERT INTO COMPETENCE (id_competence, libelle_competence) VALUES (4, 'compétence 4') ;
SELECT *, '' AS '<= COMPETENCE' FROM COMPETENCE ;
(2) - On sait créer des fonctions :
INSERT INTO FONCTION (id_fonction, libelle_fonction) VALUES (1, 'fonction 1') ;
INSERT INTO FONCTION (id_fonction, libelle_fonction) VALUES (2, 'fonction 2') ;
INSERT INTO FONCTION (id_fonction, libelle_fonction) VALUES (3, 'fonction 3') ;
SELECT *, '' AS '<= FONCTION' FROM FONCTION ;
(3) - On sait créer des équipes :
INSERT INTO EQUIPE (id_equipe, nom_equipe, disponibilite) VALUES (1, 'equipe 1', 'dispo 1') ;
INSERT INTO EQUIPE (id_equipe, nom_equipe, disponibilite) VALUES (2, 'equipe 2', 'dispo 2') ;
INSERT INTO EQUIPE (id_equipe, nom_equipe, disponibilite) VALUES (3, 'equipe 3', 'dispo 3') ;
SELECT *, '' AS '<= EQUIPE' FROM EQUIPE ;
(4) - On sait ajouter des employés dans la table EMPLOYE :
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 5, 'b04', 'Trancène', 'Jean', '2007-07-14', 'g01') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 6, 'b07', 'Bonneau', 'Jean', '2009-05-01', 'h02') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 7, 'b08', 'Ponce', 'Pierre', '2009-07-01', 'h02') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 8, 'c05', 'Titegoutte', 'Justine', '2009-09-01', 'g02') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 9, 'c06', 'Titegoutte', 'Corinne', '2009-09-02', 'g01') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 10, 'c07', 'Letracteur', 'Igor', '2010-01-01', 'g02') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 11, 'c08', 'Letracteur', 'Hercule', '2010-01-01', 'g02') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 12, 'c09', 'Hocquart de Tours', 'Adhémar', '2010-05-01', 'g01') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 13, 'd01', 'Surlessièges', 'Amedeus', '2010-05-02', 'g01') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (1, 14, 'd02', 'Labarrière', 'Ildefonse', '2010-05-03', 'g01') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (2, 21, 'e01', 'Mimolette', 'Franz', '2008-05-21', 'g02') ;
INSERT INTO EMPLOYE (id_equipe, id_employe, matricule, nom_employe, prenom_employe, date_embauche, grade)
VALUES (2, 22, 'e02', 'Jazz', 'Manu', '2008-05-22', 'g03') ;
SELECT *, '' AS '<= EMPLOYE' FROM EMPLOYE ;
(5) - On sait définir les compétences des employés :
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'b04')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'b04')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1'), 2 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'b04')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'b04')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 2'), 2 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'b04')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'b04')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 3'), 2 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c05')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c05')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1'), 2 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c05')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c05')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 3'), 2 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c06')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c06')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1'), 3 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c06')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c06')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 3'), 3 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c06')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c06')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 4'), 2 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c07')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c07')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1'), 2 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c07')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c07')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 2'), 1 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c08')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c08')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1'), 2 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'c09')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c09')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 3'), 1 ;
INSERT INTO COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence, niveau) SELECT
(SELECT id_equipe FROM EMPLOYE WHERE matricule = 'd02')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'd02')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 4'), 2 ;
SELECT *, '' AS '<= COMPETENCE_EMPLOYE' FROM COMPETENCE_EMPLOYE ;
(6) - Nomination de Jean Trancène et Pierre Ponce comme chefs de projets (équipe 1), Franz Mimolette comme chef de projet (équipe 2) :
INSERT INTO CHEF_DE_PROJET (id_equipe, id_employe)
SELECT id_equipe, id_employe FROM EMPLOYE WHERE matricule = 'b04' ;
INSERT INTO CHEF_DE_PROJET (id_equipe, id_employe)
SELECT id_equipe, id_employe FROM EMPLOYE WHERE matricule = 'b08' ;
INSERT INTO CHEF_DE_PROJET (id_equipe, id_employe)
SELECT id_equipe, id_employe FROM EMPLOYE WHERE matricule = 'e01' ;
SELECT *, '' AS '<= CHEF_DE_PROJET' FROM CHEF_DE_PROJET ;
(7) - Créons les projets Aristote et Socrate (pour l'équipe 1), Platon (pour l'équipe 2) :
INSERT INTO PROJET (id_equipe, Id_projet, code_projet, id_employe_chef, nom_projet, date_debut_projet, duree_projet)
SELECT (SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, 1
, 'ari'
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'b04'), 'Aristote', '2015-04-02', 6
FROM EQUIPE AS x
WHERE nom_equipe = 'equipe 1'
;
INSERT INTO PROJET (id_equipe, Id_projet, code_projet, id_employe_chef, nom_projet, date_debut_projet, duree_projet)
SELECT (SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, 2
, 'soc'
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'b08'), 'Socrate', '2015-04-07', 12
FROM EQUIPE AS x
WHERE nom_equipe = 'equipe 1'
;
INSERT INTO PROJET (id_equipe, Id_projet, code_projet, id_employe_chef, nom_projet, date_debut_projet, duree_projet)
SELECT (SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 2')
, 1
, 'pla'
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'e01'), 'Platon', '2015-04-08', 8
FROM EQUIPE AS x
WHERE nom_equipe = 'equipe 2'
;
SELECT *, '' AS '<= PROJET' FROM PROJET ;
(8) - On sait définir les compétences requises pour les projets Aristote, Socrate et Platon :
INSERT INTO PROJET_COMPETENCE (id_equipe, id_projet, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'ari')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1') ;
INSERT INTO PROJET_COMPETENCE (id_equipe, id_projet, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'ari')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 3') ;
INSERT INTO PROJET_COMPETENCE (id_equipe, id_projet, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'soc')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 2') ;
INSERT INTO PROJET_COMPETENCE (id_equipe, id_projet, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1' )
, (SELECT id_projet FROM PROJET WHERE code_projet = 'soc')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 4') ;
INSERT INTO PROJET_COMPETENCE (id_equipe, id_projet, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 2')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'pla')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1') ;
INSERT INTO PROJET_COMPETENCE (id_equipe, id_projet, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 2')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'pla')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 4') ;
SELECT *, '' AS '<= PROJET_COMPETENCE' FROM PROJET_COMPETENCE order by 1, 2, 3 ;
(9) - On sait affecter les employés aux projets de leur équipe parce qu’ils ont les compétences requises :
INSERT INTO PROJET_COMPETENCE_EMPLOYE (id_equipe, id_projet, id_employe, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'ari')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'b07')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1') ;
INSERT INTO PROJET_COMPETENCE_EMPLOYE (id_equipe, id_projet, id_employe, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'ari')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'b07')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 3') ;
INSERT INTO PROJET_COMPETENCE_EMPLOYE (id_equipe, id_projet, id_employe, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'ari')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c05')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 1') ;
INSERT INTO PROJET_COMPETENCE_EMPLOYE (id_equipe, id_projet, id_employe, id_competence) SELECT
(SELECT id_equipe FROM EQUIPE WHERE nom_equipe = 'equipe 1')
, (SELECT id_projet FROM PROJET WHERE code_projet = 'ari')
, (SELECT id_employe FROM EMPLOYE WHERE matricule = 'c05')
, (SELECT id_competence FROM COMPETENCE WHERE libelle_competence = 'compétence 3') ;
SELECT *, '' AS '<= PROJET_COMPETENCE_EMPLOYE' FROM PROJET_COMPETENCE_EMPLOYE order by 1, 2, 3 ;
(10) - Pour savoir quels employés sont affectés aux projets de leur équipe :
SELECT DISTINCT id_equipe, id_projet, id_employe
FROM PROJET_COMPETENCE_EMPLOYE ;
Partager