3 - Informatica Scienze Applicate

CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Vogliamo creare una struttura per permettere di memorizzari i voti della classe in tutte le materie
Per fare questo untilizziamo tre tabelle
Alunni,materie,voti
Alunni
Materie
Voti
Creo un record per ogni alunno
Creo un record per ogni materia
Creao un record per ogni voto della classe
Utilizzando Microsoft Access
Si crea la tabella Anagrafica degli alunni ,le Materie e i voti si mettono alcuni campi
Si creano via via le query e si memorizzano dando loro un nome
1
CASE STUDY INFORMATICA DATABASE VOTI
Alunni
3ASA MARZO APRILE 2015
materie
2
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Voti
Come data default metto quella di oggi
Nella tabella voti metto predefinito la data odierna
Nella tabella Alunni mettiamo IDALUNNO COME CHIAVE PRIMARIA al fine di individuare univocamente un alunno
Nella tabella Materie mettiamo IDMATERIA COME CHIAVE PRIMARIA al fine di individuare univocamente una Materia
Nella tabella voti mettiamo ID COME CHIAVE PRIMARIA al fine di individuare univocamente il voto
Mentre IDALUNNO è chiave esterna ossia il suo valore è chiave primaria in un’altra tabella (Alunni)
Infine IDMATERIA è chiave esterna ossia il suo valore è chiave primaria in un’altra tabella (Materie)
La tabella Materie è tipo è del tipo codice,descrizione e serve a codificare le materie , ossia ogni volta che faccio riferimento ad una materia utilizzo un numero
Che rende tutto più veloce questo tipo di tabella si chiama TABELLA DI DOMINIO nel senso che i valori numerici che può assumere una materia sono tutti e soli
quelli che sono scritti nella tabella.
3
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Affinché il database sia a corrente di queste regole occorre fare un passo importante definire le relazioni
SI TRASCINANO I CAMPI CHE DEVONO ESSERE UGUALI IDALUNNO TRA ALUNNO E VOTI (POSSONO AVERE ANCHE NOMI DIVERSI)
IDMATERIA TRA MATERIE E VOTI (POSSONO AVERE ANCHE NOMI DIVERSI)
Cliccando col destro sulla relazione
4
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
RELAZIONI
In base alle chiavi access capisce cosa vogliamo relazionare e se metto integrità referenziale
Aggiorna campi correlati a catena : modifico IDalunno lo modifico automaticamente nei voti
elimina record correlati a catena : elimino IDalunno elimino automaticamente tutti i suoi voti
Aggiorna campi correlati a catena : modifico IDmateria in materia automaticamente nei voti
elimina record correlati a catena : elimino IDmateria in materie tutti i voti della materia
5
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
mettiamo un po’ di contenuto e cominciamo ad interrogare
TABELLA METERIE
TABELLA ALUNNI
6
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
TABELLA VOTI
Naturalmente nell’inserire i voti occorre una interfaccia che non faccia vedere i numeri interni (valori codificati) ma nome materia cognome alunno
Ma questo esula dal nostro percorso. Se voglio vedere i voti in maniera chiara basta una query che TRADUCA i numeri in DESCRIZIONI
7
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Andando in struttura siccome ho messo le relazioni ACCESS si predispone
Non mi resta che selezionare i dati che mi interessano e la tabella dei voti diventa chiara
SQL naturalmente è più complicato poiché coinvolge più tabelle
Per legare i valori si utilizza la parola chiave INNER JOIN
8
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
QUERY sui VOTI
NOME QUERY
Vista voti
DESCRIZIONE
FA VEDERE I VOTI METTENDO DESCRIZIONE
DELLA MATERIA E NOME E COGNOME
9
SQL
SELECT alunni.Cognome, alunni.Nome,
materie.Materia, Voti.Voto, Voti.Data, alunni.IDalunno,
materie.IDmateria
FROM materie INNER JOIN (alunni INNER JOIN Voti ON
alunni.IDalunno = Voti.IDalunno) ON materie.IDmateria
= Voti.IDmateria;
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
miei voti (utilizzo query precedente)
SELEZIONA TUTTI I VOTI DI UN ALUNNO
SPECIFICO
SELECT [Vista voti].Cognome, [Vista voti].Nome, [Vista
voti].Materia, [Vista voti].Voto, [Vista voti].Data
FROM [Vista voti]
WHERE ((([Vista voti].IDalunno)=3));
Vediamo adesso come possiamo fare una sintesi dei voti
Si Utilizza l’istruzione GROUP BY (raggruppa per)
Ogni volta che voglio fare qualcosa di sintetico su uno o più valori
Esempio : Sintesi per Materia : Per ogni materia voglio numero voti ,minimo,massimo,media
Esempio : Sintesi per Alunno : Per ogni alunno voglio numero voti ,minimo,massimo,media
Esempio : Sintesi per Alunno e materia : Per ogni alunno,per ogni materia voglio numero voti ,minimo,massimo,media
10
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
sintesi_alunno
PER OGNI ALUNNO NUMERO VOTI,MAX MIN
MEDIA
SELECT Voti.IDalunno, count(voto) AS numero_voti,
min(voto) AS minimo, max(voto) AS massimo,
avg(voto) AS media
FROM voti
GROUP BY IDalunno;
sintesi_materia
PER OGNI MATERIA NUMERO VOTI,MAX MIN
MEDIA
SELECT voti.IDmateria, Count(voti.voto) AS
numero_voti, Min(voti.voto) AS minimo,
Max(voti.voto) AS massimo, Avg(voti.voto) AS media
FROM voti
GROUP BY voti.IDmateria;
11
CASE STUDY INFORMATICA DATABASE VOTI
sintesi_alunno_materia
3ASA MARZO APRILE 2015
PER OGNI ALUNNO ,PER OGNI MATERIA
NUMERO VOTI,MAX MIN MEDIA
12
SELECT voti.IDalunno, voti.IDmateria, Count(voti.voto)
AS numero_voti, Min(voti.voto) AS minimo,
Max(voti.voto) AS massimo, Avg(voti.voto) AS media
FROM voti
GROUP BY voti.IDalunno, voti.IDmateria;
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Si può fare un filtro prima di aggregare sul colonna non aggegata (WHERE) e anche dopo avere aggregato (HAVING) E ANCHE ORDINARE (ORDER BY)
sintesi_alunno_materia_filtro
PER OGNI ALUNNO ,PER OGNI MATERIA
NUMERO VOTI,MAX MIN MEDIA
AVENDO PIU DI UN VOTO
SELECT voti.IDalunno, voti.IDmateria, Count(voti.voto)
AS numero_voti, Min(voti.voto) AS minimo,
Max(voti.voto) AS massimo, Avg(voti.voto) AS media
FROM voti
GROUP BY voti.IDalunno, voti.IDmateria
HAVING (((Count(voti.voto))>1));
sintesi_alunno_materia_filtro_3
PER OGNI ALUNNO ,PER OGNI MATERIA
NUMERO VOTI,MAX MIN MEDIA
Interrogati nel 2015 con media minore di 8
Dalla media più grande alla più piccola
SELECT voti.IDalunno, voti.IDmateria, Count(voti.voto)
AS numero_voti, Min(voti.voto) AS minimo,
Max(voti.voto) AS massimo, Avg(voti.voto) AS media
FROM voti
WHERE (((Year([DATA]))=2015))
GROUP BY voti.IDalunno, voti.IDmateria
HAVING (((Avg(voti.voto))<8))
ORDER BY Avg(voti.voto) DESC;
13
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Una volta praparate le basi per fare vedere esternamente le informazioni precedenti posso operare con una query di livello superiore
sintesi_alunno_materia
PER OGNI ALUNNO ,PER OGNI MATERIA
NUMERO VOTI,MAX MIN MEDIA
SELECT alunni.Cognome, alunni.Nome,
materie.Materia, sintesi_alunno_materia.numero_voti,
sintesi_alunno_materia.minimo,
sintesi_alunno_materia.massimo,
sintesi_alunno_materia.media,
sintesi_alunno_materia.IDalunno,
sintesi_alunno_materia.IDmateria
FROM (sintesi_alunno_materia INNER JOIN materie
ON sintesi_alunno_materia.IDmateria =
materie.IDmateria) INNER JOIN alunni ON
sintesi_alunno_materia.IDalunno = alunni.IDalunno;
FROM sintesi_alunno_materia
INNER JOIN materie ON sintesi_alunno_materia.IDmateria = materie.IDmateria -> prendi stessi valori materie
INNER JOIN alunni ON sintesi_alunno_materia.IDalunno = alunni.IDalunno -> prendi stessi valori alunno
14
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
15
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
N.B. le funzioni count(),min() max(),avg() si chiamano funzioni di aggregazione
Una funzione di aggregazione importante (che non abbiamo avuto necessità di usare nel nostro esempio) è sum() che esegue la somma su una colonna
Esempio se il database contiene un valore imponibile per ogni fattura RELATIVA AD UN CLIENTE
Select
idcliente,sum(IMPONIBILE) AS TOTALE From fatture group by idcliente mi d ail fatturato per ciascun cliente
16
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Come ultima parte vogliamo avere il tabellone completo man mano che metto i voti
La query fatta in precedenza e memorizzata sintesi_alunno_materia contiene solo i voti messi ma nulla mi dice su ciò che non ho messo
Vogliamo avere il tabellone dove compaiono
•
•
tutti gli alunni
per ciascuno dei quali compaiono tutte le materia e gli eventuali voti
se gli alunni sono 6 e le materie sono 10 il tabellone dovrà contenere 6*10 righe ossia 60 righe
per avere il tabellone lo chiameremo base_tabellone
chiameremo base_tabellone
Visualizza OGNI ALUNNO e OGNI MATERIA
17
SELECT alunni.Cognome, alunni.Nome,
alunni.Datadinascita, alunni.Maschio, alunni.Altezza,
materie.Materia, materie.IDmateria, alunni.IDalunno
FROM alunni, materie;
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
18
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
19
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Mancano i voti mettiamo insieme base_tabellone e riepilogo voti e materie lo chiamiamo tabellone
tabellone
Visualizza OGNI ALUNNO e OGNI
MATERIA ed eventuali voti
ORDINE ALFABETICO ALUNNI,MATERIA
Left join prende comunque tutti gli
alunni
20
SELECT base_tabellone.Cognome, base_tabellone.Nome,
base_tabellone.Materia, sintesi_alunno_materia.numero_voti,
sintesi_alunno_materia.minimo, sintesi_alunno_materia.massimo,
sintesi_alunno_materia.media, base_tabellone.IDmateria,
base_tabellone.IDalunno
FROM base_tabellone LEFT JOIN sintesi_alunno_materia ON
(base_tabellone.IDalunno = sintesi_alunno_materia.IDalunno) AND
(base_tabellone.IDmateria = sintesi_alunno_materia.IDmateria)
ORDER BY base_tabellone.Cognome, base_tabellone.Nome,
base_tabellone.Materia;
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Left join prende comunque tutte le
materie
Quelli che non hanno tutti I voti compaiono vuoti (NULL)
21
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
22
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Finalmente posso interrogare tabellone per avere la situazione di Bianchi globale,le materie in cui è stato interrogato,le materia in cui deve essere ancora
interrogato
SELECT tabellone.*
FROM tabellone
WHERE (((tabellone.Cognome)="bianchi"));
Visualizza situazione globale bianchi
bianchi
Cognome Nome
Bianchi
Bianchi
Bianchi
Bianchi
Materia
Leonardo
Ed. fisica
Leonardo
Filosofia
Leonardo
Fisica
Leonardo Informatica
Bianchi Leonardo
Inglese
Bianchi Leonardo
Italiano
Bianchi Leonardo Matematica
Bianchi Leonardo
Bianchi Leonardo
numero_voti minimo massimo media IDmateria IDalunno
1
8
1
2
7
7
Scienze
Storia
Bianchi Leonardo Storia dell'arte
23
8
7
8,5
8
10
8
3
5
3
3
3
3
7
7,75
4
1
2
3
3
3
6
7
3
3
9
3
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Visualizza situazione interrogazioni bianchi
SELECT tabellone.*, tabellone.numero_voti
FROM tabellone
WHERE (((tabellone.Cognome)="bianchi") AND
((tabellone.numero_voti) Is Not Null));
bianchi_interrogato
Cognome Nome
Materia numero_voti minimo massimo media IDmateria IDalunno
Bianchi Leonardo Informatica
1
8
8
8
5
3
Bianchi
Bianchi
Leonardo Italiano
Leonardo Matematica
1
2
24
7
7
7
8,5
7
7,75
1
2
3
3
CASE STUDY INFORMATICA DATABASE VOTI
3ASA MARZO APRILE 2015
Visualizza situazione interrogazione che deve
ancora fare bianchi
SELECT tabellone.*, tabellone.numero_voti
FROM tabellone
WHERE (((tabellone.Cognome)="bianchi") AND
((tabellone.numero_voti) Is Null));
bianchi_da_interrogare
Cognome Nome
Bianchi Leonardo
Materia
Ed. fisica
numero_voti minimo massimo media IDmateria IDalunno
10
3
Bianchi
Bianchi
Leonardo
Leonardo
Filosofia
Fisica
8
3
3
3
Bianchi
Bianchi
Leonardo
Leonardo
Inglese
Scienze
4
6
3
3
7
9
3
3
Bianchi Leonardo
Storia
Bianchi Leonardo Storia dell'arte
Notare come queste query finali sono molto semplici (NESSUN JOIN)
RAPPRESENTANO IL LIVELLO ESTERNO (ossia quella parte vicino all’utente) DEL DBMS A PARTIRE DA TABELLE E RELAZIONI CHE ABBIAMO PROGETTATO
(LIVELLO LOGICO)
esterno: descrizione di una porzione della base di
dati di interesse, per mezzo del modello logico. Nei
sistemi più moderni non è esplicitamente presente ma
è possibile definire relazioni derivate (query memorizzate chiamate
views)
25