DCC011 Introdução a Banco de Dados -20 Revisão_ Modelagem de Dados Revisão_ Processo de Projeto de BD Revisão_ Projeto de Bancos de Dados

Prévia do material em texto

DCC011
Introdução a Banco de Dados -20
Mirella M. Moro
Departamento de Ciência da Computação
Universidade Federal de Minas Gerais
mirella@dcc.ufmg.br
Revisão: Modelagem de Dados
Revisão: Projeto de Bancos de Dados
DCC011 - profa. Mirella 3
Mini-Mundo
Análise de
Requisitos
Requisitos do BD
Projeto Conceitual
Esquema Conceitual 
(em um modelo de dados de alto nível)
Projeto Lógico
Esquema Lógico
(em um modelo de dados lógico)
Projeto Físico
Esquema Físico
(para um SGBD específico)
Requisitos Funcionais
Análise Funcional
Especificação das Transações
(em alto nível)
Projeto das Aplicações
Implementação
Programas
Independente de SGBD
Específico para um SGBD
Modelo ER
Modelo
Relacional
SGBD
Relacional
Revisão: Processo de Projeto de BD
• Caracterização
– Complexidade
– Multiplicidade de tarefas
• Fases
– Coleção e análise de requisitos
– Projeto conceitual
– Escolha de um SGBD
– Projeto lógico 
• ou mapeamento para o modelo de dados do SGBD escolhido
– Projeto físico
– Implementação e “tuning”
4
Revisão:Estratégias para o Projeto do 
Esquema Conceitual
• Centralizado
– Todos os requisitos são capturados de uma vez
• Abordagem de integração de visões
– Uma visão é projetada para cada grupo de 
usuários e aplicações
– Subseqüentemente essas visões são integradas 
em um esquema conceitual global
5
Revisão: Propriedades de Modelos ER 
(a) Modelo ER é um modelo formal
– Preciso, não ambíguo
(b) Poder de expressão é limitado
– Restrições de integridade
(c) Equivalência entre modelos
– Expressam a mesma realidade
6
Revisão: equivalência
7© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Revisão: Processo de Construção
A. Identificando construções
– Atributo versus entidade relacionada
– Atributo versus especialização/generalização
– Atributo opcional
– Atributo multivalorado
B. Verificação do modelo
– Modelo deve ser correto, completo, livre de 
redundâncias
C. Modelo deve refletir o aspecto temporal
D. Estratégias de modelagem
8
EXEMPLO
Reserva de passagens aéreas
• O objetivo do trabalho é projetar um sistema de reservas para uma companhia de 
aviação. O sistema contará com um banco de dados central, que será acessado por 
aplicações clientes, rodando tanto dentro da própria companhia, quanto fora dela. 
A transação central do sistema é a reserva. Uma reserva é identificada por um 
código gerado pelo sistema em computador. A reserva é feita para um único 
passageiro, do qual se conhece apenas o nome. A reserva compreende um 
conjunto de trechos de vôos, que acontecerão em determinada data/hora. Para 
cada trecho, a reserva é feita em uma classe (econômica, executiva, etc)
• Um vôo é identificado por um código e possui uma origem e um destino. Por 
exemplo, o vôo 959 sai de Porto Alegre com destino a São Paulo. Um vôo é 
composto de vários trechos, correspondendo às escalas intermediárias do vôo. Por 
exemplo, o vôo 959 é composto de dois trechos, um de Porto Alegre a Londrina, o 
outro de Londrina a São Paulo. Cabe salientar que há cidades que são servidas por 
vários aeroportos. Por isso, é importante informar ao passageiro que faz a reserva 
qual é o aeroporto no qual o vôo passa.
9© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Reserva de passagens aéreas
• Às vezes os clientes, ao fazer a reserva querem saber qual é o tipo de aeronave 
que será utilizada em determinado trecho de vôo. Alguns poucos vôos, 
principalmente internacionais, têm troca de aeronave em determinadas escalas. 
Nem todos vôos operam em todos dias da semana. Inclusive, certos vôos têm 
pequenas mudanças de horário em certos dias da semana.
• Cada reserva possui um prazo de validade. Caso os bilhetes não tenham sido 
emitidos até esgotar-se o prazo da reserva, a mesma é cancelada. Reservas podem 
ser prorrogadas.
• Como o check-in de todos os vôos está informatizado, a companhia possibilita a 
reserva de assento para o passageiro. Reservas de assento podem ser feitas com 
até três meses de antecedência. Além de efetivar reservas, o sistema deve servir 
para vários tipos de consultas que os clientes podem querer fazer:
– Possibilidades de viagem de uma cidade ou de um aeroporto para outro
– O mesmo, mas restrito a determinados dias da semana
– Horários de chegada ou de saída em determinados vôos
– Disponibilidade de vagas em um trecho de vôo
– Disponibilidade de determinados assentos em um trecho de vôo
10© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
a. entidades
• Entidades
– Companhia, reserva, passageiro, trecho, voo, 
cidade, aeroporto, tipo-aeronave, horário, assento
– Não foi criada uma entidade
• Pessoas que efetivaram a reserva
• Problema de homônimos
• Atributo reserva
11© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
b. relacionamentos
12© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
c. Atributos e identificadores
• RESERVA (codigo reserva, passageiro, prazo)
• VOO (numero)
• TRECHO ()
• AEROPORTO (codigo, nome)
• CIDADE (codigo, nome, país)
• TIPO AERONAVE (codigo, descrição)
• HORARIO (dia semana, horário partida, horário chegada)
• ASSENTO (numero, classe)
• RSRV-TRCH (data)
13© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
d. Restrições de integridade
• Uma reserva de trecho somente pode ser 
realizada caso existam vagas no trecho em 
questão na data em questão
• Uma reserva para um assento somente pode 
ser feita se o assento em questão existir no 
tipo de aeronave utilizada no trecho de vôo 
em questão
14© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
e. Redundância e desempenho
• Observação geral
– Solução adotada conceitual
– Não inclui redundâncias de dados que objetivem 
melhorar o desempenho
– Não atributos redundantes
• Número de vagas em um trecho de vôo, em uma data, 
inclusive discriminado por classe
• Criar entidade TRECHO-DIA
• Etapa de projeto
15© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Mapeamento ER - Relacional
1. Transformações entre Modelos
2. Algoritmo de mapeamento ER � Relacional
1. Transformações entre Modelos
• Uma vez definido o modelo conceitual, o próximo 
passo é definir o modelo lógico
• Uma alternativa: mapear as construções do modelo 
conceitual para o lógico
17
Transformações entre Modelos
18© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Transformações entre Modelos
19© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Transformações ER para 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
– Bom desempenho
– Simplificar o desenvolvimento
20© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Regras gerais de tradução
A. Evitar junções
B. Diminuir o número de chaves 
C. Evitar campos opcionais
21© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
A. Evitar junções
• Junções
– Operação para buscar dados de diversas linhas associadas pela 
igualdade de campos
– Dados de empregados e seus respectivos departamentos
• SGBD relacional normalmente armazena os dados de uma 
linha contiguamente em disco
• Junção envolve diversos acessos a disco
• Preferível
– Ter os dados necessários a uma consulta em uma única linha
22© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
B. Chave e Índice
• Implementação eficiente do controle de 
chaves: SGBD usa um índice
– Índices tendem a ocupar espaço considerável em 
disco
• Inserção e remoção de entradas em um índice
– Podem exigir diversos acesso a disco
23© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
C. Campos opcionais
• Campo opcional = campo que pode assumir o valor 
vazio (NULL em SQL)
• SGBD relacional não desperdiça espaço pelo fato de 
campos de uma linha estarem vazios
• Campo opcional não tem influência no desempenho
• EVITAR porquecontrole de campo opcional pode 
complicar programação
– Verifica quais campos podem estar vazios
24© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
2. Algoritmo de Mapeamento
Elmasri & Navathe
a. Entidades regulares
b. Atributos multivalorados
c. Entidades fracas
d. Relacionamentos
d.1 Relacionamentos binários 1:1
d.2 Relacionamentos binários 1:N
d.3 Relacionamentos binários N:M
d.4 Relacionamentos N-ários
e. Hierarquias (Especializações/Generalizações)
25
Exemplo de um Diagrama ER
26
N 1
1 11 1
N N
N
M
NEmp NomeEmp Salário NDept NomeDept
Ramal
Empregado
ProjetoDependente
Departamento
Possui
NomeDep DataNasc
NProj
NomeProj
Local
HsTrab
Participa-de
Trabalha-para
Gerencia
Controla
a. Entidades regulares
(sem atributos multivalorados)
• Entidade regular E � Relação R
• Atributo em E � Coluna em R
• Atributo identificador em E � Chave primária 
em R
27
Entidades regulares
(sem atributos multivalorados)
28
Empregado
NEmp
NomeEmp
Salário
Empregado (NEmp, NomeEmp, Salário)
Observação: 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
• Não transcrever os nomes atributos � nomes colunas
• 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
29© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Observação: Nome da chave
• 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
30© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
b. Atributos Multivalorados
31
Departamento
NDept
NomeDept
Ramal
Departamento (NDept, NomeDept)
Ramal-Departamento (NDept, Ramal)
NDept referencia Departamento, por propagação
c. Entidade Fraca
32
Empregado (NEmp,...)
Dependente (NEmp,NomeDep, DataNasc)
NEmp referencia Empregado, por propagação
1 N
NEmp NomeDep DataNasc
Empregado DependentePossui
d. Relacionamentos
• Tabela própria
• Adição de colunas a uma das tabelas
• Fusão de tabelas
• Alternativa depende da cardinalidade 
(máxima e mínima) do relacionamento
d.1 Relacionamentos binários 1:1
d.2 Relacionamentos binários 1:N
d.3 Relacionamentos binários N:M
d.4 Relacionamentos N-ários
33
d.1. Relacionamento binário 1:1
(adição de colunas)
34
11
NEmp NDept
Gerencia
Empregado (NEmp,...)
Departamento (NDept, ..., NEmp)
NEmp referencia Empregado, por bloqueio
Chave alternativa
Empregado Departamento
Relacionamento binário 1:1
(fusão de tabelas)
35
Conferência (CodConf, Nome, DataInstComOrg, EnderComOrg)
© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamento binário 1:1
(adição de colunas)
36
Departamento (CodDept, Nome)
Empregado (CodEmp, Nome, CodDept, DataLota)
CodDept referencia Departamento
© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamento binário
(0,1) – (1,1)
37© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(0,1) – (0,1) – ambas opcionais
38
Mulher (IdentM, Nome, IdentH, Data, Regime)
IdentH referencia Homem
Homem (IdentH, Nome)
Adição de Colunas
© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(0,1) – (0,1) – ambas opcionais
39
Mulher (IdentM, Nome)
Homem (IdentH, Nome)
Casamento (IdentM, IdentH, Data, Regime)
IdentM referencia Mulher
IdentH referencia Homem
Tabela Própria
© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(0,1) – (0,1) – ambas opcionais
40
Casamento (IdentM, IdentH, Data, Regime, NomeH, NomeM)
Fusão de Tabelas
© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(0,1) – (0,1) – ambas opcionais
• 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
41© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(0,1) – (1,1) – opcional e obrigatória
42
Correntista (CodCorrent, Nome, CodCartao, DataExp)
Fusão de Tabelas
© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(0,1) – (1,1) – opcional e obrigatória
43
Adição de Colunas
Correntista (CodCorrent, Nome)
Cartao (CodCartao, DataExp, CodCorrent)
CodCorrent referencia Correntista
© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(0,1) – (1,1) – opcional e obrigatória
44
Tabela Própria
© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Correntista (CodCorrent, Nome)
Cartao (CodCartao, DataExp)
CartaoCorrentista (CodCartao, CodCorrent)
CodCorrent referencia Correntista
CodCartao referencia Cartao
Relacionamentos binários
(0,1) – (1,1) – opcional e obrigatória
• 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 tem problema de campos opcionais
• Adição de colunas versus fusão de tabelas
– Fusão é melhor em termos de número de junções e 
número de chaves
– Adição é melhor em termos de campos opcionais
– Fusão é considerada a melhor e adição é aceitável
45© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(1,1) – (1,1) – ambas obrigatórias
46© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Conferência (CodConf, Nome, DataInstComOrg, EnderComOrg)
Fusão de Tabelas
Relacionamentos binários
(1,1) – (1,1) – ambas obrigatórias
• 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
47© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
d.2. Relacionamento binário 1:N
48
Empregado (NEmp, ...)
Departamento (NDept, ...)
NEmp referencia Empregado, por propagação
Trabalha-para (NEmp, Ndept)
NDept referencia Departamento, por bloqueio
NEmp(Empregado)= NEmp(Trabalha-para)π π
1N
NEmp NDept
Trabalha
-para
Empregado Departamento
Relacionamento binário 1:N
(mapeamento otimizado)
49
Empregado (NEmp, ... , NDept)
Departamento (NDept, ...)
NDept referencia Departamento, por bloqueio
1N
NEmp NDept
Trabalha
-para
Empregado Departamento
Relacionamentos binários
(1:N)
50© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(1,1) – (0,N)
51© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Adição de Colunas
Departamento (CodDept, Nome)
Empregado (CodEmp, Nome, CodDept, DataLota)
CodDept referencia Departamento
Relacionamentos binários
(1,1) – (0,N)
52© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Tabela Própria
Departamento (CodDept, Nome)
Empregado (CodEmp, Nome)
Lotacao (CodEmp, CodDept, DataLota)
CodDept referencia Departamento
CodEmp referencia Empregado
Relacionamentos binários
(1,1) – (0,N)
• 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á problema de campos opcionais
53© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamentos binários
(0,1) – (0,N)
54© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Adição de Colunas
Financeira (CodFin, Nome)
Venda (IdVenda, Data, CodFin, NoParc, TxJuros)CodFin referencia Financeira
Relacionamentos binários
(0,1) – (0,N)
55© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Tabela Própria
Financeira (CodFin, Nome)
Venda (IdVenda, Data)
Financiam (IdVenda, CodFin, NoParc, TxJuros)
IdVenda referencia Venda
CodFin referencia Financeira
Relacionamentos binários
(0,1) – (0,N)
• 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
56© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
d.3. Relacionamento binário N:M
57
Empregado (NEmp, ...)
Projeto (NProj, ...)
NEmp referencia Empregado, por propagação
Participa-de (NEmp, NProj, HsTrab)
NProj referencia Projeto, por propagação
NM
NEmp NProj
Participa
-de
Empregado Projeto
HsTrab
Relacionamento binário
(N:M)
58© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Relacionamento binário
(N:M)
59© Carlos A. Heuser
Projeto de Banco de Dados Ed. Sgra & Luzzatto
Tabela Própria
Engenheiro (CodEng, Nome)
Projeto (CodProj, Titulo)
Atuacao (CodEng, CodProj, Funcao)
CodEng referencia Engenheiro
CodProj referencia Projeto
d.4. Relacionamento N-ario
• 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
• Nova entidade Rel
– Colunas = chaves primárias das tabelas 
relacionadas
60
Relacionamento N-ario
61
e. Hierarquias
• Geralmente quatro opções
e.1. Relações : superclasse e subclasses
e.2. Relações : subclasses
e.3. Relação única
e.4. Relação única : atributos tipo
62
Hierarquias
63
Gerente
Empregado
NEmp NomeEmp
SalAd Formação
Salário
d
Técnico
Empregado = Gerente ∪ Técnico
Gerente ∩ Técnico = φ
e.1. Relações superclasse+subclasses
64
Gerente
Empregado
NEmp
SalAd Formação
Técnico
Empregado (NEmp, ...)
Gerente (NEmp, SalAd)
Gerente [NEmp] → Empregado [NEmp]
Técnico (NEmp, Formação)
Técnico [NEmp] → Empregado [NEmp]
p
p
NEmp(Gerente) ∩ NEmp(Técnico) = φπ π
NEmp(Gerente) ∪ NEmp(Técnico) =π π
NEmpπ (Empregado)
d
e.1. Relações 
superclasse
+
subclasses
65
d
e.2. Relações subclasses
66
Gerente
Empregado
NEmp
SalAd Formação
d
Técnico
Gerente (NEmp, ..., SalAd)
Técnico (NEmp, ..., Formação)
NEmp(Gerente) ∩ NEmp(Técnico) = φπ π
NEmp(Gerente) ∪ NEmp(Técnico) =π π
todos empregados
d
67
d
e.3. Relação única
68
Gerente
Empregado
NEmp
SalAd Formação
d
Técnico
Empregado (NEmp, ..., SalAd, Formação)
NEmp (Empregado)) ∩π (σSalAd ≠ nulo
NEmp (Empregado)) = φπ (σFormação ≠ nulo
NEmpπ
NEmp (Empregado)) ∪π (σSalAd ≠ nulo
NEmp (Empregado)) =π (σFormação ≠ nulo
(Empregado)
d
69
d
e.4. Relação única: atributos tipo
70
o