Esercizio - Progettazione ER e schema relazionale delle formule scientifiche (tema d'esame 22 gennaio 2025)

Testo (tema d'esame del 22/01/2025, Basi di Dati, Ing. Informatica UniPD, seconda e terza parte). Degli studenti universitari progettano un database per archiviare, categorizzare e collegare formule scientifiche e matematiche, con una risorsa completa sulle loro origini, applicazioni ed evoluzione. Requisiti:

Gli utenti che utilizzeranno questo database includono ricercatori, educatori e storici della scienza. Il database memorizzerà informazioni sulle formule includendo: i loro nomi (ad esempio legge di gravitazione di Newton), espressioni (ad esempio $F=G\frac{m_1m_2}{r^2}$), descrizioni (ad esempio "ogni punto materiale attrae ogni altro punto materiale con una forza..."), e anno di scoperta (ad esempio 1687). Per ogni formula si vuole sapere il campo di studio di riferimento e chi sono gli scienziati che l'hanno definita. Per ogni scienziato si vuole sapere nome e cognome e il campo di studio principale. Infine, per ogni formula, si vuole anche tenere traccia delle pubblicazioni scientifiche che la menzionano, tenendo conto che di ogni pubblicazione si vuole avere il titolo, un sommario, l'anno di pubblicazione ed un identificatore unico (ad esempio un DOI).

Domande: schema ER, requisiti strutturati, vincoli, ristrutturazione, schema relazionale con vincoli, query (formule attribuite a scienziati con cognome "Bernoulli"; scienziati con la Fisica come campo principale che hanno definito formule di Matematica; scienziato con più formule: parte sulle interrogazioni). Domanda 2 (lode): modifiche allo schema per rispondere più efficientemente ad almeno una query.

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 →, Relazioni ricorsive, cardinalità e vincoli di integrità nel modello ERI vincoli di integrità di uno schema ER sono di quattro tipi: di cardinalità sulle relazioni, di cardinalità sugli attributi, di identificazione sulle entità e vincoli esterni. La cardinalità (min,max) di un'entità e con ruolo u in una relazione r dice a quante istanze di r partecipa ogni istanza di e: min 0 = partecipazione opzionale, min 1 = obbligatoria, max 1 = al più una volta, max N = molte; le relazioni binarie si classificano per i massimi in uno-a-uno, uno-a-molti, molti-a-molti. I vincoli di cardinalità non permettono di violare la semantica estensionale (una coppia compare una volta sola). Una relazione ricorsiva collega un'entità con se stessa e richiede i ruoli (Predecessore e Successore, Sorgente e Destinazione): con le cardinalità si ottengono catene, alberi e grafi diretti; grafi non diretti, assenza di cicli e di cappi richiedono vincoli esterni; i multigrafi si modellano con una terza entità (etichetta, colore). Gli attributi hanno cardinalità (1,1) di default, (0,1) se opzionali, (x,N) se multivalore.Relazioni ricorsive, cardinalità e vincoli di integrità nel modello ER →, 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 sulle formule scientifiche (tema d'esame 22 gennaio 2025).

1. Che cosa è un'entità, che cosa no

Gli utenti non sono entità. "Ricercatori, educatori, storici" sono gli utilizzatori del sistema, cioè chi interagisce con la base di dati. Non si vogliono salvare informazioni su queste persone: non corrispondono a nessuna entità (Ciclo di vita di un sistema informativo e analisi dei requisitiIl ciclo di vita di un sistema informativo ha le fasi studio di fattibilità, analisi dei requisiti, progettazione (dei dati e delle applicazioni), sviluppo, collaudo, esercizio e manutenzione; il modello a cascata procede in sequenza, quello a spirale itera per versioni. I requisiti sono funzionali (che cosa fa il sistema), non funzionali (come lo fa, es. tempi) e vincoli aggiuntivi imposti dai committenti; si raccolgono da frasi in linguaggio naturale, documentazione e applicazioni esistenti e si analizzano con frasi filtrate e glossario dei dati. La progettazione di una base di dati ha tre fasi: concettuale (schema ER, indipendente dal DBMS), logica (schema relazionale, per una classe di DBMS, con il carico applicativo) e fisica (strutture del DBMS scelto). Un modello è un insieme di costrutti, lo schema è la rappresentazione del mini-mondo (livello intensionale), l'istanza sono i dati (livello estensionale); un buono schema è completo, corretto, minimale e leggibile.Ciclo di vita di un sistema informativo e analisi dei requisiti →).

Formula. Entità, con attributi nome, espressione, descrizione, anno di scoperta. L'identificatore è un ID (serial, numero progressivo senza significato). Perché non il nome? Il nome non è una buona scelta per via della lunghezza e perché non ne abbiamo la garanzia di univocità: potrebbero esistere formule con lo stesso nome ed espressioni diverse. (Stesso ragionamento per l'espressione: lunga, e con più forme equivalenti.)

Campo di studio: entità o attributo? Si vuole il campo di riferimento di ogni formula e il campo principale di ogni scienziato. Il campo è un'entità: formule diverse possono appartenere allo stesso campo, e lo stesso vale per gli scienziati. Modellarlo come attributo stringa dei due concetti duplicherebbe il dato (ridondanza) e due stringhe uguali sono solo uguaglianza testuale ("Fisica" contro "fisica"). Come entità è comune a Formula e Scienziato ed evita la ridondanza. Identificatore: ID (serial), con attributo Nome.

Scienziato. Entità con nome e cognome. Non si identifica con la coppia (Nome, Cognome): due scienziati possono avere gli stessi nome e cognome (omonimi), e non si usa il codice fiscale perché non esiste nel contesto (scienziati storici). Identificatore: ID serial.

Pubblicazione. Entità con titolo, sommario, anno e DOI come identificatore (il testo dice che c'è un identificatore unico).

2. Schema ER

Perché queste cardinalità:

• R2: una formula ha sempre uno e un solo campo di studio (1,1); un campo può avere più formule, ma anche nessuna (0,N).
• R3: una formula è definita da almeno uno scienziato (1,N) (può averne più d'uno: formule con più scopritori); uno scienziato può non aver definito formule nel database (0,N).
• R4: lo scienziato ha un solo campo principale (1,1).
• R1: la cardinalità (1,N) lato Pubblicazione è importantissima: non si vogliono salvare pubblicazioni che non menzionano formule; quindi, se c'è una pubblicazione nella base di dati, deve contenere almeno una formula. Una formula può non essere menzionata (0,N).

Vincoli esterni. Nessuno obbligatorio nel testo. Si possono esplicitare: l'anno di pubblicazione non precede l'anno di scoperta delle formule menzionate (una pubblicazione non può menzionare una formula non ancora scoperta: da usare con cautela, perché una pubblicazione storica può datare l'anno in modo diverso); l'espressione e la descrizione non sono vuote.

Il campo di studio principale dello scienziato (R4) e il campo di una formula (R2) sono relazioni diverse con la stessa entità: la stessa entità CampoStudio partecipa a due relazioni distinte, non a una relazione ricorsiva. Non c'è nessun legame vincolato tra il campo di uno scienziato e quello delle sue formule (la seconda query chiede proprio di trovare scienziati di Fisica che hanno definito formule di Matematica): è informazione indipendente.

3. Ristrutturazione

Nessun attributo multivalore o composto (l'espressione è una stringa), nessuna generalizzazione, nessuna ridondanza da eliminare: il ciclo Formula–Scienziato–CampoStudio–Formula non è ridondante, perché il campo di una formula e il campo principale dello scienziato sono informazioni diverse (nessuna dipende dall'altra). Identificatori: ID per Formula, Scienziato, CampoStudio (serial: non ci sono identificatori naturali affidabili); DOI per Pubblicazione. Nessun ciclo di identificazione esterna (tutti gli identificatori sono interni).

4. Schema relazionale

CampoStudio(*id, nome)
Formula(*id, nome, descrizione, espressione, anno, campo)         FK campo -> CampoStudio(id)
Scienziato(*id, nome, cognome, campo)                            FK campo -> CampoStudio(id)
Pubblicazione(*doi, titolo, sommario, anno)
R3(*formula, *scienziato)      FK formula -> Formula(id); FK scienziato -> Scienziato(id)
R1(*pubblicazione, *formula)   FK pubblicazione -> Pubblicazione(doi); FK formula -> Formula(id)

Traduzione:

• R2 (Formula (1,1)) uno-a-molti obbligatorio → fusa in Formula (colonna campo, NOT NULL).
• R4 (Scienziato (1,1)) → fusa in Scienziato (colonna campo, NOT NULL).
• R3 e R1 molti-a-molti → relazioni con le due chiavi come chiave primaria.
• Vincoli non esprimibili: le cardinalità minime 1 del lato "molti" (una formula ha almeno uno scienziato, una pubblicazione almeno una formula) non si dichiarano con le chiavi esterne: vanno nei vincoli esterni (trigger o controllo applicativo, o transazioni che inseriscono la formula con il suo scienziato insieme).

Versione SQL (provata in SQLite con dati di esempio: Newton, Bernoulli e due campi):

CREATE TABLE CampoStudio (id INTEGER PRIMARY KEY, nome TEXT NOT NULL UNIQUE);
CREATE TABLE Formula (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, descrizione TEXT, espressione TEXT NOT NULL,
  anno INTEGER, campo INTEGER NOT NULL REFERENCES CampoStudio(id));
CREATE TABLE Scienziato (id INTEGER PRIMARY KEY, nome TEXT NOT NULL, cognome TEXT NOT NULL,
  campo INTEGER NOT NULL REFERENCES CampoStudio(id));
CREATE TABLE Pubblicazione (doi TEXT PRIMARY KEY, titolo TEXT NOT NULL, sommario TEXT, anno INTEGER NOT NULL);
CREATE TABLE R3 (formula INTEGER REFERENCES Formula(id), scienziato INTEGER REFERENCES Scienziato(id),
  PRIMARY KEY (formula, scienziato));
CREATE TABLE R1 (pubblicazione TEXT REFERENCES Pubblicazione(doi), formula INTEGER REFERENCES Formula(id),
  PRIMARY KEY (pubblicazione, formula));

Il vincolo "una formula ha almeno uno scienziato" si può far rispettare con un trigger alla cancellazione da R3 che controlli che resti almeno una riga, e la creazione di una formula con almeno una riga di R3 nella stessa transazione (vincolo differibile in PostgreSQL con DEFERRABLE INITIALLY DEFERRED).

5. Domanda 2 (lode): rendere una query più efficiente

Il testo non riporta la risposta del corso. Una discussione plausibile:

• Query 3 (scienziato con più formule): richiede un raggruppamento su R3. Si potrebbe aggiungere un attributo derivato NumeroFormule a Scienziato, aggiornato a ogni inserimento o cancellazione in R3: la query diventa una scansione di Scienziato. Costo: ridondanza, vincolo di coerenza, scritture in più: conviene solo se la query è frequente e gli inserimenti di formule sono rari rispetto alle letture (tabella degli accessi con scritture pesate 2, 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 2: confronta il campo dello scienziato e quello della formula con due join su CampoStudio: non serve un attributo derivato, bastano indici su campo di Formula e Scienziato (scelta fisica).
• Anche un attributo NomeCampo ripetuto in Formula e Scienziato eviterebbe il join con CampoStudio ma farebbe tornare la ridondanza che la modellazione ha evitato: da scartare.

6. Osservazioni

• Nello schema del corso lo scienziato ha un attributo campo che è una chiave esterna (ID di CampoStudio) e così la formula. Le query riportate nella soluzione confrontano campo = 'Fisica' e campo = 'Matematica' come se fossero nomi: con questo schema il confronto va fatto sul nome del campo, quindi serve un join con CampoStudio (questione ripresa nell'esercizio sulle query).

Domande d'esame

Perché il campo di studio è un'entità e non un attributo? Traccia. È condiviso da Formula e Scienziato e più oggetti appartengono allo stesso campo: come attributo si duplicherebbe la stringa in entrambi (ridondanza, uguaglianza fragile); come entità c'è una sola istanza per campo.

Perché non si usa (nome, cognome) come identificatore dello scienziato? Traccia. Possono esistere omonimi, e il codice fiscale non c'è: serve un identificatore garantito univoco, un ID serial.

Che cosa significa la cardinalità (1,N) lato Pubblicazione in R1? Traccia. Ogni pubblicazione menziona almeno una formula: non si salvano pubblicazioni senza formule. Non è esprimibile con una chiave esterna e va fatta rispettare con un trigger o dall'applicazione.