sio:bloc1:td3

Ceci est une ancienne révision du document !


TD3 - SQL MySQL — Médiathèque : exercices progressifs

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.

DBeaver Community est un client de base de données. Il sert à écrire et exécuter des requêtes, mais ne contient pas le serveur MySQL.

  1. Téléchargez l’édition Community depuis le site officiel de DBeaver Community. Choisissez l’installateur qui correspond à votre système d’exploitation.
  2. Lancez l’installateur téléchargé et suivez les étapes proposées par votre système, puis ouvrez DBeaver.
  3. 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.

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

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.

  1. Dans DBeaver, démarrez l’assistant de nouvelle connexion, puis recherchez et choisissez MySQL.
  2. 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.
  3. Testez la connexion, puis validez. Si nécessaire, laissez DBeaver télécharger le pilote JDBC MySQL.
  4. 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.
  5. 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.

Pour les paramètres de connexion détaillés, consultez la documentation DBeaver pour MySQL et la documentation de création d’une connexion.

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.

Table Champs principaux Rôle / relations
editeur id_editeur, nom, ville Informations sur les éditeurs.
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.

Afficher le titre, le genre et l’année de publication de tous les livres.

Afficher les titres de science-fiction publiés en 1950 ou après.

Afficher les livres dont le titre contient « du », triés par année de publication décroissante puis par titre croissant.

Afficher les emprunts commencés entre le 1er et le 28 février 2025 inclus, du plus récent au plus ancien.

Afficher chaque titre avec le nom de son éditeur.

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.

Afficher chaque titre et chacun de ses auteurs. Le livre coécrit apparaît sur deux lignes, une par auteur.

Afficher tous les livres et le nombre d’exemplaires de chacun, y compris ceux qui en ont zéro.

Afficher le nombre de livres dans chaque genre, du genre le plus représenté au moins représenté.

Afficher uniquement les genres comptant au moins deux livres.

Afficher chaque adhérent et le nombre total de ses prêts enregistrés, même s’il n’en a aucun.

Afficher les adhérents qui ont au moins deux emprunts enregistrés (historiques et actifs confondus).

Lister les prêts encore actifs au sens pédagogique (date_retour non renseignée), avec l’adhérent et le livre concerné.

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.

Lister les prêts rendus après leur date d’échéance, avec le nombre de jours de retard au retour.

Pour chaque livre, afficher le nombre de prêts associés à ses exemplaires; inclure les livres sans exemplaire ou sans prêt.

  • sio/bloc1/td3.1794528373.txt.gz
  • Dernière modification : il y a 5 jours
  • de jcheron