Activité Du modèle logique au modèle physique

Consigne

Avant de commencer, veuillez lire une première fois le document dans son ensemble.

Pour ce travail, on vous demande dans un premier temps de suivre les instructions de la section « Mise en route ». Celle-ci vous guide d’abord dans l’installation des outils dont vous aurez besoin, puis vous permet de vous familiariser avec ces outils, et d’avoir un premier contact avec le SQL.

Dans un second temps, il s’agit de réaliser vous-même le travail décrit dans le point 7.

Le travail est individuel. Vous pouvez communiquer en respectant le code d’honneur. Si vous être bloqué, demandez de l’aide afin de ne pas prendre trop de retard.

Situation

Vous travaillez pour une entreprise appelée “TechSolutions” qui souhaite mettre en place une nouvelle base de données pour gérer les informations relatives à ses employés et aux projets auxquels ils participent. Vous avez déjà réalisé le modèle conceptuel et l’avez présenté sous la forme d’un diagramme entité-association (figure 1).

Objectifs

À la fin de ce travail, vous devez :

• Être capable de passer d’un modèle conceptuel des données (MCD) à un modèle logique (MLD), puis à un modèle physique (MPD).
• Connaître les notions de clés primaires et de clés étrangères.
• Être capable de se connecter à un serveur de base de données.
• Être capable de créer et supprimer une base de données en SQL.
• Être capable de créer et supprimer une table avec ses attributs en SQL.
• Être capable de décrire les contraintes de clé primaire et de clé étrangère en SQL.

Résultat attendu

Un bref rapport (environ 2 à 3 pages) décrivant le processus suivi, la description des commandes SQL utilisées, les défis rencontrés, et la manière dont ils ont été surmontés.

Ressources

Logiciel :

• Un serveur MySQL : svr-m164-##-mysql.lab.epai-ict.ch où ## est le numéro du réseau qui se trouve dans les informations d’identification que vous avez reçue. Le serveur est accessible dans le réseau des lab en utilisant les stations d’accueil. En dehors des lab, le serveur est accessible en utilisant le VPN.

• Visual Studio Code

Documents :

• Présentation : Le modèle physique des données

Mise en route

Installer un gestionnaire de paquet

Pour commencer, vous devez vous assurer que vous disposer d’un gestionnaire de paquet. Si vous êtes sous Linux, vous n’avez rien à faire.

Si vous êtes sous Windows ou sous macos, utilisez les liens suivants :

• Installer Chocolatey sous Windows
• Installer Homebrew sous macos

Installer le client MariaDB/MySQL

Vous pouvez maintenant installer le client MariaDB ou MySQL sur votre machine.

Sous Windows, ouvrez une fenêtre PowerShell avec des droits administeur, puis taper la commande suivante :

1
choco install mysql-cli

Sous macos, ouvrez une fenêtre de terminal et tapez la commande suivante :

1
brew install mysql-client

Lorsque l’installation est terminée, ouvrez une fenêtre de terminal (sans privilèges d’administrateur) et lancez la commande mysql --version. La commande doit renvoyer un message qui indique la version du client mysql que vous utilisez.

installez l’extension Database Client

L’extension Database Client de Visual Studio Code (vscode) permet de gérer, entre autres, des serveurs MySQL ou MariaDB.

Pour l’installer, ouvrez vscode et cliquez sur l’icône des extensions dans la barre d’activité, tout à gauche, et tapez le mot clé « mysql » dans la zone de recherche, sélectionnez l’extension « MySQL » de Weijan Chen, et cliquez « Installer ».

Lorsque l’extension est installée, cliquez sur l’icône Database dans la barre d’activité, puis sur le bouton « Create connection ».

Dans le formulaire qui apparait, saisissez le nom de la connexion (m164), sélectionnez « MariaDB », puis saisissez vos informations d’identification comme dans la figure 4. Dans le nom de l’hôte (Host), tapez svr-m164-##-mysql.lab.epai-ict.ch en remplaçait les signes ## par le numéro de réseau qui se trouve dans les informations d’identification que vous avez reçu. Ce numéro doit être écrit sur deux ou trois chiffres, exactement comme il est écrit sur la fiche d’information que vous avez reçue. Sur cette fiche, vous trouvez également le nom d’utilisateur pour le serveur MariaDB (admin) et le mot de passe. Enfin, cliquez sur le bouton « Save » pour enregistrer vos données.

Si les informations que vous avez saisies sont correctes, vous deviez pouvoir ouvrir la connexion vers votre serveur en pressant le bouton « Connect ».

Utiliser l’interpréteur de commande de MariaDB

Maintenant que vous êtes connecté au serveur, vous pouvez ouvrir une fenêtre de terminal pour utiliser l’interpréteur de commande de MariaDB.

Pour cela, placez le pointeur de la souris sur l’icône terminale sur la connexion active, comme dans la figure ci-dessous, et cliquez.

Cela devrait provoquer l’ouverture d’une fenêtre avec l’interpréteur de commande de MariaDB, comme dans la figure ci-dessous.

Si tout s’est bien passé, l’interpréteur de commande est maintenant prêt à recevoir vos ordres. Commencez par lui demander d’afficher les bases de données qui se trouvent dans ce serveur, en tapant la commande SHOW DATABASES; et en pressant la touche entrée (⏎).

Vous constatez que votre serveur contient déjà quatre bases de données : information_schema, mysql, performance_schema et sys. Ces bases de données sont des bases de données « systèmes ». Elles sont utilisées par le serveur SQL et peuvent être interrogées pour obtenir des informations sur l’état du serveur et les objets (base de données, tables, index, etc.) qu’il contient.

Essayons maintenant de créer une base de données. Pour cela, tapez la commande suivante :

1
CREATE DATABASE test_db DEFAULT CHARACTER SET = "utf8mb4";

Si la commande a été exécutée avec succès, il devrait maintenant y avoir une base de données de plus dans le serveur (si vous le souhaitez, vous pouvez vous en assurer en utilisant la commande SHOW DATABASES;). Rafraichissez la connexion pour voir apparaitre la nouvelle base de données dans la fenêtre « Database » (voir figure 10).

Question : Que se passe-t-il si vous relancez la même commande ?

Nous allons maintenant créer une table. La commande qui permet de créer une table s’écrit généralement sur plusieurs lignes. Cela ne pose pas de problème en SQL. En effet, la fin d’une commande n’est pas un retour à la ligne, mais un point-virgule (;) comme en Java. Vous pouvez donc presser la touche entrée () pour revenir à la ligne et poursuivre la saisie de la commande. Tapez les lignes suivantes et lancez la commande en pressant la touche entrée encore une fois après le point-virgule.

1
2
3
4
5
6
CREATE TABLE product (
    product_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(2000),
    PRIMARY KEY (product_id)
);

Oups ! la commande a échoué. Le message d’erreur vous indique qu’il n’y a pas de base de données sélectionnée. En effet, avant de créer des tables, il faut indiquer au serveur dans quelle base de données vous souhaitez les créer.

Sélectionnez la base de données test_db, avec la commande USE test_db;, et réessayez. Cette fois, la commande devrait fonctionner. Si c’est le cas, vous pouvez rafraichir la connexion et ouvrir la vue arborescente pour voir la table que vous venez créer comme le montre la figure 12.

Si vous avez fait une erreur, vous pouvez supprimer une table avec la commande DROP TABLEsuivie du nom de la table. Par exemple, DROP TABLE product;. Vous pouvez également supprimer la base de données en entier avec la commande DROP DATABASEsuivie du nom de la table. Par exemple, DROP DATABASE test_db;. De manière générale, en SQL, le mot DROPest utilisé pour supprimer un élément de la structure, et le mot DELETE, pour supprimer des données.

Supprimez la table productet la base de données test_db, puis reprenez le point 6.2 du début pour vous exercer.

Si vous le souhaitez, avant de commencer votre travail, vous pouvez encore vous exercer avec les trois commandes suivantes :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    street_name VARCHAR(50),
    city VARCHAR(50),
    postal_code VARCHAR(20)
);

CREATE TABLE customer_order (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATETIME NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

CREATE TABLE order_product (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES customer_order(order_id),
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

Vous voilà fin prêt pour voler de vos propres ails et créer une nouvelle base de données par vous-même.

À vous de jouer !

Tâches

1. Convertissez le diagramme entité-association du modèle conceptuel des données (MCD) en un modèle logique des données (MLD) sous la forme d’un schéma relationnel comme nous l’avons fait dans le module 164.

2. Réalisez le modèle physique des données en décrivant les tables, les attributs et les contraintes (clés primaires et clés étrangères) de votre schéma en SQL dans un fichier de texte avec l’extension « .sql ».

3. Dans l’interpréteur de commande de MariaDB

   • Créez une nouvelle base de données appelée « project_management_db » dans le serveur.

   • Sélectionner la base de données « project_management_db ».

   • Créer les tables une à une.

Attention : L’ordre dans lequel vous créez les tables est important. Pour créer une contrainte de clé étrangère, vous devez déjà avoir créé la table et l’attribut auquel elle fait référence.