TP BIOINFORMATIQUE Bases de Données relationnelles

TP BIOINFORMATIQUE
Bases de Données relationnelles
Objectif : Construire et utiliser une base de données relationnelle simple.
Déroulement : Ce TP se déroulera sur 2 séances.
1. Modélisation simplifiée sous forme de tables de la structure de la base de données.
2. Création des tables et stockage des données.
3. Mises à jour et requêtes dans la base de données.
Documents utiles :
- Manuel d’utilisation (si nécessaire) de MySQL
- en anglais, avec recherche par mot-clés http://www.mysql.org/doc/
Modélisation sous forme de tables de la structure des
entrées de SWISS-2DPAGE
A partir de l’analyse des entrées P11604 et P04406 de SWISS-2DPAGE, déterminer la meilleure
décomposition pour structurer la base de données SWISS-2DPAGE en tables.
http://world-2dpage.expasy.org/swiss-2dpage/
Création des tables et stockage des données
Préliminaire
TOUTES les lignes de commandes MySQL se terminent par un point-virgule.
Les noms de commande et mot-clés de MySQL ne sont pas sensibles à la casse, mais pour
les différencier dans la table qui suit, ils sont en majuscules.
En revanche, les noms de bases de données, de tables, de colonnes des tables sont
sensibles à la casse.
Les instructions entre '[' et ']' sont optionnelles.
Sélectionner une base de données, en connaître la structure,

Créer une table, insérer et modifier des données
Commande Signification
SHOW DATABASES Liste des bases de données disponibles
USE nombase sélectionne la base de données 'nombase'
SHOW TABLES Liste des tables disponibles pour la base
sélectionnée
DESC[RIBE] nomtable Décrit la table 'nomtable' (donne les noms de
colonnes, type, valeur par défaut...)
CREATE TABLE nomtable (col1 TYPE1,
col2 TYPE2, ...) créé la table 'nomtable', avec des colonnes col1, col2, ... de type respectif TYPE1,
TYPE2, ... Principaux types que l'on
utilisera - INTEGER, FLOAT, CHAR(n)
avec 1<=n<=255, DATE (tous les types
disponibles)
INSERT INTO nomtable [(col1, col2,...)]
VALUES ('valeur1', 'valeur2', ...) Insère un enregistrement dans la table 'nomtable' avec col1='valeur1',
col2='valeur2', ... Remarque: si l'on ne précise
pas les colonnes à initialiser après le nom de la
table, toutes les valeurs des colonnes doivent
être données, dans l'ordre établit par la
commande CREATE.
LOAD DATA [LOCAL] INFILE
'chemin/complet/nomfichier.txt' INTO TABLE
nomtable [FIELDS [TERMINATED BY '\t']
[[OPTIONALLY] ENCLOSED BY'']
[ESCAPED BY '\\' ]] [LINES TERMINATED
BY '\n'] [IGNORE number LINES]
Insère les données du fichier texte
'chemin/complet/nomfichier.txt' dans la table
'nomtable'. Remarque - le fichier texte
contient un enregistrement par ligne, les
colonnes étant séparées par des tabulations
DROP TABLE nomtable supprime la table 'nomtable' et tout son
contenu
DELETE FROM nomtable [WHERE
condition] supprime de la table 'nomtable' tous les enregistrements [respectivement: ceux
satisfaisant 'condition']
UPDATE nomtable SET
col1='valeur1',col2='valeur2', ... [WHERE
condition]
mise à jour de la table 'nomtable' avec les
nouvelles valeurs col1='valeur1',
col2='valeur2', ... pour tous les enregistrements
[Respectivement: pour ceux satisfaisant
'condition']
SELECT * FROM nomtable [WHERE
condition] affiche toutes les lignes de la table 'nomtable' [respectivement: celles satisfaisant 'condition']

Pour MySQL en ligne de commande seulement (ceux qui l’ont installé sur leurs machines),
les autres sur c:\Program Files\easyphp\mysql\data\ :
Sur votre machine, explorer les répertoires '/mysql/data' :
'/mysql/data' contient un sous-répertoire pour chaque base de données du serveur. Chacun de ces
sous-répertoires contient pour chaque table de la base de données, 3 fichiers binaires:
1. 'nomtable.frm': définition de la table
2. 'nomtable.MYD': données de la table
3. 'nomtable.MYI': index de la table
Getting Started – MySQL via EasyPHP :
Réaliser les opérations suivantes – MySQL en ligne ou bien via l’interface EasyPHP :
1. Créer une base de données 'nombase'
2. Lister les tables de votre base de données
3. Créer la table 'espece', ayant 3 colonnes: code (5 caractères), organisme (50
caractères), classification (255 caractères)
4. Vérifier la structure de cette table
5. Insérer la ligne suivante dans la table 'espece':
HUMAN
Homo sapiens (Human)
Eukaryota; Metazoa; Chordata;Craniata; Vertebrata; Mammalia; Eutheria; Primates;
Catarrhini;Hominidae; Homo
6. Vérifier que le contenu de la table 'espece' est bien conforme à ce que vous attendez.
7. Créer un fichier 'espece.txt' (dans votre répertoire personnel) avec le reste des données
de la table 'espece' (séparer les colonnes par des tabulations) :
MOUSE Mus musculus (Mouse) Eukaryota;Metazoa;Chordata;Craniata;Vertebrata;
Mammalia; Eutheria; Rodentia;Sciurognathi; Muridae; Murinae; Mus
ECOLI Escherichia coli Bacteria; Proteobacteria;
gamma subdivision; Enterobacteriaceae; Escherichia
DICDI Dictyostelium discoideum (Slime mold) Eukaryota;
Dictyosteliida; Dictyostelium
YEAST Saccharomyces cerevisiae (Baker's yeast)
Eukaryota; Fungi; Ascomycota; Hemiascomycetes;
Saccharomycetales; Saccharomycetaceae; Saccharomyces
8. Insérer les données du fichier 'espece.txt' dans la table 'espèce'
9. Vérifier le contenu de la table 'espèce'

10. Créer la table 'release', ayant 3 colonnes: code (entier), rel_num (2 caractères),
rel_date (date)
11. Vérifier sa structure
12. Insérer dans la table 'release' les données du fichier 'release.txt' présent sur votre
bureau
13. Vérifier le contenu de la table 'release'
14. Modifier les données, si nécessaire
- Pour charger les données des autres tables, vous utiliserez en interactif la commande
LOAD TABLE à partir des fichiers de données (la commande importer dans EasyPHP),
respectivement pour chaque table – par exemple le fichier ‘espece.txt’ sert à charger la
table espèce, ‘entries.txt’ pour la table entries, etc.
Des fichiers de commandes vous seront fournis :
- Le fichier 'createtables.txt' contient les commandes à exécuter pour créer les différentes
tables décomposant SWISS-2DPAGE. Visualiser ce fichier, puis l'utiliser pour créer les
tables en mode batch (i.e. sql).
Mises à jour et requêtes dans la base de données
Commande Signification
SELECT * [col1,col2 FROM nomtable
[WHERE condition] Affiche toutes les colonnes [respectivement col1 et col2] et toutes les lignes de la table
‘nomtable’ [respectivement : les lignes
satisfaisant ‘condition’]
ALTER TABLE nomtable modifications Modifie la structure de la table ‘nomtable’
selon ‘modifications’
Requêtes dans une table
1. Lister les espèces par ordre alphabétique
2. Lister les espèces eucaryotes
3. Lister les espèces de mammifères
4. Compter le nombre de protéines dans la base 'nombase'
5. Compter le nombre de protéines humaines dans la base 'nombase'
6. Afficher le ID et la description des protéines de l'espèce Dictyostelium discoideum,
dans l'ordre alphabétique de la description
7. Combien de protéines (gene name dans swiss2Dpage) ont plusieurs ACs?
Afficher ces protéines
8. Rechercher les références disponibles pour l'auteur 'HOCHSTRASSER'. Afficher
pour ces références le numéro Medline, les 50 premiers caractères des auteurs, et
la localisation; classer par ordre alphabétique de la localisation.
9. Combien existe-t-il de références dont le premier auteur est 'SANCHEZ J.-C.' ?
10. Afficher les 50 premiers caractères des auteurs, le titre et la localisation des
références où les auteurs 'SANCHEZ' et 'TONELLA' apparaissent conjointement

11. Lister les releases par ordre chronologique
12. Afficher le nombre de protéines identifiées pour chaque master
13. Afficher les différentes bases de données cross-référencées par SWISS-2DPAGE,
14. afficher pour chacune le nombre de cross-références disponibles
15. Afficher le nombre de spots identifiés pour les masters de souris, les classer dans
l'ordre croissant
16. Afficher le nombre de releases par année
17. Compter le nombre de protéines par espèce
Requêtes dans plusieurs tables
1. Lister les références de l'entrée P00938. Afficher le rang et la localisation de ces
références dans leur ordre d'apparition dans l'entrée SWISS-2DPAGE
2. Donner la liste des espèces des protéines identifiées par 'MICROSEQUENCING'
3. Quelles sont les espèces étudiées par l'auteur 'YAN' ?
4. Compter le nombre de protéines créées à la release 10. Donner la répartition par espèce
5. Donner la répartition par espèce (afficher l'organisme) du nombre de protéines créées
depuis 1999
6. Quelles sont les protéines identifiées dans les masters ECOLI5-6 et ECOLI4.5-5.5 ?
7. Quelles sont les protéines ayant une cross-référence dans les bases de données PHCI-
2DPAGE et SIENA-2DPAGE ?
8. Même requête que précédemment, mais afficher le AC des protéines dans l'ordre
alphabétique des AC.
Questions « Challenge »
9. Quelle espèce est la mieux représentée dans swiss2DPage (celle qui a le plus grand
nombre d’entrées)
10. Afficher le nombre de publications par protéine
11. Quelles protéines sont fortement citées
12. Quelles protéines sont exprimées dans toutes les espèces
13. Quelles protéines sont largement exprimées dans les gels de swiss2Dpage (celles qui ont
un maximum de spots, tous gels confondus)