Appunti Basi di dati

Appunti di Sistemi Informativi e

Basi di Dati

Corso tenuto dalla prof.ssa C. Diamantini

A. A. 2014/2015

Appunti realizzati da:

ˆ a

Davide Benedetti s1065588 studenti.univpm.it

Aggiornati in data 17 ottobre 2015

ii Premesse

Limitazione di responsabili-

tà

Il presente documento può contenere errori di varia natura o

può risultare incompleto. Si declinano responsabilità su conse-

guenze negative derivante dall’uso di questo documento.

Convenzioni tipografiche

I colori

Quattro diversi colori esprimono due particolari significati e

relative sfumature:

ˆ Dei nomi, dei soggetti, delle tematiche importanti sono co-

rosso analitico;

lorati di e compaiono nell’indice quelli non

così importanti da meritare una menzione nell’indice o co-

munque piuttosto rilevanti solo in relazione allo specifico

magenta;

argomento sono colorati di

ˆ Le azioni, i compiti, le proprietà relative ai concetti così mar-

blu;

cati sono colorati di in alternativa, se il nome in que-

stione non assume una grande rilevanza o se l’azione che

si vuole enfatizzare è marginalmente rilevante (e.g. in una

violetto.

lista di concetti), si sceglie una tonalità tendente al

Con le tonalità di rosso si evidenziano quindi degli oggetti; con

quelle del blu, le azioni che questi oggetti compiono o le pro-

prietà di loro competenza. In analogia all’analisi logica, si vuole

evidenziare soggetto e predicato.

iii

iv Premesse

Nel caso di immagini o di codice sorgente tali convenzioni sono

ignorate.

Struttura del documento

Due filosofie delineano la struttura del documento:

ˆ Massima linearità:

– Il documento può essere letto in maniera sequenziale,

dall’inizio alla fine, senza passare per delle appendi-

ci che possono disturbare il flusso di lettura; eventuali

spiegazioni di argomenti propedeutici ad alcuni concetti

presentati nel corso non vengono riportati in appendi-

ci alla fine del documento ma nel punto in cui vengono

menzionati.

ˆ Gerarchizzazione dei contenuti:

– Anziché proporre “blocchi” di testo in cui concetti impor-

tanti sono immersi in molte parole, si preferisce struttu-

rare il testo in modo da renderlo affine a una mappa

concettuale, al fine di sollevare il più possibile il lettore

dalla necessità di trasformare mentalmente un blocco

di testo in una rete concettuale più fruibile. Ciò implica

principalmente:

* Un uso spinto di elenchi puntati o numerati, anche

in presenza di un solo elemento. In tal modo:

· L’elemento d’interesse spicca comunque dal te-

sto circostante;

· Si enfatizza che tale elemento appartenga a un

certo concetto, indipendentemente dal fatto che

ne esista solo uno o più.

* L’uso di riquadri colorati che confinino esempi, os-

servazioni, teoremi, definizioni etc. in uno spazio vi-

sivo distinto dall’argomento trattato;

* Particolare attenzione nel nominare capitoli, sezioni,

sottosezioni etc. in modo che siano presenti tutte e

solo le informazioni a essi relative; v

* In generale, uno stile meno prosaico, più conciso e

schematico.

Per marcare la differenza rispetto a uno stile più “canonico” di

scrittura, tali filosofie potevano essere così descritte:

“Due filosofie delineano la struttura del documento.

Una vuole massimizzarne la linearità: il documento può esse-

re letto in maniera sequenziale, dall’inizio alla fine, senza passa-

re per delle appendici che possono disturbare il flusso di lettura;

eventuali spiegazioni di argomenti propedeutici ad alcuni concet-

ti presentati nel corso non vengono riportati in appendici alla fine

del documento ma nel punto in cui vengono menzionati.

La seconda filosofia mira a gerarchizzarne i contenuti: anziché

proporre blocchi di testo in cui concetti importanti sono immersi

in molte parole, si preferisce strutturare il testo in modo da ren-

derlo affine a una mappa concettuale, al fine di sollevare il più

possibile il lettore dalla necessità di trasformare mentalmente un

blocco di testo in una rete concettuale più fruibile. Ciò implica:

un uso spinto di elenchi puntati o numerati, anche in presenza

di un solo elemento, in modo che l’elemento d’interesse spicchi

comunque dal testo circostante e in modo da enfatizzare il fatto

che tale elemento appartenga a un certo concetto, indipenden-

temente dal fatto che ne esista solo uno o più; l’uso di riquadri

colorati che confinino esempi, osservazioni, teoremi, definizioni

etc. in uno spazio visivo distinto dall’argomento trattato; infine,

una particolare attenzione nel nominare capitoli, sezioni, sottose-

zioni etc. in modo che siano presenti tutte e solo le informazioni

a essi relative. In generale, si vuole ottenere uno stile meno pro-

saico, più conciso e più schematico.”

Tale versione, che contiene poche parole in più rispetto alla

versione schematica, è probabilmente meno piacevole alla lettu-

ra e meno immediata da comprendere e tenere a mente, da cui

la decisione di seguire le due filosofie descritte.

vi Premesse

Indice

Premesse iii

1 Introduzione 1

1.1 Concetti essenziali . . . . . . . . . . . . . . . . . . . . . . 1

1.1.1 Informatica . . . . . . . . . . . . . . . . . . . . . . 1

1.1.2 Sistema . . . . . . . . . . . . . . . . . . . . . . . . 1

1.1.3 Sistema Azienda . . . . . . . . . . . . . . . . . . 2

1.1.4 Sistema organizzativo . . . . . . . . . . . . . . . 2

1.1.5 Efficienza ed efficacia . . . . . . . . . . . . . . . 2

1.1.6 Sistema Informativo e Sistema Informatico . 3

1.1.7 Processi aziendali . . . . . . . . . . . . . . . . . . 4

1.1.8 Processi, dati e informazioni . . . . . . . . . . . 5

1.1.8.1 Architettura generale dei sistemi in-

formatici . . . . . . . . . . . . . . . . . 7

1.2 Basi di dati e DBMS . . . . . . . . . . . . . . . . . . . . . 8

1.2.1 La rivoluzione copernicana dei dati . . . . . . 8

1.2.2 Schema e istanza . . . . . . . . . . . . . . . . . . 11

1.2.3 Modello dei dati . . . . . . . . . . . . . . . . . . . 12

1.2.4 Schemi di un DBMS . . . . . . . . . . . . . . . . . 13

1.2.5 Indipendenza dei dati . . . . . . . . . . . . . . . 15

1.2.6 Transazioni . . . . . . . . . . . . . . . . . . . . . . 15

1.2.7 Caratteristiche dei DBMS . . . . . . . . . . . . . 16

1.2.7.1 Privatezza . . . . . . . . . . . . . . . . 16

1.2.7.2 Affidabilità . . . . . . . . . . . . . . . . 17

1.2.7.3 Efficienza . . . . . . . . . . . . . . . . . 17

1.2.7.4 Efficacia . . . . . . . . . . . . . . . . . . 17

1.2.8 Vantaggi e svantaggi del DBMS . . . . . . . . . 18

1.2.9 DML e DDL . . . . . . . . . . . . . . . . . . . . . . 18

1.2.10 Personaggi e interpreti . . . . . . . . . . . . . . 19

1.2.10.1 Database Administrator (DBA) . . . 19

vii

viii Indice

I Basi di dati relazionali: modello e linguaggi 21

2 Modello relazionale 23

2.1 Relazione matematica . . . . . . . . . . . . . . . . . . . . 24

2.1.1 Proprietà . . . . . . . . . . . . . . . . . . . . . . . 25

2.1.2 Strutture posizionale vs. non posizionale . . . 26

2.1.2.1 Struttura posizionale . . . . . . . . . . 26

2.1.2.2 Struttura non posizionale . . . . . . . 26

2.1.3 Tabelle e relazioni . . . . . . . . . . . . . . . . . . 28

2.1.4 Modello basato su valori . . . . . . . . . . . . . 28

2.2 Relazioni . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

2.2.1 Relazioni rappresentati strutture nidificate . 32

2.2.2 Informazione incompleta . . . . . . . . . . . . . 33

2.2.3 Chiavi . . . . . . . . . . . . . . . . . . . . . . . . . 34

2.2.3.1 Esistenza delle chiavi . . . . . . . . . 35

2.2.3.2 Importanza delle chiavi . . . . . . . . 35

2.2.3.3 Chiavi e valori nulli . . . . . . . . . . . 35

2.2.3.4 Chiave primaria . . . . . . . . . . . . . 36

2.2.3.5 Integrità referenziale . . . . . . . . . 36

2.2.4 Vincoli di integrità . . . . . . . . . . . . . . . . . 37

n-upla

2.2.4.1 Vincoli di . . . . . . . . . . . . . 39

2.2.4.2 Vincoli di integrità referenziale . . . 39

3 Algebra e Calcolo Relazionale 41

3.1 Algebra Relazionale . . . . . . . . . . . . . . . . . . . . . 41

3.2 Unione, intersezione, differenza . . . . . . . . . . . . . 42

ρ

3.3 Ridenominazione . . . . . . . . . . . . . . . . . . . . . . 43

3.4 Selezione e Proiezione . . . . . . . . . . . . . . . . . . . 45

σ

3.4.1 Selezione . . . . . . . . . . . . . . . . . . . . . . 45

π

3.4.2 Proiezione . . . . . . . . . . . . . . . . . . . . . 47

./

3.5 Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48

3.5.1 Join naturale . . . . . . . . . . . . . . . . . . . . . 48

3.5.2 Join completi e incompleti . . . . . . . . . . . . 50

3.5.3 Join esterno . . . . . . . . . . . . . . . . . . . . . . 51

n-ario,

3.6 Join intersezione, prodotto cartesiano . . . . . 52

3.7 Theta-join ed equi-join . . . . . . . . . . . . . . . . . . . 54

3.8 Interrogazioni in algebra relazionale . . . . . . . . . . 55

3.9 Equivalenza di espressioni algebriche . . . . . . . . . 59

3.10 Algebra con valori nulli . . . . . . . . . . . . . . . . . . . 63

3.10.1 Logica a tre valori . . . . . . . . . . . . . . . . . . 63

3.10.2 Condizioni di nullità . . . . . . . . . . . . . . . . 64

ix

3.11 Viste . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

3.12 Calcolo relazionale . . . . . . . . . . . . . . . . . . . . . . 67

3.12.1 Calcolo relazionale su dominî . . . . . . . . . . 68

3.12.2 Calcolo su tuple con dichiarazioni di range . 71

4 SQL 73

4.1 Definizione dei dati in SQL . . . . . . . . . . . . . . . . . 74

4.1.1 I dominî elementari . . . . . . . . . . . . . . . . . 74

4.1.1.1 Caratteri . . . . . . . . . . . . . . . . . 74

4.1.1.2 Bit . . . . . . . . . . . . . . . . . . . . . . 75

4.1.1.3 Tipi numerici esatti . . . . . . . . . . . 76

4.1.1.4 Tipi numerici approssimati . . . . . . 76

4.1.1.5 Data e ora . . . . . . . . . . . . . . . . 77

4.1.1.6 Intervalli temporali . . . . . . . . . . . 77

4.1.2 Definizione di schema . . . . . . . . . . . . . . . 78

4.1.3 Definizione delle tabelle . . . . . . . . . . . . . 78

4.1.4 Definizione dei dominî . . . . . . . . . . . . . . . 79

4.1.5 Specifica di valori di default . . . . . . . . . . . 79

4.1.6 Vincoli intrarelazionali . . . . . . . . . . . . . . . 80

4.1.7 Vincoli interrelazionali . . . . . . . . . . . . . . . 82

4.1.8 Modifica degli schemi . . . . . . . . . . . . . . . 85

4.1.8.1 Alter . . . . . . . . . . . . . . . . . . . . 85

4.1.8.2 Drop . . . . . . . . . . . . . . . . . . . . 86

4.1.9 Cataloghi relazionali . . . . . . . . . . . . . . . . 87

4.2 Interrogazioni in SQL . . . . . . . . . . . . . . . . . . . . . 88

4.2.1 Dichiaratività di SQL . . . . . . . . . . . . . . . . 88

4.2.2 Interrogazioni semplici . . . . . . . . . . . . . . 88

4.2.2.1 Clausola select . . . . . . . . . . . . . 89

4.2.2.2 Clausola from . . . . . . . . . . . . . . 90

4.2.2.3 Clausola where . . . . . . . . . . . . . 90

4.2.2.4 Gestione dei valori nulli . . . . . . . . 92

4.2.2.5 Interpretazione algebrica delle inter-

rogazioni SQL . . . . . . . . . . . . . . 92

4.2.2.6 Duplicati . . . . . . . . . . . . . . . . . 92

4.2.2.7 Join interni ed esterni . . . . . . . . . 93

4.2.2.8 Uso di variabili . . . . . . . . . . . . . . 95

4.2.2.9 Ordinamento . . . . . . . . . . . . . . . 96

4.2.3 Operatori aggregati . . . . . . . . . . . . . . . . 97

4.2.4 Interrogazioni con Raggruppamento . . . . . . 100

4.2.4.1 Predicati sui gruppi . . . . . . . . . . . 101

4.2.5 Interrogazioni di tipo insiemistico . . . . . . . 102

x Indice

4.2.6 Interrogazioni nidificate . . . . . . . . . . . . . . 104

4.2.6.1 Passaggio di Binding . . . . . . . . . . 106

4.2.6.2 Operatore exists . . . . . . . . . . . . 107

4.3 Manipolazione dei dati in SQL . . . . . . . . . . . . . . . 108

4.3.1 Inserimento . . . . . . . . . . . . . . . . . . . . . . 108

4.3.2 Cancellazione . . . . . . . . . . . . . . . . . . . . 109

4.3.3 Modifica . . . . . . . . . . . . . . . . . . . . . . . . 110

4.4 Altre definizioni dei dati in SQL . . . . . . . . . . . . . . 111

4.4.1 Vincoli di integrità generici . . . . . . . . . . . . 111

4.4.2 Asserzioni . . . . . . . . . . . . . . . . . . . . . . . 112

4.4.3 Viste . . . . . . . . . . . . . . . . . . . . . . . . . . 112

4.4.4 Viste per la scrittura di interrogazioni . . . . . 114

4.5 Funzioni scalari . . . . . . . . . . . . . . . . . . . . . . . . 114

4.5.1 Funzioni condizionali . . . . . . . . . . . . . . . . 115

4.5.1.1 Coalesce . . . . . . . . . . . . . . . . . 115

4.5.1.2 Nullif . . . . . . . . . . . . . . . . . . . . 115

4.5.1.3 Case . . . . . . . . . . . . . . . . . . . . 116

4.6 Controllo dell’accesso . . . . . . . . . . . . . . . . . . . . 116

4.6.1 Risorse e privilegi . . . . . . . . . . . . . . . . . . 116

4.6.2 Comandi per concedere e revocare privilegi 118

4.7 Transazioni . . . . . . . . . . . . . . . . . . . . . . . . . . . 119

4.7.1 Atomicità . . . . . . . . . . . . . . . . . . . . . . . 120

4.7.2 Consistenza . . . . . . . . . . . . . . . . . . . . . 120

4.7.3 Isolamento . . . . . . . . . . . . . . . . . . . . . . 120

4.7.4 Persistenza . . . . . . . . . . . . . . . . . . . . . . 121

II Progettazione di basi di dati 123

5 Progettazione Concettuale 129

5.1 Modello Entità-Relazione . . . . . . . . . . . . . . . . . . 130

5.1.1 Entità . . . . . . . . . . . . . . . . . . . . . . . . . . 130

5.1.2 Relationship . . . . . . . . . . . . . . . . . . . . . 131

5.1.2.1 Rappresentazione insiemistica . . . 132

5.1.3 Attributo . . . . . . . . . . . . . . . . . . . . . . . . 133

5.1.3.1 Attributi composti . . . . . . . . . . . 133

5.1.4 Cardinalità . . . . . . . . . . . . . . . . . . . . . . 134

5.1.4.1 Cardinalità di relationship . . . . . . 134

5.1.5 Cardinalità di attributi . . . . . . . . . . . . . . . 135

5.1.6 Tipi di relationship . . . . . . . . . . . . . . . . . 136

5.1.6.1 Uno a uno . . . . . . . . . . . . . . . . . 136

xi

5.1.6.2 Uno a molti . . . . . . . . . . . . . . . . 137

5.1.6.3 Molti a molti . . . . . . . . . . . . . . . 137

5.1.7 Identificatore di un’entità . . . . . . . . . . . . . 138

5.1.8 Generalizzazione . . . . . . . . . . . . . . . . . . 140

5.2 Documentazione associata agli schemi concettuali . 143

5.2.1 Regola aziendale . . . . . . . . . . . . . . . . . . 143

5.2.2 Dizionario dei dati . . . . . . . . . . . . . . . . . 144

5.3 Strategie di progetto . . . . . . . . . . . . . . . . . . . . . 145

5.3.1 Strategia top-down . . . . . . . . . . . . . . . . . 145

5.3.2 Strategia bottom-up . . . . . . . . . . . . . . . . 146

5.3.3 Strategia inside-out . . . . . . . . . . . . . . . . 146

5.3.4 Strategia mista . . . . . . . . . . . . . . . . . . . 147

5.4 Qualità di uno schema concettuale . . . . . . . . . . . 147

5.5 Design patterns . . . . . . . . . . . . . . . . . . . . . . . . 148

5.5.1 Reificazione di attributo di entità . . . . . . . . 148

5.5.2 Part-of . . . . . . . . . . . . . . . . . . . . . . . . . 148

5.5.3 Instance-of . . . . . . . . . . . . . . . . . . . . . . 149

5.5.4 Reificazione di relazione binaria . . . . . . . . 149

5.5.4.1 Reificazione di relazione ricorsiva . 150

5.5.5 Reificazione di attributo di relazione . . . . . . 150

5.5.6 Storicizzazione di entità . . . . . . . . . . . . . . 151

6 Progettazione logica 153

6.1 Analisi delle Prestazioni . . . . . . . . . . . . . . . . . . . 154

6.1.1 Costo di un’operazione . . . . . . . . . . . . . . 154

6.1.1.1 Tavola dei volumi . . . . . . . . . . . . 154

6.1.1.2 Stima dei volumi . . . . . . . . . . . . 155

6.1.1.3 Stima del costo di un’operazione . . 155

6.2 Attività della ristrutturazione . . . . . . . . . . . . . . . 159

6.2.1 Analisi delle ridondanze . . . . . . . . . . . . . . 160

6.2.2 Eliminazione delle generalizzazioni . . . . . . 162

7 Normalizzazione 165

7.1 Approccio informale . . . . . . . . . . . . . . . . . . . . . 165

7.2 Dipendenze funzionali . . . . . . . . . . . . . . . . . . . . 166

7.3 Forma normale di Boyce e Codd . . . . . . . . . . . . . 168

7.4 Decomposizione in forma normale di Boyce e Codd 169

7.5 Proprietà delle decomposizioni . . . . . . . . . . . . . . 171

7.5.1 Decomposizione senza perdita . . . . . . . . . 171

7.5.2 Conservazione delle dipendenze . . . . . . . . 173

xii Indice

7.6 Terza forma normale . . . . . . . . . . . . . . . . . . . . . 174

7.6.1 Decomposizione in terza forma normale . . . 176

1

Introduzione

1.1 Concetti essenziali

1.1.1 Informatica

trattamento razionale,

L’informatica è la scienza del spe-

dell’informa-

cialmente per mezzo di macchine automatiche,

zione, considerata come supporto alla conoscenza umana e alla

comunicazione.

L’informatica si basa su due pilastri:

ˆ Algoritmi;

ˆ Dati.

1.1.2 Sistema

sistema

Un è un ente che:

ˆ funzione

Svolge una (e ha un obiettivo);

ˆ uscite;

Fornendo delle determinate

ˆ input

In corrispondenza di certi presenti o passati.

Alcuni esempi:

ˆ Organismi viventi, ecosistemi;

ˆ Macchine, fabbriche, circuiti;

ˆ Aziende, enti pubblici.

Ogni sistema ha: 1

2 Introduzione

ˆ struttura, sottosistemi,

Una cioè è articolato in che inte-

ragiscono fra di loro e con il mondo esterno;

ˆ Una funzione specifica.

Gli input di un sottosistema possono provenire dal mondo ester-

no oppure da altri sottosistemi, e lo stesso vale per gli output:

possono essere diretti all’interno del sistema, oppure verso il

mondo esterno.

1.1.3 Sistema Azienda

L’obiettivo di un sistema azienda è di produrre valore. La pro-

duzione avviene tramite dei processi produttivi che consumano o

gestiscono risorse.

Alcuni esempi di processi aziendali, flussi di attività collegate:

ˆ Produzione di beni;

ˆ Gestione clienti;

ˆ Pianificazione e controllo.

1.1.4 Sistema organizzativo