Fondamenti delle basi di dati

L'architettura a tre livelli ANSI/SPARC

Il modello a tre livelli è il vocabolario di riferimento dell'architettura delle basi di dati. Questa lezione lo presenta come lo definisce lo standard, e poi esamina con onestà dove i sistemi reali se ne allontanano.

Base

Obiettivi di apprendimento

  • Nominare i tre schemi del modello ANSI/SPARC e indicare che cosa descrive ciascuno
  • Spiegare le due corrispondenze e quale indipendenza fornisce ciascuna
  • Identificare quali decisioni di progetto appartengono a quale schema
  • Spiegare le principali critiche al modello e come i DBMS reali se ne allontanano
  • Usare correttamente il vocabolario del modello quando si discute l'architettura di una base di dati

Prerequisiti

  • I DBMS: definizione e funzionalità
  • Indipendenza logica e fisica dei dati

Introduzione

Nel 1975 un gruppo di studio dell'American National Standards Institute, operante nell'ambito del suo Standards Planning and Requirements Committee (da cui ANSI/X3/SPARC), pubblicò un rapporto intermedio che proponeva un'architettura standard per i sistemi di gestione di basi di dati. La proposta aveva un solo scopo: rendere le basi di dati portabili e interoperabili separando ciò che gli utenti vedono da come i dati sono memorizzati.

Cinquant'anni dopo lo standard non è mai stato implementato integralmente, e nessun DBMS vi si conforma esattamente. È comunque il vocabolario che ogni manuale, ogni documentazione di prodotto e ogni colloquio di lavoro usano. Vale la pena capirlo con precisione: il modello è un'architettura di riferimento, non la descrizione di un prodotto.

I tre schemi

Lo standard li chiama schemi (non "livelli") e ne definisce esattamente tre.

1. Lo schema esterno (o vista esterna)

Schema esterno

Uno schema esterno descrive la parte della base di dati di cui ha bisogno un particolare gruppo di utenti, nella forma in cui quel gruppo la richiede. Possono esistere molti schemi esterni; ciascuno è derivato dallo schema concettuale.

Punti chiave:

  • È per gruppo di utenti, non per utente. Tutti gli operatori al banco condividono uno schema esterno; tutti i direttori ne condividono un altro.
  • Può ristrutturare: rinominare attributi, ometterne alcuni, combinare più tabelle in una, calcolare valori derivati, restringere le righe.
  • Non descrive la memorizzazione, e non deve descrivere l'intera base di dati.
  • È il livello in cui si esprime la sicurezza: ciò che un gruppo può vedere è esattamente ciò che contiene il suo schema esterno.

In SQL, uno schema esterno è realizzato dalle viste (e, in alcuni prodotti, da privilegi a livello di colonna).

2. Lo schema concettuale

Schema concettuale

Lo schema concettuale descrive l'intera base di dati: tutte le entità, tutti gli attributi, tutte le relazioni, tutti i vincoli, tutte le regole di integrità — senza alcun riferimento a come qualcosa è memorizzato e senza alcun riferimento a chi può vedere che cosa.

Punti chiave:

  • Esiste esattamente uno schema concettuale per base di dati. È il cuore del modello: una descrizione unica, completa e indipendente dall'implementazione.
  • Contiene informazione semantica: non solo "esiste una tabella con una colonna chiamata socio", ma "un prestito deve riferirsi a un socio esistente, e un socio può avere al massimo cinque prestiti aperti".
  • È dove vivono le decisioni di progetto: quali cose sono entità, quali sono attributi, quali sono relazioni, quali sono le chiavi.
  • È il livello che descrive un diagramma E-R concettuale — ed è per questo che il modello E-R e ANSI/SPARC si studiano insieme.

3. Lo schema interno

Schema interno

Lo schema interno descrive come i dati sono memorizzati: organizzazione dei file, collocazione dei record, strutture di indice, compressione, cifratura, dimensione delle pagine, strategia del buffer.

Punti chiave:

  • Esiste esattamente uno schema interno per base di dati (per copia fisica).
  • È dove vivono le decisioni di prestazione: quale indice esiste, se una tabella è clusterizzata, se è partizionata.
  • Non contiene semantica: nulla in esso dice che cosa significano i dati.

Le due corrispondenze

   ┌─────────────┐  ┌─────────────┐  ┌─────────────┐
   │  esterno 1  │  │  esterno 2  │  │  esterno 3  │   ← per gruppo di utenti
   └──────┬──────┘  └──────┬──────┘  └──────┬──────┘
          │                │                │
          └──── corrispondenza esterno/concettuale ─┘
                           │
                  ┌────────▼────────┐
                  │  concettuale    │                   ← esattamente uno
                  └────────┬────────┘
                           │
                  ┌────────▼────────┐
                  │    interno      │                   ← esattamente uno
                  └────────┬────────┘
                           │
                    base di dati memorizzata
Corrispondenza Collega Fornisce Chi la mantiene
Esterno/concettuale Ogni schema esterno allo schema concettuale Indipendenza logica dei dati Le viste (dichiarate in DDL)
Concettuale/interno Lo schema concettuale allo schema interno Indipendenza fisica dei dati Il motore di memorizzazione + le definizioni degli indici

Le corrispondenze sono il punto centrale. Senza di esse, tre descrizioni sarebbero tre documenti scollegati. Con esse, una modifica in una descrizione può essere assorbita aggiustando la corrispondenza — che è esattamente ciò che significa indipendenza dei dati.

Quale decisione appartiene a quale schema

Questo è il valore pratico del modello. Data una decisione, il modello dice chi la possiede e quanto costa cambiarla.

Decisione Schema Chi decide Costo del cambiamento
"Un prestito deve riferirsi a un socio esistente" Concettuale Progettista Migrazione + eventuale pulizia dei dati
"I soci sono identificati da un codice di 6 caratteri" Concettuale Progettista Migrazione, coinvolge ogni riferimento
"L'operatore al banco non può vedere il codice fiscale" Esterno Progettista + responsabile sicurezza Ridefinire una vista e un grant
"I direttori vedono aggregati mensili, non i singoli prestiti" Esterno Progettista Ridefinire una vista
"I prestiti sono indicizzati su dataInizio" Interno Progettista / DBA CREATE INDEX — minuti
"La tabella prestito è partizionata per anno" Interno DBA Ricostruzione della tabella — ore, ma nessuna modifica applicativa
"Il motore è InnoDB" Interno DBA Cambio di memorizzazione, nessuna modifica applicativa

Nota l'asimmetria che il modello rende visibile: le decisioni a livello concettuale sono costose da cambiare; quelle a livello interno sono economiche. Non è un caso dell'implementazione — deriva dal fatto che lo schema concettuale è il contratto condiviso, mentre lo schema interno è un dettaglio implementativo privato del DBMS.

Dove i sistemi reali si allontanano dal modello

Lo standard è stato criticato fin dagli anni Ottanta, e le critiche sono istruttive più che demolitrici.

1. Lo schema concettuale raramente esiste come artefatto

Il modello presuppone un unico schema concettuale completo e formalmente mantenuto. In pratica la maggior parte dei sistemi ha un insieme di tabelle generate da un framework, documentate (quando lo sono) da un diagramma E-R non aggiornato. Lo schema concettuale esiste implicitamente nelle tabelle, ma nessuno lo mantiene come descrizione.

La conseguenza è reale: senza uno schema concettuale esplicito non c'è un posto dove registrare i vincoli che le tabelle non sanno esprimere, e non c'è modo di accorgersi che due parti del sistema modellano lo stesso concetto in modo diverso.

È esattamente la lacuna che l'editor di ERMap esiste per colmare: il diagramma E-R è lo schema concettuale, e tenerlo come artefatto di prima classe è una disciplina di progetto.

2. La maggior parte dei DBMS realizza due livelli, non tre

In MySQL, PostgreSQL e SQL Server:

  • Lo schema interno è reale ma in gran parte opaco: lo influenzi con indici, scelta del motore e partizionamento, ma non lo scrivi.
  • Lo schema concettuale è realizzato da tabelle, chiavi e vincoli — uno schema logico nel vocabolario del modello, perché riflette già il modello relazionale.
  • Lo schema esterno è realizzato dalle viste.

In pratica si ottiene: viste → tabelle → memorizzazione. Tre strati, ma lo strato intermedio è uno schema relazionale, non uno puramente concettuale. Il livello puramente concettuale (entità, relazioni, generalizzazioni) vive fuori dal DBMS — nel diagramma E-R, nella documentazione, nella testa del progettista.

3. La corrispondenza non è un artefatto separato

Il modello immagina dichiarazioni esplicite di corrispondenza. I DBMS reali derivano le corrispondenze dalle definizioni delle viste e degli indici; non c'è nulla in più da mantenere. È un miglioramento rispetto allo standard, non una violazione.

4. Alcuni sistemi non hanno livello esterno

Un'applicazione che interroga direttamente le tabelle di base, senza viste, ha collassato il livello esterno in quello concettuale. È legale ed estremamente comune, e rinuncia all'indipendenza logica dei dati — il modello spiega esattamente perché.

5. I sistemi NoSQL collassano i livelli deliberatamente

I document store e i key-value store non hanno in genere alcuna separazione concettuale/interna: lo "schema" è la disposizione di memorizzazione. È uno scambio consapevole: prestazioni e flessibilità in cambio delle garanzie che il modello fornisce. La prossima lezione esamina quello scambio.

Leggere un sistema reale attraverso il modello

Una biblioteca su MySQL. Quale artefatto realizza quale schema?

Elemento del modello Artefatto reale
Schema esterno per gli operatori al banco VIEW v_prestito_banco + GRANT SELECT ON v_prestito_banco TO 'desk'
Schema esterno per i direttori VIEW v_statistiche_mensili
Schema esterno per i soci VIEW v_miei_prestiti (filtrata sull'utente di sessione)
Schema concettuale CREATE TABLE socio/prestito/... con PRIMARY KEY, FOREIGN KEY, CHECK; documentato dal diagramma E-R
Schema interno ENGINE=InnoDB, CREATE INDEX idx_prestito_dataInizio, partizionamento per anno
Corrispondenza esterno/concettuale Il SELECT dentro ogni CREATE VIEW
Corrispondenza concettuale/interno La traduzione dell'ottimizzatore dell'accesso alle tabelle in accesso a indici e pagine

Nota che lo schema concettuale è diviso fra la base di dati (tabelle e vincoli) e la documentazione (il diagramma E-R con entità, relazioni, generalizzazioni). Nessuna delle due metà basta da sola: le tabelle non possono esprimere "un socio può avere al massimo cinque prestiti aperti" come vincolo dichiarativo portabile, e il diagramma non è eseguibile.

Intermediate

Assegna la decisione allo schema

Per ciascuna decisione indica quale schema ANSI/SPARC la possiede, chi dovrebbe decidere e quanto costerebbe cambiarla in seguito.

  1. Il tipo di abbonamento di un socio è Base, Plus o Premium.
  2. L'applicazione al banco mostra nome e prestiti correnti del socio in un'unica schermata.
  3. prestito ha 4 milioni di righe e le interrogazioni per data sono lente.
  4. Il codice fiscale non deve mai essere visibile agli operatori al banco.
  5. La scadenza di un prestito è 30 giorni dopo la data di inizio.
  6. La base di dati usa la codifica utf8mb4.
  7. Il report mensile mostra una riga per dipartimento con il numero di prestiti.
  8. Un libro può avere più autori, in un ordine definito.
  9. I prestiti vecchi vengono archiviati in una tabella separata dopo due anni.
  10. Due soci non possono condividere lo stesso indirizzo e-mail.
Mostra la soluzione commentata

1. Concettuale. L'insieme dei valori ammessi è un vincolo di dominio su un attributo — un'affermazione sul mondo reale. Decide il progettista. Cambiarlo (aggiungere Gold) è una migrazione piccola se la colonna è un ENUM, ed è una migrazione di dati se è una tabella di lookup — che è di per sé una decisione concettuale (il tipo di abbonamento è un attributo o un'entità?).

2. Esterno. Descrive che cosa vede un gruppo di utenti e in quale disposizione. Decide il progettista, eventualmente con il contributo UX. Cambiarlo costa una ridefinizione di vista e un rilascio del front-end.

3. Interno. È una questione di memorizzazione e prestazioni. Decide il progettista o il DBA, guidati da EXPLAIN. Cambiarla costa un CREATE INDEX — minuti, senza modifiche applicative. Nota che il sintomo ("le interrogazioni sono lente") non è di per sé una decisione di schema; la soluzione è interna.

4. Esterno (con un grant). Progettista più responsabile della sicurezza. Cambiarlo costa una ridefinizione di vista e un cambio di privilegi. Nota che la regola ("il codice fiscale è sensibile") è un requisito concettuale/di sicurezza, ma il meccanismo che la applica è esterno.

5. Concettuale. È una regola aziendale sui dati. Decide il progettista. Nota che esprimerla non è banale: potrebbe essere un CHECK se può riferirsi solo alla riga corrente (MySQL non può riferirsi ad altre colonne in un CHECK prima della 8.0.16, e non può riferirsi ad altre righe in nessun caso), un trigger, o una regola applicativa. La regola concettuale è la stessa; cambia solo il meccanismo di applicazione — e quel meccanismo è una decisione logica/interna.

6. Interno. La codifica dei caratteri è una decisione di memorizzazione. Decide il DBA. Cambiarla su una base di dati esistente è costoso (una ricostruzione completa), ed è per questo che di solito si sceglie correttamente una volta all'inizio.

7. Esterno. Una vista aggregata per un gruppo di utenti specifico. Decide il progettista. Cambiarla costa una ridefinizione di vista.

8. Concettuale. "Più autori in un ordine definito" è una relazione con un attributo (la posizione). È una delle decisioni concettuali più importanti dell'intero modello, e sbagliarla (per esempio memorizzando autore1, autore2) è un errore classico. Il Modulo 3 la tratta in profondità.

9. Interno — ma con conseguenze concettuali. L'archiviazione è una strategia fisica/di memorizzazione. Tuttavia, se archiviare cambia ciò che le applicazioni vedono (i prestiti vecchi spariscono dai report), non è più puramente interna: gli schemi esterni vanno definiti in modo che il cambiamento sia invisibile, oppure il cambiamento di significato va accettato esplicitamente. È un buon esempio di decisione che sembra interna e non lo è del tutto.

10. Concettuale. Un vincolo di unicità su un attributo. Decide il progettista. Cambiarlo (permettere caselle condivise) è una migrazione: eliminare il vincolo. Aggiungerlo a una base di dati che contiene già duplicati richiede prima di riconciliare i dati — un compito di qualità dei dati, non di schema.

Che cosa insegna questo esercizio. Il valore del modello non è la classificazione fine a se stessa. È sapere chi possiede la decisione e quanto costa cambiarla. Le decisioni concettuali sono costose, quelle interne sono economiche, quelle esterne sono il cuscinetto che rende le altre due sopravvivibili.

Errori comuni

  • "Lo schema concettuale è il diagramma E-R." Il diagramma E-R è una notazione per lo schema concettuale. In pratica è spesso l'unico artefatto che lo registra — ed è per questo che tenerlo aggiornato conta.
  • "I tre livelli sono tre copie dei dati." Solo lo schema interno contiene dati. Gli altri sono descrizioni nel catalogo.
  • "ANSI/SPARC è obsoleto." Il vocabolario è universale. Ogni manuale descrive le viste come schemi esterni e gli indici come schema interno, qualunque nome usi.
  • "La mia applicazione non ha schema esterno, quindi il modello non si applica." Ne ha uno: è solo collassato nello schema concettuale, e l'indipendenza logica è stata ceduta. Il modello diagnostica la situazione con precisione.
  • "I sistemi NoSQL non seguono il modello, quindi il modello è sbagliato." I sistemi NoSQL fanno uno scambio deliberato contro le garanzie del modello. Capire il modello è ciò che permette di valutare lo scambio.
  • Il modello ANSI/SPARC definisce tre schemi: esterno (per gruppo di utenti), concettuale (esattamente uno, completo, indipendente dall'implementazione) e interno (esattamente uno, di memorizzazione).
  • Due corrispondenze li collegano: esterno/concettuale (fornisce l'indipendenza logica, realizzata dalle viste) e concettuale/interno (fornisce l'indipendenza fisica, realizzata dal motore di memorizzazione).
  • Il valore pratico del modello è assegnare la proprietà: le decisioni concettuali sono costose da cambiare, quelle interne sono economiche, quelle esterne sono il cuscinetto.
  • I sistemi reali implementano il modello parzialmente: lo schema interno è opaco, lo schema concettuale è diviso fra tabelle e documentazione, e molte applicazioni non hanno alcun livello esterno.
  • La deviazione più comune è lo schema concettuale assente come artefatto esplicito — la lacuna che un diagramma E-R mantenuto colma.

Domande di verifica

  1. Perché esiste esattamente uno schema concettuale ma molti schemi esterni?
  2. Quale corrispondenza fornisce l'indipendenza logica dei dati, e quale artefatto la realizza in SQL?
  3. Un collega dice "non ci servono le viste, ci pensa il nostro ORM a fare il mapping". Quale livello del modello è stato rimosso, e che cosa si è perso?
  4. Spiega perché un indice è un oggetto dello schema interno e una chiave esterna è un oggetto dello schema concettuale.
  5. Perché una modifica allo schema concettuale è più costosa di una modifica allo schema interno?
Mostra le risposte
  1. Perché lo schema concettuale descrive l'intera base di dati una volta sola, indipendentemente da chi la usa — è il contratto condiviso unico. Gli schemi esterni sono per gruppo di utenti perché gruppi diversi hanno bisogno di sottoinsiemi, disposizioni e restrizioni diversi della stessa base di dati.

  2. La corrispondenza esterno/concettuale, realizzata dalle viste (CREATE VIEW, e i GRANT che limitano l'accesso alle viste).

  3. È stato rimosso lo schema esterno: l'ORM mappa gli oggetti direttamente sulle tabelle di base. Si perde l'indipendenza logica dei dati — ogni modifica di schema che altera la forma di una tabella diventa una modifica applicativa, perché non c'è uno strato di viste stabile nel mezzo. È una scelta legittima per un sistema piccolo, ed è un costo reale che va sostenuto consapevolmente.

  4. Un indice non dice nulla sul significato: dice "accedi rapidamente a queste righe per questa colonna", che è una decisione di memorizzazione senza contenuto semantico. Una chiave esterna dice "questo valore deve esistere in un'altra tabella", che è un'affermazione di fatto sul mondo — una regola che deve valere indipendentemente da come i dati sono memorizzati. Il modello li classifica in base a che tipo di affermazione fanno.

  5. Perché lo schema concettuale è il contratto condiviso: applicazioni, viste, report, integrazioni e altri schemi dipendono da esso, quindi una modifica si propaga. Lo schema interno è un dettaglio implementativo privato del DBMS: nulla all'esterno dipende da esso, quindi può essere cambiato in isolamento. Questa asimmetria non è un caso tecnico; è la ragione per cui l'architettura a tre livelli esiste.

Dove andare adesso

Lezioni correlate