Nom : Prénom : Page 1
Université Pierre et Marie Curie – Paris 6 Master d'informatique
BDMD – NI248
23 mars 2011
Documents autorisés - 2h
Exercice 1 : Cube 5 pts
Soit une table Ventes (Magasin, Produit, Couleur, Prix). On suppose qu’il y a 2 magasins, 4 produits et 3 couleurs,
et qu’il n’y a pas de valeurs nulles dans la table. On fait l’hypothèse que tous les magasins ont vendu chaque
produit dans chacune des couleurs. .
On crée les vues matérialisées suivantes :
Create materialized View VCube as
Select magasin, produit, couleur, sum(prix) as p
From Ventes
Group by Cube (magasin, produit, couleur);
Create materialized View VRollup as
Select magasin, produit, couleur, sum(prix) as p
From Ventes
Group by Rollup (magasin, produit, couleur) ;
Question 1. Donnez le nombre de n-uplets de chacune de ces deux vues :
Nb de n-uplets de VCube : …………………
Nb de n-uplets de VRollup : ……………….
Réponse :
VCube : 60
24 pour (m,p ,c) 8 pour (m,p), 2 pour m, 6 pour (m,c), 12 pour (p, c), 4 pour p, 3
pour c et 1 pour l’ensemble).
VRollup : 35
24 pour (m,p ,c) 8 pour (m,p), 2 pour m, et 1 pour l’ensemble).
Question 2. On considère les 7 requêtes suivantes calculant le montant total des ventes de iPod :
Q1 : select sum(prix) from Ventes where produit = ‘iPod’ ;
Q2 : select sum(p) from VCube where produit = ‘iPod’ ;
Q3 : select sum(p) from VRollup where produit = ‘iPod’ ;
Q4 : select sum(p) from VCube where produit = ‘iPod’ and magasin is null and couleur is null;
Q5 : select sum(p) from VRollup where produit = ‘iPod’ and magasin is null and couleur is null;
Q6 : select sum(p) from VCube where produit = ‘iPod’ and magasin is not null and couleur is not null;
Q7: select sum(p) from VRollup where produit = ‘iPod’ and magasin is not null and couleur is not null;
On veut regrouper ces requêtes en ‘classes d’équivalence’, c'est-à-dire partitionner les requêtes en groupes tels que
-toutes les requêtes d’un groupe sont équivalentes (donnent le même résultat sur une base satisfaisant les
Nom : Prénom : Page 2
conditions données dans l’exercice)
- il n’y ait pas de requêtes appartenant à des groupes différents qui soient équivalentes (càd : les requêtes
appartenant à des groupes différents renvoient des résultats différents).
Donnez les différents groupes, en indiquant clairement la constitution de chaque groupe
G1 : ……………………………….
……………………………………
……………………………………
…………………………………….
……………………………………
……………………………………
……………………………………
Réponse :
G1 : Q1, Q4, Q6, Q7
G2 : Q2
G3 : Q3
G4 : Q5
Exercice 2 : Requêtes décisionnelles 8 pts
La BD multi dimensionnelle d’une chaine de supermarchés a le schéma suivant. Les clés sont soulignées. Aucun
attribut n’a la valeur NULL. Des jointures naturelles peuvent être faites entre des attributs de même nom.
Vente (prod, num_date, client, promo, magasin, prix, coût, quantité)
L’attribut prix est le prix d’achat unitaire d’un exemplaire du produit.
L’attribut quantité est le nombre d’exemplaires achetés.
Prod (prod, nom, classe, marque, poids)
Classe (classe, sous_cat, catégorie, groupe, famille).
Une famille est formée de plusieurs groupes, un groupe de plusieurs catégories, une catégorie de plusieurs
sous-catégories, et une sous-catégorie est formée de plusieurs classes.
Date_vente (num_date, la_date, année, num_jour, num_semaine, num_mois, trimestre)
num_date est un entier, la_date est de type date, num_jour est dans [1, 31], num_semaine est dans [1, 52],
num_mois est dans [1, 12], trimestre est dans [1, 4].
Client (client, nom, zone, revenu, nbre_enfant)
L’attribut client est le numéro de client, zone est la zone géographique de résidence.
Magasin (magasin, nom, numéro, zone, superficie)
zone est la zone géographique du magasin.
Zone (zone, ville, agglomération, région, pays)
Un pays est composé de plusieurs régions.
Promo (promo, nom, media, coût, début, fin)
L’attribut promo est le numéro de promotion, il vaut 0 pour une vente hors promotion.
Question 1 :
a) Quelles sont les dimensions et les mesures de ce schéma ? Préciser le nom de chaque dimension. Préciser
les attributs qui représentent une mesure.
Nom : Prénom : Page 3
Dimensions : ...........................................................................
Mesures : ................................................................................
Réponse :
Dimensions :
Les dimensions avec hiérarchie Produit, Date, Client, Promo, Magasin
Les dimensions à un seul niveau :
Produit : Marque, poids
Client : revenu, nbre_enfant
Magasin : superficie
Promo : media, coût, début, fin
Mesures : prix, cout, quantité
b) On s’intéresse aux dimensions représentant la classe du produit et la date de vente. On veut décrire les
hiérarchies de ces dimensions. Pour chaque niveau d’une hiérarchie, indiquer les attributs formant le
niveau, depuis le niveau le plus détaillé, jusqu’au niveau le plus général de la hiérarchie.
Dimension sur la classe de produit Dimension sur la date de vente
Niveau 1 : Vente.prod Niveau 1 : Date_vente.la_date
Niveau 2 : Niveau 2 :
3: 3:
............ ............
............ ............
........... ...........
Réponse :
Produit
1 : Vente.prod
2 : Prod.classe
3 : Classe.sous-catégorie, catégorie, groupe
4 : Classe catégorie, groupe
5 : groupe
6 : famille
7 : ALL
Date : 1ère hiérarchie
1 : la date
2 : num_jour, num_mois, année
3 : num_mois, année
4 : num_trimestre, année
5 : année
6 : ALL
Date : 2ème hiérarchie
1 : la date
2 : num_jour, num_mois, année
3 : num_semaine, année
4 : année
5 : ALL
Nom : Prénom : Page 4
Les semaines forment une hiérarchie séparées car une semaine n’est pas toujours
incluse dans un mois (elle peut commencer dans un mois et se terminer le mois
suivant).
Remarque : les 2 hiérarchies de Date se rejoignent au niveau jour.
On a donc un schéma étoile avec une dimension possédant 2 hiérarchies.
Question 2 : Exprimer en SQL les requêtes suivantes. Suivre le modèle de réponse, si possible.
2.1) Pour le groupe ‘œufs’ de la famille ‘alim’, donner le nom et le numéro de chaque produit avec son prix
d'achat unitaire moyen. Remarque, tenir compte de la quantité achetée pour calculer le prix unitaire
moyen. Le prix d’achat moyen est différent de la moyenne du montant des achats.
select
from , ,
where and
and and
group by
--R0
select p.nom, v.prod, sum(prix*quantite)/ sum(quantite) as prix_moyen
from vente v, prod p, classe c
where p.classe = c.classe
and v.prod = p.prod
and c.famille = 'Food'
and c.groupe = 'Eggs'
group by v.prod, p.nom
order by p.nom
solution erronée :
avg(prix * quantité) as prix_moyen
c’est la moyenne du montant des achats. Ca ne correspond pas au prix prix
d’achat moyen unitaire d’un produit.
2.2) On s’intéresse aux ventes effectuées en juin 2010 par des clients ayant 4 enfants. Pour chaque marque pour
laquelle la moyenne de la quantité achetée est supérieure à 3, afficher la marque, le montant total des ventes, la
quantité moyenne et le nombre de ventes effectuées. Trier le résultat par marque, dans l’ordre alphabétique.
select
from , , ,
where and and
Nom : Prénom : Page 5
and and and
group by
having
order by
prompt R2
select marque, sum(prix*quantité) as total,
avg(quantite) as quantité_moyenne,
count(*) as nbre_ventes
from vente v, date_vente d, client c, prod p
where v.num_date = d.num_date
and v.client = c.client
and p.prod = v.prod
and d.annee = 1998
and d.num_mois = 6
and c.nbre_enfant = 4
group by p.marque
having avg(quantite) >3
order by p.marque;
2.3). Afficher le numéro et le nom des clients dont le nombre d'achats en promo est supérieur à la moyenne (i.e.
supérieur au nombre moyen d'achats en promo par client)
select
from
where and and
and
group by
having > ( select
from
where and
group by
)
--R
prompt R3
select c.client, c.nom
Nom : Prénom : Page 6
from Client c, Vente v, Promo p
where c.client = v.client
and v.promo = p.promo
and p.promo <> 0
group by c.client, c.nom
having count(*) > (
select avg(count(*))
from Vente v1, Promo p1
where v1.promo = p1.promo
and p1.promo <>0
group by v1.client)
order by c.nom
;
2.4). Quel est le classement (top 3) des pays dont les magasins ont le plus grand chiffre d'affaire (CA). Le CA
est somme des prix de vente multipliés par la quantité vendue. Afficher le pays, le rang et le CA.
with T as ( select
from
where and
group by )
select * from T
where
order by
--R
with T as (select z.pays,
rank() over (order by sum(v.prix*v.quantite) desc) as rang,
sum(v.prix * quantite)
from Vente v, Magasin m, Zone z
where v.magasin = m.magasin
and m.zone = z.zone
group by z.pays)
select * from T
where rang <=3
order by rang;
Question 3 : Les requêtes C1 et C2 représentent chacune un cube. Quelle séquence d’opérations multi
dimensionnelles faut-il appliquer à partir de C1 pour obtenir C2 ? Utiliser la notation suivante pour les opérations :
DrillDown (dimension, niveau_initial niveau_final)
Nom : Prénom : Page 7
Rollup (dimension, niveau_initial niveau_final)
avec niveau_initial et niveau_final qui sont un attribut (ou une liste d’attributs) caractérisant le niveau.
Slice (dimension, prédicat)
Dice (prédicat)
ProjectionAgrégative (dimension, ..., dimension)
C1 :
select z.pays, l.famille, l.groupe, v.num_date, sum(prix)
from vente v, client c, zone z, prod p, classe l
where v.client = c.client and p.prod = v.prod
and p.classe = l.classe and z.zone = c.zone
group by z.pays, l.famille, l.groupe, v.num_date;
C2 :
select z.pays, z.region, z.aglomeration, d.annee, d.trimestre, sum(prix)
from vente v, client c, date_vente d, zone z
where v.client = c.client and v.num_date = d.num_date
and c.zone = z.zone and c.client < 10
and prix <10 and d.annee > 2010
group by z.pays, z.region, z.aglomeration, d.annee, d.trimestre;
Descendre au niveau le plus bas sur les 3 dimensions (produit,client,date)
avant de faire le dice qui porte sur le prix et non sur la somme des prix
Drill_down ( Client, pays client )
Nom : Prénom : Page 8
Drill_down ( Produit, (famille, groupe) prod)
Dice(prix<10)
Slice(Client, client<10)
Remonter au niveau aglomeration pour les clients
Rollup (Client, client (pays,region, aglomeration) )
Date : Monter au niveau année avant de faire le slice sur l’année
Rollup (Date_vente, num_date année)
Slice(année > 2010)
Drill_down (Date_vente, année (année,trimestre))
Projection agrégative pour supprimer la dimension produit
Projection_Agregative(Client, Date_vente)
