Sistema de Gerenciamento de Livrarias

Prévia do material em texto

Banco de Dados Avançados – Sistema de Gerenciamento de Livrarias
Universidade Federal de Pernambuco
Centro de Informática – CIn
Sistema de Gerenciamento de Livrarias
Daniel Ferreira (dfs3)
Domingos Sávio (dsrm)
Paulo Amorim (prfa)
Shirley Jacinto (ssj)
Thiago Maciel (tam2)
�
Conteúdo
31. Introdução	�
32. Definição das Entidades	�
33. Relacionamentos Entre Entidades	�
44. O Que se Espera Como Resultado	�
45. Diagrama Entidade-Relacionamento	�
56. Diagrama Entidade-Relacionamento	�
57. Documentação dos Scripts	�
57.1 Criação das Tabelas	�
67.2 Criação das Views	�
67.3 Inserção de Dados	�
��
1. Introdução
	A aplicação a ser desenvolvida tem como principal objetivo controlar o sistema de vendas de uma livraria. Através da aplicação, a livraria poderá controlar de forma mais automatizada suas vendas e o controle de seus respectivos clientes.
2. Definição das Entidades
	O modelo da base de dados do sistema deverá conter as seguintes entidades:
	Entidade
	Descrição
	Atributos
	CLIENTE
	Representa os clientes da livraria.
	CPF
Nome
Nascimento
	ARTIGO
	Representa todos os produtos disponíveis para venda na livraria. Tais produtos podem ser livros, revistas, CDs, etc.
	ID
Descrição
QTD Estoque
Preço
	VENDA
	Nesta entidade, serão armazenadas todas as vendas realizadas pela livraria.
	CPF Cliente
ID Artigo
Data venda
Valor
	PEDIDO
	A cada venda não realizada por falta de estoque, o sistema armazenará nesta entidade os pedidos de estoque em falta.
	ID Artigo
Data pedido
QTD
	FIDELIDADE
	Representa os clientes que apresentarem bônus com a livraria. O bônus se caracteriza de acordo com as compras de cada cliente.
	CPF Cliente
Bônus
	LOG OPERAÇÕES
	Entidade que armazenará a maioria das transações realizadas na base de dados
	Data
Operação
Os atributos sublinhados representam os identificadores de cada entidade.
3. Relacionamentos Entre Entidades
Cada venda possui apenas um artigo.
Um artigo pode estar em várias vendas.
Uma venda (compra) é realizada por um único cliente.
Um cliente pode estar em diversas vendas.
Um pedido possui apenas um artigo.
Um artigo pode estar em diversos pedidos.
4. O Que se Espera Como Resultado
	A aplicação deverá controlar o fluxo de vendas da livraria sempre baseado na quantidade em estoque disponível. De acordo com esta quantidade, o sistema poderá liberar a venda, ou solicitar a compra de mais artigos para completar o estoque em falta. 
	Também estará disponível um controle de clientes que mais realizam compras na livraria. Através de um sistema de controle de bônus, os clientes poderão receber automaticamente, descontos no total de suas compras.
5. Diagrama Entidade-Relacionamento
	
6. Diagrama Entidade-Relacionamento
7. Documentação dos Scripts
7.1 Criação das Tabelas
CREATE TABLE CLIENTE(
	cpf varchar(15) PRIMARY KEY,
	nome varchar(100) NOT NULL,
	nascimento date
);
CREATE TABLE ARTIGO(
	id int PRIMARY KEY,
	descricao varchar(60) NOT NULL,
	qtd int NOT NULL,
	preco number NOT NULL
);
CREATE TABLE VENDA(
	cpf_cliente varchar(15),
	id_artigo int,
	data_venda date NOT NULL,
	valor number,
	FOREIGN KEY(cpf_cliente) REFERENCES CLIENTE(cpf),
	FOREIGN KEY(id_artigo) REFERENCES ARTIGO(id),
	PRIMARY KEY(cpf_cliente, id_artigo)
);
CREATE TABLE FIDELIDADE(
	cpf_cliente varchar(15) PRIMARY KEY,
	bonus NUMBER,
	FOREIGN KEY(cpf_cliente) REFERENCES CLIENTE(cpf)
);
CREATE TABLE PEDIDO(
	id_artigo int,
	data_pedido date NOT NULL,
	qtd int NOT NULL,
	FOREIGN KEY(id_artigo) REFERENCES ARTIGO(id),
	PRIMARY KEY(id_artigo, data_pedido)
);
CREATE TABLE LOG_OPERACOES(
	data date NOT NULL,
	operacao varchar(200)
);
7.2 Criação das Views
CREATE VIEW VWVENDAS AS
SELECT * FROM VENDA;
CREATE VIEW VWARTIGOS AS
SELECT * FROM ARTIGO WHERE qtd > 0;
CREATE VIEW VWVendas_Cliente AS
SELECT c.NOME, a.DESCRICAO, v.DATA_VENDA, v.VALOR, f.BONUS f 
FROM CLIENTE c, ARTIGO a, VENDA v, FIDELIDADE F
WHERE c.CPF = v.CPF_CLIENTE AND v.ID_ARTIGO = a.ID AND c.CPF = f.CPF_CLIENTE;
7.3 Inserção de Dados
INSERT INTO CLIENTE VALUES('1111', 'DANIEL FERREIRA', TO_DATE('24/06/1984', 'DD/MM/YYYY'));
INSERT INTO CLIENTE VALUES('2222', 'SHIRLEY JACINTO', TO_DATE('10/03/1984', 'DD/MM/YYYY'));
INSERT INTO CLIENTE VALUES('3333', 'DOMINGOS SÁVIO', TO_DATE('19/11/1980', 'DD/MM/YYYY'));
INSERT INTO CLIENTE VALUES('4444', 'PAULO AMORIM', TO_DATE('22/05/1985', 'DD/MM/YYYY'));
INSERT INTO CLIENTE VALUES('5555', 'TIAGO MACIEL', TO_DATE('01/02/1982', 'DD/MM/YYYY'));
INSERT INTO ARTIGO VALUES(1, 'HARRY POTTER 7', 50, 60.50);
INSERT INTO ARTIGO VALUES(2, 'O MONGE E O EXECUTIVO', 15, 29.90);
INSERT INTO ARTIGO VALUES(3, 'REVISTA INFO EXAME', 60, 10.49);
INSERT INTO ARTIGO VALUES(4, 'APRENDENDO ORACLE EM 3 PASSOS', 3, 99.00);
7.4 Triggers
--STATEMENT TRIGGER
CREATE OR REPLACE TRIGGER logAumentoEstoque
AFTER UPDATE ON ARTIGO
BEGIN
 INSERT INTO LOG_OPERACOES (data, operacao)
 VALUES (SYSDATE, 'AUMENTO DO ESTOQUE');
END;
	
--STATEMENT TRIGGER
CREATE OR REPLACE TRIGGER logVenda
AFTER INSERT ON VENDA
BEGIN
 INSERT INTO LOG_OPERACOES (data, operacao)
 VALUES (SYSDATE, 'VENDA REALIZADA');
END;
--STATEMENT TRIGGER
CREATE OR REPLACE TRIGGER logNovoCliente
AFTER INSERT ON CLIENTE
BEGIN
 INSERT INTO LOG_OPERACOES (data, operacao)
 VALUES (SYSDATE, 'CLIENTE CADASTRADO');
END;
--AFTER INSERT, FOR EACH ROW
CREATE OR REPLACE TRIGGER dimEstoque AFTER INSERT
ON VENDA
FOR EACH ROW
DECLARE
 qtd_artigo artigo.qtd%TYPE;
BEGIN
 select qtd into qtd_artigo
 from artigo
 where id = :new.id_artigo;
 IF qtd_artigo > 0 THEN
 update artigo
 set qtd = qtd - 1
 where id = :new.id_artigo;
 END IF;
END;
CREATE OR REPLACE TRIGGER criar_Fidelidade AFTER INSERT 
ON CLIENTE
FOR EACH ROW
BEGIN
 INSERT INTO FIDELIDADE VALUES (:new.CPF, 0.0);
END;
--BEFORE INSERT, FOR EACH ROW, REFERENCING, EXCEPTION
CREATE OR REPLACE TRIGGER calcula_desconto BEFORE INSERT
ON venda
REFERENCING OLD AS O NEW AS N
FOR EACH ROW 
DECLARE
 desconto number;
 qtd_artigo number;
BEGIN
 desconto := 0;
 
 --SELECIONA A QUANTIDADE DO ARTIGO NO ESTOQUE
 select qtd into qtd_artigo
 from artigo
 where id = :N.id_artigo;
 --SE TIVER NO ESTOQUE
 IF qtd_artigo > 0 THEN
 SELECT bonus into desconto from fidelidade where cpf_cliente = :N.cpf_cliente;
 IF desconto < 0 THEN
 -- COM A CHAMADA DESTA FUNÇÃO O INSERT SERÁ ABORTADO!
 RAISE_APPLICATION_ERROR(-20000, 'VALOR NEGATIVO PARA DESCONTO NAO EH PERMITIDO!');
 END IF;
 :N.valor := :N.valor - desconto;
 ELSE
 -- COM A CHAMADA DESTA FUNÇÃO O INSERT SERÁ ABORTADO! 
 RAISE_APPLICATION_ERROR(-20000, 'ARTIGO INSUFICIENTE NO ESTOQUE!');
 END IF;
END;
/
--INSTEAD OF
CREATE OR REPLACE TRIGGER InOfI_VWVendas_CLiente
INSTEAD OF INSERT ON VWVendas_CLiente
FOR EACH ROW
BEGIN
 INSERT INTO VENDA VALUES((SELECT CPF FROM CLIENTE WHERE NOME = :new.Nome),
 (SELECT ID FROM ARTIGO WHERE DESCRICAO = :new.DESCRICAO),
 :new.DATA_VENDA,:new.Valor);
 UPDATE FIDELIDADE set BONUS = :new.F;
END InOfI_VWVendas_CLiente;
/
�PAGE �
�PAGE �8�
Página 
_1241423151.vsd
CLIENTE
CPF
Nome
Nascimento
ARTIGO
ID
Descricao
QTD
Preco
Compra
N
1
Data venda
Valor
PEDIDO
ID ArtigoSolicita
1
N
Data pedido
QTD
FIDELIDADE
CPF Cliente
Bônus
Compra
1
1
LOG_OPERACOES
Data
Operação
_1241423195.vsd
Table