Transformações entre Modelos
Maria Claudia Cavalcanti
IME
Base Bibliog.
Projeto de Banco de Dados – Carlos Heuser
Conceptual Database Design – Batini, Ceri, Navathe
Qual é o ponto de partida?
Esquema
Conceitual
Modelagem lógica
Engenharia reversa
Esquema
Lógico
Qual é o ponto de partida?
Esquema
ER
Mapeamento
ER-Relacional
Engenharia reversa
Esquema
Relacional
Mapeamento ER-Relacional
Diferentes esquemas relacionais podem ser gerados
Refinamentos do esquema relacional gerado são
oriundos do conhecimento maior sobre a aplicação,
inclusive sobre seu desempenho
conhecimento da
aplicação
Refinamento do
esquema
relacional
Esquema
ER
Mapeamento
ER-Relacional
Esquema
Relacional
Mapeamento do E-R para Relacional
Metodologia
–
Eliminação dos identificadores externos;
–
Eliminação de atributos compostos e multi-valorados;
Tradução de cada entidade em uma relação;
–
–
Tradução de cada relacionamento :
(a) relacionamentos n:m requerem uma relação separada
(b) relacionamentos 1:n, 1:1 podem ser modelados pela
adição de atributos às relações já existentes.
–
Tradução de Generalização/Especialização
Eliminação de Identificadores Externos
Transformá-los em identificadores internos no modelo relacional
Universidade
Código_Universidade
Nome
Cidade
Universidade
Código_Universidade
Nome
Cidade
Matrícula
Código_Universidade
Estudante
Número_Estudante
Nome_Estudante
Estudante
Número_Estudante
Nome_Estudante
Idade
Idade
A chave da entidade externa
foi importada para ESTUDANTE
Eliminação de Atributos Compostos
Pessoa
Nome
Idade
Endereço
Pessoa
Pessoa
Rua
CEP
Estado
Nome
Idade
Rua
CEP
Estado
Nome
Idade
Endereço
Eliminação do nível
intermediário
Detalhes são deixados para
a aplicação
Eliminação de Atributos Multivalorados
Entidades
Produto
Produto
Código_Produto
Códigos_de_Materiais (1,n)
Descrição
Preço
Código_Produto
Descrição
Preço
+
Produto__Material
Código_Produto
Código_de_Material
Introdução de nova entidade.
Uma para cada atributo
multi-valorado.
Eliminação de Atributos Multivalorados
Relacionamentos
Instrutor
Código_Instrutor
Departamento
Telefone
(0,m)
(0,m)
Oferece
Instrutor
Número_Max_Estudantes
Trimestre (1,n)
Oferece
Código_Instrutor
Departamento
Telefone
Número_Max_Estudantes
(1,m)
Curso
(1,n)
Curso
Número_Curso
Número_Curso
+
Oferta_de_Cursos
Tradução de Entidade
Empregado
CPF
Nome
Salário
Empregado (CPF, Nome, Salário)
Código_Instrutor
Número_Curso
Trimestre
Tradução de Relacionamentos 1:1
Cliente
(1,1)
(1,1)
tem
Nome
Código_Cliente
Carregamento
Endereço
Código_Cliente
Agrupando numa mesma relação
CLIENTE_CARREGAMENTO (Código_Cliente, Nome, Endereço)
Ambas as entidades têm o mesmo identificador
E se as entidades tiverem diferentes identificadores?
Tradução de Relacionamentos 1:1
CLIENTE
(0,1)
POSSUI
(1,1)
LIMITE_CRÉDITO
CARTÃO_DE_
NÚMERO_CARTÃO
CRÉDITO
TIPO_CARTÃO
NOME
CÓDIGO_CLIENTE
Agrupando em relações distintas
CLIENTE (CÓDIGO_CLIENTE, NOME)
*Uma
das entidades tem
participação opcional
CARTÃO_DE_CRÉDITO (TIPO_CARTÃO, NÚMERO_CARTÃO, LIMITE)
POSSUI (TIPO_CARTÃO, NÚMERO_CARTÃO, CÓDIGO_CLIENTE)
As entidades têm identificadores diferentes
(Qual seria ainda outra alternativa neste caso?)
Tradução de Relacionamentos 1:1
DATA
REGIME
(0,1)
HOMEM
HOM_CPF
CASAMENTO
(0,1)
MULHER
MLH_CPF
NOME
NOME
HOMEM (HOM_CPF, NOME)
*
Ambas as entidades têm
participação opcional
MULHER (MLH_CPF, NOME)
CASAMENTO (HOM_CPF, MLH_CPF, DATA, REGIME)
Que observação pode ser feita sobre a chave primária de CASAMENTO?
Tradução de Relacionamentos 1:n
CIDADE
NOME_CIDADE
POPULAÇÃO
(1,1)
PERTINÊNCIA
Ambas entidades têm
participação
obrigatória
(1,n)
ESTADO
NOME_ESTADO
GOVERNADOR
POPULAÇÃO
CIDADE (NOME_CIDADE, NOME_ESTADO, POPULAÇÃO)
ESTADO (NOME_ESTADO, GOVERNADOR, POPULAÇÃO)
Tradução de Relacionamentos 1:n
VENDEDOR
NOME
TELEFONE
(1,n)
DESCONTO
PREENCHE
participação opcional
(0,1)
PEDIDO
NÚMERO_PEDIDO
DATA
VENDEDOR (NOME_VEND, TELEFONE)
PEDIDO (NÚMERO_PEDIDO, DATA)
PREENCHE (NÚMERO_PEDIDO, NOME_VEND, DESCONTO)
Tradução de Relacionamentos n:m
ESTUDANTE
NÚMERO_ESTUDANTE
NOME_ESTUDANTE
(1,n)
SEMESTRE
MATRÍCULA
GRAU
Independe
das
!
cardinalidades
(1,m)
CURSO
NÚMERO_CURSO
NOME_CURSO
ESTUDANTE (NÚMERO_ESTUDANTE, NOME_ESTUDANTE)
CURSO (NÚMERO_CURSO, NOME_CURSO)
MATRÍCULA (NÚMERO_ESTUDANTE, NÚMERO_CURSO, SEMESTRE, GRAU)
Tradução de Relacionamentos n-ários
COD_PEÇA
PEÇA
DESCRIÇÃO
(1,n)
COD_FORN
NOME
ENDEREÇO
TELEFONE
FORNECEDOR
(1,n)
(1,n)
PROJETO
COD_PROJETO
NOME
DATA_ABERT
QTDE
PEÇA (COD_PEÇA, DESCRIÇÃO)
FORNECEDOR (COD_FORN, NOME, ENDEREÇO, TELEFONE)
PROJETO (COD_PROJETO, NOME, DATA_ABERT)
FORNECIMENTO (COD_PEÇA, COD_FORNECEDOR, COD_PROJETO, QTDE)
E se um dado par peça-projeto só pudesse ser fornecido por um único fornecedor?
Tradução de Auto Relacionamentos
REG_EMP
NOME
DATA_NASC
EMPREGADO
(0,n)
(0,n)
É_COORDENADO
É_COORDENADOR
COORDENA
EMPREGADO (REG_EMP, NOME, DATA_NASC)
COORDENA (REG_SUB, REG_COORDENADOR)
E se o relacionamento for 1:n?
Chaves Primárias
A princípio o atributo identificador da entidade é
considerado como chave primária. No entanto,
atualmente, há alternativas.
A escolha fica entre usar uma chave natural ou uma
chave artificial
–
–
Chave natural (Natural key): chave indicada a partir da análise do
domínio (atributo(s) identificadores da entidade)
Chave artificial (Surrogate key): chave que pode ser criada
artificialmente para identificar unicamente as tuplas de uma
relação, sem significado para o domínio em questão.
Wieringa and De Jonge (1991) definem Surrogate Key como
“ … an object in the database itself. The surrogate is internally generated
by the system and is invisible to the user or application.”
Chaves Primárias
Surrogate keys podem também ser chamadas de
–
–
–
–
–
–
–
Internal identifier,
system-generated key,
database sequence number,
synthetic key,
factless key,
technical key, or
arbitrary unique identifier.
SURROGATE
to put in the place of another: a : to appoint as successor,
deputy, or substitute for oneself b : SUBSTITUTE
Chaves Primárias
–
Chave natural (Natural key):
Vantagens:
identifica naturalmente a relação
Buscas normalmente com base em chaves naturais
– Evita a criação de algo extra
– Exemplos: CPF, código do título de eleitor
–
–
Desvantagens:
Sujeita a mudanças do negócio ou domínio em questão: ao mudarem os
valores da chave natural, todas as referências a cada valor da chave
primária (nas demais tabelas) vão precisar ser alteradas;
– Exemplos:
email - usuário pode querer mudar de email,
códigoEmpregado – uma empresa identifica seus empregados
unicamente por um código, mas ao fundir-se com outra empresa
passa a ter outra codificação que respeitar.
–
Chaves Primárias
–
Chave artificial (Surrogate key):
Vantagens:
–
Não está sujeita às mudanças de negócio: alterações na chave
natural ficam restritas a uma única tabela
Desvantagens:
–
–
–
–
Mais um atributo
Manutenção de valores únicos é uma preocupação do próprio
SGBD ao gerar os números novos a cada inclusão
Como não fazem parte do domínio as SK não são entendíveis
pelos humanos.
Tem-se que manter as chaves candidatas (alternativas) para
acesso direto: busca e visualização do usuário.
Qual usar?
Chaves Primárias
Implementações de Surrogate keys
•
•
•
•
•
•
•
•
•
Universally Unique Identifiers (UUIDs)
Globally Unique Identifiers (GUIDs)
Object Identifiers (OIDs)
Sybase or SQL Server identity column
Oracle SEQUENCE
PostgreSQL serial
MySQL AUTO_INCREMENT
AutoNumber data type in Microsoft Access
AS IDENTITY GENERATED BY DEFAULT in IBM DB2
Chaves Primárias
Ao optar por usar chaves artificiais (surrogate
keys) como chaves primárias, declarar chaves
candidatas
–
–
Estendendo a notação resumida de C. Heuser
Exemplo:
Empregado (IdEmp, matrícula, nome, idCargo, …),
idCargo referencia Cargo,
matricula é chave candidata (única e não nula);
Tradução de Hierarquias
Há duas alternativas principais:
–
Fusão:
–
Fundir todas as entidades em uma única relação
Distribuição:
Uma tabela para cada entidade específica
e uma tabela pra a entidade genérica
Tradução de Hierarquias
Fusão
–
–
–
–
–
Chave primária fica sendo a da entidade mais
genérica
Incluir uma coluna tipo para manter a informação
sobre a especialização
Passar todos os atributos das entidades
especializadas para entidade genérica
Fazer o mesmo para os relacionamentos
Aplicar as regras já conhecidas
Traduzindo Hierarquias
(0,n)
LOTACAO
(1,1)
Código
Descrição
nome
DEPTO
Codigo
nome
EMPREGADO
CREA
SECRETARIA
(1,1)
(1,n)
DOMINIO
Habilitacao
PERTENCE
(0,n)
PARTICIPACAO
(0,n)
(0,n)
Código
Descrição
nome
SW
(0,n)
ENGENHEIRO
MOTORISTA
RAMO
ENGENH
Código
Descrição
nome
PROJETO
Traduzindo Hierarquias - fusão
(0,n)
LOTACAO
CREA
Habilitacao
Codigo
nome
tipo
(1,1)
EMPREGADO
(1,n)
Código
Descrição
nome
(0,n)
(1,1)
DOMINIO
PERTENCE
(0,n)
SW
DEPTO
PARTICIPACAO
(0,n)
(0,n)
Código
Descrição
nome
RAMO
ENGENH
Código
Descrição
nome
PROJETO
Código
Descrição
nome
Código
Descrição
nome
Traduzindo Hierarquias – fusão
EMPREGADO(codEmp, nome, tipo, crea, habilitacao, codRamo, codDepto)
codRamo referencia RAMOENG
codDepto referencia DEPTO
DEPTO (codDepto, nome, desc)
SW (codSW, nome, desc)
Pergunta-se:
RAMOENG(codRamo, nome,desc)
Considerando que a especialização
PROJETO (codProj, nome, desc)
de Empregado é total e exclusiva,
DOMINIO(codSW, codEmp);
qual seria o domínio de tipo?
codSW referencia SW
codEmp referencia EMPREGADO
O que seria necessário verificar para
PARTICIPACAO (codEmp, codProj)
manter a integridade dos dados em
codProj referencia PROJETO
relação às regras de negócio?
codEmp referencia EMPREGADO
E se a especialização fosse inclusiva?
Tradução de Hierarquias - distribuição
Distribuição
–
–
–
–
Chave primária repete-se em cada relação
Nas relações específicas, a chave primária é também
chave estrangeira para a relação genérica
Incluir uma coluna tipo na relação genérica para
manter a informação sobre a especialização
Aplicar as regras já conhecidas
Traduzindo Hierarquias - distribuição
EMPREGADO(codEmp, nome, tipo, codDepto)
codDepto referencia DEPTO
MOTORISTA(codEmp, habilitacao)
codEmp referencia EMPREGADO
ENGENHEIRO (codEmp, CREA, codRamo)
codEmp referencia EMPREGADO
SECRETARIA (codEmp)
codEmp referencia EMPREGADO
DEPTO (codDepto, nome, desc)
SW (codSW, nome, desc)
RAMOENG(codRamo, nome,desc)
PROJETO (codProj, nome, desc)
DOMINIO(codSW, codEmp);
codSW referencia SW
codEmp referencia SECRETARIA
PARTICIPACAO (codEmp, codProj)
codProj referencia PROJETO
codEmp referencia ENGENHEIRO
Pergunta-se:
Porque codEmp, nas relações
específicas, deve ser chave
estrangeira para EMPREGADO?
Porque é importante manter o
atributo tipo na relação genérica?
O que seria necessário verificar
para manter a integridade dos
dados em relação às regras de
negócio?
O que ocorre quando a
especialização é total e inclusiva?
Traduzindo Hierarquias
Tabela única
–
–
Todos os dados em uma única tabela... Não há necessidade de
navegação por outras tabelas para se obter todos os dados a
respeito de um empregado
Economia de espaço por um lado pois a chave primária não é
duplicada, mas há desperdício por outro lado com os nulos que
os campos das tabelas especificas trazem
Várias tabelas
–
–
Conceitos explicitos em relações próprias
Redução de nulos
Há uma terceira alternativa?
Cada caso é um caso!
Nomes
Nomes simples e reduzidos
–
Ex data de nascimento ---> dataNasc
nao usar o nome da tabela
–
–
a não ser em campos parte de chaves primárias
Ex:codigo ----> codEmp
Exercício
nome
12 entidades
13 relacionamentos
dealer
(1,N)
nome
(1,1)
projetista
franchise
(1,N)
(1,1)
nome
(1,1)
(1,1)
(1,N)
(1,N)
(1,1)
(1,M)
(1,N)
(1,N)
(1,1)
(1,1)
(1,N)
entrega
mes
côr
côr
(1,N)
modelo
(1,N)
competidor
(1,1)
v_max
configuração
(1,N)
(1,1)
carro
carroceria
(1,1)
motor
(1,N)
mercado
(1,1)
preço
(1,N)
num
(1,1)
capacidade
(1,N)
nome
estilo
título
Exercício
nome
dealer
(1,N)
nome
(1,1)
projetista
franchise
(1,N)
(1,1)
(1,1)
(1,N)
modelo
(1,N)
nome
(1,1)
(1,N)
(1,N)
(1,1)
(1,M)
(1,N)
(1,N)
(1,1)
entrega
mes
côr
côr
competidor
(1,1) v_max
configuração
(1,1)
(1,N)
(1,1)
motor
(1,1)
(1,N)
carroceria
(1,1)
(1,N)
mercado
(1,1)
preço
(1,N)
num
carro
título
(1,N)
capacidade
nome
estilo
Exercício
nomedealer
mes
franchise
entrega
nomemodelo
mes
nomemodelo
nome
nomeprojetista
nomemodelo
configuração
motor
côr
nomemodelo
v_max
preço
nummotor
num
(M,N)
modelo
carro
nummotor
capacidade
competidor
nomemodelo
Nome
estilo
carroceria
nomeestilo
título
mercado
mercado
título
Exercício
ESQUEMA FINAL:
ENTREGA ( mes, modelo )
FRANCHISE ( dealer, mes, modelo )
MODELO ( modelo, projetista )
MODELO-CÔR ( modelo, cor )
CONFIGURAÇÃO ( modelo, motor, v_max )
CARROCERIA ( modelo, estilo, mercado )
MERCADO ( mercado, competidor )
MOTOR ( motor, capacidade )
CARRO ( modelo, estilo, motor, preço )