Soluzioni prove scritte del corso di Base di dati e Sistemi Informativi - Corso di Laurea in Ingegneria Informatica e dell'Automazione - Politecnico di Bari

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. Loseto Dipartimento di Ingegneria Elettrica e dell’Informazione Politecnico di Bari

a cura di Marco Salvatore Vanad`ıa [email protected]

29 aprile 2016

ii

a Giulia

Il presente documento è rilasciato sotto licenza cCreative Commons 3.0 by-sa-nc c b n a. ´ consentita la creazione di opere derivate, traduzioni, adattamenti, totali o parziali, fatta salva E l’attribuzione dell’autore originale e il mantenimento della licenza. Marco Salvatore Vanad`ıa Politecnico di Bari

Indice 1 Soluzioni appelli di Base di Dati e Sistemi Informativi 1.1 Appello 5 Febbraio 2014 . . . . . . . . . . . . . . . . . . . 1.2 Appello 28 Febbraio 2014 . . . . . . . . . . . . . . . . . . 1.3 Appello 29 Aprile 2014 . . . . . . . . . . . . . . . . . . . . 1.4 Appello 27 Giugno 2014 . . . . . . . . . . . . . . . . . . . 1.5 Appello 17 Giugno . . . . . . . . . . . . . . . . . . . . . . 1.6 Appello 16 Luglio 2015 . . . . . . . . . . . . . . . . . . . . 1.7 Appello 2 Settembre 2015 . . . . . . . . . . . . . . . . . . 1.8 Appello 22 Settembre 2015 . . . . . . . . . . . . . . . . . 1.9 Appello 12 Novembre 2015 - Modulo I . . . . . . . . . . . 1.10 Appello 5 Febbraio 2016 . . . . . . . . . . . . . . . . . . . 1.11 Appello 18 Febbraio 2016 . . . . . . . . . . . . . . . . . . 1.12 Esonero 20 Aprile 2016 . . . . . . . . . . . . . . . . . . . . 1.13 Traccia parziale appello 16 Febbraio 2012 . . . . . . . . . Elenco delle figure . . . . . . . . . . . . . . . . . . . . . . . . . Indice analitico . . . . . . . . . . . . . . . . . . . . . . . . . . .

iii

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

. . . . . . . . . . . . . . .

1 2 9 15 20 21 24 26 29 31 33 39 44 49 50 50

Capitolo 1

Soluzioni appelli di Base dei Dati e Sistemi Informativi

1

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 TELECOMUNICAZIONI —PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI” a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per la gestione di opere d’arte all’interno di musei. Occorre quindi memorizzare i dati relativi ai musei di cui si conosce un codice alfanumerico, nome e indirizzo. Ogni museo è suddiviso in aree caratterizzate da un identificativo numerico, univoco solo all’interno del singolo museo, nome e da un valore booleano che indica la presenza di videosorveglianza. Per ogni opera d’arte si conosce invece un codice univoco, nome, datazione, valore e l’area in cui è esposta. In particolare le opere con valore superiore a 10.000 euro dovranno trovarsi solo in aree con videosorveglianza. Le opere d’arte sono classificabili come: – dipinti: occorre memorizzare la tecnica utilizzata e la lista di operazioni di restauro a cui eventualmente l’opera è stata sottoposta. Per ogni operazione si conosce la data di inizio e fine restauro ed il costo; – sculture: occorre memorizzare il materiale e l’altezza della scultura; – altro: di cui si conosce una breve descrizione. ` infine necessario tenere traccia per ogni opera dell’artista (o degli artisti) che l’hanno realizzata E identificati attraverso un codice univoco, nome, cognome e nazionalità. Indicare le cardinalità delle relazioni e un identificatore per ciascuna entità. Soluzione 1.1.1. Progettazione concettuale dello schema Entità-Relazioni per lo scenario di gestione di opere d’arte nei musei (diagramma schema E-R in figura 1.1). Costrutti del modello concettuale: entit` a Museo chiave codice, attributi nome, attributi composti sede (normalizzato in attributi atomici: via, civico, cap) entit` a debole Area chiave id chiave esterna Museo, attributi museo, videosorveglianza, business rule videosorveglianza booleano (vincolo dominio) entit` a debole Opera chiave codice chiave esterna Area, attributi nome, datazione, valore business rule se valore>10000) videosorveglianza=1 entit` a Artista chiave codice, attributi nome, cognome, nazionalit` a gerarchia is-a generalizzazione totale esclusiva Opera entit` a figlie: entit` a Dipinto attributi tecnica entit` a Scultura attributi materiale, altezza entit` a Altro attributi descrizione entit` a debole Restauro chiave esterna Dipinto attributi costo, data inizio, data fine relazione Museo Area, cardinalit` a 1:N, partecipazione obbligatoria di Area relazione Area Opera, cardinalit` a 1:N, partecipazione obbligatoria di Opera relazione Artista Opera, cardinalit` a N:N, partecipazione non obbligatoria relazione Dipinto Restauro, cardinalit` a 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’entità padre.

1.1. APPELLO 5 FEBBRAIO 2014

3

Accorpamento/partizionamento di entit` a/associazioni Non sono necessari accorpamenti/partizionamenti. Eliminazione attributi composti mici via, civico, cap.

– Attributo indirizzo di Museo sostituito da attributi ato-

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

cod via

Museo

id 1

N

nome

Area

civico cap

1

nome cognome

cf via

videosorveglianza

Artista

cod

N

N

N

nome

Opera

civico cap

datazione valore

ISA

Dipinto

tecnica

1

N Restauro

Scultura

materiale

Altro altezza

desc

costo data inizio data fine

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 integrit` a. 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 è risolta la gerarchia Opera con le entità figlie deboli Dipinto, Scultura e Altro 4) Dipinto(codMuseo,idArea,codOpera,tecnica) 5) Scultura(codMuseo,idArea,codOpera,materiale)

4

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI 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: CREATE TABLE Museo ( codice nome via civico cap );

CHAR (10) PRIMARY KEY , VARCHAR (128) , VARCHAR (128) , VARCHAR (8) , NUMERIC (5 ,0)

CREATE TABLE Area ( codMuseo CHAR (10) , idArea CHAR (6) , nome VARCHAR (128) , videosorveglianza BIT , PRIMARY KEY ( codMuseo , idArea ) , FOREIGN KEY ( codMuseo REFERENCES Museo ( codice ) ); CREATE TABLE Opera ( codMuseo CHAR (10) , idArea CHAR (6) , codOpera CHAR (16) , nome VARCHAR (128) , datazione DATE , valore NUMERIC (10 ,2) , PRIMARY KEY ( codMuseo , idArea , codOpera ) , FOREIGN KEY ( codMuseo , idArea ) REFERENCES Area ( codMuseo , idArea ) ); CREATE ASSERTION Videosorveglianza CHECK ( SELECT * FROM Opera JOIN Area ON Opera . codMuseo = Area . codMuseo AND Opera . idArea = Area . idArea WHERE Opera . valore >10000 AND Area . videosorveglianza =1 );

CREATE TABLE Dipinto ( codMuseo CHAR (10) , idArea CHAR (6) , codOpera CHAR (16) , tecnica VARCHAR (128) , PRIMARY KEY ( codMuseo , idArea , codOpera ) , FOREIGN KEY ( codMuseo , idArea , codOpera ) REFERENCES Opera ( codMuseo , idArea , codOpera ) ); CREATE TABLE Scultura ( codMuseo

CHAR (10) ,


5

idArea CHAR (6) , codOpera CHAR (16) , materiale VARCHAR (128) , altezza FLOAT , PRIMARY KEY ( codMuseo , idArea , codOpera ) , FOREIGN KEY ( codMuseo , idArea , codOpera ) REFERENCES Opera ( codMuseo , idArea , codOpera ) ); CREATE TABLE AltraOpera ( codMuseo CHAR (10) , idArea CHAR (6) , codOpera CHAR (16) , descrizione VARCHAR (256) , PRIMARY KEY ( codMuseo , idArea , codOpera ) , FOREIGN KEY ( codMuseo , idArea , codOpera ) REFERENCES Opera ( codMuseo , idArea , codOpera ) ); CREATE TABLE Restauro ( codMuseo CHAR (10) , idArea CHAR (6) , codDipinto CHAR (16) , costo NUMERIC (10 ,2) , data_inizio DATE , data_fine DATE , PRIMARY KEY ( codMuseo , idArea , codDipinto ) , FOREIGN KEY ( codMuseo , idArea , codDipinto ) REFERENCES Dipinto ( codMuseo , idArea , codOpera ) ); CREATE TABLE Artista ( cf nome cognome nazionalit ` a );

CHAR (16) PRIMARY KEY , VARCHAR (128) , VARCHAR (128) , VARCHAR (32)

CREATE TABLE ArtistaOpera ( codMuseo CHAR (10) , idArea CHAR (6) , codOpera CHAR (16) , cfArtista CHAR (16) , PRIMARY KEY ( codMuseo , idArea , codOpera ) , FOREIGN KEY ( codMuseo , idArea , codOpera ) REFERENCES Opera ( codMuseo , idArea , codOpera ) , FOREIGN KEY ( cfArtista ) REFERENCES Artista ( cf ) );

` stata a tal fine c) Si vuole realizzare un database relativo alle prenotazioni di camere d’albergo. E costruita, da un inesperto progettista, un’unica tabella descritta dai seguenti attributi: (cod_albergo, nome_albergo, citt` a, 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 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.

6

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI Soluzione 1.1.3. Ipotesi: 1) numero camera univoco nell’albergo 2) un cliente pu` o prenotare pi` u camere 3) lo stesso cliente pu` o prenotare la stessa camera in date diverse Dipendenze funzionali: a, stelle 1) Albergo cod albergo → nome, citt` 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 le condizioni 1 e 3. Le relazioni risultanti dalla normalizzazione in 3a forma normale che preservano le dip. funz. a, stelle) 1) Albergo(cod albergo,nome, citt` 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, citt` a, 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 SELECT FROM ON WHERE AND GROUP BY ORDER BY

piva_operatore , sum ( kWh_consumati ) as kWh_forniti abitazione JOIN consumi codice = codAbitazione abitazione . citt ` a = ’ Bari ’ consumi . data BETWEEN ’ 2013/01/01 ’ AND ’ 2013/12/31 ’ piva_operatore kWh_forniti DESC


7

2) Selezionare le abitazioni che durante il 2013 non hanno mai presentato un consumo giornaliero superiore ai 5 kWh Soluzione 1.1.5. Query di selezione con condizione e valore in elenco da subquery con condizione SELECT FROM WHERE

* abitazione codice IN ( SELECT DISTINCT codAbitazione FROM consumi WHERE consumi . data BETWEEN ’ 2013/01/01 ’ AND ’ 2013/12/31 ’ AND codAbitazione NOT IN ( SELECT DISTINCT codAbitazione FROM consumi WHERE consumi . data BETWEEN ’ 2013/01/01 ’ AND ’ 2013/12/31 ’ AND kWh_consumati > 5))

e) SOLO N.O. – Illustrare la sintassi di un trigger, descrivendo in particolare il concetto di granularità Soluzione 1.1.6. In una base di dati attiva è possibile attivare un comportamento reattivo a modifiche del database con un processore di regole di produzione basate sul paradigma Evento-Condizione-Azione. La sintassi di definizione di un trigger: CREATE TRIGGER NomeTrigger AFTER|BEFORE ← modalit` a INSERT|UPDATE|DELETE ← evento ON TabellaTarget [REFERENCING [OLD AS OldAlias ], [NEW AS NewAlias ] ] ← alias referenze [FOR EACH [STATEMENT|ROW]] ← granularit` a [WHEN predicato condizione ] ← condizione per granularit` a ROW statementSQL ← azione Un trigger pu` o essere attivato immediatamente prima o dopo una primitiva di modifica dei ` dati (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 può essere sottoposta a condizione espressa da un predicato. L’azione eseguita è espressa da un statement SQL, eventualmente contenente primitive di linguaggio proprietarie. ` a livello di tupla, iterativaUna primitiva di evento pu` o essere controllata con granularita mente su tutte le tuple (sotto eventuale condizione WHEN) con l’esecuzione dell’azione dichiarata nello statement SQL del trigger, con le seguenti priorità decrescenti dettate dalla modalit` ae granularit` a: 1) 2) 3) 4)

BEFORE STATEMENT BEFORE ROW AFTER ROW AFTER STATEMENT

– Descrivere le propriet` a acide delle transazioni Soluzione 1.1.7. Le proprietà acide delle transazioni sono: Atomicit` a Una transazione è una unità atomica indivisibile di lavoro: le operazioni definite nella sequenza compresa tra BoT e EoT devono lasciare la base di dati o nello stato precedente alla esecuzione della transazione (abort) o devono essere applicati tutti gli effetti delle operazioni eseguite (commit).

8

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI Consistenza Una transazione non deve violare i vincoli di integrità della base di dati. Il compilatore del Data Definition Language del DBMS crea i vincoli di dominio, associati ai tipi predefiniti, i vincoli di integrità referenziale associati alla definizione di chiavi primarie (PRIMARY KEY) e chiavi esterne (FOREIGN KEY), i vincoli di integrità espressi da condizioni su attributi (CHECK), e condizioni espresse da asserzioni e trigger in basi di dati attive. Prima e dopo una transazione la base di dati si trova in uno stato consistente, solo durante le modifiche intermedie è possibile avere esclusivamente nel buffer in memoria degli stati inconsistenti, ma questi non sono mai archiviati nel DB senza prima passare il controllo di consistenza. Isolamento In un sistema di elaborazione di transazioni concorrenti, ogni transazione è eseguita indipendentemente dalle altre. L’effetto di una transazione non cambia e non deve dipendere dall’effetto di altre transazioni. Il fallimento di una transazione non deve interferire con altre transazioni in esecuzione. L’isolamento viene garantito dal controllore della concorrenza che si occupa di schedulare l’esecuzione fuori ordine e in parallelo delle operazioni delle transazioni garantendo l’equivalenza alla esecuzione della sequenza schedule seriale (transazioni nell’ordine originale di arrivo non inframmezzate tra loro). Durabilit` a Gli effetti di una transazione andata a buon fine con una richiesta di commit devono essere resi persistenti della base di dati. Il controllore di affidabilità si assicura che le modifiche apportate dalle operazioni di una transazione nel buffer di memoria centrale siano effettivamente scritte nella base di dati in memoria di massa. In caso di malfunzionamenti hardware o software il controllore di affidabilità fa uso delle informazioni ridondanti registrate nei file di log di sistema e delle transazioni per garantire la persistenza delle transazioni andate in commit. – Illustrare l’architettura di un DataWarehouse, descrivendo brevemente le operazioni svolte da ciascun modulo


1.2

9


a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per la gestione delle informazioni relative ai contratti di telefonia mobile in Italia. Ogni contratto è 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 su cui il contratto è attivato. Ad ogni SIM card è associato un codice identificativo, univoco solo al’interno della compagnia telefonica di riferimento, un numero telefonico (composto da prefisso internazionale, prefisso di 3 cifre e numero di 7 cifre) ed una capacità di memorizzazione. Alla SIM 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 anche del nome e della sede legale. I contratti telefonici si dividono inoltre in due tipi, le cui caratteristiche sono di seguito descritte: – contratti in abbonamento: interessa tracciare la lista dei servizi forniti, il canone mensile e un valore booleano che indica la disponibilità di una linea dati; occorre verificare che i contratti in abbonamento che hanno una linea dati disponibile siano attivi su SIM card con capacit` a di almeno 128k. – contratti con ricaricabile: interessa memorizzare la data dell’ultima ricarica e lo stato del contratto (pu` o essere “attivo” o “non attivo”); occorre verificare che se l’ultima ricarica è avvenuta prima di un anno fa l’abbonamento risulti non attivo. Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. Soluzione 1.2.1. Progettazione dello schema Entità-Relazioni per lo scenario di gestione delle informazioni relative a contratti di telefonia mobile in Italia (diagramma schema E-R in figura 1.2). Costrutti del modello concettuale: entit` a Cliente chiave codice fiscale attributi composti dati anagrafici entit` a debole SIM chiave parziale codice partecipazione totale relazione con compagnia telefonica attributo composto numero telefonico attributo capacit` a memorizzazione business rule (BR1) numero con prefisso internazionale italiano “+39” entit` a compagnia telefonica chiave ragione sociale attributi nome attributo composto sede gerarchia IS-A entit` a debole Contratto chiave parziale codice chiave esterna dipendenza da Cliente, Sim attributi data stipula, durata entit` a figlia Contratto in abbonamento attributo multivalore lista servizi forniti attributo canone mensile, linea dati business rule (BR2) se linea dati =1 capacit` a sim≥128 (vincolo integrità) business rule (BR3) linea dati booleano [vincolo dominio] entit` a figlia Contratto con ricaricabile

10

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI 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=‘non attivo’ [vincolo integrit` a] relazione Contratto-Cliente, cardinalità N:1, partecipazione totale relazione Contratto-SIM, cardinalità 1:1, partecipazione totale relazione SIM-Compagnia telefonica, cardinalità N:1, partecipazione totale relazione Abbonamento-Servizio, cardinalità N:N, partecipazione parziale Normalizzazione schema concettuale: Analisi ridondanze L’attributo derivabile stato contratto viene modificato automaticamente da un trigger definito dalla business rule BR5. Eliminazione generalizzazioni Si risolve la generalizzazione Contratto con le figlie deboli Abbonamento e Ricaricabile con associazioni 1:1 con chiave esterna verso l’entità padre. Accorpamento/partizionamento di entit` a/associazioni – Attributo multivalore lista servizi partizionato in nuova entit` a Servizio con chiave codice, attributi desc, costo mensile, e nuova relazione molti-a-molti con entità Contratto in abbonamento Eliminazione attributi composti – Attributo dati anagrafici di Cliente sostituito da attributi 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. b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrità. 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,capacit` a,pref internazionale,prefisso,numero) si è risolta la gerarchia Contratto con le entità 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: CREATE TABLE CompagniaTelefonic a ( ragione_sociale VARCHAR (25) PRIMARY KEY , nome VARCHAR (50) , via VARCHAR (50) , civico VARCHAR (10) , cap NUMERIC (5 ,0) ); CREATE TABLE Sim ( codSim

NUMERIC (20) ,


11

nomecognome cf

ragione sociale

Cliente

cod

data stipula

via civico

TelCom

1

durata

nome

cod

N 1

Contratto

1

pref int

1

cap

N

capacità

SIM

pref

numero

ISA data ultima ricarica Abbonamento linea dati

Ricaricabile

N canone mensile

N Servizio

stato contratto

cod desc costo mensile

Figura 1.2: Diagramma E/R gestione servizi telefonia

ragSocComp

VARCHAR (35) REFERENCES CompagniaTelefonica ( ragione_sociale ) , p re fi s so _ in te r na z io na l e VARCHAR (4) CHECK ( VALUE IN ( ’ +39 ’)) , prefisso NUMERIC (3 ,0) , numero NUMERIC (7 ,0) , capacit ` a INTEGER , PRIMARY KEY ( codSim , ragSocComp ) ); CREATE TABLE Cliente ( CF nome cognome via civico cap );

CHAR (16) PRIMARY KEY , VARCHAR (30) NOT NULL , VARCHAR (50) NOT NULL , VARCHAR (50) , VARCHAR (10) , NUMERIC (5 ,0)

CREATE TABLE Contratto ( codContratto NUMERIC (7 ,0) PRIMARY KEY , cfCliente CHAR (16) REFERENCES Cliente ( CF ) , codSim NUMERIC (10) , ragSocComp VARCHAR (25) ,

12

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI dataStipula DATE , durata INTERVAL YEAR (2) TO MONTH (2) , FOREIGN KEY ( codSim , ragSocComp ) REFERENCES Sim ( codSim , ragSocComp ) ); CREATE TABLE Abbonamento ( codContratto NUMERIC (7 ,0) PRIMARY KEY REFERENCES Contratto ( codContratto ) , canone_mensile NUMERIC (5 ,2) , linea_dati BIT ); CREATE ASSERTION SimAbbonamento CHECK ( SELECT * FROM ( Abbonamento NATURAL JOIN Contratto ) NATURAL JOIN SIM WHERE Abbonamento . linea_dati =1 AND SIM . capacit ` a >=128 ); CREATE TABLE Ricaricabile ( codContratto NUMERIC (7 ,0) PRIMARY KEY , cfCliente CHAR (16) REFERENCES Cliente ( CF ) , data_ultima_ricaric a DATE , stato VARCHAR (10) CHECK ( VALUE IN ( ’ attivo ’ , ’ non attivo ’ )) ); CREATE ASSERTION StatoSimRicaricabile CHECK ( SELECT * FROM ( Abbonamento NATURAL JOIN Contratto ) NATURAL JOIN SIM WHERE ( stato = ’ attivo ’ AND CURRENT_DATE () - Sim . data_ultima_ricarica <=365) OR ( stato = ’ non attivo ’ AND CURRRENT_DATE () - Sim . data_ultima_ricarica >36 ); CREATE TABLE Servizio ( codServizio NUMERIC (5 ,0) PRIMARY KEY , desc VARCHAR (50) , costo_mensile NUMERIC (5 ,2) ); CREATE TABLE ServizioAbbonament o ( codServizio NUMERIC (5 ,0) REFERENCES Servizio ( codServizio ) , codAbbonamento NUMERIC (7 ,0) REFERENCES Abbonamento ( codContratto ) , PRIMARY KEY ( codServizio , codAbbonamento ) );

` stata c) Si vuole realizzare un database relativo alla assegnazione delle postazioni in un call center. E a 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 postazione in ogni turno, ma che gli venga assegnato sempre lo stesso numero di telefono e che num telefonate sia il numero di telefonate che un operatore fa in un determinato turno, 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.


13

Soluzione 1.2.3. Ipotesi: (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 è 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 è affidata una specifica postazione Dipendenze funzionali: (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 postazione num 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 funzionale X → Y definita deve verificare almeno una delle seguenti: a) X è una superchiave di R, b) ogni attributo in Y è contenuto in almeno una chiave di R. d) Date le seguenti relazioni: 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 accademico 2013/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 con subquery annidata SELECT FROM WHERE

)

* Corso_di_Laurea codice IN ( SELECT codice_corso FROM Studente NATURAL JOIN Iscrizione_Annuale AS A WHERE anno_accademico = ’ 2013/2014 ’ AND anno_iscrizione = ’ 2012/2013 ’ GROUP BY codice_corso HAVING count (*) = ( SELECT count (*) FROM Studente NATURAL JOIN Iscrizione_Annua le WHERE anno_accademico = ’ 2012/2013 ’ AND anno_iscrizione = ’ 2012/2013 ’ AND codice_corso = A . codice_corso )

14

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI 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 con condizione SELECT FROM WHERE AND

S .* , crediti_sostenuti Studente S NATURAL JOIN Iscrizione_Annuale anno_iscrizione = ’ 2012/2013 ’ S . matricola NOT IN ( SELECT matricola FROM Iscrizione_Annuale WHERE matricola = S . matricola AND anno_accademico = ’ 2013/2014 ’

)

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 è una tabella virtuale definita su una lista di attributi appartenenti ad altre viste o tabelle di base dello schema le cui tuple sono il risultato di una query di selezione che ne estrae i valori. Quando è possibile determinare in modo univoco a quale attributo di quale tabella base appartengono tutti i valori nella vista si ha una vista aggiornabile. Sotto le seguenti ipotesi è possibile eseguire una operazione di UPDATE su una vista aggiornabile: – – – – –

non non non non non

pu` o avere attributi espressi come funzioni di aggregazione o calcolo di espressioni pu` o applicare un selettore DISTINCT pu` o effettuare JOIN tra tabelle pu` o raggruppare e filtrare dati con direttive GROUP BY e HAVING presenta condizioni WHERE con subquery

Nella definizione di vista aggiornabile è inoltre possibile specificare l’opzione WITH [LOCAL| CASCADED] CHECK OPTION per impedire che una modifica a seguito di un UPDATE abbia come risultato tuple che non rispettano il predicato di selezione della vista. L’opzione LOCAL serve a richiedere un controllo limitato alla vista definita, l’opzione CASCADED richiede un controllo propagato alle viste nidificate. – Descrivere brevemente i livelli di isolamento in SQL Soluzione 1.2.7. A partire da SQL:1999 è possibile indicare per ogni transazione il livello di isolamento richiesto al controllore di concorrenza basato sul protocollo di locking in 2 fasi 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. Evita tutte le anomalie eccetto l’inserimento fantasma (phantom insert) perché non utilizza i lock di predicato. Prestazioni migliori a scapito della consistenza. 3) read committed: utilizza lock condivisi di lettura ma non rispetta il 2PL stretto. Evita le anomalie di lettura sporca ma è affetto da tutte le altre anomalie. Ottime prestazioni per 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 esclusivi in scrittura di altre transazioni. Utilizzata per transazioni in sola lettura con le massime prestazioni. Pu` o presentare tutte le anomalie esclusa la perdita di aggiornamento (per trans. sola lettura). – Illustrare il problema dell’impedence mismatch, l’utilità e la sintassi di un cursore

1.3. APPELLO 29 APRILE 2014

1.3

15

Appello 29 Aprile 2014

a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per la programmazione di film in differenti cinema. Ogni cinema è identificato da un codice univoco, nome, indirizzo e lista dei servizi disponibili all’interno del cinema. Ogni cinema è composto da pi` u sale di cui si conosce un numero identificativo, univoco solo al’interno del cinema di riferimento, capienza e un attributo booleano che indica se la sala è attrezzata per le proiezioni in 3D. Si conoscono inoltre le informazioni sui film identificati da un codice (alfanumerico di 8 caratteri), nome, durata in minuti e genere (azione, fantasy, thriller, commedia o altro). Occorre memorizzare la programmazione realizzata dai cinema: per ogni proiezione si conosce data e ora, film proiettato, sala di riferimento, costo del biglietto e numero di posti prenotati (verificando che non siano superiori alla capienza della sala). Per ogni proiezione è possibile acquistare dei biglietti, ogni acquisto è identificato da un codice univoco e dal posto prenotato. Gli acquisti 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 che ha effettuato l’acquisto, di cui si conoscono i dati anagrafici. Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. genere cod nome

id capienza sala3D

data ora costo posti prenotati 1

Film

proietta

N

Proiezione

durata min

N

avviene in

1

Sala

1

posto prenotato cod

N app

N cod

1

Biglietto

Cinema N ISA email cognomenome

Utente

via

1 cap civico

N

offre Online

Botteghino N

costo aggiuntivo sconto id

Servizio

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

b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrit` a.

nomevia

civ cap

16

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI CREATE TABLE Film ( cod nome genere durata_min

CHAR (8) PRIMARY KEY , VARCHAR (64) NOT NULL , VARCHAR (10) CHECK ( VALUE IN ’ azione ’ , ’ commedia ’ , ’ fantasy ’ , ’ thriller ’ , ’ altro ’) , SMALLINT

); CREATE TABLE Cinema ( cod nome via civico cap );

CHAR (8) PRIMARY KEY , VARCHAR (64) NOT NULL , VARCHAR (128) , VARCHAR (8) , NUMERIC (5 ,0)

CREATE TABLE Servizio ( id desc );

INTEGER PRIMARY KEY , VARCHAR (64)

CREATE TABLE ServiziCinema ( codCinema CHAR (8) REFERENCES Cinema ( cod ) , idServizio INTEGER REFERENCES Servizio ( id ) , PRIMARY KEY ( codCinema , idServizio ) ); CREATE TABLE Sala ( codCinema CHAR (8) REFERENCES Cinema ( cod ) , id SMALLINT , capienza SMALLINT , sala3D BIT , PRIMARY KEY ( codCinema , id ) ); CREATE TABLE Proiezione ( codCinema CHAR (8) , idSala SMALLINT , codFilm CHAR (8) REFERENCES Film ( cod ) , dataora TIMESTAMP , costo NUMERIC (4 ,2) posti_prenotati SMALLINT , PRIMARY KEY ( codCinema , idSala , codFilm , dataora ) , FOREIGN KEY ( codCinema , idSala ) REFERENCES Sala ( codCinema , id ) ); CREATE TABLE Biglietto ( cod CHAR (8) , codCinema CHAR (8) , idSala SMALLINT , codFilm CHAR (8) , dataora TIMESTAMP , posto_prenotato CHAR (8) , PRIMARY KEY ( cod , codCinema , idSala , dataora ) , FOREIGN KEY ( codCinema , idSala , codFilm , dataora ) REFERENCES Proiezione ( codCinema , idSala , codFilm , dataora ) );


17

CREATE TABLE BigliettoBotteghino ( cod CHAR (8) , codCinema CHAR (8) , idSala SMALLINT , codFilm CHAR (8) , dataora TIMESTAMP , sconto NUMERIC (4 ,2) , PRIMARY KEY ( cod , codCinema , idSala , dataora ) , FOREIGN KEY ( codCinema , idSala , codFilm , dataora ) REFERENCES Proiezione ( codCinema , idSala , codFilm , dataora ) ); CREATE TABLE Utente ( email nome cognome via civico cap );

VARCHAR (64) PRIMARY KEY , VARCHAR (64) , VARCHAR (64) , VARCHAR (128) , VARCHAR (8) , NUMERIC (5 ,0)

CREATE TABLE BigliettoOnline ( cod CHAR (8) , codCinema CHAR (8) , idSala SMALLINT , codFilm CHAR (8) , dataora TIMESTAMP , costo_aggiuntivo NUMERIC (4 ,2) , email VARCHAR (64) REFERENCES Utente ( email ) , PRIMARY KEY ( cod , codCinema , idSala , dataora ) , FOREIGN KEY ( codCinema , idSala , codFilm , dataora ) REFERENCES Proiezione ( codCinema , idSala , codFilm , dataora ) );

` stata a tal fine c) Si vuole realizzare un database relativo alle presenze di iscritti a corsi di nuoto. E costruita, 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 una lezione Ipotesi aggiuntive: – IP3 : ogni corso pu` o avere pi` u livelli – IP4 : ogni livello di corso corso ha un certo numero iscritti

18

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI – IP5 : ogni iscritto pu` o essere iscritto a pi` u corsi in un dato livello – IP6 : per ogni data di lezione è prevista un ora di lezione e relativo programma Dipendenze funzionali: – – – – – – –

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 Classificazione delle dipendenze funzionali: – 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) esprimere in SQL le seguenti interrogazioni: 1) Selezionare in ordine alfabetico i medici che hanno effettuato complessivamente nel 2013 pi` u di 1000 prescrizioni a pazienti con pi` u di 60 anni Soluzione 1.3.2. SELECT FROM WHERE

Medico cf IN ( SELECT FROM WHERE

GROUP BY HAVING ) ORDER BY cognome , nome

*

DISTINCT cfMedico Prescrizione (( data_ BETWEEN ’ 2013/01/01 ’ AND ’ 2013/12/31 ’) AND ( cfPaziente IN ( SELECT cf FROM Paziente WHERE et ` a >60))) cfMedico count (*) >1000


19

2) Visualizzare per ogni farmaco di tipo ”FANS”, il numero di prescrizioni effettuate ed il numero di pazienti a cui è stato prescritto Soluzione 1.3.3. Soluzione con funzioni aggregazione [da risultati inattesi in MySql, conta solo sul raggruppamento non su cfPaziente]: SELECT FROM WHERE

GROUP BY

codFarmaco , count ( codFarmaco ) , count ( cfPaziente ) Prescrizione codFarmaco IN ( SELECT codice FROM Farmaco WHERE tipo = ’ FANS ’) codFarmaco

Soluzione 1.3.4. Soluzione con viste: CREATE VIEW SELECT FROM WHERE

fans2013 AS codFarmaco , COUNT (*) AS num_prescrizioni prescrizione codFarmaco IN ( SELECT codice FROM farmaco WHERE ( tipo = ’ FANS ’ )) GROUP BY codFarmaco ;

CREATE VIEW fans2013perpaziente AS SELECT codFarmaco , COUNT ( DISTINCT cfPaziente ) AS num_pazienti FROM prescrizione WHERE codFarmaco IN ( SELECT codice FROM farmaco WHERE ( tipo = ’ FANS ’ )) GROUP BY codFarmaco ; SELECT * FROM fans2013 NATURAL JOIN fans2013perpaziente ;

e) SOLO N.O. – Illustrare le operazioni binarie dell’algebra relazionale – Illustrare brevemente la struttura di un albero B e B+ e le modalità di ricerca evidenziando le differenze – Con riferimento alle tabelle al punto (d) scrivere gli statement SQL relativi alle seguenti operazioni: 1) aumentare di 2 euro il costo dei farmaci di tipo “FANS”; UPDATE Farmaco SET costo = costo +2 WHERE tipo = " FANS "

2) cancellare le prescrizioni effettuate prima del 2000. DELETE FROM Prescrizione WHERE data_ < ’ 2000/01/01 ’;

20


1.4

Appello 27 Giugno 2014

CORSO DI LAUREA IN ING. GESTIONALE, INFORMATICA, ELETTRONICA E TELECOMUNICAZIONI — PROVA SCRITTA DI ”BASI DI DATI E SISTEMI INFORMATIVI” E ”SISTEMI INFORMATIVI” a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per la gestione delle ricette mediche rilasciate da differenti medici. Ogni medico è identificato dai propri dati anagrafici e dalla propria specializzazione, mentre per ogni paziente si conosce codice fiscale, nome, cognome, sesso (indicato come M o F), data di nascita e residenza. Un medico pu` o rilasciare delle ricette mediche identificate da un codice alfanumerico univoco composto da 17 caratteri, data di emissione, paziente di riferimento, denominazione dell’ente di competenza, numero progressivo regionale, sigla provincia (2 caratteri) e codice ASL (3 numeri). Le ricette mediche si dividono inoltre in due tipi: – rilasciate per prescrivere farmaci: in questo caso si conosce la lista dei farmaci prescritti (per un massimo di 5) con la relativa quantità. Ogni farmaco è definito attraverso un codice alfanumerico 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 si tratta di un farmaco generico o meno; – rilasciate per prescrivere degli esame: in questo caso si conosce una descrizione dell’esame prescritto. Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrità. c) Si vuole realizzare un database relativo alle pubblicazioni di articoli su riviste scientifiche. E’ stata a 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 pi` u volte in un anno e che ordine sia un valore intero usato per 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. d) Date le seguenti relazioni: a) ATTORE (matricola, nome, cognome, nazionalit` FILM (codice, titolo, anno produzione, genere, costo produzione, totale incassi) CAST (cod film, matricola, ore impegnate, compenso attore film) esprimere in SQL le seguenti interrogazioni: 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 nazionalità 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.

1.5. APPELLO 17 GIUGNO

1.5

21

Appello 17 Giugno

CORSO DI LAUREA IN ING. GESTIONALE, INFORMATICA, ELETTRONICA E TELECOMUNICAZIONI PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI” a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per gestire dei i dati relativi a differenti ospedali. Ogni ospedale è identificato da un codice univoco, nome e indirizzo. Per ciascun ospedale si conoscono inoltre i 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 cui si conosce codice fiscale, nome, cognome, data di assunzione e lista di reparti in cui lavorano. I dipendenti si dividono in: – infermieri, si conoscono gli anni di servizio; – medici, si conosce la data di abilitazione alla professione e l’elenco di specializzazioni ottenute con relativa data. Si conoscono inoltre le informazioni dei pazienti (cf, nome, cognome, data di nascita) e dei ricoveri effettuati identificati da data e ora del ricovero, motivazione, codice gravità (rosso, giallo, verde), paziente di riferimento, reparto interessato. Sarà presente infine anche un attributo booleano che indica se il paziente è stato dimesso o meno. Occorre verificare al momento del ricovero che ci siano dei posti a disposizione nel relativo reparto. Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrit` a. ` stata a tal fine costruita, c) Si vuole realizzare un database relativo alle gestione di gare podistiche. E 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 è univoco solo per il relativo evento Dipendenze funzionali: – 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

22

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI Gli attributi che formano la chiave sono: cod evento,num pettorale Classificazione delle dipendenze funzionali: – 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) d) Date le seguenti relazioni: RICETTA ( codice , nome , tempo_preparazione , difficolt ` a , regione_appartenenza ) INGREDIENTE ( codice , nome , calorie ) LIST A_INGREDIENTI ( codRicetta , codIngrediente , quantit ` a)

esprimere in SQL le seguenti interrogazioni: 1) Visualizzare, in ordine decrescente per tempo di preparazione, le ricette che prevedono l’ingrediente ”basilico” Soluzione 1.5.2. Query di selezione con condizione di confronto fra un valore e un elenco da subquery con condizione, raggruppamento e condizione sui gruppi SELECT FROM WHERE

* ricetta codice IN SELECT FROM WHERE

( codRicetta lista_ingredienti codIngrediente = ( SELECT codice FROM ingrediente WHERE nome = ’ basilico ’

) ) ORDER BY

tempo DESC ;

2) Selezionare per ogni ingrediente il numero di regioni ed il numero di ricette in cui è utilizzato e) SOLO N.O. – Illustrare il concetto di decomposizione senza perdite e le relative condizioni da verificare Soluzione 1.5.3. Data una relazione R(X) su un insieme di attributi X = X1 ∪ X2 , si ha una decomposizione senza perdite(loseless join) se il JOIN delle proiezioni di R su X1 e X2 è uguale a R (ovvero non contiene tuple spurie) πX1 (R) B CπX2 (R) = R R si decompone senza perdite su due relazioni se l’insieme degli attributi comuni è chiave per almeno 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:

1.5. APPELLO 17 GIUGNO

23

1) Perdita di aggiornamento(lost update) Una transazione scrive una risorsa X dopo che è stata letta da un’altra transazione che utilizzer` a un dato errato R1 (X) → R2 (X) → W2 (X) → W1 (X) la scrittura W2 (X) è persa perché sovrascritta da W1 (X). 2) Lettura sporca(dirty read ) Una transazione effettua una modifica su una risorsa e poi effettua il rollback. Una altra transazione che abbia letto il valore utilizza un dato che non viene salvato nella base di dati per effetto dell’abort: R1 (X) → W1 (X) → R2 (X) → rollback1 → W2 (X) 3) Lettura inconsistente(unrepeatable read ) Ogni transazione che legga la stessa risorsa pi` u volte deve ottenere lo stesso valore per tutta la durata della transazione: R1 (X) → R2 (X) → W2 (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) → W2 (X) → W2 (Z) → R1 (Z) 5) Inserimento fantasma(phantom insert) Una transazione effettua operazione con dati aggregati e durante la sua esecuzione un’altra transazione effettua una INSERT. – Descrivere le tecniche di frammentazione realizzabili su basi di dati distribuite

24


1.6

Appello 16 Luglio 2015

CORSO DI LAUREA IN ING. GESTIONALE, INFORMATICA, ELETTRONICA E TELECOMUNICAZIONI — PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI” a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per gestire dei i dati relativi ad un laboratorio di analisi cliniche. Ogni dipendente del laboratorio è identificato da codice fiscale, nome, cognome e ruolo. Si conoscono inoltre le informazioni dei pazienti (cf, nome, cognome, data di nascita). Per ogni prelievo realizzato si conosce data e ora dello stesso, numero di campioni prelevati, paziente di riferimento e dipendente che si è 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’esito dell’esame e la sua tipologia (urgente/non urgente). I prelievi si suddividono inoltre in: – interni, realizzati nel centro di analisi, a cui è associata una data di consegna; – esterni, realizzati in ospedale di cui si conosce ragione sociale, sede ed un attributo booleano che 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 campioni prelevati. Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrità. ` stata a tal fine costruita, c) Si vuole realizzare un database relativo alle ricariche di auto elettriche. E 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 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.6.1. Ipotesi: (IP1) l’attributo cod colonnina sia univoco solo rispetto all’ Gestore (IP2) aggiungo ipotesi: ogni Auto ha un Proprietario (IP3) aggiungo ipotesi: un Auto pu` o effettuare una Ricarica per volta Dipendenze funzionali: (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 Classificazione dipendenze funzionali – Dipendenza funzionale piena: Ricarica – Dipendenza funzionale parziale: Auto

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) d) Date le seguenti relazioni: FILM ( codFilm , titolo , anno , genere , durata , incasso ) ATTORE ( codAttore , nome , cognome , nazionalita ) CAST ( codFilm , codAttore , compenso )

esprimere in SQL le seguenti interrogazioni: 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 FROM WHERE GROUP BY HAVING

codFilm FilmCast compenso >10000 codFilm count ( codAttore ) >10

2) Selezionare per ogni attore il numero di film in cui ha partecipato, visualizzando i risultati in ordine decrescente sulla base di tale valore Soluzione 1.6.3. Query di selezione con raggruppamento e ordinamento SELECT FROM GROUP BY ORDER BY

codAttore , count ( codFilm ) FilmCast codAttore count ( codFilm ) DESC

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 granularità di un trigger

26


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” a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per gestire gli annunci pubblicati su un portale online. Ogni annuncio è identificato da un codice univoco di 10 caratteri, nome, descrizione, categoria (elettronica, immobili, veicoli, altro), data pubblicazione, data scadenza e utente che lo ha pubblicato. Ogni utente è caratterizzato da codice fiscale, nome, cognome e indirizzo e-mail. Per ogni annuncio è 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 si trova l’oggetto; acquisti, è presente un attributo booleano che indica se l’acquisto dovrà avvenire esclusivamente con consegna di persona o meno. Ad ogni annuncio sono associate delle offerte di cui si conosce data, ora, importo, note eventuali e utente che ha inviato l’offerta. Per ogni offerta occorre verificare che la data sia compresa tra la data di pubblicazione e di scadenza del relativo annuncio e che l’offerta non provenga dallo stesso utente che ha pubblicato l’annuncio. Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrità. ` stata a tal fine costruita, c) Si vuole realizzare un database relativo alle playlist di brani musicali. E 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. Soluzione 1.7.1. Ipotesi: – 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 pi` u Artisti – IP4 : ogni Playlist ha in una posizione un Brano Dipendenze funzionali: – 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 trasforma in una relazione BranoArtista N a N senza attributi. Gli attributi della chiave: sono idUtente,idPlaylist,posizione Classificazione delle dipendenze funzionali: – Dipendenza piena dalla chiave: BranoPlaylist

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) d) Date le seguenti relazioni: FILM ( codFilm , titolo , anno , genere , durata , incasso ) ATTORE ( codAttore , nome , cognome , nazionalit ` a) CAST ( codFilm , codAttore , compenso )

esprimere in SQL le seguenti interrogazioni: 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 da subquery ottenuto come operazione insiemistica SELECT FROM WHERE

* Film codFilm IN ( ( SELECT codFilm FROM FilmCast WHERE codAttore SELECT FROM WHERE AND ) INTERSECT ( SELECT codFilm FROM FilmCast WHERE codAttore SELECT FROM WHERE AND

= ( codAttore Attore nome = ’ Brad ’ cognome = ’ Pitt ’

= ( codAttore Attore nome = ’ ’ 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 raggruppamento SELECT FROM WHERE

codAttore , sum ( compenso ) FilmCast codFilm IN ( SELECT codFilm FROM Film WHERE anno =2015

) GROUP BY codAttore

e) SOLO N.O.

28

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI – Illustrare la sintassi SQL per l’eliminazione di una vista, descrivendo in dettaglio le opzioni utilizzabili – Illustrare le primitive di lock e il funzionamento della relativa tabella dei conflitti – Definire la struttura di una pagina gestita dal buffer manager

1.8. APPELLO 22 SETTEMBRE 2015

1.8

29

Appello 22 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” a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per gestire un software di instant messaging per smartphone. Ogni utente è identificato dal proprio numero di cellulare, nome, data di iscrizione e data di scadenza del servizio. Per ogni utente si conoscono le conversazioni a cui partecipa, caratterizzate da un codice univoco, data di inizio conversazione e tipologia (chat privata o gruppo). Se si tratta di chat privata occorre verificare che ci siano solo due utenti nella conversazione; per le chat di gruppo si conosce invece il numero di partecipanti, il titolo del gruppo e per ogni partecipante sarà presente un campo booleano per indicare se l’utente è amministratore del gruppo. Ogni conversazione è composta da una serie di messaggi descritti attraverso un identificativo numerico progressivo (univoco solo rispetto alla conversazione), utente che l’ha inviato, stato (inviato, ricevuto o letto), data e ora di invio. 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. Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrit` a. ` stata a tal fine costruita, c) Si vuole realizzare un database relativo al rilascio di certificati di nascita. E 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, le dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in 3a forma normale, preservando le dip. funzionali. Soluzione 1.8.1. Ipotesi: (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 Dipendenze funzionali: (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 Classificazione dipendenze funzionali

30

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI – Dipendenza funzionale piena: Atto – Dipendenza funzionale parziale: nessuna – Dipendenza funzionale transitiva: Impiegato, Ufficio, Cittadino d) Date le seguenti relazioni: FILM ( codFilm , titolo , anno , genere , durata , incasso ) ATTORE ( codAttore , nome , cognome , nazionalita ) CAST ( codFilm , codAttore , compenso ) o )

esprimere in SQL le seguenti interrogazioni: 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 da subquery SELECT FROM WHERE

* Attore codAttore IN ( SELECT FROM GROUP BY HAVING

codAttore FilmCast codAttore count ( codFilm ) >= 10

)

2) Selezionare i film in cui non è presente Johnny Depp Soluzione 1.8.3. Query di selezione con condizione di confronto fra un valore e un elenco da subquery SELECT FROM WHERE

* Film codFilm NOT IN ( SELECT FROM WHERE

DISTINCT codFilm FilmCast codAttore = ( SELECT codAttore 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 modalità di ricerca e le differenze con un albero B+ – Illustrare l’architettura di un Data Warehouse

1.9. APPELLO 12 NOVEMBRE 2015 - MODULO I

1.9

31

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” a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per gestire un sito di car sharing. Occorre memorizzare i dati riferiti a ciascun utente di cui si conosce codice fiscale, nome, cognome e e-mail. Si conoscono inoltre le informazioni delle auto utilizzate durante i viaggi, identificate da targa, modello, cilindrata, km percorsi 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 (per ciascun comune si conosce CAP e nome) e lista di utenti passeggeri. Ogni passeggero indicher` a anche un voto e una recensione del viaggio. Verificare inoltre che per ciascun viaggio il numero di passeggeri non sia superiore rispetto al numero massimo indicato. Un viaggio può 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à effettuata una sosta e la relativa durata. Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrit` a. ` stata a tal fine costruita, c) Si vuole realizzare un database relativo ad acquisti di prodotti online. E 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, quantit` a) Nell’ipotesi che codProdotto sia univoco solo rispetto ad una azienda e che l’attributo quantit` a indichi il numero di pezzi selezionati in fase di acquisto per ciascun prodotto, 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. d) Date le seguenti relazioni: FILM ( codFilm , titolo , anno , genere , durata , incasso ) ATTORE ( codAttore , nome , cognome , nazionalit ` a) CAST ( codFilm , codAttore , compenso )

esprimere in SQL le seguenti interrogazioni: 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 subquery SELECT FROM WHERE

C1 . codAttore , C1 . codFilm Film NATURAL JOIN FilmCast AS C1 incasso >= ALL ( SELECT MAX ( incasso ) FROM Film NATURAL JOIN FilmCast C2 WHERE C1 . codAttore = C2 . codAttore

)

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 operazione insiemistica tra subquery

32

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI SELECT FROM WHERE

* Film codFilm IN ( ( SELECT FROM WHERE INTERSECT ( SELECT FROM WHERE

DISTINCT codFilm FilmCast NATURAL JOIN Attore nazionalit ` a = ’ francese ’) DISTINCT codFilm FilmCast NATURAL JOIN Attore nazionalit ` a = ’ italiana ’)

)

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


1.10

33


PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI” a) Si progetti uno schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per gestire un sistema per l’accesso a contenuti video. Occorre memorizzare i dati riferiti a ciascun utente di cui si conosce indirizzo e-mail (univoco per ogni utente), password e data di iscrizione. Ogni utente può sottoscrivere degli abbonamenti identificati da data di inizio, data di fine, costo e tipologia (base e premium). Per ogni abbonamento si conosce anche la lista dei dispositivi associati di cui si conosce un nome e l’indirizzo MAC (codice alfanumerico univoco di 12 caratteri). Per gli abbonamenti “base” è possibile associare un solo dispositivo. 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 se il contenuto è 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 è identificata attraverso un id numerico progressivo (univoco all’interno della serie TV) ed il numero di puntate. Si conosce infine l’elenco dei contenuti, visualizzati utilizzando ciascun abbonamento, con la relativa data e ora di visione. Indicare le cardinalità delle relazioni e un identificatore per ciascuna entit` a. Soluzione 1.10.1. Progettazione concettuale dello schema Entità-Relazioni per lo scenario di gestione di un sistema di accesso a contenuti video (diagramma schema E-R in figura 1.4). 1) Entit` a Utente con chiave email attributi psw, dataIscr. 2) Entit` a debole Abbonamento con chiave parziale dataInizio e dipendenza da Utente, e attributi dataFine e costo. 3) Relazione tra Utente e Abbonamento, partecipazione totale, cardinalità 1 utente : N abbonamenti. 4) Entit` a debole Visione, chiave parziale data,ora, dipendenza da Abbonamento. 5) Relazione tra Abbonamento e Visione, partecipazione totale, cardinalità 1 abbonamento: N visioni. 6) Entit` a Contenuto, chiave cod, attributi titolo, data, giudizio (BR1: dominio valori interi compresi 1-5), bambini (BR2: dominio booleano) 7) Gerarchia Contenuto ISA, generalizzazione totale esclusiva, entità figlie: i. Entit` a Film, attributi durata, anno ii. Entit` a Serie TV, attributi genere, desc 8) Entit` a Stagione, chiave id, attributo num puntate 9) Relazione tra Serie TV e Stagione, partecipazione totale, cardinalità 1 serie TV : N stagioni. 10) Gerarchia Abbonamento ISA, generalizzazione totale esclusiva, entità figlie: i. Entit` a Base ii. Entit` a Premium 11) Entit` a Dispositivo, chiave MAC, attributo nome 12) Relazione tra Dispositivo e abbonamento Base, partecipazione parziale, cardinalità 1 dispositivo : N abb. Base 13) Relazione tra Dispositivo e abbonamento Premium, partecipazione parziale, cardinalit` aN dispositivi : N abb. Premium

34

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

psw dataIscr

email

Utente

1

N

dataFine 1

Abbonamento

data ora N

Visione N

costo ISA Base

titolo Premium

N

1

data cod

Contenuto

N bambini

giudizio ISA

1

Dispositivo

N Film

nome

MAC durata

Serie TV 1

anno genere

desc

N Stagione

id

num puntate

Figura 1.4: Diagramma E-R per lo scenario del sistema di accesso a contenuti video

Nella ristrutturazione dello schema E/R si procede con – analisi delle ridondanze – l’attributo num puntate dell’entità Stagione è derivabile come conteggio di occorrenze ma si preferisce mantenere l’attributo motivi di efficienza e scarso impatto di memoria – eliminazione delle generalizzazioni – si risolve la gerarchia Abbonamento con le figlie Base e Premium nel padre: è necessario introdurre l’attributo tipo in Abbonamento e una nuova business rule per il check sulla relazione Abbonamento e Dispositivo in base al tipo. – la gerarchia Contenuto con le figlie deboli Film e Serie TV: è necessario introdurre due relazioni 1:1 tra il padre e le figlie deboli – partizionamento/accorpamento di entità e associazioni – si accorpano le associazioni tra entità Base e Premium con Dispositivo in una relazione N:N e si introduce una nuova business rule per il check di cardinalità in base al tipo di abbonamento – scelta degli identificatori primari

1.10. APPELLO 5 FEBBRAIO 2016 dataInizio

35 cod

tipo

Abbonamento

1

1

Contenuto

N 1

1

N Film

Stagione

Serie TV

Dispositivo id Figura 1.5: Risoluzione gerarchie ISA Abbonamento e Contenuto

b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrit` a. CREATE TABLE Utente ( email pw data_iscr );

VARCHAR (64) PRIMARY KEY , VARCHAR (50) NOT NULL , DATE

CREATE TABLE Abbonamento ( email VARCHAR (64) REFERENCES Utente ( email ) , data_inizio DATE , data_fine DATE , costo NUMERIC (5 ,2) , tipo VARCHAR (7) CHECK ( VALUE IN ( ’ base ’ , ’ premium ’)) , PRIMARY KEY ( email , data_inizio ) ); CREATE TABLE Dispositivo ( mac CHAR (12) PRIMARY KEY , nome VARCHAR (64) ); CREATE TABLE D is po sit iv oA bbo na me nto ( email VARCHAR (64) , data_inizio DATE , FOREIGN KEY ( email , data_inizio ) REFERENCES Abbonamento ( email , data_inizio ) , mac CHAR (12) REFERENCES Dispositivo ( mac ) , PRIMARY KEY ( email , data_inizio , mac ) ); CREATE ASSERTION C h e c k D i s p o s i t i v o A b b o n a m e n t o CHECK ( Abbonamento . tipo = ’ base ’ AND 1 >=( SELECT count (*) FROM D is po sit iv oA bbo na me nto WHERE Abbonamento . email = Di sp osi ti vo Abb on a m e n t o . email AND Abbonamento . data_inizio = Di sp os iti vo Ab bon am en to . data_inizio ) );

36


CREATE TABLE Contenuto ( idContenuto titolo data bambini giudizio );

INTEGER PRIMARY KEY , VARCHAR (64) NOT NULL , DATE , BIT , SMALLINT CHECK ( VALUE BETWEEN 1 AND 5)

CREATE TABLE Visione ( dataora TIMESTAMP , email VARCHAR (64) , data_inizio DATE , idVisione INTEGER REFERENCES Contenuto ( idContenuto ) , FOREIGN KEY ( email , data_inizio ) REFERENCES Abbonamento ( email , data_inizio ) , PRIMARY KEY ( dataora , email , data_inizio , idVisione ) ); CREATE TABLE Film ( idFilm durata_min anno );

INTEGER PRIMARY KEY REFERENCES Contenuto ( idContenuto ) , SMALLINT , NUMERIC (4)

CREATE TABLE SerieTV ( idSerieTV genere descr );

INTEGER PRIMARY KEY REFERENCES Contenuto ( idContenuto ) , SMALLINT , VARCHAR (256)

CREATE TABLE Stagione ( idSerieTV INTEGER REFERENCES SerieTV ( idSerieTV ) , idStagione INTEGER , PRIMARY KEY ( idSerieTV , idStagione ) , num_puntate SMALLINT );

` stata a tal fine c) Si vuole realizzare un database relativo ad attivit` a e task di differenti progetti. E costruita, da un inesperto progettista, un’unica tabella descritta dai seguenti attributi: (codPartner, nomePartner, sedePartner, codProgetto, nomeProgetto, durata, idAttivit` a, nomeAttivit` a, descrizione, idTask, nomeTask, dataInizio, dataFine ) Nell’ipotesi che idAttivit` a sia univoco solo all’interno del relativo progetto e che per ogni task si conosca l’attivit` a (rispetto a cui è univoco) ed il partner responsabile, se ne determini la chiave e si individuino, esplicitandole, le dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in terza forma normale, preservando le dip. funzionali. d) Date le seguenti relazioni: FILM ( codFilm , titolo , anno , genere , durata , incasso ) ATTORE ( codAttore , nome , cognome , nazionalit ` a) CAST ( codFilm , codAttore , compenso )

esprimere in SQL le seguenti interrogazioni: 1) Selezionare, per ogni anno, l’incasso medio ed il numero di film realizzati visualizzandoli in ordine decrescente rispetto a tale valore.


37

Soluzione 1.10.2. Query di selezione con funzioni di aggregazione e gruppi ordinati SELECT FROM GROUP BY ORDER BY

anno , AVG ( incasso ) , count (*) Film anno anno DESC

2) Visualizzare gli attori che nel 2015 hanno ottenuto il compenso maggiore e minore Soluzione 1.10.3. Query nidificata con condizione confronto valore con lista di valori da subquery SELECT FROM WHERE

* Attore codAttore SELECT FROM WHERE

AND

IN ( DISTINCT codAttore FilmCast NATURAL JOIN Film ( ( compenso >= ALL ( SELECT MAX ( compenso ) FROM FilmCast ) ) OR ( compenso <= ALL ( SELECT MIN ( compenso ) FROM FilmCast ) ) ) anno =2015

)

e) SOLO N.O. – Illustrare le prime tre forme normali Soluzione 1.10.4. Prima Forma Normale La prima Forma Normale (1NF) si ha a condizione, fondamentale nei database relazionali, per cui ogni attributo ha un dominio definito e i valori atomici assunti appartengono al dominio. Inoltre ogni relazione della base di dati deve avere definita una chiave primaria. Seconda Forma Normale La seconda Forma Normale (2NF ) si ha per una relazione in 1NF in cui ogni attributo che non fa parte della chiave primaria dipende interamente dalla chiave stessa e non solo da parte di essa (non sono definite dipendenze parziali). [Le relazioni con chiave composta da un solo attributo atomico sono in 2NF] Terza Forma Normale La terza Forma Normale si ha per una relazione in 2NF in cui ogni dipendenza X → Y deve verificare una delle seguenti: 1) X è superchiave 2) Y è attributo primo (cioè fa parte di una chiave di R) Inoltre non esistono attributi che dipendono da altri attributi non chiave (non sono definite dipendenze transitive). Si dimostra che esiste un algoritmo di sintesi di schemi in terza forma normale a partire dalla decomposizione delle dipendenze funzionali che garantisca la conservazione delle dipendenze e la decomposizione senza perdite. – Descrivere il locking a 2 fasi e illustrare le differenze tra questo ed il locking a due fasi stretto Soluzione 1.10.5. Nel protocollo di locking a 2 fasi (2PL) una transazione ha una fase iniziale dopo la dichiarazione di Begin of Transaction in cui si acquisiscono i lock per le risorse. Dopo l’utilizzo delle risorse, al primo rilascio di un lock comincia la fase di rilascio finale dei lock precedentemente acquisiti: non è pi` u possibile acquisire lock fino all’operazione di chiusura End of Transaction.

38

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

BoT

EoT

t

Transazioni ben formate, composte da 1 operazione iniziale di BoT, operazioni DDL/DML, 1 operazione di commit o 1 operazione di abort senza altre operazioni, 1 operazione finale di ` rispetto alla conflictEoT, eseguite sotto il protocollo 2PL garantiscono la serializzabilita equivalenza. Per tale propriet` a la classe di transazioni 2PL è contenuta nella classe CSR dell’insieme delle schedulazioni conflict-serializzabili. Tale protocollo non risolve l’anomalia di lettura sporca (dirty read anomaly) nell’esecuzione concorrente di transazioni in cui una transazione effettua una modifica su una risorsa a cui accede successivamente un’altra transazione in lettura, ove la prima transazione esegua un abort con il rollback della modifica. Il protocollo locking a 2 fasi stretto è la variante in cui dopo il commit o l’abort tutte le risorse acquisite ventono rilasciate contemporanetmente. Tale soluzione consente di evitare la lettura sporca ma non risolve ancora l’anomalia da inserimento fantasma. Questa richiede i lock di predicato che consentono di inibire le operazioni di inserimento. – Descrivere brevemente la struttura di un albero B, la modalità di ricerca e le differenze con un albero B+


1.11

39


CORSO DI LAUREA IN ING. GESTIONALE E ING. INFORMATICA E DELL’AUTOMAZIONE PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI” a) Si progetta lo schema concettuale Entità-Relazioni per gestire le visite di orientamento di studenti delle scuole superiori presso l’Ateneo. Per ciascuno studente di scuola superiore in visita occorre memorizzare i dati anagrafici e la classe scolastica di appartenenza. Ogni classe è caratterizzata da un numero, una lettera, l’istituto scolastico a cui la classe appartiene, un numero di studenti, un attributo booleano che indichi se la classe è sperimentale e un attributo descrittivo del tipo di sperimentazione. Occorre verificare che tale attributo descrittivo non abbia valore in caso di classi non sperimentali e che le classi siano esclusivamente di numero 4 o 5. Gli istituti scolastici sono caratterizzati da un nome e una sede e si dividono in licei e istituti tecnici. Per i licei occorre conoscere il tipo (classico o scientifico) e il numero totale di iscritti. Per gli istituti tecnici occorre conoscere il tipo (commerciale, o industriale o per geometri) e la lista degli indirizzi di studio disponibili, con relativo numero di classi formate. Per ogni classe, è necessario tenere traccia dei docenti accompagnatori, identificati dai dati anagra` necessario verificare che per ogni classe ci sia almeno un docente ogni 10 studenti in visita fici. E (rapporto 1 a 10 tra docente e studenti). Indicare le cardinalit` a delle relazioni e un identificatore per ciascuna entità. lettera num

nomecognome cf

Studente

N

freq

1

Classe

nome N

1

app

cap

Istituto

ISA

accomp

tipo tecnic Liceo

tipo liceo cf

Tecnico iscritti

Docente

num classi ha

nome

cognome cod desc Indirizzo

Figura 1.6: Diagramma E/R gestione visite orientamento studenti

b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrit` a. Soluzione 1.11.1. Le relazioni ottenute dal mapping relazionale: 1) Docente(CF,nome,cognome) si è risolta la gerarchia Istituto con le entità figlie deboli Liceo e Tecnico

40

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI 2) Istituto(nome,cap,via,civico) 3) Liceo(nome,cap,tipo liceo,iscritti) 4) Tecnico(nome,cap,tipo istTecnico) 5) Indirizzo(codice,desc) 6) TecnicoIndirizzo(nome,cap,codIndirizzo,num classi) 7) Classe(nome,cap,numero,lettera,num studenti,sperimentale,descrizione) 8) DocenteClasse(cfDocente,nome,cap,numero,lettera) 9) Studente(cf,nome,cognome,nome istituto,cap,numero,lettera) Le tabelle in linguaggio SQL: CREATE TABLE Docente ( cf CHAR (16) PRIMARY KEY , nome VARCHAR (50) , cognome VARCHAR (100) ); CREATE TABLE Istituto ( nome VARCHAR (100) , cap NUMERIC (5 ,0) , via VARCHAR (100) , civico VARCHAR (10) , PRIMARY KEY ( nome , cap ) ); CREATE TABLE Liceo ( nome VARCHAR (100) , cap NUMERIC (5 ,0) , tipoLiceo VARCHAR (11) CHECK ( tipoLiceo IN ( ’ classico ’ , ’ scientifico ’)) , iscritti INTEGER , PRIMARY KEY ( nome , cap ) , FOREIGN KEY ( nome , cap ) REFERENCES Istituto ( nome , cap ) ); CREATE TABLE Tecnico ( nome VARCHAR (100) , cap NUMERIC (5 ,0) , tipoIstTecnico VARCHAR (11) CHECK ( tipoIstTecnico IN ( ’ industriale ’ , ’ commerciale ’ , ’ geometri ’)) , PRIMARY KEY ( nome , cap ) , FOREIGN KEY ( nome , cap ) REFERENCES Istituto ( nome , cap ) ); CREATE TABLE Indirizzo ( codice CHAR (6) PRIMARY KEY , descr VARCHAR (100) ); CREATE TABLE TecnicoIndirizzo ( nome VARCHAR (100) , cap NUMERIC (5 ,0) , num_classi SMALLINT , codInd CHAR (6) REFERENCES Indirizzo ( codice ) , PRIMARY KEY ( nome , cap ) , FOREIGN KEY ( nome , cap ) REFERENCES Tecnico ( nome , cap ) );


41

CREATE TABLE Classe ( nome VARCHAR (100) , cap NUMERIC (5 ,0) , numero CHAR (1) CHECK ( numero IN ( ’4 ’ , ’5 ’)) , lettera VARCHAR (2) , num_studenti SMALLINT , sperimentale BIT , descrizione VARCHAR (100) , CHECK ( ( sperimentale =1 AND descrizione IS NOT NULL ) OR ( sperimentale =0 AND descrizione IS NULL )) , PRIMARY KEY ( nome , cap , numero , lettera ) , FOREIGN KEY ( nome , cap ) REFERENCES Istituto ( nome , cap ) ); CREATE TABLE Docente_Classe ( cfDocente CHAR (16) REFERENCES Docente ( cf ) , nome VARCHAR (100) , cap NUMERIC (5 ,0) , numero CHAR (1) , lettera VARCHAR (2) , PRIMARY KEY ( cfDocente , nome , cap , numero , lettera ) , FOREIGN KEY ( nome , cap , numero , lettera ) REFERENCES Classe ( nome , cap , numero , lette ); CREATE TABLE Studente ( cf CHAR (16) PRIMARY KEY , nome VARCHAR (50) , cognome VARCHAR (100) , nomeIstituto VARCHAR (100) , cap NUMERIC (5 ,0) , numero CHAR (1) , lettera VARCHAR (2) , FOREIGN KEY ( nome , cap , numero , lettera ) REFERENCES Classe ( nome , cap , numero , lette );

c) Si vuole realizzare un database relativo alla gestione degli appuntamenti presso un poliambulatorio ` stata a tal fine costruita, da un inesperto progettista, un’unica tabella medico convenzionato ASL. E descritta dai seguenti attributi: (codMedico, nomeMedico, cognomeMedico, specializzazioneMedico, codPaziente, nomePaziente, cognomePaziente, dataAppuntamento, oraAppuntamento, diagnosi, codRicetta, descrizioneRicetta) Nell’ipotesi che codRicetta sia univoco e che per ogni appuntamento possa essere prodotta una sola diagnosi, ma anche pi` u di una ricetta, se ne determini la chiave e si individuino, esplicitandole, le dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in terza forma normale, preservando le dip. funzionali. d) Date le seguenti relazioni: FILM ( codFilm , titolo , anno , genere , durata , incasso ) ATTORE ( codAttore , nome , cognome , nazionalit ` a) CAST ( codFilm , codAttore , compenso )

esprimere in SQL le seguenti interrogazioni: 1) Selezionare, per ogni film con incasso maggiore di un milione, il compenso medio degli attori che vi hanno recitato

42

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI Soluzione 1.11.2. Query di selezione con raggruppamento e condizione having su lista di valori ottenuti da subquery di selezione con condizione SELECT codFilm , AVG ( compenso ) FROM FilmCast GROUP BY codFilm HAVING codFilm IN ( SELECT codFilm FROM Film WHERE incasso >1000000)

2) Visualizzare i film di genere drammatico in cui non hanno recitato attori italiani Soluzione 1.11.3. Tre query di selezione nidificate con condizione su lista di valori SELECT FROM WHERE AND

* Film AS F genere = ’ drammatico ’ codFilm NOT IN ( SELECT DISTINCT codFilm FROM FilmCast AS C WHERE ( F . codFilm = C . codFilm ) AND ( codAttore IN ( SELECT codAttore FROM Attore WHERE nazionalit ` a = ’ italiano ’) )

);

Soluzione con JOIN SELECT FROM WHERE AND

F .* Film AS F NATURAL JOIN FilmCast C genere = ’ drammatico ’ codAttore NOT IN ( SELECT codAttore FROM Attore WHERE nazionalit ` a = ’ italiano ’ );

e) SOLO N.O. – Illustrare gli operatori unari dell’algebra relazionale Soluzione 1.11.4. Gli operatori unari dell’algebra relazionale definiti su relazioni e che producono relazioni (algebra chiusa) sono – operatore di selezione σcondizione (relazione) – operatore di proiezione πattributi (relazione) – operatore di ridenominazione %lista attributi rinominati←lista attributi (relazione) Selezione L’operatore di selezione σcond (R) produce una nuova relazione sugli stessi attributi di R che contiene un sottoinsieme delle tuple di R che soddisfano la condizione di selezione cond. La condizione di selezione è un predicato sugli attributi di R composto da connettivi logici e operatori di confronto applicati ad attributi (di domini compatibili) e valori costanti. La relazione risultante mantiene il grado (numero di attributi) della relazione R di ` pu` partenza. La cardinalita o essere minore o uguale rispetto alla relazione R. L’operatore di selezione è commutativo: σcond1 (σcond2 (R)) = σcond2 (σcond1 (R))


43

Proiezione L’operatore di proiezione πattributi (R) produce una nuova relazione su un sottoinsieme di attributi della relazione R, quindi un grado minore o uguale a quello della ` risultante può essere diversa. relazione R. La cardinalita Il risultato della proiezione produce lo stesso numero di tuple di R se e solo se la lista degli attributi su cui si effettua la proiezione è superchiave per R. L’operatore proiezione non è commutativo. Ridenominazione L’operatore ridenominazione consente di ottenere una nuova relazione con uno o pi` u attributi rinominati %lista attributi rinominati←lista attributi (R) ` della relazione. L’operatore non modifica il grado e cardinalita – Illustrare la tecnica di ripresa a caldo Soluzione 1.11.5. In un DBMS transazionale al fine di garantire la durabilit` a /persistenza dei dati si ha un sottosistema controllore di affidabilit` a che implementa le primitive di ripristino del sistema in caso di guasti hardware/software. La ripresa a caldo avviene a seguito di un guasto di sistema (software failure) che ha causato la perdita di dati in memoria centrale (pagine del buffer) contenenti le operazioni di transazioni che pur essendo giunte in commit non siano state rese persistenti nel database su memoria di massa. La ripresa a caldo è articolata in 4 fasi eseguite per ripristinare la base di dati utilizzando le informazioni presenti nei file di log di sistema e delle transazioni: 1) ricerca dell’ultimo checkpoint inserito nel log di sistema partendo a ritroso dal riferimento all’ultima operazione prima del guasto (log top); 2) si costruiscono due insiemi REDO-set e UNDO-set che contengono rispettivamente le transazioni che sono giunte a commit, le cui operazioni saranno rieseguite nell’ultima fase, e le transazioni che non sono pervenute a compimento con un commit o che sono state esplicitamente annullate con un abort, le cui operazioni verranno annullate nella terza fase; 3) si annullano le operazioni delle transazioni presenti nell’UNDO-set, scorrendo il log all’indietro (eventualmente anche oltre il checkpoint) fino alle rispettive operazioni di BOT (Begin of Transaction); 4) si eseguono le operazioni delle transazioni dell’insieme REDO-set procedendo in avanti fino alla fine del log. – Descrivere le differenze tra uno schema a stella ed uno schema a fiocco di neve

44


1.12

Esonero 20 Aprile 2016

CORSO DI LAUREA IN ING. INFORMATICA E DELL’AUTOMAZIONE PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” a) (12pt) Si progetti lo schema concettuale Entità-Relazioni per lo scenario pi` u sotto descritto. Una base di dati deve essere utilizzata per gestire le auto presenti in differenti auto concessionarie. Per ognuna di queste occorre memorizzare partita iva, nome e sede. Ogni concessionaria possiede delle auto caratterizzate da targa, marca, modello, cilindrata, costo e alimentazione (benzina, diesel, gpl o metano). Occorre inoltre memorizzare gli optional presenti in ogni auto di cui si conosce il costo aggiuntivo. Le auto si dividono inoltre in: auto a km zero, per cui la concessionaria propone il numero di rate e l’importo fisso associato a ciascuna di esse (verificare che l’importo derivante dalle rate non superi del 5% il costo iniziale dell’auto); auto usate, di cui si conoscono i km percorsi e l’anno di immatricolazione. Per le sole auto usate sarà memorizzata la lista di tutti i proprietari precedenti, caratterizzati dai relativi dati anagrafici, oltre che il periodo per cui l’auto è stata posseduta (data di acquisto e vendita dell’auto). Indicare le cardinalità delle relazioni e un identificatore per ciascuna entit` a. Soluzione 1.12.1. Costrutti modello schema concettuale entit` a Auto concessionaria chiave partita iva attributi atomici nome attributi composti sede entit` a Auto chiave targa attributi atomici marca,modello,cilindrata,costo,alimentazione business rule alimentazione in (’benzina’, ’diesel’, ’gpl’, ’metano’) relazione Auto-Autoconcessionaria partecipazione totale cardinalit` a 1 autoconcessionaria : N auto entit` a Optional chiave id attributi atomici desc,costo aggiuntivo relazione Auto-Optional partecipazione parziale cardinalit` a N auto : N optional generalizzazione auto entit` a Auto km zero attributi atomici numero rate, importo rata fissa business rule importorataf issa ∗ numerorate ≤ 1.05 ∗ costoauto entit` a Auto usata attributi atomici km percorsi, anno immatricolazione entit` a Proprietario chiave codice fiscale attributi atomici nome, cognome attributi composti indirizzo relazione Auto usata-Proprietario partecipazione totale cardinalit` a N proprietario : N auto usate

1.12. ESONERO 20 APRILE 2016

45

nome piva

marca

Auto concessionaria

1

N

modello

Auto KM 0

importo fisso rata

N

desc costo agg

Optional

costo alimentazione ISA

num rate

id

N

Auto

targa

via civico cap

cilindrata

data acquisto Auto usata

N

N

data vendita km percorsi anno immatricolazione

cf

nomecognome

Proprietario

cap civico

Figura 1.7: Diagramma E/R gestione autoconcessionarie

Normalizzazione schema concettuale: Analisi ridondanze Non sono presenti attributi derivabili o conteggio occorrenze Eliminazione generalizzazioni Si trasforma la generalizzazione dell’entità padre Auto con le entit` a figlie deboli Auto km 0 e Auto usata con due associazioni uno ad uno tra entit` a padre e figlie deboli. Non vi è trasferimento di attributi e le entità figlie sono identificate esternamente dall’entit` a padre. Le relazioni sono a partecipazione totale esclusiva: si hanno vincoli aggiuntivi per cui l’entità padre non può partecipare ad entrambe le associazioni, deve partecipare ad almeno una associazione. Accorpamento/partizionamento di entit` a Non è necessario partizionare o accorpare entit` ae associazioni. Eliminazione attributi composti L’attributo sede di Autoconcessionaria e indirizzo di Proprietario vengono sostituiti da attributi atomici via,civico,cap. Scelta chiavi primarie Tutte le chiavi candidate sono scelte come chiavi primarie. b) (5pt) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrit` a. Soluzione 1.12.2. Traduzione delle relazioni ottenute dal mapping relazionale: 1) Autoconcessionaria(piva,nome,via,civico,cap) 2) Auto(targa,piva,marca,modello,cilindrata,costo totale) 3) Auto km 0(targa,num rate,importo fisso rata) 4) Auto usata(targa,km percorsi,anno immatricolazione) 5) Optional(id,desc,costo agg) 6) Optional Auto(targa,id) 7) Proprietario(cf,nome,cognome,via,civico,cap) 8) ProprietarioAutoUsata(targa,cf) Le tabelle in linguaggio SQL: CREATE TABLE Autoconcessionaria ( piva CHAR (11) PRIMARY KEY , nome VARCHAR (64) ,

via

46

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI via civico cap

VARCHAR (128) , VARCHAR (16) , NUMERIC (5 ,0)

); CREATE TABLE Auto ( targa CHAR (7) PRIMARY KEY , marca VARCHAR (32) , modello VARCHAR (64) , cilindrata INTEGER , alimentazione VARCHAR (7) CHECK ( VALUE IN ( ’ diesel ’ , ’ benzina ’ , ’ gpl ’ , ’ metano ’) , costo NUMERIC (8 ,2) ); CREATE TABLE AutoKMZero ( targa CHAR (7) PRIMARY KEY REFERENCES Auto ( targa ) , num_rate SMALLINT , importo_fisso_rata NUMERIC (8 ,2) ); CREATE TABLE Autousata ( targa CHAR (7) PRIMARY KEY REFERENCES Auto ( targa ) , num_rate SMALLINT , importo_fisso_rata NUMERIC (8 ,2) ); CREATE TABLE Optional ( id desc costo_agg );

INTEGER PRIMARY KEY , VARCHAR (128) , NUMERIC (8 ,2)

CREATE TABLE Optional_Auto ( targa CHAR (7) REFERENCES Auto ( targa ) , id INTEGER REFERENCES Optional ( id ) , PRIMARY KEY ( targa , id ) ); CREATE TABLE Proprietario ( cf CHAR (16) PRIMARY KEY , nome VARCHAR (128) , cognome VARCHAR (128) , via VARCHAR (128) , civico VARCHAR (16) , cap NUMERIC (5 ,0) ); CREATE TABLE P ro pr iet ar io _Au to us ata ( cf CHAR (16) REFERENCES Proprietario ( cf ) , targa CHAR (7) REFERENCES Autousata ( targa ) , PRIMARY KEY ( cf , targa ) );

La business rule di controllo di integrit` a sui valori degli attributi importo rata fissa e numero rate di Auto km zero e dell’attributo costo di Auto è realizzata con una asserzione: CREATE ASSERTION Co nt r ol l oR at a Au to K MZ e ro CHECK ( Auto . targa = AutoKmZero . targa AND AutokmZero . num_rate * AutoKMZero . importo_fisso_rata <= 1.05* Auto . costo );

1.12. ESONERO 20 APRILE 2016

47

` stata a tal c) (5pt) Si vuole realizzare un database relativo agli orari delle lezioni del Politecnico. E fine costruita, da un inesperto progettista, un’unica tabella descritta dai seguenti attributi: (codDocente, nome, cognome, codCorso, nomeCorso, cfu, codCdL, nomeCdL, num iscritti CdL, annoCdL, semestre, cfu tot semestre, giorno settimana, ora) Nell’ipotesi che per ogni corso si conosca il docente e il CdL di riferimento e che annoCdL indichi il particolare anno di ciascun CdL (1,2,3,4 o 5), se ne determini la chiave e si individuino, esplicitandole, le dipendenze funzionali. Sulla base di queste si proceda alla normalizzazione in terza forma normale, preservando le dip. funzionali. Soluzione 1.12.3. Ipotesi: – IP1 : per ogni corso si conosca il docente e il CdL di riferimento – IP2 : annoCdL indichi il particolare anno di ciascun CdL (1,2,3,4 o 5) Ipotesi aggiuntive: – IP3 : ogni Corso si conosce l’annoCdL e il semestre in cui è erogato – IP4 : di ogni CdL si tiene conto degli num iscritti – IP5 : per ogni CdL in un dato annoCdL e semestre si conosce il numero di cfu tot semestre – IP6 : di ogni Corso si conosce l’orario delle lezioni con giorno settimana e ora Dipendenze funzionali: – DF1 : Docente codDocente -> nome, cognome – DF2 : Corso: codCorso -> nomeCorso, cfu, codDocente, codCdL, annoCdL, semestre (IP1)(IP3) – DF3 : Corso di Laurea: codCdL -> nomeCdL, num iscritti CdL (IP4) – DF4 : CFU semestre CdL: codCdL, annoCdL, semestre -> cfu tot semestre (IP5) – DF5 : Orario: codCorso, giorno settimana -> ora (IP6) La chiave è costituita dagli attributi: (codCorso, giorno settimana) Classificazione delle dipendenze funzionali: – Dipendenza funzionale piena: Orario – Dipendenza funzionale parziale: Corso – Dipendenza funzionale transitiva: Docente, Corso Di Laurea, CFU semestre CdL d) (8pt) Quesiti teorici: – Illustrare le differenze presenti tra chiave/superchiave e chiave candidata/chiave primaria Soluzione 1.12.4. Intuitivamente ogni entità di una base di dati deve essere identificata univocamente tramite una chiave, un insieme di uno o pi` u attributi che non possono avere valori duplicati in pi` u istanze dell’entità/ tuple di una relazione. Formalmente la definizione: – un insieme K di attributi è superchiave di una relazione r se r non contiene due tuple distinte t1 e t2 con t1 [K] = t2 [K] – K è chiave di r se è una superchiave minimale di r (non esiste un’altra superchiave K 0 di r che sia K 0 ⊂ K, contenuta in K come sottoinsieme proprio) – Descrivere, aiutandosi con un esempio, le anomalie da perdita di aggiornamento e lettura sporca

48

CAPITOLO 1. SOLUZIONI APPELLI DI BASE DI DATI E SISTEMI INFORMATIVI Soluzione 1.12.5. – Lettura sporca(dirty read ) Una transazione effettua una modifica su una risorsa e poi effettua il rollback. Una altra transazione che abbia letto il valore utilizza un dato che non viene salvato nella base di dati per effetto dell’abort: R1 (X) → W1 (X) → R2 (X) → rollback1 → W2 (X) – Aggiornamento fantasma(phantom update) L’ordine delle transazioni porta ad uno stato inconsistente R1 (X) → R1 (Y ) → R2 (X) → R2 (Z) → W2 (X) → W2 (Z) → R1 (Z)

1.13. TRACCIA PARZIALE APPELLO 16 FEBBRAIO 2012

1.13

49

Traccia parziale appello 16 Febbraio 2012

CORSO DI LAUREA IN ING. INFORMATICA, ELETTRONICA E TELECOMUNICAZIONI — PROVA SCRITTA DI “BASI DI DATI E SISTEMI INFORMATIVI” E “SISTEMI INFORMATIVI” a) b) Si definiscano le relazioni (tabelle) risultanti in SQL, avendo cura di esplicitare i vincoli di integrit` a. ` stata a c) Si vuole realizzare un database relativo alle gestione di denunce effettuate da cittadini. E tal fine costruita, da un inesperto progettista, un’unica tabella descritta dai seguenti attributi: (cfAttore, nomeAttore, cognomeAttore, cfRegista, nomeRegista, cognomeRegista, nomeSerie, genereSerie, numeroStagione, annoProd, numTotEpisidi, numEpisodio, titoloEpisodio, descEpisodio) Nell’ipotesi che il ogni stagione sia diretta da un solo regista, e che in ogni episodio possano partecipare pi` u attori, 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.13.1. Ipotesi: – IP1 : ogni Stagione è diretta da un solo Regista – IP2 : in un Episodio possono partecipare pi` u Attori Dipendenze funzionali: – – – – –

DF1 : DF2 : DF3 : DF4 : DF5 :

Attore cfAttore → nome, cognome Regista cfRegista → nome, cognome Serie nomeSerie → genere (IP3) Stagione nomeSerie,numStagione → annoProd,numTotEpisodi,cfRegista (IP1) Episodio nomeSerie,numStagione,numEpisodio → titolo,descrizione

L’ipotesi IP2 non da una dipendenza funzionale, in quanto nomeSerie,numStagione,numEpisodio,cfAttore → ∅, si trasforma in una relazione EpisodioAttore N a N senza attributi. Gli attributi della chiave: sono nomeSerie,numStagione,numEpisodio,cfAttore Classificazione delle dipendenze funzionali: – Dipendenza piena dalla chiave: nessuna – Dipendenza parziale dalla chiave: Attore, Serie, Stagione, Episodio – Dipendenza transitiva: Regista Non avendo dipendenze piene dalla chiave si aggiungerà una tabella Episodio Attore. Tabelle: ATTORE(cfAttore,nome,cognome) REGISTA(cfRegista,nome,cognome) SERIE(nomeSerie,genere) STAGIONE(nomeSerie,numStagione,annoProd,numTotEpisodi,cfRegista) EPISODIO(nomeSerie,numStagione,numEpisodio,titolo,descrizione) EPISODIO ATTORE(nomeSerie,numStagione,numEpisodio,cfAttore) d) e) SOLO N.O. – –

Elenco delle figure 1.1 1.2 1.3 1.4 1.5 1.6 1.7

Diagramma E/R gestione opere d’arte nei musei . . . . . . . . . . . . . Diagramma E/R gestione servizi telefonia . . . . . . . . . . . . . . . . . Diagramma E/R scenario programmazione film nei cinema . . . . . . . . Diagramma E-R per lo scenario del sistema di accesso a contenuti video Risoluzione gerarchie ISA Abbonamento e Contenuto . . . . . . . . Diagramma E/R gestione visite orientamento studenti . . . . . . . . . . Diagramma E/R gestione autoconcessionarie . . . . . . . . . . . . . . .

51

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

. . . . . . .

3 11 15 34 35 39 45

Indice analitico normalizzazione decomposizione senza perdite, 21 prima forma normale, 36 seconda forma normale, 36 sistema transazionale ripresa a caldo, 42 transazioni anomalie aggiornamento fantasma, 22, 47 inserimento fantasma, 22 lettura inconsistente, 22 lettura sporca, 22, 37, 47 perdita di aggiornamento, 22 atomicit` a, 7 ben formate, 37 consistenza, 7 isolamento, 8 locking a 2 fasi, 36 serializzabilit` a, 37 trigger, 7

52

Soluzioni prove scritte del corso di Base di dati e Sistemi Informativi - Corso di Laurea in Ingegneria Informatica e dell'Automazione - Politecnico di Bari

Recommend Documents