Projeto/Reprojeto de Bancos de Dados Relacionais:
A Ferramenta DB-Tool
AndersonA. Ferreira 1
Alb erto H. F.Laender 1
Altigran S.da Silva 2 1Departamento de Ci^encia da Computac~ao
Universidade Federal de Minas Gerais 31270-010 Belo Horizonte MG
faaf,[email protected]
2Departamento de Ci^encia da Computac~ao Universidade do Amazonas
69077-000 Manaus AM [email protected]
Abstract
This paper describes a tool that supports the design and redesign of relational databases. The tool produces optimized relational representations of entity relationship (ER) schemas and is implemented using Informix as its target database management system (DBMS). The tool operates in two phases. In the rst phase, it receives as input an ER schema and generates a list of commands to implement the corresponding Informix schema. In the second phase, it receives a list of redesign commands specifying changes to the ER schema and generates a redesign plan to reestructure the database accordingly. An example illustrates the use of the tool.
1 Introduc~ao
A representac~ao logica de esquemas ER, esquemas conceituais construdos segundo o modelo entidade-relacionamento (modelo ER) [Chen76], por meio de esquemas relacionais e um dos temas classicos da area de banco de dados. Diversos metodos [AlAL85, BaCN92, CaTL90, CaTL93, MaSh92, TeYF86] t^em sido propostos para a construc~ao de representac~oes relacio-nais a partir de esquemas ER, processo que e chamado de projeto logico ou simplesmente pro-jeto [BaCN92, ElNa94]. Esses metodos geralmente utilizam algum criterio para otimizac~ao das
representac~oes e incorporam restric~oes de integridade ao esquema relacional resultante com o objetivo de garantir as propriedades sem^anticas do modelo ER.
Em particular, o metodo proposto em [CaTL90, CaTL93] gera representac~oes relacionais otimizadas onde o numero de depend^encias de inclus~ao entre as relac~oes e reduzido. Esses mesmos trabalhos tratam ainda o problema de manutenc~ao dessas representac~oes quando o esquema ER que elas representam sofre modicac~oes em func~ao de evoluc~oes ocorridas no mundo real. Esse processo, chamadoreprojeto, e ilustrado na Figura 1 e descrito a seguir.
Seja um esquema ER S
E. Na fase de projeto (1), S
E e mapeado para uma representac~ao
relacional otimizada S
R, sendo posteriormente instanciado (2), criando um estado consistente do banco de dados. De acordo com evoluc~oes ocorridas no mundo real, S
E pode ser
modi-cado (3) de tal forma a produzir um novo esquema conceitual S
E'. O processo de reprojeto
consiste em modicar S
R gerando uma nova representac~ao relacional S
R' (4) e mapear em
um novo estado' (5), de tal forma que' seja consistente comS
R' (7) e S
R' represente S
E' de
forma correta e otimizada (6).
2
5
7 1
3
4
6
SE
SR SR
SE
’ ’
σ σ
’
Figura 1: Processo de projeto/reprojeto de bancos de dados.
Informix para gerar o esquema do banco de dados. Na fase de reprojeto, DB-Tool recebe uma lista de comandos de reprojeto especicando modicac~oes no esquema ER e gera uma sequ^encia de comandos na LDD/LMD do sistema Informix que constituem um plano de reprojeto para o banco de dados.
Diversas ferramentas de projeto de banco de dados relacionais t^em sido descritas na lite-ratura [AlAL85, Fran96, HEH+94, MaSh94]. O que torna a nossa ferramenta diferente dessas e o fato de que ela suporta n~ao apenas o processo de projeto, mas tambem o processo de re-projeto de bancos de dados relacionais. Mesmo ferramentas comerciais, tais como, ERwin e S-Designor [Butl96], suportam apenas o projeto de bancos de dados e n~ao auxiliam o projetista na realizac~ao do processo de reprojeto. Assim sendo, o proprio projetista deve reestruturar o banco de dados quando ocorrem modicac~oes nas aplicac~oes. Um outro aspecto importante da nossa ferramenta e que ela permite ao projetista avaliar o impacto das mudancas sem re-almente reestruturar o banco de dados, uma vez que, de acordo com o metodo de reprojeto adotado [Silv95, SiLC96], o novo estado do banco de dados e gerado a partir de um estado transiente (virtual) que e obtido sem modicar a representac~ao relacional corrente.
O artigo a seguir esta organizado da seguinte forma. A Sec~ao 2 apresenta uma vis~ao geral do metodo de projeto/reprojeto adotado. A Sec~ao 3 contem uma descric~ao geral da ferramenta. A Sec~ao 4 descreve um exemplo de utilizac~ao da ferramenta. Finalmente, a Sec~ao 5 apresenta algumas conclus~oes.
2 Vis~ao Geral do Metodo de Projeto/Reprojeto
2.1 Conceitos Basicos
2.1.1 Esquemas ER
Um esquema ER e uma dupla S E =
hE;Rionde E e um conjunto de esquemas de entidade eR
e um conjunto de esquemas de relacionamento.
entidade, sendo que se um esquema de entidade Eespecializa um esquema de entidade F, ent~ao
toda inst^ancia de Ee tambem uma inst^ancia de F. A denic~ao de uma chave primaria para um
esquema de entidade so e obrigatoria se este esquema de entidade n~ao especializa nenhum outro. Um esquema de relacionamento possui um nome, uma lista de esquemas de entidade partici-pantes e, opcionalmente, uma lista de atributos. As inst^ancias de um esquema de relacionamento s~ao elementos do produto cartesiano do conjunto de inst^ancias dos esquemas de entidade que s~ao participantes do esquema de relacionamento. A cada participante e atribudo um papel que deve ser unico em cada esquema de relacionamento. Um conjunto de papeis e um identicador de um esquema de relacionamento se toda combinac~ao de int^ancias desses papeis for unica entre as inst^ancias desse esquema [Silv95].
Um estado de um esquema ER S
E e consistente quando todas as restric~oes sem^anticas s~ao
respeitadas pelas inst^ancias dos seus esquemas de entidade e relacionamento. Uma denic~ao mais precisa pode ser encontrada em [Silv95].
A Figura 2 descreve o esquema ER Company que sera usado como exemplo neste artigo.
dene entityE (Employee)
attributesId char(4) not null, Salarydecimal(8,2)
key Id
dene entityP (Project)
attributes Name char(20) not null, Contractorchar(20)
key Name
dene entityA (Administrative)
attributes Job Descchar(40) not null
specialization ofE
dene entityR (Researcher)
attributes Degreechar(4) not null
specialization ofE
dene relationship W (Works-for)overR,P
attributes Hoursinteger not null,
identier R
Figura 2: Esquema ER Company.
Um grafo g= (V;A;l) de um esquema ER S
E (ou simplesmente grafo ER) e um multigrafo
dirigido, parcialmente rotulado, onde cada vertice emV corresponde a um esquema de entidade
ou relacionamento deS
E e um arco (
S;T)2A se, e somente se,Sespecializa TouTparticipa de Scom papel P, sendo l((S;T)) =Ppertencente ao conjunto de rotulos l. A Figura 3(a) mostra
o grafo ER correspondente ao esquema ER Company da Figura 2.
P
R A
E
R W
P P
W R
E
A
(a) Grafo ER g (b) Floresta de colapsamento f
Arco nao-colapsavel Arco colapsavel
Figura 3: Grafo ER e oresta de colapsamento.
2.1.2 Esquemas Relacionais
Um esquema relacional e uma tripla S R =
hT;V;Ci onde T e um conjunto de esquemas de
relac~ao,V e um conjunto de esquemas de vis~ao e C e um conjunto de restric~oes de integridade
sobre T [V.
primaria e uma lista de atributos que n~ao admitem valor nulo e uma chave alternativa e uma lista de atributos quaisquer. As restric~oes de integridade emC podem ser dos seguintes tipos:
Depend^encias de Inclus~ao (DIs) - Sejam T 1 e
T
2 esquemas de relac~ao contendo,
repectiva-mente, os conjuntos de atributosX 1 e
X
2. Uma DI da forma T 1[ X 1] T 2[ X
2] e satisfeita para
os estadost 1(
T 1) e
t 2(
T
2) se, e somente se, para cada tupla t de t
1( T
1), existe uma tupla u de t
2( T
2) tal que t[X
1] = u[X
2].
Depend^encias de Nulos (DNs) - Seja T um esquema de relac~ao contendo os conjuntos de
atributosA e B. Uma DN da forma T:A; B e satisfeita se, e somente se, para toda tupla de t(T), os atributos do conjunto B s~ao todos nulos sempre que um dos atributos de A for nulo.
Uma DN da forma T:[A] e satisfeita se, e somente se, em toda tupla de t(T) os atributos do
conjunto A s~ao todos nulos ou todos n~ao nulos [SiLC94, Silv95]. Esse segundo tipo de DN e
denominado depend^encia de nulos entre si. Um estado de um esquema relacional S
R e consistente se, e somente se, os estados dos
seus esquemas de relac~ao e vis~ao s~ao consistentes com a sua denic~ao, e todas as restric~oes de integridade (DIs e DNs) s~ao respeitadas [Silv95].
2.2 Representac~oes Relacionais de Esquemas ER
Um esquema relacional S
Re uma representac~ao relacional de um esquema ER S
E se a denic~ao
de S
R garantir que qualquer estado consistente
E de S
E pode ser representado por um estado
consistente R de
S
R e qualquer estado consistente
R representa um estado consistente de S
E [Silv95].
Uma maneira trivial de se obter uma representac~ao relacional de um esquema ERS
Ee gerar
um esquema de relac~ao (com atributos apropriados) para representar cada esquema de entidade ou relacionamento emS
E, gerando uma DI para cada arco do grafo ER correspondente de modo
a capturar adequadamente a sem^antica de S E.
Entretanto, apesar da simplicidade desse metodo, as representac~oes relacionais obtidas atraves dele s~ao inecientes, no sentido de que o numero de DIs a serem mantidas e pelo menos igual ao numero de arcos no grafo do esquema ER em quest~ao. Desta forma, visando reduzir o numero de DIs envolvidas, pode-se optar por representar em um mesmo esquema de relac~ao varios es-quemas de entidade ou relacionamento que compartilham alguma propriedade. Essa estrategia de otimizac~ao, chamada de colapsamento [CaTL90, CaTL93], e utilizada para a gerac~ao de re-presentac~oes relacionais otimizadas onde varias DIs podem ser removidas e outras podem ser substitudas por DNs cujo custo de vericac~ao e menor [Silv95, SiLC96].
Uma representac~ao relacional otimizada e gerada com base em uma estrutura chamada o-resta de colapsamento[CaTL90, CaTL93]. Uma oresta de colapsamentof de um esquema ER
e um conjunto de arvores que possui todos os vertices do grafog do esquema ER, mas no qual
somente arcos de g que s~ao colapsaveis est~ao representados. Um arco (S,T) de g e colapsavel
quando n~ao e rotulado ou se o rotulo e N e T e funcional em S com papel N. A Figura 3 (b)
mostra uma oresta de colapsamento correspondente ao grafo ER da Figura 3 (a).
Assim, em uma representac~ao relacional otimizada, para cada raiz Rda oresta de
colapsa-mento f e gerado um esquema de relac~aoR
*, chamado esquema de colapsamento, de tal forma
que em R
* s~ao representados
R e todos os seus descendentes em f. Alem disso, para cada
vertice S de f, e gerado um esquema de vis~ao, chamado esquema de representac~ao, sobre o
esquema de colapsamento correspondente que representaraSna representac~ao relacional
otimi-zada [Silv95, SiLC96]. Quanto as restric~oes de integridade necessarias, para cada arco(S,T)de gque n~ao esteja emf s~ao geradas DIs envolvendo os esquemas de representac~ao correspondentes
e para cada arco (S,T)que esteja em f s~ao geradas DNs envolvendo os atributos do esquema
2.3 Manutenc~ao de Representac~oes Relacionais
O processo de reprojeto ilustrado na Figura 1 tem por objetivo modicar a representac~ao rela-cional de um esquema ERS
Ee reestruturar o estado atual do banco de dados de forma a torna-lo
consistente com a nova representac~ao gerada. Para isso, o metodo de reprojeto [Silv95, SiLC96] recebe como entrada o esquema ER S
E, juntamente com o seu grafo
g e a oresta de
colap-samento f que dene a sua representac~ao relacional, e uma serie de comandos de reprojeto,
especicando modicac~oes emS
E, e gera como sada o plano de reprojeto correspondente. Alem
disso, o metodo tambem inclui uma analise do estado atual da representac~ao que visa vericar se e possvel transformar este estado em um estado consistente para a nova representac~ao rela-cional. O plano de reprojeto pode ser usado pelo projetista responsavel pelas modicac~oes do esquema ER original para avaliar o impacto e a viabilidade das alterac~oes necessarias no banco de dados.
Assim, distinguimos quatro etapas distintas, embora dependentes entre si, no metodo de reprojeto [Silv95, SiLC96]:
Gerac~ao do novo esquema ER. Consiste na aplicac~ao dos comandos de reprojeto ao esquema
ER S
E com o objetivo de produzir um novo esquema ER S'
E. Alem disso, devem ser gerados o
grafog 0de
S'
E e tambem uma oresta de colapsamento f
0.
Vericac~ao da adequac~ao ao novo esquema ER. Consiste em vericar se e possvel produzir
um estado consistente correspondente ao novo esquema ER a partir do estado atual do esquema ER original, que e representado pelo estado atual da representac~ao relacional corrente.
Reestruturac~ao da representac~ao relacional. Consiste na comparac~ao das orestas de
colap-samentof ef
0para realizar expans~oes (criar esquemas de colapsamento, denir novas restric~oes
e introduzir novos atributos) e contrac~oes (remover esquemas de colapsamento, restric~oes e atributos) na representac~ao relacional, alem de atualizar de forma adequada os esquemas de representac~ao.
Mapeamento de inst^ancias para a nova representac~ao relacional. Consiste em mapear, quando
necessario, as inst^ancias existentes na representac~ao original para a nova representac~ao relacio-nal.
3 A Ferramenta DB-Tool
Nesta sec~ao, apresentamos uma descric~ao geral da ferramenta DB-Tool que foi desenvolvida com base no metodo de projeto/reprojeto, descrito na sec~ao anterior. DB-Tool utiliza como SGBD alvo o sistema Informix [Info91]. A escolha do Informix como sistema alvo para a fer-ramenta foi feita basicamente em func~ao de sua disponibilidade no Departamento de Ci^encia da Computac~ao da UFMG. Entretanto, o Informix e um SGBD relacional que possui algumas caractersticas que o tornam adequado como sistema alvo para esse tipo de ferramenta, como, por exemplo, a denic~ao explcita de chaves primarias, chaves alternativas e chaves estrangeiras atraves da sua LDD. Alem disso, o Informix possibilita a criac~ao de procedimentos, vis~oes e gatilhos (\triggers"), a alterac~ao de tabelas, e a remoc~ao de procedimentos, tabelas, vis~oes e gatilhos, facilidades fundamentais para a implementac~ao do metodo de projeto/reprojeto.
3.1 Gerac~ao da Representac~ao Relacional Otimizada
Na fase de projeto, quando e realizada a gerac~ao da representac~ao relacional otimizada, o pro-jetista entra com o esquema ER e a ferramenta produz uma sequ^encia de comandos da LDD do Informix para a criac~ao das tabelas, vis~oes e restric~oes de integridade (DIs e DNs) corresponden-tes. A gerac~ao dessa representac~ao relacional e feita a partir de uma oresta de colapsamentof
Para cada raizRdef, e gerado um comando CREATE TABLE necessario para criar uma tabela
de nome R CLP, cujos atributos s~ao os atributos associados a Re todos os atributos direta ou
indiretamente associados aos descendentes deR. A chave primaria dessa tabela e a chave primaria
deR. As demais chaves deRe de seus descendentes emf s~ao denidas como chaves alternativas
da tabelaR CLP.
3.1.2 Criac~ao das Vis~oes
De acordo com o metodo de projeto/reprojeto, para cada vertice Sde f, correspondente a um
esquema de entidade ou relacionamento, e gerado um esquema de vis~aoSque sera o seu esquema
de representac~ao. O esquema de vis~ao e denido sobre o esquema de colapsamento que engloba
S. Assim, para cada verticeSde f e gerado um comando CREATE VIEW necessario para criar
a vis~ao correspondente ao esquema de representac~ao de S.
3.1.3 Criac~ao das Dep end^encias de Inclus~ao
Para cada arco (E,F) do grafog do esquema ER n~ao existente na oresta de colapsamento f
deve ser gerada uma DI correspondente. A implementac~ao de uma DI no sistema Informix e feita atraves de uma chave estrangeira denida pela clausula FOREIGN KEY. Assim, para cada arco (E,F) de g n~ao existente em f, a ferramenta inclui a denic~ao de uma chave estrangeira
na denic~ao da tabela correspondente ao esquema de colapsamento que engloba E.
E importante observar que as DIs geradas pelo metodo de projeto s~ao denidas entre esque-mas de representac~ao (vis~oes), o que n~ao e possvel de ser diretamente implementado no Infor-mix. Assim, para os arcos (E,F) nos quais F n~ao e uma raiz de f, alem da chave estrangeira,
s~ao denidos gatilhos cuja func~ao e vericar se as tuplas das tabelas envolvidas representam corretamente inst^ancias do esquema ER.
3.1.4 Criac~ao das Dep end^encias de Nulos
De acordo com o metodo de projeto/reprojeto, as seguintes DNs devem ser geradas a partir da oresta de colapsamento f:
Depend^encia de nulos entre si, para os atributos n~ao nulos dos vertices que n~ao s~ao razes
def (os atributos herdados que n~ao comp~oem a chave primaria tambem fazem parte desta
depend^encia de nulos entre si).
Depend^encia dos atributos n~ao nulos de um vertice Sque n~ao e raiz de f em relac~ao aos
atributos n~ao nulos do vertice pai deSem f que n~ao e uma raiz emf.
Depend^encia dos atributos que podem assumir nulos de um vertice n~ao raiz de f para os
atributos n~ao nulos deste vertice.
Para a implementac~ao das DNs, e usada a clausula CHECK que verica se uma tupla, ao ser inserida ou modicada, satisfaz determinadas condic~oes.
3.2 Manutenc~ao da Representac~ao Relacional
3.2.1 Gerac~ao do Novo Esquema ER
Nesta etapa, o projetista entra com os comandos de reprojeto e a ferramenta gera o novo esquema ER com seus respectivos grafos. Exemplos desses comandos s~ao mostrados na Figura 4 para o esquema ER CompanyS
E (Figura 2). Uma descric~ao detalhada dos comandos de reprojeto que
podem ser aplicados a um esquema ER pode ser encontrada em [Ferr97].
remove identierRfrom relationshipW;
remove keyNamefromP;
add attributeNumberinteger not null toP;
add keyNumber to P;
Figura 4: Comandos para modicar o esquema ER Company.
3.2.2 Vericac~ao da Adequac~ao ao Novo Esquema ER
Esta etapa tem como objetivo vericar se o estado atual do banco de dados e adequado ao novo esquema ou se a partir do estado atual e possvel gerar um estado que seja adequado ao novo esquema, gerando um estado intermediario, sobre o qual e vericada a consist^encia com o novo esquema ER. Para isso, s~ao executadas tr^es tarefas, de acordo com o metodo de reprojeto [Silv95, SiLC96]: coleta de dados, gerac~ao das representac~oes relacionais transitorias e vericac~ao da adequac~ao, propriamente dita. A seguir, descreveremos como s~ao gerados os comandos da LDD/LMD do Informix para realizar essas tarefas.
Coleta de Dados.
Esta tarefa e necessaria quando alguma das novas restric~oes sobre o novo esquema faz refer^encia a atributos cujos valores n~ao podem ser determinados na representac~ao relacional corrente correspondente, tornando-se necessario determinar esses valores antes de vericar as novas restric~oes. Uma outra situac~ao em que e necessario a coleta de dados, e quando um novo esquema de entidade ou relacionamento Se includo e precisa ser instanciado,como no caso de um esquema de entidade especializado por outro ja existente no esquema ER original. A coleta de dados e feita atraves de tabelas de valorac~ao criadas pela ferramenta e que s~ao instanciadas pelo projetista de acordo com cada situac~ao especca.
Gerac~ao das Representac~oes Relacionais Transitorias.
Nesta tarefa, para cada esquema de entidade ou relacionamento do novo esquema ER, e criada uma representac~ao relacional tran-sitoria [Silv95, SiLC96] que ira compor um banco de dados transitorio (virtual), atraves do qual sera vericado se o estado esta consistente com o novo esquema relacional e, consequentemente, com o novo esquema ER.Para a criac~ao das representac~oes relacionais transitorias s~ao observados quatro casos: (1) o es-quema de entidade ou relacionamento Sdo novo esquema ER S'
E n~ao pertence ao esquema ER
original S
E; (2) o conjunto de atributos da representac~ao relacional do esquema de entidade ou
relacionamentoSe o mesmo para o esquema ER original e para o novo esquema ER; (3) o
con-junto de atributos da representac~ao relacional de um esquema de entidadeSn~ao e o mesmo para
o esquema ER original e para o novo esquema ER; e (4) o conjunto de atributos da representac~ao relacional de um esquema de relacionamento R n~ao e o mesmo para o esquema ER original e
para o novo esquema ER.
Vericac~ao da Adequac~ao.
Para vericar a adequac~ao das representac~oes relacionais tran-sitorias em relac~ao ao novo esquema ER, e necessario vericar se s~ao satisfeitas todas as novas depend^encias de inclus~ao e todas as novas chaves denidas. Assim, s~ao gerados comandos SELECT para vericar as novas chaves e para vericar as novas depend^encias de inclus~ao que s~ao devidas a arcos do novo grafog' que n~ao existem no grafog e tambem a arcos cujas chaves,estrangeiras e primarias, usadas na denic~ao de alguma DI, mudaram.
3.2.3 Reestruturac~ao da Representac~ao Relacional
A etapa de reestruturac~ao da representac~ao relacional consiste da reestruturac~ao do esquema Informix a partir de modicac~oes feitas no esquema ER original. Para essa reestruturac~ao, a ferramenta gera os comandos Informix necessarios para criar e expandir tabelas, criar e remover chaves, atualizar a denic~ao de vis~oes, atualizar DNs e DIs, e remover e contrair tabelas. A gerac~ao desses comandos e feita comparando-se a oresta de colapsamento f com a oresta de
colapsamentof' gerada a partir das modicac~oes introduzidas no esquema ER original [SiLC96].
A seguir, e apresentada uma breve descric~ao de como esses comandos s~ao gerados pela ferra-menta.
Criac~ao e Expans~ao de Tabelas.
Para cada nova raiz def', e gerado um comando CREATETABLE para criar a tabela correspondente conforme descrito na Sec~ao 3.1. Tambem s~ao gerados comandos ALTER TABLE para alterar a denic~ao das tabelas que correspondem a razes de arvores def' cujos vertices correspondem a esquemas de entidade ou relacionamento nos quais
foram introduzidos novos atributos ou que incorporam atributos de esquemas de entidade ou relacionamento correspondentes a vertices de outra arvore def.
Remoc~ao de Depend^encias de Inclus~ao.
Para cada arco de g, n~ao existente em f,re-movido, e gerado um comando ALTER TABLE para remover a denic~ao da chave estrangeira que implementa a DI correspondente. Se para a DI que esta sendo removida existirem gatilhos relacionados a ela, estes tambem devem ser removidos. Para isso e gerado um comando DROP TRIGGER para remover a denic~ao de cada gatilho.
Atualizac~ao de Chaves.
Para cada verticeEde f' cujo esquema de entidade (ourelaciona-mento) teve a sua chave (ou identicador) alterada(o), s~ao gerados comandos ALTER TABLE para alterar a chave primaria ou alguma chave alternativa da tabela correspondente ao esquema de colapsamento que engloba E.
Atualizac~ao de Depend^encias de Nulos.
Para cada arco de f, n~ao existente em f', s~aogerados, se necessario, comandos ALTER TABLE para remover as restric~oes impostas pelas clausulas CHECK que implementam as DNs correspondentes. Analogamente, para cada novo arco criado emf', s~ao gerados, se necessario, comandos ALTER TABLE para criar a denic~ao
de clausulas CHECK que implementam a DN correspondente.
Atualizac~ao de Vis~oes.
Para cada vertice R de f n~ao existente em f', ou que teve a suaposic~ao ou lista de atributos correspondentes alterada na oresta de colapsamento f', e gerado
um comando DROP VIEW para remover a denic~ao da vis~ao correspondente. Alem disso, para cada vertice R de f' n~ao existente em f, ou que teve a sua posic~ao ou lista de atributos
correspondentes alterada na oresta de colapsamentof', e gerado um comando CREATE VIEW
Adic~ao de Novas Depend^encias de Inclus~ao.
Para cada novo arco criado em g' e n~aoexistente em f', e gerado um comando ALTER TABLE para criar a denic~ao da chave
estran-geira que implementa a DI correspondente. Se para essas novas DIs for necessaria a adic~ao de gatilhos, e gerado um comando CREATE TRIGGER correspondente.
Contrac~ao e Remoc~ao de Tabelas.
Para cada raiz R de f que n~ao e mais uma raiz em f', e gerado um comando DROP TABLE que remove a denic~ao da tabela correspondente aoesquema de colapsamento de R. Tambem s~ao gerados comandos ALTER TABLE para alterar
a denic~ao das tabelas que correspondem a razes mantidas emf', mas cujos vertices sofreram
alguma alterac~ao que requer a remoc~ao de atributos dessas tabelas.
3.2.4 Mapeamento de Inst^ancias para a Nova Representac~ao Relacional
De acordo com o metodo de reprojeto [Silv95], existem duas situac~oes onde e necessario o mapeamento de inst^ancias de um esquema de entidade ou relacionamento, representado emS
R,
para a nova representac~ao relacional: (1) o vertice, que representa o esquema de entidade ou relacionamento, se encontra em subgrafos de colapsamento distintos em f e f'; (2) o vertice
permanece no mesmo subgrafo de colapsamento, mas ganhou ou perdeu atributos nativos ou herdados.
Para resolver essas duas situac~oes, foram denidas duas operac~oes: uma de movimentac~ao de inst^anciase outra de ajuste de inst^ancias. A operac~ao de movimentac~ao visa resolver a primeira situac~ao enquanto que a operac~ao de ajuste resolve a segunda. Para ambas as operac~oes, s~ao gerados comandos SELECT, INSERT e UPDATE, denidos sobre as representac~oes relacionais transitorias, para executar o mapeamento das inst^ancias para a nova representac~ao relacional.
3.2.5 Gerac~ao do Plano de Reprojeto
Para que o novo esquema Informix e o novo estado do banco de dados sejam gerados corre-tamente, e necessario que a execuc~ao dos comandos que comp~oem o plano de reprojeto seja feita em uma ordem especca. Uma possvel ordem para a gerac~ao desses comandos e a se-guinte [Ferr97]: (1) comandos de coleta de dados, (2) comandos de gerac~ao das representac~oes relacionais transitorias, (3) comandos de vericac~ao da adequac~ao do estado transitorio ao novo esquema ER, (4) comandos para criac~ao e expans~ao de tabelas, (5) comandos de mapeamento para a nova representac~ao relacional, (6) comandos de remoc~ao de tabelas e vis~oes criadas na etapa de adequac~ao, (7) comandos de remoc~ao de depend^encias de inclus~ao, (8) comandos de atualizac~ao de chaves, (9) comandos de atualizac~ao de depend^encias de nulos, (10) comandos de atualizac~ao de vis~oes, (11) comandos de adic~ao de novas depend^encias de inclus~ao e (12) co-mandos de contrac~ao e remoc~ao de tabelas.
A execuc~ao do plano de reprojeto na ordem estabelecida acima garante que todos comandos gerados ser~ao executados. Embora essa ordem n~ao seja a unica possvel [Silv95, SiLC96], a invers~ao de certos comandos pode originar erros de processamento no sistema Informix. Por exemplo, se atualizarmos as chaves e depois removermos as DIs, podem ocorrer erros, uma vez que ao remover uma chave todas as chaves estrangeiras denidas sobre ela s~ao tambem removidas.
4 Discuss~ao de um Exemplo
4.1 Gerac~ao do Esquema Informix
A Figura 5 mostra o esquema Infomix gerado pela ferramenta a partir do esquema ER da Figura 2, conforme descrito na Sec~ao 3.1.
CREATE DATABASE Company WITH LOG; CREATE TABLE P CLP (
Name char(20) NOT NULL, Contractor char(20),
PRIMARY KEY(Name) CONSTRAINT PK P); CREATE TABLE E CLP (
Id char(4) NOT NULL, Salary decimal(8,2), Job desc char(40), Degree char(4), P Name char(20), Hours integer,
PRIMARY KEY(Id) CONSTRAINT PK E, CHECK ((P Name IS NOT NULL AND
Hours IS NOT NULL) OR
(P Name IS NULL AND Hours IS NULL)) CONSTRAINT DN W 1 ,
CHECK ((Degree IS NOT NULL) OR (P Name IS NULL AND Hours IS NULL)) CONSTRAINT DN W 2,
FOREIGN KEY (P Name) REFERENCES P CLP(Name) CONSTRAINT DI FK W P);
CREATE VIEW E AS SELECT Id, Salary FROM E CLP; CREATE VIEW A AS
SELECT Id, Job desc FROM E CLP
WHERE Job desc IS NOT NULL; CREATE VIEW P AS
SELECT Name, Contractor FROM P CLP;
CREATE VIEW R AS SELECT Id, Degree FROM E CLP
WHERE Degree IS NOT NULL; CREATE VIEW W AS
SELECT Id, P Name, Hours FROM E CLP
WHERE P Name IS NOT NULL AND Hours IS NOT NULL;
CREATE PROCEDURE ErroDIOP()
RAISE EXCEPTION -746,0,"Missing key in referenced table for referential constraint"; END PROCEDURE;
Figura 5: Esquema Informix.
Note que para cada raiz da oresta de colapsamento f da Figura 3 (b), foi gerado um
comando CREATE TABLE que dene o esquema de colapsamento correspondente e para cada vertice de f foi gerado um comando CREATE VIEW que dene o esquema de representac~ao
correspondente. Alem disso, para cada arco do grafo ER g n~ao existente em f foi includa
uma clausula FOREIGN KEY na denic~ao da tabela correspondente ao vertice de origem, e para cada arco de f foi includa uma clausula CHECK na denic~ao da tabela correspondente
a raiz da arvore que contem esse arco. Cada clausula FOREIGN KEY e CHECK implementa, respectivamente, uma DI e uma DN gerada pelo metodo de projeto.
4.2 Gerac~ao dos Comandos para Execuc~ao do Reprojeto
4.2.1 Gerac~ao do Novo Esquema ER
Considere os comandos de reprojeto mostrados na Figura 4 especicando modicac~oes sobre o esquema ER da Figura 2. O resultado dessas modicac~oes e o esquema ER mostrado na Figura 6. O grafog' e a oresta de colapsamentof' correspondentes s~ao mostrados na Figura 7.
dene entityE (Employee)
attributesId char(4) not null, Salarydecimal(8,2)
key Id
dene entityA (Administrative)
attributesA.Job Descchar(40) not null
specialization ofE
dene entityR (Researcher)
attributesDegreechar(4) not null
specialization ofE
dene entityP (Project)
attributesNumber integer not null, Name char(20) not null, Contractorchar(20)
key Number
dene relationship W (Work-for)overR,P
attributesHoursinteger not null
P
R A
E
R W
P P
W R
E
A
Arco nao-colapsavel Arco colapsavel
(b) Floresta de colapsamento f’ (a) Grafo ER g’
Figura 7: Novo grafo ER e nova oresta de colapsamento.
4.2.2 Vericac~ao da Adequac~ao ao Novo Esquema ER
Considere o esquema ER original (Figura 2), o novo esquema ER (Figura 6), o grafo ER original
g (Figura 3 (a)) e o novo grafo ER g' (Figura 7 (a)). Os comandos gerados para a realizac~ao
das tarefas de coleta de dados, gerac~ao das representac~oes relacionais transitorias e vericac~ao da adequac~ao s~ao mostrados na Figura 8.
{ Coleta de dados
CREATE TABLE P Number ( Name char(20) NOT NULL, Number integer ,
PRIMARY KEY(Name)); LOAD FROM \P Number"
INSERT INTO P Number;
{ Gerac~ao das representac~oes relacionais transitorias CREATE VIEW E RRT
AS SELECT * FROM E; CREATE VIEW R RRT
AS SELECT * FROM R; CREATE VIEW
P RTN(Name, Number, Contractor) AS SELECT
P.Name, P Number.Number, P.Contractor FROM P, P Number
WHERE P.Name = P Number.Name; CREATE VIEW A RRT
AS SELECT * FROM A;
CREATE VIEW P RRT AS SELECT
P RTN.Number,P RTN.Name,P RTN.Contractor FROM P RTN;
CREATE VIEW
W RRT(R Id, P Number, Hours) AS SELECT W.R Id, P.Number, W.Hours FROM W, P RTN P
WHERE W.P Name = P.Name; { Vericac~ao da adequac~ao
SELECT Number FROM P RRT GROUP BY Number
HAVING COUNT(*)>1;
SELECT R Id, P Number FROM W RRT GROUP BY R Id, P Number
HAVING COUNT(*)>1;
SELECT * FROM W RRT
WHERE NOT EXISTS (SELECT * FROM P RRT WHERE W RRT.P Number = P RRT.Number);
Figura 8: Comandos de vericac~ao da adequac~ao.
Note que esses comandos correspondem, respectivamente, aos comandos necessarios para criar e carregar a tabela de valorac~ao para o atributoNumberadicionado ao esquema de entidade P, criar as representac~oes relacionais transitorias dos esquemas de entidade e relacionamento do
novo esquema ER, e vericar se essas representac~oes relacionais transitorias satisfazem as novas restric~oes de integridade denidas.
4.2.3 Reestruturac~ao do esquema Informix
Comparando os grafos ER g (Figura 3 (a)) e g' (Figura 7 (a)) e as orestas de colapsamento f (Figura 3 (b)) e f' (Figura 7 (b)), a ferramenta gera os comandos mostrados na Figura 9,
CREATE TABLE W CLP ( Hours integer NOT NULL, R Id char(4) NOT NULL, P Number integer NOT NULL, PRIMARY KEY(R Id, P Number) CONSTRAINT PK W);
ALTER TABLE P CLP ADD
(Number integer DEFAULT 9 NOT NULL); ALTER TABLE E CLP DROP
CONSTRAINT DI FK W P; ALTER TABLE P CLP DROP
CONSTRAINT PK P; ALTER TABLE P CLP ADD
CONSTRAINT PRIMARY KEY (Number) CONSTRAINT PK P;
ALTER TABLE E CLP DROP CONSTRAINT DN W 1; ALTER TABLE E CLP DROP
CONSTRAINT DN W 2; DROP VIEW P;
CREATE VIEW P AS
SELECT Number, Name, Contractor FROM P CLP;
DROP VIEW W; CREATE VIEW W AS
SELECT R Id, P Number, Hours FROM W CLP;
ALTER TABLE W CLP ADD
CONSTRAINT FOREIGN KEY (P Number) REFERENCES P CLP(Number)
CONSTRAINT DI FK W P;
CREATE TRIGGER DI UP R UPDATE OF Degree ON E CLP REFERENCING OLD AS pre
NEW AS post FOR EACH ROW WHEN
(pre.degree IS NOT NULL AND post.Degree IS NULL AND EXISTS (SELECT * FROM W WHERE pre.Id = W.R Id))
(EXECUTE PROCEDURE ErroDIOP()); CREATE TRIGGER DI W R
UPDATE OF R Id ON W CLP REFERENCING NEW AS post FOR EACH ROW
WHEN (post.R Id IS NOT NULL AND (NOT EXISTS (SELECT * FROM R WHERE post.R Id = R.Id)))
(EXECUTE PROCEDURE ErroDIOP()); CREATE TRIGGER DI W
INSERT ON W CLP
REFERENCING NEW AS post FOR EACH ROW
WHEN ((post.R Id IS NOT NULL AND NOT EXISTS (SELECT * FROM R WHERE post.R Id = R.Id)))
(EXECUTE PROCEDURE ErroDIOP()); ALTER TABLE W CLP ADD
CONSTRAINT FOREIGN KEY (R Id) REFERENCES E CLP(Id)
CONSTRAINT DI FK W R;
ALTER TABLE E CLP DROP (P Name, Hours);
Figura 9: Comandos de reestruturac~ao do esquema Informix.
Observe que esses comandos criam uma nova tabela W CLP correspondente a arvore de
raiz W de f', alteram a denic~ao das tabelas P CLP e E CLP de acordo com as modicac~oes
feitas no esquema ER e atualizam as representac~oes relacionais deWeP. Note que os comandos
CREATE TRIGGER correspondem aos gatilhos que complementam a implementac~ao das novas DIs geradas, conforme discutido na Sess~ao 3.1.3.
4.2.4 Mapeamento de Inst^ancias para a Nova Representac~ao Relacional
Considere os grafos ER g (Figura 3 (a)) e g' (Figura 7 (a)) e as orestas de colapsamento f
(Figura 3 (b)) ef' (Figura 7 (b)). A ferramenta gera os comandos mostrados na Figura 10. O
comando INSERT corresponde a uma operac~ao de movimentac~ao de inst^ancias enquanto que o comando UPDATE corresponde a uma operac~ao de ajuste de inst^ancias.
SELECT Number, Name
FROM P RRT INTO TEMP Tab1;
UPDATE P CLP SET(Number) = ((SELECT Number FROM Tab1 WHERE P CLP.Name = Tab1.Name));
DROP TABLE Tab1;
INSERT INTOW CLP (R Id, P Number, Hours) SELECT R Id, P Number, Hours
FROM W RRT;
Apresentamos neste artigo uma ferramenta para projeto/reprojeto de banco de dados relacionais que foi desenvolvida com base no metodo proposto em [CaTL90, CaTL93]. A ferramenta, denominada DB-Tool, implementa os algoritmos descritos em [Silv95] e utiliza como SGBD alvo o sistema Informix. Na fase de projeto, a ferramenta recebe como entrada um esquema ER S
E e gera uma sequ^encia de comandos na LDD do sistema Informix para criar o esquema
correspondente a representac~ao relacional otimizada de S
E. Na fase de reprojeto, a ferramenta
recebe como entrada uma lista de comandos de reprojeto especicando modicac~oes sobreS E, e
gera uma sequ^encia de comandos da LDD/LMD do sistema Informix que constituem um plano de reprojeto para reestruturac~ao do banco de dados.
Na vers~ao atual, a ferramenta recebe o esquema ER e os comandos de reprojeto em formato textual. Dessa forma, uma extens~ao importante seria a criac~ao de uma interface graca que possibilitasse ao projetista criar e modicar o esquema ER e interagir com os resultados obtidos pela aplicac~ao do metodo.
Outra extens~ao poderia ser o desenvolvimento de vers~oes especcas da ferramenta para outros SGBDs relacionais, tais como Oracle e DB2. E importante observar que, para o de-senvolvimento dessas vers~oes, n~ao e necessario implementar novamente toda a ferramenta, mas apenas os modulos que geram os comandos da LDD/LMD do sistema alvo, ja que, de acordo com o metodo adotado, todo processo de projeto/reprojeto de um banco de dados e efetuado manipulando-se estruturas independentes de caractersticas de implementac~ao, que s~ao o grafo ER e a oresta de colapsamento [Silv95, SiLC96].
Finalmente, e importante ressaltar que a implementac~ao dessa ferramenta para um SGBD relacional comercial demonstrou a aplicac~ao pratica do metodo de projeto/reprojeto proposto em [CaTL90, CaTL93] e detalhado em [Silv95]. Neste contexto, destacaramos as estrategias propostas para implementac~ao das depend^encias de inclus~ao e de nulos atraves de restric~oes de integridade referencial, gatilhos e condic~oes de vericac~ao (clausulas CHECK), mecanismos encontrados na maioria dos SGBDs relacionais disponveis comercialmente.
Refer^encias
[AlAL85] Albano, A., de Antonellis, V. and di Leva, A., Computer-Aided Database Design: The DATAID Projet, North-Holland, Amsterdan (1985).
[BaCN92] Batini, C., Ceri, S. and Navathe, S. Conceptual Database Design: An Entity-Relationship Approach, Benjamin Cummings, San Mateo, California (1992).
[Butl96] Butler, B. \ER Diagramming Tools: Power Through Perspective", PC Magazine 15, 10 (May 1996).
[CaTL90] Casanova, M.A., Tucherman, L. and Laender, A.H.F. \Algorithms for Designing and Maintaining Optimized Relational Representations of Entity-Relationship Schemas", Proc. 9th Int'l Conf. on Entity-Relationship Approach - ER'90, Lausanne, Switzerland (Oct. 1990).
[CaTL93] Casanova, M.A., Tucherman, L. and Laender, A.H.F. \On the Design and Main-tenance of Optimized Relational Representations of Entity-Relationship Schemas", Data and Knowledge Engineering 11, 1 (1993).
[ElNa94] Elmasri, R. and Navathe, S. Fundamentals of Database Systems, 2nd Ed., Benjamin Cummings, San Mateo, California (1994).
[Ferr97] Ferreira, A. A. \Uma Ferramenta para Projeto e Reprojeto de Bancos de Dados Relacionais", Dissertac~ao de Mestrado, Departamento de Ci^encia da Computac~ao, UFMG, Belo Horizonte (Mar. 1997).
[Fran96] Frank, M. \The Evolution of Client/Server CASE", DBMS 9, 1 (Jan. 1996).
[HEH+94] Hainaut, J.L., Englebert, V., Henrard, J., Hick, J.M. and Roland, D. \Database Evolution: the DB-MAIN Approach", Proc. 13th Conf. on the Entity-Relationship Approach, Manchester, UK (Dec. 1994).
[Info91] Informix Guide to SQL, Reference Manual, Informix Software, Inc (1991).
[MaSh92] Markowitz, V. M. and Shoshani, A. \Representing Extended Entity-Relationship Structures in Relational Databases: a Modular Approach", ACM Transactions on Database Systems 17, 3 (Sept.1992).
[MaSh94] Markowitz, V.M. and Shoshani, A. \An Overview of the Lawrence Berkeley La-boratory Extended Relationship Tools", Proc. 13th Int'l Conf. on Entity-Relationship Approach, Manchester, UK (Dec. 1994).
[SiLC94] Silva, A.S., Laender A.H.F., Casanova M.A. \Sobre a Manutenc~ao da Correc~ao Sem^antica de Representac~oes Relacionais de Esquemas ER", Anais do 9. Simposio Brasileiro de Banco de Dados, S~ao Carlos, Brasil (Set. 1994).
[Silv95] Silva, A.S. \Uma Contribuic~ao para o Problema de Manutenc~ao de Representac~oes Relacionais Otimizadas de Esquemas Entidade-Relacionamento", Dissertac~ao de Mes-trado, Departamento de Ci^encia da Computac~ao, UFMG, Belo Horizonte (Fev. 1995). [SiLC96] Silva, A.S., Laender, A. H. F. and Casanova, M.A. \An Approach to Maintaining Optimized Relational Representations of Entity-Relationship Schemas", Proc. 15th Int'l Conf. on Conceptual Modeling - ER'96, Cottbus, Germany (Oct. 1996).
