Gli strumenti che vedremo

Tool

Basi di Dati e Sistemi Informativi Prof. Marco Di Felice

Dott.sa Sara Zuppiroli

A.A. 2012-2013

Gli strumenti che vedremo

Come imposteremo le lezioni

Per ogni strumento:

Breve introduzione

Installazione sul proprio pc del tool Creazione ed accesso a un DB

Comandi SQL da terminale e da interfaccia grafica Esempio di implementazione di un piccolo DB

Funzionalità avanzate

Confronto tra i due strumenti

Introduzione a Postgres

PostgreSQL è un Database system (relazionale e ad oggetti) open source

Nasce da un progetto del 1986 del professor Michael Stonebraker

Ad oggi PostgreSql supporta le piattaforme Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), e Windows.

supporta la gestione di tabelle, foreign keys, joins, views, triggers, and stored procedures.

Include i seguenti tipi di dato: INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, e TIMESTAMP, inoltre salva immagini, suoni e video.

Ha interfacce per i seguenti linguaggi C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC.

per maggiori informazioni

http://www.postgresql.org/docs/9.2/static/history.html

Licenza

La licenza di Postgres è The PostgreSQL Licence (PostgreSQL) È una licenza simile a MIT e BSD le cui caratteristiche

principali sono:

I La possibilità di utilizzare il codice di PostgreSQL o lo strumento gratuitamente

I L’unico vincolo è quello di citare l’autore del programma

Per maggiori informazioni

http://opensource.org/licenses/postgresql

Installazione

Ci colleghiamo alla pagina

http://www.postgresql.org/download/

Selezioniamo la piattaforma installata sul vostro computer Eseguire il download del programma

Eseguiamo le istruzioni sul file README

Architettura

PostgreSQL usa un modello client/server. Una sessione PostgreSQL consiste nei seguenti processi:

Un processo server, che gestisce il database e accetta le connessioni da parte di un client. Il server gestisce le azioni sul database su indicazione dei client. È chiamato

postmaster il programma server.

L’applicazione client che richiede le operazioni al database.

L’applicazione Client può essere un’applicazione:

I uno strumento text-oriented

I uno strumento con interfaccia grafica,

I un web server che accede al database mostrando le pagine web

I uno strumento specializzato al mantenimento del database

Alcune applicazioni client sono sviluppate da PostgreSQL distribution, molte sono sviluppate da altri utenti.

Accedere al Server

Selezionare il programma PostgreSql e selezionare SQL shell, apparirà la seguente pagina

Connessione da shell

Scrivere le seguenti linee di comando per connettersi da Shell

I primi passi

Creazione e cancellazione di un nuovo DB da shell I comdandi per creare/cancellare un DB sono

CREATE DATABASE/DROP DATABASE seguito dal nome che si vuole assegnare

In BNF (Backus - Naur Form) i comandi createdb e dropdb sono definiti come segue:

I < command >::=< create >; | < drop >;

I < create >::= CREATE DATABASE < identifier >

I < drop >::= DROP DATABASE < identifier >

I < identifier >::=< letter >< id > ∗

I < id >::=< letter > | < digit >

I < number >::=< digit > +

I < letter >::= a|b|...|z|A|...|Z

I < digit >::= 0|1|...|9

Creare un DB che si chiami DB1 Cancellare il DB appena creato

Creazione delle tabelle

Per connettersi a un DB da shell bisogna digitare il comando nome del DB

Creiamo un Database dal nome IlMeteo Ci connettiamo al Database IlMeteo

A questo punto creiamo le tabelle che appartengono a questo DB:

I La tabella Tempo che contiene almeno questi attributi:

comune, tempmin, tempmax, precipitazione, data

I La tabella Comune che contiene almeno questi attributi:

comune, posizione

I tipi

I principali tipi che si possono utilizzare sono:

I tipi numerici

I Smallint 2 bytes, integer 4 bytes, real 4 bytes e precisione a 6 numeri decimali

I Per approfondimenti http://www.postgresql.org/

docs/9.2/static/datatype-numeric.html

I tipi Data

I Timestamp, date

I Per approfondimenti http://www.postgresql.org/

docs/9.2/static/datatype-datetime.html

I tipi Geometrici

I Point, circle

I Per approfondimenti http://www.postgresql.org/

docs/9.2/static/datatype-geometric.html

Creazione delle tabelle

CREATE TABLE Tempo (

comune varchar ( 8 0 ) , −− nome

temp_min i n t , −− t e m p e r a t u r a minima temp_max i n t , −− t e m p e r a t u r a massima

p r e c i p i t a z i o n e r e a l , −− p r e c i p i t a z i o n e

data date

) ;

CREATE TABLE Comune ( nome varchar ( 8 0 ) ,

l o c a l i z z a z i o n e p o i n t ) ;

Inserimento dei dati nelle tabelle

INSERT INTO Tempo

VALUES ( ’ Bologna ’ ,7 , 11 , 0 . 0 , ’ 2012−10−29 ’ ) ; INSERT INTO Comune

VALUES ( ’ Bologna ’ , ’ ( −194.0 , 5 3 . 0 ) ’ ) ; INSERT INTO Tempo

( comune , temp_min , temp_max , p r e c i p i t a z i o n e , data ) VALUES

( ’ Reggio E m i l i a ’ , 6 , 10 , 0 . 0 5 , ’ 2012−10−29 ’ ) ; INSERT INTO Tempo

( data , comune , temp_min , temp_max ) VALUES

( ’ 2012−10−29 ’ , ’ Modena ’ , 5 , 1 0 ) ;

Vincoli interni ed esterni alle tabelle

Per inserire un vincolo interno a una tabella, bisogna inserire alla fine di insert il comando

CONSTRAINT nomeCost PRIMARY KEY (campo1, ..., campoN)

Per inserire un vincolo esterno bisogna aggiungere il comando CONSTRAINT nomeCost FOREIGN KEY

(campoTabellaDaVincolare) REFERENCES tabellaSuCuiCreareIlVincolo (campo)

MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION

Esempi di Select

Estrarre tutti i dati presenti nella tabella tempo

Trovare per ogni comune la temperatura minima e la precipitazione per ciascun giorno

Con i dati archiviati, calcolare la temperatura media di ciascun comune

Estrarre le temperature e le precipitazioni e le date in cui il comune di Bologna non ha avuto precipitazioni.

Ordinare i dati in tabella per comune e poi per comune e temperatura minima

Selezionare i singoli comuni presenti nella tabella tempo, ripetere l’esercizio ordinando i dati per comune

Esempi di Select

SELECT ∗

FROM tempo ;

SELECT comune , temp_min , p r e c i p i t a z i o n e , data FROM Tempo ;

SELECT comune , ( temp_max+temp_min ) / 2 AS temp_m FROM Tempo ;

SELECT ∗ FROM Tempo WHERE

Comune = ’ Bologna ’ AND p r e c i p i t a t i o n = 0 . 0 ;

Esempi di Select

SELECT ∗ FROM Tempo

ORDER BY comune ; SELECT ∗

FROM Tempo

ORDER BY Comune , temp_min ; SELECT DISTINCT Comune

FROM Tempo ;

SELECT DISTINCT Comune FROM Tempo

ORDER BY Comune ;

Join tra tabelle

Estrarre tutti i comuni che sono presenti nella tabella tempo e di cui si ha la posizione

Estrarre tutti i dati delle tabelle tempo e comune legati dal nome del comune

Estrarre tutti i dati delle tabelle tempo e comune anche se il comune non è stato definito nella tabella comuni

Join tra tabelle

SELECT C. ∗

FROM Comune C

WHERE C . nome i n ( s e l e c t t . comune from tempo ) and c . p o s i z i o n e <> NULL ;

SELECT comune , temp_min , temp_max , p r e c i p i t a z i o n e , data , p o s i z i o n e

FROM tempo , comune WHERE comune = nome ; SELECT ∗

FROM Tempo LEFT OUTER JOIN

Comune ON ( tempo . comune = comune . nome ) ;

Group by e Having

SELECT comune , ( temp_min + temp_max ) / 2 temp_med FROM Tempo

group by comune , ( temp_min + temp_max ) / 2 having ( temp_min + temp_max ) / 2 > 8 ;

Quali dati estrae questa query?

Trigger

Interfaccia grafica

Selezionare il programma PostgreSql e poi pgAdminIII

Import da file

Decidere il separatore delle colonne

Creare il file con tutti i dati da inserire in tabella col separatore scelto

Selezionare la tabella su cui si vuole fare l’import Importare il file dopo aver configurato l’operazione

Esercizio

Uno studio medico vuole di realizzare un archivio che permetta di gestire i dati utilizzati dai medici che ne fanno parte. I medici ad oggi sono cinque e ognuno di loro deve avere accesso al DB dal proprio computer con una propria password. Il medico deve poter inserire, gestire e cancellare i propri pazienti. Ogni

paziente avrà una scheda anagrafica che avrà almeno i

seguenti dati: nome, cognome, data di nascita, luogo di nascita, codice fiscale e numero di identificazione della tessera sanitaria.

Il paziente avrà una scheda principale in cui verranno indicate le sue patologie croniche, la sua percentuale di invalidità,

eventuale, e i medicinali prescritti permanentemente, come l’insulina ad un diabetico per esempio.

Esercizio

All’interno di questa scheda si potranno visualizzare l’elenco delle visite del paziente. Ogni visita contiene il giorno in cui è

stata effettuata, la ragione, gli eventuali medicinali prescritti e un campo in cui sono annotabili alcune note a discrezione del

medico curante. Vi è la possibilità di allegare file di tipo jpg e doc per eventuali referti di altre visite che si ritengono collegati alla visita.