From silico.biotoul.fr

Revision as of 08:37, 13 March 2012

Afin de créer la base de données qui s'appellera resistance, connectez vous sur le site http://localhost/phpMyAdmin

Il s'agit d'un serveur Web local (installé sur votre ordinateur). Le programme faisant office de serveur Web s'appelle Apache. Des pages Web dynamiques fournissent une interface Web de gestion de bases de données (phpMyAdmin) pour un serveur de bases de données MySQL.

Ces programmes sont open sources et/ou gratuits et peuvent être installés sur Windows, Linux ou MasOS. Il existe des projets les rassemblant et qui permettent donc une installation rapide pour les néophytes : http://www.wampserver.com pour Windows par exemple.

Création de la base de données

Pour commencer, il faut créer une nouvelle base de données. Pour cela, cliquez sur l'onglet Bases de données. Vous avez alors la possibilité de nommer cette nouvelle base et de cliquez sur le bouton Créer. Cette nouvelle base devrait ensuite s'afficher sur la gauche de la page avec les autres bases existantes (information_schema, mysql et test normalement).

Vous pouvez alors cliquer sur cette nouvelle base pour la sélectionner. Il devrait s'afficher une page vous proposant de créer une nouvelle table.

Si vous désirez supprimer une base, vous pouvez après l'avoir sélectionnée, aller dans l'onglet Opérations, et cliquez sur Supprimer la base de données.

Création de tables

Pour cela, il faut sélectionner une base de données. Puis, dans l'onglet Structure, indiquez le nom de la table ainsi que le nombre de colonnes. D'où l'importance de déterminer à l'avance le schéma de la base de données. Néanmoins, la plupart des systèmes de gestion de bases de données (SGBD) actuels autorisent la modification de la structure de la base (ajout, suppression, renommage de colonnes et autres).

Il va vous falloir créer les tables que vous avez déterminer pour l'étude de la résistance des plantes au microorganisme. Exemple avec la table recueillant les informations sur les lignées des plantes : organisme_pl, cette table a 7 colonnes qui sont les suivantes :

• hapmap_id
• lignee
• population
• pays
• categorie
• provenance_graines
• statut

Cette table a été obtenue à partir du projet http://www.medicagohapmap.org/. Sur ce site, les données contenues dans la table sont sur la page http://www.medicagohapmap.org/public_germ_db/germplasm_db_report.php?1

Pour créer cette table, après avoir renseigner son nom et qu'elle comporte 7 colonnes, une fenêtre apparaît dans laquelle il faut donner le nom de chaque colonne ainsi que son type.

Il y a différents types disponibles. Les principaux sont les suivant :

• nombre entier : INT
• nombre réel : FLOAT
• texte (sur une seule ligne = chaine de caractères de longueur variable) : VARCHAR(n) avec n la longueur maximale autorisée.
• texte (sur plusieurs lignes) : TEXT
• date (attention au format AAAA/MM/JJ) : DATE
• date et heure : DATETIME
• etc.

Déterminez le type des attributs et créez la table organisme_pl.

Une fois la table créée, il est encore possible de modifier sa structure. Pour cela, si besoin, il faut cliquer sur la table, puis sur l'onglet structure. Sut cette page, vous pouvez modifier le type d'un attribut, ainsi qu'ajouter ou supprimer des colonnes.

Edition manuelle des données

Une fois que vous aurez créé au moins une table, cliquez sur la page correspondante et créez une nouvelle ligne à partir de l'onglet Insérer.

Après l'insertion, en sélectionnant de nouveau cette table, une partie des lignes qu'elle contient est affichée. Vous devriez donc voir apparaître la ligne que vous venez de créer. Ceci correspond en fait au résultat de la requête de consultation qui s'affiche en début de page (SELECT * FROM ...) ; nous y reviendrons un peu plus tard.

Remarquez qu'il est également possible de modifier les lignes (appelés tuples dans le jargon des bases de données) à partir des liens Modifier, Editer en place, Effacer.

Sauvegarde et restauration d'une base de données

La sauvegarde (appelée souvent dump) se fait dans l'onglet Exporter. Selon que vous êtes sur la page de la base ou sur la page d'une table, l'export se fera soit de la base complète soit de la table sélectionnée.

L'export permet de sauvegarder une image de la structure et des données de la base, ce qui permet de la restaurer sur un autre serveur de base de données (ou le même).

Essayez donc en cliquant sur l'onglet Exporter avec les options par défaut. Enregistrez puis/ou visualisez le fichier avec un éditeur de texte. Il s'agit du langage SQL et des commandes permettant la création de(s) table(s) puis l'insertion des données qu'elles contiennent. Les principales commandes sont donc :

• CREATE TABLE nom_de_la_table (nom_colonne type_colonne, ...
• INSERT INTO nom_de_la_table (noms des colonnes concernées) VALUES (valeurs pour ces colonnes)

Maintenant que vous disposez d'une sauvegarde, vous pouvez supprimer votre base de données. Pour la suite du TP, vous allez travailler sur un dump contenant à la fois la structure (création des tables) et un sous-ensemble des données correspondant aux manips 12, 13 et 14.

Vous allez donc restaurer cette image en cliquant sur l'onglet Importer et en fournissant le fichier correspondant à la sauvegarde.

Après avoir restaurer cette image, allez sur la page de la base resistance. Combien y a-t-il de lignes dans chacune des table ?

Explorez les données et déterminez quel accession est le moins sensible (sur la moyenne du brunissement des plantes et des répétitions biologiques).

Requêtes de consultation

Nous allons maintenant appréhender le langage SQL permettant d'interroger efficacement le contenu d'une ou plusieurs table. A peu près n'importe quelle requête de consultation se fait à partir d'une seule commande : SELECT

Pour simplifier, une requête SELECT se décompose en 3 parties :

SELECT noms des colonnes à afficher
FROM noms des tables concernées
WHERE conditions pour afficher une ligne

Exemple : pour afficher la totalité du contenu de la table organisme_pl

SELECT *
FROM organisme_pl

Essayez cette commande à partir de l'onglet SQL.

Ainsi, pour n'afficher que les accessions venant d'un pays, il suffit de rajouter ce filtre/cette contrainte :

SELECT *
FROM organisme_pl
WHERE pays='Greece'

Combien d'accessions proviennent de Chypre ? de France et du Maroc ?

La partie WHERE permet de spécifier un filtre ou une contrainte pour qu'une ligne soit affichée. Cela correspond en fait a une formule logique qui doit s'interpréter à vrai ou faux ; vrai implique que la ligne sera affichée.

Affichez les fichier, id_boite, num_plant et R1 des plantes dont le brunissement est à 100%. Combien y en a-t-il ?

Afin d'y voir un peu plus clair, on peut trier par identifiant de boite puis par n° de plante pour voir si on a beaucoup de plantes d'une même boite. Pour cela, on rajoute à la requête précédente : ORDER BY id_boite, num_plant

Accès au données réparties sur plusieurs tables

La conception du schéma de la base nous a amenés à morceler les données et à les répartir sur plusieurs tables. Il est donc essentiel de pouvoir faire le lien entre ces informations. Pour cela, vous allez exploiter les clés étrangères.

Dans un premier temps, ajoutez un nouvel organisme pathogène à la table organisme_mo avec les valeurs suivantes :

• id : PP_INRA-310_V1
• genre : Phytophthora
• espèce : parasitica
• nom : P. parasitica

Ensuite, effectuez la requête suivante :

SELECT *
FROM manip, organisme_mo

Vous constaterez que chaque ligne de la première table est associée à chaque ligne de la 2ème. Il s'agit du produit de 2 tables.

Pour n'obtenir que les lignes pour lesquelles le pathogène de la manip correspond au pathogène de la table organisme_mo, il faut ajouter la contrainte que ces attributs aient même valeur :

SELECT *
FROM manip, organisme_mo
WHERE manip.id_mo=organisme_mo.id

Cela s'appelle faire une jointure entre ces 2 tables. Une écriture plus lisible de la même requête est la suivante :

SELECT *
FROM manip JOIN organisme_mo ON (manip.id_mo=organisme_mo.id)

Essayez d'écrire les requêtes permettant d'obtenir les informations suivantes :

• Générer le fichier de départ
• Images et mesures associées à un accession (HM020 par exemple)
• Afficher les informations sur la boite, et le brunissement à 15 et 21 jours en triant sur le pourcentage de brunissement
• Le nombre de racines secondaires par accession résistant (dont le pourcentage de brunissement est inférieur à 100%)
• Distribution du poids des plantes résistantes inoculées
• Afficher les plantes mortes qui ont leurs cotylédons complètement jaunis.

Annexes

• File:BD Resistance Schema.sql
• File:BD Resistance Dump.sql
• Media:BD_Resistance_Model.png