ATPS - Programação Em Banco de Dados

ANHANGUERA EDUCACIONALFACULDADE DE NEGCIOS DE BELO HORIZONTE

SISTEMAS DE INFORMAO

PROGRAMAO EM BANCO DE DADOS ATIVIDADES PRTICAS SUPERVISIONADAS ATPS

Belo Horizonte, MG2014, 238Cpia total ou parcial no autorizada.BRUNO PETER RODRIGUES RA 1299791009LCIO RAIMUNDO RA 1299488822MARCELA MELO RA 1299480616WASHINGTON FERNANDES RA 6820483180

ATIVIDADES PRTICAS SUPERVISIONADAS ATPS

Relatrio das Atividades Prticas Supervisionadas ATPS, elaborado pelos alunos supra citados, do terceiro e quarto perodo do curso de Sistemas de Informao da Faculdade Anhanguera de Belo Horizonte, de acordo com as diretivas previamente estabelecidas pela instituio, entregue ao professor de Programao em Banco de Dados para avaliao e obteno de crdito na matria. Orientador: Fbio Storck

Belo Horizonte, MG2014, LISTA DE FIGURAS

Figura 1: Modelo de Entidade e Relacionamento (Conceitual)10Figura 2: Modelo Relacional (Lgico)11Figura 3: Dados da tabela 'Produto'18Figura 4: Dados da tabela 'Entrada'19Figura 5: Dados da tabela 'Sada'19Figura 6: Dados da tabela 'Caixa'19Figura 7: Dados da tabela 'Movimentao'20Figura 8: Dados da tabela caixa aps atualizao24Figura 9: Dados da tabela movimentao aps atualizao24Figura 10: Comando Where na tabela 'Produto' em SQL Server25Figura 11: Dados da tabela produto aps o 1 where25Figura 12: Dados da tabela produto aps o 2 where25Figura 13: Delete na tabela Produto27Figura 14: Delete na tabela Entrada27Figura 15: Delete na tabela Movimentao27

LISTA DE TABELAS

Tabela 1: Listagem dos requisitos levantados9

LISTA DE CDIGOS

Cdigo 1: Criao do Banco e das Tabelas em SQL Server 201213Cdigo 2: Insero de dados na estrutura de Banco de Dados em SQL Server18Cdigo 3: Update nas tabelas 'Caixa' e 'Movimentao' em SQL Server24Cdigo 4: Delete em dados na estrutura do banco em SQL Server26Cdigo 5: Procedure Movimentao de Entrada de Produtos28Cdigo 6: Procedure Movimentao de Sada de Produtos29Cdigo 7: Procedure Clculo dos valores de movimentao de caixa30Cdigo 8: Registro da movimentao de sada de produtos31Cdigo 9: Trigger Alimentar 'Movimentao' ao Inserir32Cdigo 10: Calcular valores de caixa ao Inserir33Cdigo 11: Calcular valores de caixa ao atualizar33Cdigo 12: Trigger Alimentar estoque ao inserir produto34Cdigo 13:Trigger Alimentar estoque ao atualizar produto34Cdigo 14: Trigger alimenta estoque ao deletar entrada de Produto35Cdigo 15: Trigger alimenta estoque ao dar sada no produto35Cdigo 16: Alimenta estoque ao atualizar sada de produto36Cdigo 17: Alimenta estoque ao deletar sada de produto37

SUMRIO

LISTA DE FIGURAS3LISTA DE TABELAS4LISTA DE CDIGOS51.INTRODUO82.BANCO DE DADOS92.1Anlise de Requisitos92.2Projeto Conceitual102.3Projeto Lgico112.4Projeto Fsico123.ESTRUTURA DO BANCO DE DADOS143.1Insert143.2Update203.3Where243.4Delete264.CDIGOS DO BANCO DE DADOS284.1Procedures284.1.1Movimentao de Estoque na Entrada de Produtos284.1.2Movimentao de Estoque na Sada de Produtos294.1.3Clculo dos Valores para Movimentao de Caixa304.1.4Registro de Movimentao de Sada de Produtos314.2Triggers324.2.1Alimentar Movimentao ao Inserir dados em Caixa324.2.2Calcular Valores de Caixa ao Inserir Novos Dados324.2.3Calcular Valores de Caixa ao Atualizar dados334.2.4Alimenta Estoque ao Inserir Produto344.2.5Alimenta Estoque ao Atualizar Entrada de Produto344.2.6Alimenta Estoque ao Deletar Entrada de Produto354.2.7Alimenta Estoque ao dar Sada no Produto354.2.8Alimenta Estoque ao Atualizar Sada de Produto364.2.9Alimenta Estoque ao Deletar Sada de Produto375.CONCLUSO386.REFERNCIAS39

1. INTRODUO

Com a crescente tecnologia, quem no se adapta integrao de dados utilizando os sistemas de informao acaba perdendo mercado para a concorrncia, por isso importante investir na coleta, armazenamento, segurana e minerao dos dados objetivando a obteno de informaes para melhor aplicar em seu negcio. No contexto da tecnologia, o gerenciamento de banco de dados ganha destaque primordial por ser responsvel direto no tratamento de dados e informaes recorrentes da programao e da soluo aprimorada de lida com tecnologia e negociao direta.Uma grande padaria de SP, pertinente essa situao, disps de investimento para uma soluo de banco de dados com a finalidade de garantir melhores resultados de gerenciamento e controle de seu fluxo de caixa e de seu estoque de produtos para que no lhe falte mercadorias e nem haja estocamento desnecessrio. Este relatrio tem como objetivo mostrar os passos principais da construo do banco de dados e da programao para automatizar os clculos financeiros envolvidos, minimizando o trabalho e, consequentemente, as falhas por erros humanos.

2. BANCO DE DADOS2.1 Anlise de RequisitosDe acordo com a aderncia com o cliente, o sistema dever ser capaz de controlar a movimentao de compra e venda registrando as operaes pertinentes ao cadastro e movimentao de estoque, bem como custo de produtos e lucro real obtido no negcio. Ainda segundo tal levantamento, ficou destacado as seguintes necessidades:

Tabelas: Produto {Cdigo do Produto, Nome do Produto, Unidade, Preo de Custo, Preo de Venda}

Entrada{Cdigo da Entrada, Cdigo do Produto, Quantidade, Data Entrada, Hora Entrada}

Sada{Cdigo da Sada, Cdigo do Produto, Quantidade, Data Sada, Hora Sada}

Caixa{Cdigo do Caixa, Cdigo da Sada, Valor Da Venda}

Movimentao{Cdigo da Movimentao, Cdigo do Caixa, Data Movimentao, Hora Movimentao, Valor Recebido}

Mdulos: Compra de Produtos Cadastro de Produtos / Entrada no Estoque

Venda de ProdutosVenda de Produtos / Sada no estoque

Frente de CaixaOperao de venda

EstoqueMovimentao de estoque (entrada - Sada) [Relatrio de movimentao custo de estoque]

Movimentao GerencialRelatrio com detalhes da movimentao.

Tabela 1: Listagem dos requisitos levantados

2.2 Projeto Conceitual

Figura 1: Modelo de Entidade e Relacionamento (Conceitual)

2.3 Projeto Lgico

Figura 2: Modelo Relacional (Lgico)

2.4 Projeto FsicoCREATE database Padaria_ATPSBDGO

USE [Padaria_ATPSBD]GO

CREATE TABLE [dbo].[Produto]([codProduto] [int] IDENTITY(1,1) NOT NULL,[nomeProduto] [nvarchar](50) NOT NULL,[unidade] [nchar](3) NOT NULL,[precoCusto] [float] NOT NULL,[precoVenda] [float] NOT NULL, CONSTRAINT [PK_Produto] PRIMARY KEY CLUSTERED ([codProduto] ASC))GO

CREATE TABLE [dbo].[Entrada]([codEntrada] [int] IDENTITY(1,1) NOT NULL,[codProduto] [int] NOT NULL,[quantidade] [int] NOT NULL,[dataEntrada] [date] NOT NULL,[horaEntrada] [time](7) NOT NULL, CONSTRAINT [PK_Entrada] PRIMARY KEY CLUSTERED ([codEntrada] ASC),CONSTRAINT [FK_Entrada_Entrada] FOREIGN KEY([codProduto])REFERENCES [dbo].[Produto] ([codProduto]))GO

CREATE TABLE [dbo].[Saida]([codSaida] [int] IDENTITY(1,1) NOT NULL,[codProduto] [int] NOT NULL,[quantidade] [int] NOT NULL,[dataSaida] [date] NOT NULL,[horaSaida] [time](7) NOT NULL, CONSTRAINT [PK_Saida] PRIMARY KEY CLUSTERED ([codSaida] ASC),CONSTRAINT [FK_Saida_Produto] FOREIGN KEY([codProduto])REFERENCES [dbo].[Produto] ([codProduto]))GO

CREATE TABLE [dbo].[Caixa]([codCaixa] [int] IDENTITY(1,1) NOT NULL,[codSaida] [int] NOT NULL,[valorVenda] [float] NOT NULL, CONSTRAINT [PK_Caixa] PRIMARY KEY CLUSTERED ([codCaixa] ASC),CONSTRAINT [FK_Caixa_Saida] FOREIGN KEY([codSaida])REFERENCES [dbo].[Saida] ([codSaida]))GO

CREATE TABLE [dbo].[Movimentacao]([codMovimentacao] [int] IDENTITY(1,1) NOT NULL,[codCaixa] [int] NOT NULL,[dataMovimentacao] [date] NOT NULL,[horaMovimentacao] [time](7) NOT NULL,[valorRecebido] [float] NOT NULL, CONSTRAINT [PK_Movimentacao] PRIMARY KEY CLUSTERED ([codMovimentacao] ASC),CONSTRAINT [FK_Movimentacao_Caixa] FOREIGN KEY([codCaixa])REFERENCES [dbo].[Caixa] ([codCaixa]))GO

Cdigo 1: Criao do Banco e das Tabelas em SQL Server 2012

3. ESTRUTURA DO BANCO DE DADOSDe acordo com a estrutura apresentada anteriormente, baseado nas tabelas criadas, utilizamos os comandos insert, update, where e delete para testar instrues e funcionalidades do banco de dados.

3.1 InsertUSE [Padaria_ATPSBD]GO

/* Insert na tabela PRODUTO */

INSERT INTO [dbo].ProdutoVALUES ('Po Frances', 'kg', 10.55, 19.90)GO

INSERT INTO [dbo].ProdutoVALUES ('Po Doce', 'kg', 11.20, 21.90)GO

INSERT INTO [dbo].ProdutoVALUES ('Po Especial', 'kg', 14.30, 29.90)GO

INSERT INTO [dbo].ProdutoVALUES ('Biscoito de Polvilho', 'pct', 1.55, 3.90)GO

INSERT INTO [dbo].ProdutoVALUES ('Leite Itamb', 'l', 0.90, 2.98)GO

INSERT INTO [dbo].ProdutoVALUES ('Manteiga Itamb', 'kg', 4.00, 7.90)GO

INSERT INTO [dbo].ProdutoVALUES ('Refrigerante Coca-Cola', 'pet', 2.05, 4.85)GO

INSERT INTO [dbo].ProdutoVALUES ('Iogurt Itamb', 'uni', 0.70, 3.10)GO

INSERT INTO [dbo].ProdutoVALUES ('Bala Ice Kiss', 'uni', 0.2, 0.8)GO

INSERT INTO [dbo].ProdutoVALUES ('gua Mineral', 'uni', 1.00, 2.30)GO

SELECT *FROM [dbo].ProdutoORDER BY [nomeProduto]GO

/* Insert tabela ENTRADA */

INSERT INTO [dbo].EntradaVALUES (1, 200, '02/10/2014', '16:44:00')GO










SELECT *FROM [dbo].EntradaGO

/* Insert tabela SAIDA*/

INSERT INTO [dbo].SaidaVALUES (6, 1, '03/10/2014', '08:03:01')GO













SELECT *FROM [dbo].SaidaGO

SELECT [dbo].Produto.codProduto, [dbo].Produto.precoVenda, [dbo].Saida.quantidade, [dbo].Saida.codSaidaFROM [dbo].Produto, [dbo].SaidaWHERE [dbo].Saida.codProduto = [dbo].Produto.codProdutoGO

/* Insert na tabela CAIXA */

INSERT INTO [dbo].CaixaVALUES (1, 0)GO












SELECT * FROM [dbo].CaixaGO

/* Insert tabela MOVIMENTAO */

INSERT INTO [dbo].MovimentacaoVALUES(1, '03/10/2014', '09:00:00', 0)GO












SELECT * FROM [dbo].MovimentacaoGO

Cdigo 2: Insero de dados na estrutura de Banco de Dados em SQL Server

Figura 3: Dados da tabela 'Produto'

Figura 4: Dados da tabela 'Entrada'

Figura 5: Dados da tabela 'Sada'

Figura 6: Dados da tabela 'Caixa'

Figura 7: Dados da tabela 'Movimentao'3.2 UpdateUSE [Padaria_ATPSBD]GO

/* verificando os dados da tabela 'Saida' */

SELECT *FROM [dbo].SaidaGO

/* verificando os dados da tabela 'Caixa' */

SELECT *FROM [dbo].CaixaGO

/* Update 'valorVenda' tabela 'Caixa' */

UPDATE [dbo].CaixaSET valorVenda =((SELECT [dbo].Produto.precoVenda FROM [dbo].Produto, [dbo].Saida WHERE [dbo].Produto.codProduto = [dbo].Saida.codProduto AND [dbo].Saida.codSaida = 1) * (SELECT [dbo].Saida.quantidade FROM [dbo].Saida, [dbo].Produto WHERE [dbo].Produto.codProduto = [dbo].Saida.codProduto AND [dbo].Saida.codSaida = 1))WHERE codSaida = 1GO












/* Verificando a tabela 'Movimentao' */

SELECT * FROM [dbo].MovimentacaoORDER BY codCaixaGO

/* Update tabela 'Movimentao' */

UPDATE [dbo].MovimentacaoSET valorRecebido = (SELECT [dbo].Caixa.valorVenda FROM [dbo].Caixa WHERE codCaixa = 1)+2.1WHERE codCaixa = 1GO











UPDATE [dbo].MovimentacaoSET valorRecebido = (SELECT [dbo].Caixa.valorVenda FROM [dbo].Caixa WHERE codCaixa = 12)+20WHERE codCaixa = 12GO

Cdigo 3: Update nas tabelas 'Caixa' e 'Movimentao' em SQL Server

Figura 8: Dados da tabela caixa aps atualizao

Figura 9: Dados da tabela movimentao aps atualizao

3.3 Where

USE [Padaria_ATPSBD]GO

/* Where na tabela produto */

SELECT [dbo].Produto.precoVendaFROM [dbo].ProdutoWHERE [dbo].Produto.precoVenda > 20.90;GOSELECT [dbo].Produto.codProdutoFROM [dbo].ProdutoWHERE [dbo].Produto.codProduto >= 90;GO

Figura 10: Comando Where na tabela 'Produto' em SQL Server

Figura 11: Dados da tabela produto aps o 1 where

Figura 12: Dados da tabela produto aps o 2 where

3.4 DeleteUSE [Padaria_ATPSBD]GO

/* delete na tabela produto */delete from dbo.Produtowhere codProduto = 5

delete from dbo.Produtowhere codProduto = 10

select *from dbo.Produto

/* delete na tabela entrada */

delete from dbo.entradawhere codProduto = 2




SELECT *FROM [dbo].EntradaGO

/* delete na tabela movimentaao */

delete from dbo.movimentacaowhere codMovimentacao = 1

delete from dbo.movimentacaowhere codMovimentacao = 12

SELECT * FROM [dbo].movimentacaoGO

Cdigo 4: Delete em dados na estrutura do banco em SQL Server

Figura 13: Delete na tabela Produto

Figura 14: Delete na tabela Entrada

Figura 15: Delete na tabela Movimentao

4. CDIGOS DO BANCO DE DADOS

4.1 ProceduresProcedure uma coleo de comandos em SQL para dispensamento de Banco de dados. Suas principais funes so o encapsulamento de tarefas repetitivas, reduo do trfego na rede, melhoria de performance, segurana e integridade dos dados.Um procedure aceita parmetros de entrada e retorna um valor de status indicando falha ou aceitao da instruo, sendo executada excepcionalmente no servidor, garantindo alta disponibilidade de processamento.

4.1.1 Movimentao de Estoque na Entrada de Produtos

USE [PadariaBD]GO/****** Object: StoredProcedure [dbo].[sp_Estoque_Entrada] Script Date: 18/11/2014 20:13:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_Estoque_Entrada](@idProduto INT, @idEntrada INT)ASBEGINDECLARE @cont INT;DECLARE @quantEntrada INT;

SELECT @cont=COUNT(*) FROM PadariaBD.dbo.Estoque WHERE IdProduto = @idProdutoSELECT @quantEntrada=Quantidade FROM PadariaBD.dbo.Entrada WHERE IdEntrada = @idEntrada;

IF @cont > 0UPDATE PadariaBD.dbo.Estoque SET Quantidade = Quantidade + @quantEntrada WHERE IdProduto = @idProdutoELSEINSERT INTO PadariaBD.dbo.Estoque(IdProduto,Quantidade)VALUES(@idProduto,@quantEntrada)END

Cdigo 5: Procedure Movimentao de Entrada de Produtos

4.1.2 Movimentao de Estoque na Sada de Produtos

USE [PadariaBD]GO/****** Object: StoredProcedure [dbo].[sp_Estoque_Saida] Script Date: 18/11/2014 20:22:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_Estoque_Saida](@idProduto INT, @idSaida INT)ASBEGINDECLARE @cont INT;DECLARE @quantSaida INT;

SELECT @cont=COUNT(*) FROM PadariaBD.dbo.Estoque WHERE IdProduto = @idProdutoSELECT @quantSaida=Quantidade FROM PadariaBD.dbo.Saida WHERE IdSaida = @idSaida;

IF @cont > 0UPDATE PadariaBD.dbo.Estoque SET Quantidade = Quantidade - @quantSaida WHERE IdProduto = @idProdutoELSEINSERT INTO PadariaBD.dbo.Estoque(IdProduto,Quantidade)VALUES(@idProduto,@quantSaida)END

Cdigo 6: Procedure Movimentao de Sada de Produtos

4.1.3 Clculo dos Valores para Movimentao de Caixa

USE [PadariaBD]GO/****** Object: StoredProcedure [dbo].[sp_ValoresCaixa] Script Date: 18/11/2014 20:23:57 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_ValoresCaixa](@idProduto INT, @idSaida INT, @idCaixa INT)ASBEGINDECLARE @valorTotal NUMERIC(5,2)DECLARE @troco NUMERIC(5,2)DECLARE @valorProduto NUMERIC(5,2)DECLARE @valorRecebido NUMERIC(5,2)DECLARE @quantidade INT

SELECT @valorProduto=PrecoVenda FROM Produto WHERE IdProduto=@idProdutoSELECT @quantidade=Quantidade FROM Saida WHERE IdSaida=@idSaidaSELECT @valorRecebido=ValorRecebido FROM Caixa WHERE IdCaixa=@idCaixaSET @valorTotal=@valorProduto * @quantidadeSET @troco=@valorRecebido - @valorTotal

INSERT INTO Caixa(IdSaida,ValorRecebido,Troco,ValorTotal)VALUES(@idSaida,@valorRecebido,@troco,@valorTotal)

END

Cdigo 7: Procedure Clculo dos valores de movimentao de caixa

4.1.4 Registro de Movimentao de Sada de Produtos

USE [PadariaBD]GO/****** Object: StoredProcedure [dbo].[sp_Movimentacao] Script Date: 18/11/2014 20:27:46 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_Movimentacao](@id_produto INT, @id_Caixa INT)ASBEGINDECLARE @numVenda INT;DECLARE @nomeProduto VARCHAR(50);DECLARE @data DATETIME;DECLARE @quant_Estoque INT;DECLARE @situacaoEstoque VARCHAR(50);DECLARE @estoquemax INT;DECLARE @estoquemin INT;SELECT @numVenda= IdSaida FROM PadariaBD.dbo.Caixa WHERE IdCaixa = @id_Caixa;SELECT @nomeProduto= NomeProduto FROM PadariaBD.dbo.Produto WHERE IdProduto = @id_produto;SELECT @data = SYSDATETIME();SELECT @quant_Estoque= Quantidade FROM PadariaBD.dbo.Estoque WHERE IdProduto = @id_produto;SELECT @estoquemax=EstoqueMaximo, @estoquemin=EstoqueMinimo FROM Produto WHERE IdProduto = @id_produto;IF @quant_Estoque >= @estoquemaxSELECT @situacaoEstoque = ' ESTOQUE ALTO ';ELSEIF @quant_Estoque

Documents

ATPS - Programação Em Banco de Dados