Stefano Tortora

1       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.

Analisi dei requisiti.

Lo schema concettuale, tramite il modello E-R, presentato a diversi gradi di raffinamento.

Una descrizione delle operazioni previste e le tavole di carico.

Lo schema ottenuto per ristrutturazione dalla prima fase della progettazione logica. Lo schema logico finale.

Un listato delle interrogazioni e delle istruzioni (aggiornamenti, inserimenti, cancellazioni) SQL relative alle operazioni previste.

Contenuto di test della base di dati.

Nei prossimi paragrafi sono descritte le specifiche ottenute mediante intervista ad alcuni responsabili della settrice sicurezza.

Nelle sezioni successive sono svolte le varie attività del progetto, talvolta interamente, altre volte fornendo solo una parte della documentazione richiesta.

 

 

1.1    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.

 

1.2    Specifiche sulle operazioni

 

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

 

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

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

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

04     Spostamento esercitazioni (frequenza: 5 al giorno).

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

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

07     Cancella servizio (frequenza: 1 a settimana).

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

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

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

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

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

 

2       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

                 

 

Figura1: Porzione del glossario dei termini

 

 

Dati sui dipendenti

 

 

Dati sugli impiegati

 

 

Dati sui capiufficio

 

 

Dati sugli agenti

 

 

Dati sui Marescialli

 

 

Dati sui servizi

 

 

Dati sui veicoli

 

 

 

3       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.

 

Tabella delle entità

 

Entità	Descrizione partecipanti	Attributi	Identificatore
Dipendenti	Dati relativi a coloro che lavorano nell’agenzia	Cod fiscale, nome, cognome, sesso, indirizzo, telefono, stipendio	Cod fiscale
Impiegati	Sottoinsieme di dipendenti	Vedi dipendenti	Vedi dipendenti
Agenti	Sottoinsieme di dipendenti	Vedi dipendenti	Vedi dipendenti
Capoufficio	Sottoinsieme di impiegati	Vedi impiegati	Vedi Impiegati
Maresciallo	Sottoinsieme di agenti	Vedi agenti	Vedi agenti
Sedi	Dati relativi agli edifici usati dall’agenzia	Codice, nome, indirizzo, telefono	Codice
Ufficio	Sottoinsieme di sedi	Vedi sedi	Vedi sedi
Caserma	Sottoinsieme di sedi	Vedi sedi	Vedi sedi
Servizi	Dati relative ai luoghi (edifici o zone) da sorvegliare	Cod servizio, nome, indirizzo, ricavo	Cod servizio
Collaboratori	Dati relativi ai collaboratori esterni (altre agenzie o enti statali)	Tipo collaboratore, telefono	Tipo collaboratore
Equipaggiamento	Dati relativi agli oggetti che vengono dati agli agenti per lo svolgimento dei servizi	Codice, tipo, costo	Codice
Arma	Sottoinsieme di equipaggiamento	Codice arma, vedi equipaggiamento	Codice arma
Veicolo	Targa, vedi equipaggiamento	Sottoinsieme di equipaggiamento	Codice arma
Esercitazione	Dati relativi alle prove di tiro che gli agenti devono compiere	Num esercitazione, data, ora	Num esercitazione
Manutenzione	Dati relativi alle officine specializzate per la riparazione dei veicolo	Nome Officina, indirizzo officina, telefono	Nome officina
Fornitori	Dati relativi alle ditte che forniscono gli equipaggiamenti	Ditta, indirizzo, telefono	Ditta

Tabella relazioni

 

Relazione	Entità partecipanti	Descrizione	Attributi
Appartenenza	Sedi, dipendenti	Assegna ogni dipendente ad una sede
Ubicazione	Caserma, esercitazione	Ogni poligono di tiro si trova in una caserma
Addestramento	Esercitazione, agenti	Assegna un agente ad una esercitazione
Dirigenza	Capoufficio, ufficio	Ogni capoufficio dirige un ufficio
Turno	Agenti, servizi	Invia gli agenti ai luoghi da sorvegliare	Data, ora
Collaborazione	Collaboratori, servizi	Tiene conto dei possibili collaboratori	Num collaboratori
Responsabilità	Maresciallo, servizi	Mostra il responsabile di ogni servizio
Esigenza veicolo	Veicolo, servizi	Mostra le richieste dei servizi
Possesso	Agente, arma	Ogni agente ha un’arma
Abilitazione	Agente, veicolo	Se un agente può guidare un veicolo
Riparazioni	Veicolo, manutenzione	Elenco delle riparazioni fatte ai veicolo	Data, costo
Forniture	Fornitori, equipaggiamento	Elenco degli acquisti fatti

4       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.

 

Porzione dei dati                                                                                                Operazioni

Num operazione	Descrizione
Operazione 1	Assunzione di un dipendente
Operazione 2	Aggiunta nuovo servizio
Operazione 3	Licenziamento agente
Operazione 4	Spostamento esercitazioni
Operazione 5	Trova gli agenti che lavorano in un determinato turno
Operazione 6	Trova i costi delle vetture usate per un servizio
Operazione 7	Cancella servizio
Operazione 8	Trova gli agenti che possono guidare un mezzo in un servizio
Operazione 9	Scrivi l’elenco degli agenti che fanno un esercitazione fuori dalla propria caserma
Operazione 10	Trova i dipendenti che hanno più di un numero di telefono
Operazione 11	Trova gli agenti che hanno usato un’arma in un servizio
Operazione 12	Calcolo entrate

 

Concetto	Tipo	Volume
Dipendenti	E	5200
Impiegati	E	200
Agenti	E	5000
Capoufficio	E	5
Maresciallo	E	1000
Sedi	E	20
Ufficio	E	5
Caserma	E	15
Servizi	E	1000
Collaboratori	E	20
Equipaggiamento	E	5300
Arma	E	5000
Veicolo	E	300
Esercitazione	E	20000
Manutenzione	E	10
Fornitori	E	10
Appartenenza	R	5200
Ubicazione	R	20000
Addestramento	R	20000
Dirigenza	R	5
Turno	R	4000
Collaborazione	R	200
Responsabilità	R	1000
Esigenza veicolo	R	500
Possesso	R	5000
Abilitazione	R	1500
Riparazioni	R	2000
Forniture	R	5300

 

Tavola delle operazioni

 

Operazione	Tipo	Frequenza
1	I	5 al mese
2	I	1 al settimana
3	I	5 al mese
4	I	5 al giorno
5	I	1 al giorno
6	I	2 al settimana
7	I	1 al settimana
8	I	10 al giorno
9	B	1 al settimana
10	I	3 al giorno
11	I	3 al giorno
12	B	1 al anno

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).

Schema E-R dell’operazione 5

 

Tavola degli accessi dell’operazione 5

 

 

Concetto	Costrutto	Accessi	Tipo
Agenti	E	1	L
Turno	R	0,76	L

 

 

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.

 

 

Schema E-R dell’operazione 11

 

Tavola degli accessi dell’operazione 11

 

Concetto	Costrutto	Accessi	Tipo
Agenti	E	1	L
Possesso	R	1	L
Arma	E	1	L
Turno	R	0,76	L
Servizi	E	0,76	L

 

5       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.

 

 

5.1           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.

 

5.2           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; lo schema viene cosi trasformato:

 

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.

5.3    Partizionamento/riaccorpamento di entità/relazioni

 

Attributi multi valori

 

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.

5.4    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.

Tabella delle entità ristruttutata

 

Entità	Descrizione partecipanti	Attributi	Identificatore
Dipendenti	Dati relativi a coloro che lavorano nell’agenzia	Cod fiscale, nome, cognome, stipendio	Cod fiscale
Sedi	Dati relativi agli edifici usati dall’agenzia	Codice, nome,tipo	Codice
Servizi	Dati relative ai luoghi (edifici o zone) da sorvegliare	Cod servizio, nome, indirizzo, ricavo	Cod servizio
Collaboratori	Dati relativi ai collaboratori esterni (altre agenzie o enti statali)	Tipo collaboratore, telefono	Tipo collaboratore
Equipaggiamento	Dati relativi agli oggetti che vengono dati agli agenti per lo svolgimento dei servizi	Codice, tipo, costo	Codice
Esercitazione	Dati relativi alle prove di tiro che gli agenti devono compiere	Num esercitazione, data, ora	Num esercitazione
Manutenzione	Dati relativi alle officine specializzate per la riparazione dei veicolo	Nome o:p>	Nome officina
Fornitori	Dati relativi alle ditte che forniscono gli equipaggiamenti	Ditta	Ditta
Domicilio	Dati relativi ai numeri telefonici e agli indirizzi di dipendenti, sedi, collaboratori,fornitori e officine di manutenzione	Cod domicilio, telefono, indirizzo	Cod domicilio

 

Tabella relazioni ristrutturata

 

Relazione	Entità partecipanti	Descrizione	Attributi
Appartenenza	Sedi, dipendenti	Assegna ogni dipendente ad una sede
Ubicazione	Sedi, esercitazione	Ogni poligono di tiro si trova in una caserma(che è una sede)
Addestramento	Esercitazione, dipendenti	Assegna un agente (che è un dipendente) ad una esercitazione
Dirigenza	Sedi, dipendenti	Ogni capoufficio (che è un dipendente) dirige un ufficio
Turno	Dipendenti, servizi	Invia gli agenti ai luoghi da sorvegliare	Data, ora
Collaborazione	Collaboratori, servizi	Tiene conto dei possibili collaboratori	Num collaboratori
Responsabilità	Dipendenti, servizi	Mostra il responsabile di ogni servizio
Esigenza veicolo	Equipaggiamento, servizi	Mostra le richieste dei servizi
Possesso	Dipendente, equipaggiamento	Ogni agente ha un’arma
Abilitazione	Dipendente, veicolo	Se un agente può guidare un veicolo
Riparazioni	Equipaggiamento, manutenzione	Elenco delle riparazioni fatte a un veicolo	Data, costo
Forniture	Fornitori, equipaggiamento	Elenco degli acquisti fatti
Agenda1	Telefono, fornitori	Elenco dei dati dei fornitori
Agenda2	Telefono, manutenzione	Elenco dei dati delle officine
Agenda3	Telefono, collaboratori	Elenco dei dati dei collaboratori
Agenda4	Telefono, dipendenti	Elenco dei dati dei dipendenti
Agenda5	Telefono, sedi	Elenco dei dati delle sedi

 

 

Porzione dei dati                                                                                                                 Operazioni

Num operazione	Descrizione
Operazione 1	Assunzione di un dipendente
Operazione 2	Aggiunta nuovo servizio
Operazione 3	Licenziamento agente
Operazione 4	Spostamento esercitazioni
Operazione 5	Trova gli agenti che lavorano in un determinato turno
Operazione 6	Trova i costi delle vetture usate per un servizio
Operazione 7	Cancella servizio
Operazione 8	Trova gli agenti che possono guidare un mezzo in un servizio
Operazione 9	Scrivi l’elenco degli agenti che fanno un esercitazione fuori dalla propria caserma
Operazione 10	Trova i dipendenti che hanno più di un numero di telefono
Operazione 11	Trova gli agenti che hanno usato un’ arma in un servizio
Operazione 12	Calcolo entrate

 

Concetto	Tipo	Volume
Dipendenti	E	5200
Sedi	E	20
Servizi	E	1000
Collaboratori	E	20
Equipaggiamento	E	5300
Esercitazione	E	20000
Manutenzione	E	10
Fornitori	E	10
Telefono	E	6000
Appartenenza	R	5200
Ubicazione	R	20000
Addestramento	R	20000
Dirigenza	R	5
Turno	R	4000
Collaborazione	R	200
Responsabilità	R	1000
Esigenza veicolo	R	500
Possesso	R	5000
Abilitazione	R	1500
Riparazioni	R	2000
Forniture	R	5300
Agenda1	R	12
Agenda2	R	11
Agenda3	R	25
Agenda4	R	22
Agenda5	R	25

Tavola delle operazioni

 

Operazione	Tipo	Frequenza
1	I	5 al mese
2	I	1 al settimana
3	I	5 al mese
4	I	5 al giorno
5	I	1 al giorno
6	I	2 al settimana
7	I	1 al settimana
8	I	10 al giorno
9	B	1 al settimana
10	I	3 al giorno
11	I	3 al giorno
12	B	1 al anno

 

Schema ER Finale

6       Progettazione logica nel modello concettuale

 

6.1    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.

 

6.2           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)

 

7                  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 Dipendenti.nome, Dipendenti.cognome, Dipendenti.CodFiscale

FROM Dipendenti INNER JOIN Turno ON Dipendenti.CodFiscale = Turno.agente

WHERE (([turno].[data]="16/11/2001"));

 

Operazione 6

 

SELECT Equipaggiamento.costo, Equipaggiamento.codice, Equipaggiamento.tipo

FROM Servizi INNER JOIN Equipaggiamento ON Servizi.codServizio = Equipaggiamento.servizio

WHERE (([servizi].[codservizio]="S4"));

 

Operazione 7

 

DELETE FROM SERVIZI

WHERE codServizio= 'S14' ;

 

 

Operazione 8

 

SELECT Dipendenti.CodFiscale, Dipendenti.nome, Dipendenti.cognome

FROM Servizi INNER JOIN (Equipaggiamento INNER JOIN (Dipendenti INNER JOIN Abilitazioni ON Dipendenti.CodFiscale = Abilitazioni.dipendenti) ON Equipaggiamento.codice = Abilitazioni.equipaggiamento) ON Servizi.codServizio = Equipaggiamento.servizio

WHERE (((Servizi.codServizio)="S4"));

 

Operazione 9

 

SELECT [Dipendenti].[CodFiscale], [Dipendenti].[nome], [Dipendenti].[cognome]

FROM ((Dipendenti INNER JOIN Addestramento ON [Dipendenti].[CodFiscale]=[Addestramento].[agenti]) INNER JOIN Esercitazione ON [Addestramento].[esercitazione]=[Esercitazione].[numEsercitazione]) INNER JOIN Sedi ON [Dipendenti].[sedi]=[Sedi].[Codice]

WHERE (([sedi].[codice]<>[esercitazione].[sedi]));

 

Operazione 10

 

SELECT Dipendenti.codfiscale, Dipendenti.nome,Dipendenti.cognome

FROM Dipendenti INNER JOIN Domicilio ON Dipendenti.CodFiscale = Domicilio.dipendenti

group by Dipendenti.codfiscale,Dipendenti.nome,Dipendenti.cognome

having count(Dipendenti.codfiscale)>1;

 

 

Operazione 11

 

SELECT Equipaggiamento.codice, Equipaggiamento.tipo

FROM (Servizi INNER JOIN (Dipendenti INNER JOIN Turno ON Dipendenti.CodFiscale = Turno.agente) ON Servizi.codServizio = Turno.servizio) INNER JOIN Equipaggiamento ON Dipendenti.arma = Equipaggiamento.codice

WHERE (([servizi].[codservizio]="S1"));

 

Operazione 12

 

SELECT Sum(Servizi.ricavo) AS SommaDiricavo

8       Dati di test

 

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

 

Dipendenti                                                                                 

CodFiscale	nome	cognome	stipendio	sedi	arma	tipo
a01	Roberto	Nacchia	L. 6.000.000	C1	A1	M
a02	Stefano	Tortora	L. 6.000.000	C2	A2	M
a03	Andrea	Cavallieri	L. 6.000.000	C3	A3	M
a04	Marco	Chiaravalloti	L. 6.000.000	C1	A4	M
a05	Stefano	Tortora	L. 1.500.000	U1		I
a06	Roberto	Nacchia	L. 3.000.000	C2	A5	A
a07	Cavallieri	Andrea	L. 4.000.000	U1		D
a08	Marco	Chiaravalloti	L. 3.000.000	C3	A6	A
a09	Andrea	Tortora	L. 1.500.000	U2		I
a10	Stefano	Cavallieri	L. 3.000.000	C2	A7	A
a11	Roberto	Chiaravalloti	L. 4.000.000	U2		D
a12	Clark	Kent	L. 6.000.000	C1	A8	M
a13	Dylan	Dog	L. 6.000.000	C2	A9	M

 

Abilitazioni                                                                                                                               Collaborazioni

ID	equipaggiamento	dipendenti
2	V1	A04
3	V2	A02
4	V2	A12
5	V1	A13
6	V2	A04

ID	servizi	collaboratori	num collaboratori
1	S1	polizia	2
2	S2	carabinieri	1
3	S4	FBI	3
4	S4	Ghost buster	4
5	S5	polizia	1

                                          

Addestramento                                                                                                                        Equipaggiamento     

ID	agenti	esercitazione
1	A01	01
2	A02	02
3	A03	03
4	A04	04
5	A06	05
6	A08	01
7	A10	03
8	A12	02
9	A13	01
10	A04	03
11	A06	03
12	A13	05

codice	tipo	costo	servizio	fornitore
A1	Pistola	L. 200.000		Armaiolo1
A2	Fucile	L. 300.000		Armaiolo2
A3	Pistola	L. 200.000		Armaiolo1
A4	Pistola	L. 200.000		Armaiolo1
A5	Fucile fotonico	L. 400.000		Armaiolo3
A6	Pistola	L. 200.000		Armaiolo1
A7	Fucile	L. 300.000		Armaiolo2
A8	Pistola	L. 200.000		Armaiolo1
A9	Pistola	L. 200.000		Armaiolo1
V1	Furgone	L. 5.000.000	S5	Fiat
V2	Furgone	L. 5.000.000	S4	Fiat

                                               

 

 

 

 

 

 

                     

                     

 

Domicilio

Cod domicilio	telefono	indirizzo	sedi	fornitore	manutenzione	dipendenti	collaboratori
D01	5487964	via a				a01
D02	1254789	via b				a02
D03	6874597	via c				a03
D04	9871254	via d				a04
D05	3254896	via e				a05
D06	2145879	via f				a06
D07	6547871	via g				a07
D08	3214785	via h				a08
D09	6547812	via i				a09
D10	1239744	via l				a10
D11	1239457	via m				a11
D12	3217654	via n				a12
D13	9845175	via o				a13
D14	33365129721	via a				a01
D15	3479871234	via b				a02
D16	9846545	via h	U2
D17	1321745	via l	C1
D18	98456132	via m	C2
D19	13647465	via f	U1
D20	1384613	via m	C2
D21	9987413	via e	C3
D22	112						polizia
D23	113						carabinieri
D24	674						FBI
D25	666						Ghost buster
D26	789						Ghost buster
D27	4648811	via H		Armaiolo1
D28	32111745	via a		Armaiolo2
D29	995764	via l		Armaiolo3
D30	131456	via m		FIAT
D31	13648746	via h			Riparatutto
D32	33345487	via c			Te smonto

 

Esercitazioni                                                                                                                                                             Fornitori

numEsercitazione	data	ora	sedi
01	14/11/2001	8.00.00	C1
02	15/11/2001	8.00.00	C2
03	16/11/2001	8.00.00	C3
04	18/11/2001	12.00.00	C1
05	15/11/2001	12.00.00	C2

Ditta
Armaiolo1
Armaiolo2
Armaiolo3
Fiat

                        

 

Manutenzione                                                                                                                     Riparazioni

nome
Riparatutto
Te smonto

ID	equipaggiamento	manutenzione	costo	data
2	V1	riparatutto	L. 100.000	14/11/2001
3	V2	te smonto	L. 300.000	17/11/2001
4	V2	riparatutto	L. 100.000	14/10/2001
5	V1	riparatutto	L. 200.000	11/11/2001
6	V1	te smonto	L. 600.000	14/10/2001

                                         

 

 

 

 

 

 

 

          Sedi                                                                                                                                          Servizi

Codice	nome	tipo	dirigente
C1	Urbe	C
C2	Sparatutto	C
C3	Sanguinari	C
U1	Sinistri	U	a07
U2	Bustarelle	U	a11

codServizio	nome	ricavo	maresciallo
S1	Banca Lavoro	L. 3.000.000	A01
S2	Banca Roma	L. 3.000.000	A02
S3	Banca Crotone	L. 3.000.000	A03
S4	Scorta valori	L. 4.000.000	A04
S5	Scorta valori	L. 4.000.000	A12
S6	Sorveglianza	L. 2.000.000	A13

                                

 

      Turno                                                                                                                                               Collaboratore  

ID	agente	servizio	ora	data
1	A06	S1	15.00.00	15/11/2001
2	A10	S2	21.00.00	16/11/2001
3	A06	S3	15.00.00	16/11/2001
4	A10	S4	21.00.00	15/11/2001
5	A10	S5	15.00.00	17/11/2001

tipo collaboretore
Carabinieri
FBI
Ghost Buster
Polizia

                  

 

Mostriamo infine anche i risultati di alcune delle operazioni .

 

Operazione 5                                                                                                                                                                                                             Operazione 6

(Trova gli agenti che lavorano in un determinato turno)                                                                                                                                                             (Trova i costi delle vetture usate per un servizio)

 

nome	cognome	CodFiscale
Stefano	Cavallieri	a10
Roberto	Nacchia	a06

costo	codice	tipo
L. 5.000.000	V2	Furgone

                                     

Operazione 8                                                                                                                                            Operazione 9

(Trova gli agenti che possono guidare un  mezzo in un servizio)                                                                                               (Scrivi l’elenco degli agenti che fanno un’esercitazione fuori dalla propria caserma)

CodFiscale	nome	cognome
a02	Stefano	Tortora
a12	Clark	Kent
a04	Marco	Chiaravalloti

CodFiscale	nome	cognome
a08	Marco	Chiaravalloti
a13	Dylan	Dog
a12	Clark	Kent
a10	Stefano	Cavallieri
a04	Marco	Chiaravalloti
a06	Roberto	Nacchia

                                     

 

 

Operazione 10                                                                                                                                          Operazione 11

(Trova i dipendenti che hanno più di un numero di telefono)                                                                                (Trova gli agenti che hanno usato un’arma in un servizio)
                  

codice	tipo
A5	Fucile fotonico

codfiscale	nome	cognome
a01	Roberto	Nacchia
a02	Stefano	Tortora

 

 

Operazione 12

(Calcolo entrate)

 

SommaDiricavo
L. 19.000.000