ESERCIZIO: diagrammi ER, codice SQL per creazione tabelle e

DIAGRAMMA E/R.
RELAZIONI, FOREIGN KEY, SQL JOIN
Esercizi, classe 3c/3d aprile 2013
Proviamo ad implementare un database per gestire la prenotazione agli sportelli degli studenti tenui da
vari docenti.
Innanzitutto proviamo a studiare il diagramma ER individuando le entità che entrano nel problema con i
relativi attributi . Quello che potrebbe derivarne è il seguente.
nome
ora inizio
matricola
N
STUDENTI
materia
nome
partecipano
N
SPORTELLI
N
tiene
1
DOCENTI
email
ora fine
Andiamo ora a studiare le relazioni. Vediamo che la relazione docente-sprtello è una relazione 1:N (uno
a molti) dato che ogni sportello sarà tenuto da un solo docente, ma un docente potrà tenere più ore di
sportello.
La relazione esistente fra le entità studente e sportello è invece di natura diversa, corrispondente ad una
relazione N:N, molti a molti: infatti ogni studente potrà registrarsi a più sportelli; ad ogni sportello
potranno registrarsi più ragazzi.
Il modello è corretto ma vogliamo evitare tale relazione, on essendo poi in grado di gestirla in modo
comodo e ordinato nel database.
Aggiungendo un ulteriore entità intermedia “prenotazioni” saremo in grado di ricondurre tale relazione
N:N (molto a molti) a due relazioni 1:N (uno a molti).
Vediamo come (i campi tratteggiati indicano la chiave primaria dell'entità)
nome
matricola
classe
STUDENTI
1
registra
ora inizio
N
PRENOTAZIONI
domanda
N
n° posto
cod_prenotazione
riferite a
1
materia
SPORTELLI
N
nome
tiene
1
DOCENTI
cod_docente
ora fine
cod_sportello
email
Il codice SQL per creare le tre tabelle suddette sarà:
CREATE TABLE studenti(
matricola INT PRIMARY KEY AUTO_INCREMENT,
nome VARCHAR(30) NOT NULL,
classe CHAR(4)
) ENGINE=InnoDB;
CREATE TABLE docenti(
cod_docente INT PRIMARY KEY AUTO_INCREMENT,
nome VARCHAR(30) NOT NULL,
email CHAR(50) UNIQUE
) ENGINE=InnoDB;
CREATE TABLE sportelli(
cod_sportello INT PRIMARY KEY AUTO_INCREMENT,
materia VARCHAR(30) NOT NULL,
cod_docente INT NOT NULL,
ora_inizio TIME NOT NULL,
ora_fine TIME NOT NULL,
data DATE NOT NULL,
FOREIGN KEY (cod_docente) REFERENCES docenti(cod_docente) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB;
CREATE TABLE prenotazioni(
cod_prenotazione INT PRIMARY KEY AUTO_INCREMENT,
domanda VARCHAR(1000),
cod_studente INT NOT NULL,
cod_sportello INT NOT NULL,
FOREIGN KEY (cod_sportello)REFERENCES sportelli(cod_sportello) ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (cod_studente) REFERENCES studenti(matricola) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB;
Ora prova (utilizzando anche i comandi JOIN, ove necessario) a scrivere il codice SQL per eseguire le
seguenti operazioni sul database:
•
•
•
•
•
•
•
•
inserire un docente;
inserire uno studente;
creare un nuovo sportello
iscrivere uno studente ad una determinata ora di sportello
cancellare tutte le prenotazioni (non quelle già passate!!) dello studente Asdrubale.
posticipare di un'ora gli sportelli del docente Sbirulini.
selezionare nome dello studente, classe, domanda, ora di inizio, nome del docente delle
prenotazioni a tutti gli sportelli di fisica previsti per la settimana prossima
selezionare l'elenco di tutti i docenti con i relativi sportelli. Incrociare le tabelle in modo
standard, quindi con la sintassi INNER JOIN e NATURAL JOIN.
SELECT nome, materia, data, ora_inizio, ora_fine FROM docenti, sportelli WHERE
docenti.cod_docente=sportelli.cod_docente;
SELECT nome, materia, data, ora_inizio, ora_fine FROM docenti INNER JOIN sportelli ON
docenti.cod_docente=sportelli.cod_docente;
SELECT nome, materia, data, ora_inizio, ora_fine FROM docenti INNER JOIN sportelli
USING( cod_docente);
SELECT
nome, materia, data, ora_inizio, ora_fine FROM docenti NATURAL JOIN sportelli;
I precedenti modi sono equivalenti e danno tutti il medesimo risultato.
•
Fare la stessa ricerca, facendo però in modo che vengano stampati i dati di tutti i docenti, anche
di quelli che non tengono sportelli (questi venivano omessi dalle risposte dell'esercizio
precedente). Per questo dovrai richiamare il comando LEFT/RIGHT/FULL OUTER JOIN
SELECT nome, materia, data, ora_inizio, ora_fine FROM docenti LEFT OUTER JOIN sportelli ON
docenti.cod_docente=sportelli.cod_docente;
SELECT nome, materia, data, ora_inizio, ora_fine FROM sportelli RIGHT OUTER JOIN docente ON
docenti.cod_docente=sportelli.cod_docente;
•
Incrociare i dati di tutte 4 le tabelle stampando studenti, domande, date, nome del docente
materia di tutti gli sportelli della settimana corrente ordinando i risultati per data, cod_sportello
e nome degli iscritti.