% Relazione su AlphaDB % Valentina Ferraioli(802243) Igor Ershov(78952) Stefano Andriolo(801172) % Sessione Invernale 2018-2019
Si vuole realizzare un database per un'azienda multinazionale che vuole mantenere sotto controllo i vari tecnici che viaggiano tra le sedi per fornire supporto o manutenzione.
I dati da salvare saranno:
-
Riguardo ai paesi si vorrà salvare il nome, l'identificativo ISO e il livello di pericolo operativo.
-
Riguardo alle sedi si vorrà codificare l'identificativo aziendale, la tipologia di sede e la ragione sociale.
-
Riguarlo allo staff, cioè i dipendenti di una sottosede, si vorranno identificare la sede per cui lavora (codice sede), il ruolo e la ragione sociale.
-
I tecnici, che andranno rappresentati attraverso il livello di pericolo su cui possono operare, saranno identificati dal proprio CF.
-
Per le specializzazioni si vorrà specificare il costo, il tipo ed il codice univoco.
-
Dei macchinari si vuole conoscere il nome, la data di acquisto, il codice identificativo, se è operativo o no, e la garanzia.
-
Dei componenti si vorrà rappresentare il costo, il codice, il peso e se è operativo o no.
-
Per ogni tipologia di intervento si vorrà salvare data di inizio, fine, descrizione e stato. Ci saranno diverse tipologie di intervento:
- Gli interventi di sostituzione (di un componente), svolte da un tecnico. Si vorrà rappresentare inoltre il costo di trasferimento e il costo giornaliero del tecnico;
- Gli interventi di riparazione (di un componente), che verranno svolti dallo staff presente in azienda;
- Gli interventi di aggiornamento (istruzione dello staff), svolti da un tecnico. Si vorrà rappresentare il costo di trasferimento e il costo giornaliero del tecnico.
In generale i pagamenti vengono effettuati al completamento di un intervento
Questo DataBase verrà acceduto da utenti attraverso un interfaccia web dove diversi tipi di utenti potranno fare diverse operazioni.
Gli utenti saranno divisi in quattro gruppi: Amministratori, che avranno accesso completo CRUD a tutte le tabelle, Sedi, che avranno permessi di scrittura sulle macchine, sui componenti ,sullo staff e sulle richieste ; Tecnici che avranno accesso CRU alle tabelle richieste e R a se stessi, e staff , che avranno accesso CRU alle tabelle di richieste di riparazione, RU alle tabelle delle macchine ,dei componenti e di R su loro stessi.
| Termini | Descrizione | Sinonimi | Collegamenti |
|---|---|---|---|
| Paese | Nazione in cui può essere presente una sede | Stato/Nazione | Sede, Tecnico |
| Sede | Filiale dell'azienda multinazionale | Filiale/sottosede | Paese, Richieste, Macchine, Staff |
| Staff | Dipendenti stazionati ad una singola sede | Impiegati/ Personale | Richieste aggiornamento, Componente, Sede |
| Tecnico | Dipendente specializzato che viene trasferito per risolvere problemi relativi ai macchinari | Specializzazione, Paese, Richieste | |
| Intervento | L'operazione che è richiesta dalla sede | log/richiesta | Staff, Tecnico, Componente, Sede |
| Macchinario | Macchina che produce beni | nessuno | Componente, Sede |
| Componente | Pezzo di un macchinario | nessuno | Macchina, Staff |
| Livello di pericolo | Indice delle precauzioni necessarie a lavorare in uno stato | nessuno | |
| Multinazionale | Azienda madre di cui tutte le vari sedi sono filiali | Azienda madre | |
| Identificativo ISO | Codice standard univoco fornito ad ogni paese | ISO | |
| Specializzazione | Formazione specialistica su un determinato argomento | nessuno | Tecnico |
| Costo di trasferimento | Costo di trasferta del tecnico dal suo paese attuale a quello della sede in cui dovrà operare | nessuno | |
| Aggiornamento | Corsi di formazione dello staff da tecnici specializzati | nessuno | |
| Operativo | Se il componente o la macchina sono funzionanti e non rotti | nessuno |
La ragione sociale sarà data da nome, codice fiscale e numero telefonico in caso di persona oppure nome legale, partita iva e indirizzo in caso di sede.
La garanzia è un periodo di tempo espresso in anni in cui tutti i costi di intervento al macchinario sono coperti dalla multinazionale.
Il termine azienda può essere usato sia per definire una singola sede che per definire la multinazionale di cui sono tutte filiali.
Il costo totale è calcolato in base al costo giornaliero di un intervento moltiplicato per i giorni lavorati e sarà contenuto in log.
Una macchina si considera operativa se tutti i suoi componenti sono operativi.
-
Frasi di carattere generale
Si vuole realizzare un database per un azienda multinazionale che vuole mantenere sotto controllo i vari tecnici che viaggiano tra le sedi per fornire supporto o manutenzione. -
Frasi riguardanti i paesi
si vorrà salvare il nome , l'identificativo ISO e il livello di pericolo. -
Frasi riguardanti le sedi
si vorrà codificare l'identificativo aziendale , la tipologia di sede e la ragione sociale. -
Frasi riguardanti lo Staff
i dipendenti di una sottosede, si vorranno identificare la sede per cui lavora(codice sede), ruolo e la ragione sociale. -
Frasi riguardanti i Tecnici andranno rappresentati attraverso il livello di pericolo su cui possono operare (dipende dall'anzianità nell'azienda), ragione sociale.
-
Frasi riguaranti e specializzazioni
si vorrà specificare il costo e il tipo e il codice univoco -
Frasi riguardanti i macchinari
si vuole conoscere il nome, la data di acquisto, il codice identificativo, se è operativa o no, e la garanzia -
Frasi riguardanti i componenti
si vorrà rappresentare il costo, il codice, il peso e se è operativo o no -
Frasi riguardanti gli Interventi
si vorrà salvare data di inizio, fine, descrizione e stato. Ci saranno diverse tipologie di intervento:- Gli interventi di sostituzione (di un componente), svolte da un tecnico. Si vorrà rappresentare inoltre il costo di trasferimento e il costo giornaliero del tecnico;
- Gli interventi di riparazione (di un componente), che verranno svolti dallo staff presente in azienda;
- Gli interventi di aggiornamento (istruzione dello staff), svolti da un tecnico. Si vorrà rappresentare il costo di trasferimento e il costo giornaliero del tecnico.
- Inserire un nuovo paese (1 volta ogni paio d'anni)
- Inserire una nuova sede (1 volta all'anno)
- Inserire un nuovo tecnico (5 volte all'anno)
- Inserire un nuovo membro dello staff (2 volte al mese)
- Inserire una nuova macchina (1 volta al mese)
- Inserire un nuvo componente(1 volta al mese)
- Aggiungere una nuova richiesta di riparazione(2 volte al mese)
- Aggiungere una nuova richiesta di sostituzione(1 volta al mese)
- Aggiungere una nuova richiesta di Aggiornamento(1 volta al mese)
- Aggiornamento del componente 1 nella macchina 1 (1 volta al mese)
- Visualizzare tutte le sedi per un determinato paese dato il codice iso(1 volta ogni 6 mesi)
- Visualizzare tutti i paesi per un livello di pericolo (1 volta al giorno)
- Visualizzare tutti i paesi in cui un tecnico può operare (1 volta al giorno)
- Visualizzare tutte le macchine per una sede (1 volta al giorno)
- Vedere quali macchine non sono operative data una sede (5 volte al giorno)
- Aggiornare il livello di pericolo su cui può operare un tecnico (1 volta all'anno)
- Calcolare il numero di richiesteAggiornamento che risultano "completate"(1 volta al giorno)
- Visualizzare tutte le macchine che sono ancora in garanzia(1 volta al mese)
- Rimuovere un membro dello staff che non è più dipendente(1 volta ogni 6 mesi)
- Calcolare quanto una sede ha speso in interventi in un certo periodo di tempo (1 volta al mese), i pagamenti vengono effettuati al completamento dell'incarico
- Risponedere ad un intervento di riparazione (1 volta al giorno)
Abbiamo optato per una strategia mista, sviluppata nel modo seguente
Sono state identificate (seguendo la strategia bottom-up) inizialmente le seguenti entità: sede, paese, tecnico,specializzazione, vari tipi di richiesta, macchina, componente, staff. Le entità sopra elencate si possono suddividere in tre gruppi principali: sede che gestisce le richieste, richiesta che comprende tutti i tipi di interventi, tecnici che elaborano le richieste della sede.
Ad un primo livello di astrazione, tenendo conto delle considerazioni fatte sopra è stato concepito il seguente primo scheletro di schema concettuale:
L'entità sede è caratterizzata dagli attributi: ragione sociale e identificativo azienda,l'attributo nome indica il nominativo dell'azienda. L'entità sede è associata all'entità Paese attraverso la relazione Situata.
L'entità tecnico è caratterizzata dall'attributo livello di pericolo, che vincolerà i paesi nel quale un tecnico può spostarsi. Inoltre, per poter adempiere alcune richieste, è necessario che abbia conseguito dei corsi di formazione.
L' entità richiesta è una generalizzazione totale delle possibili richieste effettuate dalla sede. La singola richiesta necessita di comportamenti diversi
| Nome entità | Descrizione | Attributi | Identificatore |
|---|---|---|---|
| sede | filiale che effettua richieste | nome(stringa), indirizzo(stringa) | partita IVA(stringa) |
| paese | nazione in cui è presente una sottosede | nome(stringa),livello pericolo(numerico) | codice ISO(stringa) |
| tecnico | persona che risponde ad alcune delle richieste della sede | nome(stringa), telefono(numerico), livello di pericolo(numerico) | CF(stringa) |
| specializzazione | titolo richiesto per i tecnici per poter rispondere ad alcune richieste | tipo(stringa), costo(numerico) | ID(numerico) |
| macchina | strumento di lavoro posseduto dall'azienda sul quale possono essere fatte delle richieste d'intervento | nome(stringa), descrizione(stringa), data inizio(data), data fine (data), stato operativo(booleano) | ID(numerico) |
| componente | componente della macchina su cui viene fatta una richiesta di riparazione/sostituzione | peso(numerico), costo(numerico), stato operativo(booleano) | ID(numerico) |
| richiesta sostituzione | richiesta della sostituzione di un componente da parte dei tecnici | costo gg(numerico), costo trasferta(numerico) | ID(numerico) |
| richiesta riparazione | richiesta della riparazione di un componente | ID(numerico) | |
| richiesta aggiornamento | richiesta di aggiornamento dello staff tramite le lezioni di un tecnico | costo gg(numerico), costo trasferta(numerico) | ID(numerico) |
| staff | staff di una filiale, frequenta i corsi di aggiornamento e ripara i componenti delle macchine | ruolo(stringa), codiceazienda(numerico), nome(stringa), telefono(numerico) | CF(stringa) |
| Nome relazione | Descrizione | entità coinvolte | Attributi |
|---|---|---|---|
| situata | associa ad una sede il paese in cui è situata | Sede(1,1), paese(0,N) | |
| inviata | associa alle richieste la sede da cui sono state inviate | sede(1,N), richiesta(1,N) | |
| contenuto | associa un componente alla macchina di cui fa parte | macchina(1,n), componente(0,1) | |
| sostituisce | associa la richiesta di sostituzione al componente da sostituire | componente(1,N), richiesta sostituzione(1,1) | |
| riparazione | associa la richiesta di riparazione al componente da sostituire di cui si occuperà lo staff | componente(1,N), richiesta riparazione(1,1), staff(1,N) | |
| formazione | associa la richiesta di aggiornamento allo staff dell'azienda | staff(1,N), richiesta aggiornamento(1,1) | |
| soddisfatto | associa le richieste al tecnico che se ne occuperà | tecnico(1,N), richiesta(1,1) | |
| frequentato | associa il tecnico alle specializzazione che ha frequentato | tecnico(1,1), specializzazione(1,1) | |
| operato | associa il tecnico ai paesi in cui gli è consentito operare dato il suo livello di pericolo | tecnico(1,N) paese(1,n) | |
| dipendente | associa lo staff alla sede in cui lavora | sede(1,N), staff(1,1) | |
| possiede | associa le macchine alle sedi a cui appartengono | sede(1, N), macchina(1, 1) |
| Regole di vincolo |
|---|
| (RV1) per lavorare in un determinato paese il tecnico deve possedere un livello di pericolo <= al livello di pericolo del paese |
| (RV2) il livello di percolo di un paese dev'essere compreso tra 0 e 10 |
| (RV3) la data di fine garanzia - data di inizio dev'essere <= 730 giorni |
| (RV4) lo stato di una richiesta può essere: "in attesa", "in carico" o "completata" |
| (RV5) Un tecnico per fornire supporto a una richiesta di aggiornamento deve aver frequentato una specializzazione |
| (RV6) Un membro dello staff può riparare solo una macchina presente nella sede in cui lavora |
| (RV7) Una richiesta completata deve avere una data di inizio e una data di fine |
| (RV8) Una richiesta in carico deve avere una data di inizio |
| (RV9) Un tecnico può avere una singola richiesta "in carico" |
| (RV10) Un membro dello staff può avere una singola richiesta di riparazione "in carico" |
| (RV11) Una richiesta di riparazione o sostituzione può essere inserita solo per un componente non operativo |
| Regole di derivazione |
|---|
| (RD1) La durata dell'intervento è data dalla data di fine dell'intervento - la data di inizio |
| (RD2) Il costo totale di un intervento è dato dalla (durata dell'intervento * costo giornaliero dell'intervento) + costo trasferta del tecnico, oppure è solamente il costo di trasferta del tecnico se la macchina è ancora in garanzia (durata rimanente della garanzia >= 0) |
| (RD3) La durata rimanente della garanzia è data dalla data di fine garanzia - giorno attuale |
| (RD4) Spesa mensile di un azienda è data dalla somma dei costi totali di tutti gli interventi richiesti da quell'azienda in quel mese |
Tavola dei volumi:
| Concetto | Tipo | Volume |
|---|---|---|
| Paese | E | 7 |
| Sede | E | 15 |
| Staff | E | 300 |
| Tecnico | E | 50 |
| Specializzazione | E | 10 |
| Macchina | E | 70 |
| Componente | E | 1400 |
| Richiesta sostituzione | E | 200 |
| Richiesta riparazione | E | 1000 |
| Richiesta aggiornamento | E | 30 |
| Operato | R | 250 |
| Frequentato | R | 100 |
| Inviata | R | 1230 |
| Soddisfatto | R | 1230 |
| Formazione | R | 30 |
| Sostituisce | R | 200 |
| Riparazione | R | 1000 |
| Contenuto | R | 1400 |
| Possiede | R | 70 |
| Situata | R | 15 |
Tavola delle operazioni
| Operazione | Frequenza |
|---|---|
| 1 | 1 volta ogni paio d'anni |
| 2 | 1 volta all'anno |
| 3 | 5 volte all'anno |
| 4 | 2 volte al mese |
| 5 | 1 volta al mese |
| 6 | 1 volta al mese |
| 7 | 2 volte al mese |
| 8 | 1 volta al mese |
| 9 | 1 volta al mese |
| 10 | 1 volta al mese |
| 11 | 1 volta ogni 6 mesi |
| 12 | 1 volta al giorno |
| 13 | 1 volta al giorno |
| 14 | 1 volta al giorno |
| 15 | 5 volte al giorno |
| 16 | 1 volta all'anno |
| 17 | 1 volta al giorno |
| 18 | 1 volta al mese |
| 19 | 1 volta ogni 6 mesi |
| 20 | 1 volta al mese |
| 21 | 1 volta al giorno |
Eliminazione delle ridondanze:
Data la natura dell'attributo operativo di una macchina si nota una ridondanza. Questo valore si può calcolare controllando tutti i componenti associati alla macchina per vedere se sono tutti operativi.
Le operazioni che coinvolgono questo attributo sono l'operazione 15, l'operazione 21 (un componente riparato ritorna operativo e quindi anche la macchina ad esso associata)
Tavole degli accessi in presenza di ridondanza:
Operazione 15 (Assumento una media di 10 macchine per sede):
| Concetto | Costrutto | Accessi | Tipo |
|---|---|---|---|
| Macchina | Entità | 20 | R |
Operazione 21:
| Concetto | Costrutto | Accessi | Tipo |
|---|---|---|---|
| Componente | Entità | 1 | W |
| Contenuto | Relazione | 1 | R |
| Macchina | Entità | 1 | W |
Tavole degli accessi in assenza di ridondanza:
Operazione 15 (Assumendo una media di 10 macchine per sede e 10 componenti per macchina):
| Concetto | Costrutto | Accessi | Tipo |
|---|---|---|---|
| Macchina | Entità | 10 | R |
| Contenuto | Relazione | 100 | R |
| Componente | Entità | 100 | R |
Operazione 21:
| Concetto | Costrutto | Accessi | Tipo |
|---|---|---|---|
| Componente | Entità | 1 | W |
In presenza di ridondanza il costo delle varie operazioni (considerando il doppio il costo di una scrittura rispetto alla lettura):
Op.15 = 20(costo)* 150 (Volte al mese) = 3000
Op 21= (1*2 + 1 + 1*2)(costo) * 30 (Volte al mese) = 150
Totale = 3150
In assenza di ridondanza il costo delle varie operazioni (considerando il doppio il costo di una scrittura rispetto alla lettura):
Op.15 = (10+100+100)(costo)* 150 (Volte al mese) = 31500
Op 21 = (1*2)(costo) * 30 (Volte al mese) = 60
Totale = 31560
Di conseguenza si ritiene opportuno mantenere la ridondanza
Eliminazione delle gerarchie:
Riguardo all'entità richieste si è deciso di accorparle in Richiesta di sostituzione, Richiesta di riparazione e Richiesta di aggiornamento perchè le operazioni 7, 8 e 9 fanno riferimenti distinti alle varie richieste e la generalizzione è di tipo totale. Inoltre si vogliono dividere le richieste di riparazione dagli altri tipi perchè visto che sono eseguiti dallo staff non hanno costi.
Accorpamenti e partizionamenti:
Data la natura (1,1) della relazione frequentato da parte di Tecnico si nota che potrebbe essere accorpata come un campo aggiuntivo nell'entità Tecnico, rimuovendo quindi la relazione in favore di una chiave esterna.
Visto che le relazioni richiesta sostituzione inviata, richiesta riparazione inviata e richiesta sostituzione inviata hanno una relazione (1,1) da parte delle relazioni si è deciso di accorparle nelle entità richieste associate, aggiungendo una chiave esterna che rappresenta la sede che ha inserito la richiesta.
Inoltre data la relazione (1,1) tra richiesta riparazione e la relazione riparazione, e l'assenza di campi non derivati dall'ex-entità padre si è deciso di accorpare questa entità, insieme alla relazione invio richiesta riparazione direttamente nella relazione riparazione, che ora diventerà una relazione a tre tra Sede, Staff e Componente, con in aggiunta gli attributi di richiesta riparazione.
Eliminazione degli attributi multivalore:
Non sono stati rilevati attributi multivalore.
Elenco degi identificatori principali:
| Nome entità | Identificatore |
|---|---|
| Paese | codice ISO(stringa) |
| Sede | Partita IVA(stringa) |
| Staff | CF(stringa) |
| Tecnico | CF(stringa) |
| Specializzazione | ID(numerico) |
| Macchinari | ID(numerico) |
| Componenti | ID(numerico) |
| Richiesta sostituzione | ID(numerico) |
| Richiesta riparazione | ID(numerico) |
| Richiesta aggiornamento | ID(numerico) |
| Nome entità | Commento |
|---|---|
| Sede | Non esistono dipendenze non banali tra gli attributi. L’unica dipendenza presente è tra codice fiscale e ragione sociale (dipendenza banale). |
| Paese | Non presenta dipendenze non banali tra gli attributi ISO, livello pericolo, nome. |
| Componente | Non presenta dipendenze non banali tra gli attributi ID, peso, costo, statoOperativo. |
| Staff | non esistono dipendenza non banali tra gli attributi. L'unica dipendenza presente è tra codice fiscale e ragione sociale (dipendenza banale). |
| Tecnico | Non esistono dipendenze non banali tra gli attributi. L’unica dipendenza presente è tra codice fiscale e ragione sociale (dipendenza banale). |
| Richiesta Aggiornamento | Non esistono dipendenze non banali tra gli attributi. |
| Richiesta Sostituzione | Non esistono dipendenze non banali tra gli attributi. |
| Macchina | Non esistono dipendenze non banali tra gli attributi. |
| Entità-Relazione | Traduzione |
|---|---|
| Sede | Sede(PIva,nome,indirizzo) |
| Paese | Paese(ISO,nome,LivelloPericolo) |
| Tecnico | Tecnico(CF, nome,tel,PermessoPericolo,IDspec) |
| Macchina | Macchina(ID,nome,descrizione, ginizio,gfine, stato_operativo) |
| Componente | Componente(ID,peso,costo,stato_operativo) |
| RichiestaSostituzione | RichiestaSostituzione(ID,Sede,costoGG,costoTrasferta, stato,dataInizio,dataFine,descrizione) |
| RichiestaAggiornamento | RichiestaAggiornamento(ID,Sede,costoGG,costoTrasferta, stato,dataInizio,dataFine,descrizione) |
| Staff | Staff(CF,ruolo,nome,tel) |
| SoddisfaSostituzione | SoddisfaSostituzione(Tecnico,Richiesta) |
| Sostituisce | Sostituisce(Componente,Richiesta) |
| Riparazione | Riparazione(Staff,Componente,Sede,stato ,dataInizio,dataFine,descrizione) |
| SoddisfaFormazione | SoddisfaFormazione(RichiestaAggiornamento,Tecnico) |
| Formazione | Formazione(Staff,RichiestaAggiornamento,) |
| Operato | Operato(Tecnico,Paese) |
| Situata | Situata(Sede,Paese) |
| Contenuto | Contenuto(Macchina,Componente) |
| Dipendente | Dipendente(Sede,Staff) |
| Traduzione | Vincoli di riferimento |
|---|---|
| Sede(PIva,nome,indirizzo) | |
| Paese(ISO,nome,LivelloPericolo) | |
| Tecnico(CF, nome,tel,PermessoPericolo,IDspec) | |
| Macchina(ID,nome, descrizione,garanzia, ginizio,gfine,stato_operativo) | |
| Componente(ID,peso, costo,stato_operativo) | |
| RichiestaSostituzione(ID,Sede, costoGG,costoTrasferta, stato,dataInizio,dataFine,descrizione) | Sede -> Sede.PIva |
| RichiestaAggiornamento(ID,Sede, costoGG,costoTrasferta, stato,dataInizio,dataFine,descrizione) | Sede -> Sede.PIva |
| Staff(CF,ruolo,nome,tel) | |
| SoddisfaSostituzione (Tecnico,Richiesta) | Tecnico -> Tecnico.Cf , Richiesta->RichiestaSostituzione.ID |
| Sostituisce (Componente,Richiesta) | Componente -> Componente.ID , Richiesta->RichiestaSostituzione.ID |
| Riparazione (Staff,Componente,Sede, stato,dataInizio,dataFine,descrizione) | Staff -> Staff.CF , Sede->Sede.PIva , Componente-> Componente.ID |
| SoddisfaFormazione (Richiesta,Tecnico) | Richiesta->RichiestaAggiornamento.ID , Tecnico->Tecnico.CF |
| Formazione(Staff,Richiesta) | Richiesta->RichiestaAggiornamento.ID ,Staff.CF |
| Operato(Tecnico,Paese) | Tecnico->Tecnico.ID, Paese->Paese.ISO |
| Situata(Sede,Paese) | Sede->Sede.PIva, Paese->Paese.ISO |
| Contenuto (Macchina,Componente) | Macchina->Macchina.ID, Componente->Componente.ID |
| Dipendente(Sede,Staff) | Sede->Sede.PIva,Staff->Staff.CF |
| Possiede(Sede,Macchina) | Sede->Sede.PIva, Macchina->Macchina.ID |
Gli utenti di tipo Admin avranno accesso CRUD a tutte le entità.
Ora verranno elencati gli altri utenti, se un entità non è presente vorrà dire che l'utente di quel gruppo non avrà permessi su quell'entità
Gli utenti di tipo Tecnico avranno i seguenti permessi:
| Entità-Relazione | Create | Read | Update | Delete |
|---|---|---|---|---|
| Sede | Read | |||
| Paese | Read | |||
| Tecnico | Read | |||
| RichiestaSostituzione | Read | Update | ||
| RichiestaAggiornamento | Read | Update | ||
| SoddisfaSostituzione | Create | Read | Update | |
| Sostituisce | Create | Read | Update | |
| SoddisfaFormazione | Create | Read | Update | |
| Formazione | Create | Read | Update | |
| Operato | Read | |||
| Situata | Read |
Gli utenti di tipo Sede avranno i seguenti permessi:
| Entità-Relazione | Create | Read | Update | Delete |
|---|---|---|---|---|
| Sede | Read | Update | ||
| Tecnico | Read | |||
| Macchina | Create | Read | Update | Delete |
| Componente | Create | Read | Update | Delete |
| RichiestaSostituzione | Create | Read | Delete | |
| RichiestaAggiornamento | Create | Read | Delete | |
| Staff | Create | Read | Update | Delete |
| Riparazione | Create | Read | Delete | |
| Formazione | Read | |||
| Contenuto | Create | Read | Update | Delete |
| Dipendente | Create | Read | Update | Delete |
| Possiede | Create | Read | Update | Delete |
Gli utenti di tipo Staff avranno i seguenti permessi:
| Entità-Relazione | Create | Read | Update | Delete |
|---|---|---|---|---|
| Sede | Read | |||
| Macchina | Read | Update | ||
| Componente | Read | Update | ||
| Staff | Read | |||
| Riparazione | Read | Update | ||
| Formazione | Read | Update | ||
| Contenuto | Read | Update | ||
| Dipendente | Read | |||
| Possiede | Read |
CREATE TABLE sede(
PIva char(20) not null primary key,
nome char(30) not null,
indirizzo char(30)
)
CREATE TABLE paese(
ISO char(10) not null primary key,
nome char(30) not null,
livelloPericolo char(30) not null
)
CREATE TABLE tecnici(
CF char(20) not null primary key,
nome char(30) not null,
tel char(30),
PermessoPericolo char(30) not null
)
CREATE TABLE macchina(
ID int not null auto_increment,
nome char(30) not null,
descrizione char(30),
data_inizio date not null,
data_fine date not null,
stato_operativo bool not null,
primary key (ID)
)
CREATE TABLE componente(
ID int not null auto_increment,
peso numeric(5) not null,
costo numeric(5) not null,
stato_operativo bool not null,
primary key (ID)
)
CREATE TABLE richiestaSostituzione(
ID int not null auto_increment primary key,
costogiorn numeric(5) not null,
IDsede char(20) references sede(PIva),
costotrasf numeric(5) not null,
stato char(12) not null ,
dataInizio date not null,
dataFine date,
descrizione char(20) not null
)
CREATE TABLE richiestaAggiornamento(
ID int not null primary key auto_increment,
IDsede char(20) references sede(PIva),
costogiorn numeric(5) not null,
costotrasf numeric(5) not null,
stato char(12) not null ,
dataInizio date not null,
dataFine date,
descrizione char(20) not null
)
CREATE TABLE staff(
CF char(20) not null primary key,
ruolo char(20) not null,
nome char(20) not null,
tel char(20) not null,
codAzienda char(20) references sede(PIva) ,
ON DELETE SET DEFAULT
)
CREATE TABLE soddisfaSostituzione(
tecnico char(20) not null references tecnici(CF),
richiesta numeric(5) not null references richiestaSostituzione(ID),
primary key(tecnico, richiesta)
)
CREATE TABLE sotituisce(
componente numeric(5) not null references componente(ID),
richiesta numeric(5) not null references richiestaSostituzione(ID),
primary key(componente, richiesta)
)
CREATE TABLE riparazione(
componente numeric(5) not null references componente(ID),
staff numeric(5) references staff(CF),
sede char(20) not null references sede(PIva),
stato bool not null,
dataInizio date not null,
dataFine date ,
descrizione char(20),
primary key(componente,staff,sede)
)
CREATE TABLE soddisfaFormazione(
richiesta numeric(5) not null references richiestaSostituzione(ID),
tecnico char(20) not null references tecnici(CF),
primary key(richiesta,tecnico)
)
CREATE TABLE formazione(
richiesta numeric(5) not null references richiestaAggiornamento(ID),
staff char(20) not null references staff(CF),
primary key(richiesta, staff)
)
CREATE TABLE operato(
tecnico char(20) not null references tecnici(CF),
paese char(10) not null references paese(ISO),
primary key(tecnico, paese)
)
CREATE TABLE situata (
sede char(20) not null references sede(PIva),
paese char(10) not null references paese(ISO),
primary key(sede, paese)
)
CREATE TABLE contenuto(
macchina numeric(5) not null references macchina(ID),
componente numeric(5) not null references componente(ID),
primary key(macchina, componente)
ON UPDATE CASCADE
)
CREATE TABLE dipendente(
sede char(20) not null references sede(PIva),
staff char(20) not null references staff(CF),
primary key(sede,staff)
)
CREATE TABLE possiede(
sede char(20) not null references sede(PIva),
macchina numeric(5) not null references macchina(ID),
primary key(sede, macchina)
)- Inserire un nuovo paese (1 volta ogni paio d'anni
INSERT INTO paese(ISO, nome, LivelloPericolo)
VALUES ('ISO 3166-2:IT', 'ITALIA', 5);- Inserire una nuova sede (1 volta all'anno)
INSERT INTO sede (PIva, nome, indirizzo)
VALUES ('S01', 'ITALY1', 'Via rizzoli')per ogni sede:
INSERT INTO situata(sede, paese)
VALUES (..) - Inserire un nuovo tecnico (5 volte all'anno)
INSERT INTO tecnici (CF, nome, tel, PermessoPericolo)
VALUES ('MRARSS111111', 'MARIO ROSSI', '3332445559',7 ) per ogni tecnico:
INSERT INTO operato(Tecnico, Paese)
VALUES('MRARSS111111', 'it') - Inserire un nuovo membro dello staff (2 volte al mese)
INSERT INTO staff (CF,ruolo,nome,tel)
VALUES ('LGUVRD22222', 'operaio', 'luigi verdi', '1125656')per ogni nuovo membro:
INSERT INTO dipendente(sede, staff)
VALUES ('S01', 'LGIVRD') - Inserire una nuova macchina (1 volta al mese)
INSERT INTO macchina(ID, nome, descrizione, data_inizio, data_fine, stato_operativo)
VALUES ('1', 'Caffettiera', 'Macchinetta del caffe', 12/01/19, 12/01/22, True); per ogni macchina:
INSERT INTO possiede(sede, macchina)
VALUES (..)- Inserire un nuvo componente
INSERT INTO componente(peso,costo,stato_operativo)
VALUES (...)per ogni componente:
INSERT INTO contenuto(macchina,componente))
VALUES (..)- Aggiungere una nuova richiesta di riparazione
INSERT INTO riparazione(ID, Sede,
stato, dataInizio, dataFine, descrizione)
VALUES (1, 'S01', 'in attesa',
12/01/19, 14/01/19, 'riparazione componente c1' )- Aggiungere una nuova richiesta di sostituzione:
INSERT INTO richiestaSostituzione(ID, IDsede, costogiorn,
costotrasf, stato, dataInizio, dataFine, descrizione)
VALUES (1, 'S03',15,20, 'in attesa',
'2019-01-30', '2019-01-31', 'sostituzione' )per ogni richiesta di sostituzione:
INSERT INTO soddisfaSostituzione(Tecnico,Richiesta) VALUES (..)
INSERT INTO sostituisce(Componente,Richiesta) VALUESS (..)- Aggiungere una nuova richiesta di Aggiornamento:
INSERT INTO richiestaAggiornamento(ID, IDsede, costogiorn,
costotrasf, stato, dataInizio, dataFine, descrizione)
VALUES ('1', 'S03',15,20,'in attesa',
'2019-01-30','2019-01-31', 'formazione staff')per ogni richiesta di sostituzione:
INSERT INTO soddisfaFormazione(Tecnico,Richiesta) values ('MRARSS111111',2)
INSERT INTO Formazione(staff,richiestaAggiornamento) VALUES(..)- Aggiornamento del componente 1 nella macchina 1 (1 volta al mese)
UPDATE contenuto
SET macchina=0
WHERE macchina=1 and componente=1
- Visualizzare tutte le sedi per un determinato paese dato il codice iso
SELECT sede.PIva, sede.nome, paese.nome, paese.ISO
FROM situata, sede, paese
WHERE situata.sede = sede.PIva and paese.ISO='it'- Visualizzare tutti i paesi per un livello di pericolo (1 volta al giorno)
SELECT paese.ISO, paese.nome
FROM paese
WHERE paese.livelloPericolo = 5- Visualizzare tutti i paesi in cui un tecnico può operare (1 volta al giorno)
SELECT p.ISO, p.nome
FROM paese as p JOIN operato as o ON p.ISO = o.paese
JOIN tecnici t ON o.tecnico = t.CF
WHERE t.PermessoPericolo <= p.livelloPericolo AND t.CF='CCCDDD'- Visualizzare tutte le macchine per una sede (1 volta al giorno)
SELECT m.ID, m.nome
FROM sede as s JOIN possiede as p ON s.PIva = p.sede
JOIN macchina as m ON m.ID = p.macchina
WHERE s.PIva ='S01'- Vedere quali macchine non sono operative data una sede (5 volte al giorno)
SELECT macchina.ID, macchina.nome
FROM sede JOIN possiede ON sede.PIva=possiede.sede
JOIN macchina ON possiede.macchina=macchina.ID
WHERE sede.PIva = 'S01' and macchina.stato_operativo = 0- Aggiornare il livello di pericolo su cui può operare un tecnico (1 volta all'anno)
UPDATE tecnici
SET permessoPericolo=10
WHERE nome = 'MARIO ROSSI'- Calcolare il numero di richiesteAggiornamento che risultano "completate"
SELECT count(*)
FROM richiestaAggiornamento
WHERE stato='completata'- Visualizzare tutte le macchine che sono ancora in garanzia
SELECT nome, descrizione
FROM macchina
WHERE data_fine > CURRENT_DATE- Rimuovere un membro dello staff che non è più dipendente
DELETE FROM staff
WHERE CF='LGUVRD22222' - Calcolare quanto una sede ha speso in interventi in un certo periodo di tempo (1 volta al mese), i pagamenti vengono effettuati al completamento dell'incarico
SELECT SUM(
( SELECT SUM((costogiorn*(DATEDIFF(dataFine,dataInizio)))+costotrasf)
FROM richiestaSostituzione
WHERE (idsede='S03' AND stato='completata'
AND DATEDIFF(datafine,'2019-02-20')<0
AND DATEDIFF(datafine,'2019-02-09')>0 )
) +
( SELECT SUM((costogiorn*(DATEDIFF(dataFine,dataInizio)))+costotrasf)
FROM richiestaAggiornamento
WHERE (idsede='S03' AND stato='completata'
AND DATEDIFF(datafine,'2019-02-20')<0
AND DATEDIFF(datafine,'2019-02-09')>0 )
)
)- Rispondere ad un intervento di riparazione
START TRANSACTION
UPDATE riparazione SET stato = true, dataFine = DATE, staff = 'CCCDDD'
WHERE componente = 1 and sede = 'S04' ;
UPDATE componente c JOIN riparazione r ON c.id = r.componente
SET c.stato_operativo = true WHERE r.componente = 1 and r.sede = 'S04' ;
UPDATE componente c JOIN contenuto co ON c.id = co.componente
JOIN macchima m ON co.macchina = m.id JOIN possiede p ON p.macchina = m.id
JOIN sede s ON s.PIva = p.sede
SET m.stato_operativo = true
WHERE c.ID = 1 and s.PIva = 'S04';
COMMIT;Il sito web con le operazioni elencate può essere visitato al seguente
url:http://site1909.tw.cs.unibo.it