Computer Engineering - Basi di Dati

Full exam

BASI DI D ATI 1 – PROFF . S. C ERI , E. Q UINTARELLI , L. T ANCA – A.A. 16/17 TERZO APPELLO – 3 LUGLIO 2017 TEMPO A DISPOSIZIONE : 2 h E 30 m Matricola o Codice Persona: Cognome e Nome: (IN STAMPATELLO MAIUSCOLO ) Firma: Si consideri la base di dati dei passaggi di veicoli sulle autostrade d’Italia registrati dal sistema dei tutor. Ogni tutor appartiene a una sola autostrada; un ’autostrada invece può avere tanti tutor . AUTOSTRADA ( IdAutostrada , Città Inizio, Città Fine, KmTotali ) TUTOR ( IdTutor , Autostrada, AlKm, NumProgressivo , TempoMinDalTutorPrecedente ) PASSAGGI O ( Targa , Timestamp , Tutor ) I tutor di una data autostrada sono posizionati al chilometro specificato dall’attributo AlKm e secondo l’ordine espresso da NumProgressivo . P er ciascuno di loro, l’attributo TempoMinDalTutorPrecedente (di tipo INTERVAL) specifica il minimo intervallo di tempo richiesto per percorrere la tratta dal tutor precedente al tutor corrente senza superare il limite di velocità. Un veicolo che impiega meno tempo ha sicuramente superato il limite di velocità e può essere multato. Il tutor registra ogni passaggio segnalando la Targa del veicolo e il Timestamp di attra versamento . A. Linguaggi formali (6 punti) Esprimere in algebra relazion ale , calcolo relazionale e Datalog la seguente interrogazione: Le t arghe dei veicoli che hanno percorso sempre e solo la stessa autostrada. Algebra relazionale Calcolo relazionale {t | t1 Passaggio (t[Targa]=t1[Targa] ( t2 Passaggio, t3 Tutor, t4 Passaggio, t5 Tutor (t1[Targa]=t2[Targa] t2[Tutor]=t3[IdTutor] t4[Tutor]=t5[IdTutor] t2[Targa]=t4[Targa] t3[Autostrada] ≠t5[Autostrada])))} Oppure: {t | t1 Passaggio , t2 Tutor (t[Targa]=t1[Targa] t1[Tutor]=t2[IdTutor] ( t3 Passaggio, t4 Tutor (t1[Targa]=t3[Targa] t3[Tutor]=t4[IdTutor] t3[Autostrada] ≠t5[Autostrada])))} Datalog DUE_AUTOSTRADE(T) : - PASSAGGIO(T, TUT1, _), TUTOR(TUT1, A1, _, _, _), PASSAGGIO(T, TUT2, _), TUTOR(TUT2, A2, _, _, _), A1A2. UNA_AUTOSTRADA(T): PASSAGGIO(T, _, _), ¬ DUE_AUTOSTRADE(T). ?- UNA_AUTOSTRADA(x). Pass agg io Tu tor Targa , Tutor IdT utor , Autos trada Pass agg io Targa Targa Targa , Autos trada Pass agg io Tu tor Targa , Autos trada A  Autos trada - Tutor =IdT utor Targa , Tutor IdT utor , Autos trada Tutor =IdT utor Autos trada A                            B. Interrogazioni SQL (10 punti) 1. Scrivere una view che trovi , per ogni autostrada, le targhe che hanno superato il limite di velocità . (3 punti) create view Multati (Autostrada, Targa) as select distinct T1.Autostrada, P1. Targa from Passaggio P1, Tutor T1, Passaggio P2, Tutor T2 where P1.Tutor=T1.IdTutor and P2.Tutor=T2.IdTutor and T1.Autostrada=T2.Autostrada and P1.Targa = P2.Targa and T1.NumProgressivo=T2.NumProgressivo - 1 and (P2.Timestamp - P1.Timestamp) < P2.TempoMinDalTutorPrecedente 2. Trovare le autostrade su cui non sono mai state fatte infrazioni del limite di velocità (3 punti) . select IdAutostrada from Autostrada A where IdAutostrada not in (select distinct Autostrada from Multati M) 3. Trovare , per ogni autostrada, il punto più trafficato nel mese di maggio 20 17 . (4 punti) select T.Autostrada, T.IdTutor from Passaggio P join Tutor T on P.Tutor = T.IdTutor where date(Timestamp) between 1/5/2017 and 31/5/2017 group by T.Autostrada, T.IdTutor having count(*) >= ALL (select count(*) from Passaggio P2 join Tutor T2 on P2.Tutor = T2.IdTutor where date(Timestamp) between 1/5/2017 and 31/5/2017 and T2.Autostrada = T.Autostrada group P2. by Tutor ) Oppure: CREATE VIEW TutorNumPassaggi (TutorId, Autostrada, Num) AS ( SELECT T.IdTutor, T.Autostrada, CO UNT(*) FROM Passaggio AS P, Tutor AS T WHERE P.Tutor=T.IdTutor AND DATE(T.Timestamp) BETWEEN ‘2017 -05 -01’ AND ‘2017 -05 -31’ GROUP BY T.IdTutor, T.Autostrada ) SELECT T.IdTutor, T.Autostrada FROM TutorNumPassaggi AS T1 WHERE T1.Num = ( SELECT MAX(T2.Num) FROM TutorNumPassaggi AS T2 WHERE T2.Autostrada=T1.Autostrada ) C. DDL : Definizione di schemi (3 punti) a) Specificare in SQL la creazione della tabella PASSAGGIO , definendo i vincoli di tupla e di dominio ritenuti opportuni ed esprimendo eventuali vincoli di integrità referenziale relativi a tutte le tabelle dello schema. (1 punto) create table Passaggio ( Targa CHAR(7), Tutor INT REFERENCES Tutor(IdTutor) ON UPDATE CASCADE ON DELETE NO ACTIO N, Timestamp TIMESTAMP, PRIMARY KEY (Targa, Timestamp) ); b) Specificare in SQL il vincolo che la numerazione progressiva dei tutor sia coerente con il loro posizionamento sui chilometri dell’autostrada . Ovvero, i l tutor numero X si trova, dal punto di vist a dei chilometri, dopo tutti i tutor di numero X . (2 punti) create assertion Coerenza check ( not exists ( select * from Tutor T1, Tutor T2 where T1.Autostrada = T2.Autostrada and T1.NumProgressivo< T2.NumProgressivo and T1.AlKm>=T2.AlKm ) ); D. Progettazione di una base di dati (1 3 punti) Progettare la base di dati relativa alla situazione descritta nel seguito. Svolgere il progetto concettuale usando il modello ER, e il progetto logico con il modello relazionale. Specificare le chiavi primarie e i vincoli di integrità referenziale. Il Dipart imento di Elettronica, Informazione e Bioingegneria (DEIB) del Poli tecnico di Milano deve gestire i progetti di ri cerca di cui sono responsabili i suoi docent i. Ogni docente ha un nome , un cognome, un numero identificativo, un settore disciplinare e un ruolo (ordinario/associato/ricercatore). Ogni docente può essere responsabile di vari progetti e può partecipare anche ad altri progetti (tutti i responsa bili partecipano al progetto di loro competenza). Ogni progetto ha un acronimo, un titolo, una data di inizio e di fine, un finanziatore pubblico o privato, un budget che è tipicamente diviso nelle voci di personale, beni inventariabili, pubblicazioni, mis sioni ed altri costi. I progetti europei (finanziati dalla UE) sono di tre tipi: ERC, EIT, o generici H2020; il ruolo del DEIB -Politecnico nei progetti pubblici può essere di coordinatore o di partner generico, il coordinatore ha un budget speciale per il coordinamento. Per ottenere il finanziamento, ogni voce del budget del progetto deve essere coperta da costi sostenuti nel progetto. Ogni partecipazione dei docenti ad un progetto va rendicontata, indicando per ogni giorno lavorativo del progetto il numero di ore in cui ogni partecipante ha lavorato al progetto. Il DEIB -Politecnico riceve, per ogni docente che partecipa al progetto, una quota di finanziamento calcolata in base allo stipendio medio mensile del docente; inoltre, ai progetti possono afferire a nche studenti di dottorato oppure laureati che assumono posizioni a contratto, il cui costo è coperto dal progetto e rientra nella voce “personale”. Tutti i partecipanti (docenti, studenti di dottorato, laureati a contratto) possono svolgere missioni in It alia o all’e stero, caratterizzate da un luogo, una motivazione, una data di inizio e fine ed un costo complessivo; per poter attribuire una missione ad un progetto le sue date di inizio e fine devono essere comprese entro la data di inizio e di fine del pr ogetto. Schema concettuale Schema logico E. Teoria (1 punto ) Spiegare brevemente perché e in cosa Datalog è più potente degli altri linguaggi formali studiati (Calcolo Relazionale e Algebra Relazionale). NOTE  Non è ammessa la consultazione di libri e appunti.  Alla domanda di teoria occorre rispondere con parole proprie, in modo che i docenti possano valutare il livello di comprensione dell’argomento. (1,N) (0,N) Progetto Acronimo Titolo Data Inizio Partecipazione Data Fine (t,e) ProgettoEuropeo Tipo Progetto Europeo EnteFinanziatore Nome Tipo (0,N) (1,1) Finanziamento Costo Personale CostoBeni Inventariabili Costo Missioni Altri Costi Persona Identificativo Nome Cognome (t, e) Docente SSD NonDocente Ruolo Docente Salario Tipo Costo Responsabile (0,N) (1,1) Rendicontazione Rendicontazione Progetto (1,1) (0,N) Rendicontazione Docente (1,1) (0,N) Data NumeroOre Progetto Missione (0,N) Missione Codice Luogo Motivazione (1,1) DataInizio DataFine Costo Incaricato (1,1) (0,N) ProgettoCon Coordinamento Budget Coordinamento ProgettoPubblico EntePubblico Finanziatore ProgettoPrivato Azienda Finanziatrice (p,o) Acronimo Titolo Dat aInizio DataFine CostoPe rsonale CostoBe niInventariabili Cost oMissioni AltriCosti Pubblico -Privato EntePubblicoFinanziat ore * AziendaFinanziatrice * ProgettoEuropeo * TipoProgettoEuropeo * ProgettoConCoordinament o * BudgetCoordinamento * IdRespons abile Progetto Identificativo Nome Cognome Tipo SSDDoce nte * RuoloDocente * SalarioDocente * Persona Codice Luogo Motivazione DataInizio D ataFine Costo Proget to Incaricato Missione Docent e Progett o Data NumeroOre Rendicontazione Pe rsona Progett o Cos to Partecipazione