Discussion :

[laurentSc]

En effet, j'ai modifié les cardinalités (notamment remplacé les 1,1 par 0,1) et les NOT NULL ont disparu...

[fsmrel]

Bonjour,

J'ai pas compris ce que vous avez compris...

Je veux dire que le COMMENT (par exemple l’histoire des CSV) n’a pas à conditionner la modélisation conceptuelle (le MCD).

Maintenant, revenons sur la règle de gestion selon laquelle un utilisateur qui effectue une demande (ticket) doit posséder une licence.

Au stade MCD, avec Looping on sait faire le boulot en représentant cette règle au moyen d’une contrainte d’inclusion ayant pour portée (source) TICKET et pour cible LICENCE (cf. post #98), à savoir qu’un utilisateur qui effectue une demande (ticket) doit posséder une licence. C’est ce que vous a recommandé escartefigue.

Conceptuellement, on est irréprochable, on respecte le cahier des charges.

Qu’en est-il au stade SQL ?

Le MCD n’a pas à impliquer le code SQL, c’est-à-dire devoir générer le code permettant de garantir la règle de gestion, le langage SQL n’est pas la conséquence logique de la méthode Merise ! Le développeur SQL doit donc prendre le relais. Plusieurs possibilités lui sont offertes, par exemple :

Créer une assertion (instruction CREATE ASSERTION),

Créer une contrainte de type CHECK,

Mettre en oeuvre des triggers,

[...]

A titre d’exemple, partons du code SQL suivant de création des tables :

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
CREATE TABLE US_USER
(
   userId INT,
   sesa INT NOT NULL,
   nom VARCHAR(16) NOT NULL,
   prenom VARCHAR(16) NOT NULL,
   CONSTRAINT US_USER_PK PRIMARY KEY(userId),
   CONSTRAINT US_USER_AK UNIQUE(sesa)
);
 
CREATE TABLE LICENCE
(
   licenceId INT,
   userId INT NOT NULL,
   CONSTRAINT LICENCE_PK PRIMARY KEY(licenceId),
   CONSTRAINT LICENCE_US_USER_FK FOREIGN KEY(userId) REFERENCES US_USER(userId)
);
 
CREATE TABLE TICKET
(
   ticketId INT,
   ticketNum INT NOT NULL,
   userCreateurId INT NOT NULL,
   userDemandeurId INT NOT NULL,
   CONSTRAINT TICKET_PK PRIMARY KEY(ticketId),
   CONSTRAINT TICKET_AK UNIQUE(ticketNum),
   CONSTRAINT TICKET_US_USER_A_FK FOREIGN KEY(userCreateurId) REFERENCES US_USER(userId),
   CONSTRAINT TICKET_US_USER_B_FK FOREIGN KEY(userDemandeurId) REFERENCES US_USER(userId)
);

On a évoqué la création d’une assertion pour garantir la règle de gestion. On utilise à cet effet l’instruction CREATE ASSERTION telle que définie par la norme SQL :

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE ASSERTION ASSERT_01 
CHECK (NOT EXISTS 
          (SELECT * 
           FROM   TICKET AS x
           WHERE  NOT EXISTS 
                     (SELECT * FROM LICENCE AS y 
                      WHERE x.userDemandeurId = y.userId)
) ;

Sachant que le quantificateur universel (FORALL) n’a pas été prévu par les pères de SQL, comme l’enseigne la logique, on a donc utilisé la double négation du quantificateur existentiel (EXISTS) pour exprimer la contrainte :

« Il n’existe pas de demande de la part d’un utilisateur non possesseur d’une licence »

est équivalent à :

« Chaque utilisateur effectuant une demande doit être possesseur d’une licence ».

Cela dit, si le SGBD ne propose pas l’instruction CREATE ASSERTION, on peut se rabattre par exemple sur la création d’une fonction ad-hoc, à laquelle fait appel une contrainte CHECK portée par la table TICKET.

Version SQL Server, à adapter pour MySQL :

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
go
CREATE FUNCTION inclusion_ticket_licence_FN()
RETURNS INT
AS
BEGIN
   DECLARE @n INT  
   SELECT @n = COUNT(*) 
   FROM   TICKET as x
   WHERE  NOT EXISTS
         (SELECT *
          FROM   LICENCE as y
          WHERE  x.userDemandeurId = y.userId)  
   RETURN @n  
END ;  
go 
 
ALTER TABLE TICKET  
   ADD CONSTRAINT TICKET_inclusion_ticket_licence_FN CHECK (dbo.inclusion_ticket_licence_FN() = 0) 
;

Si on tente de créer une demande pour l’utilisateur Raoul alors que celui-ci n’a pas de licence, il y aura échec de la tentative de création (ou de modification) de la demande.
La fonction ci-dessus est générale, on peut l’adapter au cas particulier d’un utilisateur (performance oblige) :

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
go
CREATE FUNCTION inclusion_ticket_licence_FN(@ticketId int)
RETURNS INT
AS
BEGIN
   DECLARE @n INT  
   SELECT @n = COUNT(*) 
   FROM   TICKET as x
   WHERE  ticketId = @ticketId  
      AND NOT EXISTS
          (SELECT *
           FROM   LICENCE as y
           WHERE  x.userDemandeurId = y.userId)  
   RETURN @n  
END ;  
go 
 
ALTER TABLE TICKET  
   ADD CONSTRAINT TICKET_inclusion_ticket_licence_FN CHECK (dbo.inclusion_ticket_licence_FN(ticketId) = 0) 
;

Mais attention, seuls les INSERT sont surveillés, les UPDATE peuvent impunément violer la contrainte ! Dans le même ordre d’idée, on peut supprimer les licences sans que rien ne s’y oppose… Bref, pour parler comme Tsitsipas, on a comme des trous dans la raquette…

Ainsi, on se doute que la mise en oeuvre de triggers est plus sûre, on pourra en reparler, mais je vous laisse un peu chercher.


Question : pourquoi ne pas mettre directement en relation TICKET et LICENCE ? En l’occurrence, un ticket détermine une licence, laquelle détermine un utilisateur, donc le ticket détermine transitivement son utilisateur.

[laurentSc]

Bonsoir,

pour parler comme Tsitsipas, on a comme des trous dans la raquette…

chu moins fort que lui, mais moi aussi des trous dans la raquette : pour la nième fois, bien que abonné à la discussion, pas reçu de notification et je ne découvre votre réponse que plusieurs heures après

Le MCD n’a pas à impliquer le code SQL, c’est-à-dire devoir générer le code permettant de garantir la règle de gestion, le langage SQL n’est pas la conséquence logique de la méthode Merise !

Et pourtant, Looping fait bien 90% du boulot...

Créer une contrainte de type CHECK,

Ca va être dur :

Une contrainte "check" ne sera pas applicable ici, MySQL ne les connaissant pas . MySQL ne connait que les "ENUM", ersatz de check, en bien plus pauvre .

Question : pourquoi ne pas mettre directement en relation TICKET et LICENCE ? En l’occurrence, un ticket détermine une licence, laquelle détermine un utilisateur, donc le ticket détermine transitivement son utilisateur.

Je ne comprends pas le sens de détermine (relation de cause à effet ?). En tout cas une licence détermine un utilisateur : ce qui risque de coincer, c'est qu'il existe 3 types d'utilisateur... :

Il existe 3 types d'utilisateurs :
- les license_owners ;
- les submitters (ceux qui écrivent les tickets) ;
- les customers (les demandeurs de création d'un ticket)

Je suis impressionné par votre maîtrise du SQL

[fsmrel]

Ca va être dur

Je n’ai pas installé, donc pas testé MySQL 8.0.16, mais la doc officielle dit que la clause CHECK est désormais opérationnelle (elle ne l’était pas précédemment).

Je ne comprends pas le sens de détermine (relation de cause à effet ?)

Un ticket donné disons T1 fait référence à au moins et au plus une licence, disons L1 ;

Cette licence L1 fait référence à au moins et au plus un utilisateur, disons U1 ;

Donc le ticket T1 fait transitivement référence exactement à U1.

Par conséquent, dans ce scénario, si dans le MCD on supprime l’association REQUEST entre TICKET et USER en la remplaçant par une association (appelons-la RQ) entre TICKET et LICENCE, alors on connaît le demandeur U1 de T1 via RQ et OWN.

Au stade SQL cela se traduit par une jointure entre TICKET, LICENCE et USER.

Par exemple, qui est le demandeur pour le ticket 314116 :

Code SQL :

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

1
2
3
4
5
SELECT z.sesa
FROM   TICKET as x 
  JOIN LICENCE as y ON x.licenceId = y.licenceId
  JOIN USER as z ON y.userId = z.userId
WHERE x.ticketNum = 314116 ;

Ce qui risque de coincer, c'est qu'il existe 3 types d'utilisateur…

Le type d’utilisateur est étranger au film ! sinon cela voudrait dire par exemple que la licence appartient en même temps à 3 utilisateurs, ce qui est évidemment un non sens.

[laurentSc]

pourquoi ne pas mettre directement en relation TICKET et LICENCE ? En l’occurrence, un ticket détermine une licence, laquelle détermine un utilisateur, donc le ticket détermine transitivement son utilisateur.

J'ai fini par comprendre l'intérêt et je l'ai appliqué dans mon MCD.
D'autre part, pour éviter d'avoir des FKs nulles, j'ai créé une 2e classe user. Dorénavant, il y a US_user_license et US_user_ticket. Et plus besoin de contrainte d'intégrité.

Voici mon nouveau MCD :

Qu’est-ce qu’une organisation ?

C'est l'entité dans laquelle l'utilisateur travaille. En gros, la même chose que la propriété buunitname de la classe CO_company mais comme le vocabulaire employé diffère, il est plus simple de créer une nouvelle classe.

[escartefigue]

Bonjour Laurent

D'autre part, pour éviter d'avoir des FKs nulles, j'ai créé une 2e classe user. Dorénavant, il y a US_user_license et US_user_ticket. Et plus besoin de contrainte d'intégrité.

Mauvaise idée : un utilisateur U1 titulaire d'une licence est enregistré comme tel dans votre base de données. Plus tard, ce même utilisateur déclare un incident et ouvre donc un ticket, vous allez donc devoir créer un "clone" U1' de U1 sans garantie que ce clone soit un sosie parfait. C'est le problème de toute redondance dans une BDD : encombrement inutile et maintenance multiple en cas de double parfait et incohérence dans le cas contraire.

Pour rappel : la gestion des pièces jointes (en modifiant votre message) permet d'éviter le double affichage du MCD dans votre précédente réponse.

[laurentSc]

Bonjour,
oui, je n'avais pas pensé à ce souci de redondance et de risque de non-cohérence. Je pense qu'en utilisant le mécanisme de l'héritage comme ci-dessous, ce risque disparaît, non ?

Pour rappel : la gestion des pièces jointes (en modifiant votre message) permet d'éviter le double affichage du MCD dans votre précédente réponse.

Merci du rappel ; j'ai corrigé mon message précédent.

[escartefigue]

Attention : avec l'héritage, le sous-type n'a pas d'identifiant propre, il hérite de celui du sur-type

[Paprick]

Attention : avec l'héritage, le sous-type n'a pas d'identifiant propre, il hérite de celui du sur-type

Sauf si l'on souhaite une "Généralisation" et non une "Spécialisation" .

[escartefigue]

En effet, mais le détenteur d'une licence ne peut pré-exister à l'utilisateur, ici on est bien dans le cas d'une spécialisation

[laurentSc]

J'ai donc supprimé l'identifiant des classes fille :



Avec un tel MCD, ai-je bien éliminé le risque de non-cohérence du à la redondance ?

[fsmrel]

ai-je bien éliminé le risque de non-cohérence du à la redondance ?

Quod est demonstrandum… L’entité-type, US_user est désormais dotée de deux sous-types, USL_user_license et UST_user_ticket jusque-là invisibles : pas d’objection, selon le MCD il n’y a pas de contrainte d’exclusion entre ces deux sous-types, donc un utilisateur peut relever simultanément des deux sous-types.

Quoi qu’il en soit, considérons maintenant le cas par exemple du ticket 314116 : celui-ci fait nécessairement référence à une licence (conséquence de la cardinalité 1,1 portée par la patte d’association connectant TI_ticket et CONCERNS) laquelle fait nécessairement référence à un utilisateur (conséquence de la cardinalité 1,1 portée par la patte connectant LI_license et OWN), lequel appartient au type USL_user_license. Supposons qu’il s’agisse de l’utilisateur Raoul. Le ticket 314116 en question fait aussi nécessairement référence à un utilisateur appartenant au type UST_user_ticket (conséquence de la cardinalité 1,1 portée par la patte connectant TI_ticket et ESTABLISH) et qui peut être Fernand. Ce ticket 314116 fait encore référence à un utilisateur appartenant au type UST_user_ticket (conséquence de la cardinalité 1,1 portée par la patte connectant TI_ticket et REQUEST) et qui peut être Paul. Ainsi, Raoul, Fernand et Paul sont tous les trois concernés par le même ticket, et selon le MCD, rien ne s’ oppose. Qu’en est-il ?

[laurentSc]

Bonjour,

encore un trou dans la raquette vu que je ne vois votre message que ce matin...

je remarque votre grande maîtrise sur les MCDs car vous avez mis le doigt (à une heure avancée de la nuit !) sur une lacune de mon MCD car pour reprendre les prénoms que vous avez utilisés, le dénommé Paul (requester d'un ticket) est nécessairement aussi owner d'une licence. Je tente de modéliser cette règle avec une contrainte. Qu'en pensez-vous ?

[escartefigue]

Il y a belle lurette que dans cette discussion nous avons abordé comment s'assurer que l'émetteur d'un ticket était également possesseur d'une licence.
cf. la réponse n° 66 et la mise en place d'une contrainte d'inclusion ayant pour origine l'association (request) et pour cible l'association (own)
Et nous avons établi comme acquis le fait que le demandeur du ticket (request) et son créateur (establisher) pouvaient être deux personnes différentes.
cf. les réponses n° 37 et 38.

[laurentSc]

En effet, et comme quoi j'ai bien assimilé votre réponse (post #66), vu que dans un premier temps, j'avais retiré la contrainte, fsmrel a remarqué un défaut de mon MCD ; j'ai vu qu'il manquait une contrainte et sans regarder la proposition que vous m'aviez faite, j'ai refait exactement la même chose !

[fsmrel]

pour reprendre les prénoms que vous avez utilisés, le dénommé Paul (requester d'un ticket) est nécessairement aussi owner d'une licence. Je tente de modéliser cette règle avec une contrainte. Qu'en pensez-vous ?

Du fait de cette contrainte, Paul est seulement obligé d’avoir une licence, mais cela n’empêche en rien que le ticket 314116 évoqué précédemment puisse faire l’objet de deux demandes, une de la part de Raoul et une autre de la part de Paul. En fait, l’association REQUEST entre TI_ticket et USL_user_license est manifestement redondance et doit disparaître, à moins qu’on ne me prouve le contraire… En tout cas, avec la disparition de REQUEST, cette fois-ci le ticket a au moins un et plus un demandeur, Raoul ou Paul, mais jamais les deux (les associations parties prenantes, nécessaires et suffisantes sont évidemment CONCERNS et OWN).

[laurentSc]

Je vous suis (j'espère) ; mais si je retire l'association request, Paul ne peut plus exister. Mais les associations reliées à UST_user_ticket sont différentes de celles reliées à USL_user_license car on a pas les mêmes informations concernant les propriétaires de licences (comme Raoul) et les demandeurs de ticket (comme Paul), d'où la nécessité (selon moi) d'avoir 2 classes de user. Si je retire l'association request, je vois pas comment modéliser cela ?

[escartefigue]

Dans la dernière version de MCD les deux sous-types sont dépourvus d'attributs, ils ne se justifient donc pas de ce point de vue.
On a bien les mêmes informations : celles de la classe parente (le sur-type)

[laurentSc]

Je ne vois pas comment faire autrement car les associations reliées aux 2 sous-types sont complètement différentes...

[laurentSc]

Dans la dernière version de MCD les deux sous-types sont dépourvus d'attributs, ils ne se justifient donc pas de ce point de vue.
On a bien les mêmes informations : celles de la classe parente (le sur-type)

C'était pour éviter d'avoir des FK nulles (et beaucoup). Serait-ce un mauvais choix ?