Quelques bricoles avec SQLite

Que trouverez-vous ici ?

J’ai assuré pendant plusieurs années un enseignement des bases de SQL en utilisant le module SQLite Manager pour Firefox. Pour cela, j’ai créé une base des communes de France et quelques documents (avec LaTeX). Le master pour lequel j’ai préparé tout cela est désormais de l’histoire ancienne et, au cas où cela pourrait servir, je place sur ce site tout mon petit bazar.

Les présentations et leurs sources

Réalisées avec beamer sous LaTeX, les deux présentations se situent à niveau élémentaire : après avoir exposé à grands traits les principes des bases de données relationnelles, on passe à SQL dont on n’étudie qu’une partie du Data Manipulation Language.

La première présentation se consacre ensuite aux manipulations concernant une seule table et s’achève avec les tris et les fonctions statistiques.

La deuxième présentation aborde les manipulations utilisant plusieurs tables. On aborde donc les jointures. Les vues sont abordées ensuite. La présentation se termine avec l’expression CASE et le mot-clé DISTINCT.

Les sources sont fournies dans le fichier diapo_sql_sources.zip, archive du dossier diapo_sql_sources qui contient également deux fichiers .sql présentant des instructions compatibles avec SQLite dont plusieurs sont commentées. La base de données utilisée est france.sqlite disponible dans l’archive france.sqlite.zip.

Enfin, le fichier diapo_sql.pdf regroupe le contenu des deux présentations précédentes.

La base et ses sources

J’ai extrait les données de fichiers disponibles sur le site de l’INSEE. J’ai créé la base, au format SQLite, grace à un script que je fournis. Je donne également un script pour PostGreSQL et un autre pour MySQL.

Historique de la réalisation

J’ai récupéré sur le site de l’INSEE les données concernant la population légale de 2009 dans un fichier portant le nom évocateur de BTX_CC_POP_2009.zip contenant un classeur au format MS-Excel et un pdf dont le titre est Bases de chiffres clés : documentation // Évolution et structure de la population. N’ayant pas gardé l’adresse exacte du document sur le site de l’INSEE, je ne suis pas en mesure de la retrouver aujourd’hui, d’autant que, depuis, la population légale présentée est celle de 2012.

Je suis passé au format .ods pour traiter le fichier avec LibreOffice Calc. J’en ai extrait les données sous forme de fichiers .csv dont l’extension est .data.

À la recherche d’une solution pour tirer partie de cette base en cours — problème d’accessibilité, de compatibilités diverses, etc. — j’ai envisagé tour à tour MySQL et PostGreSQL pour lesquels j’ai écrit les scripts de création de la base. Sur la suggestion d’un collègue, je me suis tourné vers SQLite et le module pour Firefox qui résolvaient d’un coup tous les problèmes : pas besoin de créer une base centralisée car la base est contenu dans un unique fichier .sqlite donc pas de problème de gestion des accès, des autorisations de manipulation, etc. ; pas d’installation complexe car les salles étaient déjà équipées de Firefox et qu’un module s’installe sans trop de peine, etc.

Remarques techniques

On a accès à la région dans laquelle se trouve une commune en passant par le département et à l’arrondissement dont elle dépend en passant par le canton. L’identifiant d’une commune ne porte pas le même nom dans la table communes et dans la table population. La base ne comporte pas de NULL dans sa version 1, elle en comporte dans sa version 2 (SQLite seulement). La base ne comporte pas de champs de type date ou heure.

Base France au format sqlite

La base au format sqlite france.sqlite est disponible zippée ici. Elle contient neuf tables :

  1. dictionnaire : dictionnaire des champs apparaissant dans les autres tables ;
  2. zeat : liste des zones d'études et d'aménagement du territoire, voir la définition sur le site de l’INSEE ;
  3. regions : régions administratives avant réforme 2015 ;
  4. departements : idem pour les départements ;
  5. arrondissements : idem pour les arrondissements ;
  6. cantons : idem pour les cantons ;
  7. communes : idem pour les communes ;
  8. population : donne les différents comptes de la population par commune comme présentés dans le dictionnaire ;
  9. regadj : donne un moyen d’étudier l’adjacence des régions de France métropolitaine. Si deux régions apparaissent sur la même ligne, elles sont adjacentes, la direction donne une indication de la place de la deuxième par rapport à la première, le plan étant découpé en huit angles égaux de sommet le centre de la première région, 0 est le nord, je crois me souvenir qu’ensuite on tourne dans le sens des aiguilles d’une montre (sens indirect) mais c’est SGDG ;-).

La version n° 2, disponible zippée ici, contient des NULL au lieu d’un texte vide "" dans les champs ArtMaj et ArtMin. Le fichier rem_france2.txt donne la procédure suivie pour la transformation.

Les sources

Les sources sont données aziz autrement dit telles quelles et sans garanti aucune.

Quelques exercices avec corrections

Pas grand’ chose mais il parait que c’est mieux que rien. D’abord les devoirs de ces deux dernières années (SQL et Excel) et les corrections des questions sur SQL. Puis quelques requêtes avec, parfois, quelques commentaires.


Vous pouvez me contacter ici : le.texnicien.de.surface@yvon-henel.fr
[[2015-07-04T18:23:40+0200/]]

Valid HTML 5       CSS Valide !