Discussion :

[Menoto]

Bonjour,

j'aurais besoin de vos remarques et commentaires pour un MCD que j'ai réalisé.

Je l'ai attaché à ce post.

Merci à tous.

[Le Pharaon]

Bonjour,
j'aurais besoin de vos remarques et commentaires pour un MCD que j'ai réalisé.

Difficile (pour ne pas dire impossible) de commenter un MCD sans les règles de gestion qui le régissent

[fsmrel]

Du strict point de vue de la modélisation conceptuelle, ce que vous proposez est valable.
Cela dit, il faut essayer de voir plus loin, c'est-à-dire entre autres choses :
- respecter la conformité au modèle relationnel,
- avoir le souci de la performance, des traitements, disons batch, pour lesquels vous engagez la Production informatique pour plusieurs années.

Conformité au Modèle relationnel :
Suite à la dérivation des entités et relations sous forme de tables, vous allez obtenir une table TELEPHONES ayant une clé étrangère référençant la table SOCIETES et pouvant prendre la valeur nulle. En l’occurrence, NULL signifie MISSING AND INAPPLICABLE selon RM/V2 (Relational Model Version 2) et de facto vous violerez l’intégrité d’entité («No component of a foreign key is allowed to have an I-Marked value (missing-and-inapplicable)», E. F. Codd; The Relational Model for Database Management: Version 2. Reading, Mass.: Addison-Wesley, 1990). Ceci est la conséquence directe de la cardinalité 0,1 attachée au lien allant de TELEPHONES vers SOCIETES. Est-ce grave ? Déjà, si Ted Codd, le père du Modèle relationnel a énoncé la règle, on peut s’en douter, il n’a jamais rien dit ou écrit qui soit gratuit. En fait, si au niveau opérationnel vous utilisez SQL, vous serez confronté au problème d’une logique ternaire (vrai, faux, peut-être) source d’erreurs intrinsèques et que ce langage domine plus ou moins bien.
Si vous tenez vraiment à n’avoir qu’une seule table TELEPHONES, faites en sorte de respecter l’intégrité d’entité et donc, partant de l’association POSSEDER, dérivez une table POSSEDER (avec un nom plus approprié) qui aura une clé étrangère vers TELEPHONES et une autre vers SOCIETES (simultanément clé primaire (sauf peut-être si vous êtes France Télécom...))

Du point de vue de la production :
Le nombre de lignes de la table des téléphones est la somme du nombre de lignes de téléphones (sic) des contacts et de celui des lignes des sociétés. Quel intérêt à les regrouper ? Gérer un référentiel de téléphones ? Votre entreprise s’appelle-t-elle France Télécom ? Les téléphones des sociétés ne sont-ils pas plutôt des valeurs d’une propriété multivaluée TELEPHONES de SOCIETES ? En fait, la table TELEPHONES a tout pour devenir obèse : si vous voulez lui faire subir une cure d’amaigrissement, cassez-la en deux, en séparant les téléphones des contacts de ceux des sociétés (tables TELEPHONES_SOCIETES et TELEPHONES_CONTACTS). Je vous dis cela parce que j’ai vécu ce film plus d’une fois. Vous me rétorquerez : — Soit, mais si le téléphone du contact est celui de sa société, je devrais avoir ce téléphone dans les deux tables—. Appliquons le principe de parcimonie, du rasoir d’Ockham : Si pour le contact il n’y a pas de téléphone dans la table des téléphones des contacts, alors on utilise la table des téléphones des sociétés (un contact détermine une société). Vous pourriez me dire aussi que lorsqu’on modélise, on apprend : One concept, one place! Certes, il y a comme un dogme à ce sujet, et ça ne serait pas bien d’avoir deux entités TELEPHONES_SOCIETES et TELEPHONES_CONTACTS, mais quelle valeur ajoutée apporte ce dogme en l’occurrence ? On peut considérer que de ces deux entités, par UNION, on induit une entité TELEPHONES virtuelle. Bon, je n’insiste pas.

Note sur l’identification relative :
- Si au niveau de la production vous ne voulez pas être I/O bound (dégradation plus que sensible du débit due aux entrées-sorties), identifiez ADRESSES relativement à SOCIETES, même chose pour CONTACTS, etc.
- De la même façon, conservez STE_ID pour les sous-types CLIENTS et FOURNISSEURS, à moins que vous sachiez justifier CLI_ID et FOU_ID (sans perdre de vue le cauchemar de la Production informatique, à savoir l’I/O bound...)

[Menoto]

Bonjour,

je vous remercie pour votre réponse fort interessante.

Effectivement je comprends que la table téléphone ne convienne pas forcément.
Je pense que je ne vais pas conserver cette table. L'idée était de dire qu'il pouvait y avoir plusieurs type de téléphone (TEL, FAX, PORTABLE, INTERNE), etc. Mais effectivement, je m'interroge sur l'utilité d'une telle entité.

Suite à votre point Conformité au Modèle relationnel, j'ai quelques questions.
Sur le schéma il y a une relation 0,1 entre l'entité téléphone et l'entité société, car effectivement il est possible qu'une société n'ai pas de téléphone ou que ce téléphone ne soit pas connu.
Je suis d'accord pour dire que la relation 1,n entre téléphone et societe va faire qu'une clé secondaire migrera de Societe vers Telephone au passage au MPD.
Comme, il est possible qu'une société n'est pas de téléphone, aucune ligne dans la table téléphone n'apparaîtra pour la société n'en ayant pas.
Donc si je ne mets qu'une seule table pour la société, il n'y aura plus de problème avec cette clé secondaire null.
Bon en fait, y a pas de questions... C'est parce que j'ai réfléchi en écrivant.

Donc globalement, ne me conseilleriez-vous pas simplement d'ajouter dans l'entite Societe un attribut TEL, un attribut FAX plutôt que d'utiliser une table TELEPHONE.

De même pour l'entité Contact, d'ajouter un attribut TEL, FAX, PORTABLE, INTERNE.

Par contre, je n'ai pas bien compris le point Note sur l’identification relative surtout pour identifier Adresses relativement à Societe.

Merci d'avance pour vos explications.

[fsmrel]

Bonjour Menoto,

A propos de la table TELEPHONES, vous écrivez

Je pense que je ne vais pas conserver cette table. L'idée était de dire qu'il pouvait y avoir plusieurs types de téléphone (TEL, FAX, PORTABLE, INTERNE), etc. Mais effectivement, je m'interroge sur l'utilité d'une telle entité.

Vous devez avoir au moins une table pour les téléphones, puisque selon votre MCD :
- Une société a de 0 à N téléphones
- Un contact a de 0 à N téléphones.
En revanche, j’ai indiqué que vous aviez la possibilité de mettre en œuvre une table pour les sociétés (donc une entité-type TELEPHONES_SOCIETES) et une autre pour les contacts (TELEPHONES_CONTACTS).

Sur le schéma il y a une relation 0,1 entre l'entité téléphone et l'entité société, car effectivement il est possible qu'une société n'ai pas de téléphone ou que ce téléphone ne soit pas connu.

J’ai l’impression que vous interprétez mal la cardinalité 0,1. En effet, celle-ci signifie d’une part qu’un téléphone n’est pas forcément celui d’une entreprise (ce qui laisse indirectement supposer que c’est celui d’un contact) et d’autre part que qu’un téléphone ne peut pas être la copropriété des entreprises.
Si vous mettez en œuvre TELEPHONES_SOCIETES et qu’une entreprise n’a pas de téléphone ou plus vraisemblablement que celui-ci n’est pas connu, alors il n’y aura rien en toute logique pour cette entreprise dans TELEPHONES_SOCIETES.

Je suis d'accord pour dire que la relation 1,n entre téléphone et societe va faire qu'une clé secondaire migrera de Societe vers Telephone au passage au MPD.

Hum... cette migration n’est pas le fait d’une cardinalité 1,n, mais d’une cardinalité 1,1 (ou 0,1).

Donc si je ne mets qu'une seule table pour la société, il n'y aura plus de problème avec cette clé secondaire null.

Je suppose que vous parlez de TELEPHONES_ SOCIETES.

Donc globalement, ne me conseilleriez-vous pas simplement d'ajouter dans l'entite Societe un attribut TEL, un attribut FAX plutôt que d'utiliser une table TELEPHONE.
De même pour l'entité Contact, d'ajouter un attribut TEL, FAX, PORTABLE, INTERNE.

Ceci changerait les règles de gestion des données exprimées par les cardinalités, ce qui n’est pas forcément ce que vous souhaitez :

La règle « Une entreprise peut avoir plusieurs téléphones » deviendrait « Une entreprise peut avoir au plus 1 téléphone »

La règle « Un contact peut avoir plusieurs téléphones » deviendrait « Un contact peut avoir au plus 1 téléphone »

J’ai peur qu’il y ait un quiproquo concernant la lecture des cardinalités. Comme le suggère Bujuman, il serait bon que vous rédigiez en français les règles de gestion traduites par ces cardinalités.
Il apparaît que vous utilisez Power AMC. Vous serez peut-être plus à l’aise en lisant d’une autre façon les cardinalités, c’est-à-dire en utilisant la représentation E/R : Outils > Options du modèle > Notation > Entité/Relation.

Par contre, je n'ai pas bien compris le point Note sur l’identification relative surtout pour identifier Adresses relativement à Societe.

Au niveau conceptuel, une entité-type peut être qualifiée de forte ou de faible. Par exemple, une entité-type Ligne de Commande n’a pas d’existence propre, elle n’est pas autonome : elle n’a de sens que relativement à entité-type Commande, on dit qu’il s’agit d’une entité faible. On peut encore voir Ligne de Commande comme étant une propriété multivaluée de Commande (une commande peut comporter plusieurs lignes) et il va de soi que la destruction d’une commande entraîne celle de ses lignes (alors qu’à l’inverse, la suppression d’un produit ne peut pas provoquer la suppression des lignes de commande y faisant référence).
En utilisant le même raisonnement, on peut arriver à conclure que les entités-types CONTACTS et ADRESSES sont des entités faibles car n’ayant pas de sens en l’absence de l’entité-type SOCIETES, autrement dit d’existence autonome. En corollaire, un contact ne peut pas s’opposer à la destruction de la société à laquelle il appartient (idem pour l’adresse de cette société).
Qui dit entité faible (ou propriété multivaluée) dit identification relativement à une entité plus forte : si l’identifiant de SOCIETES est STE_ID, celui de ADRESSES devient {STE_ID, ADR_ID}. ADR_ID est un séquenceur prenant les valeurs 1, 2, ...n, relativement à une valeur de STE_ID (on recommence la numérotation pour chaque société). L’identification relative se défend au plan sémantique et qui plus est, a des effets très bénéfiques au niveau physique. En effet, au lieu que les adresses d’une société soient éparpillées dans des pages physiques distinctes (ce qui peut coûter cher quand il s’agit de récupérer toutes les adresses d’une société), il devient possible de les regrouper dans la même page (ou dans des pages contiguës selon leur nombre) et partant de réduire au minimum les accès physiques, c’est-à-dire de réduire, voire éliminer les phénomènes d’I/O bound que j’ai précédemment évoqués. Un DBA attentif pourra vous dire que l’on peut regrouper physiquement des lignes de commande sur un numéro de commande qui ne participe pas à la clé primaire de Ligne de commande, mais il sera coincé dès que l’on en arrivera aux engagements sur lignes de commande (l'engagement étant à son tour une propriété multivaluée de la ligne de commande, donc à identifier relativement à cette dernière).

Je suis désolé de parler de concepts physiques au niveau de ce forum, mais je le fais à cause des conséquences que peut avoir un choix dans la modélisation conceptuelle tandis que l’on n’a pas envie de charcuter le modèle physique produit pas l’AGL.

Je ne sais pas si je vous ai éclairé ou laissé désemparé, mais petit à petit l’oiseau doit faire son nid, il faut vous accrocher.

[Menoto]

Effectivement,

je me suis un peu mélangé avec tout ça.
Je comprends effectivement mieux l'identification relative.
Auriez-vous un ouvrage ou un support de cours sur internet à me conseiller m'expliquant tous ces concepts qui m'échappent encore ?

Pour en revenir à mon schéma:

Vous devez avoir au moins une table pour les téléphones, puisque selon votre MCD :
- Une société a de 0 à N téléphones
- Un contact a de 0 à N téléphones.

En fait, une société peut avoir un téléphone, et peut avoir un fax.
Un contact peut avoir un téléphone (ligne directe), un fax et/ou un portable.
Plusieurs contact peuvent avoir le même téléphone, le même fax.
Un contact peut avoir le même téléphone que celui-de la société.
Il peut aussi avoir le même fax que celui de la société.

Donc si je fais une table TelephoneSociete et une table TelephoneContact, ne devrais-je pas faire une table Telephone dont hériterait les tables TelephoneSociete et TelephoneContact ?
Certes, il n'y aura aucun attribut spécifique dans mes tables TelephoneSociete et TelephoneContact.

Pour les adresses, une société peut avoir plusieurs adresses, une adresse pour le siège, une adresse pour une filiale (quoiqu'à la limite la fillialte pourrait être une nouvelle entrée dans la table Societe), une adresse de facturation, une adresse de livraison (quoiqu'à la limite, ça pourrait faire l'objet d'un attribut adresse dans l'entité facture, un attribut adresse dans l'entité commande)...

{STE_ID, ADR_ID} serait la clé primaire de la table Adresses ?
Mais dans ce cas, cette clé est une clé composite, n'est-ce pas ?
J'ai lu un document de SQLPro précisant qu'il fallait éviter les clé composite car cela nuisait aux performances, à moins que j'ai mal compris.

Et pour les entités Clients et Fournisseurs, vous me conseillez d'utiliser comme clé primaire de ces entités, la clé primaire de société ?

Pour répondre à Bujuman, je n'avais pas pris soin d'énumérer les règles régissant ce MCD car je voyais cela comme un cas classique.
De manière classique comment cela devrait-il être mis en oeuvre ?

Une fois de plus merci pour vos explications décidément très interessante.

[fsmrel]

Hello Menoto,

Auriez-vous un ouvrage ou un support de cours sur internet à me conseiller m'expliquant tous ces concepts qui m'échappent encore ?

Au niveau conceptuel

Vous avez l'ouvrage de Dominique Nanci et Bernard Espinasse, "Ingénierie des systèmes d'information, MERISE - deuxième génération", extrêmement complet.
Vous avez les FAQ de ce forum.
Un ouvrage appréciable : Yves Tabourier "De l'autre côté de Merise" (mais le trouve-t-on encore ?)
Un document précieux, relatif à Merise/2 : "Journée AFCET du 15 novembre 1990 - Le formalisme Merise : extensions du pouvoir d'expression" (mais là aussi, peut-on encore le trouver ?)
Vous pouvez aussi récupérer l'ouvrage de Michel Diviné "Parlez-vous Merise?" à partir des liens http://michel.divine.free.fr/Livre.htm ou http://michel.divine.free.fr/Parlez.zip

Au niveau relationnel (des tables, pour être informel)

La bible : C.J. Date. An Introduction to Database Systems, 8th edition. (Pearson: Addison-Wesley (international edition), 2004). Attention, c’est un pavé de près de 1000 pages en anglais, mais que j’ai pour ma part toujours à portée de main.

Au niveau physique

Je n'ai pas cherché, mais au hasard je tombe sur des points de vue fragmentaires, en relation avec un SGBD donné, assénés et non vérifiés, que l’on peut très souvent contredire. En effet, on en arrive au niveau de l’optimisation des programmes et des accès aux tables et peu importe que celles-ci soient pertinentes, on s’intéresse plutôt aux temps de réponse des transactions et à la durée des traitements batch. Il s’agit en gros de mettre en œuvre les index qui permettront peut-être de tendre vers la rapidité souhaitée, en croisant les doigts...

Passage d’un niveau à l’autre

C’est là où le bât blesse, car les fonctionnalités et les approches ne sont pas les mêmes. Toutefois pour parler du niveau conceptuel et du niveau relationnel, les deux sont nécessaires et complémentaires : le premier nous permet d’avoir une approche sémantique des données, le second une approche plus mathématique et logique, avec une algèbre et un calcul relationnel (lequel ne peut être abordé que si l’on a déjà une bonne connaissance de la logique : quantification, règles de passage en forme prénexe ou pure, etc.)

En ce qui concerne les contraintes, les deux niveaux permettent d’exprimer les choses de façon différente. Ainsi, la surjection se traduit au niveau conceptuel par une cardinalité 1,N et au niveau relationnel par une contrainte.
Par exemple, si la règle est : « un fournisseur fournit au moins une pièce », au niveau conceptuel vous aurez une cardinalité 1,N attachée à une patte de relation entre l’entité-type Pièce (identifiant Piece_No) et la relation Fournir, tandis qu’au niveau relationnel vous écrirez (en langage D) :

CONSTRAINT Cx Fournir {Piece_No} = Pièce {Piece_No} ;

{Piece_No} étant la projection des tables Fournir et Pièce sur l’attribut Piece_No. Il n’est pas évident que les concepteurs et les DBA comprennent mutuellement leurs langages.

De l’identification relative

{STE_ID, ADR_ID} serait la clé primaire de la table Adresses ?
Mais dans ce cas, cette clé est une clé composite, n'est-ce pas ?
J'ai lu un document de SQLPro précisant qu'il fallait éviter les clés composites car cela nuisait aux performances, à moins que j’aie mal compris.

{STE_ID, ADR_ID} sera la clé primaire de la table Adresses. La clé est effectivement composée de deux attributs.
Celui qui a écrit qu’il ne fallait pas utiliser de clés composites n’a pas dû avoir à traiter des problèmes de performance en relation avec des volumes importants de données (tables de quelques dizaines à quelques centaines de millions de lignes). Pour avoir été pendant des années moi-même confronté à cela et pour l'avoir vérifié, je puis vous dire que je soutiens exactement la position contraire. Reportez-vous à ce que je vous ai dit précédemment concernant les rafales de lecture de pages physiques. Disons encore que si pour une entreprise vous avez 15 adresses et que celles-ci sont physiquement dans la même page, pour une I/O à 10 millisecondes, le coût de la lecture de ces 15 adresses est de 10 millisecondes. Si les adresses sont éparpillées sur le disque, à la limite le coût grimpe à 150 millisecondes et au bout d’un moment, vous serez candidat à l’I/O bound. Un moyen de contrôler le regroupement physique est, en amont, d’utiliser l’identification relative.

Et pour les entités Clients et Fournisseurs, vous me conseillez d'utiliser comme clé primaire de ces entités, la clé primaire de société ?

Yes Sir!

Concernant les adresses

Je suppose que vous gérez un type d’adresse : adresse de l’entreprise (celle de l’établissement principal), adresse de facturation, de livraison, etc.
Concernant les filiales, si vous estimez qu’elles méritent d’être modélisées pour elles-mêmes (c’est-à-dire si elles entretiennent des relations avec les autres entités du modèle ou possèdent des propriétés en propre), alors il serait opportun de réfléchir à la mise en œuvre éventuelle d’un surtype Personne dont les sous-types seraient Entreprise et Filiale. Voir alors s’il faut remonter les téléphones et les contacts au niveau du surtype.

Concernant les téléphones

En fonction des règles que vous avez fournies, le MCD pourrait être celui qui figure en pièce jointe.
Vous noterez que j’utilise l’identification relative aussi bien pour les contacts que pour les téléphones.
Attention lors de la dérivation en tables : la table Utiliser comportera deux fois l’attribut Ent_Id, il faudra en supprimer un des deux (sinon vous associeriez les téléphones d’entreprises différentes...

Exemple de code SQL attendu :

create table ENTREPRISE (
ENT_ID int not null,
NOM char(64) not null,
constraint PK_ENTREPRISE primary key (ENT_ID)
)

create table CONTACT (
ENT_ID int not null,
CONT_ID int not null,
NOM char(64) not null,
constraint PK_CONTACT primary key (ENT_ID, CONT_ID)
)

create table TELEPHONE (
ENT_ID int not null,
TEL_ID int not null,
TEL_NO char(20) not null,
TEL_TYPE char(1) not null,_________________/* "T" (tél fixe), "F" (fax), "M" (mixte), etc. */
TEL_SOC char(1) not null,__________________/* "S" (société), "F" (filiale), etc. */
constraint PK_TELEPHONE primary key (ENT_ID, TEL_ID)
)

create table UTILISER (
ENT_ID int not null,
CONT_ID int not null,
TEL_ID int not null,
constraint PK_UTILISER primary key (ENT_ID, CONT_ID, TEL_ID)
)

alter table CONTACT
add constraint Employer foreign key (ENT_ID)
references ENTREPRISE (ENT_ID)
on delete cascade

alter table TELEPHONE
add constraint Posseder_Tel foreign key (ENT_ID)
references ENTREPRISE (ENT_ID)
on delete cascade

alter table UTILISER
add constraint Contact_Utilise foreign key (ENT_ID, CONT_ID)
references CONTACT (ENT_ID, CONT_ID)
on delete cascade

alter table UTILISER
add constraint Tel_Sert foreign key (ENT_ID, TEL_ID)
references TELEPHONE (ENT_ID, TEL_ID)
on delete cascade