Discussion :

[fsmrel]

Bonsoir Mossane,


Le MLD est conforme.

Au sujet des propositions de triggers autres que ceux qui se rapportent à la spécialisation, mettons-nous d’accord sur les règles qu’ils ont à faire respecter.

Logiquement la règle suivante doit prévaloir :

(R1) Si un échelon n’est pas directement rattaché à une classe, alors le groupe auquel il est rattaché doit lui-même être rattaché directement à une classe (principe de transitivité).

Sinon, si l’on énonçait ceci :

(R2) Si un échelon n’est pas directement rattaché à une classe et si le groupe auquel il est rattaché n’est pas non plus directement rattaché à une classe [mais à une fonction], alors cette fonction doit être référencée (directement ou non) par une classe.

Alors il y aurait un problème, car pour une fonction il peut y avoir N classes, donc on ne saurait pas déterminer celle qui convient pour l’échelon (non respect du principe de transitivité).

Sommes-nous d’accord ?

Étant donné qu'une fonction_corps par exemple appartient à un et un seul corps, peut-on remplacer les associations simples par des compositions et passer de ce code SQL :

TABLE FONCTION_CORPS

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE FONCTION_CORPS 
(
   FonctionId              INT NOT NULL,
   CorpsId                 INT NOT NULL,
   CONSTRAINT FONCTION_CORPS PRIMARY KEY (FonctionId),
   CONSTRAINT FONCTION_CORPS_FONCTION_FK FOREIGN KEY (FonctionId)
       REFERENCES FONCTION (FonctionId) ON DELETE CASCADE,
   CONSTRAINT FONCTION_CORPS_CORPS_FK FOREIGN KEY (CorpsId)
       REFERENCES CORPS (CorpsId)    
) ;

à celui-ci ?

TABLE FONCTION_CORPS

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE FONCTION_CORPS 
(
   FonctionId              INT NOT NULL,
   CorpsId                 INT NOT NULL,
   CONSTRAINT FONCTION_CORPS PRIMARY KEY (FonctionId, CorpsId),
   CONSTRAINT FONCTION_CORPS_FONCTION_FK FOREIGN KEY (FonctionId)
       REFERENCES FONCTION (FonctionId) ON DELETE CASCADE,
   CONSTRAINT FONCTION_CORPS_CORPS_FK FOREIGN KEY (CorpsId)
       REFERENCES CORPS (CorpsId) ON DELETE CASCADE   
) ;

On ne peut pas passer au code que vous proposez. En effet, si la paire {FonctionId, CorpsId} était clé (primaire), cela voudrait dire qu’une fonction-corps pourrait appartenir à plusieurs corps, en contradiction avec ce que vous avez énoncé : « une fonction_corps par exemple appartient à un et un seul corps ».

Principe universel à appliquer aux clés des tables. Soit T une table et K une clé de T :

Unicité : Deux lignes distinctes de T ne peuvent avoir simultanément même valeur de K.
Irréductibilité (minimalité) : il n’existe pas X strictement inclus dans K tel que X vérifie aussi la propriété d’unicité.


La paire {FonctionId, CorpsId} est ce qu’on appelle une surclé (règle d’irréductibilité non exigée). Il m’arrive de m’en servir en tant que clé alternative (clause UNIQUE ci-dessous), mais (n’en dites rien à personne !) pour établir certains liens d’intégrité référentielle quand cela permet d’éviter des triggers :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE FONCTION_CORPS 
(
   FonctionId              INT NOT NULL,
   CorpsId                 INT NOT NULL,
   CONSTRAINT FONCTION_CORPS_PK PRIMARY KEY (FonctionId),
 
   CONSTRAINT FONCTION_CORPS_AK UNIQUE (FonctionId, CorpsId),
 
   CONSTRAINT FONCTION_CORPS_FONCTION_FK FOREIGN KEY (FonctionId)
       REFERENCES FONCTION (FonctionId) ON DELETE CASCADE,
   CONSTRAINT FONCTION_CORPS_CORPS_FK FOREIGN KEY (CorpsId)
       REFERENCES CORPS (CorpsId) 
) ;

[mossane]

Bonjour fsmrel,

Je n'ai pas été très vigilant. Je me suis tardivement rendu compte que la discussion s'est poursuivie sur une nouvelle page.

(R1) Si un échelon n’est pas directement rattaché à une classe, alors le groupe auquel il est rattaché doit lui-même être rattaché directement à une classe (principe de transitivité).

Dans notre contexte, si un échelon n'est pas directement rattaché à une classe, le groupe auquel il est rattaché n'est pas non plus impérativement associé à une classe. Il peut être associé à une fonction. Il y a des corps où le concept de classe n'existe pas.

(R2) Si un échelon n’est pas directement rattaché à une classe et si le groupe auquel il est rattaché n’est pas non plus directement rattaché à une classe [mais à une fonction], alors cette fonction doit être référencée (directement ou non) par une classe.

Pour les corps où le concept de classe n'existe pas, les échelons (de ces corps) sont rattachés à leurs groupes respectifs qui sont eux-mêmes rattachés à des fonctions. Ces fonctions ne peuvent pas être référencées par des classes puisqu'elles (les classes) n'existent pas. Cependant, elles (les fonctions) font référence soit directement à un corps, soit à un collège.

Reprenons le diagramme de classe ...



et la structure dérivée.



En partant d'un échelon, si on remonte dans la hiérarchie de la classification pour atteindre le corps auquel appartient un agent par exemple, plusieurs cas de figure peuvent se présenter. Énumérons-les.

Cas 1 :

ECHELON -> CLASSE -> CORPS. C'est le cas le plus général.

Cas 2 :

ECHELON -> CLASSE -> FONCTION -> CORPS

Cas 3 :

ECHELON -> CLASSE -> FONCTION -> COLLEGE -> CORPS

Cas 4 :

ECHELON -> GROUPE -> CLASSE -> CORPS.

Cas 5 :

ECHELON -> GROUPE -> CLASSE -> FONCTION -> CORPS

Cas 6 :

ECHELON -> GROUPE -> CLASSE -> FONCTION -> COLLEGE -> CORPS

Cas 7 :

ECHELON -> GROUPE -> FONCTION -> CORPS

Cas 8 :

ECHELON -> GROUPE -> FONCTION -> COLLEGE -> CORPS

Donc en partant d'un échelon, le couple de triggers (ECHELON_CLASSE, ECHELON_GROUPE) nous impose de choisir un des des deux chemins possibles. Si on décide de passer par ECHELON_CLASSE par exemple, une fois arrivé à CLASSE, il me semble qu'on ne doit plus redescendre vers GROUPE via GROUPE_CLASSE puisque dans la classification une classe est au dessus d'un groupe. C'est suivant ce raisonnement que j'ai proposé le couple de triggers (ECHELON_CLASSE, GROUPE_CLASSE) et les autres.

On ne peut pas passer au code que vous proposez. En effet, si la paire {FonctionId, CorpsId} était clé (primaire), cela voudrait dire qu’une fonction-corps pourrait appartenir à plusieurs corps, en contradiction avec ce que vous avez énoncé : « une fonction_corps par exemple appartient à un et un seul corps ».

J'ai pris note.

Merci !

[fsmrel]

Bonsoir mossane,

Il y a des corps où le concept de classe n'existe pas.

D’accord. J’avais pris pour référence les cas que vous aviez présentés ici, lesquels laissaient à penser que directement ou non, un échelon faisait référence à une classe.

Si on décide de passer par ECHELON_CLASSE par exemple, une fois arrivé à CLASSE, il me semble qu'on ne doit plus redescendre vers GROUPE via GROUPE_CLASSE puisque dans la classification une classe est au dessus d'un groupe.

Ce que l’on pourrait traduire ainsi sous forme de contrainte :

Une classe donnée C ne peut pas servir pour regrouper à la fois des échelons (par ECHELON_CLASSE) et des groupes (par GROUPE_CLASSE).

Exemple : si la classe cl1 comporte l’échelon e2, elle ne peut pas comporter le groupe g1 si celui-ci comporte l’échelon e1.

Est-ce bien ainsi qu’il faut voir les choses ?

Au sujet du MLD : du point de vue de la structure on est en phase. Il faudrait quand même faire apparaître les clés candidates des tables. Si l’AGL ne le permet pas, autant passer à SQL.

[mossane]

Bonjour fsmrel,

Ce que l’on pourrait traduire ainsi sous forme de contrainte :

Une classe donnée C ne peut pas servir pour regrouper à la fois des échelons (par ECHELON_CLASSE) et des groupes (par GROUPE_CLASSE).

Exactement ! Suivant les spécificités du corps auquel elle appartient, une classe C peut soit regrouper directement des échelons, soit des groupes, mais pas les deux à la fois. Est-ce que l'on appelle contrainte de partition ?

Exemple : si la classe cl1 comporte l’échelon e2, elle ne peut pas comporter le groupe g1 si celui-ci comporte l’échelon e1.

La contrainte c'est qu'une classe ne peut pas comporter à la fois des groupes et des échelons. Cependant, si la classe cl1 comporte le groupe g1, celui-ci peut librement comporter un échelon e2 qui lui est spécifique et qui est différent de l'échelon e2 du groupe g3 de la même classe ou d'une autre classe. C'est très similaire à ce que vous aviez proposé dans votre premier message avec le diagramme suivant :



C'est pour cette raison que je m'étais demandé s'il ne fallait pas remplacer les associations simples du diagramme suivant par des liens de type composition.



Ce qui vaut pour les classes vaut également pour les corps et les fonctions.

Une fonction peut comporter soit un groupe, soit une classe, mais pas les deux à la fois.

Un corps peut comporter soit un collège, soit une fonction, soit une classe, mais pas les trois ni les deux à la fois.

Un collège ne comporte que des fonctions.

Un groupe ne comporte que des échelons.

L'échelon est le dernier maillon de la chaine.

Merci !

[fsmrel]

Bonjour mossane,


Je suis là (avec une bonne bronchite...), je suis en train de faire des tests avec MySQL, je vous tiendrai au courant dès que possible,

François

[mossane]

Bonsoir fsmrel,

D'accord ! Portez-vous bien et merci.

Diégane

[fsmrel]

Bonsoir Diégane,



Prenons le cas des échelons. Pour le moment j’en suis aux règles suivantes :

(R1) Un échelon est directement rattaché soit à une classe, soit à un groupe.

(R2) Si un groupe est directement rattaché à une classe, alors aucun échelon ne peut être directement rattaché à cette classe.

Pour des raisons de symétrie, on peut aussi énoncer :

(R3) Si un échelon est directement rattaché à une classe, alors aucun groupe ne peut être directement rattaché à cette classe.

En SQL (MySQL) :

TABLE CLASSE

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE CLASSE 
(
   ClasseId           CHAR(3) NOT NULL,
   ClasseLibelle      VARCHAR(64) NOT NULL,
   CONSTRAINT CLASSE_PK PRIMARY KEY (ClasseId)
) ;

TABLE GROUPE

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE GROUPE 
(
   GroupeId           CHAR(3) NOT NULL,
   GroupeLibelle      VARCHAR(64) NOT NULL,
   CONSTRAINT GROUPE_PK PRIMARY KEY (GroupeId)
) ;

TABLE ECHELON

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TABLE ECHELON 
(
   EchelonId         CHAR(3) NOT NULL,
   EchelonLibelle    VARCHAR(64) NOT NULL,
   CONSTRAINT ECHELON_PK PRIMARY KEY (EchelonId)
) ;

TABLE GROUPE_CLASSE

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE GROUPE_CLASSE 
(
   GroupeId         CHAR(3) NOT NULL,
   ClasseId         CHAR(3) NOT NULL,
   CONSTRAINT GROUPE_CLASSE_PK PRIMARY KEY (GroupeId)
,  CONSTRAINT GROUPE_CLASSE_GROUPE_FK FOREIGN KEY (GroupeId)
       REFERENCES GROUPE (GroupeId)
,  CONSTRAINT GROUPE_CLASSE_CLASSE_FK FOREIGN KEY (ClasseId)
       REFERENCES CLASSE (ClasseId)
) ;

TABLE ECHELON_CLASSE

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE ECHELON_CLASSE 
(
   EchelonId        CHAR(3) NOT NULL,
   ClasseId         CHAR(3) NOT NULL,
   CONSTRAINT ECHELON_CLASSE_PK PRIMARY KEY (EchelonId)
 , CONSTRAINT ECHELON_CLASSE_CLASSE_FK FOREIGN KEY (ClasseId)
      REFERENCES CLASSE (ClasseId)
 , CONSTRAINT ECHELON_CLASSE_ECHELON_FK FOREIGN KEY (EchelonId)
      REFERENCES ECHELON (EchelonId)
) ;

TABLE ECHELON_GROUPE

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE ECHELON_GROUPE 
(
   EchelonId        CHAR(3) NOT NULL,
   GroupeId         CHAR(3) NOT NULL,
   CONSTRAINT ECHELON_GROUPE_PK PRIMARY KEY (EchelonId)
 , CONSTRAINT ECHELON_GROUPE_GROUPE_FK FOREIGN KEY (GroupeId)
      REFERENCES GROUPE (GroupeId)
 , CONSTRAINT ECHELON_GROUPE_ECHELON_FK FOREIGN KEY (EchelonId)
      REFERENCES ECHELON (EchelonId)
) ;

Les triggers...

Contrôle du rattachement d’un échelon à une classe (insert)

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TRIGGER ECHELON_CLASSE_XT_INSERT BEFORE INSERT ON ECHELON_CLASSE
FOR EACH ROW 
BEGIN
 
    SET @N = (
              SELECT COUNT(*) 
              FROM   ECHELON_GROUPE 
              WHERE EchelonId = NEW.EchelonId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout d’un échelon de type classe : un échelon de type groupe existe déjà pour l’attribut EchelonId égal à ', NEW.EchelonId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;        
    ELSE
        SET @N = (
                  SELECT COUNT(*)
                  FROM   GROUPE_CLASSE
                  WHERE  ClasseId = NEW.ClasseId
                 ) ;
         IF  @N > 0 THEN
             SET @T = CONCAT ('Echec d’ajout d’un échelon de type classe : un groupe de type classe existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
             SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
        END IF ;
    END IF ;
END ;

Contrôle du rattachement d’un échelon à une classe (update)

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TRIGGER ECHELON_CLASSE_XT_UPDATE BEFORE UPDATE ON ECHELON_CLASSE
FOR EACH ROW 
BEGIN
 
    SET @N = (
              SELECT COUNT(*) 
              FROM   ECHELON_GROUPE 
              WHERE EchelonId = NEW.EchelonId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’un échelon de type classe : un échelon de type groupe existe déjà pour l’attribut EchelonId égal à ', NEW.EchelonId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    ELSE 
        SET @N = (
                  SELECT COUNT(*)
                  FROM   GROUPE_CLASSE
                  WHERE  ClasseId = NEW.ClasseId
                 ) ;
        IF  @N > 0 THEN
            SET @T = CONCAT ('Echec d’update d’un échelon de type classe : un groupe de type classe existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
            SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
        END IF ;
   END IF ;        
END ;

Contrôle du rattachement d’un échelon à un groupe (insert)

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER ECHELON_GROUPE_XT_INSERT BEFORE INSERT ON ECHELON_GROUPE
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   ECHELON_CLASSE 
              WHERE EchelonId = NEW.EchelonId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout d’un échelon de type groupe : un échelon de type classe existe déjà pour l’attribut EchelonId égal à ', NEW.EchelonId) ;	
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
     END IF ;   
END ;

Contrôle du rattachement d’un échelon à un groupe (update)

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER ECHELON_GROUPE_XT_UPDATE BEFORE UPDATE ON ECHELON_GROUPE
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   ECHELON_CLASSE 
              WHERE EchelonId = NEW.EchelonId
             ) ;
    IF @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’un échelon de type groupe : un échelon de type classe existe déjà pour l’attribut EchelonId égal à ', NEW.EchelonId) ;	
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;   
END ;

Contrôle du rattachement d’un groupe à une classe (insert)

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER GROUPE_CLASSE_XT_INSERT BEFORE INSERT ON GROUPE_CLASSE
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   ECHELON_CLASSE 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout d’un groupe de type classe : un échelon de type classe existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
     END IF ;   
END ;

Contrôle du rattachement d’un groupe à une classe (update)

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER GROUPE_CLASSE_XT_UPDATE BEFORE UPDATE ON GROUPE_CLASSE
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   ECHELON_CLASSE 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’un groupe de type classe : un échelon de type classe existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ; 
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;   
END ;

Quelques inserts

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
INSERT INTO ECHELON (EchelonId, EchelonLibelle) VALUES ('E1', 'Echelon 1') ;
INSERT INTO ECHELON (EchelonId, EchelonLibelle) VALUES ('E2', 'Echelon 2') ;
INSERT INTO ECHELON (EchelonId, EchelonLibelle) VALUES ('E3', 'Echelon 3') ;
INSERT INTO ECHELON (EchelonId, EchelonLibelle) VALUES ('E4', 'Echelon 4') ;
INSERT INTO ECHELON (EchelonId, EchelonLibelle) VALUES ('E5', 'Echelon 5') ;
INSERT INTO ECHELON (EchelonId, EchelonLibelle) VALUES ('E6', 'Echelon 6') ;
INSERT INTO ECHELON (EchelonId, EchelonLibelle) VALUES ('E7', 'Echelon 7') ;
INSERT INTO ECHELON (EchelonId, EchelonLibelle) VALUES ('E8', 'Echelon 8') ;
 
SELECT *, '' AS  '<= ECHELON' FROM ECHELON ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
INSERT INTO CLASSE (ClasseId, ClasseLibelle) VALUES ('CL1', 'Classe 1') ;
INSERT INTO CLASSE (ClasseId, ClasseLibelle) VALUES ('CL2', 'Classe 2') ;
INSERT INTO CLASSE (ClasseId, ClasseLibelle) VALUES ('CL3', 'Classe 3') ;
INSERT INTO CLASSE (ClasseId, ClasseLibelle) VALUES ('CL4', 'Classe 4') ;
 
SELECT *, '' AS  '<= CLASSE' FROM CLASSE ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
INSERT INTO GROUPE (GroupeId, GroupeLibelle) VALUES ('G1', 'Groupe 1') ;
INSERT INTO GROUPE (GroupeId, GroupeLibelle) VALUES ('G2', 'Groupe 2') ;
INSERT INTO GROUPE (GroupeId, GroupeLibelle) VALUES ('G3', 'Groupe 3') ;
INSERT INTO GROUPE (GroupeId, GroupeLibelle) VALUES ('G4', 'Groupe 4') ;
 
SELECT *, '' AS  '<= GROUPE' FROM GROUPE ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO ECHELON_CLASSE (EchelonId, ClasseId) VALUES ('E2', 'CL2') ;
INSERT INTO ECHELON_CLASSE (EchelonId, ClasseId) VALUES ('E3', 'CL2') ;
INSERT INTO ECHELON_CLASSE (EchelonId, ClasseId) VALUES ('E8', 'CL4') ;
 
SELECT *, '' AS  '<= ECHELON_CLASSE' FROM ECHELON_CLASSE ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
INSERT INTO ECHELON_GROUPE (EchelonId, GroupeId) VALUES ('E1', 'G1') ;
INSERT INTO ECHELON_GROUPE (EchelonId, GroupeId) VALUES ('E5', 'G3') ;
INSERT INTO ECHELON_GROUPE (EchelonId, GroupeId) VALUES ('E6', 'G2') ;
INSERT INTO ECHELON_GROUPE (EchelonId, GroupeId) VALUES ('E7', 'G4') ;
 
SELECT *, '' AS  '<= ECHELON_GROUPE' FROM ECHELON_GROUPE ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
INSERT INTO GROUPE_CLASSE (GroupeId, ClasseId) VALUES ('G1', 'CL1') ;
INSERT INTO GROUPE_CLASSE (GroupeId, ClasseId) VALUES ('G3', 'CL3') ;
 
SELECT *, '' AS  '<= GROUPE_CLASSE' FROM GROUPE_CLASSE ;

Tentatives d’infraction

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
INSERT INTO ECHELON_CLASSE (EchelonId, ClasseId) VALUES ('E1', 'CL2') ;  -- E1 est déjà affecté au groupe G1
UPDATE ECHELON_CLASSE SET  EchelonId = 'E7' WHERE  EchelonId = 'E2' ;    -- E7 est déjà affecté au groupe G4
 
INSERT INTO ECHELON_CLASSE (EchelonId, ClasseId) VALUES ('E4', 'CL1') ; -- CL1 contient déjà G1
INSERT INTO ECHELON_CLASSE (EchelonId, ClasseId) VALUES ('E4', 'CL3') ; -- CL3 contient déjà G3
UPDATE ECHELON_CLASSE SET  ClasseId = 'CL1' WHERE  ClasseId = 'CL2' ;   -- CL1 contient déjà G1
 
SELECT *, '' AS  '<= ECHELON_CLASSE' FROM ECHELON_CLASSE ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
INSERT INTO ECHELON_GROUPE (EchelonId, GroupeId) VALUES ('E2', 'G4') ; -- E2 est déjà affecté à la classe CL2
UPDATE ECHELON_GROUPE SET  EchelonId = 'E8' WHERE  EchelonId = 'E1' ; --  E2 est déjà affecté à la classe CL2
 
INSERT INTO GROUPE_CLASSE (GroupeId, ClasseId) VALUES ('G4', 'CL2') ; -- CL2 contient déjà E2
UPDATE GROUPE_CLASSE SET ClasseId = 'CL2' WHERE ClasseId = 'CL3' ;    -- CL2 contient déjà E2
 
SELECT *, '' AS  '<= ECHELON_GROUPE' FROM ECHELON_GROUPE ;

Ce qui vaut pour les échelons vaut évidemment pour les autres classes.


Sommes-nous en phase ? Sinon, pas de problème, je rebelote... ^^

[mossane]

Bonsoir François,

Je reviens vers vous sous peu. Je suis en train de suivre votre tutoriel sur MySQL Workbench.

Merci !

[fsmrel]

Bonsoir Diégane,


Voilà une saine lecture

[mossane]

Bonjour François,

Prenons le cas des échelons. Pour le moment j’en suis aux règles suivantes :

(R1) Un échelon est directement rattaché soit à une classe, soit à un groupe.

(R2) Si un groupe est directement rattaché à une classe, alors aucun échelon ne peut être directement rattaché à cette classe.

Pour des raisons de symétrie, on peut aussi énoncer :

(R3) Si un échelon est directement rattaché à une classe, alors aucun groupe ne peut être directement rattaché à cette classe.

[...]

Ce qui vaut pour les échelons vaut évidemment pour les autres classes.


Sommes-nous en phase ? Sinon, pas de problème, je rebelote... ^^

Oui, nous sommes en phase . Il faudrait juste légèrement modifier la règle (R3) comme suit :Si un échelon est directement rattaché à une classe, alors aucun groupe ne peut être rattaché à cette classe (directement ou indirectement).

Les classes auxquelles des échelons peuvent directement faire référence n'admettent pas de groupe.

Si nous récapitulons, voici où nous en sommes pour le moment.



Grâce à votre excellent tutoriel, j'ai implémenté la structure dérivée (MLD) sur MySQL Workbench.



Je l'ai aussi subdivisée en deux parties :

Gestion des statuts ...



... et gestion de la classification.



Nous sommes bien avancés dans le projet. Je sais que j'ai déjà pris beaucoup de votre temps, mais je compte encore sur vous pour la toute dernière partie : la gestion des actes. Vous vous rappelez ? On l'avait abordé au tout début de la discussion.



D'ailleurs, nous pourrons partir sur la base de cette classification simplifiée. Je compte développez une petite application web pour la gestion des agents des corps standards.

Merci !

[fsmrel]

Bonsoir Diégane,

Il faudrait juste légèrement modifier la règle (R3) comme suit :Si un échelon est directement rattaché à une classe, alors aucun groupe ne peut être rattaché à cette classe (directement ou indirectement).

Supposons que la classe CL2 soit attachée à la fonction F1 et que le groupe G2 soit attaché à F1.

Techniquement (trigger oblige), jusqu’ici, on ne peut pas emprunter le chemin ECHELON -> ECHELON_GROUPE -> GROUPE : la face Nord est bloquée. Par contre, le chemin est libre par la face Sud, dans le sens ECHELON -> ECHELON_CLASSE -> CLASSE -> CLASSE-FONCTION, donc -> FONCTION -> GROUPE_FONCTION -> GROUPE. Autrement dit, on peut créer la paire < E2, CL2> (table ECHELON_CLASSE) donc accéder à la table CLASSE_FONCTION qui contient la paire <CL2, F1> , et ainsi remonter au groupe G2 via la table GROUPE FONCTION qui contient la paire <G2, F1> : indirectement, l’échelon E2 fait référence au groupe G2, ce qui est illégal si j’ai bien compris, et il faut bloquer cette face Sud...

De la même façon, il faut bloquer l’accès à la table CLASSE_CORPS, sinon on pourrait encore remonter vers un groupe, via CORPS -> FONCTION_CORPS -> FONCTION, et à nouveau GROUPE_FONCTION -> GROUPE.

Si c’est bien cela, les triggers ECHELON_CLASSE_XT_INSERT et ECHELON_CLASSE_XT_UPDATE doivent être enrichis :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CREATE TRIGGER ECHELON_CLASSE_XT_INSERT BEFORE INSERT ON ECHELON_CLASSE
FOR EACH ROW 
BEGIN
 
    SET @N = (
              SELECT COUNT(*) 
              FROM   ECHELON_GROUPE 
              WHERE EchelonId = NEW.EchelonId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout  d’un échelon de type classe : un échelon de type groupe existe déjà pour l’attribut EchelonId égal à ', NEW.EchelonId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
 
    SET @N = (
              SELECT COUNT(*)
              FROM   GROUPE_CLASSE
              WHERE  ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout d’un échelon de type classe : un groupe de type classe existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
 
    SET @N = (
              SELECT COUNT(*) 
              FROM   CLASSE_FONCTION 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout  d’un échelon de type classe : une classe de type fonction existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
 
    SET @N = (
              SELECT COUNT(*) 
              FROM   CLASSE_CORPS 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout d’un échelon de type classe : une classe de type corps existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
 
END ;

Pour le trigger ECHELON_CLASSE_XT_UPDATE, seuls les messages sont à changer.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE TRIGGER ECHELON_CLASSE_XT_UPDATE BEFORE UPDATE ON ECHELON_CLASSE
FOR EACH ROW 
BEGIN
 
    SET @N = (
              SELECT COUNT(*) 
              FROM   ECHELON_GROUPE 
              WHERE EchelonId = NEW.EchelonId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’un échelon de type classe : un échelon de type groupe existe déjà pour l’attribut EchelonId égal à ', NEW.EchelonId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
 
    SET @N = (
              SELECT COUNT(*)
              FROM   GROUPE_CLASSE
              WHERE  ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’un échelon de type classe : un groupe de type classe existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;   
 
    SET @N = (
              SELECT COUNT(*) 
              FROM   CLASSE_FONCTION 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’un échelon de type classe : une classe de type fonction existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
 
    SET @N = (
              SELECT COUNT(*) 
              FROM   CLASSE_CORPS 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’un échelon de type classe : une classe de type corps existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;    
END ;

Le chemin inverse doit être bloqué. Par exemple, si la table ECHELON_CLASSE contient la paire <E8, CL4>, pour interdire la présence de la paire <CL4, F1> dans la table CLASSE_FONCTION :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TRIGGER CLASSE_FONCTION_XT_INSERT BEFORE INSERT ON CLASSE_FONCTION
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   CLASSE_CORPS 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout d’une classe de type fonction : une classe de type corps existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;  
 
    SET @N = (
              SELECT COUNT(*) 
              FROM ECHELON_CLASSE 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’ajout d’une classe de type fonction : un échelon de type classe existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ;
END ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TRIGGER CLASSE_FONCTION_XT_UPDATE BEFORE UPDATE ON CLASSE_FONCTION
FOR EACH ROW 
BEGIN
    SET @N = (
              SELECT COUNT(*) 
              FROM   CLASSE_CORPS 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’une classe de type fonction : une classe de type corps existe déjà pour l’attribut EchelonId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
    END IF ; 
 
    SET @N = (
              SELECT COUNT(*) 
              FROM ECHELON_CLASSE 
              WHERE ClasseId = NEW.ClasseId
             ) ;
    IF  @N > 0 THEN
        SET @T = CONCAT ('Echec d’update d’une classe de type fonction : un échelon de type classe existe déjà pour l’attribut ClasseId égal à ', NEW.ClasseId) ;
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
     END IF ;
END ;

Même principe pour les tables CLASSE_CORPS, FONCTION_CORPS, FONCTION_COLLEGE...

Me suis-je fourvoyé ? Sommes-nous encore en phase ?

Pour la suite, je regarderai demain...


J’espère que le tutoriel ne vous a pas donné la migraine ^^

[mossane]

Bonjour François,

Me suis-je fourvoyé ? Sommes-nous encore en phase ?

Nous sommes encore en phase sur certains points.

Supposons que la classe CL2 soit attachée à la fonction F1 et que le groupe G2 soit attaché à F1.

La règle générale c'est qu'un élément donné de la classification ne peut pas contenir des entités de types différents.

Détaillons les différentes règles de gestion.

ECHELON

(R1) Un échelon est directement rattaché soit à une classe, soit à un groupe.

(R2) Si un groupe est directement rattaché à une classe, alors aucun échelon ne peut être directement rattaché à cette classe.

NB: un groupe peut regrouper des échelons et non l'inverse

.

(R3) Si un échelon est directement rattaché à une classe, alors aucun groupe ne peut être rattaché à cette classe (directement ou indirectement).

NB : Une classe à laquelle un échelon peut directement faire référence n'admet pas de groupes.

GROUPE

(R4) Un groupe est directement rattaché soit à une classe, soit à une fonction.

(R5) Si une classe est directement rattachée à une fonction, alors aucun groupe ne peut être directement rattaché à cette fonction.

NB : une classe peut contenir des groupes et non l'inverse.

(R6) Si un groupe est directement rattaché à une fonction, alors aucune classe ne peut être rattachée à cette fonction (directement ou indirectement).

NB : une fonction a laquelle un groupe peut directement faire référence n'admet pas de classes.

CLASSE

(R7) Une classe est directement rattachée soit à une fonction, soit à un corps.

(R8) Si une fonction est directement rattachée à un corps, alors aucune classe ne peut être directement rattachée à ce corps.

NB : une fonction peut contenir des classes et non l'inverse.

(R9) Si une classe est directement rattachée à un corps, alors aucune fonction ne peut être rattachée à ce corps (directement ou indirectement).

NB : un corps auquel une classe peut directement faire référence n'admet pas de fonctions.

FONCTION

(R10)Une fonction est directement rattachée soit à un collège, soit à un corps.

(R11)Si un collège est directement à un corps, alors aucune fonction ne peut être directement rattachée à ce corps

NB : un collège peut contenir des fonctions et non l'inverse.

(R12)Si une fonction est directement rattaché à un corps, alors aucun collège ne peut être rattaché à ce corps (directement ou indirectement).

NB : un corps auquel une fonction peut directement faire référence n'admet pas de collège.

Voici les différents triggers que j'en ai déduit. J'ai travaillé avec MySQL Workbench.

ECHELON_CLASSE

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER `fichier`.`ECHELON_CLASSE_BEFORE_INSERT` BEFORE INSERT ON `ECHELON_CLASSE` FOR EACH ROW
BEGIN
	SET @N = (
		SELECT COUNT(*)
                FROM ECHELON_GROUPE
                WHERE EchelonId = NEW.EchelonId
			 );
	IF @N  > 0 THEN
		SET @T = CONCAT('Echec d''ajout d''un échelon de type classe : un échelon de type groupe existe déjà pour l''attribut EchelonId égal à ', NEW.EchelonId);
                SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
	ELSE
		SET @N = (
		    SELECT COUNT(*)
                    FROM GROUPE_CLASSE
                    WHERE ClasseId = NEW.ClasseId
				 );
		IF @N  > 0 THEN
			SET @T = CONCAT('Echec d''ajout d''un échelon à une classe : un groupe existe déjà pour l''attribut ClasseId égal à ', NEW.ClasseId);
                        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
		END IF;
	END IF;
END;

ECHELON_GROUPE

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TRIGGER `fichier`.`ECHELON_GROUPE_BEFORE_INSERT` BEFORE INSERT ON `ECHELON_GROUPE` FOR EACH ROW
BEGIN
	SET @N = (
		SELECT COUNT(*)
                FROM ECHELON_CLASSE
                WHERE EchelonId = NEW.EchelonId
			 );
	IF @N  > 0 THEN
	SET @T = CONCAT('Echec d''ajout d''un échelon de type groupe : un échelon de type classe existe déjà pour l''attribut EchelonId égal à ', NEW.EchelonId);
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
    END IF;
END;

GROUPE_CLASSE

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER `fichier`.`GROUPE_CLASSE_BEFORE_INSERT` BEFORE INSERT ON `GROUPE_CLASSE` FOR EACH ROW
BEGIN
	SET @N = (
		SELECT COUNT(*)
                FROM GROUPE_FONCTION
                WHERE GroupeId = NEW.GroupeId
			 );
	IF @N  > 0 THEN
		SET @T = CONCAT('Echec d''ajout d''un groupe de type classe : un groupe de type fonction existe déjà pour l''attribut GroupeId égal à ', NEW.GroupeId);
                SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
	ELSE
		SET @N = (
		    SELECT COUNT(*)
                    FROM ECHELON_CLASSE
                    WHERE ClasseId = NEW.ClasseId
				 );
		IF @N  > 0 THEN
			SET @T = CONCAT('Echec d''ajout d''un groupe à une classe : un échelon existe déjà pour l''attribut ClasseId égal à ', NEW.ClasseId);
                        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
		END IF;
	END IF;
END;

GROUPE_FONCTION

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER `fichier`.`GROUPE_FONCTION_BEFORE_INSERT` BEFORE INSERT ON `GROUPE_FONCTION` FOR EACH ROW
BEGIN
	SET @N = (
		SELECT COUNT(*)
                FROM GROUPE_CLASSE
                WHERE GroupeId = NEW.GroupeId
			 );
	IF @N  > 0 THEN
		SET @T = CONCAT('Echec d''ajout d''un groupe de type fonction : un groupe de type classe existe déjà pour l''attribut GroupeId égal à ', NEW.GroupeId);
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
	ELSE
		SET @N = (
		    SELECT COUNT(*)
                    FROM CLASSE_FONCTION
                    WHERE FonctionId
				 );
		IF @N  > 0 THEN
        SET @T = CONCAT('Echec d''ajout d''un groupe à une fonction : une classe existe déjà pour l''attribut FonctionId égal à ', NEW.FonctionId);
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
        END IF;
	END IF;
END;

CLASSE_FONCTION

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER `fichier`.`CLASSE_FONCTION_BEFORE_INSERT` BEFORE INSERT ON `CLASSE_FONCTION` FOR EACH ROW
BEGIN
	SET @N = (
		SELECT COUNT(*)
                FROM CLASSE_CORPS
                WHERE ClasseId = NEW.ClasseId
			 );
	IF @N > 0 THEN
		SET @T = CONCAT('Echec d''ajout d''une classe de type fonction : une classe de type corps existe déjà pour l''attribut ClasseId égal à ', NEW.ClasseId);
                SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
	ELSE
		SET @N = (
		    SELECT COUNT(*)
                    FROM GROUPE_FONCTION
                    WHERE FonctionId = NEW.FonctionId
				 );
		IF @N > 0 THEN
			SET @T = CONCAT('Echec d''ajout d''une classe : un groupe existe déjà pour l''attribut FonctionId égal à : ', NEW.FonctionId);
			SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
        END IF;            
	END IF;
END;

CLASSE_CORPS

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TRIGGER `fichier`.`CLASSE_CORPS_BEFORE_INSERT` BEFORE INSERT ON `CLASSE_CORPS` FOR EACH ROW
BEGIN
	SET @N = (
		SELECT COUNT(*)
                FROM CLASSE_FONCTION
                WHERE ClasseId = NEW.ClasseId
			 );
	IF @N > 0 THEN
		SET @T = CONCAT('Echec d''ajout d''une classe de type corps : une classe de type fonction existe déjà pour l''attribut ClasseId égal à ',NEW.ClasseId);
                SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T ;
	ELSE
		SET @N = (
					SELECT COUNT(*)
                    FROM FONCTION_CORPS
                    WHERE CorpsId = NEW.CorpsId
				 );
		IF @N  > 0 THEN
			SET @T = CONCAT('Echec d''ajout d''une classe à un corps : une fonction existe déjà pour l''attribut CorpsId égal à ', NEW.CorpsId);
                        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
		ELSE
			SET @N = (
						SELECT COUNT(*)
                        FROM COLLEGE
                        WHERE CorpsId = NEW.CorpsId
					 );
			IF @N  > 0 THEN
				SET @T = CONCAT('Echec d''ajout d''une classe à un corps : un collège existe déjà pour l''attribut CorpsId égal à ', NEW.CorpsId);
                                SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
			END IF;
		END IF;
	END IF;
END;

FONCTION_CORPS

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TRIGGER `fichier`.`FONCTION_CORPS_BEFORE_INSERT` BEFORE INSERT ON `FONCTION_CORPS` FOR EACH ROW
BEGIN
	SET @N = (
		SELECT COUNT(*)
                FROM  FONCTION_COLLEGE
                WHERE FonctionId = NEW.FonctionId
			 );
	IF @N  > 0 THEN
		SET @T = CONCAT('Echec d''ajout d''une fonction de type corps : une fonction de type collège existe déjà pour l''attribut FonctionId égal à ', NEW.FonctionId);
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
	ELSE
		SET @N = (
		    SELECT COUNT(*)
                    FROM CLASSE_CORPS
                    WHERE CorpsId = NEW.CorpsId
				 );
		IF @N  > 0 THEN
			SET @T = CONCAT('Echec d''ajout d''une fonction à un corps : une classe existe déjà pour l''attribut CorpsId égal à ', NEW.CorpsId);
                        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
		ELSE
			SET @N = (
			SELECT COUNT(*)
                        FROM COLLEGE
                        WHERE CorpsId = NEW.CorpsId
					 );
			IF @N  > 0 THEN
				SET @N = CONCAT('Echec d''ajout d''une fonction à un corps : un collège existe déjà pour l''attribut CorpsId égal à ', NEW.CorpsId);
                                SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
			END IF;
		END IF;
	END IF;
END;

FONCTION_COLLEGE

COLLEGE

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER `fichier`.`COLLEGE_BEFORE_INSERT` BEFORE INSERT ON `COLLEGE` FOR EACH ROW
BEGIN
	SET @N = (
		SELECT COUNT(*)
                FROM FONCTION_CORPS
                WHERE CorpsId = NEW.CorpsId
			 );
	IF @N  > 0 THEN
		SET @T = CONCAT('Echec d''ajout d''un collège à un corps : une fonction existe déjà pour l''attribut CorpsId égal à ', NEW.CorpsId);
                SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
	ELSE 
		SET @N = (
		        SELECT COUNT(*)
                        FROM CLASSE_CORPS
                        WHERE CorpsId = NEW.CorpsId
				 );
		IF @N  > 0 THEN
			SET @T = CONCAT('Echec d''ajout d''un collège à un corps : une classe existe déjà pour l''attribut CorpsId égal à ', NEW.CorpsId);
                        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
		END IF;
	END IF;
END;

J’espère que le tutoriel ne vous a pas donné la migraine ^^

Le tutoriel est très bien rédigé. Je me suis bien retrouvé la plupart du temps.

Je suis sénégalais. Mon français fait souvent défaut. C'est ce qui fait que mes explications ne sont pas toujours claires.

Merci !

[fsmrel]

Bonsoir Diégane,


Les triggers remplissent leur mission : j’ai essayé de contourner les règles, peine perdue.

Concernant le trigger FONCTION_CORPS_BEFORE_INSERT : il y a un « Set @N » à remplacer par « Set @T ».


J'ai noté une bijection suspecte dans votre diagramme... :




Je n’ai pas tout revu des diagrammes, car il y a de la matière. En tout cas, vous êtes devenu en un rien de temps un virtuose de MySQL Workbench

J’ai tout oublié de la gestion des actes, il faudra que je relise calmement ce qui concerne cette partie.

Vous comme moi ne saurions prétendre avoir le talent d’un Léopold Sédar Senghor (sinon nous serions à l’Académie ^^), mais il n’empêche que votre français est impeccable. Certes, accoucher des règles de gestion est bien difficile, aussi le niveau de détail auquel vous êtes descendu était loin d’être inutile...


Encore bon courage pour la suite !

[fsmrel]

Bonsoir Diégane,


Quid de la gestion des actes ? C'est du lourd ?

[mossane]

Bonjour François,

Quid de la gestion des actes ? C'est du lourd ?

J'en étais encore à la définition de certaines règles de gestion. Je reviens vers vous dès que j'aurai terminé la réalisation d'une première esquisse du diagramme.

Encore une fois, merci.

[mossane]

Bonjour François,

J'ai noté une bijection suspecte dans votre diagramme... :

Au départ, on avait cette représentation.



La relation entre CORPS et CORPS_TYPE est du type un-à-plusieurs. Afin d'éviter qu'un corps fasse référence à texte_corps_type qui détermine un cadre dont il (corps) ne fait pas partie, on est passé à la représentation suivante.



C'est pour cette raison que j'ai mis une relation de type un-à-un entre CORPS et CORPS_TYPE_CORPS.

Une petite description de la toute dernière partie du projet.

Les différents évènements (recrutement, avancement, démission, retraite, etc.) qui surviennent dans la carrière d’un agent sont officialisés par des actes. Un agent peut donc bénéficier de plusieurs actes, tout comme, un acte peut concerner plusieurs agents.

L’acte est un document définitif. Au départ, ce n’était qu’un simple projet qui a suivi un circuit de validation en transitant par plusieurs structures. Chacune d’elles l’envoyant à la structure suivante après l’avoir traité. Un projet a un numéro et une date. Il a également un objet (démission, avancement, etc.) et est d’un type donné (décret, arrêté). Un type de projet peut fédérer plusieurs objets. Suivant leurs objets, des projets peuvent emprunter des circuits différents. Un circuit est constitué d’un ensemble de structures dont le visa est requis pour la validation du projet.

Dans chaque structure, on souhaite conserver la date d’entrée du projet et éventuellement le numéro interne que la structure lui a attribué. Au sein d’une structure, un projet peut revêtir plusieurs états (visé, repris, rejeté, etc.). Dans une structure et pour chaque état, on souhaite conserver la date (de l’état) et éventuellement l’objet (de l’état toujours), notamment en cas de rejet ou de reprise.

Une fois que le projet aura parcouru avec succès le circuit de validation, un acte est généré. L’acte est du même type et a le même objet que le projet dont il est issu.

Je considère qu’un agent est quelqu’un qui a bénéficié au moins d’un acte. Pour prendre en compte les postulants qui sont en projet et qui n’ont pas encore bénéficié d’acte, j’ai fait hériter la classe AGENT de la classe POSTULANT. J’ai également fait hériter la classe EX_AGENT de la classe AGENT pour séparer les retraités, démissionnaires et autres ex agents des agents en activité tout en conservant leurs traces.

Voici la première esquisse que j'ai tracée.



Merci !

[fsmrel]

Bonsoir Diégane,

C'est pour cette raison que j'ai mis une relation de type un-à-un entre CORPS et CORPS_TYPE_CORPS.

Je n’avais pas vu la bijection à l’époque, mais si elle est pertinente, cela veut dire qu’il faut fondre les classes CORPS et CORPS_TYPE_CORPS (au moins au niveau SQL) et l’on revient à la case départ, ce qui me chiffonne...



A propos des actes :

Félicitations pour la clarté de la présentation du sujet, un modèle du genre qui pourrait être montré en exemple chez DVP...


Questions et remarques

Pourquoi la classe TYPE est-elle en relation avec la classe OBJET ?

Certains attributs sont présents à la fois dans la classe AGENT et dans la classe POSTULANT (nom, prénom...)

Un projet n’aboutit pas forcément à un acte, donc la cardinalité 0,1 côté ACTE est justifiée, mais si un acte suit d’un projet ayant forcément abouti, la cardinalité côté PROJET devrait être 1,1 (à moins qu’il n’y ait de la génération spontanée d’actes, en dehors des projets...)

Je vais regarder de plus près la partie CIRCUIT, STRUCTURE, ETAT, car il y a des contraintes de chemin en perspective à mettre en œuvre : par exemple, un projet p1 fait référence à un circuit c1, lequel peut faire référence aux structures s1 et s2, tandis que par la relation entre PROJET et STRUCTURE, p1 peut faire référence à d’autres structures, par exemple s3 et s4.

De même, via la classe PROJET_ETAT_STRUCTURE, ce même projet p1 peut être associé aux structures s5 et s6... Par ailleurs, pour un projet et une structure donnés, peut-on avoir plus d’un état ?


Tenons le coup !

[mossane]

Bonsoir François,

Je n’avais pas vu la bijection à l’époque, mais si elle est pertinente, cela veut dire qu’il faut fondre les classes CORPS et CORPS_TYPE_CORPS (au moins au niveau SQL) et l’on revient à la case départ, ce qui me chiffonne...

Je pense que la représentation dérivée suivante serait plus appropriée.

Pourquoi la classe TYPE est-elle en relation avec la classe OBJET ?

Il existe plusieurs types de projets (décret, arrêté, décision, etc.). Pour un type donné, il peut exister plusieurs objets (nomination, titularisation, etc.). Un projet est d'un seul type et a l'un des objets associés à ce type.

Certains attributs sont présents à la fois dans la classe AGENT et dans la classe POSTULANT (nom, prénom...)

Oui ! Il y a des doublons. Un agent étant un postulant, il n'est pas nécessaire de reprendre dans AGENT les attributs déjà présents dans POSTULANT.

Un projet n’aboutit pas forcément à un acte, donc la cardinalité 0,1 côté ACTE est justifiée, mais si un acte suit d’un projet ayant forcément abouti, la cardinalité côté PROJET devrait être 1,1 (à moins qu’il n’y ait de la génération spontanée d’actes, en dehors des projets...)

C'est pour prendre en compte l'existant que j'ai mis retenu la cardinalité 0,1. Dans les archives, on pourrait se retrouver avec des actes dont on n'a plus les références des projets dont ils sont issus.

Par ailleurs, pour un projet et une structure donnés, peut-on avoir plus d’un état ?

Oui ! Pour un projet et une structure donnés, on peut avoir plus d'un état. Certes, un projet n'est visé qu'une seule fois dans une structure, mais il peut être visé après avoir été repris ou rejeté. Une structure peut également rejeter ou reprendre plusieurs fois le même projet pour divers raisons. En effet, un rejet peut ne pas être définitif. S'il émane d'un manquement d'une structure n-1 par exemple, la structure n peut rejeter le projet et le retourner à la structure n-1 pour correction.

Tenons le coup !

Merci !

[fsmrel]

Bonsoir Diégane,

Je pense que la représentation dérivée suivante serait plus appropriée.

Pourquoi pas, mais la stuation suivante est alors possible :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
TEXTE_CORPS_TYPE_CORPS (CorpsId    TexteId)             TEXTE_CORPS_TYPE (TexteId    CorpsTypeId)
                        -------    -------                                -------    -----------
                        co1        t1                                     t1         ct1


CORPS (CorpsId    CorpsTypeId)                          CORPS_TYPE (CorpsTypeId)
       -------    -----------                                       -----------
       co1        ct2                                               ct1
                                                                    ct2 

C'est-à-dire qu’en empruntant le chemin TEXTE_CORPS_TYPE_CORPS > TEXTE_CORPS_TYPE, on atteint le type de corps ct1, tandis qu’en empruntant le chemin TEXTE_CORPS_TYPE_CORPS > CORPS, on atteint le type de corps ct2.

Si la règle veut que, quel que soit le chemin emprunté, on atteigne le même type de corps, alors il faudra prévoir les triggers traduisant en SQL cette contrainte de chemin (ici en relationnel pur) :

(TEXTE_CORPS_TYPE_CORPS JOIN TEXTE_CORPS_TYPE) {CorpsTypeId} = (TEXTE_CORPS_TYPE_CORPS JOIN CORPS) {CorpsTypeId}

Je traduis : la projection sur CorpsTypeId de la jointure des variables relationnelles TEXTE_CORPS_TYPE_CORPS et TEXTE_CORPS_TYPE doit être égale à la projection sur CorpsTypeId de la jointure des variables relationnelles TEXTE_CORPS_TYPE_CORPS et CORPS.

Il existe plusieurs types de projets (décret, arrêté, décision, etc.). Pour un type donné, il peut exister plusieurs objets (nomination, titularisation, etc.). Un projet est d'un seul type et a l'un des objets associés à ce type.

Selon le DC, un projet détermine un objet, un objet détermine un type, donc par transitivité un projet détermine un type. Autrement dit, un projet « hérite » du type de son objet. Si c’est bien ainsi que vous voyez la chose, pas de problème.

C'est pour prendre en compte l'existant que j'ai mis retenu la cardinalité 0,1. Dans les archives, on pourrait se retrouver avec des actes dont on n'a plus les références des projets dont ils sont issus.

Pour éviter des anomalies concernant les dossiers « récents », vous pourriez peut-être spécialiser ACTE et forcer les récents à faire référence à un projet. A vous de juger.

Oui ! Pour un projet et une structure donnés, on peut avoir plus d'un état.

D’accord.

A propos des contraintes de chemin :

A moins que fonctionnellement ça ne soit pas possible, en candide je verrais volontiers la modélisation suivante :





A suivre...

[mossane]

Bonjour François,

Je n'ai pas eu l'occasion de travailler sur le projet ces derniers temps.

Si la règle veut que, quel que soit le chemin emprunté, on atteigne le même type de corps, alors il faudra prévoir les triggers traduisant en SQL cette contrainte de chemin (ici en relationnel pur) :

(TEXTE_CORPS_TYPE_CORPS JOIN TEXTE_CORPS_TYPE) {CorpsTypeId} = (TEXTE_CORPS_TYPE_CORPS JOIN CORPS) {CorpsTypeId}

C'est bien ce que veut la règle. Aussi, je propose les deux triggers suivants :

TEXTE_CORPS_TYPE_CORPS : ajout d'une référence d'un corps à un texte-corps-type

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
CREATE TRIGGER TEXTE_CORPS_TYPE_CORPS_BEFORE_INSERT BEFORE INSERT ON TEXTE_CORPS_TYPE_CORPS FOR EACH ROW
BEGIN
	SET @N1 = (
				SELECT CORPS.CorpsTypeId 
                                FROM  TEXTE_CORPS_TYPE_CORPS INNER JOIN CORPS 
                                ON TEXTE_CORPS_TYPE_CORPS.CorpsId = CORPS.CorpsId 
                                WHERE CORPS.CorpsId = NEW.CorpsId
			);
 
	SET @N2 = (
				SELECT TEXTE_CORPS_TYPE.CorpsTypeId
                                FROM TEXTE_CORPS_TYPE_CORPS INNER JOIN TEXTE_CORPS_TYPE
                                ON TEXTE_CORPS_TYPE_CORPS.TexteId = TEXTE_CORPS_TYPE.TexteId
                                WHERE TEXTE_CORPS_TYPE.TexteId = NEW.TexteId
			);
	IF @N1 <> @N2 THEN
		SET @T = CONCAT('Echec d''ajout d''une référence d''un corps à un texte-corps-type : le corps  ', 
                              NEW.CorpsId, ' fait référence au texte ', 
                              NEW.TexteId, ' qui détermine un cadre dont il ne fait pas partie');
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
	END IF;
END;

TEXTE_CORPS_TYPE_CORPS : modification d'une référence d'un corps à un texte-corps-type

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
CREATE TRIGGER TEXTE_CORPS_TYPE_CORPS_BEFORE_UPDATE BEFORE UPDATE ON TEXTE_CORPS_TYPE_CORPS FOR EACH ROW
BEGIN
	SET @N1 = (
				SELECT CORPS.CorpsTypeId 
                                FROM  TEXTE_CORPS_TYPE_CORPS INNER JOIN CORPS 
                                ON TEXTE_CORPS_TYPE_CORPS.CorpsId = CORPS.CorpsId 
                                WHERE CORPS.CorpsId = NEW.CorpsId
			);
 
	SET @N2 = (
				SELECT TEXTE_CORPS_TYPE.CorpsTypeId
                                FROM TEXTE_CORPS_TYPE_CORPS INNER JOIN TEXTE_CORPS_TYPE
                                ON TEXTE_CORPS_TYPE_CORPS.TexteId = TEXTE_CORPS_TYPE.TexteId
                                WHERE TEXTE_CORPS_TYPE.TexteId = NEW.TexteId
			);
	IF @N1 <> @N2 THEN
		SET @T = CONCAT('Echec de mise à jour d''une référence d''un corps à un texte-corps-type : le corps  ', 
                              NEW.CorpsId, ' fait référence au texte ', 
                              NEW.TexteId, ' qui détermine un cadre dont il ne fait pas partie');
        SIGNAL   SQLSTATE '45001' SET MESSAGE_TEXT = @T;
	END IF;
END;

Selon le DC, un projet détermine un objet, un objet détermine un type, donc par transitivité un projet détermine un type. Autrement dit, un projet « hérite » du type de son objet. Si c’est bien ainsi que vous voyez la chose, pas de problème.

C'est bien cela.

Pour éviter des anomalies concernant les dossiers « récents », vous pourriez peut-être spécialiser ACTE et forcer les récents à faire référence à un projet. A vous de juger.

Je n'y avais pas pensé, mais ça me paraît être une bonne solution.

A propos des contraintes de chemin :

A moins que fonctionnellement ça ne soit pas possible, en candide je verrais volontiers la modélisation suivante :

J'ai une petite préoccupation par rapport à ce modèle. Voici sa structure dérivée.



Peut-être que je me trompe, mais j'ai l'impression que dans une structure, un projet ne pourra pas revêtir un même état plus d'une fois. Du fait de la clé primaire constituée du triplet (ProjetId, StructureId, EtatId) dans PROJET_ETAT_STRUCTURE, dans une structure s1, un projet p1 ne pourra pas être repris (état e1) plusieurs fois par exemple.

Est-ce bien le cas ?

Merci !