9651A_1a - Politecnico di Torino

Politecnico di Torino
CeTeM
Sistemi Informativi Aziendali (9651A)
1a
Basi di dati Relazionali
Introduzione
Questo modulo, dopo una breve introduzione, descriverà le Basi di Dati Relazionali. È il
primo dei quattro moduli su cui è articolato il corso di Sistemi Informativi Aziendali
(9651A).
Per questo modulo è possibile fare riferimento ai lucidi (per lo più coincidenti con i lucidi
presentati nelle videocassette) utilizzati dal Prof. Stefano Ceri (e da lui gentilmente forniti)
e disponibili in forma elettronica sul sito del CETEM (in formato PDF, 6 lucidi per pagina).
In particolare per questa prima parte del modulo numero uno (1a) il riferimento è ai lucidi
delle prime quattro lezioni del Prof. Ceri. Per la seconda parte del primo modulo (1b) si
farà riferimento alle successive quattro lezioni del Prof. Ceri.
In queste pagine si farà un richiamo alla teoria (necessariamente non molto dettagliato),
seguito da esercizi svolti e solo proposti (per lo più ripresi dai lucidi a cui si è appena fatto
riferimento).
Il libro di riferimento per la teoria è Basi di Dati (da ora BDD), di Atzeni, Ceri, Paraboschi,
Torlone, seconda edizione (1999), McGraw-Hill editore (ISBN 88 386 0824-5).
Introduzione ai sistemi informativi aziendali
Un Sistema Informativo è un insieme di strumenti, risorse e procedure che consentono la
gestione delle informazioni aziendali, e comprende anche le risorse umane. Se tale
insieme è realizzato utilizzando calcolatori elettronici (e dunque non più solo documenti
cartacei), si parla di sistema informatico.
Un sistema informativo (quale ad es. una applicazione gestionale o finanziaria o un
sistema di prenotazione) genera informazione a partire dai dati grezzi, e gli utenti
ottengono informazioni tramite dei processi, cioè delle procedure aziendali. Tali procedure
sono però facilmente variabili nel tempo, mentre i dati rimangono maggiormente invariati.
Risulta perciò più sensato analizzare il sistema informativo a partire dai dati, strutturati e
controllati tramite un DBMS (DataBase Management System). Nulla impedisce di costruire
architetture prive di DBMS, nelle quali il software applicativo si appoggia direttamente al
sistema operativo, ma ciò rende più difficile operare sui dati ad alto livello tramite linguaggi
specializzati (SQL Structured Query Language).
Tramite l’uso di un DBMS è possibile garantire una corretta condivisione dei dati (anche
nel caso di accesso da parte di più utenti), una migliore qualità dei dati stessi e una loro
maggiore sicurezza (anche dal punto di vista del controllo degli accessi). Anche le
prestazioni risultano di buon livello, e l’intero sistema ha una elevata tolleranza ai guasti.
I dati vengono inquadrati in una struttura, e vengono poi definite delle specifiche
operazioni. In particolare la struttura o schema è su tre livelli; il primo è per le applicazioni,
ed offre la massima astrazione, mentre il secondo livello è di tipo logico, e permette una
descrizione più globale. Infine il terzo livello di schema si occupa di gestire la correlazione
con i meccanismi fisici implementati nel DBMS al fine di interfacciare il tutto con l’hardware
disponibile.
Il DBMS permette l’uso di due diversi linguaggi:
 DDL (Data Definition Language); es. CREATE, DROP, ALTER
 DML (Data Manipulation Language); es. SELECT, INSERT, UPDATE, DELETE
che permettono di definire e manipolare le basi di dati.
Inoltre il DBMS, al pari dei sistemi operativi di buona fattura (dei quali di fatto replica molte
funzioni), permette di definire degli utenti, e tra questi si hanno:
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 1 di 9
Autore: Bartolomeo Montrucchio
Politecnico di Torino
CeTeM
Sistemi Informativi Aziendali (9651A)
1a
Basi di dati Relazionali
1. DBMS Administrator, che utilizza il DDL per creare il database e gestirlo
2. Programmatori applicativi, che utilizzano il DML per programmare applicazioni
3. Utenti casuali, che utilizzano versioni agevolate del DML (magari con interfacce
grafiche)
4. Utenti finali, che si occupano esclusivamente di eseguire le applicazioni da terminale.
Le applicazioni, così come in generale il DBMS, possono essere adoperate anche via rete,
e sovente il loro sviluppo è agevolato dall’uso di strumenti appositi.
Lo scopo dunque sarà quello di imparare a progettare e interrogare le basi di dati, tenendo
presente il continuo sviluppo tecnologico, che nel settore dei database è sempre più
influenzato dalla diffusione ad ogni livello di sistemi interconnessi da Internet.
Il modello relazionale dei dati
Al fine di rappresentare i dati sono stati sviluppati nel corso degli anni vari modelli. Si tratta
del modello gerarchico (anni ’60), del modello reticolare (anni ’70), del modello relazionale
(anni ’80), e infine del modello a oggetti (anni ’90). Il modello di nostro interesse è il
modello relazionale, in quanto gli altri modelli sono superati o ancora non sufficientemente
maturi.
Il modello relazionale, inventato nel 1970 da T. Codd (IBM research), ha cominciato ad
avere successo commerciale a partire dal 1985, ed ora è il modello di riferimento per tutte
le implementazioni di maggiore successo (Oracle, Ibm DB2, Informix, Microsoft
SQLServer e Access).
La definizione formale di relazione (o tabella) è che dato un dominio D (qualunque insieme
di valori), e dato il prodotto cartesiano su n di tali domini (non necessariamente distinti),
una relazione R su tale prodotto cartesiano è un qualunque sottoinsieme del prodotto
cartesiano (purché non vi siano elementi duplicati). Il grado della relazione è il numero n di
domini, la cardinalità della relazione è pari al numero di n-ple (tuple) realizzate all’interno
del prodotto cartesiano, e l’attributo è il nome dato al dominio in una relazione (si noti che i
nomi di attributo in una relazione devono essere tutti distinti tra loro).
La definizione informale (assai più immediata) si può avere osservando i seguenti tre
esempi di tabella:
studente
MATR NOME
CITTA’
C-DIP
123
Carlo
Bologna
Inf
415
Paola
Torino
Inf
702
Antonio Roma
Log
corso
esame
MATR CODDATA
CORSO
VOTO
CODTITOLO
CORSO
DOCENTE
123
1
7-9-97 30
1
matematica Barozzi
123
2
8-1-98 28
2
informatica Meo
702
2
7-9-97 20
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 2 di 9
Autore: Bartolomeo Montrucchio
Politecnico di Torino
CeTeM
Sistemi Informativi Aziendali (9651A)
1a
Basi di dati Relazionali
Per esempio la relazione studente ha gli attributi MATR, NOME, CITTÀ e C-DIP. Avendo
più di una relazione come qui, i nomi delle relazioni (nello schema risultante) devono
essere tutti distinti tra loro.
Dunque la terminologia tra definizione formale e informale presenta le seguenti similitudini.
Relazione (nella def. formale) corrisponde a tabella (nella informale), attributo (formale) a
colonna (informale), tupla (o n-pla) (formale) corrisponde a riga (informale), dominio
(formale) a tipo di dato (informale), cardinalità (formale) a numero di righe (informale), e
grado (formale) a numero di colonne (informale). Si noti che nella definizione formale è
prevista l’assenza di duplicati, mentre nella definizione informale è permessa la presenza
di duplicati.
Ora, dato lo schema relazionale (cioè la struttura del database) riportato nelle tre tabelle
precedenti, e considerata l'istanza riportata (cioè i dati presenti nel database nel momento
in cui il database è stato riportato in figura), è possibile, tramite un linguaggio di
interrogazione, estrarre delle informazioni dal database.
Un secondo esempio di schema, relativo alla gestione del personale, è invece riportato
nelle seguenti tre tabelle:
impiegato
MATR NOME
DATA-ASS
SALARIO
MATR-MGR
1
Piero
1-1-95
3M
2
2
Giorgio
1-1-97
2,5 M
null
3
Giovanni 1-7-96
2M
2
assegnamento
progetto
MATR NUM-PROG PERC
NUM-PROG TITOLO TIPO
1
3
50
3
Idea
Esprit
1
4
50
4
Wide
Esprit
2
3
100
3
4
100
Si noti che alcune istanze potrebbero non essere corrette (per es. data inesistente). Per
garantire la correttezza vengono usati i Vincoli di Integrità, che sono le chiavi, i vincoli sui
valori nulli, l’integrità referenziale, e infine i vincoli generici.
Una chiave è un sottoinsieme degli attributi dello schema che ha le proprietà di unicità
(non esistono cioè due tuple con chiave uguale) e minimalità (sottraendo un qualunque
attributo alla chiave si perde la proprietà di unicità). Negli esempi visti le chiavi sono MATR
per studente, MATR e contemporaneamente COD-CORSO per esame, COD-CORSO per
corso, MATR per impiegato, MATR e NUM-PROG per assegnamento e NUM-PROG per
progetto. Se le chiavi possibili sono molteplici, una è definita chiave primaria, le altre
secondarie. Per es. nella relazione CLIENTE(COD-CLIENTE, INDIRIZZO, P-IVA) si ha
che COD-CLIENTE è chiave primaria, mentre P-IVA è chiave secondaria. Gli attributi che
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 3 di 9
Autore: Bartolomeo Montrucchio
Sistemi Informativi Aziendali (9651A)
Politecnico di Torino
CeTeM
1a
Basi di dati Relazionali
caratterizzano la chiave primaria vengono sottolineati. Il concetto di chiave viene spiegato
da p. 31 a p. 35 BDD.
Esempi di Interrogazioni
 Quali professori hanno interrogato Carlo?
Risposta: dalla tabella studente si trova la riga per cui nel campo nome c’è Carlo. Si
ottiene così il numero di matricola (123), e dalla tabella esame si ricava che i corsi in
questione sono il numero 1 e 2. Infine dalla tabella corso si scoprono i nomi dei docenti
relativi a quel codice corso.
 Quali studenti hanno preso 30 in matematica?
Risposta: da corso si seleziona matematica (1), nella tabella esame si trova solo la
matricola 123, e infine dalla tabella studente si ritrova il nome voluto.
 Chi è il manager di Piero?
Risposta: dalla tabella impiegato si ricava che la matricola del manager di Piero è 2, e poi
che il nome corrispondente alla matricola 2 è Giorgio.
 In quali tipi di progetti lavora Giovanni?
Risposta: Giovanni ha matricola 3 (dalla tabella impiegato). Dalla tabella assegnamento la
matricola 3 lavora sul solo progetto 4, che dalla tabella progetto risulta essere di tipo
Esprit.
Dato ora lo schema seguente:
cliente
COD-CLI INDIRIZZO P-IVA
ordine
COD-ORD COD-CLI DATA IMPORTO
dettaglio
COD-ORD
COD-PROD QTA
prodotto
COD-PROD NOME
PREZZO
come si potrebbero effettuare queste interrogazioni?
 Quali ordini ha emesso Paolo?
 Quanti ordini ha emesso Paolo?
 Quante candele sono state ordinate il 5 luglio 1993?
 Calcolare per ciascun cliente la somma degli importi di tutti gli ordini.
 Estrarre l’ordine di importo più alto.
Linguaggi di interrogazione
Esistono vari linguaggi di interrogazione. I linguaggi formali, non supportati dai pacchetti
applicativi, sono Algebra relazionale, Calcolo relazionale e Programmazione logica. I
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 4 di 9
Autore: Bartolomeo Montrucchio
Politecnico di Torino
CeTeM
Sistemi Informativi Aziendali (9651A)
1a
Basi di dati Relazionali
linguaggi programmativi sono invece l’SQL (Structured Query Language), e il QBE (Query
By Example).
Algebra Relazionale
Definita da Codd nel 1970, risulta molto utile per imparare a formulare le query, e presenta
5 sole operazioni. Queste operazioni si suddividono in unarie (selezione e proiezione), e
binarie (unione, differenza e join); gli operandi sono le tabelle. Sono anche utilizzabili
espressioni booleane di predicati semplici (con AND, OR, NOT, =, !=, <, <=, >, >=, e
TRUE e FALSE come predicati semplici).
1. SELEZIONE: SELECT [NOME=’Paola’] STUDENTE seleziona la sola tupla 415
Paola Torino Inf. dalla tabella studente. Il risultato è a sua volta una tabella, priva però
di nome. Es. SELECT [(CITTÀ=’TORINO’) OR ((CITTÀ=’ROMA’) AND NOT (CDIP=’Log’))] STUDENTE
2. PROIEZIONE: PROJECT [NOME,C-DIP] STUDENTE seleziona le sole colonne
NOME e C-DIP dalla tabella studente. Si noti che l’operazione di proiezione ELIMINA i
duplicati, mentre nel modello informale (e nei sistemi) l’eliminazione dei duplicati va
richiesta esplicitamente.
NOTA: per dare un nome alle tabelle ottenute si usa l’ASSEGNAMENTO (che peraltro non
fa parte delle operazioni algebriche). Es. INFORMATICI = SELECT [C-DIP=’Inf’]
STUDENTI
3. UNIONE: TABELLA1 UNION TABELLA2. TABELLA1 e TABELLA2 devono essere
compatibili, devono cioè avere lo stesso grado (numero di colonne) o (nei sistemi)
avere domini ordinatamente uguali. Il risultato è l’unione delle tuple (più tuple). L’unione
è senza duplicati.
4. DIFFERENZA: TABELLA1 MINUS TABELLA2. Se le due tabelle sono compatibili,
viene fatta la differenza delle tuple (il numero delle tuple scende). Si noti che NON è
commutativa.
5. JOIN:
STUDENTE JOIN [MATR=MATR] ESAME. Vengono aggiunte le colonne di
esame a quelle di studente, e le tuple sono quelle ottenute concatenando le tuple per
le quali MATR=MATR (cioè MATR è la stessa per ambedue le tabelle). MATR diventa
STUDENTE.MATR e ESAME.MATR per evitare l’omonimia. Il JOIN può essere fatto
anche usando i comparatori (Es. ESAME.MATR <= STUDENTE.MATR). L’EQUI-JOIN
è quello fatto con i soli confronti di uguaglianza, e in particolare il JOIN NATURALE è
l’EQUI-JOIN di tutti gli attributi omonimi (si può fare anche con tre tabelle, es.
STUDENTE JOIN ESAME JOIN CORSO).
NOTA: l’uso di uno schema relazionale a più tabelle presenta (rispetto alla tabella unica
ottenuta tramite il JOIN NATURALE) il vantaggio di risparmiare inutile ripetizioni di dati (ad
es. il nome è nella sola tabella studente, e non in tutte le tuple della tabella unica).
Le operazioni algebriche possono essere concatenate a formare espressioni algebriche,
consentendo così di estrarre i dati.
Esempi di espressioni algebriche
 Quali studenti sono iscritti al diploma di Informatica?
PROJECT [NOME]
SELECT [C-DIP=’Inf’]
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 5 di 9
Autore: Bartolomeo Montrucchio
Politecnico di Torino
CeTeM
Sistemi Informativi Aziendali (9651A)
1a





Basi di dati Relazionali
STUDENTE
Il risultato è una tabella formata da una sola colonna NOME, e con due tuple Carlo e
Paola.
Quali studenti di Logistica non sono di Milano?
PROJECT [NOME]
SELECT [C-DIP=’Log’ AND CITTÀ != ‘Milano’]
STUDENTE
L’unica tupla racchiude Antonio.
Quali studenti hanno preso 30 in matematica?
PROJECT [NOME]
SELECT [VOTO=30 AND TITOLO=’matematica’]
(STUDENTE JOIN ESAME JOIN CORSO)
che equivale a:
PROJECT [NOME]
(STUDENTE JOIN
(SELECT [VOTO=30] ESAME) JOIN
(SELECT [TITOLO=’matematica’] CORSO))
L’unica tupla è per Carlo.
Quali professori hanno esaminato Antonio?
PROJECT [DOCENTE]
SELECT [NOME=’Antonio’]
(STUDENTE JOIN ESAME JOIN CORSO)
che equivale a:
PROJECT [DOCENTE]
(CORSO JOIN
(ESAME JOIN
SELECT [NOME=’Antonio’] STUDENTE))
Il risultato è Meo.
Estrarre la matricola degli studenti romani oppure degli studenti che hanno sostenuto
un esame il giorno 8 gennaio 1998.
(PROJECT [MATR]
SELECT [CITTÀ=’Roma’]STUDENTE)
UNION
(PROJECT [MATR] (STUDENTE JOIN
SELECT [DATA=8-1-98] ESAME))
Il risultato è la colonna MATR con due tuple 702 e 123.
Estrarre la matricola degli studenti che hanno preso almeno un voto superiore a 28 e
non sono mai scesi sotto al 25.
(PROJECT [MATR]
SELECT [VOTO>28] ESAME)
MINUS
(PROJECT [MATR]
SELECT [VOTO<25] ESAME)
Il risultato è la MATR 123 (infatti 123 MINUS 702 dà ancora 123).
Date ora le basi dati già note esprimere in forma algebrica le seguenti interrogazioni:
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 6 di 9
Autore: Bartolomeo Montrucchio
Politecnico di Torino
CeTeM
Sistemi Informativi Aziendali (9651A)
1a





Basi di dati Relazionali
In quali tipi di progetti lavora Giovanni?
Chi è il manager di Piero?
Quali ordini ha emesso Paolo?
Quali prodotti sono ordinati da un cliente di Milano?
Quali prodotti hanno prezzo inferiore a 5000 lire e non sono presenti in nessun ordine?
Dichiarazione degli schemi in SQL
L’SQL (nome originale SEQUEL), definito nel 1976, e poi standardizzato (giunto a SQL-3,
le normali implementazioni usano SQL-2 (cioè SQL-92)), è composto da DDL (Data
Definition Language) per la definizione di domini, tabelle, indici, autorizzazioni, viste,
vincoli, procedure, trigger, e da DML (Data Manipulation Language), cioè linguaggio di
query e di modifica e comandi transazionali.
Domini elementari in SQL-2:
1. Stringhe di lunghezza fissa N CHAR(N) e variabile VARCHAR(N), e stringhe di bit
lunghe N BIT(N) e variabili VARBIT(N)
2. Valori numerici esatti con N cifre decimali NUMERIC(N), DECIMAL(N), INTEGER e
SMALLINT
3. Valori numerici approssimati. FLOAT(N) se N=2 si ha che 0.172E04 = 1700 (N cifre
decimali significative dopo la virgola). REAL e DOUBLE PRECISION
4. Domini speciali. DATE YYYYMMDD, TIME(N) HHMMSS.NNNN, TIMESTAMP (date +
time, istante di tempo), e INTERVAL (istante iniziale e finale).
Il valore NULL è polimorfico (appartiene a tutti i domini). Ha valore non noto. Può esistere
nella realtà ma essere sconosciuto al database (es. una data di nascita), oppure può
essere inapplicabile (es. numero patente per minorenni).
I vincoli di integrità di dominio sono NOT NULL (esclude il valore nullo), PRIMARY KEY
(chiave primaria, implica il NOT NULL), UNIQUE (chiave secondaria, non implica il NOT
NULL), e CHECK (predicato che deve essere soddisfatto).
Es. di definizione di un dominio applicativo:
CREATE DOMAIN PrezzoQuotidiani
AS INTEGER
DEFAULT 1500
NOT NULL
Es. di definizione di tabelle:
CREATE TABLE ESAME
( MATR CHAR(6)
COD-CORSO CHAR(6)
DATA DATE NOT NULL
VOTO SMALLINT NOT NULL
PRIMARY KEY(MATR, COD-CORSO) )
CREATE TABLE CORSO
( COD-CORSO CHAR(6) PRIMARY KEY
TITOLO VARCHAR(30) NOT NULL
DOCENTE VARCHAR(20) )
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 7 di 9
Autore: Bartolomeo Montrucchio
Politecnico di Torino
CeTeM
Sistemi Informativi Aziendali (9651A)
1a
Basi di dati Relazionali
Integrità referenziale: alcuni attributi della tabella figlio (ad es. esame è figlia di studente)
sono definiti FOREIGN KEY per esprimere il legame gerarchico tra padre e figlio. I valori
contenuti nella FOREIGN KEY devono essere sempre presenti nella tabella padre (non è
cioè possibile avere esami senza studenti).
Cosa succede degli esami (figli) se si cancellano le tuple degli studenti (padri)? Si creano
degli orfani. In questo caso ci sono quattro alternative: CASCADE (si cancellano anche gli
esami), SET NULL (gli esami vanno a NULL), SET DEFAULT (gli esami vanno a
DEFAULT), e NO ACTION (utile se si sa già che le cose sono poi destinate ad andare a
posto automaticamente in seguito). Nel caso di semplice modifica delle tuple padri ci
sono le stesse possibilità (qui CASCADE provoca la modifica della matricola anche nella
tabella studente).
È anche lecito essere figli di più padri. Es.:
CREATE TABLE ESAME
(…
PRIMARY KEY (MATR, COD-CORSO)
FOREIGN KEY MATR
REFERENCES STUDENTI
ON DELETE CASCADE
ON UPDATE CASCADE
FOREIGN KEY COD-CORSO
REFERENCES CORSO
ON DELETE NO ACTION
ON UPDATE NO ACTION )
Esempi di definizione di tabelle
 Tabella CLIENTE
CREATE TABLE CLIENTE
(COD-CLI CHAR(6) PRIMARY KEY
INDIRIZZO CHAR(50)
P-IVA CHAR(12) UNIQUE)
 Tabella ORDINE
CREATE TABLE ORDINE
( COD-ORD CHAR(6) PRIMARY KEY
COD-CLI CHAR(6) NOT NULL DEFAULT=’999999’
DATA DATE
IMPORTO INTEGER
FOREIGN KEY COD-CLI
REFERENCES ORDINE
ON DELETE SET DEFAULT
ON UPDATE SET DEFAULT )
 Tabella DETTAGLIO
CREATE TABLE DETTAGLIO
(COD-ORD CHAR(6)
COD-PROD CHAR(6)
QTA SMALLINT
PRIMARY KEY(COD_ORD, COD-PROD)
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 8 di 9
Autore: Bartolomeo Montrucchio
Politecnico di Torino
CeTeM
Sistemi Informativi Aziendali (9651A)
1a

Basi di dati Relazionali
FOREIGN KEY COD-ORD
REFERENCES ORDINE
ON DELETE CASCADE
ON DELETE CASCADE
FOREIGN KEY COD-PROD
REFERENCES PRODOTTO
ON DELETE NO ACTION
ON DELETE NO ACTION)
Tabella PRODOTTO
CREATE TABLE PRODOTTO
(COD-PROD CHAR(6) PRIMARY KEY
NOME CHAR(20)
PREZZO SMALLINT)
Provare a esprimere in SQL la dichiarazione dello schema della base di dati per la
gestione del personale (impiegato, assegnamento, progetto).
© Politecnico di Torino
Data ultima revisione 02/06/2017
Pagina 9 di 9
Autore: Bartolomeo Montrucchio