Concours des Ingénieurs d'État 1er grade - Haut-Commissariat au Plan - Génie logiciel, systèmes d'information et ingénierie des données - Session du 2 mai 2021 - Épreuve de spécialité (extrait)

### Royaume du Maroc — Haut-Commissariat au Plan ## Concours de recrutement des Ingénieurs d'État 1er grade — Session : Dimanche 2 mai 2021 **Spécialité :** Génie Logiciel et systèmes d'information, Ingénierie de Développement des Services Numériques, Ingénierie des données et Informatique décisionnelle **Organisation de l'épreuve :** - Durée : ⚠️ illisible sur la photo (probablement 4 heures) - Les documents ne sont pas autorisés - Les appareils électroniques sont strictement interdits - Les copies doivent être propres et rédigées avec clarté > ⚠️ Sujet incomplet : seules la page de garde et les exercices 1 et 2 (QCM, 3 points chacun) figurent sur les photos ; les exercices suivants de l'épreuve sont manquants. --- ### Exercice 1 : QCM (3 points) *(Une seule réponse est valide.)* **1- La source des métadonnées du cube pour OLAP** a. Schéma en étoile — b. Schéma en flocon — c. Base de données standard — d. La réponse a et b **2- Quelle est l'opération OLAP qui implique le calcul de toutes les relations de données pour une ou plusieurs dimensions.** a- Dice — b- Slice — c- roll-up — d- Pivot **3- Une base de données multidimensionnelle est une base de données normalisée.** a. Vrai — b. Faux **4- Les tables de dimensions sont des tables servant d'axes d'analyse. On peut par exemple analyser les ventes suivant l'axe des temps (table de dimensions) pour indiquer par exemple pendant quel trimestre de l'année les ventes ont explosé.** a. Vrai — b. Faux **5- Soit une table de fait *DataPoints(D1,D2,D3,x)*, et les trois requêtes suivantes :** ```sql Q1: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 Q2: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH CUBE Q3: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH ROLLUP ``` Les attributs D1, D2 et D3 ont respectivement n1, n2 et n3 valeurs différentes. Supposons que toutes les combinaisons possibles de ces valeurs sont présentes dans la table 'DataPoints' au moins une fois. Le nombre de lignes dans le résultat de chacune des trois requêtes Q1, Q2 et Q3 peut être calculé par une formule utilisant n1, n2 et n3. Choisir le tuple (a, b, c, d, e, f) dans liste ci-dessous tel que, si n1=a, n2=b et n3=c, le nombre de lignes dans les requêtes Q1, Q2 et Q3 est respectivement d, e, et f. a. (2, 2, 2, 6, 18, 8) — b. (2, 2, 2, 8, 64, 15) — c. (5, 10, 10, 500, 1000, 550) — d. (4, 7, 3, 84, 160, 117) ### Exercice 2 : QCM (3 points) *(Une seule réponse est valide.)* La base de données d'une bibliothèque contient les deux tables suivantes : **Emprunt** (<u>Adherent, Livre, DateEmprunt</u>, DateRetourPrevue, DateRetourEective) **Retard** (<u>Adherent, Livre, DateEmprunt</u>, PenalitéRetard) **1- Quelles sont les Adhérents n'ayant jamais rendu de livre en retard ?** a. ```sql SELECT t.Adherent FROM Emprunt t WHERE t.Adherent NOT IN (SELECT * FROM Retard u WHERE u.Adherent = t.Adherent) ``` b. ```sql SELECT Adherent FROM Emprunt WHERE Adherent NOT IN (SELECT Adherent FROM Retard) ``` c. ```sql SELECT Adherent FROM Emprunt WHERE Adherent NOT EXIST (SELECT Adherent FROM Retard) ``` **2- Quelles sont les Adhérents ayant emprunté tous les livres (empruntés au moins une fois) ?** a. ```sql SELECT t.Adherent FROM Emprunt t WHERE NOT IN ( SELECT * FROM Emprunt u WHERE NOT EXISTS ( SELECT * FROM Emprunt v WHERE v.Adherent = t.Adherent AND v.Livre = u.Livre ) ) ``` b. ```sql SELECT t.Adherent FROM Emprunt t WHERE NOT EXISTS ( SELECT * FROM Emprunt u WHERE NOT EXISTS ( SELECT * FROM Emprunt v WHERE v.Adherent = t.Adherent AND v.Livre = u.Livre ) ) ``` c. ```sql SELECT t.Adherent FROM Emprunt t WHERE t.Adherent NOT IN ( SELECT * FROM Emprunt u WHERE NOT EXISTS ( SELECT * FROM Emprunt v WHERE v.Adherent = t.Adherent AND v.Livre = u.Livre ) ) ``` **3- Quels sont les livres ayant été empruntés par tous les adhérents ?** a. ```sql SELECT t.Livre FROM Emprunt t WHERE NOT EXISTS ( SELECT * FROM Emprunt u WHERE NOT EXISTS ( SELECT * FROM Emprunt v WHERE u.Livre = t.Livre AND v.Adherent = u.Adherent ) ) ``` b. ```sql SELECT t.Livre FROM Emprunt t WHERE EXISTS ( SELECT * FROM Emprunt u WHERE EXISTS ( SELECT * FROM Emprunt v WHERE u.Livre = t.Livre AND v.Adherent = u.Adherent ) ) ``` c. ```sql SELECT t.Livre FROM Emprunt t WHERE NOT EXISTS ( SELECT * FROM Emprunt u WHERE EXISTS ( SELECT * FROM Emprunt v WHERE u.Livre = t.Livre AND v.Adherent = u.Adherent ) ) ``` > ⚠️ Exercices 3 et suivants : pages manquantes.

> Corrigé proposé pour la révision. ### Exercice 1 — OLAP et entrepôts de données | N° | Réponse | Justification | |---|---|---| | 1 | d | Les métadonnées du cube proviennent du modèle dimensionnel de l'entrepôt : schéma en étoile ou en flocon. | | 2 | c | Le *roll-up* (consolidation) calcule les agrégats le long des hiérarchies d'une ou plusieurs dimensions ; *slice*/*dice* restreignent le cube et *pivot* le fait tourner. | | 3 | b (Faux) | Le modèle multidimensionnel est volontairement **dénormalisé** (redondance acceptée pour accélérer les requêtes analytiques). | | 4 | a (Vrai) | Les dimensions (temps, produit, région…) sont les axes d'analyse des mesures de la table de faits. | | 5 | d | Voir le calcul ci-dessous. | **Calcul de la question 5.** - Q1 (`GROUP BY` simple) : une ligne par combinaison, soit $n_1 n_2 n_3$. - Q2 (`WITH CUBE`) : tous les sous-ensembles de $\{D_1, D_2, D_3\}$ ; chaque attribut prend ses $n_i$ valeurs ou la valeur « tous » (NULL) : $(n_1+1)(n_2+1)(n_3+1)$. - Q3 (`WITH ROLLUP`) : agrégats hiérarchiques (D1,D2,D3), (D1,D2), (D1), () : $n_1 n_2 n_3 + n_1 n_2 + n_1 + 1$. | Tuple | Q1 | Q2 | Q3 | Conclusion | |---|---|---|---|---| | a. (2,2,2) | 8 | 27 | 15 | faux (6, 18, 8 proposés) | | b. (2,2,2) | 8 | 27 | 15 | faux (64 proposé pour Q2) | | c. (5,10,10) | 500 | $6 \times 11 \times 11 = 726$ | $500+50+5+1 = 556$ | faux | | d. (4,7,3) | 84 | $5 \times 8 \times 4 = 160$ | $84+28+4+1 = 117$ | **juste** | ### Exercice 2 — SQL | N° | Réponse | Justification | |---|---|---| | 1 | b | `NOT IN` compare une colonne à une sous-requête **d'une seule colonne** : en (a), `SELECT *` renvoie plusieurs colonnes (erreur) ; en (c), `NOT EXIST` n'existe pas et `NOT EXISTS` ne s'emploie pas après une colonne. ⚠️ Si `Retard.Adherent` pouvait être NULL, `NOT IN` ne renverrait aucune ligne (préférer alors `NOT EXISTS`). | | 2 | b | Division relationnelle par double négation : « il n'existe pas de livre emprunté u que l'adhérent t n'ait pas emprunté ». (a) est syntaxiquement faux (`WHERE NOT IN` sans opérande), (c) compare une colonne à une sous-requête `SELECT *` multi-colonnes. | | 3 | a | Double `NOT EXISTS` : il n'existe pas d'adhérent u pour lequel aucun emprunt v ne relie u au livre t. (b) renvoie tout livre emprunté au moins une fois ; (c) ne renvoie généralement rien. ⚠️ Dans l'énoncé, la condition `u.Livre = t.Livre` porte sur u et non sur v (`v.Livre = t.Livre` serait la formulation rigoureuse) ; (a) reste la seule réponse construite comme une division. | > ⚠️ Les exercices suivants de l'épreuve ne figurent pas sur les photos et ne sont donc pas corrigés.