C6.1 : Bases de données relationnelles
Croissance de l'utilisation des données
Généralités
Le développement des traitements informatiques nécessite la manipulation de données de plus en plus nombreuses. Leur organisation et leur stockage constituent un enjeu essentiel de performance. Le recours aux bases de données relationnelles est aujourd'hui une solution très répandue. Ces bases de données permettent d’organiser, de stocker, de mettre à jour et d’interroger des données structurées volumineuses utilisées simultanément par différents programmes ou différents utilisateurs.
Ce qui a été vu en 1ère
En première, les données en tables ont été étudiées, et particulièrement les données en tables stockées dans des fichiers csv.
Nous verrons cette année un modèle de données plus complexe : les bases de données relationnelles.
Limites de la croissance continue des données
Modèle relationnel et base de données relationnelle
Histoire
Les bases de données relationnelles ont été proposées par Edgar Franck Codd (informaticien britannique 1923-2003) en 1970.
Les tables
Présentation
Une base de données relationnelle est une base de données dans plusieurs tableaux à deux dimensions.
Chaque tableau est appelé une table ou une relation
Exemple de table :
| titre | auteur | année |
|---|---|---|
| Dune | Frank HERBERT | 1965 |
| La Horde du contrevent | Alain DAMASIO | 2004 |
| La stratégie Ender | Orson Scott CARD | 1985 |
| Les Thanatonautes | Bernard WERBER | 1994 |
| L'Aube des dragons | Anne McCAFFREY | 1988 |
| Le Septième Fils | Orson Scott CARD | 1987 |
Les entités
Chaque ligne d'une table est une entité (on parle également d'enregistrement). Chaque entité est représentée par un n-upplet, c'est-à-dire un ensemble de n informations distinctes.
Exemple d'entité d'une table : Dans la table précédente, chaque entité correspond à un livre avec trois informations (le titre, l'auteur et l'année de publication).
Les attributs et leurs domaines
Toutes les entités d'une table (donc toutes les entités liées par la même relation) répondent au même schéma qui correspond aux colonnes de la table.
Les colonnes d'une table correspondent aux différentes informations des entités. On les appelle les attributs de la table.
Pour chaque attributs, on précise l'ensemble des valeurs que cet attribut peut prendre. Cet ensemble est appelé le domaine de l'attribut.
Exemple : Dans la table précédente, les attributs sont titre, auteur et année.
Exemples de domaines d'un attribut :
- - "entier positif",
- - "chaine de 8 caractères maximum",
- - "date au format jj/mm/aaaa"
- - ...
Clé primaire
L'une des règles des bases de données relationnelles est que les tables ne peuvent avoir deux entités identiques.
On définit alors une clé primaire, c'est-à-dire un attribut (ou une combinaison d'attributs) qui est différent pour chaque entité.
Très souvent, c'est un attribut spécifique, appelé id (pour identité) et dont le domaine est les entiers positifs, qui sert de clé primaire.
Exemple : Dans la table précédente, l'attribut titre ou l'attribut année pourraient servir de clé primaire, mais pas l'attribut auteur. Il serait également possible d'ajouter un attribut id au schéma de la relation, la table serait alors la suivante :
| id | titre | auteur | année |
|---|---|---|---|
| 1 | Dune | Frank HERBERT | 1965 |
| 2 | La Horde du contrevent | Alain DAMASIO | 2004 |
| 3 | La stratégie Ender | Orson Scott CARD | 1985 |
| 4 | Les Thanatonautes | Bernard WERBER | 1994 |
| 5 | L'Aube des dragons | Anne McCAFFREY | 1988 |
| 6 | Le Septième Fils | Orson Scott CARD | 1987 |
Remarque : Dans le schéma relationnel d'une table, il est d'usage de souligner le nom de l'attribut qui sert de clé primaire.
Exemple : Le schéma relationnel de la table ci-dessus est LIVRES(id : Int, titre : String, auteur: String, année: Int).
Schéma relationnel d'une table
Le schéma relationnel d'une table est la liste des attributs en précisant leurs domaines, en précisant la clé primaire et les éventuelles clés étrangères.
Exemple de schéma relationnel : le schéma relationnel de la table précédente peut s'écrire LIVRES(titre : String, auteur: String, année: Int).
Liens entre les tables
Présentation
Une base de données est en général constituée de plusieurs tables.
Il est alors possible, dans une table, de faire référence aux données d'une autre table.
Exemple
| id | nom | nationalité |
|---|---|---|
| 1 | Frank HERBERT | Américain |
| 2 | Alain DAMASIO | Français |
| 3 | Orson Scott CARD | Américain |
| 4 | Bernard WERBER | Français |
| 5 | Anne McCAFFREY | Américain |
| id | titre | id_auteur | année |
|---|---|---|---|
| 1 | Dune | 1 | 1965 |
| 2 | La Horde du contrevent | 2 | 2004 |
| 3 | La stratégie Ender | 3 | 1985 |
| 4 | Les Thanatonautes | 4 | 1994 |
| 5 | L'Aube des dragons | 5 | 1988 |
| 6 | Le Septième Fils | 3 | 1987 |
Dans cet exemple, l'attribut id_auteur permet d'indiquer l'auteur de chaque entité de la table "Livres" par un lien vers une entité de la table AUTEURS
Clé étrangère
Une clé étrangère est un attribut (ou un ensemble d'attributs) d'une relation dont les valeurs correspondent aux valeurs de la clé primaire d'une autre table. Une clé étrangère permet donc, dans une relation, de faire référence aux entités d'une autre relation.
Remarque : Dans le schéma relationnel d'une table, il est d'usage de faire précéder d'un # le nom de l'attribut qui sert de clé étrangère.
Exemple : Dans l'exemple précédent, l'attribut id_auteur est appelé une clé étrangère et fait référence à la table AUTEURS.
Représentation de la structure d'une base de données relationnelle
Écriture d'une base de données à partir des relations
Une base de données relationnelle est entièrement décrite par :
- • Les relations des différentes tables (en précisant les clés primaires) sous la forme NOM_TABLE(attr1:domaine1, attr2:domaine2...). Par convention, l'attribut souligné est la clé primaire.
- • Les liens entre les tables, en précisant les clés étrangères et les tables auxquelles elles font référence. Par convention, l'attribut correspondant à une clé étrangère est précédé du symbole #.
Schématisation d'une base de données relationnelle
Il est possible de représenter une base de données relationnelle sous la forme d'un diagramme.
Intégrité d'une base de données relationnelle
Une base de données relationnelle est dite intègre si elle respecte les trois contraintes d'intégrité qui ont été évoquées précédemment, et qui sont résumées ici :
- - contrainte de domaine : il faut que les données appartiennent aux domaines des attributs ;
- - contrainte de relation : chaque entité d'une table (d'une relation) doit être identifiée par une clé primaire, cette clé primaire (un attribut, ou une combinaison d'attributs) doit permettre d'identifier les entités par des valeurs uniques ;
- - contrainte de référence : lorsque l'attribut d'une table fait référence (clé étrangère) aux données d'une autre table, il faut obligatoirement utiliser les valeurs de la clé primaire de cette autre table ;
Système de gestion de bases de données relationnelles
Présentation
Une base de données est un fichier (ou un ensemble de fichiers) qui contient les données.
Pour manipuler ces données, il est nécessaire d'utiliser un outil spécifique appelé Système de Gestion de Base de Données ou SGBD.
• Le SGBD permet de créer ou de modifier la structure d'une base de données :
- - de créer les tables d'une base de données (définir les attributs de chaque table) ;
- - de préciser les contraintes de domaine des attributs ;
- - de préciser les contraintes de relation de chaque table (la clé primaire) ;
- - de préciser les contraintes de référence lors de l'établissement de liaisons entre les tables.
• Le SGBD permet d'ajouter, de modifier ou de supprimer des données, tout en assurant que les données respectent les contraintes d'intégrité de la base.
• Le SGBD permet, à l'aide d'un langage de requêtes, d'interroger la base de données : il permet de sélectionner certaines données de la base en fonction de critères spécifiés.
Plus globalement :
- - le SGBD gère d'éventuels droits d'accès aux données ;
- - le SGBD gère les accès multiples à la base (accès concurrents) de façon que deux personnes ne modifient pas les données en même temps...
Enfin, le SGBD peut assurer également la sûreté des données : duplication des données en cas de panne, sauvegarde...
Quelques exemples de SGBD
- Logiciel Acces, de la suite bureautique de Microsoft
- Logiciel Base, de la suite OpenOffice
- MySql, utilisé côté serveur par des sites sur le web
- ...
Cette année nous utiliserons DB Browser (SqLite) qui est un logiciel libre.
Exercices
Dans tous les exercices, on soulignera le nom de l'attribut qui sert de clé primaire et on fera précéder par un # les noms des attributs qui servent de clé secondaire.
Exercice 1
Proposer le schéma d'une base de données permettant de stocker la liste des élèves du lycée.
Exercice 2
On considère la table ci-dessous.
| 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 |
1) Expliquer comment optimiser cette base de donnée.
2) Donner le schéma final de la base de données obtenue en précisant les contraintes de relation et de références.
Exercice 3
Proposer le schéma d'une base de plusieurs tables permettant de gérer le prêt des livres au CDI du lycée.
Exercice 4
On considère la base de données relationnelle représentée par les tables ci-dessous :
| id | nom | adresse | |
|---|---|---|---|
| 1 | Jean Bon | 2 rue Jean Mermoz - Hobbitebourg | jean.bon@free.fr |
| 2 | Alain Térieur | 3 rue Paul Eluard - Fondcombe | alain.terieur4@hotmail.com |
| 3 | Ines Timable | 16 rue du Monde - Latère | ines.timable@orange.fr |
| 4 | Jean Némar | 1 place de la bataille - La Comté | jean14@sfr.fr |
| 4 | Hélène de Troie | 2 rue Néper - La Moria | ln23@laposte.net |
| ... | ... | ... | ... |
| Code_produit | Nom_produit | prix | stock |
|---|---|---|---|
| 12x24F | clé usb 8 Go | 13.2 | 2 |
| 21s53R | webcam | 35,5 | 3 |
| 97D74S | clé usb 16 Go | 18.50 | 10 |
| 10F36A | coque de téléphone | 13,3 | 5 |
| ... | ... | ... | ... |
| id | client | produit | quantite | expedie | date |
|---|---|---|---|---|---|
| 1 | 4 | 12x24F | 1 | 1 | 5/9/2020 |
| 2 | 2 | 21s53R | 2 | 0 | 5/9/2020 |
| 3 | 1 | clé usb 8 Go | 1 | 1 | 5/9/2020 |
| 4 | 3 | 10F36A | 4 | non | 6/9/2020 |
| 5 | 1 | 97D74S | 3 | 0 | 6/9/2020 |
1) Cette base est-elle intègre ? Justifier.
2) Modifier cette base pour la rendre intègre.
3) Donner le schéma relationnel de cette base.