Introduzione ai database

Raccolta di grandi quantità di dati archiviati su supporto magnetico secondo regole generali. Esso rappresenta l’evoluzione degli archivi “cartacei” permettendo all’utente di accedere velocemente, attraverso l’utilizzo dei calcolatori, ai dati di interesse.

Possibilità di poter essere utilizzato da più utenti contemporaneamente

I dati sono raccolti sotto forma di “registrazioni” logiche ognuna delle quali è suddivisa in un certo numero di campi che possono essere utilizzati per la ricerca di archivio.

I dati sono registrati tutti in un unico archivio evitando così le duplicazioni di informazioni e ottimizzando lo spazio di memoria occupato.

Possibilità di mettere in relazione le informazioni tra gli archivi presenti in modo da formare dei gruppi di informazioni omogenei.

Gestione dati (nei primi database) In una rete di utenti l’onere di gestire i requisiti di integrità dei dati e di ridondanza minima è lasciata agli utenti che devono conoscere

quindi come è stata definita la struttura dati.

Introduzione dei DBMS Data Base Management Systems

Con l’introduzione del DBMS non è più obbligatoria la conoscenza

della struttura dati e delle regole definite per mantenere l’integrità

Caratteristiche:

Gestione dei dati contenuta dentro un unico motore

Le applicazioni che lavorano sui dati non devono tener conto delle regole di integrità che vengono gestite dal DBMS

Nessuna applicazione potrà inserire dati non validi nel Database

Vantaggi:

1. Indipendenza dei dati dall’applicazione

2*. Gestione dell’integrità fisica dei dati

3**. Gestione dell’integrità logica dei dati

4. Ottimizzazione

*Gestione integrità fisica dei dati: La gestione degli accessi contemporanei ai dati da più utenti vengono interamente gestiti da DBMS in modo automatico, in modo da non mettere a rischio l’integrità dei dati.

** Mantenimento dei dati in modo che rispettino le regole di integrità delle relazioni che vengono stabilite all’atto della creazione della struttura.(Integrità referenziale).

ad esempio: In una relazione uno a molti : se viene cancellato un cliente verranno cancellati tutti i telefoni che rispettano la relazione di appartenenza al cliente cancellato.

Sicurezza e ottimizzazione

Un altro punto a favore del DBMS è la gestione centralizzata degli utenti:

Le funzioni per la gestione degli utenti permettono al DBA (Data Base Administrator) di stabilire le regole di lettura, scrittura, modifica ecc. che ogni utente ha su ogni singola unità del database

Schema

Si può stabilire all’interno del DBMS un modello che serve a stabilire le convenzioni per definire i diversi aspetti dei dati.

Tale definizione è denominata Schema e presuppone una fase di progettazione del database.

Fasi di progettazione database

Schema concettuale -> Schema logico -> Schema fisico

Schema concettuale

E’ la rappresentazione più astratta del sistema informativo che si vuol rappresentare in modo più vicino alla logica umana.

I modelli utilizzati per la definizione dello schema concettuale sono i modelli semantici. Il modello più diffuso è il Entity-Relationship (Entità – Relazione

Modello Entity-Relationship

Entità: oggetti che fanno parte del database raggruppati in insiemi omogenei.

Le proprietà caratteristiche di un insieme di oggetti omogenei vengono chiamate attributi.

(Ad esempio l’entità è la tabella dei clienti gli attributi sono i campi che la costituiscono)

Relationship : relazioni tra le entità.

Le relazioni tra due insiemi di entità A e B viene classificato in tre tipi:

a) Relazione 1 : 1 (uno a uno) ad ogni entità in A corrisponde una sola entità in B.

b) Relazione 1 : N (uno a molti) ad ogni entità di A possono corrispondere 1 o più entità di B mentre a ogni entità di B corrisponde una sola entità di A.

c) Relazione N : N (molti a molti) ad ogni entità di A possono corrispondere più entità di B e viceversa.

Schema logico

Lo schema logico del database non è altro che l’implementazione dello schema concettuale per il DBMS che si utilizza.

Dipende quindi dal tipo di DBMS che si sta utilizzando.

Schema fisico

E’ il livello di definizione dello schema in cui si decide dove le strutture definite nello schema logico vengano effettivamente registrate nel sistema che si sta utilizzando. Quindi non solo dipende dal tipo di DBMS che si sta utilizzando ma anche dalla piattaforma hardware dove è residente il DBMS.

Tipi di comandi SQL

DDL (Data Definition Language): definizione dello schema logico del DB.

DML (Data Manipulation Language): operazioni di interrogazione, inserimento, cancellazione,modifica dei dati.

DCL (Data Control Language): controllo dati, gestione utenti, ottimizzazione

Utenti del DBMS

Utenti: Eseguono operazioni semplici di DML

Programmatori: Eseguono operazioni complesse di DML

DBA: Eseguono operazioni di gestione del database utilizzando DDL e DCL

Linguaggio SQL

Con questo linguaggio siamo in grado di gestire completamente una base dati.

Esistono comandi SQL per attuare tutti e tre i tipi di operazioni:

Definizione dati: DDL

Manipolazione dati: DML

Controllo dati: DCL

Caratteristiche di SQL

La maggior parte dei linguaggi che operano sui dati agiscono su gli archivi scorrendoli dal primo all’ultimo record e verificano la validità della condizione per eseguire una particolare operazione.

Contrariamente SQL agisce su insiemi di dati che risolvono la condizione specificata: quindi non si agisce a livello di singolo record ma su insiemi di record che risolvono una condizione.

DCL (Data Control Language): controllo dati, gestione utenti, ottimizzazione

Utenti del DBMS

Utenti: Eseguono operazioni semplici di DML

Programmatori: Eseguono operazioni complesse di DML

DBA: Eseguono operazioni di gestione del database utilizzando DDL e DCL

Linguaggio SQL

Con questo linguaggio siamo in grado di gestire completamente una base dati.

Esistono comandi SQL per attuare tutti e tre i tipi di operazioni:

Definizione dati: DDL

Manipolazione dati: DML

Controllo dati: DCL

Caratteristiche di SQL

La maggior parte dei linguaggi che operano sui dati agiscono su gli archivi scorrendoli dal primo all’ultimo record e verificano la validità della condizione per eseguire una particolare operazione.

Contrariamente SQL agisce su insiemi di dati che risolvono la condizione specificata: quindi non si agisce a livello di singolo record ma su insiemi di record che risolvono una condizione.

Interrogazione database

Tutte le informazioni nel database sono contenute sotto forma di tabelle quindi qualsiasi tipo di richiesta di informazioni al database viene eseguita con il comando per l’interrogazione delle tabelle: SELECT

Comando SELECT

SELECT [ALL|DISTINCT] []

dove può essere composto da:

[clausola WHERE]

[clausola GROUP BY]

[clausola HAVING]

Clausola FROM : Indica la tabella o le tabelle dove effettuare l’interrogazione.

= FROM [ ...]

contiene un elenco di campi che vengono visualizzati all’esecuzione del comando.

Nel caso in cui nella siano presenti più di una tabella e ci siano dei campi con lo stesso nome che vengono listati allora è obbligatorio segnalare anche la tabella di appartenenza del campo.

es.: select tabella1.campo1, tabella2.campo1 from tabella1,tabella2

Ridenominazione delle intestazioni delle colonne:

select ““, ““

from

Quando si devono listare tutte le colonne allora si utilizza il carattere jolly *

Utilizzo funzioni per la decodifica dei campi

select decode(, valore_da_decodificare1, valore_decodificato1, valore_da_decodificare2, valore_decodificato2, valore_da_decodificare3, valore_decodificato3) , from

Distinct

L’opzione distinct da anteporre alla

permette di listare l’elenco dei valori di un campo specificato senza ripetizioni.

es.: ho una tabella di impiegati in cui ho il nome della città di residenza. per listare tutte le città in cui ho almeno un impiegato eseguo:

select distinct città from impiegati

Clausola WHERE:

permette di specificare le condizioni della selezione.

= WHERE

= espressione logica che quindi può valere vero o falso. Di solito è un espressione di confronto tra un campo e un valore.

Operatori di confronto

= uguale

diverso

> maggiore

= maggiore o uguale

<= minore o uguale

Operatori logici

Servono per mettere a confronto più espressioni logiche:

AND = vero se tutte e due le condizioni sono vere

OR = vero quando una delle due condizioni è vera

NOT = vero quando la condizione e falsa

Operatori di confronto avanzati

BETWEEN verifica se l’argomento è compreso in un intervallo di valori.

sintassi:

BETWEEN AND

IN verifica se l’argomento è contenuto in un’insieme di valori.

sintassi: IN (,…)

LIKE verifica se il valore del campo confrontato contiene una certa serie di caratteri. Per specificare la serie di caratteri si possono utilizzare i caratteri jolly:

“_” un singolo carattere qualsiasi

“%” un numero qualsiasi di caratteri

esempio: campo1 like ‘A%’ è vera per tutti i record in cui campo1 inizia per “A”.

IS NULL verifica se il contenuto di un campo è nullo.

Significa che non contiene alcun valore e non che contiene 0 o uno spazio.

Calcoli

L’istruzione select può contenere non solo i nomi di colonne ma anche espressioni matematiche tra i campi, i risultati vengono visualizzati nella colonna corrispondente.

operatori: * moltiplicazione, / divisione, + somma, – sottrazione

esempio: select stipendio+straordinario from stipendi where numimpiegato = 1

Funzioni che agiscono sull’intera selezione

MAX restituisce il massimo valore presente in un campo

MIN restituisce il minimo valore presente in un campo

SUM restituisce la somma dei valori in un campo

AVG restituisce la media dei valore presenti in campo

COUNT conta le righe

Clausola GROUP BY

Tali funzioni permettono di utilizzare il raggruppamento dei dati per effettuare delle operazioni su ogni singolo gruppo ottenuto.

Esempio: Supponiamo di voler stabile la media degli straordinari di ciascun impiegato.

Dobbiamo quindi raggruppare nella tabella stipendi per numero impiegato e applicare la funzione AVG sul campo straordinario

agiremo come segue:

select avg(straordinario) from stipendi group by numimpiegato

Il raggruppamento viene effettuato per numero impiegato e per ognuno di questi gruppi verrà calcolata la media del campo straordinario.

Clausola HAVING

L’utilizzo di questa clausola è associato alla clausola GROUP BY.

Come la clausola WHERE permette di porre condizioni sulla select così la clausola HAVING pone condizioni sui gruppi da considerare ma a posteriori della selezione.

Esempio: lista delle medie degli straordinari degli impiegati ragruppati per livello inferiore al terzo.

select avg(straordinario) from stipendi group by livello having livello < 3

Clausola ORDER BY

Sintassi: = [, { …}

= | [ASC|DESC]

esempio:

select * from stipendi order by livello DESC, anno

Interrogazioni su più tabelle

L’interrogazione su più tabelle presuppone l’esistenza di colonne in comune tra le tabelle. E’ indispensabile che le colonne che mettono in relazione due tabelle siano dello stesso tipo. Nel nostro esempio abbiamo le due tabelle impiegati e stipendi che hanno in comune la colonna

numero – numimpegato.

JOIN tra tabelle

Il collegamento nella select viene implementato nel seguente modo: nella clausola FROM vanno indicate le tabelle sulle quali questa join va effettuata nella clausola WHERE va espressa una particolare condizione chiamata condizione di join

I nomi delle colonne vanno sempre indicati con .

esempio: select * from impiegati,stipendi where impiegati.numero = stipendi.numimpiegato

Nella di una select si possono inserire anche delle condizioni sulla lista che vanno combinate mediante AND con la condizione di join, ovviamente si possono applicare tutte le clausole GROUP BY, HAVING, ORDER BY.

Riprendendo l’esempio precedente:

esempio: SELECT * FROM impiegati,stipendi WHERE impiegati.numero = stipendi.numimpiegato ORDER BY impiegati.numero

otteniamo così la lista degli stipendi di ogni impiegato negli anni.

Interrogazione con operatori tra insiemi

Si possono applicare gli operatori tra insiemi unione, differenza e intersezione agli insiemi di righe risultato di interrogazioni.

Si possono trattare due select come insiemi di righe e utilizzarle come operandi in una operazione insiemistica.

UNION

L’operatore UNION si applica tra due select e restituisce tutte le righe della prima più tutte le righe della seconda.

UNION [ALL]

N.B.: Non è possibile fare l’unione di due select che contengono campi differenti.

Se i valori dei campi restituiti sono uguali non vengono ripetuti nella lista ottenuta. Se si utilizza l’opzione ALL vengono inclusi anche i duplicati.

Esempio: select * from stipendi where numimpiegato = 1 and anno =1997 union

select * from stipendi where numimpiegato=1 and anno =1998

Ottengo la lista degli stipendi dell’impiegato 1 per l’anno 1997 e 1998.

select numimpiegato from stipendi where numimpiegato=1 union select numimpiegato from stipendi where numimpiegato=2

ottengo solo i due numeri 1 e 2 perché i valori uguali vengono eliminati

MINUS

Si applica tra due select e restituisce tutte le righe della prima tabella che non si trovano nella seconda.

MINUS

ovviamente le due select devono avere la stessa struttura.

INTERSECT

Si applica tra due select e restituisce tutte le righe che sono sia nella prima che contenute nella seconda select.

INTERSECT

ovviamente le due select devono avere la stessa struttura.

SUBQUERY

Istruzioni select come argomenti all’interno di altre istruzioni select

= ()

le parentesi rotonde sono obbligatorie.

Tipi di subquery:

Scalare: restituisce un valore unico.

es.: (select max(stipendio) from stipendi where numimpiegato=3)

Colonnare: restituisce una colonna con una o più righe.

es.: (select stipendio from stipendi where numimpiegato=3)

Tabellare: restituisce più colonne con una o più righe.

Utilizzo delle Subquery

Espressioni di confronto con subquery di tipo scalare.

Espressioni ALL e ANY

Espressioni IN

Calcolo di espressioni

Espressioni di confronto

Subquery in espressioni di confronto

la subquery utilizzata dev’essere scalare.

la sintassi deve essere sempre

= ()

non può essere:

()=

Non si possono fare confronti tra subquery

Nelle subquery non è possibile utilizzare le clausole HAVING e GROUP BY

Esempio: Trovare l’impiegato che ha lo stipendio maggiore.

select * from impiegati where numero = (select numimpiegato from stipendi where stipendio = (select max(stipendio) from stipendi))

La subquery più interna restituisce lo stipendio più alto, la subquery più esterna restituisce il numero dell’impiegato che percepisce questo stipendio.

Subquery: Epressioni ALL, ANY

Sintassi:

[ALL|ANY]

Sono espressioni per confrontare il contenuto di una subquery di tipo colonnare con il contenuto di un campo.

ALL verificato quando tutti i valori contenuti nella subquery colonnare applicati restituiscono vero.(AND)

ANY verificato quando risulta vero con uno dei valori restituiti dalla subquery.(OR)

1) Esempio:

select * from stipendi where straordinario >ALL ( select AVG(straordinario) from stipendi)

in questo esempio si ottiene la lista di tutti gli straordinari superiori alla media.

2) Esempio:

select * from stipendi where livello > 2 and straordinario < ANY (select straordinario from stipendi where livello 2 e che hanno straordinario < dei dipendenti con livello inferiore.

Subquery: Espressioni IN

Al posto di utilizzare l’operatore =ANY si può utilizzare l’operatore IN.

in questo caso la subquery si può considerare come un elenco di valori che se confrontati uno per uno con il campo specificato prima di IN risolvono la condizione.

Esempio: select * from impiegati where numero in (select numimpiegato from stipendi where straordinario > 200000) . restituisce l’elenco degli impiegati con straordinario > 200000

Subquery annidate

E’ possibile , per effettuare operazioni più complesse sui dati effettuare delle subquery all’interno di subquery. Il numero di subquery annidate non ha un limite tecnico in SQL.

Esempio:

select * from impiegati where numero in (select numimpiegato from stipendi where straordinario >ALL(select AVG(straordinario) from stipendi))

Comandi DML (Data Manipulation Language)

INSERT

Serve ad inserire dati nelle tabelle.

Sintassi:

insert into []

può essere o una serie di valori o una select di un’altra tabella.

Esempio 1 (serie di valori) :

insert into impiegati (numero,cognome,nome,indirizzo,cap,città,ufficio) values (3,’Bianchi’,'Antonio’,'Piazza della Vittoria 1′,16121,’Genova’,3);

La parte di specifica dei nomi dei campi nel caso di inserimento di tutti i valori può essere omessa.

Per inserire un valore nullo si deve utilizzare la parola chiave NULL.

Nel caso che la sia una tabella deve avere la stessa struttura della tabella che si sta aggiornando.

Esempio 2 :

insert into impiegati select * from impiegati2

supponendo di avere una tabella impiegati2 strutturalmente uguale a impiegati con questo comando si aggiungono i record di impiegati2 a impiegati.

UPDATE

Serve per modificare i dati nelle tabelle.

Sintassi:

= UPDATE SET = [ = espressione ...] [WHERE ]

= tabella da aggiornare

SET specifica i campi e i valori che devono assumere

WHERE specifica una condizione sull’aggiornamento delle righe.

Esempio1:

update impiegati set cap=20100 where città = ‘Milano’;

Esempio2:

update stipendi set stipendio = stipendio * 1.1 where livello (select avg(straordinario) from stipendi))

Da notare che non è possibile utilizzare le funzioni di gruppo come avg( ) senza dover effettuare delle subquery.

DELETE

= DELETE FROM [WHERE ]

se non viene specificata nessuna condizione il contenuto della tabella viene eliminato completamente.

Se le condizioni sono influenzate ricorsivamente dall’esecuzione della cancellazione questa ricorsione non viene considerata come si vede nel seguente esempio:

Esempio:

delete from stipendi where stipendio = ( select min(stipendio) from stipendi);

come si può notare la valutazione della subquery viene effettuata prima del confronto e quindi la cancellazione verrà eseguita solo per lo stipendio minimo.