Esercitazione Autoguidata

Obiettivi dell'esercitazione:

• Familiarizzare con il DBMS MySQL 5.0.
• Creare e una base di dati e connettersi alla base dati.
• Creare utenti e assegnare privilegi e ruoli;
• Creare Tabelle;
• Definire viste;
• Popolare lo schema relazionale;
• Effettuare interrogazioni.

Esercizio 1 

1. aprite un Prompt dei comandi, utilizzando l'icona relativa sul desktop. Se l'icona non è sul desktop selezionate la voce Run dal menù Start, digitate cmd e premete Invio; 
2. eseguite il comando

   mysqld --user=root --default-storage-engine=InnoDB

   (A seconda della versione di MySQL utilizzata, potrebbe essere necessario sostituire il comando "mysqld" con "mysqld-nt"). In questo modo viene avviato MySQL Server, utilizzando il suo sottosistema transazionale che si chiama InnoDB. Anche se in questa esercitazione non imposteremo esplicitamente delle transazioni e non ci occuperemo dei livelli di isolamento, è bene che si usi il sottosistema transazionale, perchè questo controlla che i dati che andremo ad inserire nella base di dati creata soddisfino i vincoli di foreign key che definiremo. Al contrario, gli storage engine MyISAM e Memory non garantiscono il soddisfacimento dei vincoli di foreign key. Si noti che è possibile configurare MySQL in modo che lo storage engine di default sia InnoDB. In questo caso è sufficiente usare il comando mysqld --user root per effettuare la connessione (per ulteriori dettagli sull'argomento si rimanda alla documentazione di MySQL).
   
   Nota: si noti che il comando  mysqld --user root è sufficiente nel caso in cui per l'utente root non si sia impostata una password. Altrimenti, si deve eseguire il comando  mysqld --user=root --password, a seguito del quale verrà richiesto l'inserimento della password.
   
3. dal  Prompt dei comandi eseguite il comando 
   
   mysql -u root
   
   In questo modo avrete attivato l'interprete di comandi SQL, chiamato MySQL, che è lo strumento base che userete per per accedere al DBMS di MySQL. Si noti che se l'utente root ha una password, bisogna eseguire il comando mysql -u root -p, a seguito del quale verrà richiesto l'inserimento della password.
   
4. eseguite il comando 
   
   CREATE DATABASE EX_DB;

In questo modo avrete creato una nuova base di dati chiamata EX_DB. Contestualmente, verrà creata una cartella chiamata  EX_DB nella directory <MySQLParent>\MySQL\MySQL Server 5.0\data. Questa cartella contiene fisicamente tutto il database. Verificate che la creazione sia avenuta con successo eseguendo il comando
   
   show databases;

   Nota1: Si noti che il ; è utilizzato come delimitatore dei comandi. E' possibile anche cambiare tale delimitatore, ed in alcuni casi può risultare comodo. Vedremo in seguito come e quando cambiarlo.

   (Se volete chiudere la connessione con la base dati ed uscire dall'inteprete dei comandi eseguite il comando quit;)

   Nota2: Per cancellare un database si può eseguire il comando DROP DATABASE <nomeDB>;

Esercizio 2

1. Create l'utente EX_USER con password EX_PW tramite il comando
   
   CREATE USER EX_USER IDENTIFIED BY 'EX_PW'; 

2. Verificate che l'operazione sia andata a buon fine interrogando la tabella di catalogo mysql.user, proiettando sui suoi attributi Host, User, e Password.
   Il risultato deve contenere le seguenti righe:

        +-----------+---------+-------------------------------------------+
        | Host      | User    | Password                                  |
        |           |         |                                           |   
        +-----------+---------+-------------------------------------------+
        | localhost | root    |                                           |
        | %         | EX_USER | *3ACE26AF0027B2065C57DC8D2007B41FD38A4EE5 |
        +-----------+---------+-------------------------------------------+
       

   Si noti che la password è criptata.

   Nota: La colonna Host contiene il riferimento alla macchina da cui l'utente può connettersi. Di default viene inserito il simbolo %, che indica che l'utente può connettersi da qualunque locazione. localhost permette invece all'utente di connettersi solo in locale, mentre invece, 151.100.16.50, ad esempio, permetterebbe all'utente di connettersi da remoto tramite l'indirizzo IP 151.100.16.50. Per modificare esplicitamente il campo Host conviene operare direttamente sulla tabella mysql.user. Ad esempio, per creare l'utente mario con password elvisrules che può connettersi solo in locale, si può eseguire il seguente comando:
   
   insert into mysql.user(host, user, password, ssl_cipher, ssl_type, x509_issuer, x509_subject)
values ('localhost','mario','elvisrules','','','',''); 

   I campi ssl_cipher, ssl_type, x509_issuer e x509_subject non hanno un valore di default e vanno pertanto specificati nella query di inserimento (per le finalità del corso, possiamo lasciare semplicemente in bianco tali campi). Per vedere tutte le colonne della tabella mysql.user, eseguite il comando describe mysql.user; (ridefinite le dimensioni della finestra di prompt per vedere in maniera chiara il risultato restituito da MySQL). Si noti che i campi Host e  User formano la chiave primaria della tabella. Un utente può essere cancellato cancellando la corrispondente tupla nella tabella  mysql.user. (una volta cancellato l'utente bisogna eseguire il comando FLUSH PRIVILEGES; perchè la modifica abbia realmente effetto)
   
   Una volta creato l'utente EX_USER occorre assegnargli i privilegi (altrimenti non è in grado di fare nulla). Procedete come segue.

3. Ponetevi nel contesto del database EX_DB eseguendo il comando
   
   use EX_DB;
   
4. Create la tabella EX_TAB, con la sola colonna EX_ATT di tipo intero;
5. Inserite una tupla nella tabella EX_TAB, contenente il valore 1;
6. Garantite i privilegi di lettura all'utente EX_USER sulla tabella EX_TAB tramite il comando 
   
   GRANT SELECT ON EX_TAB TO EX_USER; 

7. Disconnettevi da MySQL usando il comando Quit e riconnetetevi alla stessa con l'utente EX_USER, ponendovi poi nel contesto del database EX_DB 

8. Interrogate la tabella EX_TAB tramite la seguente query 
   
   SELECT * FROM EX_TAB; 

   Il risultato deve essere 

       +--------+
       | EX_ATT |
       +--------+
       |      1 |
       +--------+
       1 row in set (0.00 sec)     
       

9. Provate adesso ad inserire una tupla nella tabella EX_TAB Cosa risponde il DBMS?
10. Provate a creare una tabella. Cosa risponde il DBMS? 
11. Disconnettevi da MySQL e dalla base dati e riconnetetevi alla stessa con l'utente root. E' possibile ora assegnare altri privilegi all'utente  EX_USER. Ad esempio, per assegnare a EX_USER tutti i privilegi di utilizzo su tutte le tabelle del database EX_DB, occorre eseguire l'istruzione seguente:
    
    GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,CREATE VIEW ON EX_DB.* TO 'EX_USER'@'%' IDENTIFIED BY 'EX_PW';

    Assegnati i diritti, occorre poi che il server ricarichi la tabella dei privilegi tramite la seguente istruzione:
    
    FLUSH PRIVILEGES;
    
    Nota: Per assegnare a EX_DB tutti i privilegi (compresi quelli da super user, cioè i privilegi di un utente che può assegnare a sua volta privilegi ad altri utenti) occorre eseguire l'istruzione seguente:
    
    GRANT ALL PRIVILEGES ON *.* TO 'EX_USER'@'%' IDENTIFIED BY 'EX_PW' WITH GRANT OPTION;

assegnati i diritti, occorre poi che il server ricarichi la tabella dei privilegi tramite la seguente istruzione:
    
    FLUSH PRIVILEGES;

Esercizio 3

• Connettetevi al database EX_DB tramite l'utente EX_USER.
• Si crei la tabella LIBRO, contenente i seguenti attributi:
  
  • ID_LIBRO (stringa di 10 caratteri, chiave primaria);
  • ID_UTENTE_PROPRIETARIO (stringa di 8 caratteri);
  • TITOLO (stringa; non nullo);
  • AUTORI (stringa con AA.VV. come valore di default);
  • ISBN (stringa di 16 caratteri);
  • NUM_PAGINE (intero);
    
  la tabella PRESTITO, contenente i seguenti attributi:
  
  • ID_LIBRO (stringa di 10 caratteri; non nulla);
  • DATA_INIZIO (non nulla);
  • DATA_FINE;
  • ID_UTENTE_RICHIEDENTE (stringa di 8 caratteri);
    
  in cui la chiave primaria è costituita da ID_LIBRO e DATA_INIZIO, e la tabella UTENTE, contenente i seguenti attributi
  
  • ID_UTENTE (stringa di 8 caratteri; chiave primaria);
  • NOMINATIVO (stringa; non nulla);
  • DATA_NASCITA (non nulla);
  • RECAPITO_TEL (stringa; non nulla);
  • INDIRIZZO_EMAIL (stringa);
  
  Si forniscano le istruzioni SQL per creare le tre tabelle all'interno del database EX_DB.

  Nota: Per scrivere le istruzioni SQL, potete procedere direttamente da riga di comando una volta connessi al database tramite MSQL, oppure utilizzare un qualsiasi editor di testo (ad esempio Notepad). Se scegliete la seconda opzione, una volta scritto il codice salvatelo su un file, ad esempio c:\EsercitazionePBD\db\create-db.sql, e richiamate lo script così creato all'interno dell'ambiente MSQL tramite il comando

  source c:\EsercitazionePBD\db\create-db.sql

  Ogni volta che viene usato il comando source, vengono eseguite tutte le istruzioni contenute nel file. Quindi se volete usare sempre lo stesso file, per memorizzare le istruzioni SQL da sottomettere a MySQL, dovete di volta in volta cancellare le istruzioni che sono già state eseguite.

  Per verificare di aver effettivamente creato le tabelle, eseguite la seguente interrogazione

  show tables;

  che interroga (sugli attributi OWNER e TABLE_NAME) la tabella di catalogo all_tables  che memorizza informazioni su tutte le tabelle della base di dati. Il risultato dell'interrogazione è

  +-----------------+
  | Tables_in_ex_db |
  +-----------------+
  | libro           |
  | prestito        |
  | utente          |
  +-----------------+
  

Esercizio 4

fornire le istruzioni SQL per creare le seguenti viste:. 

1. Definite una vista che associ ad ogni utente il numero dei libri posseduti;
2. Definite una vista che restituisca tutti i libri disponibili (non in prestito);
3. Definite una vista che restituisca, per ciascun utente,  identificativo, nome, telefono e numero di libri presi in prestito;
4. Definite una vista che, per tutti i libri attualmente in prestito, restituisca: identificativo, titolo e autori  del libro, data inizio del prestito, identificativo, nome, telefono ed inidirzzo e-mail dell'utente richiedente il prestito; 
5. Definite una vista che, per ciascun libro, restituisca identificativo, titolo, autori e numero di volte che è stato prestato e restituito. 

Per scrivere ed eseguire le istruzioni SQL relative alle interrogazioni procedete come al punto precedente.

Per verificare la correttezza delle proprie soluzioni, popolate la base di dati con il file load-db.sql, e confrontate i risultati ottenuti interrogando le viste con quelli contenuti nel file esercizio4-ris.txt. Per popolare la base di dati, copiate il file load-db.sql nella cartella c:\EsercitazionePBD\db, ed eseguite il comando

source c:\EsercitazionePBD\db\load-db.sql