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 ConceitualDiagrama 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

Venda

Pessoa_fisica

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:VIEW valor_venda

Resultado

Tabela funcionario

Tabela pessoa_fisica

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.nomeORDER 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:

Resultado da Consulta

Tabela venda_entrega

Tabela funcionario

Tabela pessoa_fisica

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:

Resultado

Tabela lote

Tabela medicamento

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

• Consulta 12:

Resultado

Tabela venda

Tabela item_venda

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_estoqueFROM (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

ResultadoTabela 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:

Tabela venda

Tabela venda_entrega

View valor_entrega

Consultas

• Consulta 15:

Resultado

Tabela pessoa_fisica