Transformando o Modelo E-R no Modelo Relacional

1. Transformando o Modelo E-R no Modelo Relacional Gerência de Banco de Dados BCC – UFU Profa. Sandra de Amo

2. Tranformando Entidade em Tabela CREATE TABLE EMP ( CIC integer Ne char(30), End char(30), Tel integer, PRIMARY KEY (CIC) ) End Ne Tel cic Empregado

3. Transformando Relacionamento (sem restrição de chave) para Tabela Departamento did DataIn Nd Or End Ne cic Tel Empregado CREATE TABLE TRAB-EM ( CIC integer, DID char(4), End char(30), DataIn DATE, PRIMARY KEY (CID,DID,End), FOREIGN KEY (CIC) REFERENCES EMP, FOREIGN KEY (DID) REFERENCES DEP, FOREIGN KEY (End) REFERENCES LOCAL) Trabalha-em End LOCAL Area

4. Transformando Relacionamento (com restrição de chave) para Tabela DataIn End Ne cic Tel Departamento Empregado did GERENCIA Nd Or CREATE TABLE GERENCIA ( CIC integer, DID char(4), DataIn DATE, PRIMARY KEY (DID), FOREIGN KEY (CIC) REFERENCES EMP, FOREIGN KEY (DID) REFERENCES DEP) CREATE TABLE DEP ( CIC integer, DID char(4), DNOME char(20), OR REAL, DataIn DATE, PRIMARY KEY (DID), FOREIGN KEY (CIC) REFERENCES EMP)

5. Vantagens e Desvantagens • Segunda Alternativa • Somente duas tabelas (EMP, DEP) • Consultas mais rápidas • Problema: quando um departamento não tem gerente, insere-se tupla com valor NULL em CIC • Primeira Alternativa • Precisa de 3 tabelas (EMP, DEP, GERENCIA) • Consultas envolvem junções de 3 tabelas • A tabela GERENCIA só contém os dids que correspondem a departamentos com gerentes.

6. Transformando Relacionamento (com Restrição de Participação) em Tabela DataIn End Ne cic Tel Departamento Empregado did GERENCIA Nd Or Todo departamento tem um gerente e este gerente é único Trabalha-em

7. Usando a segunda alternativa... CREATE TABLE DEP ( CIC integer NOT NULL, DID char(4), DNOME char(20), OR REAL, DataIn DATE, PRIMARY KEY (DID), FOREIGN KEY (CIC) REFERENCES EMP)

8. Não é possível utilizar a primeira alternativa ! NÃO É UMA SOLUÇÃO !! Só evita que 1. se delete um gerente associado a um Departamento. 2. se delete um departamento que tem um gerente. NÃO garante que TODO departamento tem um gerente !! CREATE TABLE GERENCIA ( CIC integer NOT NULL, DID char(4) NOT NULL, DataIn DATE, PRIMARY KEY (DID), FOREIGN KEY (CIC) REFERENCES EMP, FOREIGN KEY (DID) REFERENCES DEP)

9. Logo... • Quando houver uma restrição de chave e de participação, a segunda alternativa é melhor para traduzir o relacionamento.

10. Certas restrições em relacionamentos não podem ser especificadas facilmente... DataIn End Ne cic Tel Departamento Empregado did GERENCIA Nd Or Todos os valores de CIC aparecem na tabela Trabalha-em CIC pode ser declarado em EMP como chave estrangeira Referenciando Trabalha-em ? Não pode ! Pois CIC não é chave Candidata de Trabalha-em Todos os valores de DID aparecem na tabela Trabalha-em DID poderia ser declarado em DEP Como chave estrangeira Referenciando Trabalha-em ? Não pode ! Pois DID não é chave Candidata de Trabalha-em Trabalha-em

11. Certas restrições em relacionamentos não podem ser especificadas facilmente... DataIn End Ne cic Tel Departamento Empregado did GERENCIA Nd Or Trabalha-em Tais restrições de participação só podem ser especificadas no modelo Relacional utilizando asserções em SQL (programas específicos).

12. ISA ISA Transformando Relacionamento ISA em Tabelas cic N End Sal DN Empregado Es Piloto Técnico B HV

13. Primeira Alternativa • Uma tabela EMP Atributos CIC, DN, N, End,Sal Chave Primária CIC • Uma tabela PILOTO Atributos CIC, B,HV Chave Primária CIC Chave Estrangeira : CIC referencia EMP • Uma tabela TEC Atributos CIC, Es Chave Primária CIC Chave Estrangeira CIC referencia EMP

14. Segunda Alternativa • Uma tabela PILOTO Atributos CIC, DN, N, End,Sal, B,HV Chave Primária CIC • Uma tabela TEC Atributos CIC, DN, N, End,Sal, Es Chave Primária CIC

15. Vantagens e Desvantagens • Primeira Alternativa com 3 tabelas • Mais geral • Permite fazer consultas sobre empregados que não são nem pilotos nem técnicos. • Não é a adequada quando se quer consultar atributos gerais de Pilotos e Técnicos: é preciso combinar as tabelas PILOTO e TEC com a tabela EMP.

16. Vantagens e Desvantagens • Segunda Alternativa com 2 tabelas • É adequada quando se quer consultar somente informações relacionadas a Pilotos ou Técnicos. • Não é aplicável se tivermos empregados que não são nem Pilotos nem Técnicos. • Não é aplicável se existem Pilotos que também são Técnicos e vice-versa : os dados deveriam ser armazenados duas vezes !!! • Uma consulta que pede para listar todos os empregados deve varrer duas tabelas !!!

17. Transformando Diagramas E-R com Agregação em Tabelas DataInP Departamento Projeto Patrocinado DataInM Monitorado por Empregado

18. DP CREATE TABLE PAT ( DID char(4), PID char(4), DP DATE, PRIMARY KEY (DID,PID), FOREIGN KEY (PID) REFERENCES PROJ, FOREIGN KEY (DID) REFERENCES DEP) PAT DEP PROJ DM MON EMP CREATE TABLE MONITORA ( CIC integer, DID char(4), PID char(4), DM DATE, PRIMARY KEY (CIC,DID,PID), FOREIGN KEY (CIC) REFERENCES EMP, FOREIGN KEY (DID,PID) REFERENCES PAT) PROJ PAT DEP MONITORA DEP