Esercitazione MySQL - Foreign Key

C:\.....\bin>mysql -u root -p
Enter password:******
Welcome to the MySQL monitor....
....
....
....
mysql> create database scuola;
Query OK, 1 row affected (0.00 sec)

mysql> use scuola;
Database changed

mysql> create table classe (idc integer not null auto_increment primary key, ann
o integer not null, sezione char(1) not null, descrizione varchar(25) not null);

Query OK, 0 rows affected (0.22 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'a','informatica');
Query OK, 1 row affected (0.03 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'b','informatica');
Query OK, 1 row affected (0.02 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'c','informatica');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'a','chimica');
Query OK, 1 row affected (0.02 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'b','chimica');
Query OK, 1 row affected (0.03 sec)

mysql> insert into classe (anno,sezione,descrizione) values (1,'c','chimica');
Query OK, 1 row affected (0.03 sec)

mysql> select * from classe;
+-----+------+---------+-------------+
| idc | anno | sezione | descrizione |
+-----+------+---------+-------------+
|   1 |    1 | a       | informatica |
|   2 |    1 | b       | informatica |
|   3 |    1 | c       | informatica |
|   4 |    1 | a       | chimica     |
|   5 |    1 | b       | chimica     |
|   6 |    1 | c       | chimica     |
+-----+------+---------+-------------+
6 rows in set (0.00 sec)

mysql> create table studente (idce integer, foreign key(idce) references classe(idc) on delete cascade, ids integer not null auto_increment primary key, nome varchar(25) not null, cognome varchar(25) not null, citta varchar(25) not null);
Query OK, 0 rows affected (0.19 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (1,'giacomo','leopardi','recanati');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (1,'mario','rossi','milano');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (1,'giovanni','brambilla','bologna');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (4,'massimo','bianchi','milano');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (4,'gianluca','basso','pavia');
Query OK, 1 row affected (0.03 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (4,'davide','mallemaro','torino');
Query OK, 1 row affected (0.03 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (2,'giuseppe','astri','bolzano');
Query OK, 1 row affected (0.02 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (3,'luca','bonfanti','aosta');
Query OK, 1 row affected (0.04 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (5,'susanna','castaldi','napoli');
Query OK, 1 row affected (0.04 sec)

mysql> insert into studente (idce,nome,cognome,citta) values (6,'maria','soprano','taranto');
Query OK, 1 row affected (0.04 sec)

mysql> select * from studente;
+------+-----+----------+-----------+----------+
| idce | ids | nome     | cognome   | citta    |
+------+-----+----------+-----------+----------+
|    1 |   1 | giacomo  | leopardi  | recanati |
|    1 |   2 | mario    | rossi     | milano   |
|    1 |   3 | giovanni | brambilla | bologna  |
|    4 |   4 | massimo  | bianchi   | milano   |
|    4 |   5 | gianluca | basso     | pavia    |
|    4 |   6 | davide   | mallemaro | torino   |
|    2 |   7 | giuseppe | astri     | bolzano  |
|    3 |   8 | luca     | bonfanti  | aosta    |
|    5 |   9 | susanna  | castaldi  | napoli   |
|    6 |  10 | maria    | soprano   | taranto  |
+------+-----+----------+-----------+----------+
10 rows in set (0.00 sec)

mysql> select * from classe;
+-----+------+---------+-------------+
| idc | anno | sezione | descrizione |
+-----+------+---------+-------------+
|   1 |    1 | a       | informatica |
|   2 |    1 | b       | informatica |
|   3 |    1 | c       | informatica |
|   4 |    1 | a       | chimica     |
|   5 |    1 | b       | chimica     |
|   6 |    1 | c       | chimica     |
+-----+------+---------+-------------+
6 rows in set (0.00 sec)

mysql> delete from classe where idc=1;
QUERY OK, 1 row affected (0.02 sec)

mysql> select * from classe;
+-----+------+---------+-------------+
| idc | anno | sezione | descrizione |
+-----+------+---------+-------------+
|     |      |         |             |
|   2 |    1 | b       | informatica |
|   3 |    1 | c       | informatica |
|   4 |    1 | a       | chimica     |
|   5 |    1 | b       | chimica     |
|   6 |    1 | c       | chimica     |
+-----+------+---------+-------------+
5 rows in set (0.00 sec)

mysql> select * from studente;
+------+-----+----------+-----------+----------+
| idce | ids | nome     | cognome   | citta    |
+------+-----+----------+-----------+----------+
|      |     |          |           |          |
|    4 |   4 | massimo  | bianchi   | milano   |
|    4 |   5 | gianluca | basso     | pavia    |
|    4 |   6 | davide   | mallemaro | torino   |
|    2 |   7 | giuseppe | astri     | bolzano  |
|    3 |   8 | luca     | bonfanti  | aosta    |
|    5 |   9 | susanna  | castaldi  | napoli   |
|    6 |  10 | maria    | soprano   | taranto  |
+------+-----+----------+-----------+----------+
10 rows in set (0.00 sec)

I vincoli di integrità referenziale di propagazione consentono di definire le operazioni eseguite da SQL Server quando un utente tenta di eliminare o aggiornare una chiave alla quale fa riferimento una chiave esterna.

Le clausole REFERENCES delle istruzioni CREATE TABLE e ALTER TABLE supportano le clausole ON DELETE e ON UPDATE. Le operazioni di propagazione possono anche essere definite utilizzando la finestra di dialogo Relazioni chiavi esterne:

NO ACTION è il valore predefinito se non viene specificata ON DELETE o ON UPDATE.

ON DELETE NO ACTION: Specifica che se si tenta di eliminare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verrà generato un errore e verrà eseguito il rollback dell'istruzione DELETE.

ON UPDATE NO ACTION: Specifica che se si tenta di aggiornare un valore di chiave in una riga e alla chiave fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verrà generato un errore e verrà eseguito il rollback dell'istruzione UPDATE.

Con CASCADE, SET NULL e SET DEFAULT, le eliminazioni o gli aggiornamenti di valori di chiave possono influire sulle tabelle con relazioni di chiave esterna tracciabili alla tabella in cui è stata eseguita la modifica. Se le operazioni referenziali di propagazione sono state definite anche nelle tabelle di destinazione, tali operazioni saranno inoltre valide per le righe eliminate o aggiornate. 

ON DELETE CASCADE: Specifica che se si tenta di eliminare una riga contenente una chiave a cui fanno riferimento chiavi esterne in righe esistenti in altre tabelle, verranno inoltre eliminate tutte le righe contenenti tali chiavi esterne.

ON UPDATE CASCADE: Specifica che se si tenta di aggiornare un valore di chiave in una riga e a tale valore fanno riferimento chiavi esterne in righe esistenti in altre tabelle, tutti i valori che compongono la chiave esterna verranno anch'essi aggiornati al nuovo valore specificato per la chiave.

Fonte: Esercizio pratico in laboratorio e "https://technet.microsoft.com/it-it/library/ms186973(v=sql.105).aspx"

Progettazione di un Software o Sistema Informativo

Progettare un Software o un Sistema Informativo

Ci sono diverse fasi nella progettazione di un Sistema Informativo, nel dettaglio:

-          Analisi: ha lo scopo di individuare i concetti chiave della richiesta e fornire per ogni concetto chiave una documentazione che prende il nome di “Glossario dei termini”.

-          Progettazione Concettuale: per eseguire la progettazione concettuale ho bisogno di uno strumento: un modello di riferimento. Un modello di riferimento è un insieme di regole che ha lo scopo di ricreare uno schema concettuale, il modello che andremo ad utilizzare è il modello Entità-Associazioni (Modello E/R).

Regole:

o      Entità -> Concetto chiave (rappresentato con un rettangolo)

o       Associazione -> Il legame tra i concetti chiave (rappresentato con un rombo)

o       Tipi di legame -> 1:1, 1:N, N:M … 1 _____ N (rappresentato con una riga)

*!* Per individuare le entità all'interno di una traccia dovrò fare attenzione ai sostantivi

*!* Per individuare le associazione all'interno di una traccia dovrò fare attenzione ai verbi

*!* Bisogna stare attenti a non creare da uno stesso concetto chiave più concetti chiave: in Italiano si è soliti utilizzare dei sinonimi per rendere il testo più leggibile, i sinonimi possono trarre in inganno

Esempio:

Prendiamo in analisi un associazione Classe – Studente:

Le entità sono STUDENTE e CLASSE, il legame che le unisce è la FREQUENZA di uno studente ad una determinata classe.

*!* Lo schema deve funzionare sia se letto da sinistra a destra che se letto da destra a sinistra.

-          Più Studenti frequentano una Classe

-          Una Classe è frequentata da più studenti

Sia per le entità che per le associazioni sono previsti degli attributi:

*!* Solo le entità possono prevedere un identificatore univoco rappresentato dalla sottolineatura del nome dell’attributo.

Vanno previste anche delle specifiche di progetto: dettagli di progettazione che vanno motivati per valutare ogni aspetto del progetto. 

Esempio: ogni studente deve frequentare un minimo di una classe? Se voglio lavorare su studenti che hanno un corso attivo probabilmente si, ma se devo gestire anche uno storico di studenti che negli anni hanno già portato a termine il loro percorso allora potrei avere studenti che non stanno frequentando alcun corso.