Banco de Dados Queries Versão 3.1

(1)

Sistema de Controle de Pedidos

SISCOP

Banco de Dados

Queries

Versão 3.1 Histórico de Revisão

Data Versão Descrição Autor

13/11//2010 1.0 Desenvolvimento das Queries Janaína Lima 16/11/2010 2.0 Correção das Queries Adriano Marra 26/11/2010 3.0 Desenvolvimento das Queries Janaína Lima 27/11/2010 3.1 Correção das Queries Bruno Oliveira

(2)

Documento de Banco de Dados

Sumário

INTRODUÇÃO ... 1

Convenções, termos e abreviações ... 1

1. Identificação dos Requisitos ... 1

2. Documentos Relacionados ao SISCOP ... 1

Modelo Entidade Relacionamento Conceitual e Lógico ... 2

1. MER Conceitual ... 2 2. MER Lógico ... 3 CRIAÇÃO DA TABELAS... 4 RF 001 – Módulo Usuário ... 4 1. Tabela: Cargo_funcionario ... 4 2. Tabela: Email_funcionario ... 4 3. Tabela: Telefone_funcionario ... 4 4. Tabela: Endereco_funcionario ... 4 5. Tabela: Funcionario ... 5 6. Tabela: Nivel_acesso ... 5 7. Tabela: Usuario ... 5 RF 002 – Módulo Produto ... 6 1. Tabela: Produto ... 6 2. Tabela: Especificacao_produto ... 6 3. Tabela: Material ... 6 4. Tabela: Equipamento ... 6 5. Tabela: Ferramentas ... 7

RF 003 – Módulo Ponto Remoto ... 7

1. Tabela: Ponto_remoto ... 7

RF 004 – Módulo Ordem de Serviço ... 7

1. Tabela: Ordem_de_servico ... 7 2. Tabela: Servico ... 8 3. Tabela: Itens_da_os ... 8 RF 005 – Módulo Pedidos ... 8 1. Tabela: Pedido ... 8 2. Tabela: Itens_do_pedido ... 9

(3)

Documento de Banco de Dados

CRIAÇÃO DOS RELATÓRIOS ... 10

RF 006 – Relatórios Base ... 10

1. Relatório: Usuários ... 10

2. Relatório: Produto ... 10

3. Relatório: Pedidos ... 10

4. Relatório: Ordem de Serviço ... 11

5. Relatório: Pontos Remotos ... 11

RF 006 – Relatórios Compostos ... 11

6. Relatório: Ususários x Pontos Remotos ... 11

7. Relatório: Usuários x Ordem de Serviço ... 12

8. Relatório: Usuários x Pedidos ... 12

9. Relatório: Produtos x Pedidos ... 12

(4)

Documento de Banco de Dados Introdução – P1 / 1

Introdução

Este documento especifica as queries de criação, inclusão, alteração e exclusão de tabelas e dados, além dos índices do Banco de Dados do SISCOP, Sistema de Controle de Pedidos da empresa Torre Forte Construtora LTDA. O SISCOP possui 18 tabelas relacionadas para gestão de estoque e pedidos.

Convenções, termos e abreviações

A correta interpretação deste documento exige o conhecimento de algumas convenções e termos específicos, que são descritos a seguir.

Documento Local

Glossário

http://itsolutionsproject.files.wordpress.com/2010/08/glossario_versao_1-1.pdf

1. Identificação dos Requisitos

Por convenção, a referência as queries é feita através do nome da subseção onde eles estão descritos, seguido do identificador do requisito, de acordo com o esquema abaixo:

[nome da subseção.identificador do requisito]

Por exemplo, o requisito [Controle de Pontos Remotos.RF003] está descrito em uma subseção chamada “Módulo Pontos Remotos”, em um bloco identificado pelo número [RF003].

2. Documentos Relacionados ao SISCOP

Documento Data Versão Local

Documento de Visão 14/08/2010 2.0 http://itsolutionpproject.wordpress.com/

(5)

Modelo Entidade Relacionamento Conceitual e Lógico

Esta introdução fornece o MER Conceitual e Lógico e as informações necessárias para fazer um bom uso deste documento e explicitando a lista de referências para outros documentos relacionados. As demais seções apresentam a especificação das queries do sistema SISCOP de acordo com os Módulos a que elas se referem.

(6)

(7)

Documento de Banco de Dados Exclusão de Dados – C4. P4 / 4

Capítulo

Criação da Tabelas

RF 001 – Módulo Usuário

1. Tabela: Cargo_funcionario

CREATE TABLE Cargo_funcionario

(

idcargo_funcionario VARCHAR (10) NOT NULL,

cargo_funcionario VARCHAR (20) NULL,

PRIMARY KEY (idcargo_funcionario) );

2. Tabela: Email_funcionario

CREATE TABLE Email_funcionario

(

idemail_funcionario VARCHAR (10) NOT NULL,

Endereco_email VARCHAR (30) NULL,

PRIMARY KEY (idemail_funcionario) );

3. Tabela: Telefone_funcionario

CREATE TABLE Telefone_funcionario

(

idtelefone_funcionario VARCHAR (10) NOT NULL,

numero_fone VARCHAR (15) NULL,

PRIMARY KEY (idtelefone_funcionario) );

4. Tabela: Endereco_funcionario

CREATE TABLE Endereco_funcionario

(

idendereco_funcionario VARCHAR (10) NOT NULL,

Logradouro VARCHAR (100) NULL,

Numero VARCHAR (5) NULL,

Bairro VARCHAR (40) NULL,

Cidade VARCHAR (50) NULL,

UF VARCHAR (2) NULL,

CEP VARCHAR (9) NULL,

(8)

PRIMARY KEY (idendereco_funcionario) );

5. Tabela: Funcionario

CREATE TABLE Funcionario

(

Matricula_funcionario VARCHAR (10) NOT NULL,

Nome_funcionario VARCHAR (255) NULL,

CPF VARCHAR (11) NULL,

Sexo_funcionario VARCHAR (1) NULL,

Dt_nascimento DATETIME NULL,

idendereco_funcionario VARCHAR (10) NOT NULL,

idtelefone_funcionario VARCHAR (10) NOT NULL,

idemail_funcionario VARCHAR (10) NOT NULL,

idcargo_funcionario VARCHAR (10) NOT NULL,

PRIMARY KEY (Matricula_funcionario),

FOREIGN KEY (idendereco_funcionario) REFERENCES dbo.Endereco_funcionario,

FOREIGN KEY (idtelefone_funcionario) REFERENCES dbo.Telefone_funcionario,

FOREIGN KEY (idemail_funcionario) REFERENCES dbo.Email_funcionario,

FOREIGN KEY (idcargo_funcionario) REFERENCES dbo.Cargo_funcionario

);

6. Tabela: Nivel_acesso

CREATE TABLE Nivel_de_acesso

(

idnivel_acesso VARCHAR (10) NOT NULL,

Descricao_nivel VARCHAR (10) NULL,

PRIMARY KEY (idnivel_acesso) );

7. Tabela: Usuario

CREATE TABLE Usuario

(

Matricula_funcionario VARCHAR (10) NOT NULL,

idnivel_acesso VARCHAR (10) NOT NULL,

Login VARCHAR (10) NOT NULL,

Senha VARCHAR (8) NOT NULL,

Ativo BIT NULL,

CONSTRAINT PK_Usuario

PRIMARY KEY (Matricula_funcionario, idnivel_acesso),

FOREIGN KEY (Matricula_funcionario) REFERENCES dbo.Funcionario,

(9)

FOREIGN KEY (idnivel_acesso) REFERENCES dbo.Nivel_de_acesso

);

RF 002 – Módulo Produto

1. Tabela: Produto

CREATE TABLE Produto

(

idproduto VARCHAR (10) NOT NULL,

Quantidade_produto VARCHAR (6) NOT NULL,

Num_etiquete_eletronica VARCHAR (6) NOT NULL,

Dt_entrada_estoque DATETIME NOT NULL,

Dt_saida_estoque DATETIME NOT NULL,

PRIMARY KEY (idproduto) );

2. Tabela: Especificacao_produto

CREATE TABLE Especificacao_produto

(

idespecificacao_produto VARCHAR (10) NOT NULL,

Descricao_produto VARCHAR (40) NOT NULL,

PRIMARY KEY (idespecificacao_produto) );

3. Tabela: Material

CREATE TABLE Material

(

Dt_validade_material DATETIME NULL,

PRIMARY KEY (idproduto),

FOREIGN KEY (idproduto) REFERENCES dbo.Produto,

FOREIGN KEY (idespecificacao_produto) REFERENCES dbo.Especificacao_produto

);

4. Tabela: Equipamento

(10)

(

Dt_locacao_equipamento DATETIME NULL,

Dt_devolucao_equipamento DATETIME NULL,

);

5. Tabela: Ferramentas

CREATE TABLE Ferramenta

(

Dt_locacao_ferramenta DATETIME NULL,

Dt_devolucao_ferramenta DATETIME NULL,

);

RF 003 – Módulo Ponto Remoto

1. Tabela: Ponto_remoto

CREATE TABLE Ponto_remoto

(

idponto_remoto VARCHAR (10) NOT NULL,

Endereco_ponto_remoto VARCHAR (60) NOT NULL,

PRIMARY KEY (idponto_remoto) );

RF 004 – Módulo Ordem de Serviço

1. Tabela: Ordem_de_servico

CREATE TABLE Odem_de_servico

(

(11)

Matricula_funcionario VARCHAR (10) NULL,

idponto_remoto VARCHAR (10) NULL,

Descricao_servico TEXT NOT NULL,

Descricao_produto TEXT NOT NULL,

Dt_inicial_os DATETIME NOT NULL,

Dt_final_os DATETIME NULL,

PRIMARY KEY (idos),

FOREIGN KEY (Matricula_funcionario) REFERENCES dbo.Funcionario,

FOREIGN KEY (idponto_remoto) REFERENCES dbo.Ponto_remoto

);

2. Tabela: Servico

CREATE TABLE Servico

(

idservico VARCHAR (10) NOT NULL,

Tipo_servico VARCHAR (40) NOT NULL,

PRIMARY KEY (idservico) );

3. Tabela: Itens_da_os

CREATE TABLE Itens_da_os

(

idos VARCHAR (10) NOT NULL,

idservico VARCHAR (10) NOT NULL,

Item_os VARCHAR (40) NOT NULL,

Quantidade_os INT NOT NULL,

PRIMARY KEY (idos,idservico),

FOREIGN KEY (idos) REFERENCES dbo.Ordem_de_servico,

FOREIGN KEY (idservico) REFERENCES dbo.Servico

);

RF 005 – Módulo Pedidos

1. Tabela: Pedido

CREATE TABLE Pedido

(

idpedido VARCHAR (10) NOT NULL,

idos VARCHAR (10) NOT NULL,

Dt_pedido DATETIME NOT NULL,

Dt_envio DATETIME NOT NULL,

PRIMARY KEY (idpedido),

FOREIGN KEY (idos) REFERENCES dbo.Ordem_de_servico

(12)

2. Tabela: Itens_do_pedido

CREATE TABLE Itens_do_pedido

(

idpedido VARCHAR (10) NOT NULL,

Item_do_pedido VARCHAR (40) NOT NULL,

Quantidade_item INT NOT NULL,

PRIMARY KEY (idproduto,idpedido),

FOREIGN KEY (idpedido) REFERENCES dbo.Pedido

(13)

Capítulo

Criação dos Relatórios

RF 006 – Relatórios Base 1. Relatório: Usuário

Select * from usuário;

select A.nome_funcionario, A.Matricula_funcionario, B.Login from funcionario A, usuario B

Where A.matricula_funcionario = B.matricula_funcionario;

select A.nome_funcionario, A.CPF, A.Matricula_funcionario,

C.numero_fone, B.Login

from funcionario A, usuario B, telefone_funcionario C

Where A.idtelefone_funcionario = C.idtelefone_funcionario and

A.matricula_funcionario = B.matricula_funcionario order by A.nome_funcionario;

2. Relatório: Produto

select * from produto;

select count(*)qtd_registros from produto;

select A.quantidade_produto, B.descricao_produto,

C.dt_validade_material

from produto A, especificacao_produto B, material C

where A.idproduto = C.idproduto and C.idespecificacao_produto =

B.idespecificacao_produto order by A.quantidade_produto;

3. Relatório: Pedidos select * from pedido;

(14)

select A.dt_pedido, A.dt_envio, B.item_do_Pedido,

B.quantidade_item

from pedido A, itens_do_pedido B where A.idpedido = B.idpedido;

4. Relatório: Ordem de Serviço

select * from ordem_de_servico;

select A.idos, A.descricao_servico, A.descricao_produto, B.dt_pedido, C.item_do_pedido

from ordem_de_servico A, pedido B, itens_do_pedido C where A.idos = B.idos and B.idpedido = C.idpedido

5. Relatório: Pontos Remotos

select * from ponto_remoto

select A.idos, B.idponto_remoto, B.endereco_ponto_remoto from ordem_de_servico A, ponto_remoto B

where A.idponto_remoto = B.idponto_remoto

RF 006 – Relatórios Compostos

6. Relatório: Ususários x Pontos Remotos

select B.Nome_funcionario, C.matricula_funcionario, D.idponto_remoto, E.idos, A.idpedido, A.dt_pedido from pedido A, funcionario B, usuario C,

ponto_remoto D, ordem_de_servico E

where C.matricula_funcionario = B.matricula_funcionario

and C.matricula_funcionario = E.matricula_funcionario and D.idponto_remoto = E.idponto_remoto

and A.idos = E.idos order by B.nome_funcionario

(15)

7. Relatório: Usuários x Ordem de Serviço

select A.login, B.idos, B.Dt_inicial_os from usuario A, ordem_de_servico B

where A.matricula_funcionario = B.matricula_funcionario and A.login in ('Bruno.Oliveira');

8. Relatório: Usuários x Pedidos

select A.login, B.item_do_pedido, B.quantidade_item, C.dt_pedido from usuario A, itens_do_pedido B, pedido C

where B.idpedido = C.idpedido and login like 'José%'

9. Relatório: Produtos x Pedidos

select A.idpedido, B.idproduto, B.quantidade_produto from pedido A, produto B, Itens_do_pedido C

where A.idpedido = C.idpedido and B.idproduto = C.idproduto and B.quantidade_produto >= '55'

10. Relatório: Pontos Remotos x Ordem de Serviço

select A.idponto_remoto, B.idos, B.descricao_servico, B.dt_inicial_os

from ponto_remoto A, ordem_de_servico B

where A.idponto_remoto = B.idponto_remoto