Banco de Dados Parte do contedo exposto nestas

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 1 Conceitos Básicos

Dado x Informação n DADO: algo conhecido, informado, mas sem tratamento sistêmico, ou seja, o DADO precisa de um processamento básico para se transformar em INFORMAÇÃO; n INFORMAÇÃO é o DADO processado ! Ex: Jogo de Baralho. Cartas/Jogada Algoritmo para fazer a soma de dois nºs X e Y. Os números são os dados e o resultado é a informação que se deseja saber. 3

Dado x Informação Ex: Imagine que o sistema armazene os seguintes itens a respeito dos funcionários de uma empresa. Número Nome Data Contratação Endereço Bairro O que é DADO e o que é INFORMAÇÃO? Cidade Os itens acima referem-se aos dados do funcionário e a partir destes dados é possível extrair informações. Ex: - O tempo que o funcionário trabalha na empresa; - O endereço do funcionário (endereço+bairro+ cidade) 4

Compartilhamento de Dados Quando a implantação da Informática nas organizações ocorre de forma gradual, é provável que ocorram alguns problemas. Suponha que uma indústria execute três funções básicas: Vendas: concentra as atividades relativas ao contado com os clientes, como fornecimento de cotações de preços, vendas e a disponibilidade de produtos Produção: concentra as atividades relativas à produção propriamente dita, como planejamento da produção, ou seja, dos produtos e controle do que foi produzido Compras: concentra as atividades relativas à aquisição de insumos necessários à produção, como cotações de preços junto a fornecedores, etc. 5

Compartilhamento de Dados SISTEMA DE COMPRAS SISTEMA PRODUÇÃO SISTEMA VENDAS Arquivos Produção Arquivos Vendas Arquivos Compras O que você vai PRODUZIR? O que você vai VENDER? O que você vai COMPRAR? PRODUTO É preciso saber quais os componentes dos produtos e como são produzidos. É preciso saber o preço do produto, seu prazo de validade, estoque. . . MATÉRIA-PRIMA do PRODUTO É preciso saber quais componentes serão adquiridos para fabricar o 6 produto

Compartilhamento de Dados Se cada uma das funções for informatizada de forma separada, pode ocorrer que, para cada uma delas, seja criado um arquivo separado para PRODUTOS. SISTEMA PRODUÇÃO SISTEMA VENDAS Arquivos Produção Arquivos Vendas Produto SISTEMA DE COMPRAS Arquivos Compras Produto 4 Dados de diferentes aplicações não estão integrados; 4 Dados estão projetados para atender uma aplicação específica. 7

Problemas da Falta de Integração de Dados 4 Redundância de Dados - o mesmo objeto da realidade é armazenado mais de uma vez no banco de dados. Ex: Produtos Redundância Controlada e Não Controlada de Dados Redundância Controlada - acontece quando o software tem conhecimento da múltipla representação e garante a sincronia entre as diversas representações. Ou seja, atualiza automaticamente os dados quando necessário. Ex: Sistemas distribuídos - um mesmo dado é armazenado em vários computadores, permitindo acesso rápido a partir de qualquer um deles. 8

Problemas da Falta de Integração de Dados Redundância Não Controlada - acontece quando a responsabilidade pela manutenção da sincronia entre as diversas representações de um dado está com o usuário. Redundância Não Controlada leva a : 4 Redigitação de Dados - o mesmo dado é digitado várias vezes no sistema. Este trabalho repetitivo pode levar a erros; 4 Inconsistência dos Dados - os dados podem não representar corretamente a realidade. Imagine que o usuário alterou o preço de um produto no sistema de compra mas não alterou no sistema de vendas. 4 Dificuldade de extração de informações - os dados projetados para atender uma aplicação específica po 9 dem gerar dificuldade para o cruzamento dos dados

Solução A solução para evitar a redundância NÃO CONTROLADA de informações. COMPARTILHAMENTO DE DADOS SISTEMA PRODUÇÃO SISTEMA VENDAS SISTEMA DE COMPRAS BANCO DE DADOS Produtos Assim, cada dado é armazenado uma ÚNICA VEZ, sendo acessada pelos vários sistemas que dele necessitam. 10

Compartilhamento de Dados SISTEMA PRODUÇÃO Arquivos Produção Produto SISTEMA PRODUÇÃO SISTEMA VENDAS Arquivos Vendas Produto SISTEMA VENDAS SISTEMA DE COMPRAS Arquivos Compras Produto SISTEMA DE COMPRAS BANCO DE DADOS Produto 11

Banco de Dados É o nome dado ao conjunto de arquivos integrados que atendem a um conjunto de sistemas BANCO DE DADOS Conjunto de dados integrados que tem por objetivo atender a uma comunidade de usuários “Uma coleção de dados operacionais inter-relacionados. Estes dados são armazenados de forma independente dos programas que os utilizam, servindo assim a múltiplas aplicações de uma organização. ” 12 (Kort, Henry F. )

Banco de Dados O que muda com o surgimento dos Bancos de Dados, ou seja, com o Compartilhamento dos Dados? 4 Acesso por múltiplos programas - pode haver mais de uma equipe de desenvolvimento envolvida no desenvolvimento de uma aplicação 4 Os programas devem garantir a Restrição de Integridade, ou seja, garantir a veracidade e a correção dos dados. Ex: Um funcionário não pode estar alocado em dois departamentos. 4 O BD pode ser acessado concorrentemente por múltiplos usuários - os programas devem implementar o controle de acesso concorrente 13

Banco de Dados 4 Restrições de Acesso - nem todo usuário pode acessar qualquer informação. O programa deve implementar o controle de acesso, ou seja, quem tem permissão para acessar o quê 4 Dados são de importância vital e não podem ser perdidos - mecanismos simples como cópias de “backup” não suficientes. Caso haja uma falha, o banco de dados deve ser recuperado rapidamente. Os programas devem implementar mecanismos de tolerância a falhas 4 Estruturas de dados mais complexas - os arquivos devem ser projetados para atender a diferentes necessidades dos sistemas, portanto, há que se tomar bastante cuidado na fase de definição dos DADOS. 14

Sistema de Gerência de Banco de Dados SGBD Software que incorpora as funções de definição, recuperação e alteração de dados em um banco de dados Aplicação Software que serve para armazenar e acessar dados em um banco de dados SGBD Banco de Dados 15

Sistema de Gerência de Banco de Dados: Vantagens 4 Independência de dados - SGBD oferece isolamento das aplicações em relação aos dados, ou seja, alterações no modelo de dados (estrutura) afeta pouco as aplicações 4 Abstração de dados - aplicações não se preocupam com detalhes físicos de implementação (localização no meio de armazenamento, existência de índices, caminhos de acesso. . ) 4 Controle de segurança - que usuário pode fazer o que sobre qual dado 4 Tolerância a falhas - recuperação em caso de falha imperceptível ao usuário 4 Controle a acesso concorrente - muitos usuários 16 acessando o banco ao mesmo tempo

Quatro Gerações de Gerenciamento de Dados SGBD’s OO (Versant, Objectivity) SGBD’s RELACIONAIS (DB 2, SQL Server, Oracle) SGBD’s Hierárquicos (IMS) e em Rede (CODASYL) Sistemas de Gerenciamento de Arquivos(ISAM, VSAM) 1960 1970 1980 1990 2000 17

Projeto de Banco de Dados - Aplicação Mas e daí, onde vou aplicar isto? Quando estudamos as metodologias de desenvolvimento de sistemas, estudamos análise de requisitos e definimos o que é necessário ser executado, quais as rotinas devem ser desenvolvidas para atender as necessidades do cliente, isto é, quais as informações que o cliente necessita para ter sucesso em seu negócio. Porém, para obter estas informações, é preciso definir quais são os dados que devem ser armazenados no banco de dados para que, posteriormente, possamos devolver ao cliente, as informações que satisfaçam as exigências definidas por ele, ou seja, as necessidades de informação do negócio. 18

Ainda não entendi o que afinal de contas eu vou aprender nesta disciplina!!! 19

Parte 2 Abordagem Entidade-Relacionamento

Objetivos n Compreender os conceitos de ENTIDADE e algumas de suas características: RELACIONAMENTO, ATRIBUTO, CARDINALIDADE 21

Abordagem Entidade-Relacionamento n A primeira etapa do projeto de um banco de dados é a construção de um modelo conceitual, a chamada Modelagem Conceitual MODELO CONCEITUAL MODELO LÓGICO MODELO FÍSICO n A Modelagem Conceitual tem por objetivo obter uma descrição abstrata, independente de implementação em computador, dos dados que serão armazenados 22 no banco de dados.

Abordagem Entidade-Relacionamento n Dentre as técnicas mais difundidas e utilizadas para a modelagem conceitual dos dados destacam-se: – a Abordagem Entidade-Relacionamento, definida por Peter Chen em 1976 e que segue a metodologia de desenvolvimento Estruturado de Sistemas – a UML (Unified Modeling Language), que é uma metodologia de desenvolvimento Orientado a Objeto n O Modelo Entidade Relacionamento (M. E. R. ) é representado graficamente pelo Diagrama Entidade Relacionamento (D. E. R. ) e este é convertido para o Modelo Relacional/Lógico para ser implementado fisicamente num Banco de Dados Relacional. 23

Abordagem Entidade-Relacionamento n A UML é uma excelente metodologia, porém, até este momento, depara-se com um grande problema: ainda não existe um Banco de Dados totalmente Orientado a Objeto. n Para solucionar tal problema, a UML utiliza um procedimento denominado “Mapeamento Objeto. Relacional”, Relacional de forma a permitir que as estruturas definidas no modelo Orientado a Objeto possam ser implementadas em um Banco de Dados Relacional. 24

Modelo Entidade-Relacionamento n Peter Chen, ao formular a proposta do modelo E-R baseou-se na compreensão da realidade em que se situava o problema e não na visão de um sistema de aplicação. n CHEN preocupou-se em destacar a importância de reconhecer os objetos (coisas) que compõem este negócio, independentemente de preocupar-se com formas de tratamento das informações, procedimentos, programas, etc n Estes objetos ele classificou em dois grupos: ENTIDADE e RELACIONAMENTO 25

Abordagem Entidade-Relacionamento Faz Contém PEDIDO CLIENTE PRODUTO O fato acima pode acontecer em qualquer realidade. Ele deve, portanto, ser retratado através de elementos 26 básicos que compõem o Modelo ER.

Modelo Entidade-Relacionamento (M. E. R. ) n Os componentes básicos do Modelo ER são: ENTIDADES RELACIONAMENTOS ATRIBUTOS 27

Modelo ER: ENTIDADE n “Conjunto de objetos da realidade modelada sobre os quais deseja-se manter informações no Banco de Dados” (Heuser). Considera-se objeto qualquer coisa perceptível ou manipulável. n É uma “coisa” ou um “objeto” no mundo real que pode ser identificada de forma única em relação aos outros objetos. n São as “coisas” que existem no negócio sobre as quais temos interesse em manter armazenadas no banco de dados. 28

Modelo ER: ENTIDADE n Uma ENTIDADE é uma representação de um CONJUNTO DE DADOS do negócio, um conjunto de informações de mesmas características e suas ocorrências. n É representada através de um retângulo com o nome da entidade em seu interior. CLIENTE PRODUTO NOTA FISCAL FUNCIONÁRIO ORDEM DE PRODUÇÃO 29

Modelo ER: ENTIDADE Exemplo: O retângulo CLIENTE representa o conjunto de todas as pessoas sobre as quais se deseja manter informações no BD. . CLIENTE Este objeto particular (um dos clientes) é chamado de OCORRÊNCIA de uma entidade, neste caso CLIENTE. 30

Modelo ER: ENTIDADE n As ocorrências de uma entidade não são representadas no DER mas são semanticamente interpretadas no mesmo, ou seja, ao visualizar uma entidade, devemos entendê-la como uma tabela de dados, onde cada linha representa uma ocorrência da mesma. FUNCIONÁRIO Matrícula Nome 4456 João Carlos da Silva 29/04/91 6689 Sílvia de Oliveira 26/02/92 1203 Carla Martinez 14/04/92 Data Admissão 31

Modelo ER: ENTIDADE Exemplo: n Quais são as “coisas” que vocês conseguem identificar nos LABORATÓRIOS de INFORMÁTICA da UNINOVE ? n n n Máquinas Bancadas Pessoas Quadro-negro Canetas Ar-condicionado 32

Modelo ER: ENTIDADE PERGUNTA 1 !! Todas estas “coisas” identificadas deveriam ter seus dados armazenados, caso nós quiséssemos desenvolver um Sistema para Controlar os Equipamentosdos Laboratórios de Informática? NÃO!!! Pois se quero controlar equipamentos, a entidade PESSOA, por exemplo, não teria importância alguma no contexto 33

Modelo ER: ENTIDADE PERGUNTA 2 !! Se ao invés do caso anterior, nós quiséssemos desenvolver um sistema para controlar não somente os Equipamentos existentes, mas também a Utilização dos Laboratórios ? Neste caso temos que lembrar quem utiliza, ou seja, as PESSOAS são de interesse do sistema 34

Modelo ER: PROPRIEDADES n Além de especificar as entidades, ou seja, os objetos sobre os quais se deseja manter informações, o MER deve permitir a especificação das PROPRIEDADES destas entidades. n Estas propriedades são : Ter um ATRIBUTO Participar de um Relacionamento 35

Modelo ER : ATRIBUTO n Dado que é associado a cada ocorrência de uma entidade ou de um relacionamento (características específicas) 36

Modelo ER: ATRIBUTO Ex 1: Projeto • Em uma entidade Projeto, por exemplo, poderá ser importante armazenar o Código, o Tipo e no nome do Projeto. A representação gráfica deverá ficar, então: ENTIDADE PROJETO tipo código nome ATRIBUTOS 37

Modelo ER: ATRIBUTO Ex 2: Funcionário • Vamos supor que em uma empresa temos uma entidade chamada Funcionario, ou seja, um objeto sobre o qual desejamos manter informações. O que descreve FUNCIONÁRIO? - um número de matrícula, o nome do funcionário, sua data de admissão, data de nascimento, valor do salário, . . . FUNCIONÁRIO Número Matrícula Nome Data Admissão Data Nascimento Valor Salário 38

Modelo ER: ATRIBUTO Cada ocorrência de Funcionário será formada por valores nestes atributos e o conjunto destes valores representa a informação de um funcionário que devemos visualizar como uma linha de uma tabela de dados. Entidade: Funcionário Matrícula Nome Data Admissão 29/04/91 4456 João Carlos da Silva 6689 Sílvia de Oliveira 26/02/92 1203 Carla Martinez 14/04/92 7702 Pedro Guilherme Souza 01/01/92 39

Modelo ER: ATRIBUTO CLIENTE Telefone Endereço Nome CPF Os atributos podem ser de vários tipos: monovalorado: possui apenas um valor que não pode ser decomposto. Ex: CPF multivalorado: possui vários valores na mesma ocorrência. Ex: Telefone composto: possui vários valores sobre o mesmo nome e quando decomposto não perde o sentido. Ex: Nome, Endereço 40

Modelo ER: ATRIBUTO IDENTIFICADOR Cada entidade deve possuir um identificador!!! IDENTIFICADOR é um conjunto de um ou mais atributos (e possivelmente relacionamentos) cujos valores servem para distinguir uma ocorrência da entidade das demais ocorrências da mesma entidade PESSOA código nome endereço Identificador simples DISCIPLINA Cód. Departamento Cód. Disciplina Nome da disciplina Identificador composto 41

Modelo ER: ATRIBUTO IDENTIFICADOR O identificador de uma Entidade deve obedecer UMA propriedade: Deve ser MÍNIMO isto é, se retirarmos um dos atributos ou relacionamentos que o compõe, ele deixa de ser identificador PESSOA código nome endereço Não é necessário utilizar Código e nome para identificar a entidade. Código é suficiente para distinguir as ocorrências de PESSOA 42

Modelo ER: RELACIONAMENTO n Conjunto de associações entre entidades através de algo comum. DEPARTAMENTO LOTAÇÃO PESSOA • Um conjunto de objetos classificados como pessoa (Entidade PESSOA) ; • Um conjunto de objetos classificados como departamento (Entidade DEPARTAMENTO); • Um conjunto de ASSOCIAÇÕES, cada uma ligando um departamento a uma pessoa (relacionamento 43 LOTAÇÃO);

Modelo ER: RELACIONAMENTO No nosso dia-a-dia convivemos com os mais variados tipos de entidades (objetos reais), que são descritos por uma série de atributos (características) e que expressam uma realidade de existência. Estas entidades do dia-a-dia estão relacionadas de forma a mostrar a realidade com um conteúdo lógico: ¨ As pessoas Moram em Apartamentos; ¨ Os apartamentos Formam Condomínios; ¨ Os condomínios Localizam-se em Ruas ou Avenidas; ¨ As Avenidas e Ruas Estão em uma Cidade 44

Modelo ER: RELACIONAMENTO n Assim como ocorre com as entidades, temos as ocorrências de relacionamentos. n Isto pode ser melhor observado através do Diagrama de Ocorrências. Nele, ocorrências de entidades são representadas por círculos brancos e de relacionamentos por círculos pretos. 45

Modelo ER: RELACIONAMENTO n Neste caso, uma ocorrência seria um par específico formado por uma determinada ocorrência da entidade PESSOA e por uma determinada ocorrência da entidade DEPARTAMENTO p 3 p 7 p 8 Entidade p 1 p 2 p 4 p 5 PESSOA Relacionamento LOTAÇÃO DEPARTAMENTO p 4, d 2 p , d p 1, d 1 p , d 5 3 2 1 Entidade d 1 d 2 d 3 46 Diagrama de ocorrências

Modelo ER: RELACIONAMENTO Assim como Entidade, Relacionamentos também podem possuir atributos ENGENHEIRO código nome (0, n) ATUAÇÃO Função (0, n) PROJETO código título No exemplo, ATUAÇÃO possui um atributo (Função), ou seja, o papel que um engenheiro deve desempenhar dentro de um projeto. Função ENGENHEIRO Função PROJETO 47

Modelo ER: CARDINALIDADE (mínima e máxima) num relacionamento É o número (mínimo, máximo) de ocorrências de uma entidade associadas a uma ocorrência de outra entidade através do relacionamento 48

Modelo ER: LEITURA da CARDINALIDADE HOMEM CASADO ? MULHER PERGUNTA: PERGUNTA Um homem pode estar casado com quantas mulheres? RESPOSTA: Um homem pode não ser casado com NENHUMA mulher, portanto a cardinalidade mínima é “ 0”; Um homem pode se casar com no máximo UMA mulher, portanto, a cardinalidade máxima é “ 1”; HOMEM CASADO (0, 1) MULHER 49

Modelo ER: LEITURA da CARDINALIDADE HOMEM ? CASADO MULHER PERGUNTA: PERGUNTA Uma mulher pode estar casada com quantos homens? RESPOSTA: Uma mulher pode não ser casada com NENHUM homem, portanto a cardinalidade mínima é “ 0”; Uma mulher pode se casar com no máximo UM homem, portanto, a cardinalidade máxima é “ 1”; HOMEM (0, 1) CASADO MULHER 50

Modelo ER: LEITURA da CARDINALIDADE CASADO HOMEM (0, 1) MULHER CASADO MULHER (0, 1) MULHER 51

Modelo ER: Cardinalidade MÍNIMA n Cardinalidade Mínima é o número mínimo de ocorrências de uma entidade associadas a uma ocorrência de outra entidade num relacionamento n Consideram-se apenas duas cardinalidades: Opcional (“ 0”) indica que o relacionamento existe independente de haver ou não uma ocorrência de uma entidade ligada à outra Obrigatória (“ 1”) indica que o relacionamento deve obrigatoriamente associar uma ocorrência de uma entidade a uma ocorrência de outra entidade 52

Modelo ER: Cardinalidade MÍNIMA EMPREGADO (0, n) ALOCAÇÃO (1, 1) DEPARTAMENTO n n Cada empregado deve estar obrigatoriamente alocado a um setor-departamento (“ 1”) Um setor-departamento pode existir mesmo que não exista nenhum empregado alocado nele (“ 0”) 53

Modelo ER: Cardinalidade MÁXIMA n Cardinalidade Máxima é o número máximo de ocorrências de uma entidade associadas a uma ocorrência de outra entidade num relacionamento n Consideram-se apenas duas cardinalidades: “ 1” indica que uma ocorrência de uma determinada entidade pode estar associada a no máximo UMA ocorrência da entidade relacionada a ela “n” indica que uma ocorrência de uma determinada entidade pode estar associada a muitas ocorrências da entidade relacionada a ela 54

Modelo ER: Cardinalidade MÁXIMA EMPREGADO (0, n) LOTAÇÃO Uma ocorrência de empregado pode estar associada a no máximo uma (“ 1”) ocorrência de departamento, isto é, empregado tem cardinalidade máxima 1 no relacionamento Lotação (1, 1) DEPARTAMENTO Uma ocorrência de departamento pode estar associada a muitas (“n”) ocorrências de empregado, isto é, Departamento tem cardinalidade máxima n no relacionamento Lotação 55

Modelo ER: TIPO DE RELACIONAMENTO Para a descoberta do tipo de relacionamento devemos analisar de forma macro a possibilidade de relacionamentos entre as entidades, sendo que a ocorrência de maior valor é que determina sempre o tipo do relacionamento (cardinalidade máxima). São eles: n n n 1: 1 1: N N: N 56

Modelo ER: TIPO DE RELACIONAMENTO n Relacionamento de 1: 1 Cada elemento de uma entidade relaciona-se com um e somente um elemento de outra entidade HOMEM MULHER A • • X • Y • Z • W B • C • D • HOMEM (0, 1) CASADO (0, 1) MULHER 57

Modelo ER: TIPO DE RELACIONAMENTO n Exemplo Relacionamento de 1: 1 (0, 1) DIVISÃO GERENCIADA (0, 1) GERÊNCIA Cada divisão é gerenciada por UM e apenas UM gerente Cada gerente administra UMA e apenas UMA divisão 58

Modelo ER: TIPO DE RELACIONAMENTO n Relacionamento de 1: N Cada elemento entidade A relaciona-se com muitos elementos entidade B, mas cada elemento da entidade B pode estar relacionado a um elemento entidade A MÃE A • B • C • n da da só da FILHO • a • b • c • d • e • f Este tipo de relacionamento é o mais comum no mundo real, entretanto, possui características específicas quanto ao sentido de leitura dos fatos e 59 sua interpretação

Modelo ER: TIPO DE RELACIONAMENTO n Exemplo Relacionamento de 1: N MÃE POSSUI (1, 1) (1, n) FILHO POSSUI A cardinalidade determinante é sempre a máxima obtida da interpretação dos fatos MÃE (1, 1) POSSUI (1, n) FILHO 60

Modelo ER: TIPO DE RELACIONAMENTO n Regra geral: um relacionamento é do tipo 1: N quando um sentido de leitura dos fatos nos apresenta a cardinalidade máxima de 1: N e o sentido oposto apresenta obrigatoriamente cardinalidade máxima de 1: 1 MÃE EMPREGADO (1, 1) POSSUI (1, n) (0, n) FILHO DEPENDENTE 61

Modelo ER: TIPO DE RELACIONAMENTO n Relacionamento de N: N Em ambos os sentidos de leitura encontramos uma cardinalidade máxima de 1: N, o que caracteriza ser então um contexto geral de N: N ESTUDANTE DISCIPLINA E 1 • • D 1 • D 2 • D 3 • D 4 E 2 • E 3 • E 4 • E 5 • 62

Modelo ER: TIPO DE RELACIONAMENTO n Exemplo Relacionamento de N: N ALUNO (0, n) CURSA (0, n) DISCIPLINA 63

Modelo ER: TIPO DE RELACIONAMENTO CURSA ALUNO E 1 • E 2 • E 3 • E 4 • E 5 • 1 • 2 • 3 • 4 • 5 • 6 • 7 • 8 • DISCIPLINA • D 1 • D 2 • D 3 • D 4 64

Modelo ER: TIPO DE RELACIONAMENTO n Exemplo Relacionamento de N: N FORNECEDOR (0, n) FORNECE (0, n) PRODUTO Vl_Unit Cada produto é fornecido por UM ou MUITOS fornecedores Cada fornecedor fornece UM ou MUITOS produtos Este tipo de relacionamento caracteriza-se por apresentar atributos, isto é, o relacionamento possui dados que são inerentes ao fato e não as entidades 65

Modelo ER: IDENTIFICANDO ENTIDADES IDENTIFICADOR Relacionamento quando o identificador de uma entidade é composto por atributos da própria entidade e também por relacionamentos dos quais a entidade participa código Número seqüência nome EMPREGADO nome (1, 1) (0, n) DEPENDENTE CADA dependente está relacionado a exatamente UM empregado um dependente é identificado através do código do empregado ao qual ele está relacionado e por um número de seqüência que distingue os diferentes dependentes de um mesmo empregado Alguns autores chamam esta entidade de “FRACA” pois ela só existe relacionada à outra entidade 66

Modelo ER: GRAU DE RELACIONAMENTO É o número de entidades ligadas num mesmo relacionamento. São eles: n n Grau 1 ou Auto-relacionamento Grau 2 ou Binário Grau 3 ou Ternário N-ário (acima de 3 entidades) 67

Modelo ER: GRAU DE RELACIONAMENTO n Grau 1 ou Auto-relacionamento Quando existe n apenas uma entidade envolvida num relacionamento, ou seja, uma entidade se relacionando com ela mesma. Neste caso, é necessário definir o papel da entidade no relacionamento, ou seja, a função que a entidade exerce dentro do relacionamento PESSOA marido esposa CASAMENTO p 3 p 7 p 1 p 2 p 4 p 8 p 5 marido esposa p 2, p 3 p 4, p 5 Uma ocorrência de pessoa exerce o papel de marido e a outra ocorrência exerce o papel de esposa 68

Modelo ER: GRAU DE RELACIONAMENTO n Grau 2 ou Binário é quando existem duas entidades envolvidas num mesmo relacionamento. 1: 1 Um para Um HOMEM 1 CASADO 1 MULHER 1: N Um para Muitos ALUNO n INSCRIÇÃO 1 CURSO N: N Muitos para muitos MÉDICO n ATENDE n PACIENTE 69

Modelo ER: GRAU DE RELACIONAMENTO n Grau 3 ou Ternário é quando existem três entidades envolvidas num mesmo relacionamento. CIDADE DISTRIBUIDOR n 1 DISTRIBUIÇÃO n PRODUTO Cada ocorrência do relacionamento DISTRIBUIÇÃO associa três ocorrências de entidade: - um produto a ser distribuído, - uma cidade na qual é feita a distribuição e 70 - um distribuidor

Modelo ER: GRAU DE RELACIONAMENTO n Neste caso analisaremos PARES de entidades DISTRIBUIDOR CIDADE n 1 DISTRIBUIÇÃO n A cardinalidade “ 1”refere-se a um par cidade produto PRODUTO Cada par de ocorrências de Cidade e Produto está relacionado a NO MÁXIMO um distribuidor , isto é, em cada cidade só pode haver um distribuidor para cada produto. 71

Modelo ER: GRAU DE RELACIONAMENTO CIDADE n 1 DISTRIBUIDOR DISTRIBUIÇÃO n PRODUTO n n n (Cidade, Produto) está associado a no Máximo 1 Distribuidor Cada produto só pode ter um distribuidor em cada cidade (Cidade, Distribuidor) está associada a MUITOS produtos um distribuidor pode distribuir muitos produtos em uma cidade (Distribuidor, Produto) está associado a MUITAS cidades um produto pode ser distribuído em muitas cidades por um distribuidor 72

Modelo ER: EXERCÍCIOS 1) Explique a diferença entre uma entidade e uma ocorrência de entidade. 2) Observe o MER e responda às questões: (0, n) DEPARTAMENTO (1, 1) RESPONSÁVEL (0, n) PRÉ_REQUIS (0, n) DISCIPLINA (0, n) DISC-CURSO ALUNO (0, n) INSCRIÇÃO (1, 1) (0, n) CURSO 73

Modelo ER: EXERCÍCIOS n n Identifique as entidades e os relacionamentos do modelo; Interprete cada um dos relacionamentos abaixo, identificando o tipo de cardinalidade e o grau do relacionamento: a) DEPARTAMENTO (1, 1) b) DISCIPLINA RESPONSÁVEL ALUNO (0, n) INSCRIÇÃO (0, n) c) d) PRÉ_REQUIS (0, n) (1, 1) DISCIPLINA (0, n) CURSO (0, n) DISC-CURSO DISCIPLINA CURSO (0, n) 74

Modelo ER: EXERCÍCIOS 3) Identifique a Cardinalidade dos relacionamentos, exibindo os passos conforme o exemplo: Um Aluno DEVE estar inscrito em no mínimo um curso (mínimo “ 1”) e somente UM curso (máximo “ 1”). Um Curso pode ter NENHUM aluno inscrito ou MUITOS alunos inscritos. a) ALUNO (0, n) b) MÉDICO ENGENHEIRO ____ INSCRIÇÃO ATENDE ALOCAÇÃO CURSO (1, 1) ____ PACIENTE PROJETO 75

Modelo ER: EXERCÍCIOS c) COMPOSIÇÃO ____ compõe é composto PRODUTO d) PEÇA e) EMPREGADO f) MÉDICO ____ FORNECE ____ POSSUI ____ PRESCRIÇÃO ____ ___ FORNECEDOR DEPENDENTE MEDICAMENTO 76

Modelo ER: EXERCÍCIOS 4) Identifique as entidades, os relacionamentos e a cardinalidade entre os relacionamentos, como no exercício 1. trabalha DEPARTAMENTO EMPREGADO gerencia controla possui DEPENDENTE trabalha PROJETO 77

Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO É importante, durante a visualização dos dados, prestar atenção ao nível de abstração em que estamos atuando, pois, quando definimos uma entidade, estamos com a visão de uma classe genérica de dados, que pode estar incorporando, implicitamente, diversas outras classes de dados Ou seja, temos classes diferenciadas mas que possuem características que nos permitam colocá-las sob a visão de uma única entidade. Por exemplo, CLIENTE é na realidade uma generalização para diversas classes de dados de clientes, tais como: - Cliente – Pessoa Física - Cliente – Pessoa Jurídica 78

Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO Através deste conceito é possível atribuir propriedades particulares a um subconjunto das ocorrências (especializadas) de uma entidade genérica. FILIAL (1, 1) Cliente é dividida em dois subconjutnos, as entidades PESSOA FÍSICA e JURÍDICA, cada uma com propriedades específicas (0, n) CLIENTE PESSOA FÍSICA CPF Sexo nome código PESSOA JURÍDICA CNPJ Tipo de organização 79

Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO • Mas por que a preocupação deste gênero? • Quando ela se torna importante? Ela é importante porque podemos vir a ter na análise funcional do sistema, tratamentos procedurais e diferenciados para cada subconjunto, assim como poderemos tratar simultaneamente todos os conjuntos. Desta forma, devemos representá-los de forma que possamos vir a tratá-los como um todo ou como parte do todo 80

Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO HERANÇA DE PROPRIEDADE cada ocorrência da entidade especializada possui, além de suas propriedades, também as propriedades da ocorrência da entidade genérica. FILIAL (1, 1) (0, n) CLIENTE PESSOA FÍSICA CPF SEXO nome código PESSOA JURÍDICA CNPJ Tipo de organização Pessoa Física tem como atributos nome, código, CPF e sexo. É identificada pelo código e está obrigatoriamente relacionada a exatamente uma filial. 81

Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO CLIENTE t PESSOA FÍSICA CPF Sexo nome código PESSOA JURÍDICA CNPJ Tipo de organização Generalização TOTAL para cada ocorrência da entidade genérica existe sempre uma ocorrência em uma das entidades especializadas. Isto é válido para o exemplo pois, para TODA ocorrência de cliente deve haver uma ocorrência em uma das duas 82 especializações

Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO FUNCIONÁRIO Tipo de funcionário p MOTORISTA SECRETÁRIA Generalização PARCIAL nem toda ocorrência da entidade genérica corresponde a uma ocorrência em uma entidade especializada. Ex: Nem todo funcionário é Motorista ou secretária!! Neste caso, há necessidade de especificar o atributo que identifica o tipo de ocorrência da entidade genérica 83

Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO Uma entidade pode ser especializada em qualquer número de entidades, inclusive em uma única. Tipo de funcionário FUNCIONÁRIO VEÍCULO p MOTORISTA VEÍCULO TERRESTRE AUTOMÓVEL VEÍCULO ANFÍBIO VEÍCULO AQUÁTICO BARCO 84

Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO Só pode haver UMA ENTIDADE GENÉRICA em cada hierarquia de generalização/Especialização 85

Modelo ER: ENTIDADE ASSOCIATIVA MÉDICO n CONSULTA n PACIENTE Como ficaria o modelo se quiséssemos saber QUE MEDICAMENTOS EXISTEM e QUAIS FORAM PRESCRITOS em cada consulta? Teríamos, claro, que definir uma nova entidade, denominada MEDICAMENTO. Mas, como esta nova entidade deveria estar relacionada no modelo? Ou seja, ela deveria estar ligada a MÉDICO ou a PACIENTE ? 86

Modelo ER: ENTIDADE ASSOCIATIVA 1 a OPÇÃO MÉDICO n CONSULTA n PACIENTE n PRESCRIÇÃO n MEDICAMENTO Neste caso, teríamos a informação de que médico prescreve qual medicamento. Entretanto, não teríamos a informação de quais pacientes 87 receberam a prescrição.

Modelo ER: ENTIDADE ASSOCIATIVA 2 a OPÇÃO MÉDICO n CONSULTA n PACIENTE n PRESCRIÇÃO n MEDICAMENTO Neste outro caso, teríamos a informação de quais pacientes receberam quais medicamentos, porém, não saberíamos dizer qual foi o médico que 88 prescreveu tais medicamentos.

Modelo ER: ENTIDADE ASSOCIATIVA SOLUÇÃO MÉDICO n CONSULTA n PACIENTE n PRESCRIÇÃO n MEDICAMENTO A entidade MEDICAMENTO, portanto, deve estar relacionado ao relacionamento CONSULTA. A isso, damos o nome de ENTIDADE ASSOCIATIVA, ou seja, o relacionamento que passa a ser tratado como se fosse também uma entidade. 89

Modelo ER: ENTIDADE ASSOCIATIVA MÉDICO PACIENTE (1, 1) n n CONSULTA n PRESCRIÇÃO n MEDICAMENTO Caso não se desejasse usar o conceito de Entidade Associativa, seria necessário transformar o relacionamento CONSULTA em uma entidade. Neste caso, uma consulta deve estar relacionada com exatamente um médico e um paciente. 90

Parte 3 Modelo Relacional/Lógico 91

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). coluna Código Nome 0111 0112 0271 0108 0357 0097 João Antônio Carlos Eduardo Luís Vera Data Admissão Código Depto 12/11/2000 12/12/2001 05/06/2001 03/03/2000 20/10/2001 15/02/2002 01 01 10 10 10 21 linha 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 92 é 01.

Modelo Relacional/Lógico – TABELAS coluna (atributo) Código 0111 0112 0271 0108 0357 0097 linha(tupla) Nome Data Admissão Código Depto João Antônio Carlos Eduardo Luís Vera 12/11/2000 12/12/2001 05/06/2001 03/03/2000 20/10/2001 15/02/2002 01 01 10 10 10 21 Valor do campo 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 uma ordenação (ORDER BY). 93

Modelo Relacional/Lógico – CHAVES CHAVE é a forma de identificar linhas e estabelecer relações entre linhas de tabelas de um banco de dados relacional CHAVE PRIMÁRIA é uma coluna ou uma combinação de colunas cujos valores distinguem uma linha das demais dentro de uma tabela Empregado Cod. Emp E 5 E 3 E 2 E 1 Nome Cod. Depto Souza Santos Silva Soares D 1 D 2 D 1 Categ. Funcional C 5 C 2 ---94

Modelo Relacional/Lógico – CHAVES Chave Primária Simples Chave Primária Composta Empregado Cod. Emp Nome E 5 E 3 E 2 E 1 Cod. Depto Souza Santos Silva Soares Categ. Funcional C 5 C 2 ---- D 1 D 2 D 1 Empx. Proj Cod. Emp Cod. Proj Horastrab E 1 E 2 E 6 01 02 01 01 02 86 32 180 40 120 95

Modelo Relacional/Lógico – CHAVES CHAVE ESTRANGEIRA é uma coluna ou uma combinação de colunas cujos valores aparecem necessariamente na chave primária de uma tabela. É ela que permite a implementação de relacionamentos em um banco de dados relacional Chave estrangeira Empregado Cod. Emp Nome E 5 E 3 E 2 E 1 Souza Santos Silva Soares Cod. Depto D 1 D 2 D 1 Categ. Funcional C 5 C 2 ---- 96

Modelo Relacional/Lógico – CHAVES Tabela: Departamento Cod. Depto 01 10 21 Nome Depto Contabilidade Vendas Faturamento Verba 9. 500, 00 15. 000, 00 12. 800, 00 Qual o nome do departamentodo Funcionário João? Tabela: Empregado Codigo Nome 0111 0112 0271 0108 0357 0097 João Antônio Carlos Eduardo Luís Vera Data Admissão Cod. Depto 12/11/2000 12/12/2001 05/06/2001 03/03/2000 20/10/2001 15/02/2002 01 01 10 10 10 21 97

Modelo Relac. /Lógico – EXPRESSÃO RELACIONAMENTO Tabela: Departamento Cod. Depto Nome Depto 01 10 21 Verba Contabilidade 9. 500, 00 Vendas 15. 000, 00 Faturamento 12. 800, 00 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 Tabela: Empregado Código Nome 0111 0112 0271 0108 0357 0097 João Antônio Carlos Eduardo Luís Vera Data Admissão Código Depto 12/11/2000 12/12/2001 05/06/2001 03/03/2000 20/10/2001 15/02/2002 01 01 10 10 10 21 98

Modelo Relacional/Lógico – REGRAS DE CONVERSÃO Modelo Conceitual para o Modelo Relacional/Lógico: 1. Cada entidade do Modelo Conceitual transforma-se em uma tabela no Modelo Relacional/Lógico contendo como campos os respectivos atributos da entidade. 2. O atributo identificador da entidade transforma-se em chave primária na tabela. 3. Analisar os relacionamentos entre as entidades para gerar a chave estrangeira, aplicando a regra de acordo com o tipo de relacionamento: 99

Modelo Relacional/Lógico – REGRAS DE CONVERSÃO Analisando os relacionamentos: ü Relacionamento de 1: N – Não cria nova tabela, mas a chave primária da tabela de lado 1 transforma-se em chave estrangeira na tabela de lado N do relacionamento. Caso o relacionamento contenha atributos, esses devem acompanhar a chave estrangeira. DEPARTAMENTO 1 LOTAÇÃO N EMPREGADO 100

Modelo Relacional/Lógico – REGRAS DE CONVERSÃO Analisando os relacionamentos: ü Relacionamento de N: N – Cria-se nova tabela cuja chave primária será composta pela chave primária das duas tabelas relacionadas. Caso o relacionamento contenha atributos, esses devem ser adicionados na nova tabela, do contrário, será uma tabela contendo apenas a chave primária. Esses campos receberão a restrição de chave primária composta e ao mesmo tempo serão, individualmente, chave estrangeira. PROJETO N ALOCADO N EMPREGADO Horas. Trab 101

Modelo Relacional/Lógico – REGRAS DE CONVERSÃO Analisando os relacionamentos: ü Relacionamento de 1: 1 – Não cria nova tabela, mas a chave primária de um dos lados deve se transformar em chave estrangeira do outro lado do relacionamento. O lado a ser escolhido deverá ser aquele que terá menor possibilidade de conter valores nulos na coluna que será a chave estrangeira. Caso o relacionamento contenha atributos, esses devem acompanhar a chave estrangeira. PEDIDO 1 GERA 1 NOTA FISCAL Data. Emissão 102

Modelo Relacional/Lógico – REGRAS DE CONVERSÃO ATENÇÃO: ü Qualquer tabela que não tenha sido gerada de acordo com essa regra está errada, ou seja, todas as tabelas que surgirem no modelo relacional devem corresponder a uma entidade ou ser fruto do relacionamento de N: N no modelo conceitual. ü Uma tabela gerada a partir do relacionamento de N: N só contém campos além das chaves se esses forem atributos do relacionamento na Modelagem Conceitual. 103

Modelo Relac. /Lógico – RESTRIÇÕES DE CHAVES A existência de uma chave estrangeira impõe restrições que devem ser garantidas ao executar diversas operações de alteração no banco de dados ¨ Quando da inclusão de uma linha na tabela que contém a chave estrangeira (ela deve existir na chave primária); ¨ Quando da alteração do valor da chave estrangeira (ele deve existir na chave primária); ¨ Quando da exclusão de uma linha da tabela que contém a chave primária referenciada pela chave estrangeira (verificar se outra tabela a utiliza); ¨ Quando da alteração do valor da chave primária referenciada pela chave estrangeira (garantir que na coluna chave estrangeira não apareça o antigo valor da chave primária alterada); 104

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, . . ) que os campos da respectiva coluna podem assumir. Este conjunto de valores é chamado de DOMÍNIO DA COLUNA ou do CAMPO Também deve ser especificado se os campos da coluna podem estar vazios (“null” em inglês) ou não. As colunas quais não são admitidos valores vazios são chamadas de colunas Obrigatórias e as outras são chamadas de Opcionais. 105

Parte 4 Álgebra Relacional 106

Álgebra Relacional – OPERAÇÕES Conjunto de operadores de alto nível que manipulam os dados coletados em uma ou mais tabelas. Tipos de Operações: ü Tradicionais § UNIÃO (OPERADOR OU) § INTERSECÇÃO (OPERADOR E) § DIFERENÇA § PRODUTO CARTESIANO ü Especiais § SELEÇÃO § PROJEÇÃO § JUNÇÃO 107

Álgebra Relacional – OPERAÇÕES UNIÃO COMPATÍVEL Quando as relações possuem as mesmas estruturas São consideradas operações do tipo União Compatível as operações de UNIÃO, INTERSECÇÃO e DIFERENÇA. 108

Álgebra Relacional – SIMBOLOGIAS UNIÃO – A B INTERSEÇÃO – A B DIFERENÇA – A SELEÇÃO – (Condição seleção) PROJEÇÃO – (Lista de Campos) - B PROD. CARTESIANO – A B JUNÇÃO A x – <condição de junção> B 109

Álgebra Relacional – SIMBOLOGIAS n SELEÇÃO Ex: n PROJEÇÃO Ex: n < condição de seleção > (nome da relação) < lista de campos > (nome da relação) JUNÇÃO é uma operação de Produto Cartesiano seguida pela operação de seleção. Ex: R x < condição JOIN > S < condição de seleção > (R S) 110

Álgebra Relacional – UNIÃO A união de duas tabelas normalizadas “A” e “B”, é uma tabela normalizada “C” que contém todas as linhas de “A” e/ou “B”. 111

Álgebra Relacional – INTERSECÇÃO A intersecção das tabelas “A” e “B” é uma tabela normalizada “C” que só contém aquelas linhas que pertencem a “A” e “B” simultaneamente. 112

Álgebra Relacional – DIFERENÇA A diferença entre duas tabelas “A” e “B” é uma tabela normalizada “C” que contém as linhas de “A” que não pertencem a “B”. 113

Álgebra Relacional – PRODUTO CARTESIANO O produto cartesiano de duas tabelas “A” e “B” é uma tabela “C” cujas linhas são obtidas fazendo todas as concatenações possíveis entre as linhas de “A” e “B”. 114

Álgebra Relacional – PROJEÇÃO A projeção de uma tabela normalizada sobre uma ou várias de suas colunas é uma nova tabela que contém somente as colunas projetadas. 115

Parte 5 SQL Structured Query Language 116

SQL – Structured Query Language ( Linguagem Estrutura de Consulta ) Compõe-se de: • DDL – criação do esquema, ou seja, estruturas de armazenamento • DML – linguagem de consulta baseada em álgebra relacional • DCL – linguagem de controle de acessos e autorização Estrutura básica de uma expressão SQL: Onde Ai = atributo i, ti = tabela i e C = conjunto de condições 117

SQL – Structured Query Language §Forma o produto cartesiano das tabelas indicadas na cláusula FROM (quando houver mais de uma tabela na cláusula) §Executa uma seleção da álgebra relacional usando as condições da cláusula WHERE §Projeta o resultado para os atributos da cláusula SELECT Obs. : O caractere * permite selecionar todos os atributos de uma ou mais tabelas, quando colocado após a cláusula SELECT Ex. : SELECT * FROM Tabela 1 118

SQL – Structured Query Language SELEÇÃO A SELEÇÃO DE UMA TABELA “T” COM REFERÊNCIA A CERTA CONDIÇÃO, É UMA OUTRA TABELA “R” QUE CONTÉM TODAS AS LINHAS DE “T” PARA AS QUAIS A CONDIÇÃO É CERTA. T NRO_PED DATA REG_VDA TOTAL 10 23/01/86 SP 1250 11 13/12/87 RJ 722 12 27/01/86 SP 347 14 28/04/87 MG 2455 17 27/03/86 GO 145 R=SELEÇÃO T; (REG_VDA. NEQ. ‘SP’ AND TOTAL. GT. 500) R NRO_PED DATA REG_VDA TOTAL 11 13/12/87 RJ 722 14 28/04/87 MG 2455 119

SQL – Structured Query Language SELEÇÃO - Exemplos TAB. AGENCIA a) SELECT * FROM Agencia AG_NUM AG_NOME ENDERECO REGIAO 11 -002 Itaim R. Dr. Mário F. Sul 11 -003 Sto. Amaro Lgo. 13 de Maio Sul 12 -005 Boa Vista R. Boa Vista, 2 Centro 12 -007 Direita R. Direita, 344 Centro 15 -001 Diamantina R. Diamantina Norte b) SELECT AG_NOME, REGIAO FROM Agencia AG_NOME REGIAO Itaim Sul Sto. Amaro Sul Boa Vista Centro Direita Centro Diamantina Norte 120

SQL – Structured Query Language SELEÇÃO - Exemplos TAB. AGENCIA AG_NUM AG_NOME ENDERECO REGIAO 11 -002 Itaim R. Dr. Mário F. Sul 11 -003 Sto. Amaro Lgo. 13 de Maio Sul 12 -005 Boa Vista R. Boa Vista, 2 Centro 12 -007 Direita R. Direita, 344 Centro 15 -001 Diamantina R. Diamantina Norte c) SELECT AG_NOME, REGIAO FROM Agencia WHERE REGIAO = “Centro” AG_NOME REGIAO Boa Vista Centro Direita Centro d) SELECT AG_NOME, REGIAO FROM Agencia WHERE REGIAO <> “Centro” AG_NOME REGIAO Itaim Sul Sto. Amaro Sul Diamantina Norte e) SELECT REGIAO FROM Agencia REGIAO Sul Centro Norte f) SELECT DISTINCT REGIAO FROM Agencia REGIAO Sul Centro Norte 121

SQL – Structured Query Language JOIN - Fusão A fusão de duas tabelas A e B com referência a um atributo ou conjunto de atributos comuns é uma tabela que contém todas as linhas obtidas concatenando linhas de A e linhas de B para as quais o atributo escolhido tem o mesmo valor. B: FORNECEDORES A: PEDIDOS PEDIDO FORNEC DATA FORNEC NOME 720 214 110295 214 ALFA AS 721 273 220595 273 BETA AS 916 214 120396 283 GAMA AS 930 273 240496 C: Fusão de A e B segundo FORNEC PEDIDO FORNEC DATA NOME 720 214 110295 ALFA AS 721 273 220595 BETA AS 916 214 120396 ALFA AS 930 273 240496 BETA AS 122

SQL – Structured Query Language JOIN - Exemplos TAB. AGENCIA TAB. CLIENTE AG_NUM AG_NOME ENDERECO REGIAO NUM_CLI NOM_CLI AG_NUM END_CLI 11 -002 Itaim R. Dr. Mário F. Sul 1002 Jair Bastos 12 -005 R. . . 11 -003 Sto. Amaro Lgo. 13 de Maio Sul 1003 Nair Mendes 15 -001 Av. . . 1004 Nelson Alves 11 -002 R. . . 1005 Ana Pádua 11 -002 R. . . 1006 Hugo Torres 11 -003 . . . 1007 Tiago Melo 11 -002 . . . 12 -005 Boa Vista R. Boa Vista, 2 Centro 12 -007 Direita R. Diereita, 344 Centro 15 -001 Diamantina R. Diamantina Norte a) SELECT * FROM Agencia, Cliente Produto Cartesiano b) SELECT NUM_CLI, NOM_CLI, AG_NOME FROM Agencia, Cliente WHERE Agencia. AG_NUM = Cliente. AG_NUM NUM_CLI NOM_CLI AG_NOME 1002 Jair Bastos Boa Vista 1003 Nair Mendes Diamantina 1004 Nelson Alves Itaim 1005 Ana Pádua Itaim 1006 Hugo Torres Sto. Amaro 1007 Tiago Melo Itaim 123

SQL – Structured Query Language JOIN - Exemplos TAB. AGENCIA TAB. CLIENTE AG_NUM AG_NOME ENDERECO REGIAO NUM_CLI NOM_CLI AG_NUM END_CLI 11 -002 Itaim R. Dr. Mário F. Sul 1002 Jair Bastos 12 -005 R. . . 11 -003 Sto. Amaro Lgo. 13 de Maio Sul 1003 Nair Mendes 15 -001 Av. . . 12 -005 Boa Vista R. Boa Vista, 2 Centro 1004 Nelson Alves 11 -002 R. . . 1005 Ana Pádua 11 -002 R. . . 1006 Hugo Torres 11 -003 . . . 1007 Tiago Melo 11 -002 . . . 12 -007 Direita R. Diereita, 344 Centro 15 -001 Diamantina R. Diamantina Norte c) SELECT NUM_CLI, NOM_CLI, AG_NOME FROM Agencia, Cliente WHERE Agencia. AG_NUM=Cliente. AG_NUM AND REGIAO=“Sul” d) SELECT NOM_CLI, NUM_CLI, AG_NOME FROM Agencia, Cliente WHERE Agencia. AG_NUM=Cliente. AG_NUM AND NOM_CLI LIKE “N%” NUM_CLI NOM_CLI AG_NOME 1004 Nelson Alves Itaim 1005 Ana Pádua Itaim 1006 Hugo Torres Sto. Amaro 1007 Tiago Melo Itaim NOM_CLI NUM_CLI AG_NOME Nair Mendes 1003 Diamantina Nelson Alves 1004 Itaim 124

Parte 6 Normalização de Tabelas 125

Normalização de Tabelas Normalização é uma técnica aceita para aumentar a confiabilidade na extração e na atualização de dados num banco de dados. Partimos de dados nãonormalizados e otimizados até a 3ª Forma Normal. Note que cada grau de otimização depende do anterior, isto é, para ir à 2ª F. N. é necessário que a tabela esteja na 1ª F. N. , e para ir à 3ª F. N. , é necessário que a tabela esteja na 2ª F. N. Nem sempre é conveniente atingir a 3ª F. N. , por várias razões. Por exemplo, o aumento da quantidade de tabelas (que provoca, no mínimo, prejuízo de performance) e pouco interesse de manter-se a consistência ou integridade dos dados. 126

Normalização de Tabelas Consequências da Não-Normalização PEDIDO NUM DAT NUMCLI NOMCLI DESENDCLI NUMPRD NOMPRD QTD VALPRCUNT VALPRCTOT VALPEDTOT 238 28/04 864 CARLO S RUA A, N. 76 21 GIZ 100 8, 00 800, 00 2180, 00 238 28/04 864 CARLO S RUA A, N. 76 63 LÁPIS 240 2, 00 480, 00 2180, 00 238 28/04 864 CARLO S RUA A, N. 76 16 CLIPS 10 90, 00 900, 00 2180, 00 * * * * * * * * * CARLO S RUA CHUÍ N. 240 21 GIZ 100 9, 00 900, 00 302 18/05 864 PERGUNTAS: • Pode-se manter informação sobre um cliente que nunca fez pedido? • O que acontece com os dados do cliente se for excluído o único pedido que ele fez? • O que é necessário fazer para alterar informações sobre um cliente? 127

Normalização de Tabelas PEDIDO NUM DAT A NUM_ CLI NOME_ CLI DES_ END_ CLI NUM_ PRD NOME _PRD QTD VAL_ UNIT_ PRD VAL_ TOT_ ITEM VAL_ TOT_ PED 238 28/04 864 CARLOS RUA A, N. 76 21 GIZ 100 8, 00 800, 00 2180, 00 238 28/04 864 CARLOS RUA A, N. 76 63 LÁPIS 240 2, 00 480, 00 2180, 00 238 28/04 864 CARLOS RUA A, N. 76 16 CLIPS 10 90, 00 900, 00 2180, 00 * * * * * * * * * 302 18/05 864 CARLOS RUA CHUÍ N. 240 21 GIZ 100 9, 00 900, 00 NORMALIZAÇÃO 128

Normalização de Tabelas PEDIDO NUM (PK) DATA * * * 238 * * * 302 * * * 28/04 * * * 18/05 NUM_CLI (FK) VAL_TOT_PED * * * 864 * * * 2180, 00 * * * 900, 00 ITEMPRD CLIENTE NUM (PK) NOME DES_END_CLI * * * 864 * * * CARLOS * * * RUA CHUÍ, N. 240 PRD NUM_ PED (FK) NUM_ PRD (FK) QTD * * * 238 238 * 302 * * * 21 63 16 * 21 * * * 100 240 10 * 100 VAL_TOT_ITEM * * * 800, 00 480, 00 900, 00 * 900, 00 NUM (PK) NOME VAL_UNIT_PRD * 16 * * 21 * * 63 * CLIPS * * GIZ * * LÁPIS * 98, 00 * * 9, 00 * * 2, 60 129

Normalização de Tabelas n 1ª Forma Normal A 1ª F. N. exige que não se criem grupos de repetição em uma tabela, isto é, o cruzamento de linha-coluna não deve ter mais de uma informação. Também não se pode ter registros duplicados, ou seja, deve-se eliminar os atributos compostos e multivalorados. 130

Normalização de Tabelas 1ª FN – Exemplo 1 ELIMINAR DADO ESTRUTURADO (Atributo Composto) FUN NUM (PK) 0100 0200 * * NOME NUM_TEL_DDD VAL_SAL 011 2345678 0194 424166 * * 90000 8888 * * JOÃO CRISTINA * * 1ª FN FUN NUM(PK) 0100 0200 * * NOME JOÃO CRISTINA * * DDD NUM_TEL 011 0194 * * 2345678 424166 * * VAL_SAL 9000 8888 * * 131

Normalização de Tabelas 1ª FN – Exemplo 2 ELIMINAR DADO REPETITIVO (Atributo Multivalorado) FUN NUM(PK) 0100 0300 * * NOME_DEPEND JOÃO JOSÉ * * JOSÉ, MARIA, CAIO BEATRIZ * * VAL_SAL 90000 80000 * * 1ª FN FUN NUM(PK) 0100 0300 NOME JOÃO JOSÉ NOME_DEPEND(PK) JOSÉ MARIA CAIO BEATRIZ VAL_SAL 90000 132

Normalização de Tabelas n Dependência Funcional Para entender a 2ª e 3ª formas normais que serão apresentadas a seguir, é necessário compreender o conceito de dependência funcional. Em uma tabela relacional, diz-se que uma coluna C 2 depende funcionalmente de uma coluna C 1 (ou que a coluna C 1 determina a coluna C 2) quando, em todas as linhas da tabela, para cada valor de C 1 que aparece na tabela, aparece o mesmo valor de C 2. 133

Normalização de Tabelas n Dependência Funcional O conceito fica mais fácil de entender se considerarmos um exemplo: . . . . CÓDIGO . . . . SALÁRIO E 1 10 E 3 10 E 1 10 E 2 5 E 3 10 E 2 5 E 1 10 . . . . 134

Normalização de Tabelas n Dependência Funcional A tabela anterior contém, entre outras colunas irrelevantes ao exemplo, as colunas Código e Salário. Diz-se que a coluna Salário depende funcionalmente da coluna Código (ou que a coluna Código determina a coluna Salário) pelo fato de cada valor de Código estar associado sempre ao mesmo valor de Salário. Exemplificando o valor “E 1” da coluna Código identifica sempre o mesmo valor de Salário (“ 10”). Para denotar esta dependência funcional, usa-se uma expressão na forma Código Salário. A expressão denota que a coluna Salário depende funcionalmente da coluna Código. Diz -se que a coluna Código é o determinante da dependência funcional. De forma geral, o determinante de uma dependência funcional pode ser um conjunto de colunas e não somente uma coluna 135 como na definição acima.

Normalização de Tabelas n 2ª Forma Normal Uma tabela está na 2ª F. N. se estiver na 1ª F. N. e, adicionalmente, se cada campo não pertencente à chave for dependente da chave completa, e não apenas de uma parte dela. 136

Normalização de Tabelas 2ª FN – Exemplo 1 FUN NUM 0100 * NOME_DEP JOÃO * VAL_SAL JOSÉ MARIA CAIO 90000 * NUM_DEPTO PESQUISA NOME_DIR CAROLINE 2ª FN FUN NUM(PK) NOME 0100 * VAL_SAL JOÃO * 90000 * DPD NUM_FUN(PK) 0100 * NOME_DEPTO NOME_DEP(PK) JOSÉ MARIA CAIO * PESQUISA * NOME_DIR CAROLINE * + 137

Normalização de Tabelas 2ª FN – Exemplo 2 RSV NOME CLI DT_ENTR NUM_APT TIP_APT VL_DIAR QT_DIAR VL_TOT JOSÉ ELZA * * * 01/07/89 08/07/89 * * * 104 105 82 * * * STD LUXO SUITE * * * 100, 00 140, 00 170, 00 * * * 3 3 2 * * * 300, 00 420, 00 340, 00 * * * • A tabela está na 1ª FN, pois não contém grupos repetidos. • A tabela não está na 2ª FN, pois TIP_APT e VL_DIAR são determinados apenas conhecendo-se NUM_APT. 2ª FN 138

Normalização de Tabelas 2ª FN – Exemplo 2 RSV NOMECLI JOSÉ * * * DT_ENTR 01/07/89 * * * NUM_APT 104 105 * * * QT_DIAR 3 3 * * * VL_TOT 300, 00 420, 00 * * * + APT NUM(PK) TIP_APT 104 105 82 * * * STD LUXO SUÍTE VL_DIAR 100, 00 140, 00 170, 00 * * * 139

Normalização de Tabelas n 3ª Forma Normal Uma tabela está na 3ª F. N. se estiver na 2ª F. N. e, se todos os campos não pertencentes à chave primária (completa) forem independentes entre si. 140

Normalização de Tabelas 3ª FN – Exemplo 1 FUN NUM(PK) NOME_DEPTO 0100 0200 0300 0400 PESQUISA VENDAS FINANÇAS NUM NOME_DEPTO 0100 0200 0300 0400 CAROLINE JORGE FLAVIA NOME_DEPTO NOME_DIR / NUM 3ª FN FUN NUM(PK) NOME_DIR DEP NOME_DEPTO(FK) PESQUISA VENDAS FINANÇAS + NOME_DEPTO (PK) PESQUISA VENDAS FINANÇAS NOME_DIR CAROLINE JORGE FLAVIA 141

Normalização de Tabelas 3ª FN – Exemplo 2 FUN NUM (PK) 0100 0200 0300 0400 0500 * NUM_TEL VAL_SAL_HOR 1234567 2224567 2312200 2223300 * NUM_PRJ 3, 57 4, 59 2, 05 4, 99 6, 00 * 101 202 303 202 * NOME_PRJ DT_FIM BIBLIOTECA RH PD RH * 23/06/90 20/08/91 20/12/92 20/08/91 * Está na 1ª e 2ª FN, porém, não está na 3ª FN, pois NOM-PRJ e DAT-FIM são determinados apenas conhecendo-se o NUM-PRJ FUN NUM (PK) NUM_TEL 0100 0200 030 0040 0050 1234567 2224567 2312200 2311000 2223300 VAL_SAL_HOR 3, 57 4, 59 2, 05 4, 99 6, 00 NUM_PRJ (FK) 101 202 303 202 PRJ + NUM (PK) NOME DT_FIM 101 202 303 BIBLIOTECA RH PD 23/06/90 20/08/91 20/12/92 142

Normalização de Tabelas Regras 1ª Forma Normal A 1ª F. N. exige que não se criem grupos de repetição em uma tabela, isto é, o cruzamento de linha-coluna não deve ter mais de uma informação. Também não se pode ter registros duplicados, ou seja, deve-se eliminar os atributos compostos e multivalorados. 2ª Forma Normal Uma tabela está na 2ª F. N. se estiver na 1ª F. N. e, adicionalmente, se cada campo não pertencente à chave for dependente da chave completa, e não apenas de uma parte dela. 3ª Forma Normal Uma tabela está na 3ª F. N. se estiver na 2ª F. N. e, se todos os campos não pertencentes à chave principal (completa) forem independentes entre si. 143

Normalização de Tabelas 144

Normalização de Tabelas Implicações 145