INSTITUTO TECNOLÓGICO D
INSTITUTO TECNOLÓGICO D
INSTITUTO TECNOLÓGICO D
INSTITUTO TECNOLÓGICO DE
E
E
E AERONÁ
AERONÁ
AERONÁU
AERONÁ
U
U
UTICA
TICA
TICA
TICA
CE-240 PROJETO DE SISTEMAS DE BANCO DE DADOS
LISTA DE EXÉRCICIO 04
Revisão 02Valdir Guerra
Aluno
Professor Dr. Adílson Marques
Cunha
Fundação Getulio Vargas
2
1.
INTRODUÇÃO1.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.
CONTEUDO2.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
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 RFID ?
SELECT U.usu_nome
FROM GMV_USUARIO U , GMV_MIDIA M, GMV_TIPO_MIDIA T
WHERE U.usu_id = M.usu_id
AND M.tip_id = T.tip_id AND T.tip_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 GMV_MIDIA M, GMV_EMISSOR E
WHERE M.emi_id = E.emi_id
AND E.emi_nome = 'BANCO BRADESCO'
4
SELECT DISTINCT U.usu_id, U.usu_nome
FROM GMV_USUARIO U, GMV_MIDIA M, GMV_EMISSOR E
WHERE M.emi_id = E.emi_id
AND U.usu_id = M.usu_id
AND E.emi_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_altitude
FROM GMV_ACESSO A, GMV_VALIDACAO V, GMV_MIDIA M, GMV_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
2.4.2. Teste de Exclusão
5
2.5.Dicionário de DadosO 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
O diretório de recursos esta no anexo (Anexo 4.7)
2.9.Conversão dos modelos
O diretório de recursos esta no anexo (Anexo 4.8)
3.
PRINCIPAIS CONCLUSÕESCom 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
4.
ANEXOS4.1.Modelo de Entidade Relacional
4.2.Scripts de Criação (Gerados automaticamente pelo ERWIN 8.2)
CREATE TABLE GMV_ACESSO (
ace_id INTEGER NOT NULL , ace_nome VARCHAR2(50) NOT NULL , ace_longitude NUMBER(10,6) NULL , ace_latitude NUMBER(10,6) NULL , ace_altitude NUMBER(10,6) NULL );
ALTER TABLE GMV_ACESSO
ADD CONSTRAINT XPKACESSO PRIMARY KEY (ace_id); CREATE TABLE GMV_EMISSOR
7
(emi_nome VARCHAR2(50) NOT NULL , emi_fone VARCHAR2(20) NOT NULL , emi_cnpj VARCHAR2(20) NOT NULL , emi_contato VARCHAR2(50) NOT NULL , emi_id INTEGER NOT NULL , emi_longitude NUMBER(10,6) NULL , emi_latitude NUMBER(10,6) NULL , emi_altitude NUMBER(10,6) NULL );
ALTER TABLE GMV_EMISSOR
ADD CONSTRAINT XPKEMISSOR PRIMARY KEY (emi_id); CREATE TABLE GMV_MIDIA
(
mid_id INTEGER NOT NULL ,
mid_numero VARCHAR2(255) NOT NULL , mid_longitude NUMBER(10,6) NULL , mid_latitude NUMBER(10,6) NULL , mid_altitude NUMBER(10,6) NULL , usu_id INTEGER NOT NULL , tip_id INTEGER NOT NULL , emi_id INTEGER NOT NULL );
ALTER TABLE GMV_MIDIA
ADD CONSTRAINT XPKMIDIA PRIMARY KEY (mid_id); CREATE TABLE GMV_TIPO_MIDIA
(
tip_id INTEGER NOT NULL , tip_marca VARCHAR2(50) NOT NULL , tip_modelo VARCHAR2(50) NOT NULL , tip_nome VARCHAR2(50) NOT NULL );
ALTER TABLE GMV_TIPO_MIDIA
ADD CONSTRAINT XPKDEVICE PRIMARY KEY (tip_id); CREATE TABLE GMV_USUARIO
(
usu_id INTEGER NOT NULL , usu_nome VARCHAR2(40) NOT NULL , usu_fone VARCHAR2(20) NOT NULL );
ALTER TABLE GMV_USUARIO
ADD CONSTRAINT XPKUSUARIO PRIMARY KEY (usu_id); CREATE TABLE GMV_VALIDACAO
(
val_id INTEGER NOT NULL , mid_id INTEGER NOT NULL , ace_id INTEGER NOT NULL , val_validade INTEGER NOT NULL , val_habilitado CHAR(3) NOT NULL , val_longitude NUMBER(10,6) NULL , val_latitude NUMBER(10,6) NULL , val_altitude NUMBER(10,6) NULL );
ALTER TABLE GMV_VALIDACAO
ADD CONSTRAINT XPKVALIDACAO PRIMARY KEY (val_id,mid_id,ace_id); ALTER TABLE GMV_MIDIA
ADD (CONSTRAINT FK_MIDIA__USUARIO FOREIGN KEY (usu_id) REFERENCES GMV_USUARIO (usu_id)); ALTER TABLE GMV_MIDIA
ADD (CONSTRAINT FK_MIDIA__EMISSOR FOREIGN KEY (emi_id) REFERENCES GMV_EMISSOR (emi_id)); ALTER TABLE GMV_MIDIA
ADD (CONSTRAINT FK_MIDIA__TIPO_MIDIA FOREIGN KEY (tip_id) REFERENCES GMV_TIPO_MIDIA (tip_id));
8
ADD (CONSTRAINT FK_VALIDACAO__MIDIA FOREIGN KEY (mid_id) REFERENCES GMV_MIDIA (mid_id)); ALTER TABLE GMV_VALIDACAOADD (CONSTRAINT FK_VALIDACAO__ACESSO FOREIGN KEY (ace_id) REFERENCES GMV_ACESSO (ace_id));
4.3. Scripts DML para popular o Banco de Dados
INSERT INTO GMV_USUARIO (usu_id,usu_nome, usu_fone) VALUES (1,'ANGELINA JULIE','(11)9903-0001'); INSERT INTO GMV_USUARIO (usu_id,usu_nome, usu_fone) VALUES (2,'FERNANDO PESSOA','(11)5472-0012'); INSERT INTO GMV_USUARIO (usu_id,usu_nome, usu_fone) VALUES (3,'AIKE BAPTISTA','(11)9901-0002');
INSERT INTO GMV_EMISSOR (emi_id, emi_cnpj, emi_nome, emi_contato, emi_fone, emi_longitude, emi_latitude, emi_altitude) values (1,'43.332.225/0001-23','BANCO BRADESCO','FERNANDO','(11)3392-0322',-39.112,-34.1121,112); INSERT INTO GMV_EMISSOR (emi_id, emi_cnpj, emi_nome, emi_contato, emi_fone, emi_longitude, emi_latitude, emi_altitude) values (2,'23.003.023/0001-11','BANCO ITAU','CAMILIA','(11)2231-1121',-39.112,-34.1121,112); INSERT INTO GMV_EMISSOR (emi_id, emi_cnpj, emi_nome, emi_contato, emi_fone, emi_longitude, emi_latitude, emi_altitude) values (3,'85.232.256/0001-26','BANCO BRASIL','ALESSANDRA','(11)3222-1222',-39.112,-34.1121,112); INSERT INTO GMV_TIPO_MIDIA (tip_id, tip_nome, tip_marca, tip_modelo) VALUES (1, 'RFID', 'INTEL', 'X9'); INSERT INTO GMV_TIPO_MIDIA (tip_id, tip_nome, tip_marca, tip_modelo) VALUES (2, 'TOKEN', 'MAXUEL', '1121'); INSERT INTO GMV_TIPO_MIDIA (tip_id, tip_nome, tip_marca, tip_modelo) VALUES (3, 'COD39TAG', 'WINMAX', 'A1'); INSERT INTO GMV_TIPO_MIDIA (tip_id, tip_nome, tip_marca, tip_modelo) VALUES (4, 'SMART', 'WINMAX', 'A2'); INSERT INTO GMV_TIPO_MIDIA (tip_id, tip_nome, tip_marca, tip_modelo) VALUES (5, 'PENDRIVE', 'KING', '128GB');
INSERT INTO GMV_MIDIA (mid_id, mid_numero, mid_longitude, mid_latitude, mid_altitude, usu_id, tip_id, emi_id) VALUES (1,'1121999124871391',-39.112,-34.1121,112,1,1,1);
INSERT INTO GMV_MIDIA (mid_id, mid_numero, mid_longitude, mid_latitude, mid_altitude, usu_id, tip_id, emi_id) VALUES (3,'1230987840292349',-29.112,-14.1121,112,2,2,2);
INSERT INTO GMV_MIDIA (mid_id, mid_numero, mid_longitude, mid_latitude, mid_altitude, usu_id, tip_id, emi_id) VALUES (4,'4231231SS1231229',-45.1222,-33.1121,331,3,1,3);
INSERT INTO GMV_MIDIA (mid_id, mid_numero, mid_longitude, mid_latitude, mid_altitude, usu_id, tip_id, emi_id) VALUES (5,'4123123112331236',-25.1222,-22.1121,331,1,4,1);
INSERT INTO GMV_MIDIA (mid_id, mid_numero, mid_longitude, mid_latitude, mid_altitude, usu_id, tip_id, emi_id) VALUES (2,'1109812312312291',-29.112,-23.1121,112,2,3,2);
INSERT INTO GMV_ACESSO (ace_id, ace_nome, ace_latitude , ace_longitude ,ace_altitude ) VALUES (1,'BRADESCO.COM.BR',-44.112,-46.223,100);
INSERT INTO GMV_ACESSO (ace_id, ace_nome, ace_latitude , ace_longitude ,ace_altitude ) VALUES (2,'ITAU BUILD CENTER',-42.112,-44.223,300);
INSERT INTO GMV_ACESSO (ace_id, ace_nome, ace_latitude , ace_longitude ,ace_altitude ) VALUES (3,'SISTEMA ABC',-32.112,-42.223,200);
INSERT INTO GMV_VALIDACAO (val_id, ace_id , mid_id , val_habilitado , val_latitude , val_longitude , val_altitude, val_validade) values (1,1,1,'SIM',-43.2,-34.22,112,2015/10/01);
insert into GMV_VALIDACAO (val_id, ace_id , mid_id , val_habilitado , val_latitude , val_longitude , val_altitude, val_validade) values (2,2,2,'SIM',-41.2,-31.22,112,2016/01/1);
insert into GMV_VALIDACAO (val_id, ace_id , mid_id , val_habilitado , val_latitude , val_longitude , val_altitude, 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
tip_id
Chave Estrangeira do TIPO de Midia
Sim
usu_id
Chave Estrangeira do EMISSOR
Sim
mid_numero
Numero da Midia
Sim
9
dessa mídia
mid_latitude
Posição geográfica da ultima utilização
dessa mídia
Sim
mid_altitude
Altura geográfica da ultima utilização
dessa mídia
Sim
Nome
Entidade
Definição
Atributo
Definição
Mandatório
TIPO_MIDIA
Representa os tipos
de mídia.
tip_id
Chave Primária
Sim
tip_nome
Nome do Tipo de Mídia
Sim
tip_marca
Marca ou Fabricante
Sim
tip_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
Sim
Nome
Entidade
Definição
Atributo
Definição
Mandatório
EMISSOR
Representa as
empresas habilitadas a
emitir uma mídia de
validação
emi_id
Chave Primária
Sim
emi_cnpj
Numero do Cadastro nacional de pessoa
jurídica
Sim
emi_nome
Nome da Empresa
Sim
emi_contato
Nome dos contato da empresa
Sim
emi_fone
Fone do Emissor
Sim
emi_longitude
Longitude do Emissor
Não
emi_latitude
Latitude do Emissor
Não
emi_altitude
Altitude do Emissor
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
Área geográfica do ponto de acesso.
Não
ace_latitude
Área geográfica do ponto de acesso,
Não
ace_altitude
Altitude Geográfica 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
Não
val_latitude
Area geográfica da validacao
Não
10
4.5. Diretório de dadosObjeto 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
emi_id
MIDIA
emi_id
1:N
FK_MIDIA__TIPO_MIDIA
TIPO_MIDIA
tip_id
MIDIA
tip_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.