Caso di studio: schema fisico

Concludiamo la progettazione del nostro caso di studio con la progettazione fisica. Non assumeremo l'uso di alcun DBMS specifico ma faremo riferimento ad SQL standard. In particolare questo ci impedirà di definire in SQL le strutture di memorizzazione per le tabelle e gli indici. Lo schema fisico della base di dati TeatroSQL, in tutte le sue componenti (domini, tabelle, asserzioni, trigger e viste) è il seguente:

create schema TeatroSQL 
{  

  create domain tipoBiglietto as varchar(8)
  default "Intero"
  check (tipoBiglietto in ('Intero', 'Ridotto', 'Studenti'))

  create domain mansione as varchar(5)
  check (mansione in ('CA', 'POD', 'CUSRP', 'ACF'))

  create domain tipoProduttore as varchar(7)
  check (tipoProduttore in ('interno', 'esterno'))
  
  create table teatro
  (
    nome             varchar(20) primary key, 
    telefono         varchar(15),
    fax              varchar(15),
    indirizzo        varchar(40) not null,  
    email            varchar(30),
    url              varchar(30)
  ) 
  
  create table biglietteria
  (
    nome             varchar(20) primary key, 
    indirizzo        varchar(40) not null,
    email            varchar(30),
    telefono         varchar(15),  
    teatro           varchar(20) foreign key references teatro(nome)
                                 on update cascade on delete set null
  ) 
  
  create table orario
  (
    biglietteria     varchar(20), 
    giorno           varchar(10), 
    inizio           time, 
    fine             time, 
    primary key(biglietteria, giorno, inizio),
    foreign key biglietteria references biglietteria(nome)
                on update cascade on delete cascade,
  ) 
  
  create table notizia
  (
    data        date,
    ora         time,
    oggetto     varchar(100),
    testo       CLOB,
    primary key(data, ora, oggetto)
  )
  
  create table newsletter
  (
    teatro      varchar(20),
    data        date,
    ora         time,
    oggetto     varchar(100),
    primary key(teatro, data, ora, oggetto),
    foreign key teatro references teatro(nome)
                on update cascade on delete cascade,
    foreign key (data, ora, oggetto) references notizia(data, ora, oggetto)
                on update cascade on delete cascade
  )
  
  create table dipendente
  (
    cf                     char(16) primary key, 
    nome                   varchar(20) not null, 
    cognome                varchar(20) not null,
    dataDiNascita          date,
    luogoDiNascita         varchar(20), 
    residenza              varchar(30), 
    telefonoFisso          varchar(15), 
    telefonoMobile         varchar(15),  
    email                  varchar(30)
  )
  
  create table lavoro
  (
    teatro              varchar(20), 
    dipendente          char(16),
    dataAssunzione      date,
    ruolo               mansione,
    cda                 boolean,
    primary key(teatro, dipendente),
    foreign key teatro references teatro(nome)
                on update cascade on delete cascade,
    foreign key dipendente references dipendente(cf)
                on update cascade on delete cascade,
    constraint RA1 check (
    cda = false or 
    (year(current_date) - year(dataAssunzione) > 10) or
    (year(current_date) - year(dataAssunzione) = 10 and 
       month(current_date) > month(dataAssunzione)) or
    (year(current_date) - year(dataAssunzione) = 10 and 
       month(current_date) = month(dataAssunzione) and
       day(current_date) >= day(dataAssunzione)) 
    )     
  ) 
  
  create table stipendio
  (
    dipendente    char(16), 
    inizio        date, 
    importo       decimal(6,2),
    primary key(dipendente, inizio),
    foreign key dipendente references dipendente(cf)
                on update cascade on delete cascade
  ) 
  
  create table spazio
  (
    nome             varchar(20) primary key, 
    indirizzo        varchar(40) not null,
    pianta           varchar(20),
    capienza         smallint
  ) 
  
  create table luogo
  (
    teatro     varchar(20), 
    spazio     varchar(20),
    primary key(teatro, spazio),
    foreign key teatro references teatro(nome)
                on update cascade on delete cascade
    foreign key spazio references spazio(nome)
                on update cascade on delete cascade
  ) 
  
  create table stagione
  (
    nome           varchar(20), 
    biennio        char(9), 
    teatro         varchar(20),
    primary key(nome, biennio),
    foreign key teatro references teatro(nome)
                on update cascade on delete set null
  )
  
  create table spettacolo
  (
    titolo             varchar(40) primary key,  
    descrizione        CLOB,
    annoProduzione     char(4),
  ) 
  
  create table messaInScena
  (
    data               date,  
    ora                time,
    spazio             varchar(20),
    spettacolo         varchar(40),
    postiDisponibili   smallint,
    prezzoIntero       decimal(5,2),
    prezzoRidotto      decimal(5,2),
    prezzoStudenti     decimal(5,2),
    primary key (data, ora, spazio),
    foreign key spazio references spazio(nome)
                on update cascade on delete set null,
    foreign key spettacolo references spettacolo(titolo)
                on update cascade on delete set null,
    constraint RA3-1 check (postiDisponibili >= 0)                           
  ) 
  
  create table materiale
  (
    file                   varchar(30) primary key,
    tipo                   varchar(10),
    dimensione             integer,
    dataSpettacolo         date,  
    oraSpettacolo          time,
    spazioSpettacolo       varchar(20),
    foreign key (dataSpettacolo, oraSpettacolo, spazioSpettacolo) references
                 messaInScena(data, ora, spazio)
                 on update cascade on delete set null
  )
    
  create table prenotazione
  (
    dataSpettacolo     date,  
    oraSpettacolo      time,
    spazioSpettacolo   varchar(20),
    numero             smallint,
    data               date,
    ora                time,
    posto              varchar(5),
    tipo               tipoBiglietto,
    prezzo             decimal(5,2),
    primary key(spettacolo, numero),
    foreign key (dataSpettacolo, oraSpettacolo, spazioSpettacolo) references
                 messaInScena(data, ora, spazio)
                 on update cascade on delete cascade
  ) 
  
  create table commento
  (
    autore             varchar(20),
    data               date,
    ora                time,
    testo              CLOB,
    dataSpettacolo     date,  
    oraSpettacolo      time,
    spazioSpettacolo   varchar(20),
    primary key (autore, data, ora),
    foreign key (dataSpettacolo, oraSpettacolo, spazioSpettacolo) references
                 messaInScena(data, ora, spazio)
                 on update cascade on delete set null
  )
  
  create table risposta
  (
    autoreRisposta     varchar(20),
    dataRisposta       date,
    oraRisposta        time,
    autore             varchar(20),
    data               date,
    ora                time,
    primary key (autoreRisposta, dataRisposta, oraRisposta), 
    foreign key (autoreRisposta, dataRisposta, oraRisposta) references 
                commento(autore, data, ora)
                on update cascade on delete cascade,
    foreign key (autore, data, ora) references 
                commento(autore, data, ora)
                on update cascade on delete cascade
  )
  
  create table produzione
  (
    produttore     varchar(20), 
    spettacolo     varchar(40),
    primary key(produttore, spettacolo),
    foreign key produttore references produttore(nome)
                on update cascade on delete cascade
    foreign key spettacolo references spettacolo(titolo)
                on update cascade on delete cascade  
  )
  
  create table produttore
  (
    nome           varchar(20) primary key, 
    tipo           tipoProduttore, 
    indirizzo      varchar(40), 
    telefono       varchar(15), 
    email          varchar(30),
  )
    
  create table interprete
  (
    nome                 varchar(20), 
    ruolo                varchar(15),
    cv                   CLOB,
    primary key(nome)
  )
  
  create table cast
  (
    spettacolo               varchar(40), 
    interprete               varchar(20), 
    primary key(spettacolo, interprete),
    foreign key spettacolo references spettacolo(titolo)
                on update cascade on delete cascade
    foreign key interprete references interprete(nome)
                on update cascade on delete cascade  
  ) 
        
  crate assertion RA2 check (
    not exists (
      select stagione.teatro, stagione.nome, stagione.biennio, count(*)
      from (stagione join proposta on 
           (stagione.nome = proposta.nomeStagione) and 
           (stagione.biennio = proposta.biennioStagione)) 
           join produzione on 
           (proposta.spettacolo = produzione.spettacolo) and
           (stagione.teatro = produzione.produttore)
      group by stagione.teatro, stagione.nome, stagione.biennio
      having count(*) > 2
    )
  )          
    
  crate trigger RA3-2
  after insert on prenotazione
  for each row
  update messaInScena
  set postiDisponibili = postiDisponibili - 1
  where (data = New.dataSpettacolo and 
         ora = New.oraSpettacolo 
         spazio = New.spazioSpettacolo)
    
  
  crate trigger RA3-3
  after delete on prenotazione
  for each row
  update messaInScena
  set postiDisponibili = postiDisponibili + 1
  where (data = Old.dataSpettacolo and 
         ora = Old.oraSpettacolo 
         spazio = Old.spazioSpettacolo)

  
  crate trigger RA3-4
  after insert on messaInScena
  for each row
  update messaInScena
  set postiDisponibili = (select capienza
                          from spazio
                          where nome = New.spazio) 
  where (data = New.data and 
         ora = New.ora 
         spazio = New.spazio)


  crate trigger RA4-1
  after insert on messaInScena
  for each row
  update messaInScena
  set New.prezzoRidotto = New.prezzoIntero * 0.8
  where (data = New.data and 
         ora = New.ora 
         spazio = New.spazio)
  
  
  crate trigger RA4-2
  after insert on messaInScena
  for each row
  update messaInScena
  set New.prezzoStudenti = New.prezzoIntero * 0.5
  where (data = New.data and 
         ora = New.ora 
         spazio = New.spazio)
  

  crate trigger RA4-3
  after update of prezzoIntero on messaInScena
  for each row
  update messaInScena
  set New.prezzoRidotto = New.prezzoIntero * 0.8
  where (data = New.data and 
         ora = New.ora 
         spazio = New.spazio)
  
  
  crate trigger RA4-4
  after update of prezzoIntero on messaInScena
  for each row
  update messaInScena
  set New.prezzoStudenti = New.prezzoIntero * 0.5
  where (data = New.data and 
         ora = New.ora 
         spazio = New.spazio)
  

  crate trigger RA5-1
  after insert on prenotazione
  for each row
  when (tipo = "Intero")
  update prenotazione
  set New.prezzo = (select prezzoIntero
                    from messaInScena
                    where (data = New.dataSpettacolo and 
                           ora = New.oraSpettacolo and
                           spazio = New.spazioSpettacolo))
  where (dataSpettacolo = New.dataSpettacolo and 
         oraSpettacolo = New.oraSpettacolo and
         spazioSpettacolo = New.spazioSpettacolo and 
         numero = New.numero)  
         

  crate trigger RA5-2
  after insert on prenotazione
  for each row
  when (tipo = "Ridotto")
  update prenotazione
  set New.prezzo = (select prezzoRidotto
                    from messaInScena
                    where (data = New.dataSpettacolo and 
                           ora = New.oraSpettacolo and
                           spazio = New.spazioSpettacolo))
  where (dataSpettacolo = New.dataSpettacolo and 
         oraSpettacolo = New.oraSpettacolo and
         spazioSpettacolo = New.spazioSpettacolo and 
         numero = New.numero)  
  

  crate trigger RA5-1
  after insert on prenotazione
  for each row
  when (tipo = "Studenti")
  update prenotazione
  set New.prezzo = (select prezzoStudenti
                    from messaInScena
                    where (data = New.dataSpettacolo and 
                           ora = New.oraSpettacolo and
                           spazio = New.spazioSpettacolo))
  where (dataSpettacolo = New.dataSpettacolo and 
         oraSpettacolo = New.oraSpettacolo and
         spazioSpettacolo = New.spazioSpettacolo and 
         numero = New.numero)  
  
  
  create view spettacoli(teatro, nomeStagione, biennioStagione, 
    titolo, descrizione, data, ora, spazio, indirizzo, postiDisponibili 
    prezzoIntero, prezzoRidotto, prezzoStudenti) as
  select St.teatro, St.nomeStagione, St.biennioStagione, Spe.titolo, 
    Spe.descrizione, Sce.data, Sce.ora, Sce.spazio, Spa.indirizzo, 
    Sce.postiDisponibili, Sce.prezzoIntero, Sce.prezzoRidotto, Sce.prezzoStudenti
  from stagione St, proposta P, spettacolo Spe, messaInScena Sce, spazio Spa
  where (St.nome = P.nomeStagione) and
        (St.biennio = P.biennioStagione) and
        (P.spettacolo = Spe.titolo) and
        (Spe.titolo = Sce.spettacolo)
        (Sce.spazio = Spa.nome)
  order by St.teatro, St.nomeStagione, St.biennioStagione, Sce.data        
  
  
  create view attori(teatro, nomeStagione, biennioStagione, nome, ruolo, cv) as
  select St.teatro, St.nomeStagione, St.biennioStagione, I.nome, I.ruolo, I.cv
  from stagione St, proposta P, spettacolo Sp, cast C, interprete I
  where (St.nome = P.nomeStagione) and
        (St.biennio = P.biennioStagione) and
        (P.spettacolo = Sp.titolo) and
        (Sp.titolo = C.spettacolo) and
        (C.interprete = I.nome) 
  order by St.teatro, St.nomeStagione, St.biennioStagione, I.nome, I.ruolo


  create view dipendenti(teatro, cognome, nome, cf, età, telefonoFisso,
    telefonoMobile, email, stipendio, dataDiAssunzione, ruolo, cda) as
  select L.teatro, D.cognome, D.nome, D.cf, 
    case 
      when ((month(current_date) > month(D.dataDiNascita)) or
            (month(current_date) = month(D.dataDiNascita) and 
             day(current_date) >= day(D.dataDiNascita)))
      then year(current_date) - year(D.dataDiNascita)
      else year(current_date) - year(D.dataDiNascita) - 1
    end,  
    D.telefonoFisso, D.telefonoMobile, D.email, S.stipendio, 
    L.dataDiAssunzione, L.ruolo, L.cda
  from lavoro L, dipendente D, stipendio S
  where (L.dipendente = D.cf) and (S.dipendente = D.cf) and
        (S.inizio = (select max(S2.inizio)
                     from stipendio S2
                     where S2.dipendente = D.cf))
  order by L.teatro, D.cognome, D.nome                    
    
    
  create view statScena(spettacolo, paganti, affluenza, incasso) as
  select Sce.spettacolo, count(*), count(*) / Spa.capienza, sum(P.prezzo)
  from messaInScena Sce, spazio Spa, prenotazione P
  where (Sce.spazio = Spa.nome) and
        (P.dataSpettacolo = Sce.data) and
        (P.oraSpettacolo = Sce.ora) and
        (P.spazioSpettacolo = Sce.spazio)
  group by Sce.data, Sce.ora, Sce.spazio

  create view statSpettacolo(spettacolo, paganti, affluenza, incasso) as
  select Spe.titolo, avg(Sce.paganti), avg(Sce.affluenza), avg(Sce.incasso)
  from spettacolo Spe, statScena Sce
  where (Spe.titolo = Sce.spettacolo)
  group by Spe.titolo
  
}

Seguono alcune osservazioni:

• compito della progettazione fisica è anche definire le strutture di memorizzazione e di accesso ai dati tra quelle offerte dal DBMS usato. Nel nostro caso assumiamo di definire un indice almeno sulla chiave primaria di tutte le tabelle;
• per quanto riguarda le politiche di integrità referenziale, per le modifiche abbiamo sempre usato la politica on update cascade. Per le cancellazioni abbiamo usato la politica on delete cascade per chiavi esterne di tabelle che corrispondono a relazioni concettuali (ad esempio lavoro) oppure ad entità deboli (ad esempio prenotazione) e la politica on delete set null negli altri casi. Nel primo caso vi è un forte collegamento tra la tabella master e la tabella slave e dunque una cancellazione nella tabella master dovrebbe provocare corrispondenti cancellazioni nella tabella slave;
• i vincoli di integrità corrispondenti alle regole aziendali sono stati nominati con la sigla RA seguida dal numero della regola aziendale, eventualmente seguito da un trattino e da un numero progressivo qualora siano stati necessari pi vincoli per specificare la regola;
• per specificare la prima regola aziendale e l'attributo età della vista dipendenti sono state usate le funzioni current_date che restituisce la data corrente, year, month, e day che rispettivamente estraggono l'anno, il mese e il giorno da una data;
• Per specificare la terza regola aziendale sui posti disponibili abbiamo usare i trigger RA3-2 (che decrementa di uno i posti disponibili quando una prenotazione viene inserita), RA3-3 (che incrementa di uno i posti disponibili quando una prenotazione viene cancellata), RA3-4 (che imposta il numero di posti disponibili alla capienza dello spazio teatrale quando uno spettacolo viene inserito). Inoltre, abbiamo usato il vincolo check RA3-1 che specifica che l'attributo postiDisponibili deve essere maggiore o uguale a zero. Dunque, un inserimento di una prenotazione quando i posti sono esauriti innesca il trigger RA3-2 che viola il vincolo RA3-1. Il sistema dunque annulla l'inserimento della prenotazione.
  
  Una soluzione alternativa consiste nel sostituire il vincolo RA3-1 con il seguente trigger:

    
  crate trigger RA3-1
    before insert on prenotazione
    for each row
    when (0 = (select postiDisponibili
               from messaInScena
               where (data = New.dataSpettacolo and 
                      ora = New.oraSpettacolo 
                      spazio = New.spazioSpettacolo)))
    rollback("Posti esauriti")
  

  Esso controlla, prima dell'inserimento della prenotazione nella base di dati, che vi siano posti disponibili. Se non ve ne sono, annulla l'operazione di inserimento e avvisa l'utente che i posti sono esauriti. Si noti che la soluzione funziona se RA3-1 viene eseguito prima di RA3-2 (di solito è così in quanto RA3-1 è di tipo before e RA3-2 è di tipo after).
• il dominio mansione, associato all'attributo ruolo della tabella lavoro, assume i seguenti valori corrispondenti alle seguenti mansioni:
  • CA: coordinamento artistico
  • POD: pruduzione, organizzazione e distribuzione
  • CUSRP: comunicazione, ufficio stampa e relazioni con il pubblico
  • ACF: amministrazione, controllo e finanza
• l'attributo età della vista dipendenti è stato calcolato come differenza tra la data corrente e quella di nascita del dipendente. Questa soluzione può essere impiegata per implementare attributi calcolati che dipendono dal tempo: l'attributo viene letto attraverso una vista e il suo valore è calcolato in tempo reale. Si noti che non è possibile usare un trigger per implementare un attributo calcolato come età in quanto il valore dell'età cambia indipendentemente dagli aggiornamenti della base, cioè dagli eventi che attivano i trigger;
• per gli oggetti binari (immagini e video) abbiamo preferito usare attributi di tipo stringa che contengono il percorso del file che contiene l'oggetto piuttosto che l'uso di un attributo di tipo BLOB. Per i documenti si puro testo abbiamo invece usato attributi di tipo CLOB.

Mostriamo infine l'implementazione delle transazioni tipiche di interrogazione (quelle di inserimento dipendono dai dati da inserire). Alcune di queste transazioni fanno uso delle viste definite nello schema:

• Trovare gli spettacoli messi in scena in una stagione teatrale di un teatro. Per ogni spettacolo occorre stampare il titolo, una descrizione, data, ora e luogo di scena, posti disponibili e prezzi.
  
  Basta instanziare la vista spettacoli con un teatro e una stagione.
• Trovare gli attori che hanno lavorato in una stagione di un teatro. La stessa transazione per i registi e gli autori.
  
  Basta instanziare la vista interpreti con un particolare ruolo, un teatro e una stagione.
• Trovare i dipendenti di un teatro.
  
  Basta instanziare la vista dipendenti con un teatro.
• Trovare i commenti riferiti ad uno spettacolo.

  select C.autore, C.data, C.ora, C.testo
  from messaInScena S, commento C
  where (S.data = C.dataSpettacolo) and 
        (S.ora = C.oraSpettacolo) and 
        (S.spazio = C.spazioSpettacolo) and 
        (S.titolo = 'Gli straccioni')
  

• Trovare le notizie riferite ad un teatro.

  select News.nome, N.data, N.ora, N.oggetto, N.testo
  from notizia N, newsletter News
  where (N.data = News.data) and
        (N.ora = News.ora) and  
        (N.oggetto = News.oggetto) and 
        (News.teatro = 'CSS')
  

• Trovare gli orari di apertura delle biglietterie di un teatro.

  select O.biglietteria, O.giorno, O.inizio, O.fine
  from biglietteria B, orario O
  where (B.nome = O.biglietteria) and (B.teatro = 'CSS')
  order by O.biglietteria
  

• Generare una statistica sull'andamento delle stagioni teatrali della rete. Per ogni stagione si vuole calcolare la media degli spettatori paganti, la media dell'affluenza, cioè del rapporto tra paganti e capienza e la media degli incassi riferite agli spettacoli della stagione. Si vuole inoltra stampare gli spettacoli di una particolare stagione in ordine di: (i) paganti, (ii) affluenza, (iii) incasso.

   
  select P.nomeStagione, P.biennioStagione, avg(SS.paganti) as mediaPaganti,
         avg(SS.affluenza) as mediaAffluenza, avg(SS.incasso) as mediaIncasso
  from statisticaSpettacolo SS, proposta P
  where (SS.spettacolo = P.spettacolo)
  group by P.nomeStagione, P.biennioStagione
  
  
  select SS.spettacolo, SS.paganti
  from statisticaSpettacolo SS, proposta P
  where (SS.spettacolo = P.spettacolo) and
        (P.nomeStagione = 'Contatto') and
        (P.biennioStagione = '2006/2007')
  order by SS.paganti        
        
        
  select SS.spettacolo, SS.affluenza
  from statisticaSpettacolo SS, proposta P
  where (SS.spettacolo = P.spettacolo) and
        (P.nomeStagione = 'Contatto') and
        (P.biennioStagione = '2006/2007')
  order by SS.affluenza
        
        
  select SS.spettacolo, SS.incasso
  from statisticaSpettacolo SS, proposta P
  where (SS.spettacolo = P.spettacolo) and
        (P.nomeStagione = 'Contatto') and
        (P.biennioStagione = '2006/2007')
  order by SS.incasso