Esercizio - Progettazione ER e schema relazionale della compagnia di treni (tema d'esame 17 giugno 2013)

Testo (compito d'esame del 17/06/2013, svolto a lezione nel corso di Basi di Dati, Ing. Informatica UniPD). Una nuova compagnia di treni vuole gestire con una base di dati i rifornimenti e la composizione dei treni. Requisiti in sintesi:

• un treno è composto da una o due motrici (in quest'ultimo caso una in testa e una in coda) e da un certo numero di carrozze;
• le motrici hanno un motore, una strumentazione di guida con un numero di posizioni di guida, una lunghezza e un peso; le carrozze hanno un numero di posti, di bagni, una disposizione (su uno o due piani), lunghezza e peso; ognuna ha un tipo di attacco (non tutte le motrici si agganciano a tutte le carrozze);
• ci sono fornitori, ciascuno con una partita IVA; per ogni motrice si vuole sapere il fornitore del motore e quello della strumentazione, per ogni carrozza quello di sedili e tavolini e quello dei bagni; per motrici e carrozze, chi fornisce ruote e freni;
• ogni fornitore può fornire più pezzi; un pezzo non è fornito per forza da un solo fornitore; ogni pezzo ha un prezzo diverso a seconda del fornitore;
• la composizione di ciascun treno per ogni tratta coperta (insieme di motrici e carrozze usate per le tratte specifiche): per ogni tratta, cioè coppia (origine, destinazione), un'unica composizione; contratti pluriennali mantengono i prezzi costanti (la modifica dei prezzi non va gestita).

Query: il fornitore che fornisce più pezzi degli altri; il costo complessivo di ciascuna composizione; la tratta dei treni con due motrici fornite da f1 (almeno un pezzo per ciascuna).

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 →. Query: Esercizio - Interrogazioni sulla compagnia di treni (tema d'esame 17 giugno 2013).

1. Analisi dei requisiti

Due fatti vanno tenuti distinti: chi può vendere un pezzo, a che prezzo (Offre) e chi ha venduto un certo pezzo per un certo veicolo (Vende). Il prezzo dipende solo da fornitore e pezzo (non dal veicolo), quindi sta in Offre.

2. Schema ER

Generalizzazione: Veicolo ⊃ {Motrice, Carrozza} (totale, esclusiva).

Note sulle cardinalità:

• Offre: ogni pezzo ha almeno un fornitore (1,N); un fornitore può non offrire nulla (0,N); Prezzo dipende dalla coppia.
• Vende: ogni veicolo ha almeno un pezzo con un fornitore (1,N): la tripla (fornitore, pezzo, veicolo) dice chi ha fornito quel pezzo per quel veicolo.
• ComMot (1,2): ogni composizione ha da una a due motrici; Motrice (0,1): una motrice appartiene al più a una composizione (può non essere ancora assegnata); Testa (vero o falso) indica se la motrice è in testa; con due motrici una è in testa e una in coda.
• ComCar: una composizione ha almeno una carrozza (1,N); una carrozza è in al più una composizione (0,1).
• Tratta ha identificatore esterno formato da due ruoli, perché è identificata dalla coppia (origine, destinazione): non ci sono due treni per la stessa coppia, quindi Percorre ha Tratta (0,1): una tratta ha al più una composizione; una composizione può servire più tratte (0,N).
• Perché Tratta è un'entità e non una relazione ricorsiva Città-Città: a una tratta si collega una composizione (con una relazione); una relazione non si collega ad altre relazioni (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 →).

Vincoli esterni.

1. Vende(f, p, v) esiste solo se Offre(f, p): un fornitore può vendere solo pezzi che offre.
2. Il numero di motrici con Testa = vero è esattamente uno per composizione con due motrici.
3. L'attacco di veicoli adiacenti deve essere compatibile (tipo di attacco): non si può esprimere con il diagramma.
4. Costo di una Composizione = somma, sui veicoli della composizione e sui pezzi venduti per ciascuno, del Prezzo che Offre indica per fornitore e pezzo.

3. Ristrutturazione

Attributo derivato. Costo di Composizione si ricava attraverso lo schema (somma su veicoli, pezzi e prezzi). Il corso lo tiene perché serve alla query 2 e perché i contratti pluriennali tengono costanti i prezzi (il testo dice di non affrontare la modifica dei prezzi): il valore non va aggiornato di continuo. Redundanza dichiarata, con il vincolo 4.

Generalizzazione Veicolo ⊃ {Motrice, Carrozza}. Totale ed esclusiva: sono valide sia l'opzione 2 (tutto in Veicolo) sia la 3 (due entità separate). Si usa l'opzione 2: gli attributi propri diventano opzionali, con un attributo discriminante Tipo. Il motivo: Vende e la composizione riguardano tutti i veicoli allo stesso modo, quindi con l'opzione 2 c'è una sola relazione Vende invece di due; con l'opzione 3 si duplicherebbero le relazioni verso Veicolo (Vende e Compone per le motrici e per le carrozze). Prezzo da pagare: NULL sistematici (NumeroGuida solo per le motrici, posti e bagni solo per le carrozze) e la cardinalità (1,2) di ComMot, che non si distingue più dalla (1,N) di ComCar: diventa vincolo esterno ("ogni composizione ha una o due motrici").

Dopo la ristrutturazione: Veicolo(ID, Tipo, Peso, Lunghezza, Attacco, NumeroGuida (0,1), NumeroPosti (0,1), Disposizione (0,1), NumeroBagni (0,1)); relazione Compone tra Composizione (1,N) e Veicolo (0,1) con attributo Testa (0,1) (solo per le motrici).

4. Schema relazionale

Fornitore(*piva)
Pezzo(*codice, descrizione, categoria)
Offre(*fornitore, *pezzo, prezzo)               FK fornitore -> Fornitore(piva); FK pezzo -> Pezzo(codice)
Citta(*id)
Composizione(*codice, descrizione, costo)
Veicolo(*id, tipo, peso, lunghezza, attacco, numero_guida?, numero_posti?, disposizione?, numero_bagni?,
        composizione?, testa?)                  FK composizione -> Composizione(codice)
Vende(*fornitore, *pezzo, *veicolo)             FK (fornitore, pezzo) -> Offre(fornitore, pezzo); FK veicolo -> Veicolo(id)
Tratta(*origine, *destinazione, composizione?)  FK origine -> Citta(id); FK destinazione -> Citta(id);
                                                FK composizione -> Composizione(codice)

Traduzione delle relazioni:

• Offre: molti-a-molti con attributo → relazione con le due chiavi e Prezzo.
• Vende: ternaria molti-a-molti-a-molti → relazione con le tre chiavi. Il vincolo 1 diventa una chiave esterna composta (fornitore, pezzo) verso Offre.
• Compone (Veicolo (0,1)): uno-a-molti con partecipazione opzionale del veicolo → fusione in Veicolo con composizione ammessa NULL (i NULL non sono sistematici: pochi veicoli sono ancora non assegnati). testa va in Veicolo.
• Tratta: identificatore esterno da due ruoli → chiave composta (origine, destinazione), con due FK verso la stessa tabella Citta.
• Percorre (Tratta (0,1)): fusione in Tratta con composizione opzionale.

Con questo schema il costo di una composizione è l'attributo costo; le interrogazioni sono svolte nell'esercizio sulle query.

Versione SQL (provata con SQLite e dati di esempio: composizione con due motrici fornite da f1):

CREATE TABLE Fornitore (piva TEXT PRIMARY KEY);
CREATE TABLE Pezzo (codice TEXT PRIMARY KEY, descrizione TEXT, categoria TEXT NOT NULL);
CREATE TABLE Offre (fornitore TEXT REFERENCES Fornitore(piva), pezzo TEXT REFERENCES Pezzo(codice),
                    prezzo REAL NOT NULL, PRIMARY KEY (fornitore, pezzo));
CREATE TABLE Citta (id INTEGER PRIMARY KEY);
CREATE TABLE Composizione (codice TEXT PRIMARY KEY, descrizione TEXT, costo REAL);
CREATE TABLE Veicolo (id INTEGER PRIMARY KEY, tipo TEXT NOT NULL CHECK (tipo IN ('motrice','carrozza')),
  peso REAL, lunghezza REAL, attacco TEXT NOT NULL,
  numero_guida INTEGER, numero_posti INTEGER, disposizione INTEGER, numero_bagni INTEGER,
  composizione TEXT REFERENCES Composizione(codice), testa BOOLEAN,
  CHECK ((tipo = 'motrice' AND numero_guida IS NOT NULL AND numero_posti IS NULL
          AND disposizione IS NULL AND numero_bagni IS NULL)
      OR (tipo = 'carrozza' AND numero_posti IS NOT NULL AND disposizione IS NOT NULL
          AND numero_bagni IS NOT NULL AND numero_guida IS NULL AND testa IS NULL)));
CREATE TABLE Vende (fornitore TEXT, pezzo TEXT, veicolo INTEGER REFERENCES Veicolo(id),
  PRIMARY KEY (fornitore, pezzo, veicolo),
  FOREIGN KEY (fornitore, pezzo) REFERENCES Offre(fornitore, pezzo));
CREATE TABLE Tratta (origine INTEGER REFERENCES Citta(id), destinazione INTEGER REFERENCES Citta(id),
  composizione TEXT REFERENCES Composizione(codice),
  PRIMARY KEY (origine, destinazione), CHECK (origine <> destinazione));

Il CHECK su Veicolo traduce i vincoli di tipo della generalizzazione (opzione 2).

5. Osservazioni

• Opzioni 2 e 3: il corso nota che qui entrambe le trasformazioni sono valide e assume l'opzione 2 ("portiamo tutto in entità Veicolo").

• Vincolo di unicità della tratta: una sola composizione per tratta si ottiene con la chiave (origine, destinazione) e la cardinalità (0,1) di Tratta in Percorre; non occorre altro.

• Doppia motrice: "una in testa e una in coda" è un vincolo esterno su Testa; non lo esprime nessuna cardinalità.

• Cardinalità di Città. Nel disegno a mano le cardinalità di Città nelle relazioni Orig e Dest sembrano (0,1); ma una città è l'origine (o la destinazione) di più tratte (Padova-Venezia e Padova-Milano), quindi il lato Città è (0,N) come scritto sopra, con Tratta (1,1).

Domande d'esame

Perché Tratta è un'entità e non una relazione tra città? Traccia. Perché va collegata a una composizione: una relazione non può essere collegata ad altre relazioni; l'entità ha identificatore esterno (origine, destinazione).

Quando conviene tenere un attributo derivato come il costo della composizione? Traccia. Quando il calcolo è oneroso (somma su veicoli, pezzi, prezzi), serve a un'interrogazione importante e il valore cambia di rado (prezzi costanti per contratto): si tiene, documentato, con un vincolo di coerenza.