Esercizio - Progettazione ER e schema relazionale della mensa universitaria (tema d'esame 13 febbraio 2012)

Testo (compito d'esame del 13/02/2012, riportato dal corso di Basi di Dati, Ing. Informatica UniPD). La mensa universitaria vuole un sistema informatizzato per gestire i pasti. Requisiti:

• gestire sia i rifornimenti del magazzino sia i pasti per gli studenti;
• di ogni ingrediente acquistato si vuole sapere se si trova nel magazzino grande e/o nei due magazzini piccoli; i magazzini differiscono per posizione (edifici diversi), metri quadri e quantità di ingredienti conservabili; un ingrediente ha un costo unitario (quanto paga la mensa) e può essere presente in quantità diverse nei tre magazzini;
• ogni giorno la mensa può preparare dei piatti a scelta da due menù: vegetariano e non; ogni menù è composto da quattro piatti con un nome e una categoria (primo, secondo, contorno, dolce); ogni piatto ha un costo (il prezzo che paga lo studente);
• un menù può essere riproposto in giorni diversi; di ogni piatto si sa da quanti e quali ingredienti è composto;
• ogni giorno si registra quali piatti tra i menù disponibili sono stati ordinati dagli studenti (uno studente non deve prendere tutti i piatti del menù).

Interrogazioni (parte 2 della materia): quantità totale di ogni ingrediente immagazzinato; piatti del menù vegetariano di oggi; spesa media degli studenti che hanno ordinato un primo di un menù non vegetariano.

Teoria: Modello entità-relazione - entità, relazioni e attributiIl modello entità-relazione (ER, Chen 1976) descrive il mini-mondo con entità (classi di oggetti autonomi), relazioni (associazioni tra due o più entità, di grado 2 o più) e attributi (proprietà locali di entità o relazioni su un dominio; NULL per l'informazione mancante, con tre significati; attributi composti). Ogni costrutto ha una semantica estensionale: un'entità è un insieme di oggetti (senza ripetizioni né ordine), una relazione è un sottoinsieme del prodotto cartesiano delle entità coinvolte (quindi la stessa coppia non può comparire due volte: per gli eventi ripetuti si introduce una nuova entità, come Sessione o Fattura), un attributo è una funzione totale dalle istanze a un dominio. Si sceglie un'entità invece di un attributo quando il concetto ha istanze con proprietà proprie o collegamenti; mai trasformare un'entità in relazione. Lo schema ER è documentato dal dizionario dei dati (entità, relazioni, attributi, vincoli esterni).Modello entità-relazione - entità, relazioni e attributi →, Generalizzazioni e identificatori nel modello ERUna relazione IS-A tra superclasse e sottoclasse significa che ogni istanza della sottoclasse è anche istanza della superclasse (inclusione tra estensioni: riflessiva e transitiva, non simmetrica); vale il principio di ereditarietà: attributi, relazioni e vincoli della superclasse sono propri anche delle sottoclassi e non si ridisegnano. Una generalizzazione è una superclasse con più sottoclassi secondo un solo criterio, con due vincoli: completezza (ogni istanza della superclasse è in almeno una sottoclasse) e disgiunzione (in al più una); si hanno quattro combinazioni, e partizione se completa e disgiunta. Su una stessa entità possono esserci più generalizzazioni indipendenti con criteri diversi. L'identificatore di un'entità è un insieme di proprietà che identifica ogni istanza: interno se formato da soli attributi (1,1) dell'entità, esterno se contiene ruoli di relazioni (1,1) con altre entità (con eventuali attributi), come per l'entità debole; ogni entità ne ha almeno uno.Generalizzazioni e identificatori nel modello ER →, Ristrutturazione dello schema ER e traduzione nel modello relazionaleLa progettazione logica ha due passi. 1) Ristrutturazione dello schema ER, per togliere i costrutti che il modello relazionale non esprime: analisi delle ridondanze (attributi derivati e cicli di relazioni, decisa con volume dei dati e carico, tabelle di frequenza e accessi), eliminazione degli attributi multivalore (nuova entità), composti (appiattiti o nuova entità), delle IS-A (relazione con partecipazione (1,1) dalla sottoclasse) e delle generalizzazioni (opzione 1 relazioni, 2 accorpamento nel padre con attributo discriminante, 3 accorpamento nei figli, solo se completa e disgiunta), scelta degli identificatori principali (niente cicli di identificazione esterna), riscrittura dei vincoli esterni, partizionamento e accorpamento di entità. 2) Traduzione quasi automatica nel modello relazionale: ogni entità diventa una relazione con la chiave primaria data dall'identificatore principale (anche esterno); una relazione molti-a-molti diventa una relazione con le chiavi delle entità; una uno-a-molti con partecipazione obbligatoria si fonde con l'entità dal lato max 1 con una chiave esterna; con partecipazione opzionale si fonde (NULL) o si crea una relazione a parte; le uno-a-uno si fondono sul lato con partecipazione obbligatoria; le ternarie seguono la stessa logica con le chiavi di tutte le entità.Ristrutturazione dello schema ER e traduzione nel modello relazionale →. Le query sono svolte in Esercizio - Interrogazioni sulla mensa universitaria (tema d'esame 13 febbraio 2012).

1. Analisi dei requisiti e scelte

Frasi filtrate, per concetto:

Magazzino grande o piccolo? Non serve una distinzione tra entità: i metri quadri e la quantità massima dicono già quali sono grandi o piccoli. Un'eventuale generalizzazione Magazzino ⊃ {Grande, Piccolo} sarebbe sbagliata: le sottoclassi non avrebbero proprietà proprie e "grande o piccolo" è una proprietà di Magazzino, non un concetto diverso (la specializzazione serve a rappresentare un caso particolare di un'entità più generica, Generalizzazioni e identificatori nel modello ERUna relazione IS-A tra superclasse e sottoclasse significa che ogni istanza della sottoclasse è anche istanza della superclasse (inclusione tra estensioni: riflessiva e transitiva, non simmetrica); vale il principio di ereditarietà: attributi, relazioni e vincoli della superclasse sono propri anche delle sottoclassi e non si ridisegnano. Una generalizzazione è una superclasse con più sottoclassi secondo un solo criterio, con due vincoli: completezza (ogni istanza della superclasse è in almeno una sottoclasse) e disgiunzione (in al più una); si hanno quattro combinazioni, e partizione se completa e disgiunta. Su una stessa entità possono esserci più generalizzazioni indipendenti con criteri diversi. L'identificatore di un'entità è un insieme di proprietà che identifica ogni istanza: interno se formato da soli attributi (1,1) dell'entità, esterno se contiene ruoli di relazioni (1,1) con altre entità (con eventuali attributi), come per l'entità debole; ogni entità ne ha almeno uno.Generalizzazioni e identificatori nel modello ER →).

Ingrediente-Magazzino. Relazione Conserva con attributo Quantità. Cardinalità: Ingrediente (0,3) perché ci sono tre magazzini: minimo 0 perché un ingrediente può essere terminato (con minimo 1 non si potrebbe esprimere questo fatto); Magazzino (0,N): può essere vuoto e può contenere molti ingredienti.

Categoria dei piatti. Come modellarla: (1) attributo Categoria di Piatto più un vincolo esterno "ogni menù ha un piatto per ciascuna categoria"; (2) quattro sottoentità Primo, Secondo, Contorno, Dolce. La seconda è sbagliata: dal punto di vista del Menu complica molto lo schema (quattro binarie (1,1) che nella traduzione diventano quattro colonne di MENU) e se le categorie cambiassero bisognerebbe aggiungere colonne. Si sceglie l'attributo.

Il menù del giorno. Questo è il punto delicato: lo stesso menù può essere proposto in giorni diversi.

Soluzione. L'entità MenuDelGiorno, identificata esternamente da Giorno e dal ruolo verso Menu: relazione Propone, Menu (0,N), MenuDelGiorno (1,1). La coppia (menù, giorno) è l'identificatore; il fatto che nello stesso giorno ci siano due menù (uno vegetariano, uno no) è un vincolo esterno. Lo schema tiene traccia dei menù e di quelli proposti ogni giorno.

2. Schema ER

Note sulle cardinalità: Composto (4,4) = esattamente quattro piatti per menù; Piatto (0,N): un piatto può non essere in alcun menù o in molti; Preparato Piatto (1,N): almeno un ingrediente; Ingrediente (0,N): può non servire a nessun piatto (esiste anche se non è associato).

Vincoli esterni.

1. Ogni menù ha esattamente un piatto per ciascuna categoria (primo, secondo, contorno, dolce): la categoria serve da vincolo per il menù.
2. Un menù vegetariano (Veg = vero) contiene solo piatti vegetariani; per verificarlo si guardano gli ingredienti dei piatti (non c'è un attributo Vegetariano sul piatto, per non avere ridondanza).
3. Le istanze di Scegli devono corrispondere a quelle di Composto: un piatto scelto da un menù del giorno deve far parte del menù corrispondente.
4. Ogni giorno ci sono due menù del giorno: uno vegetariano e uno no.
5. Ogni ingrediente è conservato al massimo in tre magazzini (cardinalità (0,3)); la quantità totale in un magazzino non supera QuantitàMax.

3. Ristrutturazione

Nessun multivalore, composto o generalizzazione. Identificatori: Studente, Menu (ID serial: non ha un identificatore naturale), Piatto, Ingrediente, Magazzino (codice) interni; MenuDelGiorno esterno (giorno + menu). Nessun ciclo di identificazione (MenuDelGiorno dipende da Menu, Menu non dipende da altro). Nessuna ridondanza da eliminare.

4. Schema relazionale

Studente(*matricola)
Menu(*id, veg)
MenuDelGiorno(*id, *giorno)         FK id -> Menu(id)
Piatto(*nome, categoria, prezzo)
Composto(*menu, *piatto)            FK menu -> Menu(id); FK piatto -> Piatto(nome)
Scegli(*menu, *giorno, *piatto, *studente)
        FK (menu, giorno) -> MenuDelGiorno(id, giorno); FK piatto -> Piatto(nome);
        FK studente -> Studente(matricola)
Ingrediente(*nome, descrizione, prezzo_unitario)
Preparato(*piatto, *ingrediente, quantita)
        FK piatto -> Piatto(nome); FK ingrediente -> Ingrediente(nome)
Magazzino(*codice, descrizione, mq, quantita_max, indirizzo)
Conserva(*magazzino, *ingrediente, quantita)
        FK magazzino -> Magazzino(codice); FK ingrediente -> Ingrediente(nome)

Spiegazione delle scelte:

• MenuDelGiorno: entità con identificatore esterno → chiave composta (id del menù, giorno); la FK è parte della chiave.
• Propone (1,1) lato MenuDelGiorno: fusione in MenuDelGiorno, che già contiene l'id del menù nella chiave.
• Composto: molti-a-molti con la cardinalità minima/massima (4,4) non esprimibile: va nei vincoli esterni (la chiave non dice "esattamente quattro").
• Scegli: ternaria N:N:N → chiave formata da tutte le chiavi delle tre entità; la FK verso MenuDelGiorno è composta (menu, giorno). In questa relazione la chiave primaria include anche studente: ogni studente può scegliere lo stesso piatto dallo stesso menù del giorno una volta.
• Preparato e Conserva: molti-a-molti con attributo Quantità; Conserva ha una cardinalità (0,3) che non diventa una chiave.
• Vincolo 3: ogni tupla di Scegli deve avere la corrispondente in Composto: FOREIGN KEY (menu, piatto) REFERENCES Composto(menu, piatto) lo esprime (a differenza degli altri vincoli, può essere dichiarato in SQL).

Verificato con SQLite (tabelle create, vincolo 3 come chiave esterna composta: la scelta di un piatto non presente nel menù viene rifiutata):

CREATE TABLE Studente (matricola INTEGER PRIMARY KEY);
CREATE TABLE Menu (id INTEGER PRIMARY KEY, veg BOOLEAN NOT NULL);
CREATE TABLE MenuDelGiorno (id INTEGER REFERENCES Menu(id), giorno DATE, PRIMARY KEY (id, giorno));
CREATE TABLE Piatto (nome TEXT PRIMARY KEY, categoria TEXT NOT NULL
                     CHECK (categoria IN ('primo','secondo','contorno','dolce')), prezzo REAL NOT NULL);
CREATE TABLE Composto (menu INTEGER REFERENCES Menu(id), piatto TEXT REFERENCES Piatto(nome),
                       PRIMARY KEY (menu, piatto));
CREATE TABLE Scegli (menu INTEGER, giorno DATE, piatto TEXT, studente INTEGER REFERENCES Studente(matricola),
                     PRIMARY KEY (menu, giorno, piatto, studente),
                     FOREIGN KEY (menu, giorno) REFERENCES MenuDelGiorno(id, giorno),
                     FOREIGN KEY (menu, piatto) REFERENCES Composto(menu, piatto));
CREATE TABLE Ingrediente (nome TEXT PRIMARY KEY, descrizione TEXT, prezzo_unitario REAL NOT NULL);
CREATE TABLE Preparato (piatto TEXT REFERENCES Piatto(nome), ingrediente TEXT REFERENCES Ingrediente(nome),
                        quantita REAL NOT NULL, PRIMARY KEY (piatto, ingrediente));
CREATE TABLE Magazzino (codice INTEGER PRIMARY KEY, descrizione TEXT, mq REAL, quantita_max REAL, indirizzo TEXT);
CREATE TABLE Conserva (magazzino INTEGER REFERENCES Magazzino(codice),
                       ingrediente TEXT REFERENCES Ingrediente(nome),
                       quantita REAL NOT NULL, PRIMARY KEY (magazzino, ingrediente));

5. Alternativa e osservazioni

• Dove sta l'informazione vegetariano. Nella soluzione il flag Veg è sul Menu; mettere anche un attributo Vegetariano sul piatto sarebbe una ridondanza (si ricava dagli ingredienti), utile solo per controllare il vincolo 2.
• Alternativa valida del corso: MenuDelGiorno con attributi Giorno e Vegetariano, collegato a Menu (0,N) dalla relazione Propone (1,1); il flag Veg di Menu diventa allora ridondante e si può togliere. Il controllo che il menù sia vegetariano si fa all'inserimento del menù del giorno, senza vincoli in più.
• Controllo del materiale del corso. In questo tema non ho trovato errori: ER e schema relazionale scritti a mano sono coerenti (per esempio la chiave di SCEGLIE contiene tutte e quattro le colonne, come richiede una ternaria N:N:N).

Domande d'esame

Perché il menù del giorno non può essere una specializzazione di Menu? Traccia. In una IS-A ogni istanza della sottoclasse è un'istanza della superclasse con lo stesso identificatore; il menù del giorno ha un identificatore diverso (giorno più menù) e lo stesso menù compare in più giorni: serve un'entità con identificatore esterno.

Come si traduce una ternaria N:N:N con una chiave esterna composta? Traccia. Una relazione con le chiavi delle tre entità; se un'entità ha chiave composta (MenuDelGiorno: id, giorno), la sua FK è composta e occupa due colonne della chiave primaria.