Lista de Exercícios 04 (ListEx4).

(1)

“ Lista de Exercícios 04 (ListEx4)”.

INTRODUÇÃO

Modelos de dados para as aplicações geográficas têm necessidades adicionais, tanto com relação à abstração de conceitos e entidades, quando ao tipo de entidades representáveis e

seu inter-relacionamento.

Foi utilizado o ERWIN 4.0, ROSE TIME e ORACLE 10g, como software básico para desenvolvimento do subsistema.

ITA - Instituto Tecnológico de Aeronáutica CTA - Centro Técnico Aeroespacial

(2)

OBJETIVO

Implementar a Terceira Forma Normal (3º FN) do seu Protótipo de Aplicativo de Banco de Dados (BD) utilizando um Modelo de Dados Objeto-Relacional no Sistema Gerenciador de Banco de Dados (SGBD) Oracle 10g Spatial 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). E pesquisar os Banco de Dados Hierárquico, Orientado Objeto e rede e converter a 3º FN do Protótipo para os tipos de banco de dados citados acima;

MOTIVAÇÃO

Apenas 2,5% da massa líquida são compostas de água doce, e menos de 0,01%, de potável, sendo que a Bacia Amazônica tem a maior oferta de água doce do mundo. Mas a oferta de água doce vem sofrendo escassez mundial, devido o desperdício, poluição e desmatamento.

Com desenvolvimento de um Banco de dado, podemos controlar o desperdício de água.

CONTEÚDO

LOCALIZACAO loc_cod loc_tempo loc_municipio

SETOR set_cod loc_cod (FK) set_tipo set_rio set_estacao set_vasup set_vasub

BACIA bac_cod bac_nome bac_tamp

SUB_BACIA sub_cod sub_nome sub_vasup sub_vasub bac_cod (FK)

ABASTECIMENTO aba_cod

sub_cod (FK) set_cod (FK) loc_cod (FK) aba_geom

(3)

Construção do Aplicativo e Massa de Dados Entidade: BACIA

CREATE TABLE BACIA (

bac_cod VARCHAR(20) NOT NULL, bac_nome VARCHAR(50) NOT NULL, bac_tam VARCHAR(20) NULL, PRIMARY KEY (bac_cod));

Inserção dos dados

insert into BACIA Values('001', 'Amazona', 6925000);

insert into BACIA Values('002', 'Tocantins', 107235);

insert into BACIA Values('003', 'São Francisco', 342900);

insert into BACIA Values('004', 'Região Sudeste', 229972);

Entidade: SUB_BACIA

CREATE TABLE SUB_BACIA (

sub_cod VARCHAR(20) NOT NULL, sub_nome VARCHAR(50) NOT NULL, sub_vasup VARCHAR(20) NULL, sub_vasub VARCHAR(20) NULL, bac_cod VARCHAR(20) NULL, primary key (sub_cod)

);

insert into SUB_BACIA Values('011','RIO AMAZONAS,MADEIRA,GUAPORE', 61, 31, '001');

insert into SUB_BACIA Values('012','RIO SOLIMOES,PURUS,COARI', 42,58,'001');

insert into SUB_BACIA Values('013', 'RIO

AMAZONAS,TAPAJOS,JURUENA',42,58,'001');

insert into SUB_BACIA Values('014', 'RIO AMAZONAS,TAPAJOS,JURUENA', 51,49,'001');

(4)

Entidade: SETOR

CREATE TABLE SETOR (

set_cod VARCHAR(20) NOT NULL, set_tipo VARCHAR(50) NOT NULL, set_estacao VARCHAR(50) NOT NULL, set_rio VARCHAR(50) NOT NULL, set_vasup VARCHAR(20) NOT NULL, set_vasub VARCHAR(20) NOT NULL, loc_cod VARCHAR(20) NOT NULL, primary key (set_cod),

FOREIGN KEY (loc_cod) );

insert into SETOR values ( '000001','Doméstico','Ipiranga Novo','Rio Guaporé', 21,25,'000011');

insert into SETOR values ('000002','Industrial','Cristo','Rio Mucuim', 18, 37, '000012');

insert into SETOR values ('000003', 'Agricola','Jaru','Rio Jarú', 61,38, '000013');

insert into SETOR values ('000004', 'Doméstico','Boca do Inferno','Rio Curua', 20,25, '000013');

Entidade: LOCALIZACAO

CREATE TABLE LOCALIZACAO (

loc_cod VARCHAR() NOT NULL, loc_tempo VARCHAR() NOT NULL, loc_municipio VARCHAR() NOT NULL );

insert into LOCALIZACAO values( 000011, 'janeiro','Vila Bela da St. Trindade');

insert into LOCALIZACAO values(000012, 'feveiro', 'Labrea');

insert into LOCALIZACAO values(000013, 'julho','Jarú ');

insert into LOCALIZACAO values(000014, 'dezembro', 'Alenquer');

(5)

Entidade: ABASTECIMENTO

CREATE TABLE ABASTECIMENTO (

area_cod VARCHAR(20) NOT NULL, area_geom MDSYS.SDO_GEOMETRY, sub_cod VARCHAR(20) NOT NULL, set_cod VARCHAR(20) NOT NULL, loc_cod VARCHAR(20) NOT NULL, PRIMARY KEY (area_cod)

);

Inserção de Metadado para Georeferenciamento

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('ABASTECIMENTO', 'ABA_GEOM',

MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('LONG', -46.52157, - 74.4357, 5.0E-8), MDSYS.SDO_DIM_ELEMENT('LAT', -13477605, 5.4223, 5.0E-8)), NULL);

Criação de ponto para Georeferenciamento

CREATE INDEX LOCALIZADOR_SDX ON ABASTECIMENTO (ABA_GEOM) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('SDO_INDX_DIMS=2 LAYER_GTYPE="POINT"');

insert into ABASTECIMENTO values ('000001', MDSYS.SDO_GEOMETRY( 2001, NULL, NULL, SDO_ELEM_INFO_ARRAY (1,1,1), SDO_ORDINATE_ARRAY (-15.0, -59.5)), '011', '000001', '000011');

insert into ABASTECIMENTO values ('000002', MDSYS.SDO_GEOMETRY( 2001, NULL, NULL,SDO_ELEM_INFO_ARRAY (1,1,1), SDO_ORDINATE_ARRAY (-7.2, - 64.1)), '012', '000002', '000012');

(6)

CONSULTAS SQL

LEGENDA

Atributo de Interseção entre as Tabelas.

Resultado do 1º Filtro (Where).

Resultado do 2º Filtro (Where).

Resultado do 3º Filtro (Where) Resultado final da consulta (Select).

1- Quais são as Sub-Bacias e seus volumes de água superficial e subterrâneo, onde o volume de superficial é maior que 50?

select sub_nome, sub_vasup, sub_vasub from sub_bacia

where sub_vasup>50;

2 – Quais os setores, os valor do consumo de água e qual a localização e o mês, onde rio é igual a Rio Guaporé?

select SETOR.set_tipo, SETOR.set_vasup, SETOR.set_vasub, LOCALIZACAO.loc_municipio, LOCALIZACAO .loc_tempo

from SETOR INNER JOIN LOCALIZACAO on (LOCALIZACAO.loc_cod = SETOR.loc_cod)

where SETOR.set_rio = 'Rio Guaporé'

ou

select SETOR.set_tipo, SETOR.set_vasup, SETOR.set_vasub,

(7)

LOCALIZACAO.loc_municipio, LOCALIZACAO .loc_tempo from SETOR , INNER JOIN

where (SETOR.set_rio = 'Rio Guaporé' and LOCALIZACAO.loc_cod = SETOR.loc_cod)

3 – Qual é o nome da Bacia , Sub_Bacia e do rio que abastecem os setores?

select BACIA. bac_nome, SUB_BACIA.sub_nome, SETOR.set_rio FROM SETOR, ABASTECIMENTO, SUB_BACIA, BACIA

WHERE BACIA.bac_cod = SUB_BACIA.bac_cod and ABASTECIMENTO.sub_cod = SUB_BACIA.sub_cod and ABASTECIMENTO.set_cod = SETOR.set_cod ;

(8)

4- Lista os nomes das sub_bacias e suas referências geográfica, onde sub_bacia seja diferente que RIO AMAZONAS,TAPAJOS,JURUENA.

Select abastecimento.aba_geom, sub_bacia.sub_nome from abastecimento, sub_bacia

Where ((abastecimento.sub_cod = sub_bacia.sub_cod) and (sub_bacia.sub_nome <> 'RIO AMAZONAS,TAPAJOS,JURUENA') );

(9)

TIPOS DE BANCO DE DADOS

Banco de Dados do tipo Hierárquico surgiu na década de 60, organizando os dados à semelhança de uma árvore, onde um arquivo-pai gerava vários filhos, mantendo uma dependência entre eles. Possuem caminhos bem definidos e predeterminados, sendo utilizados na solução de problemas que demandem um número limitado de respostas, cujas naturezas podem ser especificadas antecipadamente.

Aplicações típicas: processar reservas aéreas ou processamento de dados bancários.

ABASTECIMENTO

SETOR

set_cod loc_cod set_tipo set_estacao set_rio set_vasup set_vasub

SUB_BACIA Sub_cod Bac_nome Sub_nome Sub_vasup Sub_vasub

LOCALIZACAO

loc_cod loc_tempo loc_municipio

BACIA bac_cod bac_nome bac_vasub

Banco de Dados do tipo Rede surgiu nos anos 60, sendo utilizado também durante a década de 70. Ele mostra-se mais apropriado para situações em que vários dados podem estar associados a diversos outros, sendo mais flexíveis do que os modelos hierárquicos.

Porém, existem limitações de ordem prática quanto ao número de relacionamentos que podem ser estabelecidos entre os registros.

aba_cod aba_geom sub_cod loc_cod sub_cod

(10)

Aplicações típicas: transações efetuadas entre diversas contas em um sistema bancário.

ABASTECIMENTO

SUB_BACIA

Banco de Dados do tipo Relacional surgiu nos anos 70 e evoluiu durante os anos 80. São os mais utilizados atualmente, existindo diversas marcas comerciais como DB II, Oracle, SQL Server, Informix, MySQL, entre outros.

Banco de Dados do tipo Orientados Objeto começaram a ser desenvolvidos para atender a uma necessidade relacionada ao armazenamento de tipos de dados cada vez mais complexos, de difícil representação no modelo relacional. Possui três pilares principais:

herança, polimorfismo e encapsulamento.Este modelo apresenta maior flexibilidade na manipulação de seu conteúdo e por meio de identificadores de objetos manipula os dados de forma consistente.

LOCALIZACAO SETOR

BACIA

(11)

LOCALIZ ACAO loc _c od : VAR CH AR (20) loc _tempo : VAR CH AR (20) loc _munic ipio : VAR CH AR (50) insert()

update() delete()

<no rolename> ( SET OR )

SET OR set_c od : VAR CH AR (20) set_tipo : VAR CH AR (50) set_estac ao : VAR CH AR (50) set_rio : VAR CH AR (50) set_v asup : VAR CH AR (20) set_v asub : VAR CH AR (20) loc _c od : VAR CH AR (20) insert()

update() delete()

<no rolename> ( ABAST ECIM EN T O ) 1:N

BACIA bac _c od : VAR CH AR (20) bac _nome : VAR CH AR (50) bac _tam : VAR CH AR (20) insert()

update() delete()

<no rolename> ( ABAST ECIM EN T...

<no rolename> ( SU BBACIA )

SU BBACIA sub_c od : VAR CH AR (20) sub_nome : VAR CH AR (50) sub_v asup : VAR CH AR (20) sub_v asub : VAR CH AR (20) bac _c od : VAR CH AR (20) insert()

update() delete()

<no rolename> ( BACIA )

<no rolename> ( ABAST ECIM EN T O ) 0/ 1:N

ABASTECIM EN T O aba_c od : VAR CH AR (20) set_c od : VAR CH AR (20) loc _c od : VAR CH AR (20) sub_c od : VAR CH AR (20) aba_geom : M D SYS.SD O_GEO...

insert() update() delete() 1:N

1:N 0/ 1:N

1:N

1:N 1:N

Sistema de Dicionário de Dados

Dicionário de Dados descrição de atributos, entidades, relacionamentos e objetos associados, ou Banco de Dado da função administração dos recursos de Informação:

Nome Conteúdo Estrutura Integridade Segurança LOCALIZACAO Tabela contendo

os municípios e os meses

Composto por 3 atributos

Leitura, Inclusão e Alteração(Colaboradores ), Exclusão

(Administrador) loc_cod Identificador único

da LOCALIZACAO varchar(20) Primary Key not null loc_municipio Nome do município varchar(50) not null

loc_tempo ^mês varchar(50) not_null

SETOR Tabela contendo os tipos de setores que consomem água, valores em porcentagem de consumo de água

(Administrador)

set _cod Identificador único

do SETOR varchar(20) Primary Key not null loc_cod Identificador único

da LOCALIZACAO varchar(20) Foreing Key not null set_estacao Descrição da

estação de controle de consumo à água.

varchar(50) not null

set_rio Identificação do rio que abastece o setor

(12)

set_vasup Quantidade em porcentagem do consumo de água superficial

set_vasup Quantidade em porcentagem do consumo de água subterrânea

varchar(50) ^{not null}

set_setor Descrição do tipo

de setor que consomem água.

ABASTECIMENTO Tabela contendo informação

georreferenciada.

(Administrador) aba_cod Identificador único

da ABASTECIMENTO

varchar(20) Primary Key not null

set_cod Identificador único

do SETOR varchar(20) Foreing Key not null loc_cod Identificador único

da LOCALIZACAO varchar(20) Foreing Key not null sub_cod Identificador único

da SUB_BACIA varchar(20) Foreing Key not null aba_geom Atributo

Georrefernciado MDSYS.SD O_GEOME TRY

Not null

SUB_BACIA Tabela contendo as descrição das

sub bacias

Hidrográficas, que abastecem os setores.

(Administrador)

sub _cod Identificador único

da SUB_BACIA varchar(20) Primary Key not null

bac_cod Identificador único

da BACIA varchar(20) Foreing Key not null sub_nome Descrição da SUB

BACIA

HIDROGRÁFICA.

sub_vasup Quantidade em porcentagem de água superficial

sub_vasup Quantidade em porcentagem de água subterrânea

BACIA Tabela contendo

as descrição das BACIAS

Hidrográficas

(Administrador)

(13)

bac_cod Identificador único

da BACIA varchar(20) Foreing Key not null bac_nome Descrição da

BACIA

HIDROGRÁFICA.

bac_tam Tamanho de área varchar(20) not null

Diretório de dados descrição de processos associados às Entidades:

Entidade Atributo Relacionamento Entidade-Relacionamento LOCALIZACAO (PK) loc_cod (FK) loc_cod ^SETOR

SETOR (PK) set_cod (FK) set_cod LOCALIZACAO AND ABASTECIMENTO

ABASTECIMENTO (PK) aba_cod (FK) aba_cod SETOR AND SUB_BACIA

SUB_BACIA (PK) sub_cod (FK) sub_cod ABASTECIMENTO AND BACIA

BACIA (PK) aba_cod (FK) aba_cod _{SUB_BACIA}

Diretório de Recursos de Dados, descrição física das entidades e de seus ambientes associados.

O Protótipo de Aplicativo de Banco de Dados, será desenvolvido utilizando um Computador PC Atlon XP – 2600 MHz, com 1 Gbytes de memória RAM e com capacidade de 80 Gbytes de armazenamento em disco. A implementação será realizada usando o Banco de Dado Oracle 10g como SGBD, http://seti.fcmf.ita.br:5560/isqlplus/ .

Dicionário de Matados, descrição conceitual das entidades num nível alto de abstração.

(14)

CONCLUSÃO

A Constrição de uma modelagem de dado feita de modo ocorreto, facilita muito o desenvolvimento do Banco de Dados.

RECOMENDAÇÃO Navegar no site da Oracle.