relatorioListEx3 240

(1)

Pós-Graduação em Engenharia Eletrônica e

Computação - Área Informática (PG/EEC-I)

Relatório ListEx 3

CE-240

Projeto de Sistema de Banco de Dados

Prof. Dr. Adilson Marques da Cunha Aluno: Jedson Zendron Figueiredo

(2)

SUMÁRIO

1 INTRODUÇÃO ... 3

2 RESULTADOS DO DESENVOLVIMENTO DO TRABALHO ... 4

2.1. TÍTULOORIGINALMENTEPROPOSTO ... 4

2.2. MOTIVAÇÃO ... 4

2.3. OBJETIVO ... 4

2.4. NORMALIZAÇÃO ... 4

2.4.1. Antes de Aplicar a Técnica de Normalização – 0FN ... 5

2.4.2. 1ª Forma Normal – 1FN ... 5

2.4.5. Modelagem Utilizando a Ferramenta ERwin 4.1 ... 8

2.4.6. Exemplo de Tuplas das Entidades ... 8

2.4.6. Código SQL para criar as entidades no Banco de Dados... 9

3 CONCLUSÃO... 10

(3)

1 INTRODUÇÃO

Este documento apresenta o resultado quanto à Técnica de Normalização apresentada como conteúdo da matéria Projeto de Sistema de Bancos de Dados (CE-240), que visa contribuir para o aumento da eficiência do aluno no contexto da utilização apropriada desta técnica para tornar os Modelos dos Bancos de Dados bastante estáveis, robustos e confiáveis, isto é, sujeitos a poucas manutenções.

O trabalho realizado visa cumprir com os objetivos apresentados pela ListEx3, desenvolvendo a versão 1.0 do Protótipo de Projeto de Aplicativo de Banco de Dados (ABD), na 3ª Forma Normal (3FN), no contexto da temática escolhida, visando melhorar os tempos de acesso, em termos de armazenamento e recuperação de informações, e reduzir as anomalias de atualizações e inconsistências.

(4)

2 RESULTADOS DO DESENVOLVIMENTO DO TRABALHO

O trabalho está relacionado ao desenvolvimento do Protótipo de Aplicativo de Banco de Dados (ABD) numa versão 1.0, utilizando a Teoria de Normalização e a “Heurística dos 5 + ou – 2 Elementos”. Isto é, desenvolver um Aplicativo de Banco de Dados com no mínimo 03 (três) Atributos e 03 (três) Tuplas, para fins de testes futuros de verificação e validação de aprendizagem das Técnicas de Banco de Dados.

2.1. TÍTULO ORIGINALMENTE PROPOSTO

O título originalmente proposto do aplicativo de banco de dados é: “Um Aplicativo de Banco de Dados para o Gerenciamento de Difusão de Dados Hidrológicos via Web Server” (ADB - GDH).

2.2. MOTIVAÇÃO

Este trabalho motiva-se em aplicar as técnicas de normalização no Protótipo de Aplicativo de Banco de Dados, visando identificar a eficácia e a agilidade que essa técnica propicia.

2.3. OBJETIVO

De acordo com a temática escolhida, este trabalho tem por objetivo desenvolver um aplicativo de banco de dados com no mínimo 03 (três) Entidades, sendo pelo menos uma Entidade Georreferenciada, e cada uma delas possuindo no mínimo 03 (três) Atributos e 03 (três) Tuplas, para fins de testes futuros de verificação e validação de aprendizagem das técnicas de Banco de Dados.

2.4. NORMALIZAÇÃO

O conceito de Normalização foi introduzido por E. F. Codd em 1970, como Primeira Forma Normal (1FN), uma técnica que consiste de um Processo

(5)

Matemático Formal fundamentado na Teoria de Conjuntos. Por meio do Processo Matemático da Normalização, pode-se substituir gradativamente, um conjunto de Entidades e Relacionamento por outro, mais “Adequado, Praticável e Aceitável”, em relação às Anomalias (DEFeitos) de Atualização (Inclusão, Alteração e Exclusão).

2.4.1. Antes de Aplicar a Técnica de Normalização – 0FN

Nesta etapa foi construída somente uma coleção de grandes Registros Únicos ou Tupla.

0FN – GERENCIAMENTO {ger_cod, dado_hidrologico_cod, tipo_dado_cod, descricao, data_gerenciada, data_difusao, dado_hidrologico, tipo_ dado, tipo_dado_ativo, cidade_coletada, posicao_coleta}

2.4.2. 1ª Forma Normal – 1FN

Diz-se que uma relação está na 1FN, quando todos os seus registros possuem o mesmo conjunto de atributos, e esses atributos são atômicos, isto é, são itens indivisíveis. Note que nenhuma das chaves primárias não é capaz de identificar um determinado registro. Somente com a combinação de chaves é possível identificar. Portanto esta relação na 1FN, ainda contém anomalias, características que são indesejáveis que podem causar dificuldade na manipulação.

1FN – GERENCIAMENTO {ger_cod, dado_hidrologico_cod, tipo_dado_cod, descricao, data_gerenciada, data_difundida, dado_hidrologico, tipo_ dado, tipo_dado_ativo, cidade_coletada, posicao_coleta}

Quando se deseja incluir um determinado registro este poderá se repetir em várias tuplas, para converter este problema temos que verificar e assegurar que todos os registros incluídos não causariam inconsistência nos dados.

(6)

O mesmo acontece quando se deseja atualizar um determinado registro, alguma função de decomposição dos atributos deveria ser executada pelo aplicativo, a fim de encontrar cada ocorrência do valor a ser atualizado para não gerar inconsistência.

Os atributos dado_hidrologico e tipo_dado não estão se referindo a chave ger_cod, mas sim a Dados Hidrológicos e Tipo de Dados respectivamente, portanto tem-se a necessidade de criar novas Entidades, ficando assim na 2FN.

1FN – GERENCIAMENTO {ger_cod, dado_hidrologico_cod, tipo_dado_cod, descricao, data_gerenciada, data_difusao, dado_hidrologico, tipo_ dado, tipo_dado_ativo, cidade_coletada, posicao_coleta}

2FN – GERENCIAMENTO {ger_cod, descricao, data_gerenciada, data_difusao}

TIPO_DADO {tip_cod, descricao, ativo}

DADO_HIDROLOGICO {dhi_cod, descricao, posicao_coleta,

cidade_coleta}

Anomalia(s) resolvida(s) da passagem da 1FN para a 2FN:

Inclusão: Não era possível incluir somente um dado hidrológico, tinha que incluir um Gerenciamento, um tipo de dado também, com a aplicação da 2FN essa anomalia foi resolvida.

Atualização: Quando se desejava atualizar um tipo de dado, tinha que percorrer a entidade inteira para fazer a atualização em todas as gerencias. Com a aplicação da 2FN essa anomalia foi resolvida, não existindo mais duplicidades.

Exclusão: Quando se desejava excluir um tipo de dado, incluía-se também um gerenciamento e dado hidrológico. Com a aplicação da 2FN essa anomalia foi resolvida.

(7)

Refere-se ao agrupamento de relações requeridas na 2FN. Cada atributo não chave deve se referir diretamente à chave. Relações transitivas entre atributos não chave e atributos chave devem ser eliminadas.

Sentiu-se a necessidade de criar uma quarta entidade para relacionar os atributos ger_cod, dhi_cod e tip_cod que será chamada DIFUSAO. Esta relação serve para dizer que um gerenciamento pode ter vários tipos de dados, em diversos dados hidrológicos e que um tipo de dado ou dado hidrológico pode ter vários gerenciamentos.

1FN – GERENCIAMENTO {ger_cod, dado_hidrologico_cod, tipo_dado_cod, descricao, data_gerenciada, data_difusao, dado_hidrologico, tipo_ dado, tipo_dado_ativo, cidade_coletada, posicao_coleta}

2FN – GERENCIAMENTO {ger_cod, descricao, data_gerenciada, data_difusao}

DADO_HIDROLOGICO {dhi_cod, descricao, posicao_coleta,

cidade_coleta}

3FN – GERENCIAMENTO {ger_cod, descricao, data_gerenciada}

DADO_HIDROLOGICO {dhi_cod, posicao_coletada, cidade_coletada,

descricao}

DIFUSAO {ger_cod, dhi_cod, tip_cod, data_difusao}

Anomalia(s) resolvida(s) da passagem da 2FN para a 3FN:

Inclusão: Não era possível inserir somente um novo gerenciamento, tinha que inserir também um novo dado hidrológico. Essa anomalia foi resolvida com a aplicação da 3FN.

Atualização: Para atualizar um gerenciamento era necessário percorrer a entidade toda para encontrar todas as ocorrências do gerenciamento alvo, com a aplicação da 3FN isso foi resolvido, não existindo mais duplicidades e também não existia uma correlação entre as entidades.

(8)

Desta forma pode-se dizer que a versão 1.0 do Protótipo de Aplicativo de Banco de Dados individual está em Terceira Forma Normal e trigramado, seguindo o contexto da temática definida, anteriormente, na ListEx 02.

2.4.5. Modelagem Utilizando a Ferramenta ERwin 4.1

A Figura 1 representa o Modelo Entidade e Relacionamento MER do Protótipo de Aplicativo de Banco de Dados para o Gerenciamento de Difusão de Dados Hidrológicos via web Server.

Figura 1 – Modelo Entidade e Relacionamento (MER), construído no Erwin 4.1

2.4.6. Exemplo de Tuplas das Entidades

Tabela GERENCIAMENTO

ger_cod ger_descricao ger_data_gerenciada 1 Gerenciamento Periódico 08/04/2009 2 Gerenciamento Periódico 09/04/2009 3 Gerenciamento Periódico 10/04/2009

(9)

Tabela TIPO_DADO

tip_cod tip_descricao tip_avito

1 Ostensiva SIM

2 Confidencial SIM

3 Evento Crítico SIM

Tabela DADO_HIDROLOGICO dhi_co d dhi_posicao_coletad a dhi_cidade_coletad a dhi_descrica o

1 1.33, 1.44, 2.44 Manaus Níveis de Rios

2 1.32, 1.55, 5.33 Cuiabá Índices de

Chuvas 3 1.23, 4.33, 7.44 Campo Grande Índices de

Poluições

Tabela DIFUSAO

ger_cod dhi_cod tip_cod dif_data_difusao

1 1 1 11/04/2009

2 2 3 12/04/2009

3 3 3 13/04/2009

2.4.7. Código SQL para criar as entidades no Banco de Dados

(10)

3 CONCLUSÃO

Após a realização desta Lista de Exercícios, foi possível verificar a importância da utilização das técnicas de normalização, a fim de minimizar gastos ao reparar erros. A partir disso, foi possível gerar o Modelo de Entidade e Relacionamento (MER), utilizando a Ferramenta ERwin 4.1.

(11)

Anexo A

CREATE TABLE DADO_HIDROLOGICO ( dhi_cod INTEGER NOT NULL,

dhi_descricao VARCHAR(20) NOT NULL, dhi_cidade_coletada VARCHAR(20) NULL, dhi_posicao_coletada INTEGER NULL );

ALTER TABLE DADO_HIDROLOGICO ADD ( PRIMARY KEY (dhi_cod) ) ;

CREATE TABLE DIFUSAO (

dif_data_difusao DATE NULL,

ger_cod INTEGER NOT NULL, dhi_cod INTEGER NOT NULL, tip_cod INTEGER NOT NULL );

ALTER TABLE DIFUSAO

ADD ( PRIMARY KEY (ger_cod, dhi_cod, tip_cod) ) ;

CREATE TABLE GERENCIAMENTO ( ger_cod INTEGER NOT NULL, ger_descricao VARCHAR(20) NULL, ger_data_gerenciada DATE NULL );

ALTER TABLE GERENCIAMENTO ADD ( PRIMARY KEY (ger_cod) ) ;

CREATE TABLE TIPO_DADO (

tip_cod INTEGER NOT NULL, tip_descricao VARCHAR(20) NULL, tip_ativo VARCHAR(20) NULL );

ALTER TABLE TIPO_DADO

(12)

ALTER TABLE DIFUSAO

ADD ( FOREIGN KEY (tip_cod)

REFERENCES TIPO_DADO ) ;

ALTER TABLE DIFUSAO

ADD ( FOREIGN KEY (dhi_cod)

REFERENCES DADO_HIDROLOGICO ) ;

ALTER TABLE DIFUSAO

ADD ( FOREIGN KEY (ger_cod)

REFERENCES GERENCIAMENTO ) ;

create trigger tD_DADO_HIDROLOGICO after DELETE on DADO_HIDROLOGICO for each row

-- ERwin Builtin Sat May 09 15:48:41 2009 -- DELETE trigger on DADO_HIDROLOGICO declare numrows INTEGER;

begin

/* ERwin Builtin Sat May 09 15:48:41 2009 */

/* DADO_HIDROLOGICO R/2 DIFUSAO ON PARENT DELETE RESTRICT */

select count(*) into numrows from DIFUSAO where /* DIFUSAO.dhi_cod = :old.dhi_cod */ DIFUSAO.dhi_cod = :old.dhi_cod; if (numrows > 0) then raise_application_error( -20001,

'Cannot DELETE DADO_HIDROLOGICO because DIFUSAO exists.' );

end if;

-- ERwin Builtin Sat May 09 15:48:41 2009 end;

/

create trigger tU_DADO_HIDROLOGICO after UPDATE on DADO_HIDROLOGICO for each row

-- ERwin Builtin Sat May 09 15:48:41 2009 -- UPDATE trigger on DADO_HIDROLOGICO declare numrows INTEGER;

(13)

/* DADO_HIDROLOGICO R/2 DIFUSAO ON PARENT UPDATE RESTRICT */ if

/* :old.dhi_cod <> :new.dhi_cod */ :old.dhi_cod <> :new.dhi_cod then

select count(*) into numrows from DIFUSAO where /* DIFUSAO.dhi_cod = :old.dhi_cod */ DIFUSAO.dhi_cod = :old.dhi_cod; if (numrows > 0) then raise_application_error( -20005,

'Cannot UPDATE DADO_HIDROLOGICO because DIFUSAO exists.' );

end if; end if;

/

create trigger tI_DIFUSAO after INSERT on DIFUSAO for each row -- ERwin Builtin Sat May 09 15:48:41 2009

-- INSERT trigger on DIFUSAO declare numrows INTEGER; begin

/* TIPO_DADO R/3 DIFUSAO ON CHILD INSERT RESTRICT */ select count(*) into numrows

from TIPO_DADO where /* :new.tip_cod = TIPO_DADO.tip_cod */ :new.tip_cod = TIPO_DADO.tip_cod; if ( /* */ numrows = 0 ) then raise_application_error( -20002,

'Cannot INSERT DIFUSAO because TIPO_DADO does not exist.' );

end if;

(14)

/* DADO_HIDROLOGICO R/2 DIFUSAO ON CHILD INSERT RESTRICT */ select count(*) into numrows

from DADO_HIDROLOGICO where /* :new.dhi_cod = DADO_HIDROLOGICO.dhi_cod */ :new.dhi_cod = DADO_HIDROLOGICO.dhi_cod; if ( /* */ numrows = 0 ) then raise_application_error( -20002,

'Cannot INSERT DIFUSAO because DADO_HIDROLOGICO does not exist.'

); end if;

/* GERENCIAMENTO R/1 DIFUSAO ON CHILD INSERT RESTRICT */ select count(*) into numrows

from GERENCIAMENTO where /* :new.ger_cod = GERENCIAMENTO.ger_cod */ :new.ger_cod = GERENCIAMENTO.ger_cod; if ( /* */ numrows = 0 ) then raise_application_error( -20002,

'Cannot INSERT DIFUSAO because GERENCIAMENTO does not exist.' );

end if;

/

create trigger tU_DIFUSAO after UPDATE on DIFUSAO for each row -- ERwin Builtin Sat May 09 15:48:41 2009

-- UPDATE trigger on DIFUSAO declare numrows INTEGER; begin

(15)

select count(*) into numrows from TIPO_DADO where /* :new.tip_cod = TIPO_DADO.tip_cod */ :new.tip_cod = TIPO_DADO.tip_cod; if ( /* */ numrows = 0 ) then raise_application_error( -20007,

'Cannot UPDATE DIFUSAO because TIPO_DADO does not exist.' );

end if;

/* DADO_HIDROLOGICO R/2 DIFUSAO ON CHILD UPDATE RESTRICT */ select count(*) into numrows

from DADO_HIDROLOGICO where /* :new.dhi_cod = DADO_HIDROLOGICO.dhi_cod */ :new.dhi_cod = DADO_HIDROLOGICO.dhi_cod; if ( /* */ numrows = 0 ) then raise_application_error( -20007,

'Cannot UPDATE DIFUSAO because DADO_HIDROLOGICO does not exist.'

); end if;

/* GERENCIAMENTO R/1 DIFUSAO ON CHILD UPDATE RESTRICT */ select count(*) into numrows

from GERENCIAMENTO where /* :new.ger_cod = GERENCIAMENTO.ger_cod */ :new.ger_cod = GERENCIAMENTO.ger_cod; if ( /* */ numrows = 0 ) then

(16)

raise_application_error( -20007,

'Cannot UPDATE DIFUSAO because GERENCIAMENTO does not exist.' );

end if;

/

create trigger tD_GERENCIAMENTO after DELETE on GERENCIAMENTO for each row

-- ERwin Builtin Sat May 09 15:48:41 2009 -- DELETE trigger on GERENCIAMENTO declare numrows INTEGER;

begin

/* GERENCIAMENTO R/1 DIFUSAO ON PARENT DELETE RESTRICT */ select count(*) into numrows

from DIFUSAO where /* DIFUSAO.ger_cod = :old.ger_cod */ DIFUSAO.ger_cod = :old.ger_cod; if (numrows > 0) then raise_application_error( -20001,

'Cannot DELETE GERENCIAMENTO because DIFUSAO exists.' );

end if;

/

create trigger tU_GERENCIAMENTO after UPDATE on GERENCIAMENTO for each row

-- ERwin Builtin Sat May 09 15:48:41 2009 -- UPDATE trigger on GERENCIAMENTO declare numrows INTEGER;

begin

/* GERENCIAMENTO R/1 DIFUSAO ON PARENT UPDATE RESTRICT */ if

/* :old.ger_cod <> :new.ger_cod */ :old.ger_cod <> :new.ger_cod then

(17)

from DIFUSAO where /* DIFUSAO.ger_cod = :old.ger_cod */ DIFUSAO.ger_cod = :old.ger_cod; if (numrows > 0) then raise_application_error( -20005,

'Cannot UPDATE GERENCIAMENTO because DIFUSAO exists.' );

end if; end if;

/

create trigger tD_TIPO_DADO after DELETE on TIPO_DADO for each row -- ERwin Builtin Sat May 09 15:48:41 2009

-- DELETE trigger on TIPO_DADO declare numrows INTEGER;

begin

/* TIPO_DADO R/3 DIFUSAO ON PARENT DELETE RESTRICT */ select count(*) into numrows

from DIFUSAO where /* DIFUSAO.tip_cod = :old.tip_cod */ DIFUSAO.tip_cod = :old.tip_cod; if (numrows > 0) then raise_application_error( -20001,

'Cannot DELETE TIPO_DADO because DIFUSAO exists.' );

end if;

/

create trigger tU_TIPO_DADO after UPDATE on TIPO_DADO for each row -- ERwin Builtin Sat May 09 15:48:41 2009

-- UPDATE trigger on TIPO_DADO declare numrows INTEGER;

begin

(18)

if

/* :old.tip_cod <> :new.tip_cod */ :old.tip_cod <> :new.tip_cod then

select count(*) into numrows from DIFUSAO where /* DIFUSAO.tip_cod = :old.tip_cod */ DIFUSAO.tip_cod = :old.tip_cod; if (numrows > 0) then raise_application_error( -20005,

'Cannot UPDATE TIPO_DADO because DIFUSAO exists.' );

end if; end if;