Dott.sa Sara Zuppiroli Prof. Marco Di Felice Basi di Dati ...zuppirol/Postgres.pdf · Breve...

Tool

Basi di Dati e Sistemi InformativiProf. Marco Di FeliceDott.sa Sara Zuppiroli

A.A. 2012-2013

Basi di Dati e Sistemi Informativi () PostgreSQL A.A. 2012-2013 1 / 26

Gli strumenti che vedremo


Come imposteremo le lezioni

Per ogni strumento:Breve introduzioneInstallazione sul proprio pc del toolCreazione ed accesso a un DBComandi SQL da terminale e da interfaccia graficaEsempio di implementazione di un piccolo DBFunzionalità avanzateConfronto tra i due strumenti


Introduzione a PostgresPostgreSQL è un Database system (relazionale e adoggetti) open sourceNasce da un progetto del 1986 del professor MichaelStonebrakerAd oggi PostgreSql supporta le piattaforme Linux, UNIX(AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), eWindows.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, eTIMESTAMP, inoltre salva immagini, suoni e video.Ha interfacce per i seguenti linguaggi C/C++, Java, .Net,Perl, Python, Ruby, Tcl, ODBC.per maggiori informazionihttp://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 caratteristicheprincipali sono:

I La possibilità di utilizzare il codice di PostgreSQL o lostrumento gratuitamente

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

Per maggiori informazionihttp://opensource.org/licenses/postgresql


Installazione

Ci colleghiamo alla paginahttp://www.postgresql.org/download/Selezioniamo la piattaforma installata sul vostro computerEseguire il download del programmaEseguiamo le istruzioni sul file README


ArchitetturaPostgreSQL usa un modello client/server. Una sessionePostgreSQL consiste nei seguenti processi:

Un processo server, che gestisce il database e accetta leconnessioni da parte di un client. Il server gestisce le azionisul database su indicazione dei client. È chiamatopostmaster il programma server.L’applicazione client che richiede le operazioni al database.L’applicazione Client può essere un’applicazione:

I uno strumento text-orientedI uno strumento con interfaccia grafica,I un web server che accede al database mostrando le pagine

webI uno strumento specializzato al mantenimento del database

Alcune applicazioni client sono sviluppate da PostgreSQLdistribution, molte sono sviluppate da altri utenti.


Accedere al ServerSelezionare il programma PostgreSql e selezionare SQL shell,apparirà la seguente pagina


Connessione da shellScrivere le seguenti linee di comando per connettersi da Shell


I primi passiCreazione e cancellazione di un nuovo DB da shell

I comdandi per creare/cancellare un DB sonoCREATE DATABASE/DROP DATABASE seguito dal nomeche si vuole assegnareIn BNF (Backus - Naur Form) i comandi createdb e dropdbsono 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|...|ZI < digit >::= 0|1|...|9

Creare un DB che si chiami DB1Cancellare il DB appena creato


Creazione delle tabelle

Per connettersi a un DB da shell bisogna digitare il comandonome del DB

Creiamo un Database dal nome IlMeteoCi connettiamo al Database IlMeteoA questo punto creiamo le tabelle che appartengono aquesto 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 tipiI principali tipi che si possono utilizzare sono:

I tipi numericiI Smallint 2 bytes, integer 4 bytes, real 4 bytes e precisione a

6 numeri decimaliI Per approfondimenti http://www.postgresql.org/docs/9.2/static/datatype-numeric.html

I tipi DataI Timestamp, dateI Per approfondimenti http://www.postgresql.org/docs/9.2/static/datatype-datetime.html

I tipi GeometriciI Point, circleI Per approfondimenti http://www.postgresql.org/docs/9.2/static/datatype-geometric.html


http://www.postgresql.org/docs/9.2/static/datatype-numeric.html

http://www.postgresql.org/docs/9.2/static/datatype-numeric.html

http://www.postgresql.org/docs/9.2/static/datatype-datetime.html

http://www.postgresql.org/docs/9.2/static/datatype-datetime.html

http://www.postgresql.org/docs/9.2/static/datatype-geometric.html

http://www.postgresql.org/docs/9.2/static/datatype-geometric.html

Creazione delle tabelle

CREATE TABLE Tempo (comune varchar ( 80 ) , −− nometemp_min int , −− temperatura minimatemp_max int , −− temperatura massimap r e c i p i t a z i o n e rea l , −− p r e c i p i t a z i o n edata date) ;

CREATE TABLE Comune (nome varchar ( 80 ) ,l o c a l i z z a z i o n e po in t) ;


Inserimento dei dati nelle tabelleINSERT INTO TempoVALUES ( ’ Bologna ’ ,7 , 11 , 0 .0 , ’2012−10−29 ’ ) ;

INSERT INTO ComuneVALUES ( ’ Bologna ’ , ’ (−194.0 , 53 .0) ’ ) ;

INSERT INTO Tempo( comune , temp_min , temp_max , p rec i p i t az i on e , data )VALUES( ’ Reggio Emi l ia ’ , 6 , 10 , 0.05 , ’2012−10−29 ’ ) ;

INSERT INTO Tempo( data , comune , temp_min , temp_max )VALUES( ’2012−10−29 ’ , ’Modena ’ , 5 , 10 ) ;


Vincoli interni ed esterni alle tabelle

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

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

Per inserire un vincolo esterno bisogna aggiungere il comandoCONSTRAINT nomeCost FOREIGN KEY(campoTabellaDaVincolare) REFERENCEStabellaSuCuiCreareIlVincolo (campo)MATCH SIMPLE ON UPDATE NO ACTION ON DELETENO ACTION


Esempi di Select

Estrarre tutti i dati presenti nella tabella tempoTrovare per ogni comune la temperatura minima e laprecipitazione per ciascun giornoCon i dati archiviati, calcolare la temperatura media diciascun comuneEstrarre le temperature e le precipitazioni e le date in cui ilcomune di Bologna non ha avuto precipitazioni.Ordinare i dati in tabella per comune e poi per comune etemperatura minimaSelezionare 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 rec i p i t az i on e , dataFROM Tempo ;

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

SELECT ∗FROM TempoWHEREComune = ’ Bologna ’ AND p r e c i p i t a t i o n = 0 . 0 ;


Esempi di Select

SELECT ∗FROM TempoORDER BY comune ;

SELECT ∗FROM TempoORDER BY Comune, temp_min ;

SELECT DISTINCT ComuneFROM Tempo ;

SELECT DISTINCT ComuneFROM TempoORDER BY Comune ;


Join tra tabelle

Estrarre tutti i comuni che sono presenti nella tabella tempoe di cui si ha la posizioneEstrarre tutti i dati delle tabelle tempo e comune legati dalnome del comuneEstrarre tutti i dati delle tabelle tempo e comune anche se ilcomune non è stato definito nella tabella comuni


Join tra tabelle

SELECT C.∗FROM Comune CWHERE C. nome in ( select t . comune from tempo )

and c . pos iz ione <> NULL ;

SELECT comune , temp_min , temp_max , p re c i p i t az i o ne ,data , pos iz ione

FROM tempo , comuneWHERE 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_medFROM Tempogroup by comune , ( temp_min + temp_max ) / 2having ( temp_min + temp_max)/2 >8 ;

Quali dati estrae questa query?


Trigger


Interfaccia graficaSelezionare il programma PostgreSql e poi pgAdminIII


Import da file

Decidere il separatore delle colonneCreare il file con tutti i dati da inserire in tabella colseparatore sceltoSelezionare la tabella su cui si vuole fare l’importImportare il file dopo aver configurato l’operazione


Esercizio

Uno studio medico vuole di realizzare un archivio che permettadi gestire i dati utilizzati dai medici che ne fanno parte. I mediciad oggi sono cinque e ognuno di loro deve avere accesso al DBdal proprio computer con una propria password. Il medico devepoter inserire, gestire e cancellare i propri pazienti. Ognipaziente avrà una scheda anagrafica che avrà almeno iseguenti 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 lesue patologie croniche, la sua percentuale di invalidità,eventuale, e i medicinali prescritti permanentemente, comel’insulina ad un diabetico per esempio.


Esercizio

All’interno di questa scheda si potranno visualizzare l’elencodelle visite del paziente. Ogni visita contiene il giorno in cui èstata effettuata, la ragione, gli eventuali medicinali prescritti e uncampo in cui sono annotabili alcune note a discrezione delmedico curante. Vi è la possibilità di allegare file di tipo jpg edoc per eventuali referti di altre visite che si ritengono collegatialla visita.


Dott.sa Sara Zuppiroli Prof. Marco Di Felice Basi di Dati ...zuppirol/Postgres.pdf · Breve...

Documents

Transcript of Dott.sa Sara Zuppiroli Prof. Marco Di Felice Basi di Dati ...zuppirol/Postgres.pdf · Breve...