Esercitazione

Si consideri lo schema logico per la base di dati universitaria.

Interrogazione

Scrivere le seguenti interrogazioni in SQL:

Nome e cognome di tutti gli studenti ordinati per data di immatricolazione in ordine decrescente
```
select nome, cognome
from studente
order by dataImmatricolazione desc
```
Nome, cognome e data di immatricolazione di tutti gli studenti iscritti al corso di laurea di Tecnologie Web e Multimediali, immatricolati nel nuovo millennio e ordinati per cognome e nome in ordine crescente
```
select nome, cognome, dataImmatricolazione
from studente
where (cdl = 'Tecnologie Web e Multimediali') and
      (dataImmatricolazione >= '2000-01-01')
order by cognome, nome
```
Nome, cognome e corso di laurea di tutti gli studenti iscritti ad un corso di laurea che contiene la parola Tecnologie nel nome
```
select nome, cognome, cdl
from studente
where cdl like '%Tecnologie%'
```
Il nome di tutti i corsi della laurea specialistica (IV e V anno) che insegnano qualche argomento relativo a XML
```
select nome
from corso
where ((anno = 4) or (anno = 5)) and (programma like '%XML%')
```

Nome e cognome di tutti i ricercatori della facoltà di Scienze che hanno un indirizzo di posta elettronica ma non hanno un sito Web personale

select nome, cognome
from docente
where (tipo = 'Ricercatore') and (facoltà = 'Scienze') and 
      (email is not null) and (sito is null)

Il codice identificativo del presidente del corso di laurea dello studente con matricola 84444

select cdl.presidente
from studente join cdl on studente.cdl = cdl.nome
where studente.matricola = '84444'

Il nome e cognome del presidente del corso di laurea dello studente con matricola 84444

select docente.nome, docente. cognome
from (studente join cdl on studente.cdl = cdl.nome) 
     join docente on cdl.presidente = docente.codice
where studente.matricola = '84444'

Il nome e cognome del preside della facoltà dello studente con matricola 84444

select docente.nome, docente. cognome
from ((studente join cdl on studente.cdl = cdl.nome) 
     join facoltà on cdl.facoltà = facoltà.nome)
     join docente on facoltà.preside = docente.codice
where studente.matricola = '84444'

Gli studenti che hanno un omonimo nello stesso corso di laurea

select distinct s1.matricola, s1.nome, s1.cognome  
from studente s1, studente s2
where (s1.nome = s2.nome) and (s1.cognome = s2.cognome) and
      (s1.cdl = s2.cdl) and (s1.matricola <> s2.matricola)

I corsi del medesimo corso di laurea che hanno lezione lo stesso giorno alla stessa ora

select distinct l1.corso, l1.cdl
from lezione l1, lezione l2
where (l1.cdl = l2.cdl) and 
      (l1.giorno = l2.giorno) and
      (l1.fascia = l2.fascia) and
      (l1.aula <> l2.aula)

I corsi allo stesso anno di corso del medesimo corso di laurea che hanno lezione lo stesso giorno alla stessa ora

select distinct l1.corso, l1.cdl
from corso c1, lezione l1, corso c2, lezione l2
where (c1.nome = l1.corso) and
      (c1.cdl = l1.cdl) and
      (c2.nome = l2.corso) and
      (c2.cdl = l2.cdl) and
      (c1.anno = c2.anno) and
      (l1.cdl = l2.cdl) and 
      (l1.giorno = l2.giorno) and
      (l1.fascia = l2.fascia) and
      (l1.aula <> l2.aula)

Il numero di corsi insegnati dal docente Roberto Ranon

select count(*)
from corso join docente on corso.docente = docente.codice
where (docente.nome = 'Roberto') and (docente.cognome = 'Ranon')

Il numero di corsi insegnati al corso di laurea di Tecnologie Web e Multimediali per ogni tipo di docente

select docente.tipo, count(*)
from corso join docente on corso.docente = docente.codice
where cdl = 'Tecnologie Web e Multimediali'
group by docente.tipo

Il numero di studenti per ogni corso di laurea ordinati per facoltà e per corso di laurea

select cdl.facoltà, cdl.nome, count(*)
from studente join cdl on studente.cdl = cdl.nome
group by cdl.facoltà, cdl.nome
order by cdl.facoltà, cdl.nome

Il numero di studenti per ogni corso di laurea della facoltà di Scienze per i soli corsi di laurea con almeno 10 studenti ordinati per facoltà e per corso di laurea

select cdl.facoltà, cdl.nome, count(*)
from studente join cdl on studente.cdl = cdl.nome
where cdl.facoltà = 'Scienze'
group by cdl.facoltà, cdl.nome
having count(*) >= 10
order by cdl.facoltà, cdl.nome

I corsi di laurea ordinati a decrescere per numero di studenti (inclusi i corsi privi di studenti)

select cdl.nome, count(studente.matricola)
from cdl left join studente on cdl.nome = studente.cdl
group by cdl.nome
order by count(studente.matricola) desc

Tutti i corsi tenuti da un docente che è preside di qualche facoltà

select corso.nome, corso.cdl
from corso
where corso.docente in (select preside
                        from facoltà)

Tutti i corsi tenuti da un docente che non è preside di alcuna facoltà

select corso.nome, corso.cdl
from corso
where corso.docente not in (select preside
                            from facoltà)

oppure

select corso.nome, corso.cdl
from corso
  except
select corso.nome, corso.cdl
from corso
where corso.docente in (select preside
                        from facoltà)

Tutti i corsi tenuti da un docente che è preside di qualche facoltà o presidente di qualche corso di laurea

select corso.nome, corso.cdl
from corso
where corso.docente in (select preside
                        from facoltà
                          union
                        select presidente
                        from cdl)

Gli studenti che non hanno omonimi

select s1.matricola, s1.nome, s1.cognome  
from studente s1
where not exists(select s2.matricola
                 from studente s2
                 where (s1.matricola <> s2.matricola) and
                       (s1.nome = s2.nome) and
                       (s1.cognome = s2.cognome))

Tutti i corsi che mutuano sul corso di Basi di Dati presso Tecnologie Web e Multimediali

select corso, cdl
from mutuo
where (corsoMutuo = 'Basi di Dati') and 
      (cdlMutuo = 'Tecnologie Web e Multimediali')

Tutti i corsi che mutuano su un corso che mutua sul corso di Basi di Dati presso Tecnologie Web e Multimediali

select m1.corso, m1.cdl
from mutuo m1 join mutuo m2 on (m1.corsoMutuo = m2.corso) and  
                               (m1.cdlMutuo = m2.cdl)
where (m2.corsoMutuo = 'Basi di Dati') and 
      (m2.cdlMutuo = 'Tecnologie Web e Multimediali')

Definizione dei dati

Scrivere i comandi SQL per creare le tabelle e i vincoli di integrità tipici del modello relazionale.

Regole aziendali

Realizzare le seguenti regole aziendali usando il costrutto check di SQL:

il preside di una facoltà deve afferire alla facoltà;
il presidente di un corso di laurea deve afferire alla facoltà che contiene il corso di laurea;
un corso può mutuare su altri corsi solo se tali corsi appartengono ad altri corsi di laurea;
non vi possono essere collisioni di orario tra corsi dello stesso corso di laurea insegnati allo stesso anno di corso;
un corso deve essere tenuto da al più un docente;
i ricercatori non tengono corsi, gli associati ne insegnano almeno due, gli ordinari almeno tre;
ogni facoltà deve essere dislocata almeno in una sede;
uno studente può inserire nel proprio piano di studi solo corsi offerti dal proprio corso di laurea.

create table facoltà (
  nome      varchar(30) primary key
  preside   char(16)
            foreign key references docente(codice)
            on update cascade on delete set null,
  -- regola 1
  check (nome = (select docente.facoltà
                 from docente
                 where docente.codice = preside)),
  -- regola 7
  check (nome in (select dislocazione.facoltà from dislocazione)),
                           
)


create table sede (
  indirizzo   varchar(30) primary key, 
  telefono    varchar(10)
) 


create table dislocazione (
  facoltà      varchar(30) foreign key references facoltà(nome)
               on update cascade on delete cascade, 
  sede         varchar(30) foreign key references sede(indirizzo)
               on update cascade on delete cascade,
  primary key (facoltà, sede)  
) 


create table cdl (
  nome               varchar(30) primary key, 
  descrizione        CLOB, 
  presidente         char(16)
                     foreign key references docente(codice)
                     on update cascade on delete set null, 
  facoltà            varchar(30)
                     foreign key references facoltà(nome)
                     on update cascade on delete set null,
  -- regola 2
  check (facoltà = (select docente.facoltà
                    from docente
                    where docente.codice = presidente))
) 


create table studente (
  matricola                 char(8) primary key, 
  nome                      varchar(20) not null, 
  cognome                   varchar(20) not null, 
  dataImmatricolazione      date not null, 
  cdl                       varchar(30)
                            foreign key references cdl(nome)
                            on update cascade on delete set null
) 

create table corso (
  nome             varchar(30), 
  cdl              varchar(30) foreign key references cdl(nome)
                   on update cascade on delete cascade, 
  descrizione      CLOB, 
  programma        CLOB, 
  anno             smallint, 
  docente          char(16) 
                   -- regola 5
                   foreign key references docente(codice)
                   on update cascade on delete set null,
  primary key (nome, cdl)
) 


create table mutuo (
  corso varchar(30), 
  cdl varchar(30), 
  corsoMutuo not null, 
  cdlMutuo not null,
  primary key (corso, cdl),
  foreign key (corso, cdl) references corso(nome, cdl)
  on update cascade on delete cascade,
  foreign key (corsoMutuo, cdlMutuo) references corso(nome, cdl)
  on update cascade on delete cascade,
  -- regola 3
  check (cdl <> cdlMutuo)
) 


create table pianoDiStudi (
  studente    char(8) foreign key references studente(matricola)
                      on update cascade on delete cascade, 
  corso       varchar(30), 
  cdl         varchar(30),
  foreign key (corso, cdl) references corso(nome, cdl)
  on update cascade on delete cascade,
  primary key (studente, corso, cdl),
  -- regola 8
  check ((corso, cdl) in (select corso.nome, corso.cdl
                          from corso, studente
                          where (corso.cdl = studente.cdl) and 
                                (studente.matricola = studente)))
) 


create domain tipoGiorno as varchar(9)
check (tipoGiorno in ("Lunedì", "Martedì", "Mercoledì", 
                      "Giovedì", "Venerdì", "Sabato"))


create domain tipoFascia as char(5)
check (tipoGiorno in ("08-10", "10-12", "14-16", "16-18"))


create table lezione (
  giorno      tipoGiorno, 
  fascia      tipoFascia, 
  aula        varchar(3), 
  corso       varchar(30),  
  cdl         varchar(30),
  primary key (giorno, fascia, aula),
  foreign key (corso, cdl) references corso(nome, cdl)
  on update cascade on delete set null
) 


-- regola 4
create assertion collisione check (not exists(
  
  select distinct l1.corso, l1.cdl
  from corso c1, lezione l1, corso c2, lezione l2
  where (c1.nome = l1.corso) and
        (c1.cdl = l1.cdl) and
        (c2.nome = l2.corso) and
        (c2.cdl = l2.cdl) and
        (c1.anno = c2.anno) and
        (l1.cdl = l2.cdl) and 
        (l1.giorno = l2.giorno) and
        (l1.fascia = l2.fascia) and
        (l1.aula <> l2.aula)

))

create table docente (
  codice       char(16) primary key, 
  tipo         char(1), 
  nome         varchar(20) not null, 
  cognome      varchar(20) not null, 
  email        varchar(20), 
  sito         varchar(20), 
  telefono     varchar(20), 
  ufficio      varchar(5),  
  facoltà      varchar(30)
               foreign key references facoltà(nome)
               on update cascade on delete set null,
  check (tipo in ("R", "A", "O"))
)


-- regola 6 (ricercatori)
create assertion DidatticaRicercatori check (not exists(

  select codice
  from docente
  where (tipo = "R") and 
        (codice in (select corso.docente from corso))

))


-- regola 6 (associati)
create assertion DidatticaAssociati check (not exists(

  select docente.codice
  from docente left join corso on (docente.codice = corso.docente)
  where (docente.tipo = "A")
  group by docente.codice      
  having count(corso.nome) < 2

))


-- regola 6 (ordinari)
create assertion DidatticaOrdinari check (not exists(

  select docente.codice
  from docente left join corso on (docente.codice = corso.docente)
  where (docente.tipo = "O")
  group by docente.codice      
  having count(corso.nome) < 3

))

Viste

Definire una vista permanente che mostra i seguenti dati per ogni studente: matricola, nome, cognome, corso di laurea, facoltà, anno di iscrizione (ottenuto come differenza tra anno corrente e anno di immatricolazione).

create view vistaStudente(matricola, nome, cognome, corso, facoltà, anno) as
select studente.matricola, studente.nome, studente.cognome, studente.cdl,
       cdl.facoltà, (year(current_date) - year(studente.dataImmatricolazione) + 1)
from studente join cdl on (studente.cdl = cdl.nome)

Definire una vista permanente iscritti che mostra, per ogni corso di laurea, la facoltà e il numero di iscritti (anche per i corsi con nessun iscritto).

create view iscritti(corso, facoltà, numero) as
select cdl.nome, cdl.facoltà, count(*)
from cdl left join studente on (cdl.nome = studente.cdl)
group by cdl.nome, cdl.facoltà

Scrivere le seguenti interrogazioni usando la vista iscritti definita sopra:
- Il corso di laurea con il massimo numero di studenti
- Tutti i corsi di laurea con un numero di studenti superiore alla media

Usando le viste ricorsive, formulare la seguente interrogazione: tutti i corsi che mutuano, direttamente o indirettamente, sul corso di Basi di Dati presso Tecnologie Web e Multimediali

with recursive mutuo*(corso, cdl, corsoMutuo, cdlMutuo) as (
  select corso, cdl, corsoMutuo, cdlMutuo
  from mutuo
    union
  select m1.corso, m1.cdl, m2.corsoMutuo, m2.cdlMutuo
  from mutuo m1 join mutuo* m2 on (m1.corsoMutuo = m2.corso) and  
                                  (m1.cdlMutuo = m2.cdl)
)

select corso, cdl
from mutuo*
where (corsoMutuo = 'Basi di Dati') and 
      (cdlMutuo = 'Tecnologie Web e Multimediali')

Trigger

Si vuole creare un insieme di trigger per calcolare la media dei voti degli studenti. A tal fine:

aggiungere allo schema una tabella esame che registra i voti degli studenti per i corsi superati (i voti sono interi dal 18 al 30, possibilmente con lode per i 30);

create table esame
  (
    studente         char(8), 
    corso            varchar(30),
    cdl              varchar(30)
    data             date not null,
    voto             smallint not null,
    lode             char(2) not null default 'no',
    primary key      (studente, corso, cdl),
    foreign key (studente) references studente(matricola)
      on update cascade on delete cascade,
    foreign key (corso, cdl) references corso(nome, cdl)
      on update cascade on delete cascade,
    check ((voto >= 18) and (voto <= 30)),
    check (lode in ("si", "no")),
    check ((lode = "no") or (voto = 30))
  )

aggiungere alla tabella studente un campo media per la media dei voti degli esami superati dallo studente;
```
alter table studente 
add column media smallint; 
```

scrivere una interrogazione di modifica che aggiorna il campo media aggiunto alla tabella studente;

update studente
set media = (select avg(voto) 
             from esame 
             where matricola = esame.studente);

definire un insieme di trigger per calcolare il campo media.

create trigger updateAvg
after update of voto on esame
for each row
when new.voto <> old.voto
update studente
set media = (select avg(voto) 
             from esame 
             where matricola = esame.studente) 
where matricola = new.studente; 
            
            
create trigger deleteAvg
after delete on esame
for each row
update studente
set media = (select avg(voto) 
             from esame 
             where matricola = esame.studente) 
where matricola = old.studente; 


create trigger insertAvg
after insert on esame
for each row
update studente
set media = (select avg(voto) 
             from esame 
             where matricola = esame.studente) 
where matricola = new.studente;

MySQL

Realizzare la base di dati universitaria usando MySQL. In particolare, decidere le strutture di memorizzazione primarie e ausiliarie usando gli strumenti offerti da MySQL.