Discussion :

[champomy62]

Ah oui je comprends effectivement...

Je vous remercie de vos réponses. Problème résolu.

[fsmrel]

Bonsoir champomy62,

Je suis étonnée lorsque vous dites:

Si le modèle M1 du fabricant F1 est associé au logiciel L1, et s’il faut remplacer L1 par L2 dans cette association, l’update correspondant est à répercuter sur la table SITE : cela reste transparent pour les applications, à condition de prévoir ON UPDATE CASCADE pour la contrainte référentielle établie entre SITE et CATALOGUE.

Mon professeur m'a toujours dit: on ne modifie jamais une clé primaire. Pourquoi serait possible dans le cas d'une association ?

Ce que dit votre professeur vaut pour les tables issues d’entités-types « régulières » telles que FABRICANT, LOGICIEL, MODELE. Remontons donc au niveau conceptuel et réfléchissons à ce qu’à écrit Yves Tabourier à propos des MCD merisiens, dans son ouvrage (De l’autre côté de MERISE, page 80), et c’est une règle d’or qui reste malheureusement trop souvent méconnue, malgré ses 30 ans d’âge :

« ... 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, comme l’attribut FabricantId fait l’objet de la clé primaire de la table FABRICANT, il doit être invariable, et par voie de conséquence, quand on descend au niveau SQL, on est bien d’accord, on s’interdit d’utiliser la clause ON UPDATE CASCADE pour les clés étrangères faisant référence à cette clé primaire (cf. tables MODELE et LOGICIEL ci-dessous).

En plus de ne rien décrire, j’ajoute pour ma part que les valeurs prises par la clé primaire {FabricantId} ne devraient pas être connues de l’utilisateur. Pour cette raison et pour causer comme les uéméliens, je considère l’attribut FabricantId comme privé, ce qui me permet d’en garantir la stabilité, donc celle des clés étrangères qui lui font référence. Évidemment, l’utilisateur doit disposer d’un moyen pour accéder aux données d’un fabricant, ce qui veut dire qu’on doit prévoir une clé « naturelle » publique, dont l’utilisateur pourra faire ce qu’il veut, par exemple un « code fabricant », un numéro de Siret, etc., la seule règle importante du point de vue de la base de données étant que cette clé respecte la règle d’unicité valant pour toute clé candidate (primaire ou alternative). Par exemple :

Deux lignes distinctes de la table FABRICANT ne peuvent avoir simultanément la valeur pour l’attribut FabricantSiret.

=>

 
create table FABRICANT 
(
     FabricantId     int             not null,
     FabricantSiret  char(14)        not null,
     FabricantNom    varchar(32)     not null,
     constraint FABRICANT_PK primary key (FabricantId),
     constraint FABRICANT_AK unique (FabricantSiret)
) ;

Désormais, l’utilisateur peut faire ce qu’il veut du Siret d’un fabricant : cela n’a aucun impact sur les tables en relation avec la table FABRICANT.

Le principe vaut pour les autres entités-types, à doter elles aussi d’un identifiant naturel donnant lieu à une clé alternative au niveau SQL :

create table LOGICIEL
(
     FabricantId     int             not null,
     LogicielId      int             not null,
     LogicielCode    varchar(8)      not null,
     LogicielNom     varchar(32)     not null,
     constraint LOGICIEL_PK primary key (FabricantId, LogicielId),
     constraint LOGICIEL_AK unique (LogicielCode),
     Constraint LOGICIEL_FABRICANT_FK foreign key (FabricantId)
         References FABRICANT (FabricantId)
) ;

create table MODELE
(
     FabricantId     int             not null,
     ModeleId        int             not null,
     ModeleCode      varchar(8)      not null,
     ModeleNom       varchar(32)     not null,
     constraint MODELE_PK primary key (FabricantId, ModeleId),
     constraint LOGICIEL_AK unique (ModeleCode),
     Constraint MODELE_FABRICANT_FK foreign key (FabricantId)
         References FABRICANT (FabricantId)
);

Passons à la table CATALOGUE :

create table CATALOGUE
(
     FabricantId     int             not null,
     ModeleId        int             not null,
     LogicielId      int             not null,
     constraint CATALOGUE_PK primary key (FabricantId, ModeleId, LogicielId),
     constraint CATALOGUE_MODELE_FK foreign key (FabricantId, ModeleId)
         References MODELE (FabricantId, ModeleId),
     constraint CATALOGUE_LOGICIEL_FK foreign key (FabricantId, LogicielId)
         references LOGICIEL (FabricantId, LogicielId)
);

Et à la table SITE, en supposant que celle-ci fasse directement référence à la table CATALOGUE (étant entendu que, si un site peut utiliser plusieurs modèles associés à différents logiciels, il faudrait mette en œuvre une association m-n entre SITE et CATALOGUE, d’où une table SITE_CATALOGUE) :

create table SITE
(
     SiteId          int             not null,
     SiteCode        char(5)         not null,
     SiteNom         varchar(32)     not null,
     FabricantId     int             not null,
     ModeleId        int             not null,
     LogicielId      int             not null,
     constraint SITE_PK primary key (SiteId),
     constraint SITE_AK unique (SiteCode),
     constraint SITE_CATALOGUE_FK foreign key (FabricantId, ModeleId, LogicielId)
         references CATALOGUE (FabricantId, ModeleId, LogicielId)
         on update cascade
);

Où figure la clause ON UPDATE CASCADE. Pour juger de son utilité, fendons-nous d’un jeu d’essai :

insert into FABRICANT (FabricantId, FabricantSiret, FabricantNom) values (1, '12345678901234', 'Naudin') ;
insert into FABRICANT (FabricantId, FabricantSiret, FabricantNom) values (2, '43210987654321', 'Volfoni') ;

select *, '' as '<= FABRICANT' from FABRICANT ;

insert into LOGICIEL (FabricantId, LogicielId, LogicielCode, LogicielNom) values (1, 1, 'Log011', 'Logiciel 11') ;
insert into LOGICIEL (FabricantId, LogicielId, LogicielCode, LogicielNom) values (1, 2, 'Log012', 'Logiciel 12') ;
insert into LOGICIEL (FabricantId, LogicielId, LogicielCode, LogicielNom) values (1, 3, 'Log013', 'Logiciel 13') ;
insert into LOGICIEL (FabricantId, LogicielId, LogicielCode, LogicielNom) values (2, 1, 'Log021', 'Logiciel 21') ;
insert into LOGICIEL (FabricantId, LogicielId, LogicielCode, LogicielNom) values (2, 2, 'Log022', 'Logiciel 22') ;
insert into LOGICIEL (FabricantId, LogicielId, LogicielCode, LogicielNom) values (2, 3, 'Log023', 'Logiciel 23') ;

select *, '' as '<= LOGICIEL' from LOGICIEL ;

insert into MODELE (FabricantId, ModeleId, ModeleCode, ModeleNom) values (1, 1, 'Mod011', 'Modèle 11') ;
insert into MODELE (FabricantId, ModeleId, ModeleCode, ModeleNom) values (1, 2, 'Mod012', 'Modèle 12') ;
insert into MODELE (FabricantId, ModeleId, ModeleCode, ModeleNom) values (1, 3, 'Mod013', 'Modèle 13') ;
insert into MODELE (FabricantId, ModeleId, ModeleCode, ModeleNom) values (2, 1, 'Mod021', 'Modèle 21') ;
insert into MODELE (FabricantId, ModeleId, ModeleCode, ModeleNom) values (2, 2, 'Mod022', 'Modèle 22') ;
insert into MODELE (FabricantId, ModeleId, ModeleCode, ModeleNom) values (2, 3, 'Mod023', 'Modèle 23') ;

select *, '' as '<= MODELE' from MODELE ;

insert into CATALOGUE (FabricantId, ModeleId, LogicielId) values (1, 1, 1) ;
insert into CATALOGUE (FabricantId, ModeleId, LogicielId) values (1, 1, 2) ;
insert into CATALOGUE (FabricantId, ModeleId, LogicielId) values (2, 1, 1) ;
insert into CATALOGUE (FabricantId, ModeleId, LogicielId) values (2, 1, 2) ;

select *, '' as '<= CATALOGUE' from CATALOGUE ;

insert into SITE (SiteId, SiteCode, SiteNom, FabricantId, ModeleId, LogicielId) values (1, 'sit01', 'Site 1', 1, 1, 1) ;

select *, '' as '<= SITE' from SITE ;

update CATALOGUE
    set LogicielId = 3 where FabricantId = 1 and ModeleId = 1 and LogicielId = 1 ;

select *, '' as '<= CATALOGUE' from CATALOGUE ;

select *, '' as '<= SITE' from SITE ;

Avant update, le site faisait référence au triplet <1, 1, 1>

SiteId     SiteCode    SiteNom    FabricantId    ModeleId    LogicielId 
     1     sit01       Site 1               1           1             1 

Après update, le SGBD a fait son travail, le site fait référence au triplet <1, 1, 3>

SiteId     SiteCode    SiteNom    FabricantId    ModeleId    LogicielId 
     1     sit01       Site 1               1           1             3 

Si on supprime la clause ON UPDATE CASCADE, l’update de la table CATALOGUE est rejeté : je vous laisse le soin (et à votre professeur ^^) de concocter la séquence des instructions SQL permettant malgré tout d’effectuer l’update, lequel et parfaitement légitime (par exemple, un logiciel est devenu obsolète et a été remplacé par un autre).

Pour mémoire, les attributs FabricantId, ModeleId et LogicielId n’étant pas « publics », leurs valeurs ne devraient pas être codées en dur, contrairement à ce que j’ai fait ci-dessus. Ainsi, la requête :

update CATALOGUE
    set LogicielId = 3 where FabricantId = 1 and ModeleId = 1 and LogicielId = 1 ;

Devrait être remplacée par la suivante, où figurent plutôt les valeurs des attributs publics FabricantSiret, ModeleCode et LogicielCode :

update CATALOGUE
    set LogicielId = 3 
    where FabricantId = (select FabricantId 
                         from   FABRICANT 
                         where  FabricantSiret = '12345678901234')
      and ModeleId = (select ModeleId 
                      from   MODELE as x join FABRICANT as y on x.FabricantId = y.FabricantId
                      where  FabricantSiret = '12345678901234' and ModeleCode = 'Mod011')
      and LogicielId = (select LogicielId 
                       from   LOGICIEL as x join FABRICANT as y on x.FabricantId = y.FabricantId
                       where  FabricantSiret = '12345678901234' and LogicielCode = 'Log011') ;  

Et ceci vaut évidemment pour les inserts.

Cela dit, cette façon de procéder n’engage que moi...

Et pour aller au bout de la logique « uémélienne », même les noms FabricantSiret, ModeleCode et LogicielCode devraient être cachés, puisque privés : ceci est possible en déclarant des vues (qui sont des tables, virtuelles certes, mais tables quand même) dans lesquelles ces noms ne figurent pas, vues mises à la disposition des applications et n’ayant pas accès aux tables de base, même pour les opérations de mise à jour. D’un point de vue théorique, il n’y a pas de problème, une vue peut toujours être mise à jour, voyez l’ouvrage de Chris Date : View Updating and Relational Theory - Solving the View Update Problem. Avec notre SQL obèse et laid, il en va différemment, mais on arrive à ses fins au moyen de triggers interceptant les mises à jour des vues et ventilant les données dans les tables. A l’occasion, jetez un coup d’œil ici, j’ai fait figurer quelques propos tenus par les ténors au fil des ans au sujet des vues.

[champomy62]

En plus de ne rien décrire, j’ajoute pour ma part que les valeurs prises par la clé primaire {FabricantId} ne devraient pas être connues de l’utilisateur. Pour cette raison et pour causer comme les uéméliens, je considère l’attribut FabricantId comme privé, ce qui me permet d’en garantir la stabilité, donc celle des clés étrangères qui lui font référence. Évidemment, l’utilisateur doit disposer d’un moyen pour accéder aux données d’un fabricant, ce qui veut dire qu’on doit prévoir une clé « naturelle » publique, dont l’utilisateur pourra faire ce qu’il veut, par exemple un « code fabricant », un numéro de Siret, etc., la seule règle importante du point de vue de la base de données étant que cette clé respecte la règle d’unicité valant pour toute clé candidate (primaire ou alternative).

Je suis en total accord avec vous. Dans mes modèles, j'ai toujours une clé informatique que l'utilisateur ne connait pas, et une clé naturelle qui est unique.

Dans l'exemple que vous citez, par exemple pour FABRICANT. Oui il est possible d'avoir le numéro de SIRET qui identifie le constructeur, mais en général, un constructeur n'est pas forcement Français, et ce numéro est uniquement fait pour la FRANCE.
(bien entendu cela reste un exemple)

En règle general, le nom d'un fabricant sera unique, le nom de ses modèles aussi ainsi que les softwares associes!!! (ici on parle de scanner IRM, donc pas beaucoup de concurrences, (et qu'en general une entreprise dépose un brevet sur le nom ?))
Bien sur, c'est un raccourci qu'il ne faut pas toujours prendre car si un deuxième constructeur arrive et porte le même nom ........... Qu'en pensez-vous ?

Si on supprime la clause ON UPDATE CASCADE, l’update de la table CATALOGUE est rejeté : je vous laisse le soin (et à votre professeur ^^) de concocter la séquence des instructions SQL permettant malgré tout d’effectuer l’update, lequel et parfaitement légitime (par exemple, un logiciel est devenu obsolète et a été remplacé par un autre).

Je ne souhaite pas retourner a l'age de pierre ^^. Si la base de données peut travailler pour moi, elle le fera en toute circonstance beaucoup mieux. Car triggers = ligne de code, moins de ligne de codes, moins de bugs

Devrait être remplacée par la suivante, où figurent plutôt les valeurs des attributs publics FabricantSiret, ModeleCode et LogicielCode :

update CATALOGUE
    set LogicielId = 3 
    where FabricantId = (select FabricantId 
                         from   FABRICANT 
                         where  FabricantSiret = '12345678901234')
      and ModeleId = (select ModeleId 
                      from   MODELE as x join FABRICANT as y on x.FabricantId = y.FabricantId
                      where  FabricantSiret = '12345678901234' and ModeleCode = 'Mod011')
      and LogicielId = (select LogicielId 
                       from   LOGICIEL as x join FABRICANT as y on x.FabricantId = y.FabricantId
                       where  FabricantSiret = '12345678901234' and LogicielCode = 'Log011') ;  

Et ceci vaut évidemment pour les inserts.

Cela dit, cette façon de procéder n’engage que moi...

Qui ne fait pas ca ?

[champomy62]

Bonjour fsmrel,

je me permets de reouvrir le topic pour demander comment tu as fais pour les mises a jour en cascade ? Il me semble que c'est avec le champ stéréotype mais je n'ai pas trouvé.

Merci

[fsmrel]

Bonjour champomy,


J'ai fouillé dans la doc de DB-MAIN, mais je n'ai rien trouvé à ce sujet. Moralité je modifie le script généré par l'AGL...

[champomy62]

Je te remercie, c'est bien ce qu'il me semble. Si je trouve une possibilité je rouvrirai la discussion.

Merci