Correction Première Partie : Modélisation sous forme de tables de la

TP Bioinformatique – Correction
Première Partie : Modélisation sous forme de tables
de la structure des entrées de SWISS-2DPAGE
Modélisation conceptuelle avec le modèle Entité-Association
Modèle de données sous forme de tables relationnelles

Description des tables

Deuxième Partie : Création des tables et stockage
des données
1. Lister les bases de données existantes
mysql>show databases;
2. Lister les tables de la base de données 'mysql'
mysql>use mysql
mysql>show tables;
3. Lister les colonnes de la table 'user'
mysql>desc user;
4. Afficher les lignes de la table 'user'
mysql>select * from user;
5. Mêmes questions pour la table 'db'
mysql>desc db;
mysql>select * from db;
6. Lister les tables de votre base de données 'nombase'
mysql>use inra2

mysql>show tables;
7. Créer la table 'espece', ayant 3 colonnes: code (5 caractères), organisme (50
caractères), classification (255 caractères)
mysql>create table espece(code varchar(5) primary key not null, organisme
varchar(50), classification varchar(255));
8. Vérifier la structure de cette table
mysql>desc espece;
9. Insérer la ligne suivante dans la table 'espece': HUMAN Homo sapiens (Human)
Eukaryota; Metazoa; Chordata; Craniata; Vertebrata; Mammalia; Eutheria; Primates;
Catarrhini; Hominidae; Homo
mysql> insert into espece values("HUMAN", "Homo sapiens (Human)","Eukaryota,
Metazoa; Chordata; Craniata; Vertebrata; Mammalia; Eutheria; Primates; Catarrhini;
Hominidae; Homo");
10. Vérifier que le contenu de la table 'espece' est bien conforme à ce que vous attendez
mysql> select * from espece;
12. Insérer les données du fichier 'espece.txt' dans la table 'espece'
mysql> LOAD DATA LOCAL INFILE "../inra/espece.txt" INTO TABLE espece;
13. Vérifier le contenu de la table 'espece'
mysql> select * from espece;
Troisième Partie : Mises à jour et requêtes dans la
Base de données
Requêtes sur une table
1. Lister les espèces par ordre alphabétique
mysql> select distinct code_espece from entries order by code_espece asc;
2. Lister les espèces eucaryotes
mysql> select code from espece where classification like "%Eukaryota%";
3. Lister les espèces de mammifères
mysql> select code from espece where classification like "%Mammalia%";
4. Compter le nombre de protéines
mysql> select count(prot_name) from entries;
5. Compter le nombre de protéines humaines
mysql> select count(prot_name) from entries where code_espece like "%HUMAN%";
6. Afficher l'accession number (AC) et la description des protéines de l'espèce
Dictyostelium discoideum, dans l'ordre alphabétique de la description

mysql> select ac, description from entries where code_espece like "%DICDI%"
order by description asc;
7. Combien de protéines ont plusieurs ACs? Afficher ces protéines
mysql> select count(AC), prot_name from entries group by AC having count(*) >1;
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
mysql> select medline, left(auteurs, 50), localisation from ref where auteurs like
'%HOCHSTRASSER%' order by localisation asc;
9. Combien existe-t-il de références dont le premier auteur est 'SANCHEZ J.-C.' ?
mysql> select count(*) from ref where auteurs like 'SANCHEZ%';
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
mysql> select left(auteurs,50), titre, localisation from ref where auteurs like
"%SANCHEZ%" and auteurs like "%TONELLA%";
11. Lister les releases par ordre chronologique
mysql> select * from release order by rel_date asc;
12. Afficher le nombre de protéines identifiées pour chaque master
mysql> select count(*) from master group by mt_name;
13. Afficher les différentes bases de données cross-référencées par SWISS-2DPAGE
mysql> select distinct dbname from crossref;
14. Afficher pour chacune le nombre de cross-références disponibles
mysql> select dbname, count(id1) from crossref group by dbname;
15. Afficher le nombre de spots identifiés pour les masters de souris, les classer dans
l'ordre croissant
mysql> select count(spot) from spots where mt_name like "%MOUSE%" group by
mt_name as c order by mt_name asc ;
16. Afficher le nombre de releases par année
mysql> select year(rel_date), count(*) from release group by year(rel_date);
17. Compter le nombre de protéines par espèce
mysql> select code_espece, count(prot_name) from entries group by code_espece;
Requêtes dans plusieurs tables
18. Afficher le rang et la localisation des références de l'entrée P00938
mysql> select rank,localisation from coderef,ref where ac="P00938" and
ref.ref_num=coderef.ref_num ;