Esercizi

2

Progettazione ERUn 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

prof corsoins

semestrecf

num

cod

3

Progettazione ER

2) I professori possono insegnare lo stesso corso in più semestri e solo il più recente semestre deve essere registrato

prof corsoins

cf sem cod

4

Progettazione ER

3) Ogni professore insegna in qualche corso

prof corsoins

cf sem cod

(1,N)

5

Progettazione ER

4) Ogni professore insegna un solo corso

prof corsoins

cf sem cod

(1,1)

6

Progettazione ER

5) Ogni professore insegna in un corso solo ed ogni corso deve essere tenuto da almeno un professore

prof corsoins

cf sem cod

(1,1) (1,N)

7

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)

8

SQL

Dare un esempio di chiave esterna che coinvolge la relazione Dept

L’attributo did in Works si riferisce all’attributo did in Dept

9

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))

10

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)

11

SQL

Create table Dept (did integer, budget real, managerid integer, primary key (did), foreign key (managerid) references Emp.eid, on delete set null)

12

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)

13

SQLIn 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.ageFrom Emp E, works WWhere E.eid=W.eid and W.did=2

14

SQLIn 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.ageFrom Emp E, works W1, Works W2Where E.eid=W1.eid and W1.did=‘Hardware’ and

E.eid=W2.eid and W2.did=‘Software’

15

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”

16

SQL

soluzione

select piloti.nomefrom piloti, partenzewhere partenze.pilota=piloti.id and

partenze.data<=24/4/04

17

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”

18

SQL

soluzione:

select aerei.idfrom aerei, partenzewhere aerei.id=partenze.aereo and

aerei.capienza>100 and partenze.data=17/5/04

19

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”

20

SQL

soluzione:select piloti.nomefrom piloti, aerei, partenzewhere piloti.id=partenze.pilota and

aerei.id=partenze.aereo and partenze.data>17/5/04 and aerei.capienza>100

21

SQL

Trovare l’età media dei piloti che pilotano aerei che partono entro il 25 aprile ’04

select avg(piloti.età) as etàmediafrom piloti, partenzewhere piloti.id=partenze.pilota and

partenze.data<25/4/04

22

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…

23

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…

24

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

25

Progettazione ER

prof

dip

gestlavoro

cf

Uff.nomeN°

pct

etàgrado

ricerca

(1,N)

(1,1)

Appart.

super

gestione

lavoro prog

studente

LavoroProg.

aiuto

cf età corso

nome

(1,1)

(1,1)

(1,1)

(1,N)

pidbudget

fine

inizio

sponsor

26

SQL

Si consideri il seguente schema di database:

Studente(snum, nome, età, anno)Classe(nome, aula, ora, pid)Iscritto(snum, nome)Professore(pid, nome)

27

SQL

trovare nome ed età degli studenti iscritti al corso di paleontologia

Select S.nome, S.etàFrom studente S, iscritto IWhere S.snum=I.snum

and I.nome=‘paleontologia’

28

SQL

In alternativa:

Select studente.nome, studente.etàFrom studente SWhere S.snum in (select I.snum

from I where I.nome=‘paleontologia’)

29

SQL

Trovare i nomi di tutti gli studenti del primo anno iscritti ad un corso tenuto dal prof. Rossi

Select distinct S.nomeFrom studente S, corso C, iscritto I, professore PWhere S.snum=I.snum and I.nome=C.nome and

C.pid=P.pid and P.nome=‘Rossi’ and S.anno=1

30

SQL

Oppure…

Select distinct S.nome From studente SWhere 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’)))

31

SQL

Trovare l’età media degli studenti, suddivisi per anno

Select anno, avg(età)From studente SGroup by anno

32

SQL

Trovare gli studenti che non sono iscritti ad alcun corso

Select S.nomeFrom studente SWhere S.snum not in (select I.snum

from iscritto I)

33

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 SWhere 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’)

34

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.

35

Progettazione ER

impiegato dip

bambino

figlio

lavoro

direz

cd sal telbudget

nomenum

etànome