laboratorio3

La terza esercitazione richiede la traduzione di un semplice modello concettuale ad oggetti nel modello relazionale, la definizione in SQL della base di dati, il suo popolamento ed infine la formulazione di alcune query. L'esercitazione nel suo complesso può richiedere più tempo di quello a disposizione.

Si presuppone la conoscenza della parte teorica discussa a lezione. Dubbi su MySQL possono essere risolti riferendosi alla MySQL Reference Guide. Nel caso questo non fosse sufficiente, sarò in laboratorio e possiamo discutere insieme di eventuali quesiti.

Realizzazione di una base di dati

Si vuole creare una base che memorizzi informazioni relative ad un file system. Lo schema concettuale a oggetti della base di dati è una semplificazione di quello già analizzato a lezione.
Schema concettuale
I file sono organizzati in una struttura ad albero. La radice è l'unico file che ha come padre sé stesso. Le directory sono dei particolari file. Si fanno le seguenti assunzioni:
  • La cancellazione di un utente determina la cancellazione di tutti i file dei quali é proprietario.
  • I gruppi non vengono mai cancellati.
  • La cancellazione di una directory comporta la cancellazione dei file in essa contenuti.
  • Il 'Nome' dei file e il 'NomeCogn' (nome e cognome) degli utenti occupano al massimo 20 caratteri. 'Nome' dei gruppi e 'Login' degli utenti occupano al massimo 8 caratteri.

1. Progetto relazionale

Tradurre lo schema concettuale in uno schema relazionale. Per rappresentare la gerarchia usare partizionamento verticale.

2. Realizzazione dello schema in SQL

Si realizzi lo schema in SQL, tenendo conto, nella definizione delle chiavi esterne, delle assunzioni elencate dopo lo schema concettuale.

Nota: Durante la creazione e la popolazione della base di dati vi può essere utile (ad es. nel caso di riferimenti ciclici) inibire il controllo sulle chiavi esterne rispettivamente con

SET FOREIGN_KEY_CHECKS=0;    e    SET FOREIGN_KEY_CHECKS=1;

3. Popolazione della Base di Dati

Si popoli la base di dati di modo che ricalchi la situazione descritta dalla seguente tabella.
    Id     Nome                    Utente          Gruppo

1 Radice/ root admin
11 Var/ root admin
111 Mail/ mail mail
1111 rossi.mbx rossi mail
1112 verdi.mbx verdi mail
112 SubM/ root admin
12 tmp/ root admin
121 tmp0.txt rossi user
122 tmp1.txt verdi user
123 SubT/ root admin
13 home/ root admin
131 rossi/ rossi user
1311 slide.txt rossi user
1312 progetto.pdf rossi user
132 verdi/ verdi user
1321 eserc1.sql rossi user
Alcune osservazioni
  • Il primo campo indica l'Id del file. Il popolamento della BD può risultare semplificato se si nota che l'Id della directory padre si può ottenere da quello del figlio togliendo l'ultima cifra (tranne che per la radice).
  • Il secondo campo rappresenta il nome. L'eventuale '/' finale non è parte del nome, ma invece, se presente indica che il file è una directory. La struttura rispecchia la struttura dell'albero delle directory. Ad esempio La directoy Var contiene la Directory Mail che a sue volta contiene i file rossi.mbx e verdi.mbx
  • Il terzo campo è l'utente che possiede il file.
  • Il quarto campo è il gruppo che possiede il file.
I gruppi presenti, con la corrispondente data di creazione sono
    Gruppo   Data   
user 2007-01-02
mail 2006-01-01
admin 2006-02-04
sys 2006-12-25
none 2007-01-01
Gli utenti presenti, con i relativi gruppi di appartenenza sono
    Login     Nome          Gruppi    

root NULL user, mail, admin, sys, mail
verdi Gino Verdi user, mail
rossi Anna Rossi user, mail
mail NULL mail
nobody NULL

Ricordate che potete caricare le tabelle con il comando

INSERT INTO tabella VALUES ....
oppure
LOAD DATA LOCAL INFILE 'file.txt' INTO TABLE tabella
dove 'file.txt' è un file preventivamente creato che contiene le ennuple da inserire in 'tabella', campi separati da TAB, per default, ma si possono specificare anche altri separatori.

Vi potrebbero essere utili i seguenti file (nei quali i campi sono separati da TAB ...)

Directory.txt    GruppiUtenti.txt    Utenti.txt    File.txt    Gruppi.txt

Disponibili in fondo alla pagina

(Nota: Per poter usare il comando 'LOAD DATA ...' occorre aver avviato il client con il flag '--local-infile=1')

4. Effettuare interrogazioni

Fornire le interrogazioni SQL per rispondere alle seguenti domande. Ogni query è accompagnata dal risultato che si otterrebbe rispetto alla popolazione indicata sopra.
  1. Trovare il nome e Id della directory radice [output]
  2. Elencare le directory vuote (nome e Id) [output]
  3. Creare una vista FileDir che restituisce la tabella dei file che sono Directory. Selezionare tutti gli elementi della vista. [output]
  4. Utilizzare la vista FileDir per risolvere la query 2 [output] 
  5. Creare la vista FileOrd che restituisce la tabella dei file ordinari (non directory). Selezionare tutti gli elementi della vista.[output] 
  6. Elencare gli utenti (login) ed il numero di file posseduti [output] 
  7. Elencare i gruppi i cui utenti posseggono, complessivamente, il massimo numero di file ordinari [output] 
  8. Elencare gli utenti che non appartengono a nessun gruppo o a tutti i gruppi [output] 
  9. Aggiungere l'utente root al gruppo 'none' e riprovare la query precendente [output] 
  10. Cancellare le directory vuote e mostrare le directory rimanenti [output]
  11. Cancellare l'utente 'rossi' e verificare che siano stati cancellati i suoi file mostrando la tabella 'File'[output]
------------------------------------------------------- LABORATORIO 4------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    12. Definire una trigger che assicuri la validità del seguente vincolo semantico: non ci possono essere più di 256 utenti.
          Possibile intestazione dell trigger CREATE TRIGGER MaxUsers BEFORE INSERT ON Utenti FOR EACH ROW.

    13. Definire una funzione che dato un Id di file, restituisce il corrispondente pathname (es. per 121, che corrisponde a 'tmp0.txt' dovrebbe restituire '/Radice/tmp/tmp0.txt').
         Possibile intestazione della funzione CREATE FUNCTION givePath (FId INT) RETURNS CHAR(255)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Nota: Per scrivere ed eseguire le istruzioni SQL relative alle interrogazioni si può, al solito, creare un file query_n.sql per ciascuna query ed eseguirlo

  • con source query_n.sql, dal client MySQL
  • con mysql -t -h server -u login -ppwd < query_n.sql, da shell

Dal client MySQL può essere anche utile utilizzare il comando

\e
che permette di editare la query corrente (o quella appena eseguita) in un editor a vostra scelta, cosi` come impostato nella variabile d'ambiente EDITOR. Qualora questa non fosse impostata, potete definirla inserendo nel file .bash_profile nella vostra home le righe
EDITOR=emacs
export EDITOR
Se il file non esistesse potete crearlo con il contenuto sopra indicato.

Directory.txt
GruppiUtenti.txt
Utenti.txt
File.txt
Gruppi.txt