MINISTÉRIO DA AERONÁUTICA
DEPARTAMENTO DE PESQUISAS E DESENVOLVIMENTO
CENTRO TÉCNICO AEROESPACIAL
Instituto Tecnológico de Aeronáutica
Programa de Pós-Graduação em
Engenharia Eletrônica e Computação - Informática
CE-240
Projeto de Sistemas de Bancos de Dados
Professor Doutor Adilson Marques da Cunha
Listex 6
Anexo V – Consultas Revisadas em Nível Operacional (Listex 4) e Tático (Listex 5) e
Consultas do Banco de Dados Corporativo
Alexander Silva
Eduardo Mena Barreto
Eliezer Rodrigues Segeti
Fellipe Ugliara
Marco Antônio Leite
Índice
Anexo V – Consultas Revisadas em Nível Operacional (Listex 4) e Tático (Listex 5) e Consultas do Banco de Dados Corporativo ... 3 Aplicativo de dados da ILUmincação do Departamento de Polícia (ABD-ILU-PO) – Listex 4
3
Aplicativo de dados dos EQUipamentos do Departamento de Polícia (ABD-EQU-PO) – Listex 4 ... 7
Revisão das 4 consultas em linguagem estruturada de consultas para o Banco de Dados Setorial da Polícia. (Listex 5) ... 11
Anexo V – Consultas Revisadas em Nível Operacional (Listex 4) e
Tático (Listex 5) e Consultas do Banco de Dados Corporativo
Os alunos que participaram do processo de integração e normalização se preocuparam em garantir que as consultas anteriormente isoladas nos aplicativos de dados não sofresse alterações bruscas visando manter a consistência e integridade dos dados. Para isso foram executadas verificações nas consultas dos aplicativos de banco de dados dos integrantes do grupo:
Aplicativo de dados da ILUmincação do Departamento de Polícia
(ABD-ILU-PO) – Listex 4
Consulta 1 - Consulta envolvendo uma relação e ao menos um comando Select e um comando Project (ou equivalentes).
Consulta em linguagem natural: Recuperar o nome do corredor com metragem maior que 15 metros.
Consulta em linguagem estruturada de consultas
SELECT COR.COR_IDENTIFICACAO FROM CORREDOR COR WHERE COR.COR_METRAGEM > 15;
Consulta 2 - Consultas envolvendo duas relações e ao menos um comando Select, um comando Project e um comando Join (ou equivalentes).
Consulta em linguagem natural: Recuperar a voltagem de todos os equipamentos do tipo alarme sonoro.
Consulta em linguagem estruturada de consultas
SELECT EQU.EQP_TENSAO_DE_OPERACAO FROM EQUIPAMENTO EQU, TIPOEQUIPAMENTO TEQ WHERE TEQ.TEQ_DESCRICAO = 'Alarme Sonoro' AND EQU.TEQ_CODIGO = TEQ.TEQ_CODIGO;
Foram necessárias apenas alteração no nome dos atributos que foram substituídos no momento da integração, como EQU.EQP_TENSAO_DE_OPERACAO que anteriormente era EQU.VOLTAGEM, TEQ.TEQ_DESCRICAO que anteriormente era TEQ.TEQ_IDENTIFICACAO, TEQ.TEQ_CODIGO que anteriormente era TEQ.TEQ_IDENTIFICADOR e EQU.TEQ_CODIGO que anteriormente era EQU.TEQ_IDENTIFICADOR. Porem a estrutura das tabelas foi mantido e a integridade dos dados também.
Consulta 3 - Consultas envolvendo três relações e ao menos um comando Select, um comando Project e um comando Join (ou equivalentes).
Consulta em linguagem natural: Recuperar voltagem das tomadas e abreviações do tipo de todos os equipamentos do tipo alarme perímetro que estejam conectados a uma tomada com o padrão ABNT.
Consulta em linguagem estruturada de consultas
SELECT TOM.TOM_VOLTAGEM, TEQ.TEQ_ABREVIACAO FROM EQUIPAMENTO EQU, TIPOEQUIPAMENTO TEQ, TOMADA TOM WHERE TEQ.TEQ_DESCRICAO = 'Alarme Perimetro' AND EQU.TEQ_CODIGO = TEQ.TEQ_CODIGO AND EQU.TOM_IDENTIFICADOR = TOM.TOM_IDENTIFICADOR AND TOM.TOM_NORMA_ABNT = 1;
Foram necessárias apenas alteração no nome dos atributos que foram substituídos no momento da integração, como TEQ.TEQ_CODIGO que anteriormente era TEQ.TEQ_IDENTIFICADOR e EQU.TEQ_CODIGO que anteriormente era EQU.TEQ_IDENTIFICADOR. Porem a estrutura das tabelas foi mantido e a integridade dos dados também.
Consulta 4 - Consulta georreferenciada envolvendo pelo menos duas relações e um comando Select, um comando Project e um comando Join (ou equivalentes).
Consulta em linguagem natural: Recuperar a tipo do equipamento e voltagem de todos os equipamentos do tipo alarme perímetro que estejam entre a área X: 23.133117332, Y: -45,981326587 e X: -45,749762702 e Y: -23,267456262.
Consulta em linguagem estruturada de consultas
SELECT TEQ.TEQ_DESCRICAO , EQU.EQP_TENSAO_DE_OPERACAO FROM EQUIPAMENTO EQU, TIPOEQUIPAMENTO TEQ, TOMADA TOM, MEDIDOR MDD WHERE TEQ.TEQ_DESCRICAO = 'Alarme Perimetro' AND EQU.TEQ_CODIGO = TEQ.TEQ_CODIGO AND EQU.TOM_IDENTIFICADOR = TOM.TOM_IDENTIFICADOR AND MDD.MDD_CODIGO = EQU.MDD_CODIGO AND
SDO_INSIDE(MDD.MDD_LOCAL_INSTALACAO, SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(-18.0000, -15.0000, -25.000, -45.886))) = 'TRUE';
Nesta consulta, foi necessária a adição de mais um relacionamento, pois anteriormente a localização geográfica era armazenada em um equipamento e agora com a normalização e integração, a localização foi mudada para medidor. Porem a estrutura das tabelas foi mantido e
a integridade dos dados também, também. Foram necessárias apenas alteração no nome dos atributos que foram substituídos no momento da integração, como TEQ.TEQ_CODIGO que anteriormente era TEQ.TEQ_IDENTIFICADOR, EQU.TEQ_CODIGO que anteriormente era EQU.TEQ_IDENTIFICADOR, EQU.EQP_TENSAO_DE_OPERACAO que anteriormente era EQU.VOLTAGEM e TEQ.TEQ_DESCRICAO que anteriormente era TEQ.TEQ_IDENTIFICACAO.
Aplicativo de dados dos EQUipamentos do Departamento de Polícia
(ABD-EQU-PO)
– Listex 4
Consulta 1 - Consulta envolvendo uma relação e ao menos um comando Select e um comando Project (ou equivalentes).
Consulta em linguagem natural: Encontre o nome do fabricante de código 3.
Consulta em linguagem estruturada de consultas
SELECT Fab_nome FROM Fabricante WHERE Fab_codigo = 3;
Consulta 2 - Consultas envolvendo duas relações e ao menos um comando Select, um comando Project e um comando Join (ou equivalentes).
Consulta em linguagem natural: Encontre a descrição do equipamento e a descrição do estado de operação do equipamento de código 3.
Consulta em linguagem estruturada de consultas
SELECT Equipamento.Eqp_descricao, EstadoOperacao.Eop_descricao FROM Equipamento, EstadoOperacao WHERE Equipamento.Eqp_codigo = 3 AND Equipamento.Eop_codigo = EstadoOperacao.Eop_codigo;
Consulta 3 - Consultas envolvendo três relações e ao menos um comando Select, um comando Project e um comando Join (ou equivalentes).
Consulta em linguagem natural: Encontre a tensão de operação do equipamento, o nome do fabricante do equipamento e a tensão de rede do medidor do equipamento de código 5, fabricante de código 1 e Tipo de Equipamento de código 3.
Consulta em linguagem estruturada de consultas
SELECT Equipamento.Eqp_tensao_de_operacao, Fabricante.Fab_nome, Medicao.Mdc_tensao_de_rede FROM Equipamento, Fabricante, Medidor, Medicao WHERE Equipamento.Fab_codigo = Fabricante_.Fab_codigo AND Equipamento.Mdd_codigo = Medidor.Mdd_codigo AND Equipamento.Eqp_codigo = 5 AND Equipamento.Fab_codigo = 1 AND Equipamento.Teq_codigo = 3 AND Medicao.Mdd_codigo = Medidor.Mdd_codigo AND rownum <= 1 ORDER BY Medicao.Mdc_data_ocorrencia DESC
Foram necessárias alterações na estrutura da consulta, pois o campo de tensão da rede da entidade medidor foi transferido para entidade de medição, o que causou a alteração na consulta, mas a estrutura e consistência dos dados foram mantida.
Consulta 4 - Consulta georreferenciada envolvendo pelo menos duas relações e um comando Select, um comando Project e um comando Join (ou equivalentes).
Consulta em linguagem natural: Encontre a descrição do medidor e a descrição do tipo de equipamento de todos os equipamentos conectados aos medidores instalados no polígono de delimitação virtual de área de interesse da cidade de São José dos Campos.
Consulta em linguagem estruturada de consultas
SELECT DISTINCT Medidor.Mdd_descricao, Teq.descricao FROM Medidor, Equipamento, TipoEquipamento WHERE SDO_INSIDE(MEDIDOR.MDD_LOCAL_INSTALACAO,
SDO_GEOMETRY(2003,NULL, NULL,SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(-18.0000, -15.0000, -25.000, -45.886))) = 'TRUE' AND Medidor.Mdd_codigo = Equipamento.Mdd_codigo AND Equipamento.Teq_codigo = TipoEquipamento.Teq_codigo;
Revisão das 4 consultas em linguagem estruturada de consultas para
o Banco de Dados Setorial da Polícia. (Listex 5)
Consulta 5 - Obter a descrição do departamento de polícia e a identificação do local onde se encontra instalado o equipamento de código 3
SELECT DEPARTAMENTO_POLICIA.Dpp_descricao, LOCAL_.Loc_identificacao
FROM EQUIPAMENTO, TOMADA, CORREDOR, LOCAL_, DEPARTAMENTO_POLICIA WHERE EQUIPAMENTO.Eqp_codigo = 3 AND EQUIPAMENTO.TOM_IDENTIFICADOR = TOMADA.TOM_IDENTIFICADOR AND TOMADA.COR_IDENTIFICADOR =
CORREDOR.COR_IDENTIFICADOR AND CORREDOR.LOC_IDENTIFICADOR = LOCAL_.LOC_IDENTIFICADOR AND LOCAL_.DPP_CODIGO =
Consulta 6 - Obter a tensao de operação do equipamento, o modelo do equipamento, o local de instalação do equipamento e o nome de seu fabricante, para todos os equipamentos conectados à circuitos aéreos
SELECT EQUIPAMENTO.EQP_TENSAO_DE_OPERACAO, EQUIPAMENTO.EQP_MODELO, LOCAL_.LOC_IDENTIFICACAO, FABRICANTE.FAB_NOME FROM EQUIPAMENTO,
TOMADA, CORREDOR, LOCAL_, FABRICANTE, MEDIDOR, CIRCUITO WHERE
CIRCUITO_.CCT_TIPO = 'Aéreo' AND MEDIDOR.CCT_CODIGO = CIRCUITO.CCT_CODIGO AND EQUIPAMENTO.MDD_CODIGO = MEDIDOR.MDD_CODIGO AND
FABRICANTE.FAB_CODIGO = EQUIPAMENTO.FAB_CODIGO AND
EQUIPAMENTO.TOM_IDENTIFICADOR = TOMADA.TOM_IDENTIFICADOR AND CORREDOR.COR_IDENTIFICADOR = TOMADA.COR_IDENTIFICADOR AND LOCAL_.LOC_IDENTIFICADOR = CORREDOR.LOC_IDENTIFICADOR;
Consulta 7 - Obter a descrição do tipo de equipamento, o identificador da tomada ao qual o equipamento está conectado, a descrição do equipamento e a última data de medição realizada neste equipamento
SELECT TIPOEQUIPAMENTO.TEQ_DESCRICAO, TOMADA.TOM_IDENTIFICADOR, EQUIPAMENTO.EQP_DESCICAO, (SELECT MAX(MEDICAO.MDC_DATA_OCORRENCIA) FROM MEDICAO WHERE MEDICAO.MDD_CODIGO = MEDIDOR.MDD_CODIGO) AS DATA_MEDICAO FROM EQUIPAMENTO, TIPOEQUIPAMENTO, TOMADA, MEDICAO, MEDIDOR WHERE EQUIPAMENTO.TOM_IDENTIFICADOR =
TOMADA.TOM_IDENTIFICADOR AND EQUIPAMENTO.TEQ_CODIGO = TIPOEQUIPAMENTO.TEQ_CODIGO AND EQUIPAMENTO.MDD_CODIGO =
Consulta 8 - Obter a descrição do Medidor e a Descrição do Departamento de polícia dos medidores que estiverem dentro da área do departamento de polícia
SELECT MEDIDOR.MDD_DESCRICAO, DEPARTAMENTO_POLICIA.DPP_DESCRICAO
FROM MEDIDOR, DEPARTAMENTO_POLICIA WHERE
SDO_INSIDE(MEDIDOR.MDD_LOCAL_INSTALACAO, DEPARTAMENTO_POLICIA.DPP_LOCALIDADE) = 'TRUE'
Consultas no Banco de Dados Corporativo (SPU)
Abaixo será demonstrado à implementação de 03 (Três) consultas adicionais, como parte dos Aplicativos de banco de dados CCM, ENB, USO devidamente integrado no Aplicativo de Banco de Dados Setorial (VE).
Consulta 9 - Deverá ser formulada e implementada no Nível Estratégico, envolvendo, simultaneamente, pelo menos 01 (uma) relação do Aplicativo que esteja no Banco de Dados Corporativo (BDC).
Consulta em linguagem natural: Recuperar o nome do departamento de policia que possui o veículo com quilometragem maior que 30.000 km
Consulta em linguagem estruturada de consultas
SELECT d. dpp_descrição
FROM Departamento_Policia d, Veiculo v
WHERE v.vei_departamento=d.dpp_codigo
O Resultado Final, utilizando a Ferramenta Oracle SQL Developer, pode ser visualizado na Figura 10.
Figura 10 - Consulta envolvendo duas relações
Consulta 10 - Deverá ser formulada e implementada no Nível Estratégico, envolvendo, simultaneamente, pelo menos 02 (duas) relação do Aplicativo que esteja no Banco de Dados Corporativo (BDC).
Consulta em linguagem natural: Recuperar o nome do local e a área do departamento de policia que possui o veículo com quilometragem entre 10.000 km e 30.000 km
Consulta em linguagem estruturada de consultas
SELECT l.loc_identificacao, l.loc_area
FROM Departamento_Policia d, Local l, Veiculo v
WHERE v.vei_departamento=d.dpp_codigo
AND l. dpp_codigo=d. dpp_codigo
AND v.vei_hodometro_atual Between 10000 AND 30000;
O Resultado Final, utilizando a Ferramenta Oracle SQL Developer, pode ser visualizado na Figura 11.
Figura 11 - Consulta envolvendo duas relações
Consulta 11 - Deverá ser georreferenciada e também formulada e implementada Nível Estratégico, envolvendo, simultaneamente uma relação georreferenciada do Aplicativo de BD (VE) e pelo menos 01 (uma) relação georreferenciada de pelo menos 01 (um) Aplicativo do BD georreferenciado (PO).
Consulta em linguagem natural: Encontre o nome do veiculo e o nome do departamento que se encontra na posição de longitude entre 22.00 à 45.46 e de latitude entre 23.00 à 46.00
Consulta em linguagem estruturada de consultas
SELECT v.vei_nome_veiculo,d.dpp_descricao
FROM Departamento_Policia d, Veiculo v
WHERE v.vei_departamento=d.dpp_codigo
AND SDO_FILTER(d. dpp_localidade,
SDO_GEOMETRY(3001,8292,NULL,SDO_ELEM_INFO_ARRAY(1,10