697503324-Portfolio-Relatorio-Aula-Pratica-Programacao-e-desenvolvimento-de-Banco-de-Dados

Prévia do material em texto

<p>UNOPAR - UNIVERSIDADE DO NORTE DO PARANÁ</p><p>THIAGO MOREIRA BIANECK</p><p>RA: 3218661802</p><p>RELATÓRIO DE AULA PRÁTICA</p><p>Relatório técnico apresentado como requisito</p><p>para avaliação parcial na disciplina de</p><p>Programação e Desenvolvimento de Banco</p><p>de Dados.</p><p>Orientador: Profª. Jéssica Lopes.</p><p>PATO BRANCO -PR</p><p>2023</p><p>1 INTRODUÇÃO</p><p>A aula prática teve como foco a criação de um banco de dados utilizando a</p><p>linguagem SQL, com a posterior realização de operações de manipulação e acesso</p><p>aos dados. Para alcançar esse objetivo, foram utilizados o MySQL Community Server</p><p>e o MySQL Workbench como infraestrutura de suporte.</p><p>2 OBJETIVOS</p><p>Os objetivos principais desta aula prática foram:</p><p>• Criar um banco de dados denominado "Loja" usando o MySQL Server</p><p>e MySQL Workbench.</p><p>• Desenvolver a estrutura do banco de dados, incluindo tabelas,</p><p>seguindo o modelo definido em um Diagrama Entidade-</p><p>Relacionamento (DER) pré-definido.</p><p>• Realizar a inserção de dados nas tabelas do banco.</p><p>• Consultar dados armazenados, criando uma visão (View).</p><p>• Documentar todo o processo em um relatório.</p><p>3 MATERIAIS E MÉTODOS</p><p>Infraestrutura:</p><p>• MySQL Community Server.</p><p>• MySQL Workbench.</p><p>•</p><p>Procedimentos Práticos:</p><p>• Etapa 1: Criação da base de dados "Loja" com estruturas de dados</p><p>definidas no DER.</p><p>• Etapa 2: Criação do script "inserir.sql" para inserção de dados nas</p><p>tabelas.</p><p>• Etapa 3: Elaboração do script "consulta.sql" para criar uma visão de</p><p>contas não pagas.</p><p>4 RESULTADOS E DISCUSSÃO</p><p>Etapa 1: Criação da base de dados "Loja"</p><p>Utilizando o MySQL Workbench, foi criada a base de dados "Loja" com tabelas</p><p>representadas no DER. Foram respeitadas as regras, como autoincremento para</p><p>chaves primárias e enumeração para o campo "Situação" da tabela "ContaReceber".</p><p>O Script gerado foi o seguinte:</p><p>-- Schema loja</p><p>CREATE SCHEMA IF NOT EXISTS `loja` DEFAULT CHARACTER SET utf8 ;</p><p>USE `loja` ;</p><p>-- Table Estado</p><p>CREATE TABLE IF NOT EXISTS `loja`.`Estado` (</p><p>`ID` INT NOT NULL AUTO_INCREMENT,</p><p>`Nome` VARCHAR(50) NOT NULL,</p><p>`UF` CHAR(2) NOT NULL,</p><p>PRIMARY KEY (`ID`)</p><p>) ENGINE = InnoDB;</p><p>-- Table Municipio</p><p>CREATE TABLE IF NOT EXISTS `loja`.`Municipio` (</p><p>`ID` INT NOT NULL AUTO_INCREMENT,</p><p>`Estado_ID` INT NOT NULL,</p><p>`Nome` VARCHAR(80) NOT NULL,</p><p>`CodIBGE` INT NOT NULL,</p><p>PRIMARY KEY (`ID`)</p><p>) ENGINE = InnoDB;</p><p>-- Add foreign key to Municipio referencing Estado</p><p>ALTER TABLE `loja`.`Municipio`</p><p>ADD INDEX `fk_Municipio_Estado1_idx_idx` (`Estado_ID` ASC)</p><p>VISIBLE,</p><p>ADD CONSTRAINT `fk_Municipio_Estado1_idx`</p><p>FOREIGN KEY (`Estado_ID`)</p><p>REFERENCES `loja`.`Estado` (`ID`)</p><p>ON DELETE NO ACTION</p><p>ON UPDATE NO ACTION;</p><p>-- Table Cliente</p><p>CREATE TABLE IF NOT EXISTS `loja`.`Cliente` (</p><p>`ID` INT NOT NULL AUTO_INCREMENT,</p><p>`Nome` VARCHAR(80) NOT NULL,</p><p>`CPF` CHAR(11) NOT NULL,</p><p>`Celular` CHAR(11) NULL,</p><p>`EndLogradouro` VARCHAR(100) NOT NULL,</p><p>`EndNumero` VARCHAR(10) NOT NULL,</p><p>`EndMunicipio` INT NOT NULL,</p><p>`EndCEP` CHAR(8) NULL,</p><p>`Municipio_ID` INT NOT NULL,</p><p>PRIMARY KEY (`ID`)</p><p>) ENGINE = InnoDB;</p><p>-- Add foreign key to Cliente referencing Municipio</p><p>ALTER TABLE `loja`.`Cliente`</p><p>ADD INDEX `fk_Cliente_Municipio1_idx_idx` (`Municipio_ID` ASC)</p><p>VISIBLE,</p><p>ADD CONSTRAINT `fk_Cliente_Municipio1_idx`</p><p>FOREIGN KEY (`Municipio_ID`)</p><p>REFERENCES `loja`.`Municipio` (`ID`)</p><p>ON DELETE NO ACTION</p><p>ON UPDATE NO ACTION;</p><p>-- Table ContaReceber</p><p>CREATE TABLE IF NOT EXISTS `loja`.`ContaReceber` (</p><p>`ID` INT NOT NULL AUTO_INCREMENT,</p><p>`Cliente_ID` INT NOT NULL,</p><p>`FaturaVendaID` INT NULL,</p><p>`DataConta` DATE NOT NULL,</p><p>`DataVencimento` DATE NOT NULL,</p><p>`Valor` DECIMAL(18,2) NOT NULL,</p><p>`Situacao` ENUM('1', '2', '3') NOT NULL,</p><p>PRIMARY KEY (`ID`)</p><p>) ENGINE = InnoDB;</p><p>-- Add foreign key to ContaReceber referencing Cliente</p><p>ALTER TABLE `loja`.`ContaReceber`</p><p>ADD INDEX `fk_ContaReceber_Cliente_idx_idx` (`Cliente_ID` ASC)</p><p>VISIBLE,</p><p>ADD CONSTRAINT `fk_ContaReceber_Cliente_idx`</p><p>FOREIGN KEY (`Cliente_ID`)</p><p>REFERENCES `loja`.`Cliente` (`ID`)</p><p>ON DELETE NO ACTION</p><p>ON UPDATE NO ACTION;</p><p>Os passos da criação do script são os seguintes:</p><p>1. Criação do Schema:</p><p>CREATE SCHEMA IF NOT EXISTS `loja` DEFAULT CHARACTER SET utf8;</p><p>• Define o esquema "loja" se não existir, usando o conjunto de</p><p>caracteres utf8.</p><p>2. Seleção do Schema:</p><p>USE `loja`;</p><p>• Seleciona o esquema "loja" para a execução dos comandos</p><p>subsequentes.</p><p>3. Criação da Tabela Estado:</p><p>CREATE TABLE IF NOT EXISTS `loja`.`Estado` (</p><p>`ID` INT NOT NULL AUTO_INCREMENT,</p><p>`Nome` VARCHAR(50) NOT NULL,</p><p>`UF` CHAR(2) NOT NULL,</p><p>PRIMARY KEY (`ID`)</p><p>) ENGINE = InnoDB;</p><p>• Cria a tabela "Estado" com colunas ID, Nome e UF.</p><p>• Define a coluna ID como chave primária com autoincremento.</p><p>4. Criação da Tabela Municipio:</p><p>CREATE TABLE IF NOT EXISTS `loja`.`Municipio` (</p><p>`ID` INT NOT NULL AUTO_INCREMENT,</p><p>`Estado_ID` INT NOT NULL,</p><p>`Nome` VARCHAR(80) NOT NULL,</p><p>`CodIBGE` INT NOT NULL,</p><p>PRIMARY KEY (`ID`)</p><p>) ENGINE = InnoDB;</p><p>• Cria a tabela "Municipio" com colunas ID, Estado_ID, Nome e CodIBGE.</p><p>• Define a coluna ID como chave primária com autoincremento.</p><p>5. Adição de Foreign Key à Tabela Municipio:</p><p>ALTER TABLE `loja`.`Municipio`</p><p>ADD INDEX `fk_Municipio_Estado1_idx_idx` (`Estado_ID` ASC)</p><p>VISIBLE,</p><p>ADD CONSTRAINT `fk_Municipio_Estado1_idx`</p><p>FOREIGN KEY (`Estado_ID`)</p><p>REFERENCES `loja`.`Estado` (`ID`)</p><p>ON DELETE NO ACTION</p><p>ON UPDATE NO ACTION;</p><p>• Adiciona uma chave estrangeira (foreign key) à coluna Estado_ID na</p><p>tabela Municipio, referenciando a coluna ID na tabela Estado.</p><p>6. Criação da Tabela Cliente:</p><p>CREATE TABLE IF NOT EXISTS `loja`.`Cliente` (</p><p>`ID` INT NOT NULL AUTO_INCREMENT,</p><p>`Nome` VARCHAR(80) NOT NULL,</p><p>`CPF` CHAR(11) NOT NULL,</p><p>`Celular` CHAR(11) NULL,</p><p>`EndLogradouro` VARCHAR(100) NOT NULL,</p><p>`EndNumero` VARCHAR(10) NOT NULL,</p><p>`EndMunicipio` INT NOT NULL,</p><p>`EndCEP` CHAR(8) NULL,</p><p>`Municipio_ID` INT NOT NULL,</p><p>PRIMARY KEY (`ID`)</p><p>) ENGINE = InnoDB;</p><p>• Cria a tabela "Cliente" com colunas ID, Nome, CPF, Celular,</p><p>EndLogradouro, EndNumero, EndMunicipio, EndCEP e Municipio_ID.</p><p>• Define a coluna ID como chave primária com autoincremento.</p><p>7. Adição de Foreign Key à Tabela Cliente:</p><p>ALTER TABLE `loja`.`Cliente`</p><p>ADD INDEX `fk_Cliente_Municipio1_idx_idx` (`Municipio_ID` ASC)</p><p>VISIBLE,</p><p>ADD CONSTRAINT `fk_Cliente_Municipio1_idx`</p><p>FOREIGN KEY (`Municipio_ID`)</p><p>REFERENCES `loja`.`Municipio` (`ID`)</p><p>ON DELETE NO ACTION</p><p>ON UPDATE NO ACTION;</p><p>• Adiciona uma chave estrangeira à coluna Municipio_ID na tabela</p><p>Cliente, referenciando a coluna ID na tabela Municipio.</p><p>8. Criação da Tabela ContaReceber:</p><p>CREATE TABLE IF NOT EXISTS `loja`.`ContaReceber` (</p><p>`ID` INT NOT NULL AUTO_INCREMENT,</p><p>`Cliente_ID` INT NOT NULL,</p><p>`FaturaVendaID` INT NULL,</p><p>`DataConta` DATE NOT NULL,</p><p>`DataVencimento` DATE NOT NULL,</p><p>`Valor` DECIMAL(18,2) NOT NULL,</p><p>`Situacao` ENUM('1', '2', '3') NOT NULL,</p><p>PRIMARY KEY (`ID`)</p><p>) ENGINE = InnoDB;</p><p>• Cria a tabela "ContaReceber" com colunas ID, Cliente_ID,</p><p>FaturaVendaID, DataConta, DataVencimento, Valor e Situacao.</p><p>• Define a coluna ID como chave primária com autoincremento.</p><p>9. Adição de Foreign Key à Tabela ContaReceber:</p><p>ALTER TABLE `loja`.`ContaReceber`</p><p>ADD INDEX `fk_ContaReceber_Cliente_idx_idx` (`Cliente_ID` ASC)</p><p>VISIBLE,</p><p>ADD CONSTRAINT `fk_ContaReceber_Cliente_idx`</p><p>FOREIGN KEY (`Cliente_ID`)</p><p>REFERENCES `loja`.`Cliente` (`ID`)</p><p>ON DELETE NO ACTION</p><p>ON UPDATE NO ACTION;</p><p>• Adiciona uma chave estrangeira à coluna Cliente_ID na tabela</p><p>ContaReceber, referenciando a coluna ID na tabela Cliente.</p><p>Este script segue a ordem recomendada de criação de tabelas para evitar</p><p>problemas com chaves estrangeiras.</p><p>Etapa 2: Script "inserir.sql"</p><p>• Um script contendo comandos DML foi desenvolvido para inserir dados</p><p>nas tabelas do banco "Loja". Pelo menos três registros foram</p><p>adicionados por tabela, garantindo a variedade e representatividade dos</p><p>dados.</p><p>O Script criado foi o seguinte:</p><p>-- Inserir dados na tabela Estado</p><p>INSERT INTO `loja`.`Estado` (`Nome`, `UF`) VALUES</p><p>('São Paulo', 'SP'),</p><p>('Rio de Janeiro', 'RJ'),</p><p>('Minas Gerais', 'MG');</p><p>-- Inserir dados na tabela Municipio</p><p>INSERT INTO `loja`.`Municipio` (`Estado_ID`, `Nome`, `CodIBGE`)</p><p>VALUES</p><p>(1, 'São Paulo', 3550308),</p><p>(2, 'Rio de Janeiro', 3304557),</p><p>(3, 'Belo Horizonte', 3106200);</p><p>-- Inserir dados na tabela Cliente</p><p>INSERT INTO `loja`.`Cliente` (`Nome`, `CPF`, `Celular`,</p><p>`EndLogradouro`, `EndNumero`, `EndMunicipio`, `EndCEP`,</p><p>`Municipio_ID`) VALUES</p><p>('João Silva', '12345678901', '987654321', 'Rua A', '123', 1,</p><p>'01234567', 1),</p><p>('Maria Oliveira', '98765432101', '999888777', 'Rua B', '456',</p><p>2, '76543210', 2),</p><p>('Carlos Santos', '55544433322', '111222333', 'Rua C', '789',</p><p>3, '54321098', 3);</p><p>-- Inserir dados na tabela ContaReceber</p><p>INSERT INTO `loja`.`ContaReceber` (`Cliente_ID`, `FaturaVendaID`,</p><p>`DataConta`, `DataVencimento`, `Valor`, `Situacao`) VALUES</p><p>(1, 101, '2023-01-01', '2023-02-01', 1000.00, '1'),</p><p>(2, 102, '2023-02-01', '2023-03-01', 1500.50, '1'),</p><p>(3, 103, '2023-03-01', '2023-04-01', 2000.75, '1');</p><p>Explicação Passo a Passo do Script "inserir.sql":</p><p>1. Inserir dados na tabela Estado:</p><p>INSERT INTO `loja`.`Estado` (`Nome`, `UF`) VALUES</p><p>('São Paulo', 'SP'),</p><p>('Rio de Janeiro', 'RJ'),</p><p>('Minas Gerais', 'MG');</p><p>• Insere três registros na tabela `Estado`, cada um representando um</p><p>estado brasileiro com seu nome e UF.</p><p>2. Inserir dados na tabela Municipio:</p><p>INSERT INTO `loja`.`Municipio` (`Estado_ID`, `Nome`,</p><p>`CodIBGE`) VALUES</p><p>(1, 'São Paulo', 3550308),</p><p>(2, 'Rio de Janeiro', 3304557),</p><p>(3, 'Belo Horizonte', 3106200);</p><p>• Insere três registros na tabela `Municipio`, cada um representando uma</p><p>cidade, associada a um estado através da chave estrangeira</p><p>`Estado_ID`.</p><p>3. Inserir dados na tabela Cliente:</p><p>INSERT INTO `loja`.`Cliente` (`Nome`, `CPF`, `Celular`,</p><p>`EndLogradouro`, `EndNumero`, `EndMunicipio`, `EndCEP`,</p><p>`Municipio_ID`) VALUES</p><p>('João Silva', '12345678901', '987654321', 'Rua A', '123',</p><p>1, '01234567', 1),</p><p>('Maria Oliveira', '98765432101', '999888777', 'Rua B',</p><p>'456', 2, '76543210', 2),</p><p>('Carlos Santos', '55544433322', '111222333', 'Rua C', '789',</p><p>3, '54321098', 3);</p><p>• Insere três registros na tabela `Cliente`, cada um representando uma</p><p>pessoa com informações como nome, CPF, celular, endereço, e</p><p>associada a uma cidade através da chave estrangeira `Municipio_ID`.</p><p>4. Inserir dados na tabela ContaReceber:</p><p>INSERT INTO `loja`.`ContaReceber` (`Cliente_ID`,</p><p>`FaturaVendaID`, `DataConta`, `DataVencimento`, `Valor`,</p><p>`Situacao`) VALUES</p><p>(1, 101, '2023-01-01', '2023-02-01', 1000.00, '1'),</p><p>(2, 102, '2023-02-01', '2023-03-01', 1500.50, '1'),</p><p>(3, 103, '2023-03-01', '2023-04-01', 2000.75, '1');</p><p>• Insere três registros na tabela `ContaReceber`, cada um representando</p><p>uma conta a receber associada a um cliente, com informações como</p><p>data da conta, data de vencimento, valor e situação.</p><p>Este script popula as tabelas do banco de dados "loja" com dados de</p><p>exemplo para posterior utilização e teste. Certifique-se de adaptar os</p><p>valores conforme necessário para refletir os dados específicos que você</p><p>deseja inserir.</p><p>Etapa 3: Script "consulta.sql"</p><p>• Foi elaborado um script que cria uma visão (VIEW) chamada</p><p>"ContasNaoPagas" utilizando comandos DQL. Essa visão retorna</p><p>informações relevantes sobre as contas que ainda não foram pagas,</p><p>conforme especificado no checklist.</p><p>O Script criado foi o seguinte:</p><p>-- Criação da View para Contas Não Pagas</p><p>CREATE VIEW ContasNaoPagas AS</p><p>SELECT</p><p>CR.ID AS 'ID da conta a receber',</p><p>C.Nome AS 'Nome do Cliente',</p><p>C.CPF AS 'CPF do Cliente',</p><p>CR.DataVencimento AS 'Data de Vencimento',</p><p>CR.Valor AS 'Valor da Conta'</p><p>FROM</p><p>loja.ContaReceber CR</p><p>JOIN</p><p>loja.Cliente C ON CR.Cliente_ID = C.ID</p><p>WHERE</p><p>CR.Situacao = '1';</p><p>Explicação Passo a Passo do Script "consulta.sql":</p><p>1. Criação da View para Contas Não Pagas:</p><p>• Cria uma view chamada `ContasNaoPagas` que combina dados das</p><p>tabelas `ContaReceber` e `Cliente`.</p><p>• Seleciona as colunas desejadas: ID da conta a receber, Nome e CPF do</p><p>Cliente, Data de Vencimento e Valor da Conta.</p><p>• Utiliza a cláusula `JOIN` para relacionar as tabelas `ContaReceber` e</p><p>`Cliente` com base na chave estrangeira `Cliente_ID`.</p><p>• Utiliza a cláusula `WHERE` para filtrar apenas as contas com situação</p><p>igual a '1' (Conta registrada).</p><p>Esta view pode ser consultada posteriormente para obter informações sobre</p><p>todas as contas que ainda não foram pagas.</p><p>5 CONCLUSÕES</p><p>A aula prática proporcionou uma compreensão prática do processo de criação</p><p>e manipulação de bancos de dados usando SQL. O uso do MySQL Community Server</p><p>e MySQL Workbench facilitou a execução das tarefas propostas. A criação do banco</p><p>de dados, a inserção de dados e a consulta por meio de visões foram realizadas com</p><p>sucesso.</p><p>6 REFERÊNCIAS</p><p>[1] Documentação MySQL. Disponível em: https://dev.mysql.com/doc/</p>