Ce document est destiné aux étudiants de l'Université des Sciences et de la Technologie Houari Boumediene (USTHB) pour l'année académique 2014/2015. Il couvre les notions suivantes:
- Analyse du trafic aérien et évaluation des performances des compagnies aériennes.
- Modélisation conceptuelle en étoile pour un magasin de données.
- Requêtes SQL pour l'analyse des ventes et la segmentation des clients.
Examen Module SDED M2 IL -BI - business intelligence
Télécharger PDFUSTHB 2014 /2015 FEI
Département d’Informatique N. Selmoune
Examen du Module SDED
M2 IL
Exercice N°1 (9 pts):
class Class Mo...
Considérons le diagramme de classe ci-dessous représentant une partie du système d’information d’un consortium de compagnies aériennes :
- Ville
- - CodeVille: int
- - NomVille: char *
- Passager
- - CodeP: int
- Aéroport
- - NumAer: int
- Pays
- - NomP: char
- - DateNaiss: date
- - NomAer: char
- - CodeVille: char
- - CodePays: int
- - Sexe: char
- Départ
- - NomPays: char
- Arrivée
- - Date: date
- HDépart
- - HArrivée: *
- Vol
- - NumVol: int
- - TypeVol: char
- - Retard: boolean
- - Durée_théorique: int
- Compagnie
- - CodeComp: int
- - NomCom: char
- - TypeComp: char *
- Modèle
- - CodeMod: int
- - LibMod: char *
- Avion
- - NumAv: int
- Piloté par
- - Copiloté par
- Pilote
- - CodeP: int
- - NomP: char
- Constructeur
- - CodeCons: int
- - NomCons: char
Le consortium veut disposer d’un magasin de données pour analyser le trafic aérien, détecter les causes des retards, et évaluer les performances des compagnies.
1. Quelles mesures proposez-vous pour ce magasin ?
nbvol, nbpassagers, nbvolenretard, RetardMoyen
2. Quelles sont les dimensions à prendre en compte?
Temps, Avion, Pilote, Aéroport de Départ, Aéroport d’arrivée, Compagnie
3. Identifiez les hiérarchies de chaque dimension
Temps : jour->mois->année ; Avion : Avion->modèle ->Constructeur, Aéroport de Départ : Aéroport->Ville->Pays, Aéroport d’arrivée : Aéroport->Ville->Pays, Compagnie: Compagnie->Pays Compagnie :Compagnie->TypeCompagnie
4. Représentez le modèle conceptuel en étoile du magasin en précisant tous les attributs.
la dimension temporelle.
5. Nous considérons que le magasin est implémenté sous forme de tables sous oracle.
a. Donner le script de création d’une dimension (table +métadonnées), autre que
USTHB 2014 /2015 FEI Département d’Informatique N. Selmoune Create Table DMAeroport_de_Depart (NumAer Number(10) Primary Key, NomAer Varchar2(20), CodeVille Number(10), NomVille varchar2(20), CodePays Number(10),NomPays Varchar2(20)) ; Create Dimension DMAeroport_de_Depart_Dim Level L1 is (DMAeroport_de_Depart.NumAer) Level L2 is (DMAeroport_de_Depart.CodeVille) Level L3 is (DMAeroport_de_Depart.CodePays) Hierarchy h (L1 child of L2 child of L3) Attribute L1 Determines (DMAeroport_de_Depart.NomAre) Attribute L2 Determines (DMAeroport_de_Depart.NomVille) Attribute L3 Determines (DMAeroport_de_Depart.NomPays) ;
b. Proposez deux vues matérialisée susceptibles d’améliorer les temps de réponse des requêtes décisionnelles (donnez les scripts des deux vues), et justifiez votre choix.
Create Materialized view v1 Build immediate Refersh fast on demand As Select CodeVille, count(NumVol) as Nbvol From Fait, Aeroport_de_Depart Where Fait.NumAerDep=DMAeroport_de_Depart.NumAer Group by CodeVille; // Permet d’améliorer les temps de réponse pour le calcul des nombre de vol annuels grace à la commande create dimension ; Create Materialized view v2 Build immediate Refersh fast on demand As Select CodeMod, count(NumVol) as Nbvol From Fait, DMAvion Where Fait.NumAv=DMAvion.NumAv Group by CodeMod; // Permet d’améliorer les temps de réponse pour le calcul des nombre de vol par constructeur grace à la commande create dimension ;
Exercice N°2 (7 pts):
USTHB 2014 /2015 FEI Département d’Informatique N. Selmoune Considérons une table ‘ventes’ contenant l’extension ci-dessous
1. Donnez la requête SQL permettant d’obtenir le résultat ci-dessous :
select decode (grouping(numcl),1,'TotalCl',Numcl) as NumCl, decode (grouping(magasin),1,'TotalM', magasin)as magasin, sum(ca) as CA from ventes group by rollup(numcl,magasin) order by numcl, magasin;
2. Ecrire une requête qui donne le classement dense de chaque client, par rapport au chiffre d’affaires.
select numcl, Dense_Rank() over (order by(sum(ca))desc) as Classement from ventes group by numcl;
3. Donnez le résultat de la requête ci-dessous
Select NumCl, Magasin, Sum(CA) as CA, Sum(Sum(CA) ) over(partition by (magasin)) as TotalM, Ratio_to_report(Sum(CA)) over (partition by (magasin)) as Ratio From Ventes Group by NumCl, Magasin;
NUMCL MAGASIN CA TOTALM RATIO -- -- ---------- ---------- -------------------------------------------------- C1 M1 400 1180 0.34 C1 M2 760 1280 0.59 C2 M1 120 1180 0.10 C2 M2 280 1280 0.22 C3 M1 660 1180 0.56 C3 M2 240 1280 0.18
Exercice N°3 (4 pts):
Une entreprise veut segmenter ses clients en fonction de leurs chiffres d’affaires. Considérons les chiffres d’affaires suivants (pour simplifier nous considérons que l’unité est 1 million de DA): 28, 48, 61, 8, 40, 50, 26, 3, 87, 103
En appliquant la méthode des k moyennes avec k=3, et la fonction de distance =(|différence/ amplitude maximale|), donnez le résultat de la segmentation en donnant les différentes étapes.
28 48 61 8 40 50 26 3 87 103 28 0 0,2 0,33 0,2 0,12 0,22 0,02 0,25 0,59 0,75 48 0,2 0 0,13 0,4 0,08 0,02 0,22 0,45 0,39 0,55 61 0,33 0,13 0 0,53 0,21 0,11 0,35 0,58 0,26 0,42 Segment 1 2 3 1 2 2 1 1 3 3 28,00 48,00 61,00 8,00 40,00 50,00 26,00 3,00 87,00 103,00 16,25 0,12 0,32 0,45 0,08 0,24 0,34 0,10 0,13 0,71 0,87 46,00 0,18 0,02 0,15 0,38 0,06 0,04 0,20 0,43 0,41 0,57 83,67 0,56 0,36 0,23 0,76 0,44 0,34 0,58 0,81 0,03 0,19 Segment 1 2 2 1 2 2 1 1 3 3 28,00 48,00 61,00 8,00 40,00 50,00 26,00 3,00 87,00 103,00 16,25 0,12 0,32 0,45 0,08 0,24 0,34 0,10 0,13 0,71 0,87 49,75 0,22 0,02 0,11 0,42 0,10 0,00 0,24 0,47 0,37 0,53 95,00 0,67 0,47 0,34 0,87 0,55 0,45 0,69 0,92 0,08 0,08 Segment 1 2 2 1 2 2 1 1 3 3
FAQ
1. Qu'est-ce qu'un magasin de données ?
Un magasin de données est une base de données conçue pour stocker et gérer de grandes quantités de données historiques, utilisées pour l'analyse et la prise de décision.
2. Quelles sont les dimensions à prendre en compte pour un magasin de données ?
Les dimensions à prendre en compte incluent le temps, l'avion, le pilote, l'aéroport de départ, l'aéroport d'arrivée, et la compagnie.
3. Comment segmenter les clients en fonction de leurs chiffres d’affaires ?
La segmentation des clients peut être réalisée en utilisant des méthodes comme les k-moyennes, en calculant les distances entre les chiffres d’affaires et en regroupant les clients en segments.