informatique commune Chapitre 4 Bases de données relationnelles Ce chapitre est

informatique commune Chapitre 4 Bases de données relationnelles Ce chapitre est un document de révision. Se référer à votre cours de première année pour un cours plus complet sur le sujet. Introduction Schématiquement, une base de données relationnelle (BDR) est un ensemble de tables contenant des données reliées entre elles par des relations ; on y extrait de l’information par le biais de requêtes exprimées dans un langage appelé SQL (Structured Query Language). Python et sql Le système de gestion de bases de données relationnelles (SGBDR) que nous utiliserons préférentiellement s’appelle SQLite ; il présente l’avantage d’être présent dans la bibliothèque standard de Python. Cela signifie donc que vous pouvez écrire en Python une application contenant son propre SGBDR intégré à l’aide du module sqlite3. Vous trouverez en annexe un script Python rudimentaire mais suffisant pour pouvoir interagir avec une base de donnée enregistrée sur votre disque dur. Algèbre relationnelle SQL est le langage de prédilection pour interagir avec une BDR, mais ce l’est pas le seul. L’algèbre relationnelle fournit un cadre théorique indépendant du langage, proche de la théorie des ensembles. Les opérations sur les BDR y sont définies de manière formelle et permettent de les composer efficacement entre-elles. La formulation abstraite dans le cadre de l’algèbre relationnelle permet d’obtenir des requêtes non seulement correctes mais aussi et surtout efficaces. 1. Le langage SQL Pour illustrer ce document nous allons utiliser une base de données qui se nomme mondial.sq3 que vous pouvez récupérer sur le site de ce cours. À condition de bénéficier d’une liaison avec internet vous pouvez aussi vous rendre à l’adresse : http://www.semwebtech.org/sqlfrontend/ ; vous y trouverez une interface graphique permettant d’interroger directement cette BDR en utilisant un simple navigateur. 1.1 Requêtes de base Cette base de données contient de nombreuses tables de données géographiques. Parmi celles-ci on trouve une table nommée country qui possède six attributs : Name Code Capital Province Area Population Les quatre premiers sont des chaînes de caractères 1, le cinquième un nombre flottant et le dernier un entier ; le code du pays est la clé primaire de la table : son unicité est garantie, ce qui permet d’identifier de manière unique un enregistrement de cette table. Remarque. On pourrait trouver pertinent de choisir comme clé primaire le nom du pays, mais ce serait une mauvaise idée : il peut arriver qu’un pays change de dénomination après un changement de régime politique, voire que deux états distincts revendiquent le même nom ; en revanche il ne changera pas de code, ce qui sera précieux lorsqu’il faudra réaliser des jointures entre différentes tables. 1. La province désigne la région où se situe la capitale. Jean-Pierre Becirspahic 4.2 informatique commune Name Code Capital Province Area Population Lebanon RL Beirut Lebanon 10400.0 3776317 West Bank WEST None None 5860.0 1427741 Japan J Tokyo Tokyo 377835.0 125449703 South Korea ROK Seoul South Korea 98480.0 45482291 Maldives MV Male Maldives 300.0 270758 Oman OM Muscat Oman 212460.0 2186548 Mexico MEX Mexico City Distrito Federal 1972550.0 95772462 Canada CDN Ottawa Ontario 9976140.0 28820671 Figure 1 – Un extrait de la table country. Commençons par extraire de cette table le nom de tous les pays qu’elle contient (il y en a 241) : SELECT name FROM country Les mots-clés SELECT ... FROM réalisent l’interrogation de la table. Dans le cas de l’exemple ci-dessus on ne liste qu’un seul des attributs de la table, pour en avoir plusieurs on sépare les attributs par une virgule ; pour les avoir tous on les désigne par une étoile. Par exemple, les deux requêtes qui suivent donnent pour la première le nom de chacun des pays ainsi que leurs capitales, pour la seconde l’intégralité des données de la table : SELECT name , c a p i t a l FROM country SELECT * FROM country Le mot-clé WHERE filtre les données qui répondent à un critère de sélection. Par exemple, pour connaître le nom de la capitale du Botswana on écrira : SELECT c a p i t a l FROM country WHERE name = ' Botswana ' On peut observer figure 1 que West Bank (la Cisjordanie) n’a pas de capitale (reconnue internationalement). Pour connaître le nom de tous les territoires dans ce cas : SELECT name FROM country WHERE c a p i t a l i s NULL Différentes clauses permettent de formuler des requêtes plus élaborées ; la figure 2 rassemble les instructions les plus fréquentes. SELECT * sélection des colonnes SELECT DISTINCT * sélection sans doublon FROM table nom d’une ou plusieurs tables WHERE condition imposer une condition GROUP BY expression grouper les résultats HAVING condition condition sur un groupe UNION | INTERSECT | EXCEPT opérations ensemblistes sur les requêtes ORDER BY expression trier les résultats LIMIT number limiter à n enregistrements OFFSET start débuter à partir de n enregistrements Figure 2 – Principales requêtes SQL.    Exercice 1 Rédiger une requête SQL pour obtenir : 1. la liste des pays dont la population excède 60000000 d’habitants ; 2. la même liste triée par ordre alphabétique ; 3. la même liste triée par ordre décroissant de population ; 4. le nom des dix plus petits pays ; 5. le nom des dix suivants. Bases de données relationnelles 4.3 1.2 Jointures L’intérêt d’une base de données réside en particulier dans la possibilité de croiser des informations présentes dans plusieurs tables par l’intermédiaire d’une jointure. Dans la base de données qui nous occupe on trouve une table nommée encompasses qui possède trois attributs : Country Continent Percentage Le premier attribut est le code du pays, le deuxième le nom du continent et le dernier la portion du pays présente sur ce continent. La clé primaire de cette table est le couple (Country, Continent), et la valeur du troisième argument ne peut pas être nulle. Cette seconde table possède un attribut en commun avec la première table : l’attribut Country de la table encompasses est identique à l’attribut Code de la table country et va nous permettre par son intermédiaire de croiser les informations de ces deux tables. Par exemple, pour connaître la liste des pays dont une fraction au moins est en Europe on écrira la requête : SELECT country . name FROM country JOIN encompasses ON country . code = encompasses . country WHERE encompasses . continent = ' Europe ' Les mots-clés JOIN ... ON créent une table intermédiaire formée du produit cartésien des dexu tables et applique ensuite la requête sur la nouvelle relation. Remarque. L’interrogation de plusieurs tables simultanément rend nécessaire le préfixage de l’attribut par le nom de la table pour le cas où certaines d’entres-elles auraient des noms d’attributs en commun. On peut alléger cette syntaxe à l’aide d’alias pour la rendre plus compacte. Par exemple, la requête précédente peut s’écrire plus succinctement : SELECT c . name FROM country c JOIN encompasses e ON c . code = e . country WHERE e . continent = ' Europe '    Exercice 2 Rédiger une requête SQL pour obtenir : 1. le nom des pays qui sont à cheval sur plusieurs continents ; 2. les pays du contient américain qui comptent moins de 10 habitants par km2. Dans la base de données figure une table nommée city qui possède les attributs suivants : Name Country Province Population Longitude Latitude (L’attribut Country est le code du pays.) 3. Déterminer les capitales européennes situées à une latitude supérieure à 60◦. 1.3 Fonctions d’agrégation Il est possible de regrouper les lignes d’une table suivant certains critère à l’aide de fonctions d’agrégation (on trouvera la liste des fonctions statistiques figure 3). Par exemple, pour obtenir le nombre de pays africains on écrira : SELECT COUNT ( * ) FROM country c JOIN encompasses e ON c . code = e . country WHERE e . continent = ' A f r i c a ' De même, pour connaître le nombre d’habitants sur le continent américain on demandera : SELECT SUM( c . population ) FROM country c JOIN encompasses e ON c . code = e . country WHERE e . continent = ' America ' Jean-Pierre Becirspahic 4.4 informatique commune COUNT( ) nombre d’enregistrements sur une table ou une colonne distincte MAX( ) valeur maximale d’une colonne MIN( ) valeur minimale d’une colonne SUM( ) calcul de la somme sur un ensemble d’enregistrement AVG( ) calcul de la moyenne sur un ensemble d’enregistrement Figure 3 – Fonctions statistiques.    Exercice 3 La table language possède les attributs suivants : Country Name Percentage L’attribut Country est le code du pays, Name le nom d’une langue parlée dans celui-ci, et Percentage la proportion d’habitants dont c’est la langue maternelle. 1. Donner la liste ordonnée des dix langues parlées dans le plus de pays différents. 2. Quelles sont les cinq langues les plus parlées au monde (et présentes dans la base) ? On précisera pour chacune d’elles le nombre de personnes qui la parlent. 3. Dans quel pays partiellement francophone la uploads/Geographie/04-bases-de-donnees-relationnelles 1 .pdf

Tags
Administrationcountry table select données d’une
Documents similaires
  • 25
  • 0
  • 0
Afficher les détails des licences
Licence et utilisation
Gratuit pour un usage personnel Attribution requise
Partager