Introduction aux bases de données relationnelles

Introduction aux bases de données relationnelles
O Gense
9 mai 2014
Table des matières
1
Avant propos
2
2
Un exemple
2
3
4
2.1
2.2
2.3
2.4
2.5
Une base de données .
Des requêtes simples .
Séléction . . . . . . . .
Requêtes combinées . .
Requêtes avec jointure
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
2
3
4
4
4
Algèbre relationnelle
5
Architecture Client-serveur
7
3.1 Vocabulaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2 Algèbre relationnelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.1 Principe . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2 Inconvénients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.3 Architecture de trois tiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
5
5
7
7
7
1 Avant propos
Avant propos
Un des objectifs de l'informatique est la gestion et le traitement de l'information.
Nous avons déjà vu une organisation hiérarchique de l'information avec la structure arborescente des
chiers
Nous allons voir les notions élémentaires sur les bases de données relationnelles.
Nous utiliserons SQLITE à travers l'extension SQLite Manager de Firefox.
Nous disposerons de deux notions :
Les relations qui contiennent eectivement l'information.
Un langage de requête, le SQL
Sigle de Structured Query Language,
en français langage de requête structurée)
2 Un exemple
2.1 Une base de données
Nous allons commencer par un exemple très simple.
Nous allons modéliser le déroulement d'une semaine dans un centre de vacances.
Une première relation
La première information que nous avons sont les conditions météo
Météo :
date
1
2
3
4
5
6
ciel
soleil
soleil
nuage
pluie
pluie
nuage
vent
calme
fort
fort
calme
fort
calme
temps
chaud
tiède
tiède
froid
froid
tiède
Vocabulaire :
Un tableau comme celui là est une relation .
La première ligne est la lignes des attributs .
{ date,ciel,vent,temps} est le schéma relationnel de la relation Météo, c'est une relation d' arité 4.
Les autres lignes sont des enregistrements , leurs cases sont des champs .
Un enregistrement d'une relation R est une famille indexée par le schéma relationnel de R.
L'ensemble des valeurs possibles pour les champs s'appelle un domaine .
Nous nous intéressons aussi aux activités des membres,
Activités : Id date membre activité
0 1
Luc
sieste
1 1
Jose
planche
2 1
Marc
planche
Luc
voile
3 2
4 2
Jose
planche
5 2
Marc
planche
6 3
Luc
planche
7 3
Jose
nage
2
8
9
10
11
12
13
14
15
16
17
3
4
4
4
5
5
5
6
6
6
Marc
Luc
Jose
Marc
Luc
Jose
Marc
Luc
Jose
Marc
pêche
pêche
sieste
planche
voile
planche
planche
voile
planche
planche
Remarquez l'attribut champs Id qui prend une valeur diérente pour chaque enregistrement, on parle de
clé primaire.
Chaque table table doit posséder une telle clé primaire , dans la table précédente la date joue le rôle de
clé primaire.
L'ensemble des deux relations précédentes forment une base de données que nous appellerons Stage.
On dit que le schéma de la base de données Stage est
{Météo[date ciel vent temps] ;Activités[Id date membre activité]}
On peut représenter notre base de données par le schéma
Météo
* |date
Activités
* |Id
| ciel
|date
| vent
|membre
| temps
|activité
Les * désignent les clés primaires et les èches, les champs qui sont reliés.
À savoir faire
1. Créer une base de données vide avec SQLite manager.
2. Créer un relation (table)
3. Remplir une petite table à la main
4. Importer un chier .CSV pour remplir une table.
2.2 Des requêtes simples
Maintenant que nous avons nos tables voyons comment on peut les interroger.
Pour cela nous utiliserons le langage SQL
Des requêtes simples
Pour accéder aux enregistrement de la table Météo , on utilisera la commande
SELECT * FROM Météo
Si on ne veut que les champs vent et temps, on utilisera
SELECT vent,temp FROM Météo
Si on ne veut pas de répétition, on fera
SELECT DISTINCT vent,temp FROM Météo
Remarque :
Pour les commandes, SQL ne tient pas compte de la casse des caractères.
3
2.3 Séléction
Qui a fait une sieste ?
On peut mettre une condition pour sélectionner les enregistrements qui nous intéresse .
Par exemple pour savoir qui a fait au moins une sieste, on fera
SELECT DISTINCT membre FROM Activités
WHERE activité='sieste'
Si on veut savoir quels sont les jours où il y a eu de la pluie, on fera
SELECT date FROM Météo WHERE ciel='pluie'
À savoir faire
Faire une requête SQL
Comment déterminer le vent et la température les jours de pluie ?
Exporter le résultat d'une requête dans un chier .csv
Créer une vue avec une requête SQL.
Une vue est une table virtuelle créée par une requête, elle est utilisable dans d'autres requêtes.
2.4 Requêtes combinées
Requêtes combinées
Il est aussi possible de combiner plusieurs requêtes pour en construire une plus complexe.
Par exemple si on veut savoir qui n'a jamais de sieste, il va falloir comparer la liste des membres avec
ceux qui ont fait au moins une sieste.
SELECT DISTINCT membre FROM Activités
WHERE membre NOT IN
( SELECT membre FROM Activités WHERE activité ='sieste')
2.5 Requêtes avec jointure
Si on veut savoir : Qui a fait de la planche un jour de pluie ?
Il faut relier les deux tables ( "to join" in english )
pour cela nous indiquons comment les tables sont liées, ici il s'agit des dates qui doivent être identique
dans les deux tables.
SELECT DISTINCT activités.membre
FROM Météo JOIN activités ON Météo.date=activités.date
WHERE Météo.ciel='pluie'
AND activités.activité='planche'
Une autre question
Qui, un jour, a fait la même activité que Marc ?
Il est aussi possible de créer une table temporaire
CREATE TEMP table <nom la table > AS SELECT ....
Faisons une petite statistique
La question : pour chacune des activités , combien de fois a elle était choisie.
La commande SQL correspondante est
SELECT activité, COUNT(activité)
FROM activités GROUP BY activité
4
3 Algèbre relationnelle
3.1 Vocabulaire
Vocabulaire
Précisons notre vocabulaire.
Dénition 1 (Attribut - Domaine). Un attribut est identiant ( nom)
Un domaine est un ensemble non vide.
Il s'agit de la nature des informations à stocker, les principaux domaines que l'on peut rencontrer sont
les nombres entiers
les nombres à virgule
les dates
les chaînes de caractères
Dénition 2 (Enregistrement). Étant donnés n ∈ N∗ ,
un ensemble de n d'attributs N = {nom1 , . . . , nomn }
et n domaines D1 , . . . , Dn
on appelle Enregistrement de type {(nom1 , D1 ), . . . , (nomn , Dn )}
toute famille R indexée par N vériant , pour tout i , Rnomi noté aussi R.moni soit dans Di
nom1 , . . . , nomn sont les noms des champs de la relation R
Par exemple
Un tableau informatique de 10 cases peut être vus une relation indexée par les entiers de 0 a 9 .
Un vecteur de R3 comme une relation indexé par les clés {x, y, z} à valeurs dans le domaine Nombre réel.
Dénition 3 (Relation). Une relation R est un ensemble ni d'enregistrements du même type.
Ce type est aussi appelé schéma relationnel de R
Voici une exemple de relation de type {(nom,string),(prénom,string),(age,entier) } écrite en tableau
nom
Celui
Celui
Cet
Remarque : L'ordre des champs n'est pas
prénom nom
même table peut s'écrire : là
Celui
...
...
prénom
là
ci
Autre
age
25
15
35
signicatif , seuls comptent le nom et le type des champs, la
age
25
...
Dénition 4 (Clé primaire). Soit une relation R et c l'un des ses attributs
c peut être utilisé comme clé primaire de R si et seulement si l'application f ∈ R 7→ f.c est injective.
Dans l'exemple ci dessus prénom et age peuvent être utilisés comme clé primaire mais pas nom.
Les clés primaires permettent d'identier de façon unique une relation, cela est utile sur des grandes tables
munies d'index.
Dénition 5 (Base de données). Une base de données est un ensemble ni de relations
3.2 Algèbre relationnelle
Opérations sur les relations
Les requêtes SQL sont basées sur un certain nombre d'opérations sur les relations.
L'ensemble des ces opérations forment l'algèbre relationnelle.
On a déjà des opérations ensemblistes classiques
L'union de deux relations ayant le même schéma relation relationnel.
On l'obtient en SQL avec un commande du type
5
SELECT ... FROM ... UNION SELECT ... FROM ...
Le produit cartésien de deux relations
On l'obtient en SQL avec un commande du type
SELECT R1.C1,..,R1.CN,R2.c1,.. FROM R1,R2
En général on évite car il crée vite des résultats énormes
Autres opérations
La sélection σcond (R) = {f ∈ R |cond(f )}
La sélection permet de choisir les enregistrement suivant une condition
Exemple
R
nom
Celui
Celui
Cet
prénom
là
ci
Autre
σage>20 (R)=
nom prénom
Celui là
Cet
Autre
age
25
15
35
age
25
35
Code SQL
SELECT * FROM R WHERE age> 20
La projection : C 0 étant une partie de C la projection suivant C 0 de R est : πC 0 (R) = {(fn )n∈C 0 , |f ∈ R}
La projection sert à supprimer des attributs.
Exemple
R
nom prénom age
Celui là
25
Celui ci
15
Cet
Autre
35
πnom,prénom (R)=
nom prénom
Celui là
Celui ci
Cet
Autre
Commande SQL
SELECT nom,prenom FROM R
La jointure R et R0 étant deux relations et cond étant une relation liant les champs de R et R0 ,
pose R ./cond S = σcond (R × S)
Exemple
Pour R1 = a b et R2 = c
1 1
1
2 4
2
R1 ./R1.a=R2.c R2 = R.a R.b
1
1
2
4
d
-1
-2
R'.c R'.d
1
-1
2
-2
Cela permet d'indiquer les liens existants entres les champs des diérentes tables
Code SQL
SELECT * FROM R1 JOIN R2 ON R1.a=R2.c
6
NB : Le code LATEXpour ./ est \bowtie
La jointure est programmée de façon plus ecace que d'appliquer sa dénition.
Il aussi possible de changer les attributs d'une relation.
Exemple
SELECT a AS abscisse , b AS ordonnee FROM R
4 Architecture Client-serveur
4.1 Principe
En général, contrairement à l'exemple que l'on a vu , la base de données n'est directement sur la machine
sur laquelle on travaille.
Par exemple wikipédia est une base de données d'articles consultable par le WEB.
Architecture Client-Serveur
On parle d'architecture Client-Serveur quand deux programmes communiquent directement.
Les deux programmes peuvent être sur la même machine ou non.
On utilise le client pour faire une requête, de façon adapté à l'humain, sans avoir à connaître l'organisation des données.
Le client transmet la requête au serveur qui lui renvoie la réponse.
Le client transmet la réponse à l'utilisateur.
4.2 Inconvénients
La diculté de la méthode Client-Serveur est que les deux programmes doivent être compatibles.
En particulier il faudrait installer sur chaque machine un Client pour chacune des bases susceptibles
d'être consultées à partir cette machine.
Suivant le système d'exploitation, on n'est pas sur que le client soit développé
4.3 Architecture de trois tiers
Pour éviter ces problèmes, on utilise l'architecture des trois tiers ou plus précisément l'architecture des
trois niveaux
Premier niveau
Le premier niveau est l'interface, en général il s'agit d'un navigateur WEB.
Deuxième niveau
Le deuxième niveau est celui de l'application , elle sert d'intermédiaire entre l'interface et le serveur.
Troisième niveau
Le troisième niveau est le serveur, c'est le programme qui accède aux données.
L'avantage de l'architecture en trois niveaux est que seule l'application a besoin d'être spécialisée,
Pour l'interface , un navigateur web sut
Pour le serveur un gestionnaire standard de bases de données peut être utilisé.
7