Base de Dados Modelo EER --> Modelo Relacional. Vitor Vaz da Silva

(1)

Base de Dados

07 - Modelo EER

--> Modelo Relacional

Vitor Vaz da Silva

(2)

Base de Dados - Índice

Passagem do Modelo E-A para o E-R

BD – ISEL - Vitor Silva 2

(3)

Base de Dados

Modelo Entidade – Associação

• Baseado na análise da realidade a modelar.

• As classes de elementos são as entidades

• As entidades interagem através de associações (relacionamentos)

Entidade = classe de objectos que possui atributos comuns.

(4)

Base de Dados

• Atributos – características

– elementares ou atómicos

– compostos (decompostos em elementares)

• Entidade = Tabela

– Obrigatórias

– Não Obrigatórias

(5)

Base de Dados

• Associações

– Unárias, Binárias, Ternárias

• Tipos de Associação

– 1 para 1 – 1 para n – n para m

(6)

Base de Dados

Atributo (descritivo)

Nome do atributo

Atributo (chave)

Nome do atributo

Entidade Fraca

Nome da Entidade

Entidade

Nome da Entidade

Associação

Nome da Associação

Associação da Entidade Fraca

assoc.

Grau 1, conectividade

obrigatória

Grau 1, conectividade

facultativa

Grau N, conectividade

obrigatória

Grau N, conectividade

facultativa

Entidade Associativa

Nome da Associação

Associação com atributo

Nome da

Associação Nome do atributo

(7)

Base de Dados

Unário

Binário

Ternário

Associativo

(8)

Base de Dados

Matricula Disciplina Aluno

Aluno n Matricula m Disciplina

Cada aluno está matriculado pelo menos numa disciplina.

Há disciplinas que não têm alunos.

Modelo Entidade - Associação

(9)

Base de Dados

IdAlu Nome Morada

IdAluno IdDisciplina

IdD Disciplina

Alunos Disciplinas

Matriculas

Modelo Entidade - Relação

Matricula Disciplina Aluno

(10)

Passagem EA-Modelo Relacional

Uma vez que temos um Modelo Conceptual do sistema a conceber, é necessária uma forma sistemática de passar os padrões do Modelo EA para o Modelo Relacional

A forma sistemática é feita à custa de regras de passagem pré-definidos

Verificar se os Esquemas de Relação se

encontram na Forma Normal desejada, e se for caso disso, normalizá-los

A Estratégia

(11)

Passagem EA-Modelo Relacional

A cada Entidade do Modelo EA corresponde, normalmente, um Esquema de Relação,

com os mesmos atributos e chave

Por cada Associação 1:1 um dos Esquemas de Relação deve ter como Chave

Estrangeira a Chave do Esquema de

Relação que representa a outra Entidade

Regras

(12)

Base de Dados

(13)

Passagem EA-Modelo Relacional

Por cada Associação 1:N o Esquema de

Relação que representa a Entidade N deve ter como Chave Estrangeira - a Chave do Esquema de Relação que representa a

Entidade do lado 1

Regras

(14)

Base de Dados

(15)

Passagem EA-Modelo Relacional

Por cada Associação M:N deve ser criado um novo Esquema de Relação que tem como Chave, a composição das Chaves de cada

uma das Entidades envolvidas na Associação

Regras

Estas regras são gerais podendo haver

interesse em adoptar regras específicas para determinadas situações

(16)

Base de Dados

(17)

Método

1 – Entidades

2 – Entidades Fracas

3 – Relacionamentos 1:1 4 – Relacionamentos 1:N 5 – Relacionamentos M:N 6 – Atributos multivalor

7 – Relacionamentos de grau superior a 2 8 – Especializações e Generalizações

9 – Categorias

(18)

Passagem EA-Modelo Relacional

Cada Entidade corresponde a um Esquema de Relação

Passagem para Relacional - Entidades

Modelo EA

Modelo Relacional

Entidade(A, B, C)

Chaves Candidatas={A}

Regras

Entidade

A B

C

(19)

Passagem EA-Modelo Relacional

Passagem para Relacional – Entidade Fraca

Modelo EA

Regras

Chave Parcial de E2

Modelo Relacional

E1(A, B) Chaves Candidatas={A}

E2(A, C, D) Chaves Candidatas={A, C}

Chave Estrangeiras ={A}

E1

A B

E2

C D

HAS

(20)

Passagem EA-Modelo Relacional

Com uma ou duas conectividades obrigatórias

Passagem para Relacional – Associação unária 1:1

Modelo EA

Modelo Relacional

E1(A_A1, B, S, A_A2)

Chaves Candidatas={A_A1}, {A_A2}

Chaves Estrangeiras - A chave candidata que não for escolhida para primária

B

E1

A

ass S

A1

A2

B

E1

A

ass S

A1

A2

(21)

Passagem EA-Modelo Relacional

Nenhum dos lados tem uma conectividade obrigatória

Passagem para Relacional – Associação unária 1:1

Modelo EA

E1(A, B)

E1_E1(A_A1, A_A2, S)

Chave Candidatas={A_A1}, {A_A2}

Chaves Estrangeiras={A_A1}, {A_A2}

Regras

B

E1

A

ass S

A1

A2

(22)

Passagem EA-Modelo Relacional

Com conectividade obrigatória apenas do lado N

(ou obrigatória em ambos os lados)

Passagem para Relacional – Associação unária 1:N

Modelo EA

Modelo Relacional E1(A_A1, B, A_A2, S)

Chaves Candidatas={A_A1}

Chaves Estrangeiras={A_A2}

Regras

Quando existe uma conectividade obrigatória para o lado um, não é garantida essa participação obrigatória pelo Esquema Relacional (necessita processamento adicional)

B

E1

A

ass S

A1

A2

B

E1

A

ass S

A1

A2

(23)

Passagem EA-Modelo Relacional

Com nenhuma das conectividades obrigatória

Passagem para Relacional – Associação unária 1:N

Modelo EA

Modelo

Relacional E1(A, B)

E1_E1(A_A1, A_A2, S)

Chaves Candidatas={A_A1}

Chaves Estrangeiras={A_A1}, {A_A2}

Regras

B

E1

A

ass S

A1

A2

(24)

Passagem EA-Modelo Relacional

Com nenhuma das conectividades obrigatória

Passagem para Relacional – Associação unária M:N

Modelo EA

E1(A, B) Chaves

Candidatas={A_A1}

E1_E1(A_A1, A_A2, S)

Chaves Candidatas={A_A1, A_A2}

Chaves Estrangeiras={A_A1}, { A_A2}

Regras

B

E1

A

ass S

A1

A2

(25)

Passagem EA-Modelo Relacional

Passagem para Relacional – Associação unária M:N

Quando existem conectividades obrigatórias:

O Esquema Relacional resultante é o mesmo quando essa obrigatoriedade não existe

No entanto, não se consegue, recorrendo apenas aos mecanismos do Modelo Relacional, garantir essa obrigatoriedade

É necessário algum processamento adicional para que essa obrigatoriedade seja garantida

B

E1

A

ass S

A1

A2

B

E1

A

ass S

A1

A2

(26)

Passagem EA-Modelo Relacional

Um dos Esquemas de Relação deve ter como Chave Estrangeira a Chave do Esquema de Relação que representa a outra Entidade

Passagem para Relacional – Associação binária 1:1

Modelo EA

E1(A, B, C) Chaves Candidatas={A}

E2(D, E, A, Z) Chaves Candidatas = {D}, {A}

Chave Estrangeira ={A}

Regras

E1

A B

C E2

D E

Associação

Z

(27)

Passagem EA-Modelo Relacional

Se a Associação tiver uma conectividade obrigatória nos dois lados

Modelo EA

E1_E2(A, B, C, D, E, Z)

Chaves Candidatas={A}, {D}

Regras

E1

A B

C E2

D E

Associação

Z

SQL, as chaves candidatas têm de ser UNIQUE

(28)

Passagem EA-Modelo Relacional

Nenhuma das conectividades é obrigatória

Modelo EA

Regras E1(A, B, C) Chaves Candidatas={A}

E2(D, E) Chaves Candidatas={D}

E1_E2(A, D, Z) Chaves Candidatas={A}, {D}

Chave Estrangeiras ={A}, {D}

E1

A B

C E2

D E

Associação

Z

(29)

Passagem EA-Modelo Relacional

Se nenhum dos lados da Associação é

obrigatório, uma das soluções possíveis de passagem para o Modelo Relacional é

utilizando três Esquemas de Relação

Outra possível solução passa por utilizar apenas dois Esquemas de Relação, ficando idêntica às soluções em que apenas um dos lados é

obrigatório.

(30)

Passagem EA-Modelo Relacional

Para representar esta situação através de dois ou três Esquemas de Relação é necessário:

a proporção entre o número de ocorrências da Associação e o número de ocorrências das

Entidades que lhe estão associadas

• se apenas uma percentagem reduzida das Entidades está de facto associada, pode-se optar por três Esquemas de Relação

• se a grande percentagem das Entidades

está associada, a situação é idêntica aquela em que apenas um dos lados é obrigatório

(31)

Passagem EA-Modelo Relacional

Com os dois lados obrigatórios (ou apenas no lado N)

Passagem para Relacional – Associação binária 1:N

Modelo EA

Regras

E2(C, D, S, A) Chaves Candidatas={C}

Chave Estrangeiras ={A}

E1

A B

E2

C D

ass

S

E1

A B

E2

C D

ass

S

(32)

Passagem EA-Modelo Relacional

Sem obrigatoriedade em nenhum dos lados

Modelo EA

Modelo Relacional

Regras E1(A,B) Chaves Candidatas={A}

E2(C,D) Chaves Candidatas={C}

E1_E2(C,A,S) Chaves Candidatas={C}

Chave Estrangeiras ={A}, {C}

E1

A B

E2

C D

ass

S

(33)

Passagem EA-Modelo Relacional

Quando existe uma conectividade obrigatória para o lado um, não é garantida essa

participação obrigatória pelo Esquema

Relacional (necessita processamento adicional)

Quando nenhum dos lados tem uma

conectividade obrigatória, pode representar-se essa situação com três esquemas de relação, embora se possam apenas utilizar dois

Esquemas (ficando semelhante quando é obrigatória do lado N) Passagem para Relacional – Associação binária 1:N

(34)

Passagem EA-Modelo Relacional

Para se representar esta situação através de dois ou três Esquemas de Relação é necessário

ter em conta a proporção entre o número de ocorrências da Associação e o número de ocorrências da Entidade “do lado N”

se apenas uma percentagem reduzida das

Entidades está de facto associada, pode-se optar por três Esquemas de Relação

se a grande percentagem das Entidades está

associada, a situação é idêntica aquela em que apenas o “lado N” é obrigatório

(35)

Passagem EA-Modelo Relacional

Passagem para Relacional – Associação binária M:N

Modelo EA

Regras

Modelo Relacional

E2(C, D) Chaves Candidatas={C}

E1_E2(A, C, S) Chaves Candidatas={A, C}

Chaves Estrangeiras={A}, {C}

Com conectividade opcional

E1

A B

E2

C D

ass

S

(36)

Passagem EA-Modelo Relacional

Passagem para Relacional – Associação binária M:N Modelo EA

Quando existem conectividades obrigatórias:

• O Esquema Relacional resultante é o mesmo quando essa obrigatoriedade não existe

• No entanto, não se consegue, recorrendo apenas aos mecanismos do Modelo

Relacional, garantir a obrigatoriedade

É necessário algum processamento adicional para que essa obrigatoriedade seja garantida

E1 A B

E2

C D

ass

S

(37)

Passagem EA-Modelo Relacional

Passagem para Relacional – Associação ternária M:N:P

Modelo EA

Modelo Relacional

E3(E, F) Chaves Candidatas={E}

E1_E2_E3(A, C, E, S) Chaves Candidatas={A, C, E}

ChavesEstrangeiras={A}, {C}, {E}

Regras

E1

A B

E2

C D

ass

S

E3

E F

(38)

Passagem EA-Modelo Relacional

Passagem para Relacional – Associação ternária M:N:1

Modelo EA

Modelo Relacional

E1_E2_E3(A, C, E, S) Chaves Candidatas={A, C}

Chaves Estrangeiras={A}, {C}, {E}

Regras

E1

A B

E2

C D

ass

S

E3

E F

(39)

Passagem EA-Modelo Relacional

Passagem para Relacional – Associação ternária M:1:1

Modelo EA

Modelo Relacional

E1_E2_E3(A, C, E, S) Chaves Candidatas={A}

Chaves Estrangeiras={A}, {C}, {E}

Regras

E1

A B

E2

C D

ass

S

E3

E F

(40)

Passagem EA-Modelo Relacional

Passagem para Relacional – Associação Exclusiva

Modelo EA

Modelo Relacional

E1(A, B, C, E) Chaves Candidatas={A}

Chaves Estrangeiras={C}, {E}

E1(A, B, Atr) Chaves Candidatas={A}

E1_E2(A, C) Chaves Candidatas={A}

E1_E3(A, E) Chaves Candidatas={A}

E1 A B

E2

C D

ass1

E3 E F

ass2

Pouco eficiente

(41)

Passagem EA-Modelo Relacional

Passagem para Relacional – Generalização / Especialização

Modelo EA

Modelo Relacional

E2(A, C, F) Chaves Candidatas={A, F}

Chaves Estrangeiras={A}

E3(A, D) Chaves Candidatas={A}

Chaves Estrangeiras={A}

Regras

E2

F E1

A B

isa

E3 D

is-a

C

E1(A, B, C, F)

Chaves Candidatas={A, F}

Apenas com subentidade E2

(42)

Passagem EA-Modelo Relacional

Mais do que uma sub-Entidade , todas elas com a mesma Chave Primária que a Entidade Generalização (E1)

O atributo atr de E1 pode ter um dos valores:

“E2”, “E3” se a Generalização for (t, e)

“E2”, “E3”, NULL se a Generalização for (p, e)

“E2”, “E3”, “E2_E3” se a Generalização for (t, s)

“E2”, “E3”, “E2_E3”, NULL se a Generalização for (p, s)

Passagem para Relacional – Generalização / Especialização

E1(A, B, atr) Chaves Candidatas={A}

E2(A, C) Chaves Candidatas = {A}

Chaves Estrangeiras = {A}

E3(A, D) Chaves Candidatas = {A}

Regras

E3 D

E1 A B

ass

C E2

d

(43)

U U

Passagem EA-Modelo Relacional

Passagem para Relacional – Categorias (União)

E1(A) Chaves Candidatas={A}

E2(A, C) Chaves Candidatas = {A, C}

E3(A, D) Chaves Candidatas = {A, D}

Regras

E3 D

C E2

A E1

Caso E1 não tenha chave cria-se uma chamada de surrogate key

(44)

EXEMPLO II

Pretende-se realizar um sistema para a gestão de programas. O sistema tem os seguintes requisitos:

•Cada programa é caracterizado por um nome, por um ano e por um código (único)

•Os programas são constituídos por um conjunto de episódios

•Cada episódio é caracterizado por um número de ordem dentro do programa e pelo nome

•Os episódios são constituídos por um conjunto de cenas

(45)

EXEMPLO II

•Cada cena é caracterizada por um número de ordem dentro do episódio e tem a data da filmagem;

•Se for uma cena de interior, tem o nome do estúdio, se for uma cena de exterior tem informação sobre o local de filmagem

•Num programa participam pessoas, algumas como actores, representado um determinado papel, outros como realizadores. Estas pessoas são caracterizadas pelo nome e BI

Para cada programa existe apenas um realizador

(46)

EXEMPLO II

Data

Dia Mês Ano

IS - A

CENA

EXTERIOR INTERIOR

estudio local

has

EPISODIO ^numero PROGRAMA ^has

nome

ano codigo

PESSOA

nome BI

realiza

participa

nome

papel

(t,e)

numero

(47)

EXEMPLO II

PROGRAMA(Código, ano, nome, realizador) Chave={Codigo}

Chave Estrangeira para PESSOA={realizador}

EPISÓDIO(Codigo,Num_Ep, nome)

Chave={Codigo,Num_Ep} entidade fraca

CENA(Codigo, Num_Ep, Num_Cen, Dia,Mês, Ano) Chave={Codigo, Num_Ep, Num_Cen}

(48)

EXEMPLO II

INTERIOR(Codigo, Num_Ep, Num_Cen, estudio) Chave={Codigo, Num_Ep, Num_Cen}

EXTERIOR(Codigo, Num_Ep, Num_Cen, local) Chave={Codigo, Num_Ep, Num_Cen}

(49)

EXEMPLO II

PESSOA(BI, Nome) Chave={BI}

PARTICIPA_PROG(BI, Codigo, papel) Chave={BI, Codigo}

Chave Estrangeira para PESSOA={BI}

Chave Estrangeira para PROGRAMA={Codigo}

PARTICIPA_CEN(BI, Codigo, Num_EP, Num_Cen) Chave={BI, Codigo, Num_EP, Num_Cen}

Chave Estrangeira para PESSOA={BI}

Chave Estrangeira para CENA

={Codigo, Num_EP, Num_Cen}

(50)

Resumo

1:1 ambos obrigatórios – gera uma entidade, a chave primária é uma qualquer chave primária das duas entidade

1:1 um obrigatório – gera duas entidades, a

chave da entidade não obrigatória tem de ser atributo na outra

1:1 nenhuma obrigatória – gera três entidades, ambas as chaves são atributos na terceira

entidade

(51)

Resumo

1:N um ou ambos obrigatórios – gera duas

entidades, a chave primária do lado 1 tem de ser atributo na entidade do lado N

1:N nenhuma obrigatória – gera três entidades, ambas as chaves são atributos na terceira

entidade

(52)

Resumo

N:M – gera três entidades, ambas as chaves são atributos na terceira entidade

(53)

Resumo

Três ou mais entidades – gera uma outra

entidade que tem como atributos as chaves de todas as entidades.

(54)

EXEMPLO I

Pretende-se definir o modelo de dados para uma escola, apenas, no que se refere aos

relacionamentos entre departamentos, cursos, disciplinas, professores, alunos, e inscrições.

Da análise do problema concluiu-se o seguinte:

Departamentos: cada departamento é

identificado por um código único dentro da escola. Possui um presidente que é um

professor desse departamento.

(continua)

(55)

EXEMPLO I

Cursos: cada curso depende de um

departamento e possui uma identificação

própria dentro do seu departamento. Possui um responsável que é um professor desse

departamento.

Disciplinas: cada disciplina depende de um

curso e, dentro deste, possui uma identificação própria. Possui um responsável que é um

professor do respectivo departamento. Cada disciplina é leccionada por um conjunto de professores. (continua)

(56)

EXEMPLO I

Alunos: cada aluno possui um número que o identifica em toda a escola e frequenta um único curso. Os alunos inscrevem-se

anualmente em várias disciplinas do seu curso e têm uma nota em resultado dessa inscrição.

(continua)

(57)

EXEMPLO I

Professores: cada professor tem um número único dentro da escola e pertence a um

departamento. Os professores podem ser presidentes do departamento e/ou

responsáveis por um curso (apenas um). Os professores também podem ser responsáveis por disciplinas (várias). Cada professor

lecciona um conjunto de disciplinas.

a)Desenhe o modelo entidade/asociação

b)Passe este modelo para o modelo relacional

(58)

EXEMPLO I

Professor Num_Pro Pres_Dep

Pertence Cod_Dep

Departamento

Resp_C Cod_Cur

Curso Has

Cod_Dis

Disciplina Has

Resp_D

Lecciona Aluno

Num_Al Frequenta

Inscreve

Ano_Lec Nota

(59)

EXEMPLO I

DEPARTAMENTO(Cod_Dep, Pres_Dep) Chave={Cod_Dep}

Chave Estrangeira para PROFESSOR={Pres_Dep}

PROFESSOR(Num_Pro, Departamento) Chave={Num_Pro}

Chave Estrangeira para DEPARTAMENTO

={Departamento}

(60)

EXEMPLO I

CURSO(Cod_Dep, Cod_Cur, Resp_C)

Chave={Cod_Dep, Cod_Cur} entidade fraca

={Cod_Dep}

Chave Estrangeira para PROFESSOR={Resp_C}

(61)

EXEMPLO I

DISCIPLINA(Cod_Dep,Cod_Cur, Cod_Dis, Resp_D)

Chave={Cod_Dep, Cod_Cur, Cod_Dis} entidade fraca

Chave Estrangeira para PROFESSOR={Resp_D}

Chave Estrangeira para CURSO

={Cod_Dep, Cod_Cur}

={Cod_Dep}

(62)

EXEMPLO I

LECCIONA(Num_Pro, Cod_Dep,Cod_Cur, Cod_Dis)

Chave={Num_Pro, Cod_Dep, Cod_Cur, Cod_Dis}

Chave Estrangeira para PROFESSOR={Num_Pro}

Chave Estrangeira para DISCIPLINA

={Cod_Dep, Cod_Cur, Cod_Dis}

(63)

EXEMPLO I

Aluno(Num_Al, Cod_Dep, Cod_Cur)

Chave={Num_Al}

Chave Estrangeira para CURSO

={Cod_Dep, Cod_Cur}

(64)

EXEMPLO I

INSCRIÇÃO(Cod_Dep, Cod_Cur, Cod_Dis, Num_Al,

Ano_Le, Nota) Chave={Cod_Dep, Cod_Cur, Cod_Dis, Num_Al,

Ano_Le}

Chave Estrangeira para ALUNO={Num_Al}

Chave Estrangeira para DISCIPLINA

={Cod_Dep,Cod_Cur,Cod_Dis}

NOTE-SE: Embora esta entidade esteja representada como associativa ela é uma entidade fraca de

Disciplina e de Aluno

(65)

(66)

(67)

Base de Dados

Referências

(68)

Base de Dados

(69)

(70)