14-bdd

IPT 2015–2016 TP n++
L’objectif de ce TP est d’apprendre à manipuler des bases de donnée et à effectuer des re-
quêtes SQL simples. On travaillera principalement avec une base de données 14-geo.db
disponible sur le serveur.
On pourra l’ouvrir en tapant dans un terminal :
sqliteman /home/users/etourniaire/Documents/TP/14-geo.db
Si vous voulez refaire ce TP chez vous (ou si vous préférez), vous pouvez utiliser l’addon
pour firefox SQLite Manager :
— Lancer Firefox
— Dans le menu Outils 1, choisir SQLite Manager
— S’il est absent, aller dans Add-ons, le chercher, l’installer, et revenir à l’étape précé-
dente.
Dans les deux cas, il est indispensable 2de garder une trace de ce que vous faites dans
cette séance. Vous devez donc ouvrir un fichier texte dans lequel vous recopierez les re-
quêtes qui auront fonctionné, avec des commentaires. Par convention, il est indiqué de
précéder vos commentaires de -- (syntaxe de commentaires dans le langage SQL).
Note : ce TP évite les questions de création / modification de base de donnée. Mais en vrai,
c’est pas sorcier.
1 Une base de donnée géographique
Une fois le logiciel lancé, vous avez deux tables dont la structure est assez explicite, néan-
moins :
—communes est une table avec des informations sur les communes de France, et pour
chaque :
—id est un identifiant unique.
—postal est le code postal.
—nom est le nom (en majuscules)
—superficie,population,altitude sont les valeurs qu’on attend, dans des
unités raisonnables.
—dep_id donne l’identifiant du numéro de département dans lequel se trouve la
commune.
—xet ysont les longitudes et latitudes d’un point au pif dans la commune (en
fait, calculé au pif comme la moyenne des coordonnées sur le contour de la
commune 3).
1. Il faut parfois appuyer sur Alt pour faire apparaitre le menu.
2. Rappel : vous devrez comprendre vos notes dans environ un an pour les concours !
3. plus facile que calculer le barycentre surfacique...
—departements est la même chose pour les départements, avec des colonnes en
moins, et chef_lieu (l’identifiant de la commune qui est chef lieu du département).
On fera attention au fait que chaque table a une clé exterieure dans l’autre.
Les valeurs indiquées entre parenthèses sont des indications sur la réponse.
ÏQuestion 1 Donner le nombre total de communes et de départements. (96 et 36613)
ÏQuestion 2 Choisir une commune (une que vous aimez bien !), trouver sa population
et sa superficie, obtenir ce résultat en une seule requête SQL. Faire calculer sa densité par
SQL.
ÏQuestion 3 Donner le nombre d’habitants dans la commune la plus peuplée.
(440 200)
ÏQuestion 4 Quel est le numéro du département correspondant au Rhône? Combien
y a-t-il de communes dans le Rhône ? (301)
ÏQuestion 5 Quelles sont les 5 communes les plus denses ? (le 20e arrondissement est
en 4e position)
ÏQuestion 6 Donner le nom des trois communes les plus peuplées. Combien y a-t-il
de communes affichant 0 habitant ? (Toulouse, Nice sont les deux premières ; 915)
ÏQuestion 7 Combien y a-t-il de noms de communes différents ? (34024)
ÏQuestion 8 Quel est le nom et l’altitude de la commune la plus élevée ? (2713)
ÏQuestion 9 Obtenir les noms des chefs-lieux de chaque département, triés par ordre
décroissant de population dans le chef-lieu. (Nantes est troisième)
ÏQuestion 10 Donner les noms des départements des 10 communes les plus larges, en
superficie. (Il n’y a que 7 départements)
ÏQuestion 11 Donner les 5 départements ayant le plus de communes. Combien en
ont-ils chacun ? (L’Aisne est deuxième, avec 816 communes)
1/2

IPT 2015–2016 TP n++
ÏQuestion 12 Donner les trois département dont les chef-lieux sont les plus élevés. (la
Lozère est deuxième)
ÏQuestion 13 Déterminer l’altitude moyenne des communes de chaque département,
triés par cette altitude. On pourra essayer de pondérer par la superficie. (La Savoie est
deuxième ou troisième, selon le cas)
ÏQuestion 14 Dans quels départements peut-on trouver une commune d’altitude su-
périeure à 1000 m ? (il y en a 30)
ÏQuestion 15 Donner la liste des 5 départements les plus denséments peuplés. (Val-
de-Marne et Val-d’Oise sont les deux derniers)
ÏQuestion 16 Comme la question 9, mais triés par ordre décroissante de population
dans le département. (Le Rhône est en 4e position)
ÏQuestion 17 Quelle est la ville qui contient la célèbre abbaye dont les coordonnées
GPS sont (46.434031, 4.659617) ? (utilisez une application de cartographie 4)
ÏQuestion 18 Donner les coordonnées du français moyen, c’est à dire le barycentre de
tous les français. À quelle altitude se situe-t-il ? Est-ce plutôt au-dessus ou en-dessous du
niveau du sol ?
2 Des hôtels
Ouvrir le fichier 14-reservations.sqlite 5dans votre logiciel de gestion de base de
donnée.
La base de donnée contient 5 tables dont voici le schéma relationnel :
—resorts (ns,noms,types,capch)
—hotels (ns, nh, nomh, adrh, telh, cath, nbch)
—rooms (ns, nh, nch, typch, prix)
—guests (nc1, nomc1, adrc1, telc1)
—bookings (ns, nh, nch, jour, nc1)
4. Ou vos connaissance de l’histoire du Moyen Âge classique...
5. Les fichiers SQLite se terminent le plus souvent par .sqlite ou .db.
Et la signification des attributs :
— Dans la première table, chaque station possède un numéro ns, un nom, un type («
mer » ou « montagne ») et une capacité capch.
— Dans hotels, chaque hôtel est identifié par le numéro de la station où se trouve l’hô-
tel et le numéro de l’hôtel. Chaque hôtel a un nom, une adresse, un numéro de télé-
phone, une catégorie (nombre d’étoiles) et un nombre de chambres.
— Dans rooms, chaque chambre est identifiée par le numéro de la station, de l’hôtel, et
un numéro de chambre dans chaque hôtel. Chaque chambre a un type (S= « simple »,
Dpour « avec douche », DWC pour « avec douche et WC » et SDB pour avec salle de bain)
et un prix.
— Dans guests, chaque client a un numéro, un nom, une adresse et un numéro de
téléphone.
— Dans bookings, chaque réservation indique la chambre (avec ns, nh, nch) réser-
vée, le jour de l’année (entier entre 1 et 365) et le client.
Des indications sur les réponses sont fournies entre parenthèses.
ÏQuestion 19 Lister les hôtels se trouvant dans une station balnéaire, triés par nombre
d’étoile décroissants (37). Lister les chambres d’hôtels à deux étoiles dans une station de
montagne avec un prix inférieur ou égal à 50 euros (153). Donner la liste des noms des
stations au bord de la mer ayant des hôtels 4 étoiles (3).
ÏQuestion 20 Donner le nombre de clients ayant réservé une chambre avec douche
dans un hôtel d’une station balnéaire (entre 500 et 600). Donner les noms des clients dont
l’adresse est celle d’un hôtel (2). Donner le nombre de clients ayant réservé à la mer et à la
montagne (entre 700 et 800).
ÏQuestion 21 Donner la liste des hôtels avec leur nom, adresse, catégorie, et le nombre
de réservations dans l’année, triés par nombre de réservations décroissant (le troisième a
900 réservations). Quel hôtel n’a aucune réservation ?
ÏQuestion 22 Il y a des informations redondantes dans cette base de donnée. Les-
quelles ? Vérifier la cohérence à l’aide d’une requête.
ÏQuestion 23 Donner la liste des noms de clients ayant réservé plusieurs chambres, le
même jour, dans le même hôtel (8). Donner la liste des hôtels 4 étoiles n’ayant que des
chambres avec salle de bain (11).
2/2