Les bases de données - généralités
I. Présentation des bases de données⚓︎
En première nous avons eu l'occasion de travailler sur des données structurées en les stockant dans des fichiers au format CSV. Même si cette méthode de stockage de l'information peut s'avérer pratique dans certains cas précis, il est souvent souhaitable d'utiliser une base de données pour stocker des données.
👉 Une petite video pour commencer sur l'histoire des bases de données :
👉 Une autre vidéo importante pour introduire la notion de base de données :
👉 La vidéo de Lumni :
Le terme base de données est apparu au début des années 60. L'apparition des disques durs à la fin des années 50 a permis d'utiliser de stocker et manipuler des données. Avec l'apparition du Web, la quantité de données à stocker a explosé. Aujourd'hui, la plupart des sites internet (du petit site personnel au grand site d'e-commerce) utilisent au moins une base de données.
Les bases de données jouent un rôle fondamental dans notre monde devenu numérique où il est extrêmement facile de dupliquer l'information. Voilà pourquoi nous allons cette année les étudier. Il existe différents types de bases de données. Les bases de données relationnelles sont le plus utilisées au monde, c'est ce type de base de données que nous allons étudier.
Bases de données relationnelles
Les bases de données relationnelles ont été mises au point en 1970 par Edgar Franck Codd, informaticien britannique (1923-2003). Ces bases de données sont basées sur la théorie mathématique des ensembles.
Définition
Une base de données est un ensemble structuré d'informations.
- Dans le langage courant, elle peut désigner n'importe quelle source importante d'informations (dictionnaires, encyclopédies, etc.)
- En informatique, il s'agit d'informations stockées sous forme de fichiers et organisées de façon à être facilement manipulées.
II. Vocabulaire⚓︎
La notion de relation est au cœur des bases de données relationnelles.
Un peu de vocabulaire
Une relation, aussi appelée table, peut être vue comme un tableau à 2 dimensions, composé :
- d'une en-tête (intitulés des attributs)
-
d'un corps qui est composé :
- d'enregistrement ou p-uplets (lignes)
- d' attributs(colonnes)
En pratique
On parlera indifféremment de table ou relation, ainsi que d'enregistrement ou p-uplet
L'enregistrement (p-uplet) encadré en rose sur le schéma ci-contre contient les éléments suivant :
⇢ 10, Les Robots, Azimov, 1950 et 10.
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.
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)
👉 Voici un exemple de table : La relation "livre"
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, Azimov, Herbert, Huxley, Bredbury, K.Dick, Barjavel, Boulle.
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.
III. Unicité d'un enregistrement & clé primaire - primary key⚓︎
Autre contrainte très importante dans les bases de données relationnelles, une relation ne peut pas contenir 2 p-uplets identiques. Il faut donc pouvoir identifier de façon unique un p-uplet.
Par exemple, la situation ci-dessous n'est pas autorisée (ici aussi le SGBD veillera au grain) :
Afin d'être sûr de respecter cette contrainte des p-uplets identiques, on définit la notion de clé primaire de la relation.
Clé primaire
Une clef primaire est un attribut ou un ensemble d'attributs (couple, triplet, ...) dont la valeur permet d'identifier de manière unique un p-uplet de la relation.
Unicité
Autrement dit, si un attribut est considéré comme clé primaire, on ne doit pas trouver dans toute la relation 2 fois la même valeur pour cet attribut.
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" !
2. 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) |
| 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.
IV. Clé étrangère - foreign key (fk)⚓︎
Revenons à notre relation "livre". Nous désirons maintenant un peu enrichir cette relation en ajoutant des informations supplémentaires sur les auteur, nous obtenons alors :
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 (2 tables) au lieu d'une seule et créer un "lien" entre ces 2 relations.
Relation auteur
| id_auteur | 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 livre
| 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 auteur et nous avons modifié la relation livre :
- Dans la relation auteur, chaque auteur est identifié par l'attribut "id_auteur"(clé primaire de la relation)
- Dans la relation livre, on a rajouté un attribut "id_auteur" qui est la clé primaire de la relation auteur.
- 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_auteur" (clé primaire) de la relation auteur.
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).
Dans l'exemple ci-dessus, l'attribut "id_auteur" de la relation livre permet bien d'établir un lien entre la relation livre et la relation auteur, "id_auteur" correspond bien à la clef primaire de la relation auteur, conclusion : "id_auteur" est une clé étrangère.
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_realisateur | 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_realisateur 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 #)
Pour notre exemple et les relations livre et auteur :
👉 Schéma relationnel :
auteur(id_auteur : 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 :
👉 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.
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_realisateur : 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.
VI. Exercices⚓︎
Exercice 1⚓︎
(d'après Prépabac NSI, Terminale, G.CONNAN, V.PETROV, G.ROZSAVOLGYI, L.SIGNAC, éditions HATIER.)
Deux relations modélisent la flotte de voitures d'un réseau de location de voitures.
Agences
| id_agence | ville | département |
|---|---|---|
| 1 | Paris | 75 |
| 2 | Lyon | 69 |
| 3 | Marseille | 13 |
| 4 | Aubagne | 13 |
Voitures
| id_voiture | marque | modèle | kilométrage | couleur | id_agence |
|---|---|---|---|---|---|
| 1 | Renault | Clio | 12000 | Rouge | 2 |
| 2 | Peugeot | 205 | 22000 | Noir | 3 |
| 3 | Toyota | Yaris | 33000 | Noir | 3 |
Questions
- Combien la relation
Voiturescomporte-t-elle d'attributs ? - Quel est le domaine de l'attribut
id_agencedans la relationVoitures? - Quel est le schéma relationnel de la relation
Agences? - Quelle est la clé primaire de la relation
Agences? - Quelle est la clé primaire de la relation
Voitures? - Quelle est la clé étrangère de la relation
Voitures?
- 6
- Entier (
Int) - (id_agence : Int, ville : String, département :Int)
id_agenceid_voitureid_agence
Exercice 2⚓︎
Prenons la base Tour de France 2020 :
Relation Équipes
| codeEquipe | nomEquipe |
|---|---|
| ALM | AG2R La Mondiale |
| AST | Astana Pro Team |
| TBM | Bahrain - McLaren |
| BOH | BORA - hansgrohe |
| CCC | CCC Team |
| COF | Cofidis, Solutions Crédits |
| DQT | Deceuninck - Quick Step |
| EF1 | EF Pro Cycling |
| GFC | Groupama - FDJ |
| LTS | Lotto Soudal |
| ... | ... |
Relation Coureurs
| dossard | nomCoureur | prénomCoureur | codeEquipe |
|---|---|---|---|
| 141 | LÓPEZ | Miguel Ángel | AST |
| 142 | FRAILE | Omar | AST |
| 143 | HOULE | Hugo | AST |
| 11 | ROGLIČ | Primož | TJV |
| 12 | BENNETT | George | TJV |
| 41 | ALAPHILIPPE | Julian | DQT |
| 44 | CAVAGNA | Rémi | DQT |
| 45 | DECLERCQ | Tim | DQT |
| 121 | MARTIN | Guillaume | COF |
| 122 | CONSONNI | Simone | COF |
| 123 | EDET | Nicolas | COF |
| … | … | … | … |
Relation Étapes
| numéroEtape | villeDépart | villeArrivée | km |
|---|---|---|---|
| 1 | Nice | Nice | 156 |
| 2 | Nice | Nice | 185 |
| 3 | Nice | Sisteron | 198 |
| 4 | Sisteron | Orcières-Merlette | 160 |
| 5 | Gap | Privas | 198 |
| ... | ... | ... | ... |
Relation Temps
| dossard | numéroEtape | tempsRéalisé |
|---|---|---|
| 41 | 2 | 04:55:27 |
| 121 | 4 | 04:07:47 |
| 11 | 5 | 04:21:22 |
| 122 | 5 | 04:21:22 |
| ... | ... | ... |
Questions
- Quel temps a réalisé Guillaume MARTIN sur l'étape Sisteron / Orcières-Merlette ?
- À l'arrivée à Privas, qui est arrivé en premier entre Primož ROGLIČ et Simone CONSONNI ?
- Temps de Guillaume Martin (dossard 121): 04:07:47
- Aucun des deux, ils sont arrivés dans le même temps (04:21:22)
TD1⚓︎
😀 La correction est arrivée ...
TD2⚓︎
😀 La correction est arrivée ...
Crédits⚓︎
D'après David Roche, Pixees et Stéphan Van Zuijlen Lycée Jean Moulin adapté par François Halle, Valérie Mousseaux, Mireille COILHAC et Jean-Louis Thirot publié sous licence CC BY SA Exercices réalisés par Gilles Lassus.