Tranformação ER

Transformação E-R para
Relacional
Exercício
 Abaixo aparece um esquema parcial para um banco de dados
relacional. Identifique neste esquema as chaves primárias e chaves
estrangeiras:
Aluno(CodigoAluno, Nome, CodigoCurso)
Curso(CodigoCurso, Nome)
Disciplina(CodigoDisciplina, Nome, Creditos,
CodigoDepartamento)
Curriculo (CodigoCurso, CodigoDisciplina,ObrigatoriaOpcional)
Conceito(CodigoAluno, CodigoDisciplina, AnoSemestre, Conceito)
Departamento(CodigoDepartamento, Nome)
Exercício
Considere um banco de dados com o seguinte
esquema:
Paciente(CodigoConvenio, NumeroPaciente, Nome)
CodigoConvenio referencia Convenio
Convenio (CodigoConvenio, Nome)
Medico(CRM, Nome, Especialização)
Consulta (CodigoConvenio, NumeroPaciente, CRM, Data_Hora)
(CodigoConvenio,NumeroPaciente) referencia Paciente
CRM referencia Medico
Explique que verificações devem ser feitas pelo SGBD para garantir integridade referencial nas
seguintes situações:




Uma linha é incluída na tabela Consulta
Uma linha é excluída da tabela Paciente
O código do CRM em uma linha de Consulta é alterado
O código do CRM em uma linha de Médico é alterado
Transformação Entre Modelos
Abordagem ER
 é voltada à modelagem de dados de forma independente
do SGBD considerado.
 Representação:
R
entidade1
entidade2
Abordagem Relacional
- Modela os dados para um SGBD relacional.
- Um modelo neste nível de abstração é chamado de
modelo lógico.
- Representação:
nometabela2(chaveprimária,atributo1,atributo2)
Atributo2 referencia nometabela1
Transformação ER -> Relacional
As regras para a transformação ER para relacional foram
definidas tendo em vista dois objetivos básicos:
 obter um banco de dados que permita boa performance
de instruções de consulta e alteração do BD (diminuir o
número de acesso a disco, já que este consome tempo na
execução de uma instrução em um BD);
 obter um BD que simplifique o desenvolvimento e a
manutenção de aplicações;
Transformação ER -> Relacional
Regras gerais
 Aplicáveis à maioria dos casos
 Há situações
 por exigências da aplicação, outros
mapeamentos são usados
 Implementadas em ferramentas CASE
Objetivos básicos:
 Boa performance
 Simplificar o desenvolvimento
Regras gerais de tradução:
A fim de alcançar estes objetivos, as regras gerais de
tradução foram definidas tendo por base, entre outros, os
seguintes princípios:
 Evitar junções;
 Diminuir o número de chaves primárias;
 Evitar/Diminuir o número de campos opcionais;
Junção: operação para buscar dados de diversas linhas associadas pela
igualdade dos campos.
Exemplo: buscar os dados de um empregado e os dados de seu
departamento (duas tabelas diferentes)
Regras gerais de tradução:
Evitar junções
Embora os SGBD procurem implementar a junção de
forma eficiente, ela envolve diversos acesso a disco;
Todos os dados de uma linha são trazidos para a memória
em uma operação de acesso a disco.
Isto significa que, uma vez encontrada uma linha da tabela,
seus campos estão todos disponíveis sem necessidade de
acesso adicionais a disco;
Preferível: ter os dados necessários a uma consulta
em uma única linha;
Embora as junções são implementações NORMAIS
em BD. Aqui a idéia é evitar junções desnecessárias.
Regras gerais de tradução:
Diminuir o número de chaves primárias
 Para implementação eficiente do controle da chave
primária, o SGBD usa uma estrutura de acesso
auxiliar, um índice para cada chave primária.
 Índices tendem a ocupar espaço considerável em
disco.
 Além disso, inserção ou remoção de entradas em um
índice podem exigir diversos acessos a disco;
Regras gerais de tradução:
Diminuir o número de chaves primárias
podendo escolher entre 2 alternativas de implementação,
uma na qual os dados aparecem em 1 única tabela e outra
na qual os mesmos dados aparecem em 2 ou mais
tabelas com a mesma chave primária, prefira a
implementação por uma única tabela.
Veja a seguir...
Regras gerais de tradução:
Usar implementações com menos chaves
Exemplo
1a alternativa
Cliente (CodCliente, nome, nomeContato, rua, cep, cidade, fone)
-> chave primária da tabela: CodCliente
2a alternativa
Cliente (CodCliente, Nome, NomeContato)
ClienteEnder(CodCliente, rua, cep, cidade, fone)
CodCliente referencia Cliente
-> cria, para cada tabela, uma chave primária (código de cliente)
(onde, as dias tabelas possuem exatamente as mesmas chaves
primárias, resultando em armazenamento e processamento
dobrado).
Regras gerais de tradução:
Evitar campos opcionais
 Campos opcionais = campos que podem assumir o valor
VAZIO (NULL em SQL).
 A princípio não há problemas em usar esse tipo de campo
porque o SGBD relacional não desperdiça espaço pelo fato
de campos de uma linha estarem vazios;
 Campos opcionais não tem influência na performance;
Uma situação conflitante é aquela na qual a
obrigatoriedade ou não do preenchimento de um campo
depende do valor de outros campos. Neste caso, o
controle da obrigatoriedade deve ser feito pelos
programas que acessam o BD.
Regras gerais de tradução:
Evitar campos opcionais
Controle de campo opcional pode complicar
programação
Verificar quais campos podem estar vazios, quando
isto depende do tipo de linha
Passos da transformação
ER para relacional
1. Tradução inicial de entidades e
respectivos atributos
2. Tradução de relacionamentos e
respectivos atributos
3. Tradução de generalizações/
especializações
Implementação inicial
de entidades
 Cada entidade é transformada em uma tabela
 Cada atributo da entidade define uma coluna desta
tabela
 Atributos identificadores -> compõem a chave primária
da tabela
Implementação inicial
de entidades
 Exemplo
nome
codpess
Pessoa
Pessoa (CodPess, Nome, DataNasc)
dataNasc
Nomes de colunas
 Referenciados freqüentemente em
programas e outras formas de texto em
computador
 Para diminuir o trabalho de programadores
 manter os nomes de colunas curtos.
 SGBD relacional
 nome de uma coluna não pode conter
brancos e ífens.
Nomes de atributos
e nomes de colunas
 Não transcrever os nomes de atributos para nomes
de colunas.
 Nomes de atributos compostos de diversas
palavras devem ser abreviados
 Nomes de colunas não necessitam conter o nome da
tabela
 Preferível usar o nome de coluna Nome a usar os
nomes de coluna NomePess ou NomePessoa
 SQL já exige muitas vezes forma
 Pessoa. Nome
Nome da coluna chave primária
 Chave primária
 pode aparecer em outras tabelas na forma de chave
estrangeira

Recomendável
 nomes das colunas que compõem a chave primária
 sufixados ou prefixados com o nome ou sigla da tabela
na qual aparecem como chave primária
 Exemplo
 CodigoPess
Implementação de
relacionamentos
 Três formas básicas de tradução:
 Tabela própria
 Adição de colunas a uma das tabelas
 Fusão de tabelas
O fator determinante para a tradução em
relacionamentos é a cardinalidade mínima e
máxima das entidades que participam do
relacionamento
Implementação de relacionamentos
1: 1
Quando ambas entidades têm participação
opcional... Em relacionamento binários 1:1
Homens
cpfh
nome
(0,1)
(0,1)
Casamento
Mulheres
CRes
Data
Regime
cpfm Nome
Alternativa preferida – Adição de colunas, e qualquer
uma das entidade pode ser escolhida.
Homens(cpfh,Nome,CRes)
Mulheres(cpfm,Nome, cpfh, data, regime)
Cpfh referencia Homens
Quando ambas entidades têm participação
opcional... Em relacionamento binários 1:1
Homens
cpfh
nome
(0,1)
(0,1)
Casamento
Mulheres
CRes
Data
regime
cpfm Nome
Alternativa que pode ser usada – Tabela própria
Homens(cpfh,Nome,CRes)
Mulheres(cpfm,Nome)
Casamento(cpfh, cpfm,Data,Regime)
cpfm referencia Mulheres
Cpfh referencia Homens
Quando ambas entidades têm participação
opcional... Em relacionamento binários 1:1
Homens
cpfh
nome
(0,1)
(0,1)
Casamento
Mulheres
CRes
Data
Regime
cpfm Nome
Fusão é inviável – cpfm e cpfh podem opcionais -> fere a regra da
PK
Casamento(cpfm, cpfh, data, regime, Nomeh, Nomem)
1: 1 - Quando ambas entidades têm participação
opcional
discussão
 Solução por fusão de tabelas é inviável
 Chave primária artificial
 Solução por adição de colunas melhor
 Menor número de junções
 Menor número de chaves
 Solução por tabela própria aceitável
Uma entidade tem participação opcional e a outra
obrigatória
Em relacionamento binários 1:1
- com Parcialidade em uma das entidades
(1,1)
Clientes
Codcli
Nome
Posse
(0,1)
Cartões Magnéticos
Nro
Alternativa preferida (fusão)
Clientes(Codcli,Nome,Nro,Data_exped)
Data_exped
Ruim -se existem
poucos clientes com
cartão
Uma entidade tem participação opcional e a outra
obrigatória
Em relacionamento binários 1:1
- com Parcialidade em uma das entidades
(1,1)
Clientes
Codcli
Nome
Posse
(0,1)
Cartões Magnéticos
Nro
Pode ser usada (Adição de colunas)
Clientes(Codcli,Nome)
CartõesMagnéticos(Nro, Codcli,Data_exped)
Codcli referencia clientes
Data_exped
Uma entidade tem participação opcional e a outra
obrigatória
Em relacionamento binários 1:1
- com Parcialidade em uma das entidades
(1,1)
Clientes
Codcli
Nome
Posse
(0,1)
Cartões Magnéticos
Nro
Tabela própria não recomendado
Clientes(Codcli,Nome)
CartõesMagnéticos(Nro,Data_exped)
CartõesClientes(Nro. Codcli,Nome, Data_exped
Codcli referencia clientes
Nro referencia CartõesMagneticos
Data_exped
1: 1 - opcional/ obrigatória
discussão
 Solução por tabela própria é pior que a solução por adição de
colunas
 Maior número de junções
 Maior número de índices
 Nenhum têm problema de campos opcionais
 Adição de colunas versus fusão de tabelas
 Fusão de tabelas é melhor em termos de número de
junções e número de chaves
 Adição de colunas em melhor em termos de campos
opcionais
 Fusão de tabelas é considerada a melhor e adição de
colunas é aceitável
Ambas Entidades têm participação
Obrigatória
Relacionamentos binários do tipo 1:1
Deve ser usado fusão de tabelas quando ambas entidades tem
participação obrigatória
(1,1)
(1,1)
Organização
Eventos
Code
Nome
Nro
Local Responsável
Comissões
Data_inst_comissão
Produz uma única tabela (sempre! ):
Eventos(Code,Nome,Data_inst_comissão,Local, Responsável_comissão)
ou
Comissões(Nro. Local, Responsável, Data_inst_comissão, Nome_Evento)
1: 1 - Ambas obrigatórias
Discussão
 Nenhuma das demais alternativas atende
plenamente
 Em ambas
 Entidades que participam do relacionamento
seriam representadas através de duas tabelas
distintas
 Estas tabelas teriam a mesma chave primária e
relação um- para- um entre suas linhas
 Maior número de junções
 Maior número de chaves primárias
Relacionamentos 1: n
1: n - Caso 1 - A entidade que tem
cardinalidade máxima 1 é obrigatória
Faz-se inserção de colunas na tabela com cardinalidade máx. 1.
(1,1)
Departamento
CodDep
Nome
Lotação
(0,N)
DataLotação
Empregado
CodE
Contratacao
Nome
Esquema relacional correspondente:
Departamento(CodDep,Nome)
Empregado(CodE,Nome,contratacao, CodDep, datalotacao)
CodDep referencia Departamento
1: n - Caso 1 - A entidade que tem
cardinalidade máxima 1 é obrigatória
(0,N)
Empregados
CPF Nome
Idade
lotação
(1,1)
Data
Adição de colunas é a preferida
Departamentos(Codd,Nome)
Empregados(CPF,Nome,Idade,Data,Codd)
Codd referencia Departamento
Departamento
Codd
Nome
(0,N) (1,1)

(1,1) (0,N)
1: n - caso 1 - A entidade que tem
cardinalidade máxima 1 é obrigatória
(0,N)
Empregados
CPF Nome
Idade
lotação
(1,1)
Data
Tabela própria pode também ser usada
Departamentos(Codd,Nome)
Empregados(CPF,Nome,Idade)
Lotações(CPF, Codd , Data)
Codd referencia Departamentos
CPF referencia Empregados
Departamento
Codd
Nome
(0,N) (1,1)

(1,1) (0,N)
1: n - caso 1 - A entidade que tem cardinalidade
máxima 1 é obrigatória
 Fusão de tabelas
 Não se aplica
 Implicaria em
 redundância de dados de departamento, ou
 tabela aninhada
 Adição de colunas é melhor que tabela própria
 Menor número de chaves
 Menor número de junções
 Não há o problema de campos opcionais
Caso 2 - Em relacionamentos 1:n onde ambas
entidades tem participação opcional
- adição coluna – preferida;
- tabela própria – pode ser usada;
Pessoas
cpf
Nome
(0,1)
Idade
posse
(0,N)
Data
Preferida: Adição de Coluna
Pessoas(cpf,Nome,Idade)
Automóveis(Coda,modelo,cpf, data)
cpf referencia pessoa
Automóveis
Coda
modelo
Caso 2 - Em relacionamentos 1:n onde ambas
entidades tem participação opcional
pessoas
cpf
Nome
(0,1)
Idade
posse
(0,N)
Data
Pode ser usada Tabela própria:
pessoas(cpf,Nome,Idade)
automóveis(Coda,modelo)
posse(cpf,coda,Data)
cpf referencia pessoa
coda referencia automoveis
automoveis
coda
modelo
Caso 2 - Em relacionamentos 1:n onde ambas
entidades tem participação opcional
 Implementação por tabela própria também é
aceitável
 É melhor em relação a campos opcionais
 Perde em relação a junções e número de chaves
Ambas entidades têm participação obrigatória
Em relacionamentos binários do tipo 1:N
Adição de coluna – somente!
(1,N)
Empregados
CPF Nome
Idade
lotação
(1,1)
Data
Departamento
Codd
Departamentos(Codd,Nome)
Empregados(CPF,Nome,Idade,Data,Codd)
Codd referencia Departamento
Nome
Trad.
Preferida
e única
Tabela de regras 1:n
Relacionamento 1:n
(0,1)
(0,n)
(0,1)
(1,n)
(1,1)
(0,n)
(1,1)
(1,n)
Tabela
própria
Adição
coluna
Fusão
Tabelas
+-
ok
x
+-
ok
x
x
ok
x
x
ok
x
Tradução de entidade com
relacionamento identificador
IdDep Nome
(1,1)
Empregados
cpfemp
Vínculo
(0,N)
nome
Modelo Relacional:
Empregado (cpfemp, nome)
Dependentes (CPFemp, IdDep, Nome)
CPFemp referencia Empregados
Dependentes
Entidade
fraca
Um dependente é identificado, isto é, a CHAVE PRIMÁRIA é
composta pelo cpf do empregado ao qual ele está vinculado e pelo
IdDep do dependente
Tradução de entidade com
relacionamento identificador
IdDep Nome
(1,1)
Empregados
cpfemp
nome
Vínculo
(0,N)
Dependentes
Relacionamento
Identificador
Modelo Relacional:
Empregado (cpfemp, nome)
Dependentes (CPFemp, IdDep, Nome)
CPFemp referencia Empregados
Entidade
fraca
CPFemp comporá
a PK de Dependentes
Implementação inicial
de entidades
Tradução de entidade - relacionamento identificador
Exercício: Qual a chave primária
da tabela Dependente?
Relacionamentos n: n
Relacionamentos n: n
Relacionamentos n: n
(0,N)
Engenheiro
CodEng
Nome Idade
(0,N)
Atuação
Função
Projeto
Codp
Título
Produz sempre uma tabela própria para o
relacionamento:
Engenheiro(CodEng,Nome,Idade)
Projetos(Codproj,Título,Duração)
Atuação(CodEng,Codproj,Função)
CodEng referencia Engenheiro
CodProj referencia Projeto
Duração
Tabela de regras m:n
Relacionamento m:n
(0,n)
(0,n)
(0,n)
(1,n)
(1,n)
(1,n)
Tabela
própria
Adição
coluna
Fusão
Tabelas
ok
x
x
ok
x
x
ok
x
x
Em síntese!
Relacionamentos N: N – somente aceitam tabela
própria.
Síntese
 Relacionamentos 1: N – preferida é adição em colunas
 Atenção! Pode ser usada Tabela própria
 Ambas tem participação opcional (0,1)(0,N)
 A participação opcional é da que apresenta
cardinalidade máxima 1 (0,1)(1,N)
Síntese
 Relacionamentos 1: 1 – preferida é fusão quando:
 ambas entidades tem participação obrigatórias (só fusão!)
 ou uma tem cardinalidade opcional (preferida);
 Pode ser usada Adição de colunas
 uma tem cardinalidade opcional (0,1)(1,1)
 Quando ambas forem opcionais (0,1)(0,1)
 Preferida – Adição colunas
 Pode ser usada- tabela própria
Implementando
Generalização/Especialização
 Duas alternativas básicas
 uso de uma única tabela para toda hierarquia
 uso de uma tabela para cada entidade
 Outra alternativa (EXÓTICA)
 Subdivisão de entidade genérica
Implementando
Generalização/Especialização
1a. Opção
Uma tabela por
hierarquia
Cliente
Tipo
Pessoa
Física
Esquema Relacional
Código
Nome
Pessoa
Jurídica
CPF
Cliente(CodCli, Tipo, Nome, CPF, CNPJ)
CNPJ
Uma tabela por hierarquia
 Todas tabelas referentes às especializações são
fundidas em uma única tabela
 Tabela contém:
 Chave primária correspondente ao identificador
da entidade mais genérica
 Caso não exista, adicionar uma coluna Tipo
 Uma coluna para cada atributo da entidade
genérica
 Colunas referentes aos relacionamentos dos
quais participa a entidade genérica e que sejam
implementados através da alternativa de
adicionar colunas à tabela da entidade genérica
Uma tabela por hierarquia - CONT
 Tabela contém (continuação):
 Uma coluna para cada atributo de cada entidade
especializada (opcional)
 Colunas referentes aos relacionamentos dos
quais participa cada entidade especializada e que
sejam implementados através da alternativa de
adicionar colunas à tabela da entidade (campo
opcional)
Uma tabela por entidade
especializada
 Criar uma tabela para cada entidade que compõe a
hierarquia
 Incluir a chave primária da tabela correspondente à
entidade genérica, em cada tabela correspondente a
uma entidade especializada
Implementando
Generalização/Especialização
2a. Opção: Uma Tabela por Entidade Especializada
Esquema Relacional
Cliente
Tipo
Cliente(CodCli, Tipo, Nome)
PessFisica(CodCli, CPF)
CodCli referencia Cliente
PessJurid(Codcli, CNPJ)
CodCli referencia Cliente
Pessoa
Física
Pessoa
Jurídica
Implementando
Generalização/Especialização
Comparação
Vantagens de implementação por tabela
única:
•Todos os dados referentes a uma ocorrência
de entidade genérica, estão em uma única linha,
não necessitando criar junções;
•Minimiza junções
•A chave primária é armazenada uma única vez.
Implementando
Generalização/Especialização
Comparação
Vantagens de implementação com uma tabela por
entidade especializada:
•As colunas opcionais que aparecem são apenas
aquelas referentes a atributos que podem ser vazios do
ponto de vista da aplicação;
• O controle de colunas opcionais passa a ser feito pelo
aplicação com base na coluna TIPO e não pelo SGDB
como na solução alternativa.
Síntese
Generalização – Tabela única
Cliente
Código
t
Pessoa
Física
Nome
CPF
Pessoa
Jurídica
Razão_Social
CGC
Cliente(código,nome,CPF,RazãoSocial,CGC)
Tabela única
Atributos opcionais
Generalização – Tabela p/entidade
Cliente
Código
t
Pessoa
Física
Nome
CPF
Pessoa
Jurídica
RazãoSocial
CGC
Cliente(código)
Tabela p/entidade
PessoaFísica(código,CPF,nome)
PessoaJurídica(código,CGC,RazãoSocial)
código referencia Cliente
Relacionamento grau > dois
Relacionamento grau> 2
 Não são definidas regras específicas
 O relacionamento é transformado em uma
entidade
 São aplicadas regras de implementação de
relacionamentos binários
Relacionamento grau> 2
Relacionamento grau> 2
Esquema relacional
Produto (CodProd, Nome)
Cidade (CodCid, Nome)
Distribuidor (CodDistr, Nome)
Distribuição (CodProd, CodDistr, CodCid, DataInicio)
CodProd referencia Produto
CodDistr referencia Distribuidor
CodCid referencia Cidade
Refinamentos do modelo relacional
 Projeto (engenharia) em geral
 compromisso entre o ideal e o realizável dentro
das restrições de recursos impostas pelas prática
 Projeto de banco de dados
compromisso entre o ideal (regras de
implementação)
eo
alcançável frente a limitações de performance
Refinamentos do modelo relacional
 Algumas vezes
 esquema de BD criado através do uso das regras
acima não atende requisitos de performance
impostos ao sistema
 Necessário buscar alternativa que resulte em melhor
performance do sistema
 Alternativas somente devem ser tentadas em último
caso
 Do ponto de vista da programação são sempre
piores
Refinamentos do modelo relacional
 Relacionamentos mutuamente exclusivos
 Simulação de atributos multivalorados
 Informações redundantes
Relacionamentos mutuamente
exclusivos
Relacionamentos mutuamente
exclusivos
 Implementação pelas regras
 colunas CIC e CGC em Venda são especificadas
como opcionais
PessFis(CIC, Nome)
PessJur( CGC, RazSoc)
Venda( Nro, data, CIC, CGC)
CIC referencia PessFis
CGC referencia PessJur
 colunas CIC e CGC em Venda são especificadas
como opcionais
Relacionamentos mutuamente
exclusivos
 Implementação alternativa
 criar uma única coluna na qual aparece o CIC ou
o CGC do comprador
PessFis( CIC ,Nome)
PessJur( CGC, RazSoc)
Venda( No ,data, CIC/ CGC, TipoCompr)
 Desvantagem
 Não é possível especificar ao SGBD que o
campo CIC/ CGC é chave estrangeira • não
referencia uma única tabela
Tratamento de atributos
multivalorados
Atributos multivalorados
implementação padrão
Cliente (CodCli, Nome)
Telefone (CodCli, Número)
CodCli referencia Cliente
Atributos multivalorados
alternativa
 Condições de contorno:
 Raros clientes possuem mais que dois telefones.
 Quando isso ocorrer é suficiente armazenarmos
apenas dois números.
 Não há consultas ao banco de dados usando o
número de telefone como critério de seleção
 Números de telefone são apenas exibidos ou
impressos juntos às demais informações de
cliente
Simulação de atributos
multivalorados
Implementação “desnormalizada”
Cliente (CodCli, Nome, NumTel1, NumTel2)
Simular uma coluna multivalorada através da
criação de diversas colunas NumTel sufixadas
por um número
Simulação de atributos
multivalorados
 Permite que os telefones de um cliente sejam
obtidos mais rapidamente
 Implica em menos espaço ocupado
 não é necessária chave primária da tabela
Telefone
 Inconveniente
 Consulta usando o número de telefone como
critério de busca torna- se mais complicada
 Manter os telefones "alinhados à esquerda" exige
rotina complexa
Informações redundantes
 Exemplo:
 atributos que resultam de uma operação que
envolve diversas entidades do banco de dados
 valor destes atributos
 deve ser obtido com freqüência ou
 serve freqüentemente como critério de busca
de informações no banco de dados
 Pode ser mais eficiente (performance global do
sistema)
 armazenar redundantemente o atributo derivado
Informações redundantes
Exemplo
 Aula preparada baseada no material enviado pelo prof.
Heuser autor do livro Projeto de Banco de Dados