1.Objetivos

            Esta lista de exercícios tem por objetivo integrar os Aplicativos de BD Embarcações (TDE), Cargas (TDC), Rotas (TDR) e Portos (TDP), no Banco de Dados Setorial Transporte (PT-TRA) da Empresa SIG-PT (Sistema de Informações Georreferenciadas para Processamento Transacional) escolhida como Estudo de Caso, visando a melhorar as suas deficiências setoriais e reduzir os seus desperdícios de recursos.
            Além disso, esta lista também visa implementar a integração de Aplicativos de BD nos Bancos de Dados Setoriais – BDS da Empresa SIG-PT, visando testar as funcionalidades de suas integrações setoriais debaixo do SGBD previamente escolhido (ORACLE 10g Spatial), e verificar a melhoria das suas eficiências setoriais e a redução dos desperdícios de seus recursos.
             

2. Conteúdo

           
2.1. Banco de Dados Setorial Tranporte

            O Banco de Dados Setorial Transporte (PT-TRA) é responsável por todo o monitoramento de embarcações, cargas, rotas e portos existentes na Amazônia, sendo parte integrante da Empresa SIG-PT, do Projeto MONITORAMA (Monitoramento de Águas da Amazônia).
            O grupo é formado por 4 integrantes, sendo que as funções são distribuídas da seguinte forma:

2.1.1 Criação das Tabelas e Inserção dos Valores

CREATE TABLE TIPO(
       tip_codigo     INTEGER,
       tip_nome       VARCHAR(50),
       tip_capacidade            INTEGER
);

ALTER TABLE TIPO ADD ( PRIMARY KEY (tip_codigo));

INSERT INTO TIPO VALUES (1,'Canoa', 300);

INSERT INTO TIPO VALUES (2,'Barco', 10000);

INSERT INTO TIPO VALUES (3,'Navios', 100000);

CREATE TABLE EMBARCACAO(
        emb_codigo                 INTEGER,
        emb_nome   VARCHAR(50),
        tip_codigo    INTEGER,
                pro_codigo           INTEGER
);

ALTER TABLE EMBARCACAO ADD ( PRIMARY KEY (emb_codigo));

INSERT INTO EMBARCACAO VALUES (1,'Poseidon', 2, 3);

INSERT INTO EMBARCACAO VALUES (2,'Titanic', 3, 2);

INSERT INTO EMBARCACAO VALUES (3,'Ana Luzia', 2, 2);

CREATE TABLE PROPRIETARIO(
        pro_codigo   INTEGER,
        pro_nome     VARCHAR(50),
        pro_tipo        VARCHAR(50),
                pro_telefone        VARCHAR(50)
);

ALTER TABLE PROPRIETARIO ADD ( PRIMARY KEY (pro_codigo));

INSERT INTO PROPRIETARIO VALUES (1,'Joaquim Manuel', 'fisica', '(12) 3947-0001');

INSERT INTO PROPRIETARIO VALUES (2,'Jose da Silva', 'fisica', '(12) 3947-0002');

INSERT INTO PROPRIETARIO VALUES (3,'MADEIRA SA', 'juridica', '(12) 3947-0003');

CREATE TABLE CARGA(
                 car_codigo          INTEGER,
                nat_codigo           INTEGER,
                pro_codigo           INTEGER,
                car_volume          INTEGER,
                car_peso               INTEGER,
                car_valor              INTEGER
);

ALTER TABLE CARGA ADD ( PRIMARY KEY (car_codigo));

INSERT INTO CARGA VALUES (1, 1, 1, 300, 60000, 500000);

INSERT INTO CARGA VALUES (2, 2, 1, 400, 30000, 300000);

INSERT INTO CARGA VALUES (3, 3, 3, 500, 50000, 400000);

CREATE TABLE NATUREZA(
        nat_codigo   INTEGER,
        nat_nome     VARCHAR(50),
        nat_toxidade               VARCHAR(50),
                nat_fragilidade    VARCHAR(50),
                nat_inflamabilidade          VARCHAR(50)
);

ALTER TABLE NATUREZA ADD ( PRIMARY KEY (nat_codigo));

INSERT INTO NATUREZA VALUES (1,'Madeira', 'baixa', 'baixa', 'media');

INSERT INTO NATUREZA VALUES (2,'Acido Sulfurico', 'alta', 'alta', 'baixa');

INSERT INTO NATUREZA VALUES (3,'Petroleo', 'alta', 'alta', 'alta');

CREATE TABLE TRIPULANTE(
        tri_codigo     INTEGER,
        tri_nome       VARCHAR(50),
        emb_codigo INTEGER,
                tri_cargo               VARCHAR(50),
                tri_telefone           VARCHAR(50)
);

ALTER TABLE TRIPULANTE ADD ( PRIMARY KEY (tri_codigo));

INSERT INTO TRIPULANTE VALUES (1,'Ze de Souza', 2, 'capitao', '(12) 3947-0003');

INSERT INTO TRIPULANTE VALUES (2,'Moacir Matos Junior', 3, 'marijo', '(12) 3947-0004');

INSERT INTO TRIPULANTE VALUES (3,'Joao Macedo', 3, 'capitao', '(12) 3947-0005');

CREATE TABLE EMPREGADO(
        emp_codigo                 INTEGER,
        emp_nome   VARCHAR(50),
        por_codigo   INTEGER,
                emp_cargo           VARCHAR(50),
                emp_telefone      VARCHAR(50)
);

ALTER TABLE EMPREGADO ADD ( PRIMARY KEY (emp_codigo));

INSERT INTO EMPREGADO VALUES (1,'Marcos Rosa', 2, 'Janitor', '(12) 3947-0006');

INSERT INTO EMPREGADO VALUES (2,'Moacir Matos Junior', 3, 'Operador', '(12) 3947-0007');

INSERT INTO EMPREGADO VALUES (3,'Herbert Fonseca’, 3, 'Gerente', '(12) 3947-0008');

CREATE TABLE MOVIMENTO_CARGA(
        mov_codigo                INTEGER,
                atr_codigo            INTEGER,
                car_codigo           INTEGER,
                mov_tipo              VARCHAR(50)
);

ALTER TABLE MOVIMENTO_CARGA  ADD ( PRIMARY KEY (mov_codigo));

INSERT INTO MOVIMENTO_CARGA VALUES (1,1,1,'carga');

INSERT INTO MOVIMENTO_CARGA VALUES (2,2,1,'descarga');

INSERT INTO MOVIMENTO_CARGA VALUES (3,3,2,'carga');

INSERT INTO MOVIMENTO_CARGA VALUES  (4,4,2,'descarga');

INSERT INTO MOVIMENTO_CARGA VALUES (5,3,3,'carga');

INSERT INTO MOVIMENTO_CARGA VALUES (6,4,3,'descarga');

CREATE TABLE ATRACACAO(
        atr_codigo    INTEGER,
                emb_codigo         INTEGER,
                por_codigo           INTEGER,
                atr_horario_entreada        DATE,
                atr_horario_saida               DATE
);

ALTER TABLE ATRACACAO ADD ( PRIMARY KEY (atr_codigo));

INSERT INTO ATRACACAO VALUES (1,2,1,to_date('2007/05/17:09:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'), to_date('2007/05/17:06:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'));

INSERT INTO ATRACACAO VALUES (2,2,2,to_date('2007/05/20:08:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'), to_date('2007/05/20:03:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'));

INSERT INTO ATRACACAO VALUES (3,3,2,to_date('2007/05/19:04:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'), to_date('2007/05/19:07:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'));

INSERT INTO ATRACACAO VALUES (4,3,1,to_date('2007/05/25:10:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'), to_date('2007/05/25:09:00:00PM', 'yyyy/mm/dd:hh:mi:ssam'));

CREATE TABLE EXTREMO_ROTA(
        ext_codigo   INTEGER,
        rot_codigo    INTEGER,
                atr_codigo            INTEGER
);

ALTER TABLE EXTREMO_ROTA ADD ( PRIMARY KEY (ext_codigo));

INSERT INTO EXTREMO_ROTA VALUES (1,1, 1);

INSERT INTO EXTREMO_ROTA VALUES (2,1, 2);

INSERT INTO EXTREMO_ROTA VALUES (3,2, 3);

INSERT INTO EXTREMO_ROTA VALUES (4,2, 4);

CREATE TABLE ROTA(
        rot_codigo    INTEGER,
        rot_nome      VARCHAR(50),
                rot_periculosidade              VARCHAR(50)
);

ALTER TABLE ROTA ADD ( PRIMARY KEY (rot_codigo));

INSERT INTO ROTA VALUES (1,'Rota da Madeira - ida', 'alta');

INSERT INTO ROTA VALUES (2,'Rota da Madeira - volta', 'alta');

INSERT INTO ROTA VALUES (3,'Rota do Ouro ida', 'media');

CREATE TABLE DESCRICAO_ROTA(
        des_codigo   INTEGER,
        rot_codigo    INTEGER,
                tre_codigo            INTEGER
);

ALTER TABLE DESCRICAO_ROTA ADD ( PRIMARY KEY (des_codigo));

INSERT INTO DESCRICAO_ROTA VALUES (1,1,1);

INSERT INTO DESCRICAO_ROTA VALUES (2,1,2);

INSERT INTO DESCRICAO_ROTA VALUES (3,2,1);

INSERT INTO DESCRICAO_ROTA VALUES (4,2,2);

CREATE TABLE TRECHO(
        tre_codigo    INTEGER,
        rio_codigo    INTEGER,
                tre_km_inicial     INTEGER,
                tre_km_final        INTEGER
);

ALTER TABLE TRECHO ADD ( PRIMARY KEY (tre_codigo));

INSERT INTO TRECHO VALUES (1,1,58,89);

INSERT INTO TRECHO VALUES (2,2,36,90);

INSERT INTO TRECHO VALUES (3,3,50,79);
CREATE TABLE RIO(
        rio_codigo    INTEGER,
        rio_nome      VARCHAR(50),
                rio_comprimento                INTEGER
);

ALTER TABLE RIO ADD ( PRIMARY KEY (rio_codigo));

INSERT INTO RIO VALUES (1,'Amazonas',2000);

INSERT INTO RIO VALUES (2,'Solimoes',300);

INSERT INTO RIO VALUES (3,'Jaima',500);

CREATE TABLE PORTO(
        por_codigo   INTEGER,
        por_nome     VARCHAR(50),
                por_endereco       VARCHAR(50),
                por_posicao         MDSYS.SDO_GEOMETRY NOT NULL
);

ALTER TABLE PORTO  ADD ( PRIMARY KEY (por_codigo));

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID) VALUES ('PORTO', 'por_posicao', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('LONG', -50.9, -30.8, 5.0E-8), MDSYYS.SDO_DIM_ELEMENT('LAT', -60.2, 6.3, 5.0E-8)), NULL);

CREATE INDEX ORIGEM_SDX ON PORTO (por_posicao) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('SDO_INDX_DIMS=2 LAYER_GTYPE="POINT"');

CREATE UNIQUE INDEX XPKORIGEM ON PORTO
(
por_codigo
);

INSERT INTO PORTO VALUES
(1,'Porto de Manaus','Rua Santa Maria, 30',MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(-2.0,-10.1,50.0),NULL,NULL));

INSERT INTO PORTO VALUES
(2,'Porto de Japioca','Av JK, 3000',MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(-5.0,-30.1,50.0),NULL,NULL));

INSERT INTO PORTO VALUES (3,'Porto de Baiatuba','Rodovia dos Madeireiros, 200',MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(10.0,20.1,50.0),NULL,NULL));

ALTER TABLE EMBARCACAO ADD ( FOREIGN KEY (tip_codigo) REFERENCES TIPO);

ALTER TABLE EMBARCACAO ADD ( FOREIGN KEY (pro_codigo) REFERENCES PROPRIETARIO);

ALTER TABLE CARGA  ADD ( FOREIGN KEY (pro_codigo) REFERENCES PROPRIETARIO);

ALTER TABLE CARGA ADD ( FOREIGN KEY (nat_codigo) REFERENCES NATUREZA);

ALTER TABLE TRIPULANTE ADD ( FOREIGN KEY (emb_codigo) REFERENCES EMBARCACAO);

ALTER TABLE EMPREGADO ADD ( FOREIGN KEY (por_codigo) REFERENCES PORTO);

ALTER TABLE ATRACACAO ADD ( FOREIGN KEY (emb_codigo) REFERENCES EMBARCACAO);

ALTER TABLE ATRACACAO ADD ( FOREIGN KEY (por_codigo) REFERENCES PORTO);

ALTER TABLE EXTREMO_ROTA ADD ( FOREIGN KEY (rot_codigo) REFERENCES ROTA);

ALTER TABLE EXTREMO_ROTA ADD ( FOREIGN KEY (atr_codigo) REFERENCES ATRACACAO);

ALTER TABLE MOVIMENTO_CARGA ADD ( FOREIGN KEY (atr_codigo) REFERENCES ATRACACAO);

ALTER TABLE MOVIMENTO_CARGA ADD ( FOREIGN KEY (car_codigo) REFERENCES CARGA);

ALTER TABLE DESCRICAO_ROTA ADD ( FOREIGN KEY (rot_codigo) REFERENCES ROTA);

ALTER TABLE DESCRICAO_ROTA ADD ( FOREIGN KEY (tre_codigo) REFERENCES TRECHO);

ALTER TABLE TRECHO ADD ( FOREIGN KEY (rio_codigo) REFERENCES RIO);

2.2. Integração

            A Integração do Banco de Dados Setorial Transporte encontra-se na Página de Índices do Arthur de Almeida Rodrigues.
            Endereço: http://www.geocities.com/arthur84/

2.3. Normalização

            A Integração do Banco de Dados Setorial Transporte encontra-se na Página de Índices do Jordan Guimarães Lombardi.
            Endereço: http://jordanlombardi.googlepages.com/

2.4. Dicionarização

2.4.1. Dicionário de Dados

2.4.2. Diretório de Dados

2.4.3. Dicionário de Recursos de Dados

2.4.4. Dicionário de Metadados

2.5. Suporte Técnico

            O Suporte Técnico do Banco de Dados Setorial Transporte encontra-se na Página de Índices da Juliana Claro Mourisca.
            Endereço: http://jmourisca.googlepages.com/

2.6.  Testes de Verificação

            Para verificar a integração do Banco de Dados Setorial, foram realizadas consultas.

2.6.1. 1ª Consulta

            “Listar todas as informações do Porto de Manaus”.
           
SELECT *
FROM PORTO
WHERE por_nome= 'Porto de Manaus'

Observação: Como não houve alteração na tabela Porto, o resultado da consulta possui a mesma estrutura.

2.6.2. 2ª Consulta

“Listar os horários de chegada das embarcações em cada berço.”

SELECT ATRACACAO.atr_berco, HORARIO.hor_chegada
FROM ATRACACAO INNER JOIN HORARIO
ON ATRACACAO.hor_codigo=HORARIO.hor_codigo;

Anomalia: A tabela ATRACACAO foi alterada, tendo alguns de seus atributos substituídos. Além disso, a tabela HORARIO foi unida à tabela ATRACACAO, sendo que também foram retirados atributos da mesma.

Novo enunciado: “Listar os horários de chegada das embarcações nos portos.”

SELECT atr_horario_entrada, por_codigo
FROM ATRACACAO

2.6.3. 3ª Consulta

            “Listar o código, nome e bandeira da embarcação, o código da atracação e o código e o nome do porto cuja embarcação possui como destino Manaus.”

SELECT EMBARCACAO.emb_codigo, EMBARCACAO.emb_nome, EMBARCACAO.emb_bandeira, ATRACACAO.atr_codigo, PORTO.por_codigo, PORTO.por_nome
FROM ATRACACAO INNER JOIN EMBARCACAO ON ATRACACAO.emb_codigo=EMBARCACAO.emb_codigo INNER JOIN PORTO
ON ATRACACAO.por_codigo=PORTO.por_codigo
WHERE ATRACACAO.atr_destino='MANAUS';

            Anomalia: O atributo emb_bandeira da tabela EMBARCACAO foi excluído, bem como o atributo atr_destino da tabela ATRACACAO.

            Novo enunciado: “Listar o código e o nome da embarcação, o código da atracação e o código do porto cuja embarcação possui como porto de atracação o Porto de Manaus.”

SELECT EMBARCACAO.emb_codigo, EMBARCACAO.emb_nome, ATRACACAO.atr_codigo, PORTO.por_código
FROM EMBARCACAO, PORTO, ATRACACAO
WHERE PORTO.por_nome='Porto de Manaus' AND ATRACACAO.emb_codigo=EMBARCACAO.emb_codigo AND ATRACACAO.por_codigo=PORTO.por_codigo

2.6.4. 4ª Consulta

            “Listar a posição do porto no qual trabalha o empregado Marcos Rosa.”

SELECT PORTO.por_posicao
FROM PORTO, EMPREGADO
WHERE PORTO.por_codigo=EMPREGADO.por_codigo and EMPREGADO.emp_nome='Marcos Rosa'

Observação: Não houve nenhuma alteração que implicasse em alterações nessa consulta.

2.6.5. 5ª Consulta

            “Listar as informações dos tripulantes da embarcação Ana Luiza.”

SELECT TRIPULANTE.tri_codigo, TRIPULANTE.tri_nome, TRIPULANTE.tri_cargo, TRIPULANTE.tri_telefone
FROM EMBARCACAO, TRIPULANTE
WHERE EMBARCACAO.emb_codigo=TRIPULANTE.emb_codigo  and EMBARCACAO.emb_nome='Ana Luzia'

2.6.6. 6ª Consulta

            “Listar os portos em que se encontram as embarcações cujos proprietários são pessoas físicas.”

SELECT DISTINCT (PORTO.por_nome)
FROM PORTO, ATRACACAO, EMBARCACAO, PROPRIETARIO
WHERE PROPRIETARIO.pro_tipo='fisica' AND PROPRIETARIO.pro_codigo=EMBARCACAO.pro_codigo AND EMBARCACAO.emb_codigo=ATRACACAO.emb_codigo AND PORTO.por_codigo=ATRACACAO.por_codigo

2.6.7. 7ª Consulta

            “Listar o nome e o tipo das embarcações com capacidade de 10000 ou 100000 cujas periculosidades das rotas sejam altas.”

SELECT DISTINCT(EMBARCACAO.emb_nome), TIPO.tip_nome
FROM EMBARCACAO, TIPO, ATRACACAO, EXTREMO_ROTA, ROTA
WHERE (TIPO.tip_capacidade='10000' OR TIPO.tip_capacidade='100000') AND ROTA.rot_periculosidade='alta' AND EMBARCACAO.tip_codigo=TIPO.tip_codigo AND ATRACACAO.emb_codigo=EMBARCACAO.emb_codigo AND EXTREMO_ROTA.atr_codigo=ATRACACAO.atr_codigo AND ROTA.rot_codigo=EXTREMO_ROTA.rot_codigo

2.6.8. 8ª Consulta

            “Listar os dados dos portos e suas distâncias ao ponto de latitude e longitude zero cujo código de atracação também é dois.”

SELECT PORTO.*, SDO_GEOM.SDO_DISTANCE(PORTO.por_posicao,MDSYS.SDO_GEOMETRY(2001,NULL,MDSYS.SDO_POINT_TYPE(0,0,0),NULL,NULL) ,1) AS DISTANCIA
FROM PORTO, ATRACACAO
WHERE PORTO.por_nome='Porto de Japioca' AND ATRACACAO.por_codigo=PORTO.por_codigo AND ATRACACAO.atr_codigo='2'

3. Conclusão
           
             Nesta lista de exercícios foi implementada a Terceira Forma Normal do Protótipo de Aplicativo de Banco de Dados da temática de Portos, do Setor de Transportes. Também foi realizada conversão da 3ªFN do Protótipo de Aplicativo de BD no Modelo de Dados Relacional para os Modelos Hierárquico, Rede e Orientado a Objetos.
            Através desses procedimentos, resultamos na Versão 1.0 do Sistema de Dicionário de Dados e nos Modelos de Dados Relacional, Hierárquico, Rede e Orientado a Objetos.
            Dessa forma, o Protótipo de Aplicativo de BD já é passível de ser integrado às demais temáticas do Setor de Transportes, que são as temáticas de Rotas, Embarcações e Cargas.