ListEx4_ValdirGuerra rv01

(1)

INSTITUTO TECNOLÓGICO D

INSTITUTO TECNOLÓGICO DE

E

E AERONÁ

AERONÁ

AERONÁU

AERONÁ

U

UTICA

TICA

CE-240 PROJETO DE SISTEMAS DE BANCO DE DADOS

LISTA DE EXÉRCICIO 04

Revisão 01

Valdir Guerra

Aluno

Professor Dr. Adílson Marques

Cunha

Fundação Getulio Vargas

(2)

2

1.

INTRODUÇÃO

1.1.Titulo

Relato Padronizado da disciplina CE240 Projeto de Sistema de Banco de Dados – Listex4.

1.2.Motivação

Desenvolver habilidades na implementação de um modelo de banco de dados normalizado em um sistema de gerenciamento de banco de dados relacional com georreferenciamento, bem como a conversão do banco outros modelos, a fim reduzir o desperdício de recursos nas futuras fases de integração do meu projeto de tese e nos projetos profissionais.

1.3.Objetivo

Implementar a Terceira Forma Normal (3ªFN) do seu Protótipo de Aplicativo de Banco de Dados (BD) 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); e

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.

2.

CONTEUDO

2.1.Implementação do modelo relacional

A Implementação do modelo relacional foi realizada como segue abaixo:

FERRAMENTA CASE

Para realizar a modelagem e a geração automática dos scripts (DDL) para criação das tabelas, índices e objetos relacionais foi o utilizado o software ERWIN Versão 8.2.01.3297 da empresa CA.

O Modelos de Entidade Relacional encontra-se no anexo (Anexo 4.1) Os Scripts DDL para criação do objetos no banco encontram-se no

(Anexo 4.2)

SISTEMA DE GERENCIAMENTO DE BANCO DE DADOS

O Sistema Gerenciador do banco de dados utilizado nessa implementação foi o Oracle 11g Release 2 Express Edition

(3)

3

FERRAMENTA DE ACESSO AO BANCO

A Ferramenta utilizada para acessar os banco foi o ORACLE SQL Developer versão 3.1.07

Os scripts DML para popular o banco de dados encontram-se no Anexo

(Anexo 4.3)

2.2.Consultas (DQL) em linguagem natural

1) Quais são os usuários que possuem Mídias de RDIF ?

2) Quais são as mídias que estão validas para acessar o site do Bradesco ? 3) Quais são usuários que possuem mídias emitidas pelo ITAU

4) Quais são os acessos possíveis pelo usuário “Angelina Julie” 2.3.Consultas (DQL) em SQL

1) Quais são os usuários que possuem Mídias de RDIF ?

SELECT u.usu_nome

FROM USUARIO U , MIDIA M, TIPO_MIDIA T

WHERE u.usu_id = m.usu_id AND m.tpm_id = t.tpm_id AND t.tpm_nome = 'RFID'

2) Quais são as mídias que estão validas para acessar o site do Bradesco ?

SELECT M.MID_ID, M.MID_NUMERO

FROM MIDIA M, EMISSOR E

WHERE m.ems_id = e.ems_id

AND e.ems_nome = 'BANCO BRADESCO'

(4)

4 3)

Quais são usuários que possuem mídias emitidas pelo ITAU ?

SELECT DISTINCT u.usu_id, u.usr_nome

FROM USUARIO U, MIDIA M, EMISSOR E

WHERE m.ems_id = e.ems_id

AND u.usu_id = m.usu_id

AND e.ems_nome = 'BANCO ITAU'

4) Quais são os acessos possíveis pelo usuário “Angelina Julie”

SELECT a.ace_id, a.ace_nome, a.ace_latitude, a.ace_longitude, a.ace_altura

FROM ACESSO A, VALIDACAO V, MIDIA M, USUARIO u

WHERE a.ace_id = v.ace_id AND v.mid_id = m.mid_id AND m.usu_id = u.usu_id

AND u.usu_nome = 'ANGELINA JULIE'

2.4.TESTE DE INTEGRIDADE RELACIONAL 2.4.1. Teste de Inserção

(5)

5

2.4.3. Teste de Atualização

2.5.Dicionário de Dados

O dicionário das tabelas desse modelo esta no anexo (Anexo 4.4)

2.6.Diretório de Dados

O diretório de dados esta no anexo (Anexo 4.5)

2.7.Diretório de Dados

O diretório de recursos esta no anexo (Anexo 4.6)

2.8.Diretório de Recursos

2.9.Conversão dos modelos

3.

PRINCIPAIS CONCLUSÕES

Com a execução da listex 4 foi possível aprimorar o conhecimento das técnicas de implementação de um modelo normalizado em um banco de dados relacional.

A utilização de relações ou tabelas georreferenciados traz um importante diferencial para a aplicação.

Quando um MER é corretamente normalizado, diagramado, e abstraído utilizando-se de técnicas com a da heurística da objetivação nota-se que qualquer pergunta realizada em linguagem natural pode ser facilimamente estruturada e respondida em SQL.

(6)

6

4.

ANEXOS

4.1.Modelo de Entidade Relacional

4.2.Scripts de Criação (Gerados automaticamente pelo ERWIN 8.2)

CREATE TABLE ACESSO (

ace_id INTEGER NOT NULL , ace_nome VARCHAR2(50) NULL , ace_longitude NUMBER(15,5) NULL , ace_latitude NUMBER(10,6) NULL , ace_altura NUMERIC(10,6) NULL );

CREATE UNIQUE INDEX XPKACESSO ON ACESSO (ace_id ASC);

ALTER TABLE ACESSO

ADD CONSTRAINT XPKACESSO PRIMARY KEY (ace_id); CREATE TABLE EMISSOR

(7)

7

(

ems_nome VARCHAR2(50) NOT NULL , ems_fone VARCHAR2(20) NOT NULL , ems_cnpj VARCHAR2(20) NOT NULL , ems_contato VARCHAR2(50) NOT NULL , ems_id INTEGER NOT NULL );

CREATE UNIQUE INDEX XPKEMISSOR ON EMISSOR (ems_id ASC);

ALTER TABLE EMISSOR

ADD CONSTRAINT XPKEMISSOR PRIMARY KEY (ems_id); CREATE TABLE MIDIA

(

mid_id INTEGER NOT NULL , mid_numero VARCHAR2(255) NULL , mid_longitude NUMBER(10,6) NULL , mid_latitude NUMBER(10,6) NULL , mid_altitude NUMBER(10,6) NULL , usu_id INTEGER NOT NULL , tpm_id INTEGER NOT NULL , ems_id INTEGER NOT NULL );

CREATE UNIQUE INDEX XPKMIDIA ON MIDIA (mid_id ASC,usu_id ASC,ems_id ASC,tpm_id ASC); ALTER TABLE MIDIA

ADD CONSTRAINT XPKMIDIA PRIMARY KEY (mid_id,usu_id,ems_id,tpm_id); CREATE TABLE TIPO_MIDIA

(

tpm_id INTEGER NOT NULL , tpm_marca VARCHAR2(50) NOT NULL , tpm_modelo VARCHAR2(50) NOT NULL , tpm_nome VARCHAR2(50) NOT NULL );

CREATE UNIQUE INDEX XPKDEVICE ON TIPO_MIDIA (tpm_id ASC);

ALTER TABLE TIPO_MIDIA

ADD CONSTRAINT XPKDEVICE PRIMARY KEY (tpm_id); CREATE TABLE USUARIO

(

usu_id INTEGER NOT NULL , usu_nome VARCHAR2(40) NULL , usu_fone VARCHAR2(20) NULL );

CREATE UNIQUE INDEX XPKUSUARIO ON USUARIO (usu_id ASC);

ALTER TABLE USUARIO

ADD CONSTRAINT XPKUSUARIO PRIMARY KEY (usu_id); CREATE TABLE VALIDACAO

(

val_id INTEGER NOT NULL , mid_id INTEGER NOT NULL , ace_id INTEGER NOT NULL , val_validade INTEGER NULL , val_habilitado CHAR(3) NULL , val_longitude NUMBER(10,6) NULL , val_latitude NUMBER(10,6) NULL , val_altura NUMBER(10,6) NULL , usu_id INTEGER NOT NULL , ems_id INTEGER NOT NULL , tpm_id INTEGER NOT NULL );

CREATE UNIQUE INDEX XPKVALIDACAO ON VALIDACAO

(8)

8

ALTER TABLE VALIDACAO

ADD CONSTRAINT XPKVALIDACAO PRIMARY KEY (val_id,mid_id,usu_id,ems_id,tpm_id,ace_id); ALTER TABLE MIDIA

ADD (CONSTRAINT FK_MIDIA__USUARIO FOREIGN KEY (usu_id) REFERENCES USUARIO (usu_id)); ALTER TABLE MIDIA

ADD (CONSTRAINT FK_MIDIA__EMISSOR FOREIGN KEY (ems_id) REFERENCES EMISSOR (ems_id)); ALTER TABLE MIDIA

ADD (CONSTRAINT FK_MIDIA__TIPO_MIDIA FOREIGN KEY (tpm_id) REFERENCES TIPO_MIDIA (tpm_id)); ALTER TABLE VALIDACAO

ADD (CONSTRAINT FK_VALIDACAO__MIDIA FOREIGN KEY (mid_id, usu_id, ems_id, tpm_id) REFERENCES MIDIA (mid_id, usu_id, ems_id, tpm_id));

ALTER TABLE VALIDACAO

ADD (CONSTRAINT FK_VALIDACAO__ACESSO FOREIGN KEY (ace_id) REFERENCES ACESSO (ace_id));

4.3. Scripts DML para popular o Banco de Dados

INSERT INTO USUARIO (USU_ID,USU_NOME, USU_FONE) VALUES (1,'ANGELINA JULIE','(11)9903-0001'); INSERT INTO USUARIO (USU_ID,USU_NOME, USU_FONE) VALUES (2,'FERNANDO PESSOA','(11)5472-0012'); INSERT INTO USUARIO (USU_ID,USU_NOME, USU_FONE) VALUES (3,'AIKE BAPTISTA','(11)9901-0002');

INSERT INTO EMISSOR (ems_id, ems_cnpj, ems_nome, ems_contato, ems_fone) values (1,'43.332.225/0001-23','BANCO BRADESCO','FERNANDO','(11)3392-0322');

INSERT INTO EMISSOR (ems_id, ems_cnpj, ems_nome, ems_contato, ems_fone) values (2,'23.003.023/0001-11','BANCO ITAU','CAMILIA','(11)2231-1121');

INSERT INTO EMISSOR (ems_id, ems_cnpj, ems_nome, ems_contato, ems_fone) values (3,'85.232.256/0001-26','BANCO BRASIL','ALESSANDRA','(11)3222-1222');

INSERT INTO TIPO_MIDIA (TPM_ID, TPM_NOME, TPM_MARCA, TPM_MODELO) VALUES (1, 'RFID', 'INTEL', 'X9') INSERT INTO TIPO_MIDIA (TPM_ID, TPM_NOME, TPM_MARCA, TPM_MODELO) VALUES (2, 'TOKEN', 'MAXUEL', '1121');

INSERT INTO TIPO_MIDIA (TPM_ID, TPM_NOME, TPM_MARCA, TPM_MODELO) VALUES (3, 'COD39TAG', 'WINMAX', 'A1');

INSERT INTO TIPO_MIDIA (TPM_ID, TPM_NOME, TPM_MARCA, TPM_MODELO) VALUES (4, 'SMART', 'WINMAX', 'A2');

INSERT INTO TIPO_MIDIA (TPM_ID, TPM_NOME, TPM_MARCA, TPM_MODELO) VALUES (5, 'PENDRIVE', 'KING', '128GB');

INSERT INTO MIDIA (MID_ID, MID_NUMERO, MID_LONGITUDE, MID_LATITUDE, MID_ALTITUDE, USU_ID, TPM_ID, EMS_ID) VALUES (1,'1121999124871391',-39.112,-34.1121,112,1,1,1);

INSERT INTO MIDIA (MID_ID, MID_NUMERO, MID_LONGITUDE, MID_LATITUDE, MID_ALTITUDE, USU_ID, TPM_ID, EMS_ID) VALUES (4,'4231231SS1231229',-45.1222,-33.1121,331,3,1,3);

INSERT INTO ACESSO (ace_id, ace_nome, ace_latitude , ace_longitude ,ace_altura ) VALUES (1,'BRADESCO.COM.BR',-44.112,-46.223,100)

INSERT INTO ACESSO (ace_id, ace_nome, ace_latitude , ace_longitude ,ace_altura ) VALUES (2,'ITAU BUILD CENTER',-42.112,-44.223,300);

INSERT INTO ACESSO (ace_id, ace_nome, ace_latitude , ace_longitude ,ace_altura ) VALUES (3,'SISTEMA ABC',-32.112,-42.223,200);

INSERT INTO VALIDACAO (val_id, ace_id , mid_id , val_habilitado , val_latitude , val_longitude , val_altura, val_validade) values (1,1,1,'SIM',-43.2,-34.22,112,2015/10/01);

(9)

9

insert into VALIDACAO (val_id, ace_id , mid_id , val_habilitado , val_latitude , val_longitude , val_altura, val_validade) values (2,2,2,'SIM',-41.2,-31.22,112,2016/01/1);

insert into VALIDACAO (val_id, ace_id , mid_id , val_habilitado , val_latitude , val_longitude , val_altura, val_validade) values (4,2,1,'SIM',-21.2,-21.32,112,2012/01/1);

4.4. Dicionário de dados

Nome

Entidade

Definição

Atributo

Definição

Mandatório

MIDIA

Representa as Mídias

emitidas para

gerenciar e controlar a

validação das

transações realizadas

mid_id

Chave Primária

Sim

tpm_id

Chave Estrangeira do TIPO de Midia

Sim

usu_id

Chave Estrangeira do EMISSOR

Sim

mid_numero

Numero da Midia

Sim

mid_longitude

Posição geográfica da ultima utilização

dessa mídia

Sim

mid_latitude

Posição geográfica da ultima utilização

dessa mídia

Sim

mid_altitude

Atura geográfica da ultima utilização

dessa midia

Sim

Nome

Entidade

Definição

Atributo

Definição

Mandatório

TIPO_MIDI

A

Representa os tipos de

mídia.

tpm_id

Chave Primária

Sim

tpm_nome

Nome do Tipo de Mídia

Sim

tpm_marca

Marca ou Fabricante

Sim

tpm_modelo

Modelo do Tipo de Mídia

Sim

Nome

Entidade

Definição

Atributo

Definição

Mandatório

USUARIO

Representa os

usuários das mídias

usu_id

Chave Primária

Sim

usu_nome

Nome do Usuário

Sim

usu_fone

Fone do Usuário

Não

Nome

Entidade

Definição

Atributo

Definição

Mandatório

EMISSOR

Representa as

empresas habilitadas a

emitir uma mídia de

validação

ems_id

Chave Primária

Sim

ems_cnpj

Numero do Cadastro nacional de pessoa

jurídica

Sim

ems_nome

Nome da Empresa

Sim

ems_contato

Nome dos contato da empresa

Sim

ems_fone

Fone do Usuário

Não

Nome

Entidade

Definição

Atributo

Definição

Mandatório

ACESSO

Representa os pontos

de acesso, os quais as

mídias poderão ser

validadas

ace_id

Chave Primária

Sim

ace_nome

Numero do Cadastro nacional de pessoa

jurídica

Sim

ace_longitude

Area geográfica do ponto de acesso.

Sim

(10)

10 ace_altura

Altura Geografica do ponto de acesso,

Não

Nome

Entidade

Definição

Atributo

Definição

Mandatório

VALIDACA

O

Representa a validação

entre as mídias e os

acessos

val_id

Chave Primária

Sim

mid_id

Chave Estrangeira da Midia

Sim

ace_id

Chave Estrangeira do Acesso

Sim

val_validade

Data de Validade dessa Validacao

Sim

val_habilitado

Define se essa validade esta ou não

liberada

Sim

val_longitude

Area geográfica da validacao

Sim

val_latitude

Area geográfica da validacao

Sim

val_altura

Altura Geografica do ponto de acesso,

Não

4.5. Diretório de dados

Objeto Relacional

Tabela Pai

Chave Pai

Tabela Filha

Chave Filha

Cardinalidade

FK_MIDIA__USUARIO

USUARIO

usu_id

MIDIA

usu_id

1:N

FK_MIDIA__EMISSOR

EMISSOR

ems_id

MIDIA

ems_id

1:N

FK_MIDIA__TIPO_MIDIA

TIPO_MIDIA

tpm_id

MIDIA

tpm_id

1:N

FK_VALIDACAO__MIDIA

MIDIA

mid_id

VALIDACAO

mid_id

1:N

FK_VALIDACAO__ACESSO

ACESSO

ace_id

VALIDACAO

ace_id

1:N

4.6.Diretório de recursos

Banco de dados Oracle 11G, Hardware com Processado I3 com 4 GB de Memoria.

(11)

11

4.8. Modelo Hierárquico

(12)

12

MIDIA MIDIAMIDIA MIDIA ACESSO ACESSOACESSO ACESSO VAL IDACAO VAL IDACAO VAL IDACAO VAL IDACAO U SUARIO U SUARIO U SUARIO U SUARIO EMISSOR EMISSOR EMISSOR EMISSOR D EVICE D EVICED EVICE D EVICE