Memento
Chi seguirà il modulo di e-learning?
CD di installazione del SW
Esercizio 1
Rappresentazione delle informazioni
relative ai partecipanti ad un corso di
formazione
Esercizio 1
Specifiche
 Vogliamo memorizzare dati dei partecipanti ad un corso:
nome, cognome, data di nascita, se è sposato, e, nel caso lo sia, il
numero di figli
 Vogliamo, poi ricordare le città in cui risiedono e le città in
cui sono nati, insieme al numero di abitanti.
Per le città capoluogo di regione, vogliamo ricordare la regione
 Vogliamo poi sapere le lezioni che i partecipanti hanno
frequentato, con i (o il) docenti che le hanno svolte, il
corrispondente argomento ed il giorno in cui si sono svolte.
Ad ogni lezione va associato un numero progressivo.
 Per i docenti si vuole memorizzare: nome, cognome, e tipo
di enti di provenienza
Per quelli universitari l’Università da cui provengono e la materia [hp:
unica] che insegnano e la città in cui sono è ubicata l’Università
Esercizio 1
Schema ER
numero
data
argomento
nome
(1,n)
fatta da
lezione
(1,n)
lavora
frequenta
(1,n)
docente
cognome
ente
(1,1)
universitario
materia
(1,n)
nome
università
nome
cognome
(1,n)
nome
presso
(0,n)
città
#abitanti
data di
nascita
#figli
(1,1)
partecipante
(1,1)
(1,1)
sposato
nato
risiede
(0,n)
(0,n)
capoluogo
di regione
regione
Esercizio 1
Vincolo non esprimibile in ER
 Ogni città è
 la città di nascita di un partecipante
 oppure la città di residenza di un partecipante
 oppure la città in cui è ubicata un’università
Citta[Nome]  Universita[Citta] 
Partecipante[CittaResidenza]  Partecipante[CittaNascita]
 Si potrebbe introdurre un’ulteriore generalizzazione
Esercizio 1
Schema logico
numero
nome
(1,n)
data
(1,n)
Lezione(Numero, Data, Argomento)
Docente(Nome, Cognome, Ente)
Universitario(Nome, Cognome, Ente,
Materia, Universita)
Universita(Nome, Citta)
fatta da
lezione
argomento
lavora
frequenta
(1,n)
docente
cognome
ente
(1,1)
universitario
materia
(1,n)
nome
università
(1,n)
nome
cognome
nome
partecipante
presso
(0,n)
città
#abitanti
data di
nascita
#figli
(1,1)
(1,1)
(1,1)
sposato
nato
risiede
(0,n)
(0,n)
capoluogo
di regione
Citta(Nome, NumAbitanti, Regione)
FattaDa(Lezione, NomeDoc, CognomeDoc, EnteDoc)
Partecipante(Nome, Cognome,DataNascita, NumFigli, CittaResidenza, CittaNascita)
Frequenta(Nome, Cognome, DataNascita, NumLezione)
regione
Esercizio 2
Interrogazioni in SQL
Esercizio 2
Interrogazioni SQL
 Consideriamo un semplice database relazionale con il
seguente schema:
Fornitori (F#, Nome, Città)
Componenti (C#, Nome, Colore, Peso)
Progetti (P#, Nome, Città)
Forniture (F#, C#, P#, Quantità)
 Significato di una tupla della tabella Forniture:
Il fornitore “F#” rifornisce il progetto “P#” della componente
“C#” nella quantità “Quantità”
Esercizio 2
Interrogazioni SQL
 Query 1
Elencare in ordine crescente i codici dei fornitori del progetto con
codice “P1”
 Query 2
Elencare le forniture in quantità compresa tra 300 e 750, estremi
inclusi
 Query 3
Elencare i codici delle componenti fornite da fornitori di Londra
 Query 4
Elencare i codici delle componenti fornite ai progetti da fornitori locali
(stessa città del progetto)
 Query 5
Per ognuna delle componenti fornite ad un progetto, elencare:
codice della componente, codice del progetto e corrispondente
quantità totale
Esercizio 2
Interrogazioni SQL
Query 6
Elencare i colori delle componenti fornite dal fornitore con codice ‘F1’
Query 7
Elencare i codici dei progetti riforniti interamente dal fornitore ‘F1’
Query 8
Elencare i codici dei fornitori che forniscono una stessa componente
a tutti i progetti
Query 9
Elencare i codici dei fornitori che forniscono tutte le merci fornite dal
fornitore ‘F1’
Query 10
Elencare i nomi dei fornitori che forniscono tutte le merci fornite dal
fornitore ‘F1’
Esercizio 2
Soluzione
 Query 1: Elencare in ordine crescente i codici dei fornitori del progetto ‘P1’
SELECT DISTINCT F#
FROM Forniture
WHERE P# = ‘P1’
ORDER BY F#
 Query 2: Elencare le forniture in quantità compresa tra 300 e 750
SELECT F#, C#, P#, Quantità
FROM Forniture
WHERE Quantità >= 300
AND Quantità <= 750
 Query 3: Elencare i codici delle componenti fornite da fornitori di Londra
SELECT DISTINCT C#
FROM Forniture, Fornitori
WHERE Forniture.F# = Fornitori.F#
AND Città = ‘Londra’
Esercizio 2
Soluzione
 Query 4:
Elencare i codici delle componenti fornite ai progetti da fornitori locali
SELECT DISTINCT C#
FROM Forniture, Fornitori, Progetti
WHERE Forniture.F# = Fornitori.F#
AND Forniture.P# = Progetti.P#
AND Fornitori.Città = Progetti.Città
 Query 5:
Per ognuna delle componenti fornite ad un progetto, elencare: codice
della componente, codice del progetto e corrispondente quantità totale
SELECT C#, P#, SUM (Quantità)
FROM Forniture
GROUP BY C#, P#
Esercizio 2
Soluzione
 Query 6: Elencare i colori delle componenti fornite dal fornitore ‘F1’
SELECT DISTINCT Colore
FROM Componenti
WHERE C# IN
( SELECT C#
FROM Forniture
WHERE F# = ‘F1’)
 Query 7: Elencare i codici dei progetti riforniti interamente da ‘F1’
SELECT DISTINCT P#
FROM Forniture Ftura1
WHERE NOT EXISTS
( SELECT *
FROM Forniture Ftura2
WHERE Ftura2.P# = Ftura1.P#
AND Fu2.F# ~= ‘F1’ )
Esercizio 3
Progettare una base di dati per la gestione
delle spese di un condominio
Specifiche I
 Ogni condominio ha un nome che lo identifica e un
indirizzo e comprende una più scale cui sono associati
più appartamenti
 Ad ogni scala sono associati
un codice che lo identifica insieme al nome del condominio
un valore, detto quota della scala, che rappresenta la
frazione di spese del condominio (in millesimi) che competono
agli appartamenti della scala
 Ogni appartamento è identificato, nel rispettivo
condominio, dalla scala e da un numero (l’interno).
Ad ogni appartamento è associata una quota (in
millesimi) che indica la frazione delle spese (della scala)
che sono di competenza dell’appartamento
Specifiche II
 Ogni appartamento ha un proprietario per il quale
sono di interesse il cognome, il codice fiscale e
l’indirizzo al quale deve essere inviata la
corrispondenza relativa all’appartamento.
Ogni persona ha un solo codice fiscale, ma potendo
essere proprietario di più appartamenti potrebbe avere
indirizzi diversi per appartamenti diversi. Di solito, anche
chi è proprietario di molti appartamenti ha comunque
solo uno o pochi indirizzi. In molti casi, l’indirizzo del
proprietario coincide con quello del condominio.
Specifiche III
 Per la parte contabile, è necessario tenere traccia delle spese
sostenute dal condominio e dei pagamenti effettuati dai proprietari
Ogni spesa è associata ad un intero condominio, oppure ad
una scala o un singolo appartamento
Ogni pagamento è relativo a uno e un solo appartamento
 Nella base di dati vengono mantenuti pagamenti e spese relative
all’esercizio finanziario in corso (annuale) mentre gli esercizi
precedenti vengono sintetizzati attraverso il solo saldo
precedente che per ciascun appartamento indica il debito o il
credito del proprietario. In ogni istante esiste un saldo corrente
per ciascun appartamento, definito come somma algebrica del
saldo precedente e dei pagamenti (positivi) e delle spese addebitate
(negative).
Il condominio: schema ER
Descrizione
Indirizzo
Codice
Data
conto
condominio
Spesa
Importo
(0,N)
Condominio
(1,N)
(1,1)
Singola
Blocco
Nome
Generale
Composizione
Codice
(1,1)
(1,1)
conto
scala
(0,N)
Quota
Scala
(1,N)
(1,1)
conto
appartamento
Collocazione
(0,N)
Quota
Data
Importo
Descrizione
(1,1)
Interno
Codice
Appartamento
Pagamento
(1,1)
saldo
(0,N)
Saldo precedente
Saldo corrente
(1,1)
CF
proprietà
(1,N)
Nome
Cognome
Persona
(0,1)
Recapito
Indirizzo
Regole aziendali
Regole di vincolo
(RV1) Gli importi delle spese e dei pagamenti sono numeri positivi
(RV2) I pagamenti e le spese memorizzate sono solo quelle relative
all’anno in corso
Regole di derivazione
(RD1) Le spese di condominio sono ripartite fra le scale del
condominio moltiplicando l’importo per la quota in millesimi
della scala
(RD2) Le spese di una scala sono ripartite fra gli appartamenti di
quella scala moltiplicando l’importo per la quota in millesimi
dell’appartamento
(RD3) Il saldo corrente dell’appartamento è ottenuto sommando al
saldo passato la somma dei pagamenti dell’appartamento e
sottraendo il totale delle spese dell’appartamento, delle spese
ripartite della scala e del condominio a cui l’appartamento
appartiene
(RD4) Se non specificato, il recapito del proprietario di un
appartamento coincide con l’indirizzo del condominio in cui si
trova l’appartemento
Operazioni principali
 Operazione 1: registrazione di una spesa per un
condominio
 Operazione 2: registrazione di una spesa per una scala
 Operazione 3: registrazione di una spesa per un
appartamento
 Operazione 4: registrazione di un pagamento
 Operazione 5: stampa del bilancio di ogni condominio
con il totale degli accrediti e degli addebiti per ogni
appartamento e calcolo del nuovo saldo
Tavola delle operazioni
Operazione Tipo Frequenza
Op. 1
I
5000/anno
Op. 2
I
5000/anno
Op. 3
I
50000/anno
Op. 4
I
100000/anno
Op. 5
B
1/anno
Carico
Concetto
Condominio
Scale
Appartamenti
Spesa
Spesa generale
Spesa blocco
Spesa singola
Persona
Recapito
Pagamento
Proprietà
Composizione
Collocazione
Conto condominio
Conto blocco
Conto appartamento
Saldo
Tipo Volume
E
100
E
500
E
10000
E
60000
E
5000
E
5000
E
50000
E
9000
E
1500
E
100000
R
10000
R
500
R
10000
R
5000
R
5000
R
50000
R
100000
Tavola dei volumi
Ridondanze
Indirizzo
conto
condominio
(0,N)
Nome
Condominio
(1,N)
Composizione
Codice
(1,1)
conto
scala
(0,N)
Quota
Scala
(1,N)
conto
(0,N)
appartamento
Collocazione
Quota
(1,1)
Interno
Appartamento
saldo
(0,N)
Saldo precedente
Saldo corrente
 Occupazione di memoria
Appartamenti x 4 byte =
40000 byte
 Operazioni coinvolte
Op. 1: registrazione di una
spesa di condominio
Op. 2: registrazione di una
spesa di scala
Op. 3: registrazione di una
spesa di appartamento
Op. 4: registrazione di un
pagamento
Op. 5: stampa del bilancio
Operazione 1
Indirizzo
conto
condominio
(0,N)
Nome
Op. 1: registra una spesa per un condominio
Condominio
Concetto
(1,N)
Composizione
Codice
(1,1)
conto
scala
(0,N)
Quota
Scala
(1,N)
conto
(0,N)
appartamento
E
1
S
Spesa generale
E
1
S
Conto condominio
R
1
S
Condominio
E
1
L
Composizione
R
5
L
Scala
E
5
L
Collocazione
R
100
L
Appartamento
E
100
L
Appartamento
E
100
S
con ridondanza
(1,1)
Interno
Appartamento
saldo
Spesa
Collocazione
Quota
(0,N)
Saldo precedente
Costrutto Accessi Tipo
Saldo corrente
Concetto
Costrutto Accessi Tipo
Spesa
E
1
S
Spesa generale
E
1
S
Conto Condominio
R
1
S
senza ridondanza
Operazione 2
Op. 2: registra una spesa per una scala
Codice
(1,1)
conto
scala
(0,N)
Quota
Scala
(1,N)
conto
(0,N)
appartamento
Collocazione
Quota
(1,1)
Interno
Concetto
Spesa
E
1
S
Spesa blocco
E
1
S
Conto scala
R
1
S
Scala
E
1
L
Collocazione
R
20
L
Appartamento
E
20
L
Appartamento
E
20
S
con ridondanza
Appartamento
saldo
(0,N)
Saldo precedente
Concetto
Saldo corrente
Costrutto Accessi Tipo
Costrutto Accessi Tipo
Spesa
E
1
S
Spesa blocco
E
1
S
Conto scala
R
1
S
senza ridondanza
Operazione 3
Op. 3: registra una spesa per un appartamento
Concetto
conto
(0,N)
appartamento
Quota
(1,1)
Interno
Appartamento
saldo
Spesa
E
1
S
Spesa singola
E
1
S
Conto appartamento
R
1
S
Appartamento
E
1
L
Appartamento
E
1
S
con ridondanza
(0,N)
Saldo precedente
Costrutto Accessi Tipo
Saldo corrente
Concetto
Spesa
Costrutto Accessi Tipo
E
1
S
Spesa singola
E
1
S
Conto appartamento
R
1
S
senza ridondanza
Operazione 4
Op. 4: registra un pagamento
Concetto
Quota
(1,1)
Interno
Costrutto Accessi Tipo
Pagamento
E
1
S
saldo
R
1
S
Appartamento
E
1
L
Appartamento
E
1
S
Appartamento
saldo
con ridondanza
(0,N)
Saldo precedente
Saldo corrente
Concetto
Costrutto Accessi Tipo
Pagamento
E
1
S
saldo
R
1
S
senza ridondanza
Operazione 5
Indirizzo
conto
condominio
(0,N)
Nome
Op. 5: calcolo del bilancio
Condominio
(1,N)
Concetto
Composizione
(1,1)
(0,N)
conto
scala
Codice
Quota
Scala
(1,N)
conto
(0,N)
appartamento
(1,1)
Interno
Appartamento
saldo
(0,N)
Saldo precedente
Condominio
E
100
L
Composizione
R
500
L
Scala
E
500
L
Collocazione
R
10000
L
Appartamento
E
10000
L
Appartamento
E
10000
S
con ridondanza
Collocazione
Quota
Costrutto Accessi Tipo
Saldo corrente
Operazione 5
Indirizzo
conto
condominio
(0,N)
Nome
Op. 5: calcolo del bilancio
Condominio
Concetto
(1,N)
Composizione
(1,1)
(0,N)
conto
scala
Codice
Quota
Scala
(1,N)
conto
(0,N)
appartamento
Collocazione
Quota
(1,1)
Interno
Appartamento
saldo
(0,N)
Saldo precedente
Saldo corrente
Costrutto Accessi Tipo
Spesa
E
60000
L
Spesa generale
E
5000
L
Conto condominio
R
5000
L
Condominio
E
100
L
Composizione
R
500
L
Spesa blocco
E
5000
L
Conto scala
R
5000
L
Scala
E
500
L
Collocazione
R
10000
L
Spesa singola
E
50000
L
Conto appartamento
R
50000
L
Pagamento
E
100000
L
saldo
R
100000
L
Appartamento
E
100000
L
Appartamento
E
100000
S
senza ridondanza
Scelta ridondanza
Totali
Operazione
S
L
S
Operazione
L
Operazione 1
1055000
515000
Operazione 1
0
15000
Operazione 2
205000
115000
Operazione 2
0
15000
Operazione 3
50000
200000
Operazione 3
0
150000
Operazione 4
100000
300000
Operazione 4
0
200000
Operazione 5
21100
10000
Operazione 5
256100
10000
con ridondanza
senza ridondanza
Risulta evidente che conviene rimuovere
la ridondanza!
Gerarchie
Descrizione
Descrizione
Codice
Data
Data
Importo
Singola
Spesa
Blocco
Importo
(1,1)
Generale
conto
condominio
Descrizione
(1,1)
conto
appartamento
conto
scala
(1,1)
conto
condominio
Codice
Data
Importo
(1,1)
Spesa
Generale
Codice
Spesa
Blocco
Descrizione
Codice
Data
Importo
Spesa
singola
conto
scala
(1,1)
(1,1)
conto
appartamento
 Tutte le operazioni distinguono i tre sottotipi di Spesa e quindi
conviene eliminare la gerarchia eliminando il padre e riportando
tutti gli attributi nelle figlie
Schema ristrutturato
Descrizione
Data
Codice
Spesa
Generale
Importo
(1,1)
Indirizzo
conto
condominio
(0,N)
Nome
Sono stati introdotti
identificatori ad hoc (Id)
per non usare chiavi
complesse nel
collegamento delle tabelle
(chiavi composte o basate
su stringhe)
Id
Condominio
(1,N)
Composizione
Descrizione
Codice
(1,1)
Data
Spesa
Blocco
Importo
Descrizione
Spesa
singola
Data
Importo
(0,N)
(1,1)
Descrizione
Quota
(1,N)
conto
appartamento
(0,N)
Collocazione
Quota
(1,1)
Interno
Id
Codice
Appartamento
Pagamento
Codice
Scala
Codice
Data
Importo
conto
scala
(1,1)
Id
(1,1)
saldo
(0,N)
(1,1)
CF
proprietà
(1,N)
Nome Cognome
Persona
(0,1)
Saldo precedente
Recapito
Indirizzo
Id
Schema relazionale
Indirizzo
Nome
Id
Id
Nome
Indirizzo
Condominio
Condomini
(1,N)
Id
Codice
Condominio
Composizione
(1,1)
Id
Scale
Codice
Id Interno
Quota
Scala
Scala
Quota Proprietario Recapito* Saldo
Appartamenti
(1,N)
Id
indirizzo
Collocazione
Quota
(1,1)
Interno
Id
Appartamento
(1,1)
CF
proprietà
(1,N)
Nome Cognome
Recapiti
Id
Persona
Id
(0,1)
Saldo precedente
Quota
Nome
Cognome
CF
Persone
Recapito
Indirizzo
Schema relazionale
Descrizione
Codice
Data
Spesa
Generale
(1,1)
Indirizzo
Id
Nome
Nome Indirizzo
Id
(0,N)
conto
Condominio
condominio
Id
Condomini
Codice Condominio
(1,N)
Quota
Scale
Importo
Composizione
Data DescrizioneCodice
Spesa
Blocco
(1,1)
Quota
conto
scala
Importo
Data Descrizione
Codice
Spesa
singola
Data
Pagamento
Importo
Codice
Codice
(1,1)
saldo
Quota Proprietario Recapito* Saldo
Appartamenti
codice Appartamento importo data descrizione
Scala
(1,N)
Pagamenti
codice appartamento importo data descrizione
(0,N)
(1,1)
Interno
Id
Spese_singole
codice
scala
importo data descrizione
Appartamento
Spese_blocco
(0,N)
(1,1)
Scala
Collocazione
conto
appartamento
(1,1)
Importo
Descrizione
(0,N)
Id Interno
Id
Saldo precedente
Quota
codice
condominio importo data descrizione
Spese_generali
Creazione delle tabelle SQL
create schema condomini
set schema condomini
create table condomini (
id smallint not null primary key,
nome varchar(20) not null unique,
indirizzo varchar(50))
create table scale (
id smallint not null primary key,
codice char(1) not null,
condominio smallint not null references condomini(id),
quota smallint not null,
unique(codice,condominio))
create table persone (
id integer not null primary key,
nome varchar(15) not null,
cognome varchar(15) not null,
CF char(16))
Creazione delle tabelle SQL
II
create table recapiti (
id integer not null primary key,
indirizzo varchar(50) not null)
create table appartamenti (
id integer not null primary key,
interno char(4) not null,
scala smallint not null references scale(id),
proprietario smallint not null references persone(id),
recapito smallint references recapiti(id),
quota smallint not null,
saldo decimal(6,2) not null,
unique(interno,scala))
Creazione delle tabelle SQL
III
create table pagamenti (
codice integer not null primary key,
data date not null,
descrizione varchar(1000),
importo decimal(7,2) not null,
appartamento integer not null references appartamenti(id))
create table spesa_generale (
codice integer not null primary key,
data date not null,
descrizione varchar(1000),
importo decimal(7,2) not null,
condominio integer not null references condomini(id))
create table spesa_blocco (
codice integer not null primary key,
data date not null,
descrizione varchar(1000),
importo decimal(7,2) not null,
scala integer not null references scale(id))
Creazione delle tabelle SQL
IV
create table spesa_singola (
codice integer not null primary key,
data date not null,
descrizione varchar(1000),
importo decimal(7,2) not null,
appartamento integer not null references appartamenti(id))
Operazioni
Op. 1: registra una spesa per un condominio
insert into spesa_generale(codice,data,descrizione,
importo,condominio)
values ((select max(codice)+1 from spesa_generale),
:data, :descrizione,:importo,
(select id from condomini where nome=:nome))
Op. 2: registra una spesa per una scala
insert into spesa_blocco(codice,data,descrizione,
importo,scala)
values ((select max(codice)+1 from spesa_blocco),
:data, :descrizione,:importo,
(select scale.id from scale, condomini
where condomini.nome=:nome
and scale.codice=:codice
and scale.condominio=condomini.id))
Operazioni
Op. 3: registra una spesa per un appartamento
insert into spesa_singola(codice,data,descrizione,
importo,appartamento)
values ((select max(codice)+1 from spesa_singola),
:data, :descrizione,:importo,
(select appartamenti.id from scale,condomini,appartamenti
where condomini.nome=:nome
and scale.codice=:codice
and appartamenti.interno=:interno
and scale.condominio=condomini.id
and appartamenti.scala=scale.id))
Operazioni
Op. 4: registra un pagamento
insert into pagamenti(codice,data,descrizione,
importo,appartamento)
values ((select max(codice)+1 from pagamenti),
:data, :descrizione,:importo,
(select appartamenti.id from scale,condomini,appartamenti
where condomini.nome=:nome
and scale.codice=:codice
and appartamenti.interno=:interno
and scale.condominio=condomini.id
and appartamenti.scala=scale.id))
Operazioni
Op. 5: calcola bilancio
create view bilancio_condominio(id,bilancio) as
select id, sum(importo)from condomini, spese_generali
where condomini.id=spese_generali.condominio
group by id
create view tot_spese_blocco(id,tot) as
select scale.id, sum(importo) from scale, spese_blocco
where scale.id=spese_blocco.scala
group by scale.id
create view parte_scale_in_cond(id,tot) as
select scale.id, bilancio*quota/1000
from scale,bilancio_condominio
where scale.condominio= bilancio_condominio.id
create view bilancio_scale(id,bilancio) as
select tsb.id,tsb.tot + psic.tot
from tot_spese_blocco as tsb, parte_scale_in_cond as psic
where tsb.id= psic.id
Operazioni
create view tot_spese_singole(id,tot) as
select appartamenti.id, sum(importo)
from appartamenti, spese_appartamento
where appartamenti.id=spese_blocco.appartamento
group by appartamenti.id
create view parte_app_in_scale(id,tot) as
select appartamenti.id, bilancio*quota/1000
from appartamenti,bilancio_scale
where appartamenti.scala=bilancio_scale.id
create view tot_spese_appartamento(id,codice,tot) as
select tss.id, tss.tot + pais.tot
from tot_spese_singole as tss, parte_app_in_scale as pais
where tss.id= pais.id
create view tot_pag_appartamento(id,interno,tot) as
select appartamenti.id, interno, sum(importo)
from appartamenti, pagamenti
where appartamenti.id=pagamenti.appartamento
group by appartamenti.id, interno
Operazioni
create view bilancio(condominio, scala, interno, crediti,
debiti,saldo_precedente,nuovo_saldo) as
select condominio.nome, scala.codice, appartamenti.interno,
pagamenti.tot, spese.tot,
appartamenti.saldo,
appartamenti.saldo + pagamenti.tot - spese.tot
from condomini, scale, appartamenti,
tot_spese_appartamento as spese,
tot_pag_appartamento as pagamenti
where appartamenti.scala=scale.id
and scale.condominio=condomini.id
and spese.id=appartamenti.id
and pagamenti.id=appartamenti.id
order by condomini.nome, scale.codice, appartamenti.interno