Implementação no Oracle de um esquema E/R no modelo relacional e objeto-relacional - parte 2

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Veja a segunda parte do artigo:Implementação no Oracle de um esquema E/R no modelo relacional e objeto-relacional

Implementação Objeto-Relacional

Nesta seção é mostrado como pode ser implementado no banco de dados Oracle, através da tecnologia objeto relacional, o exemplo da Figura 01. Em Java, a lista de características de um objeto é chamada de Classe. No banco de dados esta lista de características é um Tipo.

Em um banco de dados queremos e precisamos armazenar os objetos. Sendo assim, precisamos de um “repositório” para armazenamento de objetos. Para resolver esta necessidade, a Oracle criou um novo tipo de tabela no banco de dados. Uma Object Table é uma tabela para armazenamento das instâncias de uma determinada classe. Sendo assim, uma Object Table é uma tabela para armazenamento de objetos, e uma tabela de objetos é construída onde seus campos são instâncias de objetos.

Num modelo Relacional, os relacionamentos são restrições que participam das regras de negócio estabelecidas no banco de dados. Num modelo Objeto-Relacional, utilizamos uma Ref (referência) para estabelecer uma ligação com um objeto específico, ou com uma instância específica de um tipo de objeto.

A expressão Ref difere de um relacionamento estabelecido no modelo Relacional. Ela, por si só, não estabelece uma restrição de integridade, não há impedimento à remoção de uma instância que esteja sendo referenciada por outra. A expressão Ref corresponde a um ponteiro, objetiva acesso mais rápido e não regra de integridade.

O fato de a cláusula Ref, por si só, não garantir a integridade referencial não quer dizer que não possamos ter esta garantia. Podemos optar entre referência sem integridade ou com integridade, de acordo com a situação. A referência pode ser usada apenas como meio de acesso direto. Desta forma temos as seguintes opções:

. Coluna Ref com a cláusula References – simultaneamente endereça e restringe;
.
Coluna Ref sem a cláusula References – apenas endereça;
. Coluna não Ref com a cláusula References – apenas restringe.

Toda vez que incluímos uma restrição temos a opção de determinar se a verificação será feita de imediato ou somente no momento do Commit e se esta situação pode ser alterada. Na cláusula References temos como opção o uso da expressão ON DELETE CASCADE, que indica que se removermos o “pai” da relação os “filhos” também devem ser removidos, ou da expressão ON DELETE SET NULL, que indica que se removermos o “pai” da relação os “filhos” não devem ser removidos, porém, o relacionamento deve ser anulado. Quando não usamos a expressão ON DELETE, indicamos que não desejamos que seja removido nenhum “pai” com “filhos”.

Em um banco de dados Objeto-Relacional, podemos criar uma tabela com uma coluna cujo tipo seja outra tabela. Isto é, tabelas podem ser embutidas em outras tabelas como valores em uma coluna.

O Oracle Server armazena os dados das tabelas aninhadas fora das linhas da tabela-pai, usando uma propriedade (store table) que ficará associada com a coluna da tabela aninhada. A linha-pai, na verdade, contém um identificador único associado com a instância correspondente da tabela aninhada. Não é possível acrescentar novas colunas em tabelas de objetos. Uma tabela aninhada não pode conter outra tabela aninhada.

O tipo de dado de uma tabela aninhada pode ser: um escalar (varchar2, number, date, raw, char, blob, clob, etc.), uma referência a um objeto (Ref), um tipo objeto. Quando seu tipo é escalar, a tabela aninhada descreve uma tabela com uma única coluna, de tipo escalar, chamada “column_value” (nome padrão pré-definido). Quando o tipo de dado é um objeto, a tabela aninhada descreve uma tabela cujas colunas correspondem a atributos do tipo objeto.

Tomando como exemplo a relação TOMADORES, na implementação relacional para o atributo multivalorado Fones é criada uma nova tabela. Na implementação objeto-relacional é criado um tipo TELEFONES_T como uma tabela do tipo char(10), onde o campo alfanumérico tem dez posições. Para não criar uma nova tabela, repetindo os códigos, é criada uma tabela aninhada Fones do tipo TELEFONES_T ao objeto TOMADORES_T. A tabela de objetos TOMADORES do tipo TOMADORES_T, possuirá a tabela aninhada Fones e os telefones de cada tomador são armazenados fisicamente em TELEFONES_OR. O “STORE AS” indica o local físico da tabela aninhada.

As tabelas aninhadas são tabelas onde a primeira forma normal, no processo de normalização, não é respeitada. Ou seja, os domínios dos atributos de uma tabela podem ser atômicos ou ser outra tabela. Dessa forma, tabelas podem ser armazenadas dentro de tabelas. Através de tabelas aninhadas, um objeto complexo pode ser representado por uma única tupla. 

O tipo TOMADORES_T é criado e o CodTomador faz referência, através do tipo Ref, a uma instância de objeto. Tabelas criadas sem o Ref implicam em redundância. Se um tomador solicitasse 5 (cinco) adiantamentos implicaria na repetição de seus dados 5 (cinco) vezes. Tabelas de objetos criadas através do tipo referência permitem que um atributo seja referência a objeto do tipo especificado.

Para criar a tabela representando o relacionamento N:M, a primeira alternativa é criar a tabela REPRESENTAM_OR como no modelo relacional, conforme demonstrado abaixo.

CREATE OR REPLACE TYPE tomadores_t AS OBJECT
(CodTomador          NUMBER   (3),
 NomeTomador         VARCHAR2 (80),
 Fones               telefones_t,
 Situacao            CHAR     (1));

CREATE TABLE tomadores_or OF tomadores_t
(CodTomador          NOT NULL,
 NomeTomador         NOT NULL,
 Situacao            NOT NULL)
NESTED TABLE Fones STORE AS telefones_or
TABLESPACE cpcadata;

CREATE OR REPLACE TYPE localidades_t AS OBJECT
(CodLocalidade       NUMBER   (3),
 NomeLocalidade      VARCHAR2 (80),
 Situacao            CHAR     (1));

CREATE TABLE localidades_or OF localidades_t
(CodLocalidade       NOT NULL,
 NomeLocalidade      NOT NULL,
 Situacao            NOT NULL)
TABLESPACE cpcadata;

CREATE TABLE representam_or
(CodTomador          NUMBER (3) NOT NULL,
 CodLocalidade       NUMBER (3) NOT NULL,
 Situacao            CHAR   (1) NOT NULL)
TABLESPACE cpcadata;

ALTER TABLE representam_or
ADD CONSTRAINT representam_or_pk PRIMARY KEY (CodTomador, CodLocalidade)
USING INDEX TABLESPACE cpcaindx;

ALTER TABLE representam_or
ADD CONSTRAINT representam_or_fk1 FOREIGN KEY (CodTomador)
REFERENCES tomadores_or (CodTomador);

ALTER TABLE representam_or

ADD CONSTRAINT representam_or_fk2 FOREIGN KEY (CodLocalidade)
REFERENCES localidades_or (CodLocalidade);

Outra alternativa é criar um tipo REPRESENTAM_T usando o tipo Ref, onde fará referência às instâncias de objeto TOMADORES_T e LOCALIDADES_T.

CREATE OR REPLACE TYPE representam_t AS OBJECT
(CodTomador          NUMBER   (3),
 CodLocalidade       NUMBER   (3),
 Situacao            CHAR     (1),
 Representam_or_fk1 REF tomadores_t,
 Representam_or_fk2 REF localidades_t);

CREATE TABLE representam_or OF representam_t
(CodTomador          NOT NULL,
 CodLocalidade       NOT NULL,
 Situacao            NOT NULL,
 Representam_or_fk1 WITH ROWID REFERENCES tomadores_or,
 Representam_or_fk2 WITH ROWID REFERENCES localidades_or)
TABLESPACE cpcadata;

A seguir é apresentada a implementação da criação dos tipos.

CREATE OR REPLACE TYPE telefones_t AS TABLE OF CHAR(10);

CREATE OR REPLACE TYPE tomadores_t AS OBJECT
(CodTomador          NUMBER   (3),
 NomeTomador         VARCHAR2 (80),
 Fones               telefones_t,

 Situacao            CHAR     (1));

CREATE OR REPLACE TYPE localidades_t AS OBJECT
(CodLocalidade       NUMBER   (3),
 NomeLocalidade      VARCHAR2 (80),
 Situacao            CHAR     (1));

CREATE OR REPLACE TYPE representam_t AS OBJECT
(CodTomador          NUMBER   (3),
 CodLocalidade       NUMBER   (3),
 Situacao            CHAR     (1),
 Representam_or_fk1 REF tomadores_t,
 Representam_or_fk2 REF localidades_t);
CREATE OR REPLACE TYPE adiantamentos_t AS OBJECT
(NumAdiantamento     NUMBER   (5),
 CodTomador          NUMBER   (3),
 CodLocalidade       NUMBER   (3),
 Data                DATE,
 Adiantamentos_or_fk1 REF tomadores_t,
 Adiantamentos_or_fk2 REF localidades_t);

CREATE OR REPLACE TYPE itens_t AS OBJECT
(NumAdiantamento     NUMBER (5),
 NumItem             NUMBER (5),
 PrazoAplicacao      NUMBER (3),
 DataAplicacao       DATE,
 PrazoPrestacaoConta NUMBER (3),
 DataPrevPrestConta  DATE,
 Valor               NUMBER (10,2),
 NumPrestacaoConta   NUMBER (5),
 DataPrestacaoConta  DATE,
 Itens_or_fk1 REF adiantamentos_t);

A seguir é apresentada a implementação da criação das tabelas.

CREATE TABLE tomadores_or OF tomadores_t
(CodTomador          NOT NULL,
 NomeTomador         NOT NULL,
 Situacao            NOT NULL)
NESTED TABLE Fones STORE AS telefones_or
TABLESPACE cpcadata;

CREATE TABLE localidades_or OF localidades_t
(CodLocalidade       NOT NULL,
 NomeLocalidade      NOT NULL,
 Situacao            NOT NULL)
TABLESPACE cpcadata;

CREATE TABLE representam_or OF representam_t
(CodTomador          NOT NULL,
 CodLocalidade       NOT NULL,
 Situacao            NOT NULL,
 Representam_or_fk1 WITH ROWID REFERENCES tomadores_or,
 Representam_or_fk2 WITH ROWID REFERENCES localidades_or)
TABLESPACE cpcadata;
CREATE TABLE adiantamentos_or OF adiantamentos_t
(NumAdiantamento     NOT NULL,
 CodTomador          NOT NULL,
 CodLocalidade       NOT NULL,
 Data                NOT NULL,
 Adiantamentos_or_fk1 WITH ROWID REFERENCES tomadores_or,
 Adiantamentos_or_fk2 WITH ROWID REFERENCES localidades_or)
TABLESPACE cpcadata;

CREATE TABLE itens_or OF itens_t
(NumAdiantamento     NOT NULL,
 NumItem             NOT NULL,
 PrazoAplicacao      NOT NULL,
 DataAplicacao       NOT NULL,
 PrazoPrestacaoConta NOT NULL,
 DataPrevPrestConta  NOT NULL,
 Valor               NOT NULL,
 NumPrestacaoConta   NULL,
 DataPrestacaoConta  NULL,
 Itens_or_fk1 WITH ROWID REFERENCES adiantamentos_or)
TABLESPACE cpcadata;

A seguir é apresentada a implementação da inserção das chaves primárias.

ALTER TABLE tomadores_or
ADD CONSTRAINT tomadores_or_pk PRIMARY KEY (CodTomador)
USING INDEX TABLESPACE cpcaindx;

ALTER TABLE localidades_or
ADD CONSTRAINT localidades_or_pk PRIMARY KEY (CodLocalidade)
USING INDEX TABLESPACE cpcaindx;

ALTER TABLE representam_or
ADD CONSTRAINT representam_or_pk PRIMARY KEY (CodTomador, CodLocalidade)
USING INDEX TABLESPACE cpcaindx;

ALTER TABLE adiantamentos_or
ADD CONSTRAINT adiantamentos_or_pk PRIMARY KEY (NumAdiantamento)
USING INDEX TABLESPACE cpcaindx;

ALTER TABLE itens_or
ADD CONSTRAINT itens_or_pk PRIMARY Key (NumAdiantamento, NumItem)
USING INDEX TABLESPACE cpcaindx;

A seguir é apresentada a implementação da inserção dos checks

ALTER TABLE tomadores_or
ADD CONSTRAINT tomadores_or_ck1 CHECK (Situacao IN ('A','I'));

ALTER TABLE localidades_or
ADD CONSTRAINT localidades_or_ck1 CHECK (Situacao IN ('A','I'));

ALTER TABLE representam_or
ADD CONSTRAINT representam_or_ck1 CHECK (Situacao IN ('A','I'));

A seguir é apresentada a implementação da criação das sequences.

CREATE SEQUENCE tomadores_or_seq1
MINVALUE 1
MAXVALUE 999
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE localidades_or_seq1
MINVALUE 1
MAXVALUE 999
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE adiantamentos_or_seq1
MINVALUE 1
MAXVALUE 999
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE itens_or_seq1
MINVALUE 1
MAXVALUE 999
START WITH 1
INCREMENT BY 1;

CREATE SEQUENCE itens_or_seq2
MINVALUE 1
MAXVALUE 999
START WITH 1
INCREMENT BY 1;

A seguir é apresentada a implementação da criação dos índices.

CREATE INDEX tomadores_or_idx1
ON tomadores_or (NomeTomador)
TABLESPACE cpcaindx;
CREATE INDEX localidades_or_idx1
ON localidades_or (NomeLocalidade)
TABLESPACE cpcaindx;

CREATE INDEX itens_or_idx1
ON itens_or (NumPrestacaoConta)
TABLESPACE cpcaindx;

CREATE INDEX telefones_or_idx1
ON telefones_or (COLUMN_VALUE)
TABLESPACE cpcaindx;

A seguir é apresentada a implementação da criação da trigger.

CREATE OR REPLACE TRIGGER Verifica_Representatividade_or
BEFORE INSERT ON adiantamentos_or
FOR EACH ROW
DECLARE
  VT_CodTomador NUMBER(3) DEFAULT 0;
BEGIN
  SELECT CodTomador INTO VT_CodTomador
  FROM   representam_or
  WHERE  CodTomador    = :NEW.CodTomador
  AND    CodLocalidade = :NEW.CodLocalidade;
  EXCEPTION
  WHEN OTHERS THEN
    IF SQL%NOTFOUND THEN
       Raise_Application_Error( -20001, 'Não há representatividade entre tomador e localidade');
    END IF;
END;

A seguir é apresentada a implementação da criação das funções.

CREATE OR REPLACE FUNCTION Calcula_Data_Aplicacao_or
(VT_NumAdiantamento NUMBER, VT_Prazo_Aplicacao NUMBER)
RETURN DATE
IS
  RT_Data DATE;
BEGIN
  SELECT Data INTO RT_Data
  FROM   adiantamentos_or
  WHERE  NumAdiantamento = VT_NumAdiantamento;
  RETURN (RT_Data+VT_Prazo_Aplicacao);
END;
CREATE OR REPLACE FUNCTION Calcula_Data_PrestConta_or
(VT_NumAdiantamento NUMBER, VT_Prazo_Aplicacao NUMBER, VT_Prazo_PrestConta NUMBER)
RETURN DATE
IS
  RT_Data DATE;
BEGIN
  SELECT Data INTO RT_Data
  FROM   adiantamentos_or
  WHERE  NumAdiantamento = VT_NumAdiantamento;
  RETURN (RT_Data+VT_Prazo_Aplicacao+VT_Prazo_PrestConta);
END;

A seguir é apresentada a implementação da criação da view.

CREATE OR REPLACE VIEW View_Adiant_Atraso_or
AS
SELECT a.NumAdiantamento,     a.CodTomador,
       a.CodLocalidade,       i.NumItem,
       i.PrazoAplicacao,      i.DataAplicacao,
       i.PrazoPrestacaoConta, i.DataPrevPrestConta,
       i.NumPrestacaoConta,   i.DataPrestacaoConta,
       ROUND(i.DataPrestacaoConta - i.DataPrevPrestConta) "Dias Atraso"
FROM   adiantamentos_or a, itens_or i
WHERE  i.itens_or_fk1       = REF(a)
AND    i.NumPrestacaoConta  IS NOT NULL
AND    i.DataPrestacaoConta > i.DataPrevPrestConta;

A seguir é apresentada a implementação das inserções nas tabelas.

INSERT INTO tomadores_or
VALUES (tomadores_t(tomadores_or_seq1.NEXTVAL,'Afonso
        Lima',telefones_t('9211111111','9222222222'),'A'));
COMMIT;

INSERT INTO TABLE
(SELECT Fones
 FROM   tomadores_or
 WHERE  CodTomador = 001)
VALUES ('9233333333');
COMMIT;

INSERT INTO localidades_or
VALUES (localidades_t(localidades_or_seq1.NEXTVAL,'GERH','A'));
COMMIT;
INSERT INTO representam_or
SELECT representam_t(tomadores_or_seq1.CURRVAL,
       localidades_or_seq1.CURRVAL,'A',REF(t),REF(l))
FROM   tomadores_or t, localidades_or l
WHERE  CodTomador    = 001
AND    Codlocalidade = 001;
COMMIT;

INSERT INTO adiantamentos_or
SELECT adiantamentos_t(adiantamentos_or_seq1.NEXTVAL,
       tomadores_or_seq1.CURRVAL,
       localidades_or_seq1.CURRVAL,
       TO_DATE(TO_CHAR(sysdate,'yyyymmdd'),'yyyymmdd'),
       REF(t),REF(l))
FROM   tomadores_or t, localidades_or l
WHERE  CodTomador    = 001
AND    Codlocalidade = 001;
COMMIT;

INSERT INTO itens_or
SELECT itens_t(adiantamentos_or_seq1.CURRVAL,
       itens_or_seq1.NEXTVAL,30,
Calcula_Data_Aplicacao_or(adiantamentos_or_seq1.CURRVAL,30),30,       Calcula_Data_PrestConta_or(adiantamentos_or_seq1.CURRVAL,30,30),100000,NULL,NULL,REF(a))
FROM   adiantamentos_or a
WHERE  NumAdiantamento = 00001;
COMMIT;

----- Inserção da Prestação de Conta -----
UPDATE itens_or
SET    NumPrestacaoConta  = itens_or_seq2.NEXTVAL,
       DataPrestacaoConta = TO_DATE('20071231','yyyymmdd')
WHERE  NumAdiantamento = 00001
AND    NumiTEM         = 00001;
COMMIT;

----- Testeda Trigger -----
INSERT INTO adiantamentos_or
SELECT adiantamentos_t(00002,002,002,
       TO_DATE(TO_CHAR(sysdate,'yyyymmdd'),'yyyymmdd'),
       REF(t),REF(l))
FROM   tomadores_or t, localidades_or l
WHERE  CodTomador    = 001
AND    Codlocalidade = 001;
ROLLBACK;

A seguir é apresentada a implementação de consultas às tabelas.

SELECT CodTomador, NomeTomador, Fones, Situacao
FROM tomadores_or;

SELECT COLUMN_VALUE Fones
FROM THE (SELECT Fones
          FROM   tomadores_or
         WHERE  CodTomador = 001);

SELECT CodLocalidade, NomeLocalidade, Situacao
FROM localidades_or;

SELECT t.NomeTomador, l.NomeLocalidade
FROM   representam_or r, tomadores_or t, localidades_or l
WHERE  r.representam_or_fk1 = REF(t)
AND    r.representam_or_fk2 = REF(l);

SELECT a.NumAdiantamento, t.NomeTomador,
       l.NomeLocalidade,  a.Data
FROM   adiantamentos_or a, tomadores_or t, localidades_or l
WHERE  a.adiantamentos_or_fk1 = REF(t)
AND    a.adiantamentos_or_fk2 = REF(l);

SELECT a.NumAdiantamento,     i.NumItem,
       i.PrazoAplicacao,      i.DataAplicacao,
       i.PrazoPrestacaoConta, i.DataPrevPrestConta,
       i.Valor,               i.NumPrestacaoConta,
       i.DataPrestacaoConta
FROM   itens_or i, adiantamentos_or a
WHERE  i.itens_or_fk1 = REF(a);

SELECT *
FROM View_Adiant_Atraso_or;

Conclusão

Neste artigo, foi apresentada a implementação de um exemplo no banco de dados Oracle, a partir do seu projeto, na forma relacional e objeto-relacional. De tudo que foi utilizado neste exemplo para o modelo relacional, não foi possível inserir valores default para o modelo objeto-relacional, porque o banco de dados Oracle não permite a modificação de tabelas de objetos. Para a realização deste artigo, utilizei o SmartDraw, o ERwin, o SQL Navigator e o banco de dados Oracle 9i.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?