Baixe o app para aproveitar ainda mais
Prévia do material em texto
THIAGO RODRIGUES LUCAS FERRAZ MATHEUS VIEIRA LIMA MATOS KAUAN NUNES SANTOS VITA VINICIUS SAMUEL NASCIMENTO BOMFIM ATIVIDADE EXTRA DE ENGENHARIA DE SOFTWARE (Tese ou Dissertação) Apresentada ao Curso de Ciência da Computação, Setor de TI, Universidade Wyden Uniruy, como requisito para realização da Atividade extra. Orientador: Talita Rocha Pinheiro Coordenador: Elisandro Lima SALVADOR - BA 2022 ANTÔNIO CARLOS SENA DAVI DOS SANTOS CHAGAS MATHEUS VIEIRA LIMA MATOS KAUAN NUNES SANTOS VITA VINICIUS SAMUEL NASCIMENTO BOMFIM TRABALHO DE BANCO DE DADOS LOJA DE JOGOS (Tese ou Dissertação) Apresentada ao Curso de Ciência da Computação, Setor de TI, Universidade Wyden Uniruy, como requisito para realização da Av. Orientador: Talita Rocha Pinheiro Coordenador: Elisandro Lima SALVADOR-BA 2022 Sumário : 1. Introdução 4 2. Modelo Conceitual 5 2.1 Modelo textual 5 2.2 Diagrama entidade relacionamento 5 4. Modelo físico: 13 5. Alimento o BD: 18 6. Segurança 21 6.1 usuário 21 6.2 Criação de usuários. 21 6.3 Código para permissões. 21 7. Consultas no banco 22 7.1 Consultas Simples 22 7.3 Consultas Múltiplas 25 7.4 Consultas Aninhadas 27 9. Gatilhos (Triggers) 31 10. Normalização 32 11. Conclusão 33 1. Introdução Realizaremos um trabalho de banco de dados. Que tem o objetivo de compor 80% da nota da av1 onde teremos que fazer um banco de dados do zero a caráter da equipe utilizando das ferramentas mysql e mysql server ensinado em aula. Faremos um banco de dados de uma loja de eletrônicos e jogos usando os métodos aprendidos em aula, como o modelo textual onde situa como irá funcionar o nosso banco de dados da loja, utilizaremos também o modelo conceitual DER (Diagrama Entidade Relacionamento) onde se encontra o fluxograma do nosso banco de dados ligando todas as entidades com as outras e seus respectivos relacionamentos. Por último, o modelo físico onde estarão as aplicações da nossa loja, tal como os create table, insert,consulta, etc. 2. Modelo Conceitual Nosso sistema se trata de uma loja de eletrônicos e jogos que fornece produtos/jogos para os clientes com o objetivo de facilitar as compras online em meio da pandemia automatizando processo comercial de compra e venda. O banco de dados da nossa loja funcionará de forma em que os fornecedores irão fornecer os seus respectivos produtos para o depósito, no depósito haverão funcionários que separam os produtos por setores que são respectivamente hardware, periféricos e jogos. E os clientes poderão realizar as compras através de pedidos, que irão avaliar se está disponível o produto escolhido no depósito. 2.1 Modelo textual 1) Cadastro de Clientes Dados necessários: CPF, nome, email, telefone, endereço e código do cliente. 2) Cadastro de Funcionários Dados necessários: CPF, nome, telefone, endereço e código do funcionário. 3) Cadastro de Fornecedores Dados necessários: nome da empresa e o código dos fornecedores. 4) Cadastro de Depósito Dados necessários: Um catálogo do depósito, setor. 5) Cadastro de Funcionários Dados necessários: CPF, nome,telefone e endereço. 6) Cadastro de produto Dados necessários: código do produto, jogos, periféricos e peças dos computadores. 7) Pedidos Dados necessários: Código do pedido, quantidade e descrição do pedido. 2.2 Diagrama entidade relacionamento Figura 1 - DER Fonte: Autoria própria ,2022. Todas as ligações entre as tabelas são 1-N (um para muitos) entre elas as obrigatórias são (pedidos para produtos, produtos para pedidos, clientes para pedidos e fornecedores para depósito.) 3. Modelo Lógico : Figura 2 - Modelo Lógico Fonte: Autoria própria ,2022. Figura 3 Fonte: Autoria própria ,2022. Depósito - Fornecedores : Um depósito pode se ter diversos fornecedores mas os fornecedores só podem ter um depósito, além disso os fornecedores enviam seus diversos itens até esse depósito também é necessário se ter pelo menos um depósito para se ter um fornecedor e vice-versa. Figura 4 Fonte: Autoria própria ,2022. Depósito - Funcionários : Para se ter o depósito é necessário se ter pelo menos um funcionário mas ele pode ter n funcionários ir para se ter funcionários é necessário se ter pelo menos um depósito. Figura 5 Fonte: Autoria própria ,2022. Funcionários - Tel_fun : O funcionário necessita se ter pelo menos um telefone mas ele pode ter n telefones registrados e para se ter um telefone registrado necessita de pelo menos um funcionário Figura 6 Fonte: Autoria própria ,2022. Funcionários - Endereço_func : Os funcionários necessitam ter pelo menos uma residência, mas ele pode possuir n residências registradas e para se ter endereço funcionário é necessário se ter pelo menos um funcionário e vice-versa. Figura 7 Fonte: Autoria própria ,2022. Tel_cliente - clientes : Necessariamente cliente precisa ter pelo menos um número mas ele pode ter diversos números e para existir telefone cliente é necessário ter pelo menos um cliente Figura 8 Fonte: Autoria própria ,2022. Endereço - cliente : Necessariamente o cliente precisa ter pelo menos um endereço, mas ele pode ter vários outros endereços e para existir endereço cliente é necessário se ter ao menos um cliente. Figura 9 Fonte: Autoria própria ,2022. Clientes - Pedidos : Necessariamente para existir pedido é preciso um cliente além disso cliente pode fazer n pedidos, mas pedidos não irão existir caso o cliente não exista. Figura 10 Fonte: Autoria própria ,2022. Produto - pedidos : Um pedido pode ter n produtos mas não ao contrário além disso nem em uma dessas tabelas necessariamente precisa da outra para existir. Figura 11 Fonte: Autoria própria ,2022. Produto - depósito : Necessário ter pelo menos um produto para se ter um depósito e a mesma coisa ao contrário, no entanto o depósito pode se ter diversos produtos. 4. Modelo físico: Abaixo podemos ver os Creates Tables: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DA TE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUT ION'; CREATE SCHEMA IF NOT EXISTS `loja` DEFAULT CHARACTER SET utf8 ; USE `loja` ; CREATE TABLE IF NOT EXISTS `loja`.`clientes` ( `Cpf` INT(11) NOT NULL, `id_cliente` INT NOT NULL, `nome` VARCHAR(45) NOT NULL, `email` VARCHAR(45) NULL, PRIMARY KEY (`Cpf`)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`endereço_cliente` ( `endereços_cliente` VARCHAR(200) NOT NULL, `clientes_Cpf` INT NOT NULL, INDEX `fk_endereço_cliente_clientes1_idx` (`clientes_Cpf` ASC) VISIBLE, PRIMARY KEY (`clientes_Cpf`), CONSTRAINT `fk_endereço_cliente_clientes1` FOREIGN KEY (`clientes_Cpf`) REFERENCES `loja`.`clientes` (`Cpf`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`tel_cliente` ( `tel_cliente` INT NOT NULL, `clientes_Cpf` INT NOT NULL, INDEX `fk_tel_cliente_clientes1_idx` (`clientes_Cpf` ASC) VISIBLE, PRIMARY KEY (`clientes_Cpf`), CONSTRAINT `fk_tel_cliente_clientes1` FOREIGN KEY (`clientes_Cpf`) REFERENCES `loja`.`clientes` (`Cpf`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`Fornecedores` ( `idFornecedores` INT NOT NULL, `Nome_da_empresa` VARCHAR(45) NULL, PRIMARY KEY (`idFornecedores`)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`Funcionarios` ( `cpf_func` INT(12) NOT NULL, `id_func` INT NOT NULL, `nome_func` VARCHAR(45) NOT NULL, `salario_func` FLOAT NOT NULL, PRIMARY KEY (`cpf_func`)) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`Deposito` ( `catalogo` VARCHAR(45) NOT NULL, `setor` INT NOT NULL, `Fornecedores_idFornecedores` INT NOT NULL, `Funcionarios_cpf_func` INT(12) NOT NULL, INDEX `fk_Deposito_Fornecedores1_idx` (`Fornecedores_idFornecedores` ASC) VISIBLE, PRIMARY KEY (`catalogo`), INDEX `fk_Deposito_Funcionarios1_idx` (`Funcionarios_cpf_func` ASC) VISIBLE, CONSTRAINT `fk_Deposito_Fornecedores1`FOREIGN KEY (`Fornecedores_idFornecedores`) REFERENCES `loja`.`Fornecedores` (`idFornecedores`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Deposito_Funcionarios1` FOREIGN KEY (`Funcionarios_cpf_func`) REFERENCES `loja`.`Funcionarios` (`cpf_func`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`endereço_func` ( `endereço_funç` VARCHAR(45) NOT NULL, `Funcionarios_cpf_func` INT(12) NOT NULL, INDEX `fk_endereço_func_Funcionarios1_idx` (`Funcionarios_cpf_func` ASC) VISIBLE, PRIMARY KEY (`Funcionarios_cpf_func`), CONSTRAINT `fk_endereço_func_Funcionarios1` FOREIGN KEY (`Funcionarios_cpf_func`) REFERENCES `loja`.`Funcionarios` (`cpf_func`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`tel_func` ( `tel_func` INT NOT NULL, `Funcionarios_cpf_func` INT(12) NOT NULL, INDEX `fk_tel_func_Funcionarios1_idx` (`Funcionarios_cpf_func` ASC) VISIBLE, PRIMARY KEY (`Funcionarios_cpf_func`), CONSTRAINT `fk_tel_func_Funcionarios1` FOREIGN KEY (`Funcionarios_cpf_func`) REFERENCES `loja`.`Funcionarios` (`cpf_func`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`pedidos` ( `id_pedido` INT NOT NULL, `quant_pedidos` INT NOT NULL, `desc_pedidos` VARCHAR(200) NOT NULL, `clientes_Cpf` INT NOT NULL, `Funcionarios_cpf_func` INT(12) NOT NULL, INDEX `fk_pedidos_clientes1_idx` (`clientes_Cpf` ASC) VISIBLE, PRIMARY KEY (`id_pedido`), INDEX `fk_pedidos_Funcionarios1_idx` (`Funcionarios_cpf_func` ASC) VISIBLE, CONSTRAINT `fk_pedidos_clientes1` FOREIGN KEY (`clientes_Cpf`) REFERENCES `loja`.`clientes` (`Cpf`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_pedidos_Funcionarios1` FOREIGN KEY (`Funcionarios_cpf_func`) REFERENCES `loja`.`Funcionarios` (`cpf_func`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `loja`.`produto` ( `id_produto` INT NOT NULL, `perifericos` VARCHAR(45) NULL, `hardware` VARCHAR(45) NULL, `jogos` VARCHAR(45) NULL, `Deposito_catalogo` VARCHAR(45) NOT NULL, `pedidos_id_pedido` INT NOT NULL, PRIMARY KEY (`id_produto`), INDEX `fk_produto_Deposito1_idx` (`Deposito_catalogo` ASC) VISIBLE, INDEX `fk_produto_pedidos1_idx` (`pedidos_id_pedido` ASC) VISIBLE, CONSTRAINT `fk_produto_Deposito1` FOREIGN KEY (`Deposito_catalogo`) REFERENCES `loja`.`Deposito` (`catalogo`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_produto_pedidos1` FOREIGN KEY (`pedidos_id_pedido`) REFERENCES `loja`.`pedidos` (`id_pedido`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; 5. Alimento o BD: Logo abaixo podemos ver os INSERT: INSERT INTO clientes (Cpf, id_cliente, nome, email) VALUES (02101746093, 01, 'thiago', 'thiago@gmail.com'); INSERT INTO clientes (Cpf, id_cliente, nome, email) VALUES (0365873128, 02, 'julia', 'juju@gmail.com'); INSERT INTO clientes (Cpf, id_cliente, nome, email) VALUES (235119004, 03, 'maria', 'mary@gmail.com'); INSERT INTO clientes (Cpf, id_cliente, nome, email) VALUES (714040540, 04, 'camila', 'cami@gmail.com'); INSERT INTO clientes (Cpf, id_cliente, nome, email) VALUES (3157246, 05, 'carlos', 'caca@gmail.com'); INSERT INTO clientes (Cpf, id_cliente, nome, email) VALUES (961198, 06, 'jose', 'zeze@gmail.com'); INSERT INTO tel_cliente VALUES (7101,961198); INSERT INTO tel_cliente VALUES (7501,3157246),(7102,714040540),(1101,235119004),(2101,0365873 128),(7103,02101746093); INSERT INTO endereço_cliente VALUES ('Rua texeira barros',961198),('Rua baixada santista',3157246),('Rua oliveira coutos',714040540), ('Rua vasco da gama',235119004),('Rua emanoel vitorino',0365873128), ('Rua padre miguel',02101746093); INSERT INTO funcionarios (cpf_func, id_func, nome_func, salario_func) VALUES (2517931, 10, 'matheus',1399.50); INSERT INTO funcionarios (cpf_func, id_func, nome_func, salario_func) VALUES (696774, 12, 'alan',1700.43); INSERT INTO funcionarios (cpf_func, id_func, nome_func, salario_func) VALUES (48075, 13, 'antonio',1850.43); INSERT INTO funcionarios (cpf_func, id_func, nome_func, salario_func) VALUES (2359, 14, 'cesar',1920.99); INSERT INTO funcionarios (cpf_func, id_func, nome_func, salario_func) VALUES (734, 15, 'paula',3500.00); INSERT INTO funcionarios (cpf_func, id_func, nome_func, salario_func) VALUES (312, 11, 'laura',1500.00); INSERT INTO funcionarios (cpf_func, id_func, nome_func, salario_func) VALUES (75, 16, 'ana',2200.50); INSERT INTO tel_func VALUES (7101,75), (2101,312), (7515,734), (7133,2359),(7112,48075),(7105,696774); INSERT INTO endereço_func VALUES ('Rua Viela Nascimento',75),('Rua José Joaquim César',312),('Rua Luminárias',734),('Rua Doutor Carlos Augusto de Campos',2359),('Rua Cristóvão da Costa',48075),('Rua Omega',696774); INSERT INTO fornecedores VALUES (1, 'Sony'), (2,'Microsoft'), (3,'Blizzard'), (4, 'Nintendo'); INSERT INTO fornecedores VALUES (5, 'Bandai'), (6,'Rockstar'); INSERT INTO fornecedores VALUES (7, 'EA'), (8,'Ubisoft'); INSERT INTO fornecedores VALUES (9, 'Konami'), (10,'Capcom'); INSERT INTO DEPOSITO VALUES ('mouse',1,2,75), ('teclado',1,4,312), ('fone',1,1,734), ('jogo_midiaFisica',3,7,2359), ('jogo_midiadigital',3,8,48075),('jogo_tabuleiro',3,5,696774), ('monitor',2,2,734), ('placa de video',2,6,312), ('processador',2,9,734); INSERT INTO PEDIDOS -- ID_PEDIDOS, QUANTIDADE_PEDIDOS VALUES (01,5,'perifericos',02101746093,75),(02,4,'hardware',036587312 8,312),(03,3,'jogos',235119004,734),(04,2,'perifericos',714040 540,2359),(05,1,'jogos',3157246,48075),(06,10,'hardware',96119 8,696774); INSERT INTO PRODUTO (id_produto,perifericos,Deposito_catalogo,pedidos_id_pedido) VALUES (1,'Mouse red dragon XL 600-1800dpi','mouse',01),(2,'Teclado mecanico razer XJERGX','teclado',04); INSERT INTO PRODUTO (id_produto,hardware,Deposito_catalogo,pedidos_id_pedido) VALUES (3,'GTX 1660 SUPER 6GB','placa de video',02),(4,'i9-10900KF Cache 20MB , 3.7 GHZ','processador',02); INSERT INTO PRODUTO (id_produto,jogos,Deposito_catalogo,pedidos_id_pedido) VALUES (5,'Grand theft auto V','jogo_midiaFisica',03),(6,'God of war','jogo_midiadigital',05); 6. Segurança 6.1 usuário Figura x - Permissões dos usuários Fonte: Autoria própria ,2022. 6.2 Criação de usuários. CREATE USER 'Administrador'@'localhost' IDENTIFIED BY 'root'; CREATE USER 'Mateus'@'localhost' IDENTIFIED BY '12345'; CREATE USER 'Davi'@'localhost' IDENTIFIED BY '12345678'; CREATE USER 'Talita'@'localhost' IDENTIFIED BY 'password'; CREATE USER 'Cassio'@'localhost' IDENTIFIED BY 'besta123'; 6.3 Código para permissões. GRANT ALL PRIVILEGES ON LOJA TO 'Administrador'@'localhost'; GRANT SELECT, DELETE ON LOJA TO 'Mateus'@'localhost'; GRANT INSERT, UPDATE ON LOJA TO 'Davi'@'localhost'; GRANT SELECT ON LOJA TO 'TALITA'@'localhost'; GRANT SELECT, UPDATE ON LOJA TO 'Cassio'@'localhost'; 7. Consultas no banco 7.1 Consultas Simples Consulta 1 : Figura x Fonte: Autoria própria ,2022. Podemos ver aqui todas as colunas listadas na tabela “clientes”. Consulta 2 : Figura x Fonte: Autoria própria ,2022. Nessa consulta especificamente fiz a busca na coluna "catálogo" na tabela "depósito" e pedi todas as informações desta coluna. Consulta 3 : Figura x Fonte: Autoria própria ,2022. A consulta apresenta todos os telefones dos clientes que temos na tabela “tel_cliente”. 7.2 Condições Múltiplas Consulta 1 : Figura x Fonte: Autoria própria ,2022. Foi solicitada nessa consulta todas as colunas da tabela “clientes” para aqueles que possuem o ID 6 e 1. Consulta 2 : Figura x Fonte: Autoria própria ,2022. O select acima solicita a coluna "catálogo" da tabela deposito apenas dos itens listados na coluna “setor” com 1 e 2. Trazendo os itens dentro dela. Consulta 3 : Figura x Fonte: Autoria própria ,2022. O select traz todas as colunas da tabela“pedidos” quando a condição de que a coluna “quant_pedidos” é maior que 1 e dentro da coluna “desc_pedidos” precisa estar escrito “perifericos”. 7.3 Consultas Múltiplas Consulta 1 : Figura x Fonte: Autoria própria ,2022. A consulta junta a tabela “pedidos” e a tabela "funcionários" trazendo as informações do id do pedido e a quantidade ao qual o funcionário está atrelado junto com o seu CPF. Consulta 2 : Figura x Fonte: Autoria própria ,2022. Essa consulta junta a tabela “clientes” e “pedidos” trazendo as informações do cliente (CPF e nome) e dos pedidos (ID e quantidade.) Consulta 3 : Figura x Fonte: Autoria própria ,2022. A junção da tabela “fornecedores” e “produto” faz com que seja possível trazer à consulta do ID dos fornecedores o que eles disponibilizam e o nome da empresa responsável. 7.4 Consultas Aninhadas Consulta 1 : Figura x Fonte: Autoria própria ,2022. A consulta busca todas as colunas de “pedidos” e o CPF do funcionário envolvido nesse pedido apenas se o funcionário tiver o salário maior que 1500 reais. Consulta 2 : Figura x Fonte: Autoria própria ,2022. O select pede todas as colunas da tabela “tel_cliente” apenas quando o CPF listado nessa tabela coincidir com o listado na tabela “clientes”. Consulta 3 : Figura x Fonte: Autoria própria ,2022. A consulta acima lista todas as informações da tabela "depósito" quando a coluna “id_produto” da tabela “produto” for listada na na chave estrangeira “pedidos_id_pedidos” trazendo assim. 8. Visões (Views) View 1: Essa view possibilita que você veja todas as informações dos fornecedores (id, nome e o que ela vende para a nossa loja). Tornando importante essa view para eventual necessidade de repor o estoque. View 2: A criação dessa view é muito importante já que será muito usada para obtermos todas as informações necessárias do cliente de forma que desafogue o banco de dados para outras buscas mais específicas. 9. Gatilhos (Triggers) Gatilho Nº1: CREATE DEFINER=`kauan_v`@`%` TRIGGER `funcionarios_BEFORE_INSERT` BEFORE INSERT ON `funcionarios` FOR EACH ROW BEGIN set new.salario_func = (new.salario_func - 10); END -Evento: Mudança no salário -Condição: Ao ser dado um insert na tabela funcionário -Ação: Altera a coluna "salário" reduzindo o salário dos funcionários em R $10. Gatilho Nº2: CREATE DEFINER=`kauan_v`@`%` TRIGGER `funcionarios_BEFORE_DELETE` BEFORE DELETE ON `funcionarios` FOR EACH ROW BEGIN set new.salario_func = (new.salario_func + 500); END -Evento: Aumento de salário para o funcionário. -Condição: Após um delete na tabela “funcionario” -Ação: Aumenta o salários dos outros funcionários em R$ 500 10. Normalização 1FN : Removemos dentro das tabelas clientes e funcionários os campos de endereço e telefone, criamos 4 novas tabelas chamadas: Endereço_cliente, Endereço_func, Tel_cliente e Tel_func assim respeitando que esta forma os atributos precisam ser atômicos, o que significa que as tabelas não podem ter valores repetidos e nem atributos possuindo mais de um valor. 2FN : Removemos a tabela do site pois só realizava o armazenamento do link do site, o que não faria sentido. Removemos a tabela setor para a normalização da forma normal 2, pois desobedece a regra da exclusividade de dependência da chave primária. 3FN : Removemos a tabela fgts_func para a normalização da forma normal 3 pois envolvia o conceito de dependência transitiva. 11. Conclusão Esse trabalho abordou a criação de um banco de dados de uma loja de produtos eletrônicos utilizando o SQL para desenvolver o mesmo. É possível concluir que conseguimos montar com êxito o banco para a loja. Cumprimos com todos os objetivos propostos, passando desde um modelo lógico do banco até o modelo físico e funcional do mesmo. Onde detalhamos cada etapa de desenvolvimento. O trabalho foi importante para o nosso desenvolvimento visto que montamos do zero um banco de dados, levando a todos da equipe aprofundar os conhecimentos apresentados em sala de aula, com o objetivo de trazer o melhor resultado dessa nossa criação.
Compartilhar