Navigation

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

Schéma Discussion :

Modélisation de ma base


Sujet :

Schéma

  1. #1
    Membre régulier
    Modélisation de ma base
    Bonjour,

    Je modélise ma première base de données.
    J'espère que je suis dans la bonne partie du forum...

    Les principes généraux sont les suivants :
    - 1 personnalité peut être un élu ou un non-élu
    - Si la personnalité est un élu, elle est rattachée à 1 ou plusieurs mandatures
    - 1 mandature est rattachée à 1 mandat (qui correspond à une période complète de mandature, je ne sais pas si ici je suis clair). Par exemple, pour le mandat 2020-2026, j’aurais peut être une personnalité qui démissionnera en 2022 et donc son mandature aura été 2020-2022
    - 1 mandature est rattachée à 1 collectivité
    - 1 collectivité a 1 type
    - 1 personnalité (quelle soit élue ou non) peut être inscrite à 1 ou plusieurs collèges
    - 1 personnalité inscrite dans 1 collège à une qualité (ex. titulaire ou suppléants)
    - 1 collège est créé dans le cadre d’un mandat

    Ma modélisation vous parait-elle correcte ?




    Merci

  2. #2
    Membre éclairé
    Bonsoir,

    Je ne suis pas à l'aise pour travailler directement sur un schéma relationnel tel que vous le présentez…
    Je pense qu'il est préférable de
    réaliser tout d'abord le modèle conceptuel de données (MCD) avec un schéma entité-association (ou un diagramme de classes UML).

    Il existe bon nombre de logiciels permettant de le faire ; parmi ceux que je connais : Win'Design, PowerAMC, JMerise, Looping (et il en existe bien d'autres) => cf. discussion Quel logiciel télécharger pour réaliser un MCD

    Une fois la phase conception parfaitement effectuée, le passage au niveau logique puis physique est automatique, quel que soit le SGBD utilisé.

    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  3. #3
    Expert éminent sénior
    Bonsoir,

    J'abonde dans le sens de l'intervention de Paprick : la bonne démarche est de commencer par les règles de gestion (ce que vous avez commencé à faire, bravo) et le modèle conceptuel qui en découle.
    Le modèle logique n'en sera que la conséquence, automatiquement générée par tout logiciel de modélisation (dont certains sont gratuits), une fois le MCD validé.

  4. #4
    Membre régulier
    Merci pour vos retours.
    Je regarde ça et je vous fais une nouvelle proposition

  5. #5
    Membre régulier
    Bonjour,

    J’ai retravaillé ma copie (avec le meilleur logiciel disponible ).
    Cette reprise de ma copie m’a d’ailleurs amené à modifier ma modélisation (pour le meilleur ou pour le pire je ne sais pas encore !).


    J’ai deux types de personnalité : élu ou non élu.
    Que la personnalité soit un élu ou non, il appartient à au moins 1 collège.
    Si c’est un élu, il a obligatoirement 1 ou plusieurs mandats.
    1 mandat est rattaché à une période électorale.
    1 période électorale comprend 1 ou plusieurs mandats.
    1 période électorale comprend 1 ou plusieurs collèges.
    1 collège ne peut appartenir qu’à une collectivité.
    1 collège ne peut être rattaché qu’à une période électorale.
    1 collectivité peut avoir 1 ou plusieurs collèges.
    1 collectivité peut avoir 1 ou plusieurs mandats.
    1 mandat ne peut être rattaché qu'à une collectivité.

    Exemple :
    M. A est un élu. Il a un mandat de maire dans la collectivité Commune X. Son mandat s’inscrit dans la période électorale 2020-2026. M. A est inscrit dans le collège « Conseil municipal ». Son collège est également rattaché à une période électorale (la même période que le mandat de M. A) et appartient à la commune X (la même que M. A).
    M. A est également un élu de l’intercommunalité. Il a donc un 2ème mandat de conseiller intercommunal dans la collectivité Intercommunalité Z. Son mandat s’inscrit dans la période électorale 2020-2026. M. A est inscrit dans le collège « Conseil intercommunal ». Son collège est rattaché à une période électorale et appartient à l’intercommunalité Z.
    M. B n’est pas élu. Il n’a donc pas de mandat. Cependant, il est inscrit dans le collège « Commission accessibilité » en qualité de membre expert invité.

    Ma modélisation vous parait-elle correcte ?
    J'hésite sur un point particulièrement : avoir une entité "élu" et une entité "non élu". Au départ je pensais faire qu'une seule entité avec un champ "catégorie". Mais comme de la catégorie "élu" découle le mandat, je me suis dit qu'il était préférable de faire deux entités distinctes.

    Merci à vous pour vos retours



  6. #6
    Nouveau membre du Club
    ça reprend un peu ma problématique que j'avais à l'époque

    as-tu regardé ce sujet : https://www.developpez.net/forums/d1...e-utilisateur/

    Je vois déjà de nombreuses incohérences dans ton schéma :
    • une collectivité est une personne morale
    • un élu et un non élu sont des personnes physiques, ils ont des caractéristiques communes : civilité, nom, prénom
    • une personne physique peut être élue
    • une personne physique élue appartient à un collège
    • une collectivité propose des rôles (maire, adjoint, ...)
    • un rôle est tenu pendant un mandat
    • un mandat couvre une période électorale
    • un mandat est pourvu par une personne élue pendant une période
    • une personne qu'elle soit physique ou morale peut être domiciliée à 1 adresse
    • une personne qu'elle soit physique ou morale peut avoir de 0 à plusieurs téléphones
    • ...

  7. #7
    Expert éminent sénior
    Bonsoir

    Les élus comme les non élus sont des personnes qui partagent l'essentiel de leurs attributs (voire tous). Il faut donc modéliser un type d'entité "PERSONNE"
    Ce qui différenciera les élus des non élus, c'est que les élus participent au moins une fois à une association "exercer" qui manque dans votre modèle et qui sera en lien avec un type d'entité MANDAT (et non pas une association comme vous l'avez modélisé)
    Chaque mandat est rattaché à une et une seule collectivité, OK
    La notion de collège n'est pas claire

  8. #8
    Membre éclairé
    Bonsoir,
    Citation Envoyé par escartefigue Voir le message

    Les élus comme les non élus sont des personnes qui partagent l'essentiel de leurs attributs (voire tous). Il faut donc modéliser un type d'entité "PERSONNE"
    Ce qui différenciera les élus des non élus, c'est que les élus participent au moins une fois à une association "exercer" qui manque dans votre modèle et qui sera en lien avec un type d'entité MANDAT (et non pas une association comme vous l'avez modélisé)
    Il est possible d'utiliser l'héritage pour traiter les quelques différences entre élus et non élus.
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  9. #9
    Expert éminent sénior
    Citation Envoyé par Paprick Voir le message
    Bonsoir,

    Il est possible d'utiliser l'héritage pour traiter les quelques différences entre élus et non élus.
    Certes et aussi si seul l'un des deux sous-types participe à certaines associations

  10. #10
    Rédacteur

    par nature, dès que dans une entité vous voyez des attributs de noms similaires se terminant par 1, 2, 3, comme c'est le cas dans votre entité Elu :
    Adresse_1, 2, 3
    Téléphone_1, 2
    Ceci indique une mauvaise modélisation, avec notamment un viol de la première forme normale par "apocope"...
    Adresse_1, _2, _3 est une forme éludée, édulcorée, contournée.... de tableau
    APOCOPE : Abrègement d’un mot par la suppression des dernières lettres

    Commencez par externaliser ces informations à l'aide d'une entité adresse et d'une autre téléphone....
    Inspirez vous des modèles que j'ai donnée en exemple ici :
    https://blog.developpez.com/exercice...n_de_personnes
    https://blog.developpez.com/exercice..._d_une_adresse

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  11. #11
    Membre éclairé
    Bonsoir,
    Citation Envoyé par SQLpro Voir le message
    par nature, dès que dans une entité vous voyez des attributs de noms similaires se terminant par 1, 2, 3, comme c'est le cas dans votre entité Elu :
    Adresse_1, 2, 3
    Téléphone_1, 2
    Ceci indique une mauvaise modélisation, avec notamment un viol de la première forme normale par "apocope"...
    Adresse_1, _2, _3 est une forme éludée, édulcorée, contournée.... de tableau
    APOCOPE : Abrègement d’un mot par la suppression des dernières lettres
    Concernant l'adresse, je pense qu'il s'agit des 3 lignes d'une même adresse, et non pas de 3 adresses différentes.
    Si c'est bien le cas, il est possible de maintenir les 3 rubriques dans la classe d'entités, l'idéal étant de les spécifier plus précisément (numéro, type de voie, libellé voie, complément, ...).
    Pour les téléphones, si l'on veut éviter d'alourdir le schéma relationnel avec la création d'une nouvelle classe d'entités, il faudrait aussi distinguer la nature ou fonction des 2 numéros (fixe, domicile, portable, ...). Mais, s'il s'agit d'associer de 0 à N numéros, la classe téléphone s'impose avec un libellé et un numéro.
    Patrick Bergougnoux - Professeur des Universités au Département Informatique de l'IUT de Toulouse III
    La simplicité est la sophistication suprême (Léonard de Vinci)
    LIVRE : Modélisation Conceptuelle de Données - Une Démarche Pragmatique
    Looping - Logiciel de modélisation gratuit et libre d'utilisation

  12. #12
    Expert éminent sénior
    Bonjour,

    Pour ce qui concerne les adresses postales, il est recommandé de prendre en compte la norme de la Poste, accessible gratuitement sur le web.
    Il s'agit de 6 lignes de 38 caractères, plus une ligne supplémentaire si c'est une adresse à l'étranger.
    La dernière ligne est consacrée au code postal et à la commune.
    cf. https://www.rnvp-internationale.com/...F-Z-10-011.php

    Bien évidemment, la norme de la poste concerne la restitution de l'adresse, pas son stockage. Il va sans dire que le respect des formes normales doit s'appliquer (par exemple en externalisant le code postal et la commune de l'adresse proprement dite).

  13. #13
    Expert éminent sénior
    Citation Envoyé par SQLpro Voir le message
    par nature, dès que dans une entité vous voyez des attributs de noms similaires se terminant par 1, 2, 3, comme c'est le cas dans votre entité Elu :
    Adresse_1, 2, 3
    Téléphone_1, 2
    Ceci indique une mauvaise modélisation, avec notamment un viol de la première forme normale par "apocope"...
    Adresse_1, _2, _3 est une forme éludée, édulcorée, contournée.... de tableau


    Il est évident que les 3 attributs Adresse_1, Adresse_2, Adresse_3 (entité-type ELU) correspondent aux 3 lignes d’une adresse comme le fait observer Paprick. La première forme normale (1NF) n’est pas violée, car pour qu’il en soit ainsi, il faudrait « apocoper » les 3 attributs c’est-à-dire n’en faire qu’un seul (à l’aide de l’opérateur NEST des théoriciens des relations NF² (Non First Normal Form)). Cet attribut serait de facto de type ensemble, et cette fois-ci la 1NF serait sciemment violée. En l’occurrence, je rappelle la définition donnée de la 1NF par E. F. Codd, père de la théorie relationnelle, dans A Data Base Sublanguage Founded on the Relational Calculus, IBM Research Report RJ893 (July 26th 1971) :



    Dans ces conditions, tout y-celui allant au-delà de cette définition devra rétropédaler.

    Pour les plus jeunes, je rappelle qu’en 1971 Codd n’utilisait pas encore le terme « attribut » (pas plus que le terme « colonne »), mais ça ne change rien au sens de la définition donnée par Codd.


    Et même en supposant que les 3 attributs Adresse_1, Adresse_2, Adresse_3, représentent 3 adresses, ils font tous référence à un même domaine dont les valeurs ne sont pas des ensembles, donc la 1NF est encore respectée. Bien sûr, la modélisation serait alors pour le moins maladroite et à revoir, mais ceci est une autre affaire et ne ressortit pas à la normalisation.

    Un exemple analogue est fourni par IBM soi-même, qui n’hésite pas à déc... à plein tube, en écrivant :

    Citation Envoyé par Db2 for z/OS V12, Administration Guide

    First normal form
    A relational entity satisfies the requirement of first normal form if every instance of an entity contains only one value, never multiple repeating attributes. Repeating attributes, often called a repeating group, are different attributes that are inherently the same. In an entity that satisfies the requirement of first normal form, each attribute is independent and unique in its meaning and its name.
    Example: Assume that an entity contains the following attributes:

    EMPLOYEE_NUMBER
    JANUARY_SALARY_AMOUNT
    FEBRUARY_SALARY_AMOUNT
    MARCH_SALARY_AMOUNT

    This situation violates the requirement of first normal form, because JANUARY_SALARY_AMOUNT, FEBRUARY_SALARY_AMOUNT, and MARCH_SALARY_AMOUNT are essentially the same attribute, EMPLOYEE MONTHLY_SALARY_AMOUNT.


    Paradoxalement, n’en déplaise à IBM, la table (appelons-la EMPLOYEE) respecte la cinquième forme normale ! En effet, cette table a pour seule clé candidate {EMPLOYEE_NUMBER}, et chaque dépendance de jointure à laquelle elle satisfait est une conséquence de cette clé.

    Exemples de dépendances de jointure satisfaites pour la table EMPLOYEE (j’abrège les noms des attributs respectivement en E, J, F, M) :

    ☼{{E, J}, {E, F}, {E, M}}
    ☼{{E, J, M}, {E, F}, {E, M}}
    ☼{{E, J, F, M}, {E, F}, {E, M}, {E, F, M}}
    ...



    Cela dit, il est à noter que si l’on ne définit qu’un seul attribut pour le salaire, on est conduit à la structure suivante :

    VAR EMPLOYEE 
        BASE RELATION
    {
            EMPLOYEE_NUMBER      INTEGER
          , MONTH_SALARY         INTEGER
          , SALARY               INTEGER
        , KEY {EMPLOYEE_NUMBER, MONTH_SALARY}
    } ; 

    La table ne satisfait alors qu’à la dépendance de jointure triviale :

    ☼{{EMPLOYEE_NUMBER, MONTH_SALARY, SALARY}}

    elle est donc de facto en sixième forme normale (6NF) !
    ∎
    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

  14. #14
    Expert éminent sénior
    Dans mon message précédent, j’ai suivi à la lettre le commandement de Codd, j’ai donc fait montre d’une sévérité certaine. Si Codd a donné une définition aussi stricte de la 1NF, c’était en fait pour montrer que la logique du 2e ordre n’était pas nécessaire, relativisant ainsi ses préconisations faites son article de 1969, Derivability, Redundancy and Consistency of Relations Stored In Large Data Banks.

    En effet, dans son article de 1970, il écrit :



    Le fils spirituel et continuateur de l’oeuvre de Codd, C. J. Date en a logiquement conclu que toute relation dans laquelle chaque tuple contient exactement une valeur pour chacun de ses attributs est normalisée (c’est-à-dire en 1NF). Cette unicité des valeurs vaut évidemment quel que soit le type (domaine) d’un attribut. Le type d’un attribut ne se limite pas à INTEGER, CHARACTER, on peut définir de façon tout à fait licite des types (et les opérateurs pour les manipuler) tels que POINT (géométrique), SEGMENT, POLYGONE, voire plus si affinité : TUPLE, RELATION (se reporter à Databases, Types, and the Relational Model, The Third Manifesto).

    Nul besoin en l’occurrence d’étendre le modèle relationnel de données, l’unicité des valeurs prises par un attribut garantissant le respect de la normalisation 1NF. Par contre dans le cas des modèles NF² (prononcer NFsquare ou carré), c’est-à-dire non 1re forme normale, les auteurs ne manquent pas d’aller au-delà du relationnel (par exemple M. Roth, H. F. Korth, A. Silberschatz : Extended Algebra and Calculus for Nested Relational Databases, December 1988, ou encore S. Abiteboul (titulaire de la chaire d’informatique au Collège de France en 2011-2012), N. Bidoit : Non first normal form relations to represent hierarchically organized data (April 1984), etc.

    Ainsi, pour que la 1NF soit respectée :

    Dans chaque relation, chaque tuple doit contenir exactement une valeur pour chacun de ses attributs.

    Tel est le cas en ce qui concerne le modèle relationnel de données.

    Qu’en est-il dans le cas de SQL ? Voici ce qu’écrit C. J. Date :

    A table is in first normal form (1NF)—equivalently, such a table is normalized—if and only if it’s a direct and faithful representation of some relvar.


    Les propriétés que doit donc respecter une table pour être conforme, c’est-à-dire 1NF :

    1. Les lignes n’ont pas à être ordonnées (disons de bas en haut).
    2. Les colonnes n’ont pas à être ordonnées (disons de gauche à droite).
    3. Il ne peut y avoir de lignes en double.
    4. A l’intersection d’une ligne et d’une colonne il y a au moins et au plus une valeur du type (domaine) applicable.
    5. Toutes les colonnes sont régulières.

    Concernant le 5e point, les colonnes sont régulières quand elles ont toutes un nom, que deux colonnes n’ont pas le même nom, qu’il n’existe pas de colonnes « cachées », accessibles non pas par leur nom, mais par des opérateurs spéciaux (identifiants d’objets par exemple, timestamps, pointeurs, etc.)

    Si par exemple on définit la relvar EMPLOYEE (cf. mon message précédent) :

    VAR EMPLOYEE 
        BASE RELATION
    {
            EMPLOYEE_NUMBER      INTEGER
          , MONTH_SALARY         INTEGER
          , SALARY               INTEGER
        , KEY {EMPLOYEE_NUMBER, MONTH_SALARY}
    } ; 


    La déclaration SQL suivante est conforme, la table EMPLOYEE est une représentation fidèle de la relvar EMPLOYEE :

    CREATE TABLE EMPLOYEE 
    (
            EMPLOYEE_NUMBER      INTEGER    NOT NULL
          , MONTH_SALARY         INTEGER    NOT NULL
          , SALARY               INTEGER    NOT NULL
        , CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMPLOYEE_NUMBER, MONTH_SALARY)
    ) ; 


    N’oublions pas que les opérations relationnelles (PROJECT, RESTRICT, JOIN, UNION, etc.) appliquées à des relations permettent de produire de nouvelles relations. Il en va de même avec SQL. Ainsi, la restriction suivante produit une table conforme :

    SELECT EMPLOYEE_NUMBER, SALARY
    FROM   EMPLOYEE
    WHERE  MONTH_SALARY = 5 ;
    
    Mais là où ça ne va plus :

    SELECT *
    FROM   EMPLOYEE
    WHERE  MONTH_SALARY = 5 ; 


    En effet, la propriété 2 n’est pas respectée.

    Ou encore :

    SELECT EMPLOYEE_NUMBER, SALARY * 2
    FROM   EMPLOYEE
    
    En effet, la 2e colonne de la table résultante n’a pas de nom, la propriété 5 n’est pas respectée.


    Etc.
    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. #15
    Expert éminent sénior
    Encore un tour du côté de la 1NF.


    Citation Envoyé par fsmrel Voir le message
    je rappelle la définition initiale donnée de la 1NF par E. F. Codd, père de la théorie relationnelle, dans A Data Base Sublanguage Founded on the Relational Calculus, IBM Research Report RJ893 (July 26th 1971) :
    Relations are sets, but not all sets are relations.
    The elements of a relation of degree n are called n-tuples or tuples.
    A relation is in first normal form if it has the property that none of its domains has elements which are themselves sets.
    An unnormalized relation is one which is not in first normal form.


    Tout en se souvenant qu’à l’époque, par domaine il fallait aussi entendre attribut. On ne disait pas « l’attribut Ai est du domaine Di » ou, comme aujourd’hui, « l’attribut Ai est du type Ti ».

    Ce qu’a écrit Codd en 1971 a été reformulé quelques mois plus tard par Date en termes un peu plus simples, dans la 1re édition de An Introduction to Database Systems :

    At every row-and-column position value within the table there exists precisely one value, never a set of values

    Aujourd’hui, la définition (stable, gravée dans le marbre !) est très précise, et fait intervenir explicitement le type des attributs :

    Soit la relation r d’attributs A1, ..., An, respectivement de types T1, ..., Tn. r est en première forme normale (1NF) si et seulement si, pour tous les tuples t présents dans r, la valeur de l’attribut Ai dans t est du type Ti (i = 1, ..., n).


    Cette insistance sur le type des attributs est la conséquence du fait que ce type ne se limite pas aux traditionnels nombres (INTEGER, FLOAT, DECIMAL, etc.), aux chaînes de caractères, dates, etc., mais peut même être le type RELATION, c’est-à-dire que les RVA (Relation Valued Attributes) sont légaux.

    Au fil des ans, les affirmations diverses, péremptoires mais réfutables ont fleuri. Exemple :

    Pour être en première forme normale (1FN ou 1NF) : Les attributs d'une relation doivent être atomiques et doivent être en dépendance fonctionnelle avec la clef primaire de cette dernière.


    1. Concernant l’atomicité :

    L’atomicité des valeurs fut, est, et restera l’objet d’interminables discussions byzantines, voire polémiques, ce terme doit donc être évacué des définitions rigoureuses de la 1NF. Certes, une valeur est atomique si elle est insécable, irréductible, non décomposable, et alors ? Ce qui est atomique pour Paul ne l’est pas forcément pour Pierre. En amont de la modélisation de la base de données, c’est au chef de projet et à la maîtrise d’oeuvre de décider de la décomposition, de l’atomisation. Pour sa part, le DBA se limitera à s’assurer que la 1NF telle que formulée ci-dessus est respectée. Ainsi, ce qui suit suscite quelques remarques (excuse-moi Capitaine !) :

    Citation Envoyé par escartefigue Voir le message
    Il va sans dire que le respect des formes normales doit s'appliquer (par exemple en externalisant le code postal et la commune de l'adresse proprement dite).

    Du point de vue de l’application, le bon sens veut que le code postal et la commune soient externalisés, mais cela relève de l’analyse et absolument pas de la théorie de la normalisation !

    2. Concernant les dépendances fonctionnelles :

    En fait, c’est là que ça se gâte. Affirmer que pour respecter la 1NF les attributs d’une relation doivent dépendre fonctionnellement de sa clé primaire est contestable, en effet la définition de la 1NF de Codd ne dit rien de tel. Par ailleurs, une telle affirmation sous-entend que les dépendances fonctionnelles en cause sont non triviales.

    Prenons par exemple le cas de la relvar (variable relationnelle) R suivante :

    VAR R 
        BASE RELATION
    {
            A1      INTEGER
          , A2      INTEGER
        , KEY {A1, A2}
    } ; 


    Tous les attributs participent à la clé, en conséquence de quoi les dépendances fonctionnelles sont toutes triviales :

    {A1} → {A1}
    {A2} → {A2}
    {A1, A2} → {A1}
    {A1, A2} → {A2}


    Ce qui rend triviale à son tour la contrainte selon laquelle les attributs de R doivent dépendre fonctionnellement de la clé de R : dans le cas présent, cette contrainte est donc à passer au rasoir d’Ockham et la définition donnée ici de la 1NF à revoir.

    En passant, rappelons une fois de plus que dans le cadre de la théorie de Codd, une relation étant un ensemble, la clé est implicitement composée de l’ensemble des (noms des) attributs de la relation.

    Côté SQL : une table SQL pouvant être un sac (bag), c’est-à-dire comporter des lignes en double, pour prétendre être en 1NF, elle devra se conformer à la règle énoncée par D. J. Date (cf. post #14), ce qui sous-entend comme prérequis la mise en oeuvre d’une clé...
    Pour reprendre l’exemple précédent :

    CREATE TABLE R 
    (
            A1      INTEGER    NOT NULL
          , A2      INTEGER    NOT NULL
        , UNIQUE (A1, A2)
    ) ; 
    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. #16
    Expert éminent sénior
    A propos de la deuxième forme normale (2NF)

    Considérons l’énoncé suivant, que l’on retrouve un peu partout, car compatible avec ce qu’a écrit Codd en 1971 (voir par exemple A Data Base Sublanguage Founded on the Relational Calculus, IBM Research Report RJ893 (July 26th 1971)) :

    Pour qu’une relation soit en 2NF, il faut qu’elle soit en 1NF et que toutes les dépendances fonctionnelles entre la clé primaire et les autres attributs de la relation soient élémentaires.


    En fait, il l y a un trou dans la raquette. Il s’agit de démontrer que cette définition est incomplète, qu’elle doit être renforcée.

    On peut commencer par lui faire subir une cure d’amaigrissement, en effet on a vu précédemment (post #15 par exemple) qu’une relation est forcément en 1NF, il est donc inutile de rappeler cette contrainte dans la définition de la 2NF. Par contre, on doit la conserver si au lieu d’une relation coddienne, on a une table SQL, car les doublons y sont autorisés (cf. post #15).

    Il faut aussi se mettre d’accord sur ce qu’est une dépendance fonctionnelle, et plus précisément ce qu’est une dépendance fonctionnelle élémentaire.

    Soit une relvar R, et X et Y deux sous-ensembles quelconques d'attributs inclus dans l'en-tête H de R.
    Une dépendance fonctionnelle (DF pour abréger) est une expression de la forme X → Y dans laquelle X joue le rôle de déterminant et Y celui de dépendant. Pour faire court, une DF est une contrainte voulant que pour une valeur de X il y ait exactement une valeur de Y. Pour une définition formelle, se reporter au dictionnaire de C. J. Date, The New Relational Database Dictionary.

    Par référence aux définitions données ici, je rappelle qu’une dépendance fonctionnelle est soit triviale, soit partielle, soit irréductible (l’adjectif élémentaire peut être utilisé à la place de celui d’irréductible, mais il est plus vague).

    La dépendance fonctionnelle X → Y est dite triviale si Y est inclus dans X (inclusion au sens large). Une DF triviale est inviolable (axiome de réflexivité).

    Pour illustrer, prenons l’exemple ci-dessous de la relvar FPV des fournisseurs, des pièces des quantités et des villes (ce qu’on montre sous forme tabulaire est un instantané de la relvar, autrement dit une valeur, plus formellement une relation) :

    Le fournisseur Four_No fournit la pièce Piece_No selon la quantité Quantite et réside dans la ville Ville.

    Règles de gestion particulières, imposées en amont par le chef de projet et la maîtrise d’oeuvre :

    (RG01) Un fournisseur donné ne fournit une pièce donnée qu’en une quantité donnée, unique.

    (RG02) Un fournisseur donné ne réside que dans une seule ville


    FPV {Four_No    Piece_No    Quantite    Ville}
         S1         P1          300         Lille  
         S1         P2          200         Lille  
         S1         P3          400         Lille  
         S1         P4          200         Lille  
         S1         P5          100         Lille  
         S1         P6          100         Lille  
         S2         P1          300         Paris  
         S2         P2          400         Paris  
         S3         P2          200         Paris  
         S4         P2          200         Lille  
         S4         P4          300         Lille  
         S4         P5          400         Lille  
    


    Exemples de dépendances fonctionnelles triviales applicables à FPV :

    {Four_No} → {Four_No}

    {Piece_No, Quantite} → {Quantite}

    {Four_No, Piece_No, Quantite, Ville} → {Four_No, Piece_No, Quantite, Ville}

    Etc.


    Revenons à la relvar R et ses sous-ensembles d’attributs X et Y, et soit C un attribut quelconque de l’en-tête de R.

    La dépendance fonctionnelle X → {C} est dite partielle si elle n'est pas triviale et s'il existe Y strictement inclus dans X tel que Y → {C}.

    Par exemple, du fait de la règle de gestion RG02, la relvar FPV contient la DF {Four_No} → {Ville}. Cette DF n’est pas triviale car {Ville} n’est pas un sous-ensemble de {Four_No}. Elle n’est pas non plus partielle car {Four_No} ne contient pas de sous-ensemble non vide qui y soit strictement inclus.

    Par contre, la DF {Four_No, Piece_No} → {Ville} inférée de la règle RG01 est partielle, car le déterminant {Four_No, Piece_No} contient le sous-ensemble {Four_No} tel que {Four_No} → {Ville}.

    La dépendance fonctionnelle X → Y est dite irréductible (ou élémentaire ou totale, au choix) si elle n’est ni triviale ni partielle.

    Dans le cas de la relvar FPV, la DF {Four_No} → {Ville} n’étant ni triviale ni partielle, elle est irréductible.

    Comme on l’a vu, il existe la DF {Four_No, Piece_No} → {Quantite} traduisant la règle RG01. Cette DF n’est ni triviale ni partielle, elle est donc irréductible elle aussi.

    Passons au clés.

    Soit une relvar R d’en-tête H = {A1, A2, ..., An}, où A1, A2, ..., An sont les (noms des) attributs.

    Soit K un sous-ensemble (non strict) d’attributs de H.
    Je rappelle que K est surclé de R si et seulement si K vérifie la propriété suivante :

    Unicité : deux tuples (lignes en SQL) distincts de R ne peuvent avoir la même valeur pour K.

    Ou encore, histoire de faire intervenir les dépendances fonctionnelles (cf. Principles of Database Systems de J. D. Ullman) :

    K est surclé si et seulement si

    K → {A1}
    K → {A2}
    ...
    K → {An}


    Je rappelle en outre que K est clé candidate de R si et seulement si K vérifie les deux propriétés suivantes (dont la propriété d’unicité qui vient d’être énoncée) :

    Unicité : deux tuples distincts de R 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é.


    Ainsi, la clé candidate est un cas particulier de la surclé.

    A noter qu’un sous-ensemble non strict S d’une clé candidate K est appelé sous-clé. Ainsi, la clé candidate est une sous-clé particulière. Si S est un sous-ensemble strict de K, alors S est une sous-clé stricte.

    Dans le cas de la relvar FPV, le quadruplet {Four_No, Piece_No, Quantite, Ville} est une surclé. Est-ce une clé candidate ? Démontrons-le.

    Jusque-là on a les DF irréductibles suivantes :

    {Four_No} → {Ville}
    {Four_No, Piece_No} → {Quantite}


    Pour trouver l’ensemble des clés candidates d’une relvar, on sort l’artillerie lourde, à savoir l’algorithme du seau. Dans notre exemple, on peut prendre un raccourci selon lequel si un attribut n’est élément d’aucun dépendant de DF non triviale, alors il appartient nécessairement aux clés candidates de la relvar.

    En vertu de quoi, dans le cas de la relvar FPV, les attributs Four_No et Piece_No appartiennent aux clés de cette relvar. La paire {Four_No, Piece_No} est-elle clé candidate ? Autrement dit vérifie-t-elle les règles d’unicité et d’irréductibilité des clés candidates ?

    Pour qu’il en soit ainsi, en reprenant la règle d’unicité selon Ullman, on doit montrer que :

    {Four_No, Piece_No} → {Four_No}
    {Four_No, Piece_No} → {Piece_No}
    {Four_No, Piece_No} → {Quantite}
    {Four_No, Piece_No} → {Ville}


    La 1re et la 2e DF sont triviales, donc automatiquement vérifiées.
    La 3e DF est donnée (conséquence de la règle de gestion RG01).
    Concernant la 4e DF, c’est un peu moins simple, pour l’obtenir on est obligé de partir de la DF donnée {Four_No} → {Ville} et de la soumettre à l’algorithme du seau, ou directement aux axiomes d’Armstrong. Utilisons ceux-ci :

    Partant de la DF {Four_No} → {Ville}, dans un 1er temps, par augmentation on produit la DF
    {Four_No, Piece_No} → {Ville, Piece_No}

    Puis dans un 2e temps, en utilisant la règle de décomposition, on produit les deux DF :

    {Four_No, Piece_No} → {Piece_No}
    {Four_No, Piece_No} → {Ville}

    La 1re DF est triviale, la 2e est celle que l’on voulait obtenir pour compléter la liste des DF permettant de garantir la règle d’unicité. On vérifie sans problème que la propriété d’irréductibilité est respectée.

    Il ne manque pas une seule des DF voulues, la paire {Four_No, Piece_No} est donc clé candidate de la relvar FPV. En existe-t-il d’autres ? Prenez votre seau à dépendants et remplissez-le, vous verrez bien (ça occupe...).

    Quoi qu’il en soit, revenons à l’énoncé initial de la deuxième forme normale. Je le reprends ici (débarrassé de la contrainte de 1NF), car il met en scène « la clé primaire » :

    Pour qu’une relation soit en 2NF, il faut que toutes les dépendances fonctionnelles entre la clé primaire et les autres attributs de la relation soient élémentaires.


    Jusqu’ici on a parlé des clés candidates mais pas des clés primaires. En fait, le concept de clé primaire a été défini par Codd dans son article fondateur de 1970, A Relational Model of Data for Large Shared Data Banks, alors qu’il n’a défini le concept de clé candidate qu’un peu plus tard (voir par exemple A Data Base Sublanguage Founded on the Relational Calculus, IBM Research Report RJ893 (July 26th 1971) ou encore Further Normalization of the Data Base Relational Model, IBM Research Report RJ909 (August 31st 1971)).

    Je cite (et traduis) Codd :

    Pour chaque relation R, une de ses clés candidates est arbitrairement désignée comme clé primaire.


    On voit immédiatement qu’il s’agit d’un choix arbitraire, menant donc à des considérations plus psychologiques que mathématiques. Autant dire qu’aujourd’hui la théorie relationnelle a été nettoyée, le concept de clé primaire en est sorti et ne relève plus que de l’histoire du relationnel, on n’a plus que des clés candidates (on peut faire désormais l’économie de l’adjectif candidate). Evidemment, si on regarde du côté SQL, du fait du poids de l’héritage, on ne peut décemment pas évacuer le concept et modifier tous les CREATE/ALTER TABLE pondus depuis le début des années quatre-vingts, et concernant des tables encore bien vivantes...

    Bon, conservons les clés primaires . La relvar FPV est-elle en 2NF ?
    La paire {Four_No, Piece_No} étant clé candidate on la prend pour clé primaire de la relvar. On a vu qu’elle est le déterminant dans les DF suivantes :

    {Four_No, Piece_No} → {Four_No}
    {Four_No, Piece_No} → {Piece_No}
    {Four_No, Piece_No} → {Quantite}
    {Four_No, Piece_No} → {Ville}


    En particulier, la DF {Four_No, Piece_No} → {Ville} n’est pas triviale, mais partielle. Je rappelle la définition :

    La dépendance fonctionnelle X → {C} est dite partielle si elle n'est pas triviale et s'il existe Y strictement inclus dans X tel que Y → {C}.


    Symbolisons {Four_No, Piece_No} par X, {Four_No} par Y et Ville par C.
    Du fait de la DF donnée {Four_No} → {Ville}, c’est-à-dire Y → {C}, comme Y est bien strictement inclus dans X, la DF {Four_No, Piece_No} → {Ville} est de facto partielle, elle n’est donc pas élémentaire.

    La relvar FPV a pour clé primaire la paire {Four_No, Piece_No} qui intervient en tant que déterminant dans la DF {Four_No, Piece_No} → {Ville}. Cette DF n’est pas élémentaire mais seulement partielle. Conclusion : la relvar n’est pas en 2NF.

    So far so good. L’histoire n’est pourtant pas terminée. Ajoutons à l’en-et tête de FPV un attribut fpvId, tel que {fpvId} soit clé candidate. On a donc désormais deux clés candidates, {fpvId} et {Four_No, Piece_No}.

    Dans le style SQL :

    CREATE TABLE FPV 
    (
            fpvId     INTEGER        NOT NULL
          , Four_No   INTEGER        NOT NULL
          , Piece_No  INTEGER        NOT NULL
          , Quantite  INTEGER        NOT NULL
          , Ville     VARCHAR(48)    NOT NULL
        , CONSTRAINT FPV_PK PRIMARY KEY (fpvId)
        , CONSTRAINT FPV_AK UNIQUE (Four_No, Piece_No)
    ) ; 


    On a choisi (« arbitrairement » pour suivre Codd ) {fpvId} pour être clé primaire. A la collection des DF recensées jusqu’ici, il faut ajouter {Four_No, Piece_No} → {fpvId}, ainsi que les suivantes :

    {fpvId} → {fpvId}
    {fpvId} → {Four_No}
    {fpvId} → {Piece_No}
    {fpvId} → {Quantite}
    {fpvId} → {Ville}


    La DF {fpvId} → {fpvId} est triviale, elle compte pour du beurre. Quant aux autres, leur déterminant {fpvId} étant singleton, elles sont élémentaires.

    Pour reprendre une fois de plus l’énoncé proposé de la 2NF :

    Pour qu’une relation soit en 2NF, il faut que toutes les dépendances fonctionnelles entre la clé primaire et les autres attributs de la relation soient élémentaires.

    En l’occurrence, on voit qu’est élémentaire chaque DF ayant pour déterminant la clé primaire {fpvId} et pour dépendant un autre attribut de l’en-tête de la relvar.

    Cette fois-ci la relvar FPV, de clé primaire {fpvId} est en 2NF.

    Le constat : selon la clé candidate retenue pour être clé primaire, dans un cas la relvar FPV viole la 2NF, dans l’autre cas elle la respecte... Autrement dit, la définition de la 2NF est à changer ! 

    Allons-y. Appelons attribut non-clé tout attribut de l’en-tête d’une relvar R qui n’appartient à aucune clé candidate de R.

    On peut alors donner la définition suivante, due à C. J. Date (Database Design and Relational Theory, Normal Forms and All That Jazz) :

    La relvar R est en deuxième forme normale (2NF) si et seulement si pour chaque clé candidate K de R et chaque attribut non-clé A de R, la DF K → {A} est élémentaire.


    Comme je l’ai écrit, le préfère irréductible à élémentaire, car plus précis, mais bon.

    Avec cette nouvelle définition de la 2NF, comment les choses se passent-elles avec la relvar FPV (d’en-tête {fpvId, Four_No, Piece_No, Quantite, Ville}) ?

    On dispose des clés candidates suivantes :

    K1 = {fpvId}

    K2 = {Four_No, Piece_No}


    Certes, K1 ne pose pas de problème, mais avec K2 ça coince. il existe en effet la DF K2 → {Ville} qui n’est pas élémentaire, mais partielle, la 2NF est donc violée.

    Moralité : Mieux vaut s’appuyer sur la bonne définition de la 2NF et évacuer la définition fautive.
    ■

    Dans l’ouvrage que j’ai cité, C. J. Date donne une définition supplémentaire, mais évidemment équivalente de la 2NF :

    La relvar R est en deuxième forme normale (2NF) si et seulement si pour chaque DF non triviale X → Y valant pour R, au moins une des conditions suivantes est vérifiée :

    (a) X est surclé
    (b) Y est sous-clé
    (c) X n’est pas sous-clé


    Cas de FPV :

    Passons par exemple à la moulinette la DF {Four_No, Piece_No} → {Ville} : le déterminant {Four_No, Piece_No} est clé candidate, donc surclé, il s’ensuit que la condition (a) est vérifiée, ainsi cette DF n’est pas délinquante, même si les conditions (b) et (c) ne sont pas vérifiées (notamment concernant (c), puisqu’une clé candidate est un cas particulier de la sous-clé).

    Passons à son tour la DF {Four_No} → {Ville} à la moulinette. {Four_No} n’est pas surclé, la condition (a) n’est donc pas vérifiée. {Ville} n’est pas sous-clé, la condition (b) n’est donc pas vérifiée. {Four_No} est sous-clé, la condition (c) n’est donc pas vérifiée. Conclusion : à cause de cette DF qui ne satisfait à aucune des conditions, FPV viole la 2NF.

    Je ne vais repasser le film avec la 3NF, je préfère énoncer celle-ci, telle que nous la fournit C. J. Date :

    La relvar R est en troisième forme normale (3NF) si et seulement si pour chaque DF non triviale X → Y valant pour R, au moins une des conditions suivantes est vérifiée :

    (a) X est surclé
    (b) Y est sous-clé


    On observera que la définition de la 3NF est celle de la 2NF, sauf que la condition (c) en est absente. Il s’ensuit que la 3NF implique de facto la 2NF, qu’une relvar en 3NF est forcément en 2NF, et que l’on peut faire l’économie du sempiternel « R est en 3NF si elle est en 2NF et... ».

    Dans la foulée, allons-y pour la forme normale de Boyce Codd (BCNF) :

    La relvar R est en forme normale de Boyce Codd (BCNF) si et seulement si pour chaque DF non triviale X → Y valant pour R, X est une surclé.


    La définition de la BCNF est celle de la 3NF, sauf que la condition (b) en est absente. Il s’ensuit que la BCNF implique de facto la 3NF, qu’une relvar en BCNF est forcément en 3NF, et que l’on peut faire l’économie du sempiternel « R est en BCNF si elle est en 3NF et... ».

    On observera encore qu’avec la 2NF on a jusqu’à 3 possibilités de s’en sortir, seulement 2 d’entre elles dans le cas de la 3NF, et seulement une d’entre elles dans le cas de la BCNF.

    D’un point de vue heuristique, on a toutes ses chances de perdre moins de temps à vérifier la normalisation si on s’attaque directement à la BCNF.
    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