Correction du TP sur la modélisation SQL vers NoSQL - MongoDB

Rappel des contraintes

Créer une seule collection (lesavions)
Utiliser les données des fichiers JSON fournis.

Modélisation NoSQL

Avec Mongo, on gère des collections qui contiennent des documents. Dans notre TP, nous avons 3 fichiers JSON structurés pour une base de données relationnelle. Nous devons raisonner différemment car les requêtes seront exécutées sur des documents appartenant à une seule Collection.

Mais comment créer une structure de document qui reprend les attributs des 3 fichiers JSON Avion, Pilote et Vol ?

En fait, il y a plusieurs solutions possibles, on pourrait prendre la structure du fichier JSON VOL puisque nous avons un Vol associé à un et un seul Pilote et forcément un et un seul Avion. Cependant, certaines requêtes seraient plus compliquées en NoSQL si l’on souhaite faire des recherches sur les Avions !

Donc la solution la plus serait de prendre la structure de départ du fichier JSON Avion et d’y greffer un tableau de Vol qui forcément est associé à un pilote ! Il pourrait y avoir des tableaux Vols vides pour un avion qui ne volent pas. Par contre, que fait-on des Pilotes qui ne volent pas ? Je vous laisseméditer…

Choix pour ce TP

Ce choix ci-dessosu, n’est pas forcément le meilleur mais il sera plus évident pour rédiger nos requêtes en NoSQL !

un document = un avion
les vols sont stockés dans un tableau
les pilotes sont inclus dans chaque vol (ce qui est logique)

proposition de structure (discutable)

Structure d’un document de notre Collection lesavions :

{
  "_id": 100,
  "constructeur": "AIRBUS",
  "modele": "A320",
  "capacite": 300,
  "site": "NICE",
  "vols": [
    {
      "vol_id": "IT100",
      "site_depart": "NICE",
      "site_arrivee": "PARIS",
      "heure_depart": "07:00",
      "heure_arrivee": "09:00",
      "pilote": {
        "id": 1,
        "nom": "DORE",
        "site": "NICE"
      }
    }
  ]
}

Avantages de cette structure

Absence de jointure (Youpi !)
Requêtes plus rapides et surtout plus simples

Inconvénients : Mais où sont les pilotes qui ne volent pas ?

Un pilote qui ne vole pas n’apparaît nulle part, on perd l’information et c’est un problème de modélisation orientée documents !

Il existe des solutions comme la possibilité d’ajouter un tableau pilotes au niveau racine de la collection, mais c’est pas l’idéal, sinon, il vaut mieux créer une collection à part pour tous les pilotes, même ceux qui ne volent pas… cependant, ce n’est pas l’objectif de ce tp.

Exemple :

{
  "_id": "pilotes",
  "pilotes": [
    {
      "id": 1,
      "nom": "DORE",
      "prenom": "Julien",
      "site": "NICE"
    },
    {
      "id": 2,
      "nom": "JORDANA",
      "prenom": "Camélia",
      "site": "PARIS"
    },
    {
      "id": 5,
      "nom": "DUPONT",
      "site": "LYON"
    }
  ]
}

Du coup, les pilotes existent même sans vol et les vols font référence à un pilote via son identifiant du pilote (pilote_id). C’est une solution.

Exemple :

{
  vol_id: "IT100",
  pilote_id: 1
}

>En NoSQL, **on modélise les données selon les usages et non selon les entités** comme dans un MCD.

## Création de la base MongoDB

>Le `use` permet de créer la base automatiquement. Vous pouvez la nommer comme vous voulez !

```js
use bd_avions

Création de notre collection :

db.createCollection("lesavions")

Insertion des données récupérées

On va utiliser insertMany()

db.lesavions.insertMany([
{
_id:100,
constructeur:"AIRBUS",
modele:"A320",
capacite:300,
site:"NICE"
},
{
_id:101,
constructeur:"BOEING",
modele:"B707",
capacite:250,
site:"PARIS"
},
{
_id:102,
constructeur:"AIRBUS",
modele:"A320",
capacite:300,
site:"TOULOUSE"
},
{
_id:103,
constructeur:"CARAVELLE",
modele:"Caravelle",
capacite:200,
site:"TOULOUSE"
},
{
_id:104,
constructeur:"BOEING",
modele:"B747",
capacite:400,
site:"PARIS"
},
{
_id:105,
constructeur:"AIRBUS",
modele:"A320",
capacite:300,
site:"GRENOBLE"
},
{
_id:106,
constructeur:"ATR",
modele:"ATR42",
capacite:50,
site:"PARIS"
},
{
_id:107,
constructeur:"BOEING",
modele:"B727",
capacite:300,
site:"LYON"
},
{
_id:108,
constructeur:"BOEING",
modele:"B727",
capacite:300,
site:"NANTES"
},
{
_id:109,
constructeur:"AIRBUS",
modele:"A340",
capacite:350,
site:"BASTIA"
}
])

Affichage des insertions

On peut compter le nombre documents ajoutés.

db.lesavions.countDocuments()

Tout simplement les afficher.

db.lesavions.find().pretty()

Proposition de requêtes NoSQL (Mongo)

1. liste de tous les numéros d’avions

 db.lesavions.find({}, {_id:1})

ou ça si vous voulez plus d’infos :

 db.lesavions.find({}, {_id:1, constructeur:1, modele:1})

2. Liste des noms des pilotes (qui volent par défaut ici)

db.lesavions.distinct("vols.pilote.nom")

Si on souhaite afficher les prénoms des pilotes, on est obligé de faire un aggregate comme ceci :

Avec Doublons :(

db.lesavions.aggregate([ { $unwind: "$vols" },  { $project: { _id: 0, nom: "$vols.pilote.nom", prenom: "$vols.pilote.prenom"  } } ])

et sans doublons :

db.lesavions.aggregate([ { $unwind: "$vols" }, { $group: {  _id: { nom: "$vols.pilote.nom", prenom: "$vols.pilote.prenom"  } } },
  {
    $project: { _id: 0, nom: "$_id.nom", prenom: "$_id.prenom" } } ])

3. Liste des marques d’avions (sans doublon)

db.lesavions.distinct("constructeur")

4. Liste des vols pour Nice

plus complexe ici, il faut utiliser aggregate.

db.lesavions.aggregate([ {$unwind:"$vols"}, {$match:{"vols.site_arrivee":"NICE"}}
])

5. Liste des avions qui ont plus de 200 places

db.lesavions.find( {capacite:{$gt:200}}, {_id:1,modele:1,capacite:1} )

6. Liste des avions AIRBUS localisés à Toulouse

db.lesavions.find({ constructeur:"AIRBUS", site:"TOULOUSE" })

7. Liste des avions AIRBUS allant à Paris

db.lesavions.aggregate([ {$match:{constructeur:"AIRBUS"}}, {$unwind:"$vols"}, {$match:{"vols.site_arrivee":"PARIS"}} ])

8. Liste des vols Paris-Nice et Toulouse-Paris

db.lesavions.aggregate([ {$unwind:"$vols"}, {$match:{ $or:[ {"vols.site_depart":"PARIS","vols.site_arrivee":"NICE"}, 
                                                            {"vols.site_depart":"TOULOUSE","vols.site_arrivee":"PARIS"}]}}
                      ])

9. Liste des avions Airbus et Boeing

db.lesavions.find({ constructeur:{$in:["AIRBUS","BOEING"]} })

10. Liste des Airbus ou des avions de plus de 200 places

db.lesavions.find({ $or:[ {constructeur:"AIRBUS"}, {capacite:{$gt:200}}] })

11. Liste des avions AIRBUS qui ne sont pas localisés à Toulouse

db.lesavions.find({ constructeur:"AIRBUS", site:{$ne:"TOULOUSE"} })

12. Liste des Airbus qui ne vont pas à Paris

db.lesavions.find({ constructeur:"AIRBUS", vols:{ $not:{ $elemMatch:{site_arrivee:"PARIS"} } } })

13. Liste des avions pour Paris qui ne sont pas des Airbus

db.lesavions.aggregate([
{$match:{constructeur:{$ne:"AIRBUS"}}},
{$unwind:"$vols"},
{$match:{"vols.site_arrivee":"PARIS"}}
])

14. Liste de tous les vols avec le modèle des avions

Les avions n’ont pas vraiment de nom c’est plutôt le modèle.

db.lesavions.aggregate([ {$unwind:"$vols"}, {$project:{ vol:"$vols.vol_id", avion:"$modele" }} ])

15. Constructeur et capacité des avions en service (donc des avions qui volent !)

ça veut dire qu’on a au moins un vol pour l’avion ! Donc on peut utiliser le vols.0 pour récupérer la première valeur de notre tableau vols avec le mot clef $exists.

db.lesavions.find({ "vols.0":{$exists:true},  {_id:1, constructeur:1,capacite:1} })

16. Liste des avions qui ne sont pas des BOEING allant à Paris

db.lesavions.aggregate([ {$ne:{constructeur:"BOEING"}}, {$unwind:"$vols"}, {$match:{"vols.site_arrivee":"PARIS"}} ])

17. Nom des pilotes en service

db.lesavions.distinct("vols.pilote.nom")

18. Nom des avions (BOEING) ayant une même capacité (auto-jointure en SQL !)

Un peu plus compliqué à écrire. on recherche par Constructeur, on regroupe par Capacité et on vérifie qu’il y a bien la même capacité pour le même modèle que nous passons sous la forme de variable $modele. le $nb nous permet de savoir que nous avons bien trouvé un Boeing.

db.lesavions.aggregate([ {$match:{constructeur:"BOEING"}}, {$group:{ _id:"$capacite", avions:{$push:"$modele"}, nb:{$sum:1} }}, {$match:{nb:{$gt:1}}} ])

19. Nombre d’avions de chaque marque

En fait, il faut comprendre marque comme constructeur.

db.lesavions.aggregate([ {$group:{ _id:"$constructeur", nombre:{$sum:1} }} ])

20. Nombre de pilotes différents pour chaque avion en service

Ici, la question est, comment savoir qu’un avion est en service ? Normalement, il vole. On peut identifier les pilotes par leur identifiant. Ici, on peut utiliser $addToSet permettant d’ajouter les pilotes trouvés à notre liste de pilotes.

db.lesavions.aggregate([ {$unwind:"$vols"}, {$group:{ _id:"$_id", pilotes:{$addToSet:"$vols.pilote.id"} }},
                         {$project:{ nb_pilotes:{$size:"$pilotes"} }} ])

21. Nombre de vols différents pour chaque pilote (regroupé par nom)

db.lesavions.aggregate([ {$unwind:"$vols"}, {$group:{ _id:"$vols.pilote.nom", nb_vols:{$sum:1} }} ])

22. Pilotes (ordre croissant des numéros) assurant plus d’un vol (Afficher: Numéro et nom des pilotes, nombre de vols)

db.lesavions.aggregate([ {$unwind:"$vols"}, {$group:{ _id:"$vols.pilote.id", nom:{$first:"$vols.pilote.nom"}, nb_vols:{$sum:1} }},
                                            {$match:{nb_vols:{$gt:1}}} ])

23. Nombre de vols assurés au départ de Nice ou de Paris par chaque pilote (Afficher: Numéros des pilotes, ville de départ et nombre de vols)

db.lesavions.aggregate([ {$unwind:"$vols"}, {$match:{ "vols.site_depart":{$in:["NICE","PARIS"]} }} ])

24. Nombre de vols assurés au départ ou à l’arrivée de Nice par chaque pilote (Afficher: Numéros des pilotes, nombre de vols)

db.lesavions.aggregate([ {$unwind:"$vols"}, {$match:{ $or:[ {"vols.site_depart":"NICE"}, {"vols.site_arrivee":"NICE"} ] }} ])

25 Liste des vols dont la ville de départ correspond à la ville où est localisé l’avion

db.lesavions.aggregate([ {$unwind:"$vols"}, {$match:{ $expr:{$eq:["$vols.site_depart","$site"]} }} ])

26 Liste des avions de capacité égale ou supérieure à la moyenne

db.lesavions.aggregate([ {$group:{ _id:null, moyenne:{$avg:"$capacite"} }} ])

27. Capacité mini et maxi des BOEING

db.lesavions.aggregate([ {$match:{constructeur:"BOEING"}}, {$group:{ _id:null, min:{$min:"$capacite"}, max:{$max:"$capacite"} }} ])

28 Capacité moyenne avions ParisCapacité moyenne des avions localisés à Paris avec 2 chiffres après la virgule

db.lesavions.aggregate([ {$match:{site:"PARIS"}}, {$group:{ _id:null, moyenne:{$avg:"$capacite"} }} ])

29. Capacité moyenne des avions par marque

Par marque = par constructeur.

db.lesavions.aggregate([ {$group:{ _id:"$constructeur", moyenne:{$avg:"$capacite"} }} ])

Optionnelles

30. Capacité totale des avions de la table avion

db.lesavions.aggregate([ {$group:{ _id:null, total:{$sum:"$capacite"} }} ])

31. Affichage de l’heure système avec les secondes

new Date().toLocaleTimeString()

Données de la Collection proposée

db.lesavions.insertMany([
  {
    _id: 100,
    constructeur: "AIRBUS",
    modele: "A320",
    capacite: 300,
    site: "NICE",
    vols: [
      {
        vol_id: "IT100",
        site_depart: "NICE",
        site_arrivee: "PARIS",
        heure_depart: "07:00",
        heure_arrivee: "09:00",
        pilote: {
          id: 1,
          prenom: "Julien",
          nom: "DORE",
          site: "NICE"
        }
      },
      {
        vol_id: "IT101",
        site_depart: "PARIS",
        site_arrivee: "TOULOUSE",
        heure_depart: "11:00",
        heure_arrivee: "12:00",
        pilote: {
          id: 2,
          prenom: "Camélia",
          nom: "JORDANA",
          site: "PARIS"
        }
      }
    ]
  },
  {
    _id: 101,
    constructeur: "BOEING",
    modele: "B707",
    capacite: 250,
    site: "PARIS",
    vols: [
      {
        vol_id: "IT102",
        site_depart: "PARIS",
        site_arrivee: "NICE",
        heure_depart: "12:00",
        heure_arrivee: "14:00",
        pilote: {
          id: 1,
          prenom: "Julien",
          nom: "DORE",
          site: "NICE"
        }
      },
      {
        vol_id: "IT111",
        site_depart: "NICE",
        site_arrivee: "NANTES",
        heure_depart: "17:00",
        heure_arrivee: "19:00",
        pilote: {
          id: 4,
          nom: "ANN",
          site: "NANTES"
        }
      }
    ]
  },
  {
    _id: 102,
    constructeur: "AIRBUS",
    modele: "A320",
    capacite: 300,
    site: "TOULOUSE",
    vols: [
      {
        vol_id: "IT110",
        site_depart: "TOULOUSE",
        site_arrivee: "PARIS",
        heure_depart: "15:00",
        heure_arrivee: "16:00",
        pilote: {
          id: 2,
          prenom: "Camélia",
          nom: "JORDANA",
          site: "PARIS"
        }
      }
    ]
  },
  {
    _id: 103,
    constructeur: "CARAVELLE",
    modele: "Caravelle",
    capacite: 200,
    site: "TOULOUSE",
    vols: []
  },
  {
    _id: 104,
    constructeur: "BOEING",
    modele: "B747",
    capacite: 400,
    site: "PARIS",
    vols: []
  },
  {
    _id: 105,
    constructeur: "AIRBUS",
    modele: "A320",
    capacite: 300,
    site: "GRENOBLE",
    vols: [
      {
        vol_id: "IT103",
        site_depart: "GRENOBLE",
        site_arrivee: "TOULOUSE",
        heure_depart: "09:00",
        heure_arrivee: "11:00",
        pilote: {
          id: 3,
          prenom: "Philippe",
          nom: "KATERINE",
          site: "GRENOBLE"
        }
      },
      {
        vol_id: "IT104",
        site_depart: "TOULOUSE",
        site_arrivee: "GRENOBLE",
        heure_depart: "17:00",
        heure_arrivee: "19:00",
        pilote: {
          id: 3,
          prenom: "Philippe",
          nom: "KATERINE",
          site: "GRENOBLE"
        }
      }
    ]
  },
  {
    _id: 106,
    constructeur: "ATR",
    modele: "ATR42",
    capacite: 50,
    site: "PARIS",
    vols: [
      {
        vol_id: "IT107",
        site_depart: "PARIS",
        site_arrivee: "BRIVE",
        heure_depart: "07:00",
        heure_arrivee: "08:00",
        pilote: {
          id: 9,
          nom: "PUNK",
          site: "PARIS"
        }
      },
      {
        vol_id: "IT108",
        site_depart: "BRIVE",
        site_arrivee: "PARIS",
        heure_depart: "19:00",
        heure_arrivee: "20:00",
        pilote: {
          id: 9,
          nom: "PUNK",
          site: "PARIS"
        }
      }
    ]
  },
  {
    _id: 107,
    constructeur: "BOEING",
    modele: "B727",
    capacite: 300,
    site: "LYON",
    vols: [
      {
        vol_id: "IT105",
        site_depart: "LYON",
        site_arrivee: "PARIS",
        heure_depart: "06:00",
        heure_arrivee: "07:00",
        pilote: {
          id: 7,
          nom: "FARMER",
          site: "LYON"
        }
      },
      {
        vol_id: "IT109",
        site_depart: "PARIS",
        site_arrivee: "LYON",
        heure_depart: "18:00",
        heure_arrivee: "19:00",
        pilote: {
          id: 7,
          nom: "FARMER",
          site: "LYON"
        }
      }
    ]
  },
  {
    _id: 108,
    constructeur: "BOEING",
    modele: "B727",
    capacite: 300,
    site: "NANTES",
    vols: []
  },
  {
    _id: 109,
    constructeur: "AIRBUS",
    modele: "A340",
    capacite: 350,
    site: "BASTIA",
    vols: [
      {
        vol_id: "IT106",
        site_depart: "BASTIA",
        site_arrivee: "PARIS",
        heure_depart: "10:00",
        heure_arrivee: "13:00",
        pilote: {
          id: 8,
          nom: "NAKAMURA",
          site: "BASTIA"
        }
      }
    ]
  }
])