TIPI DEI CAMPI
Abbiamo concluso la lezione precedente scoprendo che in un database relazionale
SQL ogni campo di una tabella può contenere solo valori di un tipo ben preciso,
oppure il simbolo speciale NULL, se per quel record il campo non ha un valore
definito.
Ora cominceremo a vedere i tipi dei dati (i più utilizzati)
BIOINFO3 - Lezione 3
1
Bit e Byte
Il funzionamento dei computer si
elettrico all`interno dei milioni di
l`attivita` di un computer consiste
combinazione e propagazione di
controllo del clock.
basano sulla presenza assenza di segnale
circuiti che lo compongono: in definitiva
essenzialmente nella continua rapidissima
questi segnali al propio interno, sotto il
Tutti i dispositivi di memoria rispecchiano questo dualismo di segnale
(presente/assente, acceso/spento, aperto/chiuso, on/off, 0/1):
RAM microcelle carica/scarica
ROM contatti elettrici presenti/assenti
dischi elementi in grado di magnetizzarsi/smagnetizzarsi
CD microelementi riflettenti/opachi
BIOINFO3 - Lezione 3
2
L`elemento minimo di memoria e` quindi sempre costituito da un`unita`
binaria (cioe` un`unita` che si puo` trovare in solo in due possibili stati),
e viene detto bit (Binary digiT). I due stati sono di solito rappresentati
con le cifre 0 o 1.
Per poter esprimere informazione piu` articolata si considerano gruppi
di 8 bit, che formano un byte.
Un byte puo` assumere tutti gli stati da 00000000 a 11111111 , per un
totale di 256 combinazioni (2x2x2.. 8 volte=256).
Il significato di ogni byte del file dipende dalla natura dei dati.. (es
carattere di testo rappresentato con un byte in accordo con una tabella
convenzionale detta ASCII)
BIOINFO3 - Lezione 3
3
NUMERI INTERI
INT
Permette di memorizzare un numero intero (in uno spazio di
bit (0/1)
4byte=32 bit)
byte (8 bit)
•Con segno (+/-)
bit del segno (0=+/1=-)
31 bit
Valori da –2.147.483.648 (-231) a 2.147.483.647 (231-1)
•Senza segno (si usa la parola chiave UNSIGNED)
Valori da 0 a 4.294.967.295 (232-1)
20=1
232
11111111
BIOINFO3 - Lezione 3
11111111
11111111
11111111
4
NUMERI INTERI
In MySQL esistono delle varianti dei numeri interi base che
permettono di risparmiare spazio se i numeri interi da inserire in
un campo di tipo int sono sempre piccoli o di riservare più spazio
se i valori sono molto grandi.
TINYINT utilizza solo 1 byte (=8 bit)
Valori da –128 a 127 (da 0 a 255 se tinyint unsigned)
SMALLINT utilizza 2 byte (=16 bit) (-32768 a 32767, 0 65535)
MEDIUMINT utilizza 3 byte (-8388608 a 8388607, 0 16777215)
BIGINT utilizza 8 byte
(-9223372036854775808 a 9223372036854775807,
0 18446744073709551615)
BIOINFO3 - Lezione 3
5
NUMERI REALI
Numeri reali in virgola mobile (notazione scientifica)
FLOAT (non puo` essere unsigned)
Numeri in virgola mobile a precisione semplice (4 byte)
da –3.402823466E+38 a –1.17549435E-38 (negativi)
0
da 1.17549435E-38 a 3.402823466E+38 (positivi)
DOUBLE PRECISION, REAL
da –1.7976921248623257E+308 a –2.2250738585072014E-308 (-)
0
Da 2.2250738585072014E-308 a 1.7976921248623257E+308 (+)
BIOINFO3 - Lezione 3
6
NUMERI REALI
Numeri reali in virgola fissa (uso tipico)
d cifre decimali
DECIMAL
decimal(n,d)
n cifre complessive
Se i numeri da utilizzare hanno sempre lo stesso formato (lo
stesso numero di cifre decimali) si utilizza decimal
Ad esempio se si dovessero memorizzare dei prezzi in euro
1 2 . 9 5
potremo utilizzare decimal(8,2)
Se i numeri sono invece estremamente eterogenei e comunque in
notazione scientifica si usa float (o double precision)
BIOINFO3 - Lezione 3
7
STRINGHE DI CARATTERI
Anche in questo caso esistono due possibilità, a seconda che i dati
siano sempre della stessa lunghezza, oppure abbiano lunghezza
variabile
CHAR
n caratteri complessivi
char(n)
Nel caso i dati siano sequenze di caratteri alfanumerici (lettere,
numeri, punteggiatura, simboli), tutte della stessa lunghezza n, si
utilizza char(n), dove n è compreso tra 1 e 255. (ogni n un bytes)
Ad esempio, se in una colonna volessimo memorizzare solo dei
codici fiscali utilizzeremo char(16) C N N N C L 6 6 E 1 5 L 7 3 6 B
Se i dati sono più corti abbiamo uno “spreco” di spazio
BIOINFO3 - Lezione 3
8
STRINGHE DI CARATTERI
Se la lunghezza delle stringhe di caratteri è variabile si usa
al massimo n caratteri ( 255)
l
VARCHAR
varchar(n)
l caratteri ( n)
In questo caso il sistema utilizza un byte supplementare per
memorizzare la lunghezza l effettiva del dato. La lunghezza
effettiva del dato dovrà essere sempre minore o uguale ad n. Ed n
in ogni caso non può superare 255.
Se volessimo, ad esempio, memorizzare dei nomi (che sono
tipicamente di varie lunghezze) potremo usare varchar(20)
6 N I COL A
Ovviamente all’utente non viene mostrato il primo byte, che
viene utilizzato solo dal sistema per trattare il dato vero e proprio
BIOINFO3 - Lezione 3
9
STRINGHE DI CARATTERI
Nel caso si debbano memorizzare stringhe di caratteri più lunghe
di 255 (è il tipico caso delle sequenze biologiche!) si usa
?
l
2 byte
TEXT
l caratteri ( n)
E’ equivalente a varchar però usa 2 byte (anziché 1) per
memorizzare la lunghezza effettiva del dato.
Quanti caratteri possono stare al massimo in un campo text?
In 2 byte ci sono 16 bit, quindi al massimo 216-1, cioè 65535
Per stringhe ancora più lunghe si usa
LONGTEXT
Utilizza 4 byte per memorizzare la lunghezza effettiva del dato
BIOINFO3 - Lezione 3
10
ENUM
E` un tipo di stringa il cui valore e` scelto a partire da una lista
di valori consentiti esplicitamente definiti durante la crazione
della tabella.
enum (‘uno’,’due’,’tre’);
Se viene inserita una stringa non contenuta nell`enum, viene
inserita una stringa vuota. Questa puo` essere distinta da una
stringa vuota perche` la stringa ha il valore numerico 0.
BIOINFO3 - Lezione 3
Valore
Indice
NULL
NULL
“”
0
“uno”
1
‘due’
2
11
DATA E ORA
E’ possibile memorizzare una data
DATE
Formati permessi: YYYY-MM-DD, YY-MM-DD, YYMMDD
Ad esempio 2003-03-05
E’ possibile memorizzare un’ora
TIME
Formati permessi: HH:MM:SS, HHMMSS HHMM
Ad esempio 10:45:37
BIOINFO3 - Lezione 3
12
LA PROGETTAZIONE DI UN DATABASE
Si possono individuare almeno tre fasi sucessive
Aspetto del
mondo reale da
“modellare”
SCHEMA
SCHEMA
SCHEMA
CONCETTUALE
LOGICO
FISICO
DEI DATI
DEI DATI
DEI DATI
ANALISI:
CI PENSA
Individuare le entità
e le loro relazioni
IL DBMS!
Entità e
relazioni
BIOINFO3 - Lezione 3
Tabelle
Implementazione
fisica (file, indice,
programmi di
gestione DB)
13
SCHEMA ESTERNO
Parallelamente allo schema concettuale dei dati in genere è
richiesta anche la progettazione di uno schema esterno.
Lo schema esterno rappresenta l’insieme degli strumenti (il più
user-friendly possibile, cioè utilizzabili, ad esempio attraverso
interfacce grafiche, anche da chi non conosce SQL) che l’utente
finale avrà a disposizione per interagire con il DB (effettuare
ricerche, inserimenti, modifiche o cancellazioni dei dati)
Gli strumenti potrebbero ad esempio essere delle pagine web (es.
Pubmed), con form per inserire (o selezionare) dei criteri di
ricerca
BIOINFO3 - Lezione 3
14
LA PROGETTAZIONE DELLE TABELLE
Individuate le entità da rappresentare si cercano di individuare le
loro relazioni e gli attributi di ciascuna, cioè i vari aspetti da
considerare.
Esempio. La regolazione della trascrizione del DNA.
Potremmo considerare come entità: sequenze di DNA, geni,
elementi regolatori, siti di legame per fattori di trascrizione
Un possibile schema delle relazioni tra le entità potrebbe essere
Relazione 1:molti
Ad una sequenza di DNA possono
corrispondere molti elementi regolatori
Relazione 1:1
Ad un sito di legame per
fattori di trascrizione
corrisponde un record di
TRANSFAC
BIOINFO3 - Lezione 3
15
IDENTIFICAZIONE DEI CAMPI
La TABELLA sequenze-DNA potrebbe ad esempio avere campi:
id (una stringa di caratteri)
sequenza (stringa di almeno 65535 caratteri, ma meno del cromosoma più lungo e sicuramente
meno di 3G)
descrizione (stringa di caratteri, meno di 255)
TABELLA elementi-regolatori:
id (una stringa di caratteri)
sequenza-dna (l’id di una sequenza-DNA)
posizione-iniziale (numero, molto grande)
posizione-finale (numero, molto grande)
TABELLA siti-legame:
id (una stringa di caratteri)
elemento-regolatorio (l’id di un elemento-regolatorio)
posizione-iniziale (numero)
posizione-finale (numero, molto grande)
codice-TRANSFAC (stringa caratteri)
BIOINFO3 - Lezione 3
16
ESEMPIO CONCRETO
Per chiarirsi le idee pensiamo ad una sequenza di DNA che chiamiamo Sc-chr1.
Avremo un record della tabella sequenze-DNA avente il campo id contenente Sc-chr1;
il campo sequenza conterrà l’intera sequenza e la descrizione ad esempio “cromosoma
1 del Lievito”. Nella sequenza Sc-chr1 supponiamo di avere individuato due elementi
regolatori: er1 e er2 rispettivamente nei tratti di sequenza 100-180 e 15400-15500.
Nell’elemento er1 supponiamo di avere individuato tre siti di legame slA, slB e slC nelle
posizioni rispettivamente 1-8, 19-20 e 65-80
id
sequenza
descrizione
Sc-chr1
ACTAGCTGCT....
Cromosoma 1 del Lievito
id
sequenza-DNA
posizione-iniziale
posizione- finale
er1
Sc-chr1
100
180
er2
Sc-chr1
15400
15500
id
elemento-regolatorio
posizione-iniziale
posizione- finale
slA
er1
1
8
slB
er1
19
20
slC
er1
65
80
BIOINFO3 - Lezione 3
codice-TRANSFAC
17
CREAZIONE DI UNA TABELLA
Una volta effettuata l’analisi (creato uno schema concettuale dei
dati, individuate le entità e i loro attributi) e progettato (su carta)
lo schema logico dei dati si può chiedere al DBMS la costruzione
fisica del database.
Si usa il comando CREATE TABLE per creare ciascuna tabella
create table <nome tabella>(
<definizione campo1>,
<definizione campo2>,
……………
<definizione campon>
)
Dove <definizione campo>::= <nome colonna> <tipo colonna>
BIOINFO3 - Lezione 3
18
UN ESEMPIO
Creare la tabella sequenze-DNA per memorizzare e gestire
sequenze di DNA
create table sequenze-DNA(
id varchar(20) primary key,
sequenza longtext,
descrizione varchar(255)
)
Si può notare la definizione del campo id come chiave della
tabella. Potrebbe succedere anche che come chiave sia necessario
scegliere una combinazione di due o più campi, ma noi non
affronteremo l’argomento.
BIOINFO3 - Lezione 3
19
ALTRO ESEMPIO
Progettare il comando di creazione di una tabella esami. Per ogni
esame si avrà un codice numerico che lo identifica, una materia,
una data, un’ora, un’aula e il piano dell’aula. Provate voi ora!
BIOINFO3 - Lezione 3
20
ALTRO ESEMPIO
Progettare il comando di creazione di una tabella esami. Per ogni
esame si avrà un codice numerico che lo identifica, una materia,
una data, un’ora, un’aula e il piano dell’aula.
create table esami(
id int unsigned primary key,
materia varchar(50),
datae date,
ora time,
aula char(1),
piano char(1)
)
N.B. Uso datae perché data è una parola riservata di MySQL
BIOINFO3 - Lezione 3
21
RIEPILOGO
•Tipi dei campi: numeri interi e reali, stringhe, date e ore
•Progettazione di un database: schema concettuale, schema
logico e schema fisico
•Lo schema esterno
•Progettazione tabelle
•Creazione di una tabella: comando CREATE
BIOINFO3 - Lezione 3
22