Discussion :

[Benzouye]

Bonjour à tous,

Considérons le MCD ci-dessous :


Dans le fonctionnement, une demande peut être rattachée à plusieurs budgets, et une commande également.
Mais vu qu'une commande découle d'une demande, je me demandais comment représenter dans le MCD la contrainte "une commande ne peut être liée qu'à un budget déjà lié à sa demande" ?
J'ai d'abord cru à la CIF, mais en creusant j'ai vite compris que non ...

D'abord, cela se fait-il dans un MCD en général ?
Ensuite, cela est-il possible dans Looping ?

D'avance merci pour vos retours.

EDIT : Faisons abstraction de la cardinalité 0,1 entre commande et demande, considérons que c'est 1,1

[fsmrel]

Bonsoir Benzouye,


La modélisation (avec Merise) de la contrainte selon laquelle "une commande ne peut être liée qu'à un budget déjà lié à sa demande" ne me semble pas réalisable de façon simple avec les contraintes de base (inclusion, simultanéité).

Cela dit, qu’en est-il au stade relationnel ? Allons-y avec SQL. Je pars de votre MCD (j’ai raccourci les noms des attributs) :

Code 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
25
26
27
28
29
30
31
32
33
 
CREATE TABLE BUDGET(
   bId VARCHAR(3),
   CONSTRAINT BUDGET_PK PRIMARY KEY(bId)
);
 
CREATE TABLE DEMANDE(
   dId VARCHAR(3),
   CONSTRAINT DEMANDE_PK PRIMARY KEY(dId)
);
 
CREATE TABLE COMMANDE(
   cId VARCHAR(3),
   dId VARCHAR(3) NOT NULL,
   CONSTRAINT COMMANDE_PK PRIMARY KEY(cId),
   CONSTRAINT COMMANDE_DEMANDE_FK FOREIGN KEY(dId) REFERENCES DEMANDE(dId)
);
 
CREATE TABLE BUD_DEM(
   bId VARCHAR(3),
   dId VARCHAR(3),
   CONSTRAINT BUD_DEM_PK PRIMARY KEY(bId, dId),
   CONSTRAINT BUD_DEM_BUDGET_FK FOREIGN KEY(bId) REFERENCES BUDGET(bId),
   CONSTRAINT BUD_DEM_DEMANDE_FK FOREIGN KEY(dId) REFERENCES DEMANDE(dId)
);
 
CREATE TABLE BUD_COM(
   bId VARCHAR(3),
   cId VARCHAR(3),
   CONSTRAINT BUD_COM_PK PRIMARY KEY(bId, cId),
   CONSTRAINT BUD_COM_BUDGET_FK FOREIGN KEY(bId) REFERENCES BUDGET(bId),
   CONSTRAINT BUD_COM_COMMANDE_FK FOREIGN KEY(cId) REFERENCES COMMANDE(cId)
);

Remarques :

(1) J’ai utilisé le type VARCHAR(3) pour les identifiants, mais c’est dans le but de se simplifier la vie pour les tests. A terme, c’est à remplacer par exemple par INT (ou un auto-incrément).

(2) La cardinalité de la patte connectant COMMANDE et DEM_COM est 1,1. Si certaines commandes ne sont pas attachées à des demandes, l’entité-type COMMANDE pourrait faire l’objet d’une spécialisation, de manière à distinguer les commandes avec demandes et les commandes sans demandes.


Un jeu d’essai :

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
 
INSERT INTO BUDGET (bId)
VALUES ('b1'), ('b2'), ('b3') ;
 
SELECT '' as BUDGET, * FROM BUDGET ;
 
INSERT INTO DEMANDE (dId)
VALUES ('d11'), ('d12')
     , ('d21'), ('d22')
     , ('d31'), ('d32')
 ;
 
SELECT '' as DEMANDE, * FROM DEMANDE ;
 
INSERT INTO COMMANDE(cId, dId)
VALUES ('c11', 'd11'), ('c12', 'd11')
     , ('c21', 'd12'), ('c22', 'd12')
 
SELECT '' as COMMANDE, * FROM COMMANDE ;
 
INSERT INTO BUD_DEM(bId, dId)
VALUES ('b1', 'd11'), ('b1', 'd12')
     , ('b2', 'd11'), ('b2', 'd12')
     , ('b2', 'd21'), ('b2', 'd22')
;
SELECT '' as BUD_DEM, * FROM BUD_DEM ;
 
/* paires {budget, commande} respectant la contrainte selon laquelle
   une commande ne peut être liée qu'à un budget déjà lié à sa demande */
 
INSERT INTO BUD_COM(bId, cId)
VALUES ('b1', 'c11'), ('b1', 'c12')
     , ('b2', 'c11'), ('b2', 'c21')
;
 
/* une paire {budget, commande} ne respectant pas la contrainte */
 
INSERT INTO BUD_COM(bId, cId)
VALUES ('b3', 'c11')
;

Pour le moment on peut violer la contrainte sans problème.

Histoire d’y voir clair, créons une vue pour les paires {budget, commande} légales :

CREATE VIEW LEGAL (bId, cId)
AS 
SELECT bId, cId 
FROM   COMMANDE as x
  JOIN BUD_DEM as y ON x.dId = y.dId

Puis créons une fonction permettant de compter les infractions lors de la mise à jour de la table BUD_COM (inserts, updates) :

CREATE FUNCTION BUD_COM_LEGAL_FN()
RETURNS INT
AS
BEGIN
   DECLARE @n INT  
   SELECT @n = COUNT(*) 
   FROM   BUD_COM as x
   WHERE  NOT EXISTS
         (SELECT *
          FROM   LEGAL as y
          WHERE  x.bId = y.bId AND x.cId = y.cId)  
   RETURN @n  
END ;  
GO  

Créons une contrainte (hébergée par la table BUD_COM) à partir de cette fonction :

ALTER TABLE BUD_COM  
    ADD CONSTRAINT BUD_COM_LEGAL CHECK (dbo.BUD_COM_LEGAL_FN() = 0) 
; 

Cette fois-ci, le SGBD (en l’occurrence SQL Server) rejettera des instructions telles que :

INSERT INTO BUD_COM(bId, cId)
VALUES ('b3', 'c11')
; 

Ou :

UPDATE BUD_COM 
    SET bId = 'b3'
    WHERE bId = 'b1'
; 

[Benzouye]

Merci pour la réponse.

Côté SQL je gère pour ma part la contrainte avec un TRIGGER BEFORE INSERT qui teste l'existence dans la table budget_demande avant d'autoriser l'insertion dans budget_commande.

Ma question portait vraiment sur le MCD et les possibilités offertes par looping pour faire cela ... Sans aller jusqu'à imaginer que Looping propose le TRIGGER ...

[fsmrel]

Bonjour Benzouye,

D’accord pour le trigger, mais, si cela vous intéresse, on peut éviter la fonction et le trigger en produisant, en plus du MCD à présenter à l’utilisateur, un MCD orienté SQL. De l’association BUD_DEM (cf. post #2), on fait alors une entité-type associative, on identifie COMMANDE relativement à DEMANDE, puis on établit une association BUD_COM entre BUD_DEM et COMMANDE :

Le code généré par Looping est le suivant :

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
 
CREATE TABLE BUDGET(
   bId VARCHAR(3),
   CONSTRAINT BUDGET_PK PRIMARY KEY(bId)
);
 
CREATE TABLE DEMANDE(
   dId VARCHAR(3),
   CONSTRAINT DEMANDE_PK PRIMARY KEY(dId)
);
 
CREATE TABLE COMMANDE(
   dId VARCHAR(3),
   cId VARCHAR(3),
   CONSTRAINT COMMANDE_PK PRIMARY KEY(dId, cId),
   CONSTRAINT COMMANDE_DEMANDE_FK FOREIGN KEY(dId) REFERENCES DEMANDE(dId)
);
 
CREATE TABLE BUD_DEM(
   bId VARCHAR(3),
   dId VARCHAR(3),
   CONSTRAINT BUD_DEM_PK PRIMARY KEY(bId, dId),
   CONSTRAINT BUD_DEM_BUDGET_FK FOREIGN KEY(bId) REFERENCES BUDGET(bId),
   CONSTRAINT BUD_DEM_DEMANDE_FK FOREIGN KEY(dId) REFERENCES DEMANDE(dId)
);
 
CREATE TABLE BUD_COM(
   bId VARCHAR(3),
   dId_1 VARCHAR(3),
   dId VARCHAR(3),
   cId VARCHAR(3),
   CONSTRAINT BUD_COM_PK PRIMARY KEY(bId, dId_1, dId, cId),
   CONSTRAINT BUD_COM_BUD_DEM_1_FK FOREIGN KEY(bId, dId_1) REFERENCES BUD_DEM(bId, dId),
   CONSTRAINT BUD_COM_COMMANDE_FK FOREIGN KEY(dId, cId) REFERENCES COMMANDE(dId, cId)
);

La table BUD_COM contient les deux colonnes redondantes dId et dId_1, en conséquence de quoi l’une d’elles doit disparaître, suite à quoi on satisfait automatiquement à la contrainte "une commande ne peut être liée qu'à un budget déjà lié à sa demande".
Supprimons par exemple la colonne dId_1. Cela entraîne le remplacement dans la table BUD_COM des contraintes BUD_COM_PK, BUD_COM_BUD_DEM_1_FK (en passant, j’utilise ici SQL Server) :

ALTER TABLE BUD_COM
    DROP CONSTRAINT BUD_COM_PK, BUD_COM_BUD_DEM_1_FK ; 

ALTER TABLE BUD_COM
    ADD CONSTRAINT BUD_COM_PK PRIMARY KEY(bId, dId, cId) 
      , CONSTRAINT BUD_COM_BUD_DEM_FK FOREIGN KEY(bId, dId) REFERENCES BUD_DEM (bId, dId) ; 

Ceci fait, on supprime la colonne dId_1 :

ALTER TABLE BUD_COM
    DROP COLUMN dId_1 ; 

Sans aller jusqu'à imaginer que Looping propose le TRIGGER…

Etant donné la dizaine de SGBD connus de Looping, et que chacun d’eux a sa propre syntaxe quant à l’instruction CREATE TRIGGER, j’imagine mal Paprick entamer un travail de titan en ce sens…

[escartefigue]

Bonjour

Etant donné la dizaine de SGBD connus de Looping, et que chacun d’eux a sa propre syntaxe quant à l’instruction CREATE TRIGGER, j’imagine mal Paprick entamer un travail de titan en ce sens…

Je suis bien d'accord, d'ailleurs certains SGBD n'implémentent que partiellement les TRIGGER (Access et MySQL par exemple ne connaissent pas les TRIGGER "instead of") et la vérification d'une contrainte sera plus performante que la mise en place d'un trigger.

[Paprick]

Etant donné la dizaine de SGBD connus de Looping, et que chacun d’eux a sa propre syntaxe quant à l’instruction CREATE TRIGGER, j’imagine mal Paprick entamer un travail de titan en ce sens…

Je suis bien d'accord, d'ailleurs certains SGBD n'implémentent que partiellement les TRIGGER (Access et MySQL par exemple ne connaissent pas les TRIGGER "instead of") et la vérification d'une contrainte sera plus performante que la mise en place d'un trigger.

Tout à fait ! De plus, Looping autorise la saisie de code SQL au sein dans la définition des contraintes inter-associations et dans des règles spécifiques telles que celles définies dans ce modèle. Ce code est ensuite automatiquement rajouté au script SQL de génération du schéma relationnel .

[fsmrel]

J’imagine mal Paprick entamer un travail de titan…

En fait, pour ceux qui ont eu le bonheur de faire des études classiques, je dirais qu’il s’agit là d’un des travaux d’Hercule, à savoir du ménage dans les écuries d’Augias !

[Benzouye]

La table BUD_COM contient les deux colonnes redondantes dId et dId_1, en conséquence de quoi l’une d’elles doit disparaître, suite à quoi on satisfait automatiquement à la contrainte "une commande ne peut être liée qu'à un budget déjà lié à sa demande".

Oui c'est super !

Reste juste à trouver dans Looping comment ne pas avoir à retoucher le SQL pour contraindre la primaire de BUD_COM sur les 3 ids (pas taper, pas taper) ...

[Paprick]

Bonjour,

Reste juste à trouver dans Looping comment ne pas avoir à retoucher le SQL pour contraindre la primaire de BUD_COM sur les 3 ids (pas taper, pas taper) ...

C'est très simple : utilisez l'outil "Règle" de Looping et introduisez le code SQL fourni par fsmrel dans la zone prévue à cet effet dans la définition de la règle.
Ce code se rajoutera automatiquement à la fin du script SQL .

[Benzouye]

OK.

Je vais paraître un poil insistant, mais vraiment pour être sûr, il n'y a pas une façon de créer le MCD pour expliquer à Looping de créer la clé primaire sur les 3 colonnes, avec les deux clés étrangères chacune composée des deux ids, sans passer par la règle ?

[Paprick]

Bonsoir,

Je vais paraître un poil insistant, mais vraiment pour être sûr, il n'y a pas une façon de créer le MCD pour expliquer à Looping de créer la clé primaire sur les 3 colonnes, avec les deux clés étrangères chacune composée des deux ids, sans passer par la règle ?

Alors, je vais tenter une proposition en ce sens, mais fsmrel va devor vérifier le bien fondé de la dite proposition !
Sur l'exemple donné par fsmrel, je propose d'enlever le (R) de l'association DEM_COM : on obtient alors le bon script SQL pour la table BUD_COM (sans le did_1).
Le problème est alors de s'assurer de l'unicité du couple {cId, dId} qui était obtenu grâce à l'identifiant relatif... que je propose de retirer...
Ma proposition : une clé alternative (AK) réalisé avec un index composé de la PK cId et de la FK dId...
La réalisation sous Looping n'est pas triviale (mais ça reste très simple quand on connait l'astuce ) : j'attends donc le quitus de François concernant la conception proposée pour expliquer comment faire !
J'ai quand même des doutes quant à l'intérêt de cette proposition dans la mesure où cId garantit à lui seul l'unicité... et donc que le couple {cId, dId} est une surclé...

[escartefigue]

Aucun logiciel de modélisation ne produit de script DDL définitif.
Même si sur ce chapitre, Looping fait (vite et bien du reste) une grande partie du boulot, c'est au DBA de faire les ajustements de ce script pour l'adapter aux contraintes de terrain.

[fsmrel]

Ave,

je propose d'enlever le (R) de l'association DEM_COM : on obtient alors le bon script SQL pour la table BUD_COM (sans le did_1). Le problème est alors de s'assurer de l'unicité du couple {cId, dId} qui était obtenu grâce à l'identifiant relatif... que je propose de retirer...
Ma proposition : une clé alternative (AK) réalisé avec un index composé de la PK cId et de la FK dId

Si tu enlèves le (R) de l'association DEM_COM, tu passes alors de l’identification relative à l’identification absolue, auquel cas il existe la dépendance fonctionnelle {cId} → {dId}. Cette DF vaut dans tous les cas de figure, autrement dit, si elle s’applique de facto pour la table COMMANDE :

   cId VARCHAR(3),
   dId VARCHAR(3) NOT NULL,
   CONSTRAINT COMMANDE_PK PRIMARY KEY(cId),
   CONSTRAINT COMMANDE_DEMANDE_FK FOREIGN KEY (dId) REFERENCES DEMANDE(dId)
); 

Elle vaut également pour la table BUD_COM. Va pour une clé alternative (contrainte BUD_COM_AK) :

   bId VARCHAR(3),
   dId VARCHAR(3),
   cId VARCHAR(3),
   CONSTRAINT BUD_COM_PK PRIMARY KEY (bId, dId, cId),
   CONSTRAINT BUD_COM_AK UNIQUE (dId, cId),   
   CONSTRAINT BUD_COM_BUD_DEM_FK FOREIGN KEY(bId, dId) REFERENCES BUD_DEM (bId, dId),
   CONSTRAINT BUD_COM_COMMANDE_FK FOREIGN KEY(cId) REFERENCES COMMANDE (cId)
); 

Mais la mise en oeuvre de la DF {cId} → {dId} doit être effective, et comment fait-on sans passer par un trigger ou une contrainte appelant une fonction ad-hoc ?


Remarque annexe : dans la table BUD_COM la 2NF (deuxième forme normale) est violée ! Dans ce cas-là, pour respecter la théorie relationnelle, on normalise en appliquant le théorème de Heath. La décomposition de BUD_COM qui s’ensuit fait qu’on retrouve les tables COMMANDE et BUD_COM du post #2, autrement dit on est revenu à la case Départ…
L’alternative est donc de violer la 2NF, mais en prenant en compte la clé candidate {bId, cId}, soit en faisant la clé primaire, soit en l’ajoutant en tant que clé alternative. Cela dit, on ne pourra pas faire l’économie d’une contrainte traduisant la DF {cId} → {dId}, au moyen d’un trigger ou d’une fonction hébergée par la contrainte, comme je l'ai évoqué ci-dessus.
Ouch ! etc.

[fsmrel]

Bonjour,

Aucun logiciel de modélisation ne produit de script DDL définitif.
Même si sur ce chapitre, Looping fait (vite et bien du reste) une grande partie du boulot, c'est au DBA de faire les ajustements de ce script pour l'adapter aux contraintes de terrain.

Exact. Pour le moment, c’est effectivement au DBA de créer par exemple la fonction qui va bien et de la prendre en compte au stade du CREATE (ou ALTER) TABLE (voyez la fonction BUD_COM_LEGAL_FN dans le post #2).

Le jour où MM. les éditeurs de SGBD prendront la peine de mieux tenir compte de la norme SQL, la vie du DBA sera grandement simplifiée grâce à la possibilité de mise en oeuvre d’une assertion.

Exemple :

CREATE ASSERTION BUD_COM_LEGAL_CHK CHECK 
    (SELECT COUNT(*) 
     FROM   BUD_COM as x
     WHERE  NOT EXISTS (SELECT * 
                        FROM   LEGAL as y
                        WHERE  x.bId = y.bId AND x.cId = y.cId) = 0) ;

Je rappelle que LEGAL est une vue qui permet au DBA et aux développeurs de se simplifier la vie là aussi (cf. post #2).