Lezione 2 – SQL: Relazioni tra tabelle
ARGOMENTI
–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 : osservazione finale
Che cos’è una relazione?
Una relazione, è 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:
| 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.
| StudentiID_SCognomeNomeData Nascita…Altro001MoroStefano06/06/1981…002BanfiMarco26/07/1981…003ColomboIvan31/03/1982… | VotiID_SEsameDataVoto001Analisi 112/06/200125001Geometria13/07/200128001Fisica 125/07/200127002Analisi 112/06/200128002Geometria20/07/200121003Analisi 112/07/200130003Fisica 125/07/200118 |
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 … ONLa sintassi relativa alla definizione di una relazione tra due tabelle ènome_tab1 INNER JOIN nome_tab2 ON nome_tab1.nome_campo1=nome_tab2.nome_campo2significa che verranno aggregati record provenienti dalle due tabelle sulla base dell’uguaglianza del contenuto dei due campi indicati
Esempio 1
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:
| StudentiID_SCognomeNomeData Nascita…Altro001MoroStefano06/06/1981…002BanfiMarco26/07/1981…003ColomboIvan31/03/1982…004PozziMaurizio31/08/1977…005TarantiniAndrea03/05/1976… | Esami di LaureaID_SDataVoto00412/06/200194/10000512/06/2001100/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.
Esercizio 1
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:
| 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 |
Esercizio 2
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:
| 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
| MaterieDEsameID_MatNomePrecedN_OreDocente1Analisi 1/… … 2Analisi 21 3Analisi 32 4Geometria/ 5Fisica 1/ 6Fisica 25 | StudentiID_SNomeCognomeDatiData_Nascita001StefanoMoro…06/06/1981002MarcoBanfi 26/07/1981003IvanColombo 31/03/1982004MaurizioPozzi 31/08/1977005AndreaTarantini 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
Esempio 2
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
