Projeto Exemplo_ Banco de Vendas

(1)

Projeto de banco de dados de Vendas

Este visa apresentar o modelo descritivo, modelo conceitual, o modelo relacional e Modelo físico de um banco de dados de vendas.

Modelo Descritivo

Este é um banco de dados de uma mercearia. Nesta mercearia são vendidas produtos para clientes.

Cada venda é feita numa data, tem um código específico e feita para um cliente, por um funcionário, pode ter um ou mais produtos e tem uma forma de pagamento.

Para os produtos deseja-se armazenar: o código, nome, categoria, quantidade em estoque e valor unitário, com todos os campos obrigatórios.

Para cada produto da venda deseja-se armazenar a quantidade comprada e o valor do produto e um produto pode aparecer mais de uma vez na mesma venda.

Os clientes podem ser pessoas físicas ou jurídicas. Para os clientes Pessoa Física serão armazenados: código, nome, cpf e data de nascimento, não podendo ter o cpf repetido e a data de nascimento não obrigatória. Para os clientes Pessoa Jurídica serão armazenados: código, Razao Social, CNPJ não podendo ter o CNPJ repetido. Para os clientes podem ser armazenados um ou mais telefones.

Para os funcionários serão armazenados: código, nome, cpf, data de nascimento e pontuação, não podendo ter cpf repetido e a data de nascimento não obrigatória, porém o restante sendo não nulo.

Um funcionário tem um supervisor que também é um funcionário e deve ser armazenada a data em que passou a supervisionar. Um funcinário pode supervisionar um ou mais funcionários e um funcionário pode ser ou não supervisionado por outro funcionário.

Um funcionário pode ou não ter dependentes. Para os dependentes armazenar o nome, sexo e data de nascimento.

(2)

(3)

(4)

Cliente

codcli, nome_razao, cpf, cnpj, dataNasc, rua, bairro, cidade, cep, uf, fone, tipo

Regras

codcli  Chave primária cpf  unique

cnpj unique

Todos os campos not null, exceto CPF, CNPJ, dataNasc e informações de endereço.

Fone_cliente codCLiente, fone Regras

codCLiente, fone  chave primária codCLiente references Cliente (codCli)

Funcionario

codFunc, nome, cpf, dataNasc, pontuação, Supervisor

Regras

codFunc  Chave primária cpf  unique

codFuncSup referencia Funcionario(codFunc) Todos os campos not null, exceto dataNasc. Dependente

codFunc, sequencial, nome, sexo e data de nascimento

Regras

codFunc, sequencial  Chave primária composta

codFunc referencia Funcionario(codFunc)

Categoria

codCateg, descricao Regras

codcateg  chave primária Todos os campos não nulo Produto

codProd, nome, codCateg, qtdEst, valUnit Regras

codProd  chave primária

codCateg referencia Categoria (codCateg) Todos os campos não nulo, exceto qtdEst FormaPag

codFPag, descricao Regras

codFPag  chave primária Todos os campos não nulo Venda

codVenda, data, codCli, codFunc, codFPag Regras

codVenda  chave primária codCli referencia Cliente (codCli)

codFunc referencia Funcionário (codFunc) codFPag referencia FormaPag (codFPag) Todos os campos obrigatório.

Itens

codItem, codVenda, codProd, qtd, valorUnit Regras

codItem, codVenda e codProd  chave primária composta

codVenda referencia Venda (cdoVenda) codProd referencia Produto (codProd)

(5)

(6)

(7)

Comandos SQL de criação e alimentação do banco

Antes de executar os comados abaixo deverá ser criado um banco de dados chamado Vendas.

Create Table Cliente(

codCli integer not null primary key, nome varchar(40) not null,

cpf_cnpj char(14) not null, rua varchar(50), bairro varchar(45), cidade varchar(45), cep char(8), uf char(2), fone varchar(15) , dataNasc date,

tipo Integer /* atribuir 0 - PF ou 1 - PJ */ );

Create unique index iu_cpf_cnpj_cli on cliente (cpf_cnpj); Create Table fone_cliente(

codCLiente integer references Cliente (codCli), fone varchar(11) );

Create Table Funcionario(

codFunc integer not null primary key, nome varchar(40) not null,

cpf char(11) not null, dataNasc date,

pontuacao integer default 0,

codFuncSup integer references Funcionario(codFunc) );

Create unique index iu_cpf_cnpj_funcionario on funcionario (cpf); Create Table Dependente(

codFunc integer not null, sequencial integer not null, nome varchar(40) not null, cpf char(11) not null, dataNasc date,

primary key (codFUnc,sequencial)); Create Table Categoria(

codCateg integer not null primary key, descriao varchar(40) not null );

Create Table Produto(

codProd integer not null primary key, nome varchar(40) not null,

codCateg integer not null references Categoria (codCateg), qtdEst integer not null,

(8)

Create Table FormaPag(

codFPag integer not null primary key, descriao varchar(40) not null ); Create Table venda(

codVenda integer not null primary key, data date not null,

codCli integer not null references Cliente (codCli),

codFunc integer not null references Funcionario (codFunc), codFPag integer not null references FormaPag (codFPag) ); Create table itens(

codItem integer not null,

codVenda integer not null references Venda (codVenda), codProd integer not null references Produto (codProd), qtd integer default 1 not null,

valorUnit float not null,

primary key (codItem,codVenda,codProd) ); commit;

/*Comandos DML:*/

Insert into cliente values (1,’Jose Barnabé’,’12345678904’,’Rua da Vitoria’,’Torre’,’Recife’,’52369789’,’PE’,’34215698’,’08/28/1965’,0); Insert into cliente values (2,’Maria Santos’,’78945612303’,’Rua da Mata’,’Torre’,’Recife’,’52379789’,’PE’,’34287896’,’09/30/1945’,0); Insert into cliente values (3,’Marcos Bezerra’,’45612378908’,’Alameda da Conquista’,’Derbi’,’Recife’,’52399789’,’PE’,’34256987’,’10/02/1975’,0); commit;

Insert into fone_cliente values (1,’8134322589’); Insert into fone_cliente values (1,’8188759863’); commit;

Insert into funcionario values (1,'Jose Silva','12345678904','08/28/1965',0,null); Insert into funcionario values (2,'Maria Santos','78945612303','09/30/1945',25,1); Insert into funcionario values (3,'Marcos Bezerra','45612378908','10/02/1975',53,null); commit;

Insert into dependente values (1,1,'Josiana Silva','12345678901','09/05/1982'); Insert into dependente values (1,2,'Josimara Silva','01123456789','01/07/1978'); commit;

Insert into categoria values (1,’Frios’); Insert into categoria values (2,’Massas’); Insert into categoria values (3,’Laticíneos’);

(9)

commit;

Insert into formapag values (1,’Dinheiro’);

Insert into formapag values (2,’Cartão Mater Card’); Insert into formapag values (3, ’Cartão Hiper Card’); commit;

Insert into produto values (1,’Salsicha Hot Dog Perdigão’,1,100,2.5); Insert into produto values (2,’Peito de Peru’,1,50,5.5);

Insert into produto values (3,’Queijo Prato’,1,80,10.5); Insert into produto values (8,’Queijo Mussarela’,1,30,12.0); Insert into produto values (4,’Macarrão Seabra’,2,60,6.0); commit;

Insert into produto values (5,’Pizza pronta’,2,60,6.0); Insert into produto values (6,’Iogurte Santana’,3,20,4.5); Insert into produto values (7,’Leite Desnatado’,3,55,8.5); commit;

Insert into venda values (1,’01/20/2006’,1,1,1); Insert into itens values (1,1,1,10,2.5);

Insert into itens values (2,1,3,5,10.5); Insert into itens values (3,1,4,5,6.0); commit;

(10)

Exemplos de Relatórios a serem implamentados:

1. Quantidade de vendas por funcionário, no mês de janeiro. select f.nome, count(*) qtd_vendas_efetuadas

from venda v, funcionario f where v.codfunc = f.codfunc

and v.data between '01/01/2006' and '01/31/2006' group by f.nome

2. Quantidade de produtos e de itens por venda no dia 20/01/2006. select v.codvenda, count(*) qtd_produtos, sum(qtd) total_itens from venda v, itens i

where i.codvenda = v.codvenda and v.data = '01/20/2006' group by v.codvenda

3. Quantidade de produtos, Quantidade de itens, Valor do Estoque, Maior valor do produto e Valor médio dos produtos.

select count(*) qtd_produtos, sum(qtdest) total_itens, sum(qtdest*valunit) valor_estoque,

max(valunit) maior_valor, avg(valunit) valor_medio from produto

4. Nome do produto, valor atual, o valor com 10% e diferença de preço com o aumento.

select nome, valunit, valunit * 1.1 acrescimo, (valunit * 0.1) diferenca from produto

5. Nomes e telefones dos clientes que compraram no mês de Janeiro de 2006. select distinct c.nome,c.fone

from venda v, cliente c where v.codcli = c.codcli

and data between '01-01-2006' and '01-31-2006'

6. Nomes e valores dos produtos que foram vendidos no dia 20/01/2006. select p.nome,i.valorunit

from venda v, itens i, produto p where v.data='20-jan-2006' and v.codvenda = i.codvenda and p.codprod = i.codprod

7. Apresentar o nome do funcionário e a sua pontuação. select nome, pontuacao from funcionario

(11)

select count(*) as qtdProdutos, sum(qtdEst * valUnit) as ValorEstoque from Produto

9. Listar os nomes dos clientes e as datas das vendas feitas pelo funcionário José Silva no mês de janeiro de 2006.

select c.nome nome_CLIENTE, v.data from cliente c, venda v, funcionario f where v.codcli = c.codcli

and f.codfunc = v.codfunc

upper(f.nome) = 'JOSE SILVA' and

v.data between '01-01-2006' and '01-31-2006'

10. Apresentar a quantidade de compras feitas por cliente no ano de 2006. select c.nome, count(*) qtd_compras

from venda v, cliente c where v.codcli = c.codcli

and v.data between '01/01/2006' and '12/31/2006' group by c.nome

11. Nome do funcionário, nome do cliente, forma de pagamento e valor total das vendas efetuadas em Janeiro de 2006.

select f.nome funcionario, c.nome cliente,

fp.descriao forma_pagamento, sum(i.qtd*i.valorunit) total_venda from funcionario f, formapag fp, venda v, cliente c, itens i

where v.codfunc = f.codfunc and v.codcli = c.codcli and v.codfpag = fp.codfpag and i.codvenda = v.codvenda

and v.data between '01/01/2006' and '01/31/2006' group by f.nome, c.nome, fp.descriao

12. Nome da categoria, quantidade de produtos e valor do estoque em dinheiro. select c.descriao categoria, count(*) qtd_produtos, sum(qtdest*valunit) valor_estoque from produto p, categoria c

where p.codcateg = c.codcateg group by c.descriao

13. Apresentar os produtos que custam mais que o valor médio dos produtos de sua categoria.

select pe.nome from produto pe

where valunit > ( select avg(valunit) from produto pi where pi.codcateg = pe.codcateg) 14. Alterar o nome da Funcionária Maria Santos para Maria Santos Fragoso da Silva. update funcionario

(12)

set nome = 'Maria Santos Fragoso da Silva' where codfunc = 2;

commit;

15. Script para inserir uma venda para o dia 30 de janeiro de 2006 feita por José Silva para o Cliente José Barnabé, onde foi vendido 5 pacotes de macarrão. Lembrar de atualizar o estoque do produto macarrão.

insert into venda values (5,'01/30/2006',1,1,1); insert into itens values (1,5,4,5,6);

update produto set qtdest = qtdest - 5 where codprod = 4; commit;

16. Excluir a venda anterior.

delete from itens where codvenda = 5; delete from venda where codvenda = 5; commit;

17. Listar o nome e endereço dos clientes cadastrados, ordenando por nome. select nome, rua || ', ' || bairro || ', ' || cidade || ', ' || cep || ', ' || uf endereco from cliente

order by nome

18. Apresentar tabela de preços de produtos, ordenando por nome. select nome, valunit from produto

order by nome

19. Apresentar o código da venda, o nome funcionário responsável e o nome cliente, para as vendas que foram efetuadas no mês de Janeiro de 2006.

select v.codvenda, f.nome funcionario, c.nome cliente from funcionario f, venda v, cliente c

where v.codfunc = f.codfunc and v.codcli = c.codcli

and v.data between '01/01/2006' and '01/31/2006'

20. Apresentar os nomes dos produtos que têm maior valor. select nome, valunit from produto

where valunit = (select max(valunit) from produto)

21. Aumentar em 10% os valores dos produtos da categoria Massas. update produto

set valunit = valunit * 1.1

(13)

22. Apresentar os nomes dos clientes que compraram mais de 2 vezes até hoje. select c.nome

from venda v join cliente c on (v.codcli = c.codcli) group by c.nome

having count(*) > 2

23. Listar o funcionario e nome do seu supervisor. Apresentar os funcionarios que não têm supervisor também.

select f.nome, f2.nome

from funcionario f LEFT JOIN funcionario f2 on (f.codFunc = f2.codFuncSup) Observação.: Usou-se o left join para apreentar todos os funcionários mesmo que não tenham supervisores.

24. Listar os funcionários e os nomes e data de nascimento de seus dependentes. Select f.nome, d.nome as Nome_dependente, d.dtNascimento

From funcionario f LEFT JOIN dependente d on (f.codFunc = d.codFunc)

Observação.: Usou-se o left join para apreentar todos os funcionários mesmo que não tenham dependetes.