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

  1. #21
    Expert éminent sénior

    Homme Profil pro
    bourreau
    Inscrit en
    mars 2010
    Messages
    4 679
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : mars 2010
    Messages : 4 679
    Points : 11 892
    Points
    11 892
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par NAGOL Voir le message
    Cela vaut peut-être le coup que j'illustre plus encore mes propos pour rendre la situation aussi clair que possible (tout en suivant la maxime présente dans la signature de Fsmrel "Faire simple, mais pas plus simple").
    C'est bien de remettre à jour périodiquement le modèle pour synthétiser les différentes propositions en amont
    Les explications complémentaires sont très utiles me concernant, j'y vois beaucoup plus clair.
    Donc les semaines sont automatiquement fermées quand le mercredi suivant est atteint.
    A analyser plus finement mais a priori, ça nous simplifie la vie : toute nouvelle mesure concerne obligatoirement la première semaine ouverte de la période, voilà comment faire le lien entre MESURE et SEMAINE via la relation "correspondre" .


    Citation Envoyé par NAGOL Voir le message
    Je n'y ai pas mis la contrainte d'intégrité fonctionnelle "id_appareil, date -> id_site" car je n'ai pas encore vu comment l'intégrer via "looping".
    Cette CIF n'a de sens que si un appareil est susceptible de changer de site et que par conséquent la relation entre appareil et site est à date. La CIF matérialisée par la flêche vers le site indique que pour une date et un appareil on n'a qu'un seul site.
    Si le besoin est avéré, je ne saurai pas vous aider, pour n'avoir jamais utilisé looping :/


    J'essaye de regarder votre nouvelle version ce WE

  2. #22
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir,


    Citation Envoyé par NAGOL Voir le message
    Je n'y ai pas mis la contrainte d'intégrité fonctionnelle "id_appareil, date -> id_site" car je n'ai pas encore vu comment l'intégrer via "looping".
    Je quitte provisoirement les périodes pour évoquer ce sujet d’un point de vue technique en général, puis Looping en particulier. Il s’agit donc de la prise en compte des CIF (contraintes d’intégrité fonctionnelle). Pour approfondir, se reporter à la référence Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001) (D. Nanci (RIP) et B. Espinasse), à partir de la page 114.

    Je cite au passage le groupe 135 de l’Afcet (« Journée du 15 novembre 1990 »), où est définie la CIF de façon formelle (contrainte d’unicité complète sur relation ») :

    « Unicité complète (sur relation : contrainte)
    Une contrainte d’unicité complète est définie sur la relation R, appelée sa "portée", avec pour "cible" l’un des individus C reliés par R, si et seulement si l’occurrence de C est déterminée de façon unique lorsque l’on connaît les occurrences des autres individus reliés par R. »


    Illustrons par l’exemple. Dans le diagramme ci-dessous on s’intéresse aux concours équestres que nous organisons à l’occasion avec Escartefigue, en compagnie notamment de CinePhil, SQLpro et Oishiiii. Y figure une ternaire à laquelle on a accroché une CIF, symbolisant la contrainte suivante :

    « Dans un concours, un cavalier ne monte qu’un cheval »

    Nom : nagol_mesures_CIF_alourdie(cheval,cavalier).png
Affichages : 127
Taille : 5,9 Ko

    Pour des raisons évidentes, il existe une 2e CIF, symbolisant la contrainte suivante :

    « Dans un concours, un cheval n’est monté que par un cavalier »

    La 1re CIF est représentée par un cercle hébergeant l’acronyme « CIF », branché sur l’association AFFECTATION qui est la portée de la CIF, tandis que CHEVAL en est la cible. Pour ne pas rendre le diagramme imbitable, je ne représente pas la 2e CIF.

    Pour que l’on puisse alléger les diagrammes, l’Afcet apporte une précision dans sa définition de la cible :

    « Cible (symbole : →) :
    [...]
    La cible est au bout d’une flèche issue du cercle représentatif de la contrainte ; pour une unicité complète dont la portée est formée d’une seule relation, on dispose d’une représentation réduite en transformant en flèche la patte qui porte sur la cible. »

    A la page 118 de leur ouvrage, Nanci et Espinasse confirment :

    « Lorsque ce type de contrainte est la seule portée par la relation, nous suggérons de l’intégrer à la relation en fléchant la patte la connectant à l’entité cible comme sur la figure 7.25. »

    Utilisons donc la « représentation réduite » puisque la portée ne contient que la seule association PARTICIPATION, et transformons en flèche la patte connectant CHEVAL et PARTICIPATION :

    Nom : nagol_mesures_CIF_allegee(cheval,cavalier).png
Affichages : 125
Taille : 6,2 Ko

    Venons-en aux appareils sur les sites et appliquons la contrainte :

    « A une date donnée, un appareil n’est utilisé que pour un seul site »

    Le diagramme correspondant est le suivant :

    Nom : nagol_mesures_CIF_allegee(appareils).png
Affichages : 126
Taille : 8,2 Ko

    A noter que Looping permet d’utiliser les CIF, mais malheureusement pas avec des représentations réduites, on va donc utiliser ici une représentation « lourde » :

    Nom : nagol_mesures_CIF_alourdie(appareils).png
Affichages : 128
Taille : 8,6 Ko

    Mode opératoire

    Quand on dessine, dans un 1er temps on crée le rond CIF, dans un 2e temps on tire un lien entre l’association et la CIF, dans un 3e temps on tire un lien entre la CIF et la cible (SITE) et on termine le travail en tirant les liens entre la CIF et les autres entités-types, à savoir APPAREIL et DATE.

    L’entité-type DATE n’a pas à donner lieu à une table dans le MLD (et surtout dans le code SQL), puisqu’on a seulement besoin de la présence de l’attribut uneDate dans la table AFFECTATION. Looping permet d’éviter la création d’une telle table DATE, il suffit de qualifier l’entité-type de « fictive », en cochant la case qui va bien (entité-type DATE) : le nom de l’entité-type est alors mis entre parenthèses par l’AGL. .

    Le MLD fourni par Looping, sous forme textuelle seule, sans diagramme

    SITE = (id_site);
    APPAREIL = (id_appareil);
    AFFECTATION = (#id_appareil, uneDate, #id_site);
    
    Code SQL fourni par Looping :

    CREATE TABLE SITE(
       id_site INTEGER PRIMARY KEY
    );
    
    CREATE TABLE APPAREIL(
       id_appareil INTEGER PRIMARY KEY
    );
    
    CREATE TABLE AFFECTATION(
       id_appareil INTEGER REFERENCES APPAREIL(id_appareil) NOT NULL,
       uneDate DATE NOT NULL,
       PRIMARY KEY(id_appareil, uneDate),
       id_site INTEGER REFERENCES SITE(id_site)
    );
    
    Ce code SQL est un peu fouillis et minimal de chez minimal, mais bon, on fera avec, sauf que :

    — Looping a quand même oublié de coder « NOT NULL » pour la colonne id_site de la table AFFECTATION ;

    — Bug de la part de son créateur et que celui-ci devra corriger : toujours concernant cette table, la ligne

    id_appareil INTEGER REFERENCES APPAREIL(id_appareil) NOT NULL

    doit être remplacée par

    id_appareil INTEGER NOT NULL REFERENCES APPAREIL(id_appareil)

    Sinon MySQL Workbench ne crée pas la table.

    Tout ça pour dire que lorsqu’on utilise Looping, alors la représentation de la contrainte « A une date donnée, un appareil est situé dans un seul site » est possible. Comme vous, je découvre cet AGL et ferai un peu de labo pour le secouer. J’ai par exemple pu constater que la mise en oeuvre des identifiants alternatifs n’est manifestement pas prévue.


    A propos du MLD

    Comme je l’ai mentionné, le MLD fourni par Looping est uniquement textuel. A condition de corriger les erreurs dans le code SQL généré, on peut demander à MySQL Workbench de produire le diagramme de ce MLD, puisque l’on peut faire de la rétro-conception :

    Nom : nagol_mesures_CIF_mld_retroconception).png
Affichages : 129
Taille : 19,9 Ko
    A faire au préalable

    Copier le code SQL et le coller par exemple dans un fichier de type texte, mais en suffixant par « sql » et en passant au format utf8 : le fichier est alors bon pour être traité par MySQL Workbench. Pour ma part, j’utilise MySQL Workbench 6.3.

    N.B.
    NAGOL, ne manquez pas de « liker » les messages qui au cours de cette discussion auront pu vous apporter un éclairage si faible fût-il, vos « formateurs » vous en sauront gré (vous pouvez aussi voter pour leurs compétences, voyez pour cela leur profil pro)…

    J’ai oublié de le signaler, pour Looping, "DATE" ne peut pas être un nom de table, donc d’entité-type, car c’est un mot réservé SQL, mais par exemple "DATE " et "[DATE]" sont acceptés.
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  3. #23
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir,


    Dans votre MCD vous allez réussir à intégrer la CIF exprimant la règle :

    « A une date donnée, un appareil n’est utilisé que pour un seul site »

    Ainsi, en plus des dates de mesure effectuées avec un appareil, on connaîtra aussi les dates d’affectation de cet appareil à un site.

    Si la mesure m1 a été effectuée avec l’appareil a1 à la date d1, sachant par ailleurs que dans l’intervalle i1 l’appareil a1 a été affecté au site s1, alors si d1 appartient à i1 on conclut que la mesure m1 a été effectuée sur le site s1.

    Est-ce bien ainsi que vous envisagez les choses ?
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  4. #24
    Membre à l'essai
    Inscrit en
    juin 2013
    Messages
    15
    Détails du profil
    Informations forums :
    Inscription : juin 2013
    Messages : 15
    Points : 12
    Points
    12

    Par défaut

    Bonjour à vous 2,

    Grâce aux précieux conseils de Fsmrel (que j'ai "plussoyé"), j'ai pu mettre en place la Contrainte d'Intégrité Fonctionnelle proposée par Escartefigue.

    Même si je n'ai pas encore le besoin précis de savoir sur quelle placette se trouve un id_appareil sur un intervalle daté, cela pourra très prochainement m'être nécessaire. Il vaut donc mieux le prévoir dès maintenant (c'est pourquoi j'ai plussoyé également cette réponse utile). Si je ne me trompe pas, je peux historiser un intervalle de temps pendant lequel un appareil est sur un site en ajoutant l'attribut "date_fin" à l'association "EQUIPER"

    De plus, en suivant le raisonnement de Fsmrel, cette même CIF me permet également de rattacher un site à une mesure donnée, via "id_appareil". Jackpot !
    Mais ne manque-t-il pas un trigger (ou autre ?) pour vérifier que l'attribut "date_mesure" de l'entité-type MESURE" (qui n'est pas Key), soit bien compris entre "date_debut_app_site" et "date_fin_app_site" ?

    Autres questions :
    a/ Estimez-vous que la "brochette" PERIODE > composer > SEMAINE > correspondre > MESURE que je présente réponde correctement à la question "Comment rattacher une mesure à une semaine donnée d'une période donnée ?"
    b/ Je me le demande de plus en plus : est-ce que "MESURE" mérite sa place d'entité-type ? Il m'importe surtout de savoir qu'un "id_appareil" ne peut donner qu'une seule mesure au cours d'une "semaine" (dans le sens "id_periode" + "num_dans_periode").

    Je tente de répondre à ces questions et j'aboutis à ce MCD. Est-il pertinent ?
    Nom : MCD_5.jpg
Affichages : 121
Taille : 114,5 Ko

  5. #25
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir,


    Citation Envoyé par NAGOL Voir le message
    j'ai pu mettre en place la Contrainte d'Intégrité Fonctionnelle proposée par Escartefigue
    Voilà une bonne chose de faite, bientôt vous pourrez apprendre aux autres comment utiliser Looping...

    Mais revenons sur le MLD fourni par Looping :

    SITE = (id_site);
    APPAREIL = (id_appareil);
    AFFECTATION = (#id_appareil, uneDate, #id_site);
    
    Par rétroconception du MLD en MCD, voici ce que l’on doit obtenir si l’on veut que le passage du MCD au MLD redonne le MLD initial (au passage, j’ai renommé l’attribut uneDate en affectation_date_debut) :

    Nom : nagol_site_appareil_affectation_pour_retro_vers_mcd_resultat.png
Affichages : 113
Taille : 4,4 Ko

    AFFECTATION est à la fois ce que Codd appelle une entité-type associative et une caractéristique (characteristic), c’est-à-dire une propriété multivaluée de l’entité-type APPAREIL, autrement dit une entité-type faible (weak entity-type), dont le sort dépend totalement, ontologiquement, de celui de l’entité-type dont elle est dépendante : la suppression d’un appareil entraîne de facto celle de ses affectations (on delete cascade en SQL). Techniquement, une entité-type faible est identifiée relativement à l’entité-type dont elle est dépendante. A cette occasion, j’observe que vous avez identifié APPAREIL relativement à TYPE_APPAREIL, donc APPAREIL serait une entité-type faible par rapport à l’entité-type « plus » forte TYPE_APPAREIL, et la suppression d’un type d’appareil entraînerait celle des appareils de ce type : sémantiquement parlant, l’entité-type APPAREIL est en droit de refuser cette situation : elle est une entité-type forte (kernel chez Codd), et côté SQL on est dans le scénario on delete no action. Bref, identifier APPAREIL relativement à TYPE_APPAREIL est abusif.

    Pour ma part, au lieu de l’association EQUIPER j’utiliserai plutôt une entité-type (telle que AFFECTATION), donc avec identification relativement à APPAREIL. L’entité-type CALENDRIER devient alors sans emploi et disparaît.


    Citation Envoyé par NAGOL Voir le message
    Si je ne me trompe pas, je peux historiser un intervalle de temps pendant lequel un appareil est sur un site en ajoutant l'attribut "date_fin" à l'association "EQUIPER"
    Il y a plus d’une façon de traiter de l’historique : en l’absence d’une date de fin, celle-ci peut être obtenue à partir de la date début de l’affectation suivante, mais ça devient compliqué à traiter si par exemple un appareil tombe en panne et n’est plus affecté à un site. Donc on appelle un chat un chat et on définit effectivement une date de fin. Le SGBD a son mot à dire dans cette histoire : par exemple PostgreSQL propose le type INTERVAL et les opérateurs qui vont bien, mais je ne sache pas que MySQL et SQL Server en fassent autant aujourd’hui.

    Se pose le problème de l’affectation actuelle : la date de fin est alors inapplicable. En SQL, elle peut valoir par exemple '9999-12-31' ou être marquée NULL. Cela dit, le bonhomme NULL est redoutable et il est absent dans les tables que je crée, il est hors la loi et je tire à vue sur lui dès qu’il montre le bout de son nez.

    Quoi qu’il en soit, à un moment donné vous devrez vous assurer du non recouvrement des périodes. Si vous utilisez PostgreSQL, vous pouvez créer une fonction ad-hoc (voyez par exemple ici, où l’on a une durée plutôt qu’une date de fin, mais on travaille au niveau de la seconde...) Sinon, triggers en vue...

    Par ailleurs, la modélisation des historiques a fait l’objet d’une théorie poussée (voyez Temporal Data and the Relational Model ainsi qu’une synthèse par Hugh Darwen). J’en reprends les bases de la structuration des données ici.

    MCD

    Nom : nagol_site_appareil_affectation_histo_mcd.png
Affichages : 115
Taille : 8,1 Ko

    MLD correspondant

    Nom : nagol_site_appareil_affectation_histo_mld.png
Affichages : 114
Taille : 7,2 Ko


    Citation Envoyé par NAGOL Voir le message
    cette même CIF me permet également de rattacher un site à une mesure donnée, via "id_appareil". Jackpot !
    Mais ne manque-t-il pas un trigger (ou autre ?) pour vérifier que l'attribut "date_mesure" de l'entité-type MESURE" (qui n'est pas Key), soit bien compris entre "date_debut_app_site" et "date_fin_app_site" ?
    J’avais posé la question suivante :

    Citation Envoyé par fsmrel Voir le message
    Si la mesure m1 a été effectuée avec l’appareil a1 à la date d1, sachant par ailleurs que dans l’intervalle i1 l’appareil a1 a été affecté au site s1, alors si d1 appartient à i1 on conclut que la mesure m1 a été effectuée sur le site s1.

    Est-ce bien ainsi que vous envisagez les choses ?

    Si réponse positive de votre part, alors au vu du MLD qui précède, pas de trigger en vue. En effet, on sait avec quel appareil a1 la mesure m1 a été faite, et la date d1 de la mesure permet alors de déterminer le site impliqué dans cette affaire (soit le site auquel a1 est actuellement affecté, soit le site tel que d1 se trouve dans l’intervalle [affectation_date_debut, affectation_date_fin].

    Pour les autres questions, je remets à un peu plus tard, car j’ai plein de fers au feu...
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  6. #26
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonjour,


    Citation Envoyé par NAGOL Voir le message
    Il m'importe surtout de savoir qu'un "id_appareil" ne peut donner qu'une seule mesure au cours d'une "semaine"
    A nouveau, dans la mesure où vous répondriez positivement à la question quant aux relations entre les sites et les mesures :

    Citation Envoyé par fsmrel Voir le message
    Est-ce bien ainsi que vous envisagez les choses ?
    Alors il existe la règle de gestion

    Pour une paire {APPAREIL, SEMAINE} il y a au moins et au plus une mesure.

    =>

    MESURER {APPAREIL    SEMAINE    MESURE    DATE_MESURE}
             a1          s1         m1        d1
    

    Le MLD d’hier, augmenté en conséquence (pour ne pas surcharger, je n’ajoute que ce qui concerne la règle de gestion ci-dessus quant aux tables et aux attributs)

    Nom : nagol_site_appareil_affectation_histo_mesure_mld.png
Affichages : 119
Taille : 12,9 Ko

    MCD (par rétroconception)

    Nom : nagol_site_appareil_affectation_histo_mesure_mcd.png
Affichages : 113
Taille : 7,8 Ko

    Pour y voir clair, en ce qui concerne les entités-types PERIODE et SEMAINE de votre MCD, pourriez-vous en fournir le contenu exhaustif pour l’année 2018 ? Merci.
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  7. #27
    Membre à l'essai Avatar de Paprick
    Homme Profil pro
    Professeur des Universités
    Inscrit en
    juin 2019
    Messages
    4
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 56
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Professeur des Universités
    Secteur : Enseignement

    Informations forums :
    Inscription : juin 2019
    Messages : 4
    Points : 10
    Points
    10

    Par défaut Looping, CIF et Commandes SQL

    Bonjour,
    Etant le concepteur de Looping, je me permets d'intervenir dans ces intéressants échanges.
    Tout d'abord pour apporter une réponse aux problèmes SQL signalés à juste titre par fsmrel :
    • le NOT NULL oublié pour les clés étrangères ciblées par une CIF a été corrigé.
    • la position de ces NOT NUL dans les commandes SQL a par ailler été clarifiée pour être compatibles avec tous les SGBD.

    Signalons cependant que, dans le cas de clés primaires, il est inutile de préciser le NOT NULL.
    Ces modifications ont été apportées pour la version 2.3 de Looping qui sera mise en ligne courant juillet.

    Par ailleurs, j'ai une question pour fsmrel : qu'appelez-vous représentation "réduite" des CIF en comparaison de la représentation "lourde" qui est la seule que je connais ?

    Quoiqu'il en soit, je ne suis pas personnellement un grand fan des associations n-aires auxquelles je préfère une décomposition en classes d'entités avec un usage sélectif d'identifiants relatifs pour assurer l'irréductibilité de la clé primaire et l'éventuelle CIF.

    Au plaisir de vous lire

  8. #28
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Merci Paprick de vous intéresser à nous et d’avoir proposé Looping.


    Citation Envoyé par Paprick Voir le message
    qu'appelez-vous représentation "réduite" des CIF en comparaison de la représentation "lourde" qui est la seule que je connais ?
    Je reprends ce que j’ai écrit dans les post #22 :

    « Je cite au passage le groupe 135 de l’Afcet (« Journée du 15 novembre 1990 »), où est définie la CIF de façon formelle (contrainte d’unicité complète sur relation ») :
    « Unicité complète (sur relation : contrainte)
    Une contrainte d’unicité complète est définie sur la relation R, appelée sa "portée", avec pour "cible" l’un des individus C reliés par R, si et seulement si l’occurrence de C est déterminée de façon unique lorsque l’on connaît les occurrences des autres individus reliés par R. »
    [...]
    Pour que l’on puisse alléger les diagrammes, l’Afcet apporte une précision dans sa définition de la cible :

    « Cible (symbole : →) :
    [...]
    La cible est au bout d’une flèche issue du cercle représentatif de la contrainte ; pour une unicité complète dont la portée est formée d’une seule relation, on dispose d’une représentation réduite en transformant en flèche la patte qui porte sur la cible. »

    Je joins le glossaire concocté par l’Afcet en 1990. Vous y retrouverez le nom d’auteurs bien connus des cercles merisiens.

    Je rappelle qu’à la page 118 de leur ouvrage de référence Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001), Nanci (RIP) et Espinasse confirment :

    « Lorsque ce type de contrainte est la seule portée par la relation, nous suggérons de l’intégrer à la relation en fléchant la patte la connectant à l’entité cible comme sur la figure 7.25. »

    (Se reporter à cette figure 7.25).


    Citation Envoyé par Paprick Voir le message
    Quoiqu'il en soit, je ne suis pas personnellement un grand fan des associations n-aires auxquelles je préfère une décomposition en classes d'entités avec un usage sélectif d'identifiants relatifs pour assurer l'irréductibilité de la clé primaire et l'éventuelle CIF.
    Pourquoi pas. A terme, on en arrive au modèle qualifié de « binaire pur » par le Professeur Henri Habrias (Le Modèle relationnel binaire, Méthode I.A. (NIAM), Eyrolles 1988, ouvrage semble-t-il devenu introuvable) qui reprend l’approche de William Kent, tous deux peu favorables au binaire pur (cf. pages 108-109 de l’ouvrage).

    Pour ma part, je privilégie l’approche de E.F. Codd (Extending the Database Relational Model to Capture More Meaning) et considère les associations comme des entités-types associatives, donc notamment associables et pouvant comporter des attributs participant à l’identification (cf. mon post #25), ce qui simplifie bien des choses. Le Professeur M. Bouzeghoub et l’incontournable A. Rochfeld en furent convaincus (cf OM La Conception Objet des systèmes d'Information), mais hélas ! un peu tard.


    A propos des identifiants alternatifs

    Au fil des décennies, j’ai fait pas mal de commentaires concernant la nécessité des identifiants alternatifs, comme ici (voyez « Histoire vécue » à la fin du billet). Il est crucial qu’un attribut d’une entité-type puisse être déclaré comme identifiant alternatif, par exemple de la façon intuitive suivante (pourquoi pas ?) :

    Nom : id_alternatf.png
Affichages : 104
Taille : 2,4 Ko

    A défaut, on doit compléter au stade SQL, ce qui devient vite une corvée plus qu’insupportable.
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  9. #29
    Membre à l'essai
    Inscrit en
    juin 2013
    Messages
    15
    Détails du profil
    Informations forums :
    Inscription : juin 2013
    Messages : 15
    Points : 12
    Points
    12

    Par défaut

    Bonsoir à vous tous,

    Tout d'abord, merci effectivement à Paprick d'avoir élaboré et partagé un logiciel tel que Looping : cela met à portée de personnes telles que moi la possibilité de créer ses MCD de manière intuitive et (du peu que j'en ai vu pour l'instant) efficace. Votre réactivité face aux propositions de Fsmrel n'est qu'un argument de plus en faveur de voter outil !

    Merci également à Fsmrel, qui ne lâche pas mon affaire et qui m'est d'un secours précieux ! J'apprends beaucoup grace à vous et Escartefigue. Suite à vos citations, je me suis également plongé dans "Ingénierie des systèmes d'information : Merise deuxième génération (4e édition, 2001) (D. Nanci (RIP) et B. Espinasse)".

    Pour me replonger dans mon sujet

    Citation Envoyé par fsmrel Voir le message
    dans la mesure où vous répondriez positivement à la question quant aux relations entre les sites et les mesures
    Je vous confirme que l'affirmation que vous me proposiez apparaît tout à fait juste, dans ma situation : "si la mesure m1 a été effectuée avec l’appareil a1 à la date d1, sachant par ailleurs que dans l’intervalle i1 l’appareil a1 a été affecté au site s1, alors si d1 appartient à i1 on conclut que la mesure m1 a été effectuée sur le site s1". Je vais donc pouvoir m'appuyer sur vos propositions pour faire évoluer mon MCD. Super nouvelle !

    Citation Envoyé par fsmrel Voir le message
    Pour y voir clair, en ce qui concerne les entités-types PERIODE et SEMAINE de votre MCD, pourriez-vous en fournir le contenu exhaustif pour l’année 2018 ? Merci.
    Dans la BDD qui m'a été confiée (et dont la partie "relationnelle" n'a pas été construite), je me trouve avec ces 2 tables (les intitulés ont été revus pour être plus clairs) :
    PERIODE = (id_periode, annee, annee_hydrique, num_dans_annee, date_debut, date_fin_S1, date_fin_S2,
              date_fin_S3, date_fin_S4, saisie_en_cours, archive, archive_date, corrige);
    MESURE = (id_site, id_periode, num_semaine, nom_operateur, date_mesure, date_decalage, heure,
              pluviometre, bac_a_neige1, bac_a_neige2, totalisateur_1, totalisateur_2, ..., remarque);
    
    PERIODE contiendrait (pour 2018) 13 occurrences : "id_periode" prenant les valeurs 340 à 352.
    MESURE contiendrait (pour 2018) 1404 occurrences,
           soit 27 "id_site" * 13 "id_periode" (de 340 à 352) * 4 "num_semaine" (1 à 4)
    
    A savoir pour PERIODE :
    - le "S" de "date_fin_S1", ... signifie "semaine" : date de fin "théorique" de la semaine 1 (c'est le mercredi qui est ciblé mais pas toujours respecté)
    - "saisie en cours", "archive" et "corrige" sont de type booleen

    A savoir pour MESURE :
    - je présente cette entité-type à titre informatif, c'est celle qui contient - dans l'état actuel - les mesures prises sur le terrain
    - l'attribut "num_semaine" ne prend que des valeurs de 1 à 4 représentatives du numéro de la semaine au sein de la période. Cet attribut n'apparait dans aucune entité-type "supérieure", je pourrais dire qu'il n'apparait que dans cette entité-type (dans l'état actuel).
    - à partir de l'attribut "pluviomètre", tous les attributs suivants caractérisent les mesures prises par les appareils cités.


    Une telle construction de base de donnée ne me paraît pas idéale, et j'imaginais plutôt les tables suivantes :
    PERIODE = (id_periode, annee, annee_hydrique, num_dans_annee, 
    SEMAINE = (num_semaine, id_periode#, num_dans_annee, date_debut, date_fin,
            saisie_en_cours, archive, archive_date, corrige);
    MESURE = (id_site#, id_periode#, num_semaine#, id_appareil#, nom_operateur,
            date_mesure, date_decalage, mesure, remarque);
    
    Où les attributs suivis de "#" correspondent à des clé étrangères
    Je ne vois alors pas d'intérêt à avoir un identifiant relatif sur MESURE
    
    PERIODE contiendrait (pour 2018) 13 occurrences : "id_periode" prenant les valeurs 340 à 352.
    SEMAINE contiendrait (pour 2018) 52 occurrences, soit 13 "id_periode" (de 340 à 352) * 4 "num_semaine" (1 à 4)
    MESURE contiendrait (pour 2018) 1404*n occurrences,
           soit 27 "id_site" * 13 "id_periode" (de 340 à 352) * 4 "num_semaine" (1 à 4) * n "id_appareil"
          (n pouvant prendre des valeurs de 0 à 22 selon les sites et les semaines)
    
    En bref, je me retrouverai avec beaucoup plus d'occurrences dans MESURE, mais beaucoup moins d'attributs (soit + d'adaptabilité et moins - voire pas - de NULL).
    Et SURTOUT, j'aurai un vrai modèle relationnel construit !

    Qu'en pensez-vous ?

  10. #30
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir,


    Vos collègues ont utilisé une représentation horizontale, vectorielle des dates :

    date_fin_S1, date_fin_S2, date_fin_S3, date_fin_S4


    Ça me rappelle l’histoire d’un de mes collègues qui avait prévu 7 colonnes dans ses stats réseau (une colonne par jour de la semaine) et à qui un beau jour on a demandé de passer à une colonne par jour de l’année : il a compris qu’il aurait mieux valu verticaliser tout ça. Ça me rappelle aussi un tableau au cœur d’un système d’exploitation chez IBM (années soixante...), prévue pour qu’un ordinateur gère au maximum 256 périphériques (on occupait à grand peine 10% de la table) : manque de chance, au début des années soixante-dix, il a fallu passer à 64000 (arrivée des écrans (passifs))...

    La représentation horizontale est évidemment à fuir, d’autant que SQL n’est pas adapté pour la manipulation des données sous cette forme.

    Pour la table PERIODE que vous envisagez, curiosité de ma part, qu’est-ce que l’année hydrique ?

    Maintenant, si l’attribut id_periode de la table PERIODE prend les valeurs 340 à 352, alors cet attribut ne peut pas prétendre participer à la clé primaire de la table, car les valeurs d’une telle clé ne doivent pas être significatives, chargées de de sens. Ce que j’écris là m’est dicté par mon expérience : pendant des décennies j’ai effectué de nombreux audit de bases de données dans des entreprises au bord de la panique, du fait de leur impossibilité à faire évoluer leurs bases dont les tables comportaient des clés significatives s’entremêlant dans tous les sens et conduisant à un blocage total. Je vous renvoie au billet De l’invariance des clés primaires.

    Dans ces conditions, si vous me suivez, il faudrait mettre en oeuvre un attribut période_code et c’est lui qui prendrait les valeurs 340 à 352, tandis que le rôle de id_periode serait limité à assurer l’unicité, le non doublonnage des périodes et, vu de l’utilisateur, cet attribut serait sans emploi, donc à lui cacher.

    Vous écrivez ;

    PERIODE contiendrait (pour 2018) 13 occurrences : "id_periode" prenant les valeurs 340 à 352.

    Indépendamment de mes remarques précédentes, reprenons votre description de l’en-tête de la table PERIODE :

    {id_periode, annee, annee_hydrique, num_dans_annee}

    Rien n’empêche que la paire {annee, num_dans_annee} comporte des doublons, en conséquence de quoi cette paire doit faire l’objet d’une clé candidate (disons alternative), ce qui, par parenthèses, avec Looping est aujourd’hui impossible : c’est au niveau SQL qu’il faudra pallier, ajouter cette clé.

    Nonobstant, par référence à votre tableau (post #9) je reste dubitatif quant à la nécessité de cette table.

    Votre tableau :

     
    PERIODE {id_periode    année    num_dans_annee}
             p1            2018     1
             p2            2018     2
             p3            2018     3
             ...           ...      ...
             p12           2018     12
             p13           2018     13
             ...           ...      ... 
             p14           2019     1
             p15           2019     2
             ...           ...      ...
    
    Mais bon, passons à la table SEMAINE. La présence des booléens saisie_en_cours, archive, corrige et autres attributs (archive_date) rend nécessaire sa mise en oeuvre. L’attribut num_semaine vous permet de distinguer chaque semaine d’une période donnée : d’accord (au stade SQL, vous pourrez même coder pour cette table une contrainte « CHECK (num_semaine BETWEEN 1 AND 4) ». Par contre, si vous conservez la table PERIODE, l’attribut num_dans_annee est alors présent à la fois dans l’en-tête des tables PERIODE et SEMAINE, il y a redondance et num_dans_annee doit donc être supprimé dans un des deux en-têtes.

    Supposons maintenant que la table PERIODE disparaisse. L’attribut annee doit migrer vers la table SEMAINE et si les valeurs 340 à 352, etc. ont un sens pour l’utilisateur, elles intègrent cette table (attribut code_periode). Puisque dans mon approche les valeurs prises par une clé primaire ne sont pas significatives, alors le singleton {id_periode} devient clé primaire tandis que le triplet {annee, num_dans_annee, num_semaine} devient clé candidate. Tant qu’à faire, renommons id_periode en id_semaine :

    SEMAINE {id_semaine  annee  num_dans_annee  num_semaine  code_periode  semaine_debut  semaine_fin  saisie en cours  ...}
             1           2018   1               1            340            db1           df1          b1
             2           2018   1               2            340            db2           df2          b2
             3           2018   1               3            340            db3           df3          b3
             4           2018   1               4            340            db4           df4          b4
    
             5           2018   2               1            341            db5           df5          b5
             6           2018   2               2            341            db6           df6          b6
             7           2018   2               3            341            db7           df7          b7
             8           2018   2               4            341            db8           df8          b8
             ...         2018   ...             ...          ...            ...           ...          ...
             49          2018   13              1            352            db49          df49         b49
             50          2018   13              2            352            db50          df50         b50
             51          2018   13              3            352            db51          df51         b51
             52          2018   13              4            352            db52          df52         b52
    
             53          2019   1               1            353            db53          df53         b53
    
    Malgré les apparences, les valeurs prises par id_semaine sont non significatives.

    Les attributs num_dans_annee et code_periode devraient apparemment être déduits l’un de l’autre, auquel l’un d’eux devrait disparaître. Supposons que ce soit num_dans_annee : le triplet {annee, code_periode, num_semaine} est alors clé candidate. Si c’est code_periode qui disparaît, c’est le triplet {annee, num_dans_annee, num_semaine} qui devient clé candidate. Si vous voulez à tout prix conserver les deux attributs, il y aura donc deux clés candidates. Il y a encore une clé candidate à prévoir, intégrant l’attribut semaine_debut (cela dit les périodes font l’objet de considérations particulières).


    Citation Envoyé par NAGOL Voir le message
    MESURE contiendrait (pour 2018) 1404*n occurrences,
    soit 27 "id_site" * 13 "id_periode" (de 340 à 352) * 4 "num_semaine" (1 à 4) * n "id_appareil"
    Selon le diagramme ci-dessous (cf. post #26), une mesure fait référence à un appareil, mais pas à un site, le nombre de mesures est donc à diviser par 27...

    Nom : nagol_site_appareil_affectation_histo_mesure_mld.png
Affichages : 119
Taille : 12,9 Ko
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  11. #31
    Membre à l'essai
    Inscrit en
    juin 2013
    Messages
    15
    Détails du profil
    Informations forums :
    Inscription : juin 2013
    Messages : 15
    Points : 12
    Points
    12

    Par défaut

    Bonjour,

    J'ai été contraint de retarder mon travail sur ce sujet pour d'autres priorités hautes, désolé pour ma réponse tardive. Pour autant, le sujet n'a pas cessé d'être tourné dans tous les sens dans un coin de ma tête, tout comme vos réponses éclairantes.

    Vos différents exemples de problèmes de gestion de base me rassurent quant-à ma perception d'une base fonctionnelle. OUF !

    Citation Envoyé par fsmrel Voir le message
    je reste dubitatif quant à la nécessité de cette table [PERIODE].
    A vous lire, j'ai d'abord pensé avoir été trop peu clair quand à l'usage du numéro de période (340 à 352 en 2018), tant cette notion de période me semble essentielle. Comme cette "immatriculation" de période est une composante de l'identification de nombreux autres objets relatifs (nous devons connaitre les divers paramètres météorologiques au grain de la semaine ET de la période, la concentration chimique des pluies au grain de la période, ... Les résultats calculés qui découlent de ces données sont aussi exprimés au grain de la semaine ET/OU de la période). Il me semblait alors important de pouvoir se référer à une telle table PERIODE pour maintenir l'intégrité référentielle.

    Avec le recul que m'ont donnés ces derniers jours sans vous répondre, je trouve finalement que l'idée d'intégrer l'attribut code_periode à la table SEMAINE est effectivement tout à fait envisageable. Ce qui m'interroge, c'est la répétition systématique (4 fois) d'un même code_periode (soit 1 fois pour chacun des 4 num_semaine) : est-ce un défaut de normalisation (je ne serais pas protégé d'une erreur de saisie) ?

    Citation Envoyé par fsmrel Voir le message
    Les attributs num_dans_annee et code_periode devraient apparemment être déduits l’un de l’autre, auquel l’un d’eux devrait disparaître.
    Effectivement :
    code_periode = (annee - 1992) * 13 + num_dans_annee - 11
    num_dans_annee = code_periode + 11 - [(annee - 1992) * 13]
    Il me semble de fait plus utile de conserver code_periode, qui est bien plus utilisé que num_dans_annee

    Citation Envoyé par fsmrel Voir le message
    une mesure fait référence à un appareil, mais pas à un site, le nombre de mesures est donc à diviser par 27...
    NON
    Je disais que MESURE contiendrait (pour 2018) 1404*n occurrences, soit 27 "id_site" * 13 "id_periode" (de 340 à 352) * 4 "num_semaine" (1 à 4) * n "id_appareil"
    En 2018, chacun des 27 sites est bien suivi pendant 13 périodes de 4 semaines. Pour chaque semaine, chaque site aura des mesures issues d'un nombre variable d'appareils :
    - 13 sites auront chacun jusqu'à 5 mesures (si tout est fonctionnel) délivrées par 5 appareils (1 pluviomètre, 2 bacs à neige, 2 tubes cumulateurs)
    - 14 sites auront chacun jusqu'à 29 mesures (si tout est fonctionnel) délivrées par 29 appareils (1 pluviomètre, 6 bacs à neige, 8 tubes cumulateurs, 9 fûts, 2 flacons, et 3 éprouvettes)

    Le diagramme proposé ne serait alors pas adapté à ma situation (?) car la mesure fait bien référence à un appareil, mais l'appareil est propre à un site donné (même si c'est le même type d'appareillage qui est installé sur chaque site). Autrement dit, si la mesure ne fait référence qu'à un appareil (et pas à un site) il faut alors considérer qu'il y a 27 occurrences de chaque appareil : l'appareil "bac à neige n° 2" est par exemple bien présent sur les 27 sites.

    Ou... faut-il rajouter 2 attributs à APPAREIL : type_appareil (ex : "bac à neige", c'était mon TYPE_APPAREIL.famille_appareil) et num_appareil (ex : "2", c'était mon APPAREIL.id_appareil) ? (cf. MCD du post #24)

    Merci d'avance pour votre aide car, à ce niveau, j'ai l'impression de m’emmêler les pinceaux...

    _____________________________________________

    Et pour vous répondre :
    Citation Envoyé par fsmrel Voir le message
    Pour la table PERIODE que vous envisagez, curiosité de ma part, qu’est-ce que l’année hydrique ?
    Comme nous sommes dans un contexte forestier, l'année hydrique correspond à la pluviosité associée à une saison de végétation. Ainsi, c'est la pluviosité qui s'étale d'octobre de l'année n-1 à septembre de l'année n qui sera utile pour la croissance des arbres au cours de l'année n.
    Pour les arbres feuillus en particulier, la croissance s'arrête au moment de la chute des feuilles et l'arbre se met dans un état d'activité réduit. L'eau qui s'accumule dans le sol à partir de l'automne lui sera utile pour reprendre sa croissance à partir du printemps suivant.

  12. #32
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir Nagol,

    Pas terrible a pluviosité à Paris ces temps-ci...


    Citation Envoyé par NAGOL Voir le message
    Comme cette "immatriculation" de période est une composante de l'identification de nombreux autres objets relatifs
    D’accord. Puisque la période est une référence pour d’autres objets, conservons l’entité-type PERIODE. Comme le code période a une valeur hautement significative pour l’utilisateur, on en fera un identifiant alternatif (unicité garantie), tandis que l’identifiant principal, artificiel, appelons-le id_periode, ne sera aucunement significatif et restera invisible pour l’utilisateur puisqu’il n’en aura pas l’usage.

    Au format tabulaire :

    PERIODE {id_periode    code_periode    annee}
             p1            340             2018
             p2            341             2018
             p3            342             2018
             p4            343             2018
             p5            344             2018
             p6            345             2018
             p7            346             2018
             p8            347             2018
             p9            348             2018
             p10           349             2018
             p11           350             2018
             p12           351             2018
             p13           352             2018
    
             p14           353             2019
    
    Les valeurs p1, p2, etc. sont purement symboliques.

    Conséquence sur la structure de l’entité-type SEMAINE : l’attribut id_periode permet la jointure avec PÉRIODE.

    SEMAINE {id_semaine  id_periode  semaine_debut  semaine_fin  saisie en cours  ...}
             s1           p1         db1            df1          b1
             s2           p1         db2            df2          b2
             s3           p1         db3            df3          b3
             s4           p1         db4            df4          b4
    
             s5           p2         db5            df5          b5
             s6           p2         db6            df6          b6
             s7           p2         db7            df7          b7
             s8           p2         db8            df8          b8
             ...          ...        ...            ....         ...
             s49          p13        db49           df49         b49
             s50          p13        db50           df50         b50
             s51          p13        db51           df51         b51
             s52          p13        db52           df52         b52
    
             s53          p14        db53           df53         b53
             ...          ...        ...            ....         ...
    
    Les valeurs s1, s2, etc. sont purement symboliques.

    MCD correspondant

    Nom : nagol_periode_semaine_mcd.png
Affichages : 78
Taille : 5,2 Ko

    MLD

    Nom : nagol_periode_semaine_mld.png
Affichages : 78
Taille : 5,3 Ko


    Citation Envoyé par NAGOL Voir le message
    si la mesure ne fait référence qu'à un appareil (et pas à un site) il faut alors considérer qu'il y a 27 occurrences de chaque appareil : l'appareil "bac à neige n° 2" est par exemple bien présent sur les 27 sites.
    Dans le MLD que j’ai proposé (posts #26, #30), APPAREIL est la table des appareils installés sur les sites, et il s’agit des appareils bien concrets. Si le "bac à neige n° 2" est installé sur chacun des 27 sites, alors il s’agit d’un type d’appareil, à moins que sur un site on ait nécessairement en même temps 27 "bac à neige n° 2"...


    Citation Envoyé par NAGOL Voir le message
    Ou... faut-il rajouter 2 attributs à APPAREIL : type_appareil (ex : "bac à neige", c'était mon TYPE_APPAREIL.famille_appareil) et num_appareil (ex : "2", c'était mon APPAREIL.id_appareil) ? (cf. MCD du post #24)
    Oui. Il est nécessaire de mettre en oeuvre une entité-type TYPE_APPAREIL. Pour sa part APPAREIL est à doter de l’attribut num_appareil (identifiant alternatif, à l’usage de l’utilisateur, contrairement à id_appareil).

    Evolution du MCD :

    Nom : nagol_site_appareil_affectation_histo_mesure_mcd(v2).png
Affichages : 81
Taille : 22,1 Ko

    Vous noterez que la patte d’association connectant APPAREIL et TYPE_APPAREIL est porteuse d’une cardinalité 1,1 et non pas 1,1(R) comme vous l’avez fait dans votre MCD (post #24). Au plan sémantique, la différence est importante : 1,1 veut dire qu’un appareil est d’un certain type, désigne un type, et par exemple qu’on ne peut supprimer un type d’appareil que s’il n’existe aucun appareil de ce type. Par contre, 1,1(R) veut dire qu’un appareil n’est qu’un composant, un élément, une pièce d’un type d’appareil, ne pesant pas plus que le nom de ce type, et la suppression d’un type entraîne de facto la suppression de tous les appareils appartenant à ce type...

    MLD

    Nom : nagol_site_appareil_affectation_histo_mesure_mld(v2).png
Affichages : 81
Taille : 23,5 Ko

    Progresse-t-on dans un sens qui vous convient ?
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  13. #33
    Membre à l'essai
    Inscrit en
    juin 2013
    Messages
    15
    Détails du profil
    Informations forums :
    Inscription : juin 2013
    Messages : 15
    Points : 12
    Points
    12

    Par défaut

    Bonjour fsmrel,

    Merci pour votre persévérance sur mon cas, qui se concrétise à mesure de vos explications. Je trouve effectivement que votre dernière proposition doit pouvoir répondre aux besoins exprimés tant pour la prise en compte de la périodicité des mesures que pour l'affectation des appareils à un site. Je me suis donc permis d'en reprendre la forme et d'aller plus loin dans les détails. Voici le résultat (que je soumet à vos critiques) :

    Nom : MCD_6.jpg
Affichages : 71
Taille : 180,8 Ko

    Par rapport à votre proposition :

    - J'ai conservé les attributs "date_semaine_debut" et "date_semaine_fin" de l'entité-type SEMAINE, tout en me demandant si "date_semaine_fin" n'était en fait pas suffisante ?

    - Je pense mettre l'attribut "mesure" (de la relation "mesurer") au format date/heure (TIMESTAMP). Vaut-il mieux avoir un attribut de type DATE + un attribut de type TIME ?

    - Si toutefois cela est envisageable (je n'ai pas trouvé de réponse dans les sources que j'ai consultées), je prévois dans TYPE_APPAREIL d'avoir un identifiant alternatif sur "code_type_app" + "num_type_app". Cela me permet de distinguer un type de matériel (par exemple "gouttière") et le modèle concerné (type de gouttière "1", ou "2" ou "3") car, pour une gouttière aux fonctions définies, différentes références peuvent exister.

    - De la même façon pour "APPAREIL", j'aurais un identifiant alternatif composé de "app_matricule_suivi" + "app_matricule_num". Cela me permet de distinguer pour un même type d'appareil "bac à neige" numéro "1" (connu via "id_type_app"), si l"id_appareil" correspond au bac à neige "1" ou "2" (: son "app_matricule_num") suivi "hors forêt" ("app_matricule_suivi" = "HCT") ou s'il s'agit du bac "1", "2", "3" ou "4" suivi en forêt ("app_matricule_suivi" = "SCP")

    - Comme détaillé ci-dessus, un même site "code_site" peut avoir une localisation "hors forêt" et une autre "en forêt" qui sont liées (bien que détachées géographiquement). De plus, il pourra y avoir plusieurs localisations géographiques simultanées pour ce même "code_site" situé "en forêt". Un "id_site" serait donc caractérisé par ses "code_site" + "type_site" + "num_site".
    Exemple : le code_site "s1" peut être composé de 1 localisation "hors forêt" + 3 localisation "sous forêt". J'aurai alors :
    SITE {id_site  code_site  type_site  num_site  coord_X  coord_Y  ...}
             i1        s1         hf         1       x1       y1
             i2        s1         sf         1       x2       y2
             i3        s1         sf         2       x3       y3
             i4        s1         sf         3       x4       y4
    
             i5        s2         hf         1       x5       y5
             i6        s2         sf         1       x6       y6
             ...       ...        ...        ...     ...      ...
    
    - Je suis allé plus loin dans l'identification du site. Dans le contexte "sous forêt", il arrive un moment ou les arbres sont coupés et où de nouveaux poussent : on change de génération (le peuplement forestier est totalement nouveau) tout en restant sur le même site (coordonnées géographique, surface, ... inchangées). J'ai donc rendu compte de cette particularité et j'y ai ajouté le suivi historique des successions de génération. Les appareils et les mesures sont bien rattachées au site géographique (et pas à la génération).

    Par ailleurs, j'ai une remarque générale sur les entités d'historisation "XXX_HISTO". J'ai lu votre article sur la 6è forme normale (très intéressant !) et la synthèse de Hugh Darwen que vous avez cité post #25. Comme je travaille avec PostgreSQL, ne sera t-il pas plus pertinent que je crée un attribut "durant" (de type INTERVAL_DATE) plutôt que "date_debut" + "date_fin" ?

    Encore une fois, merci d'avance pour tous vos précieux conseils !

  14. #34
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir,


    A propos de votre dernier MCD

    Association MESURER : quel est le sens des attributs remarque_mesure_terrain, remarque_mesure_validation ? les sites ne sont pas parties prenantes ?


    Citation Envoyé par NAGOL Voir le message
    J'ai conservé les attributs "date_semaine_debut" et "date_semaine_fin" de l'entité-type SEMAINE, tout en me demandant si "date_semaine_fin" n'était en fait pas suffisante ?
    Dans le post #11 vous avez écrit :

    Citation Envoyé par NAGOL Voir le message
    Une semaine est supposée durer idéalement 7 jours.
    Si donc il est toujours vrai que date_semaine_fin = date_semaine_debut + 7, alors il n’y a pas péril à faire l’économie d’une des deux dates, à ceci près que la constante "7" n’est plus connue par le SGBD (au fait, quel est le vôtre ?)

    En SQL on peut aussi définir une colonne en fonction d’une autre. Par exemple, avec SQL Server, au moyen de la fonction DATEADD, semaine_debut est déclaré comme égal à semaine_fin - 7 jours :

    CREATE TABLE SEMAINE 
    (
            id_semaine             INT  IDENTITY    NOT NULL
          , id_periode             INT              NOT NULL
          , semaine_debut       AS DATEADD(DAY, -7, SEMAINE_FIN) PERSISTED
          , semaine_fin            DATE             NOT NULL 
       , CONSTRAINT SEMAINE_PK PRIMARY KEY (id_semaine)
       , CONSTRAINT SEMAINE_PERIODE_FK FOREIGN KEY (id_periode)
             REFERENCES PERIODE (id_periode)
    ) ;
    
    Effectuons des inserts :

    
    INSERT INTO SEMAINE   (id_periode, semaine_fin) VALUES 
        (1, '2018-01-15'), (1, '2018-01-23')
    
    Au résultat :

    id_semaine    id_periode    semaine_debut    semaine_fin
    1             1             2018-01-08       2018-01-15
    2             1             2018-01-16       2018-01-23
    
    Les valeurs des deux colonnes (attributs) semaine_debut et semaine_fin sont remplaçables manuellement (instruction UPDATE).


    Citation Envoyé par NAGOL Voir le message
    Je pense mettre l'attribut "mesure" (de la relation "mesurer") au format date/heure (TIMESTAMP). Vaut-il mieux avoir un attribut de type DATE + un attribut de type TIME ?
    Mieux vaut ici un seul attribut de type TIMESTAMP. Les SGBD SQL offrent les fonctions permettant d’extraire la partie DATE et la partie TIME.

    Je vais examiner la suite de votre message dès que possible.



     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  15. #35
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir,


    Citation Envoyé par NAGOL Voir le message
    je prévois dans TYPE_APPAREIL d'avoir un identifiant alternatif sur "code_type_app" + "num_type_app". Cela me permet de distinguer un type de matériel (par exemple "gouttière") et le modèle concerné (type de gouttière "1", ou "2" ou "3") car, pour une gouttière aux fonctions définies, différentes références peuvent exister.
    Je vais utiliser le vocabulaire du modèle relationnel de données, plus commode pour traiter des identifiants alternatifs, d’autant plus que maintenant vous connaissez ce modèle. Ainsi, je parlerai de clé candidate plutôt que d’identifiant, de relvar (variable relationnelle) et de relation plutôt que d’entité-type, et d’entité, de tuple plutôt que de ligne ou d’occurrence, etc.

    Voyons voir si nous sommes en phase. Appelons K la paire {code_type_app, num_type_app}. K peut être clé alternative de la relvar TYPE_APPAREIL (autrement dit clé candidate) à condition de respecter les deux contraintes suivantes, participant à la définition des clés candidates :

    Unicité. Deux tuples de TYPE_APPAREIL ne peuvent avoir la même valeur pour K.
    Irréductibilité. Il n’existe pas de sous-ensemble strict de K garantissant la règle d’unicité.

    Par exemple, si 'G' est une valeur de code signifiant 'gouttière', K est effectivement clé candidate de la relation suivante :

    TYPE_APPAREIL {id_type_app  code_type_app  num_type_app  nom_type_app  dim_x ...}
                   i            G              1             gouttière     Xi    ...
                   j            G              2             gouttière     Xj    ...
                   k            G              3             gouttière     Xk    ...
    
                   m            B              1             bac à neige   Xm    ...
                   n            B              2             bac à neige   Xn    ...
    
    En observant qu’à lui seul le sous-ensemble {code_type_app} de K seul le sous-ensemble {code_type_app} de K ne respecte pas la règle d’unicité, même chose pour le sous-ensemble {code_type_app} de K.


    Par contre, dans la relation suivante, si l’on remplaçait 'G' par 'G1', 'G2', 'G3', alors la paire {code_type_app, num_type_app} ne serait pas clé alternative, la contrainte d’irréductibilité n’étant pas respectée dans la mesure où 'G1', 'G2', 'G3' seraient des valeurs respectant les deux contraintes. A noter que l’attribut num_type_app n’aurait plus ici de raison d’être :

    TYPE_APPAREIL {id_type_app  code_type_app  num_type_app  nom_type_app  dim_x ...}
                   i            G1             1             gouttière     Xi    ...
                   j            G2             2             gouttière     Xj    ...
                   k            G3             3             gouttière     Xk    ...
    
                   m            B1             1             bac à neige   Xm    ...
                   n            B2             2             bac à neige   Xn    ...
    
    Revenons à la 1re relation :

    TYPE_APPAREIL {id_type_app  code_type_app  num_type_app  nom_type_app  dim_x ...}
                   i            G              1             gouttière     Xi    ...
                   j            G              2             gouttière     Xj    ...
                   k            G              3             gouttière     Xk    ...
    
                   m            B              1             bac à neige   Xm    ...
                   n            B              2             bac à neige   Xn    ...
    
    Si pour la valeur 'G' de l’attribut code_type_app, à son tour l’attribut nom_type_app prend seulement la valeur 'gouttière', alors cette fois-ci la deuxième forme normale est violée et la relvar TYPE_APPAREIL doit être décomposée pour donner lieu aux deux relvars :

    www {code_type_app  nom_type_app}
         G              gouttière
         B              bac à neige
    
    TYPE_APPAREIL {id_type_app  code_type_app  num_type_app  dim_x ...}
                   i            G              1             Xi    ...
                   j            G              2             Xj    ...
                   k            G              3             Xk    ...
    
                   m            B              1             Xm    ...
                   n            B              2             Xn    ...
    
    On a appliqué ici le théorème de Heath.

    Si le problème se posait pour les autres attributs, même punition, même motif.


    Citation Envoyé par NAGOL Voir le message
    De la même façon pour "APPAREIL", j'aurais un identifiant alternatif composé de "app_matricule_suivi" + "app_matricule_num".
    Si je comprends bien, on aurait ceci :

    APPAREIL {id_appareil  id_type_app  app_matricule_num  app_matricule_suivi  ...}
              a1           m              1                HCT                  ...
              a2           m              1                SCP                  ...
              a3           m              2                HCT                  ...
              a4           m              2                SCP                  ...
              a5           m              3                SCP                  ...
              a6           m              4                SCP                  ...
    
    S’il en est ainsi, soit.


    Citation Envoyé par NAGOL Voir le message
    Je suis allé plus loin dans l'identification du site. Dans le contexte "sous forêt", il arrive un moment ou les arbres sont coupés et où de nouveaux poussent : on change de génération (le peuplement forestier est totalement nouveau) tout en restant sur le même site (coordonnées géographique, surface, ... inchangées).
    D’accord. Vous avez pris le temps d’étudier l’historisation : bravo !


    Citation Envoyé par NAGOL Voir le message
    Comme je travaille avec PostgreSQL, ne sera t-il pas plus pertinent que je crée un attribut "durant" (de type INTERVAL_DATE) plutôt que "date_debut" + "date_fin" ?
    Concernant la mise en oeuvre d’un attribut "durant", il faut effectivement profiter de ce que permet de faire PostgreSQL, par exemple avec le type daterange et les fonctions associées.


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  16. #36
    Membre à l'essai
    Inscrit en
    juin 2013
    Messages
    15
    Détails du profil
    Informations forums :
    Inscription : juin 2013
    Messages : 15
    Points : 12
    Points
    12

    Par défaut

    Bonjour Fsmrel,

    Citation Envoyé par fsmrel Voir le message
    Si donc il est toujours vrai que date_semaine_fin = date_semaine_debut + 7, alors il n’y a pas péril à faire l’économie d’une des deux dates, à ceci près que la constante "7" n’est plus connue par le SGBD (au fait, quel est le vôtre ?)
    Je n'avais pas dévoilé toutes les subtilités pour ne pas alourdir la réflexion mais, à l'instar des années bissextiles, nous sommes contraint d'avoir une semaine de 14 jours tous les 6 ans pour rattraper la course de la Terre autour du Soleil. Je vais donc garder les dates de début ET de fin de semaine pour rendre ce détail apparent.

    Mon SGBD est jusqu'à présent "Paradox". A l'époque où notre suivi a commencé (1992) le choix était pertinent, mais l'évolution des pratiques et des outils informatique m'amènent aujourd'hui à réaliser la migration vers PostgreSQL. J'en profite donc pour formaliser le modèle relationnel (qui n'était pas déclaré) et pour optimiser / faire évoluer "le contenant" de ces 27 années de suivi.
    J'ai pu bien avancer sur mon MCD et je vous présenterai prochainement une version complète pour le schéma que je vais construire sous PostgreSQL. Je ne vais pas griller les étapes, car des questions importantes me sont venues entre-temps.

    Citation Envoyé par fsmrel Voir le message
    Par exemple, si 'G' est une valeur de code signifiant 'gouttière', K est effectivement clé candidate de la relation suivante :

    TYPE_APPAREIL {id_type_app  code_type_app  num_type_app  nom_type_app  dim_x ...}
                   i            G              1             gouttière     Xi    ...
                   j            G              2             gouttière     Xj    ...
                   k            G              3             gouttière     Xk    ...
    
                   m            B              1             bac à neige   Xm    ...
                   n            B              2             bac à neige   Xn    ...
    
    En observant qu’à lui seul le sous-ensemble {code_type_app} de K seul le sous-ensemble {code_type_app} de K ne respecte pas la règle d’unicité, même chose pour le sous-ensemble {code_type_app} de K.
    C'est effectivement comme ça que je vois les choses.

    Citation Envoyé par fsmrel Voir le message
    Si pour la valeur 'G' de l’attribut code_type_app, à son tour l’attribut nom_type_app prend seulement la valeur 'gouttière', alors cette fois-ci la deuxième forme normale est violée
    Comme {code_type_app} ne donne pas de valeur significative pour nous et que nous n'en avons pas d'usage en l'état, je vais la supprimer et c'est {nom_type_app} qui prendra sa place. La paire {nom_type_app, num_type_app} de TYPE_APPAREIL respectera bien les contraintes d'unicité et d'irréductibilité, et la 2è forme normale sera respectée. Il en sera de même (et pour les mêmes raisons) pour les autres cas similaires.



    J'en reviens au MCD que j'ai proposé dans mon post#33 car l’historisation des données m'interroge à nouveau. Prenons le cas de la relvar "AFFECTATION_HISTO" :

    - Tel que présenté, je n'historise que l'évolution des "id_site" sur lesquels un "id_appareil" était installé sur une durée donnée. Tant que l'id_appareil existe dans APPAREIL tout fonctionne : "affectation_date_debut" stock la valeur du dernier changement de "id_site" pour cet "id_appareil". La relvar AFFECTATION_HISTO stockera les tuples décrivant sur quel(s) "id_site" et pour quelle durée était "id_appareil" avant.

    - Ceci étant dit, que se passe t-il lorsqu'un appareil est retiré d'un site sans être positionné sur un nouveau (s'il remisé ou détruit, par exemple) ? L'archivage dans "AFFECTATION_HISTO" fonctionnerait comme ci-dessus, mais cet "id_appareil" ne figurerait plus dans "APPAREIL". Comme "id_appareil" fait partie de la clé candidate de MESURER, j'ai l'impression que cela va me poser problème pour toutes les mesures rattachées à cet appareil, non ?

    Peut-être devrais-je revoir mes cardinalités de la sorte (?) pour m'autoriser à conserver dans APPAREIL un appareil non affecté à un site :
    [SITE]--0,n--(affectation_actuelle)--0,1--[APPAREIL]



    Ma deuxième question concerne mon historique de génération. Comme je l'ai décrit, plusieurs générations de peuplements forestier (: "id_generation") vont bien se succéder sur un même site géographique (: "id_site") mais j'ai l'impression que, dans cette situation, il me suffit d'avoir directement dans la relvar GENERATION une date de début et une date de fin de génération (qui serait 31/12/9999 pour la génération en cours, est-ce vraiment mieux que NULL ?).
    Une génération est clairement définie par sa date d'apparition et de disparition, mais elle n'est forcément rattachée qu'à un seul site tout au long de son existence.
    Ne puis-je donc pas faire abstraction de GENERATION_HISTO ?



    Enfin, une autre question m'est venue concernant l'historisation d'appareils de mesures. Cela ne concerne pas la partie du MCD que j'ai présenté, mais il peut tout de même parfaitement être illustré par la situation des relvar APPAREIL et AFFECTATION_HISTO:

    Je reçois des mesures en continu, stockées dans MESURER. Parfois, ces mesures me viennent avant même de savoir si un appareil a été changé (même marque, même modèle, ... simplement un autre exemplaire si le premier est en panne par exemple). Dans les faits, cela n'affecte pas la mesure. Celle-ci est par contre attribuée à un id_appareil qui n'est plus actif sur le site...
    Me sera t-il possible, après réception de la mesure, d'effectuer un update pour :
    1/ ajouter le nouvel "id_appareil" à APPAREIL, avec sa vraie date "affectation_date_debut"
    2/ lui attribuer rétroactivement les dernières mesures associées (à tort) à l'ancien appareil "id_appareil" dans MESURER (vu qu'on connait désormais "affectation_date_debut" du nouvel appareil)
    3/ archiver l'ancien "id_appareil" dans AFFECTATION_HISTO, sans perdre les mesures qui lui étaient associées avant l'affectation du nouvel appareil.

    Et comme on ne le dit jamais trop : MERCI d'avance pour votre aide !

  17. #37
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir NAGOL,


    Citation Envoyé par NAGOL Voir le message
    C'est effectivement comme ça que je vois les choses.
    D’accord. L’attribut code_type_app disparaissant, c’est donc la paire {nom_type_app, num_type_app} qui est clé alternative de la relvar TYPE_APPAREIL. Cela dit, si chaque valeur de libellé (par exemple 'gouttière') n’existait qu’une seule fois dans la base de données, ça serait prudent. En revanche, cela y ferait une table supplémentaire, mais avec le mécanisme des vues SQL, on peut rendre la chose transparente. A discuter.


    Citation Envoyé par NAGOL Voir le message
    [SITE]--0,n--(affectation_actuelle)--0,1--[APPAREIL]
    C’est presque ça, mais le bonhomme Null va s’inviter dans la base de données...

    Une solution raisonnable consiste à transformer AFFECTATION_ACTUELLE en entité-type faible, identifiée relativement à APPAREIL et lui faire porter l’attribut date_affectation_depuis :

    [SITE]--0,n--(r1)--1,1--[AFFECTATION_ACTUELLE (date_affectation_depuis)]--1,1(R)--(r2)--0,1--[APPAREIL]

    Si donc on enlève un appareil x du site auquel il est actuellement affecté (après avoir historisé) :

    DELETE FROM AFFECTATION_ACTUELLE WHERE id_appareil = x ;
    
    Alors pas de problème, Null reste banni.


    Je vais regarder la suite concernant l’historisation.


    Citation Envoyé par NAGOL Voir le message
    Et comme on ne le dit jamais trop : MERCI d'avance pour votre aide
    A l’occasion n’hésitez pas à faire comme tente d’y parvenir Mike, à savoir médailler


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  18. #38
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Citation Envoyé par NAGOL Voir le message
    Ne puis-je donc pas faire abstraction de GENERATION_HISTO ?
    Dans la mesure où l’historisation des générations ne décrit rien de particulier, une entorse est possible, la table GENERATION_HISTO peut disparaître. La date de fin de génération peut effectivement prendre la valeur 31/12/9999 pour la génération en cours (table GENERATION). Par mesure d’hygiène, évitons quand même l’intrusion du bonhomme Null dans la base de données, c’est-à-dire la confrontation à une logique ternaire posant le plus souvent des problèmes aux optimiseurs, même si dans le cas présent cela peut paraître sans danger.


    Citation Envoyé par NAGOL Voir le message
    Me sera t-il possible, après réception de la mesure, d'effectuer un update pour :
    1/ ajouter le nouvel "id_appareil" à APPAREIL, avec sa vraie date "affectation_date_debut"
    2/ lui attribuer rétroactivement les dernières mesures associées (à tort) à l'ancien appareil "id_appareil" dans MESURER (vu qu'on connait désormais "affectation_date_debut" du nouvel appareil)
    3/ archiver l'ancien "id_appareil" dans AFFECTATION_HISTO, sans perdre les mesures qui lui étaient associées avant l'affectation du nouvel appareil.
    A mon sens, l’opération est correcte. J’essaierai de trouver un moment pour faire un test.


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  19. #39
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir NAGOL,

    C’est parti pour un test !

    Les tables :

    CREATE TABLE SITE 
    (
          id_site                  INT                      NOT NULL,
          code_site                VARCHAR(5)               NOT NULL,
          nom_site                 VARCHAR(48)              NOT NULL,
       CONSTRAINT SITE_PK PRIMARY KEY (ID_SITE),
       CONSTRAINT SITE_AK UNIQUE (CODE_SITE)
    ) 
    ;
    CREATE TABLE TYPE_APPAREIL 
    (
          type_appareil_id         INT                      NOT NULL,
          code_type_appareil       VARCHAR(5)               NOT NULL,
          nom_type_appareil        VARCHAR(48)              NOT NULL,
       CONSTRAINT TYPE_APPAREIL_PK PRIMARY KEY (type_appareil_id),
       CONSTRAINT TYPE_APPAREIL_AK UNIQUE (code_type_appareil)
    ) 
    ;
    CREATE TABLE APPAREIL 
    (
          id_appareil              INT                      NOT NULL,
          num_appareil             VARCHAR(5)               NOT NULL,
          type_appareil_id         INT                      NOT NULL,
       CONSTRAINT APPAREIL_PK PRIMARY KEY (id_appareil),
       CONSTRAINT APPAREIL_AK UNIQUE (type_appareil_id, num_appareil),
       CONSTRAINT APPAREIL_TYPE_APPAREIL_FK FOREIGN KEY (type_appareil_id)
          REFERENCES TYPE_APPAREIL
    ) 
    ;
    CREATE TABLE AFFECTATION_ACTUELLE 
    (
          id_appareil              INT                      NOT NULL,
          id_site                  INT                      NOT NULL,
          affectation_date_depuis  DATE                     NOT NULL,
       CONSTRAINT AFFECTATION_ACTUELLE_PK PRIMARY KEY (id_appareil),
       CONSTRAINT AFFECTATION_ACTUELLE_APPAREIL_FK FOREIGN KEY (id_appareil)
          REFERENCES APPAREIL ON DELETE CASCADE,
       CONSTRAINT APPAREIL_SITE_FK FOREIGN KEY (id_site)
          REFERENCES SITE,
    ) 
    ;
    CREATE TABLE AFFECTATION_HISTO 
    (
          id_appareil              INT                      NOT NULL,
          affectation_date_debut   DATE                     NOT NULL,
          affectation_date_fin     DATE                     NOT NULL,
          id_site                  INT                      NOT NULL,
       CONSTRAINT AFFECTATION_HISTO_PK PRIMARY KEY (id_appareil, affectation_date_debut),
       CONSTRAINT AFFECTATION_HISTO_SITE_FK FOREIGN KEY (id_site)
          REFERENCES SITE,
       CONSTRAINT AFFECTATION_HISTO_APPAREIL_FK FOREIGN KEY (id_appareil)
          REFERENCES APPAREIL ON DELETE CASCADE
    ) 
    ;
    
    Un bout de jeu d’essai :

    INSERT INTO SITE (id_site, code_site, nom_site)
    VALUES 
       (1, '001', 'site 001')
     , (2, '002', 'site 002')
     , (3, '003', 'site 003')
    ;
    INSERT INTO TYPE_APPAREIL (type_appareil_id, code_type_appareil, nom_type_appareil) VALUES
      (1, 'bn', 'bac à neige')
    , (2, 'go', 'gouttière')
    , (3, 'pv', 'pluviomètre')
    ;
    INSERT INTO APPAREIL (id_appareil, num_appareil, type_appareil_id) VALUES
         (11, '001', 1),  (12, '002', 1),  (13, '003', 1)
       , (21, '001', 2),  (22, '002', 2),  (23, '003', 2)
       , (31, '031', 1),  (32, '032', 3),  (33, '033', 1)
    ;
    INSERT INTO AFFECTATION_ACTUELLE (id_appareil, id_site, affectation_date_depuis) VALUES
        (11, 2, '2019-06-01')
      , (21, 2, '2019-04-01')
      , (31, 3, '2019-02-01')
    ;
    INSERT INTO AFFECTATION_HISTO (id_appareil, affectation_date_debut, affectation_date_fin, id_site) VALUES
        (11, '2010-02-01', '2010-11-30', 1)
      , (11, '2010-12-01', '2015-03-31', 2)    
      , (11, '2015-04-01', '2017-10-31', 1)
      , (11, '2017-11-01', '2019-05-31', 1)
    
      , (21, '2012-02-01', '2012-11-30', 1)
      , (21, '2012-12-01', '2014-03-31', 2)    
      , (21, '2014-04-01', '2018-10-31', 1)
      , (21, '2018-11-01', '2019-03-31', 1)
    
      , (31, '2007-02-01', '2012-11-30', 1)
      , (31, '2012-12-01', '2014-03-31', 2)    
      , (31, '2014-04-01', '2016-10-31', 1)
      , (31, '2016-11-01', '2019-01-31', 1)
    ;
    

    La situation initiale est la suivante :

    (1) AFFECTATION_ACTUELLE

    id_appareil   id_site   affectation_date_depuis
    -----------   -------   -----------------------
    11            2         2019-06-01
    21            2         2019-04-01
    31            3         2019-02-01
    

    L’appareil 99 doit remplacer l’appareil 11.

    Si l’appareil 99 n’existe pas déjà, on le crée :

    INSERT INTO APPAREIL (id_appareil, num_appareil, type_appareil_id) VALUES
        (99, '099', 1)
    ;
    

    Méthode bourrin de remplacement :

    UPDATE AFFECTATION_ACTUELLE
        SET id_appareil = 99 
        WHERE id_appareil = 11
    ;
    
    Méthode pas bourrin (utilisation des seules propriétés naturelles) :

    UPDATE AFFECTATION_ACTUELLE
        SET id_appareil = 99 
        WHERE id_appareil = (SELECT id_appareil 
                             FROM   APPAREIL AS x 
                               JOIN TYPE_APPAREIL AS y 
                                    ON x.type_appareil_id = y.type_appareil_id
                             WHERE nom_type_appareil = 'bac à neige' 
                               AND num_appareil = '001') 
    ;
    

    Au résultat :

    id_appareil   id_site   affectation_date_depuis
    -----------   -------   -----------------------
    99            2         2019-06-01
    21            2         2019-04-01
    31            3         2019-02-01
    

    (2) AFFECTATION_HISTO

    Situation initiale, concernant l’appareil 11 à remplacer :

    id_appareil   affectation_date_debut   affectation_date_fin   id_site
    -----------   ----------------------   --------------------   -------
    11            2010-02-01               2010-11-30             1
    11            2010-12-01               2015-03-31             2
    11            2015-04-01               2017-10-31             1
    11            2017-11-01               2019-05-31             1
    
    Maintenant, l’appareil 99 doit remplacer l’appareil 11 pour les périodes <2015-04-01, 2017-10-31> et suivantes :

    Méthode bourrin :

    UPDATE AFFECTATION_HISTO
        SET id_appareil = 99 
          WHERE id_appareil = 11 
            AND  affectation_date_fin >= '2015-04-01'
    ;
    

    Méthode pas bourrin :

    UPDATE AFFECTATION_HISTO
        SET id_appareil = 99 
        WHERE id_appareil = (SELECT id_appareil 
                             FROM   APPAREIL AS x 
                               JOIN TYPE_APPAREIL AS y 
                                 ON x.type_appareil_id = y.type_appareil_id
                             WHERE nom_type_appareil = 'bac à neige' 
                               AND num_appareil = '001') 
        AND  affectation_date_fin >= '2015-04-01'
    ;
    
    Au résultat :

    id_appareil   affectation_date_debut   affectation_date_fin   id_site
    -----------   ----------------------   --------------------   -------
    11            2010-02-01               2010-11-30             1
    11            2010-12-01               2015-03-31             2
    99            2015-04-01               2017-10-31             1
    99            2017-11-01               2019-05-31             1
    

    Les résultats sont-ils conformes à ce que vous attendez ?


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

  20. #40
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    septembre 2006
    Messages
    6 773
    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 : 6 773
    Points : 24 683
    Points
    24 683
    Billets dans le blog
    16

    Par défaut

    Bonsoir NAGOL,


    Pour connaître la situation globale (en partant du plus récent)

    Méthode bourrin :

    SELECT id_appareil
         , affectation_date_depuis as debut
         , '9999-12-31' as fin
         , id_site
    FROM   AFFECTATION_ACTUELLE
    UNION
    SELECT id_appareil
         , affectation_date_debut
         , affectation_date_fin
         , id_site
    FROM   AFFECTATION_HISTO
    ORDER BY id_appareil, debut DESC
    ;
    
    Au résultat

    id_appareil     début         fin           id_site
    11              2010-12-01    2015-03-31    2
    11              2010-02-01    2010-11-30    1
    21              2019-04-01    9999-12-31    2
    21              2018-11-01    2019-03-31    1
    21              2014-04-01    2018-10-31    1
    21              2012-12-01    2014-03-31    2
    21              2012-02-01    2012-11-30    1
    31              2019-02-01    9999-12-31    3
    31              2016-11-01    2019-01-31    1
    31              2014-04-01    2016-10-31    1
    31              2012-12-01    2014-03-31    2
    31              2007-02-01    2012-11-30    1
    99              2019-06-01    9999-12-31    2
    99              2017-11-01    2019-05-31    1
    99              2015-04-01    2017-10-31    1
    

    Méthode pas bourrin :

    SELECT nom_type_appareil as type_appareil
         , num_appareil
         , affectation_date_depuis as debut
         , '9999-12-31' as fin
         , nom_site 
    FROM  AFFECTATION_ACTUELLE as x
          join APPAREIL as y ON x.id_appareil = y.id_appareil
          join TYPE_APPAREIL as z on y.type_appareil_id = z.type_appareil_id
          join SITE as t on x.id_site = t.id_site
    UNION
    SELECT nom_type_appareil
         , num_appareil
         , affectation_date_debut
         , affectation_date_fin
         , nom_site 
    FROM  AFFECTATION_HISTO as x
          join APPAREIL as y ON x.id_appareil = y.id_appareil
          join TYPE_APPAREIL as z on y.type_appareil_id = z.type_appareil_id
          join SITE as t on x.id_site = t.id_site
    ORDER BY nom_type_appareil, num_appareil, debut DESC
    
    
    Au résultat

    type_appareil    num_appareil    debut         fin           nom_site
    bac à neige      001             2010-12-01    2015-03-31    site 002
    bac à neige      001             2010-02-01    2010-11-30    site 001
    bac à neige      031             2019-02-01    9999-12-31    site 003
    bac à neige      031             2016-11-01    2019-01-31    site 001
    bac à neige      031             2014-04-01    2016-10-31    site 001
    bac à neige      031             2012-12-01    2014-03-31    site 002
    bac à neige      031             2007-02-01    2012-11-30    site 001
    bac à neige      099             2019-06-01    9999-12-31    site 002
    bac à neige      099             2017-11-01    2019-05-31    site 001
    bac à neige      099             2015-04-01    2017-10-31    site 001
    gouttière        001             2019-04-01    9999-12-31    site 002
    gouttière        001             2018-11-01    2019-03-31    site 001
    gouttière        001             2014-04-01    2018-10-31    site 001
    gouttière        001             2012-12-01    2014-03-31    site 002
    gouttière        001             2012-02-01    2012-11-30    site 001
    

    P.-S.
    Vu les médailles


     
    Faites simple, mais pas plus simple ! (A. Einstein)
    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 »)

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

Discussions similaires

  1. Question sur une relation ternaire dans un MCD
    Par sylsau dans le forum Schéma
    Réponses: 5
    Dernier message: 05/03/2006, 20h00
  2. Probleme de cardinalité dans mon mcd/mpd
    Par bluecurve dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 02/03/2006, 08h12
  3. Représentation d'une vue dans un MCD
    Par fredhali2000 dans le forum Schéma
    Réponses: 8
    Dernier message: 16/02/2006, 09h45
  4. besoin d'aide pour intégrer une entité dans un MCD
    Par barkleyfr dans le forum Schéma
    Réponses: 17
    Dernier message: 13/10/2005, 13h29
  5. Tables de référence dans un MCD
    Par MomoZeAsticot dans le forum Schéma
    Réponses: 6
    Dernier message: 21/02/2005, 14h37

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