· 5 years ago · May 15, 2020, 10:04 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) 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--creazione e assegnazione privilegi all'utente dell'applicativo e per la popolazione
232drop user if exists gruppo28;
233create user gruppo28 password 'esameBD';
234grant insert on citta to gruppo28;
235grant insert on dipendente to gruppo28 ;
236grant insert on locale to gruppo28 ;
237grant select on citta to gruppo28;
238grant insert on tipoMisto to gruppo28;
239grant select on tipoMisto to gruppo28;
240grant select on locale to gruppo28 ;
241grant insert on Associazione to gruppo28;
242grant insert on impiegato to gruppo28;
243grant select on dipendente to gruppo28;
244grant insert on cliente to gruppo28;
245grant insert on interesse to gruppo28;
246grant select on cliente to gruppo28;
247grant insert on visita to gruppo28;
248grant select on impiegato to gruppo28;
249grant select on associazione to gruppo28;
250grant select on visita to gruppo28;
251grant select on interesse to gruppo28;