Discussion :

[CinePhil]

L'astuce pour éviter les ambiguïté est d'utiliser des suffixes pour chaque table et de reprendre systématiquement ces suffixes dans les noms des colonnes. Ainsi, deux colonnes ne peuvent avoir le même nom.

Avec ce système de nommage, suite au MCD et MLD du message #38 de fsmrel, au lieu de ceci :

E1 = (E1Id INT, E1A2 INT);
E11 = (#E1Id, E11A1 INT);
E12 = (#E1Id, E12A1 INT);
T1 = (T1Id INT, T1A2 INT);
A1 = (#(#E1Id), #T1Id);

On aurait cela :
E1 = (E1_Id INT, E1_A2 INT);
E11 = (E11_E1_Id, E11_A1 INT);
E12 = (E12_E1_Id, E12_A1 INT);
T1 = (T1_Id INT, T1_A2 INT);
A1 = (A1_E12_Id, A1_T1_Id);

Et là on voit bien que la première clé étrangère de A1 fait bien référence à E12 et non pas à E1.

Je ne sais pas si un logiciel de modélisation est capable de prendre en compte un tel standard de nommage pour la génération automatique des MLD mais ce serait super !

[Paprick]

Préfixer le nom de la clé étrangère avec le nom de la table est effectivement une bonne solution. Le seul petit inconvénient est que cela n'est pas toujours nécessaire (et donc parfois un peu lourd pour rien).
Cette solution va dans le même sens que suffixer la clé avec un nom de rôle sur la patte de spécialisation (rôle qui peut d'ailleurs reprendre le nom de la table !).
Je vais donc faire évoluer Looping en ce sens.

Concernant, le dernier MCD soumis par fsmrel, des exemples plus simples peuvent conduire à des références circulaires. Dans ce cas, la génération du code SQL correspondant est certes possible, mais en 2 instructions pour les tables concernées : un CREATE TABLE puis un ALTER TABLE quand la référence a été définie.
Je ne sais pas s'il est utile de lancer Looping dans ce genre de génération, sachant que, de toute façon, bon nombre d'éléments de type contrôle de bornes, contraintes d'intégrité interrelationnelles, etc... nécessiteront une adaptation du code SQL pour les modèles complexes.

Enfin, j'adore le principe des identifiants relatifs, mais en abuser en cascade comme le montre l'exemple de fsmrel ne me parait pas forcément approprié, surtout lorsqu'un Id, qui pourrait alors se suffire à lui-même, est défini dans la classe d'entités...

En tout cas, je trouve ces échanges particulièrement intéressants et constructifs !

[fsmrel]

 

Bonsoir,

Concernant, le dernier MCD soumis par fsmrel, des exemples plus simples peuvent conduire à des références circulaires.

Tout peut conduire à des références circulaires, y-compris les modèles les plus simples, mais dans mon exemple, tout un chacun à jeun aura beau écarquiller les yeux, il lui sera force de reconnaître que tel n’est pas le cas. Cet exemple n’est pas bien compliqué et pour la petite histoire ne fait que reprendre un tout petit bout de l’application « Prise de commandes » d’un très grand équipementier, application dédiée à la France puis promue au niveau européen puis mondial. Cela se passait fin des années quatre-vingts et j’avais une lourde responsabilité (validation des MCD, mise en oeuvre de DB2, performances, conseil auprès des chefs de projet, etc.).

Je ne sais pas s'il est utile de lancer Looping dans ce genre de génération

Les divers AGL de modélisation que j’ai secoués produisent le MLD et le code SQL attendus, sans état d’âme (OMS, AMC, DB-MAIN). Je ne vous suis pas...

sachant que, de toute façon, bon nombre d'éléments de type contrôle de bornes, contraintes d'intégrité interrelationnelles, etc...

Quel rapport avec le MCD que j’ai proposé et le MLD attendu ?!

Enfin, j'adore le principe des identifiants relatifs, mais en abuser en cascade comme le montre l'exemple de fsmrel ne me parait pas forcément approprié, surtout lorsqu'un Id, qui pourrait alors se suffire à lui-même

Well. Quittons un instant la dunette. Je prends ma casquette de DBA, celle que j’ai portée des décennies durant. Alors, d’expérience, je dis que votre suggestion d’id se suffisant à lui-même est exactement, ce qui convient dans la soute pour inhiber l’optimiseur d’un SGBD relationnel, donc dégrader les performances de l’application.

En réalité, pour que les choses se passent bien, les clients doivent être éparpillés sur le disque (sinon risques de contention), mais les commandes d’un client et ce qui s’y rattache doit être physiquement regroupé. En ce sens, identification relative plein pot, y-compris pour l’entité-type COMMANDE :

Supposons en effet que l'on ait besoin de savoir quels camions sont concernés par les livraisons chez le client de Siret 12345678900001 (voire pour l’ensemble des clients). Si on joue l'identification absolue, outre les problèmes de performance inhérents à un clustering qui s’évapore, on devra coder une requête SQL lourde, car faisant intervenir toutes les tables intermédiaires, à savoir Commande, LigneCde, Engagement et Livraison. Si on joue l’identification relative plein pot, alors l’optimiseur de DB2 for z/OS passe tout ça au rasoir d’Ockham en prenant un raccourci décisif, court-circuitant les tables Commande, LigneCde, Engagement, et il code tout simplement :

SELECT DISTINCT CAMION.camNom
FROM CLIENT JOIN LIVRAISON ON CLIENT.cliId = LIVRAISON.cliId 
            JOIN CAMION ON LIVRAISON.camId = CAMION.camId
WHERE cliSiret = '12345678900001' ;     

Il se trouve qu’à l’époque, mon entreprise (une SSII) s’était engagée sur les performances de l’application, avec des pénalités sévères en cas de non respect ès matière. Suite à des tests poussés, je conclus que le clustering sur la colonne cliId de chacune des tables citées s’avérait décisif : la mise en oeuvre d’un id se suffisant à lui-même eut été fort mal venue, rédhibitoire. Bon, cela se passait il y a trente ans, mais quand même.

Pour remonter de la soute et grimper à la dunette : pourquoi abuserai-je en cascade de l’identification relative ?

Je n’estime pas abuser. Sémantiquement parlant, une ligne de commande est une propriété multivaluée de la commande, l'engagement sur ligne de commande est une propriété multivaluée de la ligne de commande, la livraison est une propriété multivaluée de l’engagement. Au pays des relations le Relationland, on peut exprimer cela sous forme de RVA (Relation-Valued Attributes), attributs de type RELATION décrits dans Databases, Types, and The Relational Model: The Third Manifesto (C. J Date and Hugh Darwen) :

VAR COMMANDE BASE RELATION
{
    cdeId        INTEGER 
  , cdeNo        CHAR
  , cdeDate      DATE
  , cliId        INTEGER
  , LIGNE_CDE RELATION 
    {  ligneCdeId     INTEGER
     , quantite       INTEGER
     , artId          INTEGER
     , ENGAGEMENT RELATION 
       {  engId         INTEGER
        , quantite      INTEGER
        , LIVRAISON RELATION
          {  livraisonId    INTEGER 
           , quantite       INTEGER 
           , bordereauNo    CHAR 
           , livrDate       DATE
           , camionId       INTEGER
          }  
       }
    }
} 
KEY {cdeId}
KEY {cdeNo}
FOREIGN KEY {cdeId} REFERENCES CLIENT
;

Bien entendu, je n’utilise pas les RVA, ne serait-ce que parce que Tutorial D est muet quant aux clés correspondantes, d’où nécessité d’en passer par des déclarations de contraintes, et puis il y a la complexité des mises à jour !

Quoi qu’il en soit, quand on part de la soute et qu’on récupère les tables DB2 en production, leur rétroconception génère forcément un MCD identique à celui que j’ai fourni, donc avec identification relative à tous les étages.


Remarque

Vous me direz qu’une table telle que LIVRAISON comporte de nombreux attributs et que la clé en est alourdie d’autant : il est évident que le DBA peut agir en conséquence, en retouchant le code SQL de telle sorte que les clés primaires ne comportent que deux colonnes, dont bien sûr cliId en bonne place, c’est-à-dire en tête (clustering oblige) :

CREATE TABLE CLIENT 
(
     CliId             INT                  NOT NULL
   , CliNom            VARCHAR(48)          NOT NULL
   , CliSiret          CHAR(14)             NOT NULL
  , CONSTRAINT CLIENT_PK PRIMARY KEY  (CliId)
  , CONSTRAINT CLIENT_AK1 UNIQUE (CliSiret)
) 
;
CREATE TABLE ARTICLE 
(
     ArtId                INT                  NOT NULL
   , ArtNom               VARCHAR(48)          NOT NULL 
   , PrixHT               DECIMAL(16)          NOT NULL
 , CONSTRAINT ARTICLE_PK PRIMARY KEY (ArtId)
)
 ;
CREATE TABLE CAMION 
(
     CamionId            INT                  NOT NULL
   , CamImmat            VARCHAR(14)          NOT NULL
   , CamStatut           CHAR(2)              NOT NULL
 , CONSTRAINT CAMION_PK PRIMARY KEY (CamionId)
 , CONSTRAINT CAMION_AK1 UNIQUE (CamImmat)
) ;
CREATE TABLE COMMANDE 
(
     CliId                INT                  NOT NULL
   , CdeId                SMALLINT             NOT NULL
   , CdeNo                VARCHAR(12)          NOT NULL
   , CdeDate              CHAR(10)             NOT NULL
   , CdeStatut            CHAR(2)              NOT NULL
 , CONSTRAINT COMMANDE_PK PRIMARY KEY (CliId, CdeId)
 , CONSTRAINT COMMANDE_AK1 UNIQUE (CdeNo)
 , CONSTRAINT COMMANDE_CLIENT_FK FOREIGN KEY (CliId)
      REFERENCES CLIENT (CliId) ON DELETE CASCADE
) 
;
CREATE TABLE LIGNECDE 
(
     CliId                INT                  NOT NULL
   , CdeId                SMALLINT             NOT NULL
   , LigneId              SMALLINT             NOT NULL
   , ArtId                INT                  NOT NULL
   , Quantite             INT                  NOT NULL
 , CONSTRAINT LIGNECDE_PK PRIMARY KEY (CliId, LigneId)
 , CONSTRAINT LIGNECDE_CDE_FK FOREIGN KEY (CliId, CdeId)
      REFERENCES COMMANDE (CliId, CdeId) ON DELETE CASCADE
 , CONSTRAINT LIGNECDE_ART_FK FOREIGN KEY (ArtId)
      REFERENCES ARTICLE (ArtId)
) 
;
CREATE TABLE ENGAGEMENT 
(
     CliId                INT                  NOT NULL
   , LigneId              SMALLINT             NOT NULL
   , EngId                SMALLINT             NOT NULL
   , EngType              CHAR(2)              NOT NULL
   , EngDate              CHAR(10)             NOT NULL
   , Quantite             INT                  NOT NULL
, CONSTRAINT ENGAGEMENT_PK PRIMARY KEY (CliId, EngId)
, CONSTRAINT ENGAGEMENT_LIGNE_FK FOREIGN KEY (CliId, LigneId)
      REFERENCES LIGNECDE (CliId, LigneId) ON DELETE CASCADE
) 
;
CREATE TABLE LIVRAISON 
(
     CliId                INT                  NOT NULL
   , EngId                SMALLINT             NOT NULL
   , LivrId               SMALLINT             NOT NULL
   , LivrDate             CHAR(10)             NOT NULL
   , LivrStatut           CHAR(2)              NOT NULL
   , Quantite             INT                  NOT NULL
   , CamionId             INT                  NOT NULL
   , CONSTRAINT LIVRAISON_PK PRIMARY KEY (CliId, LivrId)
   , CONSTRAINT LIVRAISON_ENG_FK FOREIGN KEY (CliId, EngId)
         REFERENCES ENGAGEMENT (CliId, EngId) ON DELETE CASCADE
   , CONSTRAINT LIVRAISON_CAMION_FK FOREIGN KEY (CamionId)
         REFERENCES CAMION (CamionId)
) 
;

En l’occurrence, au stade MCD, l’identification relative devient semi-relative ! On pourra reparler de cela.


Pardonnez les coquilles pouvant traîner et autres anomalies du même tonneau, mais j’ai passé un long moment chez l’ophtalmo et du coup je n’y vois pas grand chose...


 

[Paprick]

Bonjour,

Tout d'abord, en tant que fan des identifiants relatifs, je trouve excellente l'idée de les justifier par cette possibilité d'optimiser certaines requêtes : j'avoue que je ne justifiais pas forcément leur usage grâce à cet aspect, et je trouve ça très intéressant.
Cela m'amène aux raisons qui me font apprécier ces identifiants relatifs : je sais que ce que je vais dire ne va pas plaire à fsmrel compte tenu du MCD qu'il nous a proposé , mais je considère que leur première raison d'être est justement d'éviter la création d'identifiants qui ne font en rien partie du système d'information : en effet, pourquoi rajouter un Id_xxx alors que l'identifiant peut être composé grâce aux clés étrangères existantes (c'est d'ailleurs ce qui se passe pour les tables de correspondance issues d'associations multiples de part et d'autre). Par ailleurs, même si fsmrel me donnera probablement tord pour des raisons de performances, je n'approuve pas le fait qu'on rajoute un "artId" qui n'existe pas dans le SI, alors que "artRef" ferait un très bon identifiant (idem pour cdeId et cdeNo, ...). Ca reste juste un point de vue : après, chacun choisit selon ses sensibilités !

Revenons maintenant à ce fameux MCD de fsmrel qui a généré à tort une référence circulaire... Etant parfaitement à jeun et après avoir écarquillé les yeux , j'ai effectivement constaté qu'il n'y avait aucune référence circulaire dans ce modèle... Alors, bug de Looping ? En fait non : après avoir une nouvelle fois écarquillé les yeux (et étant toujours à jeun), je me suis rendu compte que le MLD proposé par Looping pour la table LIVRAISON n'était pas cohérent avec le MCD et faisait apparaitre un "#(#(#(#cdeid_1, ligneCdeId_1), engid_1), livraisonid_1)" totalement inexplicable... J'ai donc reproduit le MCD proposé par fsmrel et, surprise (ou pas...) : aucun problème de MLD, et donc bonne génération du code SQL correspondant... Le mystère s'épaissit donc...

J'ai alors fait du rétropédalage et j'ai imaginé un MCD qui correspondrait à ce MLD, et voici le résultat :



En fait, en construisant le MCD, une petite erreur de manip a conduit à la création de l'association réflexive A7, qui (concours de circonstance avec l'alignement sur grille de Looping) s'est retrouvée "cachée" derrière A4 ! Merci à fsmrel de vérifier en déplaçant A4 : il suffit alors de supprimer A7 et le MLD devient cohérent, et le code SQL est généré.

[CinePhil]

Par ailleurs, même si fsmrel me donnera probablement tord pour des raisons de performances, je n'approuve pas le fait qu'on rajoute un "artId" qui n'existe pas dans le SI, alors que "artRef" ferait un très bon identifiant (idem pour cdeId et cdeNo, ...). Ca reste juste un point de vue : après, chacun choisit selon ses sensibilités !

Effectivement, je pense que fsmrel va vous tomber dessus si l'idée est d'utiliser des clés signifiantes alphanumériques en clé primaire !
Lire à ce sujet l'article de SQLPro sur les clés auto-incrémentées.
Je crois me souvenir que fsmrel a une anecdote professionnelle personnelle sur l'emploi de clés alphanumériques. Comme diraient Chevalier et Laspalès : "Y'en a qu'ont essayé, ils ont eu des problèmes !"

[Paprick]

Effectivement, je pense que fsmrel va vous tomber dessus si l'idée est d'utiliser des clés signifiantes alphanumériques en clé primaire !
Lire à ce sujet l'article de SQLPro sur les clés auto-incrémentées.
Je crois me souvenir que fsmrel a une anecdote professionnelle personnelle sur l'emploi de clés alphanumériques. Comme diraient Chevalier et Laspalès : "Y'en a qu'ont essayé, ils ont eu des problèmes !"

Oui, j'en suis parfaitement conscient et c'est juste une gentille provocation ! Je connais bien cet article et les problèmes de certaines clés alphanumériques, et c'est la raison pour laquelle il ne faut surtout pas appliquer une règle sans en étudier le contexte, c'est à dire le SI. Toutes les clés naturelles, même si elles sont uniques, stables et concises ne sont pas bonnes à prendre : d'ailleurs, personnellement je rajoute toujours une qualité nécessaire en plus pour un identifiant : son formalisme, à savoir qu'il n'y ait aucun doute sur la façon de l'exprimer quelque soit la personne qui l'exprime ou le moment où il est exprimé (ce qui écarte forcément tous les VARCHAR !).
Par contre, quand une clé naturelle se présente avec toutes ces qualités, il faut la préférer à une clé rajoutée pour des raisons purement techniques et qui n'existait pas lors de l'analyse du SI.
Enfin, pour le MCD de fsmrel, il est vrai que ses "artRef" et "cdeNo" sont définis en CHAR(16), ce qui peut expliquer pourquoi il ne les a pas choisis.

[fsmrel]

Bonsoir,

A propos de A4 :

J’ai donc déplacé A4 : pas de A7 en vue. J’ai aussi bougé ENGAGEMENT et LIVRAISON : en vain. J’ai ensuite effectué une cassure sur la patte connectant LIVRAISON et A4, et là ça change ! Pas de A7, mais une belle patte porteuse d’une cardinalité 0,N source de la ternaire en cause :

Bien entendu, si je supprime la patte bizarre et peccamineuse, tout rentre dans l’ordre, mais pour en revenir au cas général, je pense aux plus jeunes qui à leur tour resteront désorientés, les bras ballants dans ce genre de situation, face à un message tel que celui qui m’a rendu si perplexe...

en tant que fan des identifiants relatifs, je trouve excellente l'idée de les justifier par cette possibilité d'optimiser certaines requêtes

Pour ma part, c’est la conjugaison de l’aspect sémantique des choses et de la performance des bases de données en production qui m’ont amené à modéliser ainsi, sans m’attarder sur des requêtes ponctuelles du genre de celle que j’ai proposée, et dont l’objet était de montrer que l’optimiseur de DB2 savait en tirer parti. Cette requête a en fait été codée bien après la construction de la base de données. En tout cas, la technique que j’ai utilisée chez mon équipementier, je l’ai en fait appliquée chez tous mes clients et ils n’ont jamais été déçus. Le pragmatisme conforté par l’épreuve du terrain a du bon. Et puis, ce qui importe après tout, c’est d’avoir en production une base de données pérenne, robuste et évolutive, pour laquelle on n’aura pas besoin d’appeler le docteur des bases pour soigner celles-ci : better prevent than cure…

Je n'approuve pas le fait qu'on rajoute un "artId" qui n'existe pas dans le SI, alors que "artRef" ferait un très bon identifiant (idem pour cdeId et cdeNo, ...). Ca reste juste un point de vue : après, chacun choisit selon ses sensibilités !

CinePhil me connaît bien et a prévu ma réponse. Je développe donc.

Tout d’abord, je fais référence à Yves Tabourier, grand Merisien devant l’Éternel, qui fournit il y a longtemps (c’était en 1986) ce que j’appelle une règle d’or, voyez à la page 80 de De l’autre côté de Merise : Systèmes d'information et modèles d'entreprise (ISBN 2-7081-0762-3) :

« ... 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... »

Et ces dégâts coûteux on conduit des entreprises à remettre à plat leurs bases de données, donc revoir de fond en comble leurs MCD. Comme par hasard, j’ai eu une importante activité d'audit et de conseil en l’espèce, dans bien des secteurs d’activité, et je suis toujours vivant !

Je crois me souvenir que fsmrel a une anecdote professionnelle personnelle sur l'emploi de clés alphanumériques.

Tu as raison, Philippe ! Et des anecdotes comme celle-ci, j’en ai des tas... Je me cite au sujet d’icelle (cf. De l’invariance des clés primaires)

« Les concepteurs d’un projet particulièrement sensible d’une grande banque avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de l’entité-type ENTREPRISE dans le MCD). Au niveau SQL, par le jeu des liens inter-tables (clé primaire - clé étrangère), le numéro Siren se propageait dans de nombreuses tables. Or, ce numéro est fourni par l’INSEE, lequel envoyait tous les mois les correctifs modifiant le Siren des entreprises venant de naître (10% d’entre elles à peu près). Les concepteurs en avaient tenu compte et me montrèrent le modèle correspondant à la mise à jour des tables impliquées : une usine à gaz ! J’avais fait observer que, vu le nombre de tables touchées et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait faire exploser la production informatique (batchs de nuit), du fait d’une activité de mise à jour excessive et en plus, délicate à ordonnancer. Après leur avoir parlé de la règle d’or de Tabourier, sans que j’ai eu à le leur demander, les concepteurs définirent dans le MCD un nouvel attribut, non porteur d’information, artificiel et invariant, destinée à devenir au stade SQL la colonne composant la clé primaire de la table ENTREPRISE, propagé en conséquence dans les autres tables, en lieu et place de la colonne NoSiren. A partir de là, modifier un numéro de Siren n’impactait plus que la seule table ENTREPRISE, les utilisateurs ayant bien évidemment toujours accès au contenu de la colonne NoSiren (et à elle seule du reste), devenue clé alternative (et n’ayant donc pas perdu ses propriétés d’unicité et d’irréductibilité). »

Enfin, pour le MCD de fsmrel, il est vrai que ses "artRef" et "cdeNo" sont définis en CHAR(16), ce qui peut expliquer pourquoi il ne les a pas choisis.

La sempiternelle raison de mon choix : la règle d’or de Tabourier et mes 50 ans de crapahuts sur tous les terrains des données, validant cette règle. Churchill disait « No sport! » et j’avance « Pas de propriété naturelle pour identifier ! ». Sorry! Les identifiants naturels donnant lieu à des clés primaires ont des conséquences pouvant être fort préjudiciables, et je ne peux m’empêcher de pasticher Bossuet qui disait : « Dieu se rit des hommes qui déplorent les effets dont ils chérissent les causes ».


 

[Paprick]

Désolé que cette maudite patte 0,n se soit planquée de cette manière ! Mais, pour mes chers étudiants, repartir du MLD pour comprendre le problème va me faire réfléchir à quelques exo intéressants !
Pour le reste et nos histoires d'identifiants naturels ou pas, je ne partage pas le jusqu'auboutisme qui consiste à systématiquement écarter la simple idée d'utiliser un identifiant naturel... En effet, je ne suis pas adepte de l'usage systématique d'identifiants purement techniques quand le naturel est possible, et surtout contrôlé.
Votre dernier exemple est significatif d'un mauvais choix d'un identifiant naturel, le numéro SIREN n'étant pas, dans ce cas, pérenne.
Pour moi, voici les 5 qualités que doit respecter un identifiant :
- Unicité (c'est la qualité de base que certains considèrent à tord comme suffisante)
- Formalisme (ce qui exclut toute forme de texte libre, les VARCHAR en tête)
- Pérennité (stable dans le temps, ce qui va dans le sens de votre exemple)
- Irréductibilité (clé minimale)
- Légalité (en particulier pour des rubriques telles que le numéro INSEE)

Si, et seulement si, une rubrique ou un groupe de rubriques possède de telles qualités, cela constitue un bon identifiant ; sinon, il faut effectivement avoir recours à un nouvel Id.
Gardons toujours à l'esprit l'ouvrage de Nanci et Espinasse, qui fait tout de même toujours référence concernant Merise, et qui utilise régulièrement (parfois même trop à mon goût !) des identifiants naturels.
D'une façon générale, la plupart des problèmes rencontrés avec les identifiants naturels viennent de problèmes de pérennité, et il est vrai qu'avec un identifiant rajouté, ce genre de problème ne se pose pas.
Yves Tabourier a bien sûr raison de dire que "le rôle fondamental d'un identifiant est d’être sûr de distinguer deux jumeaux parfaits, malgré des descriptions identiques" ; mais cela n'empêche pas à un identifiant naturel, s'il existe avec les qualités ci-dessus, de remplir ce rôle (il est vrai que si les jumeaux sont vraiment parfait à 100%, cet identifiant naturel n'existera pas ! ).

Enfin, des dates sont souvent impliquées dans les identifiants pour gérer les problèmes d'historisation souvent présents dans nos bases de données... et il parait alors normal de les intégrer dans la composition des identifiants, même si ce sont des rubriques naturelles.
Les écarter au seul prétexte de la règle d'or de Tabourier ne me parait pas judicieux.

[fsmrel]

Bonsoir,

je ne partage pas le jusqu'auboutisme qui consiste à systématiquement écarter la simple idée d'utiliser un identifiant naturel... En effet, je ne suis pas adepte de l'usage systématique d'identifiants purement techniques quand le naturel est possible, et surtout contrôlé.

Le jusqu’auboutisme donne une coloration négative à mes propos, mais il est un cas sur lequel j’ai effectivement oublié de me prononcer, celui de l’historisation, j’en traite plus loin. Sinon, qu’au niveau MCD on fasse dans un premier temps abstraction des identifiants artificiels, bien sûr, ne serait-ce que pour ne pas embrouiller la maîtrise d’oeuvre à qui l’on soumet les MCD lors des travaux de validation, MOE qui n’est pas concernée par ce genre d’artifice non « métier ». Mais par la suite, au niveau relationnel, la patrouille et votre serviteur veillent !

Pour reprendre l’exemple du numéro SIREN omniprésent dans la future base de données de la banque en question, j’étais le chef de la patrouille, j’avais entre autres pour mission de valider les modèles, et les concepteurs ont vite compris qu’il était urgent de faire du SIREN un identifiant alternatif, avec mise à la poubelle d’une montagne de code devenu inutile...

Dans la discussion qui nous occupe, j’ai parlé des conséquences de la traduction des identifiants naturels en clés primaires SQL⁽¹⁾ et conjecturé qu’il était pour le moins prudent, lors du passage du MCD au MLD, de traduire ces identifiants en clés alternatives, garantissant bien entendu les règles de base du niveau relationnel : unicité, irréductibilité. Les clés alternatives permettent d’accéder aux données, mais elles ne pourront pas se propager, proliférer dans la base de données. Dans le contexte des bases de données relationnelles, garantir l’accès au données dans le respect des règles d’unicité et d’irréductibilité que demander de plus à toutes ces clés rendues alternatives ?

Pour la forme, en plus des règles (contraintes) d’unicité et d’irréductibilité, j’ajouterai le respect de la forme normale de Boyce Codd, c’est le minimum syndical. Je rappelle pour ceux qui nous suivent :

La variable relationnelle R est en forme normale de Boyce-Codd (BCNF) si et seulement si chaque dépendance fonctionnelle de R est impliquée par les clés de R.

Respecter la BCNF est essentiel, mais je ne m’étendrai pas sur le sujet, je tiens seulement à souligner que dans son énoncé toutes les clés sont concernées et pas seulement la clé primaire, comme on le pense encore trop souvent.


Lorsque vous écrivez :

« quand le naturel est possible, et surtout contrôlé »

Lors de l’élaboration des modèles tout neufs, certes, mais au fil des ans le contrôle peut en prendre un coup lors de la maintenance des applications, quand les dossiers de conception ne sont plus à jour et que les équipes ne sont plus les mêmes. Le dicton dit : « Chassez le naturel, il revient au galop » : sans doute, mais possiblement bien déformé !

Pour moi, voici les 5 qualités que doit respecter un identifiant :
- Unicité (c'est la qualité de base que certains considèrent à tord comme suffisante)
- Formalisme (ce qui exclut toute forme de texte libre, les VARCHAR en tête)
- Pérennité (stable dans le temps, ce qui va dans le sens de votre exemple)
- Irréductibilité (clé minimale)
- Légalité (en particulier pour des rubriques telles que le numéro INSEE)

Pas de problème quant au formalisme.

Pas de problème pour l’unicité et l’irréductibilité, puisqu’au stade relationnel ces règles son en fait des contraintes qu’on ne peut enfreindre.

Toujours pour ceux qui nous suivent, je leur rappelle ces contraintes :

Une clé candidate (clé plus simplement) est un sous-ensemble d’attributs K de l’en-tête d’une variable relationnelle R, respectant nécessairement les deux contraintes suivantes :

1. Unicité : deux tuples distincts de R ne peuvent avoir simultanément la même valeur de K.

2. irréductibilité : il n’existe pas de sous-ensemble strict de K garantissant lui aussi l’unicité.

De mon côté, je laisse les concepteurs faire leurs MCD avec leurs clés candidates, mais au besoin je les rattrape au tournant.


Quant à la pérennité : certes, car qui dit stabilité dit invariance, mais cela veut dire aussi absence de signification.

Si la structure du numéro de commande m’est imposé par la maîtrise d’oeuvre, la règle de légalité est respectée, mais prudemment de ce numéro je ferai une clé alternative, car quid de la pérennité si d’aventure la MOE en change la structure ? (par exemple, parce que comme disait Zézette épouse X, « ça tient plus dans les cases »). Cet exemple n’est peut-être pas le meilleur, mais j’en ai vu d’autres, par exemple concernant le matricule des employés (même dans ma propre entreprise, à cause de l’effet Zézette !). Une anecdote en passant : en 1965 j’ai programmé la gestion du personnel d’un établissement public à Paris. Le polytechnicien de service, chargé de concevoir la structure du matricule des agents avait organisé ainsi la chose :

- Les 6 premiers caractères du nom de l’agent (complétés au besoin)

- La date de naissance (JJMMAA)

Evidemment il fut tenu compte des jumeaux : pas de problème, on ajoute 31 au jour de naissance de l’un des deux jumeaux.

Quid des triplés ? on ajoute encore 31 au jour de naissance.

Au-delà ? Ça ne peut pas arriver.

Manque de chance, notre polytechnicien ignorait que lorsqu’un employé ne connaissait que son année de naissance, alors la règle en vigueur était de le faire naître le 31 décembre de ladite année. Aïe ! Inflation de redondances ! Je ne sais pas comment le problème a finalement été réglé, pour ma part j’avais le nez dans le guidon et d’autres chats à fouetter, mais on a bien rigolé... En passant, et à propos de pérennité, je ne suis pas mécontent, car mes programmes ont servi pendant 30 ans...

En tout cas, je conjecture que tout ce qui échappe au contrôle de l’utilisateur peut être suspecté de non pérennité. Bien que respectant la règle de légalité si je vous ai bien suivi, je conviens que le numéro SIREN n’est pas pérenne, puisque modifiable par l’INSEE. Qu’en est-il du numéro INSEE des personnes (NIR) ? Avec toutes ces histoires de transgenre, ne faudra-t-il pas permuter le 1er chiffre pour les personnes changeant de sexe ? Je n’en sais rien, et comme dirait Laspalès à Chevallier « C’est vous qui voyez »...

Quoi qu’il en soit, propager un NIR à 13 chiffres dans la base de données (intégrité référentielle oblige) ça engendre de la consommation de ressources, surtout au niveau des index (je me situe en l’occurrence dans la soute). En tant que DBA, je ne validerai pas un MCD dont la conséquence serait la propagation de clés primaires/étrangères à 13 chiffres (ou à 26 chiffres dans le cas des associations réflexives). Ainsi, je verrais volontiers dans votre liste une règle de parcimonie : un INTEGER (4 octets autorisant des valeurs comprises entre -2 147 483 648 et 2 147 483 647) vs un nombre à 13 chiffres, y a pas photo, donc le NIR : clé alternative.

Question : Supposons qu’on définisse une entité-type DEPARTEMENT pour les départements français. Par référence à Wikipédia, si je modélise en 1945, il s’agit d’un code à deux chiffres, donc va pour un attribut tel que

CodeDepartement DECIMAL(2,0) (ou CHAR(2), avec contrôle de numéricité)

En 1946, je dois changer ainsi les clés dans les tables concernées :

CodeDepartement DECIMAL(3,0) (ou CHAR(3), avec contrôle de numéricité)

En 1957-1958, l’INSEE me force à passer du numérique à l’alphanumérique...

En 1976, le code 20 est éclaté en 2A et 2B.

En 2015, le code 69 est éclaté en 69M et 69D.

Que nous réserve l’avenir ?

Autant je vois bien la pérennité du numéro atomique ou du symbole pour les éléments chimiques, autant pour les univers que je modélise habituellement, j’ai des doutes. Avez-vous en l’occurrence quelques exemples d’identifiants réellement pérennes ?

des dates sont souvent impliquées dans les identifiants pour gérer les problèmes d'historisation souvent présents dans nos bases de données... et il parait alors normal de les intégrer dans la composition des identifiants, même si ce sont des rubriques naturelles.

Excellente remarque ! Je suis tout à fait d’accord, et de mon côté j’intègre des intervalles de dates dans les clés primaires des tables d’historisation. Je suis confus, j’ai oublié d’en faire mention dans les posts précédents et vous prie de me pardonner. Par vocation de l’historisation les périodes sont stables et si on les modifie c’est très ponctuellement, disons pour réparer une erreur. Il est évident que les tables d’historisation ont des clés dans lesquelles le temps intervient sous forme d’intervalles temporels (type DATERANGE de PostgreSQL par exemple).

L’historisation est un sujet extrêmement intéressant et je n’ai pas manqué d’évoquer assez longuement les travaux des Relationlanders, C. J. Date, Hugh Darwen, Nikos Lorentzos, exposés en profondeur dans Temporal Data and the Relational Model (San Francisco, Calif.: Morgan Kaufmann (2003)) ; je fournis des détails ici, en particulier au paragraphe 6.4 « Données actives et données historisées ».

Les écarter au seul prétexte de la règle d'or de Tabourier ne me parait pas judicieux

Tout à fait d’accord, Tabourier s’est manifestement focalisé sur des identifiants n’ayant pas trait à l’historisation, et il ne serait certainement pas contre une modulation de la règle d’or à ce sujet.



⁽¹⁾ En 1993, le modèle relationnel de données a officiellement abandonné le distinguo clé primaire / clé alternative, et seul reste le concept de clé. Cf. C. J. Date “The Primacy of Primary Keys: An Investigation.” in Relational Database Writings 1991-1994. Reading Mass.: Addison-Wesley (1995).



 

[Paprick]

Et bien, nous voilà d'accord sur bien des choses !
J'adhère totalement à l'idée d'un modèle conceptuel qui pourrait faire usage de clés naturelles (avec les bonnes qualités) dans la phase d'analyse du SI, sachant que lorsque le DBA entre en action, bien des optimisations devront être imaginées. Et je suis d'accord qu'un identifiant INTEGER se trimbalera bien plus aisément dans la base qu'un CHAR(30), surtout si une flopée d'identifiants relatifs lui colle aux fesses !
En fait, si l'on exclut au niveau conception cette problématique d'optimisation technique, on se rend encore et toujours compte (et votre exemple sur les départements en est une nouvelle illustration), que c'est la stabilité qui peut faire défaut. Ensuite, il faut effectivement juger le risque... Certains identifiants naturels ont moins de chances que d'autres d'évoluer (je pense en particulier à tous les codes qui sont naturellement de type INTEGER), et l'on peut prendre le risque d'une restructuration de la BD dans les cas rarissimes où le problème se pose.
Tout dépend du niveau d'exigence que l'on s'impose sur le formalisme et la pérennité de l'identifiant (et des conséquences si elle devait ne pas être respectée)... et cette exigence ou cette peur des conséquences peuvent effectivement conduire à faire systématiquement usage d'identifiants artificiels. Mais pour moi, il faut que ce soit le résultat d'une vraie réflexion et d'un vrai choix, et non pas l'application d'une règle qui cherche systématiquement à éviter des problèmes qui n'existeront peut-être jamais.
En résumé, je pense qu'il faut être très exigeant avec les identifiants naturels, mais il faut néanmoins les prendre en considération avant d'en faire des clés alternatives et de passer à l'artificiel dicté par la technique.

[escartefigue]

En fait, en construisant le MCD, une petite erreur de manip a conduit à la création de l'association réflexive A7, qui (concours de circonstance avec l'alignement sur grille de Looping) s'est retrouvée "cachée" derrière A4 ! Merci à fsmrel de vérifier en déplaçant A4 : il suffit alors de supprimer A7 et le MLD devient cohérent, et le code SQL est généré.

Bonjour

Tout d'abord merci pour le logiciel Looping que je n'utilise que depuis peu mais que j'apprécie pour sa simplicité et son ergonomie. Bravo

À propos d'associations reflexives, j'ai essayé d'en modéliser une avec looping mais sans succès. La deuxième "patte" semble se superposer à la première et disparaît aussitôt.
Quelle est l'astuce pour en construire une ?

[Paprick]

Bonjour,

Effectivement, par défaut Looping aligne les pattes sur la grille et, pour une réflexive, ça peut chevaucher ; l'astuce est simple : il suffit d'utiliser l'outil "Cassure" pour casser le lien en plusieurs brins.
Pour le faire directement, vous partez de la classe d'entités, vous cliquez une fois à coté dans du vide (à l'endroit où se mettra de la cassure) et vous rejoignez ensuite l'association.
Ensuite, en cliquant sur le lien, vous pouvez demander le lissage de la cassure (à base de courbes de Bézier).

Bonne continuation !

[fsmrel]

Bonsoir,


Pour Paprick :

J’évoque de temps en temps chez DVP un sujet a priori facile à aborder, à savoir celui des courses hippiques.

Une partie intéressante se résume à ceci :

Lors de la course C, le jockey J monte le cheval H, qui à cette occasion a le numéro N, et supporte le poids P.

MCD correspondant

Figure 1 - MCD dépourvu de CIF

Des contraintes qu’il faudra mettre en oeuvre sont évidentes. Ainsi, lors d’une course :

(C1) Un jockey ne monte qu’un cheval,

(C2) Ce cheval n’est monté que par ce jockey,

(C3) Ce cheval ne porte qu’un seul numéro,

(C4) Ce numéro n’est porté que par ce cheval.

Dans le cadre de la théorie relationnelle, ces contraintes s’expriment sous la forme de dépendances fonctionnelles :

(DF1) course, jockey → cheval ;

(DF2) course, cheval → jockey ;

(DF3) course, cheval → numéro ;

(DF4) course, numéro → cheval ;

En utilisant les axiomes d’Armstrong, on infère les dépendances fonctionnelles (par application des axiomes d’augmentation et transitivité) :

(DF5) course, jockey → numéro ;

(DF6) course, numéro → jockey ;

Autrement dit, deux contraintes dérivées des précédentes, à savoir que lors d’une course :

(C5) Un jockey n’est affecté (via son cheval) que d’un numéro ;

(C6) Ce numéro n’affecte que ce jockey.

Pour traduire ces contraintes en Merise, on passe par des CIF :

CIF1 : COURSE X JOCKEY → CHEVAL

CIF2 : COURSE X CHEVAL → JOCKEY

CIF3 : COURSE X CHEVAL → NUMERO

CIF4 : COURSE X NUMERO → CHEVAL

CIF5 : COURSE X JOCKEY → NUMERO

CIF6 : COURSE X NUMERO → JOCKEY

Si on modélise CIF1 seule :

Figure 2 - MCD avec une 1re CIF, CIF1

Le code SQL produit pour la clé primaire de la table PARTICIPER est alors conforme :

CREATE TABLE PARTICIPER(
   courseId INTEGER,
   jockeyId INTEGER,
   numero SMALLINT,
   poids DECIMAL(4,2) NOT NULL,
   chevalId INTEGER NOT NULL,
   PRIMARY KEY(courseId, jockeyId, numero),
   FOREIGN KEY(courseId) REFERENCES COURSE(courseId),
   FOREIGN KEY(jockeyId) REFERENCES JOCKEY(jockeyId),
   FOREIGN KEY(numero) REFERENCES NUMERO(numero),
   FOREIGN KEY(chevalId) REFERENCES CHEVAL(chevalId)
);

Ajoutons CIF2 :

Figure 3 - MCD avec prise en compte d'une 2e CIF, CIF2

Le code SQL produit pour la clé primaire de la table PARTICIPER n’est pas bon :

CREATE TABLE PARTICIPER(
   courseId INTEGER,
   numero SMALLINT,
   poids DECIMAL(4,2) NOT NULL,
   chevalId INTEGER NOT NULL,
   jockeyId INTEGER NOT NULL,
   PRIMARY KEY(courseId, numero),
   FOREIGN KEY(courseId) REFERENCES COURSE(courseId),
   FOREIGN KEY(numero) REFERENCES NUMERO(numero),
   FOREIGN KEY(chevalId) REFERENCES CHEVAL(chevalId),
   FOREIGN KEY(jockeyId) REFERENCES JOCKEY(jockeyId)
);

En effet, en ce qui concerne les clés candidates, on devrait plutôt avoir les deux triplets K1 et K2 suivants :

K1 = {courseId, chevalId, numero}

K2 = {courseId, jockeyId, numero}

Pour sa part, l’unique clé {courseId, numero} produite par Looping n’a lieu d’être que si les seules CIF représentées sont CIF4 et CIF6.


Faisons participer CIF1, CIF2 et CIF3 :

Figure 4 - MCD avec prise en compte d'une 3e CIF, CIF3

Au résultat :

CREATE TABLE PARTICIPER(
   courseId INTEGER,
   poids DECIMAL(4,2) NOT NULL,
   chevalId INTEGER NOT NULL,
   jockeyId INTEGER NOT NULL,
   numero SMALLINT NOT NULL,
   PRIMARY KEY(courseId),
   FOREIGN KEY(courseId) REFERENCES COURSE(courseId),
   FOREIGN KEY(chevalId) REFERENCES CHEVAL(chevalId),
   FOREIGN KEY(jockeyId) REFERENCES JOCKEY(jockeyId),
   FOREIGN KEY(numero) REFERENCES NUMERO(numero)
); 

En ce qui concerne les clés candidates, on devrait plutôt avoir les deux triplets K1 et K2 :

K1 = {courseId, chevalId}

K2 = {courseId, jockeyId}

En passant, si pour la table NUMERO on coche la case « Classe d’entités fictive », le résultat est changé mais pas meilleur (à nouveau, l’unique clé {courseId, numero} produite par Looping n’a lieu d’être que si les seules CIF présentes sont CIF4 et CIF6) :

CREATE TABLE PARTICIPER(
   courseId INTEGER,
   numero SMALLINT,
   poids DECIMAL(4,2) NOT NULL,
   chevalId INTEGER NOT NULL,
   jockeyId INTEGER NOT NULL,
   PRIMARY KEY(courseId, numero),
   FOREIGN KEY(courseId) REFERENCES COURSE(courseId),
   FOREIGN KEY(chevalId) REFERENCES CHEVAL(chevalId),
   FOREIGN KEY(jockeyId) REFERENCES JOCKEY(jockeyId)
);

Faisons enfin participer CIF1, CIF2, CIF3 et CIF4 :

Figure 5 - MCD avec prise en compte de la 4e CIF, CIF4

On attend les 3 clés candidates suivantes :

K1 = {courseId, chevalId}

K2 = {courseId, jockeyId}

K3 = {courseId, numero}

Mais on obtient le même résultat (non valide) qu’avec CIF1, CIF2 et CIF3.

J’espère ne pas m’être planté dans le calcul des clés, et c’est l’occasion pour un étudiant amateur des axiomes d’Armstrong de vérifier cela…

 

[escartefigue]

Bonjour François, bonjour à tous

Dans cet exercice, j'aurais volontiers vu le numéro comme une entité-type faible de la course et modélisé en conséquence une relation ternaire entre le numéro (identifié relativement à la course), le jockey et le cheval

Qu'en penses-tu ?

[fsmrel]

Hello Capitaine !

j'aurais volontiers vu le numéro comme une entité-type faible de la course et modélisé en conséquence une relation ternaire entre le numéro (identifié relativement à la course), le jockey et le cheval

J’en pense que cela revient en fait à tenter de cacher en vain la poussière sous le tapis, car tu empêches la modélisation des CIF CIF1 à CIF4, donc des contraintes incontournables que j’ai énumérées.

En l’absence de CIF dans le MCD, la clé de la table PARTICIPER est la suivante :

{courseId, numero, chevalId, jockeyId}

C’est une surclé, mais pas une clé candidate. Pour essayer d’améliorer ça, au mieux tu peux exprimer les CIF suivantes :

(CIF_escar1) COURSE X NUMÉRO X CHEVAL → JOCKEY

(CIF_escar2) COURSE X NUMÉRO X JOCKEY → CHEVAL

Lesquelles sont en fait des cautères sur des jambes de bois (de cheval, of course hippique), ne permettant pas de produire les clés attendues, à savoir :

K1 = {courseId, chevalId}

K2 = {courseId, jockeyId}

K3 = {courseId, numero}

Les clés qui sont les conséquences de CIF_escar1 et CIF_escar2 sont les suivantes

K4 = {courseId, numero, chevalId}

K5 = {courseId, numero, jockeyId}

Là encore, ce sont des surclés, mais aucunement des clés candidates.

 

[escartefigue]

bigre, en effet !

[Paprick]

Bonsoir,
Voilà un exercice bigrement intéressant, bien que quelque peu prise de tête !

Tout d'abord, félicitations à François pour la beauté du modèle : une fort belle étoile !

Concernant la génération SQL proposée par Looping, elle est effectivement fort simpliste et inadaptée à de telles situations... Vous l'aurez compris, Looping se contente de retirer, dans la clé primaire de l'association, l'identifiant de la classe d'entités ciblée par la CIF.
A réfléchir donc pour les prochaines versions...

Tout cela m'amène cependant à une réflexion connexe : dans la mesure où plusieurs clés sont potentiellement candidates, comment exprimer celle que l'on souhaite retenir pour le MLD et le schéma relationnel correspondant ?

Pour ma part, dans de telles situations, j'ai tendance (certes à tord) à négliger certains aspects des CIF pour me focaliser sur la clé que je souhaite choisir (dans notre cas, pour PARTICIPER).
N'étant un fan des tri-pattes en encore moins des quadri-pattes que je trouve quasi-illisibles pour le commun des mortels, je me rabats sur la décomposition de l'association en classes d'entités (Looping propose un petit bouton qui le fait automatiquement pour toute association à cardinalités multiples de part et d'autre), et je choisis les identifiants relatifs en fonction de la clé minimale souhaitée pour la classe en question.

Je suis conscient que toutes les CIF ne sont pas exprimées, mais j'ai au moins choisi ma clé.

Voici ce que j'obtiens en choisissant, par exemple, {courseId, chevalId} en clé primaire :

Effectivement, perte d'infos en termes d'expression des CIF.
Et un tel modèle pourrait alors évoluer de la façon suivante :

C'est à dire :

On est effectivement loin des CIF définies initialement, mais le code SQL est correct...
Mais j'en reviens à ma question de début de post : dans la mesure où toutes les CIF pourraient être prises en compte, comment choisir parmi les clé candidates... et comment exprimer ce choix dans le MCD ?

[fsmrel]

Bonsoir,


Tout en commentant la réponse de Paprick, je propose ici une approche « relationnelle » qui pourra intéresser les plus jeunes, mais aussi les anciens comme Escartefigue et CinePhil qui seront en droit d’opposer d’éventuelles critiques...

une fort belle étoile !

Forcément, car c’est bientôt Noël !

Vous l'aurez compris, Looping se contente de retirer, dans la clé primaire de l'association, l'identifiant de la classe d'entités ciblée par la CIF.

Certes ! Mais à mon sens il serait préférable de ne minimaliser les clés qu’une fois toutes les CIF prises en compte. Je m’explique. Je passe dans le Relationland où je me sens plus à l’aise, et là j’utilise la panoplie des outils me permettant de structurer les données sous forme de relvars (variables relationnelles), traduites ensuite en SQL sous forme de tables.

Si au beau pays qu’est Merise on exploite les règles de gestion pour produire un MCD où figurent les CIF et autres contraintes (exclusion, inclusion, etc.), au Relationland on exploite bien sûr aussi ces règles, mais c’est dans un 1er temps pour produire toutes les dépendances fonctionnelles qu’on peut en inférer (les dépendances multivaluées et les dépendances de jointure n’interviendront que lors des dernières étapes de normalisation, c’est-à-dire vérification de la quatrième et de la cinquième formes normales). Les dépendances fonctionnelles sont en effet les briques de base qui serviront pour bâtir l’édifice.


Etapes de travail au Relationland

1re étape : collecter les règles de gestion, comme en Merise (Escartefigue et CinePhil seront d’accord !)

2e étape : traduire ces règles de gestion en dépendances fonctionnelles.

Si je reprends mon message précédent, la toute 1re règle qui se présente est la suivante, ayant servi pour définir l’association PARTICIPER du MCD (cf. Figure 1) :

(RG1) Lors de la course C, le jockey J monte le cheval H, qui à cette occasion a le numéro N, et supporte le poids P.

Après avoir retourné cet énoncé (moyennement formel, un peu ambigu) dans tous les sens, la traduction sous forme de dépendance fonctionnelle dans la langue du Relationland est la suivante :

(DF1) {course, jockey, cheval, numero} → {poids}

Ou encore, pour reprendre la dernière image du message précédent de Paprick :

(DF2) {course, jockey, cheval} → {poids, numero}

Maintenant, parmi les règles de gestion figurent les CIF

CIF1 : COURSE X JOCKEY → CHEVAL

CIF2 : COURSE X CHEVAL → JOCKEY

CIF3 : COURSE X CHEVAL → NUMERO

CIF4 : COURSE X NUMERO → CHEVAL

Et je m’empresse de les traduire à leur tour sous forme de dépendances fonctionnelles :

(DF3) {course, jockey} → {cheval}

(DF4) {course, cheval} → {jockey}

(DF5) {course, cheval} → {numero}

(DF6) {course, numero} → {cheval}

L’ensemble des dépendances fonctionnelles dont je dispose est désormais le suivant :

(DF1) {course, jockey, cheval, numero} → {poids}

(DF2) {course, jockey, cheval} → {poids, numero}

(DF3) {course, jockey} → {cheval}

(DF4) {course, cheval} → {jockey}

(DF5) {course, cheval} → {numero}

(DF6) {course, numero} → {cheval}

3e étape : calculer les fermetures dépendances fonctionnelles.

Il s’agit pour chaque dépendance fonctionnelle de produire le plus grand ensemble d’attributs qui en constituent le dépendant (la partie droite de la DF). Pour calculer les fermetures, on peut utiliser les axiomes d’Armstrong, ou pour gagner du temps se servir par exemple de l’algorithme du seau à dépendants, proposé par Bernstein en 1976. Le résultat est le suivant, DF par DF :

F1+ = {course, jockey, cheval, numero}+ = {course, jockey, cheval, numero, poids}

F2+ = {course, jockey, cheval}+ = {course, jockey, cheval, numero, poids}

F3+ = {course, jockey}+ = {course, jockey, cheval, numero, poids}

F4+ = {course, cheval}+ = {course, jockey, cheval, numero, poids}

F5+ = {course, numero}+ = {course, jockey, cheval, numero, poids}

Chaque fermeture contient l’en-tête de la variable relationnelle PARTICIPER, c’est-à-dire qu’elle est en droit de postuler à être clé candidate de cette variable.

4e étape : déterminer les clés candidates de la variable relationnelle PARTICIPER.

Comme annoncé, chaque fermeture contenant l’en-tête de la variable relationnelle PARTICIPER peut postuler à être clé candidate de cette variable :

K1 = {course, jockey, cheval, numero}

K2 = {course, jockey, cheval}

K3 = {course, jockey}

K4 = {course, cheval}

K5 = {course, numero}

Les ensembles K3, K4 et K5 peuvent prétendre à être clés candidates, car irréductibles (en ôter un attribut leur ferait perdre la propriété d’unicité).

K1 et K2 sont recalées car réductibles (K3, K4, K5 en sont des sous-ensembles).

Pour résumer, les clés candidates de la variable PARTICIPER sont les suivantes :

K3 = {course, jockey}

K4 = {course, cheval}

K5 = {course, numero}

Ce travail terminé, on peut enfin passer à la déclaration de la variable (en Tutorial D) :

VAR PARTICIPER BASE RELATION
        {course    INTEGER,
         jockey    INTEGER,
         cheval    INTEGER,
         numero    INTEGER,
         poids     NUMERIC (4,2)}
    KEY {course, jockey}
    KEY {course, cheval}
    KEY {course, numero}
;

Ce à quoi, pour être complet, il faut bien sûr ajouter les clés étrangères.

En traduisant en SQL :

 
CREATE TABLE PARTICIPER 
  (
         course    INTEGER,
         jockey    INTEGER,
         cheval    INTEGER,
         numero    INTEGER,
         poids     NUMERIC (4,2)
    , CONSTRAINT PARTICIPER_K1 UNIQUE (course, jockey)
    , CONSTRAINT PARTICIPER_K2 UNIQUE (course, cheval)
    , CONSTRAINT PARTICIPER_K3 UNIQUE (course, numero)
  )
;

Pour qu’il n’y ait pas de jalouses parmi les miss clés candidates, aucune n’est élue miss clé primaire (la norme SQL n’est pas contre, idem par exemple pour PostgreSQL ou SQL Server) : au nom du rasoir d’Ockham (« Pluralitas non est ponenda sine necessitate »), le concept de clé primaire est à considérer comme obsolète et ça fait belle lurette que C. J. Date l’a fait disparaître de la théorie relationnelle. La clé primaire serait plus égale que les autres ? En faire le choix relèverait d’arguments en fait psychologiques.

dans la mesure où plusieurs clés sont potentiellement candidates, comment exprimer celle que l'on souhaite retenir pour le MLD et le schéma relationnel correspondant ?
...
dans la mesure où toutes les CIF pourraient être prises en compte, comment choisir parmi les clé candidates... et comment exprimer ce choix dans le MCD ?

Or donc, ayant allègrement passé le concept de clé primaire au rasoir d’Ockham, je n’ai plus à choisir, à élire une miss clé primaire ravalant les autres au rang de dauphines (clés alternatives)...

No primary key!

Bref :

le code SQL est correct...

Oui, il n’est pas faux, mais toutefois incomplet du fait de la non prise en compte de règles de gestion qu’il faudra réinjecter sous forme de clés alternatives...

 

[escartefigue]

Note technique 10/10 comme d'habitude
mais en plus, cerise sur le gâteau
Note artistique et humoristique 10/10 aussi

Merci François

[fsmrel]

Note technique 10/10 comme d'habitude
mais en plus, cerise sur le gâteau
Note artistique et humoristique 10/10 aussi

Merci Capitaine ! tel Achille Talon, mon front, au départ marmoréen, désormais rougeoie…