La progettazione e la creazione di un database relazionale è una fase cruciale all’interno del ciclo di vita di un processo di data management. In questo articolo descriveremo i vari stadi della progettazione di un database partendo da un project work svolto con dei miei studenti durante l’ultima giornata del corso sui database relazionali.
Il project work si è concentrato su tutti i passi principali di un progetto di analisi dati:
- progettazione concettuale del database;
- progettazione logica e creazione di un database relazione su Microsoft Sql Server 2016 tramite linguaggio SQL;
- scrittura delle procedure di popolamento del database;
- implementazione in SQL di query di estrazione e analisi dei dati.
Dato il background di uno dei partecipanti, abbiamo deciso di svolgere la lezione prendendo come contesto sottostante la gestione di un’azienda ospedaliera. In questo articolo ci concentreremo sulle sezioni relative alla progettazione e al popolamento del database.
Progettazione concettuale del database
Inizialmente abbiamo progettato concettualmente il database seguendo due diversi paradigmi:
– il classico diagramma E-R entità relazioni;
– lo star schema orientato alla progettazione data-warehouse, individuando dimensioni, fatti e fotografie.
Descriviamo brevemente la differenza tra i due approcci: il paradigma E-R parte dall’individuazione delle entità che descrivono il business oggetto del database, descrivendo le relazioni che intercorrono tra esse e gli attributi che ne identificano le proprietà. Lo star schema invece parte analizzando quali sono i fatti (le transazioni) che accadono nel business, specificando in seguito le dimensioni e le misure che identificano tali accadimenti.
Lo star schema permette di rispondere in maniera flessibile a tutte le tipologie di domande provenienti dal business:
- per query sui dati anagrafici come “quanti pazienti hanno tra i venti e i venticinque anni” verranno utilizzate le dimensioni;
- per domande sul numero di interventi con particolari proprietà si useranno le tabelle transazionali, eventualmente in Join con le dimensioni per aggiungere ulteriori filtri;
- le fotografie (snapshot table) permetteranno di rispondere velocemente a domande sulla situazione specifica dell’ospedale in una determinata data, senza dover ricostruire ogni volta il risultato dall’elenco delle transazioni.
Progettazione logica e creazione del database con l’SQL
Nella progettazione logica successiva, abbiamo creato gli script in T-SQL per generare la struttura del database relazionale su Microsoft SQL Server. In questo articolo trovi un approfondimento sull’istruzione CREATE TABLE di SQL Server.
Particolare attenzione è stata posta nella definizione di:
- tipi delle colonne;
- vincoli null/not null;
- definizione di chiavi primarie;
- definizione di chiavi esterne;
- definizione di vincoli di tipo check.
Per l’ultimo punto, abbiamo creato un check per mettere in relazione il numero di infermieri alla capienza massimo del relativo reparto.
Le tabelle contenenti le dimensioni sono state create utilizzando le temporal table di Sql Server per gestire automaticamente il salvataggio e il log delle modifiche (in questa pagina trovi un approfondimento https://docs.microsoft.com/it-it/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15). Tramite le Temporal Table sarà possibile rispondere facilmente e con delle performance elevate a domande solitamente complesse come “qual era la capienza di uno specifico reparto” in una data passata. Possiamo in questo caso usare la sintassi AS OF specifica per questa tipologia di tabelle:
SELECT *
FROM DimInfermieri
FOR SYSTEM TIME AS OF '20181231';Popolare il database
I database sono stati parzialmente popolati utilizzando strumenti differenti:
- l’interfaccia grafica di Sql Server Management Studio;
- la sintassi SQL (INSERT e BULK INSERT);
- le procedure di importazione guidate dei dati da file di testo o Excel.
Abbiamo introdotto le principali funzionalità di Sql Server Integration Services per gestire le operazioni di ETL e studiato come importare i dati all’interno del database utilizzando il potente linguaggio procedurale Python e la libreria Pyodbc.
Continua a imparare
- la normalizzazione dei database è un processo correlato alla progettazione e di fondamentale importanza per migliorare le performance del database. Ne parlo nel dettaglio in questo articolo
- in questo articolo approfondisco l’operazione di creazione delle tabelle
- puoi studiare come creare relazioni tra tabelle con le foreign key