Projeto Farmácia Paul
Eduardo Costa
Gabriel Cypriano
Rodrigo Calhau
Escopo do Sistema
• Controle de Estoque
– aviso automático de medicamentos com
estoque abaixo de um limite configurável
– aviso automático de lotes com validade
próxima do vencimento (configurável)
• Controle de Vendas
• Controle de Funcionários
• Controle de Clientes
Projeto Conceitual – parte 1
Projeto Conceitual – parte 2
Projeto Conceitual (UML com atributos)
Projeto Conceitual
Diagrama de Estados - Lote
Mapeamento de heranças
• Herança parcial
– Podem existir Vendas que não
sejam VendaEntrega
• Abordagem com apenas uma
tabela utilizando flags não
serve, pois não suporta
atributos not null de
VendaEntrega
• Utilizamos abordagem de duas
tabelas (superclasse e
subclasse)
Mapeamento de heranças
• Herança total
– Não podem existir PessoaFisica
que não seja das subclasses
• Herança sobreposta
– Pode haver PessoaFisica que seja
tanto Cliente quanto Funcionario
• Abordagem com apenas uma
tabela utilizando flags não serve,
pois não suporta atributos not
null de VendaEntrega
• Abordagem com duas tabelas
(subclasses) não serve, pois não
é uma herança disjunta
• Utilizamos abordagem de três
tabelas (superclasse e
subclasses)
Outros Mapeamentos
• Relação NxN com atributos ItemVenda foi
mapeada para uma tabela item_venda
• Atributo multivalorado Telefones de
PessoaFisica foi mapeado para uma
tabela telefones_pessoa_fisica (1a. Forma
normal)
• Atributos derivados foram mapeados nas
Views
Projeto Lógico 1FN – parte 1
Projeto Lógico 1FN – parte 2
Dependências Funcionais
(diagrama parcial da 1FN)
• Tabela Medicamento desnormalizada
Dependências Funcionais
(diagrama parcial da 3FN)
• Tabela Medicamento desnormalizada
Projeto Lógico 3FN (parcial)
Criação do Schema
• Domínios
Criação do Schema
• Tabelas (1)
Criação do Schema
• Tabelas (2)
Criação do Schema
• Tabelas (3)
Criação do Schema
• Tabelas (4)
Criação do Schema
• Tabelas (5)
Criação do Schema
• Tabelas (6)
Criação do Schema
• Tabelas (7)
Criação do Schema
• Tabelas (8)
Criação do Schema
• Tabelas (9)
Criação do Schema
• Tabelas (10)
Criação do Schema
• Views
Criação do Schema
• Restrições(1)
Criação do Schema
• Restrições(2)
Consultas
• Consulta 1: Dentre os funcionários que trabalham no caixa,
recupere seu nome e o valor total de vendas efetuadas (quanto já
vendeu), em ordem decrescente do valor de total de vendas.
SELECT nome, SUM (valor_item)
FROM item_venda, venda, pessoa_fisica
WHERE (item_venda.num_boleto_fiscal = venda.num_boleto_fiscal)
GROUP BY venda.cpf_caixa, nome, cpf
HAVING pessoa_fisica.cpf = cpf_caixa
ORDER BY SUM DESC
Item_Venda
Pessoa_fisica
Venda
Resultado
Consultas
• Consulta 2: Recupere o valor total de descontos concedidos, via cupons
de desconto, por mês, ordenados por ordem decrescente de valor
SELECT round(SUM(descontos.valor_desconto), 2) AS valor_descontos,
descontos.mes
FROM (SELECT (valor_item * 100 / (100 - desconto_medicamento)) AS
valor_desconto, to_char(data, 'Month/ YYYY') AS mes
FROM (item_venda NATURAL JOIN venda)) AS descontos
GROUP BY descontos.mes
ORDER BY SUM(descontos.valor_desconto) DESC;
Plano de Execução
Consultas
• Consulta 3:
• Enunciado: Recupere para cada funcionário o seu nome e o valor
de descontos concedidos por ele em cada mês.
• Consulta:
Consultas
• Consulta 3:
Tabela funcionario
VIEW valor_venda
Tabela pessoa_fisica
Resultado
Consultas
• Consulta 4: Dentre os clientes que residem na cidade de
Vitória, recupere seu nome e o valor total de compras
efetuadas, em ordem decrescente do valor total de compras
efetuadas
SELECT nome, SUM(item_venda.valor_item)
FROM (((item_venda JOIN venda ON venda.num_boleto_fiscal =
item_venda.num_boleto_fiscal)
JOIN endereco ON endereco.cpf_pessoa_fisica =
venda.cpf_cliente)
JOIN pessoa_fisica ON pessoa_fisica.cpf = venda.cpf_cliente)
WHERE endereco.cidade = 'Vitoria'
GROUP BY pessoa_fisica.nome
ORDER BY SUM DESC
Resultado
Consultas
• Consulta 5: Recupere os nomes dos medicamentos mais vendidos,
ordenados pelo número de unidades vendidas
•
•
•
•
•
SELECT nome, SUM(item_venda.num_unidades_medicamento)
FROM ((item_venda JOIN lote ON codigo_lote=codigo)
JOIN medicamento ON codigo_barras_medicamento=codigo_barras)
GROUP BY medicamento.nome
ORDER BY SUM(item_venda.num_unidades_medicamento) DESC;
Resultado
Consultas
• Consulta 6:
• Enunciado: Recupere para cada funcionário o seu nome e a
quantidade de entregas por ele efetuada, em ordem decrescente do
número de entregas.
• Consulta:
Consultas
• Consulta 6:
Tabela funcionario
Tabela venda_entrega
Tabela pessoa_fisica
Resultado da Consulta
Consultas
• Consulta 7: Recupere a razão social e o número de lotes
devolvidos por ano de cada distribuidor, em ordem decrescente de
lotes devolvidos
SELECT distribuidor.razao_social, COUNT(*) AS nDevolucoes
FROM lote, distribuidor, devolucao_lote
WHERE (lote.cnpj_distribuidor = distribuidor.cnpj
AND devolucao_lote.codigo_lote = lote.codigo)
GROUP BY distribuidor.razao_social
ORDER BY COUNT(*) DESC
Resultado
Consultas
•
Consulta 8: Recupere para cada medicamento o seu nome e sua
quantidade em estoque
•
•
•
•
•
SELECT nome, SUM(qtde_estoque) AS quantidade_estoque
FROM (medicamento JOIN lote ON
codigo_barras_medicamento=codigo_barras)
WHERE lote.qtde_estoque > 0
GROUP BY nome
ORDER BY SUM(qtde_estoque) DESC;
Resultado
Plano de Execução
Consultas
• Consulta 9:
• Enunciado: Recupere para o conhecimento medicamento ‘Gadernal’
as dosagens existentes e a quantidade em estoque de cada uma
das dosagens, ordenado pela quantidade em estoque.
• Consulta:
Consultas
• Consulta 9:
Tabela lote
Tabela medicamento
Resultado
Consultas
• Consulta 10: Recupere para as cidades com mais de 5 entregas
o seu nome e a quantidade de entrega, em ordem decrescente do
numero de entregas.
•
•
•
•
•
SELECT cidade, COUNT(*) AS nEntregas
FROM (venda_entrega JOIN endereco ON
venda_entrega.cpf_endereco = endereco.cpf_pessoa_fisica)
GROUP BY cidade
HAVING COUNT(*) > 5
ORDER BY nEntregas DESC
Resultado
Consultas
• Consulta 11: Para cada cliente, recupere seu nome e a quantidade de cupons
de desconto já utilizados por ele, ordenando pela ordem decrescente da
quantidade de cupons
•
•
•
•
•
SELECT nome, SUM(item_venda.num_unidades_medicamento)
FROM ((item_venda JOIN lote ON codigo_lote=codigo)
JOIN medicamento ON codigo_barras_medicamento=codigo_barras)
GROUP BY medicamento.nome
ORDER BY SUM(item_venda.num_unidades_medicamento) DESC
Resultado
Consultas
• Consulta 12:
• Enunciado: Recupere a quantidade medicamentos genéricos
contidos em vendas que foram entregues em Vila Velha.
• Consulta:
Consultas
• Consulta 12:
Tabela lote
Tabela medicamento
Consultas
Tabela venda
• Consulta 12:
Tabela item_venda
Resultado
Consultas
• Consulta 13: Recupere para cada lote cujo tempo de expiração seja menor
que o especificado no medicamento que ele representa o nome do
medicamento e a quantidade em estoque dele
SELECT tempo_expiracao_lote.codigo, nome, qtde_estoque
FROM (tempo_expiracao_lote JOIN medicamento ON
codigo_barras_medicamento=codigo_barras)
WHERE tempo_expiracao_lote.limite_dias_expiracao <
medicamento.limite_dias_expiracao
ORDER BY qtde_estoque DESC
Resultado
Plano de Execução
Consultas
• Consulta 14:
• Enunciado: Recupere para cada lote o seu código e seu tempo de
expiração em dias e o medicamento que ele refere.
• Consulta:
Consultas
• Consulta 14:
• Enunciado: Recupere para cada lote o seu código e seu tempo de
expiração em dias e o medicamento que ele refere.
• Consulta:
Consultas
• Consulta 14:
VIEW tempo_expiracao_lote
Resultado
Tabela medicamento
Consultas
• Consulta 15:
• Enunciado: Recupere para cada venda o numero do seu boleto, o
nome do caixa que efetuou a venda, o nome do cliente (caso
possua), o nome do entregador (caso possua) e o seu valor.
• Consulta:
Consultas
• Consulta 15:
View valor_entrega
Tabela venda
Tabela venda_entrega
Consultas
• Consulta 15:
Tabela pessoa_fisica
Resultado