il modello relazionale - Fondamenti di Basi di Dati

IL MODELLO RELAZIONALE
!1
Definizione: I meccanismi per definire una base di dati con il modello
relazionale sono l’ennupla e la relazione:
• int, real, boolean e string sono tipi primitivi;
• se T1, ..., Tn sono tipi primitivi, e A1, ..., An sono etichette distinte, dette
attributi, allora (A1 :T1, ..., An :Tn) è un tipo ennupla di grado n. L’ordine
degli attributi non è significativo.
• se T è un tipo ennupla,
•
{T} è il tipo relazione e
•
R:{T} è lo schema della relazione R;
• lo schema di una base di dati è un insieme di schemi di relazione Ri:{Ti};
• un’istanza di uno schema R:{T} è un insieme finito di ennuple di tipo T.
Per brevità invece di R:{T} si scriverà R(T).
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ATTENZIONE
Due schemi di relazioni: R(A :int, B :string)
!2
e
S(B :string, A :int)
Quando due tipi ennupla, due ennuple, due tipi relazioni o due relazioni sono uguali?
Due tipi ennupla sono uguali se e solo se hanno uguale grado, gli attributi e il tipo
degli attributi con lo stesso nome.
Due ennuple dello stesso tipo sono uguali se e solo se hanno lo stesso insieme di
coppie (Attributo, Valore)
Due tipi relazioni sono uguali se hanno lo stesso tipo ennupla.
Due relazioni dello stesso tipo sono uguali se hanno ennuple uguali.
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ESEMPI
!3
Due schemi di relazioni: R(A :int, B :string)
e
S(B :string, A :int)
Tipo ennupla di R ?
(A :int, B :string)
Tipo di R ?
{(A :int, B :string)}
Tipo ennupla di S ?
(B :string, A :int)
Tipo di S ?
{(B :string, A :int)}
Tipo ennupla di R = Tipo ennupla di S ?
Tipo di R = Tipo di S ?
Una ennupla di R ?
Una ennupla di S ?
(A := 3, B := ‘caio’)
(B := ‘caio’, A := 3)
(A := 3, B := ‘caio’) = (B := ‘caio’, A := 3) ?
{ (A := 3, B := ‘caio’),
(A := 4, B := ‘tizio’) } = { (B := ‘tizio’, A := 4),
(B := ‘caio’, A := 3)} ?
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ISTANZA DI RELAZIONE
!4
Schema di relazione: R(A :int, B :string)
Un’istanza di R è un insieme finito di ennuple di tipo (A :int, B :string)
R = { (A := 10, B := ”Mario”)
, (A := 18, B := ”Giovanna”)
, (A := 77, B := ”Luca”)
, (A := 18, B := ”Francesco”)
, (A := 14, B := ”Carla”)
}
R
A
B
10
Mario
18
Giovanna
77
Luca
18 Francesco
14
4. Il modello relazionale
Carla
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
CHIAVI ED ASSOCIAZIONI
!5
Chiave
Chiave primaria: una delle chiavi, in genere con meno attributi
Studenti(Matricola :int, Nome :String, Indirizzo :string)
Esempi di chiavi: (Matricola) e (Nome,Indirizzo)
Chiave esterna
Esami(Materia :string, Candidato
Candidato* :int, Voto :int, Data :string)
più precisamente
Esami(Materia :string, Candidato *Studenti :int, Voto :int, Data :string)
Associazioni ?
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
HaSostenuto
ESEMPIO
Studenti
Esami
!6
Schema:
Studenti(Nome :string, Matricola :int, Provincia :string, AnnoNascita :int)
Esami(Materia :string, Candidato* :int, Voto :int, Data :string)
Relazioni
Studenti
Studenti
Candidato
Esami
4. Il modello relazionale
Esami
Nome
Matricola
Provincia
AnnoNascita
Isaia
171523
PI
1982
Rossi
167459
LU
1980
Bianchi
179856
LI
1981
Bonini
175649
PI
1982
Materia
Candidato
Voto
Data
BD
171523 20
12/01/05
ALG
167459 30
15/09/05
MP
171523 30
25/10/05
IS
167459 20
10/10/05
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ESEMPIO: ALTRE SOLUZIONI
!7
Studenti(Nome :string, Matricola :int, Provincia :string, AnnoNascita :int)
Esami(Numero :int, Materia :int, Candidato* :int, Data :string, Voto :int)
Studenti(Nome :string, Matricola :int, Provincia :string, AnnoNascita :int, Esame* :int)
Esami(Numero :int, Materia :string, Data :string, Voto :int)
Studenti(Nome :string, Matricola :int, Provincia :string, AnnoNascita :int)
Esami(Numero :int, Materia :string, Data :string, Voto :int)
StudentiEsami(Esame* :int, Candidato*:int)
Quale preferire?
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
TRASFORMAZIONE DI SCHEMI A OGGETTI IN RELAZIONALI
Editori
Editori
Libri
Prestiti
Autori
Utenti
Libri
!8
Prestiti
Autori-Libri
Autori
4. Il modello relazionale
Utenti
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
LE SOTTOCLASSI
!9
Persone
CF
<<PK>>
Nome
AnnoNascita
Persone
<<PK>>
CF
Nome
AnnoNascita
Diventa
Studenti
<<PK1>>
<<FK(Persone)>>
Matricola <<PK2>>
CdL
CF
Studenti
Matricola <<PK>>
CdL
Oppure
Oppure
Persone
<<PK>>
CF
Nome
AnnoNascita
ÈStudente
Matricola
CdL
4. Il modello relazionale
Persone
<<PK>>
CF
Nome
AnnoNascita
Studenti
<<PK1>>
CF
Nome
AnnoNascita
Matricola <<PK2>>
CdL
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
GLI ATTRIBUTI MULTIVALORE
!10
Persone
CF
<<PK>>
Nome
LingueParlate: seq string
CF
Nome
CF
Nome
4. Il modello relazionale
Persone
<<PK>>
Persone
<<PK>>
CF
Lingua
CF
Lingua
LingueParlate
<<PK>> <<FK(Persone)>>
<<PK>>
LingueParlate
<<PK>> <<FK(Persone)>>
<<PK>> <<FK(Lingue)>>
Lingue
Lingua <<PK>>
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
AZIENDA: SCHEMA CONCETTUALE
Afferisce
Impiegati
Codice <<PK>>
NomeCognome
Sesso
AnnoNascita
Familiari : seq [ Nome
Sesso
AnnoNascita
RelazioneParentela
]
Direttori
AnnoDiNomina
Dipendenti
4. Il modello relazionale
Partecipa
Impegno
Responsabili
AnnoDiNomina
CoordinatoDa
Dirige
Dipartimenti
Numero <<PK>>
Nome
Città
!11
Gestisce
Progetti
NumeroP <<PK>>
NomeP
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
Afferisce
Impiegati
Codice <<PK>>
NomeCognome
Sesso
AnnoNascita
Familiari : seq [ Nome
Sesso
AnnoNascita
RelazioneParentela
]
Direttori
AnnoDiNomina
Dipendenti
AZIENDA: SCHEMA LOGICO
Gestisce
Progetti
NumeroP <<PK>>
NomeP
Impiegati
Codice <<PK>>
NomeCognome
Sesso
AnnoNascita
Afferisce <<FK(Dipartimenti)>>
Afferisce
Codice
Direttori
Codice <<PK>>
<<FK(Impiegati)>>
AnnoDiNomina
Dirige <<FK(Dipartimenti)>>
Dirige
4. Il modello relazionale
!12
Responsabili
AnnoDiNomina
CoordinatoDa
Dirige
Dipartimenti
Numero <<PK>>
Nome
Città
Partecipa
Impegno
Familiari
CapoFamiglia <<PK>>
Capo
<<FK(Impiegati)>>
Famiglia Nome
<<PK>>
Sesso
AnnoNascita
RelazioneParentela
Codice
Responsabili
Codice <<PK>>
<<FK(Impiegati)>>
AnnoDiNomina
Codice
Dipendenti
Codice
<<PK>>
<<FK(Impiegati)>>
CoordinatoDa <<FK(Responsabili)>>
Dipartimenti
Numero <<PK>>
Nome
Città
Impiegato
Partecipazioni
Impiegato <<PK>>
<<FK(Impiegati)>>
Progetto <<PK>>
<<FK(Progetti)>>
Impegno
Progetto
CoordinatoDa
Progetti
NumeroP <<PK>>
NomeP
A. Albano,
G. Ghelli, R.Orsini
GestitoDa
Fondamenti di basi<<FK(Dipartimenti)>>
di dati, Zanichelli, 2005
GestitoDa
LINGUAGGI RELAZIONALI
!13
Algebra relazionale: insieme di operatori su relazioni che danno come risultato
relazioni. Non si usa come linguaggio di interrogazione dei DBMS ma come
rappresentazione interna delle interrogazioni.
Calcolo relazionale: linguaggio dichiarativo di tipo logico dal quale è stato
derivato l'SQL.
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALGEBRA RELAZIONALE
Proiezione senza duplicati
Semantica
!14
(π):
πA1 ,A2 ,...,An (R)
Qual è il tipo del risultato?
{(A1 :T1, A2 :T2, ..., An :Tn)}
Se R ha n elementi quanti ne ha il risultato?
Proiezione generalizzata
πExp1 AS A1 ,Exp2 AS A2 ,...,Expn AS An (R)
Qual è il tipo del risultato?
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ESEMPI: Proiezione
Studenti
Nome
!15
Matricola
Provincia
AnnoNascita
Isaia
171523
PI
1982
Rossi
167459
LU
1980
Bianchi
179856
LI
1981
Bonini
175649
PI
1982
Trovare il nome, la matricola e la provincia degli studenti
π Nome, Matricola, Provincia(Studenti)
π Provincia,(Studenti) ?
Qual è il tipo dei risultati?
4. Il modello relazionale
Nome
Matricola
Provincia
Isaia
171523
PI
Rossi
167459
LU
Bianchi
179856
LI
Bonini
175649
PI
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALGEBRA RELAZIONALE: (cont.)
Restrizione (selezione) (σ):
!16
Semantica
σCondizione (R)
Qual è il tipo del risultato? Se R ha n elementi quanti ne ha il risultato?
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ESEMPI: Restrizione
Studenti
Nome
!17
Matricola
Provincia
AnnoNascita
Isaia
171523
PI
1982
Rossi
167459
LU
1980
Bianchi
179856
LI
1981
Bonini
175649
PI
1982
Trovare i dati degli studenti di Pisa:
σ Provincia = ’PI' (Studenti)
Nome
Qual è il tipo del risultato?
4. Il modello relazionale
Matricola
Provincia
AnnoNascita
Isaia
171523
PI
1982
Bonini
175649
PI
1982
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ESEMPI: Restrizione
Studenti
Nome
!18
Matricola
Provincia
AnnoNascita
Isaia
171523
PI
1982
Rossi
167459
LU
1980
Bianchi
179856
LI
1981
Bonini
175649
PI
1982
Trovare il nome, la matricola e l’anno di nascita degli studenti di Pisa:
π Nome,Matricola,AnnoNascita (σ Provincia = ’PI' (Studenti))
Nome
Qual è il tipo del risultato?
4. Il modello relazionale
Matricola
AnnoNascita
Isaia
171523
1982
Bonini
175649
1982
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ESERCIZIO
Studenti
!19
Nome
Matricola
Provincia
AnnoNascita
Isaia
171523
PI
1982
Rossi
167459
LU
1980
Bianchi
179856
LI
1981
Bonini
175649
PI
1982
Trovare il nome degli studenti di Pisa:
π Nome (σ Provincia = ’PI' (Studenti))
è equivalente a: σ
Provincia = ’PI'
(π Nome(Studenti))
è equivalente a: σ
Provincia = ’PI'
(π Nome,Provincia(Studenti))
è equivalente a: π
Nome(σ Provincia = ’PI'
4. Il modello relazionale
?
?
(π Nome,Provincia(Studenti)))
?
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALGEBRA RELAZIONALE (cont.)
Unione (∪):
R∪S
!20
Con R e S dello stesso tipo
Semantica
Differenza (–):
R−S
Con R e S dello stesso tipo
Semantica
Qual è il tipo del risultato? Se R e S hanno n elementi quanti ne ha il risultato?
Se t1 è un'ennupla non in R , allora
R = (R ∪ {t1 }) − {t1 })
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALGEBRA RELAZIONALE: (cont.)
Prodotto (×):
a
A
a1
A1
R×S
×
a2 A2
Qual è il tipo del risultato?
b
B
b1
B1
b2
B2
b3
B3
!21
R (A1 :T1, ..., An :Tn) e S(B1 :T1, ..., Bm :Tm)
con attributi diversi
Semantica
=
a
A
b
B
a1
A1
b1
B1
a1
A1
b2
B2
a1
A1
b3
B3
a2
A2
b1
B1
a2
A2
b2
B2
a2
A2
b3
B3
{(a :T1, A :T2, b :T3, B :T4)}
Se R e S hanno n e m elementi, quanti ne ha il risultato?
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALGEBRA RELAZIONALE (cont.)
Qual è il risultato di
!22
Studenti × Esami ?
Trovare il nome degli studenti che hanno superato l’esame di BD con 30
π Nome (σ Materia = ‘BD’ ∧
Voto = 30
(σ Matricola = Candidato (Studenti × Esami) ))
meglio usare la giunzione:
σ R.A = S.D (R × S) =
R ◃▹ S
R.A = S.D
πNome (σMateria = ’BD’∧Voto = 30 (Studenti
4. Il modello relazionale
◃▹
Matricola=Candidato
Esami))
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALGEBRA RELAZIONALE (cont.)
!23
Ridenominazione
δA→B (R)
!
Intersezione
!
R∩S
Giunzione naturale
!
!
4. Il modello relazionale
R ◃▹ S
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALGEBRA RELAZIONALE (cont.)
!24
Raggruppamento (γ):
A1 ,A2 ,...,An γf1 ,f2 ,...fk (R)
dove gli {A1, A2, ..., An} sono un sottoinsieme degli attributi di R e le fi sono
funzioni di aggregazione (min, max, count, sum, avg, ...)
Qual è il tipo del risultato?
{(A1 :T1, A2 :T2, ..., An :Tn, f1 :Tf1, f2 :Tf2, ..., fk :Tfk)}
Se R ha n elementi, quanti ne ha il risultato?
Raggruppamento generalizzato
A1 ,A2 ,...,An γf1 AS F1 ,f2 AS F2 ,...,fk AS Fk (R)
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
SEMANTICA DEL RAGGRUPPAMENTO (γ)
!25
S = A1 ,A2 ,...,An γf1 ,f2 ,...fk (R)
R
A1 ,A2 ,...,An γ... (R)
S
... γf1 ,f2 ,...fk (R)
S(A1 , A2 , . . . , An ,
f1 , f2 , . . . fk )
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ESECUZIONE DEL RAGGRUPPAMENTO
!26
Trovare per ogni candidato il numero degli esami, il voto minimo, massimo e medio
Candidato γCOUNT(*),MIN(Voto),MAX(Voto),AVG(Voto) (Esami)
Materia
Candidato
Voto
Data
Materia
Candidato
Voto
Data
BD
171523 20
12/01/05
ALG
167459 30
15/09/05
ALG
167459 30
15/09/05
IS
167459 20
10/10/05
MP
171523 30
25/10/05
BD
171523 20
12/01/05
IS
167459 20
10/10/05
MP
171523 30
25/10/05
Candidato
4. Il modello relazionale
COUNT(*)
MIN(Voto)
MAX(Voto)
AVG(Voto)
167459 2
20
30
25
171523 2
20
30
25
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
OPERATORI NON DELL’ALGEBRA
!27
Proiezione con duplicati (multiinsiemistica)
b
⇡A
(R)
1 ,A2 ,...,An
Ordinamento
⌧A1 ,A2 ,...,An (R)
Operano su {T} e ritornano un multinsieme: {{T}} (altra notazione: seq T )
Si usano solo sulla radice dell’albero logico
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
TRASFORMAZIONI ALGEBRICHE
!28
Basate su regole di equivalenza fra espressione algebriche
Consentono di scegliere diversi ordini di join e di anticipare proiezioni, restrizioni.
Alcuni esempi con le relazioni R(A, B, C), S(A, D, E), T(D, F, G):
πA (πA,B (R)) ≡ πA (R)
σC1 (σC2 (R)) ≡ σC1 ∧C2 (R)
σCR ∧CS (R ◃▹ S) ≡ σCR (R) ◃▹ σCS (S)
R ◃▹ (S ◃▹ T ) ≡ (R ◃▹ S) ◃▹ T
(R ◃▹ S) ≡ (S ◃▹ R)
σCX (X γ F (R)) ≡ X γ F (σCX (R))
4. Il modello relazionale
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALBERI LOGICI E TRASFORMAZIONI ALGEBRICHE
!29
Consideriamo le relazioni R(A, B, C, D) e S(E, F, G) e l'espressione:
πA,F (σA=100 ∧ F >5 (R
◃▹
A=E
πA,F
πA,F
◃▹
σA=100 ∧ F >5
◃▹
A=E
R
4. Il modello relazionale
S))
S
A=E
πA
πE,F
σA=100
σF >5
R
S
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005
ALBERI LOGICI
!30
τA (πA,M (σC>3 (A γ COU N T (∗) AS C,M AX(B) AS M (σA=100∧F >5 (R
◃▹
A=E
S)))))
τA
πA,M
σC>3
A γ COU N T (∗) AS C,M AX(B) AS M
σA=100 ∧ F >5
◃▹
A=E
R
4. Il modello relazionale
S
A. Albano, G. Ghelli, R.Orsini
Fondamenti di basi di dati, Zanichelli, 2005