Anexo1-MER

(1)

Instituto Tecnológico de Aeronáutica

Divisão de Engenharia da Computação

CE-240

Projeto de Sistemas de Bancos de Dados

Professor Doutor Adilson Marques da Cunha

Listex 4

Anexo1: Implementação de um Modelo Relacional para o

Aplicativo de Banco de Dados dos Equipamentos de Polícia

(ABD-EQP-PO)

Roberto Pepato Mellado

07 de maio de 2011

(2)

Sumário

1 Objetivo ... 3

2 Principais Passos ... 3

2.1 Modelo Entidade Relacionamento: ... 3

2.2 Scripts de Criação do Modelo de Dados: ... 4

2.3 Elaboração de Consultas em Linguagem Natural: ... 9

2.4 Elaboração de Consultas em Linguagem SQL: ... 9

(3)

1 Objetivo

A realização da lista de exercícios 4 foi motivada pela necessidade de aplicação dos conceitos demonstrados em aulas da disciplina CE-240 – Projetos de Sistemas de Banco de Dados, apresentados nos quatro primeiros meses do ano de 2011. Os objetivos da execução desta lista são:

1. Implementar a Terceira Forma Normal (3ªFN) do Protótipo de Aplicativo de

Banco de Dados de EQuiPamentos do departamento de POlícia

(ABD-EQP-PO) utilizando um Modelo de Dados Relacional em um Sistema Gerenciador de Banco de Dados (SGBD) previamente escolhido e testar a sua funcionalidade, visando reduzir o desperdício de recursos nas futuras fases de integração e melhorar a eficiência operacional dos futuros Bancos de Dados Setoriais (BDS), Bancos de Dados Corporativo (BDC) e do Banco de Dados Holding (BDH); 2. Construir a versão 1.0 do Sistema de Dicionário de Dados do ABD-EQP-PO; e 3. Pesquisar os Modelos de Dados Hierárquico, Rede e Orientado a Objetos, e

Converter a 3ªFN do seu Protótipo de Aplicativo de BD no Modelo de Dados Relacional para os Modelos de Dados Hierárquico, Rede e Orientado a Objetos, visando identificar algumas das suas principais diferenças e características. Este anexo apresenta uma visão detalhada das tarefas realizadas para atingir o primeiro objetivo da lista de exercícios.

2 Principais Passos

a) Assistir e participar das aulas da disciplina CE-240 Projeto de Sistemas de Bancos de Dados;

b) Tomar conhecimento dos conceitos de sistemas de redes inteligentes (smart grid) apresentados em sala e complementá-los com pesquisas adicionais;

c) Conhecer os requisitos e funcionalidades necessários ao desenvolvimento desta lista de exercícios;

d) Tomar contato com ferramentas de gerenciamento de modelos entidade-relacionamento e implementar o modelo do ABD-EQP-PO através de seu uso; e) Obter as credenciais de acesso ao ambiente integrado de desenvolvimento (Oracle

11g Spatial) dos Laboratórios do Instituto Tecnológico de Aeronáutica (ITA); f) Pesquisar o material disponibilizado em aula e a internet para utilização de Oracle

11g Spatial;

g) Projetar, implementar, implantar e testar um modelo entidade-relacionamento e sua representação física no sistema gerenciador de banco de dados (SGBD) Oracle 11g Spatial; e

h) Implementar e implantar 4 consultas em linguagem natural envolvendo 1, 2 e 3 ou mais relações do ABD-EQP-PO e implementá-las em linguagem SQL;

2.1 Modelo Entidade Relacionamento:

A partir da definição e normalização para terceira forma normal (3FN) do ABD-EQP-PO, realizada e publicada na Lista de Exercícios 3 – ListEx3, o modelo entidade-relacionamento (MER) foi projetado através da utilização da ferramenta freeware de

(4)

modelagem de aplicativos de banco de dados DBDesigner 4. O resultado do processo de modelagem é apresentado a seguir:

Figura 1 – Modelo Entidade-Relacionamento (MER) do ABD-EQP-PO

2.2 Scripts de Criação do Modelo de Dados:

A implementação do MER apresentado na Figura 1 – Modelo

Entidade-Relacionamento (MER) do ABD-EQP-PO, foi realizado através de acesso remoto aos

laboratórios do Instituto Tecnológico de Aeronáutica (ITA), utilizando a ferramenta Oracle SQL Developer para execução dos scripts de criação das estruturas, chaves primárias, chaves estrangeiras e índices espaciais do banco de dados. Os scripts utilizados para criação dos elementos citados é apresentado a seguir:

-- CRIACAO DAS TABELAS

CREATE TABLE Fabricante(

Fab_codigo INTEGER NOT NULL, Fab_nome VARCHAR(255) NOT NULL, Fab_telefone VARCHAR(20) NOT NULL);

CREATE TABLE EstadoOperacao( Eop_codigo INTEGER NOT NULL, Eop_abreviacao VARCHAR(3) NOT NULL, Eop_descricao VARCHAR(50) NOT NULL);

CREATE TABLE TipoEquipamento( Teq_codigo INTEGER NOT NULL, Teq_abreviacao VARCHAR(3) NOT NULL,

(5)

Teq_descricao VARCHAR(255) NOT NULL);

CREATE TABLE Medidor (

Mdd_codigo INTEGER NOT NULL, Eop_codigo INTEGER NOT NULL,

Mdd_local_instalacao SDO_GEOMETRY NOT NULL, Mdd_descricao VARCHAR(255) NOT NULL, Mdd_tensao_de_rede INTEGER NOT NULL); CREATE TABLE Equipamento (

Eqp_codigo INTEGER NOT NULL, Teq_codigo INTEGER NOT NULL, Fab_codigo INTEGER NOT NULL, Eop_codigo INTEGER NOT NULL, Mdd_codigo INTEGER NOT NULL, Eqp_tensao_de_operacao INTEGER NOT NULL, Eqp_descricao VARCHAR(255) NOT NULL);

-- CRIACAO DE CHAVES PRIMARIAS

ALTER TABLE Fabricante ADD (PRIMARY KEY (Fab_codigo)); ALTER TABLE EstadoOperacao ADD (PRIMARY KEY (Eop_codigo)); ALTER TABLE TipoEquipamento ADD (PRIMARY KEY (Teq_codigo)); ALTER TABLE Medidor ADD (PRIMARY KEY (Mdd_codigo));

ALTER TABLE Equipamento ADD (PRIMARY KEY (Eqp_codigo, Teq_codigo, Fab_codigo));

-- CRIACAO DE CHAVES ESTRANGEIRAS

ALTER TABLE Equipamento ADD (FOREIGN KEY (Fab_codigo) REFERENCES Fabricante);

ALTER TABLE Equipamento ADD (FOREIGN KEY (Teq_codigo) REFERENCES TipoEquipamento);

ALTER TABLE Equipamento ADD (FOREIGN KEY (Eop_codigo) REFERENCES EstadoOperacao);

ALTER TABLE Equipamento ADD (FOREIGN KEY (Mdd_codigo) REFERENCES Medidor);

ALTER TABLE Medidor ADD (FOREIGN KEY (Eop_codigo) REFERENCES EstadoOperacao);

Após a criação das estruturas de armazenamento de dados, uma massa de dados foi inserida nessas estruturas, para possibilitar a realização de testes e validações deste trabalho. O modelo de dados, juntamente com a massa de dados carregada a partir dos scripts abaixo foram elaborados respeitando a heurística das 5 mais ou menos 2 Entidades, Atributos e Tuplas. Pode-se observar nos scripts, a utilização da instrução SDO_GEOMETRY na inserção de dados na entidade Medidor, indicando a utilização das capacidades geoespaciais do Oracle 11g Spatial e o consequente georreferenciamento das tuplas dessa entidade:

(6)

-- INSERCAO DE DADOS

INSERT INTO Fabricante (Fab_codigo, Fab_Nome, Fab_Telefone) VALUES (1, 'FoxConn', '2325-3255');

INSERT INTO Fabricante (Fab_codigo, Fab_Nome, Fab_Telefone) VALUES (2, 'Siemens', '7561-1266');

INSERT INTO Fabricante (Fab_codigo, Fab_Nome, Fab_Telefone) VALUES (3, 'E-Guard', '3303-3233');

INSERT INTO Fabricante (Fab_codigo, Fab_Nome, Fab_Telefone) VALUES (4, 'Safe World', '2222-2945');

INSERT INTO EstadoOperacao(Eop_codigo, Eop_abreviacao, Eop_descricao) VALUES (1, 'LIG', 'Ligado');

INSERT INTO EstadoOperacao(Eop_codigo, Eop_abreviacao, Eop_descricao) VALUES (2, 'DES', 'Desligado');

INSERT INTO EstadoOperacao(Eop_codigo, Eop_abreviacao, Eop_descricao) VALUES (3, 'MNT', 'Em Manutenção');

INSERT INTO TipoEquipamento(Teq_codigo, Teq_abreviacao, Teq_descricao) VALUES (1, 'CMR', 'Câmera de Segurança');

INSERT INTO TipoEquipamento(Teq_codigo, Teq_abreviacao, Teq_descricao) VALUES (2, 'CTR', 'Controle de Acesso (Catraca)');

INSERT INTO TipoEquipamento(Teq_codigo, Teq_abreviacao, Teq_descricao) VALUES (3, 'CEL', 'Controle de Celas (Controle Operacional)');

INSERT INTO Medidor(Mdd_codigo, Eop_codigo, Mdd_local_instalacao, Mdd_descricao, Mdd_tensao_de_rede) VALUES (1, 1, SDO_GEOMETRY( 2001, NULL, SDO_POINT_TYPE(-23.186226, -45.885999, NULL), NULL, NULL), 'Medidor 1', 110);

(7)

INSERT INTO Equipamento (Eqp_codigo, Teq_codigo, Fab_codigo, Eop_codigo, Mdd_codigo, Eqp_tensao_de_operacao, Eqp_descricao)

VALUES(1, 1, 2, 1, 1, 5, 'Câmera Face norte');

VALUES(2, 1, 3, 1, 1, 12, 'Câmera Face central');

VALUES(3, 2, 2, 2, 2, 18, 'Controle de Acesso da Porta Principal');

VALUES(4, 2, 1, 3, 3, 18, 'Controle de Acesso da Porta Sul');

VALUES(5, 3, 1, 2, 3, 5, 'Controle Operacional das Celas da Carceragem central'); Após a inserção da massa de dados, fez-se necessário a criação de um índice espacial no banco de dados, para permitir posterior consulta através de operadores topológicos. Seguindo as instruções indicadas em sala de aula, inicialmente foram adicionados os metadados da tabela geográfica ao ABD-EQP-PO:

-- CRIACAO DE METADADOS PARA CONSULTAS GEORREFERENCIADAS

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('Medidor', 'Mdd_local_instalacao',

(8)

MDSYS.SDO_DIM_ARRAY(

MDSYS.SDO_DIM_ELEMENT('LONG', -73.994423393, -28.835883971, 5.0E-8), MDSYS.SDO_DIM_ELEMENT('LAT', -33.752061377, 5.271806856, 5.0E-8)) , NULL);

A seguir, o índice espacial foi criado através do script abaixo:

-- CRIACAO DE INDICE ESPACIAL PARA CONSULTAS TOPOLÓGICAS

CREATE INDEX medidor_spatial_index ON Medidor (Mdd_local_instalacao) INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Após a execução de todos os passos indicados acima, o aplicativo ABD-EQP-PO apresentou-se preparado para realização de consultas tabulares e geográficas.

O instantâneo abaixo apresenta uma visão sumarizada dos objetos criados no ambiente de banco de dados do laboratório do ITA:

(9)

2.3 Elaboração de Consultas em Linguagem Natural:

Esta etapa da lista de exercícios envolveu a criação de quatro consultas em linguagem natural envolvendo 1, 2 e 3 ou mais relações do ABD-EQP-PO. As restrições aplicadas à construção de cada uma das consultas foram estabelecidas conforme definição abaixo:

a) A primeira consulta deverá envolver uma relação e pelo menos um comando select e um comando project (ou equivalentes);

b) A segunda consulta deverá envolver duas relações e pelo menos um comando select, um project e um join (ou equivalentes);

c) A terceira consulta deverá envolver três relações e pelo menos um comando select, um project e um join (ou equivalentes); e

d) A quarta consulta deverá ser georreferenciada e envolver pelo menos duas relações, um comando select, um project e um join (ou equivalentes).

Considerando as definições apresentadas acima e a massa de dados pré-disponibilizada pela execução dos passos anteriores desta lista de exercícios, este aluno desenvolveu suas consultas conforme abaixo:

a) Consulta 1: Encontre o nome do fabricante de código 3;

b) Consulta 2: Encontre a descrição do equipamento e a descrição do estado de operação do equipamento de código 3;

c) Consulta 3: 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; e

d) Consulta 4: 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;

2.4 Elaboração de Consultas em Linguagem SQL:

O primeiro passo para construção das consultas envolveu a verificação do resultado de processo de carga de dados na base de dados. Para isso, este autor realizou consultas de seleção de todos os registros das tabelas criadas, visando identificar possíveis inconsistências antes do processo de criação de consultas. Este passo se mostra válido, pois, caso os dados apresentassem inconsistências, seria difícil identificar se o problema no resultado de uma consulta seria derivado da própria consulta ou de uma falha nos dados inseridos na base de dados.

(10)

Figura 3 – Dados da Entidade Fabricante

Figura 4 – Dados da Entidade EstadoOperacao

(11)

Figura 6 – Dados da Entidade Medidor

Figura 7 – Dados da Entidade Equipamento

Após a verificação da integridade dos dados inseridos pela carga de dados, este aluno prosseguiu para o próximo passo da lista de exercícios que envolveu o desenvolvimento das consultas em linguagem SQL.

As consultas a, b, c e d, definidas em linguagem natural no item 2.3, foram implementadas em linguagem SQL segundo os scripts abaixo, e executadas para sua verificação e validação:

a) SELECT Fab_nome FROM Fabricante WHERE Fab_codigo = 3;

A consulta a realiza uma seleção em uma relação (Fabricante), utilizando um filtro (código do equipamento = 3) e uma subsequente projeção (seleção do nome do fabricante). O resultado da execução no ambiente do ABD-EQP-PO desta consulta é apresentado abaixo:

Figura 8 – Consulta a: Obtenção de nome de fabricante

b) SELECT Equipamento.Eqp_descricao, EstadoOperacao.Eop_descricao

FROM Equipamento, EstadoOperacao WHERE Equipamento.Eqp_codigo = 3

AND Equipamento.Eop_codigo = EstadoOperacao.Eop_codigo;

A consulta b realiza uma seleção em duas relações (Equipamento e EstadoOperacao), utilizando dois filtros (código do equipamento = 3 e código de estado de operação de

(12)

equipamento igual a código de estado de operação da entidade EstadoOperacao) e uma subsequente projeção (seleção da descrição do equipamento e da descrição de seu estado de operação). O resultado da execução no ambiente do ABD-EQP-PO desta consulta é apresentado abaixo:

Figura 9 – Consulta b: Obtenção de nome de equipamento e estado de operação

c) SELECT Equipamento.Eqp_tensao_de_operacao, Fabricante.Fab_nome, Medidor.Mdd_tensao_de_rede

FROM Equipamento, Fabricante, Medidor

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;

A consulta c realiza uma seleção em três relações (Equipamento, Fabricante e Medidor), utilizando cinco filtros (código do equipamento = 5, código do fabricante =1, código do tipo de equipamento = 3, código do fabricante do equipamento igual ao código do fabricante da entidade Fabricante, e código do medidor do equipamento igual ao código do medidor da entidade Medidor) e uma subsequente projeção (seleção da tensão de operação do equipamento, do nome do fabricante e da tensão da rede conectada ao medidor). O resultado da execução no ambiente do ABD-EQP-PO desta consulta é apresentado abaixo:

(13)

Figura 10 – Consulta c: Obtenção de dados de três relações

d) 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;

A consulta d realiza uma seleção em três relações (Medidor, Equipamento, e TipoEquipamento), utilizando dois filtros tabulares (código do medidor do equipamento igual ao código do medidor da entidade Medidor e código do tipo de equipamento igual do código de tipo de equipamento da entidade TipoEquipamento) e um filtro espacial. A utilização do filtro espacial determina, para um polígono virtual representando uma região de interesse (a construção desse polígono é indicada a partir do retângulo construído através da instrução SDO_GEOMETRY) quais são os medidores que se encontram dentro desta área. Para realização desse filtro, é realizada a operação de consulta topológica SDO_INSIDE, proveniente do Oracle 11g Spatial e que avalia se uma geometria georreferenciada encontra-se contida em outra geometria georreferenciada. Após os filtros, foi aplicada uma subsequente projeção (seleção da descrição do medidor e da descrição dos tipos de equipamentos associados ao medidor). O resultado da execução no ambiente do ABD-EQP-PO desta consulta é apresentado abaixo:

(14)

Figura 11 – Consulta d: Consulta Geográfica

3 Conclusões

Este anexo da Lista de Exercícios 4 apresentou um modelo entidade relacionamento (MER) do PO em sua terceira forma normal. A implementação do MER do ABD-EQP-PO utilizou dados tabulares e dados de georrefenciamento. Um conjunto de dados (massa de dados) foi inserido nas estruturas propostas no MER. Consultas de verificação e validação foram realizadas contra a massa de dados e apresentaram os resultados esperados, atingindo os requisitos de consultas especificados no enunciado da Lista de Exercícios 4 e registrados no item 2.3 deste anexo.