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

Schéma Discussion :

Association n-aire, date et cardinalité [MCD]


Sujet :

Schéma

  1. #21
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    euh, encore 1 question:

    lorsqu'on est dans une situation où on doit créer une entité qui peut être identifié par des identifiants relatives, quelle est la solution la plus recommandée: utiliser des clé relatives comme dans l'image si dessous, ou créer un identifiant propre à cette entité même si il n'a aucune signification sur le plan pratique(pour le veterinaire qui va utiliser le logiciel je veux dire).

    et si la solution des clés relatives est la plus convenable, une entité sans aucun attribut (comme le présente l'image si dessous) est elle acceptable?


    à propos de l'image si dessous, voici les informations qui ont conduits à créer cet MCD:

    - une vache peut attraper 1 à plusieurs maladie par date.
    - théoriquement à une maladie peut correspondre 0 à plusieurs medicaments, et un médicament peut soigner 1 à plusieurs maladies.
    - pour traiter medicalement à une date donnée une vache ayant 1 à plusieurs maladies, on utilise 1 à plusieurs médicaments tout en respectant les relations théoriques entre les medicaments et les maladies.

    mon utilisation de 2 clés relatives est-elle correcte?
    Mon MCD répond-il correctement à l'énoncée?

    merci d'avance
    Images attachées Images attachées  

  2. #22
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 002
    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 : 8 002
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Tout d’abord, prenez l’habitude de nommer au moins les identifiants des entités-types, ça facilite le dialogue.

    L’entité-type Traitement_Soin peut parfaitement ne comporter aucun attribut au niveau conceptuel, car vous en avez fait une entité-type associative, ce qui est tout à fait possible. Au niveau logique, elle fera l’objet d’une table dont l’en-tête comportera deux attributs, correspondant aux identifiants des deux entités-types VACHE et DATE (j’utilise à nouveau Power AMC) :



    Puisque vous cherchez à passer par l’identification relative, j’aurais tendance à simplifier, car l’entité-type DATE n’est pas utile ici. TRAITEMENT_SOIN n’est qu’une propriété multivaluée de l’entité-type VACHE, ce que l’on appelle encore une entité-type faible (weak entity) ou caractéristique. Elle est identifiée relativement à VACHE, l’attribut DateSoin servant à distinguer les occurrences multiples :



    Et au niveau du MLD, la structure de la table TRAITEMENT_SOIN sera la suivante, quelle que soit la solution conceptuelle retenue :





    Du fait des contraintes (légitimes) dont il est chargé, votre MCD me paraît un peu compliqué...






    Plaçons-nous donc directement au niveau MLD, où les choses sont quand même plus faciles à représenter. Pour vous suivre, je pars du principe qu’une vache commence par attraper plusieurs maladies en même temps (pauvre bête...) et la même maladie à des dates différentes (faudra voir à forcer la dose du traitement...)



    La table ATTRAPER a donc pour clé primaire le triplet {AnimalId, MalId, DateMaladie}. Ceci se voit sur le graphique grâce au mickey <pk> (comme primary key). Le lien avec la table VACHE fait l’objet d’une clé étrangère référençant cette table, repérable par le mickey <fk1> (fk comme foreign key). Même principe pour le lien avec MALADIE.

    Le graphique ci-dessous prend en compte les liens MALADIE - MEDICAMENT (table CORRESPONDRE).



    Il n’y a plus qu’à brancher les tables ATTRAPER et CORRESPONDRE :



    On voit sur ce graphique que pour la table TRAITER, l’attribut MalId participe à deux clés étrangères, référençant la table ATTRAPER d’une part et la table CORRESPONDRE d’autre part. Ainsi, on respecte la règle qui veut qu’une vache soit soignée pour la maladie qu’elle a attrapée et pas une autre.

    Linterprétation de TRAITER est la suivante :

    La vache AnimalId a attrapée la maladie MalId à la date DateMaladie et a été traitée avec le médicament MedicId à la date DateTraitement.

    On par du principe que pour la maladie en question, la vache a pu être soignée plusieurs fois au moyen d’un médicament donné.



    Maintenant, si je demande à Power AMC de générer un MCD à partie de ce MLD, on obtient ceci (représentation non pas Merise, mais Entité/Relation) :



    C'est ce qu'on veut. Mais attention ! Si je demande la génération d’un MLD à partir de ce MCD, le résultat sera un MLD différent du précédent. En effet, au stade du MCD, on ne sait pas (moi en tout cas) signifier à l’AGL que la maladie attrapée (MalId) correspond à la maladie traitée (MalidCorr) :



    Autrement dit, si l’on part directement du MCD, il faudra (hélas !) nettoyer le MLD manuellement (table TRAITER) pour aboutir à celui qui ne comporte qu’un seul attribut MalId.
    (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.

  3. #23
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    Tout d'abord merci bien pour l'intérêt que t'as accordé à ce sujet !

    Sinon, pour être honnête je dois avouer, que j'ai commencé à apprendre mes premières notions en base de données la semaine dernière, et tout ce que j'ai appris jusqu'à maintenant est autour de l'élaboration des MCD, je compte donc renforcer mes connaissances la dessus, et après je passerai aux MLD.

    pour l'instant ce que j'arrive à suivre c'est uniquement la première partie de la réponse, pour le reste concernant le MLD, ce sera certainement très util une fois je "découvre" le domaine des MLD.

    revenons donc à MCD, concernant l'utilisation de la date comme attribut identifiant c'est beaucoup mieu, je prends ton conseil. Concernant les identifiants relatifs, n'est il pas necessaire de présenter les pattes réliées aux entités identifiantes par des flèches ?
    je pose cette question car, si par exemple on avait le cas suivant:

    vache --0,n--(etre traité)--1,1--traitement soin--1,1--effectuer--0,n--véterinaire
    date_soin

    comment savoir dans ce cas que c'est uniquement l'identifiant de la vache, avec la date_soin qui participent à l'identification du traitement soin?



    pour ce qui est des contraintes:

    - la première est pour indiquer que la liste des maladies traitées pour une telle vache, doit être bien évidament inclue dans la liste des maladies attrapées par cette même vache.
    - la deuxième indique que la liste des medicaments utilisés dans un traitment soin effectué contre cetaines maladies, doit êtres inclus dans la liste théorique des médicaments qui peuvent être utilisés (qui correspondent) contre ces maladies.
    mon utilisation des contraintes est-elle correcte?

  4. #24
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 002
    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 : 8 002
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par master_och Voir le message
    comment savoir dans ce cas que c'est uniquement l'identifiant de la vache, avec la date_soin qui participent à l'identification du traitement soin?
    Disons que c’est l’AGL qui propose le mickey permettant de repérer s’il y a identification relative ou non. Dans les exemples qui suivent, l’identifiant de TRAITEMENT_SOIN est systématiquement composé des attributs (AnimalId, DateSoin).

    Exemple 1. Représentation merisienne avec Power AMC (cf. mon message d’hier) :




    Exemple 2. Représentation Entité/Relation avec Power AMC :




    Exemple 3. Représentation merisienne selon Win’Design :




    Exemple 4. Représentation Entité/Relation selon IEF :



    Si vous n’avez pas d’AGL, ça sera à vous de préciser, pour le lecteur, votre façon de représenter la chose. Cela dit, je vous engage vivement à utiliser un outil, ne serait-ce que pour vérifier que votre MCD est valide.

    A toutes fins utiles, il existe une version gratuite de MySQL Workbench, qui permet de produire des MLD (et le code SQL à suivre), tel que celui-ci, équivalent à celui que j’ai proposé dans mon message précédent :






    Citation Envoyé par master_och Voir le message
    pour l'instant ce que j'arrive à suivre c'est uniquement la première partie de la réponse, pour le reste concernant le MLD, ce sera certainement très util une fois je "découvre" le domaine des MLD.
    Je vous engage à travailler au plus tôt au niveau MLD, car l’est là qu’on voit le maçon au pied du mur.



    Citation Envoyé par master_och Voir le message
    pour ce qui est des contraintes:
    mon utilisation des contraintes est-elle correcte?
    Du point de vue de la logique, en admettant qu’il s’agisse de contraintes d’inclusion, cela paraît correct, mais il faudrait vérifier cela avec un outil approprié (disons Win’Design). Et puis, Je répète que votre MCD est quand même un peu touffu et que les contraintes peuvent être assurées avec une représentation Entité/Relation, voire directement MLD : on y arrive même avec Workbench.
    (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.

  5. #25
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    je vous remercie pour tous vos conseils, qui me sont vraiment très utiles.

  6. #26
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    Je vous engage à travailler au plus tôt au niveau MLD, car l’est là qu’on voit le maçon au pied du mur.
    quel cours me conseillez vous pour me lancer dans les MLD?

  7. #27
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    Bonsoir

    Je fait revivre ce sujet, car j'ai presque terminé mon MCD, et il me reste deux trucs dont je doute un peu, et qui necessitent donc la confirmation d'un expert.

    le premier est illustré dans l'image suivante:


    la structure de cette partie de mon MCD, est elle admissible?

    NB: pour réaliser une insémination, on peut utiliser soit un taureau, soit une piqure sur laquelle est inscrit l'identifiant du taureau source, qui n'est biensure pas de la ferme, cad on connait ni son père ni sa mère.

    le deuxième problème est le suivant:



    Ce schéma exprime le fait que pour soigner une femelle qui attrape une maladie donnée, à une date donnée on peut effectuer plusieurs traitements soin, et qu'un soin est effectué contre une maladie, qu'une femelle attrape dans une telle date.

    le regroupement de 3 que j'ai fait est-il correcte? sinon quelle représentation me recommandez-vous?

    Merci d'avance.
    Images attachées Images attachées   

  8. #28
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 002
    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 : 8 002
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonsoir master_och,

    Le premier schéma est acceptable.

    Toutefois, qu’est-ce qu’un taureau fictif ? Est-ce un taureau mythique tel que le Minotaure ? Je dis ça parce que, selon votre MCD, un taureau réel est fils d’un taureau, lequel selon votre représentation peut être fictif (une vache pourrait être aussi fille d'un taureau fictif). Par ailleurs, en anticipant sur la saisie des données, il faudra veiller à ce qu’un animal ne soit pas son propre ancêtre...

    Concernant le 2e schéma :

    Vous effectuez une agrégation, ce qui est conceptuellement tout à fait possible. Cela dit, vous utilisez manifestement Win’Design, outil dont je ne dispose pas. Il faut espérer qu’au niveau du MLD, la table Soin comportera une clé étrangère {Num_Officiel, Nom_Maladie, Date} tout en référençant la table Attraper.

    Concernant l’identification des entités-types :

    Evitez d’identifier une entité-type par une propriété naturelle : prendre par exemple Nom_Maladie comme identifiant de Maladie. En effet, l’utilisateur a le pouvoir de remplacer les valeurs et cela n’est pas bon pour la stabilité de la base de données quand cela concerne une clé primaire (qui est dérivée de l’identifiant lors du passage du MCD au MLD) : le remplacement d'un accent dans le nom d'une maladie affectera non seulement la table Maladie, mais aussi les tables Attraper et Soin.
    (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.

  9. #29
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    Bonjour fsmrel

    toujours là pour nous sauver, merci beaucoup !

    Toutefois, qu’est-ce qu’un taureau fictif ?
    En fait il m'est apparu un peu lourd de nommer l'entité "piqure", j'ai donc choisi "fictif" ce qui est apparament encore plus lourd, et non significatif .
    à propos de la piqure j'ai déjà expliqué dans mon poste précédent.

    Par ailleurs, en anticipant sur la saisie des données, il faudra veiller à ce qu’un animal ne soit pas son propre ancêtre...
    Je tiendrai compte au niveau programmation.

    Cela dit, vous utilisez manifestement Win’Design
    il est vrai que j'ai utilisé Win'Design, mais en réalité ce logiciel n'offre pas la possibilité de représenter une agrégation, j'ai utilisé des outils de dessin pour le faire, et c'est pour ça que je me demande si je dois donner un nom à l'agrégation tout en haut dans le cadre?

    Il faut espérer qu’au niveau du MLD, la table Soin comportera une clé étrangère {Num_Officiel, Nom_Maladie, Date} tout en référençant la table Attraper.
    au niveau MLD la table soin aura la structure suivante:
    Soin (id_soin, date_soin, observation, #date, #id_maladie,#Num_officiel).
    est-ce correcte?

    Evitez d’identifier une entité-type par une propriété naturelle
    je connaissais pas ce truc, merci bien pour me l'avoir signalé.

  10. #30
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    Re

    Evitez d’identifier une entité-type par une propriété naturelle
    En se basant sur cette remarque j'ai changer beaucoup d'identifiants dans mon MCD, mais il me reste un cas dans lequel je me demande s'il est vraiment util de changer d'idetifiant, le voilà :



    dans ce cas la structure de la table ctrl_lactation, en MLD sera la suivante:

    ctrl_lactation (Date_ctrl, #id_lact, Qte)

    cela dit, que le changement d'une date de controle de lactation, n'affectera qu'une seule occurence de la table ctrl_lactation, et aucune autre table.
    Est-il donc utile dans ce cas de créer un autre identifiant id_ctrl??

  11. #31
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    oops j'ai oublié l'image, la voilà:
    Images attachées Images attachées  

  12. #32
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 002
    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 : 8 002
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Citation Envoyé par master_och Voir le message
    Citation Envoyé par fsmrel
    Toutefois, qu’est-ce qu’un taureau fictif ?
    En fait il m'est apparu un peu lourd de nommer l'entité "piqure", j'ai donc choisi "fictif" ce qui est apparemment encore plus lourd, et non significatif.
    à propos de la piqure j'ai déjà expliqué dans mon poste précédent.
    J’étais confronté à une alternative : la piqûre concernait soit un taureau fictif soit un taureau non présent : j’ai fait la mauvaise hypothèse. Comme quoi, la rédaction des règles de gestion et leur interprétation n’est pas quelque chose de simple...


    Citation Envoyé par master_och Voir le message
    il est vrai que j'ai utilisé Win'Design, mais en réalité ce logiciel n'offre pas la possibilité de représenter une agrégation, j'ai utilisé des outils de dessin pour le faire, et c'est pour ça que je me demande si je dois donner un nom à l'agrégation tout en haut dans le cadre?
    La FAQ Merise propose la rubrique suivante Comment représenter une agrégation avec win'design ?
    J’ai donc cru que la représentation donnée en exemple était applicable. Indépendamment de cela, d’après les règles de construction des agrégations, la vôtre doit de fait être nommée. Cela dit, quand vous serez au pied du mur et que vous demanderez à l’AGL de produire un MLD à partir du MCD, pour que la table Soin ait la structure (a priori correcte) que vous proposez :
    Soin (id_soin, date_soin, observation, #date, #id_maladie,#Num_officiel)
    Alors il aura fallu mettre en relation Soin et Attraper. Or Attraper est une association-type et en Merise, mettre en relation une association-type avec un autre objet-type (entité-type ou association-type) est interdit. La contrainte a été levée avec OOM (version orientée objet de Merise), mais cela ne nous avance pas dans votre cas. Il vous faudra donc transformer Attraper en entité-type pour pouvoir établir une relation avec l’entité-type Soin. Attraper sera identifiée relativement à Femelle et Maladie (on n’a rien à faire de l’entité-type DATE, autant utiliser un attribut DateMaladie, qui participe à l’identifiant d’Attraper, identifiant qui est donc le suivant : {AnimalId, MalId, DateMaladie}).

    Voici une représentation possible, doux mélange de Win’Design et Power AMC :




    Citation Envoyé par master_och Voir le message
    Citation Envoyé par fsmrel
    Évitez d’identifier une entité-type par une propriété naturelle
    je connaissais pas ce truc, merci bien pour me l'avoir signalé.
    Encore une anecdote que je raconte à l’occasion chez DVP :
    Les concepteurs d’une grande banque française avaient retenu le numéro Siren des entreprises pour identifier celles-ci (attribut NoSiren de l’entité-type Entreprise). Au niveau tabulaire, par le jeu des liens clé primaire - clé étrangère, l’attribut NoSiren se propageait dans une trentaine de tables. Balek ! Le numéro Siren est établi par un organisme extérieur à la banque, à savoir l’INSEE. J’avais fait observer que l’INSEE envoyait tous les mois les nombreux correctifs modifiant le Siren des nouvelles entreprises (10% d’entre elles à peu près) et que, vu le nombre de tables touchées et leur volumétrie (plusieurs millions de lignes chacune), cela pouvait faire exploser la production informatique (batchs de nuit). Une trentaine de tables à mettre à jour quant à leur valeur de clé primaire et/ou étrangère induit une activité de mise-à-jour excessive et en plus, délicate à ordonnancer.

    Panique à bord !

    L’identifiant de l’entreprise fit sur le champ l’objet d’un nouvel attribut, non porteur d’information, artificiel et invariant, Entreprise_Id, destiné à être clé primaire de la table Entreprise et propagé en conséquence dans les autres tables, en lieu et place de l’attribut NoSiren. A partir de là, modifier un numéro de Siren n’impactait plus une trentaine, mais une seule table, à savoir Entreprise. L’utilisateur Tartempion avait bien évidemment toujours accès à l’attribut NoSiren, devenu clé alternative de cette table (et n’ayant donc pas perdu sa propriété d’unicité), l’organisation technique ayant lieu sous le capot, de façon totalement transparente pour Tartempion qui ignorait donc jusqu’à l’existence du nouvel attribut.
    Considérez maintenant l’entité-type Femelle. Vous me direz que l’attribut Num_Officiel ne change jamais. Mais est-il totalement non significatif ? Est-ce le SGBD qui en fournit les valeurs ? Si c’est l’utilisateur qui le maîtrise, attendez-vous à des surprises. Ainsi, pas mal de programmes ont dû être modifiés quand le département français 20 (Corse) a été remplacé par 2A et 2B. Utiliseriez-vous le numéro d’immatriculation des voitures pour identifier celles-ci ? Etc.

    => Quelles que soient les propriétés naturelles candidates à l’identification d'une entité-type, utilisez systématiquement un attribut artificiel (par exemple un entier, qui sera automatiquement incrémenté par le SGBD si vous le lui demandez). Dans ces conditions, l’attribut Num_Officiel devient identifiant alternatif, manipulable par l’utilisateur selon son bon plaisir, avec pour seule contrainte (garantie par le système) que les valeurs prises par cet attribut soient uniques.



    Citation Envoyé par master_och Voir le message
    la structure de la table ctrl_lactation, en MLD sera la suivante:
    ctrl_lactation (Date_ctrl, #id_lact, Qte)
    cela dit, que le changement d'une date de controle de lactation, n'affectera qu'une seule occurence de la table ctrl_lactation, et aucune autre table.
    Est-il donc utile dans ce cas de créer un autre identifiant id_ctrl??
    1) Attention, l’ordre des attributs dans la clé primaire de la table ctrl_lactation ne sera pas celui-ci :
    (Date_ctrl, id_lact)
    mais celui-là :
    (id_lact, Date_ctrl)
    C'est-à-dire que les attributs de l’entité-type forte (lactation) passent avant ceux de l’entité-type faible (ctrl_lactation).

    2) Si vous suivez ce que j’ai écrit plus haut, l’identifiant de ctrl_lactation devrait être invariant sans signification, disons un entier plutôt qu’une date. Évidemment, cette entité-type n’est référencée par aucune autre et l’utilisateur peut donc modifier la date sans problème. Mais, on sait que les MCD évoluent dans le temps et vous serez peut-être dans le futur amené, par exemple, à spécialiser cette entité-type, auquel cas il y aura propagation de la clé primaire de la table correspondante, vers les tables spécialisées. Bon, on est aux limites et je dirais que je vous laisse face à vos responsabilités. C’est vous qui voyez comme disait Laspalès à Chevallier...

    Si au final vous retenez comme clé primaire {id_lact, id_ctrl}, n’oubliez pas de déclarer la clé alternative {id_lact, Date_ctrl}.
    (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. #33
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    La FAQ Merise propose la rubrique suivante Comment représenter une agrégation avec win'design ?
    la méthode de représentation proposée me semble très rusée, et ça aboutit à un résultat assez satisfaisant.
    à propos de la représentation je trouve la solution d'agrégation disons un peu plus élégante, mais j'ai juste fait un petit changement au niveau de l'entité date en ajoutant un id artificiel au cas ou l'utilisateur change une certaine date de maladie.



    ...utilisez systématiquement un attribut artificiel
    Tout à fait convaincu par votre explication, je prends donc cette proposition.
    Sauf qu'une petite question s'impose, si dans l'entité ctrl_lactation j'utilise un identifiant artificiel, je pourrais me passer de l'identification relative qui n'aura aucune utilité non?

    l’attribut Num_Officiel devient identifiant alternatif
    en fait j'ai bien compris la notion de l'identifiant alternatif, et j'aimerai bien savoir si cet identifiant à une représentation particulière (je vous rappel j'utilise Win'Design comme vous l'avez déjà remarqué), que ce soit au niveau MCD ou MLD

    C’est vous qui voyez comme disait Laspalès à Chevallier...
    Après l'explication tout à fait convaincante, que vous venez de rédiger, je serai forcément mené à suivre vos consignes, qui n'ont fait jusqu'à maintenant qu'ameliorer ma facon de voir

  14. #34
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 002
    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 : 8 002
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonjour,


    Citation Envoyé par master_och Voir le message
    à propos de la représentation je trouve la solution d'agrégation disons un peu plus élégante
    La technique d’agrégation a été présentée par John et Diane Smith en 1977 dans leur article Database abstractions: Aggregation and Generalization.
    A leur suite, vous devez donner un nom à l’agrégat, par exemple VACHERIE (pour ma part, je n’ai rien de mieux...)
    Par ailleurs, la patte entre ATTRAPER et TRAITER_S doit être matérialisée.




    Mais comme les AGL ne permettent manifestement pas de prendre tout cela formellement en considération, vous pourrez toujours vous rabattre sur la représentation que l’ai proposée dans mon précédent message. En passant, laissez tomber l’entité-type DATE : quand vous aurez galéré avec les tables en production, vous comprendrez alors pourquoi. Vous pouvez donc remplacer la représentation précédente par la suivante, pour n’avoir à propager que des identifiants non significatifs (mise en œuvre de l’attribut DateId) :



    Au niveau du MLD, la clé primaire d’ATTRAPER sera le trio {AnimalId, MalId, DateId}, mais la clé {AnimalId, MalId, DateMaladie} devenue alternative devra être ajoutée manuellement.



    Citation Envoyé par master_och Voir le message
    une petite question s'impose, si dans l'entité Ctrl_lactation j'utilise un identifiant artificiel, je pourrais me passer de l'identification relative qui n'aura aucune utilité non ?
    Sémantiquement parlant, il y aurait un glissement : l’entité-type Ctrl_lactation, n’étant plus identifiée relativement à l’entité-type Lactation, perdrait son statut d’entité-type faible, pour acquérir celui de forte (autonome) et pourrait tout au plus être qualifiée comme « désignant » Lactation. Au niveau MLD, peu importe en ce qui concerne l'aspect purement structurel, mais le métabolisme de la base de données (qui n’est pas qu’anatomique) peut être affecté (voyez les mots-clés RESTRICT et CASCADE du langage SQL). Si l’on se situe au niveau du MPD (modèle physique de données), alors là, changement de chanson. Il est peut-être prématuré que je vous entretienne de la chose, mais sachez que j’ai consacré pas mal de temps à auditer les bases de données de nombre de Productions informatiques de grandes entreprises, pour des problèmes de performance et mon diagnostic portait bien souvent sur ce sujet précis. Les jambes ne suivaient pas parce que du côté tête il y avait eu laxisme (ou ignorance surtout) : pour les entités-types faibles, il aurait fallu utiliser propager l’identification relative jusqu’au niveau physique pour obtenir les performances attendues. Exemple parmi tant d'autres : un traitement quotidien (lourd) prévu pour durer 8 heures et dans faits durant 240 heures, ce qui pour un traitement quotidien n’est pas très satisfaisant...

    Citation Envoyé par master_och Voir le message
    en fait j'ai bien compris la notion de l'identifiant alternatif, et j'aimerai bien savoir si cet identifiant à une représentation particulière (je vous rappelle que j'utilise Win'Design comme vous avez déjà pu le remarquer), que ce soit au niveau MCD ou MLD
    Au niveau MCD, par respect de l’approche Entité/Relation, les AGL sont construits de telle sorte qu’il n’est pas toujours possible de signifier qu’un identifiant est alternatif. C’est le cas par exemple de l’entité-type faible ATTRAPER dans ma représentation graphique précédente. Comme je l’ai évoqué, si {AnimallId, MalId, DateId} est identifiant « principal » et {AnimallId, MalId, DateMaladie} identifiant alternatif, vous serez obligé d’intervenir au niveau MLD pour définir de dernier.

    Une fois reprisé, le MLD devrait être le suivant (j’ai utilisé Power AMC) :


    Vous observerez que la table ATTRAPER a bien pour clé primaire {AnimalId, MalId, DateId} (mickey<pk>). Elle a pour clé alternative, créée manuellement, {AnimalId, MalId, DateMaladie} (mickey<ak>. Si on avait plus d’une clé alternative, on aurait les mickeys <ak1>, <ak2>, etc.) Cette table comporte deux « clés » étrangères <fk1> et <fk2> (le terme clé étrangère n’est pas très heureux, car clé sous-entend unicité or, si la vache v1 figure évidemment une seule fois dans la table FEMELLE, elle figure autant de fois dans la table ATTRAPER qu’elle aura attrapé des saloperies. Retenons qu’une clé étrangère n’est qu’un ensemble d’attributs utilisé pour faire référence à une clé primaire (ou alternative) d’une autre table. Ainsi, dans la table SOIN, le trio {AnimallId, MalId, DateId} constitue une clé étrangère servant à référencer la clé primaire de la table ATTRAPER.

    Bon courage à vous.
    (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.

  15. #35
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    Bonjour fsmrel

    encore une fois merci pour votre intervention.

    Enfin j'ai parvenu à comprendre les MLD grace à vous et voici enfin de compte, une partie du MLD représenté sous Win'Design.



    NB:
    - les attribut en vert avec une clé "sombre" sont utilisés dans des clés alternatives
    - la lettre (S) veut dire stable
    Images attachées Images attachées  

  16. #36
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 002
    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 : 8 002
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonjour,

    J'ai des petites remarques à faire, mais plus en relation avec le niveau physique (on commence à descendre dans la soute...)
    Don't worry.
    Je reprendrai cela plus tard, car j'ai des problèmes de connexion internet à résoudre (et là, je suis bien incompétent...)

    Bon week-end.
    (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.

  17. #37
    Membre du Club
    Inscrit en
    Février 2007
    Messages
    138
    Détails du profil
    Informations forums :
    Inscription : Février 2007
    Messages : 138
    Points : 64
    Points
    64
    Par défaut
    J'attends vos remarques.

    Bon weekend à vous aussi

  18. #38
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 002
    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 : 8 002
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Ce dont je souhaite vous entretenir a trait aux conséquences sur les performances des applications, résultant de certains choix d’organisation au niveau du MCD. En effet, les AGL répercutent ces choix au niveau du MLD, puis du MPD, ce dernier niveau se situant sous le capot, là où en réalité tout se joue en ce qui concerne la performance des requêtes. Je m’intéresse plus particulièrement ici à l’identification des entités-types, à savoir absolue ou relative, ainsi qu’à la façon d’ordonner les attributs dans les clés : ce dernier point peut paraître anodin, sans intérêt, mais cette façon d’ordonner peut en fait être très lourde de conséquences.

    Évidemment, si le MCD comporte un nombre réduit d’entités-types et d’associations-types, on peut toujours modifier manuellement le MLD produit par l’AGL (le bon sens voulant que l’on agisse ainsi seulement si l’on ne peut pas faire autrement), mais quand le MCD comporte des centaines de ces objets-types, cela devient mission impossible.

    Identification absolue vs identification relative

    Partons de votre MLD et supposons que l’utilisateur de la base de données souhaite disposer de la liste complète des femelles, en se limitant aux informations suivantes :
    NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT.
    Selon le Modèle Relationnel de Données (nom donné par son inventeur, Ted Codd, à la théorie relationnelle), le résultat est obtenu au moyen de la requête suivante :
    Code D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    (FEMELLE JOIN SOIN) 
            {NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT} ;
    Ce qui se lit : Sur la base de leurs attributs portant le même nom (en l’occurrence ID_FEMELLE) et dont les valeurs égales, marier (JOIN) les tables FEMELLE et SOIN. Puis, du bébé issu de ce mariage, récupérer les valeurs, uniquement pour les attributs énumérés entre les accolades (opération de projection), à savoir NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT.

    Ou encore, dans le style du modèle SQL (avec des SGBD tels que SQL Server, DB2 for z/OS, etc.) :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
     
    SELECT  NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT
    FROM    FEMELLE INNER JOIN SOIN
                ON FEMELLE.ID_FEMELLE = SOIN.ID_FEMELLE ;
    Vous aurez noté à cette occasion que seuls sont énumérés les attributs relatifs aux données qui intéressent l’utilisateur : si l’attribut ID_FEMELLE a joué un rôle clé dans cette opération, il n’en demeure pas moins qu’il est parfaitement inutile de le faire figurer dans le résultat, puisque les valeurs qu’il prend n’ont de signification que pour le SGBD. Pour imager, on pourrait dire qu’un attribut artificiel tel que ID FEMELLE, et ne concernant donc pas l’utilisateur, joue dans la production de ce résultat un rôle analogue à celui que joue le nombre imaginaire i dans la résolution d’une équation polynomiale.

    Incidemment, observons que le bébé issu du mariage est de même nature que ses parents FEMELLE et SOIN, c'est-à-dire qu’il s’agit d’une table T (dans le cadre de la théorie relationnelle, il faudrait utiliser le terme relation, mais ne compliquons pas les choses). Cette propriété porte le nom de fermeture (closure), en vertu de quoi, le bébé T peut à son tour jouer le rôle d’opérande pour une autre opération relationnelle. Il est le fruit d’une opération de jointure (que j’ai appelée mariage), et subit ensuite une opération de projection, grâce à laquelle on limite le résultat aux seuls attributs mentionnés plus haut (NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT). Toujours au nom de la propriété de fermeture, le résultat final est évidemment une table.

    Ce que je viens de décrire relève de la théorie relationnelle. On se situe au niveau de l’algèbre (ou du calcul des prédicats) : à ce stade, le coût de l’opération (disons sa durée) n’entre pas en ligne de compte, l’utilisateur ayant déclenché la requête étant théoriquement infiniment patient et n’exigeant seulement que résultat qu’on lui présente soit valide.

    Une première remarque

    Je n’ai pas fait participer la table ATTRAP à l’opération, puisque cela n’apporterait rien de plus. La requête suivante est certes correcte, et c’est elle qui serait vraisemblablement codée par les progiciels et nombre de développeurs. En effet, intuitivement, ATTRAP est un point de passage naturel, quasi obligé, pour « aller » de FEMELLE à SOIN :
    Code D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    ((FEMELLE JOIN ATTRAP) JOIN SOIN) 
            {NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT} ;
    Ou dans le style du modèle SQL :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SELECT  NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT
    FROM    FEMELLE INNER JOIN ATTRAP
                      ON FEMELLE.ID_FEMELLE = ATTRAP.ID_FEMELLE
                    INNER JOIN SOIN
                      ON ATTRAP.ID_FEMELLE = SOIN.ID_FEMELLE ;
    Quoi qu’il en soit, cette requête mérite tout simplement de passer au rasoir d’Ockham. Ainsi, l’optimiseur de DB2 for z/OS ne s’en prive pas : il la simplifie en la réécrivant de telle sorte qu’elle devienne celle que j’ai fournie initialement. Pour les autres SGBD, je ne sais pas ce qu’il en est, sinon qu’avec SQL Server (version Express 2005), la simplification n’a pas lieu, en effet ce SGBD fait participer inutilement un index de la table ATTRAP (voir ci-dessous les quelques mots qui expliquent très succinctement ce qu’est un index et à quoi il sert).

    Une deuxième remarque

    Certains ignorent (ou veulent ignorer) ce qu’est un identifiant relatif. Supposons qu’au niveau du MCD, ATTRAP soit une entité-type, dotée d’un identifiant absolu ID_ATTRAP. Dans ces conditions, la clé étrangère qui dans SOIN permet de référencer ATTRAP n’est plus {ID_FEMELLE, ID_MALADIE, ID_DATE}, mais {ID_ATTRAP} : la table ATTRAP doit désormais participer à titre de moyen terme entre les tables FEMELLE et SOIN, parce que ID_FEMELLE n’est plus un attribut de la table SOIN, et la jointure de ma première requête n’est donc plus valide (en réalité elle dégénère en produit cartésien). C’est dommage.

    Je rappelle que l’identification relative s’impose dans le cas des propriétés multivaluées. Par exemple, une facture est composée de une à plusieurs lignes de facture, autrement dit une ligne de facture est une propriété multivaluée d’une facture, et si l’on supprime une facture, ses lignes le sont aussi, de facto. Si l’on définit une entité-type LIGNE_FACTURE, celle-ci n’est qu’une entité-type faible et elle hérite de l’identifiant de l’entité-type FACTURE, auquel on ajoute un attribut artificiel, par exemple un numéroteur relatif, permettant de distinguer chaque ligne de la même facture.

    Il en va ainsi pour l’entité-type ATTRAP, faible par rapport à FEMELLE : Si l’on supprime la femelle Rosa, il est évident que tout ce qui concerne celle-ci doit disparaître de ATTRAP. Il est intéressant d’observer qu’à l’opposé, si l’on supprime une maladie, cela est en fait impossible tant qu’une femelle est concernée (de même que l’on ne peut pas supprimer un produit auquel fait référence une facture). Autrement dit, pour se situer au plan sémantique, ATTRAP n’est pas a priori une propriété multivaluée de MALADIE (sauf à me démentir) : je dirais que ATTRAP fait référence à (désigne) MALADIE, c'est-à-dire que si Rosa a attrapé une bronchopneumonie, elle n’est pas pour autant la propriété de cette maladie. Maintenant, et comme c’est le cas dans votre MLD, rien n’empêche évidemment que l’attribut ID_MALADIE soit un des éléments de la clé de la table ATTRAP. Ce n’est qu’une réminiscence du fait d’avoir considéré ATTRAP comme entité-type associative.

    Une question en passant : LACTATION et CTRL_LACTATION sont-elles des entités-types fortes ou faibles ? Si elles sont fortes, on ne touche pas au mode d’identification, sinon...

    Des performances

    Revenons-en à la première requête :
    Code D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    (FEMELLE JOIN SOIN) 
            {NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT} ;

    Une fois celle-ci déclenchée, les yeux rivés sur son écran, l’utilisateur aimerait quand même bien que le résultat lui soit fourni dans un délai raisonnable, qu’il ne faille pas une heure pour afficher les informations impliquant mille vaches (sur un plateau, si je puis dire). En ce sens, équipés d’un fer à souder, nous devrons donc descendre dans la soute, pour vérifier que les turbos ad-hoc sont opérationnels et serrer au besoin quelques boulons. Nous nous situerons alors au niveau du modèle physique de données (MPD), dont la responsabilité incombe cette fois-ci au DBA (Database Administrator) et pour lequel le concepteur n’est pas (théoriquement) compétent.

    En première approche, comment les choses se passent-elles ? Dans les tréfonds, l’image physique d’une table T est hébergée par un fichier sur le disque (ou plusieurs fichiers sur plusieurs disques au besoin). Le fichier est lui-même composé de blocs que l’on appelle encore pages. Une page P comporte un plusieurs enregistrements, chacun d’eux étant l’image physique d’une ligne de T. Une ligne cohabite donc dans P avec 0 à plusieurs autres lignes de T, selon la place disponible.
    Après examen de la requête, le SGBD lit la première page P1 du fichier affecté à la table FEMELLE. Supposons que le 1er enregistrement de P1 corresponde à la vachette Zaza. Le SGBD récupère ce dont il a besoin : le numéro officiel et la date de naissance de Zaza. L’opération est rapide. Supposons maintenant que Zaza ait fait l’objet de dix soins. La rapidité de récupération des données correspondant aux dix soins de la Zaza dépendra de la proximité physique de ces données dans le fichier hébergeant la table SOIN : soit les enregistrements utilisés pour Zaza sont physiquement regroupés dans une même page, soit au contraire, ils sont éparpillés dans des pages distinctes (« façon puzzle », comme dirait Raoul Volfoni).

    Si tous les soins concernant Zaza sont regroupés dans une même page, le SGBD peut les récupérer en un seul accès au disque. S’ils sont éparpillés dans dix pages distinctes et éloignées les unes des autres, le SGBD devra effectuer dix accès pour récupérer les informations. Le coût est alors dix fois supérieur à celui qui correspond au scénario précédent.

    Si votre cas correspond au second scénario, cela risque de ne pas être dramatique, mais si l’on en vient à la récupération des mouvements en relation avec les comptes des dix millions de clients d’une banque, cela pourrait vraiment devenir un problème insurmontable. En effet, le coût d’un accès à une page sur disque se mesure en millisecondes et les temps de traitement pour retrouver tous les mouvements de tous les clients deviennent rédhibitoires (et c’est pour cela que l’on m’a encore sollicité il y a quelques mois pour résoudre en catastrophe un problème de cette nature). D’aucuns vous diront qu’en mettant en place des caches énormes, donc en remplaçant les accès au disque par des accès à la mémoire, ce genre de problème ne devrait plus se poser : mais les faits sont là, et vous comprendrez que, dans ce genre d’expérience, on me convaincra difficilement que les caches sont la panacée. Et puis, dans un établissement tel qu’une banque, deux ou trois mille utilisateurs opèrent en même temps et personne n’a l’exclusivité des ressources. Même, en y mettant le prix, on a très peu de chances que les choses s’arrangent, et il est peut-être bon d’appliquer quelques principes éprouvés, afin d'éviter d’en arriver à des solutions extrêmes, pour des résultats fort aléatoires.

    Des index

    Toujours dans la soute, venons-en aux turbos. Depuis qu’ils existent, quels que soient les SGBD, la bonne performance des requêtes est le plus généralement la conséquence du bon choix de ce que l’on appelle les index : ce sont des objets (disons des fichiers) que l’on ne connaît pas au niveau logique, mais qui relèvent du niveau physique et que l’on « branche » sur [les fichiers hébergeant] les tables. A l’image de l’index d’un livre, la finalité d’un index d’une table est de nous permettre de retrouver les données (par exemple celles d’une femelle dans la table FEMELLE), non pas en parcourant le contenu intégral de la table (comme on lirait laborieusement le livre mot par mot, de la première à la dernière page, pour y chercher tel mot), mais en accédant directement et rapidement à la bonne page. Comme pour une table, l’accès à un index est réalisé au moyen d’une clé, image d’une clé de la table (ou composée de tous autres attributs pour lesquels les recherches doivent être rapides).

    J’ignore quel est votre niveau de connaissance en matière d’index, mais peu importe, je rappelle quelques principes généraux.

    Les éléments pour travailler efficacement sont notamment les suivants, en remontant de la soute vers le pont :
    Au niveau physique, la façon d’organiser les index.

    Au niveau logique : l’ordre des attributs dans les clés, car cet ordre conditionnera celui des clés correspondantes dans les index.

    Au niveau conceptuel : la façon d’identifier, soit de façon absolue (exemple : l’entité-type FEMELLE est identifiée de façon absolue, à l’aide de l’attribut ID_FEMELLE), soit de façon relative (dans la mesure où, en conséquence, on permet au niveau logique, la propagation des attributs de table en table : attribut ID_FEMELLE, propagé depuis la table FEMELLE jusqu’à la table SOIN, via la table ATTRAP). Je rappelle : quelles seraient les conséquences quant à la performance de la requête initiale si l’on avait identifié ATTRAP de façon absolue ?
    Et bien sûr, tout cela est conditionné par la mise en évidence des traitements transactionnels (légers) ou traitements de type batch (lourds) qui reviennent le plus souvent.

    Quelques mots ayant trait à l’organisation d’un index. Je rappelle brièvement qu’un index est en général organisé selon une structure d’arbre, avec :
    — Tout en haut (niveau 1) la racine (une seule page), point d’entrée essentiel pour accéder à l’information recherchée, en fonction d’une valeur de clé. Exemple, si l’on définit un index, appelons-le FEMELLE_PK_X, dont la clé est construite sur l’attribut ID_FEMELLE, alors on pourra accéder directement aux données de la vachette Rosa pour laquelle tout le monde sait qu’elle répond au critère ID_FEMELLE=31415, Partant de la racine, le système entamera une descente dans les niveaux inférieurs, sur la base de cette valeur.

    — Tout en bas (niveau N, avec N > 1) les feuilles de l’arbre, lesquelles contiennent les adresses des pages du fichier hébergeant la table. A noter à cette occasion que l’arbre est équilibré, c'est-à-dire que, contrairement à ce que nous observons dans la nature, les feuilles sont toutes à la même distance de la racine.

    — Au milieu (niveaux intermédiaires), les nœuds permettant au système d’atteindre les feuilles à partir de la racine (jeux de pointeurs). Le nombre de niveaux intermédiaires est variable. Par exemple, avec DB2 for z/OS, pour les dix millions de clients de la banque, ce nombre sera égal à deux. Si votre table MALADIE décrivait dix mille maladies, aucun nœud intermédiaire ne serait nécessaire, l’index se résumerait à la racine plus une trentaine de feuilles directement en relation avec la racine.
    Toujours avec DB2 for z/OS, si votre table SOIN comportait dix millions de lignes, vous devriez compter normalement sur deux index : un pour la clé primaire (appelons-le SOIN_XPK) et un pour la clé alternative (appelons-le SOIN_XAK), organisés ainsi :

    Pour l’index « primaire » SOIN_XPK (clé : ID_SOIN), la racine comporterait une page ; au deuxième niveau, un nœud comporterait une centaine de pages et au troisième et dernier niveau, le nombre de pages (feuilles) serait de l’ordre de 3000. Ainsi, pour retrouver les données d’un soin via la clé primaire, on aurait droit au plus à trois accès physiques (un pour le deuxième niveau, un pour le niveau feuille et un pour la page contenant les données (il est d’usage de ne pas tenir compte du premier niveau, présent le plus souvent en mémoire). Via l’index primaire, à dix millisecondes l’accès, le SGBD récupèrera les données d’un soin en une trentaine de millisecondes.

    Pour l’index « alternatif » SOIN_XAK (clé : ID_FEMELLE, ID_MALADIE, ID_DATE, ID_SOIN), la racine comporterait une page ; au deuxième niveau, on aurait trois pages, au troisième niveau environ 400 pages et enfin au dernier niveau, le nombre de feuilles serait de l’ordre de 64000. Ainsi, pour retrouver les données d’un soin via la clé alternative, on aurait droit à quatre accès physiques. Le SGBD récupèrerait les données d’un soin en une quarantaine de millisecondes.

    Supposons maintenant que l’on ne gère pas dix millions de femelles, mais plutôt quelques milliers. Complétons la requête initiale, pour retrouver les informations concernant plus précisément la vachette Rosa, sur la base de son identité officielle (attribut NUM_OFFICIEL prenant la valeur 1234) :
    Code D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    ((FEMELLE WHERE NUM_OFFICIEL = 1234) JOIN SOIN)
             {NUM_OFFICIEL, DATE_NAISS, DATE_SOIN, DATE_RAPPEL_TRAIT} ;
    Je suppose qu’une requête de cette nature sera fort utilisée, dans la mesure où l’utilisateur propose le numéro officiel pour avoir accès aux données de l’animal. Il est souhaitable qu’en l’occurrence le SGBD n’ait pas à balayer mot à mot la table FEMELLE pour résoudre la partie "WHERE NUM_OFFICIEL = 1234" de la requête : un index ad-hoc s’impose (nommons-le FEMELLE_OFF_X), avec NUM_OFFICIEL comme clé d’accès. A peu de choses près, les choses se passeront alors ainsi dans la soute pour traiter la requête :

    1) Le SGBD utilise l’index FEMELLE_OFF_X et consomme de l’ordre de vingt millisecondes pour retrouver toutes les données de l’animal, en particulier les valeurs des attributs ID_FEMELLE et DATE_NAISS. Supposons qu’il s’agisse donc de la vachette Rosa, pour laquelle ID_FEMELLE=31415.

    2) Ayant pris connaissance de l’information ID_FEMELLE=31415, le SGBD traverse verticalement l’index SOIN_XAK et en une dizaine de millisecondes récupère au niveau feuille les pointeurs vers les données concernant Rosa dans la table SOIN. A méditer : quelles auraient les conséquences d’une permutation des attributs ID_FEMELLE et ID_MALADIE dans la clé alternative de la table SOIN ?

    3) Supposons que Rosa ait fait l’objet de dix soins. Si les enregistrements correspondants sont regroupés dans une même page (situation idéale), en dix millisecondes les informations complémentaires sont récupérées (DATE_SOIN, DATE_RAPPEL_TRAIT), et le SGBD peut présenter à l’utilisateur le résultat voulu, pour un coût global de l’ordre d’une quarantaine de millisecondes. Si les enregistrements sont éparpillés dans le fichier hébergeant la table SOIN, le coût peut être de l’ordre de cent trente millisecondes. Dans le contexte des traitements bancaires, il est impératif de se ramener au cas précédent, dès lors que le nombre de transactions est élevé, et la production informatique ne manquera pas de nous brandir sous le nez ses statistiques et d’exiger que nous fassions le nécessaire.

    Quelques observations.

    1) J’ai considéré qu’un accès à un enregistrement du disque était de l’ordre de la dizaine de millisecondes : ceci est évidemment à titre indicatif. Selon que les accès sont sélectifs (à l’unité) ou se font en rafales, lire 64 pages peut dans un cas coûter 640 millisecondes et quatre fois moins dans l’autre cas. Selon le prix que l’on met, les disques sont plus ou moins performants. De même, la technologie rend les accès toujours plus rapides au fil des ans. Mais une chose est sûre, il n’y a aucune mesure en termes de coût entre un accès à l’information sur le disque et à cette même information quand elle est mémorisée dans les caches. Je le répète, il est prudent de partir du principe que l’information que l’on cherche n’est pas encore mémorisée.

    2) Beaucoup plus important : l’ordre des attributs de la clé alternative de la table SOIN induit l’ordre des attributs composant la clé de l’index associé SOIN_XAK :
    ID_FEMELLE, ID_MALADIE, ID_DATE, DATE_SOIN.
    Cet ordre est primordial. En effet, s’il était par exemple le suivant :
    ID_MALADIE, ID_FEMELLE, ID_DATE, DATE_SOIN,
    alors le SGBD ne pourrait plus traverser verticalement l’index et balaierait horizontalement le niveau feuilles, d’où une dégradation radicale des performances si l’on ne branchait pas un index supplémentaire pour pallier. Quand on a une poignée de tables mal indexées, on peut s’en sortir, mais si l’on en a des centaines...

    3) Comment faire pour éviter l’éparpillement des 10 soins sur le disque ? Un SGBD comme DB2 for z/OS (même chose pour SQL Server) permet, par paramétrage de l’index, d’imposer que le regroupement sur le disque des enregistrements correspondant aux lignes de la table, se fasse selon la séquence de la clé de l’index (lequel est alors qualifié de CLUSTER). Bien entendu, pour la table SOIN, seul l’index alternatif est pertinent pour être CLUSTER, puisque les soins de Rosa doivent se suivre selon la séquence ID_FEMELLE. Si vous désigniez l’index primaire comme CLUSTER, vous auriez tout faux.

    Au fait, quel est intérêt de l’attribut ID_SOIN ? Que perdriez-vous à supprimer cet attribut et à rendre primaire la clé alternative de la table SOIN ? Au niveau MCD, un soin n’est-il pas d’un point de vue sémantique une propriété multivaluée de l’entité-type ATTRAP, et par contrecoup de l’entité-type FEMELLE ?

    4) Supposons encore que l’on veuille savoir quelles femelles ont attrapé telle maladie.

    La requête serait la suivante :
    Code D : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    (((MALADIE WHERE NOM_MALADIE = 'bronchopneumonie') 
                      JOIN ATTRAP) JOIN FEMELLE) {NUM_OFFICIEL} ;
    Qui se lit : Sélectionner dans la table MALADIE la ligne pour laquelle NOM_MALADIE = 'bronchopneumonie'. Joindre le résultat avec la table ATTRAP (sur la base de l’attribut commun ID_MALADIE). Joindre le nouveau résultat avec la table FEMELLE (sur la base de l’attribut commun ID_FEMELLE). Fournir à l’utilisateur la liste des NUM_OFFICIEL.

    Vous aurez compris qu’il n’est pas superflu de disposer pour la table ATTRAP d’un index supplémentaire, ordonné sur l’attribut ID_MALADIE pour que la jointure des tables MALADIE et ATTRAP soit performante. Quant à la jointure avec la table FEMELLE, la table ATTRAP étant dotée d’office d’un index pour la clé primaire de celle-ci (même si cela relève d’un diktat arbitraire de la part des SGBD, cet index est imposé), pas de problème de traversée verticale. Maintenant, le coût de la requête n’est pas optimal, dans la mesure où les femelles sont ordonnées dans le fichier qui les héberge selon une séquence cluster qui n’est pas celle des maladies. Pour vous ça sera relativement transparent. Mais transposé dans le monde bancaire...

    Pour conclure

    Quand on construit un MCD, il est évident que l’on n’est pas concerné par la performance des requêtes qui attaqueront la base de données. Néanmoins, il est bon d’avoir un minimum de connaissances à ce sujet, pour prendre conscience que certains choix sémantiques (propriétés multivaluées, identification relative, etc.) sont lourds de conséquence quant à l’organisation des données au niveau physique, dès lors que l’on passe par les services d’un AGL, lequel traduit mécaniquement le MCD en MLD puis celui-ci en MPD. Dans le même sens, on doit être attentif quant à l’ordre des attributs dans les clés, et ne pas oublier que la performance dépend largement (au moins pour des traitements lourds) du choix de l’index cluster d’une table. En tout état de cause, il faut avoir connaissance des requêtes qui seront les plus utilisées et qui pourront orienter nos choix, sachant que favoriser celles-ci pénalisera forcément les autres. Vieux problème, but that's another story...
    (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.

  19. #39
    Modérateur

    Avatar de CinePhil
    Homme Profil pro
    Ingénieur d'études en informatique
    Inscrit en
    Août 2006
    Messages
    16 799
    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 799
    Points : 34 031
    Points
    34 031
    Billets dans le blog
    14
    Par défaut
    Pavé très intéressant qui mériterait d'être transposé en article à publier sur ce site...

    Cependant, je ne suis pas sûr d'avoir très bien compris comment est physiquement organisé un index.

    En première approche, comment les choses se passent-elles ? Dans les tréfonds, l’image physique d’une table T est hébergée par un fichier sur le disque (ou plusieurs fichiers sur plusieurs disques au besoin). Le fichier est lui-même composé de blocs que l’on appelle encore pages. Une page P comporte un plusieurs enregistrements, chacun d’eux étant l’image physique d’une ligne de T. Une ligne cohabite donc dans P avec 0 à plusieurs autres lignes de T, selon la place disponible.
    De ceci, je comprends que, par exemple, le bloc (ou la page) d'adresse 18524 sur le disque héberge les données des vaches 'Zaza', 'Blanchette', 'Marie', 'Pervenche'.

    Quelques mots ayant trait à l’organisation d’un index. Je rappelle brièvement qu’un index est en général organisé selon une structure d’arbre, avec :
    — Tout en haut (niveau 1) la racine (une seule page), point d’entrée essentiel pour accéder à l’information recherchée, en fonction d’une valeur de clé. Exemple, si l’on définit un index, appelons-le FEMELLE_PK_X, dont la clé est construite sur l’attribut ID_FEMELLE, alors on pourra accéder directement aux données de la vachette Rosa pour laquelle tout le monde sait qu’elle répond au critère ID_FEMELLE=31415, Partant de la racine, le système entamera une descente dans les niveaux inférieurs, sur la base de cette valeur.

    — Tout en bas (niveau N, avec N > 1) les feuilles de l’arbre, lesquelles contiennent les adresses des pages du fichier hébergeant la table. A noter à cette occasion que l’arbre est équilibré, c'est-à-dire que, contrairement à ce que nous observons dans la nature, les feuilles sont toutes à la même distance de la racine.

    — Au milieu (niveaux intermédiaires), les nœuds permettant au système d’atteindre les feuilles à partir de la racine (jeux de pointeurs). Le nombre de niveaux intermédiaires est variable. Par exemple, avec DB2 for z/OS, pour les dix millions de clients de la banque, ce nombre sera égal à deux. Si votre table MALADIE décrivait dix mille maladies, aucun nœud intermédiaire ne serait nécessaire, l’index se résumerait à la racine plus une trentaine de feuilles directement en relation avec la racine.
    Là j'ai plus de mal...

    Commençons en imaginant une table qui ne comporte que l'index créé par la clé primaire. Cela veut-il dire que cet index classe les identifiants dans l'ordre et met en regard l'adresse du bloc (ou de la page) sur le disque ?

    Ce qui signifierait alors, pour reprendre nos 4 vaches précédentes et en leur attribuant respectivement les identifiants :
    1 'Zaza'
    2 'Blanchette'
    3 'Marie'
    4 'Pervenche'
    Que l'index aurait alors la forme suivante :
    Racine / Feuille
    1 / 18524
    2 / 18524
    3 / 18524
    4 / 18524

    J'ai juste ou je n'ai rien compris ?

    Ce que j'ai du mal à comprendre, ce sont les niveaux intermédiaires. Surtout quand vous citez les 10 millions de client de la banque ou les 30 feuilles pour 10 000 maladies !

    Allons plus loin en ajoutant un index sur le nom de la vache. Celui-ci sera t-il un nouvel index indépendant de l'autre et qui aura la forme suivante ?
    Racine / Feuille
    'Blanchette' / 18524
    'Marie' / 18524
    'Pervenche' / 18524
    'Zaza' / 18524

    Ou bien tiendra t-il compte de l'index de la clé primaire ?
    Nom / Id
    'Blanchette' / 2
    'Marie' / 3
    'Pervenche' / 4
    'Zaza' / 1

    Bref, un peu plus de détails sur cette organisation physique des index m'intéresse vivement, surtout que, comme par hasard, je travaille sur une base de données de 67 millions de bovins et qui sera l'objet de calculs statistiques pour lesquels l'optimisation sera importante pour obtenir des temps de réponse corrects.

    Merci François au temps que tu consacres à nous délivrer des parts de ta connaissance pointue dans le domaine des SGBD.
    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 !

  20. #40
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    8 002
    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 : 8 002
    Points : 30 905
    Points
    30 905
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Citation Envoyé par CinePhil
    Citation Envoyé par fsmrel
    En première approche, comment les choses se passent-elles ? Dans les tréfonds, l’image physique d’une table T est hébergée par un fichier sur le disque (ou plusieurs fichiers sur plusieurs disques au besoin). Le fichier est lui-même composé de blocs que l’on appelle encore pages. Une page P comporte un plusieurs enregistrements, chacun d’eux étant l’image physique d’une ligne de T. Une ligne cohabite donc dans P avec 0 à plusieurs autres lignes de T, selon la place disponible.
    De ceci, je comprends que, par exemple, le bloc (ou la page) d'adresse 18524 sur le disque héberge les données des vaches 'Zaza', 'Blanchette', 'Marie', 'Pervenche'.
    Affirmatif. Maintenant, le nombre de vaches hébergées dans une page dépend de quelques facteurs :
    — La taille de la page. Par exemple, avec DB2 for z/OS V9, une page de table space (la ressource destinée à héberger les données d’une table) peut avoir une taille de 4KB (kilobytes), 8KB, 16KB, 32KB. C’est le DBA qui fixe la taille de la page quand il définit le table space (instruction CREATE TABLESPACE).

    — Le nombre d’octets réservés par le SGBD dans chaque page pour ses propres besoins.

    — Le nombre d’octets utilisés pour chaque ligne de la table.

    — Le pourcentage de place libre (free space) que l’on souhaite réserver dans chaque page, lors du chargement ou de la réorganisation du table space, pour que les INSERT qui suivront disposent de cette place, sans provoquer une désorganisation du table space.

    — Le rendement de la compression dans les pages. Il est certain que si les données sont plutôt du type CHAR ou VARCHAR, on pourra remplir les pages avec bien plus de lignes que si l’on décidait de ne pas compresser.
    Reprenons l’exemple de la table FEMELLE et partons sur la base de l’instruction suivante :
    Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    Create Table FEMELLE (
         ID_FEMELLE      Int          Not Null
       , NUM_OFFICIEL    Char(10)     Not Null
       , DATE_NAISS      DATE         Not Null
       , FEMELLE_NOM     Varchar(32)  Not Null
     , Constraint FEMELLE_PK Primary Key (ID_FEMELLE)
     , Constraint FEMELLE_AK UNIQUE (NUM_OFFICIEL) 
    ) ;
    Je précise à l’outil DB2 Estimator (gracieusement fourni par IBM) les éléments suivants :
    — Chaque colonne de la table conformément à l’instruction CREATE TABLE FEMELLE. L’outil en déduit une taille moyenne de 36 octets par ligne de la table.

    — La taille de la page : je choisis 4KB.

    — Le free space pour chaque page. Je prévois 20% (et je lui demande en plus qu’une page soit complètement libre après chaque paquet de 15 pages).

    — Je lui annonce une estimation du rendement de la compression de l’ordre de 60%.
    En réponse, DB2 Estimator me dit que chaque page de 4KB contiendra en moyenne 141 lignes, et que pour 67 000 000 de têtes, il faudra prévoir 507 000 pages, soit 2 Gigaoctets. Par comparaison si je lui dis que je ne tiens pas à comprimer les données, on passera à 3,9 Gigaoctets (966 000 pages, pour 74 lignes en moyenne par page).


    Citation Envoyé par CinePhil
    Là j'ai plus de mal...
    Don’t worry, je suis en train de rédiger à ce sujet.
    (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.

+ Répondre à la discussion
Cette discussion est résolue.
Page 2 sur 3 PremièrePremière 123 DernièreDernière

Discussions similaires

  1. Cardinalité sur association n-aire
    Par Spiff__ dans le forum Diagrammes de Classes
    Réponses: 2
    Dernier message: 30/06/2010, 15h54
  2. [MCD] cardinalité d'une association n-aire
    Par lidou87 dans le forum Schéma
    Réponses: 3
    Dernier message: 22/04/2009, 15h06
  3. [MCD] association n-aire et cardinalité.
    Par new_wave dans le forum Schéma
    Réponses: 2
    Dernier message: 12/04/2009, 19h13
  4. [DC] Implémentation d'une association n-aire (ternaire pour le coup)
    Par Kyrel dans le forum Diagrammes de Classes
    Réponses: 5
    Dernier message: 04/10/2007, 08h58
  5. association n-aire avec PowerAMC ?
    Par sara21 dans le forum PowerAMC
    Réponses: 1
    Dernier message: 25/05/2007, 02h44

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