Instituto Tecnológico de Aeronáutica

Divisão da Ciência da Computação

Curso de Pós-Graduação

                                      

 

 

 

 

 

 

 

 

 

 

 

 

 

CE-240 Projeto de Banco de Dados

 

 

 

 

Professor. Adilsom Marques da Cunha

 

 

Projeto Final

 

 

 

 

 

 

 

 

 

 

Wellingtonn Vergílio Fortes - Normalizador

 

 

 

 

 

Barra do Piraí - RJ, 28 de junho de 2004

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. Introdução

 

1.1  Motivação

 

É nos o oportuno neste projeto ampliar a nossa experiência relativa à praticidade das técnicas demonstrada em sala, assim aumentando a nossa visão em um arcabouço de um Projeto de Banco de Dados.

 

 

1.2  Objetivo

 

Tem como finalidade este projeto implementar um Projeto de Banco de Dados para a Empresa HALCS. Está empresa atua no ramo rodoviário e necessita de um sistema computadorizado para sua gestão.

 

      

2.     Conteúdo

 

 

2.1  Protótipo de Banco de Dados

 

 

2.1.1    Contextualização

 

 

A HALCS é uma empresa que trabalha com gestão de Transporte Rodoviário e tem como objetivo melhorar e qualificar o seu método administrativo.

Baseando se nesta necessidade foi feita uma analise do seu funcionamento e constatamos que devido ao grande fluxo de dados que a empresa HALCS gera ao gerir seus processos faz-se necessário desenvolver um sistema para controlar, qualificar as informações, assim facilitando os seus executivos a tomarem decisões.

Este sistema tem em vista atuar nas áreas de Apoio a Logística de Carga voltado, com o objetivo de aumentar a qualidade destes processos, assim tornando a empresa mais competitiva.

Neste trabalho atingiremos apenas o gerenciamento de Manutenção da Frota.

 

 

 

2.1.2    Definição do Problema

 

 

1.      Efeitos Adversos

 

 

 

·        Não se sabe as oficinas autorizadas e que fora acionadas .

 

·        Não se tem o histórico do veiculo em relação a manutenção.

 

·        Não tem controle do dos tipos de serviços prestados.

 

·        É impossível fazer uma manutenção preventiva, pois não a controle das peças e sua utilização.

 

·        Não se controle o orçamento das oficinas, ou seja, não é feita comparação de preços.

 

·        Não há como verificar a relação Benefício/Custo de um veículo em relação manutenção/serviço prestado.

 

 

2.      Causas

 

 

·        As informações sobre manutenção são descarta (não há registro).

 

·        Não são cadastradas as oficinas que são acionadas.

 

·        Não registro de peças.

 

·        Desinteressaram-se em checar a concorrência dos orçamentos.

 

·        Hoje o setor não tem uma visão administrativa.

 

 

 

 

 

 

 

 

 

 

3.      Tarefa

 

 

Adotar técnicas administrativas e um sistema computadorizado para gerenciar as informações. Também faz se necessário dar treinamento aos funcionários para se criar uma cultura administrativa, até junho de 2004.

 

 

4.     Propósito

 

Visa-se aumentar a visão operacional da Gestão da manutenção e aumentar a qualidade dos processos. E diminuir o custo com manutenção.

 

 

2.1.3        Enunciado do Problema

 

 

Dotar o a Empresa na área de Apoio a Logística de Carga um sistema computadorizado para controle de gasto de frota e assim possibilitando ao executivo analisar o seu custo operacional.

Também será necessário dar treinamento aos funcionários responsáveis pela gestão de frota em relação ao novo processo de controle.

 

 

2.1.4        Enunciado da Solução Escolhida

 

 

Desenvolver processos de armazenamento, recuperação e tratamento de informação visando aumentar o controle de gasto do setor de gestão de frota.

Este processo será automatizado através de um sistema computadorizado.

Tendo vista melhorar a gerência da manutenção dos  Veiculos.

 

 

 

 

2.1.4    Redução de Escopo

 

 

Apesar do enunciado da solução escolhida trazer como solução implantar um sistema computadorizado este projeto apenas atacará a parte de Banco de dados especifica para gestão de manutenção.  Não serão atribuídas a este projeto as áreas de comunicação, geração de código, hardwares, documentware, firmware.

 

 

2.1.5    Definição do Título

 

 

                                               “SIStema de  Manutenção – SIS-MANUT “

 

 

2.1.6    Especificação de Requisitos

 

O  Banco de Dados SIS-MANUT deverá ser capaz de:

1.      Propiciar um maior controle na gestão dos veiculos;

2.      Controlar as listas de matérias que são usados;

3.      Melhorar o sistema de orçamentos da Empresa, assim visando obter o melhor preço;

4.      Controlar a freqüência na qual o veiculo sofre a manutenção;

5.      Tem que informar os tipo de serviços prestados;

 

 

II – Desenvolvimento

 

 

Nesta parte será demonstrado a produção do Banco de Dados Setorial SYS-MANUT e as integrações em vários níveis e a integração do banco das empresas ALC e ALS na  empresa Holding HALCS. Este banco de dados foi desenvolvido no SGBD Interbase. Foram aplicadas técnicas de modelagem de dados e testada pelo software third.

 

 

 

 

 

 

- Banco de dados

 

• 1º nível de abstração

 

No desenvolvimento do Aplicativo de Banco de Dados foi utilizado o Modelo de Dados Relacional uma vez que ele  representa melhor a estrutura do processo na qual será armazenado no banco.

Os Modelo de entidade e Relacionamento e o dicionário estão no anexo –1 Listex 04.

O aplicativo SIS-MANUT foi normalizado, obedecendo-se às Formas Normais. A evolução da normalização desde a 1FN até a 3FN é mostrada abaixo e as Consultas (Queries) logo após:

 

 

 0FN

 

Inicialmente o banco foi apresentado com a seguinte estrutura.

 

SIS-MANUT {Oficinas, Veículos, Manutencao, Servicos, Cotacao}

 

 

1FN

 

Ao aplicando a 1ª Forma Normal estaremos deixando todos os atributos atômicos.

 

 

SIS-MANUT {codofic,nome,endereco,bairro,cidade,uf,tel, codveiculo, placa, ano,modelo,codmanut,dataentrada,datasaida,material,codservicos,descricao, valor,codcotacao,data,aprovacao}

 

 

2FN

 

Requer que todos os atributos não chave devem conter informações, que se referem à chave inteira, e não somente à parte do registro.

 

 

oficina { codofic,nome,endereco,bairro,cidade,uf,tel}

 

veículo {codveiculo, placa, ano,modelo}

 

manutencao { codmanut,dataentrada,datasaida,material}

 

servico {codservicos,descricao, valor}

 

cotacao { codcotacao,data,aprovacao}

 

 

 

 

3FN

 

Refere-se ao agrupamento de Relações requeridas na 2FN, com cada atributo não chave referindo-se diretamente a chave.

 

 

oficina { codofic,nome, endereco,bairro,cidade,uf,tel}

 

veículo {codveiculo, placa, ano,modelo}

 

manutencao { codmanut,dataentrada,datasaida }

 

MATERIAL {codmaterial,descrição,quantidade,valorunit}

 

servico {codservicos,descricao, valor}

 

cotacao { codcotacao,data,aprovacao}

 

 

 

Trigramação

 

oficina { ofi_codofic, ofi_nome, ofi_endereco, ofi_bairro, ofi_cidade, ofi_uf, ofi_tel}

 

veículo {vei_codveiculo, vei_placa, vei_ano, vei_modelo}

 

manutencao { man_codmanut, man_dataentrada, man_datasaida }

 

MATERIAL {mat_codmaterial, mat_descrição, mat_quantidade, mat_valorunit}

 

servico {ser_codservicos, ser_descricao, ser_valor}

 

cotacao { cot_codcotacao, cot_data, cot_aprovacao}

 

OCORRENCIA {oco_cod, man_codmanut, vei_codveiculo}

 

TEM{ tem_cod, man_codmanut, ser_codservico}

 

VINCULO{ vin_cod , mat_codmaterial, ser_codservico}

 

PRESTA { pre_cod , man_codmanut, ofi_codofic}

 

 

2º nível de abstração

 

            Neste nível foi feita uma junção dos bancos de dados individuais e crio-se o banco de dado setorial GFR – Gestão de Frota. Esta junção foi executada por componentes atuantes em três áreas. Normalizador, Dicionarizador e Integrador.

            O banco de dados GFR, que é derivado da unificação dos bancos SYS-MANUT, SYS-CFR e SYS-EF.

A contextualização e o código do banco estão localizados na página:

http://sergiopbarros.sites.uol.com.br/ce_240/listex_05/listex_05_n1.htm

 

O Dicionário de Dados do Banco está na página: http://geocities.yahoo.com.br/jfbrancalion/ListaExercicios5Banco.htm

 

            O  MER e a renormalização deste banco se encontra:

 

http://www.geocities.com/engdecomp/listex5240.htm

 

 

            E depois  foi criado o banco de dados DELTA, que se formou com a unificação dos banco de dados setoriais: GRE, GFR, GMI e GAP.

            Toda a documentação do banco (contextualização, dicionarização, MER e código SQL do banco) estão localizadas no link: http://geocities.yahoo.com.br/izilton_ferraiolo/listex5normalizacao2.html

 

 

 

3º nível de abstração

 

            Neste nível foi criado o banco da empresa ALC, formou-se com a união dos bancos DELTA e CHARLIE.

Integrando os Protótipos de Aplicativo de Banco de Dados da Gestão de Apoio, Frotas, Missões e Recursos (SIS-GAFMR) e do Setor Administrativo, Armazenagem e Financeiro (SIS-ADAFI).

Toda a documentação do banco (contextualização, dicionarização, MER e código SQL do banco) estão localizadas no link:

http://geocities.yahoo.com.br/halcs2004/alc_contextualizacao.html

 

O Dicionário de Dados do Banco está na página:

 

http://geocities.yahoo.com.br/halcs2004/alc_dicionarizacao.html

 

O  MER e a renormalização deste banco se encontra:

 

http://geocities.yahoo.com.br/halcs2004/alc_normalizacao.html

 

 

 

 

 

CONSULTAS (Queries) para Recuperação de Informações

 

O Nível 0 é composto pelo banco de dados SYS-MANUT.]

 

O Nível 1 é composto pelo banco de dados GFR, que é derivado da unificação dos bancos SYS-MANUT, SYS-CFR e SYS-EF.

 

O Nível 2 é composto pelo banco de dados DELTA, que é derivado da unificação dos bancos GRE, GFR, GMI e GAP.

 

O Nível 3 é composto pelo banco de dados ALC, que é derivado da unificação dos bancos DELTA e CHARLIE.

 

O Nível 4 é a união da Holding HALCS, que é derivado da unificação das empresas ALC e ALS.

 

 

·        Consulta 1  Nivel 0

 

 

Linguagem Natural

 

-     Mostras o nome e telefone das oficinas do estado do Rio de Janeiro

 

SQL :

 

select ofi_nome,ofi_tel from oficina where ofi_uf='RJ'

 

 

 

·        Consulta 2  Nivel 0

 

 

Linguagem Natural

 

-            Mostrar as proposta ou cotação de manutenção que não foram aprovadas

 

SQL

 

select cotacao.cot_codcotacao,manutencao.man_codmanut

from manutencao,cotacao where manutencao.man_codmanut=cotacao.man_codmanut and cotacao.cot_aprovacao='NAO'

 

 

 

·        Consulta 3  Nivel 0

 

 

Linguagem Natural

 

-           Mostrar as datas no qual o veiculo especificado sofreu manutenção

 

SQL

 

select veiculo.vei_placa,manutencao.man_dataentrada, manutencao.man_datasaida from manutencao,ocorrencia, veiculo where  veiculo.vei_placa='MND4567' and manutencao.man_codmanut=ocorrencia.man_codmanut and ocorrencia.vei_codveiculo=veiculo.vei_codveiculo

 

 

 

 

 

 

 

 

Consulta 4  Nivel 1

 

 

- Linguagem Natural

 

Seleciona o veiculo que sofreu a manutenção e a taxa gasta pelo veiculo. Assim comprova a integração do banco SYS-MANUT com o banco SYS-CFR. Os dois bancos foram integrados ao banco GFR.

 

- SQL

select veiculo.vei_placa,manutencao.man_dataentrada, manutencao.man_datasaida,taxa.tax_valor,taxa.tax_data

from manutencao,ocorrencia, veiculo,taxa where  veiculo.vei_placa='kmk7370'

and manutencao.man_num=ocorrencia.man_num and ocorrencia.vei_num=veiculo.vei_num and veiculo.vei_num=taxa.vei_num

 

 

Consulta 5  Nivel 1

 

- Linguagem Natural

 

Seleciona o gasto com o veiculo, no percurso feito para entrega de um frete. Para efetuar esta consulta foi necessário a integração dos bancos SYS-MANUT, SYS-CFR,  SYSEF. Os três bancos foram integrados ao banco GFR.

 

- SQL  

select frete.frt_num,frete.frt_gastos,veiculo.vei_placa,manutencao.man_dataentrada, manutencao.man_datasaida,taxa.tax_valor,taxa.tax_data

from manutencao,ocorrencia, veiculo,taxa, alocacao,frete where  veiculo.vei_placa='kmk7370'

and manutencao.man_num=ocorrencia.man_num and ocorrencia.vei_num=veiculo.vei_num

and veiculo.vei_num=taxa.vei_num and frete.frt_num=alocacao.frt_num

and veiculo.vei_num=alocacao.vei_num

 

 

 

 

Consulta 6  Nivel 2

 

- Linguagem Natural

 

Esta consulta mostra o problema ocorridos com determinado veiculo em determinada viagem e qual o serviço prestado para solucionar o problema. A consulta foi gerada no nível 2, em cima do banco DELTA.

 

- SQL

 

select viagem.vag_id, servico.sem_descricao,manutencao.man_dthr_ent, manutencao.man_dthr_saida,servico_est.ser_desc,

veiculo.vei_placa,veiculo.vei_modelo

 

from servico,manutencao, servico_est, veiculo,viagem

 

where  servico.man_id=manutencao.man_id

and servico_est.ser_id=manutencao.ser_id

and servico_est.vei_id=veiculo.vei_id

and viagem.vag_id=servico_est.vag_id

 

 

Consulta 7  Nivel 3

 

- Linguagem Natural

 

 

Qual a viagem e a ordem de serviço no qual o veiculo sofreu manutenção.  Este caso é usado para responsábilizar o cliente pela quebra do veiculo.

- SQL

 

select viagem.vag_id, servico.sem_descricao,manutencao.man_dthr_ent, manutencao.man_dthr_saida,servico_est.ser_desc,

veiculo.vei_placa,veiculo.vei_modelo,OSE_ID,PAR_ID,PCI_ID

 

from servico,manutencao, servico_est, veiculo,viagem,entrega,carga,ordem_servico

 

where  servico.man_id=manutencao.man_id

and servico_est.com_id=manutencao.ser_id

and servico_est.vei_id=veiculo.vei_id

and viagem.vag_id=servico_est.vag_id

and viagem.vag_id=entrega.ent_id

and entrega.car_id=carga.car_id

and ordem_servico.ose_id=carga.ose_id

 

 

Consulta 8  Nivel 3

 

- Linguagem Natural

 

Qual o produto transportado no qual o veiculo sofreu manutenção em viagem, e o valor dele? Esta consulta é importante ser efeutada quando o produto é perecível e o veiculo precisa de manutenção.

 

-SQL

 

 

select viagem.vag_id, servico.sem_descricao,manutencao.man_dthr_ent, manutencao.man_dthr_saida,servico_est.ser_desc,

veiculo.vei_placa,veiculo.vei_modelo,OSE_ID,PAR_ID,PCI_ID,pedido_cliente_item.pci_preco,pedido_cliente_item.PCI_GASTOSINDIRETOS,

produto.pro_descricao

 

from servico,manutencao, servico_est, veiculo,viagem,entrega,carga,ordem_servico, produto,pedido_cliente_item

 

where  servico.man_id=manutencao.man_id

and servico_est.com_id=manutencao.ser_id

and servico_est.vei_id=veiculo.vei_id

and viagem.vag_id=servico_est.vag_id

and viagem.vag_id=entrega.ent_id

and entrega.car_id=carga.car_id

and ordem_servico.ose_id=carga.ose_id

and carga.pro_id=produto.pro_id

and pedido_cliente_item.pro_id = produto

 

 

 

 

 

 

 

 

 

 

III - Simulação de Jogos de Empresa –Banco de Dados Holding (SIS-HALCS – Holding de Apoio à Logística de Cargas e Serviços)

 

 

            Devido a proposta em questão que prevê a elaboração imediata de um Anteprojeto de Banco de Dados para a Empresa Holding HALCS. Foi se preparada uma equipe de especialistas em determinadas áreas para o desenvolvimento do projeto. A equipe consiste em especialista em: Dicionarização, Normalizadores, Integradores e Suporte.

 

1.      (Re-contextualização; a Re-objetivação (redefinição do Problema e da Alternativa de Solução Escolhida para o seu Setor, a Re-intitulação do Setor). Esta artefato foi desenvolvido pela equipe de Integradores

 

Heurística do Objetivo :  Link do artefato desenvolvido

http://geocities.yahoo.com.br/halcs2004/halc_contextualizacao.html

 

2.      O processo de integração e a conseqüente re-normalização gerando o  MER, e os teste feito no aplicativo Third foram realizados pela equipe de normalizadores:

 

Link do artefato desenvolvido

http://geocities.yahoo.com.br/halcs2004/halc_normalizacao.html

 

 

3.      O processo de integração e a conseqüente re-dicionarização foram realizados pela equipes de Dicionalizadores:

 

Link do artefato desenvolvido

http://geocities.yahoo.com.br/halcs2004/halc_dicionarizacao.html

 

 

 

 

 

 

 

 

 

 

 

 

 

STORE PROCEDURED

 

Esta Stored Procedure atualiza a tabela cotacao_manut com código do material quando o orçamento é aprovado “EXECUTE PROCEDURE ATUA_COTACAO_MANUT MATERIAL.MTR_ID);.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SET TERM !!;

CREATE PROCEDURE ATUA_COTACAO_MANUT

(COT_id integer)as

  BEGIN

   UPDATE COTACAO_MANUT

   SET COTACAO_MANUT.mat_ID = :COT_id

   WHERE COTACAO_MANUT.COT_APROVACAO = 'SIM';

  END !!

 

 

VIEW

 

Relação Gasto/ Manutenção

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

/* View: TRANSPORTE_MANUTENCA, Owner: SYSDBA */

 

CREATE VIEW TRANSPORTE_MANUTENCA (

  PRODUTO,

  GASTO,

  PEDIDO,

  VALOR,

  MANUTENCAODATA,

  SERVICO,

  VEICULO

) AS

 

select produto.pro_descricao,pedido_cliente_item.PCI_GASTOSINDIRETOS,pedido_cliente_item.pci_preco, servico.sem_descricao,manutencao.man_dthr_ent, servico_est.ser_desc,

veiculo.vei_placa

from servico,manutencao, servico_est, veiculo,viagem,entrega,carga,ordem_servico, produto,pedido_cliente_item

where  servico.man_id=manutencao.man_id

and servico_est.com_id=manutencao.ser_id

and servico_est.vei_id=veiculo.vei_id

and viagem.vag_id=servico_est.vag_id

and viagem.vag_id=entrega.ent_id

and entrega.car_id=carga.car_id

and ordem_servico.ose_id=carga.ose_id

and carga.pro_id=produto.pro_id

and pedido_cliente_item.pro_id = produto.pro_id

;

 

 

TRIGGER

 

Caso apague um registro da tabela manutenção, cria-se um registro temporário na tabela del_manutenção.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CREATE TABLE DEL_MANUTENCAO

(

  DEL_MAN_ID  INTEGER NOT NULL,

  DEL_MAN_DTHR_ENT TIMESTAMP,

  DEL_MAN_DTHR_SAIDA           TIMESTAMP,

  DEL_SER_ID     INTEGER NOT NULL

);

 

SET TERM !!;

 

create trigger DELETADOS_MANUTENCAO for MANUTENCAO

after delete as

 begin

  insert into DEL_MANUTENCAO values (MANUTENCAO.MAN_ID, MANUTENCAO.MAN_DTHR_ENT, MANUTENCAO.MAN_DTHR_SAIDA, MANUTENCAO.SER_ID);

 end !!

 

SET TERM; !!

 

 

 

CONSULTA 9 NIVEL 4

 

 

Uma Consulta típica do Nível de Decisão Operacional:

 

Linguagem natural: Qual a missão os veículos sofre mais quebras.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

-SQL

 

select missao.mis_id, comunicacao.com_tipo, servico_est.ser_nome,

servico_est.ser_desc,MANUTENCAO.man_dthr_ent

from missao,comunicacao,servico_est,MANUTENCAO

where

missao.mis_id=comunicacao.mis_id and

comunicacao.com_id=servico_est.com_id and

MANUTENCAO.ser_id=servico_est.ser_id

 

 

CONSULTA 10 NIVEL 4

 

Uma Consulta típica do Nível de Decisão Tático: Quais os modelo de veículos que tem maior durabilidade? Ou manutenção mais prolongada?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

select VEICULO.vec_id,VEICULO.vec_modelo,VEICULO.vec_placa,

servico_est.ser_desc,manutencao.MAN_DTHR_ENT,

manutencao.MAN_DTHR_SAIDA

 

from veiculo,manutencao,servico_est

 

where

VEICULO.vec_id=servico_est.vec_id and

servico_est.ser_id=manutencao.ser_id

 

 

CONSULTA 11 NIVEL 4

 

Uma Consulta típica do Nível de Decisão Estratégico :

Qual o valor gasto para o transporte e o valor pago pelo cliente.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

select cliente.cli_nome,pedido_cliente.pcl_data,

pedido_cliente_item.pci_preco,carga.car_valor

 

from cliente,pedido_cliente,pedido_cliente_item,carga

 

where cliente.cli_id= pedido_cliente.cli_id and

pedido_cliente.pcl_id=pedido_cliente_item.pcl_id and

pedido_cliente_item.pcl_id=carga.pcl_id

 

 

 

 

Resultado Esperado

 

            É se esperado inicialmente a criação de um protótipo na turma, assim dando um visão do funcionamento do banco da HALCS e direcionando o caminho do desenvolvimento do banco. Este protótipo será desenvolvido em softwares gratuítos assim não onerando custo. O SGDB utilizado será o interbase 6.0.

Previsão de comclusão será datada para 28/06/2004.

Com isso os alunos acresentaram em seu domínio de conhecimento, a formulação de um projeto de banco de dados e a experiência em trabalho em equipe quando necessário nas integrações.

 

 

 

Resultado Obtido

 

Espera-se um grande acrescimo como profissional, como pessoa e intelectual para os alunos da matéria CE-240 e CES-30. E agregar mais experiência para a vida cotidiana de um profissional, ensinando-o a trabalhar em equipe, mas mais importante ensinado a trabalha em uma equipe vitoriosa e que consegue atingir um objetivo multuo.

 

 

 

IV – CONCLUSÃO

           

            Deste o ingresso, ao programa de Pós-Gradução do ITA, venho cada vez mais ampliando minha capacidade de profissional, e o mais importante me acrescentando como pessoa. Cursei as Matérias CE-230 e CE-240, e nunca pensei que além de conhecimento estas matéria melhoraria o meu modo de viver.

            Utilize deste material uma orientação para desenvolvimento de Projetos.