Pluripass L2 : bases de données relationnellesgilles.hunault "at" univ-angers.fr
|
Retour à la page principale de
(gH)
Pluripass L2 : bases de données relationnelles
gilles.hunault "at" univ-angers.fr
Attention : il s'agit d'un cours universitaire classique, pas d'une matière à concours. Il n'y a pas de PDF magique associé à ce cours qui contiendrait «toutes les solutions». Vous avez donc plus à réfléchir qu'à mémoriser car c'est un savoir-faire que vous devez acquérir ici, et non pas seulement des connaissances pour singesses et singes savants.
Le document de base de référence pour la partie MySQL est notre tuteur MySQL.
1. Pérennité et structuration de l'information
Donnée, information et connaissance ne sont pas des termes équivalents, même si savoir utiliser Google peut faire croire le contraire. Une base de données, accessible via un SGBDR, fait souvent partie d'un SI et se révèle vite obligatoire dès qu'on gère de nombreuses informations liées entre elles, comme dans l'exemple classique mais presque d'une GTI («grande tristesse inénarrable») nommé 4 tables et dont nous reproduisons ci-dessous une partie des données :
TABLE cmd (COMMANDES) +----+-----+-----+-----+ | nc | ncl | nar | qte | +----+-----+-----+-----+ | 1 | 9 | 2 | 4 | | 2 | 7 | 4 | 1 | | 3 | 6 | 14 | 3 | | 4 | 1 | 11 | 1 | | 5 | 9 | 2 | 1 | | 6 | 9 | 12 | 1 | +----+-----+-----+-----+ TABLE cli (CLIENTS) +----+------------+ | nc | nom | +----+------------+ | 1 | DUPONT | | 2 | FROGARIN | | 3 | DE MARNETS | [...] | 9 | BOND | | 10 | LUCAS | +----+------------+ TABLE art (ARTICLES) +----+--------------------+-------------+------+------+ | na | nom | ref | four | prix | +----+--------------------+-------------+------+------+ | 1 | VELO ENFANT | VE-642/05 | 1 | 85 | | 2 | MONTRE FEMME | MF-1057/07 | 3 | 160 | | 3 | HALOGENE | HA-32478/06 | 2 | 345 | [...] | 19 | BALLON OVALE | BO-88955/08 | 1 | 14 | | 20 | MONTRE ENFANT | ME-102/05 | 3 | 17.5 | +----+--------------------+-------------+------+------+ TABLE four (FOURNISSEURS) +----+------------------+ | nf | nom | +----+------------------+ | 1 | Jouets S.A. | | 2 | Cadeaux S.A.R.L. | | 3 | Gifts Inc. | | 4 | L3S | +----+------------------+
Sous le terme de pérennité de l'information se cachent les problèmes éthiques du stockage tous azimuths que ce soit pour la médecine personnalisée avec par exemple les montres connectées qui transmettent en permanence des informations biologiques, ou que ce soit, sous couvert de meilleurs conseils, l'enregistrement de tous les achats et autres activités sur internet. C'est en ce sens que le Big Data avec ses bases de données gigantesques, pose un problème citoyen, le droit à l'anonymat.
2. Systèmes d'informations, conceptualisation et SGBD[R]
SI = système d'information. SGBDR = système de gestion de bases de données relationnelles pour le stockage et l'accès aux données.
La conception d'une base de données repose sur des principes rigoureux qui demandent une analyse et un développement soutenu, du modèle conceptuel au modèle logique, en passant par le modèle entité, association, attributs, cardinalités et dont les maitres-mots sont la structuration, la cohérence, la non redondance, l'exhaustivité, l'unicité, l'intégrité, la sécurité, la fiabilité, bref, du gros et du lourd, et en plus efficaces !
Liens EA : ea1 (copie locale) ea2 (copie locale) ea3 (copie locale) ea4 (copie locale)
En d'autres termes, avant de commencer à stocker quoi que ce soit, il faut commencer à penser non seulement aux données mais aussi aux liens entre les données (MCD) et aux traitements que l'on envisage sur ces données (MCT). Les autres sigles associés sont alors par exemple MOD et MOT avec un formalisme là encore bien établi que nous ne pouvons détailler ici. On pourra consulter D318_Ch3 (copie locale) pour se familiariser avec les concepts et les termes.
Le modèle relationnel qui est la suite logique du modèle conceptuel n'est pas le seul modèle traditionnel classique, car il y a aussi le modèle hiérarchique. Plus récemment, avec le Big Data de nouveaux concepts et modèles ont émergé, comme celui de base de données orientées documents dans la mouvance nommée NoSQL mais tout ceci dépasse le cadre de ce cours.
3. Bases, tables, relations, index et clés d'appariement
L'information structurée est contenue dans des bases de données, contenant elles-mêmes des tables de données, mettant en jeu des relations, accompagnées de moyens d'accès aux champs (index) et aux informations liées (clés primaires, secondaires et étrangères).
Liens BD : bd1 pages>020 (copie locale) bd2 (copie locale) bd3 (copie locale)
Une base de données n'est donc pas un simple fichier Excel, même avec plusieurs onglets. C'est un ensemble cohérent, réfléchi de tables normalisées et controlées de données, stockées efficacement dont l'accès est rapide et efficace, par exemple au niveau des tris dans les affichages. On pourra s'en convaincre avec le parcours des bases LEAPdb et sHSPdb qui ne pourraient sans doute pas être gérées efficacement sous Excel. Sur le contenu de ces bases de données, on pourra consulter les liens LEA1, LEA2 et HSP.
Une base contient des tables structurées en champs et enregistrements, vues respectivement comme des colonnes et des lignes dont l'ordre n'est pas positionnel. Ainsi on parle de la colonne AGE et non pas de la colonne 4. De même, la notion de n premières lignes n'a de sens que pour un critère de tri explicite des lignes. Les termes anglais correspondants à champs et enregistrements sont fields et records mais l'usage courant autorise colonnes (non positionnelles) et lignes (via un critère de tri explicite). On passe du modèle conceptuel des données au modèle relationnel via des règles classiques qui ne suffisent pas en général pour tout convertir. Voir Rigaux page 37 et suivantes pour plus d'explications.
La principale commande à savoir utiliser en SQL est SELECT dont le détail de la syntaxe est ici pour MySQL et que nous reproduisons ci-dessous :
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [MAX_STATEMENT_TIME = N] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR UPDATE | LOCK IN SHARE MODE]]Comme indiqué sur la page française du Wiki pour SQL SELECT, la pratique courante de SELECT utilise -- et dans cet ordre -- les options F, W, GB, H, OB, L, à savoir FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Comme indiqué au bas de la page anglaise du Wiki pour SQL SELECT, chaque implémentation de SQL a sa propre syntaxe étendue.
Le but de la commande SELECT est de fournir des valeurs de champs (SEXE, AGE...) ou des calculs sur champs (POUCES* 2.54, MIN(PRIX)... à partir d'enregistrements dans des tables (FROM) éventuellement filtrés par des conditions (WHERE), à l'aide d'options de regroupement (GROUP BY), groupes eux-aussi éventuellement sélectionnés (HAVING), et dont l'affichage peut être trié (ORDER BY). Enfin, au lieu d'afficher tous les résultats, on peut demander seulement l'affichage des lignes tant à tant (LIMIT).
SELECT peut aussi être une option de la commande INSERT, ce qui permet de remplir une table à partir d'autres tables.
Si les commandes SELECT pour des interrogations usuelles sur une table sont assez faciles à maitriser, de même que les interrogations pour plusieurs tables lorsqu'on croise des informations présentes, les options de "JOINTURE" pour trouver ce qui n'est pas présent demandent nettement plus d'expérience, ne serait-ce que parce que les jointures peuvent être effectuées sur les tables de gauche ou sur les tables de droite, ce qui n'aboutit pas forcément aux mêmes résultats. Par exemple avec une table de clients et une table de commandes, dans un cas on pourra trouver les clients qui n'ont pas commandé et dans l'autre, les commandes sans client courant (en supposant un filtrage par date notamment...).
Sur le site de sqlpro, via l'URL sqlaz/select on trouvera une longue page en français sur cette fameuse commande SELECT. Pour comprendre les jointures en SQL, nous vous conseillons de lire dans cet ordre jointures 1 puis jointures 2.
4. Les langages SQL dont MySQL et SQLITE3
Si le langage SQL est défini comme un langage abstrait, de nombreuses implémentations réelles (ou dialectes) sont disponibles, comme Oracle, MySQL, PostgreSQL, SQLite[3]... Notre tuteur MySQL en présente les aspects techniques (non conceptuels) pour une mise en oeuvre et une utilisation élémentaire et notre page executeMysql fournit une interface rudimentaire pour tester des commandes MySQL sur nos bases de données sans aucune installation de logiciel.
Dans le cadre de cette sensibilisation aux bases de données via MySQL trois instructions sont privilégiées : USE pour ouvrir (accéder, utiliser) une base de données choisie, CREATE pour définir (créer, structurer, formater) une nouvelle table de données dans une base de données, SELECT pour interroger (consulter, sélectionner, extraire) des informations à partir d'une ou plusieurs tables de données, à l'aide de relations et de reformatage parfois complexes.
Il existe bien sûr d'autres instructions en MySQL comme le montrent les liens ci-dessous :
SQLITE (PDF) est une implémentation de SQL prévue pour une gestion rapide (sans administration) des droits et des données, pratique pour un usage personnel, pour des tests et dans des développements AGILE. La syntaxe de SQLITE est très proche de celle de MySQL.
Comme indiqué sur la page française du Wiki pour MySQL, la version Open Source de MySQL se nomme désormais MariaDB. Sur la photograpie de famille ci-dessous, My est à gauche et Maria à droite...
5. Exemple concret, la table RONFLE
Voici quelques exemple de requêtes MySQL pour la table RONFLE dans la base de données stadata sur le serveur forge du département informatique de l'université d'Angers.
Rappel : toutes les commandes MySQL présentées sont testables sur notre page executeMysql.
Après une connexion au serveur de bases de données (connexion non détaillée ici), on réalise l'accès à la base via l'instruction
USE statdata ;Une fois le nom des champs lus via
DESCRIBE ronfle ;dont le résultat est
+--------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------+------+-----+---------+-------+ | IDEN | varchar(5) | NO | | | | | AGE | int(6) | NO | | 0 | | | POIDS | int(9) | NO | | 0 | | | TAILLE | int(10) | NO | | 0 | | | ALCOOL | int(7) | NO | | 0 | | | SEXE | int(9) | NO | | 0 | | | RONFLE | int(9) | NO | | 0 | | | TABA | int(11) | NO | | 0 | | +--------+------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) Byeon est en mesure de répondre à de nombreuses questions sur cette table. Ainsi, voici comment réaliser le comptage du nombre d'hommes et de femmes
SELECT sexe, count(sexe) FROM ronfle GROUP BY sexe ;Affichage produit :
+------+-------------+ | sexe | count(sexe) | +------+-------------+ | 0 | 75 | | 1 | 25 | +------+-------------+Il est en général classique de fournir le pourcentage correspondant, disons avec une seule décimale, et d'afficher les résultats par pourcentage décroissant, en utilisant l'alphabétique du sexe en cas d'égalité de pourcentage. Voici le code correspondant et ses résultats pour les personnes de 40 ans et plus :
SELECT IF(sexe=0,"Hommes","Femmes") AS Genre, COUNT(sexe) AS nombre, ROUND(100*count(sexe)/(select count(*) from ronfle where age>=40),1) AS pct FROM ronfle WHERE AGE>=40 GROUP BY sexe ORDER BY pct DESC, Genre ASC ; # fin de selectCette commande MySQL affiche :
+--------+--------+------+ | Genre | nombre | pct | +--------+--------+------+ | Hommes | 64 | 75.3 | | Femmes | 21 | 24.7 | +--------+--------+------+ 2 rows in set (0.01 sec)Il est clair que passer de la première requête à la seconde requête demande de l'entrainement.
Voici un autre calcul : on veut connaitre les personnes qui fument et qui ronflent, de plus de soixante ans et qui ont un poids inférieur à la moyenne moins 1,25 fois l'écart-type du poids général. Avec un peu de réflexion, on peut écrire (mais c'est une solution «non évoluée») :
SELECT IDEN, AGE, POIDS, SEXE, TAILLE, ALCOOL FROM ronfle WHERE ronfle=1 AND taba=1 AND age>60 AND poids < ( (select avg(poids) from ronfle) - 1.25*(select std(poids) from ronfle) ) ORDER BY IDEN ; # fin de selectAffichage :
+-------+-----+-------+------+--------+--------+ | IDEN | AGE | POIDS | SEXE | TAILLE | ALCOOL | +-------+-----+-------+------+--------+--------+ | P0092 | 64 | 49 | 0 | 164 | 4 | | P0096 | 64 | 54 | 0 | 159 | 4 | +-------+-----+-------+------+--------+--------+Le détail des calculs pour la moyenne, l'écart-type et la limite inférieure est ci-dessous :
select AVG(poids) AS "Moyenne des poids : " , STD(poids) AS "Ecart-type des poids : " , ( (select avg(poids) from ronfle) - 1.25*(select std(poids) from ronfle) ) AS "Moyenne moins 1.25 * écart-type des poids : " FROM ronfle ;et son résultat est :
+-------------------------+-------------------------+----------------------------------------------+ | Moyenne des poids : | Ecart-type des poids : | Moyenne moins 1.25 * écart-type des poids : | +-------------------------+-------------------------+----------------------------------------------+ | 90.4100 | 18.6419 | 67.1076 | +-------------------------+-------------------------+----------------------------------------------+Voici une requête plus aboutie et son résultat où chaque ligne de résultat correspondante est numérotée, mais là encore, il faut connaitre plus de MySQL (et en particulier la notion de variable) pour y arriver :
## un calcul un peu complexe en MySQL : # affichage des personnes qui fument et qui ronflent, de plus de soixante ans # et qui ont un poids inférieur à la moyenne moins 1,25 fois l'écart-type du poids général # affichage avec lignes numérotées ; critère de tri : matricule des personnes # données : table RONFLE, base STATDATA. SET @compteur = 0 ; SET @moy = (SELECT AVG(poids) FROM ronfle ) ; SET @ect = (SELECT STD(poids) FROM ronfle ) ; SET @liminf = ( @moy - 1.25*@ect ) ; SELECT @compteur := @compteur + 1 AS "num", IDEN, AGE, POIDS, SEXE, TAILLE, ALCOOL FROM ronfle WHERE (ronfle=1) AND (taba=1) AND (age>60) AND (poids < @liminf) ; # fin de selectAffichage :
+------+-------+-----+-------+------+--------+--------+ | num | IDEN | AGE | POIDS | SEXE | TAILLE | ALCOOL | +------+-------+-----+-------+------+--------+--------+ | 1 | P0092 | 64 | 49 | 0 | 164 | 4 | | 2 | P0096 | 64 | 54 | 0 | 159 | 4 | +------+-------+-----+-------+------+--------+--------+ 2 rows in set (0.00 sec)6. Lectures conseillées
Après la lecture des pages Wiki en français et en anglais correspondant aux mots suivants
base de données entités/associations algèbre relationnelle SQL MySQL SQLite nous ne conseillons que la lecture des deux PDF ci-dessous :
Cours de bases de données de Philippe RIGAUX (copie locale) -- les 90 premières pages seulement.
Introduction aux bases de données de Michel CARTEREAU.
Toutefois, une lectrice ou un lecteur intéressé par le sujet pourra approfondir avec l'ouvrage Foundations of Databases puis découvrir avec effroi (!) tout ce qu'il resterait à apprendre pour passer à des Bases de données documentaires et distribuées...
7. Exercices de réflexion et d'entrainement
Pour la partie MySQL, on utilisera exclusivement la page
qui permet d'exécuter des requêtes sur le serveur forge de bases de données du département informatique de la faculté des sciences d'Angers.
Les exercices de réflexion ne demandent pas a priori d'utiliser un ordinateur.
Les exercices d'entrainement sont là pour vérifier que vous savez utiliser MySQL.