Transformação E-R para Relacional

1. Transformação E-R para Relacional

2. 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,Obrigatoria- Opcional) Conceito(CodigoAluno, CodigoDisciplina, Ano- Semestre, Conceito) Departamento(CodigoDepartamento, Nome)

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

4. Transformação Entre Modelos Abordagem ER • é voltada à modelagem de dados de forma independente do SGBD considerado. • Representação: 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 entidade1 R entidade2

5. 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;

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

7. 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 seguintesprincí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)

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

9. 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;

10. 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 chaveprimária, prefira a implementação por uma única tabela. Veja a seguir...

11. Regras gerais de tradução: Usar implementações com menos chavesExemplo 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).

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

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

14. Passos da transformaçãoER para relacional • Tradução inicial de entidades e respectivos atributos • Tradução de relacionamentos e respectivos atributos • Tradução de generalizações/ especializações

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

16. Implementação inicial de entidades • Exemplo nome Pessoa codpess dataNasc Pessoa (CodPess, Nome, DataNasc)

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

18. Nomes de atributose 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

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

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

24. Implementação de relacionamentos 1: 1

25. Quando ambas entidades têm participação opcional... Em relacionamento binários 1:1 (0,1) (0,1) Casamento Homens Mulheres cpfm Nome nome Data Regime cpfh CRes 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

26. Quando ambas entidades têm participação opcional...Em relacionamento binários 1:1 (0,1) (0,1) Casamento Homens Mulheres cpfm Nome regime nome Data cpfh CRes 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

27. Quando ambas entidades têm participação opcional...Em relacionamento binários 1:1 (0,1) (0,1) Casamento Homens Mulheres cpfm Nome nome Data Regime cpfh CRes Fusão é inviável – cpfm e cpfh podem opcionais -> fere a regra da PK Casamento(cpfm, cpfh, data, regime, Nomeh, Nomem)

28. 1: 1 - Quando ambas entidades têm participação opcionaldiscussã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

29. 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) (0,1) Posse Cartões Magnéticos Clientes Nro Data_exped Codcli Nome Alternativa preferida (fusão) Clientes(Codcli,Nome,Nro,Data_exped) Ruim -se existem poucos clientes com cartão

30. 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) (0,1) Posse Cartões Magnéticos Clientes Nro Data_exped Codcli Nome Pode ser usada (Adição de colunas) Clientes(Codcli,Nome) CartõesMagnéticos(Nro, Codcli,Data_exped) Codcli referencia clientes

31. 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) (0,1) Posse Cartões Magnéticos Clientes Nro Data_exped Codcli Nome 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

32. 1: 1 - opcional/ obrigatóriadiscussã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

33. Ambas Entidades têm participação ObrigatóriaRelacionamentos binários do tipo 1:1 Deve ser usado fusão de tabelas quando ambas entidades tem participação obrigatória Nro Local Responsável (1,1) (1,1) Organização Eventos Comissões Code Nome 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)

34. 1: 1 - Ambas obrigatóriasDiscussã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

35. Relacionamentos 1: n

36. 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) (0,N) Empregado Lotação Departamento Contratacao CodE Nome Nome CodDep DataLotação Esquema relacional correspondente: Departamento(CodDep,Nome) Empregado(CodE,Nome,contratacao, CodDep, datalotacao) CodDep referencia Departamento

37. 1: n - Caso 1 - A entidade que tem cardinalidade máxima 1 é obrigatória (0,N) (1,1) lotação • Empregados Departamento Data Nome Idade CPF Codd Nome (0,N) (1,1)  (1,1) (0,N) Adição de colunas é a preferida Departamentos(Codd,Nome) Empregados(CPF,Nome,Idade,Data,Codd) Codd referencia Departamento

38. 1: n - caso 1 - A entidade que tem cardinalidade máxima 1 é obrigatória (0,N) (1,1) lotação • Empregados Departamento Data Nome Idade CPF Codd Nome (0,N) (1,1)  (1,1) (0,N) 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

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

40. Caso 2 - Em relacionamentos 1:n onde ambas entidades tem participação opcional • - adição coluna – preferida; - tabela própria – pode ser usada; (0,1) (0,N) posse • Pessoas Automóveis Data Idade modelo cpf Coda Nome Preferida: Adição de Coluna Pessoas(cpf,Nome,Idade) Automóveis(Coda,modelo,cpf, data) cpf referencia pessoa

41. Caso 2 - Em relacionamentos 1:n onde ambas entidades tem participação opcional (0,1) (0,N) posse • pessoas automoveis Idade modelo Data cpf coda Nome Pode ser usada Tabela própria: pessoas(cpf,Nome,Idade) automóveis(Coda,modelo) posse(cpf,coda,Data) cpf referencia pessoa coda referencia automoveis

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

43. Ambas entidades têm participação obrigatória Em relacionamentos binários do tipo 1:N • Adição de coluna – somente! (1,N) (1,1) lotação • Empregados Departamento Data Nome Idade CPF Codd Nome Trad. Preferida e única Departamentos(Codd,Nome) Empregados(CPF,Nome,Idade,Data,Codd) Codd referencia Departamento

44. Tabela de regras 1:n (0,1) (0,1) (1,1) (1,1) (0,n) (1,n) (0,n) (1,n) Relacionamento 1:n Tabela própria Adição coluna Fusão Tabelas +- ok x +- ok x x ok x x ok x

45. Tradução de entidade com relacionamento identificador IdDep Nome (1,1) (0,N) Vínculo Empregados Dependentes Entidade fraca cpfemp nome Modelo Relacional: Empregado (cpfemp, nome) Dependentes (CPFemp, IdDep, Nome) CPFemp referencia Empregados Um dependente é identificado, isto é, a CHAVE PRIMÁRIA é composta pelo cpf do empregado ao qual ele está vinculado e pelo IdDep do dependente

46. Tradução de entidade com relacionamento identificador IdDep Nome (1,1) (0,N) Vínculo Empregados Dependentes Entidade fraca cpfemp nome Relacionamento Identificador Modelo Relacional: Empregado (cpfemp, nome) Dependentes (CPFemp, IdDep, Nome) CPFemp referencia Empregados CPFemp comporá a PK de Dependentes

47. Implementação inicial de entidades Tradução de entidade - relacionamento identificador

48. Exercício: Qual a chave primáriada tabela Dependente?

49. Relacionamentos n: n

50. Relacionamentos n: n