Différences

Ci-dessous, les différences entre deux révisions de la page.

--- sio:bloc1:td3 [2026/11/13 01:06] – jcheron
+++ sio:bloc1:td3 [2026/11/13 01:53] (Version actuelle) – jcheron
@@ Ligne 3: / Ligne 3: @@
 ===== Présentation =====
-Ce support accompagne le script ''mediatheque_pedagogique.sql'', destiné à MySQL 8.0+. Le jeu de données pédagogique décrit une médiathèque : éditeurs, adhérents, auteurs, livres, exemplaires physiques et emprunts. Les noms et données sont fictifs.
+Ce support accompagne le script {{ :sio:bloc1:mediatheque_pedagogique.zip |}}, destiné à MySQL 8.0+. Le jeu de données pédagogique décrit une médiathèque : éditeurs, adhérents, auteurs, livres, exemplaires physiques et emprunts. Les noms et données sont fictifs.
 ===== Installation et préparation =====
@@ Ligne 15: / Ligne 15: @@
   - DBeaver utilise un pilote MySQL. Si DBeaver propose de télécharger ou d’installer ce pilote lors de la création de la connexion, acceptez.
-==== 2. Installer et démarrer MySQL Server ====
+==== 2. Installer et démarrer MariaDb Server ====
-MySQL Server est un logiciel distinct. Installez MySQL Server 8.0 ou une version ultérieure, puis vérifiez que le service serveur est démarré. Consultez les [[https://dev.mysql.com/doc/refman/8.0/en/installing.html|instructions officielles d’installation MySQL]] correspondant à votre système. Sous Windows, MySQL Installer propose une installation guidée; l’installation du serveur comprend une étape de configuration.
+MariaDB Server est un logiciel distinct de DBeaver Community. Installez MariaDB Server, puis vérifiez que le service serveur est démarré. Consultez les [[https://mariadb.com/downloads/|instructions officielles d’installation de MariaDB]] correspondant à votre système.
 Conservez le nom d’utilisateur et le mot de passe configurés pour le serveur. Le compte utilisé pour l’exercice doit être autorisé à créer une base de données et des tables.
+==== 3. Connecter DBeaver à MariaDb et charger le jeu de données ====
-==== 3. Connecter DBeaver à MySQL et charger le jeu de données ====
+  - Dans DBeaver, démarrez l’assistant de nouvelle connexion, puis recherchez et choisissez ''MariaDb''.
+  - Pour une installation locale, indiquez ''localhost'' comme hôte et ''3306'' comme port, sauf si votre serveur a été configuré autrement. Saisissez le nom d’utilisateur et le mot de passe MariaDb. Vous pouvez laisser la base non renseignée pour cette première connexion.
-  - Dans DBeaver, démarrez l’assistant de nouvelle connexion, puis recherchez et choisissez ''MySQL''.
+  - Testez la connexion, puis validez. Si nécessaire, laissez DBeaver télécharger le pilote JDBC MariaDb.
-  - Pour une installation locale, indiquez ''localhost'' comme hôte et ''3306'' comme port, sauf si votre serveur a été configuré autrement. Saisissez le nom d’utilisateur et le mot de passe MySQL. Vous pouvez laisser la base non renseignée pour cette première connexion.
-  - Testez la connexion, puis validez. Si nécessaire, laissez DBeaver télécharger le pilote JDBC MySQL.
   - Ouvrez le fichier ''mediatheque_pedagogique.sql'' dans l’éditeur SQL associé à cette connexion, puis exécutez le script complet. Le script crée et sélectionne la base ''mediatheque_pedagogique'', puis crée les tables et insère les données d’exercice.
   - Pour les exercices, ouvrez un nouvel éditeur SQL sur cette connexion; le script a déjà sélectionné la base à utiliser.
-**Attention :** à chaque réexécution, le script supprime puis recrée les tables de même nom dans cette base. Utilisez un environnement de pratique et non une base contenant des données à conserver.
+===== Schéma relationnel =====
+^ Table ^ Champs utiles ^ Liens ^
+| ''editeur'' | ''id_editeur'', nom, ville | Référencé par ''livre.id_editeur''. |
+| ''adherent'' | ''id_adherent'', nom, prenom, ville, date_inscription | Référencé par ''emprunt.id_adherent''. |
+| ''auteur'' | ''id_auteur'', nom, prenom | Associé aux livres par ''livre_auteur''. |
+| ''livre'' | ''id_livre'', titre, genre, annee_publication, ''id_editeur'' | Référence ''editeur''. |
+| ''livre_auteur'' | ''id_livre'', ''id_auteur'', ordre_auteur | Table d’association livres-auteurs. |
+| ''exemplaire'' | ''id_exemplaire'', ''id_livre'', code_barres, date_acquisition | Référence ''livre''. |
+| ''emprunt'' | ''id_emprunt'', ''id_exemplaire'', ''id_adherent'', date_emprunt, date_echeance, date_retour | Référence ''exemplaire'' et ''adherent''. |
+Un ''date_retour'' à ''NULL'' signale un emprunt non rendu. Pour les exercices de retard, utilisez la date de référence **15 mars 2025**. Les livres 47 à 50 n’ont pas d’exemplaire.
+===== A. Requêtes simples, filtres et tris =====
+<WRAP todo round>
+=== Exercice 1 — Parcourir le catalogue ===
+Afficher le titre, le genre et l’année de publication de chaque livre. Trier par genre, puis par titre.
+=== Exercice 2 — Filtrer un genre ===
+Afficher le titre et l’année des livres de ''Science-fiction'' publiés depuis 2015 inclus.
+=== Exercice 3 — Recherche dans les titres ===
+Trouver les titres contenant le mot ''des''. Trier par année de publication décroissante.
+=== Exercice 4 — Livres d’un intervalle d’années ===
+Afficher le titre et l’année des livres publiés entre 2000 et 2010 inclus.
-Pour les paramètres de connexion détaillés, consultez la [[https://dbeaver.com/docs/dbeaver/Database-driver-MySQL/|documentation DBeaver pour MySQL]] et la [[https://dbeaver.com/docs/dbeaver/Create-Connection/|documentation de création d’une connexion]].
+=== Exercice 5 — Inscriptions récentes ===
-===== Contexte et schéma =====
+Afficher le nom, le prénom et la date d’inscription des adhérents inscrits depuis le 1er janvier 2024. Trier du plus récent au plus ancien.
-Le modèle relationnel comprend les tables suivantes. Les identifiants préfixés par ''id_'' sont des clés; les liens indiqués correspondent aux clés étrangères du modèle.
+=== Exercice 6 — Limiter un résultat ===
-^ Table ^ Champs principaux ^ Rôle / relations ^
+Afficher les dix livres les plus récents, du plus récent au plus ancien.
-| ''editeur'' | ''id_editeur'', ''nom'', ''ville'' | Informations sur les éditeurs. |
+</WRAP>
-| ''livre'' | ''id_livre'', ''titre'', ''genre'', ''annee_publication'', ''id_editeur'' | Catalogue; ''id_editeur'' référence ''editeur.id_editeur''. |
-| ''auteur'' | ''id_auteur'', ''nom'', ''prenom'' | Informations sur les auteurs. |
-| ''livre_auteur'' | ''id_livre'', ''id_auteur'', ''ordre_auteur'' | Association entre livres et auteurs; les deux identifiants référencent leurs tables respectives. |
-| ''exemplaire'' | ''id_exemplaire'', ''id_livre'', ''code_barres'', ''date_acquisition'' | Exemplaires physiques; ''id_livre'' référence ''livre.id_livre''. |
-| ''adherent'' | ''id_adherent'', ''nom'', ''prenom'', ''ville'', ''date_inscription'' | Informations sur les adhérents. |
-| ''emprunt'' | ''id_emprunt'', ''id_exemplaire'', ''id_adherent'', ''date_emprunt'', ''date_echeance'', ''date_retour'' | Historique des prêts; les identifiants d’exemplaire et d’adhérent référencent leurs tables respectives. |
-''date_retour'' peut être ''NULL'' lorsqu’un emprunt n’est pas encore rendu dans les données d’exercice. Les exercices qui portent sur les retards utilisent la date de référence fixe du 15 mars 2025 afin de garder des résultats reproductibles.
+===== B. Jointures et relations =====
+<WRAP todo round>
-===== A. Catalogue, filtres et tri =====
+=== Exercice 7 — Livres et éditeurs ===
-==== Exercice 1 — Lister le catalogue ====
+Pour chaque livre, afficher le titre et le nom de son éditeur. Trier par nom d’éditeur puis par titre.
-Afficher le titre, le genre et l’année de publication de tous les livres.
+=== Exercice 8 — Livres et auteurs ===
-==== Exercice 2 — Filtrer par genre ====
+Afficher le titre de chaque livre, ainsi que le nom et le prénom de ses auteurs. Utiliser la table ''livre_auteur''. Un livre ayant plusieurs auteurs doit apparaître sur plusieurs lignes.
-Afficher les titres de science-fiction publiés en 1950 ou après.
+=== Exercice 9 — Livres et exemplaires ===
-==== Exercice 3 — Rechercher et trier ====
+Afficher le titre des livres et le code-barres de leurs exemplaires.
-Afficher les livres dont le titre contient « du », triés par année de publication décroissante puis par titre croissant.
+=== Exercice 10 — Emprunts et adhérents ===
-==== Exercice 4 — Filtrer par intervalle de dates ====
+Afficher chaque emprunt avec le nom, le prénom et la ville de l’adhérent, ainsi que les dates d’emprunt, d’échéance et de retour. Inclure les emprunts non rendus.
-Afficher les emprunts commencés entre le 1er et le 28 février 2025 inclus, du plus récent au plus ancien.
+=== Exercice 11 — Parcours complet d’un emprunt ===
-===== B. Relations entre les tables =====
+Pour chaque emprunt, afficher le titre du livre, son code-barres, le nom et le prénom de l’adhérent, ainsi que les dates d’emprunt, d’échéance et de retour.
-==== Exercice 5 — Livre et éditeur (deux tables) ====
+=== Exercice 12 — Livres sans exemplaire ===
-Afficher chaque titre avec le nom de son éditeur.
+Afficher les livres qui n’ont aucun exemplaire dans la base.
+</WRAP>
+===== C. Agrégats et regroupements =====
+<WRAP todo round>
-==== Exercice 6 — Emprunts, adhérents et exemplaires (trois tables) ====
+=== Exercice 13 — Effectif par ville ===
-Lister les prêts avec le nom et le prénom de l’adhérent, le code-barres, la date d’emprunt et la date de retour. Inclure les prêts actifs.
+Compter les adhérents de chaque ville et trier de l’effectif le plus important au plus faible.
-==== Exercice 7 — Livres et auteurs ====
+=== Exercice 14 — Catalogue par genre ===
-Afficher chaque titre et chacun de ses auteurs. Le livre coécrit apparaît sur deux lignes, une par auteur.
+Compter le nombre de livres par genre et trier du plus grand nombre au plus petit.
-==== Exercice 8 — LEFT JOIN : livres sans exemplaire ====
+=== Exercice 15 — Genres avec au moins cinq titres ===
-Afficher tous les livres et le nombre d’exemplaires de chacun, y compris ceux qui en ont zéro.
+Afficher les genres qui comptent au moins cinq livres.
-===== C. Comptages et regroupements =====
+=== Exercice 16 — Nombre d’exemplaires par livre ===
-==== Exercice 9 — Nombre de livres par genre ====
+Pour chaque livre, afficher le titre et le nombre d’exemplaires. Inclure les livres qui n’en ont aucun.
-Afficher le nombre de livres dans chaque genre, du genre le plus représenté au moins représenté.
+=== Exercice 17 — Emprunts par adhérent ===
-==== Exercice 10 — HAVING : genres représentés plusieurs fois ====
+Afficher chaque adhérent avec le nombre d’emprunts enregistrés, y compris ceux qui n’ont aucun emprunt.
-Afficher uniquement les genres comptant au moins deux livres.
+=== Exercice 18 — Adhérents ayant plusieurs emprunts ===
-==== Exercice 11 — Nombre de prêts par adhérent, y compris zéro ====
+Afficher l’identifiant, le nom et le nombre d’emprunts des adhérents ayant au moins trois emprunts enregistrés.
+</WRAP>
+===== D. Dates et analyse des emprunts =====
+<WRAP todo round>
-Afficher chaque adhérent et le nombre total de ses prêts enregistrés, même s’il n’en a aucun.
+=== Exercice 19 — Emprunts actifs ===
-==== Exercice 12 — Adhérents ayant plusieurs prêts ====
+Afficher les emprunts non rendus, avec le titre du livre, le nom de l’adhérent, la date d’emprunt et la date d’échéance.
-Afficher les adhérents qui ont au moins deux emprunts enregistrés (historiques et actifs confondus).
+=== Exercice 20 — Retards au 15 mars 2025 ===
-===== D. Prêts et analyses de dates =====
+À la date du 15 mars 2025, afficher les emprunts non rendus dont la date d’échéance est antérieure à cette date. Afficher également le nombre de jours de retard.
-==== Exercice 13 — Prêts actifs ====
+=== Exercice 21 — Retours en retard ===
-Lister les prêts encore actifs au sens pédagogique (''date_retour'' non renseignée), avec l’adhérent et le livre concerné.
+Afficher les emprunts rendus après leur date d’échéance et calculer le nombre de jours séparant l’échéance du retour.
-==== Exercice 14 — Retards à la date de référence ====
+=== Exercice 22 — Nombre d’emprunts par livre ===
-Parmi les prêts actifs au 15 mars 2025, afficher ceux dont l’échéance est dépassée et calculer le nombre de jours de retard.
+Pour chaque livre, afficher son titre et le nombre total d’emprunts enregistrés. Inclure les livres sans exemplaire et ceux dont les exemplaires n’ont jamais été empruntés.
-==== Exercice 15 — Retours après échéance ====
+=== Exercice 23 — Emprunts par genre ===
-Lister les prêts rendus après leur date d’échéance, avec le nombre de jours de retard au retour.
+Pour chaque genre, afficher le nombre d’emprunts enregistrés pour ses livres. Inclure les genres pour lesquels aucun emprunt n’a été enregistré.
-==== Exercice 16 — Nombre de prêts par titre ====
+=== Exercice 24 — Auteurs de plusieurs livres ===
-Pour chaque livre, afficher le nombre de prêts associés à ses exemplaires; inclure les livres sans exemplaire ou sans prêt.
+Afficher le nom et le prénom des auteurs associés à au moins deux livres, ainsi que le nombre de livres concernés.
+</WRAP>