José Maria Monteiro Mestrado em Computação - UFC

1. Modelo Entidade-Relacionamento José Maria Monteiro Mestrado em Computação - UFC

2. Modelo Entidade-Relacionamento Modelo de dados baseado na percepção do mundo real que consiste em um conjunto de objetos básicos chamados entidades e nos relacionamentos entre esses objetos. • Foi desenvolvido para facilitar o projeto de banco de dados. • Um esquema neste modelo especifica a estrutura lógica geral do banco de dados.

3. Entidades e Conjuntos-Entidade • Entidade • É um objeto que existe e é distinguível dos outros objetos. Ex: O Zé com CPF 87464553-8 é uma entidade, pois identifica unicamente uma pessoa particular no universo. • Conjunto-Entidade • Conjunto de entidades do mesmo “tipo”. Ex: O conjunto de todas as pessoas com conta num banco pode ser definido como o conjunto-entidade cliente.

4. Entidades e Conjuntos-Entidade • Uma entidade é representada por um conjunto de atributos. • Para cada atributo existe um conjunto de valores possíveis (Domínio). • Formalmente, um atributo é uma função que mapeia um conjunto de entidades em um domínio. Então, toda entidade é descrita por um conjunto de pares (atributo,valor), um para cada atributo do conjunto-entidade.

5. Entidades e Conjuntos-Entidade • Um banco de dados inclui uma coleção de conjuntos-entidade, cada qual contendo qualquer número de entidades do mesmo tipo.

6. Relacionamentos e Conjuntos-Relacionamento • Relacionamento • É uma associação entre diversas entidades. Ex: O Zé é um cliente com conta bancária número 401. • Conjunto-Relacionamento • Conjunto de relacionamentos do mesmo “tipo”. Ex: O conjunto de todos os relacionamentos entre clientes e suas respectivas contas.

7. Entidades e Conjuntos-Entidade • Formalmente, um conjunto-relacionamento, é a relação matemática em n  2 conjuntos-entidade. Se E1, E2 , ... , En são conjuntos-entidade então o conjunto-relacionamento R é um subconjunto de {(e1 , e2 , ... , en) e1  E1 , ... en  En } onde (e1 , e2 , ... , en) é um relacionamento. • O papel que uma entidade exerce num relacionamento é chamada de função.

8. Entidades e Conjuntos-Entidade • Um relacionamento pode também ter atributos descritivos. Ex: A data pode ser um atributo do conjunto relacionamento ClienteConta, a fim de indicar a última data na qual o cliente acessou a sua conta.

9. Restrições de Mapeamento Podemos definir certas restrições com as quais o conteúdo do banco de dados tem de estar de acordo. • Uma restrição importante é o mapeamento de cardinalidade, que expressa o número de entidades as quais outra entidade pode ser associada via um conjunto-relacionamento.

10. Restrições de Mapeamento • Um-para-um • Uma entidade em A está associada no máximo a uma entidade em B, e uma entidade em B está associada no máximo a uma entidade em A. • Um-para-muitos • Uma entidade em A está associada a qualquer número de entidades em B. Uma entidade em B, entretanto, está associada a no máximo uma entidade em A. NB. Consideremos um conjunto-relacionamento R (Binário) entre os conjuntos-entidade A e B.

11. Restrições de Mapeamento • Muitos-para-um • Uma entidade em A está associada no máximo a uma entidade em B, e uma entidade em B está associada a qualquer número de entidades em A. • Muitos-para-muitos • Uma entidade em A está associada a qualquer número de entidades em B. Uma entidade em B está associada a qualquer número de entidades em A.

12. Restrições de Mapeamento A dependência de existência forma uma outra importante classe de restrições. • Se a existência da entidade x depende da existência da entidade y, então x é dito existente-dependente de y. A entidade Y é chamada de entidade dominante, e x é chamada de entidade subordinada.

13. Conjunto-Entidade Fraco X Conjunto-Entidade Forte • Conjunto-Entidade Fraco • Quando o conjunto-entidade não tem atributos suficientes para formar uma chave primária. • Conjunto-Entidade Forte • Conjunto-entidade que possui uma chave primária. Um membro de um conjunto-entidade forte é por definição uma entidade dominante, enquanto um membro de um conjunto-entidade fraco é uma entidade subordinada.

14. Diagrama Entidade-Relacionamento (DER) A estrutura lógica geral de um banco de dados pode ser expressa graficamente por um diagrama E-R. • Retângulos : Representam Conjuntos-Entidades Fortes • Retângulo com Duplo Contorno: Representam Conjuntos-Entidades Fracos. • Elipses: Representam Atributos. • Losangos: Representam Conjuntos-Relacionamentos • Linhas: Ligam Atributos a Conjunto-Entidade, Atributos a Conjunto-Relacionamento e Conjunto-Entidade a Conjuntos-Relacionamentos. • Label sobre uma Linha: Representa Indicadores de Papéis.

15. Diagrama Entidade-Relacionamento (DER) rua nome numero CPF data saldo cidade cliente conta cliente conta

16. DER - Generalização numero taxa-juros saldo bonus conta É UM Conta-poupança Conta-corrente

17. DER - Agregação id id nome numero maquinaria horas funcion. projeto trabalho usa Não é possível expressar relacionamentos entre relacionamentos.

18. DER - Agregação id id nome numero maquinaria horas funcion. projeto trabalho Entidade de nível superior chamada trabalho. Agregação é uma abstração através da qual relacionamentos são tratados como entidades. usa

19. Modelo Entidade-Relacionamento • Conclusões • O modelo de dados entidade-relacionamento fornece um alto grau de flexibilidade no projeto de um banco de dados para modelar um determinado empreendimento.

20. Modelo Entidade-Relacionamento • Exercícios

21. Diagrama Entidade-Relacionamento id id descrição descrição valor unidade produto departamento quant. q.max id descrição q.min. loc. estoque

22. Diagrama Entidade-Relacionamento id É UM nome endereço descrição cpf e. civil sexo dt. nasc. pessoa N 1 é de tp. pessoa fone nm. contato cgc salário tel contato fornecedor funcionário cliente nm. contato tel contato

23. Diagrama Entidade-Relacionamento É UM É UM É UM nome endereço pessoa fone e. civil razão sexo cgc dt. nasc. pessoa física Pessoa jurídica cpf cliente funcionário fornecedor

24. Diagrama Entidade-Relacionamento funcionário N departamento 1 trabalha N horas ocupa descrição id 1 salário base cargo

25. Diagrama Entidade-Relacionamento produto N loc. estoque N armazena quant.

26. Diagrama Entidade-Relacionamento cliente 1 compra N faz N quant id inclui vl.unit. desconto N produto

27. Diagrama Entidade-Relacionamento fornecedor 1 venda N faz N quant id inclui vl.unit. desconto N produto

28. Diagrama Entidade-Relacionamento tipo pessoa 1 movimentação N faz N quant id inclui vl.unit. desconto N produto

29. Mapeamento ER-Relacional • Primeiro Passo produto id descrição unidade valor quant. q.min. q.max departamento id descrição local de estoque id descrição tipo pessoa id descrição

30. Mapeamento ER-Relacional • Primeiro Passo pessoa id cpf cgc nome e. civil sexo dt. nasc. funcionário compra id salário id desconto cliente venda nm contato id id desconto fornecedor nm contato id

31. Mapeamento ER-Relacional • Segundo Passo

32. Mapeamento ER-Relacional • Terceiro Passo

33. Mapeamento ER-Relacional • Quarto Passo pessoa id cpf cgc nome e. civil sexo dt. nasc. id tp. pessoa funcionário id depart. horas id cargo id salário compra id desconto id cliente venda id desconto id fornecedor

34. Mapeamento ER-Relacional • Quinto Passo estoque id prod. id loc. est. quant. compra itens id compra id prod.. quant. vl. unit. venda itens id venda id prod.. quant. vl. unit.

35. Mapeamento ER-Relacional • Sexto Passo endereço id pessoa rua cep cidade bairro uf país telefone id pessoa ddd fone

36. Mapeamento ER-Relacional • Sétimo Passo

37. Microsoft SQL Server • Triggers (Gatilhos) • Um trigger é um tipo especial de stored procedure que é invocado (disparado) quando ocorre uma tentativa de modificação nos dados da tabela (relação) à qual ele está associado (vinculado). Isto ocorre quando modificações são feitas na tabela usando-se as cláusulas (operações) INSET, UPDATE ou DELETE.

38. Microsoft SQL Server • Triggers (Gatilhos) • Os triggers são usados para garantir regras de negócio e integridade dos dados, ou para ações complexas, tais como automaticamente atualizar um resumo dos dados. • Caso a execução de um trigger possa violar alguma restrição então ele não será executado. • São automaticamente disparados pelo SQL Server • Não podem ser executados diretamente por um usuário

39. Microsoft SQL Server • Triggers (Gatilhos) • Não podem ultrapassar 16 níveis • Cada tabela tem no máximo 3 triggers (para ações de INSERT, DELETE e UPDATE) • Triggers não podem ser criados sobre visões ou tabelas temporárias • Um trigger não pode incluir cláusulas SELECT que retornem resultado para o usuário

40. Microsoft SQL Server • Triggers (Gatilhos) • Não podem ser executados em um trigger: • Todos os “CREATE” • Todos os “DROP” • Alter Table e Alter Database • Select Into (Pois este comando cria uma tabela temporária)

41. Microsoft SQL Server • Triggers (Gatilhos) CREATE TRIGGER load_insert ON loan FOR INSERT AS UPDATE Copy SET on_loan = ‘Y’ FROM copy, inserted WHERE copy.isbn = inserted.isbn AND copy.copy_no = inserted.copy_no

42. Microsoft SQL Server • Triggers (Gatilhos) • Quando um UPDATE é executado sobre uma tabela que possui um UPDATE TRIGGER, as tuplas originais são movidas para uma DELETE TABLE, enquanto as tuplas atualizadas (com os novos valores) são inseridas em uma INSERTED TABLE, só então a tabela começa a ser atualizada.

43. Microsoft SQL Server • Triggers (Gatilhos) CREATE TRIGGER member_update ON member FOR UPDATE AS IF UPDATE (member_no) BEGIN RAISERROR(‘Tabela não pode ser atualizada’) ROLLBACK TRANSACTION END

44. Microsoft SQL Server • Triggers (Gatilhos) CREATE TRIGGER adult_insert ON adult FOR INSERT AS IF (SELECT COUNT(*) FROM member, inserted WHERE member.member_no = inserted.member_no) =0 BEGIN PRINT ‘A atualização não pode ser executada’ PRINT ‘Nenhuma entidade em MEMBER para este ADULT’ ROLLBACK TRANSACTION END

45. Microsoft SQL Server • Triggers (Gatilhos) CREATE TRIGGER member_delete ON member FOR DELETE AS IF (SELECT COUNT(*) FROM loan, deleted WHERE loan.member_no = deleted.member_no) > 0 BEGIN PRINT ‘A transação não pode ser executada.’ PRINT ‘Este membro possui livro não devolvido’ ROLLBACK TRANSACTION END

46. Microsoft SQL Server • Triggers (Gatilhos) ELSE DELETE reservation FROM reservation, deleted WHERE reservation.member_no = deleted.member_no

47. Microsoft SQL Server • Stored Procedures • São cláusulas SQL pré-compiladas e armazenadas no servidor • São invocadas por uma aplicação ou usuário e não automaticamente pelo SQL Server

48. Microsoft SQL Server • Stored Procedures CREATE PROC count_loanlist AS SELECT COUNT(*) FROM loanlist > EXEC count_loanlist

49. Microsoft SQL Server • Stored Procedures CREATE PROCedure book_res @member_no smallint, @title normstring = ‘%’ AS SELECT m.member_no, m.lastname, title.log_date, r.isbn FROM member m, title t, item i, reservation r WHERE m.member_no = r.member_no AND r.isbn = i.isbn AND i.title_no = t.title_no AND t.title LIKE @title AND m.member_no = @member_no GO

50. Microsoft SQL Server • Stored Procedures > EXEC book_res 7498, ‘clock%’