-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathbd.sql
318 lines (270 loc) · 12.2 KB
/
bd.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
--caso ja existam estes usuario vamos dropalos
drop user master;
drop user comum1;
drop user comum2;
--primeiramente criar os usuarios
create USER master with password 'master123'; --Cria usuários
create user comum1 with password '123';
create user comum2 with password '456';
--apos criar as tablespaces
create tablespace "t_user" OWNER master LOCATION '/var/lib/postgresql/';
--create tablespace "t_user" OWNER master LOCATION 'c:\var'; --Cria tablespace no local ''c:\var'' para windows tenha certeza de que esta pasta exista caso contrario este comando não funcionara
create tablespace "tel_use" OWNER master LOCATION '/var/lib/postgresql/';
-- create tablespace "tel_use" OWNER master LOCATION 'c:\var\1'; --Cria tablespace no local ''c:\var\1'' para windows tenha certeza de que esta pasta exista caso contrario este comando não funcionara
--o database
create database autopecas tablespace t_user;
--até aqui fora do banco;
--agora conectar ao banco autopecas como super usuario
CREATE SCHEMA esquema; --cria esquema
SET search_path to esquema; ----seta search_path
--alter database autopecas set search_path to esquema;
drop table esquema.svfunc cascade;
drop table esquema.servico cascade;
drop table esquema.peca cascade;
drop table esquema.carro cascade;
drop table esquema.fabricante cascade;
drop table esquema.telefone cascade;
drop table esquema.cliente cascade;
drop table esquema.funcionario cascade;
create table esquema.cliente (
cpf varchar(15) not null,
rg varchar(14) not null,
nome varchar(45) not null,
ender varchar(25) null,
constraint pk_cpf primary key (cpf)
);
create table esquema.telefone (
idtel integer not null,
tel integer not null,
clicod varchar(15) not null,
constraint pk_tel primary key(idtel),
constraint fk_cliente foreign key (clicod) references cliente(cpf)
);
create table esquema.fabricante (
idfab integer not null,
cnpj varchar(20) not null,
nome varchar(50) not null,
constraint pk_fab primary key(idfab)
);
create table esquema.carro (
chassi varchar(20) not null,
idfab integer not null,
anofab integer not null,
modelo varchar(20) not null,
dono varchar(15) not null,
constraint pk_carro primary key (chassi),
constraint fk_cliente_car foreign key(dono) references cliente(cpf),
constraint fk_fab_car foreign key(idfab) references fabricante(idfab)
);
create table esquema.funcionario (
cpf varchar(15) not null,
nome varchar(45) not null,
funcao varchar(25) not null,
constraint pk_func primary key(cpf)
);
create table esquema.peca (
codpeca integer not null,
idfab integer not null,
descr varchar(50) not null,
qtd integer not null,
preco varchar(10) not null,
categoria varchar(20) not null,
constraint pk_peca primary key(codpeca),
constraint fk_fab foreign key(idfab) references fabricante(idfab)
);
create table esquema.servico(
idsv integer not null,
codpeca integer not null,
chassi varchar(20) not null,
tipo varchar(20) not null,
preco varchar(10) not null,
dthorag timestamp not null,
constraint pk_servico primary key(idsv,dthorag),
constraint fk_peca foreign key(codpeca) references peca(codpeca),
constraint fk_car foreign key(chassi) references carro(chassi)
);
create table esquema.svfunc(
cpf varchar(15) not null,
idsv integer not null,
dthorag timestamp not null,
constraint pk_svfunc primary key(cpf,idsv),
constraint fk_func foreign key(cpf) references funcionario(cpf),
constraint fk_servico foreign key(idsv,dthorag) references servico(idsv,dthorag)
);
--ESSA TRIGGER adiciona novo registro em svfunc quando um novo servico é inserido
--exemplo : um produto é vendido, logo um vendedor será armazenada em svfunc;
-- Quando o produto é vendido, ele pode ser instalado, então será armazenado em svfunc ,
-- o profissional que realizou o serviço
drop trigger func_service on esquema.servico cascade;
create or replace function func_service() returns trigger as
$func_service$
DECLARE
mont funcionario%ROWTYPE;
vend1 funcionario%ROWTYPE;
--vend2 funcionario%ROWTYPE;
pintor funcionario%ROWTYPE;
mec funcionario%ROWTYPE;
allpeca peca%ROWTYPE;
--cp funcionario.cpf%type;
BEGIN
if(TG_OP = 'INSERT') then
SELECT * INTO vend1 FROM esquema.funcionario WHERE esquema.funcionario.funcao = 'Vendedor(a)' LIMIT 1; --PEGA UM VENDEDOR (NO CASO O PRIMEIRO)
SELECT * INTO pintor FROM esquema.funcionario WHERE esquema.funcionario.funcao = 'Pintor Automotivo' LIMIT 1;--um PINTOR
SELECT * INTO mec FROM esquema.funcionario WHERE esquema.funcionario.funcao = 'Mecanico' LIMIT 1; --UM MECANICO
SELECT * INTO mont FROM esquema.funcionario WHERE esquema.funcionario.funcao = 'Montador' LIMIT 1; -- UM MONTADOR
SELECT * INTO allpeca FROM esquema.peca WHERE esquema.peca.codpeca = NEW.codpeca LIMIT 1; --VERIFICA O CODIGO DA PECA
--SELECT INTO * tipo1 FROM servico;
--RAISE NOTICE '% ', allpeca.categoria;
if(NEW.tipo = 'Instalacao')then
INSERT INTO esquema.svfunc SELECT vend1.cpf,NEW.idsv,NEW.dthorag; --INSERE EM SVFUNC O VENDEDOR que realizou a venda
if(allpeca.categoria = 'Acessorios')then
INSERT INTO esquema.svfunc SELECT mont.cpf,NEW.idsv,NEW.dthorag; --INSERE EM SVFUNC quem participou do servico
elsif(allpeca.categoria = 'Peca') then
INSERT INTO esquema.svfunc SELECT mec.cpf,NEW.idsv,NEW.dthorag;
else
RAISE EXCEPTION 'Tipo % invalido', NEW.tipo;
RETURN NULL;
end if;
RETURN NEW;
elsif (NEW.tipo = 'Reposicao') then --mesma coisa que para tipo INSTALACAO
INSERT INTO esquema.svfunc SELECT vend1.cpf,NEW.idsv,NEW.dthorag;
if(allpeca.descr = 'Tinta')then
INSERT INTO esquema.svfunc SELECT pintor.cpf,NEW.idsv,NEW.dthorag;
else
INSERT INTO esquema.svfunc SELECT mec.cpf,NEW.idsv,NEW.dthorag;
end if;
RETURN NEW;
elsif(NEW.tipo = 'Venda') then --SE FOR FEITA APENAS A VENDA e não instalação ou reparação
INSERT INTO esquema.svfunc SELECT vend1.cpf,NEW.idsv,NEW.dthorag;
RETURN NEW;
else --CASO PARA ERROS
RAISE EXCEPTIOn 'tipo % de servico invalido', NEW.tipo;
RETURN NULL;
end if;
end if;
RETURN NULL;
END
$func_service$ LANGUAGE plpgsql;
create trigger func_service
AFTER INSERT on esquema.servico FOR EACH ROW EXECUTE PROCEDURE func_service();
ALTER table esquema.telefone SET TABLESPACE tel_use; --Altera tabela telefone para tablespace criada
create view esquema.car_marca( marca, qtd) as
select nome, count(*) from esquema.fabricante natural join esquema.carro group by nome;
create view esquema.funporcli (nome_funci,cpf_func,tipo_serv,preco,horario,chassi_car,nome_cli,cpf_cli) as
select s.nome,s.cpf,s.tipo,s.preco,s.dthorag,s.chassi,c.nome,c.cpf from
(esquema.funcionario f natural join esquema.svfunc natural join esquema.servico natural join esquema.carro)s
join esquema.cliente c on (s.dono=c.cpf);
create view esquema.carporcli(nome_cliente,cpf_cli,qt_caros) as
select nome,cpf,count(chassi) from esquema.carro r join esquema.cliente c on dono=cpf
group by nome,cpf;
insert into esquema.cliente ( cpf,rg,nome,ender) values
('111111','2222','Marcos A.',null),
('222222','3333','Jose M.','Joinville SC'),
('333333','4444','Carlos C.','Curitiba PR'),
('444444','5555','Ana F.','Chapeco SC'),
('555555','6666','Matheus','Chapeco SC'),
('666666','7777','Jose P.','Porto Alegre RS'),
('777777','8888','Juliana','Xaxim SC'),
('888888','9999','Adao V.','Curitiba PR'),
('999999','9915','Marcela','Chapeco SC'),
('819211','5523','Pedro','Cascavel PR');
insert into esquema.telefone (idtel,tel,clicod) values
(1,88554555,'111111'),
(2,88442222,'111111'),
(3,88664258,'111111'),
(4,99554482,'333333'),
(5,91554823,'333333'),
(6,81558542,'222222'),
(7,81522236,'222222'),
(8,99112548,'555555'),
(9,91915586,'666666'),
(10,99335841,'777777'),
(11,85654852,'999999'),
(12,9202123,'819211');
insert into esquema.fabricante (idfab,cnpj,nome) values
(1,'001546623','Volkswagen'),
(2,'221155554','Chevrolet'),
(3,'223111447','Citroen'),
(4,'222344456','Toyota'),
(5,'111222566','Ford'),
(6,'778232145','Honda'),
(7,'445634856','Fiat'),
(8,'231245698','Renault'),
(9,'664525874','Hyundai'),
(10,'33212448','Nissan');
insert into esquema.carro (chassi,idfab,anofab,modelo,dono) values
('555nh33534ff2',1,2013,'Gol G4','111111'),
('66ddf555www62',1,2010,'Golf','111111'),
('22ds1223ffvb1',7,2008,'Uno','111111'),
('23125la55vo11',2,2013,'Classic','222222'),
('ll55458ffa212',2,2007,'Corsa','333333'),
('nn65df555512s',5,2010,'Ka', '444444'),
('kk8080dfww101',8,2013,'Sandero','444444'),
('oo2021vb26741',4,2012,'Corolla','666666'),
('mon65dd202758',9,2013,'Hb20', '819211'),
('2255gh3qw22rt',6,2012,'Civic','999999'),
('koo556468fgvs',6,2012,'Civic','777777'),
('77jjqw554x258',8,2010,'Clio','819211');
insert into esquema.funcionario (cpf,nome,funcao) values
('112211','Maria','Vendedor(a)'),
('214155','Julia','Secretaria'),
('314522','Carlos','Mecanico'),
('221587','Marcos','Mecanico'),
('552184','Luiz','Montador'),
('331548','Pedro F','Vendedor(a)'),
('668452','Marcio','Pintor Automotivo'),
('991232','Joao','Pintor Automotivo'),
('115483','Clara','Manobrista'),
('775217','Marcelo','Manobrista');
insert into esquema.peca (codpeca,idfab,descr,qtd,preco,categoria) values
(12,1,'Roda Aluminio 17',20,'450,00','Acessorios'),
(13,1,'Parabrisa',17,'300,00','Acessorios'),
(14,1,'Volante esportivo',40,'100,00','Acessorios'),
(15,2,'Oleo Dexos',55,'70,00','Peca'),
(16,2,'Kit embreagem',25,'250,00','Peca'),
(17,3,'Tinta',10,'500,00','Peca'),
(18,4,'Oleo p motor',20,'150,00','Peca'),
(19,5,'Protetor Carter',50,'60,00','Peca'),
(20,6,'Filtro combustivel',23,'200,00','Peca'),
(21,8,'Defletor Carter',5,'190,00','Peca'),
(22,7,'Caixa de Cambio',10,'700,00','Peca');
--aqui é para formato aaaa-mm-dd pause aqui
insert into esquema.servico (idsv,codpeca,chassi,tipo,preco,dthorag) values
(1,12,'555nh33534ff2','Instalacao','500,00','2013-05-11 10:00'),
(2,12,'66ddf555www62','Instalacao','500,00','2013-05-12 15:00'),
(3,13,'66ddf555www62','Reposicao','400,00','2013-05-12 15:00'),
(4,14,'66ddf555www62','Reposicao','200,00','2013-05-12 15:00'),
(5,15,'23125la55vo11','Reposicao','100,00','2013-07-07 08:00'),
(6,15,'23125la55vo11','Reposicao','100,00','2013-02-09 09:00'),
(7,16,'ll55458ffa212','Reposicao','250,00','2012-05-04 11:00'),
(8,16,'ll55458ffa212','Reposicao','350,00','2013-09-02 16:00'),
(9,18,'oo2021vb26741','Reposicao','200,00','2013-12-11 15:00'),
(10,19,'nn65df555512s','Instalacao','100,00','2013-10-04 17:00'),
(11,21,'kk8080dfww101','Reposicao','250,00','2014-02-01 13:00');
--aqui é formato normal dd-mm-aaaa pause aqui
/*insert into servico (idsv,codpeca,chassi,tipo,preco,dthorag) values
(1,12,'555nh33534ff2','Instalacao','500,00','05-11-2013 10:00'),
(2,12,'66ddf555www62','Instalacao','500,00','05-12-2013 15:00'),
(3,13,'66ddf555www62','Reposicao','400,00','05-12-2013 15:00'),
(4,14,'66ddf555www62','Reposicao','200,00','05-12-2013 15:00'),
(5,15,'23125la55vo11','Reposicao','100,00','07-07-2013 08:00'),
(6,15,'23125la55vo11','Reposicao','100,00','02-09-2013 09:00'),
(7,16,'ll55458ffa212','Reposicao','250,00','05-04-2012 11:00'),
(8,16,'ll55458ffa212','Reposicao','350,00','09-02-2013 16:00'),
(9,18,'oo2021vb26741','Reposicao','200,00','12-11-2013 15:00'),
(10,19,'nn65df555512s','Instalacao','100,00','10-04-2013 17:00'),
(11,21,'kk8080dfww101','Reposicao','250,00','02-01-2014 13:00'),
(12,22,'22ds1223ffvb1','Reposicao','1000,00','03-01-2014 14:00');*/
GRANT USAGE on SCHEMA esquema to master; --habilita uso do esquema para os usuarios
GRANT USAGE ON SCHEMA esquema to comum1;
GRANT USAGE ON SCHEMA esquema to comum2;
--SET search_path to esquema; --seta search_path
GRANT ALL privileges on
all tables in schema esquema to master; --altera privilegios para os usuários
GRANT SELECT on
all tables in schema esquema to comum1;
GRANT SELECT,UPDATE on
all tables in schema esquema to comum2;
alter USER master set search_path to esquema; --Define esquema padrão para os usuários
alter USER comum1 set search_path to esquema;
alter USER comum2 set search_path to esquema;