Soluzioni prove scritte del corso di Base di dati e Sistemi Informativi - Corso di Laurea in...

8/17/2019 Soluzioni prove scritte del corso di Base di dati e Sistemi Informativi - Corso di Laurea in Ingegneria Informatica e …

1/56

Politecnico di Bari

Corso di Laurea in Ingegneria Gestionale e Ingegneria

Informatica e dell’Automazione

Soluzioni prove scritte del corso di

Base di dati e Sistemi Informativi

Corso Prof. Ing. E. Di Sciascio, Prof. Ing. G. LosetoDipartimento di Ingegneria Elettrica e dell’Informazione

Politecnico di Bari

a cura diMarco Salvatore Vanad̀ıa

[email protected]

29 aprile 2016

mailto:[email protected]:[email protected]


2/56

ii

a Giulia

Il presente documento è rilasciato sotto licenza cCreative Commons 3.0 by-sa-nc cbna.É consentita la creazione di opere derivate, traduzioni, adattamenti, totali o parziali, fatta salva

l’attribuzione dell’autore originale e il mantenimento della licenza.Marco Salvatore Vanad̀ıa

Politecnico di Bari


3/56

Indice

1 Soluzioni appelli di Base di Dati e Sistemi Informativi 11.1 Appello 5 Febbraio 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21.2 Appello 28 Febbraio 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91.3 Appello 29 Aprile 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151.4 Appello 27 Giugno 2014 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201.5 Appello 17 Giugno . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

1.6 Appello 16 Luglio 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241.7 Appello 2 Settembre 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261.8 Appello 22 Settembre 2015 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291.9 Appello 12 Novembre 2015 - Modulo I . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311.10 Appello 5 Febbraio 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331.11 Appello 18 Febbraio 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391.12 Esonero 20 Aprile 2016 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441.13 Traccia parziale appello 16 Febbraio 2012 . . . . . . . . . . . . . . . . . . . . . . . . . . . 49Elenco delle figure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50Indice analitico . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

iii


4/56


5/56

Capitolo 1

Soluzioni appelli di

Base dei Dati e

Sistemi Informativi

1


6/56

2 CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI

1.1 Appello 5 Febbraio 2014

CORSO DI LAUREA IN ING. GESTIONALE, INFORMATICA, ELETTRONICA E TELECOMU-NICAZIONI —PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMIINFORMATIVI”

a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario più sotto descritto.

Una base di dati deve essere utilizzata per la gestione di opere d’arte all’interno di musei . Occorrequindi memorizzare i dati relativi ai musei di cui si conosce un codice alfanumerico, nome e indirizzo.Ogni museo è suddiviso in aree caratterizzate da un identificativo numerico, univoco solo all’internodel singolo museo, nome e da un valore booleano che indica la presenza di videosorveglianza. Perogni opera d’arte si conosce invece un codice univoco, nome, datazione, valore e l’area in cui èesposta. In particolare le opere con valore superiore a 10.000 euro dovranno trovarsi solo in areecon videosorveglianza. Le opere d’arte sono classificabili come:

– dipinti: occorre memorizzare la tecnica utilizzata e la lista di operazioni di restauro a cuieventualmente l’opera è stata sottoposta. Per ogni operazione si conosce la data di inizio efine restauro ed il costo;

– sculture: occorre memorizzare il materiale e l’altezza della scultura;

– altro: di cui si conosce una breve descrizione.

È infine necessario tenere traccia per ogni opera dell’artista (o degli artisti) che l’hanno realizzataidentificati attraverso un codice univoco, nome, cognome e nazionalità. Indicare le cardinalità dellerelazioni e un identificatore per ciascuna entità.

Soluzione 1.1.1. Progettazione concettuale dello schema Entità-Relazioni per lo scenario di ge-stione di opere d’arte nei musei (diagramma schema E-R in figura 1.1).

Costrutti del modello concettuale:

entità Museo chiave codice, attributi nome , attributi composti sede (normalizzato in attri-

buti atomici: via, civico, cap)entità debole Area chiave id chiave esterna Museo, attributi museo, videosorveglianza ,

business rule videosorveglianza booleano (vincolo dominio)

entità debole Opera chiave codice chiave esterna Area, attributi nome, datazione, valore business rule se valore >10000) videosorveglianza=1

entità Artista chiave codice, attributi nome, cognome, nazionalità

gerarchia is-a generalizzazione totale esclusiva Opera entità figlie:

entità Dipinto attributi tecnica

entità Scultura attributi materiale, altezza

entità Altro attributi descrizione

entità debole Restauro chiave esterna Dipinto attributi costo, data inizio, data fine relazione Museo Area, cardinalità 1:N, partecipazione obbligatoria di Area

relazione Area Opera, cardinalità 1:N, partecipazione obbligatoria di Opera

relazione Artista Opera, cardinalità N:N, partecipazione non obbligatoria

relazione Dipinto Restauro, cardinalità 1:N, partecipazione obbligatoria di Restauro

Normalizzazione schema concettuale:

Analisi ridondanze Non sono presenti attributi derivabili o di conteggio.

Eliminazione generalizzazioni Si risolve la generalizzazione Opera con le figlie deboli Dipinto,Scultura e Altro con associazioni 1:1 con chiave esterna verso l’entità padre.


7/56

1.1. APPELLO 5 FEBBRAIO 2014 3

Accorpamento/partizionamento di entità/associazioni Non sono necessari accorpamenti/-partizionamenti.

Eliminazione attributi composti – Attributo indirizzo di Museo sostituito da attributi ato-mici via, civico, cap.

Scelta chiavi primarie Tutte le chiavi candidate sono scelte come chiavi primarie.

Museo

cod nome

via

civico cap

Area

id nome videosorveglianza

1 N

Opera

cod nome

datazione

valore

1

N

Artista

cf nome cognome

via

civico cap

N N

ISA

Dipinto

tecnica

Scultura

materiale altezza

Altro

desc

Restauro

costo

data inizio

data fine

1

N

Figura 1.1: Diagramma E/R gestione opere d’arte nei musei

b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrità.

Soluzione 1.1.2. Le relazioni ottenute dal mapping relazionale:

1) Museo(cod,nome,via,civico,cap)

2) Area(codMuseo,idArea,nome,videosorveglianza)

3) Opera(codMuseo,idArea,codOpera,nome,datazione,valore)

si è risolta la gerarchia Opera con le entità figlie deboli Dipinto, Scultura e Altro

4) Dipinto(codMuseo,idArea,codOpera,tecnica)

5) Scultura(codMuseo,idArea,codOpera,materiale)


8/56


6) AltraOpera(codMuseo,idArea,codOpera,descrizione)

7) Relazione(codMuseo,idArea,codDipinto,costo,data inizio,data fine)

8) Artista(cf,nome,cognome,via,civico,cap)

9) ArtistaOpera(cf,codMuseo,idArea,codOpera)

Le tabelle in linguaggio SQL:

C R EA T E T A BL E M u se o (

codice CHAR (10) PRIMARY KEY ,

nome VARCHAR (128) ,

via VARCHAR (128) ,

civico VARCHAR (8) ,

cap NUMERIC (5 ,0)

);

C R EA T E T A BL E A r ea (

c od Mu se o CHA R(10) ,

idArea CHAR (6) ,


vi de oso rv egl ia nza BIT ,P R I M AR Y K E Y ( c o d Mu s e o , i d A r e a ) ,

F O R E IG N K E Y ( c o d M u se o

R E F E R E NC E S M u s eo ( c o d i c e )

);

C R EA T E T A BL E O p er a (


idArea CHAR (6) ,

c od Op er a CHA R(16) ,


d at az io ne DATE ,

valore NUMERIC (10 ,2) ,

PRIMARY KEY(codMuseo ,idArea ,codOpera ),

F O R E IG N K E Y ( c o d Mu s e o , i d A r e a )

R E F E R E NC E S A r e a ( c o dM u s eo , i d A r e a )

);

C R E A TE A S S E R TI O N V i d e o s o r v eg l i a n z a C H E CK (

SELECT *

FROM Opera JOIN Area

ON Opera . codMuseo =Area . codMuseo AND

O p e r a . i d A r e a = A r e a . i d A r e a

WHERE Opera .valore >10000

AND Area . vide os orv eg lia nz a=1

);

C R EA T E T A BL E D i pi n to (


idArea CHAR (6) ,

c od Op er a CHA R(16) ,

tecnica VARCHAR (128) ,


FOREIGN KEY(codMuseo ,idArea ,codOpera )

REFERENCES Opera(codMuseo ,idArea ,codOpera )

);

C R EA T E T A BL E S c ul t ur a (



9/56


idArea CHAR (6) ,

c od Op er a C HAR (16) ,

m ateriale VARCH AR (1 28 ) ,

altezza FLOAT ,



REFERENCES Opera(codMuseo ,idArea ,codOpera ));

C R E A TE T A B LE A l t r a O pe r a (

c od Mu se o C HAR (10) ,

idArea CHAR (6) ,


d e s c r i zi o n e V A R C HA R ( 2 5 6 ) ,



REFERENCES Opera(codMuseo ,idArea ,codOpera )

);

C R EA T E T A BL E R e st a ur o (


idArea CHAR (6) ,

c o dD i pi n t o C H AR ( 1 6) ,

costo NUMERIC (10 ,2) ,

d a t a _ i ni z i o D A TE ,

d at a_ fi ne DATE ,

PRIMARY KEY(codMuseo ,idArea ,codDipinto ),

FOREIGN KEY(codMuseo ,idArea ,codDipinto )

REFERENCES Dipinto( codMuseo ,idArea ,codOpera )

);

C R EA T E T A BL E A r ti s ta (

cf CHAR (16) PRIMARY KEY ,

nome VARCHAR (128) ,cognome VARCHAR (128) ,

n a z i o n a l i tà V A R C HA R ( 3 2 )

);

C R E A TE T A B LE A r t i s t a Op e r a (


idArea CHAR (6) ,


c fA rt is ta C HA R (16 ) ,



REFERENCES Opera(codMuseo ,idArea ,codOpera ),

F O R E I GN K E Y ( c f A r t i st a ) R E F E R E NC E S A r t i s ta ( c f )

);

c) Si vuole realizzare un database relativo alle prenotazioni di camere d’albergo. È stata a tal finecostruita, da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(cod_albergo, nome_albergo, città, stelle, numero_camera, posti_letto,

costo_camera, cf_cliente, nome, cognome, data_inizio_soggiorno,

numero_notti)

Nell’ipotesi che il numero di una singola camera sia univoco solo all’interno di ciascun albergo sene determini la chiave e si individuino, esplicitandole, le dipendenze funzionali. Sulla base di questesi proceda alla normalizzazione in 3a forma normale, preservando le dip. funzionali.


10/56


Soluzione 1.1.3. Ipotesi:

1) numero camera univoco nell’albergo

2) un cliente può prenotare più camere

3) lo stesso cliente può prenotare la stessa camera in date diverse

Dipendenze funzionali:

1) Albergo cod albergo → nome, città, stelle

2) Camera cod albergo, numero camera → posti letto, costo camera

3) Cliente cf cliente → nome, cognome

4) Soggiorno cf cliente, cod albergo, numero camera, data inizio → numero notti

Gli attributi che formano la chiave:

cod albergo,numero camera,cf cliente,data inizio

Classificazione delle dipendenze funzionali:

– Dipendenza funzionale piena: Soggiorno

– Dipendenza funzionale parziale: Cliente,Camera,Albergo

– Dipendenza funzionale transitiva: nessuna

Le dipendenze funzionali sono in 3a forma normale se ∀ dip. funz. X → A vale una delle seguenti

1) X contiene una chiave K di R

2) A appartiene ad almeno una chiave di R

e 3) non esistono attributi che dipendono da altri attributi non chiave.

Essendo tutte dip. funz. piene o parziali, non ci sono dip. funz. transitive, sono verificate lecondizioni 1 e 3.

Le relazioni risultanti dalla normalizzazione in 3a forma normale che preservano le dip. funz.

1) Albergo(cod albergo,nome, città, stelle)

2) Camera(cod albergo,numero camera,posti letto, costo camera)

3) Cliente(cf cliente,nome, cognome)

4) Soggiorno (cf cliente, cod albergo, numero camera, data inizio,numero notti)

d) Date le seguenti relazioni:

OPERATORE (piva, nome)

ABITAZIONE (codice, via, civico, città, piva operatore)

CONSUMI (codAbitazione, data, kWh consumati)

esprimere in SQL le seguenti interrogazioni:1) Visualizzare per ogni operatore i kWh complessivamente forniti nel comune di Bari ordinando

i risultati in ordine decrescente

Soluzione 1.1.4. Query di selezione su join con condizione, raggruppamento e ordinamento

S E LE C T p i va _ op e ra t or e , s um ( k W h _ co n s um a t i ) a s k Wh _ f or n it i

FROM ab itazion e JOIN consumi

ON codice = co dAbi taz ione

WH ERE a bi ta zi on e. citt à = ’ B a r i ’

A ND c on su mi . da ta BE TW EE N ’ 2 01 3/ 01 /0 1 ’ A ND ’ 20 13 /1 2/ 31 ’

G RO UP B Y p i va _ op e ra t or e

O RD ER B Y k W h_ fo r ni ti D ES C


11/56


2) Selezionare le abitazioni che durante il 2013 non hanno mai presentato un consumo giornalierosuperiore ai 5 kWh

Soluzione 1.1.5. Query di selezione con condizione e valore in elenco da subquery concondizione

SELECT *

FROM abit azione

WHERE codice IN (

S EL EC T D IS TI NC T c od Ab it az io ne

FROM consumi

W HE RE c on su mi . d at a B ET WE EN ’ 2 01 3/ 01 / 01 ’ A ND ’ 2 01 3 /1 2/ 31 ’

AND co dAb itaz ion e NOT IN (

S EL EC T D IS TI NC T c od Ab it az io ne

FROM consumi

W HE RE c on su mi . d at a B ET WE EN ’ 2 01 3/ 01 / 01 ’ A ND ’ 2 01 3 /1 2/ 31 ’

AND kW h_c onsu mat i > 5))

e) SOLO N.O.

– Illustrare la sintassi di un trigger, descrivendo in particolare il concetto di granularit̀a

Soluzione 1.1.6. In una base di dati attiva è possibile attivare un comportamento reattivoa modifiche del database con un processore di regole di produzione basate sul paradigmaEvento-Condizione-Azione.

La sintassi di definizione di un trigger:CREATE TRIGGER NomeTrigger

AFTER|BEFORE ← modalità INSERT|UPDATE|DELETE ← eventoON TabellaTarget

[REFERENCING [OLD AS OldAlias ], [NEW AS NewAlias ] ] ← alias referenze [FOR EACH [STATEMENT|ROW]] ← granularità

[WHEN predicato condizione ] ← condizione per granularità ROW statementSQL ← azione

Un trigger può essere attivato immediatamente prima o dopo una primitiva di modifica deidati (INSERT,UPDATE,DELETE), o in differita per transazioni, con un livello di granularitàdi attivazione per singola tupla o per intera primitiva di modifica. L’attivazione può esseresottoposta a condizione espressa da un predicato. L’azione eseguita è espressa da un statementSQL, eventualmente contenente primitive di linguaggio proprietarie.

Una primitiva di evento può essere controllata con granularità a livello di tupla, iterativa-mente su tutte le tuple (sotto eventuale condizione WHEN) con l’esecuzione dell’azione dichiaratanello statement SQL del trigger, con le seguenti priorità decrescenti dettate dalla modalità egranularità:

1) BEFORE STATEMENT

2) BEFORE ROW

3) AFTER ROW

4) AFTER STATEMENT

– Descrivere le proprietà acide delle transazioni

Soluzione 1.1.7. Le proprietà acide delle transazioni sono:

Atomicità Una transazione è una unità atomica indivisibile di lavoro: le operazioni definitenella sequenza compresa tra BoT e EoT devono lasciare la base di dati o nello stato pre-cedente alla esecuzione della transazione (abort) o devono essere applicati tutti gli effettidelle operazioni eseguite (commit).


12/56


Consistenza Una transazione non deve violare i vincoli di integrità della base di dati. Ilcompilatore del Data Definition Language del DBMS crea i vincoli di dominio, asso-ciati ai tipi predefiniti, i vincoli di integrità referenziale associati alla definizione di chiaviprimarie (PRIMARY KEY) e chiavi esterne (FOREIGN KEY), i vincoli di integrità espressi dacondizioni su attributi (CHECK), e condizioni espresse da asserzioni e trigger in basi di datiattive. Prima e dopo una transazione la base di dati si trova in uno stato consistente, solodurante le modifiche intermedie è possibile avere esclusivamente nel buffer in memoriadegli stati inconsistenti, ma questi non sono mai archiviati nel DB senza prima passare ilcontrollo di consistenza.

Isolamento In un sistema di elaborazione di transazioni concorrenti, ogni transazione è ese-guita indipendentemente dalle altre. L’effetto di una transazione non cambia e non devedipendere dall’effetto di altre transazioni. Il fallimento di una transazione non deve inter-ferire con altre transazioni in esecuzione. L’isolamento viene garantito dal controllore dellaconcorrenza che si occupa di schedulare l’esecuzione fuori ordine e in parallelo delle ope-razioni delle transazioni garantendo l’equivalenza alla esecuzione della sequenza schedule seriale (transazioni nell’ordine originale di arrivo non inframmezzate tra loro).

Durabilità Gli effetti di una transazione andata a buon fine con una richiesta di commit de-vono essere resi persistenti della base di dati. Il controllore di affidabilità si assicura chele modifiche apportate dalle operazioni di una transazione nel buffer di memoria centralesiano effettivamente scritte nella base di dati in memoria di massa. In caso di malfunziona-menti hardware o software il controllore di affidabilità fa uso delle informazioni ridondantiregistrate nei file di log di sistema e delle transazioni per garantire la persistenza delletransazioni andate in commit.

– Illustrare l’architettura di un DataWarehouse, descrivendo brevemente le operazioni svolte daciascun modulo


13/56




Una base di dati deve essere utilizzata per la gestione delle informazioni relative ai contratti di telefonia mobile in Italia . Ogni contratto è identificato da un codice univoco, una data di stipula,

una durata, il cliente che l’ha stipulato (di cui si conoscono i dati anagrafici) e la SIM card sucui il contratto è attivato. Ad ogni SIM card è associato un codice identificativo, univoco soloal’interno della compagnia telefonica di riferimento, un numero telefonico (composto da prefissointernazionale, prefisso di 3 cifre e numero di 7 cifre) ed una capacità di memorizzazione. AllaSIM deve essere sempre associato un numero italiano, per cui il prefisso internazionale deve essere“+39”. Per le compagnie telefoniche, identificate dalla ragione sociale, occorre tenere traccia anchedel nome e della sede legale. I contratti telefonici si dividono inoltre in due tipi, le cui caratteristichesono di seguito descritte:

– contratti in abbonamento: interessa tracciare la lista dei servizi forniti, il canone mensile e unvalore booleano che indica la disponibilità di una linea dati; occorre verificare che i contrattiin abbonamento che hanno una linea dati disponibile siano attivi su SIM card con capacit à dialmeno 128k.

– contratti con ricaricabile: interessa memorizzare la data dell’ultima ricarica e lo stato delcontratto (può essere “attivo” o “non attivo”); occorre verificare che se l’ultima ricarica èavvenuta prima di un anno fa l’abbonamento risulti non attivo.

Indicare le cardinalità delle relazioni e un identificatore per ciascuna entit à.

Soluzione 1.2.1. Progettazione dello schema Entità-Relazioni per lo scenario di gestione delleinformazioni relative a contratti di telefonia mobile in Italia (diagramma schema E-R in figura 1.2).

Costrutti del modello concettuale:

entità Cliente

chiave codice fiscale

attributi composti dati anagrafici

entità debole SIM

chiave parziale codice

partecipazione totale relazione con compagnia telefonica

attributo composto numero telefonico

attributo capacità memorizzazione

business rule (BR1) numero con prefisso internazionale italiano “+39”

entità compagnia telefonica

chiave ragione sociale

attributi nome

attributo composto sede

gerarchia IS-A entità debole Contratto

chiave parziale codice

chiave esterna dipendenza da Cliente, Sim

attributi data stipula, durata

entità figlia Contratto in abbonamento

attributo multivalore lista servizi forniti

attributo canone mensile, linea dati

business rule (BR2) se linea dati =1 capacità sim ≥128 (vincolo integrità)

business rule (BR3) linea dati booleano [vincolo dominio]

entità figlia Contratto con ricaricabile


14/56


attributi data ultima ricarica, stato contratto

business rule (BR4) stato contratto IN(‘attivo’,‘non attivo’) [vincolo dominio]

business rule (BR5) se(CURRENT DATE()-data ultima ricarica >365) stato contratto=‘nonattivo’ [vincolo integrità]

relazione Contratto-Cliente, cardinalità N:1, partecipazione totale

relazione Contratto-SIM, cardinalità 1:1, partecipazione totale

relazione SIM-Compagnia telefonica, cardinalità N:1, partecipazione totale

relazione Abbonamento-Servizio, cardinalità N:N, partecipazione parziale

Normalizzazione schema concettuale:

Analisi ridondanze L’attributo derivabile stato contratto viene modificato automaticamente daun trigger definito dalla business rule BR5.

Eliminazione generalizzazioni Si risolve la generalizzazione Contratto con le figlie deboliAbbonamento e Ricaricabile con associazioni 1:1 con chiave esterna verso l’entità padre.

Accorpamento/partizionamento di entità/associazioni – Attributo multivalore lista ser-

vizi partizionato in nuova entità Servizio

con chiave codice, attributi desc , costo mensile ,e nuova relazione molti-a-molti con entità Contratto in abbonamento

Eliminazione attributi composti – Attributo dati anagrafici di Cliente sostituito da attri-buti atomici nome, cognome, via, civico, cap.

– Attributo numero telefonico di SIM sostituito da attributi atomici prefisso internazionale ,prefisso di 3 cifre (BR6, vincolo dominio), numero di 7 cifre (BR7, vincolo dominio).

– Attributo sede di Compagnia Telefonica sostituito da attributi atomici via, civico,cap.

Scelta chiavi primarie Tutte le chiavi candidate sono scelte come chiavi primarie.


Soluzione 1.2.2. Le relazioni ottenute dal mapping relazionale:

1) Cliente(CF,nome,cognome,via,civico,cap)

2) CompagniaTelefonica(ragione sociale,nome,via,civico,cap)

3) SIM(codice,ragSocCompagnia,capacità,pref internazionale,prefisso,numero)

si è risolta la gerarchia Contratto con le entità figlie deboli Abbonamento e Ricaricabile

4) Contratto(codice,cfCliente,sim,data stipula,durata)

5) Abbonamento(codContratto,canone mensile,linea dati)

6) Ricaricabile(codContratto,data ultima ricarica,stato contratto)

7) Servizio(cod,desc,costo mensile)

8) ServiziAbbonamento

(codServizio,codAbbonamento)Le tabelle in linguaggio SQL:

C R E A TE T A B L E C o m p a g n i a T e l e f o n i c a (

ra gio ne_ soc ia le VARCHAR (25) PRIMARY KEY ,

nome VARCHAR (50) ,

via VARCHAR (50) ,


cap NUMERIC (5 ,0)

);

C R EA T E T A BL E S im (

codSim NUMERIC (20) ,


15/56


Clientecf

nomecognome

Contratto

coddurata

data stipula

1

N

SIM

cod capacità

pref int pref numero

1 1

TelCom

ragione sociale nome via

civico

cap1

N

ISA

Abbonamento

linea dati canone mensile

Ricaricabile

data ultima ricarica

stato contratto

Servizio

cod

desc

costo mensile

N

N

Figura 1.2: Diagramma E/R gestione servizi telefonia

r a gS o cC o m p V A RC H AR ( 3 5 )

R E F E R E NC E S C o m p a g n i a T el e f o n i c a ( r a g i o n e _ s o c ia l e ) ,

p r e f i s s o _ i nt e r n a z i o n a l e V A R C H AR ( 4 ) C H E CK ( V A L U E I N ( ’ + 3 9 ’ ) ) ,

p re fi ss o N UM ER IC ( 3 ,0) ,

numero NUMERIC (7 ,0) ,

capacit à INTEGER ,

P R I M A RY K E Y ( c o dS i m , r a g S o cC o m p )

);

C R EA T E T A BL E C l ie n te (

CF CHAR (16) PRIMARY KEY ,nome VARCHAR (30) NOT NULL ,

c og no me V AR CHA R (5 0) NOT NULL ,

via VARCHAR (50) ,


cap NUMERIC (5 ,0)

);

C R EA T E T A BL E C o n tr a tt o (

codC ontr atto NUMERIC (7 ,0) PRIMARY KEY ,

c f Cl ie nt e C HA R ( 16 ) R E FE RE NC E S C li en te ( C F) ,

codSim NUMERIC (10) ,

r a g S o cC o m p V A R C H AR ( 2 5 ) ,


16/56


d a t a S t ip u l a D AT E ,

d ur ata I NT ER VA L YEA R (2) TO MO NTH (2) ,

F O R E IG N K E Y ( c o dS i m , r a g S o c C o m p ) R E F E R E N CE S S i m ( c o dS i m , r a g S o c C o m p )

);

C R EA T E T A BL E A b bo n a me n t o (

cod Cont ratt o NUMERIC (7 ,0) PRIMARY KEYR E F E R E NC E S C o n t r a tt o ( c o d C o n t r a t t o ) ,

ca non e_m ensi le NUMERIC (5 ,2) ,

l in ea _ da ti B IT

);

C R EA T E A S SE R TI O N S i m Ab b o na m en t o C H EC K (

SELECT *

FROM ( Ab bonam ento NATURAL JOIN Contrat to )

N A TU R AL J O IN S IM

WHERE A bbona mento . line a_dati =1

AND SIM . capacit à > = 1 2 8

);

C R E A TE T A B L E R i c a r i c a b i l e (

cod Cont ratt o NUMERIC (7 ,0) PRIMARY KEY ,

c fC li en te C HA R ( 16 ) R EF ER EN C ES C l i en te ( CF ) ,

d at a_ ul ti ma _r ic ar ic a D ATE ,

s ta to V AR CH AR ( 10 ) C HE CK ( VA LU E IN ( ’a tt iv o ’, ’ no n a tt iv o ’))

);

C R E A TE A S S E R TI O N S t a t o S i m R i ca r i c a b i l e C H E C K (

SELECT *

FROM ( Ab bonam ento NATURAL JOIN Contrat to )

N A TU R AL J O IN S IM

W HE RE ( s ta to = ’ a tt iv o ’ A ND C U RR E NT _D A TE ( ) - Si m . da ta _u lt i ma _r i ca ri ca < =3 65 )

OR ( s ta to = ’ no n a tt iv o ’ A ND C UR RR EN T_ DA TE ( )- Si m. d at a_ ul ti ma _r ic ar ic a > 36

);

C R EA T E T A BL E S e rv i zi o (

c o dS e r vi z i o N U ME R IC ( 5 , 0 ) P R IM A RY K EY ,

desc VARCHAR (50) ,

cos to_ men sile NUMERIC (5 ,2)

);

C R E A TE T A B L E S e r v i z i o A b b o n a m e n t o (

c o d S e r vi z i o N U M E R IC ( 5 , 0 ) R E F E R E NC E S S e r v i zi o ( c o d S e r v i z i o ) ,

c o d A b b o n am e n t o N U M E RI C ( 7 , 0 ) R E F E R E NC E S A b b o n a me n t o ( c o d C o n t r a t t o ) ,

P R I M AR Y K E Y ( c o d S er v i z io , c o d A b b o n a m e n to )

);

c) Si vuole realizzare un database relativo alla assegnazione delle postazioni in un call center . È stataa tal fine costruita, da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(numero_fila, numero_postazione, descrizione_postazione, codice_operatore,

nome_operatore, cognome_operatore, numero_telefono, data_turno,

ora_inizio_turno, ora_fine_turno, num_operatori_turno, num_telefonate)

Nell’ipotesi che numero postazione sia univoco solo per fila , che un operatore possa cambiare po-stazione in ogni turno, ma che gli venga assegnato sempre lo stesso numero di telefono e chenum telefonate sia il numero di telefonate che un operatore fa in un determinato turno , se ne de-termini la chiave e si individuino, esplicitandole, le dipendenze funzionali. Sulla base di queste siproceda alla normalizzazione in 3a forma normale, preservando le dip. funzionali.


17/56



(IP1) numero postazione sia univoco solo per la fila

(IP2) un operatore possa cambiare postazione in ogni turno

(IP3) un operatore ha assegnato sempre lo stesso numero di tel

(IP4) num telefonate è il numero di telefonate che un operatore fa in un turno(IP5) si aggiunge l’ipotesi che in un dato turno ad un dato operatore è affidata una specifica

postazione


(DF1) Postazione numero fila, num postazione → desc postazione (ip1)

(DF2) Operatore cod operatore → nome, cognome, numero telefono (ip3)

(DF3) Turno data turno, ora inizio turno → ora fine turno, num operatori

(DF4) Turno Operatore data turno, ora inizio turno, cod operatore → num fila, num postazionenum telefonate (ip2,ip4,ip5)

Gli attributi che formano la chiave: data turno, ora inizio turno, cod operatore

Classificazione dipendenze funzionali

– Dipendenza funzionale piena: Turno Operatore

– Dipendenza funzionale parziale: Turno, Operatore

– Dipendenza funzionale transitiva: Postazione

Si verifica se le dipendenze funzionali formano una terza forma normale: ogni dipendenza funzionaleX → Y definita deve verificare almeno una delle seguenti: a) X è una superchiave di R, b) ogniattributo in Y è contenuto in almeno una chiave di R.


CORSO DI LAUREA (codice, nome)

ISCRIZIONE ANNUALE (matricola, anno accademico, crediti sostenuti, anno iscrizione)STUDENTE (matricola, nome, cognome, codice corso)

esprimere in SQL le seguenti interrogazioni:

1) Visualizzare i corsi di laurea con un numero di iscritti al secondo anno nell’anno accademico2013/2014 uguale a quello di iscritti al primo anno nell’anno accademico 2012/2013.

Soluzione 1.2.4. Query di selezione con condizione su valore in elenco da subquery con join,condizione, raggruppamento e condizione su raggruppamento basata su valore e confronto consubquery annidata

SELECT *

FROM C ors o_d i_L aur ea

WHERE codice IN (

SE LE CT c od ic e_ co rs oF ROM S tu de nt e N AT URA L JOI N I sc ri zi on e_ An nu al e AS A

W HE RE a nn o_ ac ca de mi co = ’ 20 13 /2 01 4 ’

A ND a nn o_ is cr iz io ne = ’ 20 12 /2 01 3 ’

G RO UP B Y c o di ce _ co r so

HAVING count (*) = (

SELECT count (*)

F RO M S tu de nt e N AT UR AL J OI N I sc ri zi on e_ An nu al e

W HE RE a nn o_ ac ca de mi co = ’ 20 12 /2 01 3 ’

A ND a nn o_ is cr iz io ne = ’ 20 12 /2 01 3 ’

A ND c od ic e_ co rs o =A . co di ce _c or so

)

)


18/56


2) Selezionare gli studenti che si sono iscritti nell’anno accademico 2012/2013, ma non nel 2013/2014,con il relativo numero di crediti sostenuti all’atto dell’iscrizione 2012/2013.

Soluzione 1.2.5. Query di selezione con condizione su valore non in elenco da subquery concondizione

S EL EC T S .* , c re di ti _s os te nu ti

F RO M S tu de nt e S N AT UR AL J OI N I sc ri zi on e_ An nu al eW HE RE a nn o_ is cr iz io ne = ’ 20 12 /2 01 3 ’

AND S . matricola NOT IN (

S EL EC T m at ri c ol a

F RO M I sc ri zi on e_ An nu al e

W HE RE m at ri c ol a = S. m a tr ic ol a

A ND a n no _ ac ca d em i co = ’ 2 01 3/ 20 14 ’

)

e) SOLO N.O.

– Definire il concetto di vista aggiornabile, descrivendo in particolare l’utilizzo della clausola”check option”

Soluzione 1.2.6. Una vista è una tabella virtuale definita su una lista di attributi apparte-nenti ad altre viste o tabelle di base dello schema le cui tuple sono il risultato di una querydi selezione che ne estrae i valori. Quando è possibile determinare in modo univoco a qualeattributo di quale tabella base appartengono tutti i valori nella vista si ha una vista aggior-nabile. Sotto le seguenti ipotesi è possibile eseguire una operazione di UPDATE su una vistaaggiornabile:

– non può avere attributi espressi come funzioni di aggregazione o calcolo di espressioni

– non può applicare un selettore DISTINCT

– non può effettuare JOIN tra tabelle

– non può raggruppare e filtrare dati con direttive GROUP BY e HAVING

– non presenta condizioni WHERE con subquery

Nella definizione di vista aggiornabile è inoltre possibile specificare l’opzione WITH [LOCAL|CASCADED] CHECK OPTION per impedire che una modifica a seguito di un UPDATE abbia comerisultato tuple che non rispettano il predicato di selezione della vista. L’opzione LOCAL servea richiedere un controllo limitato alla vista definita, l’opzione CASCADED richiede un controllopropagato alle viste nidificate.

– Descrivere brevemente i livelli di isolamento in SQL

Soluzione 1.2.7. A partire da SQL:1999 è possibile indicare per ogni transazione il livellodi isolamento richiesto al controllore di concorrenza basato sul protocollo di locking in 2fasi stretto:

1) serializable: applica il livello massimo di isolamento con 2PL stretto e lock di predicati.Evita tutte le anomalie a scapito delle prestazioni

2) repeatable read: applica il 2PL stretto con lock di lettura a livello di tupla. Evitatutte le anomalie eccetto l’inserimento fantasma (phantom insert ) perché non utilizza ilock di predicato. Prestazioni migliori a scapito della consistenza.

3) read committed: utilizza lock condivisi di lettura ma non rispetta il 2PL stretto. Evitale anomalie di lettura sporca ma è affetto da tutte le altre anomalie. Ottime prestazioniper transazioni di sola lettura, grave rischio di inconsistenza in caso di scritture.

4) read uncommitted: non emette alcun lock condiviso in lettura ne rispetta lock esclusiviin scrittura di altre transazioni. Utilizzata per transazioni in sola lettura con le massimeprestazioni. Può presentare tutte le anomalie esclusa la perdita di aggiornamento (pertrans. sola lettura).

– Illustrare il problema dell’impedence mismatch, l’utilità e la sintassi di un cursore


19/56

1.3. APPELLO 29 APRILE 2014 15

1.3 Appello 29 Aprile 2014


Una base di dati deve essere utilizzata per la programmazione di film in differenti cinema . Ognicinema è identificato da un codice univoco, nome, indirizzo e lista dei servizi disponibili all’interno

del cinema. Ogni cinema è composto da più sale di cui si conosce un numero identificativo, univocosolo al’interno del cinema di riferimento, capienza e un attributo booleano che indica se la sala èattrezzata per le proiezioni in 3D. Si conoscono inoltre le informazioni sui film identificati da uncodice (alfanumerico di 8 caratteri), nome, durata in minuti e genere (azione, fantasy, thriller, com-media o altro). Occorre memorizzare la programmazione realizzata dai cinema: per ogni proiezionesi conosce data e ora, film proiettato, sala di riferimento, costo del biglietto e numero di posti pre-notati (verificando che non siano superiori alla capienza della sala). Per ogni proiezione è possibileacquistare dei biglietti, ogni acquisto è identificato da un codice univoco e dal posto prenotato. Gliacquisti si dividono inoltre in due tipi:

– effettuati di persona: interessa tener traccia dell’eventuale sconto ottenuto;

– effettuati online: interessa memorizzare l’eventuale costo aggiuntivo applicato e l’utente cheha effettuato l’acquisto, di cui si conoscono i dati anagrafici.


Proiezione

data ora costo posti prenotati

proiettaFilm

codgenere

nome

durata min

1 Navviene in Sala

id capienza sala3D

N 1

Biglietto

codposto prenotato

1

N

ISA

Online

costo aggiuntivo

Botteghino

sconto

Utenteemail

nomecognome via civicocap

N1

app

Cinema

cod nomevia

ci

cap

N

1

offre

Servizioid

desc

N

N

Figura 1.3: Diagramma E/R scenario programmazione film nei cinema



20/56


C R EA T E T A BL E F i lm (

cod CHAR (8) PRIMARY KEY ,

nome VARCHAR (64) NOT NULL ,

genere VARCHAR (10)

C H E CK ( V A L U E I N ’ a z i o n e ’ , ’ c o m m ed i a ’ , ’ f a n t a s y ’ , ’ t h r i l l er ’ , ’ a l t r o ’ ) ,

d u ra t a _m i n S M AL L IN T

);

C R EA T E T A BL E C i ne m a (

cod CHAR (8) PRIMARY KEY ,

nome VARCHAR (64) NOT NULL ,

via VARCHAR (128) ,


cap NUMERIC (5 ,0)

);

C R EA T E T A BL E S e rv i zi o (

id INTEGER PRIMARY KEY ,

desc VARCHAR (64)

);

C R E A TE T A B L E S e r v i z i C i n e m a (

c od Ci ne ma C HA R (8 ) R EF ER E NC ES C i ne ma ( c od ) ,

i d Se r v iz i o I N TE G ER R E FE R E NC E S S e r v iz i o ( id ) ,

P R I M AR Y K E Y ( c o d Ci n e ma , i d S e r v i z i o )

);

C R EA T E T A BL E S a la (

c od Ci ne ma C HA R (8 ) R EF ER E NC ES C i ne ma ( c od ) ,

id SMALLINT ,

c ap ie nz a S MA LL IN T ,

sala3D BIT ,

PRIMARY KEY(codCinema ,id));

C R EA T E T A BL E P r oi e zi o n e (

c od Ci ne ma C HA R (8 ),

idSala SMALLINT ,

c od Fi lm C HA R (8 ) R EF ER EN CE S F il m (c od ) ,

dataora TIMESTAMP ,

costo NUMERIC (4 ,2)

po sti _pr eno ta ti SMALLINT ,

PRIMARY KEY(codCinema ,idSala ,codFilm ,dataora ),

F O R E IG N K E Y ( c o d Ci n e ma , i d S a l a ) R E F E R E NC E S S a l a ( c o dC i n em a , i d )

);

C R EA T E T A BL E B i g li e tt o (

cod CHAR (8) ,

c od Ci ne ma C HA R (8 ),

idSala SMALLINT ,

codFilm CHAR (8) ,

dataora TIMESTAMP ,

po sto _pr eno ta to CHAR (8) ,

PRIMARY KEY(cod, codCinema ,idSala ,dataora ),

FOREIGN KEY(codCinema ,idSala ,codFilm ,dataora )

REFERENCES Proiezione( codCinema ,idSala ,codFilm ,dataora )

);


21/56


C R E A TE T A B LE B i g l i e t t o Bo t t e g h i n o (

cod CHAR (8) ,

c od Ci ne ma C HA R (8) ,

idSala SMALLINT ,

codFilm CHAR (8) ,

dataora TIMESTAMP ,

sconto NUMERIC (4 ,2) ,PRIMARY KEY(cod, codCinema ,idSala ,dataora ),



);

C R EA T E T A BL E U t en t e (

email VARCHAR (64) PRIMARY KEY ,

nome VARCHAR (64) ,

cognome VARCHAR (64) ,

via VARCHAR (128) ,


cap NUMERIC (5 ,0)

);

C R E A TE T A B LE B i g l i e t to O n l i n e (

cod CHAR (8) ,

c od Ci ne ma C HA R (8) ,

idSala SMALLINT ,

codFilm CHAR (8) ,

dataora TIMESTAMP ,

c o s t o _ a g gi u n t i v o N U M E RI C ( 4 , 2 ) ,

e ma il V AR CH AR ( 64) R EF ER EN CE S U ten te ( em ai l) ,

PRIMARY KEY(cod, codCinema ,idSala ,dataora ),



);

c) Si vuole realizzare un database relativo alle presenze di iscritti a corsi di nuoto. È stata a tal finecostruita, da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(cf_iscritto, nome_iscritto, cognome_iscritto, residenza_iscritto, cf_istruttore,

nome_istruttore, cognome_istruttore, cod_corso, nome_corso, livello,

numero_iscritti, data_lezione, ora_lezione, programma_lezione, presenza)

Nell’ipotesi che ogni corso abbia un solo istruttore e che presenza sia un attributo booleano che indichi la partecipazione di un iscritto ad una lezione , se ne determini la chiave e si individuino,esplicitandole, le dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in 3a

forma normale, preservando le dip. funzionali.

Soluzione 1.3.1. Ipotesi preliminari:

– IP1 : ogni corso abbia un solo istruttore

– IP2 : presenza sia un attributo booleano che indichi la partecipazione di un iscritto ad unalezione

Ipotesi aggiuntive:

– IP3 : ogni corso può avere più livelli

– IP4 : ogni livello di corso corso ha un certo numero iscritti


22/56


– IP5 : ogni iscritto può essere iscritto a più corsi in un dato livello

– IP6 : per ogni data di lezione è prevista un ora di lezione e relativo programma


– Iscritto: cf iscritto → nome, cognome, via, civico, cap

– Istruttore: cf istruttore → nome, cognome– Corso: cod corso → cf istruttore, nome corso (IP1)

– LivelloCorso: cod corso,livello → numero iscritti (IP3,IP4)

– IscrizioneCorso: cf iscritto,cod corso,livello → (IP5) [no dipendenza]

– Lezione: cod corso,livello,data lezione → ora lezione,programma (IP6)

– Presenza: cod iscritto, cod corso,livello,data lezione → presenza (IP2)

Gli attributi che formano la chiave sono: cf iscritto, cod corso, livello, data lezione


– Dipendenza piena dalla chiave: Presenza

– Dipendenza parziale dalla chiave: Iscritto, Corso, LivelloCorso, IscrizioneCorso,Presenza

– Dipendenza transitiva: Istruttore

Le dipendenze funzionali si trasformano nelle seguenti tabelle:

– Iscritto(cf iscritto,nome,cognome,via,civico,cap)

– Istruttore(cf istruttore,nome,cognome)

– Corso(cod corso,cf istruttore,nome corso)

– LivelloCorso(cod corso,livello,numero iscritti)

– IscrizioneCorso(cf iscritto,cod corso,livello)

– Lezione(cod corso,livello,data lezione,ora lezione,programma)

– Presenza

(cod iscritto, cod corso,livello,data lezione,presenza)d) Date le seguenti relazioni:

MEDICO (cf, nome, cognome)

PAZIENTE (cf, nome, cognome, eta)

FARMACO (codice, nome, tipo, costo)

PRESCRIZIONE (cfMedico, data, ora, codFarmaco, cfPaziente)


1) Selezionare in ordine alfabetico i medici che hanno effettuato complessivamente nel 2013 piùdi 1000 prescrizioni a pazienti con più di 60 anni

Soluzione 1.3.2. SELECT *

FROM MedicoWHERE cf IN (

SELECT DISTINCT cfMedico

FROM Pres crizi one

W HER E (( da ta _ BE TW EE N ’ 20 13 /0 1/ 01 ’ AND ’ 20 13 /1 2/ 31 ’)

A ND ( c f Pa zi en t e I N (

S E LE C T c f

FROM Paziente

WHERE et à >60)))

GROUP BY cfMedico

HAVING count (*) >1000

)

O R DE R B Y c o gn o me , n o me


23/56


2) Visualizzare per ogni farmaco di tipo ”FANS”, il numero di prescrizioni effettuate ed il numerodi pazienti a cui è stato prescritto

Soluzione 1.3.3. Soluzione con funzioni aggregazione [da risultati inattesi in MySql, contasolo sul raggruppamento non su cfPaziente]:

S EL EC T c od Fa rm ac o , c ou nt ( c o dF a rm ac o ) , c ou nt ( c f Pa zi en te )

FROM Pres criz ioneWHERE codFar maco IN (

SELECT codice

FROM Farmaco

WHERE tipo = ’ FANS ’)

GROUP BY codFar maco

Soluzione 1.3.4. Soluzione con viste:

C R EA T E V I EW f a ns 2 01 3 A S

S E LE C T c o dF a rm a co , C O UN T ( * ) A S n u m _p r e sc r i zi o n i

F RO M p re sc ri zi on e

W HE RE c od Fa r ma co IN ( SE LE CT c od ic e

F R O M f a r m a co

W H ER E ( t i po = ’ F A NS ’ ) )G R OU P B Y c o d Fa r ma c o ;

C R EA T E V I EW f a n s2 0 1 3p e r pa z i en t e A S

SELECT codFarmaco ,

C O U NT ( D I S T I N C T c f P a z i en t e ) A S n u m _ p a z ie n t i

F RO M p re sc ri zi on e

W HE RE c od Fa r ma co IN ( SE LE CT c od ic e

F R O M f a r m a co

W H ER E ( t i po = ’ F AN S ’ ) )

G R OU P B Y c o d Fa r ma c o ;

S E LE C T *

F R OM f a ns 2 01 3 N A TU R AL J O IN f a n s2 0 1 3p e r pa z i en t e ;

e) SOLO N.O.

– Illustrare le operazioni binarie dell’algebra relazionale

– Illustrare brevemente la struttura di un albero B e B+ e le modalità di ricerca evidenziandole differenze

– Con riferimento alle tabelle al punto (d) scrivere gli statement SQL relativi alle seguentioperazioni:

1) aumentare di 2 euro il costo dei farmaci di tipo “FANS”;

U P D A TE F a r m ac o

S ET c os to = c os to + 2

W H E RE t i p o = " F A NS "

2) cancellare le prescrizioni effettuate prima del 2000.

D E L E TE F R O M P r e s c r i z i o n e

W H E RE d a ta _ < ’ 2 0 0 0 / 01 / 0 1 ’ ;


24/56


1.4 Appello 27 Giugno 2014

CORSO DI LAUREA IN ING. GESTIONALE, INFORMATICA, ELETTRONICA E TELECOMU-NICAZIONI — PROVA SCRITTA DI ”BASI DI DATI E SISTEMI INFORMATIVI” E ”SISTEMIINFORMATIVI”


Una base di dati deve essere utilizzata per la gestione delle ricette mediche rilasciate da differenti medici . Ogni medico è identificato dai propri dati anagrafici e dalla propria specializzazione, mentreper ogni paziente si conosce codice fiscale, nome, cognome, sesso (indicato come M o F), data dinascita e residenza. Un medico può rilasciare delle ricette mediche identificate da un codice alfanu-merico univoco composto da 17 caratteri, data di emissione, paziente di riferimento, denominazionedell’ente di competenza, numero progressivo regionale, sigla provincia (2 caratteri) e codice ASL (3numeri). Le ricette mediche si dividono inoltre in due tipi:

– rilasciate per prescrivere farmaci: in questo caso si conosce la lista dei farmaci prescritti (perun massimo di 5) con la relativa quantità. Ogni farmaco è definito attraverso un codicealfanumerico univoco rispetto alla propria casa farmaceutica (descritta a sua volta attraverso

ragione sociale e sede), nome, tipologia, descrizione e un attributo booleano che indichi se sitratta di un farmaco generico o meno;

– rilasciate per prescrivere degli esame: in questo caso si conosce una descrizione dell’esameprescritto.

Indicare le cardinalità delle relazioni e un identificatore per ciascuna entità.


c) Si vuole realizzare un database relativo alle pubblicazioni di articoli su riviste scientifiche. E’ stataa tal fine costruita, da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(ISSNRivista, nomeRivista, genereRivista, editore, anno,

numero_rivista, numero_pagine, prefazione, idArticolo,titolo, abstract, cfAutore, nome, cognome, email, ordine)

Nell’ipotesi che ogni rivista sia pubblicata più volte in un anno e che ordine sia un valore intero usatoper elencare gli autori di una pubblicazione , se ne determini la chiave e si individuino, esplicitandole,le dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in 3a forma normale,preservando le dip. funzionali.


ATTORE (matricola, nome, cognome, nazionalit̀a)

FILM (codice, titolo, anno produzione, genere, costo produzione, totale incassi)

CAST (cod film, matricola, ore impegnate, compenso attore film)


1) Selezionare per ogni attore il film in cui ha ottenuto il compenso maggiore

2) Visualizzare i film del 2013 che presentano nel cast attori di almeno tre nazionalità diverse

e) SOLO N.O.

– Illustrare le primitive del buffer manager

– Si dia una definizione di vista aggiornabile e si illustri la sintassi SQL per creare una vista

– Descrivere le differenze tra uno schema a stella ed uno schema a fiocco di neve .


25/56

1.5. APPELLO 17 GIUGNO 21

1.5 Appello 17 Giugno

CORSO DI LAUREA IN ING. GESTIONALE, INFORMATICA, ELETTRONICA E TELECOMUNI-CAZIONI PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFOR-MATIVI”


Una base di dati deve essere utilizzata per gestire dei i dati relativi a differenti ospedali . Ogni ospe-dale è identificato da un codice univoco, nome e indirizzo. Per ciascun ospedale si conoscono inoltrei reparti che lo compongono, identificati da un codice a 6 cifre (univoco solo rispetto all’ospedale),nome e numero di posti letto. Si conoscono inoltre le informazioni relative ai dipendenti di cuisi conosce codice fiscale, nome, cognome, data di assunzione e lista di reparti in cui lavorano. Idipendenti si dividono in:

– infermieri, si conoscono gli anni di servizio;

– medici, si conosce la data di abilitazione alla professione e l’elenco di specializzazioni ottenutecon relativa data.

Si conoscono inoltre le informazioni dei pazienti (cf, nome, cognome, data di nascita) e dei ricoverieffettuati identificati da data e ora del ricovero, motivazione, codice gravit à (rosso, giallo, verde),paziente di riferimento, reparto interessato. Sarà presente infine anche un attributo booleano cheindica se il paziente è stato dimesso o meno. Occorre verificare al momento del ricovero che ci sianodei posti a disposizione nel relativo reparto.



c) Si vuole realizzare un database relativo alle gestione di gare podistiche . È stata a tal fine costruita,da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(nome_associazione_sportiva, sede_ associazione, anno_fondazione_associazione,

cf_atleta, nome_atleta, cognome_atleta, cod_evento, luogo_evento, data_evento,ora_evento, lunghezza_percorso, numero_pettorale, posizione, tempo_ottenuto)

Nell’ipotesi che ogni atleta appartenga ad un’associazione sportiva e che il numero di pettorale sia univoco solo rispetto ad un singolo evento, se ne determini la chiave e si individuino, esplicitandole,le dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in 3a forma normale,preservando le dip. funzionali.

Soluzione 1.5.1. Si determinano le dipendenze funzionali e la chiave per gli attributi della tabella

(nome_associazione_sportiva, sede_associazione, anno_fondazione_associazione,

cf_atleta, nome_atleta, cognome_atleta, cod_evento, luogo_evento, data_evento,

ora_evento, lunghezza_percorso, numero_pettorale, posizione, tempo_ottenuto)

Ipotesi preliminari:

– IP1 : ogni atleta appartiene ad una associazione

– IP2 : ogni numero di pettorale è univoco solo per il relativo evento


– Associazione: nome associazione sportiva → via, civico, cap, anno fondazione

– Atleta: cf atleta → nome atleta, cognome atleta, nome associazione sportiva

– Evento: cod evento → luogo evento, data evento, ora evento, lunghezza percorso

– Partecipazione: cod evento, numero pettorale → cf atleta, posizione, tempo ottenuto


26/56


Gli attributi che formano la chiave sono: cod evento,num pettorale


– Dipendenza piena dalla chiave: Partecipazione

– Dipendenza parziale dalla chiave: Evento

– Dipendenza transitiva: Associazione, Atleta

Le dipendenze funzionali si trasformano nelle seguenti tabelle:

– Associazione(nome associazione sportiva,via,civico,cap,anno)

– Atleta(cf atleta,nome, cognome)

– Evento(cod evento,luogo, data, ora, lunghezza)

– Partecipazione(cod evento,num pettorale,cf atleta, posizione, tempo)


R I C E TT A ( c o d ic e , n o me , t e m p o _ pr e p a r a zi o n e , d i f f i c ol t à , r e g i o n e _ a p pa r t e n e n z a )

I N G R E D I EN T E ( c o d ic e , n o me , c a l o r ie )L I S T A _ I N G RE D I E N T I ( c o d R i ce t t a , c o d I ng r e d i en t e , q u a n t it à )


1) Visualizzare, in ordine decrescente per tempo di preparazione, le ricette che prevedono l’ingre-diente ”basilico”

Soluzione 1.5.2. Query di selezione con condizione di confronto fra un valore e un elenco dasubquery con condizione, raggruppamento e condizione sui gruppi

SELECT *

FROM ricetta

WHERE codice IN (

S EL EC T c od Ri ce tt a

FROM l is ta_ in gre di en ti

WHERE c odI ngr edi ent e = (

SELECT codice

FROM in gredi ente

WHERE nome =’ basilico ’

)

)

O RD ER B Y t em po D E SC ;

2) Selezionare per ogni ingrediente il numero di regioni ed il numero di ricette in cui è utilizzato

e) SOLO N.O.

– Illustrare il concetto di decomposizione senza perdite e le relative condizioni da verificareSoluzione 1.5.3. Data una relazione R(X ) su un insieme di attributi X = X 1 ∪ X 2, si hauna decomposizione senza perdite(loseless join ) se il JOIN delle proiezioni di R su X 1 eX 2 è uguale a R (ovvero non contiene tuple spurie)

πX1(R)πX2(R) = R

R si decompone senza perdite su due relazioni se l’insieme degli attributi comuni è chiave peralmeno una delle relazioni decomposte (cond. suff. ma non necessaria).

– Descrivere le principali anomalie legate a problemi di concorrenza tra transazioni

Soluzione 1.5.4. Anomalie di concorrenza:


27/56

1.5. APPELLO 17 GIUGNO 23

1) Perdita di aggiornamento(lost update )Una transazione scrive una risorsa X dopo che è stata letta da un’altra transazione cheutilizzerà un dato errato

R1(X ) → R2(X ) →W 2(X ) →W 1(X )

la scrittura W 2(X ) è persa perché sovrascritta da W 1(X ).2) Lettura sporca(dirty read )

Una transazione effettua una modifica su una risorsa e poi effettua il rollback. Una altratransazione che abbia letto il valore utilizza un dato che non viene salvato nella base didati per effetto dell’abort:

R1(X ) →W 1(X ) → R2(X ) → rollback1 → W 2(X )

3) Lettura inconsistente(unrepeatable read )Ogni transazione che legga la stessa risorsa più volte deve ottenere lo stesso valore pertutta la durata della transazione:

R1(X ) → R2(X ) →W 2(X ) → R1(X )

4) Aggiornamento fantasma(phantom update )L’ordine delle transazioni porta ad uno stato inconsistente

R1(X ) → R1(Y ) → R2(X ) → R2(Z ) →W 2(X ) →W 2(Z ) → R1(Z )

5) Inserimento fantasma(phantom insert )Una transazione effettua operazione con dati aggregati e durante la sua esecuzione un’altratransazione effettua una INSERT.

– Descrivere le tecniche di frammentazione realizzabili su basi di dati distribuite


28/56


1.6 Appello 16 Luglio 2015

CORSO DI LAUREA IN ING. GESTIONALE, INFORMATICA, ELETTRONICA E TELECOMU-NICAZIONI — PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMIINFORMATIVI”


Una base di dati deve essere utilizzata per gestire dei i dati relativi ad un laboratorio di analisicliniche. Ogni dipendente del laboratorio è identificato da codice fiscale, nome, cognome e ruolo.Si conoscono inoltre le informazioni dei pazienti (cf, nome, cognome, data di nascita). Per ogniprelievo realizzato si conosce data e ora dello stesso, numero di campioni prelevati, paziente diriferimento e dipendente che si è occupato del prelievo. Ogni prelievo prevede una serie di esami,identificato da un codice univoco, descrizione e costo. A seconda del prelievo, si conosce l’esitodell’esame e la sua tipologia (urgente/non urgente). I prelievi si suddividono inoltre in:

– interni, realizzati nel centro di analisi, a cui è associata una data di consegna;

– esterni, realizzati in ospedale di cui si conosce ragione sociale, sede ed un attributo booleanoche indica la presenza di convenzioni con il laboratorio. Occorre infine verificare che, per ogni

prelievo, il numero di esami richiesto sia al massimo pari a tre volte il numero dei campioniprelevati.



c) Si vuole realizzare un database relativo alle ricariche di auto elettriche . È stata a tal fine costruita,da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(cod_gestore, nome_gestore, sede_gestore, cod_colonnina, indirizzo,

costo_per_kWh, targa_auto, modello_auto, cf_proprietario, nome_proprietario,

cogn_proprietario, data_ricarica, ora_ricarica, durata, kWh_ricaricati)

Nell’ipotesi che il codice di una colonnina sia univoco solo rispetto ad un gestore di energia , se nedetermini la chiave e si individuino, esplicitandole, le dipendenze funzionali. Sulla base di queste siproceda alla normalizzazione in 3a forma normale, preservando le dip. funzionali.


(IP1) l’attributo cod colonnina sia univoco solo rispetto all’ Gestore

(IP2) aggiungo ipotesi: ogni Auto ha un Proprietario

(IP3) aggiungo ipotesi: un Auto può effettuare una Ricarica per volta


(DF1) Gestore: codGestore → nome, via,civico,cap(DF2) Colonna: codGestore, codColonna → via,civico,cap,costo kWh

(DF3) Auto: targa → modello, cfProprietario (IP2)

(DF4) Proprietario: cfProprietario → nome, cognome

(DF5) Ricarica: targa,data,ora → durata, kWh caricati, codGestore, codColonna

Gli attributi che formano la chiave: targa, dataRicarica, oraRicarica


– Dipendenza funzionale piena: Ricarica

– Dipendenza funzionale parziale: Auto


29/56

1.6. APPELLO 16 LUGLIO 2015 25

– Dipendenza funzionale transitiva: Gestore, Colonna, Proprietario

Relazioni in terza forma normale:

– RICARICA(targa,data,ora,durata, kWh caricati,codGestore,codColonna)

– AUTO(targa,modello, cfProprietario)

– GESTORE(codGestore,nome, via,civico,cap)

– COLONNA(codGestore, codColonna,via,civico,cap,costo kWh)

– PROPRIETARIO(cfProprietario,nome,cognome)


F I L M ( c o d F il m , t i to l o , a nn o , g e ne r e , d u ra t a , i n c a ss o )

A T T O RE ( c o d A tt o r e , n o me , c o g no m e , n a z i o n a li t a )

C A S T ( c o d F il m , c o d At t o re , c o m p e ns o )


1) Visualizzare i film che presentano nel cast almeno 10 attori con compenso superiore a 10.000

Soluzione 1.6.2. Query di selezione con condizione, raggruppamento e condizione sui gruppi

SELECT codFilm

FROM FilmCast

W HER E c omp ens o > 100 00

G RO UP B Y c od Fi lm

H AV IN G c ou nt ( c od At to re ) > 10

2) Selezionare per ogni attore il numero di film in cui ha partecipato, visualizzando i risultati inordine decrescente sulla base di tale valore

Soluzione 1.6.3. Query di selezione con raggruppamento e ordinamento

S EL EC T c od At to re , c ou nt ( c od Fi lm )

FROM FilmCastG RO UP B Y c od At to r e

O RD ER B Y c ou nt ( c od Fi lm ) D E SC

e) SOLO N.O.

– Illustrare il funzionamento della primitiva FIX del Buffer Manager

– Illustrare la versione base e la variante ”strict” del locking a 2 fasi

– Definire il concetto di granularità di un trigger


30/56


1.7 Appello 2 Settembre 2015

CORSO DI LAUREA IN ING. GESTIONALE E ING. INFORMATICA E DELL’AUTOMAZIONE —PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI”


Una base di dati deve essere utilizzata per gestire gli annunci pubblicati su un portale online . Ogniannuncio è identificato da un codice univoco di 10 caratteri, nome, descrizione, categoria (elettroni-ca, immobili, veicoli, altro), data pubblicazione, data scadenza e utente che lo ha pubblicato. Ogniutente è caratterizzato da codice fiscale, nome, cognome e indirizzo e-mail. Per ogni annuncio èpossibile inoltre indicare una serie keywords (parole chiave) identificate attraverso codice e nome.Gli annunci si suddividono inoltre in: vendite , si conosce il prezzo indicato e il comune in cui sitrova l’oggetto; acquisti , è presente un attributo booleano che indica se l’acquisto dovrà avvenireesclusivamente con consegna di persona o meno. Ad ogni annuncio sono associate delle offerte dicui si conosce data, ora, importo, note eventuali e utente che ha inviato l’offerta. Per ogni offertaoccorre verificare che la data sia compresa tra la data di pubblicazione e di scadenza del relativoannuncio e che l’offerta non provenga dallo stesso utente che ha pubblicato l’annuncio.



c) Si vuole realizzare un database relativo alle playlist di brani musicali . È stata a tal fine costruita,da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(idUtente, nomeUtente, cognomeUtente, dataNascita, idPlaylist,

nomePlaylist, durata, data_creazione, idArtista, nomeArtista,

nazionalita, idBrano, anno, genere, posizione)

Nell’ipotesi che idPlaylist sia univoco solo rispetto ad un utente e che l’attributo posizione sia utilizzato per ordinare i brani all’interno di una playlist , se ne determini la chiave e si individuino,esplicitandole, le dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in 3a

forma normale, preservando le dip. funzionali.


– IP1 : l’attributo idPlaylist sia univoco solo rispetto ad un Utente

– IP2 : l’attributo posizione sia utilizzato per ordinare i brani all’interno di una playlist

– IP3 : ogni Brano ha più Artisti

– IP4 : ogni Playlist ha in una posizione un Brano


– DF1 : Utente idUtente → nomeUtente, cognomeUtente, dataNascita

– DF2 : Playlist idPlaylist → nomePlaylist, durata, data creazione– DF3 : Artista idArtista → nomeArtista, nazionalita

– DF4 : Brano idBrano → anno, genere

– DF5 : BranoPlaylist idUtente,idPlaylist,posizione → idBrano

L’ipotesi IP3 non da una dipendenza funzionale, in quanto idBrano,idArtista → ∅, si trasformain una relazione BranoArtista N a N senza attributi.

Gli attributi della chiave: sono idUtente,idPlaylist,posizione


– Dipendenza piena dalla chiave: BranoPlaylist


31/56

1.7. APPELLO 2 SETTEMBRE 2015 27

– Dipendenza parziale dalla chiave: Utente, Playlist

– Dipendenza transitiva: Brano, Artista

Relazioni in terza forma normale:

– Utente(idUtente,nomeUtente, cognomeUtente, dataNascita)

– Playlist(idPlaylist,nomePlaylist, durata, data creazione)

– Artista(idArtista,nomeArtista, nazionalita))

– Brano(idBrano,anno, genere)

– BranoPlaylist(idUtente,idPlaylist,posizione,idBrano)



A T T O RE ( c o d A tt o r e , n o me , c o g no m e , n a z i o n al i t à )



1) Visualizzare in ordine alfabetico i film in cui sono presenti sia Brad Pitt che Matt Damon

Soluzione 1.7.2. Query di selezione con condizione di confronto fra un valore e un elenco dasubquery ottenuto come operazione insiemistica

SELECT *

FROM Film

WHERE codFilm IN (

( S EL EC T c od Fi lm

FROM FilmCast

WHERE codAttor e = (

SELECT codAttor e

FROM Attore

WHERE nome =’ Brad ’AND cognome =’ Pitt ’

) I N TE R SE C T

( S EL EC T c od Fi lm

FROM FilmCast

WHERE codAttor e = (

SELECT codAttor e

FROM Attore

WHERE nome = ’ ’

AND cognome =’ Damon ’)

)

2) Selezionare per ogni attore la somma dei compensi ottenuti nel 2015

Soluzione 1.7.3. Query di selezione con funzione di aggregazione, condizione e raggruppa-mento

S EL EC T c od At to re , s um ( c om pe ns o )

FROM FilmCast

WHERE codFilm IN (

SELECT codFilm

FROM Film

WHERE anno =2015

)

G R OU P B Y c o dA t t or e

e) SOLO N.O.


32/56


– Illustrare la sintassi SQL per l’eliminazione di una vista, descrivendo in dettaglio le opzioniutilizzabili

– Illustrare le primitive di lock e il funzionamento della relativa tabella dei conflitti

– Definire la struttura di una pagina gestita dal buffer manager


33/56

1.8. APPELLO 22 SETTEMBRE 2015 29

1.8 Appello 22 Settembre 2015

CORSO DI LAUREA IN ING. GESTIONALE E ING. INFORMATICA E DELL’AUTOMAZIONEPROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI”


Una base di dati deve essere utilizzata per gestire un software di instant messaging per smartphone .Ogni utente è identificato dal proprio numero di cellulare, nome, data di iscrizione e data di scadenzadel servizio. Per ogni utente si conoscono le conversazioni a cui partecipa, caratterizzate da un codiceunivoco, data di inizio conversazione e tipologia (chat privata o gruppo). Se si tratta di chat privataoccorre verificare che ci siano solo due utenti nella conversazione; per le chat di gruppo si conosceinvece il numero di partecipanti, il titolo del gruppo e per ogni partecipante sar à presente un campobooleano per indicare se l’utente è amministratore del gruppo. Ogni conversazione è compostada una serie di messaggi descritti attraverso un identificativo numerico progressivo (univoco solorispetto alla conversazione), utente che l’ha inviato, stato (inviato, ricevuto o letto), data e ora diinvio. I messaggi possono essere di diverso tipo:

– testo semplice: si conosce il testo inviato;

– multimediale: si conoscono dimensione, formato ed eventuale durata del messaggio;

– posizione gps: sono indicate latitudine e longitudine del luogo inviato.



c) Si vuole realizzare un database relativo al rilascio di certificati di nascita. È stata a tal fine costruita,da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(cfCittadino, nomeCittadino, cognomeCittadino, dataNascita, codUfficio

indirizzoUfficio, telefonoUfficio, matricolaImpiegato, nomeImpiegato,

cognomeImpiegato, idAtto, annoAtto, dataRilascio)

Nell’ipotesi che idAtto si univoco solo all’interno di uno stesso anno e che ogni certificato contenga i riferimenti di padre, madre e figlio, se ne determini la chiave e si individuino, esplicitandole, ledipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in 3a forma normale,preservando le dip. funzionali.


(IP1) idAtto sia univoco solo rispetto all’ anno

(IP2) ogni Atto ha un riferimento al cittadino, al padre, alla madre

(IP3) aggiungo ipotesi ogni Impiegato lavora in un Ufficio

(IP4) aggiungo ipotesi ogni Atto ha un Impiegato responsabile


(DF1) Cittadino cfCittadino → nome, cognome, dataNascita

(DF2) Ufficio codUfficio → via, civico,cap,telefono

(DF3) Impiegato matricolaImpiegato → nome, cognome, codUfficio (IP3)

(DF4) Atto idAtto, annoAtto→ dataRilascio, matricolaImpiegato, cfCittadino, cfPadre,cfMadre (IP1,IP2,IP4)

Gli attributi che formano la chiave: idAtto, annoAtto



34/56


– Dipendenza funzionale piena: Atto

– Dipendenza funzionale parziale: nessuna

– Dipendenza funzionale transitiva: Impiegato, Ufficio, Cittadino


F I L M ( c o d F il m , t i to l o , a n no , g e ne r e , d u ra t a , i n c a ss o )

A T T O RE ( c o d A tt o r e , n om e , c o g no m e , n a z i o n a li t a )

C A S T ( c o d F il m , c o d A tt o r e , c o m p e ns o ) o )


1) Visualizzare gli attori italiani che hanno partecipato ad almeno 10 film

Soluzione 1.8.2. Query di selezione con condizione di confronto fra un valore e un elenco dasubquery

SELECT *

FROM Attore

WHERE codA ttore IN (

SELECT codAt toreFROM FilmCast

G RO UP B Y c od At t or e

H AV IN G co un t( co dF il m) >= 10

)

2) Selezionare i film in cui non è presente Johnny Depp

Soluzione 1.8.3. Query di selezione con condizione di confronto fra un valore e un elenco dasubquery

SELECT *

FROM Film

WHERE codFilm NOT IN (

S EL EC T D IS TI NC T c od Fi lmFROM FilmCast

WHERE codAt tore = (

SELECT codAt tore

FROM Attore

WHERE nome =’ Johnny ’

AND cognome =’ Deep ’

)

)

e) SOLO N.O.

– Illustrare le operazioni unarie dell’algebra relazionale

– Descrivere brevemente la struttura di un albero B, la modalità di ricerca e le differenze conun albero B+

– Illustrare l’architettura di un Data Warehouse


35/56

1.9. APPELLO 12 NOVEMBRE 2015 - MODULO I 31

1.9 Appello 12 Novembre 2015 - Modulo I

CORSO DI LAUREA IN ING. GESTIONALE E ING. INFORMATICA E DELL’AUTOMAZIONE—PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI”


Una base di dati deve essere utilizzata per gestire un sito di car sharing . Occorre memorizzare i datiriferiti a ciascun utente di cui si conosce codice fiscale, nome, cognome e e-mail. Si conoscono inoltrele informazioni delle auto utilizzate durante i viaggi, identificate da targa, modello, cilindrata, kmpercorsi e utente proprietario. Per ogni viaggio invece occorre memorizzare data e ora di partenza,auto utilizzata, numero massimo di persone, costo del viaggio, comune di partenza e di arrivo (perciascun comune si conosce CAP e nome) e lista di utenti passeggeri. Ogni passeggero indicheràanche un voto e una recensione del viaggio. Verificare inoltre che per ciascun viaggio il numero dipasseggeri non sia superiore rispetto al numero massimo indicato. Un viaggio pu ò essere di tipo:

– diretto (senza soste intermedie), di cui si conosce la lunghezza complessiva in km;

– con soste, di cui si conosce la lista dei comuni in cui verr à effettuata una sosta e la relativadurata.



c) Si vuole realizzare un database relativo ad acquisti di prodotti online . È stata a tal fine costruita,da un inesperto progettista, un’unica tabella descritta dai seguenti attributi:

(RagioneSocialeAzienda, sedeAzienda, telefonoAzienda, codProdotto,

nomeProdotto, tipologia, prezzo, IDUtente, nome Utente, cognome Utente,

e-mail, dataAcquisto, oraAcquisto, importoTotale, speseSpedizione, quantità)

Nell’ipotesi che codProdotto sia univoco solo rispetto ad una azienda e che l’attributo quantità

indichi il numero di pezzi selezionati in fase di acquisto per ciascun prodotto , se ne determini lachiave e si individuino, esplicitandole, le dipendenze funzionali. Sulla base di queste si proceda allanormalizzazione in 3a forma normale, preservando le dip. funzionali.



A T T O RE ( c o d A tt o r e , n o me , c o g no m e , n a z i o n al i t à )



1) Visualizzare, per ciascun attore, il film che ha ottenuto l’incasso maggiore

Soluzione 1.9.1. Query di selezione con condizione di confronto fra un valore e un elenco da

subqueryS EL EC T C 1 . co dA tt or e , C 1 . co dF il m

FROM Film NATURAL JOIN FilmCast AS C1

WHERE incasso >= ALL (

SE LE CT MAX ( in cas so )

FROM Film NATURAL JOIN FilmCast C2

W HER E C1 . co dA tt or e = C2 . co dA tt or e

)

2) Visualizzare i film in cui sono presenti sia attori francesi che italiani

Soluzione 1.9.2. Query di selezione con condizione di appartenenza ai risultati di operazioneinsiemistica tra subquery


36/56


SELECT *

FROM Film

WHERE codFilm IN (

( S EL EC T D IS TI NC T c od Fi lm

FROM FilmCast NATURAL JOIN Attore

WHERE naz ionali t à = ’ f r a n c e s e ’ )

INTERSECT( S EL EC T D IS TI NC T c od Fi lm

FROM FilmCast NATURAL JOIN Attore

WHERE naz ionali t à = ’ i t a l i a n a ’ )

)

d) SOLO N.O.

– Illustrare gli operatori binari dell’algebra relazionale

– Definire il concetto di transazione ed illustrare i principali comandi applicabili

– Illustrare il problema dell’impedence mismatch indicando inoltre una possibile soluzione


37/56



PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI”


Una base di dati deve essere utilizzata per gestire un sistema per l’accesso a contenuti video. Occorrememorizzare i dati riferiti a ciascun utente di cui si conosce indirizzo e-mail (univoco per ogniutente), password e data di iscrizione. Ogni utente può sottoscrivere degli abbonamenti identificatida data di inizio, data di fine, costo e tipologia (base e premium). Per ogni abbonamento siconosce anche la lista dei dispositivi associati di cui si conosce un nome e l’indirizzo MAC (codicealfanumerico univoco di 12 caratteri). Per gli abbonamenti “base” è possibile associare un solodispositivo.

Si conoscono inoltre le informazioni dei contenuti video presenti nel sistema identificati da codice,titolo, data di pubblicazione, giudizio (compreso tra 1 e 5) e un attributo booleano per indicare seil contenuto è adatto o meno ai bambini. I contenuti si dividono in:

– film, si conosce la durata e l’anno di produzione;

– serie TV, si conosce il genere, una descrizione e la lista di stagioni. Ogni stagione è identificataattraverso un id numerico progressivo (univoco all’interno della serie TV) ed il numero dipuntate.

Si conosce infine l’elenco dei contenuti, visualizzati utilizzando ciascun abbonamento, con la relativadata e ora di visione. Indicare le cardinalità delle relazioni e un identificatore per ciascuna entit à.

Soluzione 1.10.1. Progettazione concettuale dello schema Entità-Relazioni per lo scenario digestione di un sistema di accesso a contenuti video (diagramma schema E-R in figura 1.4).

1) Entità Utente con chiave email attributi psw , dataIscr .

2) Entità debole Abbonamento con chiave parziale dataInizio e dipendenza da Utente, eattributi dataFine e costo.

3) Relazione tra Utente e Abbonamento, partecipazione totale, cardinalit̀a 1 utente : Nabbonamenti.

4) Entità debole Visione, chiave parziale data,ora, dipendenza da Abbonamento.

5) Relazione tra Abbonamento e Visione, partecipazione totale, cardinalità 1 abbonamento:N visioni.

6) Entità Contenuto, chiave cod, attributi titolo, data , giudizio (BR1: dominio valori intericompresi 1-5), bambini (BR2: dominio booleano)

7) Gerarchia Contenuto ISA, generalizzazione totale esclusiva, entità figlie:

i. Entità Film, attributi durata , anno

ii. Entità Serie TV, attributi genere , desc

8) Entità Stagione, chiave id, attributo num puntate 9) Relazione tra Serie TV e Stagione, partecipazione totale, cardinalità 1 serie TV : N stagioni.

10) Gerarchia Abbonamento ISA, generalizzazione totale esclusiva, entità figlie:

i. Entità Base

ii. Entità Premium

11) Entità Dispositivo, chiave MAC, attributo nome

12) R

Soluzioni prove scritte del corso di Base di dati e Sistemi Informativi - Corso di Laurea in...

Documents

Transcript of Soluzioni prove scritte del corso di Base di dati e Sistemi Informativi - Corso di Laurea in...