Progettazione concettuale corso introduzione ai database

Progettare e realizzare un database relazionale

Nell’ultima giornata della seconda edizione del corso sui database relazionali abbiamo svolto un project work che seguiva l’intera filiera di un progetto di analisi dati:

  • analisi dei requisiti;
  • progettazione concettuale del database;
  • progettazione logica e creazione di un database relazione su Microsoft Sql Server 2016 con l’SQL;
  • analisi della reportistica;
  • 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 caso di business sottostate la gestione di un’azienda ospedaliera.

 

Progettazione concettuale del database

Inizialmente abbiamo progettato concettualmente il database seguendo diversi paradigmi:
– il classico diagramma E-R entità relazioni;
– uno star schema orientato alla progettazione data-warehouse, individuando dimensioni, fatti e fotografie.

Progettazione concettuale corso introduzione ai database

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 doverle ricostruire ogni volta dall’elenco delle transazioni.

 

Progettazione logica e traduzione in SQL

Nella progettazione logica successiva, abbiamo creato gli script in T-SQL per generare la struttura del database relazionale in ambiente SQL Server 2016.

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 ad esempio un check per relazionare in percentuale 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 questo modo 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 le temporal table.

SELECT * 

FROM DimInferieri

FOR SYSTEM TIME AS OF ‘2018-12-31’

 

Popolare il database

I database sono stati parzialmente popolati utilizzando strumenti differenti: l’interfaccia grafica di Sql Server Management Studio, la sintassi SQL e le procedure di importazioni guidate dei dati da file di testo o excel. Infine sono state introdotte le principali funzionalità di Sql Server Integration Services per gestire le operazioni di ETL in modo professionale e manutenibile.

Per concludere abbiamo mimato un’analisi funzionale con il committente per decidere su quali basi impostare i report di analisi e ulteriori controlli di data quality. Tali richieste sono state implementate in SQL utilizzando tutti i costrutti principali visti durante il corso.

 

Preparazione a colloqui di lavoro sull’SQL

Se devi prepararti ad un colloquio di lavoro sul tema SQL, leggi la storia di successo di Marco e le domande più frequenti durante un colloquio.

Torna su