Transcript Progettazione di Basi di Dati

Basi di Dati
Argomenti
• Modellazione concettuale (modello ER)
• Traduzione in un modello logico (modello
relazionale)
• Interrogazione di una base dati (SQL SELECT)
Modellazione concettuale (modello ER)
Modello Entity Relationship (ER)
Modello concettuale
Fornisce una serie di costrutti per descrivere la realtà di interesse
di facile comprensione, indipendente dal modo in cui la basi di dati
viene descritta e gestita entro un DBMS
Usato per descrivere schema concettuale di una base di dati
Costrutti principali del modello
• Entità: classi di oggetti che hanno
proprietà comuni ed esistenza autonoma
• Relazione: legame logico che coinvolge
due o più entità
• Attributo: proprietà informativa elementare
di un’entità (o anche di una relazione)
Altri costrutti del modello
• Vincoli di integrità: limitazioni o specifiche
espresse sui precedenti costrutti del modello:
- cardinalità delle Relazioni
- cardinalità degli Attributi
- identificatori delle Entità (chiavi)
• Ulteriori elementi (gerarchie di generalizzazione,
attributi compositi) che non affrontiamo
Modello ER: rappresentazione grafica
(1,n)
(0,n)
EntitàB
Relaz.C
EntitàA
Attrib.D
Attrib.E
Attrib.F
Attrib.G
Descrizione dominio
• “Si vuole realizzare un archivio per la
gestione di una libreria. I principali requisiti
espressi dall’utente sono la possibilità di
memorizzare i dati relativi ai libri (titolo,
autore, casa editrice, anno, argomento,
copie disponibili). Si devono poter cercare
i libri a partire dal titolo, dall’autore,
dall’argomento e dall’editore. [...]”
Individuazione entità in gioco
• Libro
–
–
–
–
–
–
Titolo
Anno
Copie disponibili
Autore
Casa editrice
Argomento
• Libro
– Titolo
– Anno
– Copie disponibili
• Autore
• Casa editrice
• Argomento
Perchè tutte queste entità ?
• Integrità dei dati
– un autore dovrebbe avere lo stesso nome per ogni
suo libro; eventuali modifiche non dovrebbero essere
apportate manualmente ad ogni libro
• Occupazione di memoria
– ripetere nome, cognome ed eventualmente altri dati
per ogni libro scritto da un autore è sconveniente
• Problemi in inserimento
– Inserire ogni volta i dati completi di un autore rende
molto probabile un errore
Individuazione relazioni fra entità
Libro
ConArgom.
Argomento
AutoreDi
Autore
EditoDa
Casa Editrice
Cardinalità delle relazioni
Libro
(1,n)
ConArgom.
(1,1)
AutoreDi
(0,n)
Autore
(1,1)
EditoDa
(0,n)
Casa Editrice
• Un Libro deve avere almeno un Autore
(0,n)
Argomento
• Un Libro deve avere una Casa Editrice
• Un Autore può aver scritto più Libri
Cardinalità delle relazioni
Tre tipologie in base alla cardinalità massima delle
occorrenze di Entità nella relazione:
1 a 1: ad un’occorrenza di un’Entità corrisponde esattamente
un’occorrenza di Entità
(Persona e Dipartimento nella relazione Direttore)
1 a molti: ad un’occorrenza di un’Entità possono corrispondere
più occorrenze di Entità ma non viceversa
(Libro e CasaEditrice nella relazione Editoda)
molti a molti: ad un’occorrenza di un’Entità possono
corrispondere più occorrenze di Entità e viceversa
(Libro e Argomento nella relazione conArgomento)
Individuazione attributi
Anno
Copie
Libro
Titolo
(1,n)
Cognome
(1,1)
AutoreDi
(0,n)
(1,1)
Nome
Autore
Tel
Numero albo
ConArgom.
EditoDa
(0,n)
Casa Editrice
• Titolo è una stringa (una sequenza di caratteri)
(0,n)
Argomento
• Anno è un numero intero (si potrebbero
specificare dei vincoli)
• Copie è un numero intero ≥ 0
• Tutti i suddetti non possono essere vuoti
Cardinalità attributi
Anno
Copie
Libro
Titolo
(1,n)
Cognome
(1,1)
AutoreDi
(0,n)
Autore
(1,n)
(1,1)
Nome
(0,1)
Tel
Numero albo
ConArgom.
(0,n)
Argomento
EditoDa
(0,n)
Casa Editrice
Valore minimo e massimo di valori associati ad ogni
occorrenza di entità o relazione
• Nella maggior parte dei casi è (1,1), quindi
omessa
• Card minima = 0 opzionale, = 1 obbligatorio
• Card massima = N multivalore
Determinazione delle chiavi (1)
Anno
Copie
Libro
Titolo
(1,n)
Cognome
(1,1)
AutoreDi
(0,n)
Autore
(1,n)
(1,1)
Nome
(0,1)
Tel
Numero albo
ConArgom.
(0,n)
EditoDa
(0,n)
Casa Editrice
• Non possono esistere più libri aventi
lo stesso titolo
Argomento
• Non possono esistere più autori con
stesso cognome e nome
Determinazione delle chiavi (2)
Anno
Copie
Libro
(1,n)
Cognome
(1,1)
AutoreDi
(0,n)
Autore
(1,n)
(1,1)
Nome
(0,1)
Tel
Titolo
Numero albo
ConArgom.
(0,n)
Argomento
EditoDa
(0,n)
Casa Editrice
• Non possono esistere più libri aventi lo
stesso titolo, autore ed anno di
pubblicazione
Determinazione delle chiavi (3)
Anno
Copie
Libro
(1,n)
Cognome
(1,1)
AutoreDi
(0,n)
Autore
(1,n)
(1,1)
Nome
(0,1)
Tel
Titolo
Numero albo
ConArgom.
(0,n)
Argomento
EditoDa
(0,n)
Casa Editrice
• Non possono esistere più libri aventi lo
stesso titolo, autore, casa editrice ed
anno di pubblicazione
Riassunto modello ER
Costrutti
• Entità
• Relazioni
• Attributi
Vincoli di integrità
• Cardinalità delle Relazioni/degli Attributi
• Identificatori di Entità (chiavi)
Ulteriori esempi ER
IdSoc
Società
IdProd
(0,n)
Fornisce
IdCli
(1,n)
Prodotto
(0,n)
Cliente
DataIncarico
IdDip
Dipartimento
(1,1)
Direttore
(0,1)
IdPers
Persona
Traduzione in un modello logico (modello relazionale)
Modello Relazionale
Modello logico
Fornisce una serie di costrutti per descrivere la realtà di interesse
Molto meno ricco del modello ER, più vicino alla reale
rappresentazione dei dati interna all’elaboratore
Alla base di un’altissima percentuale dei database presenti in
commercio (Access, ...)
Esistono definizioni formali, oltre ad una teoria rigorosa a riguardo
Costrutti del modello
• elemento fondamentale è la relazione,
informalmente una tabella
• ogni colonna ha associato un nome (attributo)
ed un dominio (insieme di valori che può
assumere l’attributo)
• Ogni riga di questa tabella viene detta tupla
Costrutti del modello
LIBRO
Titolo
Anno
Casa Editrice
Il giro di boa
2003
Sellerio
Il sogno di
Scipione
2003
Longanesi
I delitti di via
Medina Sidonia
1996
Sellerio
LIBRO(Titolo, Anno, Casa Editrice)
Traduzione da modello ER a modello Relazionale
Entità ed Attributi
• Ad un’Entità corrisponde una relazione (tabella)
• Agli Attributi dell’Entità corrispondono gli attributi
nella relazione
• Gli Attributi chiave dell’Entità corrispondono agli
attributi chiave nella relazione
Entità ed Attributi
Anno
Copie
Libro
Titolo
LIBRO(Titolo, Anno, Copie)
Le Relazioni
• Diversi elementi del modello ER non hanno un
corrispettivo in quello relazionale ed in particolare le
Relazioni
• Una Relazione può essere realizzata nel modello
relazionale tramite il meccanismo di chiave esterna
• Un attributo (o un gruppo di attributi) chiave di una
tabella, se trasportato in un’altra viene detto chiave
esterna e permette di realizzare una relazione fra le due
tabelle
• Praticamente una relazione viene individuata per via
dell’uguaglianza dei valori di alcuni attributi
Le Relazioni
Titolo
IdCasaEditrice
Anno
Libro
(1,1)
EditoDa
(0,n)
Nome
CasaEditrice
Uguaglianza di valori
Libro
Titolo
Anno
Casa Editrice
Il giro di boa
2003
1
Il sogno di
Scipione
2003
2
I delitti di via
Medina Sidonia
1996
1
CasaEditrice
IdCasaEditrice
Nome
1
Sellerio
2
Longanesi
Relazioni 1 a 1
IdOfferta
Offerta
IdOrdine
(0,1)
Ordine
(1,1)
BuonoOrdine
• Questo genere di relazione viene tradotta
inserendo la chiave di una delle entità
coinvolte nell’insieme degli attributi dell’altra
• C’è un grado di libertà, ma la scelta ha delle
conseguenze ‘non funzionali’
IdOfferta
Offerta(IdOfferta)
BuonoOrdine(IdOrdine, IdOfferta)
1
3
IdOrdine
IdOfferta
1
1
2
3
Relazioni 1 ad n
Titolo
Nome
Libro
(1,1)
EditoDa
(0,n)
CasaEditrice
• Questo genere di relazione viene tradotta
inserendo la chiave dell’entità del lato n,
nell’insieme degli attributi dell’altra
• In questo caso la traduzione è rigida, non ci
sono gradi di libertà
Libro(Titolo, NomeEditore)
Titolo
NomeEditore
xx
aa
yy
aa
Nome
CasaEditrice(Nome)
aa
bb
Relazioni n ad m
Anno
Libro
Copie
Cognome
(1,n)
AutoreDi
(0,n)
Nome
Autore
Titolo
• Questo genere di relazione viene tradotta
introducendo un’ulteriore relazione di supporto,
avente come chiave l’insieme degli attributi
componenti le chiavi delle altre entità coinvolte
• Questa nuova relazione non ha un significato
concettuale, ma è necessaria per poter utilizzare
il meccanismo di chiave esterna
Relazioni n ad m
Anno
Libro
Copie
Cognome
(1,n)
AutoreDi
(0,n)
Nome
Autore
Titolo
Libro(Titolo, Anno, Copie)
Autore(Cognome, Nome)
Titolo
Anno
Copie
xx
2003
1
yy
2002
2
Cognome
Nome
Cc
aa
Dd
bb
Autore-Libro(Cognome,Nome, Titolo)
Cognome
Nome
Titolo
Cc
aa
xx
Cc
aa
yy
Dd
bb
xx
Riassunto: modello relazionale e traduzione
• Entità  relazioni (tabelle)
• Attributi di entità  attributi delle relazioni (colonne
delle tabelle)
• Relazioni 1 ad 1  inserimento della chiave di una
delle relazioni nell’altra
• Relazioni 1 ad n  inserimento della chiave del lato n
nella relazione corrispondente a Entità del lato 1
• Relazioni n ad m  creazione di una nuova relazione
avente come chiave l’insieme delle chiavi delle Entità
coinvolte nella Relazione originale
Esempi (1)
IdSoc
Società
IdProd
(0,n)
Fornisce
IdCli
(1,n)
Prodotto
(0,n)
Cliente
•
•
•
•
Società (IdSoc)
Prodotto (IdProd)
Cliente (IdCli)
Fornitura(IdSoc, IdProd, IdCli) (IdSoc chiave esterna
verso Società, IdProd chiave esterna verso Prodotto,
IdCli chiave esterna verso Cliente)
Esempi (2)
DataIncarico
IdDip
Dipartimento
(1,1)
Direttore
(0,1)
IdPers
Persona
• Persona (IdPers)
• Dipartimento (IdDip, IdDiret, DataIncarico)
(IdDiret chiave esterna verso Persona)
Interrogazione di una base dati (SQL SELECT)
SQL SELECT: elementi di base
• Structured Query Language  linguaggio
dichiarativo per utilizzo di basi dati
relazionali
– Data Description Language (definizione
schema)
– Data Manipulation Language (manipolazione
dati)
– Query Langage (interrogazione della base
dati)  Comando SELECT
Cosa si cerca
SELECT “Cosa”
FROM “Dove lo trovo”
WHERE “A queste condizioni”
• “Cosa” intende i nomi degli attributi che si
intendono ottenere dalla ricerca
• In caso di ambiguità, cioè lo stesso nome di
attributo è presente in più tabelle, bisogna
chiarire a quale ci si riferisce usando la forma
Tabella.attributo
• Per selezionare tutti gli attributi di una tabella di
può usare la forma Tabella.*
Da dove si cerca
SELECT “Cosa”
FROM “Dove lo trovo”
WHERE “A queste condizioni”
• “Dove lo trovo” intende l’insieme delle
tabelle che contengono gli attributi
specificati nella clausola “cosa” o utilizzati
in quella che specifica le condizioni
Criteri di selezione
SELECT “Cosa”
FROM “Dove lo trovo”
WHERE “A queste condizioni”
• Le condizioni possono essere divise in
– semantiche (correlate al significato della ricerca)
– di join (correlate alla necessità di chiarire le relazioni
fra le tabelle)
• Si possono (devono) combinare con operatori
booleani (es. le condizioni di join devono essere
in AND fra di loro)
Tenere conto delle relazioni
Istruttore (IdIstr, Nome, Cognome, Indirizzo, N-tel)
Scheda (IdScheda, Difficoltà, Istr) (Istr chiave esterna verso Istruttore)
Esercizi-schede (IdScheda, IdEserc, Ripetizioni)
Esercizio (IdEserc, Nome, Descr)
Qualora la query coinvolga informazioni provenienti da tutte le
tabelle, la clausola WHERE dovrà indicare chiaramente che:
• Istr in Scheda dev’essere uguale a IdIstr in Istruttore
• IdScheda in Esercizi-schede dev’essere uguale a quello in Scheda
• IdEserc in Esercizio dev’essere uguale a quello in Esercizi-schede
Esempio di Query
• Selezionare gli istruttori che abbiano
assegnato l’esercizio ‘addominali’
• SELECT Istruttore.*
FROM Istruttore i, Scheda s, Esercizischede es, Esercizio e
WHERE e.Nome=‘addominali’ AND
e.IdEserc=esIdEserc AND
es.IdScheda=s.IdScheda AND
s.Istr=i.IdIstr;
Riassunto: SQL SELECT
• SELECT
– “WHAT”: Cosa si cerca
– Clausola FROM: “Dove”, da quali tabelle
– Clausola WHERE: “A che condizioni”,
semantiche e di join