TD4 - Du LDD au MLD

Inès de Courchelle - Elisabeth Ranisavljevic

Durée : 3h

Consignes :

Durant ce TD l’utilisation d’un papier et d’un crayon est fortement conseillé !

Objectifs :

  • Inserer des données
  • Manipulez des données (ajout/suppression/miseàjours)
  • Utiliser des fonctions SQL d’agrégation

Attention :

  • L’ensemble des exercices ci-dessous ne seront pas tous corrigés en cours !
  • Les éléments de correction seront donnés en TD, EN AUCUN CAS, des corrections toutes faites vous serons données ou distribuées. Vous devez prendre des notes !

Exo 1 - Mettre en place la BDD Chats

Nous considérons le MLD suivant :

Animal(idAnimal, appelation,typeA)

Chat(idChat, prénom,robe,#idAnimal)

Chien(idChien, prénom,robe,#idAnimal)

  1. Créer un script appelé mesChats.sql
  2. Ajouter les instructions suivantes au scipt permettant de :
  • Créer la base de données monPetitChat
  • Utiliser la base données monPetitChat
  • Créer la table Animal
  • Créer la table Chat
  1. Lancer mySQL sur le terminal
  2. Exécuter le script
  3. Ajouter les instructions au script permettant d’insérer les animaux suivant :
id appelation type
01 chat domestique
02 linx sauvage
03 léopard sauvage
04 chien domestique
  1. Ajouter les instructions au script permettant d’insérer les chats suivant :
id prénom robe idAnimal
01 Lizzy noire 01
02 Pounette isabelle 01
03 Leia grise 01
04 Misty tigrée 01
05 Miaoumix grise 01
  1. Ajouter les instructions au script permettant d’insérer les chiens suivant :
id prénom robe idAnimal
01 Astère golden retriever 04
02 Fidgy cane corso 04
03 Alex papillon 04

  1. Ajouter les instructions au script permettant de modifier le nom de la colonne robe dans la table chien par type

  2. Réaliser le MCD correspondant

Exo 2 - Mes premières requêtes

Nous considèrons le MLD suivant :

Artiste(idArtiste, nom, prenom)

Chanson(idChanson, titre, durée,#idAlbum)

ArtisteComposer(#idArtiste, #idAlbum)

Album(idAlbum,titre,dateDeSortie);

  1. Télécharger le script musicLDD.sql
  2. Lancer mySQL
  3. Exécuter le script musicLDD.sql
  4. Créer le script musicLMD.sql
  5. Ajouter les requêtes au script musicLMD.sql permettant de répondre aux questions suivantes :
    1. Quelles sont le titre et la durée de toutes les chansons stockées dans la BDD ? Dans le résultat de la requête, vous afficherez toutes les 2 colonnes suivante : titre et durée.
    2. Quels sont les artistes dont le nom commencent par la lettre L ? Dans le résultat de la requête, vous afficherez toutes les 3 colonnes de l’artiste (idArtiste,nom et prénom).
    3. Quels sont les titres commençant par la lettre H OU T ? Dans le résultat de la requête, vous afficherez toutes les 2 colonnes suivante : titre et durée.
    4. Quelle est la date de sortie de l’album “Physical Graffity” ? Dans le résultat de la requête, vous afficherez uniquement la colonne dateDeSortie.
    5. Combien de chanson possède l’album “Help” ? Pour cette requête, vous pouvez vous servir de l’id de l’album qui est le n°1 (atention il ne faut pas utiliser de jointure, on ne les a pas encore étudiées).
    6. Affiche les noms et prénoms des Artiste qui ont la lettre a dans leur nom ou prénom.
    7. Selectionner les Album qui sont sortis en juillet.
    8. Afficher la durée des chansons sous la forme MM min SS.
    9. Supprimer l’Artiste John Lenon.
  6. Réaliser le MCD correspondant

Exo 3 - Zelda

Nous considèrons le MLD suivant :

Joueur(idJoueur, pseudo, niveauEndurance, nbCoeurs)

Plat(idPlat, nom, famille,puissance)

Manger(#idJoueur, #idPlat,horaire)

Ingredient(idIngredient,nom);

Composer(#idIngredient,#idPlat,quantité);

Monde(idMonde, nom)

Sanctuaire(idSanctuaire, nom, niveau, #idMonde)

Visiter(#idJoueur,#idSanctuaire,horaire, vainqueur)

CreatureDivine(idCreatureDivine,nom,#idMonde)

Combattre(#idJoueur,#idCreatureDivine,horairevainqueur)

  1. Télécharger le script zeldaLDD.sql

  2. Lancer mySQL dans le terminal.

  3. Exécuter le script zeldaLDD.sql

Si vous obetenez des erreurs similaires :

Pas de panique c’est dû aux insertions aléatoires, dans la table Combattre. Une ou plusieurs générations aléatoire ont dû créer des doublons.

INSERT INTO Combattre VALUES (round(rand()*8+1),round(rand()*4+1),FROM_UNIXTIME(UNIX_TIMESTAMP('2018-09-01 8:15:15') + FLOOR(0 + (RAND() * 63072000))),true);
...
INSERT INTO Manger VALUES (05,02,FROM_UNIXTIME(UNIX_TIMESTAMP('2018-09-01 8:15:15') + FLOOR(0 + (RAND() * 63072000))));
...

Dans tous les cas, il devrait y avoir assez d’insertion pour avoir des tables plus ou moins remplies.

Il n’y a donc RIEN À FAIRE

  1. Créer le script zeldaLMD.sql
  2. Ajouter les instructions au script zeldaLMD.sqlpermettant d’afficher :
    1. Quels sont les sanctuaires commençant par la lettre T ? Dans le résultat, vous afficherez l’ensemble des colonnes de la table Sanctuaire.
    2. Compter le nombre de combat au total réalisée par tous les joueures contre les créatures divines ? Dans le résultat, vous afficherez le nombreTotal de combat.
    3. Quel est le niveau de sanctuaire le plus bas ? Dans le résultat, vous afficherez le niveau.
    4. Afficher tous les niveaux d’endurance différents des joueurs (sans doublons).
    5. Sélectionner tous les plats dont la puissance est impaire.
    6. Compter le nombre de personnes qui ont mangé entre 16h et 18h.
    7. Modifier la victoire à vrai pour tous les combats contre la créature divine dont l’id = 4.
  3. Réaliser le MCD correspondant

Exo 4 - Harrypotter

Nous considèrons le MLD suivant :

Maison(idMaison, nom, couleur)

Eleve(idEleve, nom, prenom,#idMaison)

Professeur(idProfesseur,nom, prenom);

Matiere(idMatiere,intitule,#idProfesseur);

EleveSuitMatiere(#idEleve,#idMatiere)

Points(#idProfesseur,#idEleve,horaire,motif,nbPoints);

  1. Télécharger le script harryPotterLDD.sql
  2. Lancer mySQL
  3. Exécuter le script harryPotterLDD.sql
  4. Créer le script harryPotterLMD.sql
  5. Ajouter les instructions au script harryPotterLMD.sqlpermettant d’afficher :
    1. Quels élèves ont leur prénom commençant par un “M” ? Dans le résultat de recherche, vous afficherez l’ensemble des colonnes d’un éléve.
    2. Quels sont les couleurs de la maison de serpentard ? Dans le résultat de recherche, vous afficherez la colonne couleur.
    3. Quel est le classement du nombre de points remportés par les éléves (il peut être négatif) ? Dans le résultat, vous afficherez par ordre descroissant, le nombre de points remportés et l’idEleve associé.
      1. En moyenne, combien de point le professeur dont l’id est ‘01’ a-t-il delivré de point (positivement ou négativement) ? Dans le résultat de recherche, vous afficherez la moyenne des points du professeur.
    5. Afficher tous les motifs différents des Points.
    6. Compter le nombre d’étudiants qui suivent le cours de botanique (id = 4).
    7. Calculer le nombre de points de Harry Potter (id = 1).
    8. Calculer le nombre de jours qui se sont passés entre la première et la dernière fois où Harry Potter (id=1) a eu des points négatifs. (indice : DATEDIFF).
  6. Réaliser le MCD correspondant