Discussion :

[fsmrel]

Contraintes d'inclusion (du MCD au code SQL)

Prenons le cas du MCD ci-dessous, reprenant celui de la figure 13.38 de l’ouvrage de D. Nanci et B. Espinasse Ingénierie des systèmes d'information - Merise deuxième génération (paragraphe III-C-3-aa. Inclusion de participations d'une entité à plusieurs relations (INCLUSION)) :

Figure 1

La contrainte à garantir est la suivante :

(C1) Une personne ne peut effectuer un prêt qu’à la condition d’avoir préalablement souscrit un abonnement.

Cette contrainte fait l’objet dans le MCD ci-dessus d’une contrainte d’inclusion connectant les associations Souscrire et Effectuer.

La cardinalité 0,1 portée par la patte connectant l’entité-type Personne et l’association Souscrire oblige à mettre en oeuvre la contrainte d’inclusion. Si la cardinalité avait été 1,1, toute personne aurait obligatoirement souscrit un abonnement, la contrainte (C1) aurait donc automatiquement été vérifiée, et il eut été inutile dans ces conditions de mettre en oeuvre une contrainte d’inclusion.

Passage à SQL. Pour garantir la contrainte au stade SQL, dans leur ouvrage les auteurs mettent en oeuvre une instruction CREATE ASSERTION, mais comme les SGBD SQL du marché ne la proposent pas, leur palliatif passe par une instruction CREATE TRIGGER. Sachant que cette instruction diffère sensiblement d’un éditeur à l’autre et qu’elle est pénible à coder (je plains les auteurs), le défi est de produire un code SQL garantissant la contrainte autrement qu’avec un trigger.

La proposition des auteurs, avec trigger.

En l’absence d’un LDD, prenons celui produit par Looping :

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
CREATE TABLE Abonnement(
   abno INT,
   CONSTRAINT Abonnement_PK PRIMARY KEY(abno)
);
CREATE TABLE Personne(
   psno INT,
   abno INT,
   CONSTRAINT Personne_PK PRIMARY KEY(psno),
   CONSTRAINT Personne_Abonnement_FK FOREIGN KEY(abno) REFERENCES Abonnement(abno) ON DELETE CASCADE
);
CREATE TABLE Pret(
   prno INT,
   psno INT NOT NULL,
   CONSTRAINT Pret_PK PRIMARY KEY(prno),
   CONSTRAINT Pret_Personne_FK FOREIGN KEY(psno) REFERENCES Personne(psno) ON DELETE CASCADE
);

Où l’on note que la colonne abno de la table Personne est dotée du marqueur NULL.

Le trigger (ORACLE) reprenant celui des auteurs (colonnes renommées) :

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
CREATE TRIGGER Inclusion_Effectuer_Souscrire
BEFORE INSERT ON Pret
  ON EACH ROW
    WHEN new.psno IS NOT NULL
      DECLARE
        nb_abonnement number;
      BEGIN
        SELECT COUNT(*) INTO nb_abonnement 
        FROM Personne
        WHERE psno = :new.psno ;
        IF nb_abonnement = 0 THEN
           raise_application_error (-20006, 'Un abonnement n'a pas été souscrit');
        END IF;
      END;

Une solution pour éviter la présence du bonhomme NULL et la mise en oeuvre du trigger est de passer par le MCD suivant :

Figure 2

Code SQL produit par Looping :

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 TABLE Personne(
   psno SMALLINT,
   CONSTRAINT Personne_PK PRIMARY KEY(psno)
);
 
CREATE TABLE Abonnement(
   abno SMALLINT,
   CONSTRAINT Abonnement_PK PRIMARY KEY(abno)
);
 
CREATE TABLE Souscrire(
   psno SMALLINT,
   abno SMALLINT NOT NULL,
   CONSTRAINT Souscrire_PK PRIMARY KEY(psno),
   CONSTRAINT Souscrire_Personne_FK FOREIGN KEY(psno) REFERENCES Personne(psno) ON DELETE CASCADE,
   CONSTRAINT Souscrire_Abonnement_FK FOREIGN KEY(abno) REFERENCES Abonnement(abno) ON DELETE CASCADE
);
 
CREATE TABLE Pret(
   prno INT,
   psno SMALLINT NOT NULL,
   CONSTRAINT Pret_PK PRIMARY KEY(prno),
   CONSTRAINT Pret_Souscrire_FK FOREIGN KEY(psno) REFERENCES Souscrire(psno) ON DELETE CASCADE
);

Ce LDD ne comporte pas de trigger, ouf ! Reconnaissons que le MCD de la figure 1 est, sémantiquement parlant, plus séduisant que celui de la figure 2, mais peu importe ici. Le second MCD implique-t-il le premier ? Oui, si l’on est à même de passer automatiquement du premier au second.

Passage de la figure 1 à la figure 2

Osons nous mettre à la place de Looping. Dans le MCD de la figure 1, on détecte donc une contrainte d'inclusion. Celle-ci a pour source l’association Effectuer, pour cible l’association Souscrire, et pour pivot l’entité-type Personne. La patte connectant l’association (Souscrire) et le pivot (Personne) est porteuse d’une association 0,1 : suspicion ! 

Dans cette situation, on peut proposer une méthode pour passer du premier MCD au second :

Remplacer l’association cible Souscrire par une entité-type (Souscrire) à connecter à la source (Effectuer). La patte de la connexion est à faire porter d’une cardinalité identique à celle portée par la patte connectant le pivot (Personne) et la source (Effectuer) : il s’agit en l’occurrence d’une cardinalité 0,n. A cet effet, Looping donne le moyen de transformer l’association Souscrire en entité-type :

=>

Il reste à faire pivoter la patte d’association connectant Effectuer et Personne pour qu’elle associe Effectuer et Souscrire (au passage on efface la contrainte d’inclusion rendue inutile).

Au résultat :

Pour détailler

1) Looping a transformé l’association cible Souscrire de la figure 1 en entité-type (Souscrire).

2 Looping a créé une association S_P, connectant les entités-types Souscrire et Personne. La cardinalité portée par la patte connectant Personne et S_P est celle portée par la patte connectant le pivot et la cible dans la figure 1, c’est-à-dire 0,1. La patte connectant Souscrire et S_P est rendue porteuse par Looping d’une cardinalité 1,1(R), en sorte que cette entité-type hérite de l’identifiant du pivot Personne.

3) Looping a créé une association S_A, connectant les entités-types Souscrire et Abonnement. La cardinalité portée par la patte connectant Abonnement et l’association S_A est celle portée par la patte connectant Souscrire et Abonnement dans la figure 1. La cardinalité portée par la patte connectant Souscrire et l’association S_A est forcément 1,1 (une personne ne souscrit qu’un seul abonnement).

A partir du nouveau MCD, on produit le code SQL présenté ci-dessus, suite à quoi, ce MCD ayant rempli sa mission, il disparaît.

Conclusion

Soit on adopte la méthode Nanci (page 304 de l’ouvrage cité) et l'on doit créer le trigger (pas très jojo) en fonction de son propre SGBD, soit on demande à Looping de produire directement le code SQL qui va bien (pas de trigger), selon la méthode qui vient d’être proposée. C’est vous qui voyez.

[Paprick]

Bonsoir,

Soit on adopte la méthode Nanci (page 304 de l’ouvrage cité) et l'on doit créer le trigger (pas très jojo) en fonction de son propre SGBD, soit on demande à Looping de produire directement le code SQL qui va bien (pas de trigger), selon la méthode qui vient d’être proposée. C’est vous qui voyez.

Cet exercice est très intéressant et amène plusieurs réflexions :

• S'il y a moyen de modéliser proprement, sans contrainte, ni trigger ni autre code SQL spécifique, pourquoi s'en priver ?! Personnellement, je trouve que la solution proposée par la figure 2 est peut-être moins élégante, mais tout à fait compréhensible, surtout lorsque l'on compare le MCD avec le DDL souhaité un fine.
• Nous sommes ici dans un cas bien spécifique de contrainte où le changement de clé suffit pour régler le problème : ce n'est pas toujours le cas, et même rarement le cas si l'on considère toutes les contraintes pouvant exister (Totalité, Exclusion mutuelle, Partition, Simultanéité, ...) : l'appel à du codage est alors nécessaire, sachant qu'il diffèrera en fonction du SGBD visé.
• Faire une génération spécifique dans ce cas précis me parait donc dangereux dans la mesure où l'utilisateur ne saura plus s'il doit ou pas s'occuper lui-même du codage de la contrainte : c'est la raison pour laquelle Looping ne trafique pas le DDL en fonction des contraintes.
• Seules les CIF sont effectivement prises en charge : et c'est parce qu'elles le sont de manière exhaustive, y compris dans des cas complexes que seul Looping traite correctement (c'est aussi la raison pour laquelle, l'objet CIF est distinct des autres contraintes dans l'interface graphique proposée par Looping).

[escartefigue]

Toutes ces explications me conviennent parfaitement : c'est à la fois clair et cohérent.

N'oublions pas que dès que la conception progresse, les DBA sont mis à contribution pour vérifier la robustesse du modèle et, le cas échéant, travailler sur la mise en œuvre des contraintes.
Ce que n'aura pas fait Looping le sera à ce moment là.

Enfin, comme je l'indiquais dans un autre fil de discussion sur le même sujet, le MCD de la figure 1 a ma préférence, car plus proche de la réalité. [SOUSCRIRE] n'étant pas dans la figure 2 un véritable objet de gestion mais un artifice technique pour contourner la difficulté.

[fsmrel]

Salve,

Personnellement, je trouve que la solution proposée par la figure 2 est peut-être moins élégante, mais tout à fait compréhensible, surtout lorsque l'on compare le MCD avec le DDL souhaité un fine.

 
L’élégance n’est pas partie prenante ici, puisque l’utilisateur n’a pas connaissance de la figure 2, laquelle est présente seulement sous le capot et supprimée une fois le LDD créé.
 
 

Nous sommes ici dans un cas bien spécifique de contrainte où le changement de clé suffit pour régler le problème : ce n'est pas toujours le cas, et même rarement le cas si l'on considère toutes les contraintes pouvant exister (Totalité, Exclusion mutuelle, Partition, Simultanéité, ...) : l'appel à du codage est alors nécessaire, sachant qu'il différera en fonction du SGBD visé.

Il n’y a pas de changement de clé, seulement transformation d’une association en entité-type héritant de l’identifiant de celle à laquelle elle est connectée par une patte porteuse d’une cardinalité 0,1.

Tu évoques les autres types de contrainte : Totalité, Exclusion mutuelle, etc. il est clair qu’avec la façon dont SQL est fagoté (rigidité structurelle du trio SELECT, FROM, WHERE), ces contraintes sont hélas ! intraduisibles (elles sont de de niveau base de données et non pas de niveau table) : il faut donc en passer par les fourches caudines que sont ces constructions qui nous donnent bien du souci, à savoir les triggers (lesquels, à l’occasion d’autres discussions, nous ont fait transpirer de conserve), ayant chacun sa grammaire particulière, différant donc d’un SGBD à l’autre.

Seul le Modèle Relationnel de Données permet de mettre en oeuvre ce genre de contraintes de façon simple (j’ai fait le test pour toutes ces contraintes et pondrai un article à ce sujet).
A titre d’exemple, prenons le cas de ce MCD qui parle de lui-même :
 

Les variables relationnelles (relvars)

Code :

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

1
2
3
4
5
6
7
8
VAR Personnel BASE RELATION {Id INT, Name CHAR} KEY{Id} ;
VAR AvecEmploiConnu BASE RELATION {Id INT, Job CHAR} KEY{Id} ; 
VAR AvecEmploiInconnu BASE RELATION {Id INT} KEY{Id} ;
VAR SansEmploi BASE RELATION {Id INT} KEY{Id} ;
VAR AvecSalaireConnu BASE RELATION {Id INT, Salary CHAR} KEY{Id} ;
VAR AvecSalaireInconnu BASE RELATION {Id INT} KEY{Id} ; 
VAR SansSalaire BASE RELATION {Id INT} KEY{Id} ;

Les contraintes d’intégrité référentielle

Code :

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

1
2
3
4
5
6
CONSTRAINT AvecEmploiConnu_FK AvecEmploiConnu{Id} ⊆ Personnel{Id} ;
CONSTRAINT AvecEmploiInconnu_FK AvecEmploiInconnu{Id} ⊆ Personnel{Id} ;
CONSTRAINT SansEmploi_FK SansEmploi{Id} ⊆ Personnel{Id} ;
CONSTRAINT AvecSalaireConnu_FK AvecSalaireConnu{Id} ⊆ Personnel{Id} ;
CONSTRAINT AvecSalaireInconnu_FK AvecSalaireInconnu{Id} ⊆ Personnel{Id} ;
CONSTRAINT SansSalaire_FK SansSalaire{Id} ⊆ Personnel{Id} ;

Les contraintes de partitionnement (XUNION est l’opérateur utilisé pour l’union exclusive) :

Code :

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

1
2
3
4
5
CONSTRAINT EMPLOI_XT (Personnel{Id} =  UNION {AvecEmploiConnu{Id}, AvecEmploiInconnu{Id},  SansEmp	loi{Id}}
                  AND Personnel{Id} = XUNION {AvecEmploiConnu{Id}, AvecEmploiInconnu{Id},  SansEmploi{Id}}) ;

CONSTRAINT SALAIRE_XT (Personnel{Id} = UNION  {AvecSalaireConnu{Id}, AvecSalaireInconnu{Id}, SansSalaire{Id}}
                   AND Personnel{Id} = XUNION {AvecSalaireConnu{Id}, AvecSalaireInconnu{Id}, SansSalaire{Id}}) ;

Va trouver l’équivalent SQL...
 
 

Faire une génération spécifique dans ce cas précis me parait donc dangereux dans la mesure où l'utilisateur ne saura plus s'il doit ou pas s'occuper lui-même du codage de la contrainte : c'est la raison pour laquelle Looping ne trafique pas le DDL en fonction des contraintes.

 
De même que Looping nous offre, au moyen de la case à cocher (« Générer une table de correspondance dans le MLD ») la possibilité de générer la table SQL qui va bien (c’est-à-dire dépourvue de NULL), ne pourrait-on envisager qu’il nous offre la possibilité (autre case à cocher) de demander la production du code SQL dont j’ai fait mention (cas bien entendu limité à l’inclusion) ?
 
 

comme je l'indiquais dans un autre fil de discussion sur le même sujet, le MCD de la figure 1 a ma préférence, car plus proche de la réalité. [SOUSCRIRE] n'étant pas dans la figure 2 un véritable objet de gestion mais un artifice technique pour contourner la difficulté.

 
Comme je l’ai précisé, l’utilisateur ne voit pas la figure 2. Elle est sous le capot, Looping la met en oeuvre et il est seul à en avoir connaissance et avoir l’usage du MCD qui y figure, dans le but de créer le LDD. Ceci fait, Looping supprime cette figure devenue inutile 2. 

A suivre...

-------------------------------------

[Paprick]

Bonsoir,

De même que Looping nous offre, au moyen de la case à cocher (« Générer une table de correspondance dans le MLD ») la possibilité de générer la table SQL qui va bien (c’est-à-dire dépourvue de NULL), ne pourrait-on envisager qu’il nous offre la possibilité (autre case à cocher) de demander la production du code SQL dont j’ai fait mention (cas bien entendu limité à l’inclusion) ?

Je suis loin d'être certain que tous les cas d'inclusion puissent se résoudre avec le processus que tu as décrit.

[fsmrel]

Salve Paprick,

je vais voir à fond le cas des N,N...