[MCD] Association réflexive [Résolu]

[twity]

bonsoir,
je veux modéliser la relation existante entre membre famille.(père, fils, mère, marie) est ce que je dois utiliser la notion de réflexive:un personne dont les caractéristiques sont: NSS,nom, prénom, date_naissance..peut être un père,fils..

[SQLpro]

Intrinsèquement oui. Mais vous oubliez la nature non permanente des données. Par exemple les mariages, divorces, remariages et enfantement entre temps.

En sus comment reliez vous les enfants naturels ?

Donc, pas si simple....

A +

[fsmrel]

Bonjour,

Envoyé par twity

je veux modéliser la relation existante entre membre famille.(père, fils, mère, marie) est ce que je dois utiliser la notion de réflexive:un personne dont les caractéristiques sont: NSS,nom, prénom, date_naissance..peut être un père,fils..

Au lieu d’accumuler les difficultés et de vous coller le moral dans les chaussettes, commençons au moins par ce qui est simple et naturel.

Considérez la représentation ci-dessous (Modèle Logique de Données) :



La table PERSONNE parle d’elle-même : chaque personne y figure, quelle qu’elle soit : prénom, nom patronymique, etc. En revanche dans la table FILIATION ne figurent que les enfants :

Un enfant y est représenté par l’attribut EnfantId, dont la valeur est une valeur de l’attribut PsnId de la table PERSONNE (contrainte référentielle).

Le papa de cet enfant y est représenté par l’attribut PapaId dont la valeur est une valeur de l’attribut PsnId de la table PERSONNE (contrainte référentielle).

La maman de cet enfant y est représenté par l’attribut MamamId dont la valeur est une valeur de l’attribut PsnId de la table PERSONNE (contrainte référentielle).


Exemple

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
PERSONNE     PsnId    PsnNom          PsnPrenom

                 1    Bach            Johann Ambrosius
                 2    Lämmerhirt      Maria Elisabetha
                 3    Bach            Johann Sebastian
                 4    Bach            Maria Barbara
                 5    Wilcke          Anna Magdalena 
                 6    Bach            Catharina Dorothea
                 7    Bach            Wilhelm Friedemann
                 8    Bach            Carl Philipp Emanuel
                 9    Bach            Gottfried Heinrich
                10    Bach            Johann Christoph Friedrich
                11    Bach            Elisabetha Juliana Friederica
                12    Altnikol        Johann 
                13    Altnikol        Johann
               ...    ...             ...

Code :

1
2
3
4
5
6
7
8
9
10
11
FILIATION   EnfantId    PapaId      MamanId

                   3         1            2
                   6         3            4
                   7         3            4
                   8         3            4
                   9         3            5
                  10         3            5
                  11         3            5
                  13        12           11
                 ...       ...          ...

Dans chaque table, l'attribut souligné dénote la clé primaire de celle-ci. Ainsi, un enfant est né de seulement un père et une mère (filiation naturellle et normale).

[fsmrel]

Supposons que vous vouliez traiter du cas des enfants nés dont le père ou la mère est inconnu. Le MLD peut évoluer ainsi :



On peut aussi distinguer la filiation légitime (prise en compte de la date du mariage) de celle qui ne l’est pas :



En collant les morceaux :



Pour les familles recomposées, etc., voyez les spécialistes...

[twity]

bonsoir,
si vous pouvez mettre MCD au lieu de LMD merci

[fsmrel]

Bonsoir twity,


Un peu de courage !

N'hésitez pas à proposer d'abord votre MCD, on verra à vous faire les observations d'usage à propos de sa justesse.

Pour vous aider à commencer, disons que chaque rôle "Est une personne" montre que FILIATION_LEGITIME, FILIATION_ILLEGITIME et FILIATION_MONO_PARENT (qu'il est préférable au niveau MCD de renommer respectivement en ENFANT_LEGITIME, ENFANT_ILLEGITIME et ENFANT_MONO_PARENT) sont en fait des spécialisations de la personne quand celle-ci est enfant : Jean-Sébastien Bach a bien été enfant avant de devenir papa à son tour. Ça sent l'héritage (au sens merisien du terme !)

[fsmrel]

Bonsoir twity,


Pas de MCD en vue ? Bon, voici un coup de pouce, mais n’abusons pas. Il s’agit seulement de la partie « Enfants légitimes » :



Sinon, en redescendant au niveau SQL, pour retrouver la descendance d’une personne :

Structure de la table des personnes :

Code SQL :

1
2
3
4
5
6
7
8
9
10
CREATE TABLE PERSONNE
(
        PsnId                 INT            NOT NULL
      , PsnNom                VARCHAR(64)    NOT NULL
      , PsnPrenom             VARCHAR(64)    NOT NULL
      , PsnSexe               CHAR(1)        NOT NULL
      , Commentaire           VARCHAR(64)    NOT NULL DEFAULT ''
    , CONSTRAINT PERSONNE_PK PRIMARY KEY (PsnId)
    , CONSTRAINT PERSONNE_CHK1 CHECK (PsnSexe IN ('H', 'F', '?'))
) ;

Structure de la table des couples mariés (j’ai évacué l’attribut CoupleId qui n’apporte pas grand-chose) :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE COUPLE
(
        EpouxId          INT            NOT NULL
      , EpouseId         INT            NOT NULL
      , AnneeMariage     VARCHAR(4)     NOT NULL DEFAULT '?'
      , Commentaire      VARCHAR(64)    NOT NULL DEFAULT ''
    , CONSTRAINT COUPLE_PK PRIMARY KEY (EpouxId, EpouseId)
    , CONSTRAINT COUPLE_FK1 FOREIGN KEY (EpouxId) REFERENCES PERSONNE
    , CONSTRAINT COUPLE_FK2 FOREIGN KEY (EpouseId) REFERENCES PERSONNE
    , CONSTRAINT COUPLE_CHK1 CHECK (ISNUMERIC(AnneeMariage) = 1 OR AnneeMariage = '?')
    , CONSTRAINT COUPLE_CHK2 CHECK (EpouxId <> EpouseId)
) ;

Table des enfants légitimes :

Code SQL :

1
2
3
4
5
6
7
8
CREATE TABLE FILIATION_LEGITIME
(
        EnfantId        INT            NOT NULL
      , PapaId          INT            NOT NULL
      , MamanId         INT            NOT NULL
    , CONSTRAINT FILIATION_LEGITIME_PK PRIMARY KEY (EnfantId)
    , CONSTRAINT FILIATION_LEGITIME_FK FOREIGN KEY (PapaId, MamanId) REFERENCES COUPLE
) ;

Recherche des descendants de la personne pour laquelle PsnId = 100 :

Code SQL :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE @PsnId AS INT ;
SET @PsnId = 100 ;
 
WITH W (EnfantId, PapaId) AS
(
    SELECT EnfantId, PapaId
    FROM   PERSONNE AS x JOIN FILIATION_LEGITIME AS y ON PsnId = PapaId 
    WHERE  PsnId = @PsnId
  UNION ALL
    SELECT y.EnfantId, y.PapaId 
    FROM   W AS x JOIN FILIATION_LEGITIME AS y ON x.EnfantId = y.PapaId 
)
SELECT W.EnfantId, y.PsnPrenom AS EnfantPrenom, y.PsnNom AS EnfantNom, z.PsnPrenom AS PapaPrenom, z.PsnNom AS PapaNom   
FROM   W JOIN PERSONNE AS y ON W.EnfantId = y.PsnId
         JOIN PERSONNE AS z ON W.PapaId = z.PsnId ;

A plus tard