Implementação no Oracle de um esquema E/R no modelo relacional e objeto-relacional - parte 2
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.
