ApostilaModelagemBD03

(1)

Modelagem de

Banco de Dados

Parte do conteúdo exposto nestas transparências foi retirado dos livros: “Projeto de Banco de Dados”, de Carlos A. Heuser ;

“Projeto de Banco de Dados - Uma visão prática”, de Felipe Machado e Maurício Abreu

Parte 3

(2)

Roteiro

• Introdução

• Tabela

o

Campo, Linha e Coluna

o

Tabelas x Arquivos

• Chaves

o

Chave Primária, Estrangeira e Alternativa

• Restrições de Integridade

o

Classificações Comuns

• Modelo de Banco de Dados Relacional

o

Esquema Textual e Esquema Diagramático

Introdução

• Banco de Dados Relacional

o

Mais utilizado no mercado atual.

• Composto por Tabelas ou

Relações

o

Tabelas – terminologia mais comum em produtos

comerciais e na prática

(3)

5

Modelo Relacional/Lógico – TABELAS

Uma tabela é um conjunto não ordenado de linhas

(tuplas, na terminologia acadêmica).

Cada linha é

composta por uma série de campos (valor de atributo).

A primeira linha (registro) da tabela quer dizer que o

Cliente João, cujo código é igual a 0111, foi admitido no

dia 12/11/2000 e trabalha no Departamento cujo código

é 01.

Nome

Código

0111

0112

0271

0108

0357

0097

João

Antônio

Carlos

Eduardo

Luís

Vera

Data Admissão

12/11/2000

12/12/2001

05/06/2001

03/03/2000

20/10/2001

15/02/2002

Código Depto

01

10

21 linha

coluna

Modelo Relacional/Lógico – TABELAS

As linhas de uma tabela não tem ordenação. A ordem de

recuperação pelo SGBD é arbitrária, a menos que a

instrução de consulta tenha especificado explicitamente

Nome

Código

0111

0112

0271

0108

0357

0097

João

Antônio

Carlos

Eduardo

Luís

Vera

Data Admissão

12/11/2000

12/12/2001

05/06/2001

03/03/2000

20/10/2001

15/02/2002

Código Depto

01

10

21 Valor do

campo

coluna

(atributo)

linha(tupla)

(4)

7

Modelo Relac./Lógico – DOMÍNIOS E VALORES VAZIOS

Quando uma tabela do banco de dados é definida, para

cada coluna da tabela, deve ser especificado um conjunto

de valores (alfanumérico, numérico,..) que os campos da

respectiva coluna podem assumir.

Também deve ser especificado se os campos da coluna

podem estar vazios (“null” em inglês) ou não.

Este conjunto de valores é chamado de DOMÍNIO DA

COLUNA ou do CAMPO

As colunas nas quais não são admitidos valores

vazios são chamadas de colunas Obrigatórias e as

outras são chamadas de Opcionais.

Conceitos Básicos

Chave

• Identificador de linhas

o

É a forma de identificar linhas e estabelecer

relações entre linhas de tabelas de um

banco de dados relacional

o

Permite diferenciar ou acessar uma linha

específica

• Chaves

o

Primária

o

Alternativa

o

Estrangeira

(5)

Chaves

Chave Primária

• É uma coluna ou uma combinação de colunas cujos valores distinguem uma linha das demais dentro da tabela

• Unicidade dos valores nas colunas que a compõem

o Duas linhas diferentes não podem ter o mesmo valor

• Na tabela Empregado, a chave primária é CodEmp

o Os demais campos nas demais colunas podem ser repetidos

Chaves

Chave Primária Composta

• Quando uma única coluna não é suficiente para

distinguir duas linhas quaisquer usa-se uma combinação de colunas

• A tabela Dependente é um exemplo

o Cada empregado pode ter mais de um dependente o Para identificar um dependente específico é necessário

conhecer o empregado (CodEmp) e o dependente deste empregado (NumDep)

(6)

11

Horastrab

CodEmp

E1

E2

E6

86

32

180

40

120

01

02

01

02 CodProj

Chave

Primária

Simples

EmpxProj

Nome

CodEmp

E5

E3

E2

E1

Souza

Santos

Silva

Soares

CategFuncional

C5

C2

----D1

D2

D1

CodDepto

Empregado

Chave

Primária

Composta

Modelo Relacional/Lógico – CHAVES

Chaves

Chave Estrangeira

• É uma coluna ou uma combinação de colunas

cujos valores aparecem necessariamente na

chave primária de uma tabela

• Mecanismo que permite a implementação de

relacionamentos em um BD Relacional

• Sua existência impõe restrições para garantir a

integridade das operações realizadas no Banco de

Dados

(7)

Chaves

Chave Estrangeira

• Tabela Departamento

o CodDpto é a sua chave primária

• Tabela Empregado

o CodEmp é a sua chave primária o CodDpto é chave estrangeira o Todos os valores desta coluna devem estar definidos na Tabela Departamento

Nome Depto

CodDepto

01

10

21 Contabilidade

Vendas

Faturamento

Verba

9.500,00

15.000,00

12.800,00

Nome

Codigo

0111

0112

0271

0108

0357

João

Antônio

Carlos

Eduardo

Luís

Data Admissão

12/11/2000

12/12/2001

05/06/2001

03/03/2000

20/10/2001

CodDepto

01

10

10 Qual o nome do

departamento

do Funcionário

João?

CHAVES

Tabela: Departamento

Tabela: Empregado

(8)

15

CodDepto

01

10

21 Contabilidade

Vendas

Faturamento

Verba

9.500,00

15.000,00

12.800,00

Tabela: Departamento

Tabela: Empregado

Nome

0111

0112

0271

0108

0357

0097

João

Antônio

Carlos

Eduardo

Luís

Vera

Data Admissão

12/11/2000

12/12/2001

05/06/2001

03/03/2000

20/10/2001

15/02/2002

Código Depto

01

10

21 Código

CHAVES – EXPRESSÃO RELACIONAMENTO

Quais os funcionários do Depto de vendas? - Código do Depto de vendas = 10

Quais os funcionários que tem código do Depto igual a 10?

- Carlos, Eduardo e Luís

Nome Depto

Chave Estrangeira

Restrições Impostas

• Inclusão de uma linha na tabela com chave

estrangeira

o Deve ser garantido que o valor aparece na coluna da chave primária referenciada

o No exemplo, um novo empregado deve atuar em um dos departamentos existentes (já referenciados na tabela Departamento)

• Alteração do valor da chave estrangeira

o Deve ser garantido que um novo valor de uma chave estrangeira apareça na coluna da chave primária referenciada

o No exemplo, um empregado só pode mudar para um departamento existente

(9)

Chave Estrangeira

Restrições Impostas

• Exclusão de uma linha da tabela que contém a chave primária referenciada pela chave estrangeira

o Deve ser garantido que, na coluna chave estrangeira, não apareça o valor da chave primária que está sendo excluída o No exemplo, um departamento não pode deixar de existir

enquanto houverem empregados nele

• Alteração do valor da chave primária referenciada pela chave estrangeira

o Deve ser garantido que, na coluna chave estrangeira, não apareça o antigo valor da chave primária que está sendo excluída

o No exemplo, se um departamento possui empregados, seu código não pode ser alterado

Chave Estrangeira

Cuidado!

• Nem toda chave estrangeira referencia a chave

primária de

OUTRA

tabela

o Suponha que a coluna CodEmpGerente represente o gerente responsável pelo empregado definido na linha o Como todo gerente também é um empregado, os

valores desta coluna devem existir na coluna CodEmp o Conclusão: CodEmpGerente é chave estrangeira em

(10)

Chaves

Chave Alternativa

• Em alguns casos, mais de uma coluna (ou combinações de colunas) pode servir para distinguir uma linha das demais

o Uma da colunas é escolhida chave primária

o As demais colunas são denominadas Chaves Alternativas

• Na tabela Empregado, as colunas CodEmp e CIC podem diferenciar uma determinada linha das demais.

o CodEmp foi escolhida como chave primária

o CIC é uma chave alternativa

Conceitos Básicos

Restrições de Integridade

• O objetivo fundamental de um SGBD é a

manutenção da Integridade dos Dados

o Dados são considerados íntegros quando refletem a realidade representada pelo Banco de Dados e são consistentes entre si

• Uma Restrição de Integridade é uma regra de

consistência de dados garantida pelo SGBD

• Integridade de Domínio

• Integridade de Vazio

• Integridade de Chave

(11)

Restrições de Integridade

Classificações Comuns

• Integridade de Domínio

• O valor de um campo deve obedecer a definição de valores admitidos para a coluna (o domínio da coluna)

o Primeiros SGBD relacionais, uso de domínio pré-definido (Números inteiros, reais, literais de tamanho definido, etc...) o SGBD modernos permitem definir domínios próprios (domínio

dos dias da semana, estados do país, etc...)

• Integridade de Vazio

• Especifica se os campos de uma coluna podem ou não estar vazios, isto é, se a coluna é obrigatória ou opcional

o Campos que contém a chave primária sempre devem ser diferentes de vazio

Restrições de Integridade

Classificações Comuns

• Integridade de Chave

• Restrição que define que os valores das chave primária e alternativa devem ser únicos

• Integridade Referencial

• Restrição que define que os valores dos campos que aparecem em uma chave estrangeira devem aparecer na chave primária da tabela referenciada

• Restrições Semânticas

• São restrições normalmente não classificadas nas

categorias anteriores e que não são garantidas pelo SGBD

o Um empregado estar incluso em uma categoria funcional a qual não pertence, como por exemplo, um médico

(12)

Conceitos Básicos

Modelo de BD Relacional

• Um modelo de Banco de Dados Relacional

deve conter, no mínimo, os seguintes itens

• Tabelas que formam o Banco de Dados

• Colunas que as tabelas possuem

• Restrições de Integridade

• Um modelo de Banco de dados pode ter

diferentes representações (diferentes esquemas)

Modelo de Banco de Dados Relacional

Esquema Textual

Um esquema textual resumido é um notação incompleta,mas compacta que permite avaliar e discutir a estrutura de um BD sem entrar no nível de detalhes

o Nesta notação são listadas as tabelas e suas colunas o Uma coluna sublinhada indica uma chave primária

o Chaves estrangeiras são indicadas com referência a tabela onde está definida

Tabela (coluna1, coluna2, ..., colunaN, ...) colunaN referencia Tabela2

(13)

Modelo de Banco de Dados Relacional

Esquema Textual

Empregado (CodEmp, Nome, CodDpto, Categoria) CodDpto referencia Departamento

Departamento (CodDpto, NomeDpto)

• Tabelas : Empregado e Departamento • Chaves Primárias : CodEmp e CodDpto

• Chave Estrangeira : CodDpto (em Empregado)

Banco de Dados Relacional

Questões

Considere o banco de dados relacional definido

abaixo. Este esquema está incompleto. Defina a(s)

chave(s) primárias em Empregado e justifique sua

escolha.

Empregado (CodEmpregado, Nome, numPIS)

Dependente (CodEmpregado, numDependente, Nome)

(14)

Modelo de Banco de Dados Relacional

Esquema Diagramático

• Muitas ferramentas CASE trabalham com notações gráficas (diagramas)

• De modo geral, diagramas são organizados como segue

o Cada tabela é representada como um retângulo o As colunas da tabela são listadas dentro do retângulo. o Notações adicionais podem indicar

• o domínio das coluna

• colunas que compõem as chaves primária (primary key, PK) e estrangeira (foreign key, FK)

o Setas podem representar existência de chaves estrangeiras • O sentido da seta depende da notação

Modelo de Banco de Dados Relacional

(15)

Banco de Dados

Transformações entre Modelos

Prof. Fabio Predolim

Roteiro

• Introdução

• Visão Geral do Projeto Lógico • Transformação ER para Relacional

o Princípios de Transformação

o Implementação Inicial das Entidades o Implementação dos Relacionamentos

o Implementação de Generalização / Especialização

• Refinamento do Modelo Relacional

o Redundância

o Atributos Multivalorados

(16)

Introdução

• Modelo Conceitual

o Abordagem ER é voltada à modelagem de dados independente do SGBD considerado

• Modelo Lógico

o Abordagem Relacional modela os dados no nível do SGBD relacional

• Devemos considerar a relação entre estes modelos:

• Projeto Lógico

o Determina a transformação do modelo ER em Lógico.

• Engenharia Reversa

o Processo obtém um diagrama ER a partir do modelo Lógico.

Visão Geral do Projeto Lógico

• Um modelo conceitual (ER) pode gerar diferentes

modelos lógicos (Relacionais ou outros) de maneira correta.

• Modelos lógicos diferentes (corretos) podem possuir diferentes performances e dificuldades de

implementação e manutenção do Banco de Dados. • Como consenso,

é considerado um modelo inicial do qual se faz um Refinamento.

(17)

Modelo Lógico

• Disposição do conjunto de dados em termos da estrutura de armazenamento própria da tecnologia de gerenciamento de dados escolhida, baseada nas necessidades do negócio detectadas e expressas na fase de modelagem conceitual.

o Deriva da construção de tabelas inter-relacionadas a partir das entidades e relacionamentos definidos no MER e representados pelo DER:

Transformação ER – Relacional

Estabelecimento de Regras

• Objetivos principais

• Obter um banco de dados com boa performance.

o Essencialmente significa diminuir o número de acessos ao

disco.

• Obter um banco de dados que simplifique o

desenvolvimento e manutenção.

o Outros objetivos como a redução do tamanho do espaço utilizado também são desejáveis, mas devido aos custos reduzidos das unidades de armazenamento, se tornaram menos importantes.

(18)

Princípios de Transformação

Evitar Junções

• SGBD geralmente

armazenam dados de uma linha de maneira contígua.

o Recuperar os dados de uma

linha após encontrada requer uma única operação de acesso.

• Uma operação de junção é

realizada quando o BD deve encontrar dados em “locais” diferentes.

• Operações de junção são

gerenciadas pelo SGBD e são freqüentes, mas mesmo sendo eficientes, envolvem mais de um acesso.

• Exemplo de junção: Um BD necessita buscar os dados de um empregado e do departamento em que trabalha (Tabelas abaixo).

Princípios de Transformação

Diminuir o Número de Chaves

• A implementação eficiente de uma chave primária ou alternativa é usualmente feita com uma estrutura de acesso auxiliar, um Índice.

o O índice permite um teste rápido do valor de uma chave primária quando está sendo incluída.

• Para chaves estrangeiras, o índice é usado quando a chave primária associada é alterada ou excluída.

Regra Geral - Para cada chave (primária, alternativa ou estrangeira) é necessário definir um índice.

Importante: Índices ocupam muito espaço em disco e

(19)

Princípios de Transformação

Diminuir o Número de Chaves

• Considere as duas implementações de dados sobre um

cliente em um BD Relacional.

Cliente (CodCliente, Nome, Contato, Endereço, Telefone)

o Apenas um índice, a chave primária da tabela (CodCliente).

Cliente (CodCliente, Nome, Contato)

ClienteEndereco (CodCliente, Endereço, Telefone) CodCliente referencia Cliente

o Um índice em cada tabela para o código do cliente.

o Cada cliente aparece nas duas tabelas, portanto a criação dos índices resulta em processamento dobrado.

Princípios de Transformação

Evitar Campos Opcionais

• Campos Opcionais são os campos que podem

assumir valor vazio (NULL).

• Usualmente não desperdiça espaço devido à

técnicas de compressão de dados.

o Usualmente não há problemas.

• Problemas oriundos da possível dependência

da obrigatoriedade de preenchimento de

outros campos.

o Em alguns SGBD, o controle da obrigatoriedade deve ser feito pelos programas de acesso ao banco, o que deve ser evitado.

(20)

Processo do Projeto Lógico

Implementação Inicial de Entidades

Inicialmente:

• Cada

entidade

pode ser traduzida como uma tabela

• Cada

atributo

da entidade define uma coluna

• O

atributo identificador

define a coluna da chave primária

Pessoa (CodPessoa, Nome, Endereco, DataNasc, DataAdm)

Implementação Inicial das Entidades

Nomenclatura

• Nomes de colunas são referenciados freqüentemente em programas de Banco de Dados, muitas vezes com o nome da tabela como qualificador.

Exemplo: Pessoa.Nome

• Utilização de algumas regras e recomendações:

o Os nomes não podem conter espaços em branco nem hífens.

Exemplo: Data de Nascimento

o Conveniente manter os nomes das coluna curtos e claros.

Exemplo: DataNasc OK

o Não incluir o nome da tabela na coluna que não for chave.

Exemplo: NomePessoa Exemplo: CodigoPessoa OK

o Para abreviaturas, usar o mesmo padrão em todo projeto.

(21)

Implementação Inicial das Entidades

Relacionamento Identificador

(Entidade Fraca)

Tabela da entidade identificada possui uma chave estrangeira para cada relacionamento identificador.

• Chave Estrangeiraformada por:

o Chave Primária da tabela referenciada (CodEmpregado)

• Chave Primáriaformada por:

o Colunas dos atributos identificadores (se existirem) e

o Chaves Estrangeiras que implementam estes

relacionamentos

Empregado (CodEmpregado, Nome)

Dependente (CodEmpregado, NumSeq, Nome)

Implementação Inicial das Entidades

Relacionamento Identificador

• Relacionamentos identificadores que possuem outros

relacionamentos identificadores devem propagar as chaves estrangeiras.

(22)

Processo do Projeto Lógico

Implementação dos Relacionamento

• Fator determinante: Cardinalidade

• Alternativas:

o Tabela Própria o Adição de Colunas

o Fusão de Tabelas de Entidades

Implementação dos Relacionamentos

Tabela Própria

• O relacionamento pode ser

implementado através de uma tabela

própria para o relacionamento.

• As colunas são correspondentes aos:

o

Identificadores das entidades relacionadas.

o

Atributos dos Relacionamentos.

• Regras para:

o

Cardinalidade n:n

o

Cardinalidade 1:1 e 1:n

(23)

Implementação dos Relacionamentos

Tabela Própria (Cardinalidade n:n)

• Chave primária

o Formada pelas colunas correspondentes aos identificadores das tabelas relacionadas e aos atributos identificadores do relacionamento (se existir)

• Chave estrangeira

o Formada pelas colunas correspondentes ao identificador de uma entidade

Engenheiro (CodEng, Nome) Projeto (CodProj, Titulo)

Atuação (CodEng, CodProj, Função) CodEng referencia Engenheiro CodProj referencia Projeto

Implementação dos Relacionamentos

Adição de Colunas

• O relacionamento pode ser implementado

como uma coluna em uma das tabelas

correspondentes às entidades que participam

deste relacionamento.

• Só é possível quando uma das entidades que

participa do relacionamento tem cardinalidade

máxima 1.

(24)

Implementação dos Relacionamentos

Fusão de Tabelas

• A terceira forma de implementar um relacionamento é

através da fusão das tabelas referentes às entidades envolvidas no relacionamento.

• Consiste em implementar em uma única tabela todos os

atributos das entidades envolvidas e do relacionamento.

• Só é possível quando o relacionamento é do tipo 1:1

Conferencia (CodConf, Nome, DataInstCom, EnderCom)

Implementação dos Relacionamentos

(25)

Implementação dos Relacionamentos

Alternativas para Implantação

• A última opção é uma possibilidade que deve ser

evitada.

Processo do Projeto Lógico

Implementação das Generalizações

• Para implementação de hierarquias de

generalização/especialização há duas

alternativas principais:

o

Uso de uma Tabela para cada Entidade.

o

Uso de uma única Tabela para toda a

(26)

Processo do Projeto Lógico

Implementação das Generalizações

Tabelas por Hierarquia

• Todas as tabelas referentes às especializações

de uma entidade genérica são fundidas em

uma única tabela composta de:

o Chave Primáriareferente aos identificador da

entidade mais genérica.

o Uma coluna Tipo(caso não exista) que identifica que tipo de entidade especializada está sendo

representada por cada linha da tabela. o Uma coluna para cada atributo da entidade

genérica.

o Uma coluna para cada atributo de cada entidade especializada (devem ser opcionais).

(27)

Implementação das Generalizações

Tabelas por Hierarquia

• Todas as tabelas referentes às especializações

de uma entidade genérica são fundidas em

uma única tabela composta de:

o 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.

o Colunas referentes aos relacionamentos dos quais participa cada entidade especializada e que sejam implementados através da alternativa de adicionar colunas à tabela da entidade genérica (devem ser opcionais) .

Implementação das Generalizações

(28)

Implementação das Generalizações

Tabelas por Entidades Especializadas

• Cada tabela que compõe a hierarquia pode

ser representada por uma tabela.

• Segue

as

regras

básicas

(apresentadas

anteriormente).

• Inclusão do atributo identificador da entidade

genérica

(chave

primária

da

tabela

correspondente) em cada tabela das entidades

especializadas.

o As tabelas correspondentes à entidade genérica e suas especializações tem todas a mesma chave primária.

Implementação das Generalizações

Tabelas por Entidades Especializadas

• Chave Primária igual a todas

o A toda ocorrência de uma entidade especializada corresponde à uma linha da tabela da entidade genérica.

o (Demais tabelas permanecem iguais a implementação de tabelas por hierarquia).

(29)

Implementação das Generalizações

Comparação das Alternativas

• Vantagens da implementação com Tabela Única: o Os dados referentes a uma ocorrência de entidade

genérica e dados referentes a ocorrências de sua especialização estão em uma única linha.

o Não há necessidade de junções entre entidades genérica e

especializada.

o A chave primária é armazenada uma única vez.

• Vantagens da implementação com Tabela por Entidade Especializada:

o Coluna opcionais que aparecem são apenas aquelas cujos

atributos podem ser vazios.

o Desnecessário definir colunas como opcionais como

atributos e relacionamentos de entidades especializadas na Tabela Única.

o Controle de colunas opcionais passa a ser feito pela

aplicação e não pelo SGBD.

Refinamento do Modelo

• Um

modelo conceitual

(ER)

pode

gerar

diferentes modelos lógicos (Relacionais ou

outros) de maneira correta, mas que podem

possuir diferentes performances e dificuldades

de implementação e manutenção do Banco

de Dados.

• Compromisso entre o ideal (definido pelas

regras de implementação) e o alcançável pelas

limitações de performance.

• Refinamento

para

“ajustar”

possíveis

redundâncias,

atributos

multivalorados

e

relacionamentos mutuamente exclusivo entre

outros.

(30)

Refinamento do Modelo

Redundância no Projeto

• Problema

ligado

ao

uso

de

Relacionamentos Identificadores.

o

Exemplo de uma empresa que ministra cursos

e mantém informações sobre as avaliações

dos cursos.

o

3 Entidades: Curso, Inscrito e Prova.

• Curso representa os cursos ministrados (identificado

por um código).

• Inscrito representa cada um que faz o curso

(Identificado pelo Curso e por um número seqüencial).

• Prova corresponde às provas realizadas

(Identificado pelo Curso e por um número seqüencial).

Refinamento do Modelo

Redundância no Projeto

• Exemplo de uma empresa que ministra cursos e

mantém informações sobre as avaliações dos

cursos

(31)

Refinamento do Modelo

Redundância no Projeto

Refinamento do Modelo

Atributos Multivalorados

• Na abordagem relacional não

existem colunas multivaloradas.

• Possíveis problemas de

desempenho.

• Supor algumas considerações p.e.

(32)

Refinamento do Modelo

Relacionamentos Mutuamente Exclusivos

• Entidade participa de forma mutuamente

exclusiva de dois ou mais relacionamentos.

o Uma ocorrência da entidade que participa de um relacionamento não participa dos demais relacionamentos.

Refinamento do Modelo

Relacionamentos Mutuamente Exclusivos

• No modelo anterior, se o Cliente possui CIC (pessoa física)

não possui CNPJ (pessoa jurídica) e vice-versa. o Estas colunas são opcionais naquele diagrama. o Uma das duas colunas sempre será vazia.

• Alternativa:

• Desvantagem:

(33)

Transformações entre Modelo

Exercício Exemplo

• Considere o DER do Problema 1 da lista de exercícios DER

apresentado abaixo e faça a representação do modelo relacional correspondente: