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

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

Recriamos, no computador, cópias bidimensionais do mundo em que vivemos. Começamos com arquivos, registros, relacionamentos e agora objetos. Os objetos definidos pelo usuário podem, então, aparecer e serem usados como qualquer outro tipo de dados.

Nossa intenção é caracterizar “objeto” de tal forma que possamos representá-lo em nosso mundo bidimensional. Formalmente diremos que um objeto é único e possui atributos. Além disso, queremos que nosso objeto (no computador) também realize ações intrínsecas a ele.

Quando dizemos que um objeto possui atributos, dizemos que ele armazena informações que o caracterizam; portanto, uma parte deste objeto é composta de dados. Quando dizemos que um objeto pode realizar ações, dizemos que ele realiza procedimentos, e, portanto, uma parte deste objeto é composta de rotinas (procedimentos, funções, etc.) que realizam ações. Um objeto é “algo” que, de alguma forma, junta dados (atributos) e códigos (métodos) em um único elemento.

O objetivo deste artigo é mostrar um Esquema Entidade/Relacionamento simples, a implementação no Oracle deste esquema no modelo relacional e objeto-relacional. Apesar de usar neste artigo uma pequena aplicação, foi suficiente para me permitir apresentar a partir do esquema E/R, a implementação do modelo relacional com a apresentação dos comandos SQL para a criação de tabelas, criação de restrições de integridade, criação de trigger, criação de função, criação de visão e criação de consultas, além da implementação do modelo objeto-relacional com a apresentação dos comandos SQL para a criação dos diversos objetos presentes na aplicação descrita abaixo.

Descrição da Aplicação

O Sistema de Controle de Prestação de Contas e Adiantamentos (CPCA) tem como objetivo controlar os pedidos de adiantamentos e suas respectivas prestações de contas. Além destes controles, o sistema também controla os cadastros de tomadores e de localidades as quais os pedidos de adiantamento atende.

O pedido de adiantamento é utilizado para a compra de produtos de pequenos vultos, tais como material de expediente, para a manutenção e viabilização dos departamentos e setores, cadastrado no sistema como localidades. Cada uma dessas localidades é cadastrada no CPCA recebendo um código como identificador (seqüencial). Além dessa informação, é cadastrado o nome da localidade e sua situação (ativo ou inativo) para que seja mantido o histórico quando um departamento ou setor for extinto.

De forma semelhante às localidades, os tomadores (pessoas que solicitam os pedidos de adiantamentos) também são cadastrados no CPCA. O pedido de adiantamento é solicitado por um tomador para uma localidade, desde que este tomador seja o representante desta localidade. Um tomador pode solicitar pedidos de adiantamentos para várias localidades. Uma localidade pode ter solicitações de pedidos de adiantamento de vários tomadores.

Cada tomador é cadastrado no CPCA recebendo um código como identificador (seqüencial).  Além dessa informação, é cadastrado o nome do tomador e sua situação (ativo ou inativo) para que seja mantido o histórico quando este deixar de ser tomador e não puder mais solicitar pedidos de adiantamentos.
Os pedidos de adiantamentos e as prestações de contas serão identificados por um seqüencial.

Os pedidos de adiantamento serão compostos por vários itens que poderão ser incluídos a qualquer momento. O que identificará cada item do pedido de adiantamento é o número do item.

Cada item de pedido de adiantamento terá um prazo para aplicação que variará entre 30, 60 e 90 dias, e outro para prestação de conta que variará entre 30, 60 e 90 dias a partir da data da aplicação, sendo as respectivas datas calculadas pelo sistema a partir da data corrente e dos respectivos prazos informados ao sistema.

Uma prestação de conta referir-se-á a um determinado item de um pedido de adiantamento.

Esquema E/R - Entidade/Relacionamento

A técnica de modelagem de dados mais difundida é a abordagem entidade/relacionamento (E/R). Os conceitos básicos da abordagem E/R são: entidade, relacionamento e atributo. Para explicar os conceitos da Modelagem E/R vamos usar a aplicação descrita acima. Esta aplicação está representada graficamente na Figura 01.

Figura 01: Esquema E/R

Uma entidade corresponde ao conjunto de objetos da realidade modelada sobre os quais se deseja manter informações no banco de dados. Uma entidade é representada através de um retângulo que contém o nome da entidade. Na Figura 01 são apresentadas as entidades: TOMADORES, LOCALIDADES, ADIANTAMENTOS e ITENS.

O retângulo TOMADORES representa o conjunto de todos os tomadores sobre os quais se deseja manter informações no banco de dados. Caso se deseja referir a um objeto particular (um determinado tomador) fala-se em uma entidade e não um conjunto de entidades.

Um atributo corresponde ao dado que é associado a cada ocorrência de uma entidade ou de um relacionamento. Segundo a Figura 01, podemos citar os atributos da entidade TOMADORES: CodTomador, NomeTomador, Fones e Situacao, que correspondem ao código, nome, telefones e situação do tomador, respectivamente.

Cada entidade deve possuir um identificador. Um identificador é um conjunto de um ou mais atributos (e possivelmente relacionamentos) cujos valores servem para distinguir uma ocorrência da entidade das demais ocorrências da mesma entidade. O atributo CodTomador na Entidade TOMADORES identifica unicamente um tomador. Não poderão existir dois tomadores com o mesmo código.

Um relacionamento corresponde ao conjunto de associações entre entidades. Um relacionamento é representado graficamente através de um losango, ligado por linhas aos retângulos representativos das entidades que participam do relacionamento. Na Figura 01 são apresentados relacionamentos denominados representam, suprem, solicitam e contém. Em cada um dos relacionamentos existem entidades participantes.

No Esquema E/R, existe a cardinalidade de relacionamento, que corresponde ao número de entidades que podem estar associadas via relacionamento. Existem três tipos de relacionamentos: 1:1 (um para um), 1:N (um para muitos), N:M (muitos para muitos). Existem dois tipos no exemplo: 1:N (um para muitos) e N:M (muitos para muitos). O relacionamento 1:N, solicita, que associa que um tomador (Entidade TOMADORES) solicita muitos adiantamentos (Entidade ADIANTAMENTOS), mas um adiantamento só pode ser solicitado por um tomador. O relacionamento N:M, representam, associa que vários tomadores (Entidade TOMADORES) representam várias localidades (Entidade LOCALIDADES). Vale ressaltar que nesse relacionamento tem um atributo, Situacao, que expressa se a representatividade entre tomadores e localidades está ativa ou não.

Implementação Relacional

O modelo Relacional foi definido por E. F Cood, em 1970. A grande aceitação comercial foi a partir de meados da década de 80. As razões dessa grande aceitação foram simplicidade dos conceitos básicos e poder dos operadores de manipulação. O Esquema Entidade/Relacionamento representado na Figura 01, uma vez mapeado para o Esquema Relacional, resultará nas seguintes relações representadas graficamente na Figura 02.

Figura 02: Esquema Relacional

Isso se deu tendo em vista as regras de mapeamento a seguir.

REGRA 1: Para cada entidade forte no esquema E/R, criar uma relação que inclui todos os atributos não multivalorados da entidade do Esquema E/R. Neste passo foram criadas as relações TOMADORES, LOCALIDADES e ADIANTAMENTOS com os atributos correspondentes de cada entidade.

REGRA 2: Para cada relacionamento 1:N no Esquema E/R: (i) identificar a relação que representa a entidade do lado N; (ii) incluir como chave estrangeira a chave primária da relação que representa a entidade do lado 1; e (iii) incluir os atributos do relacionamento na relação. No exemplo da Figura 01, existe um relacionamento 1:N, envolvendo as entidades TOMADORES e ADIANTAMENTOS, cujo relacionamento é solicitam. A relação que representa a entidade do lado N é a ADIANTAMENTOS. Deve ser incluída a chave da relação TOMADORES, que representa a entidade TOMADORES do lado 1, como chave estrangeira na relação ADIANTAMENTOS. Se existissem atributos de relacionamentos deveriam ser incluídos também.

REGRA 3: Para cada relacionamento M:N no Esquema E/R: (i) criar uma nova relação para representar o relacionamento; (ii) incluir como chave estrangeira as chaves primárias das relações que participam do relacionamento; (iii) essas chaves combinadas formarão a chave primária da relação; (iv) incluir também eventuais atributos do relacionamento. Através da Figura 01, tem-se o relacionamento representam que associa as entidades TOMADORES e LOCALIDADES. Para representar esse relacionamento no esquema relacional, cria-se uma relação REPRESENTAM e inclui-se as chaves primárias (CodTomador e CodLocalidade) das relações TOMADORES e LOCALIDADES como chaves estrangeiras nesta relação. Essas chaves combinadas formarão a chave primária da relação REPRESENTAM. O atributo de relacionamento Situacao será incluído na relação resultante.

REGRA 4: Para a entidade fraca no esquema E/R: (i) criar uma relação que inclui todos os atributos não multivalorados da entidade do Esquema E/R; (ii) incluir como chave estrangeira a chave primária da relação que representa a entidade forte. Neste passo foi criada a relação ITENS.

REGRA 5: Para cada atributo multivalorado: (i) criar uma nova relação; (ii) incluir o atributo correspondendo ao atributo multivalorado mais a chave primária da relação que tem esse atributo; (iii) incluir como chave primária da nova relação a combinação do atributo multivalorado mais a chave primária da entidade que tinha o atributo multivalorado.

Através da Figura 01, têm-se o atributo multivalorado Fones. Deve ser criada uma nova relação TELEFONES e incluir o atributo multivalorado Fones e a chave primária da relação TOMADORES que tem esse atributo multivalorado. A combinação do atributo multivalorado e a chave primária da relação TOMADORES formarão a chave primária da relação resultante.

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

CREATE TABLE tomadores
(CodTomador        NUMBER   (3)   NOT NULL,
 NomeTomador       VARCHAR2 (80)  NOT NULL,
 Situacao          CHAR     (1)   NOT NULL)
TABLESPACE cpcadata;

CREATE TABLE telefones
(CodTomador        NUMBER   (3)   NOT NULL,
 Fone              CHAR     (10)  NOT NULL)
TABLESPACE cpcadata;

CREATE TABLE localidades
(CodLocalidade     NUMBER   (3)   NOT NULL,
 NomeLocalidade    VARCHAR2 (80)  NOT NULL,
 Situacao          CHAR     (1)   NOT NULL)
TABLESPACE cpcadata;
CREATE TABLE representam
(CodTomador        NUMBER   (3)   NOT NULL,
 CodLocalidade     NUMBER   (3)   NOT NULL,
 Situacao          CHAR     (1)   NOT NULL)
TABLESPACE cpcadata;

CREATE TABLE adiantamentos
(NumAdiantamento   NUMBER   (5)   NOT NULL,
 CodTomador        NUMBER   (3)   NOT NULL,
 CodLocalidade     NUMBER   (3)   NOT NULL,
 Data              DATE           NOT NULL)
TABLESPACE cpcadata;

CREATE TABLE itens
(NumAdiantamento     NUMBER   (5)    not null,
 NumItem             NUMBER   (5)    not null,
 PrazoAplicacao      NUMBER   (3)    not null,
 DataAplicacao       DATE            not null,
 PrazoPrestacaoConta NUMBER   (3)    not null,
 DataPrevPrestConta  DATE            not null,
 Valor               NUMBER   (10,2) not null,
 NumPrestacaoConta   NUMBER   (5),
 DataPrestacaoConta  DATE)
TABLESPACE cpcadata;

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

ALTER TABLE tomadores
ADD CONSTRAINT tomadores_pk PRIMARY KEY (CodTomador)
USING INDEX TABLESPACE cpcaindx;

ALTER TABLE telefones
ADD CONSTRAINT telefones_pk PRIMARY KEY (CodTomador,Fone)
USING INDEX TABLESPACE cpcaindx;

ALTER TABLE localidades
ADD CONSTRAINT localidades_pk PRIMARY KEY (CodLocalidade)
USING INDEX TABLESPACE cpcaindx;

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

ALTER TABLE adiantamentos
ADD CONSTRAINT adiantamentos_pk PRIMARY KEY (NumAdiantamento)
USING INDEX TABLESPACE cpcaindx;
ALTER TABLE itens
ADD CONSTRAINT itens_pk PRIMARY Key (NumAdiantamento, NumItem)
USING INDEX TABLESPACE cpcaindx;

A seguir é apresentada a implementação da inserção das chaves estrangeiras.

ALTER TABLE telefones
ADD CONSTRAINT telefones_fk1 FOREIGN KEY (CodTomador)
REFERENCES tomadores (CodTomador);

ALTER TABLE representam
ADD CONSTRAINT representam_fk1 FOREIGN KEY (CodTomador)
REFERENCES tomadores (CodTomador);

ALTER TABLE representam
ADD CONSTRAINT representam_fk2 FOREIGN KEY (CodLocalidade)
REFERENCES localidades (CodLocalidade);

ALTER TABLE adiantamentos
ADD CONSTRAINT adiantamentos_fk1 FOREIGN KEY (CodTomador)
REFERENCES tomadores (CodTomador);

ALTER TABLE adiantamentos
ADD CONSTRAINT adiantamentos_fk2 FOREIGN KEY (CodLocalidade)
REFERENCES localidades (CodLocalidade);

ALTER TABLE itens
ADD CONSTRAINT itens_fk1 FOREIGN KEY (NumAdiantamento)
REFERENCES adiantamentos (NumAdiantamento);

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

ALTER TABLE tomadores
ADD CONSTRAINT tomadores_ck1 CHECK (Situacao IN ('A','I'));

ALTER TABLE localidades
ADD CONSTRAINT localidades_ck1 CHECK (Situacao IN ('A','I'));

ALTER TABLE representam
ADD CONSTRAINT representam_ck1 CHECK (Situacao IN ('A','I'));

A seguir é apresentada a implementação da inserção dos valores default.

ALTER TABLE tomadores
MODIFY Situacao CHAR DEFAULT 'A';

ALTER TABLE localidades
MODIFY Situacao CHAR DEFAULT 'A';

ALTER TABLE representam
MODIFY Situacao CHAR DEFAULT 'A';

ALTER TABLE adiantamentos
MODIFY Data DATE DEFAULT TO_DATE(TO_CHAR(SYSDATE,'yyyymmdd'),'yyyymmdd');

ALTER TABLE itens
MODIFY NumPrestacaoConta NUMBER DEFAULT NULL;

ALTER TABLE itens
MODIFY DataPrestacaoConta DATE DEFAULT NULL;

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

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

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

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

CREATE SEQUENCE itens_seq1
MINVALUE 1
MAXVALUE 999
START WITH 1
INCREMENT BY 1;
CREATE SEQUENCE itens_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_idx1
ON tomadores (NomeTomador)
TABLESPACE cpcaindx;

CREATE INDEX localidades_idx1
ON localidades (NomeLocalidade)
TABLESPACE cpcaindx;

CREATE INDEX itens_idx1
ON itens (NumPrestacaoConta)
TABLESPACE cpcaindx;

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

CREATE OR REPLACE TRIGGER Verifica_Representatividade
BEFORE INSERT ON adiantamentos
FOR EACH ROW
DECLARE
  VT_CodTomador NUMBER(3) DEFAULT 0;
BEGIN
  SELECT CodTomador INTO VT_CodTomador
  FROM   representam
  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
(VT_NumAdiantamento NUMBER, VT_Prazo_Aplicacao NUMBER)
RETURN DATE
IS
  RT_Data DATE;
BEGIN
  SELECT Data INTO RT_Data
  FROM   adiantamentos
  WHERE  NumAdiantamento = VT_NumAdiantamento;
  RETURN (RT_Data+VT_Prazo_Aplicacao);
END;

CREATE OR REPLACE FUNCTION Calcula_Data_PrestConta
(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
  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
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 a, itens i
WHERE  a.NumAdiantamento    = i.NumAdiantamento
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
(CodTomador,NomeTomador)
VALUES (tomadores_seq1.NEXTVAL,'Afonso Lima');
COMMIT;
INSERT INTO telefones
(CodTomador,Fone)
VALUES (tomadores_seq1.CURRVAL,'9212345678');
COMMIT;

INSERT INTO localidades
(CodLocalidade,NomeLocalidade)
VALUES (localidades_seq1.NEXTVAL,'GORF');
COMMIT;

INSERT INTO representam
(CodTomador,CodLocalidade)
VALUES (tomadores_seq1.CURRVAL,localidades_seq1.CURRVAL);
COMMIT;

INSERT INTO adiantamentos
(NumAdiantamento,CodTomador,CodLocalidade)
VALUES (adiantamentos_seq1.NEXTVAL,tomadores_seq1.CURRVAL, localidades_seq1.CURRVAL);
COMMIT;

INSERT INTO itens
(NumAdiantamento,NumItem,PrazoAplicacao,DataAplicacao, PrazoPrestacaoConta,DataPrevPrestConta,Valor)
VALUES (adiantamentos_seq1.CURRVAL,itens_seq1.NEXTVAL,30,
Calcula_Data_Aplicacao(001,30),30,
Calcula_Data_PrestConta(001,30,30),100000);
COMMIT;

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

----- Testeda Trigger -----
INSERT INTO adiantamentos
(NumAdiantamento,CodTomador,CodLocalidade)
VALUES (00002,002,002);
COMMIT;

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

SELECT CodTomador, NomeTomador, Situacao
FROM tomadores;

SELECT CodTomador, Fone
FROM telefones;
SELECT CodLocalidade, NomeLocalidade, Situacao
FROM localidades;

SELECT t.NomeTomador, l.NomeLocalidade
FROM   representam r, tomadores t, localidades l
WHERE  r.CodTomador    = t.CodTomador
AND    r.CodLocalidade = l.CodLocalidade;

SELECT a.NumAdiantamento, t.NomeTomador,
       l.NomeLocalidade,  a.Data
FROM   adiantamentos a, tomadores t, localidades l
WHERE  a.CodTomador    = t.CodTomador
AND    a.CodLocalidade = l.CodLocalidade;

SELECT a.NumAdiantamento,     i.NumItem,
       i.PrazoAplicacao,      i.DataAplicacao,
       i.PrazoPrestacaoConta, i.DataPrevPrestConta,
       i.Valor,               i.NumPrestacaoConta,
       i.DataPrestacaoConta
FROM   itens i, adiantamentos a
WHERE  i.NumAdiantamento = a.NumAdiantamento;

SELECT *
FROM View_Adiant_Atraso;

A seguir - na parte dois deste artigo - será apresentada a Implementação Objeto-Relacional.