Le modèle relationnel

1. Le modèle relationnel

Le programme de Terminale NSI prévoit uniquement l'étude du modèle relationnel.

p class="transition">Capsule vidéo expliquant le principe des bases de données relationnelles.

Théorisé en 1970 par le Britannique Edgard J. Codd, le modèle relationnel est à ce jour le modèle de base de données le plus utilisé, même si l'ère actuelle du Big Data tend à mettre en avant d'autres modèles non relationnels (nous en reparlerons).

Les principes de base du modèle relationnel

• Les données sont regroupées dans différentes tables (qu'on appellera plutôt relations et qui donnent son nom au modèle). Chaque relation contient des éléments directement en lien avec le sujet général de la table.
• Autant que possible, des données identiques ne doivent pas se trouver dans des tables différentes : on évite la redondance des données.
• Les données ne doivent pas contenir elles-mêmes d'autres données : on parle d'atomicité des données.

La notion de relation est au coeur des bases de données relationnelles.

Un peu de vocabulaire

Une relation peut être vue comme un tableau à 2 dimensions, composé d'un en-tête et d'un corps.

• Le corps est lui-même composé de t-uplets (lignes) et d'attributs (colonnes).
• L'en-tête contient les intitulés des attributs.

le corps contient les données proprement dites.

À noter que l'on emploie aussi le terme "table" à la place de "relation".

2. Première relation

Prenons l'exemple d'une bibliothèque dont la base de données possède une relation «Livres» :

Relation "Livres"

En pratique

On parlera indifféremment de table ou relation, ainsi que d'enregistrement ou p-uplet

L'enregistrement (p-uplet) encadré en jaune sur le schéma ci-dessus contient les éléments suivant :
⇢ 11, La Planète des singes, Boulle, 1963 et 8.

L'attribut "titre" en bleu est composé des éléments suivants :
⇢1984, Dune, Fondation, Le meilleur des mondes, Fahrenheit 451, Ubik, Chroniques martiennes, La nuit des temps, Blade Runner, Les Robots, La Planète des singes, Ravage, Le Maître du Haut Château, Le monde des Ā, La Fin de l’éternité et De la Terre à la Lune.

Vocabulaire

• relation, ou table : c'est l'endroit où sont rangées les données. L'ordre des lignes (que l'on appelera des enregistrements) n'a pas d'importance.
• enregistrement, ou tuple, ou n-uplet, ou t-uplet, ou vecteur : cela correspond à une ligne du tableau, et donc un ensemble de valeurs liées entre elles : l'auteur «Boulle» a bien écrit le livre «La planète des singes». Il est interdit que deux enregistrements soient totalement identiques. Le nombre d'enregistrements d'une relation s'appelle son cardinal.
• attribut : c'est l'équivalent d'une colonne. Il y a dans notre relation un attribut «code», un attribut «Titre», etc.

• Le domaine

  Pour chaque attribut d'une relation, il faut définir un domaine

  Le domaine d'un attribut est le type des valeurs possibles (entiers, flottants, chaînes de caractères, dates...).

  • INTEGER ou INT, INT permettent de coder des entiers sur 4 octets (-2.147.483.648 à 2.147.483.647) ou 2 octets (-32.768 à 32.767).
  • NUMERIC(X) désigne un entier de X chiffres au maximum.
  • DECIMAL(X,Y) ou NUMERIC(X,Y) où X et Y sont optionnels et désignent respectivement le nombre de chiffres maximum pouvant composer le nombre, et le nombre de chiffres après la virgule.
  • FLOAT(X), REAL avec X définissant la précision (nombre de bits de codage de la mantisse)..
  • TEXT, CHAR(X)
  • DATE (AAAA-MM-JJ)
  • DATETIME (AAAA-MM-JJ HH:MM:SS)
    L'attribut «auteur» est une chaîne de caractères, son domaine est donc TEXT.
    Par contre l'attribut «ann_publi» est un nombre, une année. Son domaine est donc Int.

• schéma : le schéma d'une relation est le regroupement de tous les attributs et de leur domaine respectif. Ici notre schéma serait : ((id, Entier), (titre, Chaîne de caractères), (Auteur, Chaîne de caractères), (ann_publi, Entier), (note, Entier))

👉 Reprenons la table : La relation "Livres"

Ici :

• le domaine de l'attribut "id" correspond à l'ensemble des entiers (noté INT) : la colonne "id" devra obligatoirement contenir des entiers.
• le domaine de l'attribut "titre" correspond à l'ensemble des chaînes de caractères (noté TEXT).
• le domaine de l'attribut "note" correspond à l'ensemble des entiers positifs.

Exercice

1. Faire la liste des éléments appartenant à l'attribut "auteur".

Solution

Orwell, Asimov, Herbert, Huxley, Bredbury, K.Dick, Barjavel, Boulle, Van Vogt, Verne.

2. Quel est, selon vous, le domaine de l'attribut "auteur" ?

Solution

TEXT

Renseigner le domaine

Au moment de la création d'une relation, il est nécessaire de renseigner le domaine de chaque attribut.
Le Systeme de Gestion de la Base de Données s'assure qu'un élément ajouté à une relation respecte bien le domaine de l'attribut correspondant : si par exemple vous essayez d'ajouter une note non entière (par exemple 8.5), le SGBD signalera cette erreur et n'autorisera pas l'écriture de cette nouvelle donnée.

3. Clé Primaire et Unicité d'un enregistrement

Une autre contrainte très importante dans les bases de données relationnelles, une relation ne peut pas contenir 2 t-uplets identiques .

Par exemple
la situation ci-dessous n'est pas autorisée (ici aussi c'est le SGBD qui veille au grain) :

id	titre	auteur	ann_publi	note
1	1984	Orwell	1949	10
2	Dune	Herbert	1965	8
2	Dune	Herbert	1965	8
3	Fondation	Asimov	1951	9

Afin d'être sûr de respecter cette contrainte des t-uplets identiques, on définit la notion de "clef primaire".

Clé primaire

Une clef primaire est un attributou un ensemble d'attributs (couple, triplet, ...) dont la valeur permet d'identifier de manière unique un t-uplet de la relation.
Autrement dit, si un attribut est considéré comme clef primaire, on ne doit pas trouver dans toute la relation 2 fois la même valeur pour cet attribut.

D'autre part une clé primaire :

• Ne doit pas être NULL (vide)
• Peut être composée d’une ou plusieurs colonnes

Attention

Le critère d'unicité n'est pas suffisant pour définir une clef primaire. Il faut en réalité respecter 3 critères :

• Unicité : 2 p-uplets ne peuvent pas avoir la même valeur de cet attribut
• Existence : Cet attribut est obligatoire, il a une valeur non nulle pour tous les p-uplets.
• Stabilité : La valeur de cet attribut n'est jamais modifiée. Ce critère peut sembler moins évident mais nous verrons plus loin que la clef primaire peut être utilisée comme clef étrangère dans une autre table. Dès lors, une modification de sa valeur obligerait à maintenir toutes les relations utilisant cette clef, ce qui poserait de graves problèmes.

Exercice : clé primaire

1.L'attribut "note" peut-il jouer le rôle de clef primaire ?

Solution

Non, car il est possible de trouver 2 fois la même note

2. L'attribut "ann_publi" peut-il jouer le rôle de clef primaire ?

Solution

Non, car il est possible de trouver 2 fois la même année.

3. L'attribut "auteur" peut-il jouer le rôle de clé primaire ?

Solution

Non, car il est possible de trouver 2 fois le même auteur.

4. L'attribut "titre" peut-il jouer le rôle de clé primaire ?

Solution

A priori oui, car l'attribut "titre" ne comporte pas 2 fois le même titre de roman. Mais, ce n'est pas forcément une bonne idée, car il est tout à fait possible d'avoir un même titre pour 2 romans différents. Par exemple, en 2013, l’Américaine Jill McCorkle et l’Anglaise Kate Atkison publiaient avec seulement six jours d’écart un livre intitulé "Life After Life" !

5. L'attribut "id" peut-il jouer le rôle de clef primaire ?

Solution

Il nous reste donc l'attribut "id". En fait, l'attribut "id" ("id" comme "identifiant") a été placé là pour jouer le rôle de clé primaire. A chaque fois qu'un roman est ajouté à la relation, son "id" correspond à l'incrémentation de l'id (id du nouveau=id de l'ancien+1) du roman précédemment ajouté.

👉 Il est donc impossible d'avoir deux romans avec le même id.

Un attribut "id"

Ajouter un attribut "id" afin qu'il puisse jouer le rôle de clé primaire est une pratique courante (mais non obligatoire) dans les bases de données relationnelles.

Dans le cas précis qui nous intéresse, il aurait été possible de ne pas utiliser d'attribut "id", car chaque livre édité possède un numéro qui lui est propre : l'ISBN, cet ISBN aurait donc pu jouer le rôle de clef primaire.

Un couple comme id

À noter qu'en toute rigueur, une clé primaire peut être constituée de plusieurs attributs, par exemple le couple ("auteur","titre") pourrait jouer le rôle de clé primaire (à moins qu'un auteur écrive 2 romans différents, mais portant tous les deux le même titre).

Exercice films

Voici un extrait d'une relation référençant des films :

• Listez les différents attributs de cette relation.
• Donnez le domaine de chaque attribut.
• Pour chaque attribut dire si cet attribut peut jouer le rôle de clef primaire

Solution

• id : INT - Clef primaire : oui
• titre : TEXT - Clef primaire : non (unicité)
• réalisateur : TEXT - Clef primaire : non (unicité)
• ann_sortie : DATE - Clef primaire : non (unicité)
• note : INT - Clef primaire : non (unicité)

Tous les attributs sont stables et existant (sauf la note qui pourrait ne pas exister). Seule l'id respecte le critère d'unicité dans tous les p-uplet.

Exercice réseau social

Voici le formulaire d'inscription à un nouveau réseau social, destiné aux enseignants :

Les données sont enregistrées dans une base de données.

Pour chaque attribut, précisez s'il satisfait les 3 critères :

• Unicité
• Existence
• Stabilité

Solution

Attribut	Critères
Nom	Non unique (homonymes), stable et existant (obligatoires)
Prénom	Non unique (homonymes), stable et existant (obligatoires)
email	Unique et existant, non stable
date de naissance	Non unique, stable et existant
Numen	Unique, stable mais non existant (non obligatoire)

Aucun attribut ne peut être clef primaire.

Solutions :

• Rendre le numen obligatoire à l'inscription
• Ajouter un attibut id à cette table.

4. Clé étrangère

Revenons à notre relation "Livres".
Nous désirons maintenant un peu enrichir cette relation en ajoutant des informations supplémentaires sur les auteurs, nous obtenons alors :

Relation LIVRES_AUTEURS

id	titre	nom_auteur	prenom_auteur	date_nai_auteur	langue_ecriture_auteur	ann_publi	note
1	1984	Orwell	George	1903	anglais	1949	10
2	Dune	Herbert	Frank	1920	anglais	1965	8
3	Fondation	Asimov	Isaac	1920	anglais	1951	9
4	Le meilleur des mondes	Huxley	Aldous	1894	anglais	1931	7
5	Fahrenheit 451	Bradbury	Ray	1920	anglais	1953	7
6	Ubik	K.Dick	Philip	1928	anglais	1969	9
7	Chroniques martiennes	Bradbury	Ray	1920	anglais	1950	8
8	La nuit des temps	Barjavel	René	1911	français	1968	7
9	Blade Runner	K.Dick	Philip	1928	anglais	1968	8
10	Les Robots	Asimov	Isaac	1920	anglais	1950	9
11	La Planète des singes	Boulle	Pierre	1912	français	1963	8
12	Ravage	Barjavel	René	1911	français	1943	8
13	Le Maître du Haut Château	K.Dick	Philip	1928	anglais	1962	8
14	Le monde des Ā	Van Vogt	Alfred Elton	1912	anglais	1945	7
15	La Fin de l’éternité	Asimov	Isaac	1920	anglais	1955	8
16	De la Terre à la Lune	Verne	Jules	1828	français	1865	10

Nous avons ajouté 3 attributs ("prenom_auteur", "date_nai_auteur" et "langue_ecriture_auteur").
Nous avons aussi renommé l'attribut "auteur" en "nom_auteur".

😢 Comme vous l'avez peut-être remarqué, il y a pas mal d'informations dupliquées

Par exemple, on retrouve 3 fois "K.Dick Philip 1928 anglais", même chose pour "Asimov Isaac 1920 anglais"...

Cette duplication

• est-elle indispensable ? Non !
• Est-elle souhaitable ? Non plus !

En effet, dans une base de données, on évite autant que possible de dupliquer l'information (sauf à des fins de sauvegarde, mais ici c'est toute autre chose).
Si nous dupliquons autant de données inutilement c'est que notre structure ne doit pas être la bonne !
Dans une base de donnée, on évite autant que possible la redondance d'informations.

🤔 Mais alors, comment faire pour avoir aussi des informations sur les auteur des livre ?

💡 La solution est relativement simple :

• travailler avec 2 relations au lieu d'une seule
et
• créer un "lien" entre ces 2 relations

Relation Auteurs

id	nom	prenom	ann_naissance	langue_ecriture
1	Orwell	George	1903	anglais
2	Herbert	Franck	1920	anglais
3	Asimov	Isaac	1920	anglais
4	Huxley	Aldous	1894	anglais
5	Bradbury	Ray	1920	anglais
6	K.Dick	Philip	1928	anglais
7	Barjavel	René	1911	français
8	Boulle	Pierre	1912	français
9	Van Gogt	Alfred Elton	1912	anglais
10	Vernes	Jules	1828	français

Relation Livres

id	titre	id_auteur	ann_publi	note
1	1984	1	1949	10
2	Dune	2	1965	8
3	Fondation	3	1951	9
4	Le meilleur des mondes	4	1931	7
5	Fahrenheit 451	5	1953	7
6	ubik	6	1969	9
7	Chroniques martiennes	5	1950	8
8	La nuit des temps	7	1968	7
9	Blade Runner	6	1968	8
10	Les Robots	3	1950	9
11	La planète des singes	8	1963	8
12	Ravage	7	1943	8
13	Le Maître du Haut Château	6	1962	8
14	Le monde des A	9	1945	7
15	La fin de l'éternité	3	1955	8
16	De la terre à la Lune	10	1865	10

Nous avons créé une relation Auteurs et nous avons modifié la relation Livres :

• Dans la relation Auteurs, chaque auteur est identifié par l'attribut "id"(clé primaire de la relation)
• Dans la relation Livres, on a rajouté un attribut "id_auteur" qui est la clé primaire de la relation Auteurs.
• L'attribut "id_auteur" est ce que l'on nomme une clé étrangère de la relation livre, elle permet de faire le lien entre les deux relations.
  👉 C'est une clé étrangère qui fait référence à l'attribut "id" (clé primaire) de la relation Auteurs.

L'introduction d'une relation auteur et la mise en place de liens entre cette relation et la relation livre permettent d'éviter la redondance d'informations. Remarque : il peut y avoir plusieurs clés étrangères dans une relation.

Définition : clé étrangère

Pour établir un lien entre 2 relations \(R_A\) et \(R_B\), on ajoute à \(R_A\) un attribut x qui prendra les valeurs de la clé primaire de \(R_B\).

Cet attribut x est appelé clé étrangère (l'attribut correspond à la clé primaire d'une autre table, d'où le nom).

Définition : Intégrité

Pour préserver l'intégrité d'une base de données, il est important de bien vérifier que toutes les valeurs de la clé étrangère correspondent bien à des valeurs présentes dans la clef primaire

Ici, nous aurions un problème d' intégrité de la base de données si une valeur de l'attribut "id_auteur" de la relation livre ne correspondait à aucune valeur de la clef primaire de la relation auteur.
Certains SGBD ne vérifient pas cette contrainte (ne renvoie aucune erreur en cas de problème), ce qui peut provoquer des comportements erratiques.

Exercice films

film
id	titre	ann_sortie	note_sur_10
1	Alien, le huitième passager	1979	10
2	Dune	1986	5
3	2001 : Odyssée de l'espace	1968	9
4	Blade Runner	1982	10

• En partant de la relation film ci-dessus, créez une relation réalisateur.
  Vous prendrez comme attributs de la relation réalisateur : id, nom, prenom et ann_naissance.
  😊 Vous chercherez toutes les informations nécessaires sur le Web.


• Modifiez ensuite la relation film afin d'établir un lien entre les relations film et réalisateur. Vous préciserez l'attribut qui jouera le rôle de clef étrangère.

Solution

réalisateur
id	nom	prénom	ann_naissance
1	Scott	Ridley	1937
2	Lynch	David	1946
3	Kubrick	Stanley	1928

film
id	titre	id_realisateur	ann_sortie	note_sur_10
1	Alien, le huitième passager	1	1979	10
2	Dune	2	1986	5
3	2001 : Odyssée de l'espace	3	1968	9
4	Blade Runner	1	1982	10

L'attribut id_realisateur de la table film est une clé étrangère. Elle fait référence à la clé primaire id de la table réalisateur

V. Schéma relationnel

Lorsqu’une base de données comporte plusieurs tables, l’ensemble des schémas de ces relations s’appelle le schéma relationnel de la base de données.

Définition : Schéma relationnel

On appelle schéma relationnel l'ensemble des relations présentes dans une base de données.
Quand on vous demande le schéma relationnel d'une base de données, il est nécessaire de fournir les informations suivantes :

• Les noms des différentes relations
• pour chaque relation, la liste des attributs et de leurs domaines
• pour chaque relation, la clef primaire (on la souligne)
• les clés étrangères (précédées d'un #)

Diagramme relationnel

Fréquemment, on présentera l'ensemble des renseignements d'un modèle relationnel sous forme d'un diagramme qui synthétise la composition des différentes tables et les relations entre elles.

Pour notre exemple avec les relations Livres et Auteurs

👉 Schéma relationnel :
auteur(id : INT, nom : TEXT, prenom : TEXT, ann_naissance : INT, langue_ecriture : TEXT)
livre(id : INT, titre : TEXT, #id_auteur : INT, ann_publi : INT, note : INT)

Ce schéma relationnel peut aussi être représenté de la façon suivante par un diagramme :

👉 La flèche part toujours de la clé étrangère pour aller vers la clé primaire à laquelle elle fait référence.

👉 La clé étrangère est précédée de #.

👉 La clé primaire est soulignée.

Exemple : une école

Dans une école, des étudiants peuvent s'inscrire à différents cours. Nous disposons de trois tables :

• la table Etudiants(id_etudiant, nom, prenom, email)
• la table Cours(id_cours, titre, professeur)
• la table Inscriptions(#id_etudiant, #id_cours, date_inscription)

Dans le schéma relationnel de la table inscription les deux attributs id_etudiant et id_cours sont soulignés, mais il y a une seule clé primaire. Cela signifie que le couple (#id_etudiant, #id_cours) est la clé primaire de la table Inscriptions.

On peut représenter ce schéma relationnel de la façon suivante par le diagramme :

Les deux attributs #id_etudiant et #id_cours sont soulignés. Par convention graphique, cela signifie que la clé primaire est le couple (#id_etudiant, #id_cours)

Exercice films - suite

Donnez le schéma relationnel de la base de données que vous avez définie dans l'exercice films

Solution

realisateur(id : INT , nom : TEXT, prénom : TEXT, ann_naissance : INT)

film(id : INT , titre : TEXT , # id_realisateur : INT , ann_sortie : INT ; note_sur_10 : INT)

Les contraintes d'intégrité

• contrainte de domaine : type de données (entier, texte, flottant, ...)
• contrainte de relation : chaque enregistrement d'une relation doit pouvoir être identifié par une clé primaire

• Lorsque les relations sont liées , les 3 règles suivantes doivent être respectées :

  • Une clé étrangère doit être une valeur qui est la clé primaire de la table à laquelle elle se réfère.
  • Un enregistrement de la table primaire ne peut être effacé s’il possède des enregistrements liés.
  • La clé primaire peut être changée dans la table primaire uniquement si un enregistrement considéré ne possède pas d'enregistrements liés.

Qualité d'un schéma relationel

Un schéma relationnel d’une base de données doit vérifier certains principes :

• Unicité / contrainte de relation : chaque relation ne peut contenir deux nuplets identiques ;

• Contrainte de domaine : la valeur d’un attribut doit appartenir au domaine de l’attribut ;

• Atomicité : une valeur d’un attribut est une donnée atomique, elle ne peut contenir d’autres données. Les types des attributs sont donc simples (entier, flottant, chaînes de caractères, booléens . . .) et ne peuvent être structurés (listes . . .) ;

• Non redondance : une donnée ne doit être représentée qu’une fois dans une table et des données identiques ne doivent pas se trouver dans des tables différentes. Une entité du monde réel ne doit être représentée que par un seul nuplet.

---

Bibliographie

• Numérique et Sciences Informatiques, Terminale, T. BALABONSKI, S. CONCHON, J.-C. FILLIATRE, K. NGUYEN, éditions ELLIPSES.
• Prépabac NSI, Terminale, G.CONNAN, V.PETROV, G.ROZSAVOLGYI, L.SIGNAC, éditions HATIER.
• Cours d'Olivier Lécluse, https://lecluseo.frama.io/leclusemaths/nsi/NSI_T/bdd/modrel/
• https://www.procyclingstats.com/

---