Basi di Dati - Laboratorio

TERZA ESERCITAZIONE

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.

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' e` 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

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

Ecco il file per la creazione ed il popolamento FileSystem.sql.

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.

Trovare il nome e Id della directory radice [output]
Elencare le directory vuote (nome e Id) [output]
Creare una vista FileDir che restituisce la tabella dei file che sono Directory. Selezionare tutti gli elementi della vista. [output]
Utilizzare la vista FileDir per risolvere la query 2 [output]
Creare la vista FileOrd che restituisce la tabella dei file ordinari (non directory). Selezionare tutti gli elementi della vista. [output]
Elencare gli utenti (login) ed il numero di file posseduti [output]
Elencare i gruppi i cui utenti posseggono, complessivamente, il massimo numero di file ordinari [output]
Elencare gli utenti che non appartengono a nessun gruppo o a tutti i gruppi [output]
Aggiungere l'utente root al gruppo 'none' e riprovare la query precendente [output]
Cancellare le directory vuote e mostrare le directory rimanenti [output]
Cancellare l'utente 'rossi' e verificare che siano stati cancellati i suoi file mostrando la tabella 'File' [output]
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.
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).

Soluzioni: [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] [11] [12] [13]

Nota: Per scrivere ed eseguire le istruzioni SQL relative alle interrogazioni si puo`, 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.