Discussion :

[fsmrel]

Histoire de faire le point, je déterre un sujet que nous avions abordé il y a 3 ans et qui concerne les contraintes d’inclusion.

Looping permet de modéliser ces contraintes dans les MCD. J’avais pris comme exemple la contrainte proposée par le groupe 135 de l’afcet, contrainte selon laquelle un enseignant ne peut enseigner une matière à une classe que s’il sait enseigner cette matière :


Version Looping :




Las ! Le problème demeure quant à la production du code SQL : la contrainte est ignorée.

Comme dit Paprick, « la traduction correcte et automatique de tous les cas de contraintes d'inclusion, et autres contraintes inter-associations, paraît complexe... »

Certes. Néanmoins, dans le cas qui nous concerne, lors de la génération SQL, pour la table ENSEIGNE, il suffit de ne pas faire participer les « pivots » ENSEIGNANT et MATIERE, car les attributs enseignantId et matiereId sont hérités de la table SAIT_ENSEIGNER. Plus généralement, oser court-circuiter les pivots.

Merci Paprick de me proposer des contre-exemples.

J’ai évidemment dans ma besace une solution qui fera hurler les merisiens purs et durs, mais qui fournit le code SQL sans devoir en passer par l’outil Règle :



Code SQL proposé par Looping :

CREATE TABLE ENSEIGNANT(
   enseignantId INT,
   CONSTRAINT ENSEIGNANT_PK PRIMARY KEY(enseignantId)
);

CREATE TABLE MATIERE(
   matiereId INT,
   CONSTRAINT MATIERE_PK PRIMARY KEY(matiereId)
);

CREATE TABLE CLASSE(
   classeId INT,
   CONSTRAINT CLASSE_PK PRIMARY KEY(classeId)
);

CREATE TABLE SAIT_ENSEIGNER(
   enseignantId INT,
   matiereId INT,
   CONSTRAINT SAIT_ENSEIGNER_PK PRIMARY KEY(enseignantId, matiereId),
   CONSTRAINT SAIT_ENSEIGNER_ENSEIGNANT_FK FOREIGN KEY(enseignantId) REFERENCES ENSEIGNANT(enseignantId),
   CONSTRAINT SAIT_ENSEIGNER_MATIERE_FK FOREIGN KEY(matiereId) REFERENCES MATIERE(matiereId)
);

CREATE TABLE ENSEIGNER(
   enseignantId INT,
   matiereId INT,
   classeId INT,
   CONSTRAINT ENSEIGNER_PK PRIMARY KEY(enseignantId, matiereId, classeId),
   CONSTRAINT ENSEIGNER_SAIT_ENSEIGNER_FK FOREIGN KEY(enseignantId, matiereId) REFERENCES SAIT_ENSEIGNER(enseignantId, matiereId),
   CONSTRAINT ENSEIGNER_CLASSE_FK FOREIGN KEY(classeId) REFERENCES CLASSE(classeId)
);

Affaire à suivre...

[escartefigue]

Voici un modèle assez peu intuitif à cause des acteurs fictifs qui n'en facilitent pas la compréhension ni le parallèle avec les règles de gestion

Avoir à coder un peu de DDL pour les quelques contraintes me semble préférable à cette construction, dont résulte un grand nombre de tables
Il conviendra de consolider tout ça dans des vues pour que ce soit un peu plus accessible au béotien

Cela étant dit François, je suis heureux de voir que tu as retrouvé toute ta sagacité !

[Paprick]

Bonsoir François,

Nous revoilà sur un débat que l'on connait bien !
Tout d'abord, ta proposition de modèle pourrait avoir une présentation un peu simple et lisible ; en effet il est inutile de décomposer l'association "Enseigner" :

Concernant le codage de la contrainte d'inclusion, il est vrai que, dans le cas spécifique que tu proposes, le codage semble naturel et systématique.
Comme tu le sais, ce qui me gêne le plus, c'est proposer la prise en compte de ce cas, et ne rien faire pour les autres contraintes inter-associatives : l'utilisateur pourrait ne pas s'y retrouver...
Une solution qui pourrait être cohérente serait que Looping propose automatiquement, dans la définition de la contrainte, le code SQL correspondant à cette contrainte d'inclusion avec un ALTER TABLE, laissant ensuite l'utlisateur libre d'en faire ce qu'il veut.
Qu'en penses-tu ? Peux-tu me formaliser ce code en fonction des configurations conceptuelles (pivots, ...) ?

[fsmrel]

Merci Capitaine de tes encouragements. La reprise n’est pas aisée, mais je me remets doucement et progressivement à l’entraînement...

Voici un modèle assez peu intuitif à cause des acteurs fictifs qui n'en facilitent pas la compréhension.

Bien entendu, et j'avais prévenu comme quoi la sémantique était malmenée :

J’ai évidemment dans ma besace une solution qui fera hurler les merisiens purs et durs, mais qui fournit le code SQL sans devoir en passer par l’outil Règle.

Considère cela seulement comme un exercice pour quelqu’un qui sait plutôt produire des MLD en cinquième forme normale sans pour autant connaître SQL.

Voilà un autre exercice, plus compliqué, dans lequel les fameux pivots n'interviennent pas. C'est la reprise de l’exemple b) fourni dans le document afcet de 1990, page 46 :

Je sens qu’il y a dans l’air de la règle pure et dure, voire plus que ça...

[fsmrel]

Salve Paprick !

Comme tu le sais, ce qui me gêne le plus, c'est proposer la prise en compte de ce cas, et ne rien faire pour les autres contraintes inter-associatives : l'utilisateur pourrait ne pas s'y retrouver...

C’est vrai. Il y aurait là un côté frustrant : pourquoi cette contrainte et pas les autres...

Une solution qui pourrait être cohérente serait que Looping propose automatiquement, dans la définition de la contrainte, le code SQL correspondant à cette contrainte d'inclusion avec un ALTER TABLE, laissant ensuite l'utilisateur libre d'en faire ce qu'il veut.

Ça me fait revenir en mémoire les triggers de Nanci, aïe ! aïe !...

Peux-tu me formaliser ce code en fonction des configurations conceptuelles (pivots, ...) ?

Après un an sans modéliser ni coder, je me sens rouillé et essaie donc de reprendre l’entraînement et remonter la pente. Je regarderai tout ça, calmement, mais la barre est bien haute, on grimpe vers le niveau méta, peut-être est-ce trop ? En effet, quand je vois comment des pointures comme Dominique Nanci (RIP) et ses collègues se sont pris les pieds dans le tapis avec leurs triggers (cf.
Ingénierie des systèmes d’information] : Merise deuxième génération, 4°édition (2001) , je ne promets rien, d’autant plus que je suis encore convalescent, et donc pas en pleine possession de mes moyens...

[fsmrel]

Ave !

Paprick, j’ai regardé ce qu’on pouvait faire avec la contrainte d’inclusion selon laquelle Raoul ne peut conduire un char Patton que s’il a le permis correspondant.

Partons du MCD :



Et voyons voir la traduction en SQL, sans tenir compte de la contrainte :

CREATE TABLE PERSONNE 
(
    PersonneId INT NOT NULL
  , PersonneNom VARCHAR(32) NOT NULL
  , PRIMARY KEY (PersonneId)
);

CREATE TABLE CATEGORIE 
(
    CategorieId INT NOT NULL
  , CategorieNom VARCHAR(32) NOT NULL
  , PRIMARY KEY (CategorieId)
  , UNIQUE (CategorieNom)
);

CREATE TABLE PSN_CAT 
(
    PersonneId INT NOT NULL
  , CategorieId INT NOT NULL
  , PermisDate DATE NOT NULL
  , PRIMARY KEY (PersonneId, CategorieId)
  , CONSTRAINT PSN_CAT_PSN_FK
      FOREIGN KEY (PersonneId)
      REFERENCES PERSONNE (PersonneId)
      ON DELETE CASCADE
  , CONSTRAINT PSN_CAT_CAT_FK
      FOREIGN KEY (CategorieId)
      REFERENCES CATEGORIE (CategorieId)
      ON DELETE NO ACTION
);

CREATE TABLE VEHICULE 
(
  VehiculeId INT NOT NULL,
  CategorieId INT NOT NULL,
  VehiculeImmat VARCHAR(12) NOT NULL,
  PRIMARY KEY (VehiculeId),
  UNIQUE (VehiculeImmat),
  UNIQUE (VehiculeId, CategorieId),   -- pour inclusion
  CONSTRAINT VEH_CAT_FK
    FOREIGN KEY (CategorieId)
    REFERENCES CATEGORIE (CategorieId)
    ON DELETE NO ACTION
);

CREATE TABLE CONDUIRE 
(
    PersonneId INT NOT NULL
  , VehiculeId INT NOT NULL
  , PRIMARY KEY (PersonneId, VehiculeId)
  , CONSTRAINT CON_PSN_FK
      FOREIGN KEY (PersonneId)
      REFERENCES PERSONNE (PersonneId)
      ON DELETE CASCADE
  , CONSTRAINT CON_VEH_FK
      FOREIGN KEY (VehiculeId)
      REFERENCES VEHICULE (VehiculeId)
      ON DELETE CASCADE 
);

Créons quelques lignes dans les tables :

INSERT INTO PERSONNE VALUES (1, 'Fernand') ;
INSERT INTO PERSONNE VALUES (2, 'Raoul') ;
INSERT INTO PERSONNE VALUES (3, 'Paul') ;

SELECT * FROM PERSONNE ;

PersonneId    PersonneNom
1             Fernand
2             Raoul
3             Paul

INSERT INTO CATEGORIE VALUES (1, 'catégorie VL') ;
INSERT INTO CATEGORIE VALUES (2, 'catégorie PL') ;
INSERT INTO CATEGORIE VALUES (3, 'char Patton') ;

SELECT * FROM CATEGORIE ;

CategorieId   CategorieNom
1             catégorie VL
2             catégorie PL
3             char Patton

INSERT INTO PSN_CAT VALUES (1, 1, '1950-01-04') ;
INSERT INTO PSN_CAT VALUES (1, 2, '1960-01-01') ;
INSERT INTO PSN_CAT VALUES (1, 3, '1952-12-21') ;
INSERT INTO PSN_CAT VALUES (2, 1, '1950-01-05') ;
INSERT INTO PSN_CAT VALUES (2, 2, '1950-01-05') ; 

SELECT * FROM PSN_CAT ;

PersonneId   CategorieId   PermisDate
1            1             1950-01-04
1            2             1960-01-01
1            3             1952-12-21
2            1             1950-01-05
2            2             1950-01-05

INSERT INTO VEHICULE VALUES (1, 1, '1A75') ;
INSERT INTO VEHICULE VALUES (2, 1, '1Z44') ;
INSERT INTO VEHICULE VALUES (3, 2, '1H91') ;
INSERT INTO VEHICULE VALUES (4, 3, '9G38') ;

SELECT * FROM VEHICULE ; 

VehiculeId   CategorieId   VehiculeImmat
1            1             1A75
2            1             1Z44
3            2             1H91
4            3             9G38

INSERT INTO CONDUIRE VALUES (1, 1) ;
INSERT INTO CONDUIRE VALUES (1, 2) ;
INSERT INTO CONDUIRE VALUES (1, 4) ;
INSERT INTO CONDUIRE VALUES (2, 3) ;

SELECT * FROM CONDUIRE ; 

PersonneId   VehiculeId
1            1
1            2
1            4
2            3 

A ce stade, rien n’interdit que Raoul conduise un char Patton, alors qu’il n’a pas le permis ad-hoc. Pour empêcher cela, on crée l’assertion qui va bien, quelque chose comme cela  :

CREATE ASSERTION INCL_ASSERT 
CHECK
(NOT EXISTS 
  (
    SELECT  *
    FROM 
      (
       SELECT e.personneid, d.categorieid
       FROM VEHICULE AS d INNER JOIN CONDUIRE AS e ON d.vehiculeid = e.vehiculeid
      ) AS s
    WHERE NOT EXISTS 
      (
       SELECT * 
       FROM 
         (
          SELECT e.personneid, d.categorieid
          FROM VEHICULE as d INNER JOIN CONDUIRE AS e ON d.vehiculeid = e.vehiculeid
           INNER JOIN PSN_CAT c ON c.categorieid = d.categorieid 
              AND e.personneid = c.personneid
        ) AS a
       WHERE s.personneid = a.personneid 
           AND s.categorieid = a.categorieid
      )
  );

Peu importe la lourdeur du code de cette assertion, je fais simplement observer que les 3 tables impliquées sont celles qui correspondent aux 3 associations du MCD.

Maintenant, je ne sache pas que les éditeurs de SGBD aient envie de proposer l’instruction CREATE ASSERTION, laquelle fait quand même partie de la norme SQL depuis une trentaine d’années (SQL/92 me semble-t-il). Cette instruction est déclarée gourmande par ses détracteurs, qui attendent sans doute l’utilisation des ordinateurs quantiques pour changer d’avis....

Alors, que faire ?

Intégrer la contrainte CHECK dans la table CONDUIRE ? Niet ! Les SGBD déjouent la manoeuvre...
Par exemple, avec SQL Server :

Msg 1046, Niveau 15, État 1
Les sous-requêtes ne sont pas autorisées dans ce contexte. Seules sont permises les expressions scalaires.

C’est-à-dire que seuls les contrôles les plus basiques sont légaux.

En passer par des triggers ? On se met à programmer avec les BEGIN/AFTER, for each row, etc.

Avant d’en arriver là, on peut préférer mettre en oeuvre une table reprenant la contrainte d’inclusion :

CREATE TABLE INCLUSION 
(
    PersonneId INT NOT NULL
  , VehiculeId INT NOT NULL
  , CategorieId INT NOT NULL
  , PRIMARY KEY (PersonneId, VehiculeId)
  , CONSTRAINT PSN_CAT_FK
      FOREIGN KEY (PersonneId, CategorieId)
      REFERENCES PSN_CAT (PersonneId, CategorieId)
      ON DELETE NO ACTION
  , CONSTRAINT VEH_FK
      FOREIGN KEY (VehiculeId, CategorieId)
      REFERENCES VEHICULE (VehiculeId, CategorieId)
      ON DELETE CASCADE      
);

Avec l'ajout d’une clé étrangère dans la table CONDUIRE (du coup la clé étrangère CON_VEH_FK dans la table CONDUIRE peut être supprimée) :

ALTER TABLE CONDUIRE
ADD CONSTRAINT INCLUSION_FK 
  FOREIGN KEY (PersonneId, VehiculeId)
  REFERENCES INCLUSION  (PersonneId, VehiculeId)
;

Quelques inserts de contrôle dans la table INCLUSION :

INSERT INTO INCLUSION VALUES (1, 1, 1) ;  -- psn, veh, cat
INSERT INTO INCLUSION VALUES (1, 2, 1) ;  -- psn, veh, cat
INSERT INTO INCLUSION VALUES (1, 4, 3) ;  -- psn, veh, cat
INSERT INTO INCLUSION VALUES (2, 3, 2) ;  -- psn, veh, cat 

Et tentative d'infraction dans la table CONDUIRE (Raoul et son char Patton) :

INSERT INTO CONDUIRE  VALUES (2, 4)

=>

Msg 547, Niveau 16, État 0
L'instruction INSERT est en conflit avec la contrainte FOREIGN KEY "INCLUSION_FK".

Mission accomplie.

Bien entendu la table INCLUSION ne respecte pas la deuxième forme normale puisque l’attribut CategorieId dépend de l’attribut VehiculeId, donc d’une partie de la clé primaire, d’où redondance, mais on a quand même mis ceinture, bretelles et épingle à nourrice.

Vos suggestions ?