Da#Schemi#ER#e#EER##
a#Schemi#Relazionali#
!
ESERCIZI'
2
Esercizio#1 #
Semestre
cf
Professore
Giorgio Giacinto 2011
semestreId
corsoId
InsegnaIn
Corso
#
3
Schema#Relazionale#Esercizio#1#
!  La'sua'traduzione'in'uno'schema'relazionale'è:'
Professore'(cf)
Corso'(corsoId)
Semestre'(semestreId)
InsegnaIn'(cf,semestreId,corsoId)
Giorgio Giacinto 2011
4
Definizione#Tabelle#SQL#Esercizio#1#
!CREATE TABLE Professore(
!CREATE TABLE Corso (
!CREATE TABLE Semestre(
!CREATE TABLE InsegnaIn(
Giorgio Giacinto 2011
cf
PRIMARY KEY
corsoId
PRIMARY KEY
semestreId
PRIMARY KEY
cf
corsoId
semestreId
PRIMARY KEY
FOREIGN KEY
FOREIGN KEY
FOREIGN KEY
CHAR(16),
(cf))
CHAR(10),
(corsoId))
CHAR(10),
(semestreId))
CHAR(16),
CHAR(10),
CHAR(10),
(cf,corsoId,semestreId),
(cf) REFERENCES Professore,
(corsoId) REFERENCES Corso,
(semestreId) REFERENCES
Semestre)
5
Esercizio#2#
età'
id'
Animale'
nome'
N'
Appar>ene'
1'
Specie'
M'
nome'
Se;ore'
1'
Collocata'
Giorgio Giacinto 2011
6
Schema#Relazionale#Esercizio#2#
!  Passo'1'
!  Animale(id,'età)'
!  Specie'(nome)'
!  Se4ore'(nome)'
!  Passo'4'
!  Animale(id,'età,/specie)'
'FK'specie/REF'Specie(nome)'
!  Specie(nome,/se1ore)/
/FK'se1ore/REF'Se;ore(nome)'
Giorgio Giacinto 2011
7
Definizione#Tabelle#SQL#Esercizio#2#
!CREATE TABLE Settore ( nome
CHAR(20),
PRIMARY KEY (nome))
!CREATE TABLE Specie ( nome
CHAR(20),
settore
CHAR(20) NOT NULL,
PRIMARY KEY (nome),
FOREIGN KEY (settore) REFERENCES
Settore(nome))
!CREATE TABLE Animale ( AnimID
CHAR(10),
età
integer,
specie
CHAR(20) NOT NULL,
PRIMARY KEY (AnimID),
FOREIGN KEY (specie) REFERENCES
Specie(nome))
Giorgio Giacinto 2011
8
Esercizio#3#
nome'
Colore'
Targa'
Autoveicolo'
N'
Appar>ene'
1'
Categoria'
N'
applica'
1'
Codice'
Costo'
Tariffa'
Giorgio Giacinto 2011
9
Schema#Relazionale#Esercizio#3#
!  Passo'1'
!  Autoveicolo(Targa,'colore)'
!  Categoria(nome)'
!  Tariffa(codice,/costo)'
!  Passo'4'
!  Autoveicolo(Targa,'colore,/categoria)'
'FK'categoria/REF'Categoria(nome)'
!  Categoria(nome,/tariffa)/
/FK'tariffa/REF'Tariffa(codice)'
Giorgio Giacinto 2011
10
Definizione#Tabelle#SQL#Esercizio#3#
CREATE TABLE Tariffa ( codice
CHAR(10),
costo
REAL,
PRIMARY KEY (codice))
CREATE TABLE Categoria (nome
CHAR(20),
tariffa
CHAR(10) NOT NULL,
PRIMARY KEY (nome),
FOREIGN KEY (tariffa) REFERENCES
Tariffa(codice))
CREATE TABLE Autoveicolo(targa
CHAR(8),
colore
CHAR(15),
categoria CHAR(20) NOT NULL,
PRIMARY KEY (targa),
FOREIGN KEY (categoria)
REFERENCES Categoria(nome))
Giorgio Giacinto 2011
11
Ulteriori#Vincoli#Esercizio#3#
Asserzioni per soddisfare i vincoli di partecipazione
non soddisfabili solo tramite il NOT NULL (ovvero che
ad ogni categoria appartenga almeno una macchina e
che ad ogni tariffa appartenga almeno una categoria).
!CREATE ASSERTION Tariffa_Categoria
CHECK((SELECT COUNT(DISTINCT tariffa)
FROM Categoria)=(SELECT COUNT(*) FROM Tariffa))
!CREATE ASSERTION Categoria_Auto
CHECK((SELECT COUNT(DISTINCT categoria)
FROM Automobile) = (SELECT COUNT(*)
FROM Categoria))
Giorgio Giacinto 2011
12
Esercizio#4##
nome'
CF'
Paziente'
età'
nome'
indirizzo'
CF'
N'
Sceglie'
N'
Prescrive'
M'
Giorgio Giacinto 2011
Medicina'
1'
esperienza'
specializzazione'
Medico'
data'
quan>tà'
13
Esercizio#4#
indirizzo'
nome'
formula'
telefono'
Farmacia'
M'
Medicina'
nome'
N'
Vende'
N'
prezzo'
Produce'
M'
data_inizio'
Contra;o'
data_fine'
N'
testo'
1'
telefono'
Azienda'
supervisore'
nome'
14
Schema#Relazionale#Esercizio#4#
!  Passo'1'
!  Paziente(CF,'nome,/cognome,/indirizzo,/età)'
!  Medico(CF,/nome,/cognome,/esperienza,/
specializzazione)'
!  Farmacia(nome,/indirizzo,/telefono)'
!  Azienda(nome,/telefono)'
!  Passo'2'
!  Medicina(azienda,/nome,/formula)'
'FK'azienda'REF'Azienda(nome)'
Giorgio Giacinto 2011
15
Schema#Relazionale#Esercizio#4#
!  Passo'4'
!  Paziente(CF,'nome,/cognome,/indirizzo,/età,/
nome_medico,/cognome_medico)'
'FK'nome:medico,/cognome_medico''
'
'
'REF'Medico(nome,'cognome)'
!  Passo'5'
!  Vendita(nome_medicina,/azienda,/farmacia,/prezzo)'
'FK'nome_medicina,'azienda'
'
'REF'Medicina(nome,azienda)'
'FK'farmacia'REF'Farmacia(nome)'
!  Contra;o(farmacia,/azienda,/data_inizio,/data_fine,/testo,/
supervisore)'
'FK'farmacia'REF'Farmacia(nome)'
'FK'azienda'REF'Azienda(nome)'
Giorgio Giacinto 2011
16
Schema#Relazionale#Esercizio#4#
!  Passo'7'
!  Prescrizione(nome_paziente,/cognome_paziente,/
nome_medico,/cognome_medico,/data,/quan@tà,/
nome_medicina,/azienda)'
'FK'nome_paziente,'cognome_paziente'
'
'REF'Paziente(nome,cognome)'
'FK'nome_medico,'cognome_medico'
'
'REF'Medico(nome,cognome)'
'FK'nome_medicina,'azienda'
'
'REF'Medicina(nome,azienda)'
Giorgio Giacinto 2011
17
Esercizio#5#
!  Mostrare'uno'schema'EUR'che'rappresen>'una'
realtà'bancaria'così'definita.'
!  La'banca'opera'nel'territorio'a;raverso'una'rete'di'
filiali'da'ciascuna'della'quale'dipendono'una'o'più'
agenzie.''
!  Si'vogliono'rappresentare'tu;e'le'persone'che'
hanno'rappor>'con'la'banca,'siano'esse'dipenden>'
o'>tolari'di'conto'corrente.''
!  Si'vogliono'rappresen>amo'inoltre'i'da>'rela>vi'ai'
con>'corren>'aper>'presso'la'banca.''
Giorgio Giacinto 2011
18
Esercizio#5#
!  In'par>colare:'
! 
! 
! 
! 
! 
Giorgio Giacinto 2011
Per'le'filiali'rappresen>amo'il'codice'e'la'ci;à.'Ciascuna'filiale'ha'
un'dire;ore.'
Per'le'agenzie'rappresen>amo'il'numero'che'iden>fica'l’agenzia'
all’interno'della'filiale,'e'l’indirizzo.'Ciascuna'agenzia'ha'un'
responsabile'e'dipende'da'una'sola'filiale'
Per'le'persone,'dipenden>'della'banca'e'>tolari'di'conto'corrente,'
rappresen>amo'il'codice'fiscale,'il'nome,'il'cognome,'la'data'di'
nascita,'l’indirizzo'e'un'numero'di'telefono'
I'dipenden>'possono'essere'impiega>'o'funzionari'e'dipendono'
da'un’unica'filiale.'Gli'impiega>'possono'essere'responsabili'di'
agenzia.'I'funzionari'possono'essere'dire;ori'di'filiale'
Per'i'con>'corren>'rappresen>amo'il'numero.'Ciascun'conto'
corrente'è'domiciliato'presso'una'agenzia'e'può'avere'uno'o'più'
>tolari.''
19
Schema#ER#Esercizio#5#
Specializzazioni#
Cognome'
'
Nome
CF
indirizzo
'
'
'
data'di'nascita
telefono
'
Persona'
o
Dipendente'
Titolare'CC'
d
Funzionario'
Impiegato'
Giorgio Giacinto 2011
20
Schema#ER#Esercizio#5#
Numero
M'
Titolare'CC'
Possiede'
N'
o
Conto'
Corrente'
Persona'
1'
Funzionario'
Dirige'
Giorgio'Giacinto'2011'
1'
Indirizzo
N'
1'
1'
Domiciliato'
Numero
d
Dipendenza'
N'
Agenzia'
Dipendente'
N'
'
Impiegato'
1'
1'
Responsabile'
Appar>ene'
1'
Ci;à
'
Filiale'
'
Codice
'
'
21
Schema#Relazionale#Esercizio#5#
!  PERSONALE(CF,'Nome,'Cognome,'DataNascita,'Indirizzo,'
Telefono)'
!  TITOLARE_CC(CF)'
'FK'CF'REF'PERSONA(CF)'ON'DELETE'CASCADE'
!  DIPENDENTE(CF,'Filiale)'
'FK'CF'REF'PERSONA'(CF)'ON'DELETE'CASCADE'
'FK'Filiale'REF'FILIALE(Codice)'
!  FUNZIONARIO(CF)''
'FK'CF'REF'DIPENDENTE(CF)'ON'DELETE'CASCADE'
!  IMPIEGATO(CF)''
'FK'CF'REF'DIPENDENTE(CF)'ON'DELETE'CASCADE'
Giorgio Giacinto 2011
22
Schema#Relazionale#Esercizio#5#
!  FILIALE(Codice,'Ci;à,'Dire;ore)''
'FK'Dire;ore'REF'Funzionario(CF)'
!  AGENZIA(Filiale,'Numero,'Indirizzo,'Responsabile)''
'FK'Filiale'REF'FILIALE(Codice)''
'FK'Responsabile'REF'Impiegato(CF)'
!  CONTOCORRENTE(Numero,'Agenzia,'Filiale)'
'FK'Agenzia,'Filiale'REF'AGENZIA(Numero,'Filiale)'
!  POSSESSO_CC(Numero,'CF)'
'FK'Numero'REF'CONTOCORRENTE(Numero)'
'FK'CF'REF'TITOLARE_CC(CF)'
Giorgio Giacinto 2011
23
Altri#Vincoli#Esercizio#5#
Impiegato e Funzionario sono state considerate come entità deboli
rispetto all’entità Dipendente.
La gerarchia richiede che Funzionario e Impiegato siano disgiunte.
Il vincolo corrispondente in SQL è il seguente:
CREATE ASSERTION Disgiunzione CHECK (0 = (SELECT
COUNT(CF) FROM IMPIEGATO WHERE CF IN (SELECT CF FROM
FUNZIONARIO))
Il vincolo che Funzionario e Impiegato COPRONO Dipendente si può
esprimere in SQL con
CREATE ASSERTION Copertura CHECK (0 = (SELECT
COUNT(CF) FROM Dipendente WHERE CF NOT IN (SELECT CF
FROM FUNZIONARIO) OR CF NOT IN (SELECT CF FROM
IMPIEGATO)))
Giorgio Giacinto 2011