TP n 19 : Bases de données (II) Travail sur plusieurs tables I

Lycée Victor Hugo
MPSI-PCSI 2016-2017
TP no 19 : Bases de données (II)
Travail sur plusieurs tables
I
Introduction
Dans ce TP nous allons effectuer des requêtes portant sur plusieurs tables.
Nous allons travailler sur la base de données world qui contiendra pour l’occasion la
table Country (déjà exploitée dans le TP précédent), une table City (comportant des
informations sur les villes du monde) et une table CountryLanguage (comportant des
informations sur les langues parlées dans un pays).
II
Chargement de la base de données
Action H
1. Télécharger le fichier CreationBaseWorld.sql à partir du site du lycée (il sera
copié dans le répertoire Téléchargements)
2. Ouvrir une console en appuyant sur CTRL+ALT+T simultanément ;
3. Changer de répertoire pour vous placer dans le répertoire Téléchargements en tapant la ligne suivante dans le terminal :
cd Téléchargements
puis appuyer sur Entrée ;
4. Charger la base de données en tapant dans le terminal fidèlement la commande
suivante, puis en appuyant sur Entrée (attention aux espaces, aux tirets !) ;
mysql -u eleves -p < CreationBaseWorld.sql
5. Saisir le mot de passe habituel pour l’informatique commune puis appuyer sur
Entrée ; Il n’apparaît pas à l’écran, ce qui est normal pour un mot de passe...
6. La base de données, tables, attributs et enregistrements est alors créée en quelques
secondes.
7. Lancez l’application MySQL Workbench en cliquant sur l’icône correspondante
(icône comportant un dauphin).
8. Cliquez sur le rectangle gris Connexion élèves.
Un mot de passe vous sera demandé : il s’agit de infocommune.
Il est possible de :
– Commenter une ligne en commençant par --, par exemple :
-- Script 2
Rappels :
1
Lycée Victor Hugo
MPSI-PCSI 2016-2017
– Commenter plusieurs lignes en commençant par /* et en terminant par */ :
/* Première ligne de commentaires,
Deuxième ligne de commentaires,
Troisième lignes de commentaires ... */
– Exécuter une seule requête SQL avec l’icône contenant un éclair et un curseur
(raccourci clavier : Ctrl + Entrée )
– Exécuter successivement la totalité des requêtes d’un script-tab avec l’icône contenant un éclair (raccourci clavier : Ctrl + Maj + Entrée ).
III
Schéma de la base de données
Script 1 H
Sélectionner la base de travail world en utilisant USE.
La base comporte trois relations/tables : City, Country et CountryLanguage.
La relation Country a déjà été vue dans le dernier TP et ne sera donc pas commentée.
1
Relation City
– La description de la table City donne :
mysql> DESCRIBE City;
+-------------+----------+------+-----+---------+----------------+
| Field
| Type
| Null | Key | Default | Extra
|
+-------------+----------+------+-----+---------+----------------+
| ID
| int(11) | NO
| PRI | NULL
| auto_increment |
| Name
| char(35) | NO
|
|
|
|
| CountryCode | char(3) | NO
|
|
|
|
| District
| char(20) | NO
|
|
|
|
| Population | int(11) | NO
|
| 0
|
|
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
– Une ville est donc décrite par :
— un identifiant nommé ID, de type entier, auto-incrémenté par le SGBD. C’est
la clé primaire de la table ;
— un nom (attribut Name), de type chaîne de caractères (de taille 35) ;
— un code pays (attribut CountryCode), sur 3 caractères ;
— un district, de type chaîne de caractères (20 au maximum)
— un nombre d’habitants (attribut Population), de type entier
– L’attribut CountryCode fait le lien avec le pays auquel appartient la ville. Dans une
vraie base de données il est probable que l’on aurait défini une clé dite étrangère
de cet attribut vers la table Country. Mais cette notion n’étant pas au programme
cela n’a pas été spécifié.
– On voit que tous les attributs doivent nécessairement avoir une valeur (c’est ce
qu’indique le NO dans la colonne Null)
– On voit également que la valeur par défaut de l’attibut Population est 0.
2
Lycée Victor Hugo
2
MPSI-PCSI 2016-2017
Relation CountryLanguage
Script 2 H
Décrire, de la même façon que pour la relation City, la relation CountryLanguage et
"deviner" la signification de chaque attribut.
Question 3 H
Quelle est la clé primaire de CountryLanguage ? De combien d’attributs est-elle constituée ?
IV
Exercices
Script 4 H
Afficher toutes les informations sur Besançon contenues dans City.
Script 5 H
Afficher le nom des villes qui ont le même nom qu’un pays. On fera attention au fait
que Country et City ont chacune un attribut Name.
Dans notre base le critère naturel de jointure est le code d’un pays : Code dans la
relation Country, CountryCode dans les relations City et CountryLanguage.
Script 6 H
Afficher les 5 villes les plus peuplées au monde, avec leur pays.
Script 7 H
Afficher les langues parlées en France métropolitaine, avec le nombre de personnes parlant chaque langue.
Script 8 H
Afficher les langues officielles de la Belgique (Belgium dans la base).
Pour faire le lien entre une ville et les langues parlées dans cette ville il faut utiliser
les trois tables, et donc deux jointures.
Script 9 H
Selon la base de données, quelles sont les langues parlées à New York ?
Script 10 H
Quelles sont les villes d’Afrique où l’on parle le français ?
3
Lycée Victor Hugo
V
1
MPSI-PCSI 2016-2017
Requêtes utilisant les opérateurs ensemblistes de
l’algèbre relationnelle
Introduction
Nous allons utiliser la table CountryLanguage pour illustrer l’utilisation des opérateurs
union, intersection et différence.
Nous allons nous intéresser à la répartition des pays dans lesquels sont parlées deux
langues ennemies : le français et l’anglais.
Script 11 H
Quels sont les codes des pays où on parle le français ?
Quels sont les codes des pays où on parle l’anglais ?
2
Union
Rappels
Pour pouvoir faire une union, il faut que les deux relations (après éventuelle projection)
aient le même schéma relationnel (même attributs).
En SQL on fait non pas l’union directement entre deux relations, mais entre les résultats de deux requêtes (obtenues par SELECT)
Syntaxe SQL pour faire une union.
SELECT ...
UNION
SELECT ...;
Script 12 H
Quels sont les codes des pays où on parle le français ou (non exclusif) l’anglais ?
3
Intersection
Rappels
Pour faire une intersection, il faut que les deux relations (après éventuelle projection)
aient le même schéma relationnel.
En SQL on fait non pas l’intersection directement entre deux relations, mais entre les
résultats de deux requêtes (obtenues par SELECT)
Syntaxe SQL pour faire une intersection :
SELECT ...
INTERSECT
SELECT ...;
Malheureusement, INTERSECT n’est pas supporté par MYSQL (vous pouvez cependant
utiliser INTERSECT dans un DS ou lors d’un concours...).
Script 13 H
Quels sont les codes des pays où on parle à la fois le français et l’anglais ? On pourra
faire une jointure de CountryLanguage avec elle-même.
4