Projeto Final - Padaria

1 Objetivo

• Criar uma aplicação de banco de dados para uma empresa usando pelo menos quatro tabelas.

2 Desenvolvimento

Foi escolhido como aplicação uma implementação de um banco de dados para uma padaria. Logo no início foram planejadas como seriam as tabelas e em quais schemas elas seriam encaixadas, foi optado por criar dois schemas, o padaria, que conteria as tabelas relacionadas a parte de venda dos produtos e o administracao que conteria as tabelas relacionadas aos funcionários e logs das compras:

CREATE SCHEMA IF NOT EXISTS padaria;

CREATE SCHEMA IF NOT EXISTS administracao;

No schema padaria foram criadas tabelas para o cliente, itens em estoque, pedido do cliente e uma tabela para ligar o pedido do clientes com os itens desse pedido tendo o seguinte diagrama lógico:

E o seguinte código SQL:

CREATE TABLE padaria.tb_cliente(
	id_cliente    SERIAL    CONSTRAINT pk_id_cliente  PRIMARY KEY,
	nome_completo   VARCHAR(200)    CONSTRAINT nn_nome_completo  NOT NULL,
	telefone    VARCHAR(11)    CONSTRAINT nn_telefone  NOT NULL,
	rua    VARCHAR(200)    CONSTRAINT nn_rua  NOT NULL,
	bairro    VARCHAR(200)    CONSTRAINT nn_bairro  NOT NULL,
	numero    INTEGER    CONSTRAINT nn_numero  NOT NULL
);

CREATE TABLE padaria.tb_item(
	id_item    SERIAL    CONSTRAINT pk_id_item  PRIMARY KEY,
	nome_item    VARCHAR(200)    CONSTRAINT nn_nome_item  NOT NULL,
	preco_custo    NUMERIC(7,2)    CONSTRAINT nn_preco_custo  NOT NULL,
	preco_venda    NUMERIC(7,2)    CONSTRAINT nn_preco_venda  NOT NULL,
	estoque    INTEGER    CONSTRAINT nn_estoque  NOT NULL
);

CREATE TABLE padaria.tb_pedido(
	id_pedido    SERIAL    CONSTRAINT pk_id_pedido    PRIMARY KEY,
	id_cliente    INTEGER,
	preco_final    NUMERIC(7,2)    CONSTRAINT nn_preco_final  NOT NULL,
	CONSTRAINT    fk_ped_id_cliente  FOREIGN KEY(id_cliente)
		REFERENCES padaria.tb_cliente(id_cliente)
);

CREATE TABLE padaria.tb_item_pedido (
      id_item_pedido    SERIAL,
      id_item     INTEGER,
      id_pedido     INTEGER,
      quantidade     INTEGER CONSTRAINT nn_quantidade NOT NULL,
      CONSTRAINT pk_item_pedido PRIMARY KEY(id_item_pedido),
      CONSTRAINT fk_ped_id_item FOREIGN KEY(id_item) 
        REFERENCES padaria.tb_item(id_item),
      CONSTRAINT fk_ped_id_pedido FOREIGN KEY(id_pedido) 
        REFERENCES padaria.tb_pedido(id_pedido)
 );

Já no schema administracao foram criadas uma tablea para armazenar os dados dos funcionarios, e uma para o log das compras tendo o seguinte diagrama lógico:

E com o seguinte código SQL:

CREATE TABLE administracao.tb_funcionarios (
    id_funcionario    SERIAL,
    CONSTRAINT pk_funcionarios PRIMARY KEY(id_funcionario),
    nome             VARCHAR(300) CONSTRAINT nn_nome NOT NULL,
    salario        NUMERIC CONSTRAINT nn_salario NOT NULL,
    telefone    VARCHAR(11) CONSTRAINT nn_telefone NOT NULL
);

CREATE TABLE administracao.tb_compras_log (
    id_compras_log    SERIAL,
    CONSTRAINT pk_compras_log PRIMARY KEY(id_compras_log),
    id_cliente INTEGER,
    dt_compra TIMESTAMP CONSTRAINT nn_dt_compra NOT NULL,
    preco_final NUMERIC(7, 2) CONSTRAINT nn_preco_final NOT NULL,
    CONSTRAINT fk_adm_id_cliente FOREIGN KEY(id_cliente) 
        REFERENCES padaria.tb_cliente(id_cliente)
);

Modelo conceitual das tabelas:

Com as tabelas prontas foi hora de criar algumas funções necessárias para o banco de dados a primeira delas sendo a função para preencher o campo preco_final da tabela de pedidos. Como o pedido pode ter vários itens é necessário colocar um valor inicial nesse campo e só depois de realizar todas as vinculações de itens a esse pedido na tabela itenspedido dar update nesse campo colocando o valor final da compra correto. Para calcular o preço final foi feito, então, a função fn_preco_final:

CREATE OR REPLACE FUNCTION administracao.fn_preco_final(id_p INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql AS
$$
	BEGIN 
		RETURN (SELECT sum(preco_venda * quantidade) FROM padaria.tb_item ti 
			JOIN padaria.tb_item_pedido tip ON ti.id_item = tip.id_item
			WHERE tip.id_pedido = id_p);
	END;
$$

No caso de uma aplicação real desse banco de dados um aplicativo que utilizasse esse banco faria o update do valor utilizando essa função, como nesse relatório utilizamos somente SQL foi usado o seguinte update para produzir o mesmo efeito:

UPDATE padaria.tb_pedido SET preco_final = administracao.fn_preco_final(:id_p)
	WHERE id_pedido = :id_p;

Ademais, foi feito o seguinte trigger que preenche a tabela de log_compras a cada update na tabela de pedidos, efetivamente, registrando a compra a cada vez que o valor final é calculado:

CREATE OR REPLACE FUNCTION administracao.fn_compras_log()
RETURNS trigger AS 
$$
    BEGIN 
        INSERT INTO administracao.tb_compras_log (id_cliente, dt_compra, preco_final)
        SELECT NEW.id_cliente, now(), NEW.preco_final;
        RETURN NEW;
    END
$$
LANGUAGE plpgsql;

CREATE TRIGGER tg_compras_log 
AFTER UPDATE ON padaria.tb_pedido
FOR EACH ROW EXECUTE PROCEDURE administracao.fn_compras_log();

Depois, foi feita uma função para calcular o lucro total da padaria, essa função calcula a subtração da soma de todas os preços finais das vendas, com o preço de compra dos itens vezes sua quantidade em estoque e o salário dos funcionários:

CREATE OR REPLACE FUNCTION administracao.fn_preco_final(id_p INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql AS
$$
	BEGIN 
		RETURN (SELECT sum(preco_venda * quantidade) FROM padaria.tb_item ti 
			JOIN padaria.tb_item_pedido tip ON ti.id_item = tip.id_item
			WHERE tip.id_pedido = id_p);
	END;
$$

Por fim, foram criados grupos de usuários para o gerente, aplicação e para o contador:

CREATE GROUP Gerente;
GRANT ALL ON ALL TABLES IN SCHEMA padaria TO GROUP Gerente WITH GRANT OPTION;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA padaria to Gerente WITH GRANT OPTION;
GRANT ALL ON SCHEMA padaria TO Gerente;
GRANT ALL ON ALL TABLES IN SCHEMA administracao TO GROUP Gerente WITH GRANT OPTION;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA administracao to Gerente WITH GRANT OPTION;
GRANT ALL ON SCHEMA administracao TO Gerente;

CREATE GROUP Aplicacao;
GRANT insert, select, delete, update ON ALL TABLES IN SCHEMA padaria TO GROUP Aplicacao;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA padaria to Aplicacao;
GRANT USAGE ON SCHEMA padaria TO Aplicacao;
GRANT insert, select, delete, update ON ALL TABLES IN SCHEMA administracao TO GROUP Aplicacao;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA administracao to Aplicacao;
GRANT USAGE ON SCHEMA administracao TO Aplicacao;

CREATE GROUP Contador;
GRANT select ON ALL TABLES IN SCHEMA padaria TO GROUP Contador;
GRANT USAGE ON SCHEMA padaria TO Contador;
GRANT select ON ALL TABLES IN SCHEMA administracao TO GROUP Contador;
GRANT USAGE ON SCHEMA administracao TO Contador;