IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Merise Discussion :

Règles de passage du MCD au MLD pour les associations binaires


Sujet :

Merise

  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Mai 2010
    Messages
    176
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2010
    Messages : 176
    Points : 301
    Points
    301
    Par défaut Règles de passage du MCD au MLD pour les associations binaires
    Bonjour,

    Plusieurs sujets parlent du passage du MCD au MLD et débouchent sur des règles qui entrent en contradiction avec une grande partie de la littérature sur Merise, y compris sur le site Developpez.com :

    Petit guide d'analyse des données à l'aide de la méthode MERISE
    http://sqlpro.developpez.com/cours/m...passage#L5.1.2

    FAQ Merise
    http://merise.developpez.com/faq/?pa...LD_PasserAuMLD


    Je vais donc tenter, sous votre contrôle, de synthétiser les différentes idées issues de plusieurs sujets, et de redéfinir les principales règles permettant de créer des tables à partir d'un MCD (pour les associations binaires).


    Pour une association binaire, 10 cas sont possibles :

    0,1 - 0,1
    0,1 - 1,1
    0,1 - 0,n
    0,1 - 1,n
    1,1 - 0,n

    Pour ces 5 premiers cas, la création d'une table associative permet d'éviter les NULL, le MLD correspondant comportera donc 3 tables pour chacun des cas.

    1,1 - 1,1

    Dans ce cas soit nous obtenons 2 tables, soit les deux entités sont fusionnées pour obtenir 1 table.

    1,n - 1,1

    Ici nous obtenons 2 tables (et l'identifiant de l'entité "père" devient une clé étrangère dans la table "fille")

    0,n - 0,n
    0,n - 1,n
    1,n - 1,n

    Pour ces trois derniers cas, nous obtenons 3 tables.


    En conclusion, on peut dire :

    - Si au moins une cardinalité minimum vaut zéro, on crée une table associative pour éviter les NULL.
    - Si les deux cardinalités maximales sont à n, on crée une table associative.
    - pour une relation 1,1 - 1,1, il est possible de fusionner les tables.
    - pour une relation 1,n - 1,1, on crée deux tables et l'on ajoute une clé étrangère dans la table fille.


    Je souhaite limiter cette discussion sur ces règles élémentaires pour éviter que l'on se disperse (plus tard, nous pourrons par exemple compléter ce sujet en parlant des clés, des relations unaires ou n-aires, de l'identification relative, etc...).

    Dans un premier temps j'aimerai voir si nous parvenons à obtenir un consensus sur ces règles.

    L'objectif du sujet sera d'obtenir un consensus sur un algorithme simple et optimisé qui permettra de passer d'un MCD validé à un MLD.

  2. #2
    Membre actif
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    311
    Détails du profil
    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 311
    Points : 257
    Points
    257
    Par défaut
    Bonjour,

    je n'aime pas du tout la solution de la table associative.
    Particulièrement pour ce cas : 1,1 - 0,n...
    Aussi la différence entre 0,n et 1,n est normalement réalisé par une contrainte d'intégrité au niveau physique, je ne vois pas en quoi 0,n nécessiterait une table associative.

    @+,
    Emilien.

  3. #3
    Membre averti
    Profil pro
    Inscrit en
    Mai 2010
    Messages
    176
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2010
    Messages : 176
    Points : 301
    Points
    301
    Par défaut
    Effectivement, je pense également que pour le cas 1,1 - 0,n la table associative n'est nécessaire que dans certains cas particulier, comme :

    http://www.developpez.net/forums/d87...e/#post5007231

  4. #4
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    0,1 - 0,1
    0,1 - 1,1
    0,1 - 0,n
    0,1 - 1,n
    1,1 - 0,n

    Pour ces 5 premiers cas, la création d'une table associative permet d'éviter les NULL, le MLD correspondant comportera donc 3 tables pour chacun des cas.
    Passons en revue ces différents cas :
    1) 0,1 - 0,1
    Règle de gestion :
    Une voiture peut être affectée à un employé et un employé peut se voir affecter une seule voiture.

    MCD :
    Employe -0,1----Affecter----0,1- Voiture

    Tables :
    Employe (e_id, e_nom, e_prenom...)
    Voiture (v_id, v_immatriculation...)
    Voiture_employe (ve_id_voiture, ve_id_employe)

    => Il faut créer une table associative et choisir son identifiant parmi les deux identifiants disponibles. Un trigger vérifiera, à l'ajout d'une nouvelle association, que l'autre identifiant n'existe pas déjà dans la table associative.

    2) 0,1 - 1,1
    C'est le cas classique d'un héritage.

    Règle de gestion :
    Un employé est une personne et une personne peut être un employé.

    MCD :
    Employe -(1,1)----Etre----0,1- Personne

    Tables :
    Personne (p_id, p_nom, p_prenom...)
    Employe (e_id_personne, e_matricule...)

    => Pas besoin de table associative car la table Employe est fille de la table Personne.
    Si je supprime la personne, je supprime l'employé ; logique.

    3) 0,1 - 0,n
    => Ici, comme chaque entité peut ne pas être associée à l'autre, on doit créer une table associative pour éviter les clés étrangères à NULL.

    Règle de gestion :
    Une agence peut être supervisée par une direction régionale et une direction régionale peut superviser plusieurs agences.

    MCD :
    Direction_regionale -0,n----Superviser----(0,1)- Agence

    Tables :
    Direction_regionale (dr_id, dr_nom...)
    Agence (a_id, a_nom...)
    Supervision_agences (sa_id_agence, sa_id_direction_regionale)

    => Si je supprime la direction régionale, je ne supprime pas forcément l'agence ; logique.

    4) 0,1 - 1,n
    Changeons légèrement la règle de gestion précédente :
    Règle de gestion :
    Une agence peut être supervisée par une direction régionale et une direction régionale supervise au moins une agence.

    MCD :
    Direction_regionale -1,n----Superviser----(0,1)- Agence

    Tables :
    Direction_regionale (dr_id, dr_nom...)
    Agence (a_id, a_nom...)
    Supervision_agences (sa_id_agence, sa_id_direction_regionale)

    => Les tables sont identiques, avec création d'une table associative, mais pour respecter la cardinalité minimale à 1 du côté de la direction_regionale, il faudra un trigger à la création de celle-ci pour créer en même temps l'association avec au moins la première agence supervisée.

    5) 1,1 - 0,n
    Changeons encore la règle de gestion précédente :
    Règle de gestion :
    Une agence est supervisée par une seule direction régionale et une direction régionale peut superviser plusieurs agences.

    MCD :
    Direction_regionale -0,n----Superviser----1,1- Agence

    Tables :
    Direction_regionale (dr_id, dr_nom...)
    Agence (a_id, a_id_direction_regionale, a_nom...)

    => Pas besoin de table associative. Attention à la règle d'intégrité si on supprime la direction régionale ! Faut-il pour autant supprimer l'agence ou réaffecter celle-ci à une autre région ?

    Conclusion :
    Les seuls cas où la table associative est nécessaire pour éviter les clés étrangères à NULL sont :
    1) 0,1 - 0,1
    3) 0,1 - 0,n
    4) 0,1 - 1,n

    C'est à dire celles où il y a une cardinalité 0,1 et où l'autre cardinalité n'est pas 1,1.

    1,1 - 1,1

    Dans ce cas soit nous obtenons 2 tables, soit les deux entités sont fusionnées pour obtenir 1 table.
    OK.

    1,n - 1,1

    Ici nous obtenons 2 tables (et l'identifiant de l'entité "père" devient une clé étrangère dans la table "fille")
    OK, même si je n'aime pas beaucoup les expressions d'entité père et fille que je réserve plutôt aux héritages (0,1 - 1,1).

    0,n - 0,n
    0,n - 1,n
    1,n - 1,n

    Pour ces trois derniers cas, nous obtenons 3 tables.
    Oui parce que les deux cardinalités maximales sont à n.

    En conclusion, on peut dire :
    - 2 cardinalités maximales à n => table associative
    - 1 cardinalité 1,1 => clé étrangère dans la table issue de l'entité située du côté de la cardinalité 1,1
    - 2 cardinalités 1,1 => fusion des entités à envisager
    - une cardinalité 0,1 et l'autre cardinalité n'est pas 1,1 => table associative

    Je pense avoir passé en revue tous les cas non ?
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  5. #5
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Citation Envoyé par CinePhil Voir le message
    Je pense avoir passé en revue tous les cas non ?
    Le canonnier CinePhil est de retour ! Tous les canons semblent avoir été passés en revue avec les écouvillons qui vont bien.

    Mais...


    Citation Envoyé par CinePhil Voir le message
    Passons en revue ces différents cas :
    1) 0,1 - 0,1
    Règle de gestion :
    Une voiture peut être affectée à un employé et un employé peut se voir affecter une seule voiture.

    MCD :
    Employe -0,1----Affecter----0,1- Voiture

    Tables :
    Employe (e_id, e_nom, e_prenom...)
    Voiture (v_id, v_immatriculation...)
    Voiture_employe (ve_id_voiture, ve_id_employe)

    => Il faut créer une table associative et choisir son identifiant parmi les deux identifiants disponibles. Un trigger vérifiera, à l'ajout d'une nouvelle association, que l'autre identifiant n'existe pas déjà dans la table associative.
    Pas besoin de trigger, une clé alternative fera l’affaire. Dans le style SQL :
    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
    18
    19
    CREATE TABLE Employe 
    (        e_id      INT            NOT NULL
           , e_nom     VARCHAR(48)    NOT NULL
           , ...
       , PRIMARY KEY (e_id)) ;
    
    CREATE TABLE Voiture 
    {        v_id      INT            NOT NULL
           , v_immat   VARCHAR(16)    NOT NULL
           , ...
       , PRIMARY KEY (v_id)) ;
    
    CREATE TABLE Voiture_employe 
    {        v_id      INT            NOT NULL
           , e_id      INT            NOT NULL
       , PRIMARY KEY (v_id)
       , UNIQUE (e_id)        /* Clé alternative */
       , FOREIGN KEY (v_id) REFERENCES Voiture (v_id)
       , FOREIGN KEY (e_id) REFERENCES Employe (e_id) ;

    Citation Envoyé par CinePhil Voir le message
    2) 0,1 - 1,1
    C'est le cas classique d'un héritage.
    Ou de tout autre type de relation, par exemple une relation de composition. Par exemple, je passe une commande et le règlement aura lieu ultérieurement :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    Create table Commande 
    (        c_id      INT            NOT NULL
           , c_date    DATE           NOT NULL
           , c_client  INT            NOT NULL
           , ...
       , PRIMARY KEY (c_id), ...) ;
     
    Create table Reglement 
    {        r_id      INT            NOT NULL
           , r_date    DATE           NOT NULL
           , ...
       , PRIMARY KEY (r_id)
       , FOREIGN KEY (r_id) REFERENCES Commande (c_id)) ;

    En notant bien qu’un règlement n’est pas une commande mais qu’une commande fait l’objet d’un règlement (distinguo entre « Être » et « Avoir »).


    Citation Envoyé par CinePhil Voir le message
    Pas besoin de table associative car la table Employe est fille de la table Personne
    De fait, il n’y a pas besoin de table associative. Cela dit, la table Employe n’est pas la fille de la table Personne, mais Employe représente une spécialisation de Personne.


    Citation Envoyé par CinePhil Voir le message
    MCD :
    Direction_regionale -1,n----Superviser----(0,1)- Agence

    Tables :
    Direction_regionale (dr_id, dr_nom...)
    Agence (a_id, a_nom...)
    Supervision_agences (sa_id_agence, sa_id_direction_regionale)

    => Les tables sont identiques, avec création d'une table associative, mais pour respecter la cardinalité minimale à 1 du côté de la direction régionale, il faudra un trigger à la création de celle-ci pour créer en même temps l'association avec au moins la première agence supervisée.
    Dans le contexte de la théorie relationnelle (utilisant le langage D), garantir la cardinalité 1,N passe par la mise en œuvre d’une contrainte :

    Code D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    CONSTRAINT Contrainte_123 
           (IS_NOT_EMPTY Direction_regionale JOIN Supervision_agences) ;

    Lors de la création d’une direction régionale, on crée donc simultanément (au moins) un tuple Supervision_agences :

    Code D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    INSERT Direction_regionale RELATION    
             {TUPLE {  dir_id INT 1,
                     , dr_nom CHAR 'Direction de Toulouse'
                     ...
             }} ,   /* virgule : séparateur d’opérations */
    INSERT Supervision_agences RELATION  
             {TUPLE {  sa_id_agence INT 1 
                     , sa_id_direction_regionale INT 1
             }} ;   /* point-virgule : fin de liste des opérations  */ 

    En passant à SQL, dans le cadre de la norme on utilise une assertion (auquel cas les contrôles de cardinalité 1,N doivent être différés) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
     CREATE ASSERTION Contrainte_123 CHECK 
        (NOT EXISTS 
            (SELECT  '' 
             FROM Direction_regionale AS x 
             WHERE NOT EXISTS 
                  (SELECT  '' 
                   FROM Supervision_agences AS y 
                   WHERE x.dr_id = y.sa_id_direction_regionale)))
          DEFERRABLE INITIALLY DEFERRED ;
    On effectue les INSERTS SQL classiques :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    INSERT INTO Direction_regionale (dr_id, dr_nom, ...)
             VALUES (1, 'Direction de Toulouse'...) ;
     
    INSERT INTO Supervision_agences (sa_id_agence, sa_id_direction_regionale...)
             VALUES (1, 1) ;
    Puis on demande le cas échéant au SGBD de déclencher les contrôles (si on le fait pas, ces contrôles auront lieu au moment du COMMIT) :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    SET CONSTRAINTS ALL IMMEDIATE ;

    Les choses sont plus compliquées avec nos SGBD (SQL) qui ne proposent pas l’instruction CREATE ASSERTION : il faut alors en passer par des vues et des triggers, sans oublier un seul de ces derniers, notamment concernant les DELETE.

    En passant par une vue (et en supprimant les droits d’Insert pour les tables Direction_regionale et Supervision_agences), on peut imposer que la 1re agence d’une direction soit créée en même temps que celle-ci :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE VIEW Direction_V (dr_id, dr_nom, ... sa_id_agence)
         AS SELECT   x.dr_id, x.dr_nom, ... y.sa_id_agence
            FROM     Direction_regionale AS x JOIN Supervision_agences AS y 
                     ON  x.dr_id = y.sa_id_direction_regionale ;

    Maintenant, soit le SGBD accepte les mises à jour des vues de jointure, soit il les refuse. Comme j’utilise SQL Server 2005, c’est niet, je passe donc par un trigger pour intercepter les inserts et effectuer les ventilations qui vont bien :

    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
    CREATE TRIGGER Direction_V_Insert ON Direction_V INSTEAD OF INSERT AS
      SELECT ''
      FROM   INSERTED AS x 
              JOIN Direction_regionale AS y ON x.dr_id = y.dr_id
      IF @@rowcount = 0             /* ajout d’une direction et de sa 1re agence */
        INSERT INTO Direction_regionale  
               SELECT  dr_id, dr_nom
               FROM    INSERTED
        INSERT INTO Supervision_agences  
               SELECT  sa_id_agence, dr_id
               FROM    INSERTED ;
      RETURN
      INSERT INTO Supervision_agences  /* ajout d’une agence pour une direction existante */
             SELECT  sa_id_agence, dr_id
             FROM    INSERTED ;

    Par ailleurs, si l’on supprime les agences d’une direction, il faut en conserver au moins une, d’où la mise en œuvre d’un trigger ad-hoc :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    CREATE TRIGGER Supervision_Agence_Delete ON Supervision_agences AFTER DELETE AS
      SELECT x.dr_id
      FROM   Direction_regionale AS x 
               WHERE NOT EXISTS 
                  (SELECT  '' 
                   FROM Supervision_agences AS y 
                   WHERE x.dr_id = y.sa_id_direction_regionale)
      IF @@rowcount > 0
      BEGIN 
        Raiserror ('Direction sans agence après suppression d''agences(s) : interdit!',16,1) 
      RETURN
      END


    Citation Envoyé par CinePhil Voir le message
    MCD :
    Direction_regionale -0,n----Superviser----1,1- Agence

    Tables :
    Direction_regionale (dr_id, dr_nom...)
    Agence (a_id, a_id_direction_regionale, a_nom...)

    => Pas besoin de table associative. Attention à la règle d'intégrité si on supprime la direction régionale ! Faut-il pour autant supprimer l'agence ou réaffecter celle-ci à une autre région ?
    S’il faut supprimer l’agence (ce qui est un peu violent...) : trigger ON DELETE CASCADE :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE Supervision_agences
    (
            sa_id_agence                Int          NOT NULL
          , sa_id_direction_regionale   Int          NOT NULL
        , CONSTRAINT Supervision_agences_PK PRIMARY KEY (sa_id_agence)
        , CONSTRAINT Supervision_agences_FK FOREIGN KEY (sa_id_direction_regionale) 
               REFERENCES Direction_regionale (dr_id) ON DELETE CASCADE
    ) ;
    Sinon, c'est un trigger ON DELETE RESTRICT (ou NO ACTION selon le SGBD) : il est interdit de supprimer une direction tant qu’elle a encore au moins une agence (sous-entendu qui n'a pas encore été rattachée à une autre direction, ou qui n'a pas été détruite).


    Citation Envoyé par CinePhil Voir le message
    Citation Envoyé par MacFly58 Voir le message
    1,n - 1,1

    Ici nous obtenons 2 tables (et l'identifiant de l'entité "père" devient une clé étrangère dans la table "fille")
    OK, même si je n'aime pas beaucoup les expressions d'entité père et fille que je réserve plutôt aux héritages (0,1 - 1,1).
    Les expressions table mère (père, parente), fille (enfant) sont à éviter, ou à tout le moins à utiliser avec des guillemets (du reste je plaide coupable...)

    En effet, il s’agit d’un héritage du passé, du temps où avec IMS/DL1 (le grand-frère de DB2 chez IBM) on utilisait des pointeurs nommés Physical Parent, Logical Parent, Physical Child First, Physical Child Last, etc. pour représenter des structures hiérarchiques (IMS/DL1 est un SGBD hiérarchique). Nous parlions donc en permanence de segments parents (par exemple Direction régionale) et segments enfants (Agence). A ma décharge, j’ai beaucoup utilisé IMS/DL1...

    Quant à réserver ces expressions dans le cas de l’héritage : certainement pas, car pour reprendre votre exemple, si une personne P1 est un employé, elle reste donc P1 en tant qu’employé et il est illogique de dire que P1 a pour parent P1 : P1 est P1 et pas autre chose.

    Quoi qu'il en soit, les termes Parent, Enfant, Mère, Fille, etc. sont expressément exclus du Modèle Relationnel de Données.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  6. #6
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    Pas besoin de trigger, une clé alternative fera l’affaire.
    C'est vrai. Tous mes neurones ne sont pas encore rentrés de vacances on dirait !

    Ou de tout autre type de relation, par exemple une relation de composition. Par exemple, je passe une commande et le règlement aura lieu ultérieurement
    Oui je ne citais l'héritage qu'à titre d'exemple.

    Cela dit, la table Employe n’est pas la fille de la table Personne, mais Employe représente une spécialisation de Personne.
    On peut dire aussi table mère et table fille dans le cas d'un héritage non ?

    Les expressions table mère (père, parente), fille (enfant) sont à éviter, ou à tout le moins à utiliser avec des guillemets (du reste je prêche coupable...)

    En effet, il s’agit d’un héritage du passé, du temps où avec IMS/DL1 (le grand-frère de DB2 chez IBM) on utilisait des pointeurs nommés Physical Parent, Logical Parent, Physical Child First, Physical Child Last, etc. pour représenter des structures hiérarchiques (IMS/DL1 est un SGBD hiérarchique). Nous parlions donc en permanence de segments parents (par exemple Direction régionale) et segments enfants (Agence). A ma décharge, j’ai beaucoup utilisé IMS/DL1...

    Quant à réserver ces expressions dans le cas de l’héritage : certainement pas, car pour reprendre votre exemple, si une personne P1 est un employé, elle reste donc P1 en tant qu’employé et il est illogique de dire que P1 a pour parent P1 : P1 est P1 et pas autre chose.

    Quoi qu'il en soit, les termes Parent, Enfant, Mère, Fille, etc. sont expressément exclus du Modèle Relationnel de Données.
    Dont acte !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  7. #7
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par fsmrel Voir le message
    les termes Parent, Enfant, Mère, Fille, etc. sont expressément exclus du Modèle Relationnel de Données.
    J’ai oublié de préciser quels sont les termes employés dans le cadre du Modèle Relationnel. Je fais référence à Chris Date in The Relational Database Dictionary : « enfant » est à remplacer par « référençant » (referencing) et « parent » par « référencé » (referenced).

    Dans le même sens, lorsqu'en SQL on déclare une clé étrangère (cf. vos tables Supervision_agences et Direction_regionale), celle-ci fait reférence à une clé candidate (en général primaire) :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE TABLE Supervision_agences
        ...
        CONSTRAINT Supervision_agences_FK  FOREIGN KEY (sa_id_direction_regionale) 
                   REFERENCES Direction_regionale (dr_id) ...

    Cela dit, parce qu’il y a très nettement comme une hiérarchie entre les directions et les agences, si dans le contexte SQL vous écrivez que la table Direction est « parente » de la table Agence ou que celle-ci est « fille » de l’autre, personne ne saurait vraiment vous le reprocher. Mais comme dit l’autre : Utere sed non abutere.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  8. #8
    Responsable Arduino et Systèmes Embarqués


    Avatar de f-leb
    Homme Profil pro
    Enseignant
    Inscrit en
    Janvier 2009
    Messages
    12 579
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : France, Sarthe (Pays de la Loire)

    Informations professionnelles :
    Activité : Enseignant

    Informations forums :
    Inscription : Janvier 2009
    Messages : 12 579
    Points : 56 603
    Points
    56 603
    Billets dans le blog
    40
    Par défaut
    Bonjour à tous,

    Je me pose des questions sur les associations 0,1—1,1 et 1,1—1,1.

    Par exemple, ce n’est pas la même chose d’écrire :
    1°) "une commande fait l’objet d’un règlement"
    2°) "une commande peut faire l’objet d’un règlement"
    3°) "une commande fait l’objet d’un règlement, mais qui peut avoir lieu ultérieurement"

    Dans le cas 1°), Commande---1,1----payer-----1,1----Règlement
    Dans le cas 2°), Commande---0,1----payer-----1,1----Règlement (règlement facultatif, sic !)

    Dans le cas 3°), vous écrivez :
    Citation Envoyé par fsmrel Voir le message

    Citation:
    Envoyé par CinePhil Voir le message
    2) 0,1 - 1,1
    C'est le cas classique d'un héritage.



    Ou de tout autre type de relation, par exemple une relation de composition. Par exemple, je passe une commande et le règlement aura lieu ultérieurement ...

    ...En notant bien qu’un règlement n’est pas une commande mais qu’une commande fait l’objet d’un règlement (distinguo entre « Être » et « Avoir »).
    J’ai tendance à croire que la finalité est qu’une commande doit être réglée (même si cela n’arrive qu’ultérieurement) et qu’au niveau du MCD on devrait dans ce cas écrire :
    Commande---1,1----payer-----1,1----Règlement

    J’interprète peut-être mal mais ça me trouble ce côté "ultérieur" qui du coup rendrait le règlement comme " facultatif" mais seulement temporairement…

  9. #9
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Citation Envoyé par f-leb Voir le message
    Par exemple, ce n’est pas la même chose d’écrire :
    1°) "une commande fait l’objet d’un règlement"
    2°) "une commande peut faire l’objet d’un règlement"
    3°) "une commande fait l’objet d’un règlement, mais qui peut avoir lieu ultérieurement"
    Effectivement, ce ne sont pas les mêmes phrases !

    Dans le cas 1°), Commande---1,1----payer-----1,1----Règlement
    Oui. C'est le cas si le client paye immédiatement sa commande, comme par exemple si tu vas acheter un sèche-cheveux que tu payes avec ta carte bancaire. Ou pour un achat sur internet avec le même moyen de paiement.

    Dans le cas 2°), Commande---0,1----payer-----1,1----Règlement (règlement facultatif, sic !)
    Pas forcément si idiot que ça le paiement facultatif !
    Un bon client qui achète régulièrement pour plusieurs dizaines de milliers d'euros de matériel vient chez son grossiste habituel pour remplacer le frigo de sa fille. Le patron du grossiste passe par là et voit la commande et décide de ne pas faire de facture pour cette commande.

    J’ai tendance à croire que la finalité est qu’une commande doit être réglée (même si cela n’arrive qu’ultérieurement) et qu’au niveau du MCD on devrait dans ce cas écrire :
    Commande---1,1----payer-----1,1----Règlement

    J’interprète peut-être mal mais ça me trouble ce côté "ultérieur" qui du coup rendrait le règlement comme " facultatif" mais seulement temporairement…
    Si tu mets des cardinalités 1,1 des deux côtes, ça veut dire que physiquement dans la BDD on ne peut pas créer une commande sans créer en même temps un règlement.
    Tu n'as jamais commandé du gros électroménager ou une chambre ou des travaux que tu n'as pas payé tout de suite ?

    La bonne modélisation est bien de mettre 0,1 (s'il ne peut y avoir qu'un seul paiement pour une commande) ou 0,n (si la commande peut être payée en plusieurs fois, ce qui quand même fréquent non ?).

    Ça ne veut nullement dire que le paiement est facultatif mais seulement qu'on peut créer une commande sans y associer tout de suite un paiement. C'est très fréquent comme démarche ! Je peux même te dire que dans les entreprises de BTP, c'est quasi systématique !

    Après c'est au système d'information d'éventuellement prévoir un système de relance des commandes non payées, ou plutôt des factures non payées... car il faut commencer par émettre une facture pour demander à quelqu'un de payer !
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  10. #10
    Membre régulier
    Profil pro
    Inscrit en
    Janvier 2005
    Messages
    104
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Janvier 2005
    Messages : 104
    Points : 112
    Points
    112
    Par défaut Problème d'interprétation
    Bonjour

    J’ai tendance à croire que la finalité est qu’une commande doit être réglée (même si cela n’arrive qu’ultérieurement) et qu’au niveau du MCD on devrait dans ce cas écrire :
    Commande---1,1----payer-----1,1----Règlement

    J’interprète peut-être mal mais ça me trouble ce côté "ultérieur" qui du coup rendrait le règlement comme " facultatif" mais seulement temporairement…
    Le modèle doit prendre en compte la réalité de la situation et non sa finalité.
    L'association Commande---0,1----payer-----1,1----Règlement ne signifie pas que le règlement est facultatif mais qu'à un instant T (considéré comme un état stable du système), une commande peut exister sans règlement. Ce qui est généralement le cas au moment de la transaction de création de la commande.
    Par ailleurs, l'association entre la commande et le règlement peut être totalement différente selon le contexte :
    - Lorsque le client décide de ne pas payer, il y a des commandes sans règlement.
    - Lorsque le fournisseur fait un cadeau à son client, il y a des commandes sans règlement.
    - Lorsque la commande nécessite un échéancier de paiement, il y a plusieurs règlements pour une seule commande
    - Lorsqu'un client possède une grosse ardoise chez son fournisseur, il verse un seul règlement pour un ensemble de commandes.

    Pour revenir au sujet principal du post, dans le cas d'une relation d'héritage, le choix sur le nombre de tables peut varier selon le besoin.
    Même si conceptuellement il peut être intéressant de distinguer l'employé de la personne par l'association Employe --- (1,1) --- Etre --- 0,1 --- Personne
    La construction au niveau logique pourra être différente car on peut ne vouloir qu'une seule table.
    C'est le même type de problème pour une relation 1,1 - 1,1. Soit on veut une table, soit on veut 2 tables. L'algorithme pour passer du MCD au MLD ne peut pas faire l'économie de cette question.

  11. #11
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Bonsoir,

    Citation Envoyé par apad Voir le message
    Même si conceptuellement il peut être intéressant de distinguer l'employé de la personne par l'association
    Employe --- (1,1) --- Etre --- 0,1 --- Personne
    La construction au niveau logique pourra être différente car on peut ne vouloir qu'une seule table.
    Conceptuellement, on ne distingue pas l’employé de la personne : un employé est une personne.

    Maintenant, au niveau du MLD on peut effectivement mettre en œuvre une table Employe phagocytant les attributs décrits dans le surtype Personne, même chose pour les autres spécialisations de ce surtype. Mais alors il n’y aura pas de table Personne (one fact, one place) et les associations établies entre Personne et toutes autres entités-types (Adresse, Prestation, Historique de ceci, Historique de cela, etc.) devront être faites avec chaque sous-type. Ça n’est pas une critique, mais un rappel concernant l’organisation de l’algorithme. Personnellement, au nom du principe de l’indépendance logique, je suis plutôt favorable à la mise en œuvre d’une table pour le surtype Personne et d’autant de tables qu’il y a de sous-types (Employé, Tiers, etc.) sachant que dans l’arsenal relationnel on dispose des vues pour décrire complètement chaque sous-type joint avec son surtype. Les vues sont des tables, virtuelles certes, mais tables de plein droit, que l’on peut donc mettre à jour (INSERT, UPDATE, DELETE) comme n’importe quelle autre table (cf. ci-dessus) : Les utilisateurs et les programmes ne verront et mettront à jour que les vues décrivant les employés, les tiers, etc., en ignorant la réalité des tables de base sous-jacentes. Voyez aussi la discussion avec Heledir, pour la partie concernant les opérations INSERT, etc. en relation avec surtypes/sous-types.


    Dans le cas des commandes et règlements, si on modélise ainsi :
    [Commande]---0,1----(Payer)-----1,1----[Règlement]
    • Soit on adhère à la théorie relationnelle (voyez l’ouvrage qui fait autorité en la matière : Databases, Types and the Relational Model, paragraphe "RM proscription 4: No NULLS") et on définit deux tables (variables relationnelles au sens de cette théorie) ;
    • Soit on n’adhère pas et on définit une seule table, mais en acceptant les anomalies, les dangers potentiels inhérents à NULL lors des opérations. En l’occurrence, la table ne correspondra pas à une variable relationnelle (une variable relationnelle prend des valeurs qui sont des relations, mais les attributs d’une relation ne peuvent être marqués NULL : je vous renvoie à la page 23 de l’ouvrage de référence que j’ai cité, paragraphe "Properties of Relations").
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  12. #12
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Dans la série "On dérive"...

    @MacFly58

    Je vous propose de fournir le MCD et les règles de dérivation dans le cas suivant (courses hippiques) :

    • Un prix a lieu une fois par an.
    • A un prix ont participé plusieurs chevaux.
    • Un cheval a pu participer à plusieurs prix.
    • Un cheval a pu participer plusieurs fois au même prix.
    • On connaît la place à laquelle a terminé un cheval lors d'un prix.


    Exemples :

    Le grand prix de Saint-Cloud a été remporté en 2010 par Plumania, devant Youmzain. Daryakakna prend la 3e place devant Célimène.

    Le Prix d’Amérique, édition 2009, a été remporté par Meaulnes du Corta devant Nouba du Saptel (deuxième) Qualita Bourbon (troisième), et Olga du Biwetz (quatrième).

    Le prix de l’Arc de Triomphe a été remporté en 2009 par Sea the stars devant Youmzain. Cavalryman finit à la troisième place devant Conduit quatrième.

    Le prix de l’Arc de Triomphe a été remporté en 2008 par Zarkava devant Youmzain. Soldier of fortune finit troisième, It’s Gino quatrième, tandis que Vision d’État ne finit que 5e.

    Dylan Thomas a remporté le Prix de l'Arc de Triomphe en 2007, il a fini 2e aux International Stakes de 2007, 2e de la Tattersalls Gold Cup la même année, ainsi que 3e au Derby d’Epsom (remporté par Authorized).

    Diomed a remporté le Derby d’Epsom en 1780 (oui, il y a deux-cent-trente ans...)

    Le MLD actuel (extrait) est le suivant :

    Code Tutorial D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    VAR PRIX BASE RELATION {PrixId Integer, PrixNom CHAR, ...}
        KEY {PrixId} ;
    
    VAR CHEVAL BASE RELATION {ChevalId Integer, ChevalNom CHAR, ...}
        KEY {ChevalId} ;
    
    VAR PALMARES BASE RELATION {ChevalId Integer, PrixId Integer, Annee Integer, Place Integer}
        KEY {ChevalId, PrixId, Annee} ;
        FOREIGN KEY {PrixId} REFERENCES PRIX {PrixId}
        FOREIGN KEY {ChevalId} REFERENCES CHEVAL {ChevalId} ;

    Le but est de produire le même MLD.

    __________________________

    Edit :

    N.B. On aurait pu vouloir doter la table PALMARES d'une clé alternative {Place, PrixId, Annee}, mais il faut tenir compte des ex aequo (dead-heat). On peut néanmoins réfléchir à l'impact sur la modélisation de la mise en oeuvre de ce genre de clé, en ajoutant à cette table un attribut tel que la position du cheval au départ (position par rapport à la corde).
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  13. #13
    Membre actif
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    311
    Détails du profil
    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 311
    Points : 257
    Points
    257
    Par défaut
    Bonjour,

    Conclusion :
    Les seuls cas où la table associative est nécessaire pour éviter les clés étrangères à NULL sont :
    1) 0,1 - 0,1
    3) 0,1 - 0,n
    4) 0,1 - 1,n
    dans quels cas faut-il empêcher la clef étrangère de pouvoir être NULL ? Je ne vois pas ce qui est gênant d'avoir NULL...

  14. #14
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Citation Envoyé par Tidus159 Voir le message
    dans quels cas faut-il empêcher la clef étrangère de pouvoir être NULL ? Je ne vois pas ce qui est gênant d'avoir NULL...
    Relis tout le topic et les liens qui figurent dans quelques messages !

    NULL est notamment interdit par la théorie relationnelle, bien qu'accepté par les SGBD. Ça peut poser des problèmes lors des tris, des calculs, voire au niveau des performances de certaines requêtes avec de gros volumes.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  15. #15
    Membre actif
    Étudiant
    Inscrit en
    Avril 2008
    Messages
    311
    Détails du profil
    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : Avril 2008
    Messages : 311
    Points : 257
    Points
    257
    Par défaut
    J'ai relu en diagonale ce topic et n'avais pas vu l'information...

    Ma prof' qui est une spécialiste des SI ne nous a jamais parlé de ces raisons et n'a jamais évoqué le besoin d'une table associative.
    (J'aimerais bien voir des cas comme ça.)

    Néanmoins, merci pour ta réponse (un brin agressive).

    @+,
    Emilien.

  16. #16
    Membre régulier
    Homme Profil pro
    Relationland initiate
    Inscrit en
    Novembre 2006
    Messages
    83
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Relationland initiate

    Informations forums :
    Inscription : Novembre 2006
    Messages : 83
    Points : 120
    Points
    120
    Par défaut Limites de l'exercice
    Bonjour,

    Bien que convaincu par le principe, je n'ose pas encore sacrifier au rasoir d'Ockham mon habitude de mettre une surrogate key (identifiant technique) par table et une clé unique (ou +) orientée métier. J'espère que cet discussion m'aidera à franchir le pas. Merci à MacFly38 pour l'avoir lancée et à fsmrel pour le partage de son savoir.

    L'intervalle de validité de la réflexion me titille :
    le NULL est-il aussi a interdire pour tous les attributs à la valeur inconnue à compléter + tard (ex : NoSécuritéSociale non-communiqué)
    Je vois 2 possibilités :
    - autoriser le NULL si l'attribut n'est ni clé étrangère ni participant à une clé primaire ou unique
    - externaliser tout attribut NULLable
    Fais mourir ton ennemi de plaisir ! Si tu le rates, il mourra d'ennui...
    __________________

    Pensez à cliquer sur

  17. #17
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 793
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 60
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Ingénieur d'études en informatique
    Secteur : Enseignement

    Informations forums :
    Inscription : Août 2006
    Messages : 16 793
    Points : 34 024
    Points
    34 024
    Billets dans le blog
    14
    Par défaut
    Citation Envoyé par pfortin Voir le message
    L'intervalle de validité de la réflexion me titille :
    le NULL est-il aussi a interdire pour tous les attributs à la valeur inconnue à compléter + tard (ex : NoSécuritéSociale non-communiqué)
    Je vois 2 possibilités :
    - autoriser le NULL si l'attribut n'est ni clé étrangère ni participant à une clé primaire ou unique
    - externaliser tout attribut NULLable
    Fsmrel tire à vue sur le bonhomme NULL mais je suis plus nuancé que lui.

    Si le numéro de sécu fait partie des attributs de tous les membres de l'entité considérée, je laisse celui-ci dans l'entité et j'autorise le NULL dans la table s'il est permis par les règles de conception que celui-ci ne soit pas connu.
    Autre exemple : une BDD sur le cinéma avec des artistes dont certains sont morts (Charles Chaplin) et d'autres encore en vie (Gérard Depardieu). Je ne vais pas externaliser la date de décès sous prétexte que tous les artistes ne sont pas morts. Je trouverais quand même ça un peu lourdingue.

    Par contre, si une très grande majorité de membres d'une entité ont une information secondaire manquante, il peut être plus intéressant de faire une spécialisation de ceux pour lesquels cette information est présente. Je n'ai pas d'exemple en tête.
    Philippe Leménager. Ingénieur d'étude à l'École Nationale Supérieure de Formation de l'Enseignement Agricole. Autoentrepreneur.
    Mon ancien blog sur la conception des BDD, le langage SQL, le PHP... et mon nouveau blog sur les mêmes sujets.
    « Ce que l'on conçoit bien s'énonce clairement, et les mots pour le dire arrivent aisément ». (Nicolas Boileau)
    À la maison comme au bureau, j'utilise la suite Linux Mageïa !

  18. #18
    Membre expérimenté
    Profil pro
    Inscrit en
    Juillet 2006
    Messages
    1 103
    Détails du profil
    Informations personnelles :
    Âge : 46
    Localisation : France, Meurthe et Moselle (Lorraine)

    Informations forums :
    Inscription : Juillet 2006
    Messages : 1 103
    Points : 1 561
    Points
    1 561
    Par défaut
    un exemple de données "nullable" à externaliser ?

    pourquoi pas un numéro de SIRET pour une personne... et oui une personne peut en avoir un (autoentrepreneur), sans pour autant que ce soit la grande majorité...
    je suis confronté à ce truc là sur le projet sur lequel je travail, et le siret comme pas mal d'autres données finalement viennent de finir leurs jours dans une table d'externalisation, parce que j'ai besoin de ces informations, mais sur 100 000 entrées, j'en ai à tout casser une vingtaine qui ont se numéro...
    personnellement j'ai rien contre le null, même dans une foreign key... surtout dans le cas d'une relation circulaire.
    je sais que c'est mal... et alors ? le sgbd l'autorise...
    il faut parfois aussi ne pas trop abuser sur les règles... tout dépend du niveau de criticité du domaine des données... car souvent ca équivaut à écraser une mouche au marteau pilon.
    maintenant c'est sure sur un système avec montée en charge, il me viendrais pas à l'idée d'avoir un null dans une foreign key pour modéliser un 0-1... mais bon tout est relatif et je doute qu'il soit nécessaire de sortir l'arsenal nucléaire pour une base de 10mo...

    pour la notion d'héritage... en fait, il n'y a pas de méthode toute faite... dans quelques cas, il est plus intéressant d'avoir une seule table et dans d'autres, une table pour l'entité à spécialiser, et 1 par spécialisation... tout dépend du contexte, et notamment dans le contexte, si une contrainte dépend d'une de ces spécialisations, plutôt que de l'entité en règle générale.
    c'est pourquoi encore une fois répondre avec un algorithme aveugle n'est pas une solution, et ne remplacera jamais l'oeil du dba.

  19. #19
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Citation Envoyé par pfortin Voir le message
    Bien que convaincu par le principe, je n'ose pas encore sacrifier au rasoir d'Ockham mon habitude de mettre une surrogate key (identifiant technique) par table et une clé unique (ou +) orientée métier. J'espère que cet discussion m'aidera à franchir le pas.
    Quel principe ? Je ne vois pas le rapport avec la cardinalité 0,1. Quoi qu’il en soit, l’utilisation d’un identifiant technique est très vivement recommandée, afin de garantir la stabilité des liens entre les tables. Les clés « métier », alternatives ne sont qu’un moyen, d’une part de garantir les contraintes d’unicité imposées par les règles de gestion des données recensées dans les dossiers de conception, d’autre part de garantir un moyen qu’ont les utilisateurs d’accéder aux données (les surrogate keys lui étant inaccessibles). L’exemple typique est celui du numéro SIREN des entreprises qui doit faire l’objet d’une clé alternative parce que modifiable par l’INSEE (correction des erreurs d’attribution de ce numéro).
    Par ailleurs, quel pas souhaitez-vous franchir ?

    Concernant NULL, j’ai fait quelques remarques en réponse à question posée par MacFly58.

    Par ailleurs, le Modèle Relationnel de Données (ou théorie relationnelle) repose sur la théorie des ensembles et la logique des prédicats. Il traite des relations n-aires (relation au sens mathématique du terme, voyez le paragraphe 1.2 de l’article Bases de données et normalisation), tandis que le modèle SQL (Cf. Date on Database Writings 2000-2006 page 140) s’intéresse aux tables (qui sont comme des images en 2D des relations), sans même exiger que celles soient dotées d’une clé, et il faut prendre énormément de précautions pour que celles-ci se comportent comme des relations : Le modèle SQL est la réalisation d’une abstraction qui n’est pas le Modèle Relationnel de Données.

    Ainsi, reprenons l’exemple de CinePhil :
    Direction_regionale -1,n----Superviser----(0,1)- Agence
    CinePhil a proposé une réalisation conforme au Modèle Relationnel de Données :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    Direction_regionale (dr_id, dr_nom...)
    Agence (a_id, a_nom...)
    Supervision_agences (sa_id_agence, sa_id_direction_regionale).
    Si on utilise une réalisation conforme seulement au modèle SQL :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    Direction_regionale (dr_id, dr_nom...)
    Agence (a_id, a_nom, sa_id_direction_regionale...)
    Alors l’attribut sa_id_direction_regionale de la table Agence peut accepter la présence du bonhomme NULL :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    Agence (a_id, a_nom,       sa_id_direction_regionale...)
             1    'Agence 1'   'Toulouse' 
             2    'Agence 2'   NULL
            ...   ...          ...
    Considérons le triplet <1, 'Agence 1', 'Toulouse'> de la table Agence : du point de vue du Modèle Relationnel de Données c’est un tuple (n-uplet) authentique.

    Considérons le triplet <2, 'Agence 2', NULL> de la table Agence : du fait de la présence de NULL, du point de vue du Modèle Relationnel de Données ce n’est pas un tuple.

    En conséquence, les éléments du corps d’une relation (au sens du Modèle Relationnel de Données) ne pouvant être que des tuples, Agence n’est pas une relation. En toute rigueur, une base de données relationnelle ne comportant que des relations, on devrait faire le distinguo entre base de données relationnelle et base de données SQL. Le modèle SQL est du reste bien laxiste (norme SQL:2003) puisqu'il ne fait mention ni du Modèle Relationnel de Données ni du terme relation.


    Citation Envoyé par Tidus159 Voir le message
    Ma prof' qui est une spécialiste des SI ne nous a jamais parlé de ces raisons et n'a jamais évoqué le besoin d'une table associative.
    Les spécialistes des SI ne sont pas les mieux placés pour parler de la modélisation des bases de données relationnelles. Ainsi, la littérature commise par les ténors de Merise est truffée d’erreurs ès matière. Si les spécialistes des SI se disent aussi spécialistes des bases de données relationnelles, alors ils doivent savoir qu’une clé étrangère marquée nulle est source de prétendus tuples qui n’en sont pas du point de vue du Modèle Relationnel de Données, voyez ci-dessus.

    Maintenant, si on n’adhère pas au Modèle Relationnel de Données, on peut recommander n’importe quoi (parce qu’on l’a appris ainsi), façon Messieurs Diafoirus père & fils.


    Citation Envoyé par Tidus159 Voir le message
    J'aimerais bien voir des cas comme ça.
    Qu’entendez-vous par là ?


    Citation Envoyé par CinePhil Voir le message
    Citation Envoyé par pfortin Voir le message
    L'intervalle de validité de la réflexion me titille :
    le NULL est-il aussi a interdire pour tous les attributs à la valeur inconnue à compléter + tard (ex : NoSécuritéSociale non-communiqué)
    Je vois 2 possibilités :
    - autoriser le NULL si l'attribut n'est ni clé étrangère ni participant à une clé primaire ou unique
    - externaliser tout attribut NULLable
    Fsmrel tire à vue sur le bonhomme NULL mais je suis plus nuancé que lui.

    Si le numéro de sécu fait partie des attributs de tous les membres de l'entité considérée, je laisse celui-ci dans l'entité et j'autorise le NULL dans la table s'il est permis par les règles de conception que celui-ci ne soit pas connu.
    Si je tire à vue, c’est que je me dois de parler au nom du Modèle Relationnel de Données, du Relationland (le pays des relations), lequel est entouré par une épaisse muraille qui a pour nom le Askew Wall. Si donc j’utilise un SGBD relationnel conforme, je mettrai par exemple en oeuvre un indicateur permettant de signaler que le numéro de sécurité sociale d’une personne est présent, inconnu, sans objet, etc. (même chose si j’avais eu à utiliser ISBL) :

    Code Tutorial D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    TYPE NumeroSecuIndic POSSREP {X CHAR
                                  CONSTRAINT X IN ('OK', 'Inconnu', 'Sans objet', 'Confidentiel')} ;
    ...
    VAR Personne BASE RELATION
        {PersonneId  INT, PersonneNom CHAR, NumeroSecu CHAR, NumeroSecuIndic NumeroSecuIndic}
         KEY {PersonneId}
         KEY {NumeroSecu} ;
    Exemple :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    PersonneId   PersonneNom  NumeroSecu      NumeroSecuIndic
    ----------   -----------  ----------      ---------------
        1        Albert       1920133001192   OK
        2        Bernard                      Inconnu
        3        Charlotte                    Sans objet
        4        Dany                         Confidentiel
       ...       ...          ...             ...
    Dans cet exemple, on reste calé sur la logique binaire classique, pour laquelle les valeurs de vérité sont VRAI et FAUX, alors que NULL oblige à en passer par une logique ternaire pour laquelle les valeurs de vérité sont VRAI, FAUX et INCONNU.

    Citation Envoyé par CinePhil Voir le message
    Je ne vais pas externaliser la date de décès sous prétexte que tous les artistes ne sont pas morts. Je trouverais quand même ça un peu lourdingue.
    Un p’tit indicateur pour préciser si l’artiste est en vie, mort, ou si on ne sait pas ?

    A cette occasion, je cite les frères Jolivet (dans les années soixante-dix) qui avaient une approche particulière de NULL :
    « 40% des gens ont un chat, 30% un chien et 30% n’ont pas d’opinion ».

    Citation Envoyé par cinemania Voir le message
    sur 100 000 entrées, j'en ai à tout casser une vingtaine qui ont se numéro...
    Raison de plus pour travailler proprement et utiliser une table dédiée. Supposons que l’on veuille accéder aux données d’un auto-entrepreneur à partir de son SIRET, en SQL on pourra donc avoir à utiliser une requête du genre :

    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    SELECT ...
    FROM   PERSONNE
    WHERE  NoSiret = '...'
    SQL utilisera l’index qu’on aura défini sur l’attribut NoSiret, lequel sera d’une hauteur > 1 et, au niveau feuille, comportera 100 000 valeurs de clés.
    Si vous externalisez l’attribut NoSiret dans une table dédiée (appelons-la Personne_Siret), la hauteur de l’index sera égale à 1 et il tiendra dans un seul enregistrement physique (tout comme la table). C'est un moyen d'éviter la gabegie, surtout si vous avez plusieurs tables qui sont dans la même situation (conseil de DBA).

    Par ailleurs, au nom de l’indépendance logique, rien ne vous empêche de définir une vue qui soit la jointure naturelle des tables PERSONNE et Personne_Siret, à usage des utilisateurs (les développeurs, les programmes...) qui n’auront pas à connaître la structure réelle des tables sous-jacentes (principe de l'indépendance logique).

    Citation Envoyé par cinemania Voir le message
    comme pas mal d'autres données
    Raison de plus pour ne pas tout mettre dans le même panier, rendre les tables et les index obèses. Voyez ce que je viens d'écrire concernant la gabegie.

    Citation Envoyé par cinemania Voir le message
    personnellement j'ai rien contre le null, même dans une foreign key... surtout dans le cas d'une relation circulaire.
    je sais que c'est mal... et alors ? le sgbd l'autorise...
    Manifestement, outre écrire correctement en français, bâtir un édifice solide et fiable n’est pas votre premier souci, vous êtes partisan de l’approche Nouf-Nouf et Nif-Nif



    plus que de l’approche Naf-Naf...



    Par ailleurs, selon les tables de vérité qu’il utilise, votre SGBD peut avoir un comportement anormal en ce qui concerne l’utilisation de la logique trivaluée (qu’il est obligé de connaître puisqu’il met en œuvre NULL).
    Au lieu de se limiter aux valeurs de vérité VRAI et FAUX de la logique binaire, il doit aussi tenir compte de la valeur de vérité INCONNU. Les tables de vérité qu’il utilise sont les suivantes (v est l’abréviation de VRAI, f celle de FAUX et i celle de INCONNU) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    NON |          OU | v i f        ET | v i f  
    ----|---       ---|-------       ---|-------
     v  | f         v | v v v         v | v i f
     i  | i         i | v i i         i | i i f
     f  | v         f | v i f         f | f f f
    Pour les connecteurs logiques NON, OU, ET, tout va bien. Passons au conditionnel SI p ALORS q, qui se ramène à (NON p) OU q et au biconditionnel p SI ET SEULEMENT SI q qui se ramène à (SI p ALORS q) ET (SI q ALORS p). Les tables de vérité sont alors les suivantes :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SI | v i f         SSI | v i f       
    ---|-------        ----|-------     
     v | v i f          v  | v i f      
     i | v i i          i  | i i i      
     f | v v v          f  | f i v
    Et dans ce cas, il y a des tautologies qui n’en sont plus, telle celle-ci : SI p ALORS p au cas où p est inconnu. Par exemple : « Si je chante alors je chante » n’est plus une évidence. Même chose pour une contradiction telle que p ET NON p : « Je chante et je ne chante pas » n’est plus une contradiction. De la même façon, dans le cas du biconditionnel, p SI ET SEULEMENT SI p, on retrouve les mêmes problèmes : « Je chante si et seulement si je chante » n’est plus une tautologie.
    Êtes-vous certain que votre SGBD utilise de telles tables de vérité, que ce soit celles-ci ou d’autres ? La référence en en ce qui concerne les logiques multivaluées, Nicholas Rescher in Many-Valued Logic rappelle que l’équivalence doit être réflexive, c'est-à-dire que p SI ET SEULEMENT SI p est nécessairement vrai, pour toutes les valeurs de p, contrainte qui est violée si la valeur de vérité de p est i (inconnue). On peut toujours s’essayer a utiliser d’autres tables de vérité : on débouche sur de nouveaux problèmes de tautologies.
    => L’optimiseur de votre SGBD peut fournir des résultats faux.
    Pour approfondir le sujet, je vous renvoie à l’ouvrage de Chris Date : Date on Database: Writings 2000-2006, au chapitre 18 « Why three - And four-valued logic don’t work ».


    Citation Envoyé par cinemania Voir le message
    je doute qu'il soit nécessaire de sortir l'arsenal nucléaire pour une base de 10mo...
    Il n’y pas que des bases de données de 10 Mo... Ceux qui ont la responsabilité d’une base de données de 2000 tables dont la volumétrie de certaines atteint le milliard de lignes, ne peuvent être que des gens rigoureux et dignes de confiance, qui doivent exercer au quotidien une vigilance extrême, sinon bonjour les dégâts.

    Citation Envoyé par cinemania Voir le message
    pour la notion d'héritage... en fait, il n'y a pas de méthode toute faite... dans quelques cas, il est plus intéressant d'avoir une seule table et dans d'autres, une table pour l'entité à spécialiser, et 1 par spécialisation...
    Le concepteur conçoit. C’est en accord avec le DBA que la décision sera prise de la structure finale. On tient compte par exemple des relations entretenues par l’entité-type surtype avec les autres entités-types (Adresse, Prestation, Historique de ceci, Historique de cela, etc. comme je l’ai déjà précisé). Une fois de plus, au nom de l’indépendance logique, grâce au mécanisme des vues, on sait définir des tables virtuelles Tiers, Fournisseur, Employé, etc., de telle sorte que les utilisateurs, notamment les programmes, n’aient pas à connaître la structure réelle des tables sous-jacentes. En cas de doute, le choix définitif des tables de base passera par la mise en œuvre d’un prototype de performances.

    Citation Envoyé par cinemania Voir le message
    c'est pourquoi encore une fois répondre avec un algorithme aveugle n'est pas une solution, et ne remplacera jamais l'oeil du dba.
    L’étudiant ne peut guère faire autrement qu’appliquer les règles qu’on lui enseigne, lesquelles sont parfois trop simples. Sur le terrain, l’ingénieur fait valoir l’expérience qu’il a acquise, parfois au prix de quelques claques. Quand il s’engage vis-à-vis de la Maîtrise d’ouvrage (je sais de quoi je parle), les pénalités financières sont là pour le stimuler. Et même s’il n’est pas un perdreau de l’année, vingt fois, cent fois sur le métier il remettra son ouvrage et, je le répète, prototypera, à l'instar du DBA Naf-Naf.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  20. #20
    Membre régulier
    Homme Profil pro
    Relationland initiate
    Inscrit en
    Novembre 2006
    Messages
    83
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 51
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Relationland initiate

    Informations forums :
    Inscription : Novembre 2006
    Messages : 83
    Points : 120
    Points
    120
    Par défaut
    Je ne pensais pas déclencher autant de réponses. Merci à leurs auteurs.
    Quel principe ? Je ne vois pas le rapport avec la cardinalité 0,1. Quoi qu’il en soit, l’utilisation d’un identifiant technique est très vivement recommandée, afin de garantir la stabilité des liens entre les tables. Les clés « métier », alternatives ne sont qu’un moyen, d’une part de garantir les contraintes d’unicité imposées par les règles de gestion des données recensées dans les dossiers de conception, d’autre part de garantir un moyen qu’ont les utilisateurs d’accéder aux données (les surrogate keys lui étant inaccessibles). L’exemple typique est celui du numéro SIREN des entreprises qui doit faire l’objet d’une clé alternative parce que modifiable par l’INSEE (correction des erreurs d’attribution de ce numéro).
    Par ailleurs, quel pas souhaitez-vous franchir ?
    Pour préciser mon propos certes flou :
    Le principe est de ne pas stocker de valeur NULL.
    La conviction acquise est que la vie est plus belle au RelationLand.
    Le pas à franchir consiste à sortir de SqlLand pour y entrer.
    Au passage, merci du visa que vous nous fournissez.
    Quant au rapport avec les relations, c'est plus bas.
    Concernant NULL, j’ai fait quelques remarques en réponse à question posée par MacFly58.
    Je m'efforce de lire tous vos posts dont celui-ci (qui est à l'origine de cette discussion).
    J'avoue un retard sur le lien vers "ON THE NOTHING THAT’S WRONG WITH NULLS" pour cause de vacances.
    Je promets de me rattraper et de le lire ainsi que toutes les nouvelles références que vous faites.
    Par ailleurs, le Modèle Relationnel de Données (ou théorie relationnelle) repose sur la théorie des ensembles et la logique des prédicats. Il traite des relations n-aires (relation au sens mathématique du terme, voyez le paragraphe 1.2 de l’article Bases de données et normalisation), tandis que le modèle SQL (Cf. Date on Database Writings 2000-2006 page 140) s’intéresse aux tables (qui sont comme des images en 2D des relations), sans même exiger que celles soient dotées d’une clé, et il faut prendre énormément de précautions pour que celles-ci se comportent comme des relations : Le modèle SQL est la réalisation d’une abstraction qui n’est pas le Modèle Relationnel de Données.
    Si j'ai glissé vers le vocable SQL, c'était tout à fait volontaire : je pensais en particulier aux tables issues des relations pour lesquelles j'ai la (mauvaise ?) habitude de créer aussi une surrogate key dont je doute de plus en plus de la pertinence, la clé alternative constituée de surrogate keys étrangère(s) n'étant peut-être pas si "métier" que ça à bien y réfléchir. J'y perds mon latin.
    Pour moi, c'était aussi un bon moyen de limiter la propagation de clés composites susceptibles d'évolutions ultérieures.

    J'ai pris un mauvais exemple. Le numéro de sécu est un terrain miné. Il ne s'agissait pas de parler de clé candidate mais d'un attribut quelconque.
    J'aurai dû citer le nom de naissance, la taille en cm, la couleur des yeux, etc. Toutes ces données existent sans pour autant être toutes connues le jour de la saisie.
    Un indicateur complémentaire semble être la réponse adéquate.

    Je suis justement confronté à une base de cabinet comptable où quasiment toute donnée est optionnelle jusqu'à la période fiscale afin d'avancer les saisies.
    Mais au moment d'éditer les déclarations fiscales, tout doit être renseigné. J'ai donc une énorme quantité d'indicateurs à ajouter...
    J'adhère à l'idée mais je vais m'abstenir de la mettre en oeuvre partout. Question de survie si je croise les développeurs (ce qui se produit chaque matin ).
    Dans la prochaine version, par contre, j'y songerai très sérieusement.

    Dans votre exemple, les valeurs de NumeroSecu non renseignées sont-elles des chaines vides ? Que stockez-vous dans les cas d'une variable numérique ?

    PS : Si j'ai bien compris son post, Cinemania a déjà externalisé le SIRET ce qui rend votre charge quelque peu disproportionnée...
    Fais mourir ton ennemi de plaisir ! Si tu le rates, il mourra d'ennui...
    __________________

    Pensez à cliquer sur

Discussions similaires

  1. Réponses: 1
    Dernier message: 07/12/2008, 15h38
  2. passage du MCD AU MLD
    Par stef51 dans le forum Schéma
    Réponses: 2
    Dernier message: 12/06/2007, 09h31
  3. [merise] passage de MCD a MLD
    Par dj_cue dans le forum Schéma
    Réponses: 9
    Dernier message: 01/04/2006, 00h06
  4. Passage du MCD en MLD en MPD
    Par shinshon dans le forum Schéma
    Réponses: 3
    Dernier message: 02/11/2005, 16h42
  5. MCD ou MLD pour postgresql?
    Par jujuz dans le forum PostgreSQL
    Réponses: 2
    Dernier message: 26/01/2005, 23h22

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo