Chapitre 2 - Entraînement : Bases de données relationnelles

---

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é :

Livres(id_livre : INT, titre : VARCHAR, auteur : VARCHAR, annee : INT)
    Clé primaire : id_livre

Emprunts(id_emprunt : INT, livre_id : INT, emprunteur : VARCHAR,
         date_emprunt : DATE)
    Clé primaire : id_emprunt
    Clé étrangère : livre_id → Livres(id_livre)

---

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.

a.

-- Votre requête

b.

-- Votre requête

c.

-- Votre requête

d.

-- Votre requête

e.

-- Votre requête

Solution

a.

SELECT titre FROM Livres;

b.

SELECT titre, annee FROM Livres WHERE annee > 1900;

c.

SELECT DISTINCT genre FROM Livres;

d.

SELECT titre, annee FROM Livres ORDER BY annee DESC;

e.

SELECT COUNT(*) FROM Livres;

---

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.

a.

-- Votre requête

b.

-- Votre requête

c.

-- Votre requête

d.

-- Votre requête

Solution

a.

SELECT titre, annee FROM Livres
WHERE genre = 'Roman' AND annee BETWEEN 1830 AND 1900;

b.

SELECT titre FROM Livres
WHERE titre LIKE 'Le %' OR titre LIKE 'Les %';

c.

SELECT * FROM Emprunts WHERE date_retour IS NULL;

d.

SELECT titre, annee FROM Livres
ORDER BY annee ASC
LIMIT 1;

Ou avec une sous-requête :

SELECT titre, annee FROM Livres
WHERE annee = (SELECT MIN(annee) FROM Livres);

---

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).

a.

-- Votre requête

b.

-- Votre requête

c.

-- Votre requête

d.

-- Votre requête

Solution

a.

SELECT Livres.titre, Auteurs.nom
FROM Livres
JOIN Auteurs ON Livres.auteur_id = Auteurs.id;

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.

SELECT Auteurs.nom, Auteurs.prenom, COUNT(*) AS nb_livres
FROM Livres
JOIN Auteurs ON Livres.auteur_id = Auteurs.id
GROUP BY Auteurs.id;

---

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.

UPDATE Auteurs SET nationalite = 'Algérienne'
WHERE nom = 'Camus';

d.

UPDATE Emprunts SET date_retour = '2025-10-05'
WHERE id = 4;

e.

DELETE FROM Emprunts WHERE date_retour < '2025-09-20';

---

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.

SELECT Adherents.nom, Adherents.prenom, Sports.intitule
FROM Inscriptions
JOIN Adherents ON Inscriptions.adherent_id = Adherents.id_adherent
JOIN Sports ON Inscriptions.sport_id = Sports.id_sport;

---

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 :

1. Crée une base de données carnet.db avec une table Contacts (id, nom, prenom, telephone, email) ;
2. Insère 5 contacts ;
3. Affiche tous les contacts triés par nom ;
4. Recherche un contact par nom (saisi par l'utilisateur) ;
5. Modifie le numéro de téléphone d'un contact ;
6. 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 :

1. Afficher le nom des clients habitant à « Lyon » ;
2. Afficher les produits dont le stock est inférieur à 10 ;
3. Afficher le nom du client et la date pour chaque commande ;
4. Afficher, pour chaque ligne de commande, la désignation du produit et la quantité ;
5. Calculer le montant total d'une commande (somme de prix × quantité).

Solution

a. Clés étrangères :

• Commandes.client_id → Clients.id_client
• LignesCommande.commande_id → Commandes.id_commande
• LignesCommande.produit_id → Produits.id_produit

b.

SELECT nom FROM Clients WHERE ville = 'Lyon';

SELECT designation, stock FROM Produits WHERE stock < 10;

SELECT Clients.nom, Commandes.date_commande
FROM Commandes
JOIN Clients ON Commandes.client_id = Clients.id_client;

SELECT Produits.designation, LignesCommande.quantite
FROM LignesCommande
JOIN Produits ON LignesCommande.produit_id = Produits.id_produit;

SELECT SUM(Produits.prix * LignesCommande.quantite) AS total
FROM LignesCommande
JOIN Produits ON LignesCommande.produit_id = Produits.id_produit
WHERE LignesCommande.commande_id = 1;

---

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 :

1. Trouver les suspects habitant à Paris ;
2. Parmi eux, ceux qui sont grands et ont les cheveux bruns ;
3. Vérifier s'ils ont un alibi couvrant la plage 21h-23h ;
4. Trouver les témoignages les impliquant.

Solution

SELECT * FROM Suspects WHERE ville = 'Paris';

SELECT * FROM Suspects
WHERE ville = 'Paris' AND taille = 'grand' AND cheveux = 'bruns';

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';

Les suspects retournés par cette requête ont un alibi et peuvent être écartés.

SELECT Suspects.nom, Temoignages.lieu, Temoignages.heure,
       Temoignages.description
FROM Temoignages
JOIN Suspects ON Temoignages.suspect_id = Suspects.id
WHERE Suspects.ville = 'Paris'
  AND Suspects.taille = 'grand'
  AND Suspects.cheveux = 'bruns';

---

Projet

Projet - Système de gestion de notes

Créer un programme Python complet qui gère les notes d'une classe :

1. Base de données notes.db avec les tables :

   • Eleves (id, nom, prenom, classe)
   • Matieres (id, intitule, coefficient)
   • Evaluations (id, eleve_id, matiere_id, note, date_eval)

2. 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).

3. 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()