basi di dati biologiche

BASI DI DATI BIOLOGICHE
Corso di Bionforma;ca
–––––––––––––––––
Corso di Laurea in Scienze Biologiche
Prof. R. Oliveto -­‐ A.A. 2011/2012
lunedì 19 marzo 12
1
Introduzione (1)
I database sono ormai una componente fondamentale della vita di tuI i giorni: molte delle nostre più banali aIvità ci portano ad interagire con qualche ;po di database
Prenotazioni di alberghi, biglieI aerei, etc
Telepass / Viacard
Richiesta di documen;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
2
Introduzione (2)
Le aIvità appena descriQe coinvolgono applicazioni di database tradizionali, avendo a che fare principalmente con tes; e numeri
I progressi tecnologici, però, stanno aprendo la strada a nuove interessan;ssime applicazioni di database:
I Database Mul;mediali (immagini, videoclip, suoni, ecc)
I sistemi informa;vi geografici
I sistemi Data Warehouse
I motori di ricerca
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
3
La nozione di DataBase (1)
Una Base di Da; (Database o DB) è una collezione di da; correla;
Esempio: una rubrica telefonica creata usando Access, Paradox o Excel, ecc.
Per “da;” si intendono dei faI no;, con un significato implicito, che possono essere memorizza;
Esempio: nome, cognome, indirizzo e telefono di un abbonato telefonico
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
4
La nozione di DataBase (2)
Un “database” deve presentare le seguen; proprietà:
Rappresenta alcuni aspeI del mondo reale, deQo miniworld o Universo del Discorso (UOD). Cambiamen; al miniworld sono riflessi nel database
E’ una collezione di da1 logicamente correla1 con qualche significato inerente
Un assor;mento casuale di da; non può correQamente essere considerato un database.
E’ progeQato, costruito e riempito di da; per un u1lizzo specifico. Ha una ;pologia ben definita di uten1 ed è realizzato per delle applicazioni a cui tali uten; sono interessa;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
5
Banche da; biologiche
Una banca da1 biologica raccoglie informazioni e da; derivan; dalla... le9eratura
da analisi effe9uate sia in laboratorio sia aQraverso analisi bioinforma;che
Ogni banca da; biologica è caraQerizzata da un elemento biologico centrale che cos;tuisce l’oggeQo principale intorno al quale viene costruita la entry della banca da;
Esempi di elemento centrale:
1) la sequenza nucleo;dica di DNA nelle banche da; di acidi nucleici
2) promotore nelle banche da; di promotori eucario;ci
Ciascuna entry raccoglie tuQe le informazioni che caraQerizzano l’elemento centrale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
6
Raccolte di da; biologici
Raccolte di dati biologici
Libri
- 1960 Margaret Dayhoff, collezione di proteine (NBRF)
Floppy
disk
- 1977 PDB Strutture di macromolecole
Cd-rom
- 1980 EMBL (Heidelberg) acidi nucleici
1982 GenBank (NCBI) acidi nucleici
- 1991 EST (Expressed Sequence Tags)
Internet
WWW
- 1996 Genomi
- 1998 Trascrittomi
.
Oggi: migliaia di collezioni: funzioni, famiglie
di proteine, motivi, vie metaboliche...
Riccardo Percudani
Corso banchedati.odp
di Bioinformatica - Basi di dati biologiche
File:
lunedì 19 marzo 12
02/03/04
R. Oliveto
7
Tipologie di banche da;
Primarie o derivate
Nelle banche da; primarie sono presen; solo le informazioni minime necessarie da associare ai da; per iden;ficarli al meglio
Le banche da1 derivate contengono invece insiemi di da; omogenei che possono derivare da banche da; primarie, ma rivis; e annota; con varie informazioni che danno un valore aggiunto alla banca da; stessa
Non Curate o curate
Le banche da1 non curate contengono i da; grezzi così come sono forni; da chi li ha oQenu;, o con annotazioni da sistemi automa;ci
Le banche da1 curate presentano informazioni che sono verificate, confrontate con quelle di altre banche da;, opportunamente correQe (o per lo meno con segnalazione di possibili errori e confliI con altri da;)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
8
Molecural Biology Database (1)
1380 database organizza; in 14 categorie e 41 soQocategorie
Nel 2004, 548 database
Incremento del 150%
Nel 2011, 1330 database
Incremento del 3%
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
9
Molecural Biology Database (2)
Con;ne un archivio di da1 biomolecolari di vario ;po
Oltre 50 categorie
Ha un’effecitente organizzazione logica di queste informazioni
Che consente un rapido accesso alle informazioni
Fonisce strumen; per accedere alle informazioni
Con interfaccia web
L’informazione proviene da
le9eratura specializzata sull’argomento
analisi effeQuate in laboratorio (in vitro o in vivo)
analisi bioinforma;che (in silico)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
10
Problema;che
L’enorme mole di da; biologici prodoI porta a dover ges;re le seguen; problema;che:
Archiviazione di enormi moli di da1: creare, ges;re e mantenere banche da;
Recupero di informazioni in modo automa;co dalle banche da;
Analisi automa1ca dei da1
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
11
Archiviazione da;
Si prenda come esempio il progeQo GENOMA
Le molecole di DNA sono molecole lineari che, astraendo dalla struQura 3D, possono essere rappresentate come sequenze di caraQeri dell’alfabeto (A, T, C, G) che rappresentano le 4 basi, ovvero adenina (A), citosina (C), guanina (G) e ;mina (T).
Da un punto di vista informa;co per memorizzare 1 caraQere (cioè 1 base) abbiamo bisogno di 1 byte.
Approssima;vamente il genoma umano è lungo 3,2 * 109 caraQeri (basi). Quindi per memorizzarlo occorrono 3,2 * 109 byte
Siamo nell'ordine dei giga per 1 sola sequenza!
Un DVD Single Layer -­‐ Single Side riesce a memorizzare 4,7 GB
Necessario un tale DVD per una singola sequenza
In un HD da un terabyte (103 Gigabyte) si possono memorizzare circa 300 sequenze
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
12
Recupero di informazioni
Una banca da; priva di modalità di accesso efficien; una “tomba di da;”. Necessario usare tecniche per garan;re un accesso oImale
Solitamente nascoste agli u;lizzatori, hanno molto a che fare con l’organizzazione logica dei da; Ad esempio
Potrebbe essere necessario prevedere interfacce per permeQere agli uten; di leggere e estrarre informazioni
Può essere necessario correlare informazioni contenute in banche da; separate (cross-­‐reference)
Servono techniche per recupera informazioni simultaneamente in diversi archivi di da; distribui; su diversi computer
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
13
Interazione con una BDB
Esistono sistemi di interrogazioni via Web che consentono di recupera informazioni da una BDB
Da semplici interrogazioni per parola chiave a più raffinate interrogazioni Estrazione da1
E’ possibile estrarre da; da una base di da; in deversi forma;
Scaricare un dato biologico estraQo nel formato scelto in locale come file di testo
Estrazione delle informazioni mediante semplici programmi
ScriI in bioperl o biopython, o altri linguaggi Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
14
Perchè sia interessa; alle BDB (1)
Vi sono una serie di strumen; bioinforma;ci che permeQono di ricavarne informazioni.
Essi si sono sviluppa; in base a ques; tre processi biologici fondamentali
la sequenza del DNA determina la sequenza amminoacidica della proteina (mediante il processo della sintesi proteica);
la sequenza aminoacidica determina la struQura tridimensionale della proteina;
la struQura tridimensionale della proteina ne determina la funzione
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
15
Perchè sia interessa; alle BDB (2)
AQraverso le BDB saremo in grado di:
data una sequenza di acidi nucleici o proteica trovare una sequenza simile in banca da;;
data una struQura proteica trovare, in banca da;, una struQura simile ad essa;
data una sequenza proteica prevedere una possibile struQura tridimensionale;
e tanto altro...
Impa9o
possibilità di ricostruire le tappe evolu;ve delle varie specie, incluso l'uomo
possibilità di ricavare caraQeris;che per le varie biomolecole u;li nella progeQazione di nuove molecole e farmaci in seQori diversi, dall'agroalimentare a quello farmaceu;co
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
16
Un po’ di numeri
Esistono più di 1000 differen; database biologici
La grandezza varia da diversi Kb a diversi Gb
DNA: > 10 Gb
Proteine: 1 Gb
StruQure 3D: 5 Gb
altri: più piccoli
Le basi di da; sono in con;nua evoluzione con aggiornamen; giornalieri, mensili o annuali
Alta visibilità grazie al Web (alcune sono anche free) con possibilità di scaricabili in locale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
17
GESTIRE E PROGETTARE UNA BASE DI DATI
Basi di da; Biologiche
Prof. R. Oliveto -­‐ A.A. 2011/2012
lunedì 19 marzo 12
18
Ges;re un DataBase
Un database può essere ges;to manualmente (es. lo schedario di una biblioteca) o aQraverso un elaboratore ele9ronico
Un database computerizzato può essere creato e ges;to o da programmi realizza; “ad hoc” o da un Database Management System (DBMS)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
19
IL DBMS
Un database management system (DBMS) è una collezione di programmi che permeQe di creare e manutenere una base di da;
E’ un souware "general-­‐purpose" che facilita la creazione, costruzione e ges;one di database per differen; applicazioni
Fornisce un modo per memorizzare informazioni in struQure da; efficien;, scalabili e flessibili
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
20
DBMS vs Database
Stessa differenza esistente tra Word (applica;vo) e file .DOC (da;)
Un DBMS è un applica1vo per ges;re database Esempio: MySQL Un database è un insieme di da1 Esempio: file con estensione .MDB
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
21
Funzionalità di un DBMS (1)
Il DataBase Management System è un applica;vo che definizione, creazione e manipolazione di un database
I principali servizi che offre un DBMS sono:
memorizzazione dei da;
organizzazione dei da;
ges;one dei da;
ricerca dei da;
elaborazione dei da;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
22
Funzionalità di un DBMS (2)
Altre funzioni di un DBMS sono:
Validare i da1 controllando che essi siano correI e che rispondano alle regole d’inserimento fissate
Normalizzare il database verificando che non ci siano inu;li duplicazioni di da; e permeQendo di eliminarle senza perdita sostanziale di informazione
Indicizzare i da1 organizzandoli secondo la struQura prestabilita e ordinandoli di conseguenza
Verificare la coerenza dei da; in modo che alla modifica di un dato vengano aggiorna; automa;camente tuI i da; eventualmente dipenden; da esso
Regolare l’accesso agli uten; alla base da; Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
23
Interagire con un DBMS
Un DBMS può ricevere comandi
DireQamente dall’utente in modo interaIvo, tramite par;colari comandi appartenen; ai linguaggi “acceQa;” da quel par;colare DBMS
Tramite un programma scriQo in un linguaggio di programmazione tradizionale che ingloba alcuni comandi appartenen; ai linguaggi “acceQa;” dal DBMS. Ques; linguaggi possono essere raggruppa; in base alle loro funzioni
DDL (Data Descrip1on Language), tramite i quali si definiscono le struQure del database. Si dice cioè come dovrà esse organizzata la base di da;
DML (Data Manipula1on Language), che servono per impar;re comandi di elaborazione dei da;
DCL (Data Control Language), che consente di fornire o revocare agli uten; i permessi necessari per poter u;lizzare i comandi DDL e DML
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
24
Componen; di un DBMS
Tabella di descrizione database, è una tabella in cui è descriQo il modello e le caraQeris;che del database. Tabella delle autorizzazioni, è una tabella in cui sono presen; le informazioni dei vari uten; riguardo ai loro permessi di accesso ai vari da;. Tabella per accesso concorrente, ha la funzione di permeQere la ges;one del traffico di più richieste opera; contemporaneamente sui medesimi da;. Language processor, i comandi vengono ricevu; da questa unità, che ha il compito di meQerli in relazione con le specifiche di definizione del modello (contenute nella tabella di descrizione del database) e trasmeQerli al Database Manager Database Manager, ha il ruolo di ricevere i comandi espressi a livello conceQuale cioè operan; sul modello astraQo dei da; e tradurli in comandi a livello fisico trasmeQendoli al file system del sistema opera;vo non prima di aver faQo gli opportuni controlli sulla tabella di autorizzazione e sulla tabella di accesso concorrente
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
25
Tipologie di DBMS
Piano o non Relazionale
I da; sono contenu; in un’unica tabella
Relazionale
La sua struQura è più complessa di un DBMS Piano perché i da; sono raccol; in più tabelle collegate tra loro
Gerachico
Organizzato secondo uno schema ad albero, cos;tuito da tan; nodi collega; tra loro in maniera gerarchica, ciascuno dei quali con;ene un’informazione
Re1colare
Basato sui grafi, escludendo qualsiasi ;po di gerarchia che colleghi in qualche modo le informazioni
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
26
Database relazionali
Nel Database Relazionale, i da; sono organizza; in più tabelle, che possono essere correlate, ed è possibile estrarre informazioni da più tabelle contemporaneamente
Gli elemen; cos;tu;vi di un Database Relazionale, partendo dal più piccolo, sono
Campi, le singole careQeris;che di un’en;tà del miniworld
Record, insieme di campi che careQerizzano un’en;tà
Tabella, insieme di record e quindi insieme di en;tà File, rappresentazione fisica di una o più tabelle
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
27
I Campi
Un campo è l’unità base di un Database
E’ uno spazio nel Database che con;ene un’unità di informazione, ed è iden;ficato da un nome
Quando si crea un Database è necessario includere un campo per ogni categoria di da; cui si è interessa;
Nome campo
Valore campo
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
28
I record
Un record è cos;tuito da un insieme di campi che riportano informazioni riferite ad un singolo soggeQo Record
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
29
Le tabelle
Una tabella è un elenco che visualizza più record contemporaneamente
Tabella
Più precisamente ogni riga di una tabella del Database è cos;tuita da un record, ognuno dei quali è suddiviso in campi, che cos;tuiscono le colonne
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
30
Le tre fasi della progeQazione
Un Database, come ogni archivio, va progeQato
Le fasi di progeQazione di un Database sono tre:
Fase 1. ProgeQazione Conce9uale
Fase 2. ProgeQazione Logica
Fase 3. ProgeQazione Fisica
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
31
La progeQazione ConceQuale
E’ questa la prima fase di costruzione di un Database, il cui scopo è quello di individuare la stru9ura dei da1 che devono essere “archivia;” e le relazioni tra loro esisten;. Tale fase prevede la costruzione di diagrammi en;tà relazioni (ERD), cos;tui; da 4 elemen; principali
En1tà: un qualsiasi oggeQo conceQuale che caraQerizza la base dei da; in ques;one e che può essere individuato e dis;nto dagli altri
A9ribu1: insieme di valori che caraQerizzano un’en;tà
A9ribu1 chiave: insieme degli aQribu; sufficien; ad iden;ficare univocamente un’en;tà
Relazione: dipendenze o associazioni di interesse informa;vo tra le en;tà rappresentate. Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
32
Esempio ERD
Nome
Cognome
Nome
DataNascita
Cognome
CF
Specializzazioni
Sesso
NumAlbo
Descrizione
Paziente
Biologo
Codice
n
m
Sostiene
Esito
CF
Costo
EsameLaboratorio
n
Attributo chiave
DataNascita
m
Effettua
Data
Paziente
Data
Cognome
Attributo
Entità
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
Sesso
Specializzazioni
Sostiene
Attributo multivalore
Relazione
R. Oliveto
33
La progeQazione logica
Questa è la seconda fase della costruzione di una base di da;, in cui lo schema ER precedentemente costruito viene trasformato nello schema logico relazionale
Il più diffuso schema logico è quello relazionale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
34
Il Data Model Relazionale (1)
Fu proposto da Codd nel 1970 per favorire l’indipendenza dei da; e reso disponibile come modello logico in DBMS reali nel 1981 E’ il modello più diffuso, sia a livello teorico sia commerciale
La forza del modello relazionale è nella sua semplicità e nei solidi formalismi matema;ci su cui si poggia
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
35
Il Data Model Relazionale (2)
Si basa sul conceQo matema;co di Relazione
Le relazioni hanno una rappresentazione naturale per mezzo di tabelle Ciascuna riga rappresenta una collezione di valori di da; rela;
Il database è rappresentato come una collezione di relazioni
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
36
Esempio schema relazionale
Paziente
CF
Cognome
Nome
DataNascita
Sesso
EsamiSostenuti
Paziente
Esame
Data
Esito
Esame
Specializzazioni
Medico
Codice
Descrizione
Costo
Specializzazione
EsamiEffettuati
Medico
Esame
Data
Medico
NumAlbo Cognome
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
Nome
DataNascita
Sesso
R. Oliveto
37
Dal punto di vista matema;co
Siano D1, D2, …, Dn n insiemi
Il prodoQo cartesiano D1 × D2 ×…× Dn, è l’insieme di tuQe le n-­‐
uple ordinate (d1, d2, …, dn) tali che d1 ∈ D1, d2 ∈ D1, …, dn ∈ Dn
Una relazione matema;ca su D1, D2, …, Dn è un soQoinsieme del prodoQo cartesiano D1 × D2 ×…× Dn
D1, D2, …, Dn sono i domini della relazione. Una relazione su n domini ha grado n
Il numero di n-­‐uple è la cardinalità della relazione. Nelle applicazioni reali, la cardinalità è sempre finita
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
38
Relazione matema;ca: esempio
D1 = {a,b} e D2 = {x,y,z}
Prodotto cartesiano D1 × D2
a
a
b
b
x
y
y
z
x
y
z
x
y
z
Una relazione r ⊆ D1 × D2
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
a
a
a
b
b
b
R. Oliveto
39
Relazioni nel modello relazionale
A ogni dominio (aQributo) è associato un nome, unico nella relazione, che “descrive” il ruolo del dominio
L’ordinamento fra gli aQribu; è irrilevante
Nella terminologia del modello relazionale, una riga è deQa tupla
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
40
Domini, aQribu;, tuple e relazioni
Nel modello relazionale, un dominio D è un insieme di valori atomici, cioè indivisibili.
Un metodo per specificare un dominio è specificare un ;po di dato da cui sono presi i da; che formano il dominio
Esempi
Usa_Phone_Numbers: insieme di numeri a 10 cifre che rappresentano numeri telefonici validi negli sta; uni;
Social_Security_Number: insieme di SSN validi, di 9 cifre
Employee_Ages: possibile età dei dipenden;, da 16 a 80 anni
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
41
Domini
Per ogni dominio viene specificato un ;po di dato (o formato)
Esempio: USA_PHONE_NUMBER può essere dichiarato come una stringa (ddd)ddd-­‐dddd
Potrebbe essere necessario specificare l’unita di misura per interpretare i valori di un dominio
Esempio: peso_persona è espresso con l’unità di peso kg
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
42
Schemi di relazione
Uno schema di relazione, denotato da R(A1, A2,…, An), descrive una relazione.
Uno schema di relazione è formato da:
Un nome di relazione R
Una lista di aQribu; (A1, A2,…, An)
Ciascun Ai è il nome di un ruolo giocato da qualche dominio D nello schema R
Il grado di una relazione è il numero di aQribu;, n, del suo schema di relazione
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
43
Schemi di relazione (Esempio)
Nome della relazione: Student
Grado 7
Dom(SSN) = social_Security_Numbers Dom(Age) = student_ages
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
44
Istanze di relazione
Una relazione (o istanza di relazione) r dello schema R(A1, A2,…, An), denotata r(R) è un insieme di tuple r = {t1, t2,…, tn}
Ogni ti è una lista ordinata di n valori t = <v1,v2,…,vn> dove ciascun vi ∈ Dom(Ai) ∪ {null}
t[Ai] si riferisce al valore vi per l'aQributo Ai
Intensione della relazione: R -­‐ schema
Estensione della relazione: r(R) -­‐ istanza di relazione
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
45
Schemi e istanze di relazione
Schema di relazione
Istanza di relazione
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
46
CaraQeris;che di una relazione
L’ordinamento delle tuple di una relazione non è parte della definizione. La definizione non specifica alcun ordine
Una definizione alterna;va di relazione considera non significa;vo anche l’ordine degli aQribu;
In accordo a tale definizione una tupla può essere considerata come un insieme di (<aQributo>, <valore>) Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
47
CaraQeris;che di una Relazione
Relazioni equivalenti, con diversi ordinamenti
di righe e colonne
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
48
Schema di database relazionale
Uno schema di database relazionale è un insieme di schemi di relazione
S{R1, R2,…, Rn} Una istanza di database relazionale DB di S è un insieme di istanze di relazione
DB={r1, r2,…, rn} tale che ri è una istanza di R1
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
49
Schema di database relazionale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
50
Istanza di database relazionale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
51
Vincoli nel modello relazionale
Nel modello relazionale, i valori presen; in un’istanza di relazione devono soddisfare una serie di vincoli:
Vincoli di dominio
Vincoli di chiave
Vincoli di integrità di en1tà
Vincoli di integrità referenziale Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
52
Vincoli di dominio
Il valore di ciascun aQributo di A deve essere un valore atomico {caraQere, stringa a lunghezza fissa e variabile, data, ora, valuta, ecc…} appartenente a Dom(A)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
53
Superchiave (1)
Una relazione è definita come un insieme di tuple. Per definizione tuI gli elemen; di un insieme sono dis;n;, quindi tuQe le tuple devono essere dis;nte
Devono allora esistere dei soQoinsiemi di aQribu; con la proprietà di non avere la stessa combinazione di valori in più tuple. Sia sk un tale soQoinsieme di aQribu;, quindi t1[sk] ≠ t2[sk]
L’insieme di aQribu; sk è deQo superchiave di R
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
54
Superchiave (2)
Formalmente, una chiave k di uno schema di relazione R è una superchiave tale che, rimovendo uno dei suoi aQribu;, non è più una superchiave k è deQa anche superchiave minimale
Informalmente, una chiave k è un insieme di aQribu; minimale che permeQe di iden;ficare univocamente una tupla
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
55
Chiave primaria
In una relazione possono esistere più chiavi, deQe chiavi candidate: in tal caso se ne sceglie una, deQa chiave primaria
Una chiave deve godere anche delle proprietà di Time_Invariant (invariante nel tempo)
Esempio di chiave: {SSN} è una chiave. Ogni insieme di aQribu; che include SSN è una superchiave.
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
56
Vincoli di chiave
In una relazione R, non possono esistere valori duplica; per aQribu; chiave k
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
57
Vincoli di integrità di en;tà
Nessun valore di chiave primaria può essere “null”.
Questo perché:
Se ciò fosse permesso, non si avrebbe modo di iden;ficare l’en;tà descriQa nella tupla
Non si vogliono memorizzare informazioni su en;tà non iden;ficabili
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
58
Vincoli di integrità referenziale
Specifica; tra due relazioni, sono usa; per mantenere consistenza tra tuple delle due relazioni
Informalmente: una tupla di una relazione, che riferisce ad una tupla di un’altra relazione, deve riferire ad una tupla esistente
E’ il conceQo portante del modello relazionale!
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
59
Esempio
L’aQributo DNO di Employee deve riferire ad un DNUMBER esistente nella relazione Department. La relazione Employee è deQa essere relata a Departement tramite l’aQributo DNO
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
60
Formalizzazione (1)
Un insieme di aQribu; FK in uno schema di relazione Ri è una chiave esterna se vale:
gli aQribu; in FK hanno lo stesso dominio degli aQribu; della chiave primaria PK di un altro schema di relazione R2 (gli aQribu; in FK riferiscono alla relazione R2)
un valore di FK in una tupla t1 di R1 o occorre come un valore di PK per qualche tupla t2 di R2 o è null.
T1[FK]= T2[PK] oppure T1[FK]=null
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
61
Formalizzazione (2)
L’aQributo DNO di Employee è una chiave esterna, poiché rispeQa le condizioni appena elencate
Una tupla t1 di una relazione R1 è deQa referenziare una tupla t2 di una relazione R2 se vale t1[FK] = t2[PK] Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
62
Vincoli di integrità referenziale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
63
Violazioni: Insert (1)
Può violare tuI e quaQro i ;pi di vincoli
Dominio: un valore di un aQributo può non apparire nel corrispondente dominio
Chiave: il valore della chiave nella nuova tupla già esistente nella relazione r(R)
Integrità di en1tà: la chiave primaria è inserita a null
Integrità referenziale: il valore di una chiave esterna riferisce ad una tupla che non esiste nella relazione referenziata. Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
64
Violazioni: Insert (2)
Come ges;re la violazione?
Forzare l’inserimento completo (della relazione riferita)
Rifiutare l’inserimento
Nel primo caso la violazione può riguardare in cascata l’inserimento su altre relazioni Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
65
Violazioni: Delete
La delete può violare solo l’integrità referenziale
Come ges;re la violazione:
RigeQare la cancellazione
Tentare di propagare la cancellazione
Modificare i valori dell’aQributo referenziante (posto a null)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
66
Violazioni: Modifica
Nessun problema per aQribu; che non sono né chiave primaria né chiave esterna
Modifica chiave primaria:
Analogo a cancellare una tupla e inserirne un’altra
Modifica chiave esterna: Il DBMS deve verificare che riferisca ad una tupla esistente Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
67
Cos’è la normalizzazione
Una tecnica di verifica dei risulta; della progeQazione che porta all’organizzazione dei da; omogenei in tabelle correlate con legami efficien;, eliminando ridondanze, inconsistenze e anomalie in aggiornamento
La normalizzazione avviene usando tre forme normali (forme di verifica):
Prima Forma Normale
Seconda Forma Normale
Terza Forma Normale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
68
Prima forma normale (1)
Condizione: in una tabella, gli elemen; delle colonne devono essere ad un sol valore, ovvero ogni aQributo non può avere più di un valore per un singolo record
Articolo
NegoziAcquirenti
1
ABCD
In questo caso l’aQributo “Negozi acquiren;” riporta quaQro valori per un solo record; non essendo ciò possibile occorre la Normalizzazione.
N.B. L’insieme di da; omogenei raggruppa; tra loro si chiama Array (A B C D) e quindi in una tabella si può dire che gli elemen; delle colonne non possono essere degli array
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
69
Prima forma normale (2)
Normalizzazione: nel caso di presenza di array, normalizzare la tabella significa suddividere le informazioni in tante righe quan; sono i valori dell’array.
Articolo
NegoziAcquirenti
1
A
1
B
1
C
1
D
Esempio: In questo caso, i record diventano quaQro e non più uno
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
70
Seconda Forma Normale (1)
Condizione: se in una tabella la chiave primaria è cos;tuita da più di un aQributo (più campi), ogni campo (o colonna) non appartenente alla chiave deve dipendere dall’insieme delle chiavi, e non solo da una di queste
Articolo
NegoziAcquirenti
Quantità
IndirizzoNegozio
1
Conad
100
via Roma, 234
In questo caso le chiavi primarie sono “Ar;colo” e “Negozio acquirente”, l’aQributo “Quan;tà” dipende da tuQe e due (una certa quan;tà di un ar;colo è acquistata da un determinato negozio), ma l’aQributo “Indirizzo” dipende solo dalla chiave “Negozio acquirente” (l’indirizzo appar;ene al negozio, indipendentemente dall’ar;colo acquistato)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
71
Seconda Forma Normale (2)
Normalizzazione: normalizzare significa ‘spezzare la tabella’ e produrre tante tabelle che soddisfino la condizione, in modo, quindi, che i campi non appartenen; alla chiave dipendano da essa
Articolo
NegoziAcquirenti
Quantità
1
Conad
100
Negozio
IndirizzoNegozio
Conad
via Roma, 234
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
72
Terza Forma Normale (1)
Condizione: in una tabella, la dipendenza fra le colonne deve essere basata solo sulla chiave primaria, ovvero non ci deve essere nessun legame di dipendenza fra aQribu; che non sono chiavi primarie (dipendenze transi;ve).
Codice
Titolo
Cantante
DataNascita
Nazionalità
1
Attenti al lupo
Lucio Dalla
4/3/1943
Italia
Nell’esempio, gli aQribu; “DataNascita” e “Nazionalità” non dipendono dalla chiave “Codice”, ma dall’aQributo “Cantante” che non è chiave primaria
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
73
Terza Forma Normale (2)
Normalizzazione: normalizzare significa produrre tante tabelle che soddisfino la condizione, quindi ‘spezzare’ la tabella che non soddisfa la condizione e spostare le dipendenze transi;ve in una tabella collegata alla prima tramite una chiave esterna
Cantante
DataNascita
Nazionalità
Lucio Dalla
4/3/1943
Italia
Codice
Titolo
Cantante
1
Attenti al lupo
Lucio Dalla
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
74
MANIPOLARE UNA BASE DI DATI CON IL LINGUAGGIO SQL
Basi di da; Biologiche
Prof. R. Oliveto -­‐ A.A. 2011/2012
lunedì 19 marzo 12
75
SQL
Il linguaggio SQL permeQe la definizione, la manipolazione (aggiornamento e recupero) e la ges;one di basi di da; relazionali
E’ una delle ragioni del successo dei db relazionali in ambito commerciale: essendo uno standard in tuI i DBMS relazionali, gli uten; sono poco propensi a migrare verso data model diversi, quali il gerarchico o il re;colare
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
76
SQL: un po’ di storia (1)
Nel 1970 Codd propone il modello relazionale: iniziano esperimen; e ricerche per la realizzazione di linguaggi relazionali, cioè di linguaggi in grado di realizzare le caraQeris;che del modello astraQo
Il primo risultato è SEQUEL (Structured English QUEry Language), definito all’IBM Research
Facile da imparare e u;lizzare, in quanto basato su termini inglesi che mascherano i difficili conceI dell’algebra relazionale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
77
SQL: un po’ di storia (2)
Una versione rivista, il SEQUEL/2, ridenominata SQL (Structured Query Language) viene definita nel 1976
Il primo prodoQo basato su SQL viene chiamato Oracle (1979), lanciato dalla Rela;onal Souware, Inc.
Nel 1981 IBM annuncia un prodoQo SQL denominato SQL/Data System; nel 1983 viene rilasciato il DBMS relazionale DB2 compa;bile con SQL/DS
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
78
SQL: un po’ di storia (3)
Oggi SQL è implementato da tuI i principali fornitori di DBMS, ed è il linguaggio per database più usato al mondo
L’ANSI e l’ISO hanno sviluppato una serie di standard per SQL, quali ANSI SQL-­‐86, SQL-­‐92 (SQL2) ed SQL3
Sfortunatamente ogni DBMS relazionale implementa un suo livello (o dialeQo) di SQL, che è un’estensione o un soQoinsieme di un livello standard
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
79
Il linguaggio SQL
SQL fornisce istruzioni per la definizione di da;, query e aggiornamen;, quindi è sia un DDL (Data Defini;on Language) che un DML (Data Manipula;on Language)
Fornisce inoltre facility per definire viste e per ricavare indici
SQL può essere usato interaIvamente (con maschere del DBMS) o essere incorporato (embedded) in programmi C, Cobol, Java, ecc.
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
80
Schema in SQL Il conceQo di schema SQL è usato per raggruppare tabelle ed altri costruI che appartengono alla stessa applicazione di database
Uno schema SQL è iden;ficato da un nome dello schema, ed include un iden;ficatore di autorizzazione per indicare l’utente proprietario dello schema, così come dei descriQori per ogni elemento dello schema
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
81
Creare uno schema
Uno schema include: tabelle, domini, viste e altri costruI, quali permessi di autorizzazione, ecc
La sintassi per creare uno schema è:
CREATE SCHEMA nome_schema
AUTHORIZATION nome_utente
Crea uno schema chiamato nome_schema, il cui proprietario è l’utente con account nome_utente
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
82
Il comando CREATE TABLE
CREATE TABLE è usato per specificare una nuova relazione, assegnandole un nome ed un insieme di aQribu; e vincoli
Gli aQribu; sono specifica; da un nome, un ;po di dato per definire il dominio dei valori, ed eventuali vincoli
In ul;mo si specifica la chiave, i vincoli di integrità di en;tà e di integrità referenziale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
83
Tipi di da; e domini (1)
Numerici
Interi (INTEGER o INT, SMALLINT) e Reali (FLOAT, REAL, DOUBLE PRECISION)
Numeri formaQa; (DECIMAL(i,j), DEC(i,j), NUMERIC(i,j))
i, deQa precisione, indica il numero di cifre decimali, mentre j, deQa scala, indica il numero di cifre dopo la virgola
Stringhe di caraQeri
A lunghezza fissa (CHAR(n), CHARACTER(n)) e a lunghezza variabile (VARCHAR(n) o CHAR VARYING(n))
Per default n, il numero massimo di caraQeri, è 1
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
84
Tipi di da; e domini (2)
Stringhe di bit
A lunghezza fissa (BIT(n)) e a lunghezza variabile (BIT VARYING(n))
DATE
Ha dieci posizioni, con componen; YEAR, MONTH e DAY. Formato YYYY-­‐MM-­‐DD
TIME
Ha (almeno) oQo posizioni con componen; HOUR, MINUTE e SECOND.
Formato HH:MM:SS
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
85
Domini personalizza;
In SQL2 è possibile sia dichiarare il ;po di dato di un aQributo, sia dichiarare il dominio
Ciò semplifica il cambiamento di un ;po per un dominio usato più volte nello schema. Esempio:
CREATE DOMAIN ssn_type AS CHAR(9);
e poi si usa ssn_type per gli aQribu;
ssn e superssn di Employee
mgrssn e essn di Department
essn di Works_on
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
86
I valori null e default
Poiché SQL consente che un aQributo abbia valore null, se si vuole impedire ciò si usa il vincolo NOT NULL
Tale vincolo deve sempre essere specificato per la chiave primaria
E’ anche possibile specificare un valore di default per un aQributo, aQraverso la clausola DEFAULT <value>, dopo la dichiarazione dell’aQributo
Senza tale clausola il valore di default di un aQributo è null
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
87
CREATE TABLE: Esempio
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
88
Altri vincoli (1)
Dopo le specifiche degli aQribu;, possono essere specifica; i vincoli di tabella, quali chiave ed integrità referenziale
La clausola PRIMARY KEY specifica uno o più aQribu; che faranno da chiave primaria
La clausola UNIQUE specifica una chiave alterna;va
La clausola FOREIGN KEY specifica l’integrità referenziale
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
89
Altri vincoli (2)
Il progeIsta dello schema può specificare l’azione da intraprendere se si viola un vincolo di integrità referenziale, aQraverso la cancellazione di una tupla referenziata o aQraverso la modifica di un valore di chiave referenziata
L’azione referenziale triggered può essere specificata nella clausola FOREIGN KEY
Possibili azioni sono SET NULL, CASCADE e SET
DEFAULT, qualificate da opzioni ON DELETE e ON
UPDATE
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
90
Altri vincoli: Esempio (1)
Specifica di valori di
default e
azioni referenziali
triggered
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
91
Altri vincoli: Esempio (2)
Nell’esempio, per la chiave esterna SUPERSSN di EMPLOYEE ci sono i vincoli:
SET NULL ON DELETE
Se la tupla dell’impiegato che supervisiona viene cancellata, il valore di SUPERSSN è posto a null in tuQe le tuple impiegato che lo referenziano.
CASCADE ON UPDATE
Se il valore SSN di un impiegato che supervisiona è aggiornato, il nuovo valore è riportato in SUPERSSN di tuQe le tuple impiegato che referenziano il valore aggiornato
Ai vincoli può essere dato un nome (per poterli riu;lizzare), usando la keyword COSTRAINT
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
92
Relazioni base e virtuali
Le relazioni create con CREATE TABLE sono deQe tabelle base o relazioni base in SQL, e significa che sono create e memorizzate come file dal DBMS
Le relazioni base sono dis;nte dalle relazioni virtuali, create mediante CREATE VIEW, cui può o meno corrispondere un file fisico
In SQL gli aQribu; sono considera; ordina; nella sequenza in cui sono sta; specifica;. Le righe non sono considerate ordinate
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
93
Il comando DROP SCHEMA
Se uno schema non è più necessario, si usa il comando DROP SCHEMA, con due possibili opzioni (drop behaviour): CASCADE e RESTRICT
Esempi:
DROP SCHEMA Company CASCADE;
Lo schema del db COMPANY viene rimosso, con tuQe le tabelle, domini ed altri elemen;
DROP SCHEMA Company RESTRICT;
Lo schema è eliminato solo se non con;ene elemen;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
94
Il comando DROP TABLE
PermeQe di eliminare una tabella
DROP TABLE Dependent CASCADE;
Se non si vuole tenere più traccia delle persone a carico nel db COMPANY
DROP TABLE Dependent RESTRICT;
La tabella è eliminata solo se non è referenziata in alcun vincolo o vista. Con l’opzione CASCADE sarebbero automa;camente elimina; insieme alla tabella stessa
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
95
Il comando ALTER TABLE
La definizione di una tabella base può essere cambiata usando il comando ALTER TABLE
Possibili azioni di modifica di una tabella sono:
Aggiunta o rimozione di aQribu;
Cambio di definizione di una colonna
Aggiunta o rimozione di un vincolo
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
96
ALTER TABLE: Esempi
Vogliamo aggiungere il lavoro svolto da un impiegato nella tabella Employee
ALTER TABLE Company.Employee
ADD job VARCHAR(12);
Il valore di Job o si specifica di default o sarà null. Con la ALTER TABLE non è permessa la clausola NOT
NULL
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
97
ALTER TABLE: Esempi
Vogliamo eliminare una colonna: occorre scegliere l’opzione CASCADE o RESTRICT
Con CASCADE tuI i vincoli e le viste che referenziano la colonna sono elimina; automa;camente dallo schema
Con RESTRICT il comando ha successo solo se nessun vincolo o vista referenzia la colonna
Esempio: rimuovere address dalla tabella Employee
ALTER TABLE Company.Employee
DROP address CASCADE;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
98
ALTER TABLE: Esempi
Modifica di una colonna eliminando una clausola di default o definendone una nuova. Esempi:
ALTER TABLE Company.Department
ALTER mgrssn DROP DEFAULT;
ALTER TABLE Company.Department
ALTER mgrssn SET DEFAULT “333444555”;
Cambio di vincoli
E’ possibile eliminare un vincolo solo se ha un nome
ALTER TABLE Company.Employee
DROP COSTRAINT empsuperfk CASCADE;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
99
Il comando SELECT (1)
Il comando SELECT è l’istruzione di base per recuperare informazioni da un database
Il SELECT dell’SQL non ha relazioni con l’operatore di select dell’algebra relazionale.
La forma di base, deQa mapping o blocco di SELECT
FROM WHERE è formata da tre clausole:
SELECT <lista_attributi>
FROM <lista_tabelle>
WHERE <condizione>
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
100
Il comando SELECT (2)
<lista_attributi> è una lista di nomi di aQribu; i cui valori devono essere recupera; dalla query
<lista_tabelle> è una lista di nomi di relazioni richies; per elaborare la query
<condizione> è un’espressione booleana di ricerca che iden;fica la tupla da ritrovare
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
101
SELECT: Esempio (1)
Trovare la data di nascita e l’indirizzo dell’impiegato di nome ‘John B. Smith’
SELECT bdate, address
FROM Employee
WHERE fname=‘JOHN’ AND minit=‘B‘
AND lname=‘SMITH’;
BDATE e ADDRESS sono deI anche a9ribu1 di proiezione
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
102
SELECT: Esempio (2)
Viene effettuata prima una selezione e poi una proiezione
SELECT bdate, address FROM employee
WHERE fname=‘JOHN’ AND minit=‘B’ AND lname=‘SMITH’;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
103
SELECT: Esempio (3)
Trovare cognome, nome e indirizzo di tuI gli impiega; del dipar;mento numero 5
SELECT fname, lname, address
FROM Employee
WHERE dno=5;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
104
Cross Product o Cross Join
Se non si specifica la clausa WHERE e nella clausola FROM sono specificate più tabelle, si effeQua un cross product o cross join (prodoQo cartesiano)
ProdoQo cartesiano
R(A1, A2,…, An) x S(B1, B2,…, Bm) = Q(A1, A2,…, An, B1, B2,…, Bm)
In Q si ha una tupla per ogni combinazione di una da R ed una da S
Se R con;ene nr tuple ed S con;ene ns tuple, allora R x S con;ene nr x n1 tuple
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
105
ProdoQo cartesiano: Esempio
Impiegati
Impiegato
Rossi
Neri
Bianchi
Reparto
A
B
B
Reparti
Codice
A
B
Capo
Mori
Bruni
Impiegato
Reparto
Codice
Capo
Rossi
A
A
Mori
Rossi
A
B
Bruni
Neri
B
A
Mori
Neri
B
B
Bruni
Bianchi
B
A
Mori
Bianchi
B
B
Bruni
SELECT * FROM Impiegati, Reparti
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
106
Tabelle joined
Possono essere usa; i seguen; ;pi di join:
INNER JOIN
LEFT OUTER JOIN / RIGHT OUTER JOIN
FULL OUTER JOIN
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
107
Inner Join: Esempio (1)
Impiegati
Impiegato
Rossi
Neri
Bianchi
Reparto
A
B
B
Impiegat Reparto
o
Rossi
A
Neri
B
Bianchi
B
Codice
A
B
B
Capo
Mori
Bruni
Bruni
Reparti
Codice
A
B
Capo
Mori
Bruni
SELECT * FROM
(Impiegati JOIN Reparti ON
Impiegati.Reparto = Reparti.Codice)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
108
Inner Join: Esempio (2)
Trovare il nome e l'indirizzo di ogni impiegato che lavora per il Dipar;mento ‘Research’:
SELECT fname, lname, address
FROM (Employee JOIN Department
ON dno=dnumber)
WHERE dname='Research‘;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
109
Join: Tuple tagliate fuori
Impiegati
Impiegato
Rossi
Neri
Bianchi
Reparto
A
B
B
Impiegat Reparto
o
Neri
B
Bianchi
B
Codice
B
B
Capo
Bruni
Bruni
Reparti
Codice
B
C
Capo
Bruni
Verdi
SELECT * FROM
(IMPIEGATI JOIN REPARTI ON
IMPIEGATI.REPARTO = REPARTI.CODICE
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
110
Join esterno
Sinistro: man;ene tuQe le ennuple del primo operando, estendendole con valori nulli, se necessario
Destro: ... del secondo operando ...
Completo: … di entrambi gli operandi ...
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
111
Leu Join: Esempio
Impiegati
Impiegato
Rossi
Neri
Bianchi
Reparto
A
B
B
Reparti
Codice
B
C
Capo
Bruni
Verdi
Impiegato
Reparto
Codice
Capo
Rossi
A
Neri
B
null
B
null
Bruni
Bianchi
B
B
Bruni
SELECT * FROM
(Impiegati LEFT JOIN Reparti ON
Impiegati.Reparto = Reparti.Codice)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
112
Right Join: Esempio
Impiegati
Impiegato
Rossi
Neri
Bianchi
Reparto
A
B
B
Reparti
Codice
B
C
Capo
Bruni
Verdi
Impiegato
Reparto
Codice
Capo
null
null
C
Verdi
Neri
B
B
Bruni
Bianchi
B
B
Bruni
SELECT * FROM
(Impiegati RIGHT JOIN Reparti ON
Impiegati.Reparto = Reparti.Codice)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
113
Full Join: Esempio
Impiegati
Impiegato
Rossi
Neri
Bianchi
Reparto
A
B
B
Reparti
Codice
B
C
Capo
Bruni
Verdi
Impiegato
null
Rossi
Neri
Bianchi
Codice
C
null
B
B
Capo
Verdi
null
Bruni
Bruni
SELECT * FROM
(Impiegati FULL JOIN Reparti ON
Impiegati.reparto = Reparti.Codice)
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
Reparto
null
A
B
B
R. Oliveto
114
Renaming (1)
In SQL lo stesso nome può essere usato per più aQribu; solo se ques; appartengono a relazioni diverse
Se una query coinvolge tali relazioni, occorre qualificare il nome dell’aQributo con il nome della relazione per evitare ambiguità
Esempio: Employee.SSN
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
115
Renaming (2)
Si può avere ambiguità anche nel caso di query che riferiscono due volte alla stessa relazione
Esempio: Per ogni impiegato, trovare il nome ed il cognome suo e del suo superiore direQo
SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN=S.SSN;
Abbiamo dichiarato nomi di relazione alterna;vi E ed S, deI alias, per la relazione Employee
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
116
Il caraQere jolly “*”
Recupera tuI gli aQribu; delle tuple selezionate
Esempio: trovare tuI i valori degli aQribu; degli impiega; che lavorano per il dipar;mento n°5
SELECT *
FROM Employee
WHERE dno=5;
Trovare tuI gli aQribu; di Employee e gli aQribu; del Department per cui lavora ogni impiegato del Dipar;mento ‘Research’
SELECT *
FROM Employee, Department
WHERE dname=‘Research’ AND dno=dnumber;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
117
Duplicazioni di tuple in SQL SQL non traQa relazioni come insiemi: tuple duplicate possono apparire più di una volta.
Se le duplicazioni non sono volute,lo si specifica con la clausola DISTINCT
Esempi: Trovare i salari di tuI gli impiega;
SELECT salary
FROM Employee;
Trovare i salari dis;n; degli impiegatI
SELECT DISTINCT salary
FROM Employee;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
118
Confronto tra soQostringhe
Per il confronto tra stringhe si usa l’operatore LIKE
CaraQeri jolly:
‘%’ rimpiazza qualsiasi numero di caraQeri
‘_’ rimpiazza un singolo caraQere
Esempio: trovare tuI gli impiega; il cui indirizzo è a Houston, Tx
SELECT fname, lname
FROM Employee
WHERE address LIKE ‘%Houston, Tx%’;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
119
Uso di “LIKE” (1)
Gli impiega; che hanno un nome che inizia per 'A' e ha una 'd' come terza leQera
SELECT *
FROM Employee
WHERE fname LIKE 'A_d%'
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
120
Uso di “LIKE” (2)
Trovare tuI gli impiega; na; negli anni ’50. Il formato di data è YYYY-­‐MM-­‐DD.
SELECT fname, lname
FROM Employee
WHERE bdate LIKE ‘_ _ 5_ _ _ _ _ _ _ _’;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
121
Calcolo di valori
Mostrare i salari risultan; se a tuI gli impiega; che lavorano sul progeQo ‘Product X’ viene concesso un aumento del 10%
SELECT fname, lname, 1.1*salary
FROM Employee, Works_on, Project
WHERE essn=ssn AND pno=pnumber
AND pname=‘Product X’;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
122
Ges;one dei valori nulli
Recuperare gli tutti gli impiegati la cui età
potrebbe essere maggiore di 40
Matricola Cognome
7309
5998
Rossi
Neri
5998
9553
Bruni
Neri
9553
Bruni
Filiale
Milano
Roma
Milano
Milano
Età
32
45
NULL
45
NULL
SELECT *
FROM Impiegato
WHERE eta > 40 OR eta IS NULL
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
123
Aggregazione e raggruppamento
Le funzioni di aggregazione e di raggruppamento sono diffusissime nella ges;one di basi di da;. SQL incorpora le seguen; funzioni:
COUNT: conteggio tuple SUM: somma dei valori di un aQributo in una tabella
MAX: valore massimo tra gli aQribu; di una tabella
MIN: valore minimo tra gli aQribu; di una tabella
AVG: valore medio tra gli aQribu; di una tabella
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
124
Aggregazione e Esempio: trovare la somma dei salari di tuI gli impiega;, il massimo, il minimo e la media dei salari:
SELECT SUM(salary), MAX(salary),
MIN(salary), AVG(salary)
FROM Employee;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
125
Count (1)
Res;tuisce il numero di tuple nel risultato della query (*)
Esempio: conta il numero di impiega;
SELECT COUNT(*)
FROM Employee;
Esempio: conta il numero di impiega; del dipar;mento “Research”
SELECT COUNT(*)
FROM Employee, Department
WHERE dno=dnumber AND dname=‘Research’;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
126
Count (2)
Conta il numero di valori di s;pendi dis;n;
SELECT COUNT(DISTINCT salary)
FROM Employee;
Elencare il nome ed il cognome degli impiega; che hanno due o più persone a carico
SELECT lname, fname
FROM Employee
WHERE (SELECT COUNT(*)
FROM Dependent
WHERE ssn=essn)>=2;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
127
Ordinamento di tuple (1)
Per ordinare le tuple nel risultato della query si usa la clausola ORDER BY
Esempio: Ritrovare una lista di impiega; e dei progeI su cui lavorano, ordina; per dipar;mento, e nell’ambito di ciascun dipar;mento, alfabe;camente per cognome e nome
SELECT dname, fname, lname, pname
FROM Department, Employee,
Works_on, Project
WHERE dnumber=dno AND ssn=essn
AND pno=pnumber
ORDER BY dname, lname, fname;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
128
Ordinamento di tuple (2)
L’ordine di default è crescente
ASC per crescente
DESC decrescente
Esempio: per avere un ordine decrescente di dipar;mento e crescente per nome e cognome:
ORDER BY dname DESC, lname ASC,
fname ASC
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
129
Group by (1)
Raggruppiamo le tuple che hanno lo stesso valore per alcuni aQribu;
Esempio: Recuperare, per ogni diparimento, il numero di impiega; e il salario medio SELECT dno, COUNT(*), AVG(salary)
FROM Employee
GROUP BY dno;
Le tuple sono divise in gruppi, ogni gruppo ha lo stesso valore per DNO.
Le funzioni COUNT e AVG sono applicate ad ogni gruppo di queste tuple
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
130
Group by (2)
Risultato
Dno
COUNT(*)
AVG(salary)
1
4
23000
4
3
25000
3
4
22000
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
131
Group by (3)
Per ogni progeQo, visualizzare il numero del progeQo, il nome del progeQo ed il numero di impiega; che lavorano su quel progeQo:
SELECT pnumber, pname, COUNT(*)
FROM Project, Works_on
WHERE pnumber=pno
GROUP BY pnumber, pname;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
132
Group by (4)
Per ogni progeQo su cui lavorano più di due impiega;, visualizzare il numero del progeQo, il nome del progeQo ed il numero di impiega; che lavorano su quel progeQo:
SELECT pnumber, pname, COUNT(*)
FROM Project, Works_on
WHERE pnumber=pno
GROUP BY pnumber, pname;
HAVING COUNT(*) > 2;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
133
Group by (5)
Per ogni progeQo visualizzare il numero del progeQo, il nome del progeQo ed il numero di impiega; del dipar;mento n.5 che lavorano su quel progeQo:
SELECT pnumber, pname, COUNT(*)
FROM Project, Works_on, Employee
WHERE pnumber=pno AND sss=essn
AND dno=5
GROUP BY pnumber, pname;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
134
Group by (6)
Determinare, per ogni dipar;mento che ha più di 6 impiega;, il numero totale degli impiega; il cui s;pendio è maggiore di $40.000
SELECT dname, COUNT(*)
FROM Department, Employee
WHERE dnumber=dno AND salary>40000
GROUP BY dname
HAVING COUNT(*)>6;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
135
Aggiornamen; in SQL
In SQL sono previs; tre comandi per modificare il database:
INSERT
DELETE
UPDATE
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
136
Il comando Insert
Il comando INSERT INTO inserisce nuove righe in una relazione
Sintassi:
INSERT INTO Target [(FieldName,…)]
VALUES (Value1,…);
Oppure:
INSERT INTO Target [(FieldName,…)]
SELECT FieldNames
FROM TableExpression
WHERE Conditions;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
137
Insert: Esempio (1)
Aggiungere una nuova tupla alla relazione ‘Employee’:
INSERT INTO Employee
VALUES (‘Richard’, ‘K’, ‘Marini’,
‘654765876’, ’30-DEC-52’,
‘98 Oak Forest, Katy, TX’,
‘M’, 37000, ‘987654321’, 4);
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
138
Insert: Esempio (2)
E’ possibile non assegnare valori a tuI gli aQribu;
In tal caso, ques; avranno il valore di default o null
Esempio:
INSERT INTO
Employee (fname, lname, ssn)
VALUES (‘Richard’, ‘Marini’,
‘654765876’);
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
139
Insert: Esempio (3)
Creare una tabella temporanea che ha nome, numero di impiega; e salari totali per ciascun dipar;mento:
CREATE TABLE Depts_Info (
dept_name VARCHAR(15),
no_of_emps INTEGER,
total_sal INTEGER);
INSERT INTO
Depts_Info (dept_name, no_of_emps, total_sal)
SELECT dname, COUNT(*), SUM(salary)
FROM Department, Employee
WHERE dnumber=dno
GROUP BY dname;
Eventuali aggiornamen; successivi non influenzano la tabella originale. Per aggiornarle, è invece necessario definire una vista.
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
140
Il comando DELETE
Il comando DELETE rimuove una o più tuple da una relazione
Sintassi:
DELETE
FROM TableName
WHERE Criteria;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
141
DELETE: Esempi
Eliminare l’impiegato il cui cognome è “Brown”
DELETE FROM Employee
WHERE lname=‘Brown’;
Eliminare tuI gli impiega; che afferiscono al dipar;mento “Research”
DELETE FROM Employee
WHERE dno IN (SELECT dnumber
FROM Department
WHERE dname=‘Research’);
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
142
Il comando UPDATE
Il comando UPDATE consente di modificare uno o più aIbu; di una o più tuple di una relazione
Sintassi:
UPDATE TableName
SET Attribute1 = NewValue1,...
WHERE Criteria;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
143
UPDATE: Esempio (1)
Modificare il luogo e il dipar;meto che ges;sce il progeQo 5:
UPDATE Project
SET plocation=‘Bellaire’, dnum=5
WHERE pnumber=10;
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
144
UPDATE: Esempio (2)
Incrementare del 10% il salario di tuI gli impiega; che lavorano sul dipar;mento “Research”:
UPDATE Employee
SET salary=salary * 1.1
WHERE dno IN (SELECT dnumber
FROM Department
WHERE dname=‘Research’);
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
145
Domande e/o commen;?
Corso di Bioinformatica - Basi di dati biologiche
lunedì 19 marzo 12
R. Oliveto
146