Lezione 2 - SQL: Relazioni tra tabelle
Che cos'è una relazione?
Relazioni tra tabelle diverse
Parole del linguaggio: INNER JOIN ... ON
Tipi di relazione: Uno-A-Molti
Tipi di relazione: Uno-A-Uno
Tipi di relazione: Molti-A-Molti
Funzione della tabella EsamiStudenti: osservazione finale
Una relazione, naturalmente si parla di database relazionali, è un legame logico che permette di aggregare informazioni.
In un DB una tabella è una relazione, vale a dire una "legge" che mette insieme opportunamente le informazioni
che individua, a formare righe (record) e colonne (campi) della tabella stessa.
Spesso è necessario aggregare dati memorizzati in tabelle diverse.
Un esempio chiarirà il concetto. Se si vogliono elencare gli esami sostenuti dagli studenti degli esempi precedenti,
può essere necessario un insieme di informazioni del tipo:
Elenco esami
| ID |
Cognome |
Nome |
Data Nascita |
Esame |
Data |
Voto |
| 001 |
Moro |
Stefano |
06/06/1981 |
Analisi 1 |
12/06/2001 |
25 |
| 001 |
Moro |
Stefano |
06/06/1981 |
Geometria |
13/07/2001 |
28 |
| 001 |
Moro |
Stefano |
06/06/1981 |
Fisica 1 |
25/07/2001 |
27 |
| 002 |
Banfi |
Marco |
26/07/1981 |
Analisi 1 |
12/06/2001 |
28 |
| 002 |
Banfi |
Marco |
26/07/1981 |
Geometria |
20/07/2001 |
21 |
| 003 |
Colombo |
Ivan |
31/03/1982 |
Analisi 1 |
12/07/2001 |
30 |
| 003 |
Colombo |
Ivan |
31/03/1982 |
Fisica 1 |
25/07/2001 |
18 |
In un database correttamente impostato non è proponibile una tabella così strutturata. Infatti si può notare facilmente
come ci siano dati ripetuti diverse volte.
Che cosa comporta la memorizzazione di informazioni ripetute? Almeno due tipi di inconvenienti:
- Un'occupazione di spazio maggiore: si riporta la stessa informazione più volte
- Una maggiore difficoltà di mantenere i dati coerenti: tutte le copie della stessa informazione devono essere
sempre identiche
Tutto questo senza contare il maggiore rischio di commettere errori nell'inserimento dati: più si scrive, maggiore è la
probabilità di sbagliare
In breve, la data di nascita, il nome e il cognome sono informazioni sempre uguali
per una persona, ma potrebbe essere richiesto di doverle visualizzare come nell'esempio riportato.
Relazioni tra tabelle diverse
Come si risolve il problema?
Innanzi tutto dividendo le informazioni. Infatti si dovranno memorizzare una sola volta i dati anagrafici di ogni
studente. Ci sarà per questo una tabella specifica (Studenti). Servirà quindi una seconda tabella (Voti) in cui registrare i dati degli esami
sostenuti.
Studenti
| ID_S |
Cognome |
Nome |
Data Nascita |
...Altro |
| 001 |
Moro |
Stefano |
06/06/1981 |
... |
| 002 |
Banfi |
Marco |
26/07/1981 |
... |
| 003 |
Colombo |
Ivan |
31/03/1982 |
... |
|
Voti
| ID_S |
Esame |
Data |
Voto |
| 001 |
Analisi 1 |
12/06/2001 |
25 |
| 001 |
Geometria |
13/07/2001 |
28 |
| 001 |
Fisica 1 |
25/07/2001 |
27 |
| 002 |
Analisi 1 |
12/06/2001 |
28 |
| 002 |
Geometria |
20/07/2001 |
21 |
| 003 |
Analisi 1 |
12/07/2001 |
30 |
| 003 |
Fisica 1 |
25/07/2001 |
18 |
|
Il campo ID_S è l'identificatore di uno studente; è la chiave primaria della tabella Studenti, quindi ne identifica univocamente
ogni record. Nella tabella Voti il campo ID_S è necessario per individuare la persona che ha sostenuto un esame in quella data,
con quella valutazione. Sarà
l'unico riferimento allo studente presente in quest'ultima tabella.
Tutte le altre informazioni possono essere recuperate stabilendo una relazione tra la tabella Studenti e la tabella Voti.
Parole del linguaggio: INNER JOIN ... ON
La sintassi relativa alla definizione di una relazione tra due tabelle è
nome_tab1 INNER JOIN nome_tab2 ON nome_tab1.nome_campo1=nome_tab2.nome_campo2
significa che verranno aggregati record provenienti dalle due tabelle sulla base dell'uguaglianza del contenuto dei due campi
indicati
Istruzione SQL
SELECT STUDENTI.ID_S, STUDENTI.Cognome, STUDENTI.Nome, STUDENTI.Data_Nascita, VOTI.Esame, VOTI.Data, VOTI.Voto
FROM STUDENTI INNER JOIN VOTI ON STUDENTI.ID_S = VOTI.ID_S
Per ogni record della tabella Voti (quindi per ogni esame sostenuto da uno studente) vengono individuate le informazioni
dello studente interessato, prese dalla tabella Studenti
Tipi di relazione: Uno-A-Molti
Il tipo di relazione dell'esempio precedente è il più frequente. Si tratta di una relazione Uno-A-Molti (1-M). Significa
che ad un record della tabella-lato-uno, nel nostro caso Studenti, ne possono corrispondere molti dell'altra tabella (Voti),
mentre non è vero il contrario.
In pratica per ogni studente ci saranno diversi record nella tabella Voti, mentre ogni voto (ogni esame sostenuto) farà
capo ad un unico studente.
Di norma per realizzare una relazione di questo tipo si inserisce nella tabella lato-Molti (nel
nostro caso, Voti) un campo che corrisponde alla la chiave primaria della tabella lato-Uno (Studenti). Questo campo
aggiunto prende il nome di chiave esterna . Nell'esempio considerato, la chiave esterna per la tabella Voti è il campo ID_S,
che ha la funzione di chiave primaria nella tabella Studenti.
Tipi di relazione: Uno-A-Uno
La relazione più semplice tra due tabelle è quella definita Uno-A-Uno (1-1). Significa che ad un record di una delle due tabelle
della relazione, ne corrisponde uno dell'altra tabella e viceversa.
Un esempio anche per questo:
Studenti
| ID_S |
Cognome |
Nome |
Data Nascita |
...Altro |
| 001 |
Moro |
Stefano |
06/06/1981 |
... |
| 002 |
Banfi |
Marco |
26/07/1981 |
... |
| 003 |
Colombo |
Ivan |
31/03/1982 |
... |
| 004 |
Pozzi |
Maurizio |
31/08/1977 |
... |
| 005 |
Tarantini |
Andrea |
03/05/1976 |
... |
|
Esami di Laurea
| ID_S |
Data |
Voto |
| 004 |
12/06/2001 |
94/100 |
| 005 |
12/06/2001 |
100/100 |
|
Per ogni studente ci sarà un solo record nella tabella Esami di Laurea, ed ogni voto di laurea è proprio di un solo
studente. Non è detto che il numero di record delle due tabelle sia uguale. In generale non è così.
Perchè realizzare due tabelle diverse e non mettere tutti i campi in una tabella unica? Nel caso in esame, per esempio,
non tutti gli studenti hanno i dati dell'esame di laurea. Finchè non ci saranno questi dati (per qualcuno non ci saranno mai)
è inutile tenere spazio occupato per campi privi di informazione. Infatti è questo che avverrebbe se avessimo una tabella
Studenti comprensiva dei campi Data_Laurea e Voto_Laurea.
A partire dalle tabelle
Studenti (ID_S, Cognome, Nome, Data_Nascita,....)
Esami di Laurea(ID_S, Data, Voto)
scrivere il codice SQL necessario a realizzare il seguente set di dati:
Studenti Laureati
| ID_S |
Cognome |
Nome |
Data_Nascita |
Data |
Voto |
| 004 |
Pozzi |
Maurizio |
31/08/1977 |
12/06/2001 |
94/100 |
| 005 |
Tarantini |
Andrea |
03/05/1976 |
12/06/2001 |
100/100 |
A partire dalle stesse tabelle
Studenti (ID_S, Cognome, Nome, Data_Nascita,....)
Esami di Laurea(ID_S, Data, Voto)
scrivere il codice SQL necessario a realizzare il seguente set di dati, ordinato rispetto alla data di nascita, senza che
quest'ultima venga visualizzata:
Studenti Laureati
| ID_S |
Cognome |
Nome |
Data |
Voto |
| 005 |
Tarantini |
Andrea |
12/06/2001 |
100/100 |
| 004 |
Pozzi |
Maurizio |
12/06/2001 |
94/100 |
Tipi di relazione: Molti-A-Molti
La situazione più complessa è quella realizzata attraverso una relazione Molti-A-Molti (M-M). In questo caso ad
ogni record di una delle due tabelle coinvolte possono corrispondere più record nell'altra tabella e viceversa.
Se ne può consideare un'applicazione nell'esempio esaminato degli studenti. Se si rende necessario memorizzare
ulteriori informazioni relative ad ogni singola materia d'esame, si deve ricorrere all'utilizzo di una tabella specifica per
gli esami stessi.
MaterieDEsame (
ID_Mat, Nome, Precedenze, N_Ore_annuali, Docente,....)
questa conterrà un record per ogni materia, con le caratteristiche proprie della materia e dell'esame corrispondente.
In tal caso la relazione tra Studenti e MaterieDEsame è di tipo Molti-A-Molti: ad ogni studente corrispondono
diversi record nella tabella MaterieDEsame, come ad ogni materia d'esame (ogni record della tabella) corrispondono molti
studenti
MaterieDEsame
| ID_Mat |
Nome |
Preced |
N_Ore |
Docente |
| 1 |
Analisi 1 |
/ |
...
|
...
|
| 2 |
Analisi 2 |
1 |
|
|
| 3 |
Analisi 3 |
2 |
|
|
| 4 |
Geometria |
/ |
|
|
| 5 |
Fisica 1 |
/ |
|
|
| 6 |
Fisica 2 |
5 |
|
|
|
Studenti
| ID_S |
Nome |
Cognome |
Dati |
Data_Nascita |
| 001 |
Stefano |
Moro |
... |
06/06/1981 |
| 002 |
Marco |
Banfi |
|
26/07/1981 |
| 003 |
Ivan |
Colombo |
|
31/03/1982 |
| 004 |
Maurizio |
Pozzi |
|
31/08/1977 |
| 005 |
Andrea |
Tarantini |
|
03/05/1976 |
|
Come si può vedere, tra le due tabelle in questione non esistono informazioni comuni. La relazione può essere creata
attraverso un'ulteriore tabella che farà da tramite. Si chiamerà EsamiStudenti. Nel precedente esempio, relativo alla relazione 1-M,
una tabella Voti riportava le informazioni sugli esami degli studenti. Ora invece Voti viene sostituita da EsamiStudenti.
La struttura delle tre tabelle e i link che le collegano sono quelli riportati di seguito
In questo modo non vengono inserite chiavi intermedie in nessuna delle due
tabelle di partenza. Sarà la nuova tabella EsamiStudenti che avrà il compito di contenere le chiavi esterne. Così si crea
una doppia relazione:
- relazione 1-M tra MaterieDEsame (lato 1) e EsamiStudenti (lato M), basata sull'uguaglianza del contenuto dei campi
MaterieDEsame.ID_Mat e EsamiStudenti.Nome_Esame
- relazione 1-M tra Studenti (lato 1) e EsamiStudenti(lato M), basata sull'uguaglianza del contenuto dei campi
Studenti.ID_S e EsamiStudenti.ID_S
La tabella EsamiStudenti di fatto
è la relazione di tipo Molti-A-Molti tra Studenti e MaterieDEsame.
La sintassi della query che realizza la relazione M-M, apparentemente complicata, in realtà è semplicemente
la realizzazione della doppia relazione 1-M appena descritta
Istruzione SQL
SELECT STUDENTI.ID_S, STUDENTI.Cognome, MaterieDEsame.Nome_mat, EsamiStudenti.Data_Esame, EsamiStudenti.Voto_Esame
FROM MaterieDEsame INNER JOIN (STUDENTI INNER JOIN EsamiStudenti ON STUDENTI.ID_S = EsamiStudenti.ID_S)
ON MaterieDEsame.ID_Mat = EsamiStudenti.Nome_Esame;
Funzione della tabella EsamiStudenti: osservazione finale
Nella tabella EsamiStudenti i campi strettamente necessari per realizzare la relazione M-M sono solo due: ID_S, chiave
primaria della tabella Studenti, e Nome_Esame che corrisponde a ID_Mat, chiave primaria della tabella MaterieDEsame.
L'esistenza in EsamiStudenti di altri campi, dipende dal fatto che la tabella in questione NON ha il SOLO scopo di
realizzare la relazione, ma ha una reale funzione nella rappresentazione delle informazioni degli studenti. Infatti, come
rilevato in precedenza, sostituisce la tabella Voti.
L'impostazione attuale Studenti - EsamiStudenti - MaterieDEsame, in alternativa alla precedente Studenti - Voti,
permette al DB un maggiore, o migliore, contenuto informativo
Home
Lezione 1
Lezione 3