Discussion :

[laowantong]

Bonjour,

Je voudrais faire suite à une question de StackOverflow (https://stackoverflow.com/questions/...y-relationship) qui présente l'ERD suivant :



Attention, les cardinalités ne sont pas placées en Look Here (comme en Merise), mais en Look Across (cf. p. ex. crow's foot notation). Pour les associations binaires, on passe de l'une à l'autre en permutant simplement les cardinalités. Pour les associations n-aires, la sémantique est tout autre. Ci-dessus, de gauche à droite et de haut en bas :

1 : pour un couple (employé, site) donné, 1 seul projet possible ;
N : pour un couple (projet, site) donné, plusieurs employés possibles ;
1 : pour un couple (projet, employé) donné, 1 seul site possible.

Avec Merise, les assertions seraient plutôt du type : « pour un x donné, 1 seul / plusieurs couples (y, z) possibles ». C'est donc les références au singleton et au n-1-uplet qui sont permutées.

Ma question porte sur les trois niveaux de la modélisation :

- conceptuel : comment représenteriez-vous cet ensemble de contraintes dans un MCD Merise ?
- logique : pour les colonnes de la table Affectation, on peut aussi bien avoir (employé, site, projet) que (employé, projet, site), mais dans les deux cas on fait l'impasse sur une dépendance fonctionnelle. Peut-on faire mieux ?
- physique : comment traduiriez-vous ces contraintes lors de la création des tables ?

[escartefigue]

Vraisemblablement, les règles de gestion sont incomplètes

Je suppose qu'un employé est affecté à un projet pour une période, il peut ensuite changer de projet, un seul projet à un instant "t" pour un même employé
De la même façon, un employé est certainement affecté à un site pour une période, puis peut ensuite changer de site, un seul site à un instant "t" pour un même employé

Ce qui donne le MCD suivant selon le formalisme Merise :



Notez les flèches vers [PROJET] et [SITE] qui matérialisent les contraintes d'unicité
Notez également l'entité-type [CALENDRIER] mise entre parenthèses, ça signifie qu'elle ne deviendra pas une table, elle n'est là que pour faire participer la date à la PK des tables issues des assos ternaires


Le MLD correspondant est le suivant :




Et le DDL ici décliné pour SQL Server est le suivant :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE TABLE PRO_projet(
   PRO_ident INT IDENTITY,
   PRO_code CHAR(8) NOT NULL,
   PRO_libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(PRO_ident),
   UNIQUE(PRO_code)
);
 
CREATE TABLE PER_personne(
   PER_ident INT IDENTITY,
   PER_nom VARCHAR(50) NOT NULL,
   PER_prenom VARCHAR(50) NOT NULL,
   PER_NIR CHAR(13) NOT NULL,
   PRIMARY KEY(PER_ident)
);
 
CREATE TABLE SIT_site(
   SIT_ident INT IDENTITY,
   SIT_libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(SIT_ident)
);
 
CREATE TABLE AFF_affecter(
   PER_ident INT,
   CAL_date DATE,
   AFF_dtfin DATE NOT NULL,
   PRO_ident INT NOT NULL,
   PRIMARY KEY(PER_ident, CAL_date),
   FOREIGN KEY(PER_ident) REFERENCES PER_personne(PER_ident),
   FOREIGN KEY(PRO_ident) REFERENCES PRO_projet(PRO_ident)
);
 
CREATE TABLE LOC_localiser(
   PER_ident INT,
   CAL_date DATE,
   LOC_dtfin DATE NOT NULL,
   SIT_ident INT NOT NULL,
   PRIMARY KEY(PER_ident, CAL_date),
   FOREIGN KEY(PER_ident) REFERENCES PER_personne(PER_ident),
   FOREIGN KEY(SIT_ident) REFERENCES SIT_site(SIT_ident)
);

Gràce aux contraintes matérialisées par les flêches du MCD, les attributs correspondants ont été évacués des PK des tables associatives pour garantir l'unicité du site pour un coupe (personne, date) et l'unicité du projet pour un couple (personne, date)

[laowantong]

Merci beaucoup pour cette réponse rapide, précise et détaillée.

Cependant, votre modélisation me semble autoriser la vue en extension suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Affectations

Alice | lundi | projet 1 | lundi
Alice | mardi | projet 1 | mardi

Localisations

Alice | lundi | Paris | lundi
Alice | mardi | Lyon | mardi

En d'autres termes, Alice travaillerait sur le même projet lundi et mardi, mais sur des sites différents. Or, d'après l'énoncé : Each employee assigned to a project works at only one location for that project.

J'ai l'impression qu'il y a quelque chose que je n'ai pas compris, soit dans les règles de gestion originales, soit dans votre proposition...

[escartefigue]

En effet, mea culpa, je n'ai pas tenu compte de cette contrainte.
Mais, l'énoncé est incomplet, car on ne sait pas si à un instant "t", un employé peut travailler sur plusieurs projets. Qu'en est-il ?

[laowantong]

Désolé, je viens seulement de voir votre message.

J'aurais tendance à penser qu'à un instant t, un employé n'est pas censé travailler sur deux projets à la fois, mais en effet l'énoncé ne dit rien là-dessus. Si une modélisation en LH répondant aux règles de gestion explicites devait trancher cette question, à mon avis on ne pourrait pas lui reprocher de le faire par oui ou par non. Mais le fait que la modélisation en LA n'oblige pas à préciser de règle supplémentaire indiquerait alors que les domaines couverts par l'une et l'autre convention ne coïncident pas exactement, ce qui serait intéressant en soi.

D'après Paré, Rafael Camps. “Transforming N-ary relationships to database schemas: an old and forgotten problem.” (2002) :

LA style (Chen style) is more powerful than LH style (Merise style). All the possible FD patterns for ternary relationships can be obtained using the LA style: one LA ternary relationship plus some additional imposed binary FDs that can be seen as imposed LA binary relationships. But this would be not true for LH style.

Il est bien possible qu'on soit dans ce cas-là.

[Paprick]

Bonsoir,

Je n'ai peu de le temps de me pencher sur votre problème, mais je peux affirmer sans le moindre doute que tout SI peut être modélisé avec un schéma E/A (Merise).
Je laisse escartefigue poursuivre sa modélisation... je viendrai à la rescousse si nécessaire, mais je doute que ce soit nécessaire !

[laowantong]

Du nouveau, et un élément de réponse.

J'ai trouvé par hasard la source de l'énoncé : Toby J. Teorey, Sam S. Lightstone, Tom Nadeau, H.V. Jagadish, Database Modeling and Design - Logical Design, 5th Edition - February 10, 2011 (Elsevier). Le modèle conceptuel est donné p. 29 et sa traduction en SQL p. 98. La table qui nous intéresse est :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
create table assigned_to (
    emp_id char(10),
    project_name char(20),
    loc_name char(15) not null,
    primary key (emp_id, project_name),
    foreign key (emp_id) references EMPLOYEE on delete cascade on update cascade,
    foreign key (project_name) references PROJECT on delete cascade on update cascade,
    foreign key (loc_name) references LOCATION on delete cascade on update cascade,
    unique (emp_id, loc_name)
)

Rien de fou-fou donc : l'une des deux clés composites candidates a été choisie comme primaire, et l'autre pourvue d'une contrainte d'unicité. Cela répond à ma 3e question... Et facilite peut-être la reconstruction des modèles relationnel et conceptuel à la Merise ?

[laowantong]

Encore moi. Après quelque cogitation, je crois pouvoir me répondre ainsi :



(Dans le schéma relationnel, les u1 en exposant dénotent la contrainte d'unicité n°1.)

----

Si c'est correct et optimal, force est de reconnaître que la notation LA est beaucoup plus économique : 4 nœuds au lieu de 6 / 3 arêtes au lieu de 11 / 3 types d'arêtes au lieu de 1... En est-elle moins claire ? Quant à moi, je ne puis me défendre de l'impression que les associations ternaires représentent pour Merise une occasion manquée, consécutive au choix d'une notation LH. L'introduction de cet élément dans le vocabulaire graphique est sous-exploité : des 4 combinaisons de cardinalités maximales (111, 11N, 1NN et NNN), les trois premières sont honnies, seule la dernière est tolérée (et encore !). En notation LA, chacune a sa raison d'être et sa propre sémantique (cf. le bouquin cité dans mon message précédent). La dernière (NNN) coïncide heureusement avec LH, mais ça s'arrête là. Dommage...

Je sais que je suis au royaume de Merise, mais ne sortez pas les fourches : c'est juste mon impression... jusqu'à plus ample informé !

[escartefigue]

Ce schéma autorise un employé à travailler sur plusieurs projets simultanément, ce qui semble conforme à l'énoncé, bien que ce ne soit pas clairement dit, mais semble implicitement découler de la règle pour un couple (employé, site) donné, 1 seul projet possible

[Paprick]

Bonjour,

Voici un sujet très intéressant !
Tout d'abord une petite question : quel outil utilisez-vous pour réaliser le MCD avec les 2 CIF, et obtenir cette notation avec les u1 en exposant dans le MLD ?
Ensuite, concernant la lourdeur graphique relative aux CIF, je suis bien d'accord et j'ai récemment validé pour Looping un principe (suggéré par fsmrel) de ne pas relier graphiquement les entités émettrices en cas de CIF à unicité complète.
Voici ce que cela donne :

C'est de suite beaucoup plus clair ! La génération correcte MLD et DDL sera en place dans la prochaine version 4.1 de Looping.

Par ailleurs, pouvez-vous m'indiquer comment se comporte la notation LA en cas de CIF à unicité incomplète ?
J'avoue ne pas bien voir comment faire...

[laowantong]

Je suis utilisateur (et auteur) de Mocodo (https://www.mocodo.net), un logiciel défavorablement connu sur ce forum, mais qui me rend bien service dans mon modeste cours d'introduction aux BD.
Oui, il prend vaguement en charge les CIF : https://rawgit.com/laowantong/mocodo...solution-:-CIF
La notation des clés candidates est jusqu'ici dans une branche de développement, cf. https://github.com/laowantong/mocodo/issues/79
J'espère sortir la prochaine version majeure en septembre. Suite à la présente discussion, je viens de me rajouter la gestion complète des associations ternaires équivalentes aux (NNN, 1NN, 11N, 111) de la notation LA. Jusqu'ici je ne traite moi-même correctement que les deux premiers cas.

Par ailleurs, pouvez-vous m'indiquer comment se comporte la notation LA e[/COLOR][COLOR=#3E3E3E]n cas de CIF à unicité incomplète ?
J'avoue ne pas bien voir comment faire...

Et moi je dois avouer que je ne vois pas du tout ce que c'est ! Je suis loin d'être spécialiste des BD, et n'en sais en fait guère plus que ce que je peux raconter en 30 heures à mes infortunés étudiants.

PS: Bonne idée pour la représentation simplifiée des CIF. Je me rends compte qu'on peut déjà faire ça avec Mocodo, puisqu'on énumère simplement les liens qu'on veut, sans aucune sémantique derrière.

[Paprick]

Je connais Modoco (je n'ai reconnu les modèles qu'après avoir envoyé le message !), et je trouve que c'est un outil très intéressant pédagogiquement.
Le fait que le graphique soit le résultat avec une source textuelle doit effectivement décourager un peu les concepteurs, mais les modèles obtenus infine sont vraiment très bien.
Pour préciser cette histoire d'unicité incomplète, c'est lorsque une ou plusieurs classes d'entités sont reliées à l'association et ne participe pas à la CIF (ni émetteur, ni cible).
Voici un exemple bien connu :

Est-ce modélisable en notation LA ?

Quoiqu'il en soit, pourriez-vous le reproduire avec Modoco et me donner le MLD et le DDL qui en ressort ?
C'est un aspect de la modélisation conceptuelle qui m'intéresse beaucoup ; Merci !

[laowantong]

Oui, j'ai bien conscience que les gens veulent du WYSIWYG. Les logiciels qui exigent l'apprentissage d'un DSL resteront toujours confidentiels. Ceci dit, il y a pas mal de choses qui se passent en ce moment dans cette mouvance, et même un vrai travail de fond : p. ex. https://dbml.dbdiagram.io/home/ ou https://kroki.io.

Votre MCD me donne des sueurs froides, ou est-ce un flashback de mes folles années de code spaghetti ? Je vous avoue que je n'ai pas le courage d'aller au-delà pour le moment. Le concept en question nécessite-t-il vraiment une association quadruple et trois CIF, ou pourriez-vous en produire une version minimale ?

En tout cas, concernant Mocodo, je peux vous dire à l'avance qu'il ignorera toutes les CIF : il ne gère actuellement que l'aspect graphique (j'ai introduit les contraintes juste pour dépanner quelques mécontents qui lançaient un logiciel de dessin pour « compléter » la sortie de Mocodo).

[Paprick]

Votre MCD me donne des sueurs froides, ou est-ce un flashback de mes folles années de code spaghetti ? Je vous avoue que je n'ai pas le courage d'aller au-delà pour le moment. Le concept en question nécessite-t-il vraiment une association quadruple et trois CIF, ou pourriez-vous en produire une version minimale ?

C'est le cas classique du jockey qui participe à une course sur un cheval avec un numéro de dossard ! Mais bon, ces associations n-aires restent assez anecdotiques.

En tout cas, concernant Mocodo, je peux vous dire à l'avance qu'il ignorera toutes les CIF : il ne gère actuellement que l'aspect graphique (j'ai introduit les contraintes juste pour dépanner quelques mécontents qui lançaient un logiciel de dessin pour « compléter » la sortie de Mocodo).

Pourtant, dans votre exemple, votre MLD est tout à fait correct et prend en compte les CIF... Ce n'est pas Modoco qui a généré ce MLD (et le DDL qui va avec) ?

[laowantong]

D'accord, pour la course hippique, je regarderai un peu quand j'aurai plus de temps, et reviendrai vers vous si j'ai quelque chose d'intéressant à dire.

Vous l'aurez compris, mon but n'était pas de faire une démonstration de mon logiciel, mais de proposer une solution au problème de modélisation que j'avais soulevé initialement. En l'occurrence, le MLD actuellement généré par Mocodo dans un cas comme celui-là est erroné. J'ai donc dû retoucher à la main la sortie pour modifier les soulignements et ajouter les contraintes d'unicité (qui jusqu'ici ne sont gérés que dans les tables issues d'entités).

Je viens justement d'ouvrir une issue avec mon projet de correction : https://github.com/laowantong/mocodo/issues/88. Cela peut vous intéresser, dans la mesure où je reprends les quatre exemples de l'ouvrage cité plus haut, et montre comment les traduire en relationnel. Ça me servira de référence en tout cas quand je m'attaquerai à cette partie du code.

[escartefigue]

Une autre façon de modéliser ce cas, est de gérer la notion d'équipe projet.

Le MCD devient :



Et le MLD :



La personne n'étant à un instant "t" affectée qu'à une seule équipe, elle est par transitivité affectée à un seul site

[laowantong]

J'ai l'impression que votre modélisation autoriserait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
AFFECTER
lundi Alice projet_1 Paris
mardi Alice projet_2 Paris

ÉQUIPE
projet_1 Paris équipe_A
projet_2 Paris équipe_B

Dans ce cas, la règle « pour un couple (employé, site) donné, 1 seul projet possible » serait enfreinte.

[escartefigue]

En effet, mais j'ai pensé qu'une affectation ne devait être unique qu'à un instant "t", que tout employé pouvait changer d'affectation.
C'est le cas le plus souvent, pour ne pas dire toujours

Sur mon modèle, il faudra veiller à poser une contrainte check pour vérifier que la date de fin d'affectation est supérieure à la date de début d'affectation et mettre en place un contrôle (par trigger par exemple) pour vérifier les éventuels chevauchements, sinon la règle évoquée serait en effet violée.

Edit : ce modèle répond aussi à la supposition que nous avons partagée, à savoir :

J'aurais tendance à penser qu'à un instant t, un employé n'est pas censé travailler sur deux projets à la fois, mais en effet l'énoncé ne dit rien là-dessus.

Si toutefois cette règle n'était pas applicable, alors ce modèle ne convient évidemment pas !

[laowantong]

Je crois que cet exemple a été choisi juste pour rendre plus intuitif un problème complètement général. Résoudre le problème-métier induit a sans doute un intérêt en soi, et j'apprécie vos efforts pour lui donner plus de réalisme, mais pour moi l'énoncé pourrait tout aussi bien se formuler sur :



Bien que j'aie fini par trouver une solution indépendante de la sémantique de surface, j'avoue que je me pose encore une question : peut-on modéliser cette association 11N sans CIF, mais avec des entités faibles, comme on peut le faire avec une association 1NN ?

Mon but, vous l'aurez compris, n'est pas de faire du conseil d'entreprise, mais de savoir jusqu'où je peux aller dans l'automatisation du traitement de telles structures.

[Paprick]

Bonsoir,

Voici un MCD qui répond à la demande : la flèche indique la CIF et définit la clé primaire ; u1 vient définir la clé alternative :

On retrouve alors le MLD défini plus haut ; l'avantage par rapport aux autres représentations, c'est la possibilité de définir, dès le MCD, quelle clé candidate sera clé primaire.