TP d’introduction aux bases de données 1

TP d’introduction aux bases de données

Le but de ce TP est d’apprendre à manipuler des bases de données. Dans le cadre du programme
d’informatique pour tous, on va se servir de bases de données déjà créées sur lesquelles on va réaliser des
requêtes SQL à l’aide du logiciel EasyPHP.

1 Introduction et installation
1.1 Définitions d’une base de données
• Une base de données est un grand ensemble de données structurées et mémorisées sur un support
permanent. Cela permet un accès direct à des données précises, plutôt que de devoir faire une
lecture et une analyse de toutes les informations contenues dans un fichier quelconque afin d’obtenir
l’information souhaitée. Les bases de données permettent aussi d’éviter la perte d’information en cas
de pannes ou d’erreurs logicielles ou matérielles.
• Les données sont stockées dans des tableaux à deux dimensions appelés tables.
• Chaque colonne a un nom appelé attribut, leur ordre n’a pas d’importance, mais ils doivent être
distincts. À chaque attribut A est associé un domaine noté Dom(A), qui représente l’ensemble des
valeurs possibles de l’attribut. Un domaine est décrit par un type (chaine de caractères, entier etc.)
auquel on peut associer des contraintes. Par exemple, le domaine de l’attribut ”couleur” défini par
{rouge, vert, bleu} est de type CHAR.
• Une ligne de la table contient des valeurs pour chacun des attributs et est appelée n-uplet ou tuple.
Ils ne sont pas ordonnés et les doublons ne sont pas pris en compte. L’ensemble des n-uplets d’une
table s’appelle une relation. Une base de données relationnelle (BDR) est formée d’un ensemble de
relations.
• Un schéma de relation R(A1 , A2 , ..., An ) est constitué d’un nom R et d’une liste d’attributs
A1 , A2 , ..., An .
Par exemple, la base de données ”Aéroport” que nous allons utiliser est composée des trois schémas de
relation suivants 1 :
• Avion(NumAV,NomAV,Capacite,Localisation)
• Pilote(NumP,NameP,Adresse,Salaire)
• Vol(NumV,NumP,NumAV,Dep T,Arr T,Dep H,Arr H)
1. le modèle entité-association qui permet de modéliser le réel sous cette forme n’est pas au programme.
TP d’introduction aux bases de données 2

où les attributs sont entre parenthèses : NumAV représente le Numéro de l’AVion etc. Cette base de
données est remplie des tuples suivants :

Avion
Pilote
NumAV NomAV Capacite Localisation
NumP NomP Adresse Salaire
101 A300 300 Marseille
2 Pierre Nice 325 000
102 A330 300 Nice
3 Pierre Paris 250 000
103 A300 300 Paris
4 Alain Marseille 250 000
104 A330 300 Marseille
5 Roger Marseille 250 000
105 A330 300 Marseille
6 Brice Nice 300 000
106 A320 300 Nice
7 Norbert Marseille 325 000
107 A320 300 Marseille
8 Roger Paris 250 000
108 A330 300 Paris
9 Roger Paris 300 000
109 A350 500 Paris

Vol
NumV NumP NumAV DEP H DEP V ARR H ARR V
1002 3 104 2 : 00 PM Marseille 2 : 20 PM Nice
1003 4 105 1 : 00 AM Paris 2 : 05 AM Nice
1004 2 101 12 : 00 AM Paris 1 : 00 PM Marseille
1005 3 101 12 : 00 AM Marseille 12 : 45 PM Lyon
1006 4 107 9 : 00 AM Nice 9 : 20 AM Marseille
1007 5 101 10 : 00 AM Paris 10 : 45 AM Lyon
1008 6 101 7 : 00 PM Lyon 7 : 45 PM Marseille
1009 7 101 4 : 00 PM Paris 5 : 00 PM Marseille
1010 2 109 8 : 00 AM Nice 9 : 00 AM Paris
On remarque que certains attributs sont soulignés ou en italique. C’est ce que l’on appelle des clés :
• une clé primaire représente l’ensemble minimal d’attributs qui permet d’identifier de façon unique
chaque tuple d’une relation. La clé est souvent limitée à un seul attribut, qui prend des valeurs
distinctes pour chaque tuple de la relation. Il peut y avoir plusieurs clés, on les appelle alors des clés
candidates et on en choisit alors une qui devient la clé primaire. Dans le cas présenté, on n’a pas le
choix, seule NumAV convient pour la relation Avion.
• une clé étrangère explicite les liens entre les tables de la base de données. Elle permet d’assurer
que les relations entre tables demeurent cohérentes. Quand une table possède une clé étrangère vers
une autre table, il n’est pas possible d’ajouter un tuple dans la première si un tuple correspondant
n’existe pas dans l’autre. C’est le cas de NumV et NumP dans la relation Vol.

1.2 Opérations sur une base de données

On cherche un langage qui puisse :
• définir les données : création et destruction d’une relation, ajout et suppression d’un attribut,
définition des contraintes (clés) ;
• les manipuler : saisie des tuples d’une relation, affichage et modification de ceux-ci mais aussi pouvoir
faire des requêtes, c’est-à-dire consulter des relations.
En théorie, on se base sur l’algèbre relationnelle, qui est un langage procédural (on indique comment
trouver le résultat), facile à programmer mais difficile pour un utilisateur.
En pratique, on utilise le langage SQL (Structured Query Language), qui est un langage déclaratif
(on indique ce que l’on veut faire). C’est plus facile et naturel pour l’utilisateur, SQL se charge ensuite de
traduire et d’optimiser la requête en utilisant l’algèbre relationnelle.
Les opérations sur une base de données prennent en entrée une ou deux relations (ensembles de tuples) et
le résultat est toujours une relation (un ensemble aussi). On parle d’opérations relationnelles (ensemblistes).
TP d’introduction aux bases de données 3

Il y a 5 opérations de base pour exprimer toutes les requêtes. Deux opérations unaires que sont la
projection et la sélection et trois opérations binaires que sont l’union, la différence et le produit
cartésien.
D’autres opérations s’expriment en fonction de ces 5 opérations de base : la jointure, l’intersection
et la division.
On ne les détaillera pas ici, on se limitera plutôt à leur utilisation au travers de la syntaxe SQL dans
la partie suivante.

1.3 Installation
1.3.1 Prérequis
On a besoin de trois choses :
• Un serveur, par exemple Apache, pour stocker la base de données.
• Un système de gestion de base de données (SGBD 2 ) qui est un logiciel système destiné à stocker
et à partager des informations dans une base de données. Cela tout en garantissant la qualité, la
pérennité et la confidentialité des informations, et en cachant la complexité des opérations. Il y en a
des commerciaux 3 comme Oracle Express, IBM DB2 ou Microsoft SQL Server mais aussi des libres
comme MySQL que nous utiliserons.
• Un logiciel d’administration de MySQL au travers d’une interface utilisateur passant par une page
web. On utilisera PhpMyAdmin.
On peut les installer séparément quelque soit le système d’exploitation et les paramétrer, mais il est
plus aisé d’utiliser un package tout fait. Sous Windows, on dispose d’EasyPhp (voir ci-dessous) ou de
WAMP (pour Windows Apache MySQL Php). Sous Linux et Mac, on trouve les équivalents LAMP et
MAMP.

1.3.2 Installation sous Windows

Le logiciel libre EasyPhp comprend le SGBD MySQL, un serveur Apache pou stocker la base de
données et l’interface d’administration PhpMyAdmin. Pour l’installer, il suffit d’aller sur le site http:
//www.easyphp.org et de télécharger la dernière version.
Une fois le logiciel installé, une petite icône en forme de e carré apparait dans la barre des tâches. En
cliquant dessus, on peut vérifier que les serveurs MySQL (et Apache) ont bien été démarrés.
Ouvrir un navigateur internet pour se rendre à l’adresse locale http://localhost/home. Sur cette
page d’accueil, repérer le module Administration MySQL : phpMyAdmin et cliquer sur open.

2. ou DBMS, pour database management system, en anglais.

3. qui sont souvent gratuits pour usage particulier ou éducatif.
TP d’introduction aux bases de données 4

On se retrouve alors sur l’interface phpMyAdmin de gestion de la base de données suivante :

On peut alors :
• Changer la langue en français.
• Aller dans l’onglet Bases de données et créer une base de données qui a pour nom : avions. Celle-ci
apparaı̂t alors dans le bandeau de gauche.
• Cliquer dessus pour la sélectionner puis aller dans l’onglet Importer.

• Dans la partie Fichier à importer, parcourir son ordinateur afin de charger la base de données déjà
remplie ”avions.sql” et cliquer sur le bouton Exécuter en bas de la page.
TP d’introduction aux bases de données 5

• On voit alors apparaı̂tre des nouvelles tables dans l’onglet de gauche, cliquer dessus pour voir ce
qu’elles contiennent.
On remarque alors, en haut, un bandeau vert détaillant ce que phpMyAdmin a fait, le temps qu’il a
mis et surtout, en dessous, la requête SQL correspondant à ce qu’il vient de faire 4 .

2 Utilisation
SQL est le langage des requêtes standard pour les SGDB relationnels. Il n’est pas sensible à la casse
(majuscule ou minuscule) mais nous indiquerons la syntaxe en majuscule pour plus de lisibilité.

2.1 Projection
La projection définit une partie des attributs (colonnes) d’une table.
Par exemple, pour connaı̂tre le nom et la localisation des avions, on projette la relation Avion en ne
gardant que les attributs NomAV et Localisation avec :

NomAV Localisation
SELECT NomAV, Localisation A300 Marseille
FROM Avion ... ...
A350 Paris

Remarques :
• On peut obtenir l’intégralité d’une table avec SELECT * FROM suivi du nom de celle-ci.
• On peut ordonner le résultat en rajoutant ORDER BY suivi de l’attribut et au besoin de DESC (des-
cendant) ou ASC (ascendant).
• On peut limiter les résultats aux tuples entre d et d + n en rajoutant LIMIT d,n.
Q1. Écrire une requête donnant la liste des pilotes par ordre alphabétique descendant avec leurs salaire.

2.2 Sélection
La sélection permet de ne choisir que certains tuples (lignes) d’une table à l’aide de la clause WHERE.
Par exemple, pour connaı̂tre tous les attributs des avions de type A330, on écrit :

NumAV NomAV Capacite Localisation

SELECT * 102 A330 300 Nice
FROM Avion 104 A330 300 Marseille
WHERE NomAV =’A330’ 105 A330 300 Marseille
108 A330 300 Paris

Remarque :
• D’autres opérateurs sont compris par SQL, comme > ou <=, par exemple.
• On peut, bien entendu, combiner sélection et projection.
Q2. Écrire une requête donnant la localisation et le numéro des avions de plus de 300 places.
4. c’est un des intérêts d’EasyPHP et cela permet de se familiariser avec la syntaxe SQL.
TP d’introduction aux bases de données 6

2.3 Union, intersection et différence

À l’aide des opérateurs AND, OR, NOT, on peut réaliser des sélections sur des unions, intersections ou
différences de domaines d’attributs, qui sont des ensembles.
Par exemple, pour connaı̂tre les heures de départ et d’arrivée de tous les vols partant de Paris et allant
à Marseille, on écrit :

SELECT DEP_H, ARR_H DEP H ARR H

FROM Vol 12 : 00 AM 1 : 00 PM
WHERE DEP_V =’Paris’ AND ARR_V = ’Marseille’ 4 : 00 PM 5 : 00 PM

Q3. Écrire une requête donnant les numéros et noms des avions localisés à Nice ou qui ont moins de 350
passagers.
Q4. Écrire une requête donnant les adresses et salaires des pilotes s’appelant Roger sauf ceux qui habitent
Marseille.

2.4 Agrégation
L’agrégation calcule un résultat sur le domaine d’un attribut. Les fonctions d’agrégation possibles sont
MAX, MIN, SUM, AVG (moyenne), COUNT.
Par exemple, pour connaı̂tre le salaire moyen d’un pilote, on écrit :

SELECT AVG(Salaire) AVG(Salaire)

FROM Pilote 281250.0000

Q5. Écrire une requête donnant le nombre d’A330.

2.5 Produit cartésien

Le produit cartésien de deux relations (tables) est composé de tuples, obtenus en formant toutes les
associations possibles de tuples de chacune des deux relations.
Par exemple, on fait le produit cartésien d’Avion et Pilote en écrivant :

NumAV NomAV Capacite Localisation NumP NomP Adresse Salaire

101 A300 300 Marseille 2 Pierre Nice 325 000
SELECT * 101 A300 300 Marseille 3 Pierre Paris 250 000
FROM Avion, Pilote ... ... ... ... ... ... ... ...
109 A350 500 Paris 8 Roger Paris 250 000
109 A350 500 Paris 9 Roger Paris 300 000

La table correspondante est imposante (elle contient 9 × 8 = 72 tuples) et possède beaucoup d’infor-
mation redondante. C’est d’ailleurs une des raisons pour laquelle on préfère séparer les tables lorsque l’on
utilise des bases de données !
Il est plus utile de limiter le produit cartésien aux données qui nous intéressent, cf ci-après.
TP d’introduction aux bases de données 7

2.6 Jointure
Lorsque l’on veut faire une sélection sur plusieurs relations à la fois, on les réunit avec une jointure. On
réalise pour cela le produit cartésien des relations puis on sélectionne les tuples qui vérifient une condition
particulière.
Par exemple, pour connaı̂tre les noms de pilotes des différents vols, on réunit les relations Vol et Pilote
grâce à la clé commune NumP, puis on sélectionne les données demandées ainsi :

NumV NomP
1004 Pierre
1010 Pierre
1002 Pierre
SELECT NumV, NomP
1005 Pierre
FROM Vol JOIN Pilote
1003 Alain
ON Vol.NumP=Pilote.NumP
1006 Alain
1007 Roger
1008 Brice
1009 Norbert

Q6. Écrire une requête donnant la localisation actuelle des avions ainsi que l’heure de départ et la ville
de laquelle ils doivent partir pour leur vol.

2.7 On mélange tout !

Q7. Écrire une requête donnant le nombre de vols dont le pilote gagne au moins 300 000 euros et habite
à Nice.
On s’intéressera aux requêtes imbriquées et aux regroupements de tuples dans le TP suivant...