Gestione dati e archivi - progettazione ER e SQL

Progettazione ER

Schemi ER per situazioni universitarie

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) Corsi registrati per ogni semestre

I professori possono insegnare lo stesso corso in più semestri ed ogni corso (per semestre) deve essere registrato.

numsemestrecf codprof ins corso

2) Solo il più recente semestre registrato

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

semcf codprof ins corso

3) Ogni professore insegna in qualche corso

Ogni professore insegna in qualche corso.

semcf cod(1,N)prof ins corso

4) Ogni professore insegna un solo corso

Ogni professore insegna un solo corso.

semcf cod(1,1)prof ins corso

5) Ogni corso deve essere tenuto da almeno un professore

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

semcf cod(1,1) (1,N)prof ins corso

SQL

Esempio di chiave esterna

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)

Dare un esempio di chiave esterna che coinvolge la relazione Dept.

L'attributo did in Works si riferisce all'attributo did in Dept.

Definizione delle relazioni SQL

Si scrivano le istruzioni SQL per definire le relazioni Emp, Works e Dept.

Definizione tabella Emp

Create table Emp (eid integer, ename char(10), age integer, salary real, primary key (eid))

Definizione tabella Works

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)

Definizione tabella Dept

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

Definire Dept con manager obbligatorio

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)

Interrogazione 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