Discussion :

[mango86]

Bonjour,

Je souhaite créer une petite base de données de livres, magazines et articles sur le jeu d'échecs et je suis complètement débutant dans la conception de BDD. Sur la base des articles parcourus sur ce site et ailleurs, une étape liminaire semble être la création d'un MCD. Dans celui que j'imagine, la table Ouvrage est centrale, les tables Auteur, Collection (suite de livres), Type (livre, magazine, article), Edition s'y rapportent directement.

Dans un répertoire "Echecs" enregistré sur mon DD, j'ai une arborescence de dossiers et sous-dossiers jusqu'aux fichiers que je souhaite traduire dans ma BDD. Par exemple, dans la table Theme, je peux avoir quelques thèmes comme "Ouvertures", "Milieu de jeu", "Finales". Dans "Ouvertures", il y a "Début Fermé", "Début semi-ouvert" et "Début ouvert". Dans "Début semi-ouvert", plusieurs noms d'ouvertures, etc, jusqu'à mon fichier. Dans mon MCD, j'ai créé des relations "se rattacher à" entre un thème et un thème parent ("theme 1P"), ce même thème parent étant rattaché à thème parent supérieur ("theme 2P"), qui lui même à un ascendant "theme 3P" etc. Est-ce que je suis sur la bonne voie pour transcrire cette hiérarchie, sinon que proposez-vous comme MCD/MLD ?

Merci pour vos conseils
MCD créé avec mocodo online

AUTEUR ( id, nom, prenom, annee_naissance )
REDIGER UN ( id, id.1 )
OUVRAGE ( id, titre, annee, ISBN10, ISBN13, id.1, id.2, id.3 )
COLLECTION ( id, collection )
TYPE ( id, type )
EDITION ( id, num_edition, id.1 )
EDITEUR ( id, nom_editeur )
TRAITER D'UN ( id, id.1 )
THEME ( id, theme, id.1 )
THEME 1P ( id, theme, id.1 )
THEME 2P ( id, theme, id.1 )
THEME 3P ( id, theme )

[CinePhil]

Bonjour,
Pour un débutant, ce n'est pas mal du tout !

Par contre, pour la modélisation des thèmes et sous-thèmes, il convient de faire une association réflexive.

Règle de gestion :
Un (sous-)thème est fils d'un thème et un thème peut être parent d'un à plusieurs (sous-)thèmes.

MCD :
Thème -0,n----(parent)----|
|----------0,1----(fils)---------|

S'il y a plusieurs "étages" hiérarchiques, on peut alors mettre en oeuvre dans la base de données un arbre en représentation intervallaire.

[escartefigue]

Bonjour,

L'ISBN est un attribut facultatif lié à l'édition.
Facultatif car il n'existe que depuis 1970 et qu'il n'est créé que pour les publications avec dépôt légal.
Lié à l'édition, il est différent en fonction de l'éditeur et de la collection.
On ne doit donc pas le rattacher directement à l'ouvrage rédigé par le ou les auteurs, mais à l'édition de cet ouvrage.

Il y a une discussion récente ici sur ce sujet dans laquelle ces éléments sont expliqués

Voici le modèle "type" d'une bibliothèque de prêt correspondant aux règles de gestion du sujet ci-dessus
Pièce jointe 601470

[mango86]

Bonjour,

Merci CinePhil et escartefigue pour vos contributions.

J'ai donc repris mon MCD pour arriver à celui ci-dessous :

S'il y a plusieurs "étages" hiérarchiques, on peut alors mettre en oeuvre dans la base de données un arbre en représentation intervallaire.

Voila pour moi un point dur. Le dossier racine comprend plus de 1500 fichiers et environ 300 dossiers, et 4 à 5 niveaux hiérarchiques (la structure n'est pas complètent figée), mon arbre est plutôt un baobab . Du coup, je cherche un algorithme pour remplir une table telle que présentée dans l'article (Représentation intervallaire des arborescences).
Pour l'instant, comment je compte m'y prendre :
- Etape 1 : Export de l'arborescence vers un tableau Excel (via Power Query) => Pas de problème
- Etape 2 : Transformation du tableau pour mettre sous forme d'une table avec un identifiant (clé primaire) pour chaque dossier et fichier, et une colonne "parent" pour faire une autojointure de la table => A voir, ça peut être coton
- Etape 3 : Utiliser la procédure de SQLpro procédure de dérécursivation pour transformer la table modélisée par auto-référence en table en mode intervallaire.

Je me suis penché un peu à l'étape 3, malheureusement mes maigres connaissances en SQL ne me permettent pas de comprendre l'algorithme, je vise juste à bien le mettre en oeuvre. Pou l'instant, je n'y suis pas parvenu. J'ai copié collé le code dans MariaDB en mode console - j'ai juste changé le nom de la BDD "dbo" en "Parente", le nom de ma BDD - (après avoir changé le DELIMITER |), en terminant par | , j'ai une erreur de syntaxe retournée ERROR 1064 (42000). Peut-être y a-t-il eu des évolutions de syntaxe (le code date de 2004) ?

Autre petite question, voila la définition de ma table T_EMPLOYE_EMP

Une contrainte a été mise sur la colonne EMP_ID_CHEF : INT REFERENCES T_EMPLOYE_EMP (EMP_ID), mais je ne sais pas comment l'écrire. Ce n'est pas une contrainte de clé étrangère, s'agissant d'une seule et même table, non ?

N'hésitez pas si vous avez des suggestions s'il y a plus simple, merci par avance pour votre aide.

[escartefigue]

Une contrainte a été mise sur la colonne EMP_ID_CHEF : INT REFERENCES T_EMPLOYE_EMP (EMP_ID), mais je ne sais pas comment l'écrire. Ce n'est pas une contrainte de clé étrangère, s'agissant d'une seule et même table, non ?

Si, c'est tout à fait correct

Voici un exemple de lien hiérarchique modélisé avec Looping et le script correspondant ici décliné pour MariaDB :

Pièce jointe 601660

Code SQL :

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

1
2
3
4
5
6
7
8
9
CREATE TABLE PE_personne(
   PE_ident INT AUTO_INCREMENT,
   PE_nom VARCHAR(50) NOT NULL,
   PE_prenom VARCHAR(50) NOT NULL,
   PE_ddn DATE NOT NULL,
   PE_ident_chef INT,
   PRIMARY KEY(PE_ident),
   FOREIGN KEY(PE_ident_chef) REFERENCES PE_personne(PE_ident)
);

EDIT : la collection doit être rattachée à l'édition.

[escartefigue]

Un ouvrage peut être édité par un éditeur ou à compte d'auteur, par contre, la collection n'existe que chez un éditeur.
Il faut donc vérifier que la collection de parution est bien une collection d'un éditeur ayant édité l'ouvrage.
D'où la contrainte d'inclusion dans le modèle ci-dessous.

Concernant l'ISBN, je ne pense pas qu'une édition puisse avoir les deux formats.
Soit l'édition date d'avant 2007 et l'ISBN ne fait que 10 caractères, soit elle est plus récente et il en fait 13.
Aussi, un seul attribut de type char(13) suffit (pour des raisons de performances, on se gardera d'utiliser du varchar pour une donnée aussi courte et dont la longueur effective ne bouge pas).

Voici un extrait de MCD correspondant à la partie collection et édition

Pièce jointe 601661

[mango86]

Bonjour,

Merci escartefigue pour vos remarques. Je propose ci-dessous un nouvel MCD qui les prend en compte.

Un ouvrage peut être édité par un éditeur ou à compte d'auteur,

Si je comprends bien, un ouvrage édité à compte d'auteur est traduit dans le MCD par la cardinalité 0 de l'association EDITER UNE, 01 EDITION, ?

Pour créer une table intervallaire, je cherche à mettre en oeuvre la procédure de SQLpro procedure derecursivation mais sauf erreur de ma part, elle n'est pas compatible avec MariaDB.

Savez-vous comment faire pour la rendre compatible niveau syntaxe ?

Voici la description de table de départ (autoréférente) que j'ai créée, suite à l'ajout de la contrainte de clé étrangère sur EMP_ID_CHEF. J'ai inséré une cinquantaine de n-uplets pour tester.

Merci par avance

[escartefigue]

Si je comprends bien, un ouvrage édité à compte d'auteur est traduit dans le MCD par la cardinalité 0 de l'association EDITER UNE, 01 EDITION, ?

Tout à fait

Savez-vous comment faire pour la rendre compatible niveau syntaxe ?

Je ne vois pas ce qui cloche dans la syntaxe, y a -t-il un message d'erreur

Au passage, un datetime pour la date de naissance sera rarement utile, l'heure de naissance étant rarement connue et encore plus rarement utilisée, à vérifier dans votre cas.
Également, tant que vos noms d'objets, tables, colonnes et autres, n'ont pas de caractères spéciaux et ne sont pas des noms réservés SQL, vous pouvez vous affranchir des quotes inversées, elles sont inutiles

[mango86]

Bonjour escartefigue,

Également, tant que vos noms d'objets, tables, colonnes et autres, n'ont pas de caractères spéciaux et ne sont pas des noms réservés SQL, vous pouvez vous affranchir des quotes inversées, elles sont inutiles

En fait, je n'ai jamais inséré de quotes inversées, ma capture d'écran est ce que j'obtiens après la commande SHOW CREATE TABLE t_employe_emp;

Au passage, un datetime pour la date de naissance sera rarement utile, l'heure de naissance étant rarement connue et encore plus rarement utilisée, à vérifier dans votre cas.

Je travaille juste sur l'exemple donné ici, le type DATETIME a été mis à EMP_DATE_NAISSANCE :

CREATE TABLE dbo.T_EMPLOYE_EMP
(EMP_ID INT PRIMARY KEY,
EMP_ID_CHEF INT REFERENCES T_EMPLOYE_EMP (EMP_ID),
EMP_NOM VARCHAR(32) NOT NULL,
EMP_DATE_NAISSANCE DATETIME);

Je ne vois pas ce qui cloche dans la syntaxe, y a -t-il un message d'erreur

Oui, j'ai fait un copié collé de la procédure, après avoir changé le délimiteur au préalable (DELIMITER |), j'ai un message d'erreur de syntaxe (voir ci-dessous). Si vous avez des idées car je ne vois pas comment progresser.

Merci

[escartefigue]

il s'agit de SQL dynamique, pour débuger, il faut vérifier le contenu de chaque variable et afficher le contenu de la requête (il peut y avoir par exemple des erreurs lors de la concaténation, typiquement, des "nulls")

[mango86]

Je mets le sujet en résolu car je pense que mon MCD est abouti et que je peux passer à la suite. Pour la suite (MLD) ou pour la procédure de transformation en table intervallaire, j'ouvrirai un nouveau sujet, je pense que ça sera plus clair comme ça. Merci pour votre aide.

[escartefigue]

hum, en relisant, je me rends compte que j'ai fait une coquille dans ma réponse n°7.
Le pivot de la contrainte d'inclusion n'est pas l'éditeur, mais l'édition : on veut contrôler que l'ouvrage de la collection est bien associé à l'éditeur

Le schéma est donc
Pièce jointe 602061

Selon qu'on a choisi que l'association EI_editer devient ou non une table, on adaptera le script de la contrainte

[mango86]

Bonjour,

C'est noté, voici ci-dessous mon MCD mis-à-jour.



Merci

[escartefigue]

Comme expliqué dans l'autre discussion relative au chargement des tables ICI, identifier l'ouvrage relativement au type est certainement une erreur. À argumenter.

[mango86]

Bonjour escartefigue,

J'ai donc enlevé l'identifiant relatif de la cardinalité (1,1). Pour ce qui est des cardinalités en elles-mêmes, j'ai laissé (1,1) côté ouvrage car un ouvrage est d'un et seul type, et (1,n) côté type car un type donné peut être le "support" de 1 à n ouvrages.

Voici mon MCD mis-à-jour ci-dessous. J'ai ajouté l'attribut "nationalité" à auteur et "tome" à ouvrage pour les identifier les entités de manière unique.



Merci pour votre remarque - je me penche sur les transactions dès que possible.

[escartefigue]

Pour les typologies, il est recommandé de positionner une cardinalité minimale de zéro de la typologie vers la ou les entité-type rattachées

Par exemple : [TYPE] 0,n --- (typer) --- 1,1 [OUVRAGE]

En effet, même si à un instant "t" vous n'avez aucun ouvrage d'un certain type, ce type doit pouvoir subsister, ce qu'une cardinalité minimale de 1 interdit.

[mango86]

Bonjour,

Je me demande s'il y a lieu d'ajouter une ou plusieurs règles, car sauf erreur de ma part, les requêtes SQL de création de ma base de données (générées par Looping) ne prennent pas en compte la contrainte d'inclusion. Il faudrait transcrire le fait qu'un ouvrage paru dans une collection a forcément été édité dans une édition.

Dans l'affirmative, pouvez-vous me guider ou me dire comment les écrire ?

Merci par avance,

[escartefigue]

Bonjour,

Dans Looping, vous pouvez directement ajouter le script lié aux contraintes en double cliquant sur le symbole de la contrainte.
Voici un exemple :

Pièce jointe 603705

[mango86]

Bonjour escartefigue,

Merci pour votre réponse.

Pour ma base de données, comme l'entité PARAITRE DANS n'est pas transformée en table dans mon MLD, sauf erreur de ma part, je dois faire porter la contrainte de clé étrangère sur la colonne id_collection de COLLECTION.

La requête devient donc :

Code SQL :

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

1
2
3
4
ALTER TABLE COLLECTION
ADD CONSTRAINT COLLECTION_FK_001
FOREIGN KEY id_editeur
REFERENCES EDITION(id_editeur)

Pouvez-vous svp confirmer, est-ce bien correct ?

[escartefigue]

bonjour,

Si l'association ne devient pas une table c'est à cause de la cardinalité 0,1 coté [OUVRAGE]
Dans la vraie vie, un même ouvrage peut paraître dans plusieurs collections d'un même éditeur ou d'éditeurs différents.
C'est pourquoi j'avais mis 0,n dans l'extrait de MCD proposé plus haut.

Mais suis-je bête : l'éditeur n'est pas présent comme FK dans la table EO_edition puisque dans mon MCD j'ai activé la création de la table associative EI_editer
Et l'éditeur n'étant pas unique ici, on ne peut pas contrôler par contrainte FK cette cohérence. Il faudra donc créer un trigger.