Instituto Politécnico de Beja
Escola Superior de Tecnologia e Gestão
GesStock
Aplicação para Gestão de Stocks
Engenharia Informática
Base de Dados II
Docente: Artur Lança
Isabel Sofia Brito
Nuno Gonçalo Varela – n.º 4383
2008/2009
Base de Dados II
Índice
Introdução
3
Enunciado do Problema
4
Organização e armazenamento de dados
5
Normalização
6
Modelo Conceptual de Dados
7
Modelo Físico de Dados
8
Código SQL para criação de base de dados (SQL Server 2005)
14
Código para a criação da base de dados
14
Código para a criação das tabelas da base de dados
15
Conclusão
19
2
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Introdução
No âmbito da disciplina de Base de Dados II foi solicitada a elaboração de uma
base de dados, a partir de um enunciado elaborado pelos alunos e previamente
aprovado pelo docente da disciplina.
Para a elaboração da base de dados vamos utilizar como métodos de estudo o
modelo relacional e o de normalização.
O objectivo é construir uma base de dados utilizando o Microsoft SQL Server
2005, com a ferramenta SQL Sever Management Studio, a partir do desenho
dos modelos conceptual e físico de dados (MCD e MFD).
3
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Enunciado do Problema
Uma empresa de criação de software pretende desenvolver uma aplicação
para Gestão de Stocks, o GesStock, que será comercializada pela mesma, que
tenha várias funcionalidades, que possa gerar vários tipos de documentos e
que sirva também de análise de stocks e de gestão.
O software a ser criado terá as seguintes funcionalidades permitindo gerir:
- tabelas de fornecedores e clientes com as respectivas contas correntes;
- condições de pagamentos;
- modos de pagamento;
- produtos;
- famílias de produtos;
- armazéns.
Este software permite gerar vários tipos de documentos:
- facturas;
- vendas a dinheiro;
- notas de débito e de crédito;
- guias de transporte;
- orçamentos;
- facturas pró-forma.
Também será uma ferramenta de análise, em que a qualquer momento se
pode saber:
- a localização dos produtos em armazém;
- descontos nas compras e vendas;
- mapas de IVA de compras e vendas;
- inventário e análise de stock.
Assim sendo, pretende-se criar de uma base de dados, que permita gerir o
stock dos produtos a comercializar, as compras aos diversos fornecedores e as
respectivas vendas a clientes, e também elaborar alguns relatórios que possam
ajudar na respectiva gestão de produto.
4
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Organização e armazenamento de dados
O RAID 5 é frequentemente usado e funciona à semelhança do RAID 4, mas
supera alguns dos problemas mais comuns sofridos por esse tipo. As
informações sobre paridade para os dados do array são distribuídas ao longo
de todos os discos do array, ao invés de serem armazenadas num disco
dedicado,
oferecendo
assim
mais
desempenho
que
o
RAID
4,
e,
simultaneamente, tolerância a falhas.
Para aumentar o desempenho de leitura de um array RAID 5, o tamanho de
cada segmento em que os dados são divididos pode ser optimizado para o
array que estiver a ser utilizado. O desempenho geral de um array RAID 5 é
equivalente ao de um RAID 4, excepto no caso de leituras sequenciais, que
reduzem a eficiência dos algoritmos de leitura por causa da distribuição das
informações sobre paridade. A informação sobre paridade é distribuída por
todos os discos; perdendo-se um, reduz-se a disponibilidade de ambos os
dados e a paridade, até à recuperação do disco que falhou. Isto causa
degradação do desempenho de leitura e de escrita.
Não é recomendado para bases de dados, devido à penalidade de
desempenho em escritas pela necessidade de gravar a paridade e ao risco de
perda do conjunto quando um segundo disco falhar durante a recuperação de
uma falha qualquer; vide RAID 1+0, ou 10, abaixo.
5
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Normalização
Após
a
normalização
do
diagrama
Entidade
Relação,
anteriormente
desenvolvido, foram definidas as tabelas necessárias à implementação da BD,
pedida no enunciado. Este processo teve como base reconhecer e eliminar a
redundância dos dados. Desta forma evitamos quaisquer problemas que
possam advir quando se actualizam, eliminam ou inserem dados. Assim sendo,
e com o objectivo de suportar os dados relevantes ao universo do nosso
enunciado, vamos desenhar um esquema de BD que sustente esses dados de
uma forma adequada.
Depois de efectuada a normalização, até à 3.ª forma normal, desenhámos o
modelo conceptual representado na figura 1.
6
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Modelo Conceptual de Dados
Pagamentos
Recibos
@N_Pagamento
1
N_Factura
Data
Tipo_Pagamento
Referencia
Valor
@N_Recibo
N_Factura
Data
Tipo_Pagamento
Referencia
Valor
Produtos
@Referencia
1 Descricao1
Descricao2
Gr_Produto
Localizacao
Unid_Stock
Quantidade
Pr_Custo
Pr_Venda
Facturas
n
@N_Factura
Data
N_Encomenda
n
Cod_IVA
V_IVA
V_Total
1
Facturas
@N_Factura
Data
N_Encomenda
Cod_IVA
V_IVA
V_Total
n
n
Vendas_Dinheiro
Encomendas_Fornecedor
Produtos_Encomendas
1
@N_Encomenda
Data
N_Fornecedor
n
Referencia
Quantidade
V_Unitario
Desconto
V_Iliquido
n
1
n
@Referencia
@N_Encomenda
n
@N_Venda_Dinheiro
Data
N_Encomenda
Cod_IVA
V_IVA
V_Total
n
Encomendas_Cliente
1
Fornecedores
Clientes
@N_Fornecedor
Nome
Morada
Cod_Postal
Localidade
Telefone
Telemovel
Fax
Contribuinte
Nome_Contacto
E_Mail
Cond_Credito
Lim_Crédito
@N_Cliente
Nome
Morada
Cod_Postal
Localidade
Telefone
Telemovel
Fax
Contribuinte
Nome_Contacto
E_Mail
Cond_Credito
Lim_Credito
1
1 @N_Encomenda
Data
n N_Cliente
Referencia
Quantidade
V_Unitario
Desconto
V_Iliquido
1
Legenda
Tabelas
Nome da Tabela
Atributos da Tabela
Ligação entre tabelas
7
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Modelo Físico
Produtos
Tabela
Produtos
Descrição da tabela
Tabela para gestão de stock dos produtos
Tipo de dados
Dinâmico
Definição dos campos
Referencia
Tipo de
dados
Varchar
Descricao1
Varchar
50
Descricao2
Varchar
50
Gr_Produto
Smallint
10
NOT
Divisão dos tipos de produtos
Localizacao
Varchar
10
NOT
Localização em armazém
Unid_Stock
Varchar
5
NOT
Diferencia a unidade do metro
Quantidade
Smallint
5
NOT
Quantidade em stock
Pr_Custo
Money
10
NOT
Preço de custo do produto
Pr_Venda
Money
10
NOT
Preço de venda do produto
Nome do Campo
Tamanho
Null
Descrição
15
NOT
Chave primária (identity)
NOT
Nome do produto
Nome do produto (cont.)
Produtos/Encomendas
Tabela
Produtos_Encomendas
Tabela de ligação entre as tabelas Produtos e Encomendas de
Clientes e de Fornecedores
Dinâmico
Descrição da tabela
Tipo de dados
Definição dos campos
Referencia
Tipo de
dados
Varchar
N_Encomenda
Smallint
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave primária (estrangeira)
NOT
Chave primária (estrangeira)
8
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Encomendas de Clientes
Tabela
Encomendas_Cliente
Descrição da tabela
Tabela de criação das encomendas dos clientes
Tipo de dados
Dinâmico
Definição dos campos
N_Encomenda
Tipo de
dados
Smallint
Data
Datetime
N_Cliente
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave primária (identity)
10
NOT
Data de criação da encomenda
Varchar
50
NOT
Chave estrangeira
Referencia
Varchar
15
NOT
Referência dos produtos
Quantidade
Smallint
5
NOT
Quantidade de produtos
V_Unitario
Money
10
NOT
Valor da unidade do produto
Desconto
Smallint
5
V_Iliquido
Money
10
Valor do desconto
NOT
Valor final da encomenda
Clientes
Tabela
Clientes
Descrição da tabela
Tabela de gestão dos dados dos clientes
Tipo de dados
Dinâmico
Definição dos campos
N_Cliente
Tipo de
dados
Varchar
Nome
Varchar
Morada
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave primária (identity)
50
NOT
Nome do cliente
Varchar
80
NOT
Morada do cliente
Cod_Postal
Varchar
8
NOT
Código Postal
Localidade
Varchar
30
NOT
Localidade
Telefone
Smallint
15
NOT
Número de telefone do cliente
Telemovel
Smallint
15
NOT
Número de telemovel do cliente
Fax
Smallint
15
Contribuinte
Smallint
9
Nome_Contacto
Varchar
30
Pessoa com quem contactar
E_Mail
Varchar
20
Cond_Credito
Smallint
2
E-mail do cliente ou do contacto
Condições de pagamento dos
clientes
Número de fax do cliente
NOT
NOT
Número de contribuinte do cliente
9
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Lim_Credito
Money
10
Limite de crédito do cliente
Facturas
Tabela
Facturas
Descrição da tabela
Tabela de gestão de facturação (crédito) dos clientes
Tipo de dados
Dinâmico
Definição dos campos
N_Factura
Tipo de
dados
Smallint
Data
Datetime
N_Encomenda
Smallint
Cod_IVA
Smallint
V_IVA
V_Total
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave primária (identity)
NOT
Data da emissão do documento
NOT
Chave estrangeira
5
NOT
Percentagem de IVA s/produtos
Money
10
NOT
Valor de IVA
Money
10
NOT
Valor total do documento
10
Vendas a Dinheiro
Tabela
Vendas_Dinheiro
Descrição da tabela
Tabela de gestão de facturação (dinheiro) dos clientes
Tipo de dados
Dinâmico
Definição dos campos
N_Venda_Dinheiro
Tipo de
dados
Smallint
Data
Datetime
N_Encomenda
Smallint
Cod_IVA
Smallint
V_IVA
V_Total
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave primária (identity)
NOT
Data da emissão do documento
NOT
Chave estrangeira
5
NOT
Percentagem de IVA s/produtos
Money
10
NOT
Valor de IVA
Money
10
NOT
Valor total do documento
10
10
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Recibos
Tabela
Recibos
Descrição da tabela
Tabela de gestão de pagamentos dos clientes
Tipo de dados
Dinâmico
Definição dos campos
N_Recibo
Tipo de
dados
Smallint
N_Factura
Varchar
Data
Datetime
Tipo_Pagamento
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave Primária (identity)
NOT
Chave estrangeira
10
NOT
Data de emissão do recibo
Varchar
15
NOT
Tipo de pagamento do cliente
Referencia
Varchar
15
NOT
Referência do cliente
Valor
Money
10
NOT
Valor total de recibo
Encomendas a Fornecedores
Tabela
Encomendas_Fornecedor
Descrição da tabela
Tabela de criação das encomendas dos fornecedores
Tipo de dados
Dinâmico
Definição dos campos
N_Encomenda
Tipo de
dados
Smallint
Data
Datetime
N_Fornecedor
Varchar
Referencia
Varchar
Quantidade
Smallint
V_Unitario
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave primária (identity)
10
NOT
Data de criação da encomenda
50
NOT
Chave estrangeira
NOT
Referência dos produtos
5
NOT
Quantidade de produtos
Money
10
NOT
Valor da unidade do produto
Desconto
Smallint
5
V_Iliquido
Money
10
Valor do desconto
NOT
Valor final da encomenda
11
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Fornecedores
Tabela
Fornecedores
Descrição da tabela
Tabela de gestão dos dados dos fornecedores
Tipo de dados
Dinâmico
Definição dos campos
N_Fornecedor
Tipo de
dados
Varchar
Nome
Varchar
Morada
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave Primária (identity)
50
NOT
Nome do fornecedor
Varchar
80
NOT
Morada do fornecedor
Cod_Postal
Varchar
8
NOT
Código Postal
Localidade
Varchar
30
NOT
Localidade
Telefone
Smallint
15
NOT
Número de telefone do fornecedor
Telemovel
Smallint
15
NOT
Número de telemovel do fornecedor
Fax
Smallint
15
Contribuinte
Smallint
9
NOT
Nome_Contacto
Varchar
30
NOT
Número de fax do fornecedor
Número de contribuinte do
fornecedor
Pessoa com quem contactar
E_Mail
Varchar
20
Cond_Credito
Smallint
2
Lim_Credito
Money
10
NOT
E-mail do fornecedor ou do contacto
Condições de pagamento aos
fornecedores
Limite de crédito do fornecedor
Facturas
Tabela
Facturas
Descrição da tabela
Tabela de gestão de facturação dos fornecedores
Tipo de dados
Dinâmico
Definição dos campos
N_Factura
Tipo de
dados
Smallint
Data
Datetime
N_Encomenda
Smallint
Cod_IVA
Smallint
V_IVA
V_Total
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave primária (identity)
NOT
Data da emissão da factura
NOT
Chave estrangeira
5
NOT
Percentagem de IVA s/produtos
Money
10
NOT
Valor de IVA
Money
10
NOT
Valor total do documento
10
12
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Pagamentos
Tabela
Pagamentos
Descrição da tabela
Tabela de gestão de pagamentos das crianças
Tipo de dados
Dinâmico
Definição dos campos
N_Recibo
Tipo de
dados
Smallint
N_Factura
Varchar
Data
Datetime
Tipo_Pagamento
Nome do Campo
Tamanho
Null
Descrição
NOT
Chave primária (identity)
NOT
Chave estrangeira
10
NOT
Data do pagamento
Varchar
15
NOT
Tipo de pagamento ao fornecedor
Referencia
Varchar
15
NOT
Referência do pagamento
Valor
Money
10
NOT
Valor total de recibo
13
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Código SQL para criação de base de dados (SQL Server 2005)
Código para a criação da base de dados
CREATE DATABASE GesStock
ON PRIMARY
( NAME = GesStockPrimario,
FILENAME = 'E:\Eng.
Informática\2008_09\3_BD2\GesStock\GesStockPrimario.mdf',
SIZE = 200MB,
MAXSIZE = 1500MB,
FILEGROWTH = 100MB),
FILEGROUP GesStockDados
( NAME = GesStockDados1,
FILENAME = 'E:\Eng.
Informática\2008_09\3_BD2\GesStock\GesStockDados1.ndf',
SIZE = 200MB,
MAXSIZE = 1500MB,
FILEGROWTH = 100MB),
( NAME = GesStockDados2,
FILENAME = 'E:\Eng.
Informática\2008_09\3_BD2\GesStock\GesStockDados2.ndf',
SIZE = 200MB,
MAXSIZE = 1500MB,
FILEGROWTH = 100MB),
FILEGROUP GesStockHistorico
( NAME = GesStockHistorico1,
FILENAME = 'E:\Eng.
Informática\2008_09\3_BD2\GesStock\GesStockHistorico1.ndf',
SIZE = 200MB,
MAXSIZE = 1000MB,
FILEGROWTH = 100MB),
( NAME = GesStockHistorico2,
FILENAME = 'E:\Eng.
Informática\2008_09\3_BD2\GesStock\GesStockHistorico2.ndf',
SIZE = 200MB,
MAXSIZE = 1000MB,
FILEGROWTH = 100MB)
LOG ON
( NAME = GesStockLog,
FILENAME = 'E:\Eng.
Informática\2008_09\3_BD2\GesStock\GesStockLog.ldf',
SIZE = 100MB,
MAXSIZE = 500MB,
FILEGROWTH = 50MB)
14
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Código para a criação das tabelas da base de dados
CREATE TABLE Produtos
(
Rererencia VARCHAR (15) PRIMARY KEY,
Descricao1 VARCHAR (50) NOT NULL,
Descricao2 VARCHAR (50),
Gr_Produto SMALLINT (10) NOT NULL,
Localizacao VARCHAR (10) NOT NULL,
N_Fornecedor VARCHAR (6) NOT NULL,
Unid_Stock VARCHAR (5) NOT NULL,
Quantidade SMALLINT (5) NOT NULL,
Pr_Custo MONEY (10) NOT NULL,
Pr_Venda MONEY (10) NOT NULL
)
CREATE TABLE Produtos_Encomendas
(
Referencia VARCHAR,
N_Encomenda SMALLINT,
PRIMARY KEY (Referencia, N_Encomenda),
FOREIGN KEY (Referencia)
REFERENCES Produtos (Referencia)
ON UPDATE CASCADE
ON DELETE CASCADE,
FOREIGN KEY (N_Encomenda)
REFERENCES Encomendas_Cliente (N_Encomenda)
ON UPDATE CASCADE
ON DELETE CASCADE,
REFERENCES Encomendas_Fornecedor (N_Encomenda)
ON UPDATE CASCADE
ON DELETE CASCADE,
)
CREATE TABLE Encomendas_Clientes
(
N_Encomenda SMALLINT PRIMARY KEY,
Data DATETIME (10) NOT NULL,
N_Cliente VARCHAR NOT NULL,
Referencia VARCHAR (15) NOT NULL,
Quantidade SMALLINT (5) NOT NULL,
V_Unitario MONEY (10) NOT NULL,
Desconto SMALLINT (5),
V_Unitario MONEY (10) NOT NULL,
FOREIGN KEY (N_Cliente)
REFERENCES Clientes (N_Cliente)
ON UPDATE CASCADE
ON DELETE CASCADE,
)
15
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
CREATE TABLE Clientes
(
N_Cliente VARCHAR PRIMARY KEY,
Nome VARCHAR (50) NOT NULL,
Morada VARCHAR (80) NOT NULL,
Cod_Postal VARCHAR (8) NOT NULL,
Localidade VARCHAR (30) NOT NULL,
Telefone SMALLINT (15) NOT NULL,
Telemovel SMALLINT (15) NOT NULL,
Fax SMALLINT (15),
Contribuinte SMALLINT (9) NOT NULL,
Nome_Contacto VARCHAR (30),
E_Mail VARCHAR (20),
Cond_Credito SMALLINT (2) NOT NULL,
Lim_Credito MONEY (10),
)
CREATE TABLE Facturas
(
N_Factura SMALLINT PRIMARY KEY,
Data DATETIME (10) NOT NULL,
N_Encomenda SMALLINT NOT NULL,
Cod_IVA SMALLINT (5) NOT NULL,
V_IVA MONEY (10) NOT NULL,
V_Total MONEY (10) NOT NULL,
FOREIGN KEY (N_Encomenda)
REFERENCES Encomendas_Clientes (N_Encomenda)
ON UPDATE CASCADE
ON DELETE CASCADE,
)
CREATE TABLE Vendas_Dinheiro
(
N_Documento SMALLINT PRIMARY KEY,
Data DATETIME (10) NOT NULL,
N_Encomenda SMALLINT NOT NULL,
Cod_IVA SMALLINT (5) NOT NULL,
V_IVA MONEY (10) NOT NULL,
V_Total MONEY (10) NOT NULL,
FOREIGN KEY (N_Encomenda)
REFERENCES Encomendas_Clientes (N_Encomenda)
ON UPDATE CASCADE
ON DELETE CASCADE,
)
16
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
CREATE TABLE Recibos
(
N_Recibo SMALLINT PRIMARY KEY,
N_Factura SMALLINT NOT NULL,
Data DATETIME NOT NULL,
Tipo_Pagamento VARCHAR (15) NOT NULL,
Referencia VARCHAR (15) NOT NULL,
Valor MONEY (10) NOT NULL,
FOREIGN KEY (N_ Factura)
REFERENCES Facturas (N_ Factura)
ON UPDATE CASCADE
ON DELETE CASCADE,
)
CREATE TABLE Encomendas_Fornecedores
(
N_Encomenda SMALLINT PRIMARY KEY,
Data DATETIME (10) NOT NULL,
N_Fornecedor VARCHAR NOT NULL,
Referencia VARCHAR (15) NOT NULL,
Quantidade SMALLINT (5) NOT NULL,
V_Unitario MONEY (10) NOT NULL,
Desconto SMALLINT (5),
V_Unitario MONEY (10) NOT NULL,
FOREIGN KEY (N_Fornecedor)
REFERENCES Fornecedores (N_Fornecedor)
ON UPDATE CASCADE
ON DELETE CASCADE,
)
CREATE TABLE Fornecedores
(
N_Fornecedor VARCHAR PRIMARY KEY,
Nome VARCHAR (50) NOT NULL,
Morada VARCHAR (80) NOT NULL,
Cod_Postal VARCHAR (8) NOT NULL,
Localidade VARCHAR (30) NOT NULL,
Telefone SMALLINT (15) NOT NULL,
Telemovel SMALLINT (15) NOT NULL,
Fax SMALLINT (15),
Contribuinte SMALLINT (9) NOT NULL,
Nome_Contacto VARCHAR (30),
E_Mail VARCHAR (20),
Cond_Credito SMALLINT (2) NOT NULL,
Lim_Credito MONEY (10),
)
17
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
CREATE TABLE Facturas
(
N_Factura SMALLINT PRIMARY KEY,
Data DATETIME (10) NOT NULL,
N_Encomenda SMALLINT NOT NULL,
Cod_IVA SMALLINT (5) NOT NULL,
V_IVA MONEY (10) NOT NULL,
V_Total MONEY (10) NOT NULL,
FOREIGN KEY (N_Encomenda)
REFERENCES Encomendas_Fornecedores (N_Encomenda)
ON UPDATE CASCADE
ON DELETE CASCADE,
)
CREATE TABLE Pagamentos
(
N_Pagamento SMALLINT PRIMARY KEY,
N_Factura SMALLINT NOT NULL,
Data DATETIME NOT NULL,
Tipo_Pagamento VARCHAR (15) NOT NULL,
Referencia VARCHAR (15) NOT NULL,
Valor MONEY (10) NOT NULL,
FOREIGN KEY (N_Factura)
REFERENCES Facturas (N_Factura)
ON UPDATE CASCADE
ON DELETE CASCADE,
)
18
Eng. Informática – Pós-Laboral
Nuno Varela – 4383
Base de Dados II
Conclusão
Podemos concluir que as Bases de Dados hoje em dia facilitam imenso a
criação, a organização e a pesquisa de informação nos mais diversos meios.
No caso apresentado desenvolvi a base do GesStock, que será num futuro
próximo uma aplicação de gestão de stocks, tendo como suporte uma base de
dados que permite uma maior facilidade de movimento e acesso de dados de
um qualquer tipo de comércio.
Posso referenciar que em termos de trabalho futuro deverei desenvolver
melhor a estrutura da base de dados, aperfeiçoando o já realizado, com a
criação de outro tipo de tabelas, criação de novas consultas, de modo que a
informação que possamos extrair, seja o mais detalhada possível na análise da
informação contida na base de dados, conforme a necessidade de futuros
utilizadores.
19
Eng. Informática – Pós-Laboral
Nuno Varela – 4383