Relation identifiée/non identifiée

Version imprimable

29/07/2016, 15h45
Mathaousse

1 pièce(s) jointe(s)

Relation identifiée/non identifiée

Bonjour,

J'aimerai avoir des explications sur les relations identifiiés/non-identifié dans MYSql Workbench.
J'ai fait un exemple très rapide

Pièce jointe 216762

Ce que je ne comprends pas se situe par exemple au niveau du groupe Si je mets une relation identifiée entre la table GROUPE et la table UTILISATEUR j'ai une clef composite GroupeId et UTILISATEUR_UtilisateurId. Or il me semble que même si je veux savoir qui a créé le groupe, GoupeID se suffit à lui-même pour la clé primaire. Donc j'enlève le PK sur la UTILISATEUR_UtilisateurId (en le laissant NN) dans workbench, la relation devient non-identifié. Mais si je supprime l'utilisateur qui a créé le groupe il faut que le groupe vire aussi.
Et lisant çà : http://alexandre.clain.info/mysql-re...on-identifiee/ par exemple ou d'autres discussion relative à ce sujet sur ce forum je ne suis pas sûr que ma définition de l'identifié/non identifié soit bonne.

Merci de vos éclairages.
29/07/2016, 16h09
escartefigue

Bonjour,

L'exemple dont vous avez donné le lien est très mauvais et il embrouille les esprits !

Le premier souci est que my-sql workbench ne se situe pas vraiment au niveau conceptuel, or, l'identification relative est de niveau conceptuel.

L'identification relative est nécessaire quand l'entité-type concernée n'a pas d'identifiant propre suffisant.
L'exemple typique est l'entité-type ligne de facture qui n'a de sens que relativement à la facture à laquelle elle se rapporte.

FACTURE 1,n --- comporter --- 1,1 LIGNE

Le n° de ligne de facture seul ne permet pas de savoir quelle facture est concernée, on utilise donc l'identification relative pour que, lors de la génération du MPD à partir du MCD, l'identifiant de l'entité-type facture soit dupliqué dans la table issue de l'entité-type ligne de facture

Dans ce contexte l'entité-type "facture" est appelée entité forte, et l'entité-type "ligne de facture" est appelée entité faible
Certains outils de modélisation mettent des parenthèses autour des cardinalités, pour mettre en évidence l'identification relative

Pour revenir à votre cas, l'utilisateur et le groupe ont leur existence propre, vous n'avez donc pas à utiliser d'identification relative
29/07/2016, 16h16
escartefigue

J'ajoute

Un lien vers une explication très claire (comme il en a l'habitude) de Fsmrel sur l'identification relative ici

Le besoin de suppression d'un groupe si l'utilisateur qui l'a créé est supprimé n'a pas de lien direct avec l'identification relative. Il faut pour cela ajouter une contrainte dans la table groupe sur la FK utilisateur_ID de la table utilisateur avec l'instruction "on delete cascade" ;)

Salut à tous.

Lorsque nous avons deux entités, l'une fille et l'autre mère, la relation va se traduire par une clef étrangère sous MySql.

Mais qu'est-ce que "relation identifiée" ? ou en anglais "identifying relationship" ?

Une relation identifiée est une ligne de l'entité fille qui dépend d'une ligne de l'entité mère.
Comment cela va se traduire avec la clef étrangère ?

La clef étrangère de l'entité fille va pointer sur l'identifiant (la primary key) de l'entité mère.
Et cette clef étrangère, dans l'entité fille, ne va pas admettre le marqueur NULL.
Cela se traduit en mettant la clef étrangère comme clef composite de la clef primaire de l'entité fille.
En d'autre terme, la valeur de la clef étrangère de l'entité fille est toujours connue dans l'entité mère (pas de NULL).

Et qu'est-ce qu'une "relation non identifiée" ? ou en anglais "" ?

Une relation non identifiée se caractérise par le fait que la ligne de l'entité fille ne pointe pas systématiquement sur une ligne de l'entité mère.
Cela se traduit par la présence du marqueur NULL.

Autrement dit, la clef étrangère n'appartient pas comme clef composite de la clef primaire de l'entité fille.
En d'autre terme, la valeur de la clef étrangère de l'entité fille n'est pas systématiquement connue dans l'entité mère (possibilité d'avoir un NULL).

En résumé, si le marqueur NULL est interdit, c'est une relation identifiée et dans le cas contraire, c'est une relation non identifiée.

Citation:

Envoyé par Mathaousse

Ce que je ne comprends pas se situe par exemple au niveau du groupe Si je mets une relation identifiée entre la table GROUPE et la table UTILISATEUR j'ai une clef composite GroupeId et UTILISATEUR_UtilisateurId.

Dans la table groupe, tout ce dont vous avez besoin, c'est de la clef primaire, de nom "GroupId".

Dans la table utilisateur, vous devez créer une clef primaire comprenant :
--> un identifiant unique sur la colonne "utilisateurId".
--> la clef étrangère de nom "GroupId" qui va pointer sur la clef primaire "groupeID" de la table group."

De ce fait, l'entité de nom "utilisateur_dans_groupe" n'a aucune raison d'être !

Citation:

Envoyé par Mathaousse

Or il me semble que même si je veux savoir qui a créé le groupe, GoupeID se suffit à lui-même pour la clé primaire.

En fait, vous avez deux clefs étrangères, l'une qui définie la relation groupe vers utilisateur dans le sens de l'appartenance.
Puis une autre clef étrangère, définissant la relation entre utilisateur et groupe, dans le sens du créateur du groupe.

Citation:

Envoyé par Mathaousse

Donc j'enlève le PK sur la UTILISATEUR_UtilisateurId (en le laissant NN) dans workbench, la relation devient non-identifié.

Seule la clef étrangère entre la table groupe (entité mère) et la table utilisateur (entité fille) à un sens sous MySql.
C'est une relation de type hiérarchique qui est ainsi définie.
Si tu utilises aussi une clef étrangère dans le sens inverse, donc de la table utilisateur (entité mère) vers ma table groupe (entité fille), tu vas créer un dead lock.
MySql ne sait pas faire cela. Inversement Microsoft SQL Server sait gérer cela !

Et comme je vous l'ai indiqué ci-dessus, elle sera bien une relation identifiée.

Citation:

Envoyé par Mathaousse

Mais si je supprime l'utilisateur qui a créé le groupe il faut que le groupe vire aussi.

Non, cela ne peut pas se faire ainsi sous MySql.

Pour supprimer une ligne de la table utilisateur, vous devez au préalable supprimer toutes les lignes de la table group_user qui sont en relation.
Il existe une autre solution qui consiste à définir la clef étrangère de cette façon (dans la table group_user) :

Code:

  CONSTRAINT `FK_GROUP_USER_1` FOREIGN KEY (`id_user`)  REFERENCES `utilisateur` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

Ainsi en supprimant une ligne de la table utilisateur, toutes les lignes en relation seront aussi supprimées.

Voici mon exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `groupe`
--------------
 
--------------
CREATE TABLE `groupe`
( `id`          integer unsigned NOT NULL auto_increment primary key,
  `nom_group`   varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `groupe` (`nom_group`) values
  ('administrateur'),('modérateur'),('rédacteur'),('membre')
--------------
 
--------------
select * from groupe
--------------
 
+----+----------------+
| id | nom_group      |
+----+----------------+
|  1 | administrateur |
|  2 | modérateur     |
|  3 | rédacteur      |
|  4 | membre         |
+----+----------------+
--------------
commit
--------------
 
--------------
DROP TABLE IF EXISTS `utilisateur`
--------------
 
--------------
CREATE TABLE `utilisateur`
( `id`        integer unsigned NOT NULL auto_increment primary key,
  `nom_user`  varchar(255)     NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `utilisateur` (`nom_user`) values
  ('Admin'),('Mathaousse'),('Escartefigue'),('Artemus')
--------------
 
--------------
select * from utilisateur
--------------
 
+----+--------------+
| id | nom_user     |
+----+--------------+
|  1 | Admin        |
|  2 | Mathaousse   |
|  3 | Escartefigue |
|  4 | Artemus      |
+----+--------------+
--------------
commit
--------------
 
--------------
DROP TABLE IF EXISTS `group_user`
--------------
 
--------------
CREATE TABLE `group_user`
( `id_user`   integer unsigned NOT NULL,
  `id_group`  integer unsigned NOT NULL,
  CONSTRAINT `FK_GROUP_USER_1` FOREIGN KEY (`id_user`)  REFERENCES `utilisateur` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_GROUP_USER_2` FOREIGN KEY (`id_group`) REFERENCES `groupe`      (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  primary key (`id_user`,`id_group`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `group_user` (`id_user`,`id_group`) values
  (1,1),(1,2),(1,3),(1,4),(2,3),(2,4),(3,2),(3,4),(4,2),(4,4)
--------------
 
--------------
select * from group_user
--------------
 
+---------+----------+
| id_user | id_group |
+---------+----------+
|       1 |        1 |
|       1 |        2 |
|       3 |        2 |
|       4 |        2 |
|       1 |        3 |
|       2 |        3 |
|       1 |        4 |
|       2 |        4 |
|       3 |        4 |
|       4 |        4 |
+---------+----------+
--------------
commit
--------------
 
--------------
select t3.nom_user as utilisateur,
       group_concat(t2.nom_group order by t2.id) as groupe
 
from       group_user  as t1
 
inner join groupe      as t2
on t2.id = t1.id_group
 
inner join utilisateur as t3
on t3.id = t1.id_user
 
group by t3.nom_user
order by t3.nom_user
--------------
 
+--------------+--------------------------------------------+
| utilisateur  | groupe                                     |
+--------------+--------------------------------------------+
| Admin        | administrateur,modérateur,rédacteur,membre |
| Artemus      | modérateur,membre                          |
| Escartefigue | modérateur,membre                          |
| Mathaousse   | rédacteur,membre                           |
+--------------+--------------------------------------------+
--------------
DROP TABLE IF EXISTS `message`
--------------
 
--------------
CREATE TABLE `message`
( `id`               integer unsigned NOT NULL AUTO_INCREMENT primary key,
  `id_emetteur`      integer unsigned NOT NULL,
  `id_destinataire`  integer unsigned NOT NULL,
  `datime`           datetime         NOT NULL,
  `text`             varchar(255)     NOT NULL,
  CONSTRAINT `FK_MESSAGE_1` FOREIGN KEY (`id_emetteur`)     REFERENCES `utilisateur` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_MESSAGE_2` FOREIGN KEY (`id_destinataire`) REFERENCES `utilisateur` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `message` (`id_emetteur`,`id_destinataire`, `datime`, `text`) values
  (1, 2, '2016-07-30 20:54:33', 'bienvenue ''Mathaousse''.'),
  (1, 3, '2016-07-30 21:05:17', 'bienvenue ''Escartefigue''.'),
  (3, 2, '2016-07-30 22:01:01', 'J''ai répondu à ta question'),
  (2, 3, '2016-07-30 22:25:45', 'merci beaucoup !'),
  (1, 4, '2016-07-30 23:23:23', 'bienvenue ''Artemus''.'),
  (4, 2, '2016-07-30 23:47:11', 'J''espère que mon exemple est assez clair !'),
  (2, 4, '2016-07-30 23:49:25', 'Je vais étudier cela de plus près.')
--------------
 
--------------
select * from message
--------------
 
+----+-------------+-----------------+---------------------+--------------------------------------------+
| id | id_emetteur | id_destinataire | datime              | text                                       |
+----+-------------+-----------------+---------------------+--------------------------------------------+
|  1 |           1 |               2 | 2016-07-30 20:54:33 | bienvenue 'Mathaousse'.                    |
|  2 |           1 |               3 | 2016-07-30 21:05:17 | bienvenue 'Escartefigue'.                  |
|  3 |           3 |               2 | 2016-07-30 22:01:01 | J'ai répondu à ta question                 |
|  4 |           2 |               3 | 2016-07-30 22:25:45 | merci beaucoup !                           |
|  5 |           1 |               4 | 2016-07-30 23:23:23 | bienvenue 'Artemus'.                       |
|  6 |           4 |               2 | 2016-07-30 23:47:11 | J'espère que mon exemple est assez clair ! |
|  7 |           2 |               4 | 2016-07-30 23:49:25 | Je vais étudier cela de plus près.         |
+----+-------------+-----------------+---------------------+--------------------------------------------+
--------------
commit
--------------
 
--------------
select t1.id        as identifiant_message ,
       group_concat(t5.nom_group order by t5.id) as group_emetteur,
       t2.nom_user  as user_emetteur,
       t3.nom_user  as user_destinataire,
       t1.datime    as date_message,
       t1.text      as message
 
from       message     as t1
 
inner join utilisateur as t2
on t2.id = t1.id_emetteur
 
inner join utilisateur as t3
on t3.id = t1.id_destinataire
 
inner join group_user  as t4
on t4.id_user = t1.id_emetteur
 
inner join groupe      as t5
on t5.id = t4.id_group
 
group by t1.id, t2.nom_user, t3.nom_user, t1.datime
order by t1.id, t2.nom_user, t3.nom_user, t1.datime
--------------
 
+---------------------+--------------------------------------------+---------------+-------------------+---------------------+--------------------------------------------+
| identifiant_message | group_emetteur                             | user_emetteur | user_destinataire | date_message        | message                                    |
+---------------------+--------------------------------------------+---------------+-------------------+---------------------+--------------------------------------------+
|                   1 | administrateur,modérateur,rédacteur,membre | Admin         | Mathaousse        | 2016-07-30 20:54:33 | bienvenue 'Mathaousse'.                    |
|                   2 | administrateur,modérateur,rédacteur,membre | Admin         | Escartefigue      | 2016-07-30 21:05:17 | bienvenue 'Escartefigue'.                  |
|                   3 | modérateur,membre                          | Escartefigue  | Mathaousse        | 2016-07-30 22:01:01 | J'ai répondu à ta question                 |
|                   4 | rédacteur,membre                           | Mathaousse    | Escartefigue      | 2016-07-30 22:25:45 | merci beaucoup !                           |
|                   5 | administrateur,modérateur,rédacteur,membre | Admin         | Artemus           | 2016-07-30 23:23:23 | bienvenue 'Artemus'.                       |
|                   6 | modérateur,membre                          | Artemus       | Mathaousse        | 2016-07-30 23:47:11 | J'espère que mon exemple est assez clair ! |
|                   7 | rédacteur,membre                           | Mathaousse    | Artemus           | 2016-07-30 23:49:25 | Je vais étudier cela de plus près.         |
+---------------------+--------------------------------------------+---------------+-------------------+---------------------+--------------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
 
Appuyez sur une touche pour continuer...

La table group_user est une table associative car j'ai considéré qu'un utilisateur pouvait appartenir à plusieurs groupes à la fois.

Pour la table des messages, elle pointe sur la table des utilisateurs, soit en tant qu’émetteur du message, soit en tant que destinataire de ce message.
S'il y a plusieurs destinataires de ce message, cette structure ne convient pas.
Il faudra en plus de la table des messages, faire aussi une table associative entre l’émetteur, les destinataires et l'identifiant du message.

@+

31/07/2016, 18h25
Mathaousse

Merci,

Quelques petites explications sur vos indications :

Citation:

Envoyé par Artemus24

Une relation non identifiée se caractérise par le fait que la ligne de l'entité fille ne pointe pas systématiquement sur une ligne de l'entité mère.
Cela se traduit par la présence du marqueur NULL.
@+

Dans MYSql Workbench on a la possibilité d'avoir une référence à la ligne mère qui n'entre pas dans la composition de la clé primaire et où la valeur "Null" est interdite (NN)

Citation:

Envoyé par Artemus24

De ce fait, l'entité de nom "utilisateur_dans_groupe" n'a aucune raison d'être !
@+

Si un utilisateur peut appartenir a plusieurs groupes ? Cela revient à faire votre groupe_user?

Citation:

Envoyé par Artemus24

En fait, vous avez deux clefs étrangères, l'une qui définie la relation groupe vers utilisateur dans le sens de l'appartenance.
Puis une autre clef étrangère, définissant la relation entre utilisateur et groupe, dans le sens du créateur du groupe.
@+

Surement bête comme question mais je ne comprends pas. Dans quelle table se trouve ces clés?
31/07/2016, 21h11
Artemus24

Salut Mathaousse.

Citation:

Envoyé par Mathaousse

Dans MYSql Workbench on a la possibilité d'avoir une référence à la ligne mère qui n'entre pas dans la composition de la clé primaire et où la valeur "Null" est interdite (NN)

On peut très bien interdire le marqueur NULL dans une clef étrangère, sans que cette clef étrangère soit dans la clef primaire de la table fille.
Il suffit de mettre "NOT NULL" sur la déclarative de la colonne. Mais ce n'est pas cela qui va rendre la relation identifiée.

J'ai l'impression que même avec nos explications, vous ne comprenez pas bien ce que représente une "relation identifiée. Je fais essayer d'être plus explicite.

Vous avez une double contrainte !

1) il y a une relation de type hiérarchique entre la table fille et la table mère.
Pour résoudre ce problème, il est nécessaire d'utiliser une clef étrangère.
Comme la clef étrangère admet le marqueur NULL, le fait de mettre "NOT NULL" sur cette colonne est nécessaire mais pas suffisant.
Si le marqueur NULL est utilisée dans la clef étrangère, votre relation n'est pas identifiée. Cette condition est discriminatoire.

2) vous n'avez pas d'autres clefs candidates et vous devez construire votre clef primaire afin de rendre unique les lignes de votre table fille.
La solution va consister à créer votre clef primaire, en associant plusieurs colonnes.
Il y aura nécessairement votre clef étrangère. Simplement le fait de la mettre dans la clef primaire, va interdire automatiquement le marqueur NULL.
Et l'autre colonne sera par exemple de type numérique afin de distinguer les lignes ayant la même clef étrangère.

L'exemple d'Escartefigue sur les lignes de commandes illustre ce que représente une relation identifiée.
La clef primaire, est donc composé :
--> de la clef étrangère qui va pointer sur la table mère donnant le descriptif de la facture.
--> une simple numérotation "1 2, 3, ..." pour distinguer les lignes de détail de cette facture. Cela sert aussi à numéroter les lignes dans l'ordre d'apparition sur la facture.
Cette clef primaire, qui est une clef composite (plus de une colonne) va rendre vos lignes unique dans la table fille.

C'est l'exemple de ce que j'ai fait dans la table "group_user".
Il y a deux clefs étrangères, l'une vers la table "groupe" et l'autre vers la table "utilisateur".
Le couple (groupe ; utilisateur) doit être unique et identifiée, d'où les clefs étrangères.

Citation:

Envoyé par Mathaousse

Si un utilisateur peut appartenir a plusieurs groupes ? Cela revient à faire votre group_user?

Ma table "group_user" est une table association pour résoudre le fait qu'un utilisateur peut appartenir à plusieurs groupes.
Toutes les informations concernant l'utilisateur sont stockées dans la table utilisateur.
La table "group_user" n'est porteur d'aucune autres informations.

Citation:

Envoyé par Mathaousse

Surement bête comme question mais je ne comprends pas. Dans quelle table se trouve ces clés?

Dans aucune table de mon exemple.
Je vous ai dit que pour résoudre votre problème, vous deviez utiliser deux clefs étrangères, l'une allant de la table fille vers la table mère (ça, mysql sait le faire) et l'autre de la table mère vers la table fille (ça, mysql ne sait pas faire car cela va créer un dead lock).
A cause de ce Dead Lock (le verrou mortel), vous ne seriez plus dans la capacité d'introduire une ligne dans la table fille ou mère.

Ceci est possible avec Microsoft SQL Server. Escartefigue pourra vous donner une exemple de ce genre de problème.
L'exemple qui me vient à l'esprit est celle du chef de classe qui est référencé dans la table classe.
Or la table "classe" est la table mère, et la table "élève" est la table fille.
Le chef de classe doit obligatoirement est pris comme élève de la classe concernée.

Je tiens à signaler que ce forum est consacré à des problèmes techniques de MySQl.
Vos questions concernent des problèmes de modélisation qui sont du domaine de la fonctionnelle, voire de Merise.

@+
04/08/2016, 08h57
Mathaousse

Merci de votre aide.

Effectivement le sujet n'est pas posté au bon endroit. Je ne suis pas sûr que j'ai la possibilité d'y remédier.