2013 – Dogana aeroportuale

Risulta difficile descrivere le procedure relative ai controlli nei punti di dogana di un aeroporto

1. Un funzionario svolge un servizio in un punto di dogana o secondo dei turni?
2. Un punto di dogana ha più punti di controllo?
3. Un addetto svolge un servizio in un punto di controllo o secondo dei turni?
4. Un passeggero può avere più bagagli?
5. Un passeggero può avere più merci addosso e/o in un bagaglio?
6. Il controllo di una merce può portare all’imposizione di un dazio e/o al sequestro e/o al fermo del passeggero?
7. …

Dipendenti

1. Un punto di dogana contiene uno o più punti di controllo.
2. Un funzionario svolge i suoi servizi nei punti di dogana.
3. Un addetto svolge i suoi servizi nei punti di controllo.
4. Un servizio è registrato con un Inizio e una Fine.

Merci

1. Una merce appartiene a una categoria e si presenta con una certa quantità.
2. Una categoria di merce ha un eventuale dazio da applicare alle merci corrispondenti.

Passeggeri

1. Un passeggero passa al punto di controllo con certi dati già registrati (aeroporto di arrivo/destinazione, nazionalità, motivo del viaggio, …) che aiutano l’addetto nella valutazione della merce.
2. Le informazioni sul volo (aeroporto di arrivo/destinazione, nazionalità, motivo del viaggio, …) potrebbero essere entità.

Controlli

1. Quando un passeggero attraversa un punto di controllo tutta la merce che possiede viene registrata e controllata da un addetto in servizio
2. Durante un controllo su una merce si registra
   Apertura, Chiusura, Esito, … ed eventualmente DazioDovuto.
3. Nei casi di contestazione
   dazio dovuto, sequestro, fermo
   interviene il funzionario in servizio presso il punto di dogana.

Il diagramma E/R ridisegnato in orizzontale  e con più attributi

Note

1. Alcuni attributi potrebbero essere delle entità: Esito, Arrivo, Destinazione, Nazionalità, Motivo, …
2. Chiusura > Apertura
3. Fine > Inizio
4. DazioDovuto=Quantità*Dazio, con dazio corrispondente alla categoria di appartenenza della merce
5. Esito=NULL in mancanza di contestazione
6. Esito={x, y, z} per dazio dovuto, merce sequestrata, proprietario fermato.

SCHEMA RELAZIONALE

• FUNZIONARIO(IDfunzionario, Cognome, Nome, … )
• PUNTOD(IDpuntod, …)
• FUNZIONARIO_PUNTOD(funzionarioID, puntodID, Inizio, Fine, …)
• ADDETTO(IDaddetto,Cognome, Nome, … )
• PUNTOC(IDpuntoc ,.., puntodID)
• ADDETTO_PUNTOC(addettoID, puntocID, Inizio, Fine, …)
• CATEGORIA(IDcategoria, Nome, Dazio)
• MERCE(IDmerce, Descrizione, Quantità, Apertura, Esito, DazioDovuto, …, Chiusura, puntocID, categoriaID, passeggeroID)
• PASSEGGERO(IDpasseggero, Cognome, Nome, …, Arrivo, Destinazione, Nazionalità, Motivo)

Definizione delle relazioni

CREATE TABLE MERCE
(
   IDmerce      INT AUTO_INCREMENT,
   Descrizione  VARCHAR(100),
   Quantità     INT,
   Apertura     DATETIME,
   Esito        CHAR(15),
   DazioDovuto  DOUBLE,
   Chiusura     DATETIME,
   puntocID     INT,
   categoriaID  INT,
   passeggeroID INT,

   PRIMARY KEY(IDmerce),
   FOREIGN KEY(puntocID)     REFERENCES PUNTOC(IDpuntoc),
   FOREIGN KEY(categoriaID)  REFERENCES CATEGORIA(IDcategoria),
   FOREIGN KEY(passeggeroID) REFERENCES PASSEGGERO(IDpasseggero)
)

QUERY

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

SELECT   PUNTOD.Nome, PUNTOC.Nome, Apertura, PASSEGGERO.*
FROM     PUNTOD INNER JOIN PUNTOC     ON ...
                INNER JOIN MERCE      ON ...
                INNER JOIN PASSEGGERO ON ...
WHERE    DATE(Apertura) = CURRENT_DATE()
ORDER BY PUNTOD.Nome, PUNTOC.Nome, Apertura

2. Visualizzare per ciascun punto di controllo l’ammontare dei dazi doganali registrati

SELECT   puntocID, Sum(DazioDovuto)
FROM     MERCE
GROUP BY puntocID

3. Calcolare e visualizzare quante merci per ogni categoria sono state respinte dall’inizio dell’anno

SELECT   CATEGORIA.nome, Sum(quantità)
FROM     MERCE INNER JOIN CATEGORIA ON MERCE.categoriaID = CATEGORIA.IDcategoria
WHERE    (Esito = Y) AND (YEAR(Fine) = YEAR(CURRENT_DATE())
GROUP BY CATEGORIA.nome

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

SELECT   IDaddetto, Cognome, Nome, Count(*)
FROM     ADDETTO INNER JOIN ADDETTO_PUNTOC ON ...
                 INNER JOIN PUNTOC         ON ...
                 INNER JOIN MERCE          ON ...
WHERE    Esito IS NOT NULL
GROUP BY IDaddetto
ORDER BY Cognome, Nome

5. Calcolare la durata media dei controlli per ogni punto di controllo nell’arco della giornata

SELECT   puntocID, SEC_TO_TIME(AVG((TIME_TO_SEC(TIMEDIFF(Fine,Inizio)))) AS 'Durata media'
FROM     MERCE
WHERE    DATE(Chiusura) = CURRENT_DATE()
GROUP BY puntocID

Osserva

1. Durata: TIMEDIFF(Fine,Inizio)
2. Durata in secondi: TIME_TO_SEC(TIMEDIFF(Fine,Inizio))
3. Durata media in secondi: AVG(TIME_TO_SEC(TIMEDIFF(Fine,Inizio)))
4. Durata media: SEC_TO_TIME(AVG((TIME_TO_SEC(TIMEDIFF(Fine,Inizio))))

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   Nazionalità,PASSEGGERO.*, Chiusura, puntocID
FROM     ...
WHERE    (Esito=Z) AND (YEAR(Chiusura) = YEAR(CURRENT_DATE())
ORDER BY Nazionalità, Cognome, Nome

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

SELECT   FUNZIONARIO.cognome, FUNZIONARIO.nome, ...
         ADDETTO.cognome,     ADDETTO.nome, ...
FROM     FUNZIONARIO INNER JOIN FUNZIONARIO_PUNTOD ON ...
                     INNER JOIN PUNTOD             ON ...
                     INNER JOIN PUNTOC             ON ...
                     INNER JOIN ADDETTO_PUNTOD     ON ...
                     INNER JOIN ADDETTO            ON ...
WHERE    (FUNZIONARIO_PUNTOD.Inizio = ...) AND (ADDETTO_PUNTOC.Inizio = ...) AND ...
ORDER BY FUNZIONARIO.cognome, FUNZIONARIO.nome,
         ADDETTO.cognome,     ADDETTO.nome