Progetto di Base di Dati

Salvatore Allegra

Studio analitico per la creazione di una Base di Dati

 per il noleggio urbano distribuito di veicoli a basso impatto ambientale.

Descrizione e specifiche

Si vuole realizzare il progetto di una base di dati per la gestione di un sistema di noleggio di biciclette e monopattini in una città finalizzato alla riduzione dell’impatto ambientale dei mezzi privati sull’inquinamento atmosferico nei centri urbani, mettendo in atto strategie per scoraggiare l’utilizzo da parte dei minori di minicar, di scooter o altri mezzi a combustione interna sviluppando una sensibilità verso la mobilità sostenibile.

Le biciclette possono essere tradizionali oppure a pedalata assistita, i monopattini sono tutti elettrici.

I mezzi (circa 2500 biciclette di cui 500 tradizionali e 500 monopattini) sono distribuiti in diverse aree della città (circa 200).

In ogni area ci sono diversi stalli (in media 30) dove i mezzi possono essere noleggiati e riconsegnati.

I cittadini (circa 2000000) possono creare un profilo presso un portale contestualmente otteranno una Carta Mobilità oltre ad avere una serie di agevolazioni.

I Cittadini  che non intendono effettuare una registrazione possono noleggiare un mezzo presso le aree di noleggio, tutti gli utenti debbono essere in possesso di Carta di Identità elettronica (circa 200000), in modo da potere riconoscere chi ha usufruito del servizio e addebitargliene il costo in caso di mancato pagamento.

I pagamenti in caso di utente non registrato sul portale possono essere effettuati tramite contante o carta di credito presso gli appositi box attigui all’area di noleggio.

I cittadini che scelgono di iscriversi al servizio di noleggio online oltre ad avere uno sconto variabile sui noleggi, in funzione dei chilometri percorsi e possono ritirare una Carta Mobilità prepagata dietro un pagamento una tantum, più il costo della ricarica.

L’utente con profilo potrà  accedere tramite nome utente e password al suo profilo.

L’utente con profilo potrà tramite la Carta Mobilità di avere sotto controllo i propri noleggi effettuati e il numero dei chilometri percorsi e il credito residuo.

Si vuole dare all’utente che registra un profilo la possibilità di richiedere una ulteriore Carta Mobilità connessa a quella principale per ogni figlio all’interno del nucleo familiare, in questo caso l’utente dovrà inserire gli estremi della carta di identità elettronica di ogni altro componente previsto, quindi il sistema terrà  traccia anche delle generalità degli eventuali utenti connessi a tutte le Carte Mobilità acquistate dal Titolare del profilo.

Solo l’utente Titolare di un profilo potrà verificare i dati delle Carte Mobilità attivate.

Si vuole tenere traccia di uno storico di tutte le Carte acquistate dal Titolare comprese quelle disattivate.

Il prezzo del noleggio è orario.

Un mezzo può essere noleggiato in un’area e restituito in un'altra.

Per ogni area si vuole conoscere il numero noleggi che effettua giornalmente.

Ogni mezzo va in manutenzione dopo un periodo di operatività di 3 mesi, se il mezzo è elettrico va in manutenzione ogni 2 mesi di operatività per constatare lo stato di deterioramento della batteria se questo è elettrico in questo caso deve essere memorizzato il livello di deterioramento della batteria, se il mezzo non è elettrico deve essere memorizzato lo stato di deterioramento dei freni.

Analisi dei requisiti. Glossario dei termini

Termini	Descrizione	Sinonimi	Altri termini collegati
Bicicletta a pedalata assistita	Le biciclette elettriche possono essere noleggiate e riconsegnati presso gli stalli.	Mezzo Bici elettrica	Noleggio
Bicicletta tradizionale	Le biciclette tradizionali possono essere noleggiati e riconsegnati presso gli stalli.	mezzo	Noleggio
Monopattino elettrico	I monopattino possono essere noleggiati e riconsegnati presso gli stalli.	mezzo	Noleggio Stallo Manutenzione
Mezzo	Ovvero biciclette elettriche, biciclette tradizionali, monopattini elettrici	Bicicletta Monopattino	Stallo Noleggio
Area	L’area è la zona in cui ci sono diversi stalli.	zona	Stallo
Stallo	Gli stalli sono le varie postazioni all’interno della singola area dalla quale è possibile prelevare e consegnare una singola bicicletta o un monopattino	posizione	Area, Bicicletta tradizionale, Bicicletta elettricam Monopattino, Mezzo
Cittadino	I cittadini possono noleggiare biciclette o monopattini.	persona	Noleggio, Utente,
Profilo	Il profilo contiene la username e la password e il numero di carta di credito, il profilo è posseduto solo da chi si è registrato dal portale.		Utente
Titolare	Un titolare è un utente registrato possessore di una singola Carta Mobilità.	Utente	Carta Mobilità
Utente	L’utente è un utente registrato con un profilo oppure che è stato accreditato da un utente registrcon profilo, oppure può essere anche un cittadino che si accredita presso l’area di noleggio tramite carta di identità elettronica.	Titolare	Cittadino, Profilo, Carta Mobilità
Carta Mobilità	La Carta Mobilità permette di avere sotto controllo i noleggi effettuati, il numero dei chilometri percorsi e il credito residuo. Solo un utente con profilo o un utente accreditato da un utente con profilo possiede una Carta Mobilità.	Carta	Titolare Utente
Registrazione	La registrazione è l’atto che compiono  i cittadini quando accedono al sistema di noleggio i propri dati sul sistema di noleggio tramite portale.		Utente
Manutenzione	La manutenzione ordinaria è un controllo che viene fatto sulla bicicletta periodicamente	controllo	Bicicletta Officina
Noleggio	Il noleggio presuppone che il sistema tenga traccia dell’utente e inizia con l’atto di sganciare il mezzo dallo stallo e finisce con la riconsegna del mezzo presso un altro stallo.	operazione	Bicicletta stallo
Restituzione	La restituzione consiste nell’agganciare la bicicletta nell’apposito stallo, il noleggio finisce con la restituzione.	operazione	Bicicletta Stallo
Servizio	Un servizio è una parte accessoria opzionale di un noleggio.		Noleggio
Pagamento	Il pagamento di un noleggio viene effettuato dall’Utente dopo la consegna del mezzo.	tariffa	Noleggio, Cittadino Utente

Raggruppiamo i requisiti in insiemi omogenei

Dati di carattere generale:

Si vuole realizzare il progetto di una base di dati per la gestione di un sistema di noleggio di biciclette e monopattini in una città finalizzato alla riduzione dell’impatto ambientale dei mezzi privati sull’inquinamento atmosferico nei centri urbani, mettendo in atto strategie per scoraggiare l’utilizzo da parte dei minori di minicar, di scooter o altri mezzi a combustione interna sviluppando una sensibilità verso la mobilità sostenibile.

Dati sulle biciclette.

Le biciclette possono essere tradizionali oppure a pedalata assistita, Le biciclette (circa 2500 biciclettei cui 500 tradizionali ) sono distribuite in diverse aree della città (circa 200). Ogni bicicletta ha un RFID che è letto in fase di noleggio o restituzione. Ogni bicicletta va in manutenzione ogni 3 mesi. Un mezzo può essere noleggiato in un’area e restituito in un'altra.

Dati sui monopattini.

I monopattini sono tutti elettrici (circa 500). Ogni monopattino ha un RFID che è letto in fase di noleggio o restituzione. Ogni bicicletta va in manutenzione ogni 2 mesi. Un mezzo può essere noleggiato in un’area e restituito in un'altra.

Dati sui mezzi

I mezzi (circa 2500 biciclette di cui 500 tradizionali e 500 monopattini) sono distribuiti in diverse aree della città (circa 200). Un mezzo può essere noleggiato in un’area e restituito in un'altra.

Dati sulle aree

In ogni area ci sono diversi stalli (in media 30) dove i mezzi possono essere noleggiati e riconsegnati. Per ogni area si vuole conoscere il numero noleggi che effettua giornalmente.

Dati sugli stalli

Ci sono diversi stalli (circa 30) per ogni area  dove possono essere noleggiate e riconsegnate le biciclette.

 

Dati sui cittadini

I cittadini (circa 2000000) possono creare un profilo presso un portale contestualmente otteranno una Carta Mobilità oltre ad avere una serie di agevolazioni. I cittadini  che non intendono effettuare una registrazione possono noleggiare un mezzo presso le aree di noleggio, tutti gli utenti debbono essere in possesso di Carta di Identità elettronica (circa 200000), in modo da potere riconoscere chi ha usufruito del servizio e addebitargliene il costo in caso di mancato pagamento. I cittadini che scelgono di iscriversi al servizio di noleggio online oltre ad avere uno sconto variabile sui noleggi, in

funzione dei chilometri percorsi e possono ritirare una Carta Mobilità prepagata dietro un pagamento una tantum, più il costo della ricarica.

Dati sul Profilo

L’utente registrato potrà  accedere tramite nome utente e password al suo profilo.

Dati sui Pagamenti

I pagamenti in caso di utente non registrato sul portale possono essere effettuati tramite contante o carta di credito presso gli appositi box attigui all’area di noleggio.

Dati sull’Utente

L’utente con profilo potrà  accedere tramite nome utente e password al suo profilo. L’utente con profilo potrà tramite la Carta Mobilità di avere sotto controllo i propri noleggi effettuati e il numero dei chilometri percorsi e il credito residuo. Si vuole dare all’utente che registra un profilo la possibilità di richiedere una ulteriore Carta Mobilità connessa a quella principale per ogni figlio all’interno del nucleo familiare, in questo caso l’utente dovrà inserire gli estremi della carta di identità elettronica di ogni altro componente previsto, quindi il sistema terrà  traccia anche delle generalità degli eventuali utenti connessi a tutte le Carte Mobilità acquistate dal Titolare del profilo.

Dati sulla Carta Mobilità

Solo l’utente Titolare di un profilo potrà verificare i dati delle Carte Mobilità attivate. Si vuole tenere traccia di uno storico di tutte le Carte acquistate dal Titolare comprese quelle disattivate.

Dati sul Noleggio

Il prezzo del noleggio è orario.

Dati sulla Manutenzione

Ogni mezzo va in manutenzione dopo un periodo di operatività di 3 mesi, se il mezzo è elettrico va in manutenzione ogni 2 mesi di operatività per constatare lo stato di deterioramento della batteria se questo è elettrico in questo caso deve essere memorizzato il livello di deterioramento della batteria, se il mezzo non è elettrico deve essere memorizzato lo stato di deterioramento dei freni.

Individuare i concetti più rilevanti e rappresentarli in uno schema scheletro.

SCHEMA SCHELETRO I

Schema Scheletro

Schema Intermedio

Schema Finale – con Cardinalità e Attributi.

Dizionari dei dati relativo alle Entità.

Termini	Descrizione	Attributi	Identificatore
Area	Il luogo da che raggruppa gli stalli	id_area totale_noleggi indirizzo denominazione	id_area
Stallo	La posizione da dove parte un noleggio e dove finisce il noleggio, lo può contenere un mezzo	id_stallo id_area	Area, Bicicletta
Mezzo	Il mezzo che si intende noleggiare, bicicletta, bicicletta elettrica, monopattino.	rfid tipologia inizio_attivita	rfid
Manutenzione	L’operazione periodica di controllo sui mezzi	rfid data	(rfid,data)
Controllo_carica	Relativa esclusivamente ai mezzi elettrici	rfid data codice_batteria livello	(rfid,data)
Controllo_frenata	Relativa esclusivamente alle biciclette tradizionali	rfid data usura_ant usura_post	(rfid,data)
Cittadino	Colui il quale è potenzialmente un Utente. La tabella contenente i dati dei Cittadini non necessariamente deve essere integrata nel database, potrebbe essere una tabella raggiungibile dall’esterno di un ipotetico  database di un anagrafe cittadino.	cf nome cognome stato_civile sesso data_nascita` luogo_nascita provincia residenza numero_civico citta_residenza doc_tipo doc_id doc_comune doc_ente_rilascio doc_rilascio doc_scadenza	cf
Utente	Colui il quale interagisce col sistema sia questo con un profilo, accreditato da un utente con profilo o semplicemente un utente che noleggia un mezzo senza avere mai avuto accesso al portale	cie nome cognome data_nascita cap indirizzo civico	cie
Profilo	Conserva i dati relativi all’acceso di un utente titolare di un profilo quali username e password e il numero di carta di credito.	username password carta_credito email cellulare carta_identita	username
Cartamobilita	Mantiene uno storico dei chilometri percorsi, e del numero dei noleggi effettuati dall’utente e il credito residuo.	id_carta data_rilascio credito num_noleggi totale_km	id_carta
Noleggio	L’atto di prendere una bicicletta, il noleggio parte una volta che la bicicletta è stata recuperata dallo stallo.	data_ora stallo area data_in_ora_in stallo_in area_in km id_utente id_mezzo	(data_ora,id_utente)
Pagamento	Il pagamento si crea una volta che è finito il noleggio e si perfeziona una volta saldato.	data_ora id_utente costo stato_pagamento	(data_ora,id_utente)
Servizi	Sono delle opzioni che si possono aggiungere al noleggio	id_servizio descrizione	id_servizio

Dizionari dei dati relativo alle Relazioni.

Termini	Entità in relazione	Attributi	Descrizione
Accredita	Utente su Utente	cie cie_afferente	Un utente Accredita un altro Utente che afferisce al proprio profilo
Attiva	Noleggio Servizi	data_ora id_utente id_servizio	Un Noleggio può attivare un Servizio
Occupa	Mezzo Stallo	rfid stallo area	Un Mezzo Occupa uno Stallo
Titolarita	Utente Cartamobilita	cie id_carta status_carta	Un Utente è Titolare di una o più Carte mobilità
Parentela E’ relativa all’Entità Cittadino esterna al database del sistema di noleggio.	Cittadino su Cittadino	dichiarante componente	Un Cittadino dichiara di avere un certo componente in quanto figlio nel suo nucleo familiare.

Specifichiamo le Cardinalità.

 

Concetto	Tipo
Effettua	Relazione	La relazione EFFETTUA  tra UTENTE e NOLEGGIO è (0,n), un UTENTE può non effettuare noleggi ma può effettuarne nel tempo più di uno. Al contrario è (1,1) un singolo NOLEGGIO riguarda un solo UTENTE.
Appartiene	Relazione	La relazione APPARTIENE  tra NOLEGGIO e MEZZO è (1,1), in quanto un NOLEGGIO vincola a se la relativa BICICLETTA, non può esserci un noleggio senza bicicletta. Al contrario è (0,n) un singolo MEZZO può essere legato a più noleggi nel corso del tempo ma anche a nessuno.
Occupa	Relazione	La relazione OCCUPA tra BICICLETTA e STALLO è (0,1), in quanto un mezzo può non occupare uno stallo se il mezzo è stato noleggiato o se semplicemente non c’è mai stata una bicicletta nello stallo oppure non può esserci più di una bicicletta nello stallo. Al contrario la cardinalità è (0,1), può esserci uno stallo che non è occupato da  una bicicletta ma al massimo può esserci una bicicletta nello stallo.
Appartiene	Relazione	La relazione APPARTIENE tra STALLO e AREA è di (1,1) in quanto uno stallo non può stare in più aree. Al contrario un AREA può avere più stalli e la relazione è di (1,n), naturalmente non può esistere un’area senza almeno uno stallo.
Ritiro	Relazione	La relazione RITIRO tra NOLEGGIO e STALLO è di tipo (1,1) in quanto il singolo noleggio è relativo a un singolo stallo di una singola area. Al contrario la relazione tra STALLO e NOLEGGIO è di (0,n), in una STALLO può non avvenire alcun noleggio o possono aver avuto luogo più noleggi.
Restituzione	Relazione	Per la relazione RESTITUZIONE tra NOLEGGIO e STALLO è di tipo (0,1) in quanto il noleggio potrebbe non essere stato completato con la consegna della bicicletta inoltre al massimo la bicicletta può essere consegnata in un solo STALLO di una singola area. Al contrario per la relazione RESTITUZIONE tra STALLO e NOLEGGIO vale la cardinalità (0,n) in quanto in uno stallo può non esserci alcuna stata alcuna consegna oppure possono essere avvenute più consegne differenti nel corso del tempo dovute a differenti noleggi.
Comprende	Relazione	La relazione COMPRENDE tra PAGAMENTO e NOLEGGIO è (1,1) ad un pagamento corrisponde a un noleggio. Al contrario è sempre di (1,1) non possiamo avere più pagamenti per il medesimo NOLEGGIO.
Richiede	Relazione	La relazione RICHIEDE tra MANUTENZIONE e MEZZO è di (1,1) ad ogni manutenzione corrisponde un mezzo. Al contrario ad ogni MEZZO può corrispondere più di un controllo ma anche nessuno quindi al relazione è di (0,n)
Registra	Relazione	La relazione REGISTRA tra UTENTE e PROFILO è di (0,1) un utente ha la massimo un solo profilo, al contrario ad ogni PROFILO deve corrispondere un solo UTENTE.
Titolarita	Relazione	La  relazione TITOLARITA tra UTENTE e CARTAMOBILITA è di (0,n) in quanto un utente può aver posseduto altre carte disattivate al contrario è di (1,1) una carta appartiene ad un solo utente.
Accredita	Relazione	La  relazione ACCREDITA  è auto-referenziante un UTENTE di tipo (0,n) perché un utente può acrreditare uno o più utenti ma un utente è accreditato accreditato solo da un altro utente (1,1).

Descriviamo le operazioni previste del sistema di Noleggio e relativo carico.

Operazione	Descrizione	Tipo	Frequenza
O1	Inserisci un nuovo Utente	I	10/giorno
O2	Inserisci un nuovo Utente con profilo	I	20/giorno
O3	Inserisci un nuovo Utente accreditato	I	4/giorno
O4	Effettua un Noleggio	I	40/giorno
O5	Effettua un Pagamento	I	40/giorno
O6	Controlla il numero di noleggi per zona	B	1/giorno
O7	Manda un mezzo in manutenzione	B	10/mese

Tavola dei Volumi

Concetto	Tipologia	Volume
Area	Entità	200
Stallo	Entità	6000 = (30 Stalli)*(200 Aree)
Mezzo	Entità	3000=(2500 elettrici e 500 tradizionali)
Manutenzione	Entità	3000
Controllo_carica	Entità	2500
Controllo_frenata	Entità	500
Cittadino	Entità	2milioni
Utente	Entità	200000 = (10 % dei 2milioni)
Profilo	Entità	200000
Cartamobilita	Entità	200000
Noleggio	Entità	10milioni dopo un anno (ipotizzando che tutti i possibili utenti possano fare almeno un noleggio a settimana per circa 50 settimane)
Pagamento	Entità	10milioni
Servizi	Entità	3
Accredita	Relazione	50000
Attiva	Relazione	3
Occupa	Relazione	3000
Titolarita	Relazione	50000

 

Analisi delle ridondanze

Esaminiamo l’operazione O6 (Controlla i noleggi per zona) con ridondanza e senza ridondanza.

L'attributo “totale_noleggi” di Area è ridondante può essere calcolato  può essere calcolato facendo una selezione dell’attributo “area” da  Noleggio.

(SELECT COUNT(Noleggio.area) FROM Noleggio WHERE Noleggio.area=Area.id_area).

Valutiamo il costo della ridondanza

Per mantenere l'attributo “totale_noleggi” dell'entità Area, assumendo che richieda 4 byte, abbiamo bisogno di 4*200 byte per area che equivale a 800 byte

Con ridondanza avremo

Concetto	E/R	Accessi	Tipo
Area	E	1	L
Area	E	1	S

Il costo dell’operazione O6 è di un accesso in lettura implica 1 lettura

Il costo dell'operazione O7 in presenza di ridondanza, assumendo che 1 scrittura valga quanto 2 letture, è (40*(1L+1S))=40*3=120 letture

Senza ridondanza avremo

L’ operazione interessata la  O6 che viene eseguita una volta al giorno deve accedere in lettura alla tabella Noleggio e fare una ricerca sull’attributo “area” relativamente all’area di cui si vuole sapere il numero di noleggi.

Concetto	E/R	Accessi	Tipo
Noleggio	E	1	L

1Lx200=200 letture il tutto si deve moltiplicare per la mole di noleggi nella tabella noleggio il che risulta certamente più oneroso di 120 letture.

Quindi manteniamo la ridondanza dell’attributo “totale_noleggi” nella tabella Area.

Traduzione Entità-Relazioni.

AREA(id_area, totale_noleggi, indirizzo, denominazione)

STALLO(id_stallo,id_area)

MEZZO(rfid, tipologia, inizio_attivita)

MANUTENZIONE(rfid, data)

CONTROLLO_CARICA(rfid, data, codice_batteria, livello)

CONTROLLO_FRENATA(rfid, data usura_ant, usura_post)

UTENTE(cie, nome, cognome, data_nascita, cap, indirizzo, civico)

PROFILO(username, password, carta_credito, email, cellulare, carta_identita)

CARTAMOBILITA(id_carta, data_rilascio, credito, num_noleggi, totale_km)

NOLEGGIO(data_ora, id_utente,  stallo , area, data_in_ora_in, stallo_in, area_in, km, id_mezzo)

PAGAMENTO(data_ora, id_utente, costo, stato_pagamento)

SERVIZI(id_servizio, descrizione)

ACCREDITA(cie, cie_afferente)

ATTIVA(data_ora, id_utente, id_servizio)

OCCUPA(rfid, stallo, area)

TITOLARITA(cie, id_carta, status_carta)

CITTADINO(cf, nome, cognome, stato_civile, sesso, data_nascita, luogo_nascita, provincia, residenza, numero_civico,           citta_residenza, doc_tipo, doc id, doc_comune, doc_ente_rilascio, doc_rilascio, doc_scadenza)

PARENTELA(dichiarante, componente)

Creazione del database in SQL.

CREATE TABLE `Accredita` (

  `cie` varchar(11) NOT NULL,

  `cie_afferente` varchar(11) NOT NULL

);

CREATE TABLE `Area` (

  `id_area` int(11) NOT NULL,

  `totale_noleggi` int(11) DEFAULT NULL,

  `denominazione` varchar(40) NOT NULL,

  `indirizzo` varchar(40) NOT NULL

) ;

CREATE TABLE `Attiva` (

  `data_ora` datetime NOT NULL,

  `id_utente` varchar(11) NOT NULL,   `id_servizio` int(11) NOT NULL) ;

CREATE TABLE `Cartamobilita` (

  `id_carta` varchar(11) NOT NULL,

  `data_rilascio` date NOT NULL,

  `credito` float NOT NULL,

  `num_noleggi` int(11) NOT NULL,

  `totale_km` int(11) NOT NULL

) ;

CREATE TABLE `Cittadino` (

  `cf` varchar(16) NOT NULL,

  `nome` varchar(20) NOT NULL,

  `cognome` varchar(20) NOT NULL,

  `stato_civile` set('Celibe','Sposato','Nubile','Sposata') NOT NULL,

  `sesso` set('M','F') NOT NULL,

  `data_nascita` date NOT NULL,

  `luogo_nascita` varchar(20) NOT NULL,

  `provincia` varchar(20) NOT NULL,   `residenza` varchar(40) NOT NULL,

  `numero_civico` varchar(5) NOT NULL,

  `Citta_residenza` varchar(20) NOT NULL,

  `doc_tipo` set('CIE','CI') NOT NULL,

  `doc_id` varchar(11) NOT NULL,

  `doc_comune` varchar(20) NOT NULL,

  `doc_ente_rilascio` varchar(20) NOT NULL,

  `doc_rilascio` date NOT NULL,

  `doc_scadenza` date NOT NULL

) ;

CREATE TABLE `Controllo_carica` (

  `rfid` int(11) NOT NULL,

  `data` date NOT NULL,

  `codice_batteria` varchar(11) NOT NULL,

  `livello` int(11) NOT NULL

) ;

CREATE TABLE `Controllo_frenata` (

  `rfid` int(11) NOT NULL,

  `data` date NOT NULL,

  `usura_ant` int(11) NOT NULL,

  `usura_post` int(11) NOT NULL

) ;

CREATE TABLE `Manutenzione` (

  `rfid` int(11) NOT NULL,

  `data` date NOT NULL

) ;

CREATE TABLE `Mezzo` (

  `rfid` int(11) NOT NULL,

  `tipologia` varchar(30) NOT NULL,

  `inizio_attivita` date NOT NULL

) ;

CREATE TABLE `Noleggio` (

  `data_ora` datetime NOT NULL,

  `stallo` int(11) NOT NULL,

  `area` int(11) NOT NULL,

  `data_in_ora_in` datetime DEFAULT NULL,

  `stallo_in` int(11) DEFAULT NULL,

  `area_in` int(11) DEFAULT NULL,

  `km` int(11) DEFAULT NULL,

  `id_utente` varchar(11) NOT NULL,

  `id_mezzo` int(11) NOT NULL) ;

CREATE TABLE `Occupa` (   `rfid` int(11) NOT NULL,

  `stallo` int(11) NOT NULL,

  `area` int(11) NOT NULL

) ;

CREATE TABLE `Pagamento` (

  `data_ora` datetime NOT NULL,

  `id_utente` varchar(11) NOT NULL,

  `costo` float DEFAULT NULL,

  `stato_pagamento` set('pagato','richiesto') NOT NULL

) ;

CREATE TABLE `Parentela` (

  `dichiarante` varchar(20) NOT NULL,

  `componente` varchar(20) NOT NULL

) ;

CREATE TABLE `Profilo` (

  `username` varchar(64) NOT NULL,   `password` varchar(64) NOT NULL,

  `carta_credito` varchar(16) NOT NULL,

  `email` varchar(100) NOT NULL,

  `cellulare` varchar(14) NOT NULL,

  `carta_identita` varchar(11) NOT NULL

);

CREATE TABLE `Servizi` (

  `id_servizio` int(11) NOT NULL,

  `descrizione` varchar(20) NOT NULL

) ;

CREATE TABLE `Stallo` (

  `id_stallo` int(11) NOT NULL,

  `id_area` int(11) NOT NULL

) ;

CREATE TABLE `Titolarita` (

  `cie` varchar(11) NOT NULL,

  `id_carta` varchar(11) NOT NULL,

  `status_carta` set('attiva','bloccata') NOT NULL

) ;

CREATE TABLE `Utente` (

  `cie` varchar(11) NOT NULL,

  `nome` varchar(20) NOT NULL,

  `cognome` varchar(20) NOT NULL,

  `data_nascita` date NOT NULL,

  `cap` varchar(6) NOT NULL,

  `indirizzo` varchar(40) NOT NULL,

  `civico` varchar(5) NOT NULL

) ;

ALTER TABLE `Accredita`

  ADD PRIMARY KEY (`cie`,`cie_afferente`),

  ADD KEY `cie_afferente` (`cie_afferente`);

ALTER TABLE `Area`

  ADD PRIMARY KEY (`id_area`);

ALTER TABLE `Attiva`

  ADD PRIMARY KEY (`data_ora`,`id_utente`,`id_servizio`),

  ADD KEY `id_servizio` (`id_servizio`);

ALTER TABLE `Cartamobilita`

  ADD PRIMARY KEY (`id_carta`);

ALTER TABLE `Cittadino`

  ADD PRIMARY KEY (`cf`),

  ADD UNIQUE KEY `doc_id` (`doc_id`);

ALTER TABLE `Controllo_carica`

  ADD PRIMARY KEY (`rfid`,`data`);

ALTER TABLE `Controllo_frenata`

  ADD PRIMARY KEY (`rfid`,`data`);

ALTER TABLE `Manutenzione`

  ADD PRIMARY KEY (`rfid`,`data`);

ALTER TABLE `Mezzo`

  ADD PRIMARY KEY (`rfid`);

ALTER TABLE `Noleggio`

  ADD PRIMARY KEY (`data_ora`,`id_utente`),

  ADD KEY `stallo` (`stallo`,`area`),

  ADD KEY `stallo_in` (`stallo_in`,`area_in`),

  ADD KEY `id_utente` (`id_utente`),

  ADD KEY `stallo_in_2` (`stallo_in`),

  ADD KEY `Noleggio_ibfk_1` (`id_mezzo`),

  ADD KEY `data_in_ora_in` (`data_in_ora_in`);

ALTER TABLE `Occupa`

  ADD PRIMARY KEY (`rfid`,`stallo`,`area`),

  ADD UNIQUE KEY `stallo` (`stallo`,`area`),

  ADD UNIQUE KEY `rfid` (`rfid`),   ADD KEY `area` (`area`);

ALTER TABLE `Pagamento`

  ADD PRIMARY KEY (`data_ora`,`id_utente`);

ALTER TABLE `Parentela`

  ADD PRIMARY KEY (`dichiarante`,`componente`),

  ADD KEY `dichiarante` (`dichiarante`,`componente`),

  ADD KEY `figlio_7` (`componente`);

ALTER TABLE `Profilo`

  ADD PRIMARY KEY (`username`),

  ADD KEY `Profilo_ibfk_1` (`carta_identita`);

ALTER TABLE `Servizi`

  ADD PRIMARY KEY (`id_servizio`);

ALTER TABLE `Stallo`

  ADD PRIMARY KEY (`id_stallo`,`id_area`),

  ADD KEY `id_area` (`id_area`);

ALTER TABLE `Titolarita`

  ADD PRIMARY KEY (`cie`,`id_carta`),

  ADD KEY `id_carta` (`id_carta`);

ALTER TABLE `Utente`

  ADD PRIMARY KEY (`cie`);

ALTER TABLE `Accredita`

  ADD CONSTRAINT `Accredita_ibfk_1` FOREIGN KEY (`cie`) REFERENCES `Utente` (`cie`),

  ADD CONSTRAINT `Accredita_ibfk_2` FOREIGN KEY (`cie_afferente`) REFERENCES `Utente` (`cie`); ALTER TABLE `Attiva`

  ADD CONSTRAINT `Attiva_ibfk_1` FOREIGN KEY (`id_servizio`) REFERENCES `Servizi` (`id_servizio`),   ADD CONSTRAINT `Attiva_ibfk_2` FOREIGN KEY (`data_ora`,`id_utente`) REFERENCES `Noleggio` (`data_ora`, `id_utente`);

ALTER TABLE `Controllo_carica`

  ADD CONSTRAINT `Controllo_carica_ibfk_1` FOREIGN KEY (`rfid`,`data`) REFERENCES `Manutenzione`

(`rfid`, `data`);

ALTER TABLE `Controllo_frenata`

  ADD CONSTRAINT `Controllo_frenata_ibfk_1` FOREIGN KEY (`rfid`,`data`) REFERENCES `Manutenzione`

(`rfid`, `data`);

ALTER TABLE `Manutenzione`

  ADD CONSTRAINT `Manutenzione_ibfk_1` FOREIGN KEY (`rfid`) REFERENCES `Mezzo` (`rfid`);

ALTER TABLE `Noleggio`

  ADD CONSTRAINT `Noleggio_ibfk_1` FOREIGN KEY (`id_mezzo`) REFERENCES `Mezzo` (`rfid`),

  ADD CONSTRAINT `Noleggio_ibfk_3` FOREIGN KEY (`stallo`,`area`) REFERENCES `Stallo` (`id_stallo`, `id_area`),

  ADD CONSTRAINT `Noleggio_ibfk_4` FOREIGN KEY (`id_utente`) REFERENCES `Utente` (`cie`),

  ADD CONSTRAINT `Noleggio_ibfk_5` FOREIGN KEY (`stallo_in`,`area_in`) REFERENCES `Stallo` (`id_stallo`, `id_area`);

ALTER TABLE `Occupa`

  ADD CONSTRAINT `Occupa_ibfk_1` FOREIGN KEY (`stallo`,`area`) REFERENCES `Stallo` (`id_stallo`, `id_area`),

  ADD CONSTRAINT `Occupa_ibfk_2` FOREIGN KEY (`rfid`) REFERENCES `Mezzo` (`rfid`);

ALTER TABLE `Pagamento`

  ADD CONSTRAINT `Pagamento_ibfk_1` FOREIGN KEY (`data_ora`,`id_utente`) REFERENCES `Noleggio`

(`data_ora`, `id_utente`);

ALTER TABLE `Parentela`

  ADD CONSTRAINT `Parentela_ibfk_1` FOREIGN KEY (`dichiarante`) REFERENCES `Cittadino` (`doc_id`),

  ADD CONSTRAINT `Parentela_ibfk_2` FOREIGN KEY (`componente`) REFERENCES `Cittadino` (`doc_id`);

ALTER TABLE `Profilo`

  ADD CONSTRAINT `Profilo_ibfk_1` FOREIGN KEY (`carta_identita`) REFERENCES `Utente` (`cie`);

ALTER TABLE `Stallo`

  ADD CONSTRAINT `Stallo_ibfk_1` FOREIGN KEY (`id_area`) REFERENCES `Area` (`id_area`);

ALTER TABLE `Titolarita`

  ADD CONSTRAINT `Titolarita_ibfk_1` FOREIGN KEY (`id_carta`) REFERENCES `Cartamobilita` (`id_carta`),   ADD CONSTRAINT `Titolarita_ibfk_2` FOREIGN KEY (`cie`) REFERENCES `Utente` (`cie`);

ALTER TABLE `Utente`

  ADD CONSTRAINT `Utente_ibfk_1` FOREIGN KEY (`cie`) REFERENCES `Cittadino` (`doc_id`);

Grafico delle Relazioni

Trigger implementati.

 Trigger relativi a Noleggio.

Incrementa il numero di chilometri totali all’interno della Carta Mobilità quando viene riconsegnata una bici solo se l’utente è un utente registrato

DELIMITER $$ CREATE TRIGGER `CARTAMOBILITA_INCREMENTO_KM` AFTER UPDATE ON `Noleggio` FOR EACH ROW BEGIN DECLARE presente varchar(20); DECLARE IDU varchar(20); SELECT Titolarita.cie into presente FROM Titolarita WHERE Titolarita.cie=new.id_utente; IF presente!='NULL' THEN     SELECT Titolarita.id_carta into IDU from Titolarita WHERE Titolarita.cie=old.id_utente AND Titolarita.status_carta='attiva';                              UPDATE Cartamobilita SET Cartamobilita.totale_km=Cartamobilita.totale_km+new.km WHERE Cartamobilita.id_carta=IDU;             END IF;                 END $$ DELIMITER ;

 

Incrementa il numero di noleggi totali per ogni area una volta inserito un nuovo noleggio

DELIMITER $$ CREATE TRIGGER `AREA_INC` AFTER INSERT ON `Noleggio` FOR EACH ROW UPDATE Area SET Area.totale_noleggi=Area.totale_noleggi+1 WHERE id_area=NEW.area $$ DELIMITER ;

Crea un nuovo record all’interno della tabella Pagamento alla consegna del mezzo presso lo stallo come da UPDATE

DELIMITER $$ CREATE TRIGGER `PAGAMENTO_up_consegna_noleggio` AFTER UPDATE ON `Noleggio` FOR EACH ROW BEGIN DECLARE differenza INT DEFAULT 0; DECLARE presente varchar(20); SET differenza=TIMESTAMPDIFF(HOUR,old.data_ora,new.data_in_ora_in); SELECT Titolarita.cie into presente FROM Titolarita WHERE Titolarita.cie=new.id_utente; IF presente!='NULL' THEN INSERT INTO Pagamento VALUES (old.data_ora,old.id_utente,differenza-(new.km*0.1),"richiesto"); ELSE INSERT INTO Pagamento VALUES (old.data_ora,old.id_utente,differenza,"richiesto"); END IF; END $$ DELIMITER ;

Aggiorna  il totale dei noleggi dell’utente qualora questi fosse un utente registrato dopo l’inserimento di un nuovo noleggio (O4):

DELIMITER $$ CREATE TRIGGER `UTENTE_AGGIORNAMENTO_NOLEGGI` AFTER INSERT ON `Noleggio` FOR EACH ROW BEGIN DECLARE presente varchar(20); DECLARE IDU varchar(20); SELECT Titolarita.cie into presente FROM Titolarita WHERE Titolarita.cie=new.id_utente; IF presente!='NULL' THEN         SELECT Titolarita.id_carta into IDU from Titolarita WHERE Titolarita.cie=new.id_utente AND Titolarita.status_carta='attiva';                                                            UPDATE Cartamobilita SET Cartamobilita.num_noleggi=Cartamobilita.num_noleggi+1 WHERE Cartamobilita.id_carta=IDU;

            END IF;

                END

$$

DELIMITER ;

Libera una bici da uno stallo

DELIMITER $$ CREATE TRIGGER `STALLO_SBLOCCA_BICI` AFTER INSERT ON `Noleggio` FOR EACH ROW DELETE FROM Occupa WHERE rfid=New.id_mezzo $$ DELIMITER ;

inserisce una bici in uno stallo alla consegna

DELIMITER $$ CREATE TRIGGER `STALLO_BLOCCA_BICI` AFTER UPDATE ON `Noleggio` FOR EACH ROW INSERT INTO Occupa VALUES (old.id_mezzo,new.stallo_in,new.area_in) $$ DELIMITER ;

Trigger relativi a Pagamento

Dopo aver pagato se l’utente possiede la Carta Mobilità e se il costo residuo della carta è maggiore o uguale alla tariffa richiesta allora decrementa il valore del credito residuo nella Carta. (O5)

DELIMITER $$ CREATE TRIGGER `PAGAMENTO_EFFETTUATO` AFTER UPDATE ON `Pagamento` FOR EACH ROW BEGIN     DECLARE ICS FLOAT;     DECLARE IDU varchar(20);     DECLARE presente varchar(20); SELECT Titolarita.cie into presente FROM Titolarita WHERE Titolarita.cie=new.id_utente; IF presente!='NULL' THEN  SELECT Titolarita.id_carta into IDU from Titolarita WHERE Titolarita.cie=old.id_utente AND Titolarita.status_carta='attiva';     SELECT Cartamobilita.credito into ICS from Cartamobilita WHERE Cartamobilita.id_carta=IDU; IF (ICS > new.costo)     THEN                              UPDATE Cartamobilita SET Cartamobilita.credito=Cartamobilita.credito-new.costo WHERE Cartamobilita.id_carta=IDU;     END IF;     END IF;             END $$ DELIMITER ;