Come tradurre un diagramma Entità Relazioni in un database

Le informazioni che un Database deve contenere possono essere codificate tramite una serie di regole e rappresentazioni note come Diagramma Entità-Relazione (o Diagramma E-R). Questo diagramma è pensato per una rappresentazione concettuale dei dati contenuti nel Database, senza fare riferimento alla particolare tipologia di organizzazione dei dati.

È importante essere consapevoli che teoricamente il Diagramma E-R esiste a prescindere dalla scelta tra un Database relazionale e uno non relazionale. In questo articolo, comunque, vedremo come tradurre il diagramma in un Database relazionale con SQL Server. Tali regole di traduzione sono sostanzialmente le stesse per tutti gli RDBMS, anche se la sintassi di creazione delle tabelle potrebbe ovviamente subire qualche modifica, ad esempio tra MySQL e SQL Server.

Diagramma E-R

In questo link del mio repository Github trovi un esempio di Diagramma Entità-Relazione per descrivere le informazioni che devono essere contenute all’interno di un Database che modellizzi un sito web di tipo blog.

Le figure utilizzate nell’immagine non sono scelte a caso:

  • i rettangoli contengono le entità del database: Articolo, Autore e Commento;
  • i rombi contengono le relazioni tra le entità: ad esempio tra le entità Articolo e Autore esiste la relazione Pubblicazione;
  • le elissi contengono gli attributi semplici relativi ad ogni entità o (più raramente) alle relazioni: ad esempio l’entità Articolo ha gli attributi Data pubblicazione, Titolo e Testo. Un’ellisse doppia indica un attributo multi-valore, che può assumere cioè più di un singolo valore (ad esempio un Autore può avere più Email).

Notiamo inoltre che su ogni freccia tra le entità e le relazioni è presente una coppia di valori (le cardinalità): il primo può essere 1 (uno) oppure 0 (zero), mentre il secondo può essere 1 o N. Esploriamone il significato tramite degli esempi:

  • il primo 1 nella freccia che collegata l’Articolo alla Pubblicazione indica che ogni articolo deve obbligatoriamente avere un autore;
  • il primo 0 (zero) nella freccia che collega l’Autore alla Pubblicazione indica che può esistere un autore che non abbia un articolo associato;
  • il secondo N nella freccia che collega l’Articolo alla  Pubblicazione indica che un articolo può essere associato a più autori;
  • il secondo 1 nella freccia che collega il Commento all’Associazione indica che un commento può essere associato al massimo a un articolo.

Traduzione delle entità

Vediamo come tradurre il diagramma E-R precedente in un database su SQL Server. Per prima cosa creiamo una tabella per ogni entità, le cui colonne sono date dagli attributi semplici:

CREATE TABLE Articoli(
 Titolo VARCHAR(100) NOT NULL,
 Testo VARCHAR(MAX) NOT NULL,
 DataPubblicazione DATE NOT NULL);
CREATE TABLE Autori(
 Nome VARCHAR(100) NOT NULL,
 Cognome VARCHAR(100) NOT NULL);
CREATE TABLE Commenti(
 Testo VARCHAR(1000) NOT NULL);

È prassi utilizzare il plurale per i nomi delle tabelle e il singolare per il nome delle colonne.

Assicuriamoci che ogni tabella abbia una colonna o una combinazione di colonne che identifichino univocamente, senza duplicati, ogni riga della tabella. Su di esse inseriremo il vincolo di chiave primaria.

Spesso non è semplice trovare colonne del genere, ad esempio  per la tabella Articoli, anche se raramente, potrebbero esserci due righe con lo stesso titolo: pensiamo ad esempio ad un blog sportivo e ad articoli dal titolo “Italia campione!”. Una scelta più sicura potrebbe essere la combinazione di Titolo e Testo, ma vi anticipo che ciò si rivelerebbe pessimo dal punto di vista delle performance e della memoria occupata (presto capiremo meglio perché).

In questi casi si preferisce introdurre una colonna artificiale, contenente spesso un numero progressivo, che abbia proprio il ruolo di identificare ogni riga della tabella. Poiché questo discorso vale per tutte le entità, aggiungiamo alle tre relative tabelle delle colonne con codici artificiali e con il vincolo di chiave primaria.

ALTER TABLE Articoli
ADD IdArticolo INT NOT NULL PRIMARY KEY;
ALTER TABLE Autori
ADD IdAutore INT NOT NULL PRIMARY KEY;
ALTER TABLE Commenti
ADD IdCommento INT NOT NULLPRIMARY KEY;

Per ogni attributo multi-valore creiamo un’ulteriore tabella contenente in ogni riga la chiave primaria dell’entità di partenza e un valore dell’attributo. Nel nostro caso dobbiamo svolgere questa attività per l’attributo Email dell’entità Autore.

CREATE TABLE AutoriEmail(
 IdAutore INT NOT NULL,
 Email VARCHAR(100) NOT NULL,
 PRIMARY KEY (IdAutore,Email),
 FOREIGN KEY (IdAutore)
 REFERENCES dbo.Autori(IdAutore) );

Come mostrato dal codice in alto, la chiave primaria di questa tabella sarà data dalla combinazione delle due colonne presenti. Inoltre, inserirò un vincolo di chiave esterna relativo alla chiave primaria della tabella “principale” che rappresenta l’entità.

Traduzione delle relazioni

In base al secondo valore di ogni coppia presente sulle frecce del diagramma E-R, possiamo individuare tre tipi di relazione:

  • uno a uno se per entrambe le entità il valore è 1;
  • uno a molti se per un’entità il valore è 1 mentre per l’altra è N;
  • molti a molti se per entrambe le entità il valore è N.

Nel nostro caso Articoli-Autori sarà una relazione molti a molti, mentre Commenti-Articoli sarà una relazione molti a uno. Procederemo in questo modo:

  • creeremo una nuova tabella per la relazione molti a molti;
  • aggiungeremo una colonna nella tabella corretta per gestire la relazione molti a uno. Nel caso che stiamo seguendo occorrerà aggiungere alla tabella Commenti (in cui è presente un 1 come secondo numero) la chiave primaria della tabella Articoli (in cui è presente un N come secondo numero).

Scriveremo allora:

CREATE TABLE dbo.ArticoliAutori(
 IdArticolo INT NOT NULL,
 IdAutore INT NOT NULL,
 PRIMARY KEY (IdArticolo,IdAutore),
 FOREIGN KEY (IdArticolo)
   REFERENCES dbo.Articoli(IdArticolo),
 FOREIGN KEY (IdAutore)
   REFERENCES dbo.Autori(IdAutore));
ALTER TABLE dbo.Commenti
ADD IdArticolo INT NOT NULL;
ALTER TABLE dbo.Commenti
ADD FOREIGN KEY (IdArticolo)
  REFERENCES dbo.Articoli(IdArticolo);

Osserviamo che in queste operazioni abbiamo spesso ripetuto le chiavi primarie in altre tabelle (ad esempio per le relazioni e gli attributi multi-valore). Ciò ci mostra quanto sia importante per le performance evitare la scelta di combinazioni di colonne che occupino troppa memoria.

Ulteriori dettagli sul diagramma E-R

Non è scopo di questo articolo darà una spiegazione onnicomprensiva del diagramma E-R e della traduzione nel modello relazionale, per la quale esistono dei libri specifici. Tuttavia spero di avervi dato l’idea corretta di cosa c’è dietro la costruzione dei Database che normalmente interroghiamo.

Ad esempio quando scriviamo la JOIN tra due tabelle stiamo utilizzando molto spesso la presenza delle chiavi primarie e delle chiavi esterne.

Quando scriviamo una GROUP BY stiamo raggruppando i dati su un attributo semplice qualitativo (come la regione, la data, la categoria, ecc.) e applicando le funzioni di aggregazione su un altro attributo semplice quantitativo (l’importo, le quantità, il saldo, ecc.)

Ecco alcuni cenni su altri temi del diagramma E-R:

  • oltre agli attributi semplici e multi-valore, possono esistere attributi composti. Ad esempio l’attributo indirizzo è composto dai sotto-attributi via, numero civico, città e CAP. In questo caso è buona norma creare una colonna per ogni sotto-attributo semplice;
  • possono esistere i cosiddetti attributi calcolati, il cui valore dipende da un altro attributo. Ad esempio l’attributo età dipende dall’attributo data nascita. In questo caso la situazione è da valutare caso per caso, spesso si tende a non creare una colonna standard per questi attributi in quanto esiste già una formula per calcolarli;
  • le entità possono essere specializzate in più sotto-entità. Ad esempio l’entità Lavoratore può essere specializzata in Dipendente e Libero Professionista. In questo caso occorrerà valutare caso per caso se accorpare o meno le sotto-entità in un’unica tabella del modello relazionale;
  • il diagramma E-R non è l’unico modo possibile di guidare la progettazione concettuale di un Database, ma è sicuramente tra i più famosi soprattutto per quanto riguarda i Database operativi (OLTP), costruiti cioè a supporto di un altro software o applicazione. Esistono anche altre possibili rappresentazioni come lo star schema che sono più idonei per i Database dedicati all’analisi dei dati (OLAP). 

Continua a imparare

Torna in alto
Torna su