Basi di Dati

UNIVERSITÀ DEL SALENTO

Department of Innovation Engineering

Master’s degree in Computer Engineering

Database

Database

Dott. Marco Chiarelli

Academic Year 2016/2017 1

Quest’opera è stata rilasciata con licenza Creative Commons Attribuzione - Non commerciale

- Condividi allo stesso modo 3.0 Unported. Per leggere una copia della licenza visita il sito web

http://creativecommons.org/licenses/by-nc-sa/3.0/ o spedisci una lettera a Creative Commons,

171 Second Street, Suite 300, San Francisco, California, 94105, USA.

cbea

Questi appunti sono stati scritti utilizzando L TEX tramite la distribuzione MiKTeX

A http:

//miktex.org/

Come editor è stato usato TeXMaker 4.5 http://www.xm1math.net/texmaker/

Dei contenuti rielaborati in questa opera, salvo esplicitamente scritto il contrario, il prof.

Mario Alessandro Bochicchio non se ne assume alcuna responsabilità.

Tali contenuti sono stati scritti INTEGRALMENTE dagli studenti di Ingegneria Informa-

tica at UNISALENTO, II anno 2016/2017. Marco Chiarelli, seppure l’unico redattore di tale

manodopera, NON ne è in alcun modo il suo autore principale.

2

Indice

Introduzione ii

1 Modeling 1

1.1 Progettazione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.1.1 Caso di Studio - Google . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1.1.2 Organizzazioni . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3

1.1.3 Lo scenario della progettazione - Progetto e realizzazione . . . . . . . . . 5

1.1.4 Scenario Ingegneristico della Progettazione . . . . . . . . . . . . . . . . . 6

1.2 Architettura di un Progetto Software . . . . . . . . . . . . . . . . . . . . . . . . . 7

1.2.1 L’hardware . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

1.2.2 Il software - Architettura a tre livelli . . . . . . . . . . . . . . . . . . . . . 9

1.3 Modellazione dei dati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

1.3.1 ESEMPIO – APPLICAZIONE CLIENT-SERVER . . . . . . . . . . . . . 11

1.3.2 ESEMPIO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

1.3.3 ESEMPIO - THE COMPANY DATABASE . . . . . . . . . . . . . . . . . 14

1.4 Conceptual Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

1.4.1 Il valore NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

1.4.2 ANALIST . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

1.4.3 SCHEMA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

1.4.4 Come iniziare la progettazione di un DB . . . . . . . . . . . . . . . . . . . 22

1.4.5 Ternary relation type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

1.5 APPENDICE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

1.5.1 DESIGN PATTERN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24

1.6 Esercizio - Fermate Autobus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

1.6.1 Design Pattern: Preventivo-Consuntivo . . . . . . . . . . . . . . . . . . . 32

1.6.2 Conclusione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33

1.7 DATA CENTRIC APPLICATION . . . . . . . . . . . . . . . . . . . . . . . . . . 34

1.7.1 USER TYPES . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34

1.7.2 ESEMPIO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35

1.7.3 SIMPLIFIED MAPPING ALGORITHM . . . . . . . . . . . . . . . . . . 35

1.7.4 PRESENTATION LAYER FOR CUSTOMER OR DIRECTOR . . . . . 36

1.7.5 BUSINESS RULE LAYER . . . . . . . . . . . . . . . . . . . . . . . . . . 38

1.8 LIST VIEW and DETAIL VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

1.8.1 DATA PATTERN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

1.9 DBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

1.9.1 Descrizione dei principali DBMS . . . . . . . . . . . . . . . . . . . . . . . 54

1.9.2 Installazione e configurazione di MySQL . . . . . . . . . . . . . . . . . . . 55

1.9.3 PRIMO CONTATTO CON MYSQL WORKBENCH . . . . . . . . . . . 63

1.10 IMPORTANZA MODELLI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74

3

1.10.1 IMPORTANZA MODELLO DATI . . . . . . . . . . . . . . . . . . . . . . 74

1.10.2 IMPORTANZA INFORMAZIONE . . . . . . . . . . . . . . . . . . . . . . 75

1.11 DIAGRAMMI EER . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

1.11.1 SCHEMA EER CAPITOLO 4 . . . . . . . . . . . . . . . . . . . . . . . . 79

1.11.2 ESERCIZIO SVOLTO IN CLASSE . . . . . . . . . . . . . . . . . . . . . 79

2 MODELLO LOGICO 81

2.1 SQL QUERY: Le interrogazioni del database . . . . . . . . . . . . . . . . . . . . 81

2.1.1 ALGEBRA RELAZIONALE . . . . . . . . . . . . . . . . . . . . . . . . . 82

2.1.2 Operatore Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84

2.2 Sviluppo di una Web Application . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

2.2.1 XAMPP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86

2.2.2 MySQL Workbench . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92

2.2.3 DataGrip . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95

2.3 Algebra Relazionale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

2.3.1 INTRODUZIONE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

2.3.2 ALTRI OPERATORI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

2.3.3 ESEMPI DI QUERY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

2.4 Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101

2.5 Popolazione casuale DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

2.5.1 Tipi di dato in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

2.5.2 Attributi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

2.5.3 Funzioni di Aggregazione . . . . . . . . . . . . . . . . . . . . . . . . . . . 107

2.5.4 Popolazione del DataBase . . . . . . . . . . . . . . . . . . . . . . . . . . . 108

2.6 MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

2.6.1 Tipi di tabelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113

2.6.2 AUTO-INCREMENT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117

2.6.3 Tipi di dato in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

2.6.4 Funzioni di aggregazione . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123

2.6.5 phpMyAdmin – indici . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127

2.6.6 Popolare database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

2.7 SQL Recap . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129

2.7.1 VINCOLI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

2.7.2 Meccanismo base Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131

2.7.3 ESERCIZIO . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132

2.8 SQL per manipolare i dati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135

2.8.1 Transazioni e concorrenza (accenno) . . . . . . . . . . . . . . . . . . . . . 137

2.8.2 Algebra a tre livelli . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138

2.8.3 Query più complesse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139

2.8.4 Vincoli in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

2.8.5 Asserzioni . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144

2.8.6 Trigger . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

2.8.7 Viste (Views) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145

2.8.8 Esercizio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146

2.9 Continuazione Popolamento DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147

2.9.1 CRUD (Create, Read, Update, Delete) . . . . . . . . . . . . . . . . . . . . 150

2.10 CRUD cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165

2.10.1 PDO (PhpData Objects) . . . . . . . . . . . . . . . . . . . . . . . . . . . 166

2.10.2 Grid per le operazioni CRUD . . . . . . . . . . . . . . . . . . . . . . . . . 167

4

2.11 Enhanced Mapping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178

2.11.1 ALGORITMO DI MAPPING . . . . . . . . . . . . . . . . . . . . . . . . . 179

2.12 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181

2.12.1 Modellazione di dati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183

2.13 Mapping RECAP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185

2.13.1 Svolgimento traccia d’esame . . . . . . . . . . . . . . . . . . . . . . . . . . 188

2.14 Qualità di un DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196

2.14.1 DIPENDENZE FUNZIONALI . . . . . . . . . . . . . . . . . . . . . . . . 199

2.15 Transazioni e Concorrenza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

2.15.1 Transazioni . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

2.15.2 Concurrency Control Techniques . . . . . . . . . . . . . . . . . . . . . . . 207

2.16 Indicizzazione e Indici . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

3 DATA WAREHOUSE 209

3.1 Introduzione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209

3.2 Analisi multidimensionale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210

3.2.1 Operazioni concettuali . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213

3.3 Analisi Multidimensionale e Data Warehousing . . . . . . . . . . . . . . . . . . . 214

3.4 COSTRUTTI AVANZATI DEL DFM . . . . . . . . . . . . . . . . . . . . . . . . 221

3.4.1 ESEMPIO DELLE VENDITE (DA E/R) . . . . . . . . . . . . . . . . . . 224

3.4.2 Passaggio dall’ER riconciliato al DFM: l’ALBERO DEGLI ATTRIBUTI . 224

3.4.3 CARICO DI LAVORO . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225

3.4.4 PROGETTAZIONE LOGICA . . . . . . . . . . . . . . . . . . . . . . . . 226

3.5 LE TABELLE PIVOT DI EXCEL PER IL DATA WAREHOUSE . . . . . . . . 227

3.5.1 QUERY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230

3.6 PROGETTAZIONE LOGICA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235

3.6.1 Indicatori . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235

3.6.2 Motore Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237

3.6.3 SnowFlake Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237

Appendici 238

3.7 Esercizio (Core di Facebook) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238

3.7.1 Esempi di interrogazioni . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242

3.7.2 Meta-Database e Meta-Dati . . . . . . . . . . . . . . . . . . . . . . . . . . 243

3.8 Modellazione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243

3.8.1 Stima della dimensione del database . . . . . . . . . . . . . . . . . . . . . 244

3.8.2 MODELLO VISIBILITY . . . . . . . . . . . . . . . . . . . . . . . . . . . 245

3.9 Esercizio (Compagnia Aerea) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246

3.9.1 Continuazione ESERCITAZIONE . . . . . . . . . . . . . . . . . . . . . . 250

3.9.2 DFM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253

3.10 DATABASE TECHNOLOGIES . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255

3.10.1 CLOUD COMPUTING . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255

3.10.2 NIST REFERENCE ARCHITECTURE . . . . . . . . . . . . . . . . . . . 256

3.10.3 BIG DATA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

3.10.4 CAP’S THEOREM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 257

3.10.5 DATABASE NoSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258

3.10.6 MAP REDUCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258

5

6

Ringraziamenti

Un grazie particolare va ai miei compagni

d’università, Dino Sbarro, Gabriele Accarino, Giampiero D’Autilia, Matteo Settembrini, Paolo

Panarese ed Emanuele Costa Cesari.

Introduzione

• Prerequisiti

Buona conoscenza di linguaggi Object-Oriented (almeno uno), tecniche e strumenti. Ele-

menti di computer networks e tecnologie di Rete, Web;

• Abilità acquisite

Lo studente sarà in grado di progettare e capire i modelli dei dati, creare e gestire database

e progettare ed implementare applicazioni data-centric.

Lo scopo è fornire le basi circa le principali teorie sui database, tecniche e strumenti per

usare i database e progettare/implementare database applications.

Argomenti:

• Database, database relazionali, NoSQL e NewSQL;

• Sistemi di gestione dei database (DBMS);

• Modello Relazionale ed Algebra Relazionale;

• SQL: definizioni dei dati e loro manipolazioni;

• Basi della Computer-Human Interaction e progettazione delle interfacce;

• Aspetti architetturali: Clients, Servers, Peers, Dispositivi, IoT, ...

• Principi di Data-Analytics;

• Analisi multidimensionale e data-warehouse;

ii

Capitolo 1

Modeling

1.1 Progettazione

Dal punto di vista ingegneristico i costi di progettazione di una Ferrari e di una Fiat non

sono poi cosı̀ differenti. La reale differenza risiede nel modo in cui i due differenti progetti sono

ottimizzati per lo specifico target di clientela e per le caratteristiche ed aspettative, evidente-

mente differenti, delle due automobili. Un ingegnere progetta qualcosa di customizzato, cioè

creato su misura per il committente, di fatto proiettando un’idea che risiede nella sua mente.

A differenza di un progettista di automobili o di edifici, un progettista del software è in grado

di interagire con la mente delle persone e di cambiarne le idee.

1.1.1 Caso di Studio - Google

Oggigiorno i Database sono ovunque. Google è uno dei DB più diffusi. Quando viene

effettuata una ricerca in realtà viene eseguita una query per comunicare con il SW di Google. La

successiva figura illustra un sistema Client/Server per interrogare un Database. Un DBMS

(Database Management System) è un sistema che consente di gestire uno o più DB.

1

Figura 1.1: Architettura Client-Server

La nuvola rappresenta Internet, un insieme dinamico di connessioni, mentre il Client è una

macchina (HW) al cui interno risiede il SW. Tramite l’inserimento dell’url nel browser viene

generato un round-trip. Una volta stabilita la connessione con il DBMS sarà possibile accedere

alle informazioni che risiedono nel DB. Ad esempio la ricerca delle parole: “Cinema Lecce”

restituirà tutte le occorrenze dell’intersezione degli insiemi costituiti dai risultati di ricerca delle

due singole parole. Sui computer di proprietà di Google oltre al DBMS esistono altri software:

Spider o Crawler e Indexer.

Supponiamo che il web sia un grafo connesso, ovvero che ogni pagina web che risponde ad

un url contenga i riferimenti (url) ad altre pagine web e cosı̀ via, fornendo un url allo Spider esso

ricorsivamente percorre tutto il grafo passando ciascuna pagina visitata all’Indexer. L’Indexer

ha il compito di effettuare lo stamming delle parole ovvero la loro estrazione ed il mantenimento

dei riferimenti alle pagine cui sono state trovate. L’idea pertanto consiste nel mantenere un

indice analitico delle parole e i riferimenti a tutte le pagine web che le contengono. Questo

consente di riuscire ad immagazzinare tantissimi riferimenti utilizzando pochi gigabytes. Quindi

quando vogliamo effettuare una ricerca nel web ricorrendo ad un motore di ricerca le ricerche

in realtà sono state già effettuate e l’unica cosa che fa il software è incrociare le informazioni.

Google viene pagata da altre grosse società per far salire in cima i risultati delle ricerche per i

contenuti che le riguardano. Osserviamo che l’ipotesi di rappresentazione del web attraverso un

grafo connesso non è rappresentativa di alcune realtà come quella del deep web. Il deep web è

costituito da isole cioè porzioni di grafo non connesse a quelle del web pertanto non raggiungibili

sebbene nelle isole vengano utilizzati gli stessi protocolli del web (HTTP, ecc.).

2

1.1.2 Organizzazioni

Per quanto concerne le persone che hanno a che fare con un sistema SW, possiamo di-

{User,

stinguere tre tipologie principali: Consumer, Customer}. Per quanto riguarda le

modalità di interazione e comunicazione con un sistema SW, troviamo la tipica architettura

Client-Server (C/S) e la Peer-to-peer (P2P). Nel P2P le entità in gioco sono tutte alla

pari, nel senso che avvengono degli scambi alla pari di informazioni. Internet è oggigiorno do-

minata da questi due meccanismi. Abbiamo cominciato a vedere una prima tipologia, gli user,

che sono però una parte di tutto il resto: gli stakeholders che letteralmente sono dei portatori

di interesse, qualcuno interessato ad un sistema. Gli UT (User Types) sono un sottoinsieme

degli Stakeholders. Ad esempio, il rettore è uno stakeholder, ma non un utente. A volte sono

proprio loro che comandano il tutto! Egli potrebbe pure non interagire con il SW, decide però

le regole in gioco. Per parlare di uno stakeholder bisogna necessariamente precisare il sistema:

per Google ad esempio, Ferrari è uno stakeholder particolare, denominato business partner.

Parliamo ora di Sistema ed Organizzazione. Cos’è un’organizzazione? Esistono le persone e

poi le organizzazioni. L’Università è un tipico esempio di organizzazione: struttura di persone

che svolgono dei compiti ed hanno delle responsabilità, esse si occupano tipicamente di prodotti

e di servizi. All’interno di un’azienda non vi può essere soltanto il singolo venditore, tipicamente

vi è anche una fitta rete di assistenza. Noi abbiamo sempre a che fare con bundle di prodotti e

servizi, ad esempio il comune telefono è un prodotto ed un servizio allo stesso tempo pertanto

oggi quasi tutto è quindi un bundle di prodotti e servizi. Per schematizzare una tipica orga