INFO 10 Bases de données relationnelles Introduction au modèle relationnel et requêtes élémentaires en SQL. 10 Bases de données relationnelles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Les systèmes de gestion de bases de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.1 Architecture client-serveur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.2 Architecture trois-tiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.3 Gestion et interrogation d’une base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Le modèle relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.1 Schéma relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2.2 Trouver « le bon » modèle relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Création et alimentation d’une base de données relationnelle . . . . . . . . . . . . . . . . . . . . . . . . . 3.1 Création par import de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.2 Création ex-nihilo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 L’algèbre relationnelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.1 Opérations sur les relations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.2 Groupes et fonctions d’agrégation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4.3 Exemples de requêtes sur la base de données prenoms . . . . . . . . . . . . . . . . . . . . . . . . . 4.4 Exemples de requêtes sur la base de données Biblio . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Le langage SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5.1 Traduction en SQL des opérations de l’algèbre relationnelle . . . . . . . . . . . . . . . . . . . . 5.2 Création et alimentation d’une base de données en SQL . . . . . . . . . . . . . . . . . . . . . . . 5.3 Requêtes depuis une application de gestion de SGBD . . . . . . . . . . . . . . . . . . . . . . . . . . 5.4 Requêtes depuis un interpréteur Python . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 3 3 4 4 4 5 5 7 8 10 11 11 14 14 15 17 17 20 21 21 PCSI2 \ 2014-2015 Laurent Kaczmarek ES ordinateurs ont été initialement créés pour calculer. Mais, depuis l’invention des disques durs en 1956, les ordinateurs permettent aussi de collecter, classer et stocker de grandes quantités d’informations. Le terme database (base de données) est apparu en 1964 pour désigner une collection d’informations partagées par différents utilisateurs d’un même réseau. Les premières utilisations des bases de données furent militaires. Elles sont de nos jours très largement utilisées, de l’échelle la plus petite (accès local, par les employés d’une entreprise par exemple) à la plus grande (accès global, par n’importe quel internaute par exemple). L Deux points fondamentaux découlent des objectifs pratiques des bases de données : . Le partage à grande échelle de données implique certaines précautions (pour garantir la pérennité des données par exemple). . Pour traiter un grand nombre de données, il faut également réfléchir à la meilleure manière de les structurer afin de faciliter les algorithmes de recherche. Partager des données La Bibliothèque Nationale de France posséde un catalogue impressionnant d’ouvrages enregistrés dans une base de données. Elle peut être consultée par les utilisateurs qui peuvent par exemple effectuer des recherches thématiques : quelles sont les ouvrages disponibles écrits par tel écrivain ? quelles sont les éditions disponibles ? quelle est la date d’acquisition de telle édition ? etc. Les bibliothécaires ont un droit supplémentaire : modifier les données (ajouter des données, en supprimer, les altérer). En achetant sur Amazon, un client consulte une base de donées constituée des descriptions des biens vendus, des prix, des vendeurs, des stocks et des commentaires des acheteurs. Un achat modifie automatiquement ces données (l’état du stock par exemple). Les acheteurs ont également accès aux données et peuvent modifier certaines données (prix, stocks, etc). Les données des stations méteorologiques sont enregistrées dans des bases de données puis traitées par les ingénieurs de Méteo-France. Partager des données implique des règles de précautions : . Les données doivent être facilement accessibles par un grand nombre de clients et il faut garantir une certaine pérennité des données. . Afin de protéger les données, il convient de respecter les points suivants : † Les utilisateurs ne manipulent pas directement les fichiers. † Les utilisateurs ne s’occupent pas de la façon dont sont stockées les informations, ni où elles le sont. . Les utilisateurs doivent pouvoir collecter, fournir ou modifier des données sans programmer. Ceci impose l’utilisation d’un langage de description de requêtes. Structurer des données Les structures « plates » que nous avons jusqu’à présant étudiées ont leur limite. . Considérons le problème suivant : créer un catalogue des œuvres exposées dans les musées de la RMN. . Les œuvres et les musées ont des attributs propres : nom et auteur pour les œuvres, nom et ville pour le musée. . On peut représenter le catalogue par une liste de musées contenant chacune une liste d’œuvres : cat=[["Louvre","paris",[["La Joconde","De Vinci"],["Gilles","Watteau"],...]], ["Palais des beaux-arts","Lille",[["Les vieilles","Goya"],...]],...] . Il est facile de trouver la liste des œuvres d’un musée donné au moyen d’un parcours en temps linéaire de la liste cat. LLG \ PCSI2 INFO 10 \ 2 PCSI2 \ 2014-2015 Laurent Kaczmarek . La détermination de la liste de toutes les œuvres exposées dans les musées de la RMN d’un peintre donnée est un peu plus longue à obtenir car nécessite un parcours linéaire de cat au sein duquel il faut parcourir linéaire la liste des œuvres de chacun des musées (ce que l’on peut réaliser au moyen de deux boucles for imbriquées). . La situation serait encore plus délicate dans le cas de la recherche des œuvres portant le même nom. . La manière dont est structuré cat induit une disymétrie dans le traitement des données : l’appartenance d’une œuvre à un musée est privilégiée au lien entre l’œuvre et sont auteur. . On aurait pu bien sûr structurer inversement : une liste d’auteurs contenant une liste d’œuvre avec un attribut musée. Dans ce cas, les difficultés sont inversées, il est plus difficile d’obtenir la liste des œuvres d’un musée que la liste des œuvres d’un artiste. . Les bases de données permettent de simplifier et d’améliorer l’exploration de données en les structurant de manières à ne privilégier aucun lien. Mais alors, comment structurer les données ? Depuis les années soixante, plusieurs modèles ont été crées. Ils correspondent à des manières différentes de structurer des données. 1. Les systèmes de gestion de bases de données La gestion et l’accès à une base de données sont assurés par un ensemble de programmes qui constituent un système de gestion de bases de données (SGBD). . Un SGBD doit permettre l’ajout, la modification et la recherche de données. . Un SGBD doit masquer la représentation physique des données et assurer la cohérence et la protection des données dans le contexte d’un partage des données entre un grand nombre d’utilisateurs. Exemple 10.1. Principaux SGBD. Les principaux acteurs dans le secteur des SGBD commerciaux sont Oracle Corporation (Oracle), IBM (DB2) et Mycrosoft (SQL-Server). La réponse apportée à cette double contrainte liée au partage et à la structure des données caractérise un SGBD. . Les SBGD sont conçus selon l’architecture Ansi/Sparc développée par Bachman vers 1965. Elle repose sur une division en trois niveaux : physique (là où sont stockées les données), logique (la manière dont elles sont structurées) et externe (l’interface avec l’utilisateur). . Un SGBD est caractérisé par le modèle de description des données qu’il supporte. Rapide historique des modèles : . le modèle hiérarchique, vers 1960. . le modèle relationnel créé par Edgar F.Codd, ingénieur chez IBM, en 1970. . le modèle objet-relationnel au début des années 90. Le modèle relationnel est très largement utilisé de nos jours et figure au programme. 1.1. Architecture client-serveur . Les clients accèdent directement à la base de données (ce sont le plus souvent des programmes que des opérateurs humains, cf. les sites de vente en ligne). . Les communications passent le plus souvent par l’intermédiaire d’un réseau (par exemple Internet). . L’inconvénient de cette architecture est le lien direct des utilisateurs aux données (bas niveau de sécurité). LLG \ PCSI2 INFO 10 \ 3 PCSI2 \ 2014-2015 Laurent Kaczmarek 1.2. Architecture trois-tiers La terminologie est un anglicisme, il faut comprendre architecture à trois étages (3-tier in english) : . Un client équipé d’une interface chargée de la présentation. . Un serveur d’application (appelé middleware) qui fournit la ressource, mais en faisant appel à un autre serveur. . Un serveur de données qui fournit au middleware les données requises pour répondre au client. Cette architecture renforce la sécurité des données en supprimant le lien avec le client. Le serveur applicatif a un rôle de traducteur, de gendarme et de filtre. 1.3. Gestion et interrogation d’une base de données La création, l’administration et l’interrogation d’une base de données de taille modeste peut se faire via une application. Il existe de nombreuses applications MySql, PhpMyAdmin, etc. Afin d’illustrer ce cours, nous utiliserons Sqliteman, application gratuite. Ouvrons par exemple une base de données disponible sur le site www.data.gouv.fr, la liste des prénoms donnés à Paris de 2004 à 2013. Les données sont structurées en quatre colonnes : prénom, nombre, sexe et année. L’utilisateur peut modifier et/ou interroger la base en programmant en SQL dans une des fenêtres. Dans la pratique, il est beaucoup plus facile de modifier la base de données en utilisant les différents boutons, on n’utilise le langage SQL que pour formuler des requêtes. 2. Le modèle relationnel Conformément au programme, nous nous limiterons au modèle relationnel. Nous illustrerons ce paragraphe par deux exemples : LLG \ PCSI2 INFO 10 \ 4 PCSI2 \ 2014-2015 Laurent Kaczmarek . Un exemple élémentaire, la liste des prénoms donnés à Paris entre 2004 et 2013. Les données sont authentiques et ont été trouvées sur le site www.data.gouv.fr sous la forme d’un fichier .csv qui a ensuite été transformé en une base de donnée. Elles sont structurées en quatre colonnes : P (prénom), Nb (nombre), S (sexe) et A (année). . Un exemple plus complexe, la gestion par une bibliothèque des prêts d’enregistrements sur CD d’œuvres lyriques. Dans ce cas, on se posera en plus la question de la conception d’une base de donnée (le modèle de conception par Entités/Associations n’est pas au programme, nous nous contenterons d’énoncer quelques principes généraux). 2.1. Schéma relationnel Le vocabulaire est spécifique (relation au lieu de tableau, attribut au lieu de colonne, enregistrement au lieu de ligne, etc). Définition 10.2. Relation, enregistrements, attributs, domaine, schéma relationnel Une base de données est structurée en un nombre fini de relations : . Une relation est un tableau comportant des lignes distinctes (pas de doublons) et des colonnes nommées. . Les enregistrements d’une relation sont les lignes de cette relation. . Les attributs d’une relation sont les noms donnés aux colonnes de cette relation. Si deux relations R et R0 ont un attribut en commun A, on les distinguera en les notant R.A et R0 .A. . Le domaine d’un attribut est l’ensemble des valeurs admissibles de cet attribut. . Une base de données est décrite au moyen d’un schéma relationnel, ie la donnée d’un ensemble fini de relations, chacune étant décrites par ses attributs et leurs domaines. Exemple 10.3. La relation prenom Par exemple, les données sur les prénoms déclarés à Paris entre 2004 et 2013 décrites ci-dessus se concrétisent par une base de donnnée Prepar à une seule table prenom dont on trouvera un extrait ci-dessus : P Nb S A Liz 5 F 2012 Lohan 9 M 2012 Les attributs de la relation prenom sont P, Nb, S, A. Pour la relation Prepar, les domaines des attributs P, Nb, S et A sont respectivement : l’ensemble des chaînes de caractères, l’ensemble des entiers naturels, l’ensemble des chaînes de caractères et l’ensemble des entiers naturels. Voici le schéma relationnel de prenom : Lohan 9 M 2012 est un enregistrement de la relation prenom. prenom P Chaîne de caractères Nb Entier naturel S Chaîne de caractères A Entier naturel 2.2. Trouver « le bon » modèle relationnel Considérons une bibliothèque souhaitant créer une base de données pour gérer le prêt de CD d’opéras. . Une bibliothèque souhaite répertorier les différents enregistrements d’œuvres lyriques sur CD mises au prêt. . Il faut pouvoir intéroger cette relation afin d’obtenir les réponses à des questions telles que : combien de fois tel CD a-t-il été emprunté ? Quel est le compositeur dont les œuvres sont les plus empruntées ? Quel est le client qui emprunte le plus sur une période donnée ? Quel est le numéro de téléphone du dernier client ayant emprunté tel CD ? etc. LLG \ PCSI2 INFO 10 \ 5 PCSI2 \ 2014-2015 Laurent Kaczmarek . Comment la bibliothèque peut-elle (doit-elle ?) organiser ces différentes données ? . Dans un premier temps, on envisage une seule relation avec les attributs suivants : nom de l’emprunteur, date de l’emprunt, titre du CD emprunté, numéro de téléphone et adresse de l’emprunteur. Voici un premier essai, la relation EmpruntsCD : CD Client Tel Adresse Date Orfeo Vrick 06.11.22.33.44 Paris 15/04/2014 Parsifal Kazan 06.23.45.67.89 Lille 12/01/2014 Jenufa Vrick 06.11.22.33.44 Paris 10/05/2014 Cette première mouture a des inconvénients évidents : . Il y a des informations redondantes (nom du client, son numéro de téléphone et son adresse) : stockage non optimal, risque d’erreur à chaque nouvelle saisie des informations redondantes (ce qui posera problème lors des recherches dans la base). . Le titre de l’opéra ne suffit pas à identifier un CD car il peut y avoir plusieurs versions de la même œuvre au catalogue. On peut y remédier en introduisant quatre relations Opera, CD, Client et Emprunt se faisant mutuellement référence. Emprunt Opera Client ? Compositeur Chaîne ↑ Chef Chaîne Annee Entier Date Date . ↓ Client CD Opera ? ? CD Chaîne Titre Nom Chaîne Tel Chaîne Adresse Chaîne Il faut faire référence sans ambiguité à un enregistrement d’une autre relation au moyen d’un attribut, voire d’un ensemble d’attributs (cf. les points d’interrogation dans le schéma précédent). Définition 10.4. Clé candidate On appelle clé candidate d’une relation tout attribut ou ensemble d’attributs d’une relation permettant d’identifier chaque enregistrement de manière unique. Exemple 10.5. Clé candidate. L’attribut Titre n’est pas une clé candidate de la relation CD mais les attributs Titre, Compositeur, Chef, Date en constituent une. L’attribut Nom n’est pas une clé candidate de la relation Client mais Tel en est une (plutôt mauvaise car un client peut changer de numéro). . Afin d’éviter la multiplication les attributs dans une clé candidate, il est souvent plus efficace d’introduire un attribut supplémentaire, un identifiant qui, par construction, sera unique pour chaque enregistrement de la relation. . Ce procédé est courant : cf. les plaques minéralogiques, les numéros de sécurité sociale, les codes ISBN, etc. LLG \ PCSI2 INFO 10 \ 6 PCSI2 \ 2014-2015 Laurent Kaczmarek Définition 10.6. Clé primaire On appelle clé primaire d’une relation la clé candidate retenue. Une clé primaire d’une relation R1 utilisée comme attribut d’une autre relation R2 sera qualifiée d’extérieure à R2 (on la notera avec #). Après réflexion, on adopte le schéma suivant pour la base de données Biblio : Emprunt Opera IdCD #IdOpera Chef Annee 1978 IdOpera Chaîne #IdCD Chaîne CD001 OP010 Mackerras Titre Chaîne #IdClient Chaîne CD002 OP003 Knapersbuch 1954 CD003 OP009 Jacobs CD004 OP004 Britten 1965 CD005 OP006 Boulez 1974 CD006 OP002 Gardiner 1982 CD007 OP007 Bohm 1973 CD008 OP008 Solti 1968 CD009 OP009 Haim 2004 Compositeur Chaîne ↑ . ↓ CD Client Chaîne IdCD Date Date #IdOpera Chaîne IdClient Chaîne Nom Chaîne Chaîne Chef Chaîne Tel Annee Entier Adresse Chaîne 1997 Extrait de la relation CD Schéma relationnel de la BD Biblio IdClient Nom IdOpera Titre Compositeur Tel Adresse CL001 Kaczmarek 06.12.34.56.78 Paris OP001 Orphée et Eurydice Gluck CL002 Vrick 06.11.22.33.44 Paris OP002 Les Boréades Rameau CL003 Kazan 06.98.76.54.32 Lille OP003 Parsifal Wagner OP004 Billy Budd Britten OP005 Elektra Strauss OP006 Wozzeck Berg OP007 La Flûte Enchantée Mozart IdEmprunt #IdCD #IdClient Date OP008 Don Carlo Verdi E001 CD006 CL002 12/12/2013 OP009 Orfeo Monteverdi E002 CD001 CL003 13/04/2014 OP010 Katia Kabanova Janacek E003 CD009 CL001 16/05/2014 Extrait de la relation Opera Extrait de la relation Client Extrait de la relation Emprunt 3. Création et alimentation d’une base de données relationnelle Ces aspects ne sont pas au programme officiel. Nous illustrerons ces aspects au moyen de l’environnement Sqliteman. On dispose de trois fenêtres : une pour la structure de la base de donnée, une autre où l’on peut disposer de vues des différentes relations de la base et une dernière dédiée aux requêtes en SQL. LLG \ PCSI2 INFO 10 \ 7 PCSI2 \ 2014-2015 Laurent Kaczmarek 3.1. Création par import de données . On peut facilement importer un tableau au format .xls (Microsoft Excel), .ods (Open Office) ou encore .csv (coma separated values). . On commence pour cela par ouvrir le fichier avec une application appropriée (un simple éditeur de texte pour les fichiers .csv) afin de repérer le nombre de colonnes de la future table et avoir une idée des contenus. . Cf. ci-contre l’extrait de la table Prenom sous sa forme brute. On crée ensuite sous Sqliteman une table avec un nombre de colonnes et des types adaptés au fichier importé. En selectionnant la table à alimenter dans l’aborescence, on trouve dans onglet Base de données une option Importer les données de la table. LLG \ PCSI2 INFO 10 \ 8 PCSI2 \ 2014-2015 Laurent Kaczmarek On peut choisir (et prévisualiser) le fichier à importer en naviguant dans les répertoires. On peut alors obtenir une vue de la table dans sqliteman. Une fenêtre permet d’effectuer des requêtes SQL dans la base (fichier .db) ainsi créée. LLG \ PCSI2 INFO 10 \ 9 PCSI2 \ 2014-2015 Laurent Kaczmarek 3.2. Création ex-nihilo On crée une table comme vu précédemment. En la selectionnant, on accède à l’option Peupler la table. On pourra ensuite obtenir une vue de la table et l’alimenter au moyen des boutons. LLG \ PCSI2 INFO 10 \ 10 PCSI2 \ 2014-2015 Laurent Kaczmarek 4. L’algèbre relationnelle Dans ce paragraphe, nous allons décrire plusieurs opérations sur les relations. Le but est d’arriver à extraire l’information que l’on cherche dans la base de données en créant une relation donnant la réponse. Considérons par exemple la relation prenom : P Nb S A Liz 5 F 2012 Lohan 9 M 2012 Pour déterminer le nombre de fois que le prénom Julie a été donné à Paris en 2012, il suffit de considérer la relation prenom et de supprimer les lignes inutiles (celles où A 6= 2012 ou P 6= "Julie"). Afin de trouver une information dans une base de données, on effectue des opérations sur les relations. Les traitements que nous ferons subir aux relations formant une base de données s’apparentent à des opérations ensemblistes (intersection, etc) mais aussi à des collages, des découpages, des suppressions de lignes ou de colonnes, etc. L’algèbre relationnelle étudie ces opérations. 4.1. Opérations sur les relations Définition 10.7. Union, intersection et différence Soient R et R0 deux relations ayant le même schéma relationnel. On définit les relations suivantes par le même schéma relationnel que R et R0 et les conditions suivantes : . la réunion de R et R0 est la relation formée des enregistrements figurant dans R ou R0 . . L’intersection de R et R0 est la relation formée des enregistrements figurant dans R et R0 . . R privé de R0 est la relation formée des enregistrements figurant dans R mais pas dans R0 . On note respectivement R ∪ R0 , R ∩ R0 et R \ R0 . Exemple 10.8. Union, intersection et différence. Soient R et R0 les relations suivantes. R Prénom Classe Guy TS1 François TS5 Julie TS3 Sexe M M F R’ Classe TS2 TS1 TS5 Sexe M M M Prénom Gustave Viviane François R ∪ R’ Prénom Classe Guy TS1 François TS5 Julie TS3 Gustave TS2 Viviane TS1 R \ R’ Sexe M M F M M Prénom Classe Sexe Guy TS1 M Julie TS3 F R ∩ R’ Prénom Classe Sexe François TS5 M La projection Définition 10.9. Projection La projection d’une relation R sur les attributs A1 , . . ., An est la relation obtenue à partir de R en supprimant les attributs ne figurant pas dans {A1 , . . . , An } et en supprimant les éventuels enregistrements doublons. On note π[A1 , . . . , An ](R) la projection de R sur A1 , . . ., An . LLG \ PCSI2 INFO 10 \ 11 PCSI2 \ 2014-2015 Laurent Kaczmarek Exemple 10.10. Une projection. Une illustration sur une relation à trois attributs. π[Prénom,Sexe](R) est la relation : Soit R définie par : Prénom John Julie Julie Marc Classe TS4 TS3 TS6 TS3 Sexe M F F M Prénom John Julie Marc Sexe M F M La sélection Définition 10.11. Sélection La sélection de R suivant une condition C (portant sur ses attributs) est la relation de même schéma relationnel que R mais dont les enregistrements sont les enregistrements de R vérifiant la condition C. On note σ[C](R) . Exemple 10.12. Une sélection. On reprend la relation de l’exemple précédent : σ[Sexe="F"](R) est la relation : R est définie par : Prénom John Julie Julie Marc Classe TS4 TS3 TS6 TS3 Sexe M F F M Prénom Sexe Julie TS3 F Julie TS6 F Le renommage Définition 10.13. Renommage Le renommage de l’attribut A de R en A0 est la relation obtenue en changeant dans R le nom de A en A0 . On note ρ[A : A0 ](R) . Exemple 10.14. Un renommage. On reprend la même relation : ρ[Sexe :S](R) est la relation : R est définie par : Prénom John Julie Julie Marc Classe TS4 TS3 TS6 TS3 Sexe M F F M Prénom John Julie Julie Marc Classe TS4 TS3 TS6 TS3 S M F F M Le produit cartésien Définition 10.15. Produit cartésien Soient R et R0 deux relations n’ayant pas d’attribut commun. de R par R0 est la relation formée de toutes les combinaisons possibles d’enregistrements des relations R et R0 . On note R × R0 . LLG \ PCSI2 INFO 10 \ 12 PCSI2 \ 2014-2015 Laurent Kaczmarek Exemple 10.16. Un produit cartésien. Toujours le même exemple. R est définie par Prénom John Julie Julie Marc Prénom Classe Sexe Numéro Ville Classe TS4 TS3 TS6 TS3 Sexe M F F M ...et R0 par... Numéro Ville 123 Naples 567 Venise John TS4 M 123 Naples Julie TS3 F 123 Naples Julie TS6 F 123 Naples Marc TS3 M 123 Naples John TS4 M 567 Venise Julie TS3 F 567 Venise Julie TS6 F 567 Venise Marc TS3 M 567 Venise Le produit cartésien R × R0 est donné par : La division cartésienne Définition 10.17. Division cartésienne Soient R et R0 deux relations tels que les attributs de R0 soient des attributs de R. La division cartésienne de R par R0 est la plus grande relation R00 telle que R00 × R0 soit contenue dans R. On note R ÷ R0 . Exemple 10.18. Une division cartésienne. Idem : Prénom Sexe Julie F Marc M R est définie par... Prénom John Julie Julie Marc Classe TS4 TS3 TS6 TS3 Sexe M F F M ...et R0 par... La division cartésienne R ÷ R0 est donnée par : Classe TS3 C’est la liste des classes où tous les prénoms de R0 apparaissent. La jointure Définition 10.19. Jointure La jointure de deux relations R et R0 suivant une condition C donnée est la sous-relation de R×R0 formée des enregistrements vérifiant C. On note R \ [C]R0 . Exemple 10.20. Une jointure. Idem ! LLG \ PCSI2 INFO 10 \ 13 PCSI2 \ 2014-2015 Laurent Kaczmarek R définie par... La jointure R \ [Classe=Terminale]R0 est donnée par : Prénom John Julie Julie Marc Classe TS4 TS3 TS6 TS3 Sexe M F F M Prénom Classe Sexe Terminale Voyage ...et R0 par... Terminale Voyage TS3 Venise TS4 Londres John TS4 M TS4 Londres Julie TS3 F TS3 Venise Marc TS3 M TS3 Venise 4.2. Groupes et fonctions d’agrégation Les fonctions d’agrégation permettent d’effectuer des opérations statistiques sur une relation R : comptage, maximum, minimum, somme et moyenne. Soient A1 , . . ., An des attributs de R. Il est possible de n’effectuer ces opérations que sur les groupes d’enregistrements de R ayant les mêmes attributs A1 , . . ., An . Définition 10.21. Fonctions d’agrégation, groupes Soit R une relation. . Soient A, B des attributs de R et f une fonction d’agrégation, on note A γ(R)A, f (B) la relation à deux attributs, A et f (B), dont chaque enregistrement est formé des valeurs de A et f (B) sur les groupes d’enregistrements de R ayant le même attribut A. . On généralise à A1 ,...,Am γ(R)A01 ,...,A0p , f 1 (B1 ),..., f k (Bk ) avec {A1 , . . . , A0p } ⊂ {A1 , . . . , Am }. Exemple 10.22. Fonctions d’agrégation et groupes. Toujours le même exemple... C γ(R)somme(N) R est définie par : P John Julie Julien Marc C TS3 TS2 TS2 TS3 N 17 15 12 14 est : somme(N) 31 27 C γ(R)C,somme(N) est : C somme(N) TS3 31 TS2 27 4.3. Exemples de requêtes sur la base de données prenoms Dans ce paragraphe, nous allons voir comment utiliser l’algèbre relationnelle pour répondre à des questions portant sur des informations rangées dans une base de données. Petit rappel, voici la base de données prenom : LLG \ PCSI2 INFO 10 \ 14 PCSI2 \ 2014-2015 Laurent Kaczmarek P Nb S A Liz 5 F 2012 Lohan 9 M 2012 Comment obtenir les prénoms donnés en 2008 ou 2012 ? On effectue une sélection sur la date suivie d’une projection sur les prénoms. . S = σ[A = 2008](prenom), T = σ[A = 2012](prenom) et U = S ∪ T : Emilien 14 M 2005 Elio P Alban Anna Liz Lohan 11 M 2007 P Nb S A Alban 24 M 2008 Anna 127 F 2008 Margot 75 F 2013 Alienor 24 F 2006 Cesar 25 M 2006 Alban 24 M 2008 Anna 127 F 2008 Nb 24 127 5 9 S M F F M A 2008 2008 2012 2012 . π[P](U) P Alban Anna Liz Lohan Zakaria 32 M 2009 Abdoul 10 M 2010 Amalia 7 P Nb S A Liz 5 F 2012 Lohan 9 M 2012 F 2011 La relation prenom Combien de prénoms ont-ils été donnés entre 2011 et 2013 ? On commence par une sélecction sur la date puis on applique une fonction de comptage. . T = σ[A > 2010](prenom) P Liz Lohan Margot Amalia Nb 5 9 75 7 . γ(T)somme(Nb) S F M F F A 2012 2012 2013 2011 somme(Nb) 96 4.4. Exemples de requêtes sur la base de données Biblio On reprend l’exemple de la base de données Biblio permettant la gestion des emprunts de CD d’opéras dans une bibiothèque. Voici le schéma de la base de données suivie de ses relations. Opera Emprunt IdOpéra Chaîne #IdCD Titre Chaîne #IdClient Chaîne Compositeur Chaîne ↑ Date Date . ↓ CD IdCD Chaîne Client Chaîne #IdOpéra Chaîne IdClient Chaîne Nom Chaîne Chef Chaîne Téléphone Chaîne Année Entier Adresse IdCD CD001 CD002 CD003 CD004 CD005 CD006 CD007 CD008 CD009 CD010 #IdOpéra OP010 OP003 OP009 OP004 OP006 OP002 OP007 OP008 OP009 OP011 Chef Mackerras Kubelik Jacobs Britten Boulez Gardiner Bohm Solti Haim Colin Davis Annee 1978 1982 1997 1965 1974 1982 1973 1968 2004 1978 Relation CD Chaîne La base de donnée Biblio LLG \ PCSI2 INFO 10 \ 15 PCSI2 \ 2014-2015 IdOpera OP001 OP002 OP003 OP004 OP005 OP006 OP007 OP008 OP009 OP010 OP011 Laurent Kaczmarek Titre Alceste Les Boréades Parsifal Billy Budd Elektra Wozzeck Cosi fan tutte Don Carlo Orfeo Katia Kabanova Peter Grimes Compositeur Gluck Rameau Wagner Britten Strauss Berg Mozart Verdi Monteverdi Janacek Britten IdClient CL001 CL002 CL003 Nom Kaczmarek Vrick Kazan Tel 06.12.34.56.78 06.11.22.33.44 06.98.76.54.32 Adresse Paris Paris Lille La relation Client IdEmprunt E001 E002 E003 E004 E005 E006 Relation Opera #IdCD CD006 CD001 CD009 CD004 CD001 CD010 #IdClient CL002 CL003 CL001 CL002 CL003 CL001 Date 12/12/2013 13/04/2014 16/05/2014 12/12/2013 13/04/2014 16/05/2014 Relation Emprunts Date des emprunts des opéras de Britten ? . R = σ[Compositeur=Britten]Opera IdOpera Titre Compositeur OP004 Billy Budd Britten OP011 Peter Grimes Britten . S = R \ [R.IdOpera=CD.IdOpera]CD IdOpera Titre Compo. IdCD IdOpera Chef Annee OP004 Billy Budd Britten CD004 OP004 Britten 1965 OP011 Peter Grimes Britten CD010 OP011 Colin Davis 1978 . T = S \ [S.IdCD=Emprunt.IdCD]Emprunt IdOpera Titre Compositeur IdCD IdOpera Chef ... OP004 Billy Budd Britten CD004 OP004 Britten ... OP011 Peter Grimes Britten CD010 OP011 Colin Davis . . . . . . Annee IdEmprunt #IdCD #IdClient Date . . . 1965 E004 CD004 CL002 12/12/2013 . . . 1978 E006 CD010 CL001 16/05/2014 . π[Dat e](T) Date 12/12/2013 16/05/2014 Nombre de CD empruntés par le client Kaczmarek ? . R = σ[Nom="Kaczmarek"](Client), S = R \ [R.IdClient=Emprunt.IdClient]Emprunt et finalement T = γ(S)comptage(Nom) count(Nom) 2 LLG \ PCSI2 INFO 10 \ 16 PCSI2 \ 2014-2015 Laurent Kaczmarek 5. Le langage SQL SQL, structured query language (langage de requête structurée) est un langage informatique normalisé servant à exploiter des bases de données relationnelles. Il permet de rechercher, d’ajouter, de modifier ou de supprimer des données dans les bases de données relationnelles. Créé en 1974, normalisé depuis 1986, le langage est supporté par la grande majorité des SGBD du marché. Il vpermet de traduire quasilittéralement les opérations de la’algèbre relationnelle. Nous illustrerons ce paragraphe par la base de données à deux relations suivante : ville Lille Douai Paris Quimper id 01 01 02 03 id 01 02 03 pop 225787 42621 2221000 63235 population nom Nord-Pas-de-Calais Ile-de-France Bretagne region Distinguer les attributs En SQL, on distinguera les colonnes id de chacune de ces relations en écrivant departement.id et population.id. Attention, les tables sont temporaires. Hormis les tables de la base de données, toutes les tables créées en SQL ne sont que temporaires, on ne peut pas les enregistrer dans une variable. Il faudra donc effectuer chacune des requêtes en une seule ligne de commande. 5.1. Traduction en SQL des opérations de l’algèbre relationnelle La projection . C’est la commande SELECT qui permet d’effectuer une projection (et non une sélection). SELECT ville,id FROM population ville Lille Douai Paris Quimper id 01 01 02 03 . On utilise SELECT * pour projeter sur toutes les colonnes. . Attention, la commande SELECT n’élimine pas les doublons. On obtiendra une sélection au sens de l’algèbre relationnelle en précisant SELECT DISTINCT. SELECT id FROM population id 01 01 02 03 LLG \ PCSI2 SELECT DISTINCT id FROM population id 01 02 03 INFO 10 \ 17 PCSI2 \ 2014-2015 Laurent Kaczmarek La sélection . On peut effectuer une sélection en ajoutant une clause WHERE à SELECT. SELECT ville,pop FROM population WHERE id=01 ville pop Lille 225787 Douai 42621 . Liste des id des régions où il existe des villes de population supérieure strictement à 70000. id 01 02 SELECT DISTINCT id FROM population WHERE pop>70000 . On peut construire des conditions plus élaborées en utilisant les opérateurs logiques AND, OR et NOT. ville pop Lille 225787 SELECT ville,pop FROM population WHERE id=01 AND pop>45000 Renommage . C’est le mot-clé AS qui permet le renommage d’une colonne. SELECT ville,pop AS city,popu FROM population city Lille Douai Paris Quimper popu 225787 42621 2221000 63235 Produit cartésien . On décrit un produit cartésien de tables en énumérant les tables après FROM. SELECT * FROM population,region ville Lille Lille Lille Douai Douai Douai Paris Paris Paris Quimper Quimper Quimper population.id 01 01 01 01 01 01 02 02 02 03 03 03 pop 225787 225787 225787 42621 42621 42621 2221000 2221000 2221000 63235 63235 63235 region.id 01 02 03 01 02 03 01 02 03 01 02 03 nom Nord-Pas-de-Calais Ile-de-France Bretagne Nord-Pas-de-Calais Ile-de-France Bretagne Nord-Pas-de-Calais Ile-de-France Bretagne Nord-Pas-de-Calais Ile-de-France Bretagne . La présence du même attribut id dans les deux tables ne pose pas de problème car ils sont en fait manipulés comme region.id et population.id. Division cartésienne . Il n’existe aucune commande pour la division cartésienne en SQL ; on peut la simuler par d’autres commandes. LLG \ PCSI2 INFO 10 \ 18 PCSI2 \ 2014-2015 Laurent Kaczmarek Jointure . Une jointure s’effectue au moyen de JOIN ... ON. SELECT ville,pop,nom FROM population JOIN region ON population.id=region.id ville Lille Douai Paris Quimper pop 225787 42621 2221000 63235 nom Nord-Pas-de-Calais Nord-Pas-de-Calais Ile-de-France Bretagne . Obtenir la liste des villes de la région Nord-Pas-de-Calais : SELECT ville FROM population,region WHERE population.id=region.id AND nom="Nord-Pas-de-Calais" ville Lille Douai . On peut effectuer une jointure multiple par : SELECT ... FROM table1 JOIN table2 JOIN table3 ON cond1 AND cond2 ... Il faut éviter les produits cartésiens. On peut simuler une jointure en parcourant un produit cartésien avec une clause WHERE. Cette méthode est à proscrire car oblige à calculer le produit cartésien SELECT ville,nom FROM population,region WHERE population.id=region.id qui pour certaines tables a une taille gigantesque. La jointure permet d’éviter l’explosion de la complexité due à la taille du produit cartésien. Opérations ensemblistes . L’opérateur UNION réalise la réunion de deux tables. SELECT * FROM table1 UNION SELECT * FROM table2 . L’opérateur INTERSECT réalise la réunion de deux tables. SELECT * FROM table1 INTERSECT SELECT * FROM table2 . L’opérateur EXCEPT réalise la différence de deux tables. SELECT * FROM table1 EXCEPT SELECT * FROM table2 Comptage, max, min, somme et moyenne . Le comptage des enregistrements d’une table s’effectue au moyen de la fonction COUNT. SELECT COUNT(*) AS total FROM region total 3 . MAX, AVG, MIN et SUM permettent de déterminer le maximum, la moyenne, le minimum et la somme d’une colonne. SELECT MAX(pop) AS maximum FROM population LLG \ PCSI2 maximum 2221000 INFO 10 \ 19 PCSI2 \ 2014-2015 Laurent Kaczmarek Groupement . On peut appliquer une fonction à un groupe de lignes en utilisant la commande GROUP BY. SELECT nom,SUM(pop) AS popu FROM population,region WHERE population.id=region.id GROUP BY nom nom Nord-Pas-de-Calais Ile-de-France Bretagne popu 268408 2221000 63235 . La clause HAVING remplace WHERE lorsque les colonnes intervenant dans la condition proviennent d’une fonction. SELECT nom,SUM(pop) AS popu FROM population,region WHERE population.id=region.id GROUP BY nom HAVING popu<65000 nom popu Bretagne 63235 Ordonner selon un attribut . C’est la clause ORDER BY expression ASC / DESC qui permet de ranger une table selon une relation d’ordre spécifiée (par exemple ordre alphabétique, ordre usuel sur R, dates, etc). SELECT ville,pop FROM population ORDER BY pop ASC ville Douai Quimper Lille Paris pop 42621 63235 225787 2221000 Sous-requêtes . Il est possible d’imbriquer une requête dans une clause SELECT, ou (le plus souvent) au sein d’un filtre WHERE ou HAVING, la sous-requête devant être délimitée par des parenthèses. On peut par exemple selectionner les villes ayant une population strictement supérieure à la moyenne nationale. ¡ ¢ SELECT ville FROM population WHERE pop> SELECT AVG(pop) FROM population ville Paris 5.2. Création et alimentation d’une base de données en SQL Il est possible de créer et de supprimer une base de données au moyen d’un code SQL. De même, il est possible de l’alimenter par du code SQL. Ces aspects en sont pas au programme. LLG \ PCSI2 INFO 10 \ 20 PCSI2 \ 2014-2015 Laurent Kaczmarek 5.3. Requêtes depuis une application de gestion de SGBD On code en SQL dans un interpréteur, par exemple dans Sqliteman : 5.4. Requêtes depuis un interpréteur Python Il est possible de manipuler une base de données directement depuis un interpréteur Python. On utilise pour cela la bibliothèque sqlite3. On commence par créer une connexion à la base de données au moyen de la méthode connect : conn=sqlite3.connect(’nom.db’) On crée alors un curseur au moyen de la méthode cursor : c=conn.cursor() On peut alors utiliser du code SQL encapsulé au moyen de execute : c.execute(’SELECT...’) On récupère le résultat au moyen de la méthode fetchall : print(c.fetchall()) Exemple 10.23. Connection à une bade de données depuis un interpréteur Python. >>> import sqlite3 >>> conn=sqlite3.connect(’films.db’) >>> c=conn.cursor() >>> c.execute(’SELECT prenom FROM acteur’) <sqlite3.Cursor object at 0xb582e760> >>> print(c.fetchall()) [(u’Tim’,), (u’Morgan’,), (u’Bob’,),... LLG \ PCSI2 INFO 10 \ 21