CREATE TABLE COMPETENCE
(
id_competence Int Not null,
libelle_competence Varchar(64) Not null,
CONSTRAINT COMPETENCE_PK PRIMARY KEY (id_competence)
) ;
CREATE TABLE FONCTION
(
id_fonction INT NOT NULL,
libelle_fonction VARCHAR(64) NOT NULL,
CONSTRAINT FONCTION_PK PRIMARY KEY (id_fonction)
) ;
CREATE TABLE EQUIPE
(
id_equipe Int Not null,
nom_equipe Varchar(64) Not null,
disponibilite Varchar(64) Not null,
CONSTRAINT EQUIPE_PK PRIMARY KEY (id_equipe)
) ;
CREATE TABLE EMPLOYE
(
id_equipe Int Not null,
id_employe Int Not null,
matricule Varchar(5) Not null,
nom_employe Varchar(64) Not null,
prenom_employe Varchar(64) Not null,
date_embauche Date Not null,
grade Varchar(3) Not null,
CONSTRAINT EMPLOYE_PK PRIMARY KEY (id_equipe, id_employe),
CONSTRAINT EMPLOYE_AK UNIQUE (matricule),
CONSTRAINT EMPLOYE_EQUIPE_FK FOREIGN KEY (id_equipe)
REFERENCES EQUIPE (id_equipe)
) ;
CREATE TABLE CHEF_DE_PROJET
(
id_equipe Int Not null,
id_employe Int Not null,
CONSTRAINT CHEF_DE_PROJET_PK PRIMARY KEY (id_equipe, id_employe),
CONSTRAINT CHEF_DE_PROJET_EMPLOYE_FK FOREIGN KEY (id_equipe, id_employe)
REFERENCES EMPLOYE( id_equipe, id_employe)
) ;
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)
) ;
CREATE TABLE PROJET_EMPLOYE
(
id_equipe Int Not null,
id_projet Int Not null,
id_employe Int Not null,
date_debut_affectation Date Not null,
date_fin_affectation Date Not null,
CONSTRAINT PROJET_EMPLOYE_PK PRIMARY KEY (id_equipe, id_projet, id_employe),
CONSTRAINT PROJET_EMPLOYE_EMPLOYE_FK FOREIGN KEY (id_equipe, id_employe)
REFERENCES EMPLOYE (id_equipe, id_employe),
CONSTRAINT PROJET_EMPLOYE_EQUIPE_FK FOREIGN KEY (id_equipe, id_projet)
REFERENCES PROJET (id_equipe, id_projet)
) ;
CREATE TABLE PROJET_COMPETENCE
(
id_equipe Int Not null,
id_projet Int Not null,
id_competence Int Not null,
CONSTRAINT PROJET_COMPETENCE_PK PRIMARY KEY (id_equipe, id_projet, id_competence),
CONSTRAINT PROJET_COMPETENCE_PROJET_FK FOREIGN KEY (id_equipe, id_projet)
REFERENCES PROJET (id_equipe, id_projet),
CONSTRAINT PROJET_COMPETENCE_COMPETENCE_FK FOREIGN KEY (id_competence)
REFERENCES COMPETENCE (id_competence)
) ;
CREATE TABLE TACHE
(
id_equipe Int Not null,
id_projet Int Not null,
id_tache Int Not null,
libelle_tache Varchar(64) Not null,
CONSTRAINT TACHE_PK PRIMARY KEY (id_equipe, id_projet, id_tache),
CONSTRAINT TACHE_PROJET_FK FOREIGN KEY (id_equipe, id_projet)
REFERENCES PROJET (id_equipe, id_projet)
) ;
CREATE TABLE FORMATION
(
id_equipe Int Not null,
id_projet Int Not null,
id_tache Int Not null,
date_debut date Not null,
date_fin date Not null,
lieu_formation varchar(64) Not null,
CONSTRAINT FORMATION_PK PRIMARY KEY (id_equipe, id_projet, id_tache),
CONSTRAINT FORMATION_TACHE_FK FOREIGN KEY (id_equipe, id_projet, id_tache)
REFERENCES TACHE (id_equipe, id_projet, id_tache)
) ;
CREATE TABLE MISSION
(
id_equipe Int Not null,
id_projet Int Not null,
id_tache Int Not null,
date_debut date Not null,
date_fin date Not null,
lieu_mission varchar(64) Not null,
CONSTRAINT MISSION_PK PRIMARY KEY (id_equipe, id_projet, id_tache),
CONSTRAINT MISSION_TACHE_FK FOREIGN KEY (id_equipe, id_projet, id_tache)
REFERENCES TACHE (id_equipe, id_projet, id_tache)
) ;
CREATE TABLE AUTRE_TACHE
(
id_equipe Int Not null,
id_projet Int Not null,
id_tache Int Not null,
CONSTRAINT AUTRE_TACHE_PK PRIMARY KEY (id_equipe, id_projet, id_tache),
CONSTRAINT AUTRE_TACHE_TACHE_FK FOREIGN KEY (id_equipe, id_projet, id_tache)
REFERENCES TACHE (id_equipe, id_projet, id_tache)
) ;
CREATE TABLE COMPETENCE_EMPLOYE
(
id_equipe Int Not null,
id_employe Int Not null,
id_competence Int Not null,
niveau Int Not null,
CONSTRAINT COMPETENCE_EMPLOYE_PK PRIMARY KEY (id_equipe, id_employe, id_competence),
CONSTRAINT COMPETENCE_EMPLOYE_EMPLOYE_FK FOREIGN KEY (id_equipe, id_employe)
REFERENCES EMPLOYE (id_equipe, id_employe),
CONSTRAINT COMPETENCE_EMPLOYE_COMPETENCE_FK FOREIGN KEY (id_competence)
REFERENCES COMPETENCE (id_competence)
) ;
CREATE TABLE PROJET_COMPETENCE_EMPLOYE
(
id_equipe Int Not null,
id_projet Int Not null,
id_employe Int Not null,
id_competence Int Not null,
CONSTRAINT PROJET_COMPETENCE_EMPLOYE_PK PRIMARY KEY (id_equipe, id_projet, id_employe, id_competence),
CONSTRAINT PROJET_COMPETENCE_EMPLOYE_PROJET_EMPLOYE_FK FOREIGN KEY (id_equipe, id_projet, id_employe)
REFERENCES PROJET_EMPLOYE (id_equipe, id_projet, id_employe),
CONSTRAINT PROJET_COMPETENCE_EMPLOYE_COMPETENCE_EMPLOYE_FK FOREIGN KEY (id_equipe, id_employe, id_competence)
REFERENCES COMPETENCE_EMPLOYE (id_equipe, id_employe, id_competence),
CONSTRAINT PROJET_COMPETENCE_EMPLOYE_PROJET_COMPETENCE_FK FOREIGN KEY (id_equipe, id_projet, id_competence)
REFERENCES PROJET_COMPETENCE (id_equipe, id_projet, id_competence)
) ;
CREATE TABLE PTE
(
id_equipe Int Not null,
id_projet Int Not null,
id_tache Int Not null,
id_employe Int Not null,
id_competence Int Not null,
id_fonction Int Not null,
CONSTRAINT PTE_PK PRIMARY KEY (id_equipe, id_projet, id_tache, id_employe),
CONSTRAINT PTE_TACHE_FK FOREIGN KEY (id_equipe, id_projet, id_tache)
REFERENCES TACHE (id_equipe, id_projet, id_tache),
CONSTRAINT PTE_PROJET_COMPETENCE_EMPLOYE_FK02 FOREIGN KEY (id_equipe, id_projet, id_employe, id_competence)
REFERENCES PROJET_COMPETENCE_EMPLOYE (id_equipe, id_projet, id_employe, id_competence),
CONSTRAINT PTE_FONCTION_FK FOREIGN KEY (id_fonction)
REFERENCES FONCTION (id_fonction)
) ;
Partager