Informatique/Sujets de TP/TP11

1
Fermat, 2016/17, TP info
TP11 - Bases de données
Deux mots sur les mécanismes d'accès
• Vous avez peut-être travaillé auparavant sur un serveur distant : vous avez tous accès en lecture à une même base
de données, et vos requêtes transitent par internet (interface phpMyadmin pour une base MySQL).
• Dans ce TP, chacun de vous va travailler en local sur sa propre base de données (ça ne change pas grand chose
tant qu'on n'agit qu'en lecture...), via un logiciel de gestion très simple inclus dans le navigateur.
Les requêtes que vous envoyez seront traitées sur votre PC.
Pour ceux qui ne connaissent pas les arcanes des découpages administratifs
en France
La base que j'ai récupéré en est restée aux anciennes subdivisions ; on s'en
contentera. Au moins ont-elles un nom !
Les villes françaises sont appelées administrativement "communes".
Schématiquement, la France est découpée en
régions, qui sont découpées en départements, qui sont découpés en cantons, qui
(en fait
ce n'est pas vrai à cause des grandes villes mais
passons).
Chaque département a un chef-lieu (qui est
une commune où se trouvent les services départementaux) ; chaque région a aussi un cheflieu.
Les départements ont un numéro : par
exemple nous sommes en Haute-Garonne =
31.
Les numéros à partir de 95 désignent les territoires "outre-mer".
contiennent plusieurs communes
Manipulations préliminaires
Rendez-vous sur le site cahier-de-prepas et suivez les instructions de la page d'en-tête. Notamment
• Téléchargez la base de données (version zippée) disponible sur le site cahier-prepas, dézippez-la en villes.sqlite.
• Téléchargez l'add-on spécialisé de Firefox " SQLite Manager " ; vous pouvez aussi le télécharger ici
https ://addons.mozilla.org/fr/refox/addon/sqlite-manager/
• Installez-le. L'outil de gestion de base de données est alors disponible dans la rubrique "outils".
• Vous pouvez ouvrir le fchier villes.sqlite
• Vous pouvez commencer à découvrir le contenu de la base de données via les onglets "structure" et "parcourir et
rechercher". Mais nous utiliserons principalement l'onglet "exécuter le sql".
I.
Requêtes sur une seule table
1.
Quelques requêtes de base
a. Donner la liste alphabétique des communes de moins de 10 habitants, avec leur population (recensement de 2010).
b. Dresser la liste des 10 communes françaises les plus étendues en surface.
Fermat, 2016/17, TP info
2
c. Même question que b. en se limitant à la métropole (numéro du département <96).
2.
Agrégats
a. Combien y a-t-il de communes en Haute-Garonne (département numéro 31) ?
b. Combien y a-t-il d'habitants dans la Haute-Garonne (= dans l'ensemble des communes de Haute-Garonne) ?
c. Quelle est la densité de population de la France (métropole) ?
d. Quelles sont les communes de Haute-Garonne dont la densité est supérieure à 10 fois la densité de la France ?
II.
Requêtes avec jointure
Dans ces requêtes on achera les noms des départements et des régions en clair, pas simplement leur code.
1.
Simples jointures
a. Quelle est la liste des départements contenant une commune nommée Andilly ?
b. On cherche les 10 départements dont les chefs lieux sont les plus peuplés. On tape ceci
SELECT departement.nom,cheflieu,pop2010 FROM ville
JOIN departement ON departement.cheflieu=ville.nom
ORDER BY pop2010 DESC LIMIT 0,10
Tester.
On essaye ensuite de faire la même chose avec les 10 moins peuplés : que sut-il de faire ? Le tester ; il y a un bug :
expliquer pourquoi et critiquer la représentation de données choisie... Qu'aurait-il fallu faire ?
c. Quelles sont les villes de Haute Garonne qui ont un homonyme en France ? On demande le nom, l'identiant
(idville) de l'homonyme et son numéro de département (qu'on pourra laisser ici sous la forme numdep).
Ensuite même question avec juste la liste des communes ayant un homonyme, sans répétition.
2.
Agrégats et jointure
a. Donnez la liste des 10 départements qui contiennent le plus de communes. On donnera le nom du département
et le nombre de communes.
b. Donnez la liste des départements qui sont plus denses que la moyenne nationale.
c. Donnez la liste des départements qui contiennent plus de communes que la Haute-Garonne. On donnera juste le
numéro de département et le nombre de communes dans un premier temps, puis le vrai nom du département ensuite.
d. Quelle est la densité de population de chaque région (faire une liste alphabétique) ?
III.
1.
Exploitation graphique
Comment utiliser le module sqlite3 dans Python
• Il faut d'abord l'importer :
import sqlite3 as sql
• Puis ouvrir une connexion avec la base : par exemple
Base=sql.connect("C ://Users/Fabrice/Desktop/baselite/villes.sqlite")
• Pour eectuer une requête
req="SELECT numreg,nom FROM regions"
result=Base.execute(req)
• Voici un exemple où l'on récupère les diérents enregistrements par un for :
les attributs successifs par enreg[0], enreg[1], ...
3
Fermat, 2016/17, TP info
for enreg in result :
print("Numéro : ",enreg[0]," - ", enreg[1])
• Pour un tracé : on peut utiliser scatter pour créer des points de couleur variable en fonction d'une liste T de
valeurs
import matplotlib.pyplot as plt
plt.axis('equal') # même échelle en x et en y
plt.scatter(X,Y,c=T)
2.
Mise en ÷uvre
Pour rester en métropole on se limitera systématiquement à des numéros de département inférieurs à 95. On passera
de la longitude à la coordonnée x en multipliant par cos (ceci approche bien la projection de Mercator pour la zone
correspondant à la France).
2π
9
1.
Placer les 5000 communes les plus peuplées sur un dessin à partir de leur latitude et leur longitude.
2. Représenter l'altitude maximale de chaque canton. On représentera donc un canton par un seul point, en faisant
la moyenne de la latitude et celle de la longitude.
3. Représenter les accroissements de population 1999-2010 de chaque canton, en pourcentage. On représente là aussi
un canton par un seul point.
Remarque : pour un meilleur résultat j'ai appliqué la fonction T=np.log10(T+1.5)
IV.
Pour les rapides
Comment reprendriez-vous la conception de cette base de données ? On cherchera notamment comment gérer
- les chef-lieux
- les cantons qui font moins d'une commune, ou qui sont formés d'un bout de commune et d'autre communes
- la compatibilité et la durabilité des données lors d'un changement de découpage administratif tel que celui que la
France vient de connaître.