[Diaporama] TNSI - Modèle relationnel

Modèle relationnel ================== <style> .reveal ol, .reveal dl, .reveal ul { display: block; text-align: left; margin: 0 0 0 1em; } table { margin: 1em auto!important; } </style> <img class="centre image-responsive" src="https://upload.wikimedia.org/wikipedia/en/5/58/Edgar_F_Codd.jpg" alt="Edgar Codd"> <p class="legende"> <small> <strong>Edgar F. Codd</strong> <br>Source : <a href="https://upload.wikimedia.org/wikipedia/en/5/58/Edgar_F_Codd.jpg" target="_blank">wikimedia.org</a> </small> </p> <div markdown="1"> <small> *Ce document a été créé en collaboration avec Claude AI (modèle Claude Sonnet 3.5, version du 10/09/2024), qui a été utilisé pour transposer le cours correspondant (accessible [ici](modele-relationnel)) en un diaporama. Spoile : Claude a été plutôt mauvais. Pour voir la conversation [cliquez ici](https://aiarchives.org/id/1NLhr9D6XuPDVRDItR51-p).* </small> </div> --- # Introduction ---- ## Introduction - Traitements informatiques actuels : nécessité de gérer des données de plus en plus nombreuses - Limitations des fichiers CSV pour les gros volumes de données car : * le volume des données est gigantesque * les requêtes peuvent être (très) complexes * les données peuvent être utilisées simultanément par différents programmes et utilisateurs > Comment gérer (mémoriser et traiter) un ensemble volumineux de données ? ---- ## Introduction  ---- ## Introduction - **Solution** : utilisation de **bases de données *relationnelles*** - SGBD (Système de Gestion de Base de Données) = logiciel pour interagir avec une base de données (nécessaire) --- # Modèle relationnel ---- ## Modèle relationnel <img class="r-stretch centre image-responsive" src="https://upload.wikimedia.org/wikipedia/en/5/58/Edgar_F_Codd.jpg" alt="Edgar Codd"> <p class="legende"> <small> <strong>Edgar F. Codd</strong> <br>Source : <a href="https://upload.wikimedia.org/wikipedia/en/5/58/Edgar_F_Codd.jpg" target="_blank">wikimedia.org</a> </small> </p> - Défini en 1970 par Edgar F. Codd (prix Turing 1981) dans ses travaux chez IBM - Basé sur des concepts mathématiques - Modélise les **relations** entre informations - Indépendant de toute considération informatique ---- ## Relation, attribut, domaine, schéma <img class="centre image-responsive" src="data/vinyl.svg" alt="diagramme" width="200"> **Exemple** : Un disquaire qui permet d'emprunter des albums de musique. * L'ensemble de ses albums peut être représenté par l'ensemble : ``` Album = { ("I Still Do", "Eric Clapton", 2016, Vrai), ("Axis: Bold as Love", "Jimi Hendrix", 1967, Faux), ("Continumm", "John Mayer", 2006, Faux), ... } ``` ---- ### Relation, attribut, domaine, schéma (suite) ``` Album = { ("I Still Do", "Eric Clapton", 2016, Vrai), ("Axis: Bold as Love", "Jimi Hendrix", 1967, Faux), ("Continumm", "John Mayer", 2006, Faux), ... } ``` - Cet ensemble représente une **relation** (la relation *Album*) - Chaque élément (ligne) s'appelle un **enregistrement** - Les composantes de chaque enregistrement sont identiques, on parle des **attributs** de la relation. Ici, il y en 4 : * `titre` : titre de l'album * `artiste` : le ou les artistes de l'album * `annee` : année de parution de l'album * `dispo` : disponibilité de l'album ---- ### Relation, attribut, domaine, schéma (suite) <div style="font-size:0.85em"> ``` Album = { ("I Still Do", "Eric Clapton", 2016, Vrai), ("Axis: Bold as Love", "Jimi Hendrix", 1967, Faux), ("Continuum", "John Mayer", 2006, Faux), ... } ``` </div> * Chaque attribut possède un **domaine** (= son *type*) : INT, BOOL, TEXT, etc. * Une relation est représentée par son **schéma** = description indiquant pour chaque attribut son *nom* et son *domaine* * Exemple : le schéma de la relation *Album* est : ``` Album(titre TEXT, artiste TEXT, annee INT, dispo BOOL) ``` > ⚠️ Cette relation n'est pas satisfaisante, nous allons l'améliorer ---- ## Représentation tabulaire * Une **relation** est aussi souvent représentée par une **table** (on confond souvent les deux termes): * Exemple (avec notre table `Album`, pas encore satisfaisante) : | titre | artiste | annee | dispo | |-------|---------|-------|-------| | I Still Do | Eric Clapton | 2016 | Vrai | | Axis: Bold as Love | Jimi Hendrix | 1967 | Faux | | Continuum | John Mayer | 2006 | Faux | | ... | | | | ---- ## Base de données relationnelle - Base de données relationnelle = un ensemble de relations - **Schéma d'une base de données relationnelle** = ensemble des schémas des relations - Exemple (incomplet et insatisfaisant) : ``` Album(titre TEXT, artiste TEXT, annee INT, dispo BOOL) Client(...) Emprunt(...) ``` --- # Conception d'une base de données ---- ## Conception d'une base de données * Travail pas toujours aisé mais primordial * En général, on procède ainsi : 1. Déterminer les entités à manipuler 2. Modéliser les entités comme des relations (schéma à donner) 3. Définir les contraintes d'intégrité (domaine, relation, référence) = les propriétés logiques vérifiées par les données à chaque instant ---- ### Contrainte de domaine des attributs * Domaine (= type) à définir obligatoirement pour chaque attribut de chaque relation * **Contrainte de relation** : garantit des valeurs conformes au domaine défini * Concrètement : * Le SGBD s'assure à chaque instant que le domaine de chaque attribut est respecté * Si on essaie de saisir une valeur de type FLOAT pour un attribut ayant INT pour domaine : le SGBD refuse et génère une erreur ---- ### Contrainte de domaine des attributs * Définir le domaine d'un attribut = travail assez simple mais il y a des pièges * Exemple : quel domaine pour un attribut correspondant à un code postal ? <div class="fragment" markdown="1"> Une chaîne de caractères (TEXT) </div> ---- ## Clé primaire - **Clé primaire** = attribut ou réunion d'attributs identifiant de manière unique un enregistrement - À définir obligatoirement pour chaque relation ! ---- ### Clé primaire de la relation *Album* - Que choisir pour la relation *Album* ? ... ``` Album(titre TEXT, artiste TEXT, annee INT, dispo BOOL) ``` ... il y a un problème ! ---- ### Clé primaire de la relation *Album* - **Solution** : * créer une clé primaire "artificielle" `id_album` de type INT * chaque album aura un attribut `id_album` différent - Symbolisée par un soulignement dans le schéma <em>Album</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>id_album</em></span> INT, <em>titre</em> TEXT, <em>artiste</em> TEXT, <em>annee</em> INT, <em>dispo</em> BOOL) ---- ### Clé primaire de la relation *Album* * Exemple de la table *Album* avec clé primaire | id_album | titre | artiste | annee | dispo | |----------|-------|---------|-------|-------| | 2 | I Still Do | Eric Clapton | 2016 | Vrai | | 5 | Axis: Bold as Love | Jimi Hendrix | 1967 | Faux | | 24 | Continuum | John Mayer | 2006 | Faux | ---- ### Clé primaire de la relation *Client* * Le disquaire récolte les données suivantes sur ses clients : nom, prénom, adresse email * Quels sont les attributs et leurs domaines ? <div class="fragment"> <em>Client</em>(<em>nom</em> TEXT, <em>prenom</em> TEXT, <em>email</em> TEXT) </div> <div class="fragment"> * Quel(s) attribut(s) peuvent jouer le rôle de clé primaire dans cette relation ? </div> <div class="fragment"> <em>Client</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>id_client</em></span> INT, <em>nom</em> TEXT, <em>prenom</em> TEXT, <em>email</em> TEXT) </div> ---- ### Clé primaire de la relation *Client* * Table *Client* avec la clé primaire : <div style="height:1rem"></div> | id_client | nom | prenom | email | | --- | --- | --- | --- | | 1 | Dupont | Florine | dupont.florine@domaine.net | | 5 | Pacot | Jean | jpacot@music.com | | 8 | Rouger | Léa | NULL | | 3 | Marchand | Grégoire | greg.marchand49@music.com | ---- ### Contrainte de relation - **Contrainte de relation** : garantit l'*unicité* des enregistrements - Réalisée par la **clé primaire** pour chaque relation - Concrètement : * le SGBD s'assure à chaque instant que deux enregistrements d'une relation ne possèdent pas des valeurs identiques pour les attributs jouant le rôle de clé primaire * génère une erreur sinon ---- ## Clé étrangère - **Clé étrangère** = attribut qui est clé primaire d'une autre relation - Permet de lier les relations entre elles ---- ## Exemple de relation Emprunt * Un emprunt faire référence à un album (de la relation *Album*) et un client (de la relation *Client*), et à une date d'emprunt (ou de retour) * Une proposition de schéma de la relation *Emprunt* : <div style="font-size:0.75em"> ``` Emprunt(id_client INT, nom TEXT, prenom TEXT, email TEXT, id_album INT, titre TEXT, artiste TEXT, annee INT, dispo BOOL, date DATE) ``` </div> > ⚠️ Cette relation n'est pas satisfaisante pour le moment ! <div class="fragment"> * Quelles sont les clés étrangères de cette relation ? </div> <div class="fragment"> `id_client` et `id_album` </div> ---- ## Exemple de relation Emprunt * Cela donnerait une table *Emprunt* du genre : <div style="font-size:1.2rem; break-word:break"> | id_client | nom | prenom | email | id_album | titre | artiste | annee | dispo | date | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | Dupont | Florine | dupontf@domaine.net | 5 | Axis: Bold as Love | Jimi Hendrix | 1967 | Faux | 10/09/2021 | | 3 | Mira | Grégoire | gmira49@music.com | 8 | Riding With The King | Eric Clapton et B.B. King | 2000 | Faux | 18/08/2021 | | 3 | Mira | Grégoire | gmira49@music.com | 24 | Continumm | John Mayer | 2006 | Faux | 18/08/2021 | | 5 | Pacot | Jean | jpacot@music.com | 25 | Continumm | John Mayer | 2006 | Faux | 12/09/2021 | </div> * Que choisir comme clé primaire ? <div class="fragment"> On peut choisir `id_album` (car un album ne peut pas être emprunté par deux clients en même temps) </div> ---- ### Redondance des données <div style="font-size:1.2rem; break-word:break"> | id_client | nom | prenom | email | id_album | titre | artiste | annee | dispo | date | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 1 | Dupont | Florine | dupontf@domaine.net | 5 | Axis: Bold as Love | Jimi Hendrix | 1967 | Faux | 10/09/2021 | | 3 | Mira | Grégoire | gmira49@music.com | 8 | Riding With The King | Eric Clapton et B.B. King | 2000 | Faux | 18/08/2021 | | 3 | Mira | Grégoire | gmira49@music.com | 24 | Continumm | John Mayer | 2006 | Faux | 18/08/2021 | | 5 | Pacot | Jean | jpacot@music.com | 25 | Continumm | John Mayer | 2006 | Faux | 12/09/2021 | </div> * Problème actuel : la **redondance des données** * Il faut éviter de dupliquer les informations entre relations * (et éviter que des mêmes informations se retrouvent dans plusieurs enregistrements d'une même relation) ---- ### Redondance des données * **Solution** : on ne conserve que `id_client`, `id_album` et `date` dans la relation *Emprunt* : <em>Emprunt</em>(<em>#id_client</em> INT, <span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>#id_album</em></span> INT, <em>date</em> DATE) | id_client | id_album | date | | --- | --- | --- | | 1 | 5 | 10/09/2021 | | 3 | 8 | 18/08/2021 | | 3 | 24 | 18/08/2021 | | 5 | 25 | 12/09/2021 | * **Moralité** : on utilise les clés étrangères pour lier les tables entre elles ---- ### Redondance des données * **Pourquoi éviter la redondance des données ?** * coût en mémoire supplémentaire si une même information est stockées inutilement dans plusieurs relations * si des corrections doivent être faites (par exemple un changement de nom) : plus simple de les faire à un seul endroit ---- ## Contrainte de référence - **Contrainte de référence** : garantit la cohérence des liens entre relations - Réalisée par les **clés étrangères** ---- ## Contrainte de référence - Concrètement : * une clé étrangère d'une relation doit nécessaire être la clé primaire d'une autre relation * permet de ne pas ajouter de valeurs fictives qui ne correspondent à aucune entité connue de la base de données ---- ## Contrainte de référence - Concrètement (suite): * un enregistrement ne peut être effacé que si sa clé primaire n'est pas associée à des enregistrements liés dans d'autres relations * on ne peut pas supprimer le client "Dupont Florine" de la relation *Client* car il apparaît dans les enregistrements de la relation *Emprunt* <div style="display:flex; gap:3rem; font-size:1.2rem;"> <div> Table *Client* | id_client | nom | prenom | email | | --- | --- | --- | --- | | 1 | Dupont | Florine | dupont.florine@domaine.net | | 5 | Pacot | Jean | jpacot@music.com | | 8 | Rouger | Léa | NULL | | 3 | Marchand | Grégoire | greg.marchand49@music.com | </div> <div> Table *Emprunt* | id_client | id_album | date | | --- | --- | --- | | 1 | 5 | 10/09/2021 | | 3 | 8 | 18/08/2021 | | 3 | 24 | 18/08/2021 | | 5 | 25 | 12/09/2021 | </div> </div> ---- ## Contrainte de référence - Concrètement (suite) : * une clé primaire ne peut pas être modifiée si l'enregistrement en question est associé à des enregistrements liés dans d'autres tables * on ne peut pas modifier la clé primaire `id_client` du client "Dupont Florine" de la relation *Client* car il apparaît dans les enregistrements de la relation *Emprunt* <div style="display:flex; gap:3rem; font-size:1.2rem;"> <div> Table *Client* | id_client | nom | prenom | email | | --- | --- | --- | --- | | 1 | Dupont | Florine | dupont.florine@domaine.net | | 5 | Pacot | Jean | jpacot@music.com | | 8 | Rouger | Léa | NULL | | 3 | Marchand | Grégoire | greg.marchand49@music.com | </div> <div> Table *Emprunt* | id_client | id_album | date | | --- | --- | --- | | 1 | 5 | 10/09/2021 | | 3 | 8 | 18/08/2021 | | 3 | 24 | 18/08/2021 | | 5 | 25 | 12/09/2021 | </div> </div> ---- ## Lien entre albums et artistes <em>Album</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>id_album</em></span> INT, <em>titre</em> TEXT, <em>artiste</em> TEXT, <em>annee</em> INT, <em>dispo</em> BOOL) * Le choix d'utiliser une chaîne de caractères pour l'artiste directement dans la table *Album* peut conduire à quelques problèmes : * rien n'empêche d'associer plusieurs fois le même artiste à un album ("Éric Clapton et Éric Clapton") sans que cela génère d'erreur malgré un problème de cohérence * problème possible de redondance des données si un artiste change de nom (il faudrait le modifier sur tous les enregistrements) ---- ### Lien entre albums et artistes * Solution : * Séparer la relation *Album* en trois relations : *Album*, *Artiste* et *Artiste_de* * Utiliser les clés étrangères pour faire les associations entre artistes et albums <em>Album</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>id_album</em></span> INT, <em>titre</em> TEXT, <em>annee</em> INT, <em>dispo</em> BOOL) <em>Artiste</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>id_artiste</em></span> INT, <em>nom</em> TEXT, <em>prenom</em> TEXT) <em>Artiste_de</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>#id_artiste</em></span> INT, <span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>#id_album</em></span> INT) ---- ### Lien entre albums et artistes <em>Artiste_de</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>#id_artiste</em></span> INT, <span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>#id_album</em></span> INT) * Dans la relation *Artiste_de* c'est le couple (id_artiste, id_album) qui forme la clé primaire * un même artiste **et** un même album ne peuvent pas se trouver plusieurs fois dans la relation : empêche d'associer deux fois le même artiste à un même album * un même artiste peut être associé à plusieurs albums (car `id_artiste` seul n'est pas clé primaire) ---- ### Lien entre albums et artistes <div style="display:flex; gap:3rem;font-size:1.2rem;"> <div> Relation _Album_ : | id_album | titre | annee | dispo | | --- | --- | --- | --- | | 2 | I Still Do | 2016 | Vrai | | 5 | Axis: Bold as Love | 1967 | Faux | | 24 | Continuum | 2006 | Faux | | 25 | Continuum | 2006 | Faux | | 8 | Riding With The King | 2000 | Faux | | 11 | Don't explain | 2011 | Vrai | </div> <div> Relation _Artiste_ : | id_artiste | nom | prenom | | --- | --- | --- | | 1 | Clapton | Éric | | 3 | Hendrix | Jimi | | 4 | Mayer | John | | 8 | B.B. King | NULL | | 6 | Hart | Beth | | 15 | Bonamassa | Joe | </div> <div> Relation *Artiste_de* : | id_artiste | id_album | | --- | --- | | 1 | 2 | | 1 | 8 | | 8 | 2 | | 4 | 24 | | 4 | 25 | | 3 | 5 | | 6 | 11 | | 15 | 11 | </div> </div> * Chaque chaîne de caractères n'est enregistrée qu'une seule fois * Utiliser des entiers est plus efficace pour les recherches (comparaisons entre entiers plus rapide qu'entre chaînes de caractères) ---- ## Bilan de modélisation de la base de données ### Schéma final <em>Album</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>id_album</em></span> INT, <em>titre</em> TEXT, <em>annee</em> INT, <em>dispo</em> BOOL) <em>Artiste</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>id_artiste</em></span> INT, <em>nom</em> TEXT, <em>prenom</em> TEXT) <em>Artiste_de</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>#id_artiste</em></span> INT, <span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>#id_album</em></span> INT) <em>Client</em>(<span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>id_client</em></span> INT, <em>nom</em> TEXT, <em>prenom</em> TEXT, <em>email</em> TEXT) <em>Emprunt</em>(<em>#id_client</em> INT, <span style="padding-bottom:3px; border-bottom: 1px solid black;"><em>#id_album</em></span> INT, <em>date</em> DATE)</code> ---- ## Bilan de modélisation de la base de données ### Autre représentation : un diagramme <img class="centre image-responsive" src="data/diagramme_bdd.png" alt="diagramme"> --- # Bilan ---- ## Bilan - Modèle relationnel pour structurer les données - Relations avec attributs et domaines - Clés primaires pour définir de manière unique les enregistrements d'une relation - Clés étrangères pour lier les relations - Contraintes d'intégrité à respecter : - de domaine - de relation (réalisée par clé primaire) - de référence (réalisée par clés étrangères) - Conception réfléchie pour éviter les anomalies - Prochaine étape : utilisation du langage SQL pour interagir avec la base de données --- #### Références - Equipe éducative DIU EIL, Université de Nantes. - Cours OpenClassrooms pour l'idée de la base de données d'un disquaire : [Découvrez le framework Django](https://openclassrooms.com/fr/courses/4425076-decouvrez-le-framework-django). - Livre *Numérique et Sciences Informatiques, 24 leçons, Terminale*, T. BALABONSKI, S. CONCHON, J.-C. FILLIATRE, K. NGUYEN, éditions ELLIPSES. - Livre *Prepabac NSI, Tle*, G. Connan, V. Petrov, G. Rozsavolgyi, L. Signac, éditions HATIER. - Cours de Gilles Lassus sur le [modèle relationnel](https://github.com/glassus/terminale_nsi/blob/main/docs/T4_Bases_de_donnees/4.1_Modele_relationnel/cours.md) --- Germain BECKER, Lycée Mounier, ANGERS CC BY-SA ![Licence Creative Commons](https://i.creativecommons.org/l/by-sa/4.0/88x31.png)