Analyser un schéma inconnu : la médiathèque

Tu reçois trois tables de médiathèque sans dictionnaire préparé. Avant de calculer un indicateurVocabulaire · indicateurUne mesure qu'on suit pour piloter une activité, comme le taux de livraisons en retard. Il se choisit à partir de la décision qu'il doit déclencher et du lecteur à qui il s'adresse., tu dois déterminer ce qu'une ligne représente, comment les tables se relient et quelles dates répondent à la question. Cette fiche travaille BC01 pour l'extraction et BC02 pour l'analyse.

Refaire une requête sur des tables connues mesure la mémoire. La refaire sur des tables inconnues mesure la compétence. C'est le format de l'évaluation : un jeu de données jamais vu et une question métier. Pour cette raison, aucun corrigé n'est donné. Chaque exercice se vérifie en exécutant une seconde requête.

Toutes les requêtes de cette page tournent dans le bac à sable, bouton « Exécuter », sans rien installer.

1. Lire le schéma

Un schémaVocabulaire · schémaLa description des tables d'une base : leurs noms, leurs colonnes, et le type de chaque colonne. est un espace de noms qui regroupe des tables. Les trois tables de la médiathèque sont dans le schéma atelier. Il faut préfixer chaque nom de table : atelier.emprunts, et non emprunts.

SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'atelier'
ORDER BY table_name, ordinal_position;

Exécute-la avant de lire la suite. Deux choses à repérer :

• Les colonnes de clé étrangèreVocabulaire · clé étrangèreUne colonne qui pointe vers la clé primaire d'une autre table, et qui permet la jointure. portent un préfixe. Une clé étrangère est la colonne qui renvoie à une ligne d'une autre table. Ici elle s'appelle emprunts.emprunt_adherent_id, et non adherent_id. Olist a la même particularité : l'énoncé annonce orders.customer_id et le schéma dit order_customer_id. Un nom de colonne se lit dans le schéma, il ne se devine pas.
• emprunts a trois colonnes de date : la sortie de l'ouvrage, la date de retour prévue, la date de retour réelle. La troisième n'est pas toujours remplie.

2. Compter les lignes et compter les valeurs

SELECT COUNT(*) FROM atelier.emprunts;

Note le résultat. Lance maintenant celle-ci :

SELECT COUNT(date_retour_reel) FROM atelier.emprunts;

Avant de l'exécuter : tu attends le même nombre, ou un autre ? Si c'est un autre, plus grand ou plus petit, et de combien ?

3. Six exercices

Aucun corrigé n'est donné. Chaque exercice se vérifie en exécutant une deuxième requête, et l'énoncé dit laquelle.

1. La période couverte. Quelle est la date du premier emprunt, et celle du dernier ? Vérification : compare avec ce que donne ORDER BY emprunt_id LIMIT 1. Les deux requêtes tournent. Une seule répond à la question. Explique l'écart.

2. Les adhérents qui n'ont jamais rien emprunté. Combien sont-ils ? Vérification : compte les adhérents, puis compte les adhérents distincts présents dans emprunts, et compare avec ce que renvoie ta requête. Une jointureVocabulaire · jointureRapprocher deux tables sur une colonne commune, pour lire ensemble des informations qui sont stockées séparément. interne (JOIN sans LEFT) ne garde que les lignes qui ont une correspondance des deux côtés. Si tu en as utilisé une, elle ne peut pas trouver ces adhérents : cherche pourquoi.

3. La répartition des ouvrages par catégorie, en valeur absolue et en pourcentage, avec les ouvrages sans catégorie visibles dans le résultat. Vérification : la somme de ta colonne de pourcentages doit faire 100, et la somme de tes effectifs doit faire le nombre total d'ouvrages. Si l'une des deux ne tombe pas, tu as perdu des lignes.

4. Le taux de retard. Sur les emprunts effectivement rendus, quelle part l'a été après la date prévue ? Vérification : écris ensuite le même taux avec le nombre total d'emprunts au dénominateur. Regarde ensuite les emprunts jamais revenus : leur date de retour prévue est-elle passée ? Si oui, ce second taux les compte au dénominateur sans les compter au numérateur. Il les traite donc comme rendus à l'heure. Un troisième taux est défendable, à toi de l'écrire.

5. Les ouvrages jamais empruntés. Combien le catalogue en contient-il, et lesquels ? Vérification : compte les ouvrages, puis compte les ouvrages distincts présents dans emprunts, et vérifie que la différence tombe sur le compte que ta requête renvoie. Un ouvrage jamais emprunté n'apparaît dans aucune ligne de emprunts. Ta requête doit donc le chercher là où il est.

6. Les emprunts par adhérent. Nombre moyen, médianeVocabulaire · médianeLa valeur qui coupe la population en deux parts égales : la moitié des lignes sont en dessous, l'autre moitié au-dessus. Contrairement à la moyenne, une valeur extrême ne la déplace presque pas. (la valeur qui sépare les adhérents en deux moitiés), minimum, maximum. Vérification : ton dénominateur est le nombre d'adhérents, ou le nombre d'emprunteurs ? Calcule les deux, l'écart est réel. Compare ensuite ta moyenne à ta médiane. Dis si la moyenne est représentative ici, en le justifiant par les deux chiffres et non par une intuition.

4. Ce que ces six exercices testent

Ils reprennent un par un les pièges des autres fiches, sur un schéma où aucune requête ne peut être recopiée.

Si un exercice te bloque plus de vingt minutes, la fiche correspondante est dans le module « Les notions dont le TP a besoin ». Sélectionne le passage concerné pour poser ta question, ou utilise le formulaire de Mon espace pour un sujet général.