create table est(
gi int unsigned primary key auto_increment,
acc char(8)not null,
nome varchar(20),
descrizione varchar(255),
sequenza text
);
insert into est values(‘’,’BU275994’,’ Cr_Emb_01B02_TEXF1’,’’,’ GGGGAATAATGCTC’);
BIOINFO3 - Lezione 08
1
QUERY
Ci interessano tutti i nomi degli enzimi ordinati alfabeticamente
Vogliamo vedere i nomi degli enzimi, ordinati per lunghezza del
nome e, a parità di lunghezza, alfabeticamente
BIOINFO3 - Lezione 08
2
QUERY
Vorremmo vedere tutti i nomi degli enzimi e la corrispondente
lunghezza della sequenza, ordinati per lunghezza decrescente e, a
parità di lunghezza, in ordine alfabetico
BIOINFO3 - Lezione 08
3
QUERY
Ci interessa sapere il numero degli enzimi che tagliano in
posizione 1
BIOINFO3 - Lezione 08
4
QUERY
Ci interessa sapere il nome e la posizione di taglio degli enzimi
che tagliano fuori della sequenza
BIOINFO3 - Lezione 08
5
QUERY
Come si chiama l’enzima che riconosce la sequenza GGATCC
Ci interessano i nomi e le sequenze riconosciute degli enzimi che
riconoscono sequenze contenenti basi indefinite, ordinati
alfabeticamente secondo il nome
BIOINFO3 - Lezione 08
6
BACKUP
Per sicurezza, vogliamo effettuare un dump del database
BIOINFO3 - Lezione 08
7
Tabella “orfconsensus”
Tabella “orfannotation”
BIOINFO3 - Lezione 08
8
Vogliamo selezionare il campo name dalla tabella “orfconsensus” e il campo
description dalla tabella “orfannotation” relativo ai geni sul chromosoma 1 che
vanno dalla base 100 alla base 5000
select orfconsensus.name, orfannotation.description,
orfconsensus.chromStart, orfconsensus.chromEnd
from orfconsensus,orfannotation
where orfconsensus.chromStart>=100 and orfconsensus.chromEnd<=5000
and orfconsensus.name=orfannotation.name and
orfconsensus.chrom='chr1';
BIOINFO3 - Lezione 08
9
Vogliamo selezionare il campo name, chromStat, chromEnd dalla tabella
“orfconsensus” e il campo description dalla tabella “orfannotation” relativo ai primi
10 geni sul chromosoma 1 che hanno una descrizione che contiene la parola
“transporter”, ordinati per posizione sul cromosoma.
select orfconsensus.name, orfannotation.description, orfconsensus.chromStart,
orfconsensus.chromEnd
from orfconsensus,orfannotation
where orfannotation.description like '% transporter %' and
orfconsensus.name=orfannotation.name and orfconsensus.chrom='chr1' order by
orfconsensus.chromStart limit 10;
BIOINFO3 - Lezione 08
10
REQUISITI UTENTE
Parallelamente (ma spesso è la prima cosa da fare!) bisogna
pensare allo schema esterno, cioè al progetto di una interfaccia
che permetta agli utenti finali di interagire (tipicamente
attraverso INTERNET) in modo intuitivo e semplice (non di
certo attraverso SQL!) con il database e la tabella enzimir in
particolare.
E’ importante che siano chiare quali saranno le esigenze degli
utenti (REQUISITI UTENTE)
Nel nostro esempio, tipicamente un utente vorrà poter
visualizzare i record del database impostando dei criteri di
ricerca. Si potrebbe poi decidere che gli utenti possano anche
cancellare o modificare i record oppure inserirne di nuovi
BIOINFO3 - Lezione 08
11
SCHEMA ESTERNO
Pagina
WEB
iniziale
(MENU)
ENZIMI R.
nome
sequenza
Query
Inserimento
Tabella con i
record selezionati
nome sequenza posiz. sito
posiz. sito
Inserimento
da file
RICERCA
Form di
ricerca
nome
sequenza
file
TROVA
posiz. sito
INSERISCI
Form per
l’inserimento
di un record
BIOINFO3 - Lezione 08
INSERISCI
Form per
l’inserimento
di un file di
record
nome
sequenza
nome
sequenza
posiz. sito
posiz. sito
MODIFICA
Form per la
modifica di
un record
CANCELLA
Form per la
cancellazione
di un record
12
SCHEMA ESTERNO
•Pagina WEB iniziale (MENU)
Funziona da menu, cioè permette all’utente di scegliere quale operazione eseguire tra quelle
disponibili
•Form di ricerca
Permette di inserire in alcune caselle di input, corrispondenti ai vari campi, i valori richiesti per i
record da visualizzare (o di selezionare i valori da un insieme già predisposto)
•Tabella con i record selezionati
Elenca, sotto forma tabellare, i record selezionati dalla query
•Form per la modifica di un record
Permette di modificare un record selezionato, sostituendo direttamente il valore dei campi
•Form per la cancellazione di un record
Permette di cancellare un record selezionato
•Form per l’inserimento di un record
Permette di inserire un record, digitando direttamente i valori dei campi nelle corrispondenti
caselle
•Form per l’inserimento di un file di record
Permette di inserire più record, leggendoli da un file, il cui nome è inserito in una casella di input
BIOINFO3 - Lezione 08
13
ESERCIZIO SQL
Supponiamo che esista una tabella di sequenze EST creata con il seguente
comando
create table est(
gi int unsigned primary key,
acc char(8),
nome varchar(20),
descrizione varchar(255),
sequenza text
)
BIOINFO3 - Lezione 08
14
ESERCIZI SQL
Selezionare tutti i nomi delle est in ordine alfabetico
select nome from est order by nome
Selezionare le sequenze delle est che non contengono nucleotidi
indefiniti (N)
select sequenza from est
where not (sequenza like ‘%N%’)
OPPURE
select sequenza from est where sequenza not like ‘%N%’
Selezionare tutte le sequenze est e la loro lunghezza ordinate per
lunghezza crescente
select sequenza,length(sequenza) from est
order by length(sequenza)
BIOINFO3 - Lezione 08
15
ESERCIZI SQL
Selezionare gi e sequenza delle est che non contengono N e sono
di lunghezza superiore a 200 basi, ordinate per gi
select gi, sequenza from est
where sequenza not like ‘%N%’
and length(sequenza)>200
order by gi
BIOINFO3 - Lezione 08
16
LE PROSSIME LEZIONI
Nelle prossime lezioni impareremo a progettare delle pagine web
contenenti delle form in grado di ricevere degli input dall’utente
Impareremo successivamente anche a scrivere dei semplici
programmi per interpretare gli input ricevuti, interagire con il
database e restituire all’utente delle pagine web con i risultati
opportunamente formattate
BIOINFO3 - Lezione 08
17
RIEPILOGO
•Progettazione di un database di enzimi di restrizione (creazione
di un modello informatico di un aspetto ben preciso e limitato del
mondo reale)
•Analisi
•Schema concettuale
•Schema logico
•Implementazione
•Schema esterno
•Query a volontà...
BIOINFO3 - Lezione 08
18