Algèbre relationnelle / requêtes SQL

algèbre relationnelle
attribut
domaine de l’attribut
schéma relationnel
relation, table
enregistrement, valeur
Nom
Projection
Sélection
Union
Intersection
Différence
Produit cartésien
Jointure
2017
définition
n-uplet d’attributs deux à deux distincts
ensemble de n-uplets associés au schéma relationnel
un n-uplet associé au schéma relationnel
algèbre relationnelle
définition
R1 ∪ R2
R1 ∩ R2
R1 r R2
R1 × R2
R1 o
n R2
A1 =A2
Choix de certaines colonnes SELECT a1, a2 FROM table1 ;
Choix de certaines lignes
SELECT * FROM table WHERE a1 = α ;
Attention ! La sélection se fait avec WHERE tandis que la projection se fait avec SELECT
UNION
INTERSECT
EXCEPT ou MINUS
SELECT * FROM table1, table2 ;
idem σA1 =A2 (R1 × R2 )
SELECT * FROM table1 JOIN table2 ON a1=a2 ;
somme
maximum/minimum
moyenne
jointure plutôt qu’une sélection sur un produit cartésien
SELECT a1 AS b1 FROM table ;
SELECT t.a1 FROM table AS t ;
SELECT DISTINCT ... ;
SELECT COUNT(*) FROM table
SELECT a1, COUNT(*) FROM table GROUP BY a1
SELECT a1, SUM(a2) FROM table GROUP BY a3
MAX/MIN
AVG
SELECT * FROM table GROUP BY a2
HAVING SUM(a3) >= val ;
SELECT a1, a2 FROM table GROUP BY a3
ORDER BY a2 ASC (ou DESC) ;
1/5
Python 3 Bases de données et algèbre relationnelle
Même si certains SGBD savent optimiser, il est préférable de faire une
Renommage
ρA1 ←B1 (R)
L’attribut A1 s’appelle B1
Renommage d’une table
Suppression des doublons
Fonctions d’agrégation
nombre
Sélection suivant une
fonction d’agrégation
Tri des résultats
requête SQL
(le résultat est une table sur laquelle on peut faire des requêtes)
(le résultat est une relation)
π(A1 ,A2 ) (R1 )
σA1 =α (R)
équivalent SQL
nom de colonne
type des éléments de la colonne
les entêtes des colonnes
table
une ligne dans une table
PC*
2016-2017
Algèbre relationnelle / requêtes SQL
2017
PC*
Python 3 Bases de données et algèbre relationnelle
Quelques « illustrations visuelles »
Schéma relationnel / Attributs / n-uplets / Relation
schéma relationnel = liste des attributs
un enregistrement
=
une ligne
Numéro
1
2
3
4
Prénom
Jean
Marie
Jacques
Hadrien
Nom
Dupont
Malherbe
Nicolas
Piroux
Email
[email protected]
[email protected]
[email protected]
[email protected]
un attribut
= nom d’une colonne
la relation
Remarque. Les éléments d’une même colonne (= un attribut Ai ) doivent tous être du même type (entiers
ou chaînes de caractères ou ...), que l’on appelle domaine de l’attribut Ai .
Sélection
Numéro
Prénom
Toto
Nom
Email
Titi
Toto
Sélection σPrénom = ’Toto’ (R)
Rominet
Toto
Toto
Projection
Numéro
Prénom
Nom
Email
Tél
Projection πNuméro,Prénom,Nom,Tél (R)
2/5
2016-2017
2017
PC*
Python 3 Bases de données et algèbre relationnelle
Exo 1
Afin d’éviter les collisions entre avions, les altitudes de vol en croisière sont normalisées. Dans la
majorité des pays, les avions volent à une altitude multiple de 1000 pieds (un pied vaut 30,48 cm) audessus de la surface isobare à 1013,25 hPa. L’espace aérien est ainsi découpé en tranches horizontales
appelées niveaux de vol et désignées par les lettres « FL » (flight level) suivies de l’altitude en centaines
de pieds : « FL310 » désigne une altitude de croisière de 31000 pieds au-dessus de la surface isobare
de référence.
Eurocontrol est l’organisation européenne chargée de la navigation aérienne, elle gère plusieurs
dizaines de milliers de vols par jour. Toute compagnie qui souhaite faire traverser le ciel européen à un
de ses avions doit soumettre à cet organisme un plan de vol comprenant un certain nombre d’informations : trajet, heure de départ, niveau de vol souhaité, etc. Muni de ces informations, Eurocontrol
peut prévoir les secteurs aériens qui vont être surchargés et prendre des mesures en conséquence pour
les désengorger : retard au décollage, modification de la route à suivre, etc.
Nous modélisons (de manière très simplifiée) les plans de vol gérés par Eurocontrol sous la forme
d’une base de données comportant deux tables :
• la table vol qui répertorie les plans de vol déposés par les compagnies aériennes ; elle contient
les colonnes
— id_vol : numéro du vol (chaîne de caractères) ;
— depart : code de l’aéroport de départ (chaîne de caractères) ;
— arrivee : code de l’aéroport d’arrivée (chaîne de caractères) ;
— jour : jour du vol (de type date, affiché au format aaaa-mm-jj) ;
— heure : heure de décollage souhaitée (de type time, affiché au format hh:mi) ;
— niveau : niveau de vol souhaité (entier).
id_vol
AF1204
AF1205
AF1504
AF1505
depart
CDG
FCO
CDG
FCO
arrivee
FCO
CDG
FCO
CDG
jour
2016-05-02
2016-05-02
2016-05-02
2016-05-02
heure
07:35
10:25
10:05
13:00
niveau
300
300
310
310
Figure 1 – Extrait de la table vol : vols de la compagnie Air France entre les aéroports Charles-deGaulle (Paris) et Lénoard-de-Vinci à Fiumicino (Rome)
• la table aeroport qui répertorie les aéroports européens ; elle contient les colonnes :
— id_aero : code de l’aéroport (chaîne de caractères) ;
— ville : principale ville desservie (chaîne de caractères) ;
— pays : pays dans lequel se situe l’aéroport (chaîne de caractères).
id_aero
CDG
ORY
LYS
FCO
ville
Paris
Paris
Lyon
Rome
pays
France
France
France
Italie
Figure 2 – Extrait de la table aeroport
2016-2017
3/5
2017
PC*
Python 3 Bases de données et algèbre relationnelle
Les types SQL date et time permettent de mémoriser respectivement un jour du calendrier grégorien
et une heure du jour. Deux valeurs de type date ou de type time peuvent être comparées avec les
opérateurs habituels (=, <, <=, etc.). La comparaison s’effectue suivant l’ordre chronologique. Ces valeurs peuvent également être comparées à une chaîne de caractères correspondant à leur représentation
externe ('aaaa-mm-jj' ou 'hh:mi').
(1) Écrire une requête SQL qui fournit le nombre de vols qui doivent décoller dans la journée du 2
mai 2016 avant midi.
(2) Écrire une requête SQL qui fournit la liste des numéros de vols au départ d’un aéroport desservant
Paris le 2 mai 2016.
(3) Que fait la requête suivante ?
SELECT id_vol
FROM vol
JOIN aeroport AS d ON d.id_aero = depart
JOIN aeroport AS a ON a.id_aero = arrivee
WHERE
d.pays = 'France' AND
a.pays = 'France' AND
jour = '2016-05-02'
(4) Certains vols peuvent engendrer des conflits potentiels : c’est par exemple le cas lorsque deux
avions suivent un même trajet, en sens inverse, le même jour et à un même niveau. Écrire une
requête SQL qui fournit la liste des couples (id1 , id2 ) des identifiants des vols dans cette situation.
4/5
2016-2017
Pour manipuler une base de données, on utilise par exemple SQLite
Manager.
• Lancer Firefox
• Aller dans le menu Outils:Modules complémentaires (ou
Tools:Add-ons)
• Dans le champ de recherche, chercher SQLite Manager, puis l’installer.
Redémarrer Firefox pour que l’installation soit effective.
• Lancer SQLite Manager par le menu Outils (ou Tools).
Attention ! La base de données doit être stockée localement sur l’ordinateur, et pas sur un volume réseau. Pour connecter une base de
données, utiliser le menu Base de données:Connecter la base de
données (ou Database:Connect Database)
Interagir avec une base de données avec Python
On importe le module sqlite3
2017
PC*
2016-2017
Installation de SQLite Manager
import sqlite3
On se connecte à la base
db_loc = sqlite3.connect('./base.sqlite')
On crée un curseur, outil de manipulation de la base de données, qui « pointe »
dessus
cursor = db_loc.cursor()
On fait une requête SQL
resultat = cursor.execute("SELECT * FROM table WHERE cond;")
On en récupère le résultat que l’on peut alors afficher (ou en faire autre chose)
liste = resultat.fetchall()
print(liste)
On alimente la base avec une nouvelle donnée. Ici, pour une table à 3 attributs, on crée un nouvel enregistrement avec les valeurs 'foo', 'bar' et
42
cursor.execute("INSERT INTO table VALUES (?, ?, ?);", ('foo', 'bar', 42))
On « commit » pour que ça soit effectif dans la base
Dans la colonne de droite, les trois premiers boutons sont intéressants :
5/5
Structure Parcourir & rechercher Exécuter le SQL
(ou Structure Browse & Search Execute SQL )
data = [
("foo", "bar", 42),
("bla", "bla", 17),
("bla", "blabla", 19),
]
cursor.executemany("INSERT INTO table VALUES (?, ?, ?);", data)
db_loc.commit()
Python 3 Bases de données et algèbre relationnelle
Dans la colonne de gauche, rubrique Tables, sélectionner la table qui
vous intéresse. Les autres rubriques ne nous sont pas utiles.
db_loc.commit()
Pour voir l’effet sur la base, aller dans SQLite Manager, rafraîchir (ou reconnecter) la base.
On alimente la base avec plusieurs nouvelles données provenant par exemple
d’un fichier csv