Discussion :

[Age_of_Aquarius]

Bonjour à tous,

Soit le MCD suivant correspondant à une ancienne base de données qu'on veut perfectionner :



Après l'analyse des besoins, on a décidé d'ajouter de nouvelles données à notre future base de données. Pour simplifier, ces données sont représentées par la nouvelle entité entite_2 :



Dans les deux MCD, chaque occurrence de l'entité entite_3 doit avoir une et une seule occurrence de l'entité entite_1.
J'aimerais bien que (dans la future base de données) pour les nouvelles occurrences de l'entité entite_3, l'occurrence correspondante dans l'entité entite_1 soit associée par le nouveau chemin (en vert), c'est-à-dire par l'intermédiaire de l'entité entite_2. (Je crois que c'est possible si on tient compte des cardinalités.)

Ma question est la suivante : Est-ce qu'il est nécessaire de garder l'association association 1-3 ? Et si c'est non, comment assurer le transfert des anciens données sans passer par cette association ?

Merci.

[fsmrel]

Bonsoir Age_of_Aquarius,

Est-ce qu'il est nécessaire de garder l'association association 1-3 ?

Elle fait double emploi et doit passer au rasoir d’Ockham. On ne la conserverait que si les occurrences cibles devaient être différentes selon le chemin emprunté pour aller de E3 à E1.

Comment assurer le transfert des anciens données sans passer par cette association ?

Avant de transférer les données de la source vers la cible, il faut être clair quant à la structure de la base de données, donc faire un MLD et pondre le script de déclaration des tables (CREATE TABLE). Comme dans une discussion précédente, utilisons Workbench.


Par exemple




Script pour la source :

Code SQL :

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

1
2
3
4
5
6
CREATE  TABLE E1_V0 
(
        E1Id          INT           NOT NULL
      , E1_Data       VARCHAR(64)   NOT NULL
    , CONSTRAINT E1_V0_PK PRIMARY KEY (E1Id) 
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE  TABLE E3_V0 
(
        E3Id          INT           NOT NULL
      , E1Id          INT           NOT NULL
      , E3_Data       VARCHAR(64)   NOT NULL
    , CONSTRAINT E3_V0_PK PRIMARY KEY (E3Id) 
    , CONSTRAINT E3_V0_FK FOREIGN KEY (E1Id) REFERENCES E1_V0 (E1Id)
) ;

Script pour la cible :

Code SQL :

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

1
2
3
4
5
6
CREATE  TABLE E1 
(
        E1Id          INT           NOT NULL
      , E1_Data       VARCHAR(64)   NOT NULL
    , CONSTRAINT E1_PK PRIMARY KEY (E1Id) 
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE  TABLE E2 
(
        E2Id          INT           NOT NULL
      , E1Id          INT           NOT NULL
      , E2_Data       VARCHAR(64)   NOT NULL
    , CONSTRAINT E2_PK PRIMARY KEY (E2Id)
    , CONSTRAINT E2_E1_FK FOREIGN KEY (E1Id) REFERENCES E1
) ;

Code SQL :

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

1
2
3
4
5
6
CREATE  TABLE E3 
(
        E3Id          INT           NOT NULL
      , E3_Data       VARCHAR(64)   NOT NULL
    , CONSTRAINT E3_PK PRIMARY KEY (E3Id) 
) ;

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE  TABLE E2_3 
(
        E2Id          INT           NOT NULL
      , E3Id          INT           NOT NULL
    , CONSTRAINT E2_3_PK PRIMARY KEY (E2Id)
    , CONSTRAINT E2_3_AK UNIQUE (E3Id)
    , CONSTRAINT E2_3_E2_FK FOREIGN KEY (E2Id) REFERENCES E2
    , CONSTRAINT E2_3_E3_FK FOREIGN KEY (E3Id) REFERENCES E3
) ;

E2_3 a pour clé primaire E2Id et clé alternative E3Id, mais les rôles pourraient tout aussi bien être inversés, à vous de voir. Par ailleurs, selon cet exemple, E1 et E1_V0 c’est kif-kif. De son côté, E3 est semblable à E3_V0, sinon que la clé étrangère {E1Id} présente dans E3_V0 doit être absente à terme de E3 (tout comme la colonne E3).

Maintenant, la suite dépend de ce que vous mettez dans E2, car la partie délicate concerne l’alimentation automatique de E2_3. Par exemple, certaines données de E3_V0 doivent-elles migrer dans E2 ?


Question : Quel est votre SGBD ?

[Age_of_Aquarius]

Bonjour fsmrel,

On ne la conserverait que si les occurrences cibles devaient être différentes selon le chemin emprunté pour aller de E3 à E1.

Effectivement, tous les nouveaux enregistrements de E3 devraient emprunter le nouveau chemin (vert) pour aller chercher l’enregistrement correspondant dans E1.
L’ancien chemin (association 1-3) ne devrait être emprunté que par les enregistrements anciens.
En fait, il y a une contrainte de partition entre les deux chemins. Les occurrences de l’entité E3 sont réparties en deux sous-ensembles :
- le premier contient toutes les occurrences de l’entité E3 existant déjà dans l’ancienne base de données.
- le deuxième contient toutes les nouvelles occurrences de l’entité E3 qui n’existaient pas dans l’ancienne base de données.
L’union au sens mathématique de ces deux sous-ensembles est égale à l’ensemble (entité) E3.
Leur intersection est un ensemble vide.

Certaines données de E3_V0 doivent-elles migrer dans E2 ?

Non. Suivant votre MLD, E3_V0 représente l’entité E3 dans l’ancien système (le premier sous-ensemble). On n’est pas intéressé d’associer à ces anciens occurrences des occurrences correspondantes dans E2. Ce qui est important par contre c’est de garder les anciennes occurrences correspondantes dans E1 (E1_V0).

Question : Quel est votre SGBD ?

MySQL (moteur InnoDB) et j’utilise MySQL Workbench pour le design.

Merci pour ton aide.

[fsmrel]

Bonsoir Age_of_Aquarius,


Vu les dernières précisions, le MLD ci-dessous pourrait convenir. Les mickeys (clés vertes, Paint est mon ami !) sont là pour rappeler que les attributs concernés sont à la source de clés alternatives (clause UNIQUE selon la norme SQL) : {E2Id} (table E2_3) :


Conformément à la norme SQL, les déclarations des tables sont les suivantes :


Table E1

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE E1 
(
        E1Id          INT           NOT NULL
      , E1_Data       VARCHAR(64)   NOT NULL
    , CONSTRAINT E1_PK PRIMARY KEY (E1Id) 
) ;

Table E2

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE E2 
(
        E2Id          INT           NOT NULL
      , E1Id          INT           NOT NULL
      , E2_Data       VARCHAR(64)   NOT NULL
    , CONSTRAINT E2_PK PRIMARY KEY (E2Id)
    , CONSTRAINT E2_E1_FK FOREIGN KEY (E1Id) REFERENCES E1
) ;

Table E3

Code SQL :

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

1
2
3
4
5
6
CREATE TABLE E3 
(
        E3Id          INT           NOT NULL
      , E3_Data       VARCHAR(64)   NOT NULL
    , CONSTRAINT E3_PK PRIMARY KEY (E3Id) 
) ;

Table E1_3

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE E1_3 
(
        E3Id          INT           NOT NULL
      , E1Id          INT           NOT NULL
    , CONSTRAINT E1_3_PK PRIMARY KEY (E3Id)
    , CONSTRAINT E1_3_E3_FK FOREIGN KEY (E3Id) REFERENCES E3
    , CONSTRAINT E1_3_E1_FK FOREIGN KEY (E1Id) REFERENCES E1
) ;

Table E2_3

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE E2_3 
(
        E3Id          INT           NOT NULL
      , E2Id          INT           NOT NULL
    , CONSTRAINT E2_3_PK PRIMARY KEY (E3Id)
    , CONSTRAINT E2_3_AK UNIQUE (E2Id)
    , CONSTRAINT E2_3_E3_FK FOREIGN KEY (E3Id) REFERENCES E3
    , CONSTRAINT E2_3_E2_FK FOREIGN KEY (E2Id) REFERENCES E2
) ;

Contrainte de partitionnement

L’assertion ci-dessous permet de faire respecter la contrainte d’exclusion mettant en jeu les tables E1_3 et E2_3 :

Code SQL :

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

1
2
3
4
5
6
7
CREATE ASSERTION
    CHECK (NOT EXISTS
               (
                SELECT *
                FROM   E1_3 JOIN E2_3 ON E1_3.E3Id = E2_3.E3Id
               )
          );

La contrainte de partitionnement est respectée par la conjugaison de la contrainte d’exclusion et des contraintes référentielles E1_3_E3_FK, E2_3_E3_FK.

MySQL ne permettant pas de coder l’instruction CREATE ASSERTION, la contrainte d’exclusion est à faire respecter des triggers : lors d’un INSERT dans la table E1_3, il faut s’assurer que la valeur prise par l’attribut E3Id n’est pas déjà une valeur prise par l’attribut E3Id de la table E2_3, et inversement lors d’un INSERT dans la table E2_3, que la valeur prise par l’attribut E3Id n’est pas déjà une valeur prise par l’attribut E3Id de la table E1_3. De la même façon, il faut s’assurer que la contrainte d’exclusion est respectée lors des UPDATE de ces deux tables.

A titre indicatif, le MCD (notation IE) ci-dessous met en évidence que E1_3 et E2_3 sont des spécialisations de E3 :

[fsmrel]

Bonjour Age_of_Aquarius,


Une précision à propos de la spécialisation

Si l’on considère qu’effectivement E1_3 et E2_3 sont des spécialisations de E3 (cf. le MCD précédent), alors la jointure naturelle d'une occurrence E3 et d'une occurrence E3_1 est un tout, même principe pour une occurrence E3 ainsi associée à une occurrence E3_2. Dans ces conditions, il serait illogique d’exécuter une instruction DELETE ne portant que sur une occurrence E3_1 (ou E3_2), entraînant par ailleurs un viol de la contrainte de partitionnement (pour sa partie totalité, l’union des projections de E1_3 et de E2_3 sur {E3Id} n’étant plus alors égale à la projection {E3Id} de E3) : un DELETE doit porter seulement sur la table E3, tandis que la suppression des occurrences touchées doit se propager par CASCADE, d’où l’aménagement des instructions CREATE TABLE concernées, savoir E1_3 et E2_3 :

Table E1_3

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE E1_3 
(
        E3Id          INT           NOT NULL
      , E1Id          INT           NOT NULL
    , CONSTRAINT E1_3_PK PRIMARY KEY (E3Id)
    , CONSTRAINT E1_3_E3_FK FOREIGN KEY (E3Id) REFERENCES E3 ON DELETE CASCADE
    , CONSTRAINT E1_3_E1_FK FOREIGN KEY (E1Id) REFERENCES E1
) ;

Table E2_3

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE E2_3 
(
        E3Id          INT           NOT NULL
      , E2Id          INT           NOT NULL
    , CONSTRAINT E2_3_PK PRIMARY KEY (E3Id)
    , CONSTRAINT E2_3_AK UNIQUE (E2Id)
    , CONSTRAINT E2_3_E3_FK FOREIGN KEY (E3Id) REFERENCES E3 ON DELETE CASCADE
    , CONSTRAINT E2_3_E2_FK FOREIGN KEY (E2Id) REFERENCES E2
) ;

[Age_of_Aquarius]

Bonjour fsmrel,

Lors d’un INSERT dans la table E1_3, il faut s’assurer que la valeur prise par l’attribut E3Id n’est pas déjà une valeur prise par l’attribut E3Id de la table E2_3

Même si le peuplement des deux tables va se faire d’une manière séquentielle ?
Parce que la table E1_3 (mais aussi E3) sera peupler la première pendant la migration des anciens données. Pendant ce temps-là, on sait déjà que la table E2_3 est complétement vide.

- J’aimerais bien éliminer les valeurs NULL dans le futur schéma tel que conseillé dans des discussions précédentes. Comment assurer une migration dans ce cas-ci, si l’ancien schéma autorise cette valeur ?

- Est-ce qu’il y a d’autres façons de faire le transfert de données entre les deux schémas autre que l’exécution des requêtes SQL et l’utilisation des fichiers CSV comme intermédiaires ?


Merci beaucoup pour ton aide.

[fsmrel]

Bonsoir Age_of_Aquarius,


Une remarque préalable : du fait de copier/coller intempestifs dans mes précédents messages, en recopiant E2_3, j’ai défini {E1Id} comme clé alternative pour E1_3. J’ai donc apporté les modifications qui s’imposaient.

J’aimerais bien éliminer les valeurs NULL dans le futur schéma.

Si certaines colonnes d’une table peuvent être déclarées « nullables », c’est qu’il est probable que pour certaines lignes l’information y est sans objet. Par exemple, si la table SALARIÉ comporte une colonne COMMISSION qui ne vaut que pour les commerciaux de l’entreprise — les seuls à toucher des commissions contrairement aux autres salariés —, soit on utilise la facilité épicière de la marque NULL proposée par SQL, soit on force la colonne COMMISSION à 0, soit on modélise correctement, en ayant à l’esprit le principe de la spécialisation.

Forcer COMMISSION à 0 pour les salariés qui ne sont pas commerciaux peut poser quelques problèmes, car 0 correspond au montant de la commission d’un commercial qui ... n’en a pas eu . On pourrait pallier en définissant un type MontantCommission, mais je crois savoir que définir ses propres types n’est pas possible aujourd’hui avec MySQL (en tout cas je n’ai pas trouvé d’instruction CREATE TYPE dans la documentation).

C’est le moment de faire du ménage sémantique, en mettant en évidence le fait que certains salariés ont des caractéristiques supplémentaires, en l’occurrence les commerciaux (COMMERCIAL est alors un type d'entité spécialisé) :

MCD




MLD



Le bonhomme NULL commence à avoir du mal à se manifester, mais en transposant cela veut dire que dans votre base de données vous aurez une table par type d'entité spécialisée... A vous de voir.

Est-ce qu’il y a d’autres façons de faire le transfert de données entre les deux schémas autre que l’exécution des requêtes SQL et l’utilisation des fichiers CSV comme intermédiaires ?

Je ne connais pas MySQL, aussi le mieux est que vous posiez la question dans le forum MySQL. Ceci dit, à vue de nez, l’utilisation de fichiers CSV devrait pouvoir convenir pour des volumétries « raisonnables », ne faisant pas exploser les fichiers (quitte à exporter/importer par « rondelles »).

La table E1_3 (mais aussi E3) sera peuplée la première pendant la migration des anciens données. Pendant ce temps-là, on sait déjà que la table E2_3 est complètement vide.

La table E3_V0 ci-dessous (dérivée de votre entité-type Entite_3) comporte un attribut E1Id donnant lieu à une clé étrangère {E1Id} référençant la table E1_V0.



Dans la base de données cible, la table E3 ne comportera pas cet attribut E1Id, mais en contrepartie ce sera la table E1_3 qui l’hébergera :


=>

Code SQL :

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

1
2
3
4
5
6
7
8
CREATE TABLE E1_3 
(
        E3Id          INT           NOT NULL
      , E1Id          INT           NOT NULL
    , CONSTRAINT E1_3_PK PRIMARY KEY (E3Id)
    , CONSTRAINT E1_3_E3_FK FOREIGN KEY (E3Id) REFERENCES E3 ON DELETE CASCADE
    , CONSTRAINT E1_3_E1_FK FOREIGN KEY (E1Id) REFERENCES E1
) ;

Supposons que la table E3_V0 ne comporte pas de colonnes nullables. Un scénario peut être le suivant :

1) Décharger à l’identique le contenu de la table E3_V0 source (phase UNLOAD), exception faite bien sûr de la colonne E1d qui ne fait pas partie des colonnes de la table E3 cible.

Le contenu du fichier correspondant (appelons-le F_E3) est l’image du résultat du SELECT :

Code SQL :

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

1
2
SELECT E3Id, E3_Data
FROM   E3_V0 ;

2) Charger F_E3 dans la table cible E3 (phase RELOAD).


3) Décharger la paire {E3Id, E1Id} de la table E3_V0 source (phase UNLOAD). Le contenu du fichier correspondant (appelons-le F_E1_3) est l’image du résultat du SELECT :

Code SQL :

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

1
2
    SELECT E3Id, E1Id 
    FROM   E3_V0 ;

4) Charger F_E1_3 dans la table cible E1_3 (phase RELOAD).


Cas des colonnes marquées NULL.

Supposons que la table E3 source comporte des colonnes nullables. Chacune d’elles devrait en théorie faire l’objet d’une table dans la base de données cible. Pour reprendre l’exemple de la table COMMERCIAL :

Code SQL :

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

1
2
3
4
5
6
7
CREATE TABLE E3_Commercial
(
        E3Id          INT           NOT NULL
      , Commission    INT           NOT NULL
    , CONSTRAINT E3_COMM_PK PRIMARY KEY (E3Id)
    , CONSTRAINT E3_COMM_FK FOREIGN KEY (E3Id) REFERENCES E3 ON DELETE CASCADE
) ;

Maintenant, pour les colonnes de type CHAR, VARCHAR et assimilées, vous pouvez les conserver dans la table E3 cible, et les déclarer en NOT NULL DEFAULT :

Code SQL :

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

1
2
3
4
5
CREATE TABLE E3
        E3Id          INT            NOT NULL
        ...           ...            ...
      , Remarque      VARCHAR(254)   NOT NULL    DEFAULT  '' 
        ...           ...            ...

J'espère qu'on tend vers quelque chose de viable...

[Age_of_Aquarius]

Bonsoir fsmrel,

J'espère qu'on tend vers quelque chose de viable...

Absolument . Les choses sont maintenant beaucoup plus claires pour moi.
Parmi les cas que je rencontre souvent, c'est lorsque un champ a comme valeur une date et qui devrait être NULL au moins temporairement. Mais en suivant vos explications, je peux résoudre ce cas particulier par analogie.
Prenant l’entité suivante :



Par exemple un organisme à but non lucratif venant en aide aux personnes ayant des difficultés à trouver un logement.
On crée un dossier pour chaque nouvelle personne. Une fois son problème est réglé, on ferme le dossier.
Au moment de la création du dossier dans le système, la date de fermeture ne peut être connue à l’avance bien sûr. Donc on va la déclarer comme NOT NULL et mettre une valeur par défaut.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE dossier
(
  dossier_id               INT          NOT NULL AUTO_INCREMENT,
  dossier_titre            VARCHAR(50)  NOT NULL,
  dossier_nom_personne     VARCHAR(20)  NOT NULL,
  dossier_responsable      VARCHAR(20)  NOT NULL,
  dossier_date_ouverture   DATE         NOT NULL,
  dossier_date_fermeture   DATE         NOT NULL DEFAULT '0000-00-00',
  
  CONSTRAINT pk_dossier    PRIMARY KEY (dossier_id)
)

Si le maître d'ouvrage n’aime pas voir des zéros dans une date, je peux adapter l’affichage au niveau de l’application.

Merci infiniment.

[fsmrel]

Bonjour Age_of_Aquarius,

Si le maître d'ouvrage n’aime pas voir des zéros dans une date, je peux adapter l’affichage au niveau de l’application.

On dira que c’est même un devoir ! Si un utilisateur voit une date telle que 00-00-0000, il pourrait avoir des doutes quant à l’intégrité de la base de données ou la santé mentale des informaticiens... Selon la situation il vaut mieux afficher un texte du genre « dossier non clos », « / », « sans objet », « inconnu », « top secret », etc.

En tout cas, je vous souhaite bonne route (et n’hésitez pas à marquer la discussion comme résolue).

A bientôt peut-être,

François