Retour à la page précédente.

Du modèle conceptuel à la base de données

Un exemple de modèle conceptuel

Exemple de modèle conceptuel

Ce modèle conceptuel permet de traiter (de manière relativement simple mais efficace) un questionnement relevant de la méthode proposopographique, comprenant ces aspects:

dater et localiser les naissances des personnes (afin de les afficher sur une carte)

représenter les liens familiaux: parents, fratrie, jumeaux, etc.

gérer les différentes appellations des personnes

classifier les personnes avec une classification définie par l'utilisateur (tags)

traiter les métiers et autres occupations des personnes, leur évolution dans le temps et pouvoir les cartographier

traiter les spécialisation des métiers et leur appartenances à différents domaines.

Sur le modèle conceptuel on reconnaît les classes avec leurs propriétés et les relations (orientées) entre les classes avec leurs cardinalités.

Le modèle logique ou relationnel

Dans le cours d'introduction aux bases de données, Laurent Audibert présente les règles de passage du modèle conceptuel au modèle logique ou relationnel.
A noter que une classe (UML) correspond à une entité-type (Merise).

Les principales règles:

on crée une table ou relation pour chaque classe

le nom de la table sera sans espaces et sans accents, uniquement avec des lettres en minuscule

on attribue un identifiant unique à chaque classe

cet identifiant s'appelle clé primaire, primary key

par convention nous choisissons de nommer cet identifiant avec le nom de la table, précédé de 'pk_', par ex. pk_person pour la table ou relation person.

les relations de '1 à n' sont exprimées par un report de la clé primaire de la table/classe du côté '1' vers la table/classe du côté 'n'

la clé primaire (primary key) de la table du côté '1' de vient une clé étrangère (foreign key) dans la table du côté 'n'

par convention, les clés primaires sont préfixées par 'fk_'

les relations de 'n à n' sont exprimées par la création d'une nouvelle table ou relation.

cette table établit le lien entre les deux classes et exprime leur relation de 'n à n'

les clés primaires (primary key) de chaque table sont reportées et deviennent des clés étrangères (foreign key) dans la table relation

la table relation a le nom de la relation – celui-ci doit donc être unique dans le modèle conceptuel

pour des raisons pratiques nous ajoutons une clé primaire spécifique pour la table relation sous la forme 'pk_' + nom de la table, par ex. pk_specializes_tag pour la table specializes_tag.

Si on applique ces réglès au modèle conceptuel de l'exemple, on obient ces relations (les clés primaires sont en italique, les clés étrangères sont soulignées):

person(pk_person, name, definition, gender, death_date, fk_birth, notes)

appellation(pk_appellation, pk_person, name, sources, notes)

tag(pk_tag, fk_parent_tag, name, definition, notes)

tags(pk_tags, fk_person, fk_tag, notes)

pursuit(pk_pursuit, fk_person, fk_occupation, fk_organisation, begin_date, end_date, sources, notes)

organisation(pk_organisation, fk_geographical_place, name, definition, notes)

occupation(pk_occupation, name, definition, notes)

specializes_occupation(pk_specializes_occupation, fk_parent_occupation, fk_child_occupation, notes)

birth(pk_birth, date, fk_geographical_place, fk_union, sources, notes)

union(pk_union, fk_union_type, begin_date, end_date, fk_person_1, fk_person_2, sources, notes)

union_type(pk_union_type, name, definition, notes)

geographical_place(pk_geographical_place, name, definition, longitude,latitude, fk_geographical_place_type, notes)

geographical_place_type(pk_geographical_place_type, name, definition, fk_parent_geographical_place_type, notes)

Création de la base de données SQLite qui implémente le modèle logique ou relationnel

Après avoir défini le modèle logique ou relationnel, on passe à la réalisation physique de la base de données, à son implémentation avec le logiciel SQLite.

Pour ce faire nous allons utiliser le client graphique SQLite Studio (cf. cette page).
Les instructions qui suivent permettent de guider l'utilisateur dans les tout premiers pas et elles ne remplacent pas le manuel d'utilisation est disponible sur cette page qu'on doit avoir toujours sous la main.

Création de la base de données

Instructions:

créer un dossier (répertoire) dans le dossier dédié au cours et l'appeler 'bases_sqlite' (sous-entendu bases de données sqlite) qui va contenir une ou plusieurs bases de données

ouvrir le logiciel SQLiteStudio

dans le menu Database ou avec le bouton correspondant activer la commande 'Add a database'

choisir le type par dévaut SQLite3

appuyer sur le bouton '+' en vert

entrer un nom pour la base de données (sans espaces, seulement avec des '_' entre les paroles, et sans accents), par ex. prosopographie

naviguer vers le dossier 'bases_sqlite' précédemment créé.

appuyer sur le bouton 'Enregistrer'

on revient à la boîte de dialogue 'Database', on lit le chemin vers le fichier SQLite et son nom, prosopographie.db, et on valide avec le bounton 'OK'.

La nouvelle base de données s'affiche dans la liste des bases de données à gauche. Appuyer deux fois sur elle et l'ouvrir.

Ajouter de nouvelles tables

Instructions:

commencer par ajouter des tables qui n'ont pas de clés étrangères, par ex. 'geographical_place_type'

click droit sur 'Tables' à gauche dans la liste des objets de la base de données et 'Create a table'

insérer le nom de la table

ajouter des colonnes à partir du modèle logique ou relationnel avec le bouton ajout de colonne (ligne verte au milieu), clicker pour ajouter chaque colonne:

pk_geographical_place_type, data type: INTEGER, click sur case Primary key car ce sera la clé primaire de la table.

Une colonne clé primaire de type entier (integer) sera autoincrémentée de 1 à n

Pour des tables avec un très grand nombre de lignes utiliser le type de valeur BIGINT

name, data type: VARCHAR (caractères de longueur variable, utilisable pour les labels et noms), Size: 150 (la saisie sera limitée à 150 caractères

definition, data type: TEXT

IMPORTANT : sauvegarder la table en appuyant sur le bouton vert en forme de 'V'

apparaît alors l'instruction SQL qui sera envoyée à la base de données et que vous pourriez aussi envoyer directement pour créer de nouvelles tables en la collant et exécutant dans 'Tools > Open SQL Editor'

appuyer sur 'OK'

créer la table 'geographical_place' avec la même méthode

après avoir créé clé primaire, nom et descriptif, ajouter:

les champs longitude et latitude qui seront de type NUMERIC

ajouté la clé étrangère vers la table 'geographical_place_type'

la colonne s'appellera 'fk_geographical_place_type'

elle sera de type INTEGER

on coche ensuite Foreign Key et on ouvre 'Configure'

on choisit la 'Foreign table' : geographical_place_type

on choisit la 'Foreign column': 'pk_geographical_place_type'

on coche MATCH ce qui va introduire un contrôle automatique

on applique: 'Apply'

on crée la colonne

on n'oublie pas d'enregistrer la nouvelle table !

On procède ainsi pour toutes les autres tables.

A noter que on ne pourra pas créer la table 'person' ou 'birth' directement avec toutes les clé étrangères, car 'birth' dépend de 'union' qui dépend à son tour de 'person'.

on créer d'abord la table 'person'

on ajoute la colonne INTEGER pour la clé étrangère vers 'birth' qui sera appelée 'fk_birth' et on ne renseigne pas le lien vers la clé primaire

on enregistre la table 'person'

on crée ensuite 'union', puis 'birth'

enfin on va modifier la table 'person' (click droit sur la table et 'Edit the table'), modifier la colonne (double-click sur la colonne) et on ajoutera la référence de la clé étrangère vers la table 'birth'