Aula 7 - Transformacao entre modelos ER Relacional

Prévia do material em texto

Transformação entre modelos
(ER  Relacional)
Banco de dados 1
Fabrício Nogueira
fabricio.silva@uva.br
Objetivo
• Transformar um modelo conceitual em um projeto lógico relacional
Projeto lógico de 
BD relacional
Engenharia reversa 
de BD relacional
Modelo ER
(nível conceitual)
Modelo 
relacional
(nível lógico)
Projeto lógico
• Um modelo ER pode gerar diversos modelos relacionais
• Cada versão do modelo relacional pode ser considerado uma 
implementação correta do modelo ER
• O que diferencia uma implementação das outras?
• Diferentes desempenhos 
• Maior facilidade ou dificuldade de desenvolvimento e manutenção do sistema
Projeto lógico
• Como transformar de ER para relacional?
Modelo ER
(nível conceitual)
Transformação ER 
para relacional
Modelo 
relacional 
(nível lógico)
Refinamento
Conhecimento 
sobre a aplicação
Transformação ER para relacional
• Objetivos básicos
• Bom desempenho de instruções de consultas e alteração dos dados
• Número de acessos a disco
• Simplificar o desenvolvimento e manutenção das aplicações
• Ocupar menos espaço em disco
Transformação ER para relacional
• Princípios básicos para definição das regras de transformação
• Evitar junções
• Diminuir o número de chaves
• Evitar campos opcionais que dependem de outros
• Ex.: Se for casado, preencher os campos A, B e C
Transformação ER para relacional
• Exemplo
• Cliente(CodCliente, Nome, NomeContato, Endereço, Telefone)
OU
• Cliente (CodCliente, Nome, NomeContato)
• EnderecoCliente (CodCliente, Endereco, Telefone) CodCliente referencia 
Cliente
Criação de índice
Criação de índice
Criação de índice
Processos da transformação
• Implementação inicial das entidades e seus atributos
• Implementação dos relacionamentos e seus atributos
• Implementação de generalizações/especializações
Implementação inicial das entidades
• Entidade  Tabela
• Atributo  Coluna
• Atributos identificadores  Colunas que compõem a chave primária
• Pessoa (CodigoPess, Nome, Endereco, DataNasc, DataAdm)
PESSOA
CÓDIGO
NOME
ENDEREÇO
DATA DE ADMISSÃO
DATA DE NASCIMENTO
Nomes de atributos e colunas
• Não é aconselhável transcrever o nome dos atributos para nomes de 
colunas
• Atributos do DER são em linguagem natural
• Colunas do projeto relacional são para uso do SGBD e das aplicações
• Não usar espaços em branco e caracteres especiais
• Nomes de colunas não necessitam conter o nome da tabela
• Usar Nome ao invés de NomeAluno, NomeCliente, NomeEmpregado
• Dependendo do nome da chave primária, usa-se também o nome da 
tabela
• idCliente, idEmpregado, idAluno, codigoCidade, codigoProfessor, codUF, 
codProduto
Relacionamento identificador
• Para cada relacionamento identificador, cria-se uma chave estrangeira na tabela que 
implementa a entidade fraca referenciando a tabela que implementa a entidade forte.
• A chave primária que implementa a entidade fraca, possui colunas que representam os 
atributos identificados e chaves estrangeiras para a entidade forte
Dependente (CodigoEmp, NoSeq, Nome)
Empregado
Nome
Código
Dependente
Nome
Número de 
sequencia
(0,n)(1,1)
Relacionamento identificador
• Grupo (CodGrupo, Nome)
• Empresa (CodGrupo, NoEmpresa, Nome)
• Empregado (CodGrupo, NoEmpresa, NoEmpregado, Nome)
• Dependente (CodGrupo, NoEmpresa, NoEmpregado, NoSeq, Nome)
Grupo
Nome
Código
Empresa
Nome
Número de 
empresa
(0,n)(1,1)
Empregado
Nome
Número de 
empregado
Dependente
Nome
Número de 
sequencia
Implementação de relacionamentos
• Alternativas básicas
• Tabela própria
• Adição de colunas em uma das tabelas
• Fusão de tabelas
• A cardinalidade (máxima e mínima) irá determinar qual alternativa 
usar
Implementação de relacionamentos
• Tabela própria
• Colunas contendo os identificadores das entidades relacionadas
• Colunas contendo os atributos do relacionamento
Engenheiro
Nome
Código
Projeto
Título
Código
(0,n)(0,n)
Função
Engenheiro (CodEng, Nome)
Projeto (CodProj, Titulo)
Atuacao (CodEng, CodProj, Funcao)
CodEng referencia Engenheiro
CodProj referencia Projeto
Implementação de relacionamentos
• Tabela própria
Engenheiro
CodEng Nome
1 Souza
2 Silva
3 Santos
Projeto
CodProj Título
A Consultoria
B Construção
C Minério
D Automação
Atuação
CodEng CodProj Funcao
1 A Consultor
1 B Calculista
1 D Analista
2 C Consultor
3 C Gerente
1
n
Implementação de relacionamentos
• Tabela própria
Engenheiro
CodEng Nome
1 Souza
2 Silva
3 Santos
Projeto
CodProj Título
A Consultoria
B Construção
C Minério
D Automação
Atuação
CodEng CodProj Funcao
1 A Consultor
1 B Calculista
1 D Analista
2 C Consultor
3 C Gerente
n
1
Implementação de relacionamentos
• Adição de colunas
• Em uma das tabelas correspondentes às entidades que participam do 
relacionamento
• Somente quando uma das entidade possui cardinalidade máxima 1
• Inserir na tabela que representa a entidade oposta àquela com cardinalidade 
máxima 1 as colunas:
• Que representam o identificador da entidade relacionada
• Que representam aos atributos do relacionamento
Implementação de relacionamentos
• Adição de colunas
Departamento
Nome
Código
Empregado
Nome
Código
Lotação
(0,n)(1,1)
Data lotação
Departamento (CodDept, Nome)
Empregado (CodEmp, Nome, CodDept, DataLota)
CodDept referencia Departamento
Implementação de relacionamentos
• Fusão de tabelas
• Entidades envolvidas no relacionamento compõem uma única tabela
• Somente pode ser aplicada quando o relacionamento é do tipo 1:1
Conferência
Nome
Código
Comissão
Endereço
Organização
(1,1)(1,1)
Data instalação
Conferência (CodConf, Nome, DataInstComOrg, EnderecoComOrg)
Implementação de relacionamentos
• Fusão de tabelas
• Entidades envolvidas no relacionamento compõem uma única tabela
• Somente pode ser aplicada quando o relacionamento é do tipo 1:1
Empregado (CodEmp, Nome, DataAlocacao, NoMesa)
Empregado MesaAlocação
1 1
Nome
Código
NúmeroData de
alocação
Detalhes de implementação
Cardinalidade Tabela própria Adição de coluna Fusão de tabelas
Relacionamentos 1:1
(0,1) .. (0,1) +/- V X
(0,1) .. (1,1) -/+ +/- V
(1,1) .. (1,1) -/+ +/- V
Relacionamentos 1:n
(0,1) .. (0,n) +/- V X
(0,1) .. (1,n) +/- V X
(1,1) .. (0,n) -/+ V X
(1,1) .. (1,n) -/+ V X
Relacionamento n:n
(0,n) .. (0,n) ou (0,n) .. (1,n) ou (1,n) .. (1,n) V X X
Detalhes de implementação
• Relacionamentos 1:1 (0,1 .. 0,1 – ambas entidades possuem participação opcional)
• Solução com adição de coluna
• Mulher (IdentM, Nome, IdentH, Data, Regime) IdentH referencia Homem
• Homem (IdentH, Nome)
Homem MulherCasamento
(0,1) (0,1)
Nome Identidade Regime Nome IdentidadeData
Escolha arbitrária da tabela 
Mulher. Poderia ter sido a 
outra.
Detalhes de implementação
• Relacionamentos 1:1 (0,1 .. 0,1 – ambas entidades possuem participação opcional)
• Solução com tabela própria
• Mulher (IdentM, Nome)
• Homem (IdentH, Nome)
• Casamento (IdentM, IdentH, Data, regime) IdentM referencia Mulher, IdentH referencia 
Homem
Homem MulherCasamento
(0,1) (0,1)
Nome Identidade Regime Nome IdentidadeData
Detalhes de implementação
• Relacionamentos 1:1 (0,1 .. 0,1 – ambas entidades possuem participação opcional)
• Fusão de tabelas
• Tabela (IdentM, NomeM, IdentH, NomeH, Data, regime)
• Não se aplica, pois Homem e Mulher são opcionais.
• Qual seria a chave primária?
Homem MulherCasamento
(0,1) (0,1)
Nome Identidade Regime Nome IdentidadeDataDetalhes de implementação
• Relacionamentos 1:1 (0,1 .. 1,1 – uma entidade opcional e outra obrigatória)
• Fusão de tabelas
• Correntista (CodCorrentista, Nome, CodCartao, DataExp)
Correntista Cartão
(1,1) (0,1)
Nome Código Data de 
expiração
Número
Detalhes de implementação
• Relacionamentos 1:1 (0,1 .. 1,1 – uma entidade opcional e outra obrigatória)
• Adição de colunas
• Correntista (CodCorrentista, Nome)
• Cartão (CodCartao, DataExp, CodCorrentista) CodCorrentisra referencia Correntista
Correntista Cartão
(1,1) (0,1)
Nome Código Data de 
expiração
Número
Detalhes de implementação
• Relacionamentos 1:1 (1,1 .. 1,1 – Ambas as entidades com participação obrigatória)
• Fusão de tabelas
• Conferencia (CodConf, Nome, DataInstalacaoComOrg, EnderecoComOrg)
Conferência
Nome
Código
Comissão
Endereço
Organização
(1,1)(1,1)
Data instalação
Detalhes de implementação
• Relacionamentos 1:n
• Alternativa preferida: Adição de colunas
• Edificio (CodigoEd, Endereco)
• Apartamento (CodigoEd, NumeroAp, AreaAp) CodigoEd referencia Edificio
Edifício
Endereço Código
Apartamento
Número
(1,n)(1,1)
Área
Por ser um relacionamento 
identificador, a chave 
estrangeira faz parte da chave 
primária
Detalhes de implementação
• Relacionamentos 1:n
• Alternativa preferida: Adição de colunas
• Financeira (CodFin, Nome)
• Venda (Idvenda, Data, CodFin, NoParc, TxJuros) CodigoFin referencia 
Financeira
Financeira
Nome Código
Venda
Id
Finamciamen
to
(0,n)(0,1)
Taxa de juros
N parcelas
Detalhes de implementação
• Relacionamentos 1:n
• Alternativa: Tabela própria
• Financeira (CodFin, Nome)
• Venda (IdVenda, Data)
• Financiamento (Idvenda, CodFin, NoParc, TxJuros) IdVenda referencia Venda, 
CodFin referencia Financeira
Financeira
Nome Código
Venda
Id
Finamciamen
to
(0,n)(0,1)
Taxa de juros
N parcelas
Desvantagens:
Junções entre venda e
financiamento
Dois índices para a
mesma chave
Detalhes de implementação
• Relacionamentos n:n
• Sempre implementados por tabela própria
Engenheiro
Nome
Código
Projeto
Título
Código
(0,n)(0,n)
Função
Engenheiro (CodEng, Nome)
Projeto (CodProj, Titulo)
Atuacao (CodEng, CodProj, Funcao)
CodEng referencia Engenheiro
CodProj referencia Projeto
Generalização/Especialização
1. Tabela única
• Chave primária do atributo identificador da entidade genérica
• Coluna “Tipo” para identificação da entidade especializada
• Colunas para os atributos da entidade genérica
• Colunas para os relacionamentos da entidade genérica, se a solução usar adição de 
colunas
• Colunas para os atributos das entidades especializadas (devem ser opcionais)
• Colunas para os relacionamentos das entidades especializadas, se implementadas via 
adição de colunas
2. Uma tabela para cada entidade
1. Cada Entidade  Tabela
2. Compartilham o mesmo valor de chave primária
Generalização/Especialização
• Tabela única Empregado
(CodEmp, Tipo, Nome, CPF, CodDept, CartHabil, Crea, CodRamo)
CodDept referencia Depto
CodRamo referencia Ramo
Depto (CodDepto, Nome)
Ramo (CodRamo, Nome)
ProcTexto (CodProc, Nome)
Dominio (CodEmp, CodProc)
CodEmp referencia Empregado
CodProc referencia ProcTexto
Projeto (CodProj, Nome)
Participacao (CodEmp, CodProj)
CodEmp referencia Empregado
CodProj referencia Projeto
Comuns Especializados
Tabelas próprias 
que não geram 
colunas em 
Empregado
Opcionais
Generalização/Especialização
• Uma tabela por entidade Empregado (CodEmp, Tipo, Nome, CPF, CodDept)
CodDept referencia Depto
Motorista (CodEmp, CartHabil)
CodEmp referencia Empregado
Engenheiro (CodEmp, Crea, CodRamo)
CodEmp referencia Empregado
CodRamo referencia Ramo
Depto (CodDepto, Nome)
Ramo (CodRamo, Nome)
ProcTexto (CodProc, Nome)
Dominio (CodEmp, CodProc)
CodEmp referencia Empregado
CodProc referencia ProcTexto
Projeto (CodProj, Nome)
Participacao (CodEmp, CodProj)
CodEmp referencia Empregado
CodProj referencia Projeto
Comparti-
lhamento de 
chave primária
Chave primária 
e estrangeira
Generalização/Especialização
• Tabela única X Uma tabela para cada entidade
• Não realiza junções para obtenção de dados genéricos e especializações
• Armazenamento único de chave primária
• Colunas opcionais são apenas referentes aos atributos opcionais do ponto de 
vista da aplicação
Generalização/Especialização
• Subdivisão da entidade genérica Empregado (CodEmp, Tipo, Nome, CPF, CodDept)
CodDept referencia Depto
Motorista (CodEmp, Nome, CPF, CodDept, CartHabil)
CodDept referencia Depto
Engenheiro (CodEmp, Nome, CPF, CodDept, Crea, CodRamo)
CodDept referencia Depto
CodRamo referencia Ramo
Depto (CodDepto, Nome)
Ramo (CodRamo, Nome)
ProcTexto (CodProc, Nome)
Dominio (CodEmp, CodProc)
CodEmp referencia Empregado
CodProc referencia ProcTexto
Projeto (CodProj, Nome)
Participacao (CodEmp, CodProj)
CodEmp referencia Empregado
CodProj referencia Projeto
Tabelas 
independetes
Generalização/Especialização
• Subdivisão da entidade genérica
• A chave primária CodEmp não é estrangeira nas tabelas quem implementam 
as entidades especializadas
• Quando o tipo de especialização for total, não existirá uma tabela genérica 
(Empregado)
• Elimina os problemas de colunas opcionais da primeira alternativa
• Elimina as redundâncias de chave primária da segunda alternativa
• Desvantagem: 
• Dificulta-se a garantia de unicidade do empregado  Não garantido pelo SGBD
• O SGBD não garante integridade referencial ao conjunto de empregados como um todo
• Ex.: Auto-relacionamento entre empregado
Refinamentos do modelo relacional
• O processo de tradução leva a um banco de dados correto
• O banco de dados projetado não deve ser visto como final
• Algumas vezes as regras de tradução não levam à melhor alternativa 
em termos de desempenho e redundância de dados
Refinamentos do modelo relacional
• Redundância no banco de dados
Curso
Inscrito
Prova
Avali
ação
nome Data início
Data fim
código
número data
(1,1) (0,n)
(0,n)
(1,1)
(0,n)
(0,n)
número CPF nota
Refinamentos do modelo relacional
• Redundância no banco de dados
• Curso (CodCurso, Nome, DataIni, DataFim)
• Prova (CodCurso, NumeroProva, DataProva) CodCurso referencia Curso
• Inscrito (CodCurso, NumeroInscrito, cpf) CodCurso referencia Curso
• Avaliacao (CodCurso, NumeroProva, CodCurso, NumeroInscrito, Nota) 
(CodCurso, NumeroProva) referencia Prova
(CodCurso, NumeroInscrito) referencia Inscrito
Refinamentos do modelo relacional
• Redundância no banco de dados
• Curso (CodCurso, Nome, DataIni, DataFim)
• Prova (CodCurso, NumeroProva, DataProva) CodCurso referencia Curso
• Inscrito (CodCurso, NumeroInscrito, cpf) CodCurso referencia Curso
• Avaliacao (CodCurso, NumeroProva, NumeroInscrito, Nota) (CodCurso, 
NumeroProva) referencia Prova
(CodCurso, NumeroInscrito) referencia Inscrito
Refinamentos do modelo relacional
• Manutenção de informações redundantes no banco de dados
Voo Reserva
roteiro Número de reservas
código
número passageiro
(1,1) (0,n)
Refinamentos do modelo relacional
• Simulação de atributos multivalorados
Cliente Telefone
nome
código
número
(1,1) (0,n)
Cliente
nome Número de telefone (0,n)
código
Cliente (CodCli, Nome)
Telefone (CodCli, Numero) 
CodCli referencia Cliente
Refinamentos do modelo relacional
• Simulação de atributos multivalorados
• Raramente os clientes possuem mais de dois telefones
• Não há consultas usando o telefone como critério de seleção
• Melhor implementação: Cliente (CodCli, Nome, NumTel1, NumTel2)
• Obtenção mais rápida dos telefones• Não há a necessidade de criação de chave
• Menos espaço ocupado
• Consultas usando o telefone como critério de busca ficam mais complexas
Refinamentos do modelo relacional
• Relacionamentos mutuamente exclusivos
Pessoa física Venda
nome
cpf
(0,1) (0,n)
data
Pessoa 
Jurídica
número
cnpj Razao social
(0,n)
(0,1)
PessFis (CPF, Nome)
PessJur (CNPJ, RazSoc)
Venda (Num, data, CPF, CNPJ)
CPF referenca PessFis
CNPJ referencia PessJur
Refinamentos do modelo relacional
• Relacionamentos mutuamente exclusivos
• Não é possível especificar a chave estrangeira
• A integridade referencial passa a ser implementada pela aplicação
PessFis (CPF, Nome)
PessJur (CNPJ, RazSoc)
Venda (Num, data, CPF/CNPJ, TipoComprador)
CPF referenca PessFis
CNPJ referencia PessJur
Engenharia reversa de modelos relacionais
• Modelo relacional Modelo ER
• Banco de dados desenvolvido empiricamente
• Banco de dados evoluiu e o modelo ER não foi atualizado
• Etapas
• Identificação da construção ER correspondente a cada tabela
• Definição dos relacionamentos 1:n e 1:1
• Definição dos atributos
• Definição dos identificadores de entidades e relacionamentos
Engenharia reversa de modelos relacionais
• Identificação da construção ER correspondente a cada tabela
• Disciplina (CodDisc, Nome)
• Curso (CodCr, Nome)
• Curric (CodCr, CodDisc, Obr_Opc) 
CodCr referencia Curso
CodDisc referencia Disciplina
• Sala (CodPr, CodSl, Capacidade)
CodPr referencia prédio
• Predio (CodPr, Endereco)
• Turma (Anosem, CodDisc, SiglaTur, Capacidade, CodPr, CodSl)
CodDisc referencia Disciplina
(CodPr, CodSl) referencia Sala
• Laboratorio (CodPr, CodSl, Equipamento)
(CodPr, CodSl) referencia Sala
Engenharia reversa de modelos relacionais
• Identificação da construção ER correspondente a cada tabela
• Uma tabela pode corresponder a:
• Entidade
• Relacionamento n:n
• Entidade especializada
Engenharia reversa de modelos relacionais
• Identificação da construção ER correspondente a cada tabela
• Fatores determinantes
• Chave primária composta por mais de uma chave estrangeira  Relacionamento n:n
• Ex.: Curric (CodCr, CodDisc, Obr_Opc)
• Chave primária completa forma uma chave estrangeira  Entidade especializada
• Ex.: Laboratório (CodPr, CodSl, Equipamento)
• Demais casos  Entidade
• Ex.: Curso, Sala, Disciplina, Prédio e Turma
Engenharia reversa de modelos relacionais
• Identificação da construção ER correspondente a cada tabela
Turma
Sala
LaboratórioPrédio
Currículo
Disciplina
n
n
Engenharia reversa de modelos relacionais
• Definição de relacionamentos 1:n ou 1:1
• Toda chave estrangeira que não corresponde a um relacionamento n:n e nem 
a uma entidade especializada  relacionamento 1:1 ou 1:n
• Chaves estrangeiras que:
• Contêm colunas que não fazem parte de chaves primárias
• Fazem parte de uma chave primária, a chave primária não contém outras chaves 
estrangeiras e contém colunas que não são chaves estrangeiras
• Quando a chave estrangeira faz parte da chave primária  Relacionamento 
1:n
• Quando a chave estrangeira não faz parte da chave primária  Necessário 
avaliar os dados
Engenharia reversa de modelos relacionais
• Definição de relacionamentos 1:n ou 1:1
• Toda chave estrangeira que não corresponde a um relacionamento n:n e nem 
a uma entidade especializada  relacionamento 1:1 ou 1:n
• Chaves estrangeiras que:
• Contêm colunas que não fazem parte de chaves primárias
• Fazem parte de uma chave primária, a chave primária não contém outras chaves 
estrangeiras e contém colunas que não são chaves estrangeiras
• Quando a chave estrangeira faz parte da chave primária  Relacionamento 
1:n
• Ex.: Sala e Turma
• Quando a chave estrangeira não faz parte da chave primária  Necessário 
avaliar os dados
Engenharia reversa de modelos relacionais
• Identificação da construção ER correspondente a cada tabela
Turma
Sala
LaboratórioPrédio
Currículo
Disciplina
n
n
n
1
1
n
1n
Engenharia reversa de modelos relacionais
• Definição de atributos
• Cada coluna que não seja chave estrangeira  Atributo da 
entidade/relacionamento
• Chaves estrangeiras  Relacionamentos (Já mapeados)
Engenharia reversa de modelos relacionais
• Definição de atributos
Turma
Sala
LaboratórioPrédio
Currículo
Disciplina
n
n
n
1
1
n
1
n
capacidade
Ano/sem
sigla
código
capacidade
código
nome
equipamento
código
Ende-
reço
nome código
Obrigatória/Opcional
Engenharia reversa de modelos relacionais
• Definição de atributos identificadores
• Chave primária que não é chave estrangeira  Um atributo identificador da 
entidade/relacionamento
• Chave primária e que é chave estrangeira  Identificador externo 
(relacionamento identificador)
• Ex.: CodDisc em Turma e CodPr em Sala
Engenharia reversa de modelos relacionais
• Definição de atributos
Turma
Sala
LaboratórioPrédio
Currículo
Disciplina
n
n
n
1
1
n
1
n
capacidade
Ano/sem
sigla
código
capacidade
código
nome
equipamento
código
Ende-
reço
nome código
Obrigatória/Opcional