· 6 years ago · Dec 01, 2019, 09:18 AM
1BEGIN;
2CREATE DATABASE IF NOT EXISTS Progetto;
3COMMIT;
4
5SET GLOBAL event_scheduler = ON;
6
7/*Distruttore*/
8
9DROP TABLE IF EXISTS Agriturismo;
10/*ALLEVAMENTO*/
11
12DROP TABLE IF EXISTS Famiglia;
13DROP TABLE IF EXISTS Fornitore;
14DROP TABLE IF EXISTS Vendita;
15DROP TABLE IF EXISTS Posizione;
16DROP TABLE IF EXISTS Animale;
17DROP TABLE IF EXISTS Capienza;
18DROP TABLE IF EXISTS Pavimento;
19DROP TABLE IF EXISTS Locale;
20DROP TABLE IF EXISTS Stalla;
21DROP TABLE IF EXISTS CompostoNocivo;
22DROP TABLE IF EXISTS ValoreCompostoVolatile;
23DROP TABLE IF EXISTS RichiestaIntervento;
24DROP TABLE IF EXISTS Personale;
25DROP TABLE IF EXISTS StatoLocale;
26DROP TABLE IF EXISTS Allestimento;
27DROP TABLE IF EXISTS TipologiaAbbeveratoio;
28DROP TABLE IF EXISTS Abbeveratoio;
29DROP TABLE IF EXISTS Additivo;
30DROP TABLE IF EXISTS StoricoAbbeveratoio;
31DROP TABLE IF EXISTS Mangiatoia;
32DROP TABLE IF EXISTS TipologiaMangiatoia;
33DROP TABLE IF EXISTS CondizioneMangiatoia;
34DROP TABLE IF EXISTS ComponenteForaggio;
35DROP TABLE IF EXISTS Foraggio;
36DROP TABLE IF EXISTS Recinzione;
37DROP TABLE IF EXISTS AttivitaDiPascolo;
38
39/*HEALTCARE*/
40
41/*
42 Veterinario(Matricola, Nome, Cognome, Parcella, NumeroTelefono, Agriturismo)
43 Visita(Codice, DataProgrammata, DataEffettiva, Tipologia, Stato/Esito, Veterinario, Animale)
44 Patologia(Nome, ParteCorpo, Tipologia)
45 Farmaco(NomeCommerciale, PrincipioAttivo, Costo, Pezzi)
46 Indicazione(Farmaco, Patologia, Avita, Orari, Periodo, Dosaggio)
47 Esordio(Codice, Gravità, Cronica, DataGuarigione, DataEsordio, Visita, Patologia)
48 Terapia(Codice, DataInizio, DataFine, Esordio, Veterinario)
49 Prescrizione(Terapia, Farmaco, DataInizio, DataFine, Posologia, DescrizionePosologia)
50 EsameDiagnostico(Codice, Data, Nome, Veterinario, Visita)
51 TipoEsame(Nome, Macchinario, Descrizione)
52 Quarantena(Animale, DataInizio, DataFine, Locale)
53*/
54
55DROP TABLE IF EXISTS Veterinario;
56DROP TABLE IF EXISTS Visita;
57DROP TABLE IF EXISTS SchedaMedica;
58DROP TABLE IF EXISTS Patologia;
59DROP TABLE IF EXISTS Farmaco;
60DROP TABLE IF EXISTS Indicazione;
61DROP TABLE IF EXISTS Esordio;
62DROP TABLE IF EXISTS Terapia;
63DROP TABLE IF EXISTS Prescrizione;
64DROP TABLE IF EXISTS EsameDiagnostico;
65DROP TABLE IF EXISTS TipoEsame;
66DROP TABLE IF EXISTS Quarantena;
67
68/*RIPRODUZIONE*/
69
70/*
71 Riproduzione(Codice, Madre, Padre, DataEOra, Esito, Veterinario)
72 Gravidanza(Codice, Stato, DataAborto, Riproduzione, Veterinario)
73 Parto(Figlio, Gravidanza)
74*/
75
76DROP TABLE IF EXISTS Riproduzione;
77DROP TABLE IF EXISTS Parto;
78DROP TABLE IF EXISTS Gravidanza;
79
80/*PRODUZIONE*/
81
82/*
83 Mungitrice(Codice, Modello, Marca, CordinataX, CordinataY)
84 Mungitura(Codice, DataEOra, Quantità, Animale, Mungitrice)
85 ComponentiMungitura(Mungitura, Nome, Percentuale)
86 Conservazione(Mungitura, Silos)
87 Silos(Codice, Capacità)
88 Trasporto(Silos, DataEOra, Laboratorio, QuantitàLatte)
89 Laboratorio(Codice)
90 SegueRicetta(Laboratorio, FormaggioIdeale)
91 FormaggioIdeale(Nome, Ingredienti, Provenienza, GradoDiDeperibilità, Prezzo, D.O.P.)
92 PrezzoFormaggio(Nome, DataInizio, DataFine, Prezzo)
93 FaseIdeale(FormaggioIdeale, Numero, Durata, Procedura)
94 FormaggioProdotto(Codice, Nome, Stato, Laboratorio, Lotto)
95 FaseProduttiva(Formaggio, IstanteInizio, IstanteFine)
96 ParametroFaseProduttiva(Formaggio, InizioFase, Nome, Valore)
97 Conservazione(Formaggio, NumeroScaffale, Magazzino)
98 ScaffaleMagazzino(Numero, Magazzino)
99 Magazzino(Codice)
100 Stagionatura(Formaggio, NumeroScaffale, Cantina)
101 ScaffaleCantina(Numero, Cantina)
102 Cantina(Codice)
103 StatoCantina(Magazzino, Data, Temperatura, Umidità)
104 Lotto(Codice, DataScadenza, Data)
105 LavoraIn(Personale, Laboratorio)
106*/
107
108DROP TABLE IF EXISTS Mungitrice;
109DROP TABLE IF EXISTS Mungitura;
110DROP TABLE IF EXISTS ComponentiMungitura;
111DROP TABLE IF EXISTS TrasferimentoSilos;
112DROP TABLE IF EXISTS Silos;
113DROP TABLE IF EXISTS Trasporto;
114DROP TABLE IF EXISTS Laboratorio;
115DROP TABLE IF EXISTS SegueRicetta;
116DROP TABLE IF EXISTS ParametroFaseIdeale;
117DROP TABLE IF EXISTS FaseIdeale;
118DROP TABLE IF EXISTS FormaggioIdeale;
119DROP TABLE IF EXISTS PrezzoFormaggio;
120DROP TABLE IF EXISTS FormaggioProdotto;
121DROP TABLE IF EXISTS ParametroFaseProduttiva;
122DROP TABLE IF EXISTS FaseProduttiva;
123DROP TABLE IF EXISTS Conservazione;
124DROP TABLE IF EXISTS ScaffaleMagazzino;
125DROP TABLE IF EXISTS Magazzino;
126DROP TABLE IF EXISTS Stagionatura;
127DROP TABLE IF EXISTS ScaffaleCantina;
128DROP TABLE IF EXISTS Cantina;
129DROP TABLE IF EXISTS StatoCantina;
130DROP TABLE IF EXISTS Lotto;
131DROP TABLE IF EXISTS LavoraIn;
132
133/*STORE*/
134
135/*
136 Utente(NomeUtente, Nome, Cognome, NumeroTelefono, CartaDiCredito, CodiceFiscale, IndirizzoEmail, Password, DomandaSegreta, RispostaSegreta, Documento)
137 Documento(NumeroDocumento, DataScadenza, Tipologia, Ente)
138 Ordine(Codice, DataEOra, Stato, NumeroCivico, Cap, Via, Utente, Pagamento)
139 Cap(Cap, Città)
140 PagamentoOrdine(Codice, DataEOra)
141 Spedizione(Codice, Stato, DataConsegna, DataStimata, Ordine)
142 Tracking(Spedizione, Città, Via, NumeroCivico, DataEOraArrivo, DataEOraPartenza)
143 Hub(Città, Via, NumeroCivico)
144 Recensione(Utente, FormaggioIdeale, Data, QualitàPercepita, GradimentoGenerale, Gusto, Testo, Conservazione)
145 PrenotazioneFormaggio(Ordine, FormaggioIdeale, Quantità)
146 Prelievo(FormaggioProdotto, Ordine)
147 Reso(FormaggioProdotto, Ordine, Data)
148*/
149
150DROP TABLE IF EXISTS Utente;
151DROP TABLE IF EXISTS Documento;
152DROP TABLE IF EXISTS Ordine;
153DROP TABLE IF EXISTS Cap;
154DROP TABLE IF EXISTS PagamentoOrdine;
155DROP TABLE IF EXISTS Spedizione;
156DROP TABLE IF EXISTS Tracking;
157DROP TABLE IF EXISTS Hub;
158DROP TABLE IF EXISTS Recensione;
159DROP TABLE IF EXISTS PrenotazioneFormaggio;
160DROP TABLE IF EXISTS Prelievo;
161DROP TABLE IF EXISTS Reso;
162
163/*SOGGIORNO*/
164
165/*
166 Cliente(Codice, Nome, Cognome, NumeroDitelefono, NumeroCarta, PayPal)
167 TransazioneCliente(Cliente, Pagamento)
168 PagamentoPrenotazione(Codice, Tipologia, DataEOra)
169 TransazioneUtente(Utente, Pagamento)
170 Prenotazione(Codice, DataFine, DataInizio, Pagamento)
171 Occupazione(Stanza, Agriturismo, Prenotazione)
172 Stanza(NumeroStanza, Agriturismo, Tipololgia, CostoGiornaliero, Capienza)
173 ServizioPrenotato(Codice, Data, Prenotazione, Servizio)
174 Servizio(Nome, Disponibile, Costo)
175 Escursione(Codice, IstanteInizio, IstanteFine, ServizioPrenotato, Guida)
176 Sosta(Escursione, IstanteInizio, IstanteFine, NomeArea)
177*/
178
179DROP TABLE IF EXISTS Cliente;
180DROP TABLE IF EXISTS TransazioneCliente;
181DROP TABLE IF EXISTS PagamentoPrenotazione;
182DROP TABLE IF EXISTS TransazioneUtente;
183DROP TABLE IF EXISTS Occupazione;
184DROP TABLE IF EXISTS Prenotazione;
185DROP TABLE IF EXISTS Stanza;
186DROP TABLE IF EXISTS ServizioPrenotato;
187DROP TABLE IF EXISTS Servizio;
188DROP TABLE IF EXISTS Escursione;
189DROP TABLE IF EXISTS Sosta;
190
191/*RIDONDANZE*/
192
193/*
194 AttivitàMensile(Veterinario, Anno, Mese, NumeroVisite)
195 Calendario(Anno, Mese)
196 Specializzazione(Veterinario, Patologia, Punteggio)
197*/
198
199DROP TABLE IF EXISTS AttivitaMensile;
200DROP TABLE IF EXISTS Calendario;
201DROP TABLE IF EXISTS Specializzazione;
202
203/*Creazione DataBase*/
204
205CREATE TABLE Agriturismo
206(
207 Codice INT AUTO_INCREMENT NOT NULL,
208 Via VARCHAR(50) NOT NULL,
209 Citta VARCHAR(50) NOT NULL,
210 NumeroCivico INT NOT NULL,
211 PRIMARY KEY(Codice),
212 CONSTRAINT Controlli CHECK
213 (
214 (NumeroCivico > 0)
215 )
216)ENGINE=InnoDB DEFAULT CHARSET=latin1;
217
218/*ALLEVAMENTO*/
219
220CREATE TABLE Stalla
221(
222 Numero INT AUTO_INCREMENT NOT NULL,
223 Agriturismo INT NOT NULL
224 REFERENCES Agriturismo(Codice),
225 Lunghezza FLOAT NOT NULL,
226 Altezza FLOAT NOT NULL,
227 Larghezza FLOAT NOT NULL,
228 PRIMARY KEY(Numero, Agriturismo),
229 CONSTRAINT Controlli CHECK
230 (
231 Larghezza > 0 AND
232 Altezza > 0 AND
233 Lunghezza > 0
234 )
235)ENGINE=InnoDB DEFAULT CHARSET=latin1;
236
237CREATE TABLE Allestimento
238(
239 Specie VARCHAR(50) NOT NULL,
240 Temperatura FLOAT NOT NULL,
241 Umidita FLOAT NOT NULL,
242 TipoAbbeveratoi VARCHAR(50) NOT NULL,
243 NumeroAbbeveratoi INT NOT NULL,
244 TipoMangiatoie VARCHAR(50),
245 NumeroMangiatoie INT NOT NULL,
246 TipoDispositiviLuce VARCHAR(50) NOT NULL,
247 NumeroDispositiviLuce INT NOT NULL,
248 TipoCondizionatori VARCHAR(50),
249 NumeroCondizionatori INT NOT NULL,
250 PRIMARY KEY(Specie),
251 CONSTRAINT Controlli CHECK
252 (
253 (NumeroAbbeveratoi > 0) AND
254 (NumeroMangiatoie > 0) AND
255 (NumeroDispositiviLuce > 0) AND
256 (NumeroCondizionatori > 0) AND
257 (Umidita > 0 AND Umidita <= 100) /*E' una percentuale*/
258 )
259)ENGINE=InnoDB DEFAULT CHARSET=latin1;
260
261CREATE TABLE Locale
262(
263 Codice INT AUTO_INCREMENT NOT NULL,
264 Larghezza FLOAT NOT NULL,
265 Altezza FLOAT NOT NULL,
266 Lunghezza FLOAT NOT NULL,
267 OrientamentoFinestre CHAR(2) NOT NULL,
268 Agriturismo INT NOT NULL,
269 NumeroStalla INT NOT NULL,
270 Specie VARCHAR(50) NOT NULL
271 REFERENCES Allestimento(Specie),
272 PRIMARY KEY(Codice),
273 FOREIGN KEY(NumeroStalla, Agriturismo)
274 REFERENCES Stalla(Numero, Agriturismo),
275 INDEX(Larghezza, Altezza, Lunghezza, Specie), /*Per essere usata come foreign key senza essere una primary key*/
276 CONSTRAINT Controlli CHECK
277 (
278 (
279 Larghezza > 0 AND
280 Altezza > 0 AND
281 Lunghezza > 0
282 )
283 AND
284 (
285 OrientamentoFinestre = "N" OR
286 OrientamentoFinestre = "S" OR
287 OrientamentoFinestre = "E" OR
288 OrientamentoFinestre = "O"
289 )
290 )
291)ENGINE=InnoDB DEFAULT CHARSET=latin1;
292
293CREATE TABLE Pavimento
294(
295 Specie VARCHAR(50) NOT NULL
296 REFERENCES Locale(Specie),
297 TipologiaPavimenti VARCHAR(50) NOT NULL,
298 PRIMARY KEY(Specie)
299)ENGINE=InnoDB DEFAULT CHARSET=latin1;
300
301CREATE TABLE Capienza
302(
303 Larghezza FLOAT NOT NULL,
304 Altezza FLOAT NOT NULL,
305 Lunghezza FLOAT NOT NULL,
306 Specie VARCHAR(50) NOT NULL,
307 Capienza INT NOT NULL,
308 PRIMARY KEY(Larghezza, Altezza, Lunghezza, Specie),
309 FOREIGN KEY(Larghezza, Altezza, Lunghezza, Specie)
310 REFERENCES Locale(Larghezza, Altezza, Lunghezza, Specie)
311)ENGINE=InnoDB DEFAULT CHARSET=latin1;
312
313CREATE TABLE Animale
314(
315 Codice INT AUTO_INCREMENT NOT NULL,
316 Sesso CHAR NOT NULL,
317 Razza VARCHAR(50) NOT NULL,
318 Specie VARCHAR(50) NOT NULL,
319 DataNascita DATE NOT NULL,
320 Locale INT NOT NULL
321 REFERENCES Locale(Codice),
322 PRIMARY KEY(Codice),
323 INDEX(Codice, Locale), /*Serve per l'analytics sul comportamento degli animali*/
324 CONSTRAINT Controlli CHECK
325 (
326 (Sesso = "F" OR Sesso = "M")
327 )
328)ENGINE=InnoDB DEFAULT CHARSET=latin1;
329
330CREATE TABLE Famiglia
331(
332 Specie VARCHAR(50) NOT NULL
333 REFERENCES Animale(Famiglia),
334 Famiglia VARCHAR(50) NOT NULL,
335 PRIMARY KEY(Specie)
336)ENGINE=InnoDB DEFAULT CHARSET=latin1;
337
338CREATE TABLE Fornitore
339(
340 PartitaIva CHAR(11) NOT NULL,
341 RagioneSociale VARCHAR(50) NOT NULL,
342 Nome VARCHAR(50) NOT NULL,
343 Citta VARCHAR(50) NOT NULL,
344 Via VARCHAR(50) NOT NULL,
345 NumeroCivico INT NOT NULL,
346 PRIMARY KEY(PartitaIva),
347 CONSTRAINT Controlli CHECK
348 (
349 (NumeroCivico > 0)
350 )
351)ENGINE=InnoDB DEFAULT CHARSET=latin1;
352
353CREATE TABLE Vendita
354(
355 Animale INT NOT NULL
356 REFERENCES Animale(Codice),
357 Fornitore CHAR(11) NOT NULL
358 REFERENCES Fornitore(PartitaIva),
359 DataAcquisto DATE NOT NULL,
360 DataArrivo DATE NOT NULL,
361 PRIMARY KEY(Animale, Fornitore)
362 /*Controlli sulle date via trigger*/
363)ENGINE=InnoDB DEFAULT CHARSET=latin1;
364
365CREATE TABLE Posizione
366(
367 Animale INT NOT NULL
368 REFERENCES Animale(Codice),
369 DataEOra TIMESTAMP NOT NULL,
370 CordinataX FLOAT NOT NULL,
371 CordinataY FLOAT NOT NULL,
372 PRIMARY KEY(Animale, DataEOra)
373)ENGINE=InnoDB DEFAULT CHARSET=latin1;
374
375CREATE TABLE Abbeveratoio
376(
377 Codice INT AUTO_INCREMENT NOT NULL,
378 Locale INT NOT NULL
379 REFERENCES Locale(Codice),
380 PRIMARY KEY(Codice)
381)ENGINE=InnoDB DEFAULT CHARSET=latin1;
382
383CREATE TABLE TipologiaAbbeveratoio
384(
385 Locale INT NOT NULL
386 REFERENCES Abbeveratoio(Locale),
387 Tipologia VARCHAR(50) NOT NULL,
388 PRIMARY KEY(Locale)
389)ENGINE=InnoDB DEFAULT CHARSET=latin1;
390
391CREATE TABLE StoricoAbbeveratoio
392(
393 Abbeveratoio INT NOT NULL
394 REFERENCES Abbeveratoio(Codice),
395 `Data` DATE NOT NULL,
396 PRIMARY KEY(Abbeveratoio, `Data`)
397)ENGINE=InnoDB DEFAULT CHARSET=latin1;
398
399CREATE TABLE Additivo
400(
401 Abbeveratoio INT NOT NULL,
402 `Data` DATE NOT NULL,
403 Nome VARCHAR(50) NOT NULL,
404 Quantita FLOAT NOT NULL,
405 PRIMARY KEY(Abbeveratoio, Nome, `Data`),
406 FOREIGN KEY(Abbeveratoio, `Data`)
407 REFERENCES StoricoAbbeveratoio(Abbeveratoio, `Data`),
408 CONSTRAINT Controlli CHECK
409 (
410 (Quantita > 0 AND Quantita <= 100) /*E' una percentuale*/
411 )
412)ENGINE=InnoDB DEFAULT CHARSET=latin1;
413
414CREATE TABLE Mangiatoia
415(
416 Codice INT AUTO_INCREMENT NOT NULL,
417 Locale INT NOT NULL
418 REFERENCES Locale(Codice),
419 PRIMARY KEY(Codice)
420)ENGINE=InnoDB DEFAULT CHARSET=latin1;
421
422CREATE TABLE TipologiaMangiatoia
423(
424 Locale INT NOT NULL
425 REFERENCES Mangiatoia(Locale),
426 Tipologia VARCHAR(50) NOT NULL,
427 PRIMARY KEY(Locale)
428)ENGINE=InnoDB DEFAULT CHARSET=latin1;
429
430CREATE TABLE CondizioneMangiatoia
431(
432 Mangiatoia INT NOT NULL
433 REFERENCES Mangiatoia(Codice),
434 DataEOra TIMESTAMP NOT NULL,
435 QuantitaResidua FLOAT NOT NULL,
436 Criticita BOOL NOT NULL DEFAULT FALSE,
437 PRIMARY KEY(Mangiatoia, DataEOra),
438 CONSTRAINT Controlli CHECK
439 (
440 (QuantitaResidua >= 0)
441 /*Controlli sulle date via trigger*/
442 )
443)ENGINE=InnoDB DEFAULT CHARSET=latin1;
444
445CREATE TABLE Foraggio
446(
447 Mangiatoia INT NOT NULL
448 REFERENCES Mangiatoia(Codice),
449 OrarioSomministrazione TIMESTAMP NOT NULL,
450 Quantita FLOAT NOT NULL,
451 Calorie FLOAT NOT NULL,
452 Stato VARCHAR(50) NOT NULL,
453 PRIMARY KEY(Mangiatoia, OrarioSomministrazione),
454 CONSTRAINT Controlli CHECK
455 (
456 (Quantita >= 0) AND
457 (Calorie >= 0) AND
458 (Stato = "Insilato" OR Stato = "Fieno")
459 )
460)ENGINE=InnoDB DEFAULT CHARSET=latin1;
461
462CREATE TABLE ComponenteForaggio
463(
464 Mangiatoia INT NOT NULL,
465 OrarioForaggio TIMESTAMP NOT NULL,
466 Nome VARCHAR(50) NOT NULL,
467 Percentuale FLOAT NOT NULL,
468 PRIMARY KEY(Mangiatoia, OrarioForaggio, Nome),
469 FOREIGN KEY(Mangiatoia, OrarioForaggio)
470 REFERENCES Foraggio(Mangiatoia, OrarioSomministrazione),
471 CONSTRAINT Controlli CHECK
472 (
473 (Percentuale > 0 AND Percentuale <= 100) /*E' una percentuale*/
474 )
475)ENGINE=InnoDB DEFAULT CHARSET=latin1;
476
477CREATE TABLE StatoLocale
478(
479 Locale INT NOT NULL
480 REFERENCES Locale(Codice),
481 OrarioRilevazione TIMESTAMP NOT NULL,
482 LivelloDiSporcizia FLOAT NOT NULL,
483 Temperatura FLOAT NOT NULL,
484 Umidita FLOAT NOT NULL,
485 PRIMARY KEY(Locale, OrarioRilevazione),
486 CONSTRAINT Controlli CHECK
487 (
488 (LivelloDiSporcizia > 0 AND LivelloDiSporcizia <= 100) AND /*E' una percentuale*/
489 (Umidita > 0 AND Umidita <= 100) /*E' una percentuale*/
490 )
491)ENGINE=InnoDB DEFAULT CHARSET=latin1;
492
493CREATE TABLE ValoreCompostoVolatile
494(
495 Locale INT NOT NULL,
496 OrarioRilevazione TIMESTAMP NOT NULL,
497 Nome VARCHAR(50) NOT NULL,
498 Valore FLOAT NOT NULL,
499 PRIMARY KEY(Locale, OrarioRilevazione, Nome),
500 FOREIGN KEY(Locale, OrarioRilevazione)
501 REFERENCES StatoLocale(Locale, OrarioRilevazione),
502 CONSTRAINT Controlli CHECK
503 (
504 (Valore > 0 AND Valore <= 100)
505 )
506)ENGINE=InnoDB DEFAULT CHARSET=latin1;
507
508CREATE TABLE CompostoNocivo
509(
510 Nome VARCHAR(50) NOT NULL,
511 Nocivo BOOL NOT NULL,
512 PRIMARY KEY(Nome)
513)ENGINE=InnoDB DEFAULT CHARSET=latin1;
514
515CREATE TABLE Personale
516(
517 Codice INT AUTO_INCREMENT NOT NULL,
518 Nome VARCHAR(50) NOT NULL,
519 Cognome VARCHAR(50) NOT NULL,
520 NumeroDiTelefono BIGINT NOT NULL,
521 Mansione VARCHAR(50) NOT NULL,
522 Agriturismo INT NOT NULL
523 REFERENCES Agriturismo(Codice),
524 PRIMARY KEY(Codice),
525 CONSTRAINT Controlli CHECK
526 (
527 (Mansione = "Pulizie" OR Mansione = "Formaggio" OR Mansione = "Guida")
528 )
529)ENGINE=InnoDB DEFAULT CHARSET=latin1;
530
531CREATE TABLE RichiestaIntervento
532(
533 Locale INT NOT NULL,
534 OrarioRilevazione TIMESTAMP NOT NULL,
535 Stato VARCHAR(50) NOT NULL,
536 Personale INT NOT NULL,
537 PRIMARY KEY(Locale, OrarioRilevazione, Personale),
538 FOREIGN KEY(Locale, OrarioRilevazione)
539 REFERENCES StatoLocale(Locale, OrarioRilevazione),
540 FOREIGN KEY(Personale)
541 REFERENCES Personale(Codice),
542 CONSTRAINT Controlli CHECK
543 (
544 (Stato = "Richiesto" OR Stato = "Effettuato")
545 )
546)ENGINE=InnoDB DEFAULT CHARSET=latin1;
547
548CREATE TABLE Recinzione
549(
550 Codice INT AUTO_INCREMENT NOT NULL,
551 AngoloDelimiter1 FLOAT NOT NULL,
552 AngoloDelimiter2 FLOAT NOT NULL,
553 CoordinataXCentro FLOAT NOT NULL,
554 CoordinataYCentro FLOAT NOT NULL,
555 Raggio FLOAT NOT NULL,
556 Agriturismo INT NOT NULL
557 REFERENCES Agriturismo(Codice),
558 PRIMARY KEY(Codice),
559 CONSTRAINT Controlli CHECK
560 (
561 (AngoloDelimiter1 > 0 AND AngoloDelimiter1 < 180) AND
562 (AngoloDelimiter2 > 0 AND AngoloDelimiter2 < 180)
563 )
564)ENGINE=InnoDB DEFAULT CHARSET=latin1;
565
566CREATE TABLE AttivitaDiPascolo
567(
568 Locale INT NOT NULL
569 REFERENCES Locale(Codice),
570 IstanteInizio TIMESTAMP NOT NULL,
571 IstanteFine TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
572 Recinzione INT NOT NULL
573 REFERENCES Recinzione(Codice),
574 PRIMARY KEY(Locale, IstanteInizio),
575 CONSTRAINT Controlli CHECK
576 (
577 (IstanteFine > IstanteInizio)
578 )
579)ENGINE=InnoDB DEFAULT CHARSET=latin1;
580
581/*HEALTCARE*/
582
583/*
584 Veterinario(Matricola, Nome, Cognome, Parcella, NumeroTelefono, Agriturismo)
585 Visita(Codice, DataProgrammata, DataEffettiva, Tipologia, Stato/Esito, Veterinario, Animale)
586 SchedaMedica(Visita, Parametro, Valore, Descrizione)
587 Patologia(Nome, ParteCorpo, Tipologia)
588 Farmaco(NomeCommerciale, PrincipioAttivo, Costo, Pezzi)
589 Indicazione(Farmaco, Patologia, Avita, Orari, Periodo, Dosaggio)
590 Esordio(Codice, Gravità, Cronica, DataEsordio, DataGuarigione, Visita, Patologia)
591 Terapia(Codice, DataInizio, DataFine, Esordio, Veterinario)
592 Prescrizione(Terapia, Farmaco, DataInizio, DataFine, Posologia, DescrizionePosologia)
593 EsameDiagnostico(Codice, Data, Nome, Veterinario, Visita)
594 TipoEsame(Nome, Macchinario, Descrizione)
595 Quarantena(Animale, DataInizio, DataFine, Locale)
596*/
597
598CREATE TABLE Veterinario
599(
600 Matricola INT AUTO_INCREMENT NOT NULL,
601 Nome VARCHAR(50) NOT NULL,
602 Cognome VARCHAR(50) NOT NULL,
603 Parcella FLOAT NOT NULL,
604 NumeroTelefono BIGINT NOT NULL,
605 Agriturismo INT NOT NULL
606 REFERENCES Agriturismo(Codice),
607 PRIMARY KEY(Matricola),
608 CONSTRAINT Controlli CHECK
609 (
610 (Parcella > 0)
611 )
612)ENGINE=InnoDB DEFAULT CHARSET=latin1;
613
614CREATE TABLE Visita
615(
616 Codice INT AUTO_INCREMENT NOT NULL,
617 DataProgrammata DATE NOT NULL,
618 DataEffettiva DATE,
619 Tipologia VARCHAR(50) NOT NULL,
620 StatoEsito VARCHAR(50) NOT NULL,
621 Veterinario INT NOT NULL
622 REFERENCES Veterinario(Matricola),
623 Animale INT NOT NULL
624 REFERENCES Animale(Codice),
625 PRIMARY KEY(Codice),
626 UNIQUE(DataProgrammata, Animale),
627 INDEX(Codice, DataProgrammata, Veterinario), /*Mi serve per poter essere usata come FOREIGN KEY dalla SchedaGestazione*/
628 CONSTRAINT Controlli CHECK
629 (
630 (
631 (StatoEsito = "Programmato") OR
632 (StatoEsito = "Positivo") OR
633 (StatoEsito = "Negativo")
634 )
635 AND
636 (
637 (Tipologia = "ControlloPeriodico") OR
638 (Tipologia = "ControlloGestazione") OR
639 (Tipologia = "Urgenza")
640 )
641 )
642)ENGINE=InnoDB DEFAULT CHARSET=latin1;
643
644CREATE TABLE SchedaMedica
645(
646 Visita INT NOT NULL
647 REFERENCES Visita(Codice),
648 Parametro VARCHAR(50) NOT NULL,
649 Valore FLOAT,
650 Descrizione VARCHAR(400),
651 PRIMARY KEY(Visita, Parametro)
652)ENGINE=InnoDB DEFAULT CHARSET=latin1;
653
654CREATE TABLE Patologia
655(
656 Nome VARCHAR(50) NOT NULL,
657 ParteCorpo VARCHAR(50),
658 Tipologia VARCHAR(50) NOT NULL,
659 PRIMARY KEY(Nome),
660 CONSTRAINT Controlli CHECK
661 (
662 (Tipologia = "DisturboComportamentale" AND ParteCorpo IS NULL) OR
663 (Tipologia = "Lesione" AND ParteCorpo IS NOT NULL) OR
664 (Tipologia = "Malattia")
665 )
666)ENGINE=InnoDB DEFAULT CHARSET=latin1;
667
668CREATE TABLE Farmaco
669(
670 NomeCommerciale VARCHAR(50) NOT NULL,
671 PrincipioAttivo VARCHAR(50) NOT NULL,
672 Costo FLOAT NOT NULL,
673 Pezzi INT NOT NULL,
674 PRIMARY KEY(NomeCommerciale),
675 CONSTRAINT Controlli CHECK
676 (
677 (Costo > 0) AND
678 (Pezzi > 0)
679 )
680)ENGINE=InnoDB DEFAULT CHARSET=latin1;
681
682CREATE TABLE Indicazione
683(
684 Farmaco VARCHAR(50) NOT NULL
685 REFERENCES Farmaco(NomeCommerciale),
686 Patologia VARCHAR(50) NOT NULL
687 REFERENCES Patologia(Nome),
688 Avita BOOL NOT NULL,
689 Orari VARCHAR(400) NOT NULL, /*E' una stringa contenente gli orari di uso consigliati*/
690 Periodo INT NOT NULL,
691 Dosaggio INT NOT NULL,
692 PRIMARY KEY(Farmaco, Patologia),
693 CONSTRAINT Controlli CHECK
694 (
695 (Periodo > 0) AND
696 (Dosaggio > 0)
697 )
698)ENGINE=InnoDB DEFAULT CHARSET=latin1;
699
700CREATE TABLE Esordio
701(
702 Codice INT AUTO_INCREMENT NOT NULL,
703 Gravita FLOAT NOT NULL,
704 Cronica BOOL NOT NULL,
705 DataEsordio DATE NOT NULL,
706 DataGuarigione DATE,
707 Visita INT NOT NULL
708 REFERENCES Visita(Codice),
709 Patologia VARCHAR(50) NOT NULL
710 REFERENCES Patologia(Nome),
711 PRIMARY KEY(Codice),
712 UNIQUE(Visita, DataEsordio, Patologia),
713 CONSTRAINT Controlli CHECK
714 (
715 (Gravita > 0 AND Gravita <= 100) AND /*E' una percentuale*/
716 (
717 (DataGuarigione IS NULL)
718 OR
719 (DataGuarigione IS NOT NULL AND DataGuarigione > DataEsordio)
720 )
721 )
722)ENGINE=InnoDB DEFAULT CHARSET=latin1;
723
724CREATE TABLE Terapia
725(
726 Codice INT AUTO_INCREMENT NOT NULL,
727 DataInizio DATE NOT NULL,
728 DataFine DATE, /*L'animale può essere malato a vita*/
729 Esordio INT NOT NULL
730 REFERENCES Esordio(Codice),
731 Veterinario INT NOT NULL
732 REFERENCES Veterinario(Codice),
733 PRIMARY KEY(Codice),
734 UNIQUE(Esordio, DataInizio),
735 CONSTRAINT Controlli CHECK
736 (
737 (DataFine IS NULL)
738 OR
739 (DataFine IS NOT NULL AND DataFine > DataInizio)
740 )
741)ENGINE=InnoDB DEFAULT CHARSET=latin1;
742
743CREATE TABLE Prescrizione
744(
745 Terapia INT NOT NULL
746 REFERENCES Terapia(Codice),
747 Farmaco VARCHAR(50) NOT NULL
748 REFERENCES Farmaco(NomeCommerciale),
749 DataInizio DATE NOT NULL,
750 DataFine DATE,
751 Posologia INT NOT NULL,
752 DescrizionePosologia VARCHAR(400) NOT NULL,
753 PRIMARY KEY(Terapia, Farmaco, DataInizio),
754 CONSTRAINT Controlli CHECK
755 (
756 (Posologia > 0) AND
757 (
758 (DataFine IS NULL)
759 OR
760 (DataFine IS NOT NULL AND DataFine > DataInizio)
761 )
762 )
763)ENGINE=InnoDB DEFAULT CHARSET=latin1;
764
765CREATE TABLE EsameDiagnostico
766(
767 Codice INT AUTO_INCREMENT NOT NULL,
768 `Data` DATE NOT NULL,
769 Nome VARCHAR(50) NOT NULL,
770 Veterinario INT NOT NULL
771 REFERENCES Veterinario(Matricola),
772 Visita INT NOT NULL
773 REFERENCES Visita(Codice),
774 PRIMARY KEY(Codice),
775 UNIQUE(Visita),
776 INDEX(Nome) /*Per poter essere usata come Foreign key da parte di "TipoEsame"*/
777)ENGINE=InnoDB DEFAULT CHARSET=latin1;
778
779CREATE TABLE TipoEsame
780(
781 Nome VARCHAR(50) NOT NULL
782 REFERENCES EsameDiagnostico(Nome),
783 Macchinario VARCHAR(50) NOT NULL,
784 Descrizione VARCHAR(400) NOT NULL,
785 PRIMARY KEY(Nome)
786)ENGINE=InnoDB DEFAULT CHARSET=latin1;
787
788CREATE TABLE Quarantena
789(
790 Animale INT NOT NULL
791 REFERENCES Animale(Codice),
792 DataInizio DATE NOT NULL,
793 DataFine DATE,
794 Locale INT NOT NULL
795 REFERENCES Locale(Codice),
796 CONSTRAINT Controlli CHECK
797 (
798 (DataFine IS NULL)
799 OR
800 (DataFine IS NOT NULL AND DataFine > DataInizio)
801 )
802)ENGINE=InnoDB DEFAULT CHARSET=latin1;
803
804/*RIPRODUZIONE*/
805
806/*
807 Riproduzione(Codice, Madre, Padre, DataEOra, Esito, Veterinario)
808 Gravidanza(Codice, Stato, DataAborto, Riproduzione, Veterinario)
809 Parto(Figlio, Gravidanza)
810*/
811
812CREATE TABLE Riproduzione
813(
814 Codice INT AUTO_INCREMENT NOT NULL,
815 Madre INT NOT NULL
816 REFERENCES Animale(Codice),
817 Padre INT NOT NULL
818 REFERENCES Animale(Codice),
819 DataEOra TIMESTAMP NOT NULL,
820 Esito VARCHAR(50) NOT NULL,
821 Veterinario INT NOT NULL
822 REFERENCES Veterinario(Matricola),
823 PRIMARY KEY(Codice),
824 UNIQUE(Madre, Padre, DataEOra),
825 CONSTRAINT Controlli CHECK
826 (
827 (Esito = "Successo") OR
828 (Esito = "Insuccesso")
829 )
830)ENGINE=InnoDB DEFAULT CHARSET=latin1;
831
832CREATE TABLE Gravidanza
833(
834 Codice INT AUTO_INCREMENT NOT NULL,
835 Stato VARCHAR(50) NOT NULL,
836 DataAborto DATE,
837 Riproduzione INT NOT NULL
838 REFERENCES Riproduzione(Codice),
839 Veterinario INT NOT NULL
840 REFERENCES Veterinario(Matricola),
841 PRIMARY KEY(Codice),
842 INDEX(Riproduzione, Veterinario), /*Per creare la scheda gestazione*/
843 CONSTRAINT Controlli CHECK
844 (
845 (Stato = "InCorso") OR
846 (Stato = "Insuccesso") OR
847 (Stato = "Successo")
848 )
849)ENGINE=InnoDB DEFAULT CHARSET=latin1;
850
851CREATE TABLE Parto
852(
853 Figlio INT NOT NULL
854 REFERENCES Animale(Codice),
855 Gravidanza INT NOT NULL
856 REFERENCES Gravidanza(Codice),
857 PRIMARY KEY(Figlio, Gravidanza)
858)ENGINE=InnoDB DEFAULT CHARSET=latin1;
859
860/*Produzione*/
861
862/*
863 Mungitrice(Codice, Modello, Marca, CordinataX, CordinataY)
864 Mungitura(Codice, DataEOra, Animale, Mungitrice)
865 ComponentiMungitura(Mungitura, Nome, Quantita, Anomalo)
866 TrasferimentoSilos(Mungitura, Silos)
867 Silos(Codice, Capacità)
868 Trasporto(Silos, DataEOra, Laboratorio, Quantita)
869 Laboratorio(Codice)
870 SegueRicetta(Laboratorio, FormaggioIdeale)
871 FormaggioIdeale(Nome, Ingredienti, Provenienza, GradoDiDeperibilità, Prezzo, D.O.P.)
872 PrezzoFormaggio(Nome, DataInizio, DataFine, Prezzo)
873 FaseIdeale(FormaggioIdeale, Numero, Durata, Procedura)
874 ParametroFaseIdeale(FormaggioIdeale, NumeroFase, Nome, Valore)
875 FormaggioProdotto(Codice, Nome, Stato, Laboratorio, Lotto)
876 FaseProduttiva(Formaggio, IstanteInizio, IstanteFine)
877 ParametroFaseProduttiva(Formaggio, InizioFase, Nome, Valore)
878 Conservazione(Formaggio, NumeroScaffale, Magazzino)
879 ScaffaleMagazzino(Numero, Magazzino)
880 Magazzino(Codice)
881 Stagionatura(Formaggio, NumeroScaffale, Cantina)
882 ScaffaleCantina(Numero, Cantina)
883 Cantina(Codice)
884 StatoCantina(Cantina, Data, Temperatura, Umidità)
885 Lotto(Codice, DataScadenza, Data)
886 LavoraIn(Personale, Laboratorio)
887*/
888
889CREATE TABLE Mungitrice
890(
891 Codice INT AUTO_INCREMENT NOT NULL,
892 Modello VARCHAR(50) NOT NULL,
893 Marca VARCHAR(50) NOT NULL,
894 CordinataX FLOAT NOT NULL,
895 CordinataY FLOAT NOT NULL,
896 /*Trigger per impedire che due mungitrici abbiano le stesse cordinate*/
897 PRIMARY KEY(Codice),
898 UNIQUE(CordinataX, CordinataY)
899)ENGINE=InnoDB DEFAULT CHARSET=latin1;
900
901 CREATE TABLE Mungitura
902 (
903 Codice INT AUTO_INCREMENT NOT NULL,
904 DataEOra TIMESTAMP NOT NULL,
905 Animale INT NOT NULL
906 REFERENCES Animale(Codice),
907 Mungitrice INT NOT NULL
908 REFERENCES Mungitrice(Codice),
909 PRIMARY KEY(Codice),
910 UNIQUE(Animale, DataEOra)
911)ENGINE=InnoDB DEFAULT CHARSET=latin1;
912
913CREATE TABLE ComponentiMungitura
914(
915 Mungitura INT NOT NULL
916 REFERENCES Mungitura(Codice),
917 Nome VARCHAR(50) NOT NULL,
918 Quantita FLOAT NOT NULL,
919 Anomalo BOOL NOT NULL,
920 PRIMARY KEY(Mungitura, Nome),
921 CONSTRAINT Controlli CHECK
922 (
923 (Quantita > 0)
924 )
925)ENGINE=InnoDB DEFAULT CHARSET=latin1;
926
927CREATE TABLE Silos
928(
929 Codice INT AUTO_INCREMENT NOT NULL,
930 Capacita FLOAT NOT NULL,
931 PRIMARY KEY(Codice),
932 CONSTRAINT Controlli CHECK
933 (
934 (Capacita > 0)
935 )
936)ENGINE=InnoDB DEFAULT CHARSET=latin1;
937
938CREATE TABLE TrasferimentoSilos
939(
940 Mungitura INT NOT NULL
941 REFERENCES Mungitura(Codice),
942 Silos INT NOT NULL
943 REFERENCES Silos(Codice),
944 PRIMARY KEY(Mungitura, Silos)
945)ENGINE=InnoDB DEFAULT CHARSET=latin1;
946
947CREATE TABLE Trasporto
948(
949 Silos INT NOT NULL
950 REFERENCES Silos(Codice),
951 DataEOra TIMESTAMP NOT NULL,
952 Laboratorio INT NOT NULL
953 REFERENCES Laboratorio(Codice),
954 Quantita FLOAT NOT NULL,
955 PRIMARY KEY(Silos, DataEOra, Laboratorio),
956 CONSTRAINT Controlli CHECK
957 (
958 (Quantita > 0)
959 )
960)ENGINE=InnoDB DEFAULT CHARSET=latin1;
961
962CREATE TABLE Laboratorio
963(
964 Codice INT AUTO_INCREMENT NOT NULL,
965 PRIMARY KEY(Codice)
966)ENGINE=InnoDB DEFAULT CHARSET=latin1;
967
968CREATE TABLE FormaggioIdeale
969(
970 Nome VARCHAR(50) NOT NULL,
971 Ingredienti VARCHAR(400) NOT NULL,
972 Provenienza VARCHAR(50) NOT NULL,
973 GradoDiDeperibilita VARCHAR(50) NOT NULL,
974 DOP BOOL NOT NULL,
975 PRIMARY KEY(Nome),
976 CONSTRAINT Controlli CHECK
977 (
978 (
979 (GradoDiDeperibilita = "Basso") OR
980 (GradoDiDeperibilita = "Medio") OR
981 (GradoDiDeperibilita = "Alto")
982 )
983 )
984)ENGINE=InnoDB DEFAULT CHARSET=latin1;
985
986CREATE TABLE PrezzoFormaggio
987(
988 FormaggioIdeale VARCHAR(50) NOT NULL
989 REFERENCES FormaggioIdeale(Nome),
990 `Data` DATE NOT NULL,
991 Prezzo FLOAT NOT NULL,
992 PRIMARY KEY(FormaggioIdeale, `Data`),
993 CONSTRAINT Controlli CHECK
994 (
995 (Prezzo > 0)
996 )
997)ENGINE=InnoDB DEFAULT CHARSET=latin1;
998
999CREATE TABLE SegueRicetta
1000(
1001 Laboratorio INT NOT NULL
1002 REFERENCES Laboratorio(Codice),
1003 FormaggioIdeale VARCHAR(50) NOT NULL
1004 REFERENCES FormaggioIdeale(Nome),
1005 PRIMARY KEY(Laboratorio, FormaggioIdeale)
1006)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1007
1008CREATE TABLE FaseIdeale
1009(
1010 FormaggioIdeale VARCHAR(50) NOT NULL
1011 REFERENCES FormaggioIdeale(Nome),
1012 Numero INT NOT NULL,
1013 Durata TIME NOT NULL,
1014 Procedura VARCHAR(400) NOT NULL,
1015 PRIMARY KEY(FormaggioIdeale, Numero),
1016 CONSTRAINT Controlli CHECK
1017 (
1018 (Numero > 0) AND
1019 (Durata > "00:00:00")
1020 )
1021)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1022
1023CREATE TABLE ParametroFaseIdeale
1024(
1025 FormaggioIdeale VARCHAR(50) NOT NULL,
1026 NumeroFase INT NOT NULL,
1027 Nome VARCHAR(50) NOT NULL,
1028 Valore FLOAT NOT NULL,
1029 PRIMARY KEY(FormaggioIdeale, NumeroFase, Nome),
1030 FOREIGN KEY(FormaggioIdeale, NumeroFase)
1031 REFERENCES FaseIdeale(FormaggioIdeale, Numero),
1032 CONSTRAINT Controlli CHECK
1033 (Valore > 0)
1034)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1035
1036CREATE TABLE FormaggioProdotto
1037(
1038 Codice INT AUTO_INCREMENT NOT NULL,
1039 Nome VARCHAR(50) NOT NULL
1040 REFERENCES FormaggioIdeale(Nome),
1041 Stato VARCHAR(50) NOT NULL,
1042 Laboratorio INT NOT NULL
1043 REFERENCES Laboratorio(Codice),
1044 Lotto INT NOT NULL
1045 REFERENCES Lotto(Codice),
1046 PRIMARY KEY(Codice),
1047 CONSTRAINT Controlli CHECK
1048 (
1049 (Stato = "Lavorazione") OR
1050 (Stato = "Stagionatura") OR
1051 (Stato = "Non idoneo") OR
1052 (Stato = "Pronto") OR
1053 (Stato = "Venduto")
1054 )
1055)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1056
1057CREATE TABLE FaseProduttiva
1058(
1059 Formaggio INT NOT NULL
1060 REFERENCES FormaggioProdotto(Codice),
1061 IstanteInizio TIMESTAMP NOT NULL,
1062 IstanteFine TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
1063 PRIMARY KEY(Formaggio, IstanteInizio),
1064 CONSTRAINT Controlli CHECK
1065 (
1066 (IstanteFine IS NULL)
1067 OR
1068 (IstanteFine IS NOT NULL AND IstanteFine > IstanteInizio)
1069 )
1070)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1071
1072CREATE TABLE ParametroFaseProduttiva
1073(
1074 Formaggio INT NOT NULL,
1075 InizioFase TIMESTAMP NOT NULL,
1076 Nome VARCHAR(50) NOT NULL,
1077 Valore FLOAT NOT NULL,
1078 PRIMARY KEY(Formaggio, InizioFase, Nome),
1079 FOREIGN KEY(Formaggio, InizioFase)
1080 REFERENCES FaseProduttiva(Formaggio, IstanteInizio)
1081)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1082
1083CREATE TABLE Magazzino
1084(
1085 Codice INT AUTO_INCREMENT NOT NULL,
1086 PRIMARY KEY(Codice)
1087)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1088
1089CREATE TABLE ScaffaleMagazzino
1090(
1091 Numero INT AUTO_INCREMENT NOT NULL,
1092 Magazzino INT NOT NULL
1093 REFERENCES Magazzino(Codice),
1094 PRIMARY KEY(Numero, Magazzino)
1095)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1096
1097CREATE TABLE Conservazione
1098(
1099 Formaggio INT NOT NULL
1100 REFERENCES FormaggioProdotto(Codice),
1101 NumeroScaffale INT NOT NULL,
1102 Magazzino INT NOT NULL,
1103 FOREIGN KEY(NumeroScaffale, Magazzino)
1104 REFERENCES ScaffaleMagazzino(Numero, Magazzino),
1105 PRIMARY KEY(Formaggio, NumeroScaffale, Magazzino)
1106)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1107
1108CREATE TABLE Cantina
1109(
1110 Codice INT AUTO_INCREMENT NOT NULL,
1111 PRIMARY KEY(Codice)
1112)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1113
1114CREATE TABLE ScaffaleCantina
1115(
1116 Numero INT AUTO_INCREMENT NOT NULL,
1117 Cantina INT NOT NULL
1118 REFERENCES Cantina(Codice),
1119 PRIMARY KEY(Numero, Cantina)
1120)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1121
1122CREATE TABLE Stagionatura
1123(
1124 Formaggio INT NOT NULL
1125 REFERENCES FormaggioProdotto(Codice),
1126 NumeroScaffale INT NOT NULL,
1127 Cantina INT NOT NULL,
1128 FOREIGN KEY(NumeroScaffale, Cantina)
1129 REFERENCES ScaffaleCantina(Numero, Cantina),
1130 PRIMARY KEY(Formaggio, NumeroScaffale, Cantina)
1131)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1132
1133CREATE TABLE StatoCantina
1134(
1135 Cantina INT NOT NULL
1136 REFERENCES Cantina(Codice),
1137 `Data` DATE NOT NULL,
1138 Temperatura FLOAT NOT NULL,
1139 Umidita FLOAT NOT NULL,
1140 PRIMARY KEY(Cantina, `Data`),
1141 CONSTRAINT Controlli CHECK
1142 (
1143 (Umidita >= 0 AND Umidita <= 100) /*E' una percentuale*/
1144 )
1145)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1146
1147CREATE TABLE Lotto
1148(
1149 Codice INT AUTO_INCREMENT NOT NULL,
1150 DataScadenza DATE NOT NULL,
1151 `Data` DATE NOT NULL,
1152 PRIMARY KEY(Codice),
1153 CONSTRAINT Controlli CHECK
1154 (DataScadenza > `Data`)
1155)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1156
1157CREATE TABLE LavoraIn
1158(
1159 Personale INT NOT NULL
1160 REFERENCES Personale(Codice),
1161 Laboratorio INT NOT NULL
1162 REFERENCES Laboratorio(Codice),
1163 PRIMARY KEY(Personale, Laboratorio)
1164)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1165
1166/*STORE*/
1167
1168/*
1169 Utente(NomeUtente, Nome, Cognome, NumeroTelefono, CartaDiCredito, PayPal, CodiceFiscale, IndirizzoEmail, Password, DomandaSegreta, RispostaSegreta, Documento)
1170 Documento(NumeroDocumento, DataScadenza, Tipologia, Ente)
1171 Ordine(Codice, DataEOra, Stato, NumeroCivico, Cap, Via, Utente, Pagamento)
1172 Cap(Cap, Città)
1173 PagamentoOrdine(Codice, DataEOra)
1174 Spedizione(Codice, Stato, DataConsegna, DataStimata, Ordine)
1175 Tracking(Spedizione, Città, Via, NumeroCivico, DataEOraArrivo, DataEOraPartenza)
1176 Hub(Città, Via, NumeroCivico)
1177 Recensione(Utente, FormaggioIdeale, Data, QualitàPercepita, GradimentoGenerale, Gusto, Testo, Conservazione)
1178 PrenotazioneFormaggio(Ordine, FormaggioIdeale, Quantità)
1179 Prelievo(FormaggioProdotto, Ordine)
1180 Reso(FormaggioProdotto, Ordine, Data)
1181*/
1182
1183CREATE TABLE Utente
1184(
1185 NomeUtente VARCHAR(50) NOT NULL,
1186 Nome VARCHAR(50) NOT NULL,
1187 Cognome VARCHAR(50) NOT NULL,
1188 NumeroTelefono BIGINT NOT NULL,
1189 CartaDiCredito BIGINT,
1190 PayPal VARCHAR(50),
1191 CodiceFiscale VARCHAR(50) NOT NULL,
1192 IndirizzoEmail VARCHAR(50) NOT NULL,
1193 `Password` VARCHAR(50) NOT NULL,
1194 DomandaSegreta VARCHAR(50) NOT NULL,
1195 RispostaSegreta VARCHAR(50) NOT NULL,
1196 Documento INT NOT NULL,
1197 PRIMARY KEY(NomeUtente),
1198 UNIQUE(IndirizzoEmail),
1199 UNIQUE(Documento),
1200 UNIQUE(CodiceFiscale)
1201)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1202
1203CREATE TABLE Documento
1204(
1205 NumeroDocumento INT AUTO_INCREMENT NOT NULL
1206 REFERENCES Utente(Documento),
1207 DataScadenza DATE NOT NULL,
1208 Tipologia VARCHAR(50) NOT NULL,
1209 Ente VARCHAR(50) NOT NULL,
1210 PRIMARY KEY(NumeroDocumento)
1211)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1212
1213CREATE TABLE PagamentoOrdine
1214(
1215 Codice INT AUTO_INCREMENT NOT NULL,
1216 DataEOra TIMESTAMP NOT NULL,
1217 PRIMARY KEY(Codice)
1218)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1219
1220CREATE TABLE Ordine
1221(
1222 Codice INT AUTO_INCREMENT NOT NULL,
1223 DataEOra TIMESTAMP NOT NULL,
1224 Stato VARCHAR(50) NOT NULL,
1225 NumeroCivico INT NOT NULL,
1226 Cap INT NOT NULL,
1227 Via VARCHAR(50) NOT NULL,
1228 Utente VARCHAR(50) NOT NULL
1229 REFERENCES Utente(NomeUtente),
1230 Pagamento INT NOT NULL
1231 REFERENCES PagamentoOrdine(Codice),
1232 PRIMARY KEY(Codice),
1233 UNIQUE(DataEOra, Utente),
1234 CONSTRAINT Controlli CHECK
1235 (
1236 (Stato = "Pendente") OR
1237 (Stato = "In Processazione") OR
1238 (Stato = "In Preparazione") OR
1239 (Stato = "Spedito") OR
1240 (Stato = "Evaso")
1241 )
1242)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1243
1244CREATE TABLE Cap
1245(
1246 Cap INT NOT NULL
1247 REFERENCES Ordine(Cap),
1248 Città VARCHAR(50) NOT NULL,
1249 PRIMARY KEY(Cap)
1250)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1251
1252CREATE TABLE Spedizione
1253(
1254 Codice INT AUTO_INCREMENT NOT NULL,
1255 Stato VARCHAR(50) NOT NULL,
1256 DataConsegna DATE,
1257 DataStimata DATE,
1258 Ordine INT NOT NULL
1259 REFERENCES Ordine(Codice),
1260 PRIMARY KEY(Codice),
1261 UNIQUE(Ordine),
1262 CONSTRAINT Controlli CHECK
1263 (
1264 (Stato = "Pronta" AND DataConsegna IS NULL) OR
1265 (Stato = "Spedita" AND DataConsegna IS NULL) OR
1266 (Stato = "InTransito" AND DataConsegna IS NULL) OR
1267 (Stato = "InConsegna" AND DataConsegna IS NULL) OR
1268 (Stato = "Consegnata" AND DataConsegna IS NOT NULL)
1269 )
1270)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1271
1272CREATE TABLE Hub
1273(
1274 Citta VARCHAR(50) NOT NULL,
1275 Via VARCHAR(50) NOT NULL,
1276 NumeroCivico INT NOT NULL,
1277 PRIMARY KEY(Citta, Via, NumeroCivico)
1278)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1279
1280CREATE TABLE Tracking
1281(
1282 Spedizione INT NOT NULL
1283 REFERENCES Spedizione(Codice),
1284 Citta VARCHAR(50) NOT NULL,
1285 Via VARCHAR(50) NOT NULL,
1286 NumeroCivico INT NOT NULL,
1287 DataEOraArrivo TIMESTAMP NOT NULL,
1288 DataEOraPartenza TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
1289 Ultimo BOOL NOT NULL DEFAULT FALSE,
1290 PRIMARY KEY(Spedizione, Citta, Via, NumeroCivico),
1291 FOREIGN KEY(Citta, Via, NumeroCivico)
1292 REFERENCES Hub(Citta, Via, NumeroCivico),
1293 CONSTRAINT Controlli CHECK
1294 (
1295 (DataEOraPartenza IS NULL) OR
1296 (DataEOraPartenza IS NOT NULL AND DataEOraPartenza > DataEOraArrivo)
1297 )
1298)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1299
1300CREATE TABLE PrenotazioneFormaggio
1301(
1302 Ordine INT NOT NULL
1303 REFERENCES Ordine(Codice),
1304 FormaggioIdeale VARCHAR(50) NOT NULL
1305 REFERENCES FormaggioIdeale(Nome),
1306 Quantita INT NOT NULL,
1307 PRIMARY KEY(Ordine, FormaggioIdeale),
1308 CONSTRAINT Controlli CHECK
1309 (Quantita > 0)
1310)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1311
1312CREATE TABLE Prelievo
1313(
1314 FormaggioProdotto INT NOT NULL
1315 REFERENCES FormaggioProdotto(Nome),
1316 Ordine INT NOT NULL
1317 REFERENCES Ordine(Codice),
1318 PRIMARY KEY(FormaggioProdotto, Ordine)
1319)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1320
1321CREATE TABLE Reso
1322(
1323 FormaggioProdotto INT NOT NULL
1324 REFERENCES FormaggioProdotto(Nome),
1325 Ordine INT NOT NULL
1326 REFERENCES Ordine(Codice),
1327 `Data` DATE NOT NULL,
1328 PRIMARY KEY(FormaggioProdotto, Ordine)
1329)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1330
1331CREATE TABLE Recensione
1332(
1333 Utente VARCHAR(50) NOT NULL
1334 REFERENCES Utente(NomeUtente),
1335 FormaggioIdeale INT NOT NULL
1336 REFERENCES FormaggioIdeale(Nome),
1337 `Data` DATE NOT NULL,
1338 QualitaPercepita INT NOT NULL,
1339 GradimentoGenerale INT NOT NULL,
1340 Gusto INT NOT NULL,
1341 Testo VARCHAR(400) NOT NULL,
1342 Conservazione INT NOT NULL,
1343 PRIMARY KEY(Utente, FormaggioIdeale),
1344 CONSTRAINT Controlli CHECK
1345 (
1346 (QualitaPercepita >= 0 AND QualitaPercepita <=5) AND
1347 (GradimentoGenerale >= 0 AND GradimentoGenerale <=5) AND
1348 (Gusto >= 0 AND Gusto <=5) AND
1349 (Conservazione >= 0 AND Conservazione <= 5)
1350 )
1351)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1352
1353/*SOGGIORNO*/
1354
1355/*
1356 Cliente(Codice, Nome, Cognome, NumeroDitelefono, NumeroCarta, PayPal)
1357 TransazioneCliente(Cliente, Pagamento)
1358 PagamentoPrenotazione(Codice, Tipologia, DataEOra)
1359 TransazioneUtente(Utente, Pagamento)
1360 Prenotazione(Codice, DataFine, DataInizio, Pagamento)
1361 Occupazione(Stanza, Agriturismo, Prenotazione, NumeroPersone)
1362 Stanza(Numero, Agriturismo, Tipololgia, CostoGiornaliero, Capienza)
1363 ServizioPrenotato(Codice, Data, Prenotazione, Servizio)
1364 Servizio(Nome, Disponibile, Costo)
1365 Escursione(Codice, IstanteInizio, IstanteFine, ServizioPrenotato, Guida)
1366 Sosta(Escursione, IstanteInizio, IstanteFine, NomeArea)
1367*/
1368
1369CREATE TABLE Cliente
1370(
1371 Codice INT AUTO_INCREMENT NOT NULL,
1372 Nome VARCHAR(50) NOT NULL,
1373 Cognome VARCHAR(50) NOT NULL,
1374 NumeroDitelefono BIGINT NOT NULL,
1375 NumeroCarta BIGINT,
1376 PayPal VARCHAR(50) NOT NULL,
1377 PRIMARY KEY(Codice),
1378 CONSTRAINT Controlli CHECK
1379 (
1380 (NumeroCarta IS NULL AND PayPal IS NOT NULL) OR
1381 (NumeroCarta IS NOT NULL AND PayPal IS NULL) OR
1382 (NumeroCarta IS NOT NULL AND PayPal IS NOT NULL)
1383 )
1384)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1385
1386CREATE TABLE PagamentoPrenotazione
1387(
1388 Codice INT AUTO_INCREMENT NOT NULL,
1389 DataEOra TIMESTAMP NOT NULL,
1390 Tipologia VARCHAR(50) NOT NULL,
1391 PRIMARY KEY(Codice),
1392 CONSTRAINT Controlli CHECK
1393 (
1394 (Tipologia = "PayPal") OR
1395 (Tipologia = "Carta") OR
1396 (Tipologia = "Contanti")
1397 )
1398)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1399
1400CREATE TABLE TransazioneCliente
1401(
1402 Cliente INT NOT NULL
1403 REFERENCES Cliente(Codice),
1404 Pagamento INT NOT NULL
1405 REFERENCES PagamentoPrenotazione(Codice),
1406 PRIMARY KEY(Cliente, Pagamento)
1407)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1408
1409CREATE TABLE TransazioneUtente
1410(
1411 Utente VARCHAR(50) NOT NULL
1412 REFERENCES Utente(NomeUtente),
1413 Pagamento INT NOT NULL
1414 REFERENCES PagamentoPrenotazione(Codice),
1415 PRIMARY KEY(Utente, Pagamento)
1416)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1417
1418CREATE TABLE Prenotazione
1419(
1420 Codice INT AUTO_INCREMENT NOT NULL,
1421 DataInizio DATE NOT NULL,
1422 DataFine DATE NOT NULL,
1423 Pagamento INT NOT NULL
1424 REFERENCES Pagamento(Codice),
1425 PRIMARY KEY(Codice),
1426 UNIQUE(Pagamento),
1427 CONSTRAINT Controlli CHECK
1428 (DataFine >= DataInizio)
1429)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1430
1431CREATE TABLE Stanza
1432(
1433 Numero INT AUTO_INCREMENT NOT NULL,
1434 Agriturismo INT NOT NULL
1435 REFERENCES Agriturismo(Codice),
1436 Tipologia VARCHAR(50) NOT NULL,
1437 CostoGiornaliero INT NOT NULL,
1438 Capienza INT NOT NULL,
1439 PRIMARY KEY(Numero, Agriturismo),
1440 CONSTRAINT Controlli CHECK
1441 (
1442 (
1443 (Tipologia = "Suite" AND Capienza >= 1 ) OR
1444 (Tipologia = "Semplice" AND Capienza = 1)
1445 )
1446 AND
1447 (CostoGiornaliero > 0)
1448 )
1449)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1450
1451CREATE TABLE Occupazione
1452(
1453 NumeroStanza INT NOT NULL,
1454 Agriturismo INT NOT NULL,
1455 Prenotazione INT NOT NULL,
1456 NumeroPersone INT NOT NULL,
1457 PRIMARY KEY(NumeroStanza, Agriturismo, Prenotazione),
1458 FOREIGN KEY(NumeroStanza, Agriturismo)
1459 REFERENCES Stanza(Numero, Agriturismo),
1460 FOREIGN KEY(Prenotazione)
1461 REFERENCES Prenotazione(Codice)
1462)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1463
1464CREATE TABLE Servizio
1465(
1466 Nome VARCHAR(50) NOT NULL,
1467 Disponibile BOOL NOT NULL,
1468 Costo FLOAT NOT NULL,
1469 PRIMARY KEY(Nome)
1470)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1471
1472CREATE TABLE ServizioPrenotato
1473(
1474 Codice INT AUTO_INCREMENT NOT NULL,
1475 `Data` DATE NOT NULL,
1476 Prenotazione INT NOT NULL
1477 REFERENCES Prenotazione(Codice),
1478 Servizio VARCHAR(50) NOT NULL
1479 REFERENCES Servizio(Nome),
1480 PRIMARY KEY(Codice)
1481 /*Trigger per verificare che la prenotazione riguardi una suite*/
1482)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1483
1484CREATE TABLE Escursione
1485(
1486 Codice INT AUTO_INCREMENT NOT NULL,
1487 IstanteInizio TIMESTAMP NOT NULL,
1488 IstanteFine TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
1489 ServizioPrenotato INT NOT NULL
1490 REFERENCES ServizioPrenotato(Codice),
1491 Guida INT NOT NULL
1492 REFERENCES Presonale(Codice),
1493 PRIMARY KEY(Codice),
1494 CONSTRAINT Controlli CHECK
1495 (
1496 (IstanteFine IS NULL) OR
1497 (IstanteFine IS NOT NULL AND IstanteFine > IstanteInizio)
1498 /*Trigger per verificare la mansione del personale per la guida*/
1499 /*Trigger per verificare la distanza di due giorni tra la prenotazione del servizio e l'escursione*/
1500 )
1501)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1502
1503CREATE TABLE Sosta
1504(
1505 Escursione INT NOT NULL
1506 REFERENCES Escursione(Codice),
1507 IstanteInizio TIMESTAMP NOT NULL,
1508 IstanteFine TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
1509 NomeArea VARCHAR(50) NOT NULL,
1510 PRIMARY KEY(Escursione, IstanteInizio)
1511)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1512
1513/*RIDONDANZE*/
1514
1515/*
1516 AttivitàMensile(Veterinario, Anno, Mese, NumeroVisite)
1517 Calendario(Anno, Mese)
1518 Specializzazione(Veterinario, Patologia, Punteggio)
1519*/
1520
1521CREATE TABLE Calendario
1522(
1523 Anno YEAR NOT NULL,
1524 Mese INT NOT NULL,
1525 PRIMARY KEY(Anno, Mese),
1526 CONSTRAINT Controlli CHECK
1527 (Mese >= 1 AND Mese <= 12)
1528)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1529
1530CREATE TABLE AttivitaMensile
1531(
1532 Veterinario INT NOT NULL
1533 REFERENCES Veterinario(Matricola),
1534 Anno YEAR NOT NULL,
1535 Mese INT NOT NULL,
1536 NumeroVisite INT NOT NULL,
1537 PRIMARY KEY(Veterinario, Anno, Mese),
1538 FOREIGN KEY(Anno, Mese)
1539 REFERENCES Calendario(Anno, Mese),
1540 CONSTRAINT Controlli CHECK
1541 (NumeroVisite >= 0)
1542)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1543
1544CREATE TABLE Specializzazione
1545(
1546 Veterinario INT NOT NULL
1547 REFERENCES Veterinario(Matricola),
1548 Patologia VARCHAR(50) NOT NULL
1549 REFERENCES Patologia(Nome),
1550 Punteggio INT NOT NULL,
1551 PRIMARY KEY(Veterinario, Patologia),
1552 CONSTRAINT Controlli CHECK
1553 (Punteggio >= 0)
1554)ENGINE=InnoDB DEFAULT CHARSET=latin1;
1555
1556COMMIT;