TP : Ré-ingénierie d'un tableau de données

On considère les données de résultats du premier tour des élections présidentielles 2022, par bureau de vote. Les données sont présentées sous la forme d'un tableau unique, dénormalisé ; à partir de l'étude de ces données, l'objectif est de retrouver le schéma entités/associations sous-jacent.

1. Téléchargez les fichiers CSV de trois départements métropolitaine de votre choix.

2. Importez ces données, en passant par DuckDB, dans une relation "elections_brut" de votre base postgreSQL. Une manière de faire (non optimale, à améliorer) est de passer par le moteur SQL DUCKDB qui permet de manipuler efficacement divers formats de données en SQL.

Il faut une cellule :

%sql duckdb:///
%sql ATTACH 'postgresql://username:passwd@hostname/dbname' AS pg (TYPE POSTGRES);
%sql create table elections_brut as select * from 'elections_2022_loire.csv';
%sql drop table if exists pg.elections_brut;
%sql create table pg.elections_brut as select * from elections_brut;
# puis pour chacun de vos autres fichiers CSV à rajouter : 
%sql insert into pg.elections_brut select * FROM 'elections_2022_rhone.csv';

Vous pouvez alors ne plus utiliser DuckDB et switcher maintenant vers votre connexion habituelle psql :

%sql --section pg

Si vous avez l'utilitaire 'psql' installé sur votre machine vous pouvez aussi utiliser en ligne de commande la fonction \copy. Il faudra alors au préalable créer une table destinée à recevoir les données.

DROP TABLE IF EXISTS elections_brut;
CREATE TABLE elections_brut (
"Code du département"           TEXT,
"Libellé du département"        TEXT,
"Code de la circonscription"    TEXT,
"Libellé de la circonscription" TEXT,
"Code de la commune"            TEXT,
"Libellé de la Commune"         TEXT,
"Code du b.vote"                TEXT,
"Inscrits"                      TEXT,
"Abstentions"                   TEXT,
"% Abs/Ins"                     TEXT,
"Votants"                       TEXT,
"% Vot/Ins"                     TEXT,
"Blancs"                        TEXT,
"% Blancs/Ins"                  TEXT,
"% Blancs/Vot"                  TEXT,
"Nuls"                          TEXT,
"% Nuls/Ins"                    TEXT,
"% Nuls/Vot"                    TEXT,
"Exprimés"                      TEXT,
"% Exp/Ins"                     TEXT,
"% Exp/Vot"                     TEXT,
"N°Panneau"                     TEXT,
"Sexe"                          TEXT,
"Nom"                           TEXT,
"Prénom"                        TEXT,
"Voix"                          TEXT,
"% Voix/Ins"                    TEXT,
"% Voix/Exp"                    TEXT,
"Code Officiel EPCI"            TEXT,
"Nom Officiel EPCI"             TEXT,
"Code Officiel Région"          TEXT,
"Nom Officiel Région"           TEXT,
"scrutin_code"                  TEXT,
"location"                      TEXT,
"lib_du_b_vote"                 TEXT
);

3. Étudiez les données, donnez la signification de chaque attribut. Distinguez les attributs "sources" de ceux qui sont des résultats de calculs à partir d'autres attributs. (pour simplifier on ignore la colonne "scrutin_code" dans l'analyse de ces données.)
4. Recherchez l'attribut (ou l'ensemble d'attributs) qui permet de définir de façon unique les éléments suivants :

• Une région
• Un département
• Une commune
• Une circonscription
• Un bureau de votes
• un ou une candidate

Vérifiez bien chaque identifiant par une requête. Vous pouvez utiliser par exemple la requête suivante, qui permet de vérifier dans une relation si la DF $X\longrightarrow Y$ est bien valide.

select X, array_agg(distinct Y)
from r
group by X
having count(distinct Y) > 1;

On obtient une ligne pour chaque valeur de X sur laquelle existent des contre-exemples, avec la liste des valeurs de Y correspondantes. Si le résultat est vide, la DF est satisfaite.

Cette requête peut être rendue générique grâce à une fonction :

drop function if exists test_DF;
drop type if exists contre_exemple_DF;
create type contre_exemple_DF as (X text, Y text);
create or replace function test_DF(X TEXT, Y TEXT) returns setof contre_exemple_DF AS
$$
begin
return query execute 
    'select concat_ws('||quote_literal(';')||','||X||') as X, string_agg(distinct '||Y||','||quote_literal(';')||' ) as Y
    from elections_brut
    group by '||X||'
    having count(distinct ('||Y||')) > 1';
end;
$$ language PLPGSQL

Par exemple pour tester la DF "Code du b.vote","Libellé de la Commune"' --> '"location" on écrira :

select * from test_DF('"Code du b.vote","Libellé de la Commune"', '"location"')

Notez qu'il est possible de mettre un ensemble d'attributs à gauche en paramètre ; pour la partie droite, on ne peut tester qu'avec un seul attribut dans cette version mais cela n'enlève rien aux possibilité, puisque toute DF peut être décomposée / recomposée à droite.

Si le résultat est vide, alors la DF est satisfaite. Si la DF n'est pas satisfaite, alors la requête retourne un tuple pour chaque valeur de X qui possède plusieurs valeurs de Y, avec la liste de ces valeurs.

Note : une DF peut être non satisfaite car l'hypothèse n'était pas bonne ; mais parfois, une DF peut être contredite par quelques contre-exemples issus d'erreurs dans les données. Si c'est le cas, vous pourrez corriger cette erreur par des requêtes.

5. Finalement, expliquez le "prédicat" de la relation, c'est-à-dire la signification d'un tuple.
6. Dressez le schéma E/A de ces données avec mocodo. Précisez bien le type de chaque attribut.

7. Générez et exécutez le script de création de la base avec Mocodo.
8. Faites les requêtes d'importation des données brutes vers votre base de donnée normalisée.

Partie supplémentaire si vous avez terminé les étapes précédentes

9. Dans un autre schéma, importez de la même façon les données des élections présidentielles 2017 pour les mêmes départements.
10. Vous allez intégrez les données des deux élections dans une même base d'analyse. Cette base aura un schéma dit en étoile, c'est-à-dire un ensemble de faits (ici les résultats par commune) entourés de dimensions d'analyse. Ce schéma en étoile est le suivant :

11. Créez la base de données, et intégrez les données. Vous pourrez séparer les "tendances" des candidats en quatre grands blocs : gauche, centre, droite, extrême droite.
12. Calculez en SQL l'évolution des scores de chaque candidat dans un département donné ainsi que l'évolution du pourcentage de participation pour ce département. Faites la requêtes pour chacun des trois départements.