version imprimable

Bases de données
(1/4)
Stéphane Gonnord
Bases de données (1/4)
Problématique
Des exemples
Un cahier des charges
Introduction
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Stéphane Gonnord
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
[email protected]
www.mp933.fr
Lycée du parc - Lyon
Vendredi 11 et 18 avril 2014
Lycée du parc
Algèbre relationnelle
Plan
Bases de données
(1/4)
Stéphane Gonnord
Problématique
Problématique
Des exemples
Un cahier des charges
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
SGBDR, SQL : WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Les villes, départements et régions
Bases de données
(1/4)
Stéphane Gonnord
I
I
Ville(Lyon, Rhône, 484344) ; Département(Rhône,
69, Rhône-Alpes) ; Région(Rhône-Alpes).
Comment relier tout cela ? Et si un département
change de nom ?
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
I
Quelques questions :
I
I
I
I
I
I
Quelles sont les villes de plus de 100000 habitants ?
Combien y a-t-il de communes en France ?
Quelle est la population vivant dans les villes
française ?
Quelles sont les villes de la Loire ?
Combien de départements/villes/habitants en région
Poitou-Charentes ?
...
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Base de notes de colles
Bases de données
(1/4)
Stéphane Gonnord
I
Colleurs et collés : nom, prénom, identifiant.
I
Comment relier tout cela ; qu’est-ce qu’une colle ?
I
Quelques questions :
I
I
I
I
Quelles sont les notes de l’élève Ducobu ? Et la
moyenne ?
Quelles ont été les colles avec une note ≤ 5 ?
Quels colleurs ont donné des notes ≥ 17 ?
Problèmes supplémentaires :
I
I
Doit pouvoir être consultée par différentes
personnes.
Doit pouvoir être modifiée par différentes personnes ;
pas les mêmes !
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Bases de données
(1/4)
Un annuaire
Stéphane Gonnord
I
I
I
I
« Ensemble » de personnes... nom, prénom,
téléphone, numéro sécu.
Problématique
Des exemples
Un cahier des charges
Comment stocker ces données (en mémoire et sur le
disque dur) ?
Quelques valeurs/lignes/enregistrements :
SGBDR, SQL :
WTF ?
Gonnord
Moynot
Garcia
...
Modèle relationnel
Stéphane
Olivier
Antonio
068655****
06********
06********
1711179****
1*****
1*****
Quelques questions :
I
I
I
Quelles sont les personnes ayant comme prénom
“Olivier” ?
Quel est le nom des personnes nées en 1971 ?
Qui a un nom commençant par un ’G’ ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Des maths un peu fumeuses
Algèbre relationnelle
Cahier des charges
Bases de données
(1/4)
Stéphane Gonnord
I
Ne pas tripatouiller soi-même des fichiers.
I
Ne pas s’occuper de la façon dont sont stockées les
informations... ni où.
I
Pouvoir collecter/fournir des informations sans
programmer. Avoir un langage de description de
requêtes.
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
I
Avoir une bonne puissance d’expressivité, gérer des
entités et des relations entre elles.
I
Autoriser des accès de différents clients.
I
Garantir une certaine pérennité des données.
Systèmes de Gestion de Base de
Données
Bases de données
(1/4)
Stéphane Gonnord
Problématique
Des exemples
I
I
Un utilisateur ; des données. Et entre les deux ?
Quelques scénarios :
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
I
I
I
I
I
I
I
Alice va cherche un livre dans sa bibliothèque.
Alice demande à Bob d’aller chercher un livre dans
sa bibliothèque.
Au téléphone !
Alice va chercher sur google les pages contenant
«Bob» et «coléoptère».
Alice fait une requête SQLite depuis sqliteman
Alice fait une requête SQL depuis Python + sqlite3
Le SGBD est toujours entre l’utilisateur et les
données !
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Bases de données
(1/4)
Architecture « client-serveur »
Stéphane Gonnord
I
Accès direct utilisateur/base de donnée.
I
Architecture légère.
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
client
cré
eu
Le langage SQL
ne
rela
Modèle relationnel
tion
Des maths un peu fumeuses
Algèbre relationnelle
effectue une recherche
fie
di
mo
client
des
eur
val
client
s
serveur de base
de données
Bases de données
(1/4)
Architecture « trois tiers »
Stéphane Gonnord
I
Tiers applicatif : intermédiaire.
I
Rôle de gendarme/filtre/traducteur.
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
client
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
client
serveur applicatif
client
serveur de base
de données
Via sqliteman
Bases de données
(1/4)
Stéphane Gonnord
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Via la librairie sqlite3
Bases de données
(1/4)
Stéphane Gonnord
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Une base de données taupinale
Bases de données
(1/4)
Stéphane Gonnord
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Villes, départements et régions
Bases de données
(1/4)
Stéphane Gonnord
I Schéma relationnel :
Problématique
communes(id integer, dep integer, nom text,
pop integer)
I Nom des villes de plus de 100000 habitants ?
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
SELECT nom FROM communes WHERE pop>=100000;
Modèle relationnel
Des maths un peu fumeuses
41, dont deux « Saint-Denis »
SELECT DISTINCT nom FROM communes WHERE pop>=100000;
I Population vivant dans des villes française ?
SELECT COUNT(pop) FROM communes;
36705... pas bézef ! !
SELECT SUM(pop) FROM communes;
64612967 : mieux !
Algèbre relationnelle
Bases de données
(1/4)
Premières jointures
Stéphane Gonnord
I Autres tables :
departements(id integer, reg integer, nom text)
regions(id integer, nom text)
I Quels sont les noms des villes de la Loire ?
SELECT communes.nom FROM
communes JOIN departements
ON communes.dep = departements.id
WHERE departements.nom = ’Loire’;
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
I Combien de départements/villes/habitants en région Poitou-Charentes ?
SELECT COUNT(*) FROM
regions JOIN departements
ON regions.id = departements.reg
WHERE regions.nom = ’Poitou-Charentes’;
SELECT COUNT(*) FROM
regions JOIN departements JOIN communes
ON regions.id = departements.reg AND communes.dep = departements.id
WHERE regions.nom = ’Poitou-Charentes’;
SELECT SUM(pop) FROM
regions JOIN departements JOIN communes
ON regions.id = departements.reg AND communes.dep = departements.id
WHERE regions.nom = ’Poitou-Charentes’;
Des maths un peu fumeuses
Algèbre relationnelle
Bases de données
(1/4)
Colleurs et collés
Stéphane Gonnord
I Schéma relationnel :
Problématique
Des exemples
colleurs(idc integer, nom tet, prenom text)
eleve(idc integer, nom tet, prenom text)
SGBDR, SQL :
WTF ?
colle(idc integer, ide integer, date text, note integer)
Un cahier des charges
Architecture
Ça ressemble à quoi ?
I Quelles sont les notes de l’élève Ducobu ? Et la moyenne ?
Le langage SQL
Modèle relationnel
SELECT note FROM
colles JOIN eleves
ON colles.ide = eleves.ide
WHERE nom=’Ducobu’;
Des maths un peu fumeuses
Algèbre relationnelle
SELECT AVG(note) from ...
I Quels colleurs ont donné des notes
≥ 17 ?
SELECT DISTINCT colleurs.nom from
colles JOIN colleurs
ON colles.idc = colleurs.idc
WHERE colles.note>=18;
Annuaire basique
Bases de données
(1/4)
Stéphane Gonnord
I Schéma relationnel :
Problématique
Des exemples
personnes(nom text, prenom text, tel text, num_secu text)
Un cahier des charges
I Quelles sont les personnes ayant comme prénom “Olivier” ?
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
SELECT * FROM personnes
WHERE prenom = ’Olivier’
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
I Quel est le nom des personnes nées en 1971 ? Plus dur,
Algèbre relationnelle
n’est-ce pas ?
nom_secu = list(curseur.execute(
"""SELECT nom, secu FROM personnes"""))
res = [nom for (nom, secu) in nom_secu if secu[1:3]=’71’]
I Qui a un nom commençant par un ’G’ ? Idem
Algèbre relationnelle
Bases de données
(1/4)
Stéphane Gonnord
I
I
Cadre mathématique pour être (faire ?) plus
sérieux/rigoureux.
Un vocabulaire spécifique de description de données
(tuples).
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
I
I
Une série d’opérateurs permettant de décrire
formellement les requêtes.
À moins que ce ne soit l’inverse :-)
Attendu du programme : faire le lien entre :
I
I
I
une requête en langage courant ;
sa traduction en algèbre relationnelle ;
sa traduction en langage SQL.
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle
Des maths fumeuses
Bases de données
(1/4)
Stéphane Gonnord
I
Un schéma relationnel :
S=((nom,texte),(prenom,texte),(classe,entier))
I
Trois attributs, avec leur domaine (type).
I
Une valeur (ligne/ un enregistrement) :
(nom:’Gonnord’, prenom:’Stéphane’, classe:841)
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
I
Une relation (table) : ensemble de valeurs
Des maths un peu fumeuses
Algèbre relationnelle
R(S) =
{
(nom:’Gonnord’, prenom:’Stéphane’, classe:842)
(nom:’Garcia’, prenom:’Antonio’, classe:841)
(nom:’Moynot’, prenom:’Olivier’, classe:842)
(nom:’Gonnord’, prenom:’Stéphane’, classe:841)
}
(conception mauvaise ; pourquoi ?)
Opérateurs de l’algèbre relationnelle
Bases de données
(1/4)
Stéphane Gonnord
I
I
Quel est le nom des département dont l’identifiant est
10 ou 11 ?
Sélection σCond selon une condition (valeur d’un
champ, non, et, ou).
Problématique
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
I
Union, intersection de deux tables. Inutile ?
Le langage SQL
Modèle relationnel
I
Projection πX d’une table selon un (des) attribut(s) X .
Des maths un peu fumeuses
Algèbre relationnelle
πNom (σId =10 ou Id =11 (departements))
I
Soit, en SQL :
SELECT nom FROM departements WHERE id in (10, 11)
Ou encore :
SELECT nom FROM departements WHERE id=10 OR id=11
Bases de données
(1/4)
C’est fini
Stéphane Gonnord
Problématique
Merci de votre attention !
Des exemples
Un cahier des charges
SGBDR, SQL :
WTF ?
Architecture
Ça ressemble à quoi ?
Le langage SQL
Modèle relationnel
Des maths un peu fumeuses
Algèbre relationnelle