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