¶ Conception d'une BD relationnelle
Un merci particulier au professeur Vincent Leduc pour les notes ci-dessous
¶ Avant propos
Veuillez vous créer un compte sour Erdplus.com
¶ Objectifs de ce tutoriel
1.Analysez des données et concevoir un diagramme Entité-Relation (Modèle Conceptuel des Données)
2.À l’aide de l’outil erdplus, créez un script de création de bases de données (Modèle Physique des Données)
3.Saisir des données avec une interface graphique (Workbench)
4.Distinguez, à partir de 3 cas d’étude, la présence ou non de relation entre des entités et les relations de type « 1 à plusieurs » ou « plusieurs à plusieurs » (cardinalité)
¶ Théorie
Une base de données relationnelle est une collection de tables avec des liens entre-elles. Tentez de répondre vous-même aux questions suivantes en analysant les données ci-dessous:
Questions
- Que représente ces données?
- Combien de tables?
- Quel carburant est consommé par le tracteur Kubota?
- Quelle(s) colonne(s) représente un lien entre les tables (relation) ?
- Est-ce qu’un véhicule peut utiliser plus d’un type de carburant?
- Est-ce qu’une auto peut avoir plus d’un propriétaire?
Une base de données relationnelle est une collection de tables avec des liens entre-elles.
¶ Modélisation d’une BD relationnelle
Il existe plusieurs modèles pour représenter une BD relationnelle. Voici quelques exemples:
Pour les besoins du cours, nous allonrs focuser sur 2 modèles:
- Le Modèle Conceptuel des Données (MCD): ce modèle, proche d’un diagramme UML, est utilisé par un architecte, à partir de données, pour identifier comment l’information peut être organisée sous forme d’entités avec leurs propriétés (ou attributs) et de relations entre ces entités.
- Le Modèle Physique Des Données (MPD): ce modèle, directement exploitable par un logiciel informatique (SGBDR), est créé à partir d’un MCD, par un développeur SQL qui code des objets SQL, essentiellement des tables avec des colonnes caractérisées par des types (exemple: int date float).
Voici un exemple généré avecERDPlus:
Prenons une autre exemple de MCD. Répondez aux questions suivantes:
- Combien d’entités?
- Quelles sont les propriétés/attributs?
- Les relations?
¶ Équivalence MCD / MPD
Le modèle logique des données MCD identifie principalement 3 notions :
1.Une entité et ses attributs qui se traduit dans un MPD par une table et des colonnes dont les valeurs sont strictement typées.
2.Une relation entre 2 entités qui se traduit soit par une colonne de type clef étrangère dans une table (cas d’une relation de cardinalité « 1 à plusieurs ») soit ou par une table (cas d’une relation de cardinalité « plusieurs à plusieurs »)
| MCD | MPD |
| Entité | Table |
| Propriété ou attribut | Champ ou colonne (typé) |
| Relation (cardinalité) |
Clef étrangère (si 1 à plusieurs) Table (si plusieurs à plusieurs) |
¶ Étapes de modélisation d’une base relationnelle
- Analyser les données pour recenser les entités, attributs et relations
- Établir le Modèle Conceptuel des Données (ERDPlus – ER Diagram)
- Générer Le Modèle Physique des Données (ERDPlus – Relational schema)
- Ajuster/Optimiser le modèle relationnel (ERDPlus - MPD - facultatif)
- Créer la BD (SGBDR – script SQL) puis saisir les données (Workbench)
¶ Exemple d'étude de cas
¶ Études de cas avec 1 entité
Soit la table suivante:
¶ Étape 1 - Analyser les données
- Combien d’entité(s) et d’attributs?
- Relation(s)?
¶ Étape 2 Créer un MCD dans ERDplus
¶ Étape 3 - Générer le schéma relationnel dans ERDplus
Dans la liste des documents, accéder aux options de votre diagramme ER puis cliquer sur Convert to relational Schema.
¶ Étape 4 - Ajuster/Optimiser le schéma relationnel dans ERDplus
•Type d’un champ (Data Type)
•Taille d’un champ (Data Type Size)
¶ Étape 5.1 - Créer un script SQL à partir d’un diagramme ER
Sélectionner un document ER Diagram et générer le script SQL
Copier le script
¶ Étape 5.2 Intégration dans Workbench
Créer une nouvelle BD Automobile dans MySQL et insérer le code pour la table Véhicule
Rouler le script et voici le résultat:
¶ Études de cas avec 2 entités
Refaire l'exercice mais maintenant avec 2 entités pour la table et l'énoncé suivant:
Un vendeur peut vendre un véhicule
¶ Étape 1 - Analyser les données
- Combien d’entité(s) et d’attributs? Nous avons 2 entités (véhicule et vendeur) avec les attrivbuts suivants:
- Relation(s)? Une relation est un verbe qui lie 2 entités. Dans notre cas, le verbe est VEND
•Ajouter une relation (bouton Relationship
puis cliquer dans la surface du diagramme)
•Nommer-la Vend
•Sélectionner les 2 entités en relation
(Entity One – Entity two)
¶ Cardinalité des relations
Il s’agit de déterminer le minimum et le maximum d’occurrences de chaque entité impliquée dans la relation. Cela se formule ainsi:
- Un véhicule est relié au minimum à combien de vendeur(s)? 1. Dans notre bd, cela veut dire qu’il est donc obligatoire « Mandatory » qu’un véhicule soit relié à un vendeur
- Un véhicule est relié au maximum à combien de type de vendeur(s)? 1 « One ».
- Un vendeur est relié au minimum à combien de véhicule(s)? 0 « Optional ». Dans notre bd, un vendeur pourrait être conservé alors qu’il n’a pas encore de véhicule à vendre (ou bien qu’elles soient toutes vendues).
- Un vendeur est relié au maximum à combien de véhicule(s)? N soit « Many »
¶ 2 entités et 1 relation
Sélectionner une relation
Pour la première entité, sélectionner la cardinalité minimale (Mandatory pour 1 ou Optional pour 0), en répondant à la question suivante ; est ce que l’entité 1 doit absolument avoir une relation avec la seconde entité (i.e le véhicule a au moins un propriétaire?) ou cette relation est optionnelle (i.e un véhicule peut ne pas avoir de propriétaire?)
Puis, sélectionner la cardinalité maximale (One pour 1 ou Many pour N) ; est ce que l’entité 1 peut être en relation avec plusieurs entités 2 ou au plus une seule (i.e le véhicule peut-il avoir plusieurs propriétaires ou pas plus d’un seul propriétaire?)
Répéter l’étape précédente pour déterminer les cardinalités de la seconde entité
Nous devrions arriver au schéma suivant:
Les étapes suivantes seront:
- Étape 3 - Générer le schéma relationnel MPD dans ERDplus
- Étape 4 - Ajuster/Optimiser le type des colonnes du MPD dans ERDplus
- Étape 5.1 - Générer le script SQL à partir du MPD dans ERDplus
- Étape 5.2 - Créer une nouvelle BD Vehicule2 dans Workbench
- Étape 5.3 - Sélectionner la bd crée en double cliquant sur son nom puis coller le script SQL dans une fenêtre de requête et exécuter
- Étape 5.4 - Saisir les données; commencer par saisir les données de l’entité secondaire (Vendeur) car elle est utilisée par une clef étrangère dans l’entité principale Vehicule
¶ Résumé de la modélisation
La modélisation ou conception de‘une base de données consiste à décrire une information sous sa forme logique (MCD) puis de la transformer en une base de données physique (fichier sous MuSQL par exemple) MPD
| Modèle Conceptuel des Données - MCD | Modèle Physique des Données – MPD |
| Entité | Table |
| Relation ou association | Table ou clef étrangère |
| Cardinalité d‘une relation (0,1) (1,1) (0,N) (1,N) | |
| Propriété/Attribut | Champ (Colonne d‘une table) |
| Identifiant | Clef primaire |
| Pas de données | Enregistrement (rangée d‘une table) |
| ERDPlus | MySql Workbench |
¶ Études de cas avec 3 entités
Comme dans le cas précédent, un véhicule est identifié par son numéro d’immatriculation et son vendeur par son adresse courriel.
On souhaite entrer une seule fois dans la base de données la liste des catégories qu’il pourrait utiliser pour chercher un certain type de véhicule. Il y en a 5 au total; VUS, Camionnette, berline, Écoresponsable et Luxe.
Cette base de données doit permettre de regrouper un véhicule dans plusieurs catégories (ex; la Toyota Prius est dans la catégorie Berline et la catégorie Écoresponsable).
Une catégorie peut ne pas avoir de véhicules (c’est le cas de la catégorie Luxe).
Un véhicule cependant, est catégorisé au moins par une des 5 catégories.
Utilisez le script fourni pour créer la base Vehicule_3 et ses tables puis saisissez les données pour reproduire ces écrans
