Exam

Nom : Universite Pierre et Marie Curie – Paris 6 Prenom : BDWA EXAMEN – 27 MARS 2006 Documents autorises Page 1 Master d’informatique Exercice 1. Requetes decisionnelles On considere une base de donnees de schema 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, quantite_vendue, montant_ventes) pts Question 1. Definissez une vue V1 (Pays, Produit, MeilleureAnnee) sur le schema S, donnant, par pays et par produit, l’annee ou les ventes ont ete 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. d_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. Definissez une vue

Désolé, mais les essais complets ne sont disponibles que pour les utilisateurs enregistrés

Choisissez un plan d'adhésion
V2 (Pays, Produit, Annee, Ventes) sur le schema S, donnant la somme des ventes par pays, par produit, et par annee. CREATE VIEW V2 AS SELECT DISTINCT nom_pays Pays, om_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 requete R1 sur la vue V2 en utilisant une fonction analytique permettant de calculer la somme des ventes des 5 dernieres annees (annee en cours incluse) par pays, produit et annee. prompt R1 somme des ventes par pays, produit et annee sur les 5 dernieres annees rompt solution avec fenetre glissante select pays, produit, annee, sum(ventes) over (partition by pays, produit order by annee range between 4 preceding and current row) as somme5ans from V2 order by pays, produit, annee ; Nom : Prenom : Page 2 prompt solution avec requete imbriquee select pays, produit, annee, ( select sum(ventes) from v2 b where a. pays = b. pays and a. produit = b. produit and b. annee between (a. annee – 4) and a. annee ) somme5ans from V2 a group by pays, produit, annee order by pays, produit, annee ; rompt solution avec jointure select a. pays, a. produit, a. annee, sum(b. ventes) as somme5ans from V2 a, V2 b where a. pays = b. pays and a. produit = b. produit and b. annee between (a. annee – 4) and a. annee group by a. pays, a. produit, a. annee order by a. pays, a. produit, a. annee ; prompt solution erronee : la somme ne se limite pas a un produit et une annee : select pays, produit, annee, sum(ventes) over (order by annee range between 4 preceding and current row) as somme5ans from V2 order by pays, produit, annee ; Question 4.

Definissez une vue V3, donnant la somme des ventes par pays, produit, annee et l’annee ou les ventes ont ete les meilleures pour ce produit dans ce pays. CREATE VIEW V3 AS SELECT V1. Pays, v1. Produit, Annee, Ventes, MeilleureAnnee FROM V1, V2 WHERE V1. Pays = V2. Pays AND V1. Produit=V2. Produit ; Question 5. Ecrivez la requete SQL sur la vue V2, qui permet de construire un tableau contenant, pour l’Italie et pour la France, les ventes de chaque produit par annee, en respectant les regles suivantes : Les ventes de livres en 2004 sont de 1000.

En 2005, les ventes de livres sont la somme des ventes de livres en 2003 et 2004. Les ventes de disques en 2005 sont les memes qu’en 2004. Ce tableau contient des donnees extraites de la vue et des donnees calculees. Un exemple de tableau montrant un resultat de cette requete est donne ci-dessous : PAYS Italie Italie Italie France France PRODUIT Livre Livre Disque Livre Livre ANNEE 2001 2002 2002 2001 2002 VENTES 1000 5333 81500 1000 6130 Nom : France

Requete R2 Prenom : Disque 2002 89700 Page 3 SELECT SUBSTR(pays,1,20) country, SUBSTR(produit,1,15) product, annee, ventes FROM V2 WHERE pays in (‘Italie’, ‘France’) MODEL RETURN UPDATED ROWS MAIN simple_model PARTITION BY (pays) DIMENSION BY (produit, annee) MEASURES (ventes) RULES (ventes[‘Livre’, 2004] = 1000, ventes[‘Livre’, 2005] = ventes[‘Livre’, 2004] + ventes[‘Livre’, 2003], ventes[‘Disque’, 2005] = ventes[‘Disque’, 2004]) ORDER BY pays, produit, annee;

Complement de l’exercice, a faire eventuellement en TD : CREATE VIEW sales_view AS SELECT country_name country, prod_name product, calendar_year year, SUM(amount_sold) sales, COUNT(amount_sold) cnt, MAX(calendar_year) KEEP (DENSE_RANK FIRST ORDER BY SUM(amount_sold) DESC) OVER (PARTITION BY country_name, prod_name) best_year, MAX(calendar_year) KEEP (DENSE_RANK LAST ORDER BY SUM(amount_sold) DESC) OVER (PARTITION BY country_name, prod_name) worst_year FROM sales, times, customers, countries, products WHERE sales. time_id = times. time_id AND sales. rod_id = products. prod_id AND sales. cust_id =customers. cust_id AND customers. country_id=countries. country_id GROUP BY country_name, prod_name, calendar_year; Cette requete utilise les fonctions analytiques FIRST et LAST, et permet de Calculer la somme et le nombre de ventes, regroupees par pays, produit, et annee. Pour chaque produit vendu dans un pays, on aura l’annee ou les ventes ont ete meilleures pour ce produit dans ce pays. C’est l’attribut best_year. L’annee ou les ventes pour ce produit ont ete les pires est donnee dans worst_year.

Exercice 2 : OLAP : Agregation, Modele, Cube Soit S1 le schema relationnel suivant: Personne (nss, nom, age, etude) nss est le n° de securite sociale etude est le niveau d’etude d’une personne Entreprise (ne, nom, ville, activite) ne est l’identifiant de l’entreprise Emploi (nss, ne, salaire) L’employe nss a un emploi remunere dans l’entreprise ne Remarque: la cle de chaque relation est soulignee Question 1 7 pts Une personne peut occuper plusieurs emplois, pendant sa carriere, dans une ou plusieurs entreprises avec des salaires eventuellement differents.

Soit nbemp le nombre d’emplois d’une personne. Ecrire en SQL la requete R1 qui donne le salaire moyen pour chaque nombre d’emplois differents ? Par exemple, si la base de donnees contient des personnes qui ont eu 1, 2, 4 ou 5 emplois, le resultat de la requete R1 est : Nom : nbemp | salaire_moyen ——————1 2 4 5 2102 1511 2312 5312 Prenom : Page 4 Ex : 1511 est le salaire moyen de tous les emplois des personnes qui ont eu exactement 2 emplois dans leur carriere. Reponse elect nbemp, avg(salaire_moyen) as salaire_moyen from ( select count(*) as nbemp, avg(salaire) as salaire_moyen from emploi group by nss) group by nbemp Ici on a un cas particulier : la moyenne des salaires moyens pour une valeur donnee de nbemp est egale a la moyenne des salaires des personnes concernees. Par exemple pour nbemp = 2, on a ((s11 + s12)/2 + (s21 + s22°)/2)/ 2 = (s11+ s12 + s21+ s22) /4 Ceci se verifie car on effectue la moyenne des salaires moyens d’un meme nbemp.

Attention : en general la moyenne n’est pas distributive Par exemple pour nbemp = 3 et nbemp=1 ((S11+ s12 + S13)/3 + s21) /2 ? (s11 + s12 + s13 + s21) /4 Question 2 a) Ecrire en SQL une requete R2 qui donne, pour chaque niveau d’etude, la moyenne des salaires des personnes qui ont ce niveau d’etude. Le resultat de la requete a le schema : R2(etude, salaire_moyen) Reponse prompt moyenne des salaires des personnes ayant le meme niveau d’etude select etude, avg(salaire) as salaire_moyen from Emploi e, Personne p where e. nss = p. nss group by etude ; ) Le salaire moyen d’une personne est la moyenne des salaires percus pendant sa carriere. Ecrire en SQL une requete R3 qui donne, pour chaque niveau d’etude, la moyenne du salaire moyen des personnes qui ont ce niveau d’etude. Le schema de la requete R3(etude, salaire_moyen). Reponse prompt moyenne des salaires MOYENS de chaque personne regroupes par niveau d’etude: select etude, avg(salaire_moyen) as salaire_moyen from ( select etude, avg(salaire) as salaire_moyen from Emploi e, Personne p where e. nss = p. nss group by p. nss, etude Nom : ) group by etude ;

Prenom : Page 5 prompt autre solution avec un group by en moins (mais une requete imbriquee en plus) select etude, avg(salaire_moyen) as salaire_moyen from ( select etude, (select avg(salaire) from Emploi where nss = p. nss) as salaire_moyen from Personne p ) group by etude ; prompt autre solution avec un IN select etude, avg(salaire_moyen) as salaire_moyen from ( select etude, salaire_moyen from Personne p, ( select nss, avg(salaire) as salaire_moyen from Emploi group by nss) a where p. nss = a. nss ) group by etude ; c) R2 et R3 sont elles equivalentes ?

Justifier Reponse Non la moyenne n’est pas distributive Question 3 On dispose d’un outil OLAP pour analyser les salaires selon l’age et le niveau d’etude des personnes et la situation geographique des entreprises. L’analyse selon l’age peut se faire par annee ou par decade (tranches de 10 annees a partir de 14 ans et jusqu’a 73 ans). L’analyse du niveau d’etude peut se faire par le niveau d’enseignement atteint en fin d’etudes (primaire, secondaire, superieur) ou par le dernier diplome obtenu (certificat de fin d’etude primaire, BEPC, Bac, Licence, Master).

L’analyse de la situation geographique peut se faire par ville, departement, region ou pays. Tenir compte de l’existence eventuelle de villes synonymes dans plusieurs regions ou pays Quel est le schema relationnel en etoile le plus approprie pour cette analyse ? Pour chaque relation, donner son nom et ceux de ses attributs, souligner les attributs de la cle. Rmq : Nommer un attribut cle etrangere avec le meme nom que l’attribut cle correspondant. Reponse Table de fait : Salarie(age, diplome , nv, alaire) Dimensions : Nom : Prenom : Page 6 Age (age, decade) L’attribut Age. age est un nombre un nombre d’annee, l’attribut Age. decade est un n° de decade, par exemple int( (age-4) /10 ) Etude (diplome, enseignement) Ville(nv, ville, departement, region, pays) Completer le schema precedent pour pouvoir analyser les salaires – selon les tranches d’age suivantes {mineurs, jeunes de 18 a 25 ans, actifs de 26 a 65 ans, plus de 65 ans}, en plus des tranches prealablement definies. le type d’enseignement suivi : generaliste, professionnel ou mixte – l’aspect maritime du departement ou travaille la personne (avec ou sans front de mer) Le schema obtenu est il en etoile, flocon ou constellation ? Reponse Le nom du diplome ne permet pas de deduire su l’enseignement est pro ou generaliste. C’est pourquoi on remplace le nom de diplome par un attribut numero de diplome ndipl. Table de fait : Salarie(age, ndipl, nv, salaire) Chaque decade n’est pas incluse dans une seule tranche, donc les tranches ne sont pas un niveau superieur au dessus des decades.

La dimension annee a deux branches AgeDecade (annee, decade) AgeTranche(annee, tranche) Etude (ndipl, diplome, enseignement) Etude (ndipl, type) Ville (nv, ville, departement, region, pays) Ville (departement, maritime) Schema en flocon : une seule table de fait, les dimensions ont plusieurs branches Question 4 On utilise la fonction moyenne (average) pour agreger les mesures lors des operations de rollup et de projection agregative. Soit C1 le cube qui presente les salaires moyens suivant les dimensions : decade, diplome, departement.

Quelles sont, dans l’ordre, les operations a appliquer sur C1 pour obtenir les cubes suivants ? Repondre en donnant pour chaque operation, son nom suivi d’une breve description. C2 presente, pour chaque region et chaque diplome, le salaire moyen des personnes de plus de 24 ans, qui ont percu au moins une fois dans leur carriere un salaire superieur a 1000, et qui ont quitte l’universite avec une licence. C2 Nom de l’operation 1 : ……… description : …………………………… Nom de l’operation 2 : ……… description : ……………………………. Nom : 2 3 4 5 dice avec predicat sal ;1000 Prenom : Page 7 projection sur les decades : de la 2eme a la nieme decade projection agregative des decades restantes slice sur la dimension du niveau d’etude : licence Rollup : departement ? region C3 contient, pour chaque ville, le salaire moyen des personnes de 65 ans Nom de l’operation 1 : ……… description : …………………………… Nom de l’operation 2 : ……… description : ……………………………. … 1 2 3 4 drill down : decade ? age slice sur l’age = 65 drill down department ? ville projection agregative sur le niveau d’etude