Enseignement > Bases de données  

Base de données

TD modélisation d'une base de données de formation universitaire

On cherche à créer une base de données à partir du schéma ci-dessous

Exemple de requete sur cette base de données

SELECT DISTINCT etudiant.nom, etudiant.prenom FROM etudiant,inscrit,diplome,compose,matiere,enseigne,enseignant WHERE enseignant.nom='BONNEVILLE' AND enseignant.Prenom='François' AND etudiant.NoEtudiant=inscrit.NoEtudiant AND inscrit.Identifiant=diplome.Identifiant AND diplome.Identifiant=compose.Identifiant AND compose.IdModule=matiere.IdModule AND matiere.IdModule=enseigne.IdModule AND enseigne.IdEnseignant=enseignant.IdEnseignant
 

TP Cinema

Importer ce fichier SQL dans MySQL et effectuer des requètes pour répondre aux questions suivantes :

  • Afficher la liste des films
    SELECT * FROM film
  • Afficher la liste des personnes dont le nom contient ROBERT
    SELECT * FROM personnes WHERE nom LIKE "%ROBERT%"
  • Afficher la liste des films français du XXIe siecle ayant dépassé 1M d'entrée, classés dans l'ordre décroissant du box office
    SELECT * FROM film WHERE  film.`année de sortie`>2000 AND film.entrées>=1000000  
    ORDER BY film.entrées DESC
  • Quel est le plus grand nombre d'entrées réalisées par film français ?
    SELECT film.titre, film.entrées FROM film WHERE film.nationalité LIKE "%FR%" ORDER BY film.entrées DESC
     
  • Quel est titre du film français qui a fait le plus d'entrées ?
    Solution 1 :
    SELECT film.titre, film.entrées FROM film WHERE film.nationalité LIKE "%FR%" ORDER BY film.entrées DESC LIMIT 1
  • Solution 2 :
    SELECT titre FROM film WHERE entrées = (SELECT MAX(entrées) FROM film WHERE nationalité LIKE "%FR%")

  • Quel sont les films qui ont fait le plus d'entrées, par pays ?

SELECT f.titre, f.entrées, f.nationalité FROM film f WHERE entrées = (
     SELECT MAX(entrées)
  FROM film
  WHERE nationalité = f.nationalité
)

  • Corriger la nationalité "IT/FR" en "FR/IT"

UPDATE film SET nationalité="FR/IT" where nationalité="IT/FR"

  • Quels sont les films où Bourvil apparait au générique ?

SELECT DISTINCT film.titre FROM film, casting, personne
WHERE film.idFilm = casting.idFilm AND casting.idPersonne = personne.id AND nom = 'Bourvil';

  • Dans combien de film Harrison Ford apparait-il au générique ?
    SELECT count(film.titre), personne.nom FROM film, casting, personne WHERE personne.id=casting.idPersonne AND casting.idFilm=film.idFilm AND personne.nom ="Harrison Ford" 
     
  • Donner les noms des compositeurs de musique de film
    SELECT * FROM casting, personne WHERE personne.id=casting.idPersonne AND fonction="musique"
     
  • Donner les noms des réalisateurs de film
    SELECT * FROM casting, personne WHERE personne.id=casting.idPersonne AND fonction="réalisateur
     
  • Y a-t-il des personnes qui ont été réalisateurs et acteurs ?
    SELECT personne.nom FROM casting, personne WHERE personne.id=casting.idPersonne AND casting.fonction="acteur" AND personne.id IN ( SELECT personne.id FROM casting, personne WHERE personne.id=casting.idPersonne AND casting.fonction="réalisateur" )
     
  • Avec quels réalisateurs Harrison Ford a-t-il tourné ?
    SELECT personne.nom FROM personne, casting WHERE personne.id=casting.idPersonne AND casting.fonction="réalisateur" AND casting.idFilm IN ( SELECT film.idFilm FROM film, casting, personne WHERE personne.id=casting.idPersonne AND casting.idFilm=film.idFilm AND personne.nom ="Harrison Ford")
     
  • Donner le total des entrées réalisées par chaque personne triés dans l'ordre décroissant
    SELECT sum(film.entrées) as somme, personne.nom FROM personne,casting,film WHERE personne.id=casting.idPersonne AND casting.idFilm=film.idFilm GROUP BY personne.nom ORDER BY somme DESC

TP "Gestion commerciale"

On s'intéresse aux données de la gestion commerciale d'une petite boutique de jouets en bois.

I. Examinez attentivement les fichiers de données suivants pour comprendre les relations qui les lient.
produits.csv, clients.csv, Commande.csv, ligneCommande.csv

Créez un modèle conceptuel de données avec Looping ou tout autre outil à votre convenance.

Telecharger le fichier modèle généré par looping

II. Dans le SGBB MySQL, créez une nouvelle base de données
Dans cette base de données, créez les tables issues de votre modèle conceptuel de données, puis importez les données à partir des fichiers .csv

III. Ecrivez des requêtes permettant de

  1. Trier les produits selon leur prix de vente dans l'ordre décroissant
  2. Afficher les clients qui ne résident pas en France
  3. Afficher la référence et le nom des produits commandés par Mr Jérome Fostinelli
  4. Afficher les noms des clients ayant commandé le produit "corde à sauter"
  5. Calculer le chiffre d'affaires réalisé au cours de l'année 2019
  6. Afficher le chiffre d'affaires réalisé par famille de produits
  7. Afficher le hit-parade des ventes (Liste des noms et des quantités des produits les plus vendus dans l'ordre décroissant des ventes)
  8. Trouver les produits qui ne se sont pas vendus
  9. Calculer le panier moyen
  10. Faire une promotion de 20% sur tous les produits de la famille "poupées"

On trouve dans la table "produits" et dans la table "ligneCommande" des champs relatifs au prix de vente. Il y a là une certaine redondance. Expliquez en quoi cette redondance peut être utile.

Corrigé

Modèle conceptuel de données

Code SQL de création des tables :

CREATE TABLE Client(numClient INT, civilite VARCHAR(50), nom VARCHAR(50), prenom VARCHAR(50),
adresse VARCHAR(150), codepostal CHAR(7), ville VARCHAR(50), pays VARCHAR(50),
PRIMARY KEY(numClient));

CREATE TABLE Produit(numProduit INT, famille VARCHAR(50), nom VARCHAR(50), prixHT DECIMAL(15,2),
prixPromoHT DECIMAL(15,2), TVA DECIMAL(15,2), designation VARCHAR(250), PRIMARY KEY(numProduit));

CREATE TABLE Commande(numCommande INT, dateCommande DATE, numClient INT NOT NULL,
PRIMARY KEY(numCommande), FOREIGN KEY(numClient) REFERENCES Client(numClient));

CREATE TABLE ligneCommande(numProduit INT, numCommande INT, prixTTC DECIMAL(15,2), quantite INT,
PRIMARY KEY(numProduit, numCommande), FOREIGN KEY(numProduit) REFERENCES Produit(numProduit),
FOREIGN KEY(numCommande) REFERENCES Commande(numCommande));

 

Question 1
SELECT * FROM produit ORDER BY prixHT DESC

Question 2
SELECT * FROM clientele WHERE pays<>"France"

Question 3
SELECT NumProduit,titre FROM clientele, Commande, ligneCommande, produit WHERE clientele.Nom="Fostinelli" AND clientele.Prenom="Jérome" AND clientele.NumClient=Commande.NumeroClient and Commande.NumeroCommande=ligneCommande.Commande and ligneCommande.Produit=produit.NumProduit

Question 4
SELECT DISTINCT nom,prenom FROM clientele, Commande, ligneCommande, produit WHERE produit.titre="Corde à sauter" AND clientele.NumClient=Commande.NumeroClient and Commande.NumeroCommande=ligneCommande.Commande and ligneCommande.Produit=produit.NumProduit

Question 5
SELECT sum(prixTTC*Quantite) FROM ligneCommande,Commande WHERE ligneCommande.Commande=Commande.NumeroCommande and year(Date_commande)=2019

Question 6
SELECT sum(ligneCommande.prixTTC*ligneCommande.Quantite),produit.famille FROM ligneCommande,produit WHERE ligneCommande.Produit=produit.NumProduit GROUP BY produit.famille

Question 7
SELECT SUM(Quantite) AS SOMME,titre FROM ligneCommande,produit WHERE produit.NumProduit=ligneCommande.Produit GROUP BY ligneCommande.Produit ORDER BY SOMME DESC

Question 8
SELECT produit.titre FROM produit WHERE produit.NumProduit NOT IN (SELECT ligneCommande.Produit FROM ligneCommande)

App Question 9
SELECT AVG(somme) FROM (SELECT sum(ligneCommande.prixTTC*ligneCommande.Quantite) as somme FROM ligneCommande GROUP by ligneCommande.Commande) AS PANIER

Question 10
UPDATE produit SET prixPromoHT=prixHT/1.2 WHERE famille="poupées"

La table ligneCommande contient le prix de vent du produit au moment de l'achat, alors que la table produit contient le prix de vente actuel. Entre temps, ce prix a pu changé ; il est donc indispensable d'enregistrer le prix de vente au moment de l'achat.

 

TP "Aviation"

On vous donne l'exportation d'une base de données SQL concernant des avions et des compagnies aériennes.

Importez  cette base de données puis examiner ces données et essayer de comprendre la structure.

Noter que le modèle relationnel n'est pas mis en place. Créer les relations entre les tables.

Écrire les requètes SQL permettant de répondre aux questions suivantes :

  • Combien d'avions la compagnie Air France exploite-t-elle ?
  • Quelle est la compagnie aérienne qui exploite le plus d'avion ?

    SELECT count(*) as nb,compagnies_aériennes.Nom FROM flotte, compagnies_aériennes WHERE flotte.ID_Compagnie=compagnies_aériennes.ID_Compagnie GROUP BY compagnies_aériennes.ID_Compagnie ORDER BY nb DESC LIMIT 1
  • Quels sont les types d'avions utilisés par British Airways ?

    SELECT DISTINCT avions_ligne.ID_ALFull, avions_ligne.Constructeur FROM flotte, compagnies_aériennes, avions_ligne WHERE avions_ligne.ID_AL=flotte.ID_AL AND flotte.ID_Compagnie=compagnies_aériennes.ID_Compagnie AND compagnies_aériennes.Nom="British Airways"
  • Quelle compagnie aérienne est le meilleur client d'Airbus ?

    SELECT count(*) as nb,compagnies_aériennes.nom FROM flotte, compagnies_aériennes, avions_ligne WHERE avions_ligne.ID_AL=flotte.ID_AL AND flotte.ID_Compagnie=compagnies_aériennes.ID_Compagnie AND avions_ligne.Constructeur="Airbus" GROUP BY compagnies_aériennes.ID_compagnie ORDER BY nb DESC
  • Combien de passagers la compagnie Lufthansa peut-elle transporter simulatnément au maximum de ses capacités ?

    SELECT SUM(avions_ligne.NbrePassMax) FROM flotte, compagnies_aériennes, avions_ligne WHERE avions_ligne.ID_AL=flotte.ID_AL AND flotte.ID_Compagnie=compagnies_aériennes.ID_Compagnie AND compagnies_aériennes.nom="Lufthansa"
  • Quel est le moteur d'avion qui a été le plus fabriqué ?

    SELECT count(moteurs.ID_Moteur * avions_ligne.NbreMoteurs) as nb ,moteurs.ID_MoteurFull, moteurs.Fabricant FROM flotte, compagnies_aériennes, avions_ligne, moteurs WHERE moteurs.ID_Moteur=avions_ligne.ID_Moteur AND avions_ligne.ID_AL=flotte.ID_AL AND flotte.ID_Compagnie=compagnies_aériennes.ID_Compagnie GROUP BY moteurs.ID_MoteurFull order by nb DESC LIMIT 1
     
  • Qui est le plus grand fabricant de moteurs d'avions (en nombre de moteurs fabriqués) ?

    SELECT count(moteurs.ID_Moteur * avions_ligne.NbreMoteurs) as nb, moteurs.Fabricant FROM flotte, compagnies_aériennes, avions_ligne, moteurs WHERE moteurs.ID_Moteur=avions_ligne.ID_Moteur AND avions_ligne.ID_AL=flotte.ID_AL AND flotte.ID_Compagnie=compagnies_aériennes.ID_Compagnie GROUP BY moteurs.Fabricant order by nb DESC LIMIT 1
  • Quelle compagnie est le meilleur client de Rolls-Royce (en nombre de moteurs fabriqués) ?

    SELECT count(moteurs.ID_MoteurFull*avions_ligne.NbreMoteurs) as nb, compagnies_aériennes.nom FROM flotte, compagnies_aériennes, avions_ligne, moteurs WHERE moteurs.ID_Moteur=avions_ligne.ID_Moteur AND avions_ligne.ID_AL=flotte.ID_AL AND flotte.ID_Compagnie=compagnies_aériennes.ID_Compagnie AND moteurs.Fabricant="Rolls-Royce" GROUP BY compagnies_aériennes.nom order by nb DESC

Améliorer le modèle pour ajouter les tables nécessaires pour pouvoir réaliser une application permettant de programmer des trajets aériens entre des destinations, avec des pilotes qui seront chargés de piloter les avions.

Remplir ces tables avec quelques exemples, puis proposer des requêtes intéressantes pour exploiter cette base de données.