Algèbre relationnelle / requêtes SQL

2017
PC* Python 3 Bases de données et algèbre relationnelle
Algèbre relationnelle / requêtes SQL
algèbre relationnelle dénition équivalent SQL
attribut nom de colonne
domaine de l’attribut type des éléments de la colonne
schéma relationnel n-uplet d’attributs deux à deux distincts les entêtes des colonnes
relation, table ensemble de n-uplets associés au schéma relationnel table
enregistrement, valeur un n-uplet associé au schéma relationnel une ligne dans une table
Nom algèbre relationnelle dénition requête SQL
(le résultat est une relation) (le résultat est une table sur laquelle on peut faire des requêtes)
Projection π(A1,A2)(R1)Choix de certaines colonnes SELECT a1, a2 FROM table1 ;
Sélection σA1=α(R)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 R1∪R2UNION
Intersection R1∩R2INTERSECT
Diérence R1rR2EXCEPT ou MINUS
Produit cartésien R1×R2SELECT * FROM table1, table2 ;
Jointure R1on
A1=A2
R2idem σA1=A2(R1×R2)SELECT * FROM table1 JOIN table2 ON a1=a2 ;
Même si certains SGBD savent optimiser, il est préférable de faire une jointure plutôt qu’une sélection sur un produit cartésien
Renommage ρA1←B1(R)L’attribut A1s’appelle B1SELECT a1 AS b1 FROM table ;
Renommage d’une table SELECT t.a1 FROM table AS t ;
Suppression des doublons SELECT DISTINCT ... ;
Fonctions d’agrégation nombre SELECT COUNT(*) FROM table
SELECT a1, COUNT(*) FROM table GROUP BY a1
somme SELECT a1, SUM(a2) FROM table GROUP BY a3
maximum/minimum MAX/MIN
moyenne AVG
Sélection suivant une SELECT * FROM table GROUP BY a2
fonction d’agrégation HAVING SUM(a3) >= val ;
Tri des résultats SELECT a1, a2 FROM table GROUP BY a3
ORDER BY a2 ASC (ou DESC) ;
2016-2017 1/5

2017
PC* Python 3 Bases de données et algèbre relationnelle
Quelques « illustrations visuelles »
Schéma relationnel / Attributs / n-uplets / Relation
Numéro Prénom Nom Email
1 Jean Dupont jdupon[email protected]
2 Marie Malherbe [email protected]
3 Jacques Nicolas [email protected]
4 Hadrien Piroux [email protected]
la relation
un attribut = nom d’une colonne
un enregistrement
=
une ligne
schéma relationnel = liste des attributs
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 Nom Email
Toto
Toto
Toto
Toto
Sélection σPrénom = ’Toto’ (R)
Titi
Rominet
Projection
Numéro Prénom Nom Tél
Projection πNuméro,Prénom,Nom,Tél(R)
Email
2/5 2016-2017

2017
PC* Python 3 Bases de données et algèbre relationnelle
Exo 1
An 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) au-
dessus 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 » (ight 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’informa-
tions : 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, modication de la route à suivre, etc.
Nous modélisons (de manière très simplié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, aché au format aaaa-mm-jj) ;
—heure : heure de décollage souhaitée (de type time, aché au format hh:mi) ;
—niveau : niveau de vol souhaité (entier).
id_vol depart arrivee jour heure niveau
AF1204 CDG FCO 2016-05-02 07:35 300
AF1205 FCO CDG 2016-05-02 10:25 300
AF1504 CDG FCO 2016-05-02 10:05 310
AF1505 FCO CDG 2016-05-02 13:00 310
Figure 1 – Extrait de la table vol : vols de la compagnie Air France entre les aéroports Charles-de-
Gaulle (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 ville pays
CDG Paris France
ORY Paris France
LYS Lyon France
FCO Rome 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’eectue suivant l’ordre chronologique. Ces va-
leurs 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 dON d.id_aero = depart
JOIN aeroport AS aON a.id_aero = arrivee
WHERE
d.pays = 'France' AND
a.pays = 'France' AND
jour = '2016-05-02'
(4) Certains vols peuvent engendrer des conits 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 identiants des vols dans cette situation.
4/5 2016-2017

2017
PC* Python 3 Bases de données et algèbre relationnelle
Installation de SQLite Manager
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’ins-
taller.
Redémarrer Firefox pour que l’installation soit eective.
• Lancer SQLite Manager par le menu Outils (ou Tools).
Attention ! La base de données doit être stockée localement sur l’or-
dinateur, 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)
Dans la colonne de gauche, rubrique Tables, sélectionner la table qui
vous intéresse. Les autres rubriques ne nous sont pas utiles.
Dans la colonne de droite, les trois premiers boutons sont intéressants :
Structure Parcourir & rechercher Exécuter le SQL
(ou Structure Browse & Search Execute SQL )
Interagir avec une base de données avec Python
On importe le module sqlite3
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 acher (ou en faire autre chose)
liste = resultat.fetchall()
print(liste)
On alimente la base avec une nouvelle donnée. Ici, pour une table à 3 at-
tributs, 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 eectif dans la base
db_loc.commit()
Pour voir l’eet sur la base, aller dans SQLite Manager, rafraîchir (ou recon-
necter) la base.
On alimente la base avec plusieurs nouvelles données provenant par exemple
d’un chier csv
data = [
("foo","bar", 42),
("bla","bla", 17),
("bla","blabla", 19),
]
cursor.executemany("INSERT INTO table VALUES (?, ?, ?);", data)
db_loc.commit()
2016-2017 5/5