Exercices - Bases de données¶
Modèle relationnel¶
Exercice 1 - Identifier les concepts¶
On considère le schéma relationnel suivant d'une base de données pour une compagnie aérienne :
Avions(id_avion : INT, modele : VARCHAR, capacite : INT, compagnie_id : INT)
Clé primaire : id_avion
Clé étrangère : compagnie_id → Compagnies(id_compagnie)
Compagnies(id_compagnie : INT, nom : VARCHAR, pays : VARCHAR)
Clé primaire : id_compagnie
Vols(id_vol : INT, avion_id : INT, ville_depart : VARCHAR,
ville_arrivee : VARCHAR, date_vol : DATE, prix : FLOAT)
Clé primaire : id_vol
Clé étrangère : avion_id → Avions(id_avion)
a. Combien de relations comporte cette base de données ? Nommer chaque relation.
b. Donner les attributs de la relation Vols.
c. Quel est le domaine de l'attribut capacite de la relation Avions ?
d. Identifier toutes les clés primaires et clés étrangères.
e. Peut-on insérer un vol avec avion_id = 99 si aucun avion n'a cet identifiant ? Justifier.
Solution
a. 3 relations : Avions, Compagnies, Vols.
b. Les attributs de Vols sont : id_vol, avion_id, ville_depart, ville_arrivee, date_vol, prix.
c. Le domaine de capacite est INT (entier).
d. Clés primaires : id_avion (Avions), id_compagnie (Compagnies), id_vol (Vols).
Clés étrangères : compagnie_id dans Avions (→ Compagnies), avion_id dans Vols (→ Avions).
e. Non. La contrainte d'intégrité référentielle impose que toute valeur de clé étrangère doit correspondre à une clé primaire existante dans la table référencée. L'insertion serait refusée par le SGBD.
Exercice 2 - Anomalies de conception¶
Un développeur stocke les informations d'une bibliothèque dans une seule table :
| id | titre | auteur | annee | emprunteur | date_emprunt |
|---|---|---|---|---|---|
| 1 | Germinal | Zola | 1885 | Alice | 2025-09-01 |
| 2 | Germinal | Zola | 1885 | Bob | 2025-09-10 |
| 3 | Le Petit Prince | Saint-Exupéry | 1943 | Alice | 2025-09-05 |
a. Identifier les informations redondantes.
b. Que se passe-t-il si on veut ajouter un livre qui n'a jamais été emprunté ?
c. Que se passe-t-il si on supprime l'emprunt de Bob (ligne 2) ?
d. Proposer un schéma relationnel normalisé (plusieurs tables) qui élimine ces anomalies.
Solution
a. Le titre, l'auteur et l'année de « Germinal » sont répétés dans les lignes 1 et 2. C'est de la redondance.
b. Anomalie d'insertion : on ne peut pas ajouter un livre sans emprunteur, car la ligne serait incomplète (emprunteur et date_emprunt seraient NULL, ce qui n'est pas significatif).
c. Si la ligne 2 était le seul enregistrement pour « Germinal », on perdrait les informations sur le livre lui-même. C'est une anomalie de suppression. Ici, ce n'est pas le cas car la ligne 1 conserve Germinal, mais le risque existe.
d. Schéma normalisé :
Requêtes SQL - Interrogation¶
On utilise la base de la médiathèque du cours (tables Livres, Auteurs, Emprunts).
Exercice 3 - Requêtes simples¶
Écrire les requêtes SQL pour :
a. Afficher tous les titres de livres.
b. Afficher le titre et l'année des livres publiés après 1900.
c. Afficher les genres distincts présents dans la table Livres.
d. Afficher les livres triés par année de publication décroissante.
e. Compter le nombre total de livres.
Solution
a.
b.
c.
d.
e.
Exercice 4 - Conditions composées¶
Écrire les requêtes SQL pour :
a. Afficher les romans publiés entre 1830 et 1900.
b. Afficher les livres dont le titre commence par « Le » ou « Les ».
c. Afficher les emprunts non encore retournés (date_retour est NULL).
d. Afficher le titre du livre le plus ancien.
Solution
a.
b.
c.
d.
Ou avec une sous-requête :Exercice 5 - Jointures¶
Écrire les requêtes SQL pour :
a. Afficher le titre de chaque livre et le nom de son auteur.
b. Afficher les titres des livres écrits par « Saint-Exupéry ».
c. Afficher le nom de l'adhérent et le titre du livre pour chaque emprunt en cours.
d. Afficher le nombre de livres par auteur (nom et prénom de l'auteur + nombre de livres).
Solution
a.
b.
SELECT Livres.titre
FROM Livres
JOIN Auteurs ON Livres.auteur_id = Auteurs.id
WHERE Auteurs.nom = 'Saint-Exupéry';
c.
SELECT Emprunts.adherent, Livres.titre
FROM Emprunts
JOIN Livres ON Emprunts.livre_id = Livres.id
WHERE Emprunts.date_retour IS NULL;
d.
Requêtes SQL - Mise à jour¶
Exercice 6 - INSERT, UPDATE, DELETE¶
Écrire les requêtes SQL pour :
a. Ajouter l'auteur « Molière » (id 6, prénom « Jean-Baptiste », nationalité « Française »).
b. Ajouter le livre « Le Malade imaginaire » (id 9, auteur_id 6, année 1673, genre « Théâtre »).
c. Modifier la nationalité de l'auteur « Camus » en « Algérienne ».
d. Enregistrer le retour du livre emprunté par Sophie (emprunt n° 4) à la date du 2025-10-05.
e. Supprimer tous les emprunts dont la date de retour est antérieure au 2025-09-20.
Solution
a.
INSERT INTO Auteurs (id, nom, prenom, nationalite)
VALUES (6, 'Molière', 'Jean-Baptiste', 'Française');
b.
INSERT INTO Livres (id, titre, auteur_id, annee, genre)
VALUES (9, 'Le Malade imaginaire', 6, 1673, 'Théâtre');
c.
d.
e.
Conception et cas pratiques¶
Exercice 7 - Concevoir un schéma relationnel¶
Un club sportif souhaite gérer ses adhérents, les sports proposés et les inscriptions. Chaque adhérent peut s'inscrire à plusieurs sports, et chaque sport peut accueillir plusieurs adhérents.
a. Proposer un schéma relationnel avec les tables nécessaires, en précisant les attributs, clés primaires et clés étrangères.
b. Donner un exemple de contenu (3 adhérents, 2 sports, 4 inscriptions).
c. Écrire la requête SQL pour afficher le nom de chaque adhérent et les sports auxquels il est inscrit.
Solution
a.
Adherents(id_adherent : INT, nom : VARCHAR, prenom : VARCHAR,
date_naissance : DATE)
Clé primaire : id_adherent
Sports(id_sport : INT, intitule : VARCHAR, jour : VARCHAR,
horaire : VARCHAR)
Clé primaire : id_sport
Inscriptions(id_inscription : INT, adherent_id : INT,
sport_id : INT, date_inscription : DATE)
Clé primaire : id_inscription
Clé étrangère : adherent_id → Adherents(id_adherent)
Clé étrangère : sport_id → Sports(id_sport)
b. Exemple :
Adherents :
| id_adherent | nom | prenom | date_naissance |
|---|---|---|---|
| 1 | Leroy | Emma | 2010-05-12 |
| 2 | Moreau | Lucas | 2009-11-03 |
| 3 | Simon | Jade | 2010-02-28 |
Sports :
| id_sport | intitule | jour | horaire |
|---|---|---|---|
| 1 | Natation | Mercredi | 14h-16h |
| 2 | Tennis | Samedi | 10h-12h |
Inscriptions :
| id_inscription | adherent_id | sport_id | date_inscription |
|---|---|---|---|
| 1 | 1 | 1 | 2025-09-01 |
| 2 | 1 | 2 | 2025-09-01 |
| 3 | 2 | 1 | 2025-09-05 |
| 4 | 3 | 2 | 2025-09-10 |
c.
Exercice 8 - Intégrité référentielle¶
On considère la base du club sportif de l'exercice 7 avec le contenu donné.
a. Peut-on exécuter INSERT INTO Inscriptions VALUES (5, 99, 1, '2025-10-01') ? Justifier.
b. Peut-on exécuter DELETE FROM Sports WHERE id_sport = 1 si des inscriptions y font référence ? Que faudrait-il faire avant ?
c. Peut-on avoir deux inscriptions avec le même adherent_id et le même sport_id ? Est-ce souhaitable ? Comment l'empêcher ?
Solution
a. Non. adherent_id = 99 ne correspond à aucune clé primaire dans la table Adherents. La contrainte d'intégrité référentielle est violée.
b. En principe, non : la contrainte d'intégrité référentielle empêche de supprimer un enregistrement référencé par une clé étrangère. Il faudrait d'abord supprimer (ou modifier) toutes les inscriptions faisant référence au sport n° 1.
c. Avec le schéma actuel, oui (la clé primaire est id_inscription, un numéro auto-incrémenté). Ce n'est pas souhaitable : un adhérent ne devrait pas être inscrit deux fois au même sport. On peut l'empêcher en ajoutant une contrainte d'unicité sur le couple (adherent_id, sport_id), ou en utilisant ce couple comme clé primaire composée.
Activités¶
🧪 Activité 1 - Manipuler une base avec Python et SQLite¶
Créer un programme Python qui :
- Crée une base de données
carnet.dbavec une tableContacts(id, nom, prenom, telephone, email) ; - Insère 5 contacts ;
- Affiche tous les contacts triés par nom ;
- Recherche un contact par nom (saisi par l'utilisateur) ;
- Modifie le numéro de téléphone d'un contact ;
- Supprime un contact.
Solution
import sqlite3
def creer_base():
conn = sqlite3.connect("carnet.db")
c = conn.cursor()
c.execute("""
CREATE TABLE IF NOT EXISTS Contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nom TEXT NOT NULL,
prenom TEXT NOT NULL,
telephone TEXT,
email TEXT
)
""")
conn.commit()
return conn
def inserer_contacts(conn):
contacts = [
("Dupont", "Alice", "0601020304", "[email protected]"),
("Martin", "Bob", "0611223344", "[email protected]"),
("Durand", "Clara", "0622334455", "[email protected]"),
("Petit", "David", "0633445566", "[email protected]"),
("Leroy", "Emma", "0644556677", "[email protected]"),
]
c = conn.cursor()
c.executemany("""
INSERT INTO Contacts (nom, prenom, telephone, email)
VALUES (?, ?, ?, ?)
""", contacts)
conn.commit()
def afficher_tous(conn):
c = conn.cursor()
c.execute("SELECT nom, prenom, telephone, email FROM Contacts ORDER BY nom")
for nom, prenom, tel, email in c.fetchall():
print(f"{nom} {prenom} - {tel} - {email}")
def rechercher(conn, nom_cherche):
c = conn.cursor()
c.execute("SELECT * FROM Contacts WHERE nom = ?", (nom_cherche,))
resultats = c.fetchall()
if resultats:
for r in resultats:
print(r)
else:
print("Aucun contact trouvé.")
def modifier_telephone(conn, nom, nouveau_tel):
c = conn.cursor()
c.execute("UPDATE Contacts SET telephone = ? WHERE nom = ?",
(nouveau_tel, nom))
conn.commit()
print(f"Téléphone de {nom} mis à jour.")
def supprimer_contact(conn, nom):
c = conn.cursor()
c.execute("DELETE FROM Contacts WHERE nom = ?", (nom,))
conn.commit()
print(f"Contact {nom} supprimé.")
conn = creer_base()
inserer_contacts(conn)
print("=== Tous les contacts ===")
afficher_tous(conn)
print("\n=== Recherche : Durand ===")
rechercher(conn, "Durand")
print("\n=== Modification téléphone Martin ===")
modifier_telephone(conn, "Martin", "0699887766")
print("\n=== Suppression Petit ===")
supprimer_contact(conn, "Petit")
print("\n=== Contacts après modifications ===")
afficher_tous(conn)
conn.close()
🧪 Activité 2 - Analyser un schéma existant¶
On considère le schéma relationnel d'un site de vente en ligne :
Clients(id_client, nom, email, ville)
Produits(id_produit, designation, prix, stock)
Commandes(id_commande, client_id, date_commande)
LignesCommande(id_ligne, commande_id, produit_id, quantite)
a. Identifier les clés étrangères (non indiquées) et dessiner le diagramme des relations.
b. Écrire les requêtes SQL pour :
- Afficher le nom des clients habitant à « Lyon » ;
- Afficher les produits dont le stock est inférieur à 10 ;
- Afficher le nom du client et la date pour chaque commande ;
- Afficher, pour chaque ligne de commande, la désignation du produit et la quantité ;
- Calculer le montant total d'une commande (somme de prix × quantité).
Solution
a. Clés étrangères :
Commandes.client_id→Clients.id_clientLignesCommande.commande_id→Commandes.id_commandeLignesCommande.produit_id→Produits.id_produit
b.
1.
2.
3.
SELECT Clients.nom, Commandes.date_commande
FROM Commandes
JOIN Clients ON Commandes.client_id = Clients.id_client;
4.
SELECT Produits.designation, LignesCommande.quantite
FROM LignesCommande
JOIN Produits ON LignesCommande.produit_id = Produits.id_produit;
5.
🧪 Activité 3 - Détective SQL¶
On dispose d'une base de données criminalistique avec les tables suivantes :
Suspects(id, nom, ville, taille, cheveux)
Temoignages(id, temoin, suspect_id, lieu, heure, description)
Alibis(id, suspect_id, lieu, heure_debut, heure_fin)
Le crime a eu lieu à « Paris » entre 21h et 23h. Le témoin décrit un individu « grand aux cheveux bruns ».
Écrire les requêtes SQL successives pour identifier le coupable :
- Trouver les suspects habitant à Paris ;
- Parmi eux, ceux qui sont grands et ont les cheveux bruns ;
- Vérifier s'ils ont un alibi couvrant la plage 21h-23h ;
- Trouver les témoignages les impliquant.
Solution
1.
2.
3.
SELECT Suspects.nom, Alibis.lieu, Alibis.heure_debut, Alibis.heure_fin
FROM Suspects
JOIN Alibis ON Suspects.id = Alibis.suspect_id
WHERE Suspects.ville = 'Paris'
AND Suspects.taille = 'grand'
AND Suspects.cheveux = 'bruns'
AND Alibis.heure_debut <= '21:00'
AND Alibis.heure_fin >= '23:00';
4.
Projet¶
🎯 Projet - Système de gestion de notes¶
Créer un programme Python complet qui gère les notes d'une classe :
-
Base de données
notes.dbavec les tables :Eleves(id, nom, prenom, classe)Matieres(id, intitule, coefficient)Evaluations(id, eleve_id, matiere_id, note, date_eval)
-
Fonctionnalités :
- Ajouter un élève, une matière, une évaluation ;
- Afficher le bulletin d'un élève (toutes ses notes avec les matières) ;
- Calculer la moyenne d'un élève (pondérée par les coefficients) ;
- Afficher le classement de la classe pour une matière donnée ;
- Afficher les statistiques par matière (moyenne, min, max).
-
Interface : menu textuel en boucle permettant de choisir l'action.
Solution
import sqlite3
def initialiser_base():
conn = sqlite3.connect("notes.db")
c = conn.cursor()
c.execute("""CREATE TABLE IF NOT EXISTS Eleves (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nom TEXT NOT NULL, prenom TEXT NOT NULL, classe TEXT)""")
c.execute("""CREATE TABLE IF NOT EXISTS Matieres (
id INTEGER PRIMARY KEY AUTOINCREMENT,
intitule TEXT NOT NULL, coefficient REAL DEFAULT 1)""")
c.execute("""CREATE TABLE IF NOT EXISTS Evaluations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
eleve_id INTEGER, matiere_id INTEGER,
note REAL, date_eval TEXT,
FOREIGN KEY(eleve_id) REFERENCES Eleves(id),
FOREIGN KEY(matiere_id) REFERENCES Matieres(id))""")
conn.commit()
return conn
def ajouter_eleve(conn, nom, prenom, classe):
conn.execute("INSERT INTO Eleves (nom, prenom, classe) VALUES (?, ?, ?)",
(nom, prenom, classe))
conn.commit()
def ajouter_matiere(conn, intitule, coefficient):
conn.execute("INSERT INTO Matieres (intitule, coefficient) VALUES (?, ?)",
(intitule, coefficient))
conn.commit()
def ajouter_evaluation(conn, eleve_id, matiere_id, note, date_eval):
conn.execute("""INSERT INTO Evaluations
(eleve_id, matiere_id, note, date_eval) VALUES (?, ?, ?, ?)""",
(eleve_id, matiere_id, note, date_eval))
conn.commit()
def bulletin(conn, eleve_id):
c = conn.cursor()
c.execute("""SELECT Matieres.intitule, Evaluations.note,
Evaluations.date_eval
FROM Evaluations
JOIN Matieres ON Evaluations.matiere_id = Matieres.id
WHERE Evaluations.eleve_id = ?
ORDER BY Matieres.intitule, Evaluations.date_eval""",
(eleve_id,))
return c.fetchall()
def moyenne_ponderee(conn, eleve_id):
c = conn.cursor()
c.execute("""SELECT SUM(Evaluations.note * Matieres.coefficient),
SUM(Matieres.coefficient)
FROM Evaluations
JOIN Matieres ON Evaluations.matiere_id = Matieres.id
WHERE Evaluations.eleve_id = ?""", (eleve_id,))
somme, coeff = c.fetchone()
if coeff and coeff > 0:
return round(somme / coeff, 2)
return None
def classement_matiere(conn, matiere_id):
c = conn.cursor()
c.execute("""SELECT Eleves.nom, Eleves.prenom,
AVG(Evaluations.note) AS moy
FROM Evaluations
JOIN Eleves ON Evaluations.eleve_id = Eleves.id
WHERE Evaluations.matiere_id = ?
GROUP BY Eleves.id
ORDER BY moy DESC""", (matiere_id,))
return c.fetchall()
def stats_matiere(conn, matiere_id):
c = conn.cursor()
c.execute("""SELECT AVG(note), MIN(note), MAX(note)
FROM Evaluations
WHERE matiere_id = ?""", (matiere_id,))
return c.fetchone()
conn = initialiser_base()
ajouter_eleve(conn, "Dupont", "Alice", "T3")
ajouter_eleve(conn, "Martin", "Bob", "T3")
ajouter_matiere(conn, "NSI", 2)
ajouter_matiere(conn, "Mathématiques", 3)
ajouter_evaluation(conn, 1, 1, 17, "2025-09-15")
ajouter_evaluation(conn, 1, 2, 15, "2025-09-18")
ajouter_evaluation(conn, 2, 1, 14, "2025-09-15")
ajouter_evaluation(conn, 2, 2, 16, "2025-09-18")
print("=== Bulletin de Dupont Alice ===")
for matiere, note, date in bulletin(conn, 1):
print(f" {matiere} : {note}/20 ({date})")
print(f" Moyenne pondérée : {moyenne_ponderee(conn, 1)}/20")
print("\n=== Classement en NSI ===")
for i, (nom, prenom, moy) in enumerate(classement_matiere(conn, 1), 1):
print(f" {i}. {nom} {prenom} : {moy:.1f}/20")
print("\n=== Statistiques NSI ===")
moy, mini, maxi = stats_matiere(conn, 1)
print(f" Moyenne : {moy:.1f} | Min : {mini} | Max : {maxi}")
conn.close()