Vista Frontale

Vista Laterale

Vista Laterale

• Descrizione e specifiche

Si vuole realizzare il progetto della base di dati di un’agenzia di vigilanza, partendo da un insieme di requisiti. Le fasi da svolgere vanno dall’analisi dei requisiti, (ovvero si chiariscono e si organizzano le specifiche dei requisiti), alle varie fasi dell’analisi (analisi delle prestazioni: si valuta il costo di un’operazione e l‘occupazione di memoria; e analisi delle ridondanze ovvero dei dati che possono essere ricavati da altri) fino all’implementazione delle operazioni previste. Durante il progetto è necessario produrre un insieme di documenti, che costituiscono appunto la documentazione del progetto.

Specifiche sui dati

Si vuole progettare  il sistema informativo di un’agenzia di vigilanza.

Diversi tipi di persone sono coinvolti nell’agenzia: dipendenti, impiegati, agenti, capiufficio, capigruppo. Di questi vogliamo rappresentare alcuni dati anagrafici: nome, cognome, età, indirizzo,  stipendio, codice fiscale (che li identifica).

L’agenzia ha più sedi, distinguibili in caserme e uffici, entrambe caratterizzate da un indirizzo, un telefono, codice e un nome. Ogni impiegato lavora in un ufficio diretto da un capoufficio; gli agenti  diretti dai capigruppo, hanno sede, insieme a questi ultimi, nelle caserme.

Gli agenti prima di essere assegnati insieme ai capigruppo ad alcuni servizi, identificati da un codice servizio, un nome, un indirizzo e un ricavo, devono essere addestrati almeno una volta nelle caserme tramite esercitazioni. Ogni esercitazione ha un numero esercitazione, una data e un’ora.

Ogni servizio ha un capogruppo e  può essere ‘coperto’ oltre che dagli agenti anche da collaboratori esterni, indipendenti dall’agenzia, identificati da un tipo e un numero.

Gli agenti sono forniti di equipaggiamento composto da armi  e veicoli aventi entrambi un codice: possono guidare i veicoli necessari al servizio solo se muniti di abilitazione e ognuno possiede un’arma.

La ditta che fornisce l’equipaggiamento, caratterizzato da un costo, un tipo e un codice, ha un indirizzo, un nome  e un numero di telefono. I veicoli hanno bisogno di manutenzione.

Specifiche sulle operazioni

Per l’agenzia di vigilanza sono previste alcune operazioni, di cui riportiamo una breve descrizione ed il carico previsto.

1. Assunzione di un nuovo dipendente (frequenza: 5 al mese).

2. Aggiunta di un nuovo servizio (frequenza: 1 a settimana).

3. Licenziamento di agente (frequenza: 5 al mese).

4. Spostamento esercitazioni (frequenza: 5 al giorno).

5. Trova gli agenti che lavorano in un determinato turno (frequenza: 1 al giorno).

6.   Trova i costi delle vetture usate per un servizio (frequenza: 2 a settimana).

7.   Cancella servizio (frequenza: 1 a settimana).

8.  Trova gli agenti che possono guidare un  mezzo in un servizio (frequenza: 10 al giorno).

9.  Scrivi l’elenco degli agenti che fanno un’ esercitazione fuori dalla propria caserma (frequenza: 1 a settimana).

10.  Trova i dipendenti che hanno più di un numero di telefono (frequenza:3 al giorno).

11.  Trova gli agenti che hanno usato un’arma in un servizio (frequenza: 3 al giorno).

12. Calcolo entrate (frequenza:1 all’anno).

• Analisi dei requisiti

In questo paragrafo ci occupiamo della fase di analisi e ristrutturazione dei requisiti raccolti, producendo un insieme omogeneo e non ambiguo di specifiche da utilizzare nelle fasi successive della progettazione.

Viene omessa una descrizione più dettagliata di questa attività.

Nella fase di analisi vogliamo individuare i termini più rilevanti,i termini sinonimi e/o omonimi, le correlazioni tra i vari termini e la presenza di eventuali ambiguità. Per fare questo è utile la costruzione di un glossario dei termini nel quale, ad ogni termine rilevante individuato nelle specifiche, associamo una breve descrizione, eventuali omonimi e/o sinonimi ed i termini ad esso collegati logicamente. Una porzione del glossario dei termini per l’applicazione in esame è riportata in figura 1.

A questo punto è utile riorganizzare le specifiche, partizionandole in gruppi di frasi che fanno riferimento ad informazioni omogenee. In questa fase cerchiamo di filtrare le ambiguità presenti ed utilizzare una terminologia più accurata facendo uso del glossario.

Otteniamo al termine di questa fase le specifiche che seguono.

Dati di carattere generale

Si vuole progettare il sistema informativo di un’agenzia di vigilanza rappresentando esclusivamente i dati relativi a:

• Dipendenti dell’ agenzia: impiegati, agenti, capiufficio, capigruppo.

• Sedi del dipartimento:caserme, uffici.

• Turni di servizio.

• Equipaggiamento degli agenti.

Dati sui dipendenti

Per i dipendenti (circa 5200), rappresentiamo alcuni dati anagrafici, quali il nome, il cognome, l’ indirizzo, il numero di telefono, il codice fiscale e lo stipendio (dipendente dalla sua posizione nell’azienda). I dipendenti sono classificati rispetto a diverse tipologie, essendo composti da impiegati, agenti, capiufficio e capigruppo.

Dati sugli impiegati

Per ogni impiegato (circa 200) vogliamo rappresentare esclusivamente i suoi dati anagrafici, lo stipendio, l’ufficio in cui lavora e il suo superiore.

Dati sui capiufficio

Per il capoufficio (circa 2) vogliamo rappresentare oltre ai dati anagrafici anche  il suo stipendio, l’ufficio diretto ed i suoi inferiori.

Dati sugli agenti

Per gli agenti (circa 5000) vogliamo rappresentare i turni di servizio, il suo equipaggiamento, ovvero l’arma  e i veicoli di cui possiede l’abilitazione, e il tipo di addestramento seguito nella rispettiva caserma.

Dati sui Marescialli

Per i Marescialli (circa 1000) si vuole rappresentare il servizio di cui è unico responsabile e gli agenti comandati.

Dati sui servizi

Per i servizi (circa 1000) si vuole rappresentare il nome, il codice servizio che lo identifica, il costo, l’indirizzo, il ricavo, i veicoli necessari al servizio, i collaboratori esterni.

Dati sui veicoli

Per i veicoli (circa 300) si vuole rappresentare il costo per il suo utilizzo, il codice veicolo, il fornitore. Ogni veicolo può aver bisogno di riparazioni presso un’officina di cui si conosce l’indirizzo; è necessario conoscere anche il costo delle riparazioni.

• Progettazione concettuale

Riflettendo sull’analisi delle specifiche abbiamo deciso di utilizzare la tecnica mista per la stesura del modello E-R. Analizzati i punti di maggior interesse abbiamo realizzato tre piccoli schemi iniziali che rappresentassero le tre parti principali: i dipendenti della nostra agenzia, i servizi di vigilanza e gli equipaggiamenti necessari.

In questo primo schema viene mostrata la relazione che lega ogni dipendente alla sua sede.Si è deciso di suddividere i dipendenti in impiegati e agenti e le sedi in caserme e uffici. Per l’entità impiegati stata creata un’altra entità: capoufficio, sottoinsieme degli impiegati, allo scopo di mostrare la relazione tra capiufficio ed il proprio ufficio.

In questo secondo schema vengono messe in evidenza le assegnazioni degli agenti ai rispettivi servizi. Abbiamo deciso di utilizzare relazioni sia per le assegnazioni agenti-servizi che per quelle collaborati-servizi. Per l’entità agenti si è deciso di suddividerla in un'altra entità: maresciallo, sottoinsiemi degli agenti, per mostrare la relazione tra i marescialli e i servizi.

Infine nell’ultimo schema abbiamo rappresentato le necessità dei servizi riguardo agli equipaggiamenti; è nata così la decisione di usare una gerarchia per utilizzare in modo più efficiente le relazioni sulle manutenzioni e sui fornitori, poiché per i servizi sono necessari esclusivamente i veicoli. Ogni agente è infine legato agli equipaggiamenti tramite una relazione abilitazione per i veicoli e tramite un possesso per le armi.

Riportiamo quindi lo schema intermedio che viene creato dall’unione dei precedenti tre. A quest’unione vengono inoltre aggiunte alcune realtà d’interesse tralasciate nei precedenti tre schemi quali le esercitazioni degli agenti e le manutenzioni dei veicoli da parte di officine

Vengono rappresentati alcuni vincoli richiesti dalle specifiche mentre per quelli non visualizzabili viene dato un elenco qui sotto.

Vincoli non espressi dallo schema

• Esercitazioni: un agente non può effettuare un esercitazione se sta effettuando un servizio ne eseguire più esercitazioni nello stesso giorno.

• Capogruppo: se un servizio richiede un solo agente esso deve essere necessariamente un maresciallo.

• Stipendi: gli stipendi dei dipendenti di grado più alto (capoufficio e marescialli) devono essere superiori a quello dei dipendenti di grado più basso (impiegati ed agenti).

Per la stesura dello schema finale non vengono apportati cambiamenti allo schema intermedio ma vengono aggiunti gli attributi e denominate le relazioni.

•    Analisi delle funzioni

Per la progettazioni delle funzioni e necessario valutare il carico della applicazione sia per quanto riguarda il volume dei dati che ci aspettiamo sia la frequenza con cui essi vengono usati. Il primo concetto viene mostrato nella tabella dei volumi che esplicita per ciascun oggetto dello schema E-R (entità, relazioni) il numero atteso di istanze che quel concetto avrà in un funzionamento di regime dell’applicazione. La tavola delle frequenze mostra invece la frequenza con cui le operazioni vengano eseguite.

Avendo a disposizione queste informazioni, è possibile fare un stima del costo di un’operazione sulla base di dati contando il numero di accessi alle occorrenze di entità e relazioni necessario per eseguire le operazioni; decidiamo di fare la stima del costo solo di alcune delle operazioni previste: ad esempio delle operazioni 5 e 11.

• Facendo riferimento allo schema della operazione 5 dobbiamo innanzitutto accedere ad 1 occorrenza dell’entità agenti, per poi accedere ad 0.76 occorrenze dell’associazione turno (gli agenti sono infatti 5000 e i turni circa 4000).

• Per l’operazione 11 invece dobbiamo accedere ad 1 occorrenza dell’entità agenti per poi accedere a 1 occorrenza dell’entità possesso e da qui ad 1 dell’ entità arma. Successivamente per conoscere in quali turni lavora dobbiamo accedere a 0.76 occorrenze dell’associazione turni e attraverso queste a 0.76 occorrenze dell’entità turni.

• Progettazione Logica indipendente dal modello

 Effettuiamo ora la progettazione logica della base di dati. In questo paragrafo ci occupiamo della fase alta della progettazione logica, ovvero quella indipendente dal modello dei dati scelto, suddivisa in analisi delle ridondanze, eliminazione delle gerarchie, partizionamento-riaccorpamento di entità-relazioni e scelta degli identificatori principali. Questa fase conduce alla stesura di un progetto E-R ristrutturato dalle realtà di interesse. Nel prossimo paragrafo ci occuperemo della traduzione di questo schema E-R in uno schema relazionale.

• Analisi delle ridondanze

In questa fase vanno individuate eventuali ridondanze nello schema concettuale. Le ridondanze possono essere costituite da relazioni (in presenza di cicli di relazioni) oppure da attributi (chiamati anche dati derivati). Utilizzando le informazioni sul carico, dobbiamo poi decidere se mantenere (introdurre in alcuni casi) oppure eliminare queste ridondanze. 

Dopo un’attenta analisi abbiamo appurato che nel nostro schema non sono presenti ridondanze anche perché nella progettazione concettuale avevamo da subito eliminato, ogni possibile forma di ridondanza, passiamo dunque direttamente alla eliminazione delle gerarchie.

• Eliminazione delle Gerarchie

Nello schema sono presenti cinque gerarchie, in particolare tre di esse fanno riferimento all’entità dipendenti, una all’entità sedi e l’ultima all’entità equipaggiamento. 

• Per quanto riguarda i dipendenti le operazioni non fanno grosse distinzioni tre agenti e impiegati, questa generalizzazione può essere eliminata incorporando l’entità figlie agenti ed impiegati nell’entità padre dipendenti, alla quale va aggiunto l’attributo tipo, che assumerà il valore A nel caso in cui si tratta di un agente, ed il valore I se viceversa è un impiegato. Per i due sottoinsiemi di agenti ed impiegati: capo ufficio e maresciallo, possiamo fare lo stesso discorso, e l’attributo tipo oltre ad assumere i valori precedentemente descritti può assumere anche M per i marescialli e C per i capi ufficio; 

• Per quanto riguarda la gerarchia tra l’entità padre sede e  le entità figlie caserma e uffici, si può fare un discorso simile a quello fatto per  dipendenti ed impiegati e agenti, si possono quindi anche qui fondere le entità figlie nell’entità padre creando anche in questo caso un attributo tipo che assumendo il valore C se si tratta  di una caserma e U se si tratta di un  ufficio.

• Anche nell’ultima gerarchia non fanno molte distinzioni le operazioni tra le occorrenze e gli attributi di equipaggiamento, Arma e Veicoli; quindi anche in quest’ultimo caso abbiamo ritenuto opportuno eliminare le entità figlie e aggiungere a quella padre un attributo tipo con il valore A se si tratta di un Arma e V di un Veicolo. Anche in quest’ultimi due casi bisogna fare attenzione alla cardinalità minima del nuovo attributo.

lo schema viene cosi trasformato:

• Partizionamento/riaccorpamento di entità/relazioni

Per quanto riguarda l’attributo multivalore “telefono”nell’entità dipendenti, sedi, collaboratori, fornitori e manutenzione, abbiamo deciso di creare una nuova entità telefono ed eliminare l’attributo da tutte le entità, creando le associazioni Recapito dipendenti,Recapito sede, Recapito collaboratori, Recapito fornitori, Recapito manutenzione, che unisce le varie entità.

La stessa osservazione può essere fatta per l’attributo”indirizzo”delle stesse entità, abbiamo così deciso di creare una nuova entità indirizzo ed eliminare l’attributo dalle varie entità, creando le associazioni Ubicazione dipendenti, Ubicazione sede, Ubicazione collaboratori, Ubicazione fornitori, Ubicazione che unisce le varie entità.

Abbiamo infine ritenuto opportuno unire le due entità telefono e indirizzo cosi da ottenere una nuova entità domicilio con gli attributi telefono, indirizzo.

• Scelta degli identificatori principali

Non ci sono entità presenti nello schema E-R che abbiano o  identificatori composti o identificatori esterni, le chiavi scelte sono quindi già delle chiavi primarie.

Con la modifica fatta nel paragrafo precedente si viene a creare un problema per la chiave dell’entità domicilio, abbiamo cosi deciso di aggiungere un nuovo attributo a tale entità: codice domicilio, che sarà poi la chiave primaria della relazione.

• Progettazione logica nel modello concettuale

• Traduzione delle Entità

La prima fase di questa attività individua un insieme di nomi di R-relazioni (le R-relazioni sono le relazioni nel modello relazionale,non da confondere con quelle del modello entità-relazione) ottenuti dalla traduzione delle entità presenti nello schema E-R ristrutturato.Questo passo individua un insieme di attributi che costituiscono lo schema iniziale di tali R-relazioni.

SEDI (Codice, nome, tipo)

DIPENDENTI (Cod fiscale, nome, cognome, stipendio)

DOMICILIO (Cod Domicilio, telefono, indirizzo)

ESERCITAZIONE (Num esercitazione, data, ora)

FORNITORI (Ditta)

EQUIPAGGIAMENTO (Codice, tipo, costo)

SERVIZI (Cod servizio, nome, ricavo)

COLLABORATORI (Tipo collaboratore)

MANUTENZIONE (Nome)

Nel nostro caso non ci sono entità identificate esternamente, nella parte successiva dobbiamo quindi occuparci della traduzione di tutte le relazioni.

• Traduzione delle relazioni

Consideriamo innanzi tutto le relazioni di tipo uno a uno, poi quelle di tipo uno a molti ed infine quelle di tipo molti a molti. Si può subito notare che nello schema ristrutturato sono presenti solo due relazioni di tipo uno ad uno. Possiamo quindi tradurre la prima relazione, dirigenza, che collega le entità dipendenti con le entità sedi introducendo un attributo dirigente (con vincolo di integrità con il codice fiscale). Lo stesso discorso può essere fatto con la relazione responsabilità, che collega dipendenti con servizi, introduciamo quindi anche in quest’ultima entità un attributo maresciallo mantenendo sempre un vincolo di integrità. Anche per la relazione possesso, che collega dipendenti con equipaggiamento applichiamo la stessa regola e aggiungiamo l’attributo arma all’entita dipendenti.Possiamo quindi passare ad esaminare direttamente le relazioni di tipo uno a molti. Possiamo quindi tradurre la relazione agenda5, che collega sedi a domicilio, introducendo un attributo sedi in domicilio.Ugualmente possiamo fare per tutte le altre relazioni che collegano l’entità domicilio: collaboratori, manutenzione, fornitori e dipendenti, aggiungendo a questa i quattro rispettivi attributi. Per quanto riguarda la relazione esigenza veicolo che collega l’entità servizi con quella equipaggiamento possiamo anche qui eliminarla e aggiungere ad equipaggiamento l’attributo servizio. Per la relazione ubicazione,che collega le due entità sedi e esercitazione, vale la stessa cosa, sostituiamo quindi la relazione con l’aggiunta dell’attributo sedi ad esercitazione. Applichiamo lo stesso tipo di ragionamento anche per le relazioni appartenenza e forniture , che collegano rispettivamente sedi con dipendenti e fornitori con equipaggiamento; aggiungiamo quindi gli attributi sede in dipendenti e fornitore in equipaggiamento. Dobbiamo tradurre le restanti relazioni: riparazioni, abilitazioni, turno e collaborazione che sono di tipo molti a molti, con R-relazioni. Otteniamo quindi i seguenti schemi di R-Relazioni.

RIPARAZIONI (equipaggiamento, manutenzione, costo, data)

ABILITAZIONI (equipaggiamento, dipendenti)

TURNO (dipendenti, servizi, ora,data)

COLLABORAZIONE (servizi, collaboratori, num collaboratori)

• Schema logico

SEDI (Codice, nome, tipo, dirigente)

DIPENDENTI (Cod fiscale, nome, cognome, stipendio, sedi,arma)

DOMICILIO (Cod Domicilio, telefono, indirizzo, sedi, fornitori, manutenzione, dipendenti)

ESERCITAZIONE (Num esercitazione, data, ora, sedi)

FORNITORI (Ditta)

EQUIPAGGIAMENTO (Codice, tipo, costo, servizio, fornitore)

SERVIZI (Cod servizio, nome, ricavo, maresciallo)

COLLABORATORI (Tipo collaboratore)

MANUTENZIONE (Nome)

RIPARAZIONI (equipaggiamento, manutenzione, costo, data)

ABILITAZIONI (equipaggiamento, dipendenti)

TURNO (dipendenti, servizi, ora,data)

ADDESTRAMENTO(dipendenti,esercitazione)

COLLABORAZIONE (servizi, collaboratori, num collaboratori)

• Implementazioni di alcune operazioni

In questo paragrafo viene proposta l’implementazione di alcune delle operazioni che sono definite per l’agenzia di vigilanza.

Dopo aver creato la base di dati contente tutte le tabelle viste nello schema logico, effettuiamo le operazioni previste, riportando poi i risultati ottenuti.

Operazione 1: 

INSERT INTO DIPENDENTI ( CodFiscale , nome , cognome , stipendio , sedi , arma ) VALUES  ( 'Cod fiscale' ,' nome' ,' cognome' , 'stipendio' , 'sedi' , 'arma' ) ;

Operazione 2:

INSERT INTO SERVIZI  (CodServizio, nome, ricavo, maresciallo) VALUES  ( 'Cod servizio', 'nome',' stipendio' , 'maresciallo' )

Operazione 3;

DELETE FROM DIPENDENTI WHERE codFiscale= 'A14' ;

Operazione 4:

UPDATE Esercitazione SET Data ='18/11/2001' WHERE numesercitazione ='04';

Operazione 5:

SELECT Dip.nome, Dip.cognome, Dip.CodFiscale FROM Dipendenti as Dip INNER JOIN Turno ON  Dip.CodFiscale = Turno.agente WHERE turno.data="16/11/2001";

Operazione 6:

SELECT Eq.costo, Eq.codice, Eq .tipo FROM Servizi as sr INNER JOIN Equipaggiamento as Eq ON sr .codServizio = Eq .servizio WHERE (sr.codservizio="S4");

Operazione 7:

DELETE FROM SERVIZI WHERE codServizio= 'S14' ;

Operazione 8:

SELECT Dip.CodFiscale, Dip.nome, Dip .cognome FROM Servizi as sr INNER JOIN (Equipaggiamento  as Eq INNER JOIN (Dipendenti as Dip INNER JOIN Abilitazioni as ab ON Dip.CodFiscale = ab.dipendenti) ON Eq.codice = ab.equipaggiamento) ON sr.codServizio = Eq.servizio WHERE (((sr.codServizio)="S4"));

Operazione 9:

SELECT dip.CodFiscale, dip.nome, dip.cognome FROM ((Dipendenti as dip INNER JOIN Addestramento as add ON dip.CodFiscale=add.agenti) INNER JOIN Esercitazione as es ON add.esercitazione=es.numEsercitazione) INNER JOIN Sedi ON dip.sedi=Sedi.Codice WHERE sedi.codice<>esercitazione.sedi;

Operazione 10:

SELECT dip.codfiscale, dip.nome,dip.cognome FROM Dipendenti as dip INNER JOIN Domicilio ON dip.CodFiscale =  Domicilio.dipendenti group by dip.codfiscale,dip.nome,dip.cognome having count(dip.codfiscale)>1;

Operazione 11:

SELECT Eq.codice, Eq.tipo FROM (Servizi as ser INNER JOIN (Dipendenti as dip INNER JOIN Turno ON dip.CodFiscale = Turno.agente) ON ser.codServizio = Turno.servizio) INNER JOIN Equipaggiamento as Eq ON dip.arma = Eq.codice WHERE ser.codservizio="S1";

Operazione 12: 

SELECT Sum(Servizi.ricavo) AS SommaDiricavo

• Dati di test

In questa sezione devono essere mostrate le schermate delle tabelle popolate con dati a testare la correttezza delle operazioni implementat

Mostriamo infine anche i risultati di alcune delle operazioni