Exercices : bases de données et SQL

C. Un attribut est le nom donné à une colonne dans le modèle relationnel. A (enregistrement) désigne une ligne, aussi appelée tuple ou n-uplet. B (relation) désigne la table elle-même. D (clé) désigne un attribut particulier servant à identifier ou relier des lignes.

D. La clé primaire garantit l’unicité de chaque ligne : deux lignes ne peuvent pas avoir la même valeur de clé primaire. A est faux (le chiffrement relève de la sécurité). B est faux (le tri se fait avec ORDER BY). C décrit le rôle d’une clé étrangère.

A. La syntaxe correcte est SELECT ... FROM ... WHERE condition. B est faux (IF n’existe pas en SQL standard pour les requêtes). C a un ordre de clauses incorrect (WHERE doit suivre FROM). D confond la condition de filtrage avec la liste des colonnes à afficher.

C. CREATE TABLE modifie la structure de la base : c’est du LDD. Les ordres A, B et D modifient ou consultent le contenu de la base : ce sont des ordres LMD (langage de manipulation des données).

D. Une clé étrangère est un attribut qui fait référence à la clé primaire d’une autre table, créant un lien entre les deux tables. C’est le mécanisme fondamental pour relier les données dans le modèle relationnel. B décrit une clé primaire, pas une clé étrangère.

a) Le schéma comporte trois tables. Livres a quatre attributs (id_livre, titre, auteur, annee). Adherents a trois attributs (num_adherent, nom, prenom). Emprunts a cinq attributs (id_emprunt, id_livre, num_adherent, date_emprunt, date_retour).

b) La clé primaire de Emprunts est id_emprunt. Ses clés étrangères sont id_livre (qui référence Livres.id_livre) et num_adherent (qui référence Adherents.num_adherent).

c) L’attribut num_adherent de la table Emprunts contiendra 42 et l’attribut id_livre contiendra 7. Ces deux attributs sont les clés étrangères qui font le lien entre l’emprunt, l’adhérent et le livre.

d) Non, c’est interdit. La contrainte d’intégrité référentielle impose que la valeur d’une clé étrangère doit correspondre à une valeur existante de la clé primaire qu’elle référence. Puisque id_livre dans Emprunts référence Livres.id_livre, il faut que le livre 999 existe dans la table Livres.

e) Non, deux emprunts ne peuvent pas avoir le même id_emprunt car c’est la clé primaire de la table Emprunts. La contrainte d’unicité de la clé primaire interdit les doublons.

a)

SELECT nom, prix
FROM Produits
WHERE categorie = 'Informatique';

Résultat : Clé USB 32 Go (8.90), Cartouche encre (25.00), Souris sans fil (15.50), Casque audio (35.00).

b)

SELECT nom, prix
FROM Produits
WHERE prix < 5
ORDER BY prix;

Résultat : Stylo bleu (1.20), Colle (1.50), Cahier A4 (2.50), Classeur (3.80). Par défaut, ORDER BY trie en ordre croissant (ASC).

c)

SELECT DISTINCT categorie
FROM Produits;

Résultat : Papeterie, Informatique. Le mot-clé DISTINCT élimine les doublons dans le résultat.

d)

SELECT COUNT(*)
FROM Produits
WHERE stock < 50;

Résultat : 3. Les produits concernés sont : Clé USB 32 Go (45), Cartouche encre (20), Casque audio (15).

e)

SELECT AVG(prix)
FROM Produits;

Résultat : $(2.50 + 1.20 + 8.90 + 25.00 + 3.80 + 15.50 + 1.50 + 35.00) / 8 = 93.40 / 8 = 11.675$.

f)

SELECT nom, prix
FROM Produits
WHERE prix = (SELECT MAX(prix) FROM Produits);

Résultat : Casque audio (35.00). On utilise une sous-requête (SELECT MAX(prix) FROM Produits) pour obtenir le prix maximal, puis on filtre avec WHERE pour trouver le produit correspondant.

a) L’attribut id_classe est présent dans les deux tables. Dans Eleves, c’est une clé étrangère ; dans Classes, c’est la clé primaire. C’est cet attribut qui permet de relier chaque élève à sa classe.

b) L’élève Marie Dupont a id_classe = 101. Dans la table Classes, la ligne id_classe = 101 correspond à « Terminale A », salle B204. Sa classe est donc « Terminale A ».

c) Tableau complété :

On a associé chaque élève à la ligne de Classes ayant le même id_classe.

d) La clause JOIN Classes indique que l’on combine la table Eleves avec la table Classes. La condition ON Eleves.id_classe = Classes.id_classe précise comment établir la correspondance : on relie les lignes dont la valeur de id_classe est identique dans les deux tables.

e)

SELECT Eleves.prenom
FROM Eleves
JOIN Classes ON Eleves.id_classe = Classes.id_classe
WHERE Classes.salle = 'B204';

Résultat : Marie, Emma.

f)

SELECT COUNT(*)
FROM Eleves
JOIN Classes ON Eleves.id_classe = Classes.id_classe
WHERE Classes.nom_classe = 'Terminale A';

Résultat : 2 (Dupont Marie et Leroy Emma).

a)

INSERT INTO Classes (id_classe, nom_classe, salle)
VALUES (103, 'Terminale C', 'A308');

b)

INSERT INTO Eleves (id_eleve, nom, prenom, id_classe)
VALUES (4, 'Petit', 'Paul', 103);

La classe 103 existe bien (insérée en a), donc la contrainte d’intégrité référentielle est respectée.

c)

UPDATE Eleves
SET id_classe = 101
WHERE nom = 'Martin' AND prenom = 'Lucas';

Après cette modification, Lucas Martin est en Terminale A.

d) Après la modification de c), plus aucun élève n’a id_classe = 102. On peut donc supprimer cette classe sans violer la contrainte d’intégrité référentielle :

DELETE FROM Classes
WHERE id_classe = 102;

Si un élève avait encore id_classe = 102, la suppression aurait été refusée (ou aurait causé une incohérence) car la clé étrangère de cet élève référencerait une classe inexistante.

e) L’insertion serait refusée par le SGBD. La contrainte d’intégrité référentielle impose que la valeur de id_classe dans Eleves doit correspondre à une valeur existante de id_classe dans Classes. Puisque la classe 999 n’existe pas, l’insertion viole cette contrainte.

a) Non : dans les lignes 1 et 3, le téléphone d’Alice Dupont est 0612345678, mais dans la ligne 4, il est 0612340000. C’est une anomalie de mise à jour (ou incohérence) : les mêmes informations client sont dupliquées dans plusieurs lignes, et lors d’une modification (changement de numéro), toutes les occurrences n’ont pas été mises à jour.

b) En supprimant la ligne 2, on perd non seulement la vente du « Vélo ville 28 », mais aussi les informations sur le client Bob Martin (nom, téléphone). Si Bob Martin n’a fait aucun autre achat, il disparaît complètement de la base. C’est une anomalie de suppression : supprimer un fait (la vente) entraîne la perte d’un autre fait indépendant (l’existence du client).

c) Non, on ne peut pas ajouter un vélo sans vente. La table exige un id_vente, un client et une date, or on n’a ni client ni date pour un vélo simplement référencé au catalogue. C’est une anomalie d’insertion : on ne peut pas enregistrer un fait (l’existence d’un produit) sans enregistrer simultanément un fait indépendant (une vente).

d) Les trois problèmes sont : l’anomalie de mise à jour (a), l’anomalie de suppression (b) et l’anomalie d’insertion (c). Ces anomalies sont causées par la redondance des données dans une table unique mal conçue.

e) Schéma relationnel corrigé :

• Clients(id_client, nom, telephone)
• Velos(id_velo, modele, marque, prix)
• Ventes(id_vente, #id_client, #id_velo, date_vente)

Les clés étrangères id_client et id_velo dans Ventes référencent respectivement Clients.id_client et Velos.id_velo. Chaque information est stockée une seule fois : le téléphone d’Alice n’apparaît que dans une seule ligne de Clients, et le vélo « VTT Enfant 24 » peut exister dans Velos sans nécessiter de vente.

a)

SELECT nom, prenom
FROM Adherents
WHERE annee_naissance = 2007
ORDER BY nom;

Résultat : Dupont Marie, Petit Hugo.

b)

SELECT COUNT(*)
FROM Inscriptions;

Résultat : 7 inscriptions au total.

c)

SELECT Adherents.nom, Adherents.prenom
FROM Adherents
JOIN Inscriptions ON Adherents.id_adh = Inscriptions.id_adh
JOIN Activites ON Inscriptions.id_act = Activites.id_act
WHERE Activites.nom_activite = 'Natation';

Résultat : Dupont Marie, Martin Lucas, Petit Hugo.

d)

SELECT COUNT(*)
FROM Inscriptions
JOIN Activites ON Inscriptions.id_act = Activites.id_act
WHERE Activites.nom_activite = 'Natation';

Résultat : 3 inscrits (inscriptions 1, 3 et 5).

e)

DELETE FROM Inscriptions
WHERE id_adh = 4 AND id_act = 30;

On identifie Hugo Petit par id_adh = 4 et l’escalade par id_act = 30. On pourrait aussi écrire WHERE id_insc = 6, puisque c’est l’identifiant de cette inscription.

f)

INSERT INTO Activites (id_act, nom_activite, jour, creneau, places_max)
VALUES (40, 'Yoga', 'Vendredi', '17h-18h30', 25);

g)

INSERT INTO Inscriptions (id_insc, id_adh, id_act, date_inscription)
VALUES (8, 5, 30, '2025-10-01');

La contrainte d’intégrité référentielle est respectée : id_adh = 5 (Léa Bernard) existe dans Adherents et id_act = 30 (Escalade) existe dans Activites. Les deux clés étrangères pointent vers des valeurs existantes.

h) Non, l’insertion serait refusée. L’attribut id_act dans Inscriptions est une clé étrangère qui référence Activites.id_act. La contrainte d’intégrité référentielle impose que toute valeur de cette clé étrangère corresponde à une valeur existante de la clé primaire de Activites. L’activité 99 n’existant pas, l’insertion viole cette contrainte.