ESERCIZIO BONUS 1 (+0.8/+1 pt) –
Implementazione di basi di dati relazionali con MySQL
20/10/2020, Consegna: 2/11/2020, ore 23.55
Modalità di consegna à Attraverso la piattaforma di e-learning VIRTUALE (https://virtuale.unibo.it) di UNIBO. Passi da svolgere:
1) Collegarsi a: https://virtuale.unibo.it, utilizzando le proprie credenziali istituzionali UNIBO (email/password) per l’accesso.
2) Scegliere il corso di Basi di dati della Laurea in Informatica per il Management, anno accademico 2020/2021 (codice: 70155).
3) Cliccare su Esercizio Bonus 1 à Consegna File, e procedere con l’upload del file contenente la soluzione dell’esercizio. E’ possibile ripetere l’operazione di upload.
4) Quando si vuole consegnare l’elaborato DEFINITIVO, cliccare su “Consegna Compito”.
Da questo momento, nessun ulteriore upload è possibile. Se non si clicca su
“Consegna Compito”, il docente NON vede l’elaborato.
5) La sottomissione è possibile dal 20/10/2020 al 2/11/2020, ore 23.55.
COSA CONSEGNARE?
• Allegare un singolo file consegna1.sql, contenente tutto il codice SQL che implementa le specifiche dell’esercizio descritto a seguire. NON USARE sqldump!
---
0) Utilizzando il software MySQL, costruire il database MYTPER, relativo ad una piattaforma di gestione del trasporto pubblico della città di Bologna. Il database contiene le informazioni relative alle fermate degli autobus, alle linee degli autobus, alle rotte temporali (orari in cui gli autobus si fermano alle fermate), agli utenti che hanno sottoscritto degli abbonamenti ed alle eventi di validazione degli abbonamenti sugli autobus (ossia quando gli utenti registrati validano un abbonamento salendo sull’autobus, posizionando il badge in prossimità del lettore posto sul veicolo).
Il database MYTPER è composto dalle seguenti tabelle (usare il table engine: INNODB):
FERMATA(Nome, Latitudine, Longitudine, Pensilina)
AUTOBUS(Numero, FermataPartenza, FermataArrivo, ModelloVeicolo, Capienza) TIMETABLE(Codice, NrAutobus, NomeFermata, OrarioArrivo)
ABBONAMENTO(Id, Nome, Cognome, AnnoNascita, Prezzo, Tipologia, NumUtilizzi) VALIDAZIONE(Codice, IdAbbonamento, NrAutobus, DataAttuale)
CIRCOLAZIONE(Codice, DataAttuale, NumeroUtenti)
Vincoli sui dati:
Ø Vincolo di integrità referenziale tra AUTOBUS.FermataPartenza e FERMATA.Nome Ø Vincolo di integrità referenziale tra AUTOBUS.FermataArrivo e FERMATA.Nome Ø Vincolo di integrità referenziale tra TIMETABLE.NrAutobus e AUTOBUS.Numero Ø Vincolo di integrità referenziale tra TIMETABLE.NomeFermata e FERMATA.Nome Ø Vincolo di integrità referenziale tra VALIDAZIONE.IdAbbonamento e ABBONAMENTO.Id Ø Vincolo di integrità referenziale tra VALIDAZIONE.NrAutobus e AUTOBUS.Numero Ø FERMATA.Pensilina è un campo booleano.
Ø AUTOBUS.Capienza è un intero con valore di default pari a 75. Si assume che gli autobus di una linea (es. linea 13) hanno tutti la stessa capienza (e modello di veicolo).
Ø AUTOBUS.ModelloVeicolo è una stringa di lunghezza massima pari a 30 caratteri.
Ø TIMETABLE.Codice è un intero auto-incrementale.
Ø TIMETABLE.OrarioArrivo è di tipo TIME (ora:minuti).
Ø TIMETABLE.OrarioArrivo non può essere NULL.
Ø ABBONAMENTO.Nome e ABBONAMENTO.Cognome sono stringhe di lunghezza massima pari a 50 caratteri.
Ø ABBONAMENTO.Tipologia (type ENUM) può assumere tre valori: “ANNUALE”, “SEMESTRALE”, o “AGEVOLATO”.
Ø ABBONAMENTO.NumUtilizzi è un intero con valore di default pari a 0.
Ø VALIDAZIONE.Codice è un intero auto-incrementale.
Ø VALIDAZIONE.DataAttuale non può essere NULL ed è di tipo DATETIME.
Ø CIRCOLAZIONE.Codice è un intero auto-incrementale.
Ø CIRCOLAZIONE.DataAttuale non può essere NULL ed è di tipo DATETIME.
Ø Se rimuovo un AUTOBUS, cancello anche tutte le righe nella TIMETABLE, e tutte le righe nella VALIDAZIONE che fanno riferimento a quell’autobus.
Ø Se rimuovo un UTENTE, cancello anche tutte le righe nella VALIDAZIONE che fanno riferimento a quell’utente.
Costruire il database MYTPER, rimuovendo prima (da codice) ogni altro database pre- esistente con lo stesso nome presente nell’installazione corrente di MYSQL.
1) Popolare il contenuto delle tabelle FERMATA e AUTOBUS caricando i dati dai file fermate.txt e autobus.txt. I file sono disponibili sulla pagina Web del corso. Il caricamento dei dati di una tabella deve avvenire attraverso un’unica istruzione di LOAD DATA (NO INSERT ripetute).
2) Implementare le seguenti Stored Procedure:
a) GeneraTimeTable (IN NumAutobus INT, IN NomeFermata VARCHAR(20), IN OraInizio TIME, IN OraFine TIME, IN Frequenza INT) à Verifica che NumAutobus sia presente nella tabella AUTOBUS e NomeFermata sia presente nella tabella FERMATA. In caso affermativo, aggiunge in automatico delle righe nella tabella TIMETABLE per l’autobus e la fermata passate in input, considerando tutti i possibili orari di arrivo compresi nell’intervallo temporale tra OraInizio ed OraFine (estremi inclusi), con frequenza -espressa in minuti- data dal campo Frequenza. Ad esempio, invocando la procedura nel modo seguente:
CALL GeneraTimeTable(19, “Ugo Bassi”, “9:00:00”, “9:50:00”, 20) Inserisce le seguenti righe nella tabella TIMETABLE:
#RIGA_CORRENTE, 19, “Ugo Bassi”, 9:00:00
#RIGA_CORRENTE+1, 19, “Ugo Bassi”, 9:20:00
#RIGA_CORRENTE+2, 19, “Ugo Bassi”, 9:40:00
NOTA: Implementare le operazioni sui dati all’interno di una transazione.
b) NuovoAbbonamento (IN IdAb INT, IN NomeUtente VARCHAR(50), IN CognomeUtente VARCHAR(50), IN Nascita INT, IN Tipo VARCHAR(0)) à Inserisce una nuova riga nella tabella ABBONAMENTO, con id pari a IDAb, utente NomeUtente, cognome CognomeUtente, anno di nascita Nascita e tipologia Tipo. Il
prezzo viene settato automaticamente in base alla tipologia di inserimento, nel modo seguente:
tipologia: “ANNUALE” à prezzo: 400 tipologia: “SEMESTRALE” à prezzo: 200 tipologia: “AGEVOLATO” à prezzo: 150
NOTA: Implementare le operazioni sui dati all’interno di una transazione.
c) NuovaValidazione(IN IdAb INT, IN NumeroAutobus INT, IN DataVal DATETIME) à Verifica l’esistenza di un abbonamento nella tabella ABBONAMENTO con l’Id pari a quello passato in input (IdAb); nel caso, aggiunge una riga nella tabella VALIDAZIONE con id dell’abbonamento pari a IdAb, numero di autobus pari a NumeroAutobus e data pari a DataVal.
NOTA: Implementare le operazioni sui dati all’interno di una transazione.
d) EliminaValidazioni() à Rimuove tutte le validazioni nella relativa tabella, che abbiano una data anteriore (= più “vecchia”) di ALMENO 1 mese rispetto alla data corrente.
3) Implementare i seguenti trigger:
a) AumentaUtilizzi à Dopo ogni inserimento nella tabella VALIDAZIONE, si modifica la tabella ABBONAMENTO aumentando di 1 unità il campo NumUtilizzi per l’utente abbonato corrispondente (ossia dell’utente di cui è stata aggiunta la validazione).
b) RimuoviUtilizzi à Dopo ogni cancellazione nella tabella VALIDAZIONE, si modifica la tabella ABBONAMENTO riducendo di 1 unità il campo NumUtilizzi per l’utente abbonato corrispondente (ossia dell’utente di cui è stata rimossa la validazione).
4) Implementare il seguente evento (trigger temporizzato):
a) [OPZIONALE, +0.2pt] ControllaUtentiAttivi à Ogni 5 secondi, l’evento calcola il numero di UTENTI che sono in circolazione, e sposta il risultato nella variabile globale NUM_UTENTI_CIRCOLANTI. Un utente è circolante se ha effettuato una validazione del suo abbonamento con DataAttuale maggiore o uguale all’orario attuale meno 5 minuti (NOW()-5 min). Se un utente valida l’abbonamento più volte (es. su autobus differenti) deve essere contato solo 1 volta. Come azione, l’evento aggiunge una nuova riga nella tabella CIRCOLAZIONE, con DataAttuale pari al valore di NOW() e NumeroUtenti pari al calcolo di cui sopra.
5) Implementare le seguenti viste:
a) NUMERO_CORSE_MATTINA(Conteggio) à restituisce il conteggio totale di autobus che si fermano presso la fermata “Ugo Bassi”, nella fascia oraria compresa tra le 7 e 9, estremi inclusi.
b) LISTA_UTENTI_TOP(Nome, Cognome) à restituisce nome e cognome degli utenti che hanno effettuato il numero maggiore di corse con gli autobus (ossia che hanno effettuato il numero maggiore di validazioni dell’abbonamento).
c) FERMATE_POCO_SERVITE(NomeFermata, Latitudine, Longitudine) à restituisce il nome, latitudine e longitudine delle fermate dotate di pensilina nelle quali si fermano - complessivamente- meno di 3 autobus.
d) LISTA_UTENTI_FEDELI (Nome, Cognome) à restituisce nome e cognome degli utenti nati dopo il 1970 che hanno validato i loro abbonamenti SEMPRE e SOLO su una stessa linea di autobus.
6) Creare un nuovo utente: utenteWeb. Assegnare all’utente SOLO i permessi minimi per poter utilizzare le quattro viste definite in precedenza.
--- SUGGERIMENTI
a) Domini Temporali (https://dev.mysql.com/doc/refman/8.0/en/datetime.html) o Il dominio TIME gestisce tipi di dato temporali con formato Ore:Minuti:Secondi o Il dominio DATETIME gestisce tipi di dato temporali con formato
o Anno-Mese-Giorno Ore:Minuti:Secondi
o Il comando NOW() restituisce la DATETIME attuale
o Il comando DATE_ADD(D1,D2) effettua la somma tra due DATETIME o TIME o Il comando DATE_SUB(D1,D2) effettua la somma tra due DATETIME o TIME Es. Inserimento di un campo DATETIME
INSERT INTO TABELLA(Campo_DATETIME) VALUES (“2019-10-22 10:00:00”) DATE_SUB(NOW(), INTERVAL 5 SECOND) à data corrispondente a 5 min fa
b) Eventi temporizzati (https://dev.mysql.com/doc/refman/8.0/en/create-event.html) Costruisce trigger eseguiti ad intervalli di tempo prefissati o ricorrenti
CREATE EVENT NomeEvento
ON SCHEDULE AT X (X=intervallo temporale, vedi link sopra) DO SEQUENZA_DI_COMANDI_SQL_DA_ESEGUIRE
--- VINCOLI DI CONSEGNA
• L’implementazione della base di dati deve essere completa e funzionante.
• NON sono consentite consegne di gruppo. Consegne multiple (ossia stesso codice da parte di più studenti) NON sono valutate.
• Inserire ove possibile dei commenti nel codice (in MySQL i commenti si inseriscono tramite i caratteri speciali: #Commento su una riga o /* Commento su più righe */).
• Seguire esattamente le specifiche del testo per quanto riguarda i nomi del database, tabelle, attributi, viste, stored procedure, etc.
• La consegna deve avvenire attraverso la piattaforma indicata nella prima pagina, ed entro la deadline stabilita. Consegne via email NON saranno valutate.
• L’assegnamento del bonus è una funzione booleana (assegnato/non assegnato).
• E’ possibile testare l’implementazione dei vari punti mediante un programma di test messo a disposizione sulla pagina Web del corso.
