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, 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.
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 | 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"(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" (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 "id" 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 | 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 #)
Pour notre exemple avec les relations livre et auteur
👉 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 :
👉 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 :
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.
VI. Exercices⚓︎
QCM⚓︎
Auteur : Frédéric Junier
Questions
Modèle relationnel - bases de données
-
Dans la formalisation mathématique du modèle relationnel, quel est l'équivalent d'une ligne d'une table ?
- Un nuplet
- Un attribut
- Un domaine
- Une relation
-
Une base de données contient une seule table. Laquelle des propositions décrit correctement le contenu du schéma relationnel de cette relation ?
- Le nom de la relation, la liste des attributs (tous distincts), le domaine de valeur de chaque attribut et l'ensemble d'attribut(s) constituant la clef primaire souligné(s)
- La liste ordonnée de toutes les lignes puis des colonnes de la table
- Le plan physique de stockage des fichiers sur disque
- Les utilisateurs autorisés et leurs droits d'accès
-
On considère la relation suivante qui décrit les exemplaires de livres d'une bibliothèque :
cote_exemplaire titre auteur annee isbn EX001 L'Étranger Albert Camus 1942 9782070360024 EX002 L'Étranger Albert Camus 1942 9782070360024 EX003 Germinal Émile Zola 1885 9782253004226 EX004 Les Misérables Victor Hugo 1862 9782070409181 Quel attribut peut être choisi comme clé primaire pour identifier de façon unique chaque exemplaire ?
- cote_exemplaire
- titre
- auteur
- isbn
-
On considère une base de données où une clé étrangère FK de la relation R2 référence une clef primaire PK de la relation R1. Quelle affirmation est correcte dans le modèle relationnel ?
- Chaque valeur de FK doit appartenir à l'ensemble des valeurs de la clé primaire PK prises dans R1
- FK peut prendre n'importe quelle valeur tant que son type est le même que celui de PK
- FK doit être unique dans R2 et ne peut pas être NULL
- FK et PK doivent avoir le même nom d'attribut
-
Laquelle des situations viole le principe d'atomicité des valeurs d'attribut ?
- Un attribut « adresse » qui stocke « 12 rue des Fleurs, 75000 Paris » dans une seule chaîne concaténant plusieurs données structurées
- Un attribut « annee » de type INT
- Un attribut « nom_poste » de type TEXT
- Un attribut « version » pouvant être vide (NULL)
-
Laquelle des propositions traduit la contrainte d'unicité associée à une clé primaire ?
- Une même valeur de clé primaire ne peut apparaître qu'une seule fois dans une relation
- Une valeur de clé primaire peut être modifiée librement si le type reste identique
- Une clé primaire peut être définie plusieurs fois dans une même relation
- Les valeurs de clé primaire doivent être des entiers strictement positifs
-
On considère une base de données où une clé étrangère FK de la relation R2 référence une clef primaire PK de la relation R1. À propos de l'intégrité référentielle, quelle opération est interdite par le SGBD ?
- Supprimer un nuplet de la table R1 s'il est référencé par au moins un nuplet de R2 via une clé étrangère
- Insérer un nuplet dans la table R1
- Supprimer un nuplet de la table R2
- Supprimer un nuplet de la table R1 qui n'est pas référencé par au moins un nuplet de R2 via une clé étrangère
-
Laquelle des propositions illustre le principe de non redondance lors de la conception d'un schéma ?
- Séparer une grande table avec des répétitions en plusieurs tables reliées par des clés étrangères
- Regrouper toutes les informations dans une seule table large pour éviter les jointures
- Dupliquer les colonnes communes dans chaque table pour accélérer la lecture
- Concaténer plusieurs champs dans une même colonne pour réduire le nombre d'attributs
-
On modélise une médiathèque : Eleves(idEleve, nom, prenom, classe) et Emprunts(idEmprunt, idEleve, isbn, dateEmprunt, dateRetour). Quelle clé étrangère peut-on choisir dans Emprunts pour assurer la cohérence avec Eleves ?
- idEleve dans Emprunts
- idEmprunt dans Emprunts
- isbn dans Emprunts
- classe dans Eleves
-
On considère la table suivante dont on donne le schéma relationnel la clef primaire est en gras :
Personnes(nom: TEXT, prenom: TEXT, code: CHAR(6))
nom prenom code Dupont Alice '013007' Martin Hugo '084750' Bernard Clara '042571' Peut-on insérer le nuplet suivant dans la relation Personnes ?
(Durand, Léa, '084750')- Oui, car le nom est différent
- Oui, car le prénom est différent
- Non, car sinon on viole la contrainte d'unicité pour une clé primaire
- Non, car la combinaison (nom, prenom) est déjà présente dans la table
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)
Exercice 3⚓︎
D'après un exercice de Frédéric Junier
Le parc informatique
On considère la relation suivante qui liste les postes informatiques dans une entreprise.
Cette relation respecte bien le principe d’unicité car chaque nuplet est identifié de façon unique
par la valeur de l’attribut num_serie ou même par la valeur de l’attribut nom_poste.
En revanche la relation présente de nombreuses redondances. Ces répétitions sont liées au fait qu’un nuplet ne contient pas des données sur une seule mais sur trois entités distinctes du monde réel : * les attributs num_serie et nom_poste caractérisent une entité poste informatique ; * les attributs nom_modele, fabricant et annee caractérisent une entité modèle de poste ; * les attributs nom_systeme et version caractérisent une entité système d’exploitation.
On peut résumer ceci dans le graphique suivant :
1. Question 1.
Donner quelques exemples d’anomalies que peut entraîner la redondance d’informations pour une relation.
On considérera les situations suivantes : on insère un nouveau nuplet, on modifie une valeur d’attribut
redondant, on supprime un nuplet.
Solution
-
On insère un nouveau nuplet : si celui-ci contient des informations redondantes dans la table (par exemple le nom du système exploitation dans la base exemple), alors il faut les saisir à l’identique sinon la cohérence de la table est cassée: aucune sélection selon ces informations ne sera possible.
-
On modifie une valeur d’attribut redondant : il faut modifier ces valeurs pour toutes les redondances de cette valeur dans la table sinon la cohérence de la table est cassée.
-
On supprime un nuplet : on risque de supprimer des informations utiles de la base par exemple le nom d’un système d’exploitation disponible sur le serveur mais qui n’est plus déployé sur un poste actif.
2. Question 2.
Pour éliminer les redondances, ont peut séparer la base de données en trois relations, une par entité,
en éliminant les doublons dans les relations modele et systeme. Proposer un schéma relationnel.
Solution
3. Question 3.
On donne les extraits de tables suivants :
Relation modele
| id_modele | nom | fabricant | annee |
|---|---|---|---|
| 5 | hp probook 400 | hp | 2019 |
Relation systeme
| id_systeme | nom | version |
|---|---|---|
| 4 | fedora linux | 36 |
En utilisant le SGBD de la question 2, donner donner l'extrait de la relation poste contenant
uniquement le premier enregistrement correspondant au numéro de série S2069FST
Solution
Relation poste
| num_serie | nom | id_modele | id_systeme |
|---|---|---|---|
| S2069FST | Portable-01 | 5 | 4 |
TD1⚓︎
😀 La correction est arrivée ...
TD2⚓︎
😀 La correction est arrivée ...
Crédits⚓︎
D'après Frédéric Junier, 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 et Frédéric Junier