Nom :
Université Pierre et Marie Curie – Paris 6

Prénom : BDWA
EXAMEN - 27 MARS 2006 Documents autorisés

Page 1
Master d'informatique

Exercice 1. Requêtes décisionnelles
On considère une base de données de schéma S suivant :
CLIENTS (id_client, nom_client, id_pays) PAYS (id_pays, nom_pays, region) PRODUITS (id_prod, nom_prod, description, categorie) TEMPS(id_temps, jour, annee, mois) VENTES (id_prod, id_client, id_temps, quantité_vendue, montant_ventes)

7 pts

Question 1. Définissez une vue V1 (Pays, Produit, MeilleureAnnee) sur le schéma S, donnant, par pays et par produit, l’année où les ventes ont été les meilleures pour ce produit dans ce pays.
CREATE VIEW V1 AS SELECT DISTINCT nom_pays Pays, nom_prod Produit, annee MeilleureAnnee FROM VENTES V, TEMPS T, PRODUITS P, CLIENTS C, PAYS Pa WHERE V.id_prod= P.id_prod AND V.id_temps = T.id_temps AND V.id_client = C.id_client AND C.id_pays = Pa.id_pays AND Montant_ventes = ( SELECT MAX(montant_ventes) FROM VENTES V1, TEMPS T1, PRODUITS P1, CLIENTS C1, PAYS Pa1 WHERE V1.id_prod= P1.id_prod AND V1.id_temps = T1.id_temps AND V1.id_client = C1.id_client AND C1.id_pays = Pa1.id_pays AND Pa.id_pays = Pa1.id_pays AND P.id_prod = P1.id_prod ) ;

Question 2. Définissez une vue V2 (Pays, Produit, Annee, Ventes) sur le schéma S, donnant la somme des ventes par pays, par produit, et par année.
CREATE VIEW V2 AS SELECT DISTINCT nom_pays Pays, nom_prod Produit, annee Annee, SUM(montant_ventes) Ventes FROM VENTES V, TEMPS T, PRODUITS P, CLIENTS C, PAYS Pa WHERE V.id_prod= P.id_prod AND V.id_temps = T.id_temps AND V.id_client = C.id_client AND C.id_pays = Pa.id_pays GROUP BY nom_pays, nom_prod, annee ;

Question 3. Ecrivez une requête R1 sur la vue V2 en utilisant une fonction analytique permettant de calculer la somme des ventes des 5 dernières années (année en cours incluse) par pays, produit et année. prompt R1 somme des ventes par pays, produit et année sur les 5 dernières années

prompt solution avec