Esercizio - Progettazione ER e schema relazionale dello streaming di musica (tema d'esame 12 febbraio 2024)

Testo (compito d'esame del 12/02/2024, Basi di Dati, Ing. Informatica UniPD, seconda e terza parte). Degli studenti vogliono realizzare una piattaforma per lo streaming di brani musicali. I requisiti per il database dell'applicazione:

• ogni utente è iscritto con un nome utente e una password e può, se vuole, inserire l'anno di nascita e i generi musicali preferiti (per migliorare i suggerimenti sui brani non ancora ascoltati);
• ogni brano è identificato da un codice, ha un titolo, una durata e un artista di riferimento;
• ogni artista è identificato da un codice, ha un nome e può essere un gruppo o un artista singolo. Per un gruppo si vuole l'anno di formazione e chi è il cantante leader, sempre definito per un gruppo e sempre un artista singolo; le formazioni non cambiano nel tempo. Per ogni artista singolo: nome, cognome, anno di nascita e sesso;
• un brano appartiene a uno o più generi; ogni genere è identificato dal nome e ha una descrizione; un genere deve avere almeno un brano;
• per i brani che l'utente ha ascoltato almeno una volta si vuole sapere quante volte in totale lo stesso utente l'ha riprodotto.

Si chiede: schema ER, requisiti strutturati se occorre, vincoli, ristrutturazione, schema relazionale con vincoli (senza attributi ridondanti), poi le query (numero totale di ascolti per brano; band rock ascoltata dal maggior numero di utenti; cantanti leader dei gruppi pop dei brani ascoltati da utenti nati dopo il 2000: parte sulle interrogazioni). Domanda 2 (solo per la 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 →, 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 piattaforma di streaming musicale (tema d'esame 12 febbraio 2024).

1. Requisiti strutturati

Scelte:

• Generalizzazione Artista ⊃ {Gruppo, Cantante}: totale (ogni artista è l'uno o l'altro) ed esclusiva. Il nome è comune e sta nel padre; le sottoclassi hanno attributi propri: è una generalizzazione ben motivata (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 →).
• Ascolti = attributo della relazione tra Utente e Brano: "numero di riproduzioni" è una proprietà dell'associazione, non dell'utente né del brano. Non servono eventi ripetuti con data (il testo chiede solo il totale per coppia): una coppia (utente, brano) ha un solo valore, perfettamente compatibile con la semantica della relazione come insieme. Se servisse ogni singolo ascolto con data, sarebbe necessaria un'entità Ascolto (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 →).
• Leader: relazione tra Gruppo e Cantante, non attributo: il leader è un artista singolo con le sue proprietà.

2. Schema ER

Generalizzazione: Artista ⊃ {Gruppo, Cantante} (totale, esclusiva).

Cardinalità: R1 l'utente può non indicare preferenze (0,N), un genere può non essere preferito da nessuno; R2 ogni brano ha almeno un genere (1,N) e ogni genere almeno un brano (1,N), come richiesto; R3 nessun obbligo di ascolto; R4 ogni brano ha un artista di riferimento (1,1); il lato Artista è (1,N) (ogni artista ha almeno un brano; con (0,N) si ammetterebbero anche artisti ancora senza brani); R5 ogni gruppo ha esattamente un leader (1,1), un cantante può guidare zero o più gruppi (0,N).

Vincoli esterni.

1. NumeroAscolti $\ge 1$ (esiste un'istanza di R3 solo per i brani ascoltati almeno una volta).
2. Il leader di un gruppo è un Cantante, non un altro gruppo: lo esprime la relazione R5 con il lato Cantante (e nella traduzione un vincolo di tipo).
3. Le formazioni dei gruppi non cambiano: nessuna storia da gestire.
4. AnnoNascita (0,1): l'utente può non indicarlo.
5. (R2) un genere deve avere almeno un brano: già nelle cardinalità (1,N).

3. Ristrutturazione

Attributi derivati / ridondanze. Nessun attributo ridondante (la consegna lo chiede). Nessun ciclo di relazioni ridondante.

Generalizzazione Artista ⊃ {Gruppo, Cantante}. Completa ed esclusiva; la relazione R5 collega due sottoclassi; R4 usa la superclasse. Si sceglie l'opzione 2 (accorpamento nel padre, con attributo discriminante Tipo, (1,1) perché completa e disgiunta): un'unica entità Artista con gli attributi dei figli opzionali (AnnoFormazione (0,1) per i gruppi; Cognome, AnnoNascita, Sesso (0,1) per i cantanti). Motivo: R4 e le query riguardano gli artisti in generale (brano ascoltato di un gruppo o di un cantante), quindi l'opzione 3 duplicherebbe R4; la 1 aggiungerebbe tabelle e join. R5 diventa una relazione ricorsiva su Artista, Leader, con due ruoli: Gruppo con cardinalità (0,1) (solo i gruppi hanno un leader, e uno solo) e Leader con cardinalità (0,N) (un cantante può essere leader di più gruppi).

Vincoli esterni nuovi: (a) Tipo = gruppo ⇒ AnnoFormazione e Leader non NULL; Tipo = cantante ⇒ Cognome, AnnoNascita, Sesso non NULL (e Leader, AnnoFormazione NULL); (b) il leader è un Artista con Tipo = cantante.

4. Schema relazionale

Lo schema del corso:

Utente(*username, password, anno_nascita?)
Genere(*nome, descrizione)
Artista(*codice, tipo, nome, anno_formazione?, leader?, cognome?, anno_nascita?, sesso?)
        FK leader -> Artista(codice)
Brano(*codice, titolo, durata, artista)        FK artista -> Artista(codice)
Preferisce(*utente, *genere)                   FK utente -> Utente(username); FK genere -> Genere(nome)
Appartiene(*genere, *brano)                    FK genere -> Genere(nome); FK brano -> Brano(codice)
Ascolta(*utente, *brano, numero)               FK utente -> Utente(username); FK brano -> Brano(codice)

Traduzione:

• Esegue (R4) Brano (1,1): uno-a-molti obbligatorio → fusa in Brano (FK artista NOT NULL).
• Leader (R5): ricorsiva uno-a-molti con partecipazione opzionale (solo i gruppi) → FK leader in Artista, NULL per i cantanti (sistematici, ma sono il prezzo dell'opzione 2).
• Preferisce, Appartiene, Ascolta: molti-a-molti → relazioni con le due chiavi; Ascolta ha l'attributo numero, e la chiave è la coppia (utente, brano): ogni coppia compare una volta, con il totale degli ascolti.
• Vincolo "ogni genere ha almeno un brano": non esprimibile con chiavi esterne (una tabella N:N non impone il minimo 1 sul lato opposto): vincolo esterno da controllare con trigger o a livello applicativo.

Versione SQL (provata in SQLite con dati di esempio, tra cui un gruppo rock con leader):

CREATE TABLE Utente (username TEXT PRIMARY KEY, password TEXT NOT NULL, anno_nascita INTEGER);
CREATE TABLE Genere (nome TEXT PRIMARY KEY, descrizione TEXT);
CREATE TABLE Artista (codice INTEGER PRIMARY KEY, tipo TEXT NOT NULL CHECK (tipo IN ('gruppo','cantante')),
  nome TEXT NOT NULL, anno_formazione INTEGER, leader INTEGER REFERENCES Artista(codice),
  cognome TEXT, anno_nascita INTEGER, sesso TEXT CHECK (sesso IN ('M','F')),
  CHECK ((tipo = 'gruppo' AND anno_formazione IS NOT NULL AND leader IS NOT NULL
          AND cognome IS NULL AND anno_nascita IS NULL AND sesso IS NULL)
      OR (tipo = 'cantante' AND leader IS NULL AND anno_formazione IS NULL
          AND cognome IS NOT NULL AND anno_nascita IS NOT NULL AND sesso IS NOT NULL)));
CREATE TABLE Brano (codice INTEGER PRIMARY KEY, titolo TEXT NOT NULL, durata INTEGER NOT NULL,
  artista INTEGER NOT NULL REFERENCES Artista(codice));
CREATE TABLE Preferisce (utente TEXT REFERENCES Utente(username), genere TEXT REFERENCES Genere(nome),
  PRIMARY KEY (utente, genere));
CREATE TABLE Appartiene (genere TEXT REFERENCES Genere(nome), brano INTEGER REFERENCES Brano(codice),
  PRIMARY KEY (genere, brano));
CREATE TABLE Ascolta (utente TEXT REFERENCES Utente(username), brano INTEGER REFERENCES Brano(codice),
  numero INTEGER NOT NULL CHECK (numero >= 1), PRIMARY KEY (utente, brano));

Il vincolo "il leader è un cantante" non è dichiarabile con una chiave esterna (che punta a qualsiasi artista): serve un trigger che controlli tipo dell'artista referenziato. Una violazione si cerca con:

SELECT g.codice FROM Artista g JOIN Artista l ON g.leader = l.codice WHERE l.tipo <> 'cantante';

Verificata con dati di esempio: restituisce zero righe.

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

La query "numero totale di ascolti per brano" richiede un raggruppamento su Ascolta. Un attributo derivato TotaleAscolti in Brano la renderebbe una lettura diretta, ma introduce ridondanza: va aggiornato a ogni modifica di Ascolta e serve un vincolo esterno di coerenza. Si decide con la tabella degli accessi (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 →): conviene se la query è frequente e le riproduzioni (scritture) sono rare rispetto alle letture; le riproduzioni però sono molte (un'operazione online frequente), quindi qui il vantaggio è dubbio. Un'alternativa che non tocca lo schema concettuale è un indice sulle chiavi esterne di Ascolta (scelta fisica, Basi di dati e DBMS - architettura ANSI-SPARC e indipendenza dei datiUna base di dati (database, DB) è una collezione integrata e persistente di dati correlati che rappresenta un frammento di mondo reale (mini-mondo); un DBMS è il software generico che permette di definirla (schema, nel catalogo come metadati), costruirla e manipolarla (interrogazioni e aggiornamenti). Rispetto a una raccolta di file il DBMS riduce ridondanza e incoerenza, condivide i dati tra applicazioni con controllo della concorrenza (isolamento, atomicità), garantisce durabilità, sicurezza (autenticazione e autorizzazione) ed efficienza. L'architettura ANSI/SPARC ha tre livelli: schema esterno (una vista per applicazione), logico (rappresentazione integrata, indipendente dalla memorizzazione), interno (strutture fisiche); da qui l'indipendenza fisica (cambia l'interno senza toccare logico ed esterno) e logica (cambia il logico senza toccare le applicazioni). Attori: sviluppatori del DBMS, DBA, progettisti di basi di dati e di applicazioni, utenti parametrici e occasionali.Basi di dati e DBMS - architettura ANSI-SPARC e indipendenza dei dati →).

6. Osservazioni sul materiale del corso

• Nel testo dell'ER il lato Utente di R3 è scritto (1,N) nel disegno a mano: ogni utente avrebbe ascoltato almeno un brano. Il testo non lo richiede ("per le canzoni che l'utente ha ascoltato almeno una volta"), e un utente appena iscritto non ha ascolti: la scelta corretta è (0,N), usata qui.
• Il disegno dà il nome anche a Cantante, ma il nome è già un attributo ereditato da Artista: ripeterlo viola il principio di ereditarietà (nella soluzione relazionale c'è una sola colonna nome, con in più il cognome).
• R5 è disegnata con (1,1) lato Gruppo e (0,N) lato Cantante: coerente con il testo (leader sempre definito, un cantante può guidare più gruppi). Nello schema relazionale del corso la colonna leader sta in Artista, con i valori NULL per i cantanti.

Domande d'esame

Perché il numero di ascolti è un attributo della relazione e non un'entità? Traccia. Il testo chiede solo il totale per coppia (utente, brano): una coppia ha un valore, compatibile con la semantica degli insiemi. Se servissero data e ora di ogni ascolto sarebbe necessaria un'entità Ascolto (eventi ripetuti).

Come si traduce la relazione Leader dopo aver accorpato Gruppo e Cantante in Artista? Traccia. Ricorsiva uno-a-molti con partecipazione opzionale: FK leader in Artista, NULL per i cantanti; vincolo esterno: il leader è di tipo cantante, i gruppi hanno sempre un leader.