Le modèle relationnel

Un peu d'histoire

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 qui repose sur des principes mathématiques mis en avant par E.F. Codd (laboratoire de recherche d’IBM) est souvent implémentée dans une base de données.

1970	Edgar Franck Cood pose les bases du modèle relationnel
1974	Création du langage SQL
1979	Création du premier système de gestion de base de données Oracle
1980	Le volume mondial de données stockées est estimé à \(10^{18}\) octets
1990	Le volume mondial de données stockées est estimé à \(10^{19}\) octets
1995	Première version du langage MySQL
2002	Le volume mondial de données stockées est estimé à \(10^{20}\) octets
2010	Le volume mondial de données stockées est estimé à \(10^{21}\) octets
2014	Le volume mondial de données stockés est estimé à \(10^{22}\) octets

Activité 1 - Les limites des outils traditionnels

Prenons l’exemple d’une médiathèque. Elle souhaite recenser les ouvrages qu’elle possède. Voici le tableau qu’elle a réalisé.

- Identifiez les redondances dans ce tableau, et les éléments qui semblent uniques. - Le livre de Ray Bradbury intitulé « Chroniques martiennes » est régulièrement indisponible car il est trop souvent emprunté. La médiathèque achète donc un deuxième exemplaire, paru chez le même éditeur. Il portera le numéro d’inventaire 236984. Complétez le tableau avec ce nouvel ouvrage. - Existe-t-il un élément unique qui va référencer totalement l’ouvrage? - La médiathèque s’ouvre depuis peu à la bande dessinée. Elle souhaite enregistrer l’album de bande dessinée en langue française L’incal noir , scénarisé par Alejandro Jodorowsky , dessiné par Moebius et édité par Les humanoïdes associés en 1982 . Quel est (sont) le(s) problème(s) rencontré(s)?

Correction

1. Redondances :
   • nom auteur (par ex : Bradbury)
   • prenom auteur (par ex : George)
   • editeur (par ex : Pocket)
   • langue (par ex : anglais)
   • annee publication (par ex : 1968) Eléments uniques :
   • titre
   • reference
2. On rajouter la ligne : Chroniques martiennes, Bradbury, Ray, Denoël, anglais, 1950, 236984
3. La référence est un élément unique qui référencera totalement un ouvrage.
4. Il n’y a pas qu’un seul auteur, alors qu’il n’y a qu’une colonne pour cela...

La base de données

Base de données

Une donnée est une information représentée sous une forme conventionnelle, afin de pouvoir être traitée automatiquement. Une base de données (BDD) représente un ensemble ordonné de données dont l’organisation est régie par un modèle.

Les données sont généralement regroupées selon leur appartenance à un objet du monde réel.

• Le regroupement d’objets homogènes constitue une entité .
• Une entité est décrite par un ensemble d’ attributs .
• Chacun de ces attributs prend une valeur pour chaque objet.

Exemple

• Entité : un être humain
• Attributs : nom, prénom, date de naissance, lieu de naissance
• Valeurs : Young, Neil, 12 novembre 1945, Toronto

Activité 2 - Carte d'identité

On souhaite représenter les données issues de la carte nationale d’identité. À partir de votre carte personnelle, donnez le nom de l’entité correspondante, les attributs qui lui sont associés et les valeurs qui vous sont propres.

Correction

Entité : Français

Attributs : nom, prénom, sexe, date de naissance, lieu de naissance, taille, adresse, date de validité, date de livraison, prefecture

Valeurs : ...

Vidéo : Le modèle relationnel

Le modèle relationnel

Modèle relationnel

Un des modèles de données le plus courant est le modèle relationnel. Les principes de base de ce modèle sont les suivants :

• séparer les données dans plusieurs tables
• chaque table contient des données relatives à un même sujet
• on évite la redondance des données
• on ne stocke pas des données qui peuvent être calculées (exemple : une ligne Total)
• mettre les tables en relation par l’utilisation de clés
• clés primaires : leurs valeurs (souvent des entiers) permettent d’identifier une donnée de manière unique
• clés étrangères : elles référencent une clé primaire d’une autre table

Activité 3 - Bon de commande

La société Le Coin, située à Caen, a commandé des produits chimiques à la société BonPrixChim.

Le bon de commande est indiqué ci-dessous :

À partir de ce bon de commande, on peut séparer les informations en 3 entités :

• une entité commande qui va regrouper les données de la commande;
• une entité client qui va regrouper les données du client;
• une entité produits qui va regrouper les données d’un détail.

1. Établissez, pour chaque entité, la liste des attributs.
2. Représentez chaque entité par une table contenant les attributs et la liste de leurs valeurs.
3. Pensez-vous qu’il y ait des redondances? S’il y en a, modifiez les tables pour les supprimer.
4. En réalité, la table Produits n’est pas pertinente. Elle ne devrait contenir que les informations associées à un produit. Quels seraient ses attributs?
5. Construisez une quatrième table intitulé Details reprenant les attributs manquants.
6. Comment éditera-t-on le bon de commande?

Correction

1) Entité : Commande
Attributs : numéro de commande, date

Entité : Client
Attributs : numéro de client, nom, adresse, ville

Entité : Produits
Attributs : référence, libellé, prix, quantité

2) Commandes

Numéro de commande	Date
14010	24/09/2014

Clients

Numéro de client	Nom	Adresse	Ville
BD2014	LECOIN	24 RUE SAINT-JEAN	CAEN

Produits

Référence	Libellé	Prix	Quantité
190464K	Calcium chlorure 1 mol/l	77	10
31535.292	Sodium chlorure 1 mol/l	105	15
30024.290	Acide chlorhydrique 1 mol/l (1 N)	41	3
30917.320	Iode 0,05 mol/l (0,1 N)	117	8

3) Non
4) Référence, Libellé, et Pris
5) Détails : Référence et Quantités
6) Il faudra faire des liens entre les tables.
Il y aura un problème du fait de l’absence de l’attribut numero commande dans la table Details et de l’absence de l’attribut numero client dans la table Commande)

La relation

Relation

Une relation (on parle aussi de table) est composée d’un en-tête (le libellé des attributs ) et d’un corps composé d’un ou plusieurs t-uplets (on parle aussi d’enregistrement).

Domaine de valeurs d'un attribut

Chaque valeur possède un type et un ensemble fini ou non des valeurs possibles. Cela constitue le domaine de valeurs d’un attribut.

Exemple

• L’attribut idAuteur de la relation Auteurs est un entier positif. Son type est entier. Le domaine de valeur correspond à tous les entiers de 0 à \(+\infty\) .
• L’attribut titre de la relation Livres est une chaîne de caractères. Son type est chaîne de caractères . Le domaine de valeur correspond à toutes les chaînes de caractères.

Activité 4 - Domaine de valeurs

Déterminez le domaine de valeurs des attributs de la relation Produits .

reference	libelle	prix
1900464K	Calcium chlorure 1 mol/L	77
31535.292	Sodium chlorure 1 mol/L (1N)	105
30024.290	Acide chlorhydrique 1 mol/L (1N)	41
30917.320	Iode 0,05 mol/L (0,1 N)	936

Correction

1. Domaine de valeur de :
   • reference : toutes les chaînes de caractères
   • libelle : toutes les chaînes de caractères
   • prix : tout réel supérieur strict à 0

Clé primaire d'une relation

Clé primaire

À l’intérieur d’une relation, deux t-uplets identiques ne sont pas autorisés. Il faut pouvoir identifier de façon unique un t-uplet.

Une clé primaire permet d’identifier un t-uplet de manière unique.

Il faut déterminer, parmi les attributs, lequel permet d’identifier de manière unique un t-uplet.

Cet attribut sera considéré comme la clé primaire de la relation.

Dans la figure précédente :

• L’attribut auteur ne peut pas jouer le rôle de clé primaire (deux ouvrages pouvant avoir le même auteur).
• De même pour les attributs titre et annPubli .
• Il reste donc l’attribut idLivre (pour identifiant), qui a été ajouté ici pour jouer le rôle de clé primaire.

Remarque

Ici, nous avons créé artificiellement une clé primaire, car aucun des autres attributs ne pouvait convenir (ce n’est pas toujours le cas).

Clé étrangère d'une relation

Clé étrangère

Dans la relation Auteurs , chaque auteur est identifié par l’attribut idAuteur (clé primaire de la relation).

Dans la relation Livres , on a rajouté un attribut idAuteur qui est la clé primaire de la relation Auteurs .

Une clé étrangère référence une clé primaire d’une autre table.

L’attribut idAuteur est ce que l’on nomme une clé étrangère de la relation Livres , elle permet de faire le lien entre les deux relations.

Remarque

Il peut y avoir plusieurs clés étrangères dans une relation.

Activité 5 - Clés primaires et étrangères

1. Reprenez les relations des bons de commande, en identifiant une clé primaire pour chacune des tables, ou en en introduisant une si elle n’existe pas.
2. Justifiez que dans la relation Details , les attributs reference et numero commande ne peuvent pas constituer individuellement une clé primaire.
3. Justifiez que dans la relation Details , le couple (reference, numero commande) constitue une clé primaire.
4. Identifiez les clés étrangères.

Correction

1. Commande : Numéro de commande
   Client : Numéro de client
   Produit : Référence
   Détail : Référence, numero de commande
2. Pour une même quantité, il peut y avoir différentes références et différents numéros de commande
3. Pour avoir l’unicité du détail d’un produit, il faut connaître la référence ET le numéro de commande .
4. Clé étrangère : numéro client, référence et numéro de commande

Représentations du modèle relationnel

Représentation

Le schéma d’une relation définit cette relation. Il est composé :

• du nom de la relation (on la place en tête, et en gras),
• de la liste de ses attributs avec les domaines respectifs dans lesquels ils prennent leurs valeurs (cette liste est placée entre parenthèses),
• de la clé primaire (elle est soulignée),
• des clés étrangères (on place un # entre la clé étrangère et la référence),
• des autres valeurs.

L’ensemble des relations peut être représenté soit par un schéma, soit par une notation textuelle.

Exemple : notation textuelle

Livres (idLivre, titre, idAuteur # Auteurs(idAuteur), annPubli)
Auteurs (idAuteur, nom, prenom, langueEcriture)

Exemple : schéma relationnel

Activité 6 - Notation textuelle et schéma relationnel

Les relations de l'activité "Bon de commande" sont représentées ci-dessous :

Les clés primaires et étrangères ont été définies précédemment.

1. Écrivez la notation textuelle du modèle relationnel.
2. Réalisez le schéma relationnel.

Correction

1. 
   Commande : (Numéro de commande, # numéro client, date)
   Client : (Numéro de client, nom, adresse, ville)
   Produit : (Référence, libelle, prix)
   Détail : (# Référence, # numero de commande, quantite)
2. 
   

Les contraintes d'intégrité

Contraintes d'intégrité

Il est important d’assurer la cohérence et donc l’intégrité des données présentes dans une base de données. Cela consiste à s’assurer que les données stockées sont cohérentes entre elles, c’est à dire qu’elles respectent toutes les règles exigées par le concepteur de la base de données. C’est une assertion vérifiée par les données de la base, à tout moment.

Une contrainte d’intégrité est une règle appliquée à un attribut ou une relation et qui doit toujours être vérifié.

Les contraintes d’intégrité sont vérifiées par le système de gestion des bases de données (SGBD).

Si l’une des règle n’est pas respectée, le SGBD signalera cette erreur et n’autorisera pas l’écriture de cette nouvelle donnée.

Contrainte de domaineContrainte de relationContrainte de référence

Chaque attribut doit prendre une valeur dans son domaine de valeurs.

Exemple

• La note obtenue dans une matière doit être comprise entre 0 et 20;
• la quantité commandée est obligatoire ET doit être strictement supérieure à 0.

Chaque relation dans le modèle relationnel est identifiée par une clé primaire qui doit être unique et non nulle . Donc, chaque t-uplet est également identifié par une clé primaire.

Une clé étrangère dans une relation doit être une clé primaire dans une autre. De plus, le domaine de valeurs de ces deux clés doit être identique. Enfin, la valeur d’une clé étrangère doit exister dans la clé primaire qui y fait référence.

Exemple

Dans la figure ci-dessous, la valeur de la clé étrangère idAuteur ne peut pas être supérieure à 8.

Activité 7 - Médiathèque

Dans l’exemple de la Médiathèque, on souhaite créer 3 relations :

• Auteur , contenant toutes les informations sur un auteur
• Livre , contenant toutes les informations sur un livre

• Ouvrage , contenant toutes les informations sur un ouvrage disponible à l’emprunt.

• Pour chacune des relations, indiquez les attributs avec leur type et leur domaine de valeurs.

• Pour chacune des relations, indiquez la clé primaire et éventuellement la ou les clé(s) étrangère(s).
• Représentez le schéma relationnel correspondant, ainsi que la notation textuelle qui lui est associée.

Correction

1) 2) 3)
Auteur(idAuteur, nomAuteur, prenomAuteur,langue) Livre(idLivre,titre,#idAuteur,annee) Ouvrage(#idLivre,quantité)

Domaine de valeur : - idAuteur, idLivre : nombre entier strictement positif - nomAuteur, prenomAuteur, langue, titre, annee : toute chaîne de caractères - quantité : nombre entier positif ou nul

3) ...

Activité 8 - Laboratoire médical

Un laboratoire souhaite gérer les médicaments qu’il conçoit :

• Un médicament est décrit par un nom, qui permet de l’identifier. En effet il n’existe pas deux médicaments avec le même nom.
• Un médicament comporte une description courte en français, ainsi qu’une description longue en latin.
• On gère aussi le conditionnement du médicament, c’est à dire le nombre de pilules par boîte (qui est un nombre entier).
• À chaque médicament on associe une liste de contre-indications, généralement plusieurs, parfois aucune.
• Une contre-indication comporte un code unique qui l’identifie, ainsi qu’une description.
• Une contre-indication est toujours associée à un et un seul médicament.

Voici deux exemples de données :

• Le Chourix a pour description courte « Médicament contre la chute des choux » et pour description longue « Vivamus fermentum semper porta. Nunc diam velit, adipiscing ut tristique vitae, sagittis vel odio. Maecenas convallis ullamcorper ultricies. Curabitur ornare. ». Il est conditionné en boîte de 13.
  Ses contre-indications sont :

• CI1 : Ne jamais prendre après minuit.

• CI2 : Ne jamais mettre en contact avec de l’eau.

• Le Tropas a pour description courte « Médicament contre les dysfonctionnements intellectuels » et pour description longue « Suspendisse lectus leo, consectetur in tempor sit amet, placerat quis neque. Etiam luctus porttitor lorem, sed suscipit est rutrum non. ». Il est conditionné en boîte de 42.
  Ses contre-indications sont :

• CI3 : Garder à l’abri de la lumière du soleil.

1. Donnez la représentation sous forme de tables des relations Medicament et ContreIndication .
2. Écrivez le schéma relationnel permettant de représenter une base de données pour ce laboratoire.

Correction

1)
Médicament

nomMedicament	descriptionCourte	descriptionLongue	quantite
Chourix	Médicament contre la chute des choux	Vivamus fermentum semper porta. Nunc diam velit, adipiscing ut tristique vitae, sagittis vel odio. Maecenas convallis ullamcorper ultricies. Curabitur ornare.	13
Tropas	Médicament contre les dysfonctionnements intellectuels	Suspendisse lectus leo, consectetur in tempor sit amet, placerat quis neque. Etiam luctus porttitor lorem, sed suscipit est rutrum non	42

ContreIndication :

idCi	nomMedicament	descriptionCI
CI1	Chourix	Ne jamais prendre après minuit
CI2	Chourix	Ne jamais mettre en contact avec de l’eau
CI3	Tropas	Garder à l’abri de la lumière du soleil

2)
Medicament(nomMedicament, descriptionCourte, descriptionLongue, quantite)

ContreIndication(idCI, # nomMedicament, descriptionCI)

• Domaine de valeur :
  • nomMedicament, descriptionsCI,descriptionCourte : chaine de caractère en français
  • descriptionLongue : chaine de caractère en latin
  • idCI , quantite : entier strictement positif