Activité : Schéma relationnel de base de données

  • Thierry Vaira <>

  • 14/04/2018 (rev. 1)

Schéma relationnel de base de données

Expression du besoin

Produire un schéma relationnel sous la forme d’un diagramme en reverse engineering (rétro conception) soit à partir d’une base de données soit à partir d’un fichier SQL.

La notation UML permet aussi de modéliser un schéma relationnel : le diagramme de classes représentant un ensemble de tables. Pour préciser qu’une classe représentera une table, on utilise le stéréotype <<table>>. La classe contient des attributs. On peut relier plusieurs classes entre elles en prenant garde d’insérer convenablement les clés étrangères.

Voir aussi :

Rappels - Notions de base

Une base de données (database) est un « conteneur » permettant de stocker et de retrouver l’intégralité de données brutes ou d’informations. Dans la très grande majorité des cas, ces informations sont très structurées, et la base est localisée dans un même lieu et sur un même support.

Une base de données relationnelle est une base de données où l’information est organisée dans des tableaux à deux dimensions appelés des relations ou tables. Les lignes de ces relations sont appelées des nuplets (tuples) ou enregistrements. Les noms des colonnes (ou champs) sont appelées des attributs.

Le modèle relationnel prévoit trois types de relations entre tables :

  • 1:1 (un enregistrement de la table A se rapporte seulement à un enregistrement de la table B),
  • 1:N (un enregistrement de la table A se rapporte à un ou plusieurs enregistrements de la table B), et
  • N:N (un enregistrement de la table A se rapporte à un ou plusieurs enregistrements de la table B et un enregistrement de la table B se rapporte à un ou plusieurs enregistrements de la table A. Une relation N:N peut donc être décomposées en deux relations 1:N.).

Exemples :

  • 1:1 (un directeur dirige une école et une école ne comporte qu’un seul directeur : la table « Écoles » est en relation 1:1 avec la table « Directeurs »),
  • 1:N (un seul membre de la table « Classes » peut se rapporter à plusieurs membres de la table « Élèves »), et
  • N:N (une classe a plusieurs professeurs et un professeur peut être responsable de plusieurs classes : les tables « Classes » et « Professeurs » sont en relation N:N).

Les logiciels qui permettent de créer, utiliser et maintenir des bases de données relationnelles sont des système de gestion de base de données relationnels (SGBDR).

Pratiquement tous les systèmes relationnels utilisent le langage SQL (Structured Query Language) pour interroger les bases de données. Ce langage permet permet de rechercher, d’ajouter, de modifier ou de supprimer des données dans les bases de données relationnelles.

Exemple :

MySQL est un système de gestion de bases de données relationnelles (SGBDR). Il est distribué sous une double licence GPL et propriétaire. Il fait partie des logiciels de gestion de base de données les plus utilisés au monde, autant par le grand public (applications web principalement) que par des professionnels, en concurrence avec Oracle, Informix et Microsoft SQL Server. MySQL est un serveur de bases de données relationnelles SQL. Il est multi-thread et multi-utilisateur. MySQL fonctionne sur de nombreux systèmes d’exploitation différents, incluant Linux, Mac OS X et Windows. Les bases de données sont accessibles en utilisant les langages de programmation C, C++, VB, VB .NET, C#, Delphi/Kylix, Eiffel, Java, Perl, PHP, Python, Windev, Ruby et Tcl. Une API spécifique est disponible pour chacun d’entre eux. MySQL fait partie du quatuor LAMP : Linux, Apache, MySQL, PHP. Il appartient également à ses variantes WAMP (Windows) et MAMP (Mac OS).

SQLite est une bibliothèque écrite en C qui propose un moteur de base de données relationnelle accessible par le langage SQL. Contrairement aux serveurs de bases de données traditionnels, comme MySQL ou PostgreSQL, sa particularité est de ne pas reproduire le schéma habituel client-serveur mais d’être directement intégrée aux programmes. L’intégralité de la base de données (déclarations, tables, index et données) est stockée dans un fichier indépendant de la plateforme. SQLite est le moteur de base de données le plus distribué au monde, grâce à son utilisation dans de nombreux logiciels grand public comme Firefox, Skype, Google Gears, dans certains produits d’Apple, d’Adobe et de McAfee et dans les bibliothèques standards de nombreux langages comme PHP ou Python. De par son extrême légèreté (moins de 300 Kio), il est également très populaire sur les systèmes embarqués, notamment sur la plupart des smartphones modernes : l’iPhone ainsi que les systèmes d’exploitation mobiles Symbian et Android l’utilisent comme base de données embarquée.

Fichier SQL de l’exemple

-- -----------------------------------------------------
-- Table `QCM`.`liste`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `QCM`.`liste` (
  `idQCM` INT(11) NOT NULL AUTO_INCREMENT ,
  `libelle` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`idQCM`) );

-- -----------------------------------------------------
-- Table `QCM`.`questions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `QCM`.`questions` (
  `idQuestion` INT(11) NOT NULL AUTO_INCREMENT ,
  `libelle` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`idQuestion`) );

-- -----------------------------------------------------
-- Table `QCM`.`propositions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `QCM`.`propositions` (
  `idProposition` INT(11) NOT NULL AUTO_INCREMENT ,
  `idQuestion` INT(11) NOT NULL ,
  `libelle` VARCHAR(255) NOT NULL ,
  `juste` TINYINT(1) NOT NULL ,
  PRIMARY KEY (`idProposition`, `idQuestion`) ,
  INDEX `idQuestion` (`idQuestion` ASC) ,
  CONSTRAINT `fk_questions_id1`
    FOREIGN KEY (`idQuestion` )
    REFERENCES `QCM`.`questions` (`idQuestion` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

-- -----------------------------------------------------
-- Table `QCM`.`qcm`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `QCM`.`qcm` (
  `idQCM` INT(11) NOT NULL ,
  `idQuestion` INT(11) NOT NULL ,
  `points` DOUBLE NOT NULL ,
  PRIMARY KEY (`idQCM`, `idQuestion`) ,
  INDEX `idQCM` (`idQCM` ASC) ,
  INDEX `idQuestion` (`idQuestion` ASC) ,
  CONSTRAINT `fk_liste_id`
    FOREIGN KEY (`idQCM` )
    REFERENCES `QCM`.`liste` (`idQCM` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_questions_id`
    FOREIGN KEY (`idQuestion` )
    REFERENCES `QCM`.`questions` (`idQuestion` )
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

Fichier SQL : qcm.sql

Les outils

PhpMyAdmin

Il est déjà possible d’obtenir un schéma à partir de l’onglet Concepteur de PhpMyAdmin :

MySQL Workbench

MySQL Workbench est un logiciel de gestion et d’administration de bases de données MySQL. Via une interface graphique, il permet, entre autres, de créer, modifier ou supprimer des tables, des comptes utilisateurs, et d’effectuer toutes les opérations inhérentes à la gestion d’une base de données. Pour ce faire, il doit être connecté à un serveur MySQL. Les tables sont disponibles sous forme de diagramme, permettant ainsi la modélisation des données.

Il est disponible pour les environnements Windows, Mac OS X, GNU/Linux.

Sous Ubuntu 12.04 LTS :

$ sudo apt-get install mysql-workbench

$ mysql-workbench &

On va procéder à la rétro conception d’une base de données MySQL QCM :

On se connecte à notre serveur de base dedonnées :

On sélectionne ensuite la base de données :

On termine par une suite de Next …

On obtient un diagramme pour notre base de données que l’on peut déjà exporter sous la forme d’une image PNG :

MySQL Workbench propose quelques options pour personnaliser le diagramme :

  • Object Notation Classic :
  • Relationship Notation Connect to Columns :
  • Relationship Notation UML :

Bouml

BOUML est un logiciel de création de diagrammes UML (programmé en C++ et Qt). Auparavant distribué gratuitement sous licence GPL, puis commercialisé pour les versions 5.0 à 6.12, il est de nouveau distribué gratuitement depuis la version 7.0 (mais pas intégralement sous licence GPL). Il est multilingue, supporte la génération de code et la rétro-ingénierie.

Avec la version 7.4 de BOUML, il est possible d’effectuer une rétro conception à partir d’un fichier SQL.

On sélectionne le langage MySQL :

Puis on lance un reverse engineering en choisissant un fichier SQL :

Et on obtient :

On crée ensuite un diagramme de classes et on y place les différentes tables :

À partir des options de dessin, il est possible de faire apparaître la définition complète des membres (pour avoir les types) :

Par contre, il n’y a pas les multiplictés (cardinalité) qu’il faudra éditer manuellement :

Remarque : BOUML semble fonctionner aussi avec un fichier SQL pour les base de données SQLite.

Export de fichier SQL

  • Pour MySQL :
# pour la base de données QCM
$ mysqldump -u root -ppassword --skip-extended-insert --compact QCM > qcm.sql

Ou utiliser PhpMyAdmin.

  • Pour SQLite :
# pour une base de données QCM
$ sqlite3 QCM.db .dump > qcm.sql

Ou utiliser les programmes sqliteman, sqlitebrowser ou le plugin SQLite Manager de Firefox.

Conversion MySQL - SQLite

$ ./mysql2sqlite qcm.sql | sqlite3 qcm.db
$ ./sqlite3-to-mysql qcm-sqlite.sql > qcm-mysql.sql

Ou le script sqlite-to-mysql.

Retour au sommaire

© 2018 Thierry Vaira