James Hoffman

Techniques de l'Informatique - Cégep de Shawinigan

Menu

Base de données 1

Réaliser les manipulations suivantes dans le fichier fournit en répondant dans la section correspondante.

  • Le fichier doit pouvoir être exécuté plusieurs fois sans erreurs
  • Portez une attention particulière à l'utilisation judicieuse des mécanismes et fonctions MariaDB

Aéroport

Créer la base de données airport et les tables représentées dans le modèle relationnel ci-dessous.

Serveur

  • Ajouter les colonnes, clés primaires et clés étrangères pour respecter les relations identifiées selon les contraintes suivantes:

    • Un pays est identifié par son code ISO 3166-1 alpha-3
    • Chaque pilote possède une numéro d'employé qui augmente à chaque embauche
    • Une route est identifiée via un code IATA et une séquence de 4 chiffres
    • Chaque vol est identifié via un numéro unique auto-incrément
    • On peut associer un pilote à un vol, en indiquant si c'était le copilote ou non.
    • De façon sporadique, les vols sont audités pour vérifier la conformités de quelques critères. Chaque critère est noté de 1 à 4: médiocre, insatisfaisant, satisfaisant, au-delà des attentes
    • Un avion est identifié par un code CAA
    • Créer une lookup table pour encapsuler les valeurs possibles de manufacturiers d'avion
    • Un entretien effectué sur un avion est identifié par un numéro unique auto-incrément

  • Respecter les règles d'intégrité suivantes

    • Un vol possède un pilote, un copilote et ceux-ci ne peuvent pas être la même personne
    • Lorsqu'un avion est supprimé ses entretiens le sont aussi
    • On peut annuler l'inspection d'un vol en la supprimant
    • Autrement, toutes les autres relations doivent être conservées

Insérer des données de départ dans chaque table en couvrant différentes relations possibles, ex: Avions avec 0 vols, 1 vol, Plusieurs vols

Récupération

Les pilotes, ayant piloté, qui ont plus de temps de vol que la moyenne

  • flight time, le temps de vol total
  • diff, indique combien de temps de plus
  • Trier par temps de vol décroissant
+-------+-------------+----------+
| pilot |*flight time*| *diff* |
+-------+-------------+----------+
| alice | hh:mm:ss | hh:mm:ss |
...
+-------+-------------+----------+

Tous les pilotes en calculant

  • flights, le nombres de vols total
  • pilot, le nombre de vols en tant que pilote
  • copilot, le nombre de vols en tant que copilote
  • Donner une valeur par défaut aux données nulles
  • Trier en ordre décroissant de flights, pilot, copilot
+---------+---------+-------+---------+
| name |*flights*|*pilot*|*copilot*|
+---------+---------+-------+---------+
| alice | 6 | 3 | 3 |
| bob | 2 | 1 | 1 |
| charlie | 0 | 0 | 0 |
...
+---------+---------+-------+---------+

Pour chaque possibilité de pays, calculer

  • Pour chaque combinaison, en origine ou destination
    • flights, le nombres de vols
    • routes, le nombre de routes
    • pilots, le nombre de pilotes dont la nationalité correspond
  • Donner une valeur par défaut aux données nulles
  • Trier en ordre d'origine croissant et flights décroissant
+--------+-------------+---------+--------+--------+
| origin | destination |*flights*|*routes*|*pilots*|
+--------+-------------+---------+--------+--------+
| can | can | 6 | 1 | 2 |
| can | usa | 4 | 2 | 3 |
| can | gbr | 0 | 0 | 1 |
| usa | usa | 2 | 2 | 0 |
| usa | can | 0 | 0 | 0 |
| usa | gbr | 0 | 0 | 0 |
| gbr | can | 0 | 0 | 0 |
| gbr | gbr | 0 | 0 | 0 |
| gbr | usa | 0 | 0 | 0 |
+--------+-------------+---------+--------+--------+

Résumé des vols

  • route, formatte Code IATA : origine -> destination
  • plane, combine le manufacturier, le modèle et le CAA
  • pilots, identifier le pilote* avec une étoile et le copilote par leurs noms
  • audit, calcule la moyenne d'une inspection et affiche le commentaire(si présent sinon N/A)
  • Trier par route et départ
+----------------------+---------------------+----------+----------+-------------------+-------------+---------------------------------------+
|*route* | departure | duration | delay |*plane* |*pilots* |*audit* |
+----------------------+---------------------+----------+----------+-------------------+-------------+---------------------------------------+
| ABC1234 : can -> can | 2020-04-15 14:10:00 | 06:40:00 | 00:00:00 | boeing 737 C-FPQR | alice, bob* | 3.6 : cold food, missing toilet paper |
| ABC1234 : can -> can | 2021-01-01 08:00:00 | 02:20:00 | 00:00:00 | airbus 380 C-FASW | alice* | 4.0 : N/A |
| ABC1234 : can -> can | 2021-01-02 08:10:00 | 02:30:00 | 00:00:00 | airbus 380 C-FASW | bob, alice* | NULL |
| ABC1234 : can -> can | 2021-02-01 09:05:00 | 02:22:00 | 04:44:00 | airbus 380 C-FASW | NULL | NULL |
| ABC1234 : can -> can | 2021-02-02 09:20:00 | 02:20:00 | 04:40:00 | airbus 380 C-FASW | NULL | NULL |
| DEF9876 : usa -> usa | 2021-01-03 13:20:00 | 05:10:00 | 01:07:00 | boeing 747 C-FIER | alice | NULL |
| DEF9876 : usa -> usa | 2021-02-03 14:22:00 | 05:20:00 | 10:40:00 | boeing 747 C-FIER | NULL | NULL |
| XYZ1289 : can -> usa | 2021-01-05 09:15:00 | 07:35:00 | 03:34:00 | airbus 380 C-FASW | NULL | NULL |
| XYZ1289 : can -> usa | 2023-11-09 09:00:00 | 07:25:00 | 00:00:00 | airbus 380 C-FASW | alice | NULL |
| XYZ1289 : can -> usa | 2023-11-11 09:10:00 | 07:30:00 | 00:00:00 | airbus 380 C-FASW | NULL | NULL |
+----------------------+---------------------+----------+----------+-------------------+-------------+---------------------------------------+

Sommaire des heures de vols par modèle d'avion de tous les manufacturier

  • Donner une valeur par défaut aux données nulles
  • Exploiter la mécanique rollup pour calculer les super-aggrégats
    • Formater les sous-totaux par manufacturer avec >
    • Indiquer le grand total en MAJUSCULES
+--------------+-------------+
|*plane* |*flight time*|
+--------------+-------------+
| airbus 380 | 32:02:00 |
| > airbus | 32:02:00 |
| boeing 737 | 06:40:00 |
| boeing 747 | 10:30:00 |
| > boeing | 17:10:00 |
| > bombardier | 00:00:00 |
| > bombardier | 00:00:00 |
| TOTAL | 49:12:00 |
+--------------+-------------+

Remise

13 novembre, 8h AM via LÉA

Envoyer uniquement le fichier .sql fournis contenant vos requêtes

  • Inscrire votre nom en commentaire

Base de données 1

Laboratoire 3 - 5%

/ 33

Nom:

Exécution répétée 0     -0.5     -1     -2
Qualité de rédaction 0     -0.5     -1     -2
Qualité de l'implémentation, arrondis 0     -0.5     -1     -2
Serveur
BD Airport 0     -1
Table pilots, colonnes, types, attributs, PK/FK 2     1.5     1     0
Table countries, colonnes, types, attributs, PK/FK 2     1.5     1     0
Table routes, colonnes, types, attributs, PK/FK 2     1.5     1     0
Table flights, colonnes, types, attributs, PK/FK 2     1.5     1     0
Table flights_pilots, colonnes, types, attributs, PK/FK 2     1.5     1     0
Table planes, colonnes, types, attributs, PK/FK 2     1.5     1     0
Table maintenances, colonnes, types, attributs, PK/FK 2     1.5     1     0
Table audits, colonnes, types, attributs, PK/FK 2     1.5     1     0
Table lookup, colonnes, types, attributs, PK/FK 2     1.5     1     0
Insertions suffisantes 0     -0.5     -1
Récupération = excellent, ajustements mineurs, satisfaisant, minimal, insatisfaisant
Pilotes, > moyenne, pilot, flight time, diff, tri temps décroissant 3     2.5     2     1     0
Pilotes/Copilotes, nuls défauts, name, flights, pilots, copilot, tri vol, pilote, copilote 3     2.5     2     1     0
Possibilités de pays, nuls défauts, origin, destination, flights, routes, pilots, tri origine, vols 3     2.5     2     1     0
Résumé des vols, route, departure, duration, delay, plane, pilots, audit, tri route, départ 3     2.5     2     1     0
Sommaire des heures de vol, nuls défauts, formattage, plane, flight time, rollup 3     2.5     2     1     0