Discussion :

[Age_of_Aquarius]

Bonjour,

J'aimerais savoir lequel des deux schémas relationels que j'ai faites dans MySQL Workbench est correct ?

MCD :

MLD 1:

MLD 2:


Merci.

[Richard_35]

Bonjour Age_of_Aquarius,

J'aimerais savoir lequel des deux schémas relationels que j'ai faites dans MySQL Workbench est correct ?

==> eh bien, je n'en sais rien, mais ta question vient sans doute du fait que le MCD, lui-même, est incorrect.

Ton MCD modélise la règle de gestion suivante :

un emprunteur peut emprunter plusieurs livres ==> ça, c'est OK
un livre ne peut être emprunté qu'une seule fois ==> donc, par un seul emprunteur, et, à une date : bizarre...

Nous sentons bien ce que tu as voulu dire, mais tu as mélangé deux choses : les différents emprunts d'un livre, et la date d'emprunt.

En fait, tu as, sans doute, voulu dire :

un emprunteur peut emprunter plusieurs livres
un livre peut être emprunté par plusieurs emprunteurs ==> sous entendu, à une date

Il faut donc modifier ton MCD en remplaçant la cardinalité 0,1 par 0,N. L'attribut "date_emprunt" de la relation associative est OK. En final, la relation deviendra une table associative contenant l'identifiant de tes deux entités et l'attribut "date_emprunt".

Je ne sais pas trop si j'ai utilisé les bons termes aux bons endroits (merci pour la correction future qui ne tardera pas d'arriver)... mais, je pense que tu voies l'idée.

[Age_of_Aquarius]

Bonjour Richard_35,

Merci pour ta réponse.

Explication du MCD :

- Un livre ne peut être emprunté que par un seul emprunteur au plus ; c-à-d un livre ne peut être emprunté par deux emprunteurs à la fois. (Donc le chiffre 1 dans 0,1 réfère au nombre maximal de personnes pouvant emprunter un livre simultanément)

- Un livre peut ne pas être emprunté.

- Un emprunteur peut emprunter plusieurs livres à la fois.

- Un emprunteur peut n'emprunter aucun livre.

La règle de gestion ne s’intéresse pas à l'historique des emprunts mais uniquement à l'état du livre : est-ce qu'il est emprunté ou pas, si oui qui est l'emprunteur et à quelle date il l'a emprunté.

Merci.

[fsmrel]

Bonsoir,

Un livre ne peut être emprunté que par un seul emprunteur au plus ; c-à-d un livre ne peut être emprunté par deux emprunteurs à la fois.

Il y a une contradiction dans les deux propositions. En réalité, un livre a pu faire le bonheur d’une foule de personnes, mais une seule à la fois comme vous l’écrivez dans votre 2e proposition, ce que disait bien Richard.

Peu importe. Il existe une contrainte d’unicité fonctionnelle (CIF en Merise) que l’on peut symboliser ainsi :

LIVRE X DATE → EMPRUNTEUR

Pour une valeur de la paire {livre, date} il y a exactement une valeur du singleton {emprunteur}.

Ce qu’on symbolise dans le MCD par la flèche rouge ci-dessous :




D’où le MLD en MySQL Workbench :

[Age_of_Aquarius]

Bonjour fsmrel,

D'abord, Je te remercie beaucoup pour le temps que t'as prie pour me répondre tout en créant les schémas et pour les explications détaillés.

La table "emprunt" créé sert justement à garder l'historique des emprunts. Or comme j'ai dis plus haut, je ne veux pas garder l'historique des emprunts mais juste connaitre l'état d'un livre : est ce qu'il est emprunté ou pas.

Je crois que mon exemple n'est pas bon, puisque ce qui m’intéresse le plus c'est de savoir si les schémas que j'ai fait dans MySQL Workbench pour la relation (0,1) - (0,n) typique sont corrects ou pas.

Disant, par exemple, pour ce MCD :


Merci.

[fsmrel]

OK. Considérons que les modèles que je vous ai proposés reflètent comme vous dites l’historique des emprunts. On complète avec les emprunts en cours. Le prédicat de la table EMPRUNT_EN_COURS est le suivant :

Le livre LivreId est actuellement emprunté par la personne EmprunteurId depuis la date DateEmprunt.

La représentation correcte avec Workbench est la suivante :



Pour transformer la cardinalité 1,1 en 0,1, on double clique sur le lien entre LIVRE et EMPRUNT_EN_COURS, ce qui ouvre un onglet « Foreign Key » et on y décoche la case « Mandatory » concernée :



Par ailleurs, la table EMPRUNT_EN_COURS a pour clé le singleton {LivreId} mais pas la paire {LivreId, EmprunteurId}, sinon cela reviendrait à transformer la cardinalité 0,1 en 0,N.

Ça va-t-y mieux comme ça ?

[CinePhil]

Petit détail François...

Ton association correspond à celle ci en MCD merisien classique :
Livre -0,1----emprunt_en_cours----1,n- Emprunteur

Mais Age_of_Aquarius voulait ceci :
Livre -0,1----emprunt_en_cours----0,n- Emprunteur

Donc il faut ceci en Workbench (flemme de faire le schéma aussi beau que le tien) :
Livre -||----o| emprunt_en_cours >o----||- Emprunteur

[fsmrel]

Exact Philippe, j'ai oublié de me pencher sur la cardinalité 0,N côté Emprunteur, je corrige donc :

[Age_of_Aquarius]

Bonjour,

Merci CinePhil pour ta participation.

Là je comprend mieux. En fait, mes deux MLD ne sont pas corrects.
Dans le MLD proposé par fsmrel, la table "EMPRUNT_EN_COURS" sert justement à garder la liste des livres qui sont empruntés. Par conséquent, elle constitue un sous-ensemble (sous-domaine) de la table "Livre".

Sinon, que pensez vous du MLD suivant ?



Dans ce cas la clé étrangère "emprunteur_id" peut être NULL. Mais si le SGBD (ici MySQL) l’accepte et la gère, alors je pense que ce MLD sera plus simple.

Merci.

[CinePhil]

Plus simple en apparence mais tu laisses une armée de NULL envahir ta BDD et fsmrel va se pointer avec son char pour exterminer cet envahisseur !

[Age_of_Aquarius]

Oui mais dans les bases de données généralement on prévoit des champs qui peuvent être NULL.

Est-ce que le problème vient de l'armée de NULL comme tu dis, ou du fait que certain SGBD n’accepte pas que la valeur d'une clé étrangère soit NULL ?

[fsmrel]

NULL est une incongruité. Quand il l'a inventé, il y a bientôt 40 ans, Don Chamberlin aurait mieux fait de rester couché et Codd de ne pas en remettre une couche.

NULL, c'est pour Nouf-Nouf et Nif-Nif, mais pas pour Naf-naf.

NULL peut-être source d'erreurs, entre autres en relation avec la jointure, par exemple le théorème de Heath ne fonctionne plus...

Fermez le ban.

[Age_of_Aquarius]

Ok. Donc ce n'est pas juste une question de clé étrangère NULL (indéfinie) mais, d'après ce que j'ai compris, c'est général ; c'est-à-dire aucun champs défini (colonne) du schéma de la base de données ne doit être défini comme NULL.

Parfois, lorsqu'on ajoute un nouvel enregistrement dans une table, on doit laisser des champs vides soit pour les compléter après, ou bien les valeurs de ces champs n'ont pas de sens pour cet enregistrement (exemple : "nom de jeune fille" d'un homme dans la table "Personne".)

Qu'est-ce qu'il faut faire dans ce cas ? Forcer que tous les champs soient NON NULL et exiger une valeur par défaut ?

Merci.

[CinePhil]

Parfois, lorsqu'on ajoute un nouvel enregistrement dans une table, on doit laisser des champs vides soit pour les compléter après, ou bien les valeurs de ces champs n'ont pas de sens pour cet enregistrement (exemple : "nom de jeune fille" d'un homme dans la table "Personne".)

Nous avons tous un nom de naissance qui est aussi, au début de notre vie, notre nom usuel et notre nom officiel au registre d'état civil. Certains d'entre nous changent de nom usuel par mariage. Certains de ceux qui ont changé de nom usuel reprennent leur nom de naissance comme nom usuel parce qu'ils ont divorcé.

Une bonne manière d'enregistrer tout ça serait d'avoir :
- une colonne pour le nom de naissance / d'état civil ;
- une colonne pour le nom usuel ayant pour valeur par défaut une chaîne vide.

Liste des personnes par nom usuel :

Code SQL :

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

1
2
3
4
5
6
7
8
SELECT
	CASE
		WHEN prs_nom_usuel = '' THEN prs_nom_civil
		ELSE prs_nom_usuel
	END AS nom_usuel,
	prs_prenom
FROM te_personne_prs
ORDER BY nom_usuel, prs_prenom

On peut aussi faire une table pour les noms usuels, la proportion de cas de nom usuel différent du nom civil étant inférieur à 50% (quasiment tous les hommes et quasiment toutes les femmes non mariées ou divorcées).

Code SQL :

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

1
2
3
4
5
6
7
8
9
SELECT
	CASE
		WHEN u.usl_nom_usuel IS NULL THEN p.prs_nom_civil
		ELSE u.usl_nom_usuel
	END AS nom_usuel,
	p.prs_prenom
FROM te_personne_prs p
LEFT OUTER JOIN te_nom_usuel_usl u ON u.usl_id_personne = p.prs_id
ORDER BY nom_usuel, p.prs_prenom

Qu'est-ce qu'il faut faire dans ce cas ? Forcer que tous les champs soient NON NULL et exiger une valeur par défaut ?

Oui, mais attention à l'interprétation de la valeur par défaut, comme expliqué dans l'autre discussion citée par fsmrel !
Un nom usuel vide signifie t-il que le nom usuel est le nom civil ou que le nom usuel n'est pas connu ?

[Richard_35]

Bonjour à tous,

aucun champs défini (colonne) du schéma de la base de données ne doit être défini comme NULL.

==> non : nous parlons, uniquement, des clés étrangères (pas des colonnes de type "Observations", par exemple).

Un autre aspect important à prendre en compte, Age_of_Aquarius : avec le temps il arrive très souvent (toujours ?) que nous ayons besoins de stocker d'autres attributs propres à la partie dont la clé étrangère peut être NULL. Dans ce cas, tu seras obligé d'ajouter des champs au fur et à mesure des besoins, augmentant, de ce fait, la liste des champs NULL (pour les livres jamais encore empruntés, dans ton exemple).

Toujours dans ton exemple, les tables que tu décris sont donc les suivantes :

Emprunteur(EmprunteurId, EmprunteurName, EmprunteurAge, ...)
Livre(LivreId, LivreTitre, LivreGenre, #EmprunteurId, DateEmprunt, ...)

Avec le temps, la partie de Livre concernant l'emprunt proprement dit (et non pas le livre) aura tendance à évoluer :

• date de retour du livre ;
• état du livre retourné ;
• montant du dédommagement concernant l'état ;
• Observations ;
• etc...

Par conséquent, la justification d'une table intermédiaire trouve tout son sens, même avec une relation 0,1 à sa gauche, soit :

Emprunteur(EmprunteurId, EmprunteurName, EmprunteurAge, ...)
Livre(LivreId, LivreTitre, LivreGenre, ...)
Emprunteur_Livre(#EmprunteurId, #LivreId, DateEmprunt, ...)

Notons, au passage, qu'il suffit de modifier la table associative de cette manière :

Emprunteur_Livre(#EmprunteurId, #LivreId, DateEmprunt, ...)

pour pouvoir gérer un historique (dont le besoin ne manquera pas de se faire sentir, avec le temps).

En résumé, conceptuellement, il existe bien deux entités différentes : les livres et les emprunts... et il y a toujours danger à vouloir, absolument, fusionner deux entités conceptuelles.

[fsmrel]

aucun champs défini (colonne) du schéma de la base de données ne doit être défini comme NULL.

non : nous parlons, uniquement, des clés étrangères (pas des colonnes de type "Observations", par exemple).

Si. Le défi est de coller au Modèle Relationnel de Données où la logique trivalente n’a pas droit de cité. L'algèbre relationnelle ne concerne pas seulement les attributs participant à des clés étrangères.

[Richard_35]

L'algèbre relationnelle ne concerne pas seulement les attributs participant à des clés étrangères.

==> c'est le débat éternel !...

Personnellement, je pose cette limite aux clés étrangères. Mais bon, cela n'engage que moi. Je trouve un peu lourd de créer une entité (une table, en final) pour tous les attributs (les champs, en final) qui pourraient être NULL.

Par exemple, les champs de type :

• "Observations" ;
• "N° (dans la rue)", dans les adresses (car, parfois, il n'y a pas de n°) ;
• ...

ne nécessitent pas, de mon point de vue, la création d'une table à par entière. Mais bon, tout est possible !

Rappelons-nous que tous les excès sont mauvais... d'un autre côté, c'est vrai, l'équilibre est toujours difficile à trouver.

[Age_of_Aquarius]

Bonjour tout le monde,

Par conséquent, la justification d'une table intermédiaire trouve tout son sens, même avec une relation 0,1 à sa gauche

Ok. Supposons cette fois-ci que l'emprunteur n'a le droit d'emprunter qu'un seul livre à la fois. (Relation (0,1) - (0,1))

Le MLD dans ce cas est le suivant :



Sur le blog de CinePhil, il est indiqué que la 2éme clé étrangère (qui n'est pas clé primaire dans la nouvelle table), doit être unique, alors que dont la relation (0,1) - (0,n), ce n'est pas le cas. Pourquoi ?

[Richard_35]

Supposons cette fois-ci que l'emprunteur n'a le droit d'emprunter qu'un seul livre à la fois. (Relation (0,1) - (0,1))

==> une nouvelle fois, tu tentes de regrouper plusieurs notions et, de ce fait, tu vas trop vite (et, je dois bien l'avouer, cela ne manque pas d'intérêt).

Livre -0,1---[Emprunter]---0,1- Emprunteur

ne veut pas dire :

l'emprunteur n'a le droit d'emprunter qu'un seul livre à la fois

mais :

l'emprunteur n'a le droit d'emprunter qu'un seul livre tout court
un livre n'a le doit d'être emprunté qu'une seule fois

ou encore

1 livre peut être emprunté (0) ou 1 fois maximum (1) par un seul emprunteur ==> sous entendu, une fois qu'un livre est emprunté, il ne peut plus être emprunté par quiconque
1 emprunteur peut emprunter (0) ou 1 fois maximum (1) un livre ==> sous entendu, une fois qu'un emprunteur emprunte un livre, il ne peut plus emprunter aucun autre livre

donnant :

Livre(LivreId, …) ;
Emprunteur(EmprunteurId, …) ;
Livre_Emprunteur(#LivreId, #EmprunteurId, …) + index unique sur #EmprunteurId
ou
Emprunteur_Livre(#EmprunteurIdId, #LivreId, …) + index unique sur #LivreId

Ton erreur vient de deux choses, dans ton précédent message :

• tu fais intervenir la notion de temps dans une relation 0,1 ;
• tu pars de la table associative : une table associative est une conséquence d'un modèle conceptuel, pas une base de travail.

[fsmrel]

Sur le blog de CinePhil, il est indiqué que la 2éme clé étrangère (qui n'est pas clé primaire dans la nouvelle table), doit être unique, alors que dont la relation (0,1) - (0,n), ce n'est pas le cas. Pourquoi ?

Quand vous définissez une cardinalité 0,n (côté EMPRUNTEUR), cela veut dire qu’une personne peut avoir plusieurs livres en cours de prêt à la fois, alors que lorsque vous définissez une cardinalité 0,1, c'est un seul livre ; disons qu’il y a alors une injection de EMPRUNT_EN_COURS dans EMPRUNTEUR, faisant l’objet d’une clé alternative, ce qu’on implémente en SQL au moyen d’une contrainte d’unicité :



Si on ne procède pas ainsi, rien n’empêchera que dans la table une personne ait plusieurs livres en cours de prêt, auquel cas l’injection est remplacée par une banale application.

MySQL Workbench ne proposant pas de mickey pour la clé alternative, j’en ai ajouté un sur le dessin (la petite clé à gauche de l’attribut EmprunteurId dans EMPRUNT_EN_COURS) :




On peut faire apparaître la contrainte UNIQUE (index (sic !) EmprunteurId_UNIQUE) mais la représentation graphique devient un tantinet chargée, j’aime mieux la petite clé... :




Si vous avez encore des doutes, n'hésitez pas, profitez des bonnes dispositions de tous