Esercizio - Progettazione ER e schema relazionale del social network (esercizio social e laboratorio)

Testo (esercizio di modellazione ER "social network" e laboratorio, corso di Basi di Dati, Ing. Informatica UniPD). Si vuole sviluppare una piattaforma social in cui ogni utente registrato può creare uno o più post. Un utente può anche rispondere a un post con un commento, oppure rispondere a un commento già presente. Un post può avere più commenti, così come un commento può ricevere a sua volta più risposte. Gli utenti si registrano fornendo email e password e possono seguirsi a vicenda. Infine si tiene traccia della data e ora in cui sono scritti post e messaggi. Il laboratorio aggiunge username, data di iscrizione e visibilità del post (pubblico, privato, amici).

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 →, 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 →.

1. Analisi dei requisiti

Frasi filtrate, per concetto:

Scelte di modellazione:

• Utente, Post, Commento sono entità: hanno identità e proprietà proprie. "Messaggi" nel testo significa i commenti.
• Seguire è un legame tra utenti: relazione ricorsiva su Utente, con ruoli Follower e Followed, molti-a-molti.
• Un commento risponde a un post oppure a un commento. Il corso sceglie: ogni commento appartiene a un post e, se è una risposta, ha anche un commento di origine. Il vantaggio è che ogni commento conosce subito il suo post, senza risalire la catena.
• La data e ora è una proprietà dell'azione (chi ha scritto, quando), quindi attributo delle relazioni Pubblica e Scrive.

2. Schema ER

Lettura delle cardinalità:

• Pubblica: ogni post ha esattamente un autore (1,1); un utente può non aver pubblicato nulla (0,N).
• Scrive: ogni commento è associato a una sola tripla (utente, post, commento): ha un autore e un post (1,1); un utente può non aver commentato, un post può non avere commenti (0,N).
• Risponde: un commento ha al più un commento di origine (0,1), ma può riceverne molte risposte (0,N): un albero di risposte. I commenti di primo livello non hanno origine.
• Segue: tutti (0,N): si può seguire nessuno e non essere seguiti.

Perché una ternaria? Scrive lega tre concetti insieme e il commento ha (1,1): conoscere il commento determina utente e post. È equivalente a due relazioni binarie (1,1) con Data_Ora attributo del commento; la ternaria mostra che l'azione "scrivere" ha tre partecipanti.

Vincoli esterni.

1. Un utente non segue se stesso: Follower $\ne$ Followed.
2. Una risposta appartiene allo stesso post del commento a cui risponde.
3. La data e ora di un commento non precede quella del post, né quella del commento di origine.
4. Username unico (chiave candidata di Utente oltre all'email).
5. Visibilita ammette solo public, private, friends.

3. Ristrutturazione

Nessun attributo multivalore, composto o derivato, nessuna generalizzazione. Gli identificatori sono già principali: Email per Utente (interno), ID per Post e Commento (serial, senza significato). Le relazioni Pubblica e Scrive hanno già la forma giusta per la fusione.

4. Schema relazionale

Chiave primaria sottolineata, chiavi esterne in elenco.

Utente(*email, username, password, subscription_date)           UNIQUE(username)
Segue(*follower, *followed)      FK follower -> Utente(email); FK followed -> Utente(email)
Post(*id, testo, data_ora, utente, visibilita)                  FK utente -> Utente(email)
Commento(*id, testo, data_ora, commento_origine?, post, utente)
         FK commento_origine -> Commento(id); FK post -> Post(id); FK utente -> Utente(email)

Perché così:

• Segue: molti-a-molti → relazione con le due chiavi, chiave primaria entrambe, due chiavi esterne verso la stessa tabella (colonne con nomi diversi per i due ruoli).
• Pubblica: uno-a-molti con partecipazione obbligatoria di Post (1,1) → fusione in Post, con Utente come chiave esterna non NULL e Data_Ora come attributo di Post.
• Scrive: ternaria con Commento (1,1) → fusione in Commento: chiavi esterne verso Utente e Post (entrambe NOT NULL) e Data_Ora.
• Risponde: ricorsiva uno-a-molti: la chiave esterna sta dal lato max 1, cioè Commento_Risposta: la riga di un commento contiene il suo commento di origine. Partecipazione opzionale (0,1), quindi commento_origine può essere NULL (commento di primo livello). Alternativa: tabella a parte Risponde(risposta, origine); non conviene, perché molti commenti non rispondono ad altri ma in generale i NULL non sono sistematici.

Versione SQL (provata in SQLite; in PostgreSQL id sarebbe serial e visibilita un tipo enumerato):

CREATE TABLE Utente (
  email TEXT PRIMARY KEY, username TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL, subscription_date DATE NOT NULL);
CREATE TABLE Segue (
  follower TEXT NOT NULL REFERENCES Utente(email) ON UPDATE CASCADE ON DELETE CASCADE,
  followed TEXT NOT NULL REFERENCES Utente(email) ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY (follower, followed), CHECK (follower <> followed));
CREATE TABLE Post (
  id INTEGER PRIMARY KEY, testo TEXT NOT NULL, data_ora TIMESTAMP NOT NULL,
  utente TEXT NOT NULL REFERENCES Utente(email) ON DELETE CASCADE,
  visibilita TEXT NOT NULL DEFAULT 'public' CHECK (visibilita IN ('public','private','friends')));
CREATE TABLE Commento (
  id INTEGER PRIMARY KEY, testo TEXT NOT NULL, data_ora TIMESTAMP NOT NULL,
  commento_origine INTEGER REFERENCES Commento(id) ON DELETE CASCADE,
  post INTEGER NOT NULL REFERENCES Post(id) ON DELETE CASCADE,
  utente TEXT NOT NULL REFERENCES Utente(email) ON DELETE CASCADE);

Il vincolo 1 è un CHECK; il vincolo 5 un CHECK ... IN; il 4 un UNIQUE; i vincoli 2 e 3 coinvolgono più tabelle e non sono esprimibili con un CHECK: servono trigger o controllo applicativo. Una query che trova le violazioni del vincolo 2:

SELECT r.id FROM Commento r JOIN Commento o ON r.commento_origine = o.id
WHERE r.post <> o.post;

Con i dati del laboratorio (5 utenti, 5 post, 6 commenti di cui 3 risposte) restituisce zero righe: il vincolo è rispettato.

ON DELETE CASCADE. Come nel laboratorio, cancellare un utente cancella i suoi post, commenti e legami di follow; cancellare un post cancella i suoi commenti; cancellare un commento cancella le sue risposte.

5. Osservazioni sul materiale del corso

• Nel diagramma ER del laboratorio le relazioni Segue e Pubblica sono entrambe etichettate SCEGLIE, ma in uno schema i nomi delle relazioni devono essere univoci: è un errore di etichetta (nello schema relazionale e nelle query si usano Segue e il campo utente di Post).
• Il laboratorio aggiunge allo schema un vincolo di unicità sullo username e il controllo che nessuno segua se stesso, che non compaiono nell'ER del tema e vanno dunque dichiarati tra i vincoli esterni.

Domande d'esame

Perché Risponde ha cardinalità (0,N) lato origine e (0,1) lato risposta? Traccia. Un commento può ricevere molte risposte ma risponde al più a un altro commento (albero). Con (1,1) sul lato risposta nessun commento di primo livello potrebbe esistere.

Come si garantisce che una risposta appartenga allo stesso post dell'origine? Traccia. Non è esprimibile con il diagramma né con un CHECK a tabella singola: vincolo esterno, realizzato con trigger o controllo applicativo; una query di verifica cerca le coppie con post diversi.