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