mini Appunti

Una Società Aeroportuale commissiona lo sviluppo di un software per la gestione dei controlli passeggeri e merci nei vari punti di dogana presenti in un Aeroporto Internazionale. Si richiede la memorizzazione, per ogni controllo effettuato, dei dati relativi a:

1. passeggero
   cognome, nome, nazionalità, N. passaporto o carta d'identità, aeroporto di provenienza/destinazione, motivo del viaggio;
2. merce trasportata
   categoria di appartenenza (generi alimentari, strumentazione elettronica, farmaci, abbigliamento, ...), descrizione, quantità dichiarata;
3. controllo effettuato
   punto di controllo, identificativo dell'addetto alcontrollo, data e ora inizio controllo, ... , esito (nessuna segnalazione, merce respinta, fermo del passeggero, ...), dazio doganale (non dovuto / importo del dazio, se dovuto), note.

La procedura prevede che il funzionario incaricato provveda ad autenticarsi sul sistema e inserisca in tempo reale i dati relativi ad ogni nuovo controllo. Il sistema dovrà registrare i dati provenienti dai vari punti di controllo e fornire le necessarie funzioni per la segnalazione ai funzionari incaricati, delle situazioni da gestire:

• ritiro della merce sequestrata da parte del personale addetto al controllo;
• apertura della pratica di fermo del passeggero con segnalazione all’organo preposto (Polizia Aeroportuale);
• analisi delle note di controllo e gestione delle eventuali contestazioni;
• chiusura di tutti i rapporti di controllo aperti dagli addetti secondo le loro competenze.

Il candidato realizzi:

• un'analisi della realtà di riferimento, completa delle ipotesi aggiuntive necessarie, a suo motivato giudizio, per la realizzazione di uno schema adeguato dell'architettura proposta;
• uno schema concettuale ed uno schema logico del database;
• la definizione delle relazioni
• e le seguenti interrogazioni espresse in linguaggio SQL:
  1. visualizzare i dati di tutti i passeggeri che sono stati controllati in ciascuno dei punti di dogana nell’arco della giornata;
  2. visualizzare per ciascun punto di controllo l’ammontare dei dazi doganali registrati;
  3. calcolare e visualizzare quante merci per ogni categoria sono state respinte dall’inizio dell’anno;
  4. calcolare e visualizzare quante contestazioni sono state registrate da ciascun addetto;
  5. calcolare la durata media dei controlli per ogni punto di controllo nell’arco della giornata;
  6. visualizzare l’elenco, in ordine alfabetico, raggruppato per nazionalità, dei passeggeri in stato di fermo, registrati dall’inizio dell’anno in tutti i punti di controllo;
  7. visualizzare gli addetti in servizio nella giornata, suddivisi per nome del funzionario incaricato.
• la codifica in un linguaggio di programmazione a scelta di un segmento significativo del progetto realizzato.
  
  su

Si userà il DBMS MYSQL e il linguaggio php per l'implementazione dell'interfaccia grafica (pagina web) e delle interrogazioni al database. Si ipotizza quindi di poter testare il funzionamento del software in un ambiente preinstallato in locale che comprende una piattaforma integrata di sviluppo web (come ad esempio EasyPhp) contenente:

• il server web Apache
• il server di database MySQL
• un interprete di script php
• e l'interfaccia grafica phpMyAdmin per la creazione delle tabelle e il test delle query in SQL.

Ipotesi aggiuntive:

1. si ipotizza che per le merci ci sia la necessità di avere una "bolla di accompagnamento" allo scopo di registrare (come per i passeggeri), l'aeroporto di provenienza/destinazione e delle note
2. si ipotizza per semplicità che la figura del funzionario coincida con quella dell'addetto.

Saranno usati gli stessi strumenti per l'installazione del software dopo la fase di test negli host del cliente. Si prevede in particolare l'uso di un computer server (contenente il server web e il motore MySQL e php) e poi l'uso di un computer client in rete col server per ogni punto di controllo contenente l'interfaccia grafica che interagisce col database situato sul server remoto.

su

apri nella dimensione originale

Note:

1. per viaggio si intende in realtà
   • biglietto aereo se riguarda un passeggero che parte da un aeroporto, arriva in un'altro e viaggia per un certo motivo (lavoro, turismo ecc.),
   • bolla di accompagnamento se riguarda una merce che anch'essa parte da un aeroporto e arriva in un altro; il campo motivo del viaggio sarà usato in questo caso per delle note sulla merce in questione.
2. l'associazione tra viaggio (bolla d'accompagnamento) e merce è molti a uno (più bolle per la stessa merce), perché si suppone che la merce potrebbe, per qualche motivo, essere respinta e tornare indietro.

Quindi per intenderci ogni occorrenza dell'entità viaggio ha come caratteristiche: un passeggero o una merce, un aeroporto di partenza, un aeroporto di arrivo, un motivo del viaggio (o note sulla merce).

su

La progettazione logica sarà fatta elencando le tabelle e le relazioni tra le tabelle

tabelle:

aeroporti(idaeroporto, nome, idcomune) comuni(idcomune, comune, idnazione) nazioni(idnazione, nazione) passeggeri(idpasseggero, cognome, nome, idnazione, idtipodoc, numerodocumento) tipodoc(idtipodoc, tipodoc) viaggio(idviaggio, idpasseggeromerce1, idaeroportoprovenienza, idaeroportodestinazione, motivoviaggio) merce(idmerce, nome, idcategoria, descrizione, qta) categorie(idcategoria, categoria) funzionari(idfunzionario, nome, cognome, username, password) controlli(idcontrollo, idpuntocontollo, idfunzionario, idcontrollato2, dataeorainizio, dataeorafine, idesito, dazio3, note) punticontrollo(idpuntocontrollo, nome, descrizione) esiticontrolli(idesito, esito) pratichedifermo(idpratica, idpasseggero, data, idfunzionario, descrizione) contestazioni(idcontestazione, testo, idcontrollo, datidicontatto)

Note:

^ 1: per idpasseggeromerce si intende: l'id del passeggero se si tratta di un biglietto aerero, l'id della merce se si tratta di una bolla di accompagnamento (vedi ipotesi aggiuntive e note alle progettazione concettuale)
^ 2: per idcontrollato si intende: l'id del passeggero se viene controllato un passeggero, l'id della merce se viene controllata la merce;
^ 3: il valore del campo dazio vale zero se il dazio non è dovuto, un valore diverso da zero in caso contrario.

ATTENZIONE: anche se le scelte descritte nelle note 1 e 2 sono in contraddizione con la progettazione concettuale, che prevede due associazioni (e quindi due chiavi esterne) per ogni tabella (viaggio e controlli), semplifica l'implementazione delle query ed evita il problema dei valori nulli che si creerebbe in entrambe le chiavi.

---

relazioni:^*

Tabella 1	Tabella 2	Relazione	tramite
viaggio	passeggeri	M-1	idpasseggeromerce
viaggio	merce	M-1	idpasseggeromerce
viaggio	aeroporto	M-1	idaeroportoprovenienza
viaggio	aeroporto	M-1	idaeroportodestinazione
passeggeri	nazione	M-1	idnazione
passeggeri	tipodoc	M-1	idtipodoc
controlli	passeggeri	M-1	idcontrollato
controlli	merce	M-1	idcontrollato
controlli	funzionari	M-1	idfunzionario
controlli	punticontrollo	M-1	idpuntocontrollo
controlli	esiticontrolli	M-1	idesito
pratichedifermo	passeggeri	1-1	idpasseggero
pratichedifermo	funzionari	M-1	idfunzionario
contestazioni	controlli	1-1	idcontrollo
merce	categorie	M-1	idcategoria
aeroporti	comuni	M-1	idcomune
comuni	nazioni	M-1	idnazione

^ * NB: le relazioni tra le tabelle, sono descritte in maniera non grafica poichè la progettazione concettuale descrive già ampiamente le associazioni tra le entità; ciononostante è opportuno descriverle indicando, per ogni relazione, la maniera nella quale sono collegate, e cioè: o tramite una chiave esterna (nel caso di relazioni uno a uno e uno a molti), oppure tramite un'altra tabella (nel caso di relazione molti a molti). Nella tabella quindi il campo tramite assume proprio: il nome del campo chiave esterna (relazioni 1-1, m-1) o il nome della tabella che fa da "ponte" tra le due. Si intende anche specificare che, la chiave esterna che fa da collegamento (tramite) tra le due tabelle, abbia lo stesso nome della chiave primaria alla quale si collega.

su

La definizione delle relazioni è fatta solo per tre delle tabelle esistenti. Non si ritiene necessario in questa sede definire tutte le relazioni (tabelle) visto che si è scelto di usare l'interfaccia phpMyAdmin che permette di creare le tabelle in modalità grafica. Si fornisce comunque il codice SQL per la creazione della tabella passeggeri e delle due tabelle ad essa collegate tramite le chiavi esterne idnazione e idtipodoc in maniera da poter usare queste due come esempio per la creazione delle altre tabelle presenti nello schema logico.

create table nazioni ( idnazione int unsigned not null auto_increment, nazione varchar(50), primary key (idnazione) );

create table tipodoc ( idtipodoc int unsigned not null auto_increment, tipodoc varchar(20), primary key (idtipodoc) );

create table passeggeri ( idpasseggero int unsigned not null auto_increment, cognome varchar(30), nome varchar(30), idnazione int unsigned, idtipodoc int unsigned, numerodoc varchar(15), unique(numerodoc), primary key (idpasseggero), foreign key (idnazione) references nazioni, foreign key (idtipodoc) references tipodoc );

su

1. visualizzare i dati di tutti i passeggeri che sono stati controllati in ciascuno dei punti di dogana nell'arco della giornata;

   SELECT cognome, nome, nazione, tipodoc, numerodocumento FROM passeggeri, controlli, nazioni, tipodoc WHERE ( DATE_FORMAT(dataeorainizio, '%Y-%m-%d') = curdate() ) and ( (passeggeri.idnazione = nazioni.idnazione) and (passeggeri.idtipodoc = tipodoc.idtipodoc) ) and (controlli.idcontrollato = passeggeri.idpasseggeromerce)

   NB:
   si ipotizza che la data sia memorizzata nel formato: yyyy-mm-dd (formato usato in genere in MYSQL che è il DBMS scelto).
   La funzione DATE_FORMAT serve per estrapolare solo la data nello stesso formato in cui è memorizzata.

   mostra versione SQL SERVER
   

   Se invece si volesse usare SQL SERVER, è necessaria la funzione getdate(); questa funzione fornisce la data e l'ora corrente nel seguente formato: 2008-11-11 12:45:34.243; quindi la query diventerebbe:

   SELECT cognome, nome, nazione, tipodoc, numerodocumento FROM passeggeri WHERE ( ( convert(varchar(10), dataeorainizio, 103 ) = convert(varchar(10),getdate(), 103 ) ) and ( (passeggeri.idnazione = nazioni.idnazione) and (passeggeri.idtipodoc = tipodoc.idtipodoc) )

   la funzione convert( varchar(10),getdate(), 105 ) converte data e ora corrente (ottenuti tramite getdate() ) in solo data; infatti varchar(10) prende solo i primi 10 caratteri e cioè quelli contenenti la data nel formato 105 (dd-mm-yy), per maggiori informazioni); convert(varchar(10),dataeorainizio, 105 ) fa la stessa cosa con il campo dataeorainizio della tabella.
   
   

   
   
2. visualizzare per ciascun punto di controllo l'ammontare dei dazi doganali registrati;

   SELECT idpuntocontrollo, nome as 'Punto di controllo', sum(dazio) FROM controlli, punticontrollo WHERE (dazio>0 and (controlli.idpuntocontrollo = punticontrollo.idpuntocontrollo ) GROUP BY idpuntocontrollo

3. calcolare e visualizzare quante merci per ogni categoria sono state respinte dall'inizio dell'anno;

   SELECT categoria, count(*) as 'merci respinte' FROM merce, categoria, controlli, esiticontrolli WHERE (esito='respinto') and ( ( merce.idcategoria = categorie.idcategoria ) and (esiticontrolli.idesito = controlli.idesito) and ( controlli.idcontrollato = merci.idmerce ) and ( DATE_FORMAT(dataeorainizio, '%Y-%m-%d') between '2013-01-01' and curdate() ) ) GROUP BY idcategoria

   Per rendere la query più generale si potrebbe usare al posto di una data del tipo 2013-01-01, una data che contenga l'anno corrente. Per fare questo si può usare: year(now()) oppure year(curdate()).
   Quindi la query diventerebbe:

   ...between concat(year(now()),'-01-01') and curdate()
   dove ovviamente la funzione concat serve a concatenare anno, mese e giorno.

4. calcolare e visualizzare quante contestazioni sono state registrate da ciascun addetto;

   SELECT nome, cognome, count(*) as contestazioni FROM funzionari,contestazioni, controlli WHERE(funzionari.idfunzionario=controlli.idfunzionario) and ( contestazioni.idcontrollo = controlli.idcontrollo) GROUP BY idfunzionario

5. calcolare la durata media dei controlli per ogni punto di controllo nell'arco della giornata;

   SELECT idpuntocontrollo, nome, avg( TIMESTAMPDIFF(MINUTE, dataeorainizio, dataeorafine) ) as 'tempo medio di controllo in minuti' FROM controlli, punticontrollo WHERE ( dataeorainizio between '2013-12-17 00:00:01' and '2013-12-17 23:59:59' ) and ( dataeorafine between '2013-12-17 00:00:01' and '2013-12-17 23:59:59' ) and ( controlli.idpunticontrollo = punticontrollo.idpuntocontrollo ) GROUP BY idpuntocontrollo

   la funzione TIMESTAMPDIFF restituisce i minuti (MINUTE) di differenza tra inizio e fine

6. visualizzare l'elenco, in ordine alfabetico, raggruppato per nazionalità, dei passeggeri in stato di fermo, registrati dall'inizio dell'anno in tutti i punti di controllo;

   SELECT nome, cognome, nazione FROM passeggeri, controlli, esiticontrolli, nazioni WHERE (esito = 'fermo') and ( controlli.idcontrollato = passeggeri.idpasseggeromerce ) and ( controlli.idesito = esiticontrolli.idesito ) and (nazioni.idnazione=passeggeri.idnazione) and ( DATE_FORMAT(dataeorainizio, '%Y-%m-%d') between concat( year(now() ),'-01-01') and curdate() ) GROUP BY nazione ORDER BY cognome,nome

7. visualizzare gli addetti in servizio nella giornata, suddivisi per nome del funzionario incaricato

   SELECT distinct cognome, nome FROM funzionari, controlli WHERE ( controlli.idfunzionario = funzionari.idfunzionario ) and ( controlli.dataeorainizio = date(now() ) ) ORDER BY cognome, nome

   ipotizzo che questa query sia fatta per sapere chi erano i funzionari in servizio nella giornata odierna (per questo: date(now()) e che il funzionario abbia già effettuato almeno un controllo in questa giornata. In mancanza di questa ipotesi si dovrebbero prevedere le tabelle necessarie a memorizzare il calendario dei servizi dei vari funzionari cosa che porterebbe a complicare ulteriormente la progettazione e l'implementazione del database.

NB: le query qui presenti non sono state provate, quindi sono possibili errori od omissioni.
su

in lavorazione (ma forse non la farò mai ;) :D almenochè qualcuno non me la chieda espressamente....nel frattempo potresti essere interessato a guardare la codifica php della simulazione di esame di Stato 2014/2015 proposta dal ministero e che potete trovare qui).
su