Aline Bianca Silva Pugas - N423234 - CC4P22

Prévia do material em texto

Ciência da Computação
3º e 4º Semestre
Trabalho Semestral de Bd
Aline Bianca Silva Pugas		 N423234 cc4p22
Gabriel Duarte Plaza F0498G1 cc4p22
Gabriel Vedolin do Nascimento N394544 cc4p22
São Paulo – SP
Requisitos do BD
Modelo lógico gráfico 
Modelo físico comentado em SQL (com regras de integridade de domínio e integridade referencial)
Create database teste;
Use teste;
CREATE TABLE TB_CATEGORIA (
ID_CATEGORIA INTEGER PRIMARY KEY,
NOME_CATEGORIA VARCHAR(100)
);
CREATE TABLE TB_SUBCATEGORIA (
ID_SUBCATEGORIA INTEGER PRIMARY KEY,
NOME_SUBCATEGORIA VARCHAR(1000),
ID_CATEGORIA INTEGER,
 FOREIGN KEY(ID_CATEGORIA) REFERENCES TB_CATEGORIA(ID_CATEGORIA)
);
CREATE TABLE TB_PRODUTO (
ID_PRODUTO INTEGER PRIMARY KEY,
VALOR_PRODUTO decimal(4,2 ),
DESCRICAO VARCHAR(1000 ),
QUANTIDADE_PRODUTO INTEGER,
NOME VARCHAR(1000 ),
ID_CATEGORIA INTEGER,
 FOREIGN KEY(ID_CATEGORIA) REFERENCES TB_CATEGORIA(ID_CATEGORIA),
ID_SUBCATEGORIA INTEGER,
 FOREIGN KEY(ID_SUBCATEGORIA) REFERENCES TB_SUBCATEGORIA(ID_SUBCATEGORIA));
CREATE TABLE TB_TIPO_PAGAMENTO (
ID_TP INTEGER PRIMARY KEY,
TP_NOME VARCHAR(1000 )
);
CREATE TABLE TB_CLIENTE (
ID_CLIENTE INTEGER PRIMARY KEY,
NOME_CLIENTE VARCHAR(1000 ),
EMAIL_CLIENTE VARCHAR(1000 ),
CPF INTEGER,
Estado VARCHAR(100),
Cidade VARCHAR(100)
);
CREATE TABLE TB_VENDA (
ID_VENDA INTEGER PRIMARY KEY,
VEN_DATA DATETIME,
TOTAL DECIMAL(4,2 ),
NUMERO_PARCELA INTEGER,
ID_CLIENTE INTEGER,
 FOREIGN KEY(ID_CLIENTE) REFERENCES TB_CLIENTE(ID_CLIENTE),
ID_TP INTEGER,
 FOREIGN KEY(ID_TP) REFERENCES TB_TIPO_PAGAMENTO(ID_TP)
);
CREATE TABLE ItensVenda (
ID_ITV INTEGER PRIMARY KEY,
ITV_QUANTIDADE INTEGER,
Valor DECIMAL(4,2),
ID_VENDA INTEGER,
 FOREIGN KEY(ID_VENDA) REFERENCES TB_VENDA(ID_VENDA),
ID_PRODUTO INTEGER,
 FOREIGN KEY(ID_PRODUTO) REFERENCES TB_PRODUTO(ID_PRODUTO)
);
CREATE TABLE TB_VENDA_PARCELAS (
ID_VP INTEGER PRIMARY KEY,
VP_VALOR DECIMAL(4,2),
VP_DATAPAGAMENO DATETIME,
VP_DATAVENCIMENTO DATETIME,
ID_VENDA INTEGER,
 FOREIGN KEY(ID_VENDA) REFERENCES TB_VENDA(ID_VENDA)
);
CREATE TABLE TB_TELEFONE_CLIENTE (
ID_TEL INTEGER PRIMARY KEY,
NUMERO varchar(11),
ID_CLIENTE integer,
 FOREIGN KEY(ID_CLIENTE) REFERENCES TB_CLIENTE(ID_CLIENTE));
INSERT DO BANCO
insert into TB_CLIENTE values('1','Gabriel','gabriel@live.com','06620010','Sao Paulo','Sao Paulo');
insert into TB_CLIENTE values('2','Josè','jose@live.com','06650010','Sao Paulo','Sao Paulo');
insert into TB_TELEFONE_CLIENTE values(1,'11981716209','1');
insert into TB_TELEFONE_CLIENTE values(2,'11981625147','2');
insert into TB_TIPO_PAGAMENTO values(1,'A Vista');
insert into TB_TIPO_PAGAMENTO values(2,'Cartão');
insert into TB_CATEGORIA values(1,'Brinquedo');
insert into TB_CATEGORIA values(2,'Roupa');
insert into TB_SUBCATEGORIA values(1,'Marvel',1);
insert into TB_SUBCATEGORIA values(2,'DC',2);
insert into TB_PRODUTO values(1,42.00,'Boneco do homem de ferro Era de ultron',6,'Boneco do homem de ferro',1,1);
insert into TB_PRODUTO values(2,52.00,'Roupa do Flash Ano 1',13,'Roupa do Flash',2,2);
insert into TB_VENDA values(1,'2019/02/21',94.00,2,'1',2);
insert into TB_VENDA values(2,'2019/02/21',52.00,0,'2',1);
insert into TB_VENDA_PARCELAS values(1,47.00,'2019/03/21','2019/03/28',1);
insert into TB_VENDA_PARCELAS values(2,47.00,'2019/04/21','2019/04/28',1);
insert into ItensVenda values(1,1,42.00,1,1);
insert into ItensVenda values(2,2,52.00,1,2);
insert into ItensVenda values(3,1,52.00,2,2);
3 relatórios (consultas) em SQL que atendam o negócio (envolvendo várias tabelas e com a devida complexidade)
1)SELECT C.CPF,C.NOME_CLIENTE as 'Cliente',TP.TP_NOME as 'Tipo de Pagamento',PRD.NOME as 'Nome do Produto' FROM TB_VENDA V
	INNER JOIN TB_CLIENTE C 
		ON V.ID_CLIENTE = C. ID_CLIENTE
	INNER JOIN TB_TIPO_PAGAMENTO TP
		ON V.ID_TP = TP. ID_TP
	INNER JOIN ITENSVENDA ITV
		ON V.ID_VENDA = ITV. ID_VENDA
	INNER JOIN TB_PRODUTO PRD
		ON ITV.ID_PRODUTO = PRD. ID_PRODUTO
WHERE TP.ID_TP = 2;
2)SELECT * FROM TB_VENDA V
	right JOIN TB_CLIENTE C 
		ON V.ID_CLIENTE = C. ID_CLIENTE
	right JOIN TB_VENDA_PARCELAS VP
		ON V.ID_VENDA = VP.ID_VENDA
WHERE V.ID_VENDA = 1;
3)SELECT * FROM TB_PRODUTO P
	left JOIN TB_CATEGORIA C
		ON P.ID_CATEGORIA = C.ID_CATEGORIA
	left JOIN TB_SUBCATEGORIA SC
		ON P.ID_SUBCATEGORIA = SC. ID_SUBCATEGORIA;
Function
1)DELIMITER $$
create function RemoverNumero(cNOME char(255))
returns char(255)
begin
	set cNOME = replace(cNOME, '0' , ''),
		cNOME = replace(cNOME, '1' , ''),
		cNOME = replace(cNOME, '2' , ''),
		cNOME = replace(cNOME, '3' , ''),
		cNOME = replace(cNOME, '4' , ''),
		cNOME = replace(cNOME, '5' , ''),
		cNOME = replace(cNOME, '6' , ''),
		cNOME = replace(cNOME, '7' , ''),
		cNOME = replace(cNOME, '8' , ''),
		cNOME = replace(cNOME, '9' , '');
	return cNOME;
end;
$$
select RemoverNumero(NOME_CLIENTE) from TB_CLIENTE
TRIGGER
1)DELIMITER $$
	create trigger baixa_estoque
 after insert on itensvenda
 for each row
 begin
	update tb_produto
set tb_produto.quantidade_produto = tb_produto.quantidade_produto - new.ITV_QUANTIDADE
 where tb_produto.ID_PRODUTO = new.ID_PRODUTO;
end $$
ANTES
DEPOIS
insert into ItensVenda values(44,4,52.00,1,2);
Código abaixo irá funcionar a partir da versão 5.7.2 do my sql
2)DELIMITER $$
CREATE OR REPLACE TRIGGER SUBCATEGORIA 
AFTER UPDATE OR DELETE ON TB_SUBCATEGORIA
FOR EACH ROW 
BEGIN 
 	update TB_PRODUTO
 set TB_PRODUTO.ID_SUBCATEGORIA = ''
 where TB_VENDA.ID_SUBCATEGORIA = new.ID_SUBCATEGORIA;
END $$
ANTES
DEPOIS
update TB_SUBCATEGORIA
 set TB_SUBCATEGORIA.NOME_SUBCATEGORIA = 'Teste'
 where TB_SUBCATEGORIA.ID_SUBCATEGORIA = 1
PROCEDURES
1)create procedure consulta_produto ( varproduto integer)
	SELECT * FROM TB_PRODUTO P
	inner JOIN TB_CATEGORIA C
		ON P.ID_CATEGORIA = C.ID_CATEGORIA
	inner JOIN TB_SUBCATEGORIA SC
		ON P.ID_SUBCATEGORIA = SC. ID_SUBCATEGORIA
 where ID_PRODUTO = varproduto;
call consulta_produto(2);
2)create procedure consulta_cliente ( varcliente integer)
	SELECT * FROM TB_CLIENTE 
 where ID_CLIENTE = varcliente;
call consulta_cliente(2);