Ce TP a pour but de se confronter à des problèmes que l’on peut recontrer lors de l’intégration d’un jeu de données au sein d’une base: compréhension des données et correction.
Remarque: ce TP peut s’effectuer sur la base de donnée PostgreSQL du département doc.
Ce TP sera évalué lors d’un contrôle la semaine du 30/10/2023 (à priori en début de la séance du 02/11/2023).
On considère les données concernant les subventions de l’état aux associations, disponibles sur www.data.gouv.fr. Dans le cadre de ce TP, on se concentrera dans un premier temps sur les données des subventions 2016 rendues disponibles dans le cas projet de loi de finances (PLF) 2018.
Fichier csv à télécharger: copie locale ou www.data.gouv.fr.
Le code suivant permet de créer une table postgresql afin d’y importer les données csv brutes:
CREATE TABLE s2016 (
"Programme 2016" DECIMAL NOT NULL,
"SIREN" VARCHAR NOT NULL,
"NIC" VARCHAR,
"Dénomination" VARCHAR NOT NULL,
"Montant" DECIMAL NOT NULL,
"Objet" VARCHAR NOT NULL,
"Parlementaire" VARCHAR,
"Réserve 2016" VARCHAR,
"Convention 2016" VARCHAR,
"COG : code département" VARCHAR,
"COG : code commune" DECIMAL,
"COG : ville ou pays" VARCHAR NOT NULL,
"Nomenclature juridique" DECIMAL NOT NULL,
"Code NAF" VARCHAR NOT NULL,
"Situation SIRENE" VARCHAR,
"RNA" VARCHAR
);
Le code suivant permet d’insérer les données du csv via le shell psql:
\copy s2016 from projet-de-loi-de-finances-pour-2018-plf-2018-donnees-de-lannexe-jaune-effort-fin.csv csv delimiter ';' encoding 'utf-8' header;
On pourra renommer les attributs de la table afin de simplifier l’écriture des requêtes dans la suite du TP.
Certaines données devraient être des données numériques mais les attributs correspondant ont une valeur textuelle.
Comprendre pourquoi c’est le cas et corriger les données afin de pouvoir changer le type dans un type numérique
Aide: documentation sur les expressions rationnelles
Regarder les différentes valeurs de l’attribut “Réserve 2016” et faire les modifications nécessaires pour en faire un attribut booléen.
Essayer de comprendre un peu les données, on pourra s’aider de la version excel disponible ici (version locale) comprenant des onglets sur la signification de différents codes utilisés.
Vérifier qu’il existe des lignes qui ne diffèrent que par le montant de la subvention.
Expliquer pourquoi, du point de vue métier, le montant de la subvention n’est cependant pas un attribut qui devrait faire partie de la clé.
Essayer de deviner quelles dépendances fonctionnelles devraient être satisfaites dans la table.
Vérifier la satisfaction de ces dépendances.
Pour les dépendances non satisfaites, déterminer si les cas de violation sont dus à des erreurs. Corriger ces erreurs au besoin.
Sans prendre en compte l’attribut “Parlementaire”, proposer un schéma relationnel pour représenter les données, créer les tables et y insérer les données.
Il sera peut-être nécessaire d’ajouter des identifiants artificiels, en particulier pour distinguer des subventions faites à une même association pour un même objet. Il est possible d’utiliser des séquences pour générer ces identifiants.
L’attribut parlementaire n’est pas en première forme normale (plusieurs valeurs sont dans un même attribut).
Comprendre en regardant les données le type d’information contenu dans cette colonne, modifier votre schéma relationnel et corriger les données pour y remédier.
Aide: la fonction regexp_split_to_table peut être utile ici.
Les données de subventions aux associations sont disponibles à partir de l’année 2010 (PLF 2012), les CSV sont les suivants:
2010, 2011, 2012, 2013, 2014, 2015, 2016.
En regardant ces fichiers, on peut constater que leur schéma évolue d’année en année. Si l’on souhaite analyser ces subventions sur plusieurs années, il faut intégrer ces informations dans le schéma précédent.
Reprendre le travail précédent pour l’adapter aux années 2010 à 2015 et regrouper les données en un seul schéma.