Memento zChi seguirà il modulo di e-learning? zCD di installazione del SW.

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

lezione

numero

data

argomento

lezione

numero

data

argomento

numero

data

argomento

sposato#figli

sposato#figli#figli

partecipante

nome

cognome

data dinascita

partecipante

nome

cognome

data dinascita

nome

cognome

data dinascita

universitariomateria

universitariomateriamateria

città

nome

#abitanti

città

nome

#abitanti

nome

#abitanti

capoluogodi regione

regionecapoluogodi regione

regioneregione

docentecognome

ente

nome

docentecognome

ente

nome

cognome

ente

nome

risiede (0,n)(1,1)risiede (0,n)(1,1)

frequenta

(1,n)

(1,n)

frequenta

(1,n)

(1,n)

fattada(1,n) (1,n)

fattadafattada(1,n) (1,n)

universitànome

universitànomenome

nato(1,1) (0,n)

nato(1,1) (0,n)

lavora

(1,n)

(1,1)lavora

(1,n)

(1,1)

presso(1,1) (0,n)

presso(1,1) (0,n)

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

Lezione(Numero, Data, Argomento)

Docente(Nome, Cognome, Ente)

Universitario(Nome, Cognome, Ente,

Materia, Universita)

Universita(Nome, Citta)

Citta(Nome, NumAbitanti, Regione)

FattaDa(Lezione, NomeDoc, CognomeDoc, EnteDoc)

Partecipante(Nome, Cognome,DataNascita, NumFigli, CittaResidenza,

CittaNascita)

Frequenta(Nome, Cognome, DataNascita, NumLezione)

lezione

numero

data

argomento

lezione

numero

data

argomento

numero

data

argomento

sposato#figli

sposato#figli#figli

partecipante

nome

cognome

data dinascita

partecipante

nome

cognome

data dinascita

nome

cognome

data dinascita

universitariomateria

universitariomateriamateria

città

nome

#abitanti

città

nome

#abitanti

nome

#abitanti

capoluogodi regione

regionecapoluogodi regione

regioneregione

docentecognome

ente

nome

docentecognome

ente

nome

cognome

ente

nome

risiede (0,n)(1,1)risiede (0,n)(1,1)

frequenta

(1,n)

(1,n)

frequenta

(1,n)

(1,n)

fattada(1,n) (1,n)

fattadafattada(1,n) (1,n)

universitànome

universitànomenome

nato(1,1) (0,n)

nato(1,1) (0,n)

lavora

(1,n)

(1,1)lavora

(1,n)

(1,1)

presso(1,1) (0,n)

presso(1,1) (0,n)

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 2Elencare le forniture in quantità compresa tra 300 e 750, estremi inclusi

Query 3Elencare i codici delle componenti fornite da fornitori di Londra

Query 4Elencare i codici delle componenti fornite ai progetti da fornitori locali (stessa città del progetto)

Query 5Per ognuna delle componenti fornite ad un progetto, elencare: codice della componente, codice del progetto e corrispondente quantità totale

Esercizio 2 Interrogazioni SQLQuery 6

Elencare i colori delle componenti fornite dal fornitore con codice ‘F1’

Query 7Elencare i codici dei progetti riforniti interamente dal fornitore ‘F1’

Query 8Elencare i codici dei fornitori che forniscono una stessa componente a tutti i progetti

Query 9Elencare i codici dei fornitori che forniscono tutte le merci fornite dal fornitore ‘F1’

Query 10Elencare 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 FornitureWHERE P# = ‘P1’ORDER BY F#

Query 2: Elencare le forniture in quantità compresa tra 300 e 750

SELECT F#, C#, P#, QuantitàFROM FornitureWHERE Quantità >= 300AND Quantità <= 750

Query 3: Elencare i codici delle componenti fornite da fornitori di Londra

SELECT DISTINCT C#FROM Forniture, FornitoriWHERE 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, ProgettiWHERE 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 FornitureGROUP BY C#, P#

Esercizio 2 Soluzione

Query 6: Elencare i colori delle componenti fornite dal fornitore ‘F1’

SELECT DISTINCT ColoreFROM ComponentiWHERE 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 Ftura1WHERE 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 condominiocondominio ha un nome che lo identifica e un

indirizzo e comprende una più scalescale cui sono associati più appartamentiappartamenti

Ad ogni scala sono associati un codicecodice che lo identifica insieme al nome del condominio un valore, detto quota della scalaquota 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’internointerno). Ad ogni appartamento è associata una quotaquota (in millesimi) che indica la frazione delle spese (della scala) che sono di competenza dell’appartamento

Specifiche II

Ogni appartamento ha un proprietarioproprietario per il quale sono di interesse il cognomecognome, il codice codice fiscalefiscale e l’indirizzoindirizzo 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 spesaspesa è associata ad un intero condominio, oppure

ad una scala o un singolo appartamento Ogni pagamentopagamento è 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 precedentesaldo precedente che per ciascun appartamento indica il debito o il credito del proprietario. In ogni istante esiste un saldo correntesaldo corrente per ciascun appartamento, definito come somma algebrica del saldo precedente e dei pagamenti (positivi) e delle spese addebitate (negative).

Il condominio: schema ER

Condominio

Composizione

IndirizzoNome

(1,N)

(1,1)

Scala

Appartamento

Collocazione

Persona

Spesa

Pagamento

Generale

Blocco

Singola

proprietà

saldo

contocondominio

contoscala

contoappartamento

(1,N)

(1,1)

(1,1) (1,N)

(0,1)

(1,1) (0,N)

(1,1)

(1,1) (0,N)

(0,N)

(1,1) (0,N)

Codice

Quota

Interno

Saldo precedenteSaldo corrente

Indirizzo

Data Descrizione Codice

Importo

Importo

Data

Descrizione Codice

CFNome

CognomeQuota

Recapito

(1,1)

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 corsoRegole di derivazione

(RD1) Le spese di condominio sono ripartite fra le scale delcondominio moltiplicando l’importo per la quota in millesimidella scala

(RD2) Le spese di una scala sono ripartite fra gli appartamenti diquella scala moltiplicando l’importo per la quota in millesimidell’appartamento

(RD3) Il saldo corrente dell’appartamento è ottenuto sommando alsaldo passato la somma dei pagamenti dell’appartamento esottraendo il totale delle spese dell’appartamento, delle speseripartite della scala e del condominio a cui l’appartamentoappartiene

(RD4) Se non specificato, il recapito del proprietario di unappartamento coincide con l’indirizzo del condominio in cui sitrova l’appartemento

Operazioni principali

Operazione 1Operazione 1: registrazione di una spesa per un condominio Operazione 2Operazione 2: registrazione di una spesa per una scala Operazione 3Operazione 3: registrazione di una spesa per un appartamento Operazione 4Operazione 4: registrazione di un pagamento Operazione 5Operazione 5: stampa del bilancio di ogni condominio con il totale degli accrediti e degli

addebiti per ogni appartamento e calcolo del nuovo saldo

Operazione Tipo FrequenzaOp. 1 I 5000/annoOp. 2 I 5000/annoOp. 3 I 50000/annoOp. 4 I 100000/annoOp. 5 B 1/anno

Tavola delle operazioni

Carico

Concetto Tipo VolumeCondominio E 100Scale E 500Appartamenti E 10000Spesa E 60000Spesa generale E 5000Spesa blocco E 5000Spesa singola E 50000Persona E 9000Recapito E 1500Pagamento E 100000Proprietà R 10000Composizione R 500Collocazione R 10000Conto condominio R 5000Conto blocco R 5000Conto appartamento R 50000Saldo R 100000

Tavola dei volumi

Ridondanze

Occupazione di memoriaOccupazione di memoriaAppartamenti x 4 byte = 40000 byte

Operazioni coinvolteOperazioni 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

Condominio

Composizione

IndirizzoNome

(1,N)

(1,1)

Scala

Appartamento

Collocazione

saldo

contocondominio

contoscala

(1,N)

(1,1)

(0,N)

(0,N)

(0,N)

(0,N)

Codice

Quota

Interno


Quota

contoappartamento

Operazione 1

Op. 1: registra una spesa per un condominio

Concetto Costrutto Accessi Tipo

Spesa 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


Spesa E 1 S

Spesa generale E 1 S

Conto Condominio R 1 S

senza ridondanza

Condominio

Composizione

IndirizzoNome

(1,N)

(1,1)

Scala

Appartamento

Collocazione

saldo

contocondominio

contoscala

(1,N)

(1,1)

(0,N)

(0,N)

(0,N)

(0,N)

Codice

Quota

Interno


Quota

contoappartamento

Operazione 2

Op. 2: registra una spesa per una scala


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


Spesa E 1 S

Spesa blocco E 1 S

Conto scala R 1 S

senza ridondanza

(1,1)

Scala

Appartamento

Collocazione

saldo

contoscala

(1,N)

(1,1)

(0,N)

(0,N)

(0,N)

Codice

Quota

Interno


Quota

contoappartamento

Operazione 3

Op. 3: registra una spesa per un appartamento


Spesa E 1 S

Spesa singola E 1 S

Conto appartamento R 1 S

Appartamento E 1 L

Appartamento E 1 S

con ridondanza


Spesa E 1 S

Spesa singola E 1 S

Conto appartamento R 1 S

senza ridondanza

Appartamento

saldo

(1,1)

(0,N)

(0,N)

Interno


Quota

contoappartamento

Operazione 4

Op. 4: registra un pagamento


Pagamento E 1 S

saldo R 1 S

Appartamento E 1 L

Appartamento E 1 S

con ridondanza


Pagamento E 1 S

saldo R 1 S

senza ridondanza

Appartamento

saldo

(1,1)

(0,N)

Interno


Quota

Operazione 5

Op. 5: calcolo del bilancio

con ridondanza

Condominio

Composizione

IndirizzoNome

(1,N)

(1,1)

Scala

Appartamento

Collocazione

saldo

contocondominio

contoscala

(1,N)

(1,1)

(0,N)

(0,N)

(0,N)

(0,N)

Codice

Quota

Interno


Quota

contoappartamento


Condominio E 100 L

Composizione R 500 L

Scala E 500 L




Operazione 5

Op. 5: calcolo del bilancio


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


Spesa singola E 50000 L

Conto appartamento R 50000 L

Pagamento E 100000 L

saldo R 100000 L



senza ridondanza

Condominio

Composizione

IndirizzoNome

(1,N)

(1,1)

Scala

Appartamento

Collocazione

saldo

contocondominio

contoscala

(1,N)

(1,1)

(0,N)

(0,N)

(0,N)

(0,N)

Codice

Quota

Interno


Quota

contoappartamento

Scelta ridondanza

Risulta evidente che conviene rimuovere la ridondanza!

Totali

Operazione L S

Operazione 1 1055000 515000

Operazione 2 205000 115000

Operazione 3 50000 200000

Operazione 4 100000 300000


con ridondanza

Operazione L S






senza ridondanza

Gerarchie

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

Spesa

Generale

Blocco

Singola

contocondominio

contoscala

contoappartamento

(1,1)

(1,1)

(1,1)

Importo

Data

Descrizione Codice

SpesaGenerale

SpesaBlocco

contocondominio

contoscala

contoappartamento

(1,1)

(1,1)

(1,1)

Importo

Data

Descrizione Codice

Importo

Data

Descrizione Codice

Spesasingola

Importo

Data

Descrizione Codice

Schema ristrutturato

Condominio

Composizione

IndirizzoNome

(1,N)

(1,1)

Scala

Appartamento

Collocazione

Persona

Pagamento

proprietà

saldo

contocondominio

contoscala

contoappartamento

(1,N)

(1,1)

(1,1) (1,N)

(0,1)

(0,N)

(0,N)

(0,N)

(1,1) (0,N)

Codice

Quota

Interno

Saldo precedente

Indirizzo

Data Descrizione Codice

Importo

CFNomeCognomeQuota

Recapito

SpesaGenerale

SpesaBlocco

(1,1)

(1,1)

(1,1)

Importo

Data

Descrizione Codice

Importo

Data

Descrizione Codice

Spesasingola

Importo

Data

DescrizioneCodice

Id

Id

Id

Id

Sono stati introdotti identificatori ad hoc (Id) per non usare chiavi complesse nel collegamento delle tabelle (chiavi composte o basate su stringhe)

Schema relazionale

Scale

Id Nome Indirizzo

Condomini

Appartamenti

IdInternoScalaQuotaProprietarioRecapito*Saldo

Id Nome Cognome CF

Persone

Id Codice Condominio Quota

Id indirizzo

Recapiti

Condominio

Composizione

IndirizzoNome

(1,N)

(1,1)

Scala

Appartamento

Collocazione

Personaproprietà

(1,N)

(1,1)

(1,1) (1,N)

(0,1)

Codice

Quota

Interno

Saldo precedente

Indirizzo

CFNomeCognomeQuota

Recapito

Id

Id

Id

Id

Schema relazionale

Scale

Id Nome IndirizzoCondomini

Appartamenti

Pagamenti

Id Codice Condominio Quota

codice Appartamento importo data descrizione

Spese_generali

codice condominio importo data descrizione

Condominio

Composizione

IndirizzoNome

(1,N)

(1,1)

Scala

Appartamento

Collocazione

Pagamento saldo

contocondominio

contoscala

contoappartamento

(1,N)

(1,1)

(0,N)

(0,N)

(0,N)

(1,1)

(0,N)

Interno

Saldo precedente

DataDescrizione

Codice

Importo

Quota

SpesaGenerale

SpesaBlocco

(1,1)

(1,1)

(1,1)

Importo

Data DescrizioneCodice

Importo

DataDescrizioneCodice

Spesasingola

Importo

DataDescrizioneCodice

Id

Id

Id

Quota

Codice

IdInternoScalaQuotaProprietarioRecapito*Saldo

codice scala importo data descrizione

Spese_blocco

codice appartamento importo data descrizione

Spese_singole

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

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. 1: registra una spesa per un condominio

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))

Op. 2: registra una spesa per una scala

Operazioni

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))

Op. 3: registra una spesa per un appartamento

Operazioni

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))

Op. 4: registra un pagamento

Operazioni

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

Op. 5: calcola bilancio

Operazionicreate 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

Memento zChi seguirà il modulo di e-learning? zCD di installazione del SW.

Documents

Transcript of Memento zChi seguirà il modulo di e-learning? zCD di installazione del SW.