Esercizi
Progettazione ER
Un database universitario contiene informazioni
riguardanti i professori (identificati dal codice
fiscale) e informazioni relative ai corsi (identificati
da un codice) tenuti dai professori.
Disegnare gli schemi ER che descrivono le seguenti
situazioni:
1) I professori possono insegnare lo stesso corso in
più semestri ed ogni corso (per semestre) deve
essere registrato
num
semestre
cf
prof
ins
cod
corso
1
Progettazione ER
2) I professori possono insegnare lo stesso
corso in più semestri e solo il più recente
semestre deve essere registrato
sem
cf
prof
ins
cod
corso
2
Progettazione ER
3) Ogni professore insegna in qualche corso
sem
cf
prof
(1,N)
ins
cod
corso
3
Progettazione ER
4) Ogni professore insegna un solo corso
sem
cf
prof
(1,1)
ins
cod
corso
4
Progettazione ER
5) Ogni professore insegna in un corso solo
ed ogni corso deve essere tenuto da
almeno un professore
sem
cf
prof
(1,1)
ins
cod
(1,N)
corso
5
SQL
Si consideri il seguente schema relazionale:
Emp(eid:integer, ename:char, age:integer,
salary:real)
Works(eid:integer, did:integer,
pct_time:integer)
Dept(did:integer, dname:char, budget:real,
managerid:integer)
6
SQL
Dare un esempio di chiave esterna che
coinvolge la relazione Dept
L’attributo did in Works si riferisce all’attributo did in Dept
7
SQL
Si scrivano le istruzioni SQL per definire le
relazioni Emp, Works e Dept
Create table Emp (eid integer,
ename char(10),
age integer,
salary real,
primary key (eid))
8
SQL
create table Works (eid integer,
did integer,
pct_time integer,
primary key (eid, did),
foreign key (did) references Dept,
foreign key (eid) references Emp,
on delete cascade)
9
SQL
Create table Dept (did integer,
budget real,
managerid integer,
primary key (did),
foreign key (managerid) references Emp.eid,
on delete set null)
10
SQL
Definire Dept in modo che ogni dipartimento
abbia un manager
Create table Dept (did integer,
budget real,
managerid integer not null,
primary key (did),
foreign key (managerid) references Emp,
on delete set null)
11
SQL
In riferimento agli schemi di relazione
appena definiti, si esprima in SQL la
seguente interrogazione:
“Trovare i nomi e l’età di tutti gli impiegati che
lavorano nel dipartimento 2”
Select E.ename, E.age
From Emp E, works W
Where E.eid=W.eid and W.did=2
12
SQL
In riferimento agli schemi di relazione
appena definiti, si esprima in SQL la
seguente interrogazione:
“Trovare i nomi e l’età di tutti gli impiegati che
lavorano sia nel dipartimento Hardware che
nel dipartimento Software”
Select E.ename, E.age
From Emp E, works W1, Works W2
Where E.eid=W1.eid and W1.did=‘Hardware’ and
E.eid=W2.eid and W2.did=‘Software’
13
SQL
si considerino i seguenti schemi di relazione
piloti(id:int, nome:char, stipendio:int,età:int)
aerei(id:int, capienza:int, autonomia:int)
partenze(pilota:int, aereo:int, data:date)
si esprima in SQL la seguente
interrogazione: “Trovare i nomi dei piloti che
partono entro il 25 aprile ‘04”
14
SQL
soluzione
select
from
where
piloti.nome
piloti, partenze
partenze.pilota=piloti.id and
partenze.data<=24/4/04
15
SQL
In riferimento agli schemi relazionali definiti
nella slide precedente,
si esprima in SQL la seguente
interrogazione: “Trovare tutti gli identificativi
degli aerei con capienza maggiore di 100
che partono il 17 maggio ‘04”
16
SQL
soluzione:
select
from
where
aerei.id
aerei, partenze
aerei.id=partenze.aereo and
aerei.capienza>100 and
partenze.data=17/5/04
17
SQL
sempre in riferimento agli stessi schemi
relazionali, esprimere in SQL la seguente
interrogazione:
“Trovare tutti i nomi dei piloti che partono
dopo il 17 maggio ’04 con un aereo avente
capienza maggiore di 100 posti”
18
SQL
soluzione:
select
piloti.nome
from
piloti, aerei, partenze
where
piloti.id=partenze.pilota and
aerei.id=partenze.aereo and
partenze.data>17/5/04 and
aerei.capienza>100
19
SQL
Trovare l’età media dei piloti che pilotano
aerei che partono entro il 25 aprile ’04
select
from
where
avg(piloti.età) as etàmedia
piloti, partenze
piloti.id=partenze.pilota and
partenze.data<25/4/04
20
Progettazione ER
Si considerino le seguenti informazioni riguardanti un
DB universitario:
i professori sono univocamente identificati dal
codice fiscale, inoltre posseggono un nome, età,
grado ed attività di ricerca
i progetti sono identificati da un numero, inoltre
posseggono uno sponsor, data d’inizio, data di fine
e budget
gli studenti laureati sono univocamente identificati
dal codice fiscale, nome, età e corso di studi
inoltre…
21
Progettazione ER
Ogni progetto è gestito da un professore
Ad ogni progetto lavorano uno o più professori
I professori possono gestire e\o lavorare a più
progetti
Ad ogni progetto lavorano uno o più studenti
Quando uno studente lavora ad un progetto, un
professore supervisiona lo studente
E poi…
22
Progettazione ER
I dipartimenti sono identificati da un numero, inoltre
hanno un nome ed un ufficio principale
Ogni dipartimento è gestito da un professore
I professori lavorano in uno o più dipartimenti e, per
ogni dip., viene registrata la percentuale di tempo
di lavoro
Gli studenti hanno un dipartimento di appartenenza
Ogni studente ha uno studente più anziano che lo
aiuta nella scelta dei corsi
23
Progettazione ER
inizio
sponsor
età
cf
fine
pid
grado
budget
ricerca
(1,N)
prog
lavoro
prof
(1,N)
(1,1)
gestione
gest
lavoro
Lavoro
Prog.
(1,1)
super
pct
dip
N°
nome
(1,1)
(1,1)
Uff.
aiuto
studente
Appart.
nome
cf
età
corso
24
SQL
Si consideri il seguente schema di database:
Studente(snum, nome, età, anno)
Classe(nome, aula, ora, pid)
Iscritto(snum, nome)
Professore(pid, nome)
25
SQL
trovare nome ed età degli studenti iscritti al
corso di paleontologia
Select S.nome, S.età
From studente S, iscritto I
Where S.snum=I.snum
and I.nome=‘paleontologia’
26
SQL
In alternativa:
Select studente.nome, studente.età
From studente S
Where S.snum in (select I.snum
from I
where I.nome=‘paleontologia’)
27
SQL
Trovare i nomi di tutti gli studenti del primo
anno iscritti ad un corso tenuto dal prof.
Rossi
Select distinct S.nome
From
studente S, corso C, iscritto I, professore P
Where
S.snum=I.snum and I.nome=C.nome and
C.pid=P.pid and P.nome=‘Rossi’ and S.anno=1
28
SQL
Oppure…
Select distinct S.nome
From
studente S
Where
S.anno=1 and S.snum in (select I.snum
from iscritto I
where I.nome in (select C.nome
from corso C
where C.pid in (select P.pid
from professore P
where P.nome = ‘Rossi’)))
29
SQL
Trovare l’età media degli studenti, suddivisi
per anno
Select
anno, avg(età)
From
studente S
Group by anno
30
SQL
Trovare gli studenti che non sono iscritti ad
alcun corso
Select S.nome
From studente S
Where S.snum not in (select I.snum
from iscritto I)
31
SQL
Trovare l’età degli studenti piu’ anziani che
sono iscritti al secondo anno oppure che
seguono un corso tenuto dal prof. Rossi
Select max(S.age)
From studente S
Where S.anno=2 or S.snum in (select I.snum
from iscritto I, corso C, Professore P
where I.nome=C.nome and C.pid=P.pid and
P.nome=‘Rossi’)
32
Progettazione ER
Progettare lo schema concettuale di un db
che contenga informazioni relative a
impiegati (identificati da un codice ed aventi
un salario ed un numero di telefono),
dipartimenti (identificati da un codice ed
aventi un nome ed un budget) e figli degli
impiegati (aventi un nome ed età).
Gli impiegati lavorano nei dipartimenti. Ogni
dipartimento è gestito da un impiegato.
Ogni bambino è univocamente determinato
dal nome e dal codice del genitore.
33
Progettazione ER
cd
sal
num
tel
nome
budget
direz
dip
impiegato
lavoro
figlio
nome
bambino
età
34