Modèle relationnel
Notion de base de données
Définition
Une base de données est un ensemble d'informations structurées dont l'organisation est régie par un modèle.
Dans ce cours on s'intéresse aux bases de données dites relationnelles.
On prendra notamment pour exemple dans ce cours une base de données simplifiée d'un CDI de lycée.
Notion de modèle relationnel
Définition
Le modèle relationnel est une manière de modéliser les relations existantes entre plusieurs informations et de les ordonner entre elles.
Cette modélisation, mis en avant par E.F. Codd dans les années 1970, repose sur les principes suivants :
- On sépare les données dans plusieurs tables et on met ces tables en relation grace à l'utilisation de ce que l'on appelle des clés afin :
- de regrouper les données directement relatives à un même "sujet" ;
- d'éviter la redondance des données.
- On ne stocke pas les données pouvant être calculées.
Application
Exercice 1 : Non-respect du modèle relationnel
On va analyser la modélisation utilisée pour les données officielles relatives à l'historique des stocks de vaccins contre la Covid-19 par départements.
Ces données sont disponibles en fichier CSV ici sur le site www.data.gouv.fr.
Une visualisation en ligne du contenu de ce fichier CSV est disponible ici à la ligne stocks-es-par-dep.csv :
Justifier en quoi cette modélisation ne respecte pas les principes du modèle relationnel.
Notions de relation, d'entité et d'attribut
La notion de relation est une notion fondamentale des bases de données relationnelles.
Lors de son implémentation, une relation est représentée sous la forme d'un tableau à 2 dimensions que l'on nomme alors également table (à l'image du traitement de données en tables vu précédemment).
Voici, pour exemple, l'implémentation, sous la forme d'une table, de la relation Livre représentative de l'ensemble des livres disponibles dans un (très petit 😅) CDI :
| id_livre | isbn | titre | auteur | annee_premiere_parution | editeur |
|---|---|---|---|---|---|
| 1 | 978-2070368228 | 1984 | Orwell | 1949 | Folio |
| 2 | 978-2266233200 | Dune | Herbert | 1965 | |
| 3 | 978-2070360536 | Fondation | Asimov | 1951 | Folio |
| 4 | 978-2266230919 | La nuit des temps | Barjavel | 1968 | |
| 5 | 978-2218069192 | Les Robots | Asimov | 1950 | Hatier |
| 6 | 978-2266283021 | La Planète des singes | Boulle | 1963 | |
| 7 | 978-2070362387 | Ravage | Barjavel | 1943 | Gallimard |
A présent voici, pour exemple, l'implémentation de la relation Eleve représentative de l'ensemble des élèves présents dans un lycée (très petit également...) :
| id_eleve | nom | prenom | date_naissance |
|---|---|---|---|
| 1 | Doignon | Lucas | 12/08/2002 |
| 2 | Trian | Fabien | 24/03/2003 |
| 3 | Rasti | Jade | 01/11/2003 |
Définition
Un élément de la relation (donc une ligne de la table) correspond à une entité unique qui peut représenter un objet en particulier, une personne réelle, une action particulière, etc.
- Cas de la table Livre :
Une ligne de la table Livre modélise un seul et unique livre (entité unique), exemple :
| id_livre | isbn | titre | auteur | annee_premiere_parution | editeur |
|---|---|---|---|---|---|
| 7 | 978-2070362387 | Ravage | Barjavel | 1943 | Gallimard |
La relation Livre modélise donc l'ensemble des entités que sont l'ensemble des livres présents dans le CDI.
Remarque
Ici, cette table Livre autorise la présence d'un même livre en plusieurs exemplaires grâce à la présence de l'attribut id_livre qui correspond à un identifiant unique pour chaque exemplaire de livre présent dans le CDI :
| id_livre | isbn | titre | auteur | annee_premiere_parution | editeur |
|---|---|---|---|---|---|
| 7 | 978-2070362387 | Ravage | Barjavel | 1943 | Gallimard |
| 8 | 978-2070362387 | Ravage | Barjavel | 1943 | Gallimard |
- Cas de la table Eleve :
De la même manière, une ligne de la table Eleve modélise un seul et unique élève (entité unique).
| num_eleve | nom | prenom | date_naissance |
|---|---|---|---|
| 1 | Doignon | Lucas | 12/08/2002 |
La relation Eleve modélise quant à elle l'ensemble des élèves présents au lycée.
Remarque
Cette table Eleve autorise la présence d'homonyme chez les élèves (élèves portant les mêmes nom et prénom) grâce à la présence de l'attribut num_eleve qui correspond à un identifiant unique pour chaque élève présent dans le lycée :
| num_eleve | nom | prenom | date_naissance |
|---|---|---|---|
| 1 | Doignon | Lucas | 12/08/2002 |
| 4 | Doignon | Lucas | 12/08/2002 |
Définition
Un attribut décrit une information stockée dans une base de données.
Dans une relation, chaque entité possède un certain nombre d'attributs.
Par exemple ici, chaque entité de la relation Eleve possède 4 attributs :
- id_eleve : le numéro d'identification de l'élève
- nom : le nom de l'élève
- prenom : le prénom de l'élève
- date_naissance : la date de naissance de l'élève
Définition
Une relation est donc un ensemble d'entités uniques décrites par des attributs communs.
La notion de relation est très fortement rattachée à la notion d'ensemble : il n'y pas d'entités en doublon dans une table.
Vocabulaire
L'étude des bases de données conduit souvent à utiliser plusieurs termes pour définir un même concept, par exemple :
- relation (théorie),
- sous-ensemble (mathématique),
- table (implémentation),
- tableau de tableaux ou tableau de dictionnaires (informatique),
sont associés au même concept.
De la même manière les termes :
- entité (théorie),
- ligne / enregistrement / occurences (implémentation),
- tableau ou tuple ou dictionnaires (informatique),
sont associés au même concept et leur utilisation dépend du point de vue choisi.
Application
Exercice 2 : Cas de l'association canine
On considère ici le cas d'une association canine.
Soit la table Adhérent :
| id_adherent | nom | prenom |
|---|---|---|
| 1 | Filo | Jade |
| 2 | Cavette | Emma |
| 3 | Dupois | Loïc |
| 4 | Desnure | Lana |
| 5 | Gaullois | Michel |
Indiquer ce que modélise la relation Adhérent (s'aider du contexte de la question et du nom de la table).
Donner une entité de la relation Adhérent. Indiquer ce que cette entité modélise.
Donner l'intitulé des attributs de la relation Adhérent.
Notion de schéma de relation et de domaine d'un attribut
Chaque relation respecte ce que l'on appelle un schéma de relation.
Pour exemple, les deux relations Livre et Eleve précédentes sont associées à des schémas de relation que l'on écrit ainsi :
Schéma de la relation Livre :
Livre(id_livre: Entier,
isbn: Chaîne de caractères,
titre: Chaîne de caractères,
auteur: Chaîne de caractères,
annee_premiere_parution: Entier,
editeur: Chaîne de caractères)
Le schéma de relation indique que la relation Livre possède 5 attributs (4 dont le domaine est ici associé à une chaîne de caractères et 1 à un entier).
Schéma de la relation Eleve :
Eleve(id_eleve: Entier,
nom: Chaîne de caractères,
prenom: Chaîne de caractères,
date_naissance: Chaîne de caractères)
Le schéma de relation indique que la relation Eleve possède 4 attributs (3 dont le domaine est ici associé à une chaîne de caractères et 1 à un entier).
Définition
Le schéma de relation précise le nom de la relation ainsi que la liste des attributs avec leurs domaines.
Exemple :
Livre(id_livre: Entier,
isbn: Chaîne de caractères,
titre: Chaîne de caractères,
auteur: Chaîne de caractères,
annee_premiere_parution: Entier,
editeur: Chaîne de caractères)
Définition
Le domaine d'un attribut décrit l'ensemble des valeurs que peut prendre un attribut.
Exemple :
annee_premiere_parution: Entier
Application
Exercice 3 : Cas de l'association canine
On considère ici le cas d'une association canine.
Soient les 3 tables suivantes.
Table Race :
| id_race | libelle |
|---|---|
| 1 | Beagle |
| 2 | Caniche |
| 3 | Epagneul |
| 4 | Berger_Australien |
| 5 | Saint-Bernard |
| 6 | Labrador_Retriver |
Table Adhérent :
| id_adherent | nom | prenom |
|---|---|---|
| 1 | Filo | Jade |
| 2 | Cavette | Emma |
| 3 | Dupois | Loïc |
| 4 | Desnure | Lana |
| 5 | Gaullois | Michel |
Table Chien :
| id_tatouage | nom | annee_naissance | sexe | id_race | id_adhérent |
|---|---|---|---|---|---|
| 158det | Pifou | 2014 | m | 2 | 1 |
| 267soi | Rex | 2014 | m | 6 | 3 |
| 172frg | Smoothie | 2016 | f | 4 | 1 |
| 269qzv | Noisette | 2018 | f | 3 | 1 |
| 349jad | Cassoulet | 2015 | m | 2 | 5 |
| 698fri | Némo | 2015 | m | 5 | 4 |
| 523chu | Basta | 2016 | f | 1 | 2 |
| 472koa | Némo | 2017 | m | 4 | 4 |
Donner les schémas de relation de chacune des relations décrites ci-dessus.
Respect du principe du modèle relationnel
Dans le cas du CDI, un élève est évidemment susceptible d'emprunter un livre.
Le modèle associé au CDI du lycée doit nécessaire prendre en compte l'emprunt d'un livre par un élève. Or les deux seuls relations actuelles Livre et Eleve ne permettent pas de modéliser cette situation...
La solution consiste donc en la création d'une table supplémentaire que l'on nomme ici Emprunt.
Cette table Emprunt pourrait naïvement être la suivante :
| id_livre | isbn | titre | auteur | annee_premiere_parution | editeur | id_eleve | nom | prenom | date_naissance | date_retour |
|---|---|---|---|---|---|---|---|---|---|---|
| 2 | 978-2266233200 | Dune | Herbert | 1965 | 1 | Doignon | Lucas | 12/08/2002 | 27/08/2021 | |
| 6 | 978-2266283021 | La Planète des singes | Boulle | 1963 | 1 | Doignon | Lucas | 12/08/2002 | 28/08/2021 | |
| 7 | 978-2070362387 | Ravage | Barjavel | 1943 | Gallimard | 3 | Rasti | Jade | 01/11/2003 | 15/02/2021 |
Cette relation indique que Doignon Lucas né le 12/08/2002 emprunte actuellement les livres dont les titres sont :
- Dune (pour une date de retour au 27/08/2021),
- La Planète des singes (pour une date de retou.r au 28/08/2021)
De la même façon cette relation indique que Rasti Jade née le 01/11/2003 emprunte le livre :
- Ravage (pour une date de retour au 15/02/2021).
Cette table semble donc effectivement modéliser le fait qu'un élève puisse emprunter un livre avec une date de retour donnée.
Non-respect des principes du modèle relationnel
Cependant, cette table Emprunt ne respecte pas les principes du modèle relationnel :
Remarque 1
Cette table est susceptible de comporter beaucoup d'informations redondantes :
Exemple :
- le nom et le prénom d'un l'élève emprunteur,
- le titre, l'auteur, ... du livre emprunté.
Il faut imaginer les conséquences s'il était nécessaire de mettre à jour des données dans cette table Emprunt (comme une date de naissance erronée saisie lors de l'inscription d'un élève).
Comme les données y sont redondantes, il serait nécessaire de parcourir toute la table à recherche des dates de naissance à mettre à jour...
Remarque 2
Les données que regroupe cette table dépassent le simple fait d'emprunter un livre, en effet des données sont ici non directement relatives à même sujet.
Exemple : Le nom Doignon n'a en soit aucun rapport direct avec le titre Dune.
Par contre l'élève dont l'identifiant est 1 a un rapport direct avec le livre dont l'identifiant est 2.
Remarque : On rappelle qu'ici les identifiants id_eleve et id_livre sont uniques pour une entité donnée.
Il semble donc plus pertinent de considérer la relation Emprunt suivante :
| id_livre | id_eleve | date_retour |
|---|---|---|
| 2 | 1 | 27/08/2021 |
| 6 | 1 | 28/08/2021 |
| 7 | 3 | 15/02/2021 |
Cette relation indique que Doignon Lucas né le 12/08/2002 emprunte actuellement les livres dont les titres sont :
- Dune (date de retour au 27/08/2021),
- La Planète des singes (date de retour au 28/08/2021).
De la même façon cette relation indique que Rasti Jade née le 01/11/2003 emprunte le livre :
- Ravage (date de retour au 15/02/2021).
Le schéma de la relation Emprunt est le suivant :
Emprunt(id_livre: Entier,
id_eleve: Entier,
date_retour: Chaîne de caractères)
La relation Emprunt possède 2 attributs de type entier et 1 attribut de type chaînes de caractères.
En résumé
En résumé, nous avons ici mis en place une modélisation relationnelle portant sur un CDI et dont l'ensemble des schémas de relation est le suivant :
Livre(id_livre: Entier,
isbn: Chaîne de caractères,
titre: Chaîne de caractères,
auteur: Chaîne de caractères,
annee_premiere_parution: Entier,
editeur: Chaîne de caractères)
Eleve(id_eleve: Entier,
nom: Chaîne de caractères,
prenom: Chaîne de caractères,
date_naissance: Chaîne de caractères)
Emprunt(id_livre: Entier,
id_eleve: Entier,
date_retour: Chaîne de caractères)
Application
Exercice 4 : Cas de l'association canine
On considère ici le cas d'une association canine.
Soient les 2 tables suivantes :
Table Chien :
| id_tatouage | nom | annee_naissance | sexe |
|---|---|---|---|
| 158det | Pifou | 2014 | m |
| 267soi | Rex | 2014 | m |
| 172frg | Smoothie | 2016 | f |
| 269qzv | Noisette | 2018 | f |
| 349jad | Cassoulet | 2015 | m |
| 698fri | Némo | 2015 | m |
| 523chu | Basta | 2016 | f |
| 472koa | Némo | 2017 | m |
Table Concours :
| id_concours | ville | annee_concours |
|---|---|---|
| 1 | Orange | 2019 |
| 2 | Avignon | 2019 |
| 3 | Avignon | 2020 |
| 4 | Carpentras | 2020 |
| 5 | Bollène | 2021 |
Donner le schéma de relation de chacune des relations Chien et Concours.
Proposer le schéma d'une relation nommée Participe modélisant le fait que certains chiens de l'association canine aient pû participer à un concours canin.
A noter que :
- La relation Chien modélise l'ensemble des chiens de l'association canine ;
- La relation Concours modélise l'ensemble des concours canins pour lesquels l'association a participé avec un ou plusieurs chiens ;
- L'identifiant id_tatouage a une valeur unique pour chaque entier issue de la relation Chien ;
- L'identifiant id_concours a une valeur unique pour chaque entité issue de la relation Concours.
Proposer un contenu possible comportant 4 enregistrements (4 lignes) pour la table Participe.
Modélisation relationnelle de données
La modélisation des données que l'on souhaite stocker dans une base de données passe par les étapes successives suivantes :
1- déterminer les entités (objets, personnes, actions) qu'il est nécessaire de manipuler.
Par exemple ici :
- Un livre particulier,
- Un élève donné,
- L'emprunt d'un livre particulier par un élève donné.
2- choisir les attributs communs qui décrivent ces entités.
Par exemples ici :
- Un livre particulier est décrit par son identifiant, l'éditeur, son ISBN, ...
- Un élève donné est décrit par son identifiant, son nom, ...
- L'emprunt d'un livre particulier par un élève donné est décrit par l'élève emprunteur, le livre emprunté, ...
3- choisir le domaine le plus approprié pour chaque attribut.
Exemples ici pour la relation Eleve :
- L'attribut id_eleve est un entier,
- L'attribut nom est une chaîne de caractères
- ...
4- modéliser les ensembles d'entités sous la forme de relations en donnant leur schéma de relation.
Notion de contrainte d'intégrité
Définition
On appelle contraintes d'intégrité l'ensemble des règles que les données présentes dans la base doivent à tout instant respecter.
Les contraintes d'intégrité garantissent la cohérence des données présentes dans la base de données.
Intérêt des contraintes d'intégrité par l'exemple
En absence de contraintes d'intégrité, dans le cas de la base de données du CDI du lycée décrite précédemment, on pourrait imaginer par exemple que :
- Lors de l'enregistrement d'un nouveau livre dans la base de données, le documentaliste pourrait saisir par erreur le nom de l'éditeur dans l'année de parution (attribut annee_premiere_parution) sans que le logiciel de Système de Gestion de Base de Données (SGBD) ne signale d'erreur.
| id_livre | isbn | titre | auteur | annee_premiere_parution | editeur |
|---|---|---|---|---|---|
| 1 | 978-2070368228 | 1984 | Orwell | 1949 | Folio |
| 2 | 978-2266233200 | Dune | Herbert | 1965 |
- La présence de doublons dans la relation Livre serait acceptée par le logiciel de Système de Gestion de Base de Données (une même entité ne serait pas unique dans la relation).
| id_livre | isbn | titre | auteur | annee_premiere_parution | editeur |
|---|---|---|---|---|---|
| 1 | 978-2070368228 | 1984 | Orwell | 1949 | Folio |
| 2 | 978-2266233200 | Dune | Herbert | 1965 | |
| 2 | 978-2266233200 | Dune | Herbert | 1965 |
- L'enregistrement par documentaliste de l'emprunt d'un même exemplaire de livre par deux élèves différents n'engendrerait pas d'erreur par le logiciel Système de Gestion de Base de Données.
| id_livre | id_eleve | date_retour |
|---|---|---|
| 2 | 1 | 27/08/2021 |
| 6 | 1 | 28/08/2021 |
| 7 | 3 | 15/02/2021 |
| 7 | 1 | 29/07/2021 |
Application
Exercice 5 : Cas de l'association canine
Repérer les incohérences dans les tables fournies ci-dessous :
Table Race :
| id_race | libelle |
|---|---|
| 1 | Beagle |
| 2 | Caniche |
| 3 | Epagneul |
| 4 | Berger_Australien |
| 5 | Saint-Bernard |
| 6 | Labrador_Retriver |
Table Adhérent :
| id_adherent | nom | prenom |
|---|---|---|
| 1 | Filo | Jade |
| 2 | Cavette | Emma |
| 3 | Dupois | Loïc |
| 4 | Desnure | Lana |
| 5 | Gaullois | Michel |
Table Chien :
| id_tatouage | nom | annee_naissance | sexe | id_race | id_adhérent |
|---|---|---|---|---|---|
| 158det | Pifou | 2014 | m | 2 | 1 |
| 267soi | Rex | 2014 | m | 6 | 3 |
| 172frg | Smoothie | 2016 | f | 4 | 1 |
| 269qzv | Noisette | 2018 | f | 3 | 1 |
| 349jad | 2015 | Cassoulet | m | 2 | 5 |
| 698fri | Némo | 2015 | m | 5 | 4 |
| 523chu | Basta | 2016 | f | 7 | 2 |
| 472koa | Némo | 2017 | m | 4 | 4 |
Table Concours :
| id_concours | ville | annee_concours |
|---|---|---|
| 1 | Orange | 2019 |
| 2 | Avignon | 2019 |
| 3 | Avignon | 2020 |
| 4 | Carpentras | 2020 |
| 4 | Carpentras | 2020 |
| 5 | Bollène | 2021 |
Table Participe :
| id_tatouage | id_concours | classement |
|---|---|---|
| 172frg | 3 | 1 |
| 472koa | 2 | 10 |
| 349jad | 2 | 7 |
| 523chu | 6 | 3 |
| 472koa | 2 | 5 |
Contrainte de domaine
Définition
La contrainte de domaine impose que toute valeur prise par un attribut doit nécessairement appartenir au domaine défini pour cet attribut.
Autrement dit, lors d'un enregistrement dans la base de données chaque attribut doit prendre une valeur dans son domaine de valeurs.
Le choix du domaine des attributs permet :
- de représenter le plus exactement possible les valeurs possibles d'un attribut ;
- rendre difficile voire impossible la saisie de valeurs incohérentes.
Remarque
Dans le cas de la base de données du CDI du lycée, impossible donc pour le documentaliste de saisir par erreur le nom de l'éditeur dans l'année de parution car le logiciel de SGBD générerait immédiatement une erreur du fait du non respect de la contrainte de domaine.
Limites
Evidemment, la contrainte de domaine ne garantit pas complètement que les données saisies soient cohérentes.
Le documentaliste pourrait par exemple ici saisir 2651 comme valeur pour l'attribut annee_premiere_parution si aucune aucune vérification supplémentaire n'est prévue.
Application
Exercice 6 : Cas de l'association canine
Reprendre les incohérences trouvées dans l'application précédente, à relire ici, et indiquer celle(s) qui est(sont) liée(s) au non-respect de la contrainte de domaine.
Contrainte de relation
Définition
La contrainte de relation impose que chaque entité présente dans une relation soit unique et identifiée de façon non ambiguë.
Application 1
Exercice 7 : Cas de l'association canine
Reprendre les incohérences trouvées dans l'application précédente, à relire ici, et indiquer celle(s) qui est(sont) liée(s) au non-respect de la contrainte de relation.
Pour ce faire il est nécessaire de mettre en place ce que l'on nomme une clé primaire pour chaque relation.
Définition
On appelle clé primaire l'ensemble des attributs qui permettent d'identifier de façon unique les entités présentes dans une relation.
On indique l'ensemble des attributs faisant office de clé primaire dans une relation en les soulignant dans le schéma de relation.
Le choix de la clé primaire (ensemble d'attributs) d'une relation est primordial afin de respecter la contrainte de relation.
La présence de doublons dans les relations Livre, Eleve, Emprunt de la base de donnée du CDI du lycée n'est donc pas permise.
Cas de la table Livre :
Dans la table Livre, comme l'attribut id_livre est unique pour chacun des exemplaires de livres (la valeur de cet attribut est donnée de façon unique par le documentaliste lors de l'enregistrement d'un nouveau livre dans la base de donnée du CDI), l'attribut id_livre peut être choisi comme clé primaire de la table Livre.
Le schéma de la relation Livre indiquant l'ensemble des attributs faisant office de clé primaire (ici un seul attribut) est le suivant :
Livre(id_livre: Entier,
isbn: Chaîne de caractères,
titre: Chaîne de caractères,
auteur: Chaîne de caractères,
annee_premiere_parution: Entier,
editeur: Chaîne de caractères)
Comme l'attribut id_livre est clé primaire de la table Livre, on le souligne dans le schéma de relation de la table Livre.
Choix de la clef primaire par l'exemple
Utiliser l'attribut isbn comme clé primaire de la relation Livre n'aurait pas permis d'identifier de manière unique différents exemplaires d'un même livre.
Cas de la table Eleve :
Dans la table Eleve, comme l'attribut id_eleve est unique pour chacun des élèves du lycée (la valeur de cet attribut est donnée de façon unique par l'administration lors de l'inscription d'un élève au lycée), l'attribut id_eleve peut être choisi comme clé primaire de la table Eleve.
Le schéma de la relation Eleve indiquant l'ensemble des attributs faisant office de clé primaire (ici un seul attribut) est le suivant :
Eleve(id_eleve: Entier,
nom: Chaîne de caractères,
prenom: Chaîne de caractères,
date_naissance: Chaîne de caractères)
Comme l'attribut id_eleve est clé primaire de la table Eleve, on le souligne dans le schéma de relation de la table Eleve.
Choix de la clef primaire par l'exemple
Là encore, choisir l'ensemble des attributs nom et prenom comme clé primaire n'aurait pas permis d'assurer l'unicité des entités de la relation.
En effet, il est tout à fait probable que deux élèves du lycée aient les même nom et prénom.
Application 2
Exercice 8 : Cas de l'association canine
Soient les tables suivantes :
Table Race :
| id_race | libelle |
|---|---|
| 1 | Beagle |
| 2 | Caniche |
| 3 | Epagneul |
| 4 | Berger_Australien |
| 5 | Saint-Bernard |
| 6 | Labrador_Retriver |
Table Adhérent :
| id_adherent | nom | prenom |
|---|---|---|
| 1 | Filo | Jade |
| 2 | Cavette | Emma |
| 3 | Dupois | Loïc |
| 4 | Desnure | Lana |
| 5 | Gaullois | Michel |
Table Chien :
| id_tatouage | nom | annee_naissance | sexe | id_race | id_adhérent |
|---|---|---|---|---|---|
| 158det | Pifou | 2014 | m | 2 | 1 |
| 267soi | Rex | 2014 | m | 6 | 3 |
| 172frg | Smoothie | 2016 | f | 4 | 1 |
| 269qzv | Noisette | 2018 | f | 3 | 1 |
| 349jad | Cassoulet | 2015 | m | 2 | 5 |
| 698fri | Némo | 2015 | m | 5 | 4 |
| 523chu | Basta | 2016 | f | 1 | 2 |
| 472koa | Némo | 2017 | m | 4 | 4 |
Table Concours :
| id_concours | ville | annee_concours |
|---|---|---|
| 1 | Orange | 2019 |
| 2 | Avignon | 2019 |
| 3 | Avignon | 2020 |
| 4 | Carpentras | 2020 |
| 5 | Bollène | 2021 |
Table Participe :
| id_tatouage | id_concours | classement |
|---|---|---|
| 172frg | 3 | 1 |
| 349jad | 2 | 7 |
| 523chu | 1 | 3 |
| 472koa | 2 | 5 |
Donner le schéma de relation associé à chaque de ces tables et y faire apparaître la clé primaire (ensemble de un ou plusieurs attributs).
Contrainte de référence
Définition
La contrainte de référence est lié au fait que la notion de clé primaire ne sert pas seulement à identifier de façon unique les entités d'une relation.
Ces clés primaires peuvent aussi servir de référence dans d'une autre relation.
Une clé primaire servant de référence dans une autre relation se nomme une clé étrangère.
Dans le schéma de relation, on repère généralement les attributs faisant office de clés étrangères grace à la présent du symbole #.
Cas de la table Emprunt :
Pour rappel, la table Emprunt est la suivante :
| id_livre | id_eleve | date_retour |
|---|---|---|
| 2 | 1 | 27/08/2021 |
| 6 | 1 | 28/08/2021 |
| 7 | 3 | 15/02/2021 |
Les attributs id_livre et id_eleve font référence aux attributs décrits respectivement dans les tables Livre et Eleve.
Dans la table Emprunt, les attributs id_eleve et id_livre sont donc des clés étrangères.
Cela signifie que :
- dans la relation Emprunt, la valeur de l'attribut id_livre doit obligatoirement correspondre à une valeur de l'attribut id_livre dans la relation Livre ;
- dans la relation Emprunt la valeur de l'attribut id_eleve doit obligatoirement correspondre à une valeur de l'attribut id_eleve dans la relation Eleve.
Le schéma complet de la relation Emprunt (la clé primaire et les clés étrangères y sont repérés) est ici le suivant :
Emprunt(#id_livre: Entier
#id_eleve: Entier,
date_retour : Chaîne de caractères)
Comme les attributs id_eleve et id_livre sont des clés étrangères dans la table Emprunt, on leur ajoute le # dans le schema de relation.
Cas de la clé primaire
La clé primaire est ici uniquement constituée de l'attribut id_livre car un exemplaire de livre ne peut être emprunté qu'une seule fois par un élève.
En analysant les valeurs des attributs présents dans les relations Emprunt, Eleve et Livre on peut constater le respect de la contrainte de référence :
Relation Emprunt :
| id_livre | id_eleve | date_retour |
|---|---|---|
| 2 | 1 | 27/08/2021 |
| 6 | 1 | 28/08/2021 |
| 7 | 3 | 15/02/2021 |
Relation Eleve :
| id_eleve | nom | prenom | date_naissance |
|---|---|---|---|
| 1 | Doignon | Lucas | 12/08/2002 |
| 3 | Rasti | Jade | 01/11/2003 |
Relation Livre :
| id_livre | isbn | titre | auteur | annee_premiere_parution | editeur |
|---|---|---|---|---|---|
| 2 | 978-2266233200 | Dune | Herbert | 1965 | |
| 6 | 978-2266283021 | La Planète des singes | Boulle | 1963 | |
| 7 | 978-2070362387 | Ravage | Barjavel | 1943 | Gallimard |
Ici la contrainte de référence permet s'assurer du fait que la relation Emprunt ne fasse référence qu'à des élèves ou des livres présents dans la base de données du CDI.
Définition
La contrainte de référence empêche de faire référence à des entités fictives (non présentes dans la base de données).
Intérêt de la contrainte de référence par l'exemple
Pour exemple, la situation ci-dessous viole la contrainte de référence car l'élève dont l'attribut id_eleve est 5 est fictif car il n'existe pas dans la base de données du CDI (cet élève n'apparaît pas dans la relation Eleve dont id_eleve est clé primaire):
Relation Eleve :
| id_eleve | nom | prenom | date_naissance |
|---|---|---|---|
| 1 | Doignon | Lucas | 12/08/2002 |
| 3 | Rasti | Jade | 01/11/2003 |
Relation _Livre :
| id_livre | isbn | titre | auteur | annee_premiere_parution | editeur |
|---|---|---|---|---|---|
| 1 | 978-2070368228 | 1984 | Orwell | 1949 | Folio |
| 2 | 978-2266233200 | Dune | Herbert | 1965 | |
| 6 | 978-2266283021 | La Planète des singes | Boulle | 1963 | |
| 7 | 978-2070362387 | Ravage | Barjavel | 1943 | Gallimard |
Relation Emprunt :
| id_livre | id_eleve | date_retour |
|---|---|---|
| 2 | 1 | 27/08/2021 |
| 6 | 1 | 28/08/2021 |
| 1 | 5 | 15/02/2021 |
| 7 | 3 | 15/02/2021 |
Définition
La contrainte de référence empêche de supprimer des entités d'une relation présentent, sous forme de clés étrangères, dans d'autres relations de la base.
Intérêt de la contrainte de référence par l'exemple
Dans le cas de la base de donnée du CDI, le respect de la contrainte de référence n'autorise, par exemple, pas la suppression d'une entité élève dans la relation Eleve si cette entité existe dans une autre relation.
Dans l'exemple ci-dessous, il est impossible de supprimer l'élève Lucas Doignon (id_eleve de valeur 1) de la table Eleve car il apparaît dans la relation Emprunt :
Relation Eleve :
| id_eleve | nom | prenom | date_naissance |
|---|---|---|---|
| Doignon | Lucas | 12/08/2002 | |
| 3 | Rasti | Jade | 01/11/2003 |
Relation Emprunt :
| id_livre | id_eleve | date_retour |
|---|---|---|
| 2 | 1 | 27/08/2021 |
| 6 | 1 | 28/08/2021 |
| 7 | 3 | 15/02/2021 |
De la même façon, un livre emprunté ne peut pas être supprimé de la relation Livre.
Application
Exercice 9 : Cas de l'association canine
Reprendre les schémas de relation issus de l'application précédente, à relire ici, et y repérer les clés étrangères par un #.
Représentation de la structure d'une base de données
Il est pertinent de remarquer que la modélisation relationnelle d'une base de données peut être représentée de plusieurs manières.
La conception d'une modélisation relationnel et les règles de passage d'une représentation à une autre n'est cependant pas au programme de terminale NSI.
Schémas de relations
Définition
Comme vu précédemment, la modélisation relationnelle d'une base de données peut être décrite par un ensemble de schémas de relation.
C'est la représentation que vous retrouverez le plus souvent dans les sujets de bac (avec parfois des variantes dans la notation des clés primaire et étrangère notamment).
Exemple : Structure de la base de données du CDI du lycée décrite par l'ensemble des schémas de relation :
Livre(id_livre: Entier,
isbn: Chaîne de caractères,
titre: Chaîne de caractères,
auteur: Chaîne de caractères,
annee_premiere_parution: Entier,
editeur: Chaîne de caractères)
Eleve(id_eleve: Entier,
nom: Chaîne de caractères,
prenom: Chaîne de caractères,
date_naissance: Chaîne de caractères)
Emprunt(#id_livre: Entier
#id_eleve: Entier,
date_retour : Chaîne de caractères)
Les schémas de relation indiquent que :
- Chaque exemplaire de livres du CDI est associé à la clé primaire id_livre
- Chaque élève inscrit dans le CDI est associé à la clé primaire id_eleve
-
Chaque emprunt au CDI est associé à la clé primaire id_livre qui s'avère être aussi clé étrangère associée à la clé primaire id_livre de la relation Livre.
En d'autres termes un emprunt ne concerne qu'un seul et unique livre.
La relation Emprunt met aussi en évidence la clé étrangère id_eleve associée à la clé primaire id_eleve de la relation Eleve. Cette clé étrangère id_eleve peut apparaître dans plusieurs entités de la relation Emprunt.
En d'autres termes un élève inscrit au CDI peut emprunter autant de livre qu'il le souhaite.
Modélisation conceptuelle de données
Définition
La modélisation relationnelle d'une base de données peut aussi être décrite par un modèle conceptuel de données.
Exemple : Voici la représentation du modèle conceptuel de la base de données du CDI du lycée :
Cette représentation permet de mettre visuellement en évidence que dans cette base de données :
- Un exemplaire d'un livre donné peut être emprunté par minimum aucun élève ou par maximum 1 élève (d'où le 0 , 1 indiqué sur la liaison de droite) ;
- Un élève donné peut emprunter 0 exemplaire de livres (minimum) ou un nombre n d'exemplaires de livres (maximum) (d'où le 0 , n indiqué sur la liaison de gauche).
Modélisation logique de données
Définition
Une modélisation relationnelle d'une base de données peut être aussi décrite par un modèle logique de données.
C'est la représentation graphique à la plus "proche" du schéma relationnel.
Exemple : Voici la représentation du modèle logique des données du CDI du lycée :
Dans cette représentation, chaque rectangle correspond à une table (donc à un schéma de relation). Sur le schéma ci-dessus, la légende y est la suivante :
- Souligné en gras : Clé primaire
- Italique en bleu : Clé étrangère
- Souligné en gras et Italique en bleu : Clé étrangère faisant office de clé primaire
Application
Exercice 10 : Cas de l'association canine
Reprendre les schémas de relation issus de l'application précédente, à relire ici, puis proposer, en respectant la légende décrite ci-dessus, une représentation possible pour le modèle logique des données de l'association canine.
Exercice 11 : Cas de la modélisation de l'historique des stocks de vaccins
Proposer une modélisation relationnelle de la base de données portant sur l'historique des stocks de vaccins contre la Covid-19 par département sous la forme :
- de schémas de relation
- d'un MLD