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] <lista di campi> <espressione1 > [<clausola ORDER BY>]

dove <espressione1> può essere composto da:

<clausola FROM>

[clausola WHERE]

[clausola GROUP BY]

[clausola HAVING]

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

 <clausola FROM> = FROM <nome tabella> [<nome tabella> ...]

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

Nel caso in cui nella <clausola FROM> 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 <campo1>  “<Intestazione colonna1>“, <campo2>  “<Intestazione colonna2>“

from <tabella>

 

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

 

---

Utilizzo funzioni per la decodifica dei campi

  select decode(<campo1>, valore_da_decodificare1, valore_decodificato1, valore_da_decodificare2, valore_decodificato2, valore_da_decodificare3, valore_decodificato3)  , <campo2>  from <tabella>

---

Distinct

L’opzione distinct da anteporre alla

<lista campi> 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.

<clausola WHERE> = WHERE <condizione>

<condizione> = 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

< minore

>= 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:

    <campo> BETWEEN <valore1> AND <valore2> 

 

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

sintassi: <campo> IN (<valore1>,<valore2>...)

---

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: <clausola ORDER BY>= <ordinamento>[, {<ordinamento> ...}

    <ordinamento> = <nome colonna> | <numero colonna> [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 <nome tabella>.<nome colonna>

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

Nella <clausola WHERE> 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.

<select1> UNION [ALL] <select2>

 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.

<select1> MINUS <select2>

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.

<select1> INTERSECT <select2>

ovviamente le due select devono avere la stessa struttura.

---

SUBQUERY

Istruzioni select come argomenti all’interno di altre istruzioni select

    <subquery> = (< 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

    <nome campo> = (<subquery scalare>)

    non può essere:

    (<subquery scalare>)=<nome campo>

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:

<nome campo> <operatore di confronto> [ALL|ANY] <subquery>

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)

in questo esempio si ottengono tutti i record di 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 <tabella> [<lista campi>] <provenienza>

<provenienza> 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 <provenienza> 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:

<comando update> = UPDATE <tabella> SET <campo> = <espressione> [<campo> = espressione ...] [WHERE <condizione>]

<tabella> = 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 <3;

 N.B.: il significato dell’assegnazione è il seguente: al contenuto del campo stipendio assegno il proprio valore * 1.1.

 Esempio 3:
update impiegati set ufficio = 2 where ufficio = 3 and numero in (select numimpiegato from stipendi where straordinario > (select avg(straordinario) from stipendi))

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

---

DELETE

<comando DELETE> = DELETE FROM <tabella> [WHERE <condizione>]

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.