Discussion :

[pfortin]

Bonjour,

Je tente une explication en espérant la confirmation du maître.

En regardant le script de fsmrel, je m'aperçois que néni. La cle primaire de la table DetailOper est définie sur OperdID et DetailId.
J'ai fais la modification, mais je ne comprends pas trop pourquoi.
Quelle aurait été la conséquence si je n'avais gardé comme clé primaire que DetailId ?
Au passage, j'ai définie comme clé unique le DetailId avec l'auto incrémentation. Ceci explique que les résultats obtenus à partir du code ci-dessous différent des résultats de fsmrel (au niveau du DetailId).

Il faut bien distinguer votre DetailId autoincrémenté (unique dans toute la table) du numéro d'ordre (unique pour une opération mais pas dans la table) de fsmrel.

La clé primaire de fsmrel correspond à un identifiant principal au niveau conceptuel qui est composé d'informations compréhensibles par l'utilisateur.
En l'occurrence, il s'agit du numéro d'ordre (DetailId) de la ligne ventilée dans l'opération (OperId).

Votre clé est une clé technique unique par construction et non porteuse d'information.
En principe, elle ne doit donc pas apparaitre dans les MCD qui ne traitent que des informations.

Les 2 sont tout à fait conciliables dans une même table physique :
votre colonne sera clé primaire (CONSTRAINT PRIMARY KEY), les 2 autres formeront un clé unique (CONSTRAINT UNIQUE KEY).
J'ai moi-même cette habitude mais fsmrel vous expliquera probablement que dans le cadre de cette relation, votre clé technique inutile doit être sacrifiée au nom du Rasoir d'Ockham.
Je lui laisse bien volontiers le dernier mot sur le sujet.

[fsmrel]

Bonjour,

je souhaite avoir la liste complète des couples catégorie--sous-catégorie

Le résultat que vous produisez ne répond pas à votre souhait. Votre requête ne permet pas d’avoir la liste complète, car il manque — entre autres — les couples <Loisirs-Livres>, <Alimentation-Boucherie>, etc.

La liste complète peut être obtenue avec la requête suivante :

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
SELECT    x.CategNom AS Catégorie, '----' AS Sous_Catégorie
FROM      CategorieNoeud AS x
WHERE     NOT EXISTS
          (SELECT ''
           FROM   SousCategorie AS y
           WHERE  x.CategNoeudId = y.SousCategId)
   AND    NOT EXISTS
          (SELECT ''
           FROM   SousCategorie AS y
           WHERE  x.CategNoeudId = y.CategParenteId)
UNION     
SELECT z.CategNom, x.CategNom
FROM      CategorieNoeud AS x 
            INNER JOIN SousCategorie AS y
                 ON x.CategNoeudId = y.SousCategId
            INNER Join CategorieNoeud AS z
                 ON y.CategParenteId = z.CategNoeudId ;

Le 1er NOT EXISTS permet de savoir quelles catégories ne font pas partie des sous-catégories. Le 2e NOT EXISTS permet de ne retenir que celles qui ne sont jamais référencées par des sous-catégories.

Le 2e SELECT diffère du vôtre en ce sens qu’il permet d’obtenir chaque sous-catégorie d’une catégorie.

[pfortin]

Code :

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

1
2
3
4
5
SELECT c1.CategNom CATEGORIE, COALESCE(c2.CategNom, '----') 'Sous CATEGORIE'
FROM CategorieNoeud AS c1
LEFT OUTER JOIN SousCategorie AS sc  ON sc.CategParenteId = c1.CategNoeudId
LEFT OUTER JOIN CategorieNoeud AS c2 ON sc.SousCategId = c2.CategNoeudId

ou

Code :

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

1
2
3
4
5
6
SELECT c1.CategNom CATEGORIE, COALESCE(c2.CategNom, '----') 'Sous CATEGORIE'
FROM CategorieNoeud AS c1 
LEFT OUTER JOIN  (SousCategorie AS  scr 
INNER JOIN CategorieNoeud AS c2 ON scr.SousCategId = c2.CategNoeudId) 
 AS  sc  ON sc.CategParenteId = c1.CategNoeudId

fonctionneraient aussi. Mais, le bonhomme NULL y figurant de manière contrôlée, sont-elles décentes au sens du modèle relationnel ?

[tavarlindar]

Tout d'abord bonjour pfortin, fsmrel et merci pour vos réponses

Concernant ma requête et le résultat affiche précédemment, j'ai fais une copie d'écran avant de saisir toutes les données
Donc le résultat est bien ce que je souhaite :


La proposition de fsmrel ne donne pas le résultat souhaité, mais le code est néanmoins intéressant. je souhaite avoir toutes les catégories et sous catégories avec leur catégorie mère. Sans doute que ma première formulation n'était pas suffisamment claire.

La première proposition de pfortin n'est pas adaptée : on retrouve des doublons : ex Pharmacie non remboursée, et on liste des sous-catégorie dans la colonne CATEGORIE.


La seconde proposition ne fonctionne pas chez moi.

[pfortin]

La première proposition de pfortin n'est pas adaptée : on retrouve des doublons : ex Pharmacie non remboursée, et on liste des sous-catégorie dans la colonne CATEGORIE.

Ca m'apprendra à envoyer sans avoir testé.

La seconde proposition ne fonctionne pas chez moi.

Elle aurait produit exactement le même résultat inadéquat.

[fsmrel]

Bonjour,


Votre demande initiale était la suivante :

je souhaite avoir la liste complète des couples catégorie--sous-catégorie

La requête SQL que je vous avais proposée est celle qui est conforme à la réponse à cette demande.

Mais ensuite, vous la reformulez ainsi :

je souhaite avoir toutes les catégories et sous catégories avec leur catégorie mère. Sans doute que ma première formulation n'était pas suffisamment claire.

D’après votre copie d’écran (un bon dessin...), j’interprète la chose ainsi :

Présenter chacune des catégories (sous-entendu : une catégorie n'est jamais sous-catégorie). Présenter simultanément chaque couple (catégorie, sous-catégorie) quand il existe.

Vos deux formulations ne sont donc pas équivalentes, la première étant plus restrictive. Pour répondre à la seconde, il suffit d’aménager la requête que SQL j’avais proposée, en y supprimant le 2e NOT EXISTS :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT    x.CategNom AS Catégorie, '----' AS Sous_Catégorie
FROM      CategorieNoeud AS x
WHERE     NOT EXISTS
          (SELECT ''
           FROM   SousCategorie AS y
           WHERE  x.CategNoeudId = y.SousCategId)
UNION     
SELECT    z.CategNom, x.CategNom
FROM      CategorieNoeud AS x 
            INNER JOIN SousCategorie AS y
                 ON x.CategNoeudId = y.SousCategId
            INNER Join CategorieNoeud AS z
                 ON y.CategParenteId = z.CategNoeudId ;

[fsmrel]

A propos de la requête, à supposer qu'elle fournisse les résultats attendus :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT    x.CategNom AS Catégorie, '----' AS Sous_Catégorie
FROM      CategorieNoeud AS x
WHERE     NOT EXISTS
          (SELECT ''
           FROM   SousCategorie AS y
           WHERE  x.CategNoeudId = y.SousCategId)
UNION     
SELECT    z.CategNom, x.CategNom
FROM      CategorieNoeud AS x 
            INNER JOIN SousCategorie AS y
                 ON x.CategNoeudId = y.SousCategId
            INNER JOIN CategorieNoeud AS z
                 ON y.CategParenteId = z.CategNoeudId ;

D’un point de vue académique, la présence de NOT EXISTS peut paraître incongrue (quantification existentielle immergée dans un ensemble d’opérateurs relationnels). On peut préférer utiliser l’opérateur DIFFERENCE (MINUS ou EXCEPT selon les dialectes) :

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
SELECT y.CategNom, '----' Sous_Catégorie
FROM
       (SELECT CategNoeudId
        FROM   CategorieNoeud
        EXCEPT 
        SELECT SousCategId
        FROM   SousCategorie) AS x
    INNER JOIN CategorieNoeud AS y
               ON x.CategNoeudId = y.CategNoeudId
UNION     
SELECT    z.CategNom, x.CategNom
FROM      CategorieNoeud AS x 
            INNER JOIN SousCategorie AS y
                 ON x.CategNoeudId = y.SousCategId
            INNER Join CategorieNoeud AS z
                 ON y.CategParenteId = z.CategNoeudId ;

[fsmrel]

Nuances...

La clé primaire de fsmrel correspond à un identifiant principal au niveau conceptuel qui est composé d'informations compréhensibles par l'utilisateur.
En l'occurrence, il s'agit du numéro d'ordre (DetailId) de la ligne ventilée dans l'opération (OperId).

S’il se trouve que la clé primaire de la table Operation est lisible, c’est fortuit, elle n’a aucune signification et n’a pas pour objet d’être visible par l’utilisateur. A son intention, je définirai pour ma part un attribut OperNumero, clé alternative :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Operation 
(
       OperId               INT           NOT NULL
     , OperLibelle          VARCHAR(48)   NOT NULL
     , OperDateValeur       Char(10)      NOT NULL
     , CompteId             INT           NOT NULL
     , OperNumero           INT           NOT NULL       /* clé alternative */
   , CONSTRAINT Operation_PK PRIMARY KEY  (OperId)
   , CONSTRAINT Operation_AK UNIQUE  (OperNumero)
   , CONSTRAINT Compte_FK FOREIGN KEY (CompteId)
          REFERENCES Compte (CompteId)) ;

Ce qui vaut pour la table Operation vaut évidemment pour les tables OprSansVentil et DetailOper, à savoir que l’attribut OperId n’est pas pour l’utilisateur. Il en va de même pour l’attribut DetailId. Si celui-ci est numéroté relativement à OperId, cela correspond à une technique dont on se servait jadis, quand l’auto-incrémentation n’existait pas. Par exemple, si l’utilisateur crée un détail pour l’opération 1000, l’INSERT correspondant pourrait ressembler à ceci (améliorable) :

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
INSERT INTO DetailOper (OperId, DetailId, CategId, DetailMontant, DetailNote)
       SELECT x.OperId, 1, 12, -100.00, 'à la douzaine)'
       FROM   Operation AS x 
       WHERE  NOT EXISTS
             (SELECT ''
              FROM   DetailOper AS y
              WHERE  x.OperId = y.OperId)
         AND  x.OperNumero = 1000
      UNION 
       SELECT x.OperId, Max(y.DetailId)+1, 12, -100.00, 'à la douzaine'
       FROM   Operation AS x JOIN DetailOper AS y
              ON x.OperId = y.OperId
       WHERE  x.OperNumero = 1000
       GROUP BY x.OperId ;

Où l’on observe l’utilisation de l’opérateur MAX que l’on incrémente soi-même. Évidemment, les choses se simplifient avec les possibilités d’auto-incrémentation offertes aujourd’hui par les SGBD. Mais en tout état de cause, la clé primaire de la table DetailOper est inchangée : (OperId, DetailId). En effet, ça n’est pas une facilité offerte par un SGBD qui altérera l’essence d’un objet (l'auto-incrémentation de l'attribut DetailId en l'occurrence). La structure de la table DetailOper reste la même :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE DetailOper (
       OperId               INT                  NOT NULL
     , DetailId             INT                  NOT NULL
     , CategId              INT                  NOT NULL
     , DetailMontant        INT                  NOT NULL
     , DetailNote           Varchar(16)          NOT NULL 
   , CONSTRAINT DetailOper_PK PRIMARY KEY  (OperId, DetailId)
   , CONSTRAINT DetailOper_Operation_1 FOREIGN KEY (OperId)
      REFERENCES Operation (OperId)
   , CONSTRAINT DetailOper_CategorieNoeud_2 FOREIGN KEY (CategId)
      REFERENCES CategorieNoeud (CategNoeudId) ) ;

Que les valeurs prises par l’attribut DetailId diffèrent selon la technique, pas de problème puisque cet attribut ne concerne pas l’utilisateur. S’il a besoin de numéroter ses détails, on lui définira un attribut as-hoc et l'on pourra se servir de la technique du « MAX + 1 ».

[fsmrel]

A propos de l’Outer Join

[...] fonctionneraient aussi. Mais, le bonhomme NULL y figurant de manière contrôlée, sont-elles décentes au sens du modèle relationnel ?

Une réponse de Normand : Oui, dans mesure où le résultat est une relation au sens du Modèle Relationnel de Données. Non, parce que le SGBD résout l’Outer join en s’appuyant sur une logique trivalente dont on sait qu’elle n’est pas valide en ce qui concerne le conditionnel (si... alors) , le biconditionnel (si et seulement si) et autres joyeusetés de la logique : voyez les commentaires dont je vous ai gratifiés à ce sujet ainsi que MacFky58. Quand on voit à quel point se plantent ceux qui font la norme SQL (revoyez ce que j'ai écrit à la fin des commentaires...)

[CinePhil]

Votre clé est une clé technique unique par construction et non porteuse d'information.
En principe, elle ne doit donc pas apparaitre dans les MCD qui ne traitent que des informations.

Sauf que les outils modernes génèrent automatiquement le MLD à partir du MCD puis le script SQL de création de la BDD ou même sa génération directe par connexion au SGBD.

Il est donc préférable de faire figurer ces clés techniques dès le MCD, sous peine de devoir corriger le MLD et/ou le script SQL à la main. S'il y a 3 tables, c'est assez rapide, mais s'il y en a plusieurs dizaines avec un paquet de clés étrangères, c'est très fastidieux et casse-gueule !

[pfortin]

Nuances...
...
Il en va de même pour l’attribut DetailId. Si celui-ci est numéroté relativement à OperId, cela correspond à une technique dont on se servait jadis, quand l’auto-incrémentation n’existait pas.
...
Où l’on observe l’utilisation de l’opérateur MAX que l’on incrémente soi-même. Évidemment, les choses se simplifient avec les possibilités d’auto-incrémentation offertes aujourd’hui par les SGBD. Mais en tout état de cause, la clé primaire de la table DetailOper est inchangée : (OperId, DetailId). En effet, ça n’est pas une facilité offerte par un SGBD qui altérera l’essence d’un objet (l'auto-incrémentation de l'attribut DetailId en l'occurrence). La structure de la table DetailOper reste la même :
...
Que les valeurs prises par l’attribut DetailId diffèrent selon la technique, pas de problème puisque cet attribut ne concerne pas l’utilisateur. S’il a besoin de numéroter ses détails, on lui définira un attribut as-hoc et l'on pourra se servir de la technique du « MAX + 1 ».

Je vois ce que vous voulez dire.
Néanmoins, l'usage de l'auto-incrémentation me semble loin d'être neutre et "tue" quelque peu toute autre information associée dans un identifiant.
Si je l'entends conceptuellement, le résultat au niveau physique ne cesse de me choquer car on se retrouve, de fait, avec une clé réductible...
Les interprétations conceptuelle et physique du précepte "The key, the whole key, nothing but the key" (Chris Date) divergent-elles ? Bon, d'accord, j'abuse un peu là...
Mais ce 'domaine' (type de donnée) n'a pas son pareil pour perturber la modélisation.

[Parenthèse technique]
je suis personnellement allergique à l'auto-incrementation autant qu'à la technique du MAX+1.
Elles tiennent extrêmement mal dans un contexte de réplication avec mise à jour distribuées...
Je leur préfère, et de loin, l'usage d'un GUID y compris sur les associations.
[/Parenthèse technique]

voyez les commentaires dont je vous ai gratifiés à ce sujet ainsi que MacFky58. Quand on voit à quel point se plantent ceux qui font la norme SQL (revoyez ce que j'ai écrit à la fin des commentaires...)

J'avais bien lu ces références. Ce sont justement elles qui m'ont inquiété sur le bien fondé de l'usage du OUTER JOIN. D'où ma question...
J'en userai donc avec encore plus de parcimonie. Merci.

Sauf que les outils modernes génèrent automatiquement le MLD à partir du MCD puis le script SQL de création de la BDD ou même sa génération directe par connexion au SGBD.

Il est donc préférable de faire figurer ces clés techniques dès le MCD, sous peine de devoir corriger le MLD et/ou le script SQL à la main. S'il y a 3 tables, c'est assez rapide, mais s'il y en a plusieurs dizaines avec un paquet de clés étrangères, c'est très fastidieux et casse-gueule !

J'en sais quelque chose, hélas. Mes round-trips sont un enfer.
Mais je vais peut-être lancer une discussion sur le sujet.

[fsmrel]

Votre clé est une clé technique unique par construction et non porteuse d'information.
En principe, elle ne doit donc pas apparaitre dans les MCD qui ne traitent que des informations.

Il est donc préférable de faire figurer ces clés techniques dès le MCD, sous peine de devoir corriger le MLD et/ou le script SQL à la main.

C’est tellement évident que je n’avais même pas prêté attention à cette observation concernant l’occultation des clés « techniques ». Heureusement que CinePhil lit bien tout (N. B. On remplacera « préférable » par « obligatoire »).

Au-delà du problème de leur ajout à la main, la règle veut en effet que les identifiants « techniques » figurent dans le MCD. Pour la nième + 1 fois, je cite Yves Tabourier (De l’autre côté de MERISE (Les Éditions d’organisation, 1986) :

« ... la fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Ainsi l’entité-type Operation est bien porteuse d’un identifiant technique, invariant, non significatif, inodore et sans saveur, OperId (utilisé au niveau relationnel pour les contraintes d’entité et d’intégrité référentielle, pour les opérations de jointure, etc.) d’où la nécessité de faire figurer aussi un identifiant « naturel » alternatif, accessible et modifiable par l’utilisateur puisque celui-ci n’a pas connaissance de l’identifiant technique.



Pour mémoire, je rappelle le code SQL correspondant :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE Operation 
(
       OperId               INT           NOT NULL
     , OperLibelle          VARCHAR(48)   NOT NULL
     , OperDateValeur       Char(10)      NOT NULL
     , CompteId             INT           NOT NULL
     , OperNumero           INT           NOT NULL       /* clé alternative */
   , CONSTRAINT Operation_PK PRIMARY KEY  (OperId)
   , CONSTRAINT Operation_AK UNIQUE  (OperNumero)
   , CONSTRAINT Compte_FK FOREIGN KEY (CompteId)
          REFERENCES Compte (CompteId)) ;

Il n’y a que dans les systèmes orientés objet que l’identifiant (Object ID) est totalement caché, que ce soit au niveau conceptuel ou au niveau du SGBD. Ceci étant formellement prohibé par le Modèle Relationnel de Données, le type REF faisant partie de la norme SQL est condamné sans appel. Depuis sa naissance, le Modèle Relationnel repose sur le principe suivant (Le principe de l’information) : toute l’information contenue dans la base de données est représentée de façon uniforme, à savoir sous forme de valeurs explicites dans les colonnes et lignes des tables.

Autrement dit, on ne joue pas au pointer-chasing, réminiscence des systèmes pré-relationnels.

[JPhi33]

Bonsoir à tous,

Au-delà du problème de leur ajout à la main, la règle veut en effet que les identifiants « techniques » figurent dans le MCD.

Au risque de me fâcher avec tout le monde, j'ai un avis complètement opposé au vôtre. Je ne sais pas de quelle règle vous voulez parler exactement mais celle que je connais, et pratique, depuis des lustres interdit formellement le non significatif dans un MCD qui, rappelons-le (mais est-ce bien utile ?), modélise le QUOI et s'attache à la description sémantique de l'aspect statique du SI.

Pour la nième + 1 fois, je cite Yves Tabourier (De l’autre côté de MERISE (Les Éditions d’organisation, 1986) :

« ... la fonction d’une propriété est de décrire les objets (et les rencontres), alors que l’identifiant ne décrit rien. Son rôle fondamental est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques.
L’expérience montre d’ailleurs que l’usage des “identifiants significatifs” (ou “codes significatifs”) a pu provoquer des dégâts tellement coûteux que la sagesse est d’éviter avec le plus grand soin de construire des identifiants décrivant les objets ou, pis encore, leurs liens avec d’autres objets... »

Moi aussi, je suis en total accord avec cette citation d'Yves TABOURIER : l'identifiant ne décrit rien. Pour cette seule raison, il n'a pas droit de cité dans le MCD. Mais, à ce que je lis (je n'ai pas le texte complet), Yves TABOURIER ne dit pas que l'identifiant doit figurer dans le MCD.



La conséquence, à mon sens, est double (dans le cadre d'une démarche s'appuyant sur Merise).

1. Les identifiants, lorsqu'ils sont nécessaires, doivent être ajoutés à l'étape Logique, voire à l'étape Physique. Les AGL de modélisation ne sont que des outils. Ceux qui pensent pouvoir générer entièrement leur base de donnée après avoir dessiné 3 carrés et 2 ovales se trompent.

2. S'agissant des grands Systèmes d'Information, on ne peut se passer du binôme Concepteur/DBA. Chacun a son rôle à jouer. Certains concepteurs pensent pouvoir se passer du DBA et, n'ayant pas la connaissance suffisante de ce qui se passe "sous le capot", conduisent à des catastrophes, si rigoureux et pertinent que soit leur MCD. L'inverse est vrai aussi (certains DBA pensent... etc.)

[fsmrel]

Bonsoir,

Au-delà du problème de leur ajout à la main, la règle veut en effet que les identifiants « techniques » figurent dans le MCD.

Au risque de me fâcher avec tout le monde, j'ai un avis complètement opposé au vôtre. Je ne sais pas de quelle règle vous voulez parler exactement mais celle que je connais, et pratique, depuis des lustres interdit formellement le non significatif dans un MCD qui, rappelons-le (mais est-ce bien utile ?), modélise le QUOI et s'attache à la description sémantique de l'aspect statique du SI.

[...] à ce que je lis (je n'ai pas le texte complet), Yves TABOURIER ne dit pas que l'identifiant doit figurer dans le MCD.

Quand je parle d’identifiant technique, je parle de l’identifiant au sens de Tabourier, c'est-à-dire non significatif, inodore et sans saveur. Ainsi, quand je définis une entité-type CLIENT, je n’identifie pas celle-ci par le numéro SIRET. Que celui-ci fasse l’objet d’un identifiant alternatif, c’est la moindre des choses, mais c’est tout. Sur la photo que j’ai faite de la description des « objets » (Cf. [1] page 79), concernant l’entité-type CLIENT (ou individu type chez Tabourier), l’attribut N° CLIENT représenté est identifiant (il est souligné).

Si un client a une raison sociale et un N° SIRET qu’il me communique, ce n’est pas lui qui se dote de son numéro de client, c’est « mon » système qui le fait, autrement dit l’attribut N° CLIENT est technique (ou plutôt artificiel, par opposition à naturel). Cet attribut ne correspond pas à une propriété (naturelle) de l’entité-type, mais il figure cependant en bonne place dans le rectangle (donc dans le MCD) au titre d’identifiant.

A votre intention j’ai aussi photographié la page 80 :

Pour sa part, D. Nanci écrit (Cf. [2]) :

Le choix d’un identifiant est un problème délicat. On peut opter pour :
– Une propriété « naturelle » , par exemple le nom d’un pays pour l’entité pays ;
– une propriété « artificielle », inventée par le concepteur pour identifier l’entité qu’il vient de concevoir (tous les numéros, références, codes, etc. en sont l’illustration) ;
– ...

Et il fait figurer lui aussi les identifiants (soulignés) dans les rectangles.

Les identifiants, lorsqu'ils sont nécessaires, doivent être ajoutés à l'étape Logique, voire à l'étape Physique.

Quand un identifiant est-il ou non nécessaire ?

Pour Rochfeld (Cf. [3]), on lit page 188, quand il parle du Modèle Relationnel de Données et des relations (au sens relationnel) :

A toute relation correspond une clé, simple ou composée, qui repère les différents tuples...

Et il écrit en gras :

Cette notion est strictement équivalente à la notion d’identifiant.

Je ne suis pas particulièrement d'accord avec Rochfeld quant à cette équivalence car, contrairement à ce qu'il croit, il ne connaît pas le Modèle Relationnel de Données. Nonobstant, puisque les clés sont nécessaires, les identifiants le sont aussi si j'en crois Rochfeld.

En attendant, en tant que DBA, je ne me vois pas compléter manuellement un MLD de 1500 tables dépourvues de clé (primaire), alors que pour un concepteur, un attribut de plus ou de moins dans une entité-type, ça ne coûte rien.

S'agissant des grands Systèmes d'Information, on ne peut se passer du binôme Concepteur/DBA. Chacun a son rôle à jouer. Certains concepteurs pensent pouvoir se passer du DBA et, n'ayant pas la connaissance suffisante de ce qui se passe "sous le capot", conduisent à des catastrophes, si rigoureux et pertinent que soit leur MCD. L'inverse est vrai aussi (certains DBA pensent... etc.)

Je ne vous le fais pas dire. J’ai passé mon temps dans les grandes entreprises à binômer avec les concepteurs dès le début de la conception (l'idéal), ou à refuser des MCD propres à faire capoter des applications (mais souvent au motif que ces MCD étaient incomplets), et ce parfois 6 mois après le départ des concepteurs de la SSII X ou Y. (Par communication privée) je pourrais vous en parler pendant des heures.


[1] Y. Tabourier. De l'autre côté de MERISE. (Les Éditions d'organisation. 1986).
[2] D. Nanci, B. Espinasse. Ingénierie des systèmes d’information : Merise deuxième génération (Troisième édition. 1996).
[3] A. Rochfeld et J. Morejon. La Méthode MERISE. Tome 3. Gamme opératoire. (Les Éditions d'organisation. 1989).

[tavarlindar]

Bonjour,
J'ai lu attentivement vos commentaires. Intéressant !

Je reviens sur la requête de fsmrel :

Code :

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
SELECT y.CategNom, '----' Sous_Categorie
FROM
       (SELECT CategNoeudId
        FROM   CategorieNoeud
        EXCEPT 
        SELECT SousCategId
        FROM   SousCategorie) AS x
    INNER JOIN CategorieNoeud AS y
               ON x.CategNoeudId = y.CategNoeudId
UNION     
SELECT    z.CategNom, x.CategNom
FROM      CategorieNoeud AS x 
            INNER JOIN SousCategorie AS y
                 ON x.CategNoeudId = y.SousCategId
            INNER JOIN CategorieNoeud AS z
                 ON y.CategParenteId = z.CategNoeudId ;

Elle ne fonctionne pas chez moi.Après recherche, et sous réserve d'avoir bien chercher, l'opérateur EXCEPT n'est pas implémenté dans Mysql (version 5.0).

J'ai noté avec intérêt la notion de MAX+1, ca peut servir.

Pour ce qui est de mettre ou pas au niveau du MCD l'identifiant technique, je me pose même pas la question. Je le mets systématiquement ou presque. Ou presque sinon cela ne serait pas drôle.

Évidemment, lorsqu'au niveau du MCD on modélise une relation (O,N) (0,N) entre deux entités, il n'y a pas à ce stade de notion d'identifiant pour la troisième table résultante qui apparaitra au niveau du MLD. Au niveau du MLD, je ne m'amuse pas non plus à crée un autre identifiant dans la table résultante.

Vous le verrez plus tard (vous n'êtes pas encore débarrassé de moi ), j'ai une table taux de change. Cette table taux de change a pour identifiant une date (clé primaire) et d'autres attributs pour stoker les différentes parités : euro_usd, euro_gbp, etc. Je n'ai pas créé un identifiant technique, car pour une date donnée, j'ai un est un seul taux de change (ex : euro/£) possible qui doit être pris en compte par l'application. Créer un identifiant technique aurait laisser la porte ouverte à : j'autorise pour une même date plusieurs valeurs possibles pour une parité donnée, ce qui est tout a fait conforme à la réalité. Chaque minutes, les cours évoluent. J'aurais très bien pu créer un identifiant technique et interdire que pour une même date, on se retrouve avec tous les cours du jour pour une parité donnée, mais cela aurait compliqué le Smilblick. Introduire au niveau du MCD, un identifiant technique aurait pu au niveau du MLD rajouté une certaine confusion.
Lorsque je suis absolument sûre qu'un attribut est unique par essence, j'en fais ma clé primaire, l' identifiant principal de ma table. Comme dans 99% des cas , on a pas d'attribut dont on peut dire à 1000% qu'il sera unique, pour ma part je crée un identifiant technique qui sera ma clé primaire.
Par ailleurs, la finalité d'un MCD étant de déboucher sur un MLD et MPD, je trouve que mettre un identifiant au niveau du MCD aide à la compréhension. Avant même d'avoir le MLD, on comprend mieux le MCD.

Au-delà de la théorie ou des différentes approches ou écoles de pensées, un MCD est un outil. Ce qui compte c'est que la personne qui l'utilise, l'utilise bien pour obtenir le résultat voulu. Au de-là des règles de l'art, tout le monde n'utilise pas un outil de la même façon.
Tout le monde sait à quoi sert un marteau. Maintenant si vous voulez scier une planche en deux avec, libre à vous de le faire. Evidemment, la probabilité que votre planche soit droite après est ... quasi nulle. Maintenant, il y a des gens qui même en utillant l'outil adapté (une scie !) ne parviendront jamais à avoir un bout planche droit (2 en toute logique !). Alors si vous avec votre marteau, vous y arrivez, utilisez le marteau.
Tout cela pour dire que ce qui compte, ce n'est pas le mode d'emploi de l'outil, mais ce que vous en faite. Evidemment, le mode d'emploi ça aide, mais si au final vous passez outre et que le résulat est là ...

Donc que certains mettent systèmatiquement ou pas un identifiant technique au niveau du MCD (parce que ceci ou cela), ce qui compte c'est qu'un jour un utilisateur ne vienne pas poser la question : à comment fait-on ca. Et là, après recherche(s) on s'aperçoit que la structure de la base de données ne permet pas de répondre à son besoin. La faute à un MCD mal conçu ? certainnement que oui, mais à cause d'un identifiant technique mis en trop au stade du MCD ? J'en doute fort.

Quant à créer un identifiant naturel ou pas ... c'est du cas par cas. Pour ce qui est de la table operation, pour le moment, je n'en vois pas l'utilité.

Voilà ce que j'ai à dire à ce sujet !

et encore merci pour vos réponses.

Message à pfortin : je prèfère une "mauvaise" réponse, que pas de réponse. C'est vrai, c'est préférable de tester la solution que l'on soumet sinon l'autre personne à l'autre bout, se pose 1000 questions. Maintenant, toute réponse peut être un déclic pour trouver la solution que l'on recherche.

Bien à vous Tous,

Tavar

[fsmrel]

Ave,

Néanmoins, l'usage de l'auto-incrémentation me semble loin d'être neutre et "tue" quelque peu toute autre information associée dans un identifiant.

Relisez ce que dit Tabourier : l’identifiant artificiel (« technique », primaire, système, ...) n’a pas à être porteur d’information. Seuls les autres identifiants peuvent véhiculer de l’information, c'est-à-dire les identifiants alternatifs, naturels, « non techniques » connus de l’utilisateur, tels que le numéro d’opération que j’ai ajouté dans l’entité-type Opération, le numéro Siret des entreprises, le numéro de Sécu des personnes, le matricule de l’employé, le pseudonyme d’un membre de Developpez.com, etc.

Si je l'entends conceptuellement, le résultat au niveau physique ne cesse de me choquer car on se retrouve, de fait, avec une clé réductible...

Les SGBD ne proposent pas tous que des auto-incréments absolus. Voyez par exemple le cas des tables MyIsam de MySQL.

Quoi qu’il en soit, le fait que l’auto-incrémentation rende une clé réductible n’a aucune incidence du point de vue logique. En effet, selon le Modèle Relationnel de Données, DetailOper est une relvar (variable relationnelle) qui prend des valeurs qui sont des relations. Pour illustrer, considérons les règles de gestion au niveau conceptuel :

– Une opération comporte plusieurs détails.
– Un détail d’une opération fait référence exactement à une catégorie.
– Un détail d’une opération est porteur d’un montant et d’une note.

Au niveau relationnel, ces règles donnent lieu à l’ensemble S de dépendances fonctionnelles non triviales suivant, associé à la table DetailOper :

{{OperId, DetailId} → {CategId}, {OperId, DetailId} → {DetailMontant}, {OperId, DetailId} → {DetailNote}}

En conséquence, la relvar DetailOper n’a que la paire {OperId, DetailId} pour clé candidate, et elle respecte la BCNF.

Quand vous dites que la clé est réductible, c’est que pour vous l’ensemble S serait à remplacer par l’ensemble S2 suivant :

{{DetailId} → {OperId}, {DetailId} → {CategId}, {DetailId} → {DetailMontant}, {DetailId} → {DetailNote}}

Mais ceci n’est pas la conséquence des règles de gestion de données : c’est seulement la conséquence d’un artifice proposé par un certain SGBD, à savoir l’auto-incrémentation, qui fait que si l’on examine le contenu des relations, là où l’on s’attend à trouver a priori plus d’une fois la valeur 1 pour la colonne DetailId de la table DetailOper (c'est-à-dire autant de fois qu’il y a d’opérations), a posteriori on ne la retrouve en fait qu’une seule fois. La belle affaire ! les règles de gestion des données ne sont pas affectées, qui peut le plus peut le moins et c’est en fait l’inverse qui serait gênant, avoir affaire à une règle de gestion pouvant provoquer des doublons pour la paire {OperId, DetailId}.

Autrement dit, ne confondez pas modèle et implémentation. Un modèle définit les objets de façon abstraite, logique, indépendante, même chose pour les contraintes portant sur les objets (clés candidates, clés étrangères et tout le toutim). L’implémentation est la concrétisation du modèle : l’auto-incrémentation des clés, l’utilisation de l’opérateur MAX, d’un horodateur (timestamp), d’un GUID, bref toutes ces choses qui dépendent du SGBD et de sa technologie du moment n’ont pas à remettre en cause le niveau logique. En supposant que Tavar ait conçu son modèle en 1970, celui-ci n’aurait pas bougé, alors que sous le capot, son implémentation aurait pu être remaniée au fil des ans et des évolutions technologiques, mais de façon transparente pour les utilisateurs et les programmes. Un modèle est portable tel quel, une implémentation, non.

Les interprétations conceptuelle et physique du précepte "The key, the whole key, nothing but the key" (Chris Date) divergent-elles ?

Qu’entendez-vous par interprétation physique ? L’auteur du précepte reste au niveau du modèle et ne s’intéresse en aucune façon à l’implémentation. Je rappelle que le Modèle Relationnel de Données a à voir seulement avec la théorie des ensembles (finis) et la logique du 1er ordre, mais certainement pas avec les artifices, les trucs et astuces proposés par les SGBD pour générer des valeurs, fussent-elles uniques pour un attribut. Ainsi, du point de vue du MCD, la règle étant « Une opération est composée de détails », sa traduction littérale au niveau du MLD est légitime et légale ; la paire {OperId, DetailId} ne perd pas son statut de clé candidate par exemple parce qu’un beau jour quelqu’un a effectué un ALTER TABLE et codé un auto-incrément pour la colonne DetailId en remplacement d’une routine comportant un INSERT faisant du MAX + 1. Cela relève de l’indépendance logique.


En passant, la citation que vous faites concerne William Kent. Date s’est contenté de citer celui-ci et d’écrire à propos de sa communication A Simple Guide to Five Normal Forms in Relational Database Theory :

« The source of the following intuitively attractive characterization of "3NF" (more accurately, BCNF): Each attribute must represent a fact about the key, the whole key, and nothing but the key (slightly paraphrased) ».

Mais ce 'domaine' (type de donnée) n'a pas son pareil pour perturber la modélisation.

Plaît-il ? S’il s’agit des conséquences de l’auto-incrémentation, celle-ci n’a rien à voir avec le domaine (type) auquel l’attribut DetailId fait référence : un domaine est seulement un ensemble de valeurs au sens de la théorie des ensembles (donc invariant).

[Parenthèse technique]
je suis personnellement allergique à l'auto-incrementation autant qu'à la technique du MAX+1.
Elles tiennent extrêmement mal dans un contexte de réplication avec mise à jour distribuées...
Je leur préfère, et de loin, l'usage d'un GUID y compris sur les associations.
[/Parenthèse technique]

Sous le capot vous faites comme bon vous semble, à condition de ne pas remettre en cause les règles de gestion, donc l’ensemble S des dépendances fonctionnelles défini ci-dessus.
Je ne connais rien au GUID, mais si j'en crois Wikipedia, je note que ses valeurs ont pu être significatives à une époque, donc que sa sémantique a pu varier au fil du temps, ce qui n’est pas bon du tout pour les relations entre les clés candidates et les clés étrangères, sauf si désormais vous en avez la totale maîtrise. Pour l'anecdote, cf. l’histoire du ver Melissa.

[JPhi33]

Les identifiants, lorsqu'ils sont nécessaires, doivent être ajoutés à l'étape Logique, voire à l'étape Physique.

Quand un identifiant est-il ou non nécessaire ?

La phrase correcte est "Les identifiants artificiels..."

Veuillez pardonner cette imprécision due à mon imprégnation de l'article de TABOURIER dans lequel il oppose la propriété (signifiant) à l'identifiant (non signifiant).

[pfortin]

Ouille !

Relisez ce que dit Tabourier : l’identifiant artificiel (« technique », primaire, système, ...) n’a pas à être porteur d’information.

Nous sommes d'accord sur cette définition. J'ai encore commis une maladresse.
J'ai usé du mot "information" pour éviter d'écrire "champ" mais j'aurai dû utiliser le mot "attribut".

Pour illustrer, considérons les règles de gestion au niveau conceptuel :

– Une opération comporte plusieurs détails.
– Un détail d’une opération fait référence exactement à une catégorie.
– Un détail d’une opération est porteur d’un montant et d’une note.

Au niveau relationnel, ces règles donnent lieu à l’ensemble S de dépendances fonctionnelles non triviales suivant, associé à la table DetailOper :

{{OperId, DetailId} → {CategId}, {OperId, DetailId} → {DetailMontant}, {OperId, DetailId} → {DetailNote}}

En conséquence, la relvar DetailOper n’a que la paire {OperId, DetailId} pour clé candidate, et elle respecte la BCNF.

Quand vous dites que la clé est réductible, c’est que pour vous l’ensemble S serait à remplacer par l’ensemble S2 suivant :

{{DetailId} → {OperId}, {DetailId} → {CategId}, {DetailId} → {DetailMontant}, {DetailId} → {DetailNote}}

Mais ceci n’est pas la conséquence des règles de gestion de données : c’est seulement la conséquence d’un artifice proposé par un certain SGBD, à savoir l’auto-incrémentation, qui fait que si l’on examine le contenu des relations, là où l’on s’attend à trouver a priori plus d’une fois la valeur 1 pour la colonne DetailId de la table DetailOper (c'est-à-dire autant de fois qu’il y a d’opérations), a posteriori on ne la retrouve en fait qu’une seule fois. La belle affaire ! les règles de gestion des données ne sont pas affectées, qui peut le plus peut le moins et c’est en fait l’inverse qui serait gênant, avoir affaire à une règle de gestion pouvant provoquer des doublons pour la paire {OperId, DetailId}.

Autrement dit, ne confondez pas modèle et implémentation. Un modèle définit les objets de façon abstraite, logique, indépendante, même chose pour les contraintes portant sur les objets (clés candidates, clés étrangères et tout le toutim). L’implémentation est la concrétisation du modèle : l’auto-incrémentation des clés, l’utilisation de l’opérateur MAX, d’un horodateur (timestamp), d’un GUID, bref toutes ces choses qui dépendent du SGBD et de sa technologie du moment n’ont pas à remettre en cause le niveau logique. En supposant que Tavar ait conçu son modèle en 1970, celui-ci n’aurait pas bougé, alors que sous le capot, son implémentation aurait pu être remaniée au fil des ans et des évolutions technologiques, mais de façon transparente pour les utilisateurs et les programmes. Un modèle est portable tel quel, une implémentation, non.

Là, je me dois quand même de réagir !

Je vous accorde que l'artifice technique m'embrouille quelque peu mais la frontière est ténue entre
- Une opération comporte plusieurs détails ({OperId, DetailId})
et
- Un détail [d'opération] fait référence à une et une seule opération {DetailId} → {OperId}

Considérer Detail :
- soit comme une entité faible identifiée relativement à opération
- soit comme une entité identifiée techniquement mais uniquement avec DetailId et traiter OperId comme un attribut non-identifiant issu de la relation
me semble non pas équivalent, mais tout de même très proche.
En l'espèce, je pense qu'il s'agir surtout d'art de modéliser.

En passant, la citation que vous faites concerne William Kent. Date s’est contenté de citer celui-ci et d’écrire à propos de sa communication A Simple Guide to Five Normal Forms in Relational Database Theory :

« The source of the following intuitively attractive characterization of "3NF" (more accurately, BCNF): Each attribute must represent a fact about the key, the whole key, and nothing but the key (slightly paraphrased) ».

Important : Penser à corriger l'article de Wikipedia !
Vous aussi faites aussi une citation incomplète. J'avais pourtant pris la peine d'ajouter :

Bon, d'accord, j'abuse un peu là...

Je ne connais rien au GUID, mais si j'en crois Wikipedia, je note que ses valeurs ont pu être significatives à une époque, donc que sa sémantique a pu varier au fil du temps, ce qui n’est pas bon du tout pour les relations entre les clés candidates et les clés étrangères, sauf si désormais vous en avez la totale maîtrise. Pour l'anecdote, cf. l’histoire du ver Melissa.

Méfiez-vous de Wikipedia (cf plus haut) et des conclusions hâtives !
C'était une des implémentations du générateur de valeurs qui cachait un peu de sens en son sein.
La vocation de l'algorithme sous-jacent est de produire des valeurs aléatoires.

Mon aversion pour l'autoincrement tient uniquement au fait qu'il soit ordonné contrairement au GUID (pseudo-aléatoire).
Tout utilisateur (et nombre de développeurs aussi, hélas) qui l'aperçoit pense à un numéro d'ordre (signifiant).

Personne ne m'a jamais fais le coup avec un GUID (ou UUID).
On m'a même reproché récemment qu'ils soient illisibles dans Firebird...

comme aurait dit un personnage de Tex Avery.

[CinePhil]

Avec un GUID, le risque n'est pas nul d'avoir deux fois le même non ?
Alors qu'avec un auto-incrément, c'est certain !

Il me semble avoir déjà vu un débat quelque part sur notre forum entre les partisans de l'auto-incrément et les partisans d'un GUID ou autre du même genre.
Et je crois me souvenir que SQLPro avait pointé avec sa vigueur habituelle le fait qu'un GUID était plus gourmand et moins performant qu'un entier auto-incrémenté sur les gros volumes de données.

[fsmrel]

Bonsoir,

La phrase correcte est "Les identifiants artificiels..."

Veuillez pardonner cette imprécision due à mon imprégnation de l'article de TABOURIER dans lequel il oppose la propriété (signifiant) à l'identifiant (non signifiant).

Il est évident que vous êtes tout pardonné. De mon côté, quand je relis certains de mes messages, je constate que les imprécisions s’y ramassent à la pelle, bien plus que je ne le voudrais...

Pour autant, quand le lis dans l’ouvrage de référence [1], au paragraphe 5.7.3 « Règles de passage du modèle conceptuel des données au modèle logique dans une représentation relationnelle » :

Les individus sont transformés en relations au sens relationnel, l’identifiant de l’individu devient la clé primaire de la relation.

Si je vous suis, alors ce que dit [1] est trop simple et doit être remplacé par quelque chose ressemblant à ceci :

Les individus sont transformés en relations au sens relationnel. S’il est artificiel, l’identifiant d’un individu devient la clé primaire de la relation, sinon (s’il est naturel), alors il devient clé alternative, la définition et la mise en œuvre de la clé primaire restant à la charge du DBA.

Votre avis ? (On admettra que, comme c’est souvent le cas, les concepteurs ne son plus là et ne binômeront donc pas avec les DBA).


[1] Hubert Tardieu, Arnold Rochfeld, René Colletti. La méthode Merise, Tome 1 : Principes et outils (Les Éditions d’organisation, réimpression de juin 1991).