· 5 years ago · May 16, 2020, 08:48 PM
1drop table if exists cliente cascade;
2drop table if exists citta cascade;
3drop table if exists locale cascade;
4drop table if exists dipendente cascade;
5drop table if exists impiegato cascade;
6drop table if exists tipoMisto cascade;
7drop table if exists interesse cascade;
8drop table if exists visita cascade;
9drop table if exists associazione cascade;
10
11create table citta(
12 cap char(5) primary key check(char_length(cap)=5),
13 regione varchar(30) not null,
14 nomeC varchar(1024) unique not null
15);
16
17create table cliente(
18 codF char(16) primary key check(char_length(codf)=16),
19 citta char(5) references citta(cap) on delete cascade on update cascade not null,
20 sesso char(1) check(sesso='M' or sesso='F') not null,
21 nome varchar(1024) not null,
22 cognome varchar(1024) not null,
23 dataN date not null,
24 indirizzo varchar(1024) not null
25);
26create table locale(
27 citta char(5) references citta(cap) on delete cascade on update cascade,
28 ID char(5) check(char_length(ID)=5),
29 telefono char(10) check(char_length(telefono)=10),
30 capienza int check(capienza>=0),
31 nome varchar(1024) not null,
32 orarioApertura time,
33 orarioChiusura time,
34 orario24H boolean not null,
35 animali boolean,
36 prenotazione boolean not null,
37 tipo varchar(1024),
38 alcolici boolean,
39 specialita varchar(1024),
40 consegnaADomicilio boolean,
41 categoriaLocale varchar(3) check(categoriaLocale='c&b'or categoriaLocale='d' or categoriaLocale='m') not null,
42 indirizzo varchar(1024) not null,
43 primary key (citta,ID)
44);
45
46create table dipendente(
47 codFiscale char(16) primary key check(char_length(codFiscale)=16),
48 orarioInizio time not null,
49 orarioFine time not null,
50 pausa interval hour,
51 sesso char(1) check(sesso='M' or sesso='F') not null,
52 nome varchar(1024) not null,
53 cognome varchar(1024) not null,
54 dataN date not null
55);
56
57create table impiegato(
58 dipendente char(16) references dipendente(codFiscale) on delete cascade on update cascade deferrable initially deferred,
59 cittaLocale char(5),
60 locale char(5),
61 dataAssunzione date not null,
62 salario numeric(6,2) check(salario>=0) not null,
63 primary key (dipendente,cittaLocale,locale),
64 foreign key (cittaLocale, locale) references locale(citta,ID) on delete cascade on update cascade deferrable initially deferred
65
66);
67create table tipoMisto(
68 tipoS varchar(1024) primary key
69);
70create table associazione(
71 cittaLocale varchar(5),
72 locale varchar(5),
73 tipo varchar(1024) references tipoMisto(tipos) on delete cascade on update cascade,
74 primary key(cittalocale,locale,tipo),
75 foreign key(cittaLocale,locale) references locale(citta,ID) on delete cascade on update cascade deferrable initially deferred
76);
77
78create table interesse(
79 cittaLocale char(5),
80 locale char(5),
81 cliente char(16) references cliente(codF) on delete cascade on update restrict deferrable initially deferred,
82 --supponiamo che se cambia il CF questo si riferisca ad un'altra persoa che non può avere gli stessi interssi--
83 primary key(cittaLocale,locale,cliente),
84 foreign key (cittaLocale, locale) references locale(citta,ID) on delete cascade on update cascade
85);
86
87create table visita(
88 cittaLocale char(5),
89 locale char(5),
90 cliente char(16) references cliente(codF)on delete cascade on update restrict deferrable initially deferred, --Per la stessa cosa di sopra
91 dataVisita date,
92 votazione smallint check(votazione>=1 and votazione<=5),
93 recensione varchar(140),
94 spesa money,
95 primary key(dataVisita,cittaLocale,locale,cliente),
96 foreign key (cittaLocale, locale) references locale(citta,ID) on delete cascade on update restrict
97 --Si suppone che un locale una volta eliminato non è più aperto e non interessa mantenere le visite passate a quel locale--
98 --Se un locale cambia città o nome si suppone che questo sia nuova apertura e tutte le visite ad esso collegate devono essere elimanate--
99);
100
101
102
103
104create or replace function controllaOrario() returns trigger as $$
105begin
106if(new.orarioApertura is not null or new.orarioChiusura is not null) then
107 raise exception 'OrarioException';
108end if;
109return new;
110end $$ language plpgsql;
111
112--Controlla che se un locale è aperto 24H non si debba specificare l'orarario di apertura e chiusura
113create trigger orario
114after insert or update on locale
115for each row
116when(new.orario24H=true)
117execute procedure controllaOrario();
118
119
120create or replace function controllaTelefono() returns trigger as $$
121begin
122if(new.telefono is null) then
123 raise exception 'TelefonoException';
124end if;
125return new;
126end $$ language plpgsql;
127
128--Contralla che il telefono sia presente se un locale effettua consegne a domicilio o prende prenotazioni
129create trigger telefono
130after insert or update on locale
131for each row
132when(new.prenotazione=true or new.consegnaADomicilio=true)
133execute procedure controllaTelefono();
134
135
136create or replace function controllaValutazione() returns trigger as $$
137begin
138if(new.recensione is not null) then
139 raise exception 'TelefonoException';
140end if;
141return new;
142end $$ language plpgsql;
143
144
145--controlla che una recensione non possa essere specificato se non è specificata la valutazione
146create trigger valutazione
147after insert or update on visita
148for each row
149when(new.votazione is null)
150execute procedure controllaValutazione();
151
152
153create or replace function controllaCategoria() returns trigger as $$
154begin
155if(new.categoriaLocale='c&b') then
156 if(new.consegnaADomicilio is null or new.tipo is null ) then
157 raise exception 'CiboException';
158 end if;
159elsif(new.categoriaLocale='d') then
160 if(new.tipo is null or new.alcolici is not null or new.specialita is not null or new.consegnaADomicilio is not null)then
161 raise exception 'DivertimentoException';
162 end if;
163elsif(new.categoriaLocale='m')then
164 if(new.consegnaADomicilio is null or new.tipo is not null) then
165 raise exception 'MistoException';
166 end if;
167 if(not exists(select * from associazione where (locale=new.ID and cittaLocale=new.citta)))then
168 raise exception 'TipoException';
169 end if;
170end if;
171return new;
172end $$ language plpgsql;
173
174--Se il valore di Locale.categoriaLocale è “c&b” allora devono essere per forza valorizzati gli attributi
175--consegna a domicilio e tipo. Se il valore di categoriaLocale è “d”
176--deve essere valorizzato l'attributo Tipo e devono essere NULL gli attributi Alcolici, Specialità e
177--consegna a domicilio. Se il valore di categoriaLocale è “m” viene valorizzato l'attributo consegna
178--a domicilio mentre è impostato a NULL l'attributo Tipo. Controlla inoltre l'appertenza di un locale misto
179--ad associazione in modo che sia obbligariamente associato ad almeno un tipo
180create trigger insertLocale
181after insert or update on locale
182for each row
183when(new.categoriaLocale='c&b' or new.categoriaLocale='d' or new.categoriaLocale='m')
184execute procedure controllaCategoria();
185
186
187create or replace function controllaCliente() returns trigger as $$
188begin
189if(not exists(select * from interesse where cliente=new.codF) and not exists(select * from visita where cliente=new.codF))then
190 raise exception 'ClienteException';
191end if;
192return new;
193end $$ language plpgsql;
194
195--controlla che un cliente abbia almeno una visita o un interesse presso un locale e non ne permette modifica o inserimento
196
197create trigger controllaVisitaInteresse
198after insert or update on cliente
199for each row
200execute procedure controllaCliente();
201
202
203create or replace function controllaImpiegato() returns trigger as $$
204begin
205if(not exists(select * from impiegato where locale=new.ID))then
206 raise exception 'ImpiegatoException';
207end if;
208return new;
209end $$ language plpgsql;
210
211--Controlla che ci sia almeno un dipendente(il proprietario) per il locale inserito o modificato o non permette l'aggiunta/aggiornamento
212create trigger controllaLocale
213after insert or update on locale
214for each row
215execute procedure controllaImpiegato();
216
217create or replace function controllaLocale() returns trigger as $$
218begin
219if(not exists(select * from impiegato where dipendente=new.codFiscale))then
220 raise exception 'LocaleException';
221end if;
222return new;
223end $$ language plpgsql;
224
225--Controlla che il dipendente che si vuole inserire/modificare sia presente presso almeno un locale
226create trigger controllaDipendente
227after insert or update on dipendente
228for each row
229execute procedure controllaLocale();
230
231
232
233
234create or replace function eliminazioneLocaleDipendente() returns trigger as $$
235begin
236if(not exists(select * from impiegato where old.dipendente=dipendente))then
237 delete from dipendente where old.dipendente=codFiscale;
238end if;
239if(not exists(select * from impiegato where old.cittaLocale=cittaLocale and old.locale=locale))then
240 delete from locale where old.cittaLocale=citta and old.locale=ID;
241end if;
242return new;
243end $$ language plpgsql;
244
245
246--All'eliminazione di una riga da impiegato controlla se è l'ultima tupla che si riferisce a un dato
247--locale e in caso positivo cancella il locale dalla tabella Locale. Il trigger effettua lo stesso tipo di verifica sul dipendete
248--oggetto della cancellazione e se positiva lo cancella dalla tabella di appertenza.
249create trigger eliminazioneImpiegato
250after delete on impiegato
251for each row
252execute procedure eliminazioneLocaleDipendente();
253
254create or replace function eliminazioneTipoMisto() returns trigger as $$
255begin
256if(not exists (select * from associazione where old.cittaLocale= cittaLocale and old.locale=locale))then
257 delete from locale where old.cittaLocale=citta and old.locale=ID;
258end if;
259return new;
260end $$ language plpgsql;
261
262--All'eliminazione di una riga da associazione verifica che questa sia l'ultima per il locale
263--bersaglio dell'operazione se positivo elimina il locale dalla tabella locale.
264create trigger eliminazioneAssociazione
265after delete on associazione
266for each row
267execute procedure eliminazioneTipoMisto();
268
269create or replace function eliminazioneClienteVisita() returns trigger as $$
270begin
271if(not exists(select * from visita where old.cliente=cliente) and not exists(select * from interesse where old.cliente=cliente)) then
272 delete from cliente where codF=old.cliente;
273end if;
274return new;
275end $$ language plpgsql;
276
277--All'eliminazione di una riga in visita verifica che non sia l'ultima riga nella tabella
278--visita e nella tabella interesse per il cliente target se risulta positivo cancella il cliente
279--corrispondente dalla tabella cliente
280create trigger eliminazioneVisita
281after delete on visita
282for each row
283execute procedure eliminazioneClienteVisita();
284
285
286
287
288create or replace function eliminazioneClienteInteresse() returns trigger as $$
289begin
290if(not exists(select * from visita where old.cliente=cliente) and not exists(select * from interesse where old.cliente=cliente)) then
291 delete from cliente where codF=old.cliente;
292end if;
293return new;
294end $$ language plpgsql;
295
296--All'eliminazione di una riga in visita verifica che non sia l'ultima riga nella tabella
297--visita e nella tabella interesse per il cliente target se risulta positivo cancella il cliente
298--corrispondente dalla tabella cliente
299create trigger eliminazioneInteresse
300after delete on visita
301for each row
302execute procedure eliminazioneClienteInteresse();
303
304--Vista che verrà utilizzata per la versione elegante della query 8;
305create view visiteLocale As
306select cittaLocale,locale,cliente,votazione,dataVisita,recensione
307from visita V
308where (V.cittaLocale='00000' and V.locale='77777' );
309
310--creazione e assegnazione privilegi all'utente dell'applicativo e per la popolazione
311drop user if exists gruppo28;
312create user gruppo28 password 'esameBD';
313grant insert on citta to gruppo28;
314grant insert on dipendente to gruppo28 ;
315grant insert on locale to gruppo28 ;
316grant select on citta to gruppo28;
317grant insert on tipoMisto to gruppo28;
318grant select on tipoMisto to gruppo28;
319grant select on locale to gruppo28 ;
320grant insert on Associazione to gruppo28;
321grant insert on impiegato to gruppo28;
322grant select on dipendente to gruppo28;
323grant insert on cliente to gruppo28;
324grant insert on interesse to gruppo28;
325grant select on cliente to gruppo28;
326grant insert on visita to gruppo28;
327grant select on impiegato to gruppo28;
328grant select on associazione to gruppo28;
329grant select on visita to gruppo28;
330grant select on interesse to gruppo28;