Banco de Dados Revisão de Banco de Dados Banco de Dados Adilson Silva [email protected].
Banco de Dados Parte do conteúdo exposto nestas transparências foi retirado dos livros: Projeto de...
Transcript of Banco de Dados Parte do conteúdo exposto nestas transparências foi retirado dos livros: Projeto de...
1
Banco de DadosBanco 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
2
Parte 1Parte 1
Conceitos BásicosConceitos Básicos
3
Dado x Informação
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;
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çãoque se deseja saber.
4
Dado x Informação
Ex: Imagine que o sistema armazene os seguintes itens a respeito dos funcionários de uma empresa.
NúmeroNomeData Contratação Endereço BairroCidade
O que é DADO e o que é
INFORMAÇÃO?
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)
5
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.
6
SISTEMA PRODUÇÃO
SISTEMA VENDAS
SISTEMA DE COMPRAS
O que você vai PRODUZIR?
PRODUTO
É preciso saber quais os componentes dos produtos e como são
produzidos.
O que você vai VENDER?
PRODUTO
É preciso saber o preço do produto, seu prazo de
validade, estoque...
O que você vai COMPRAR?
MATÉRIA-PRIMA do PRODUTO
É preciso saber quais componentes serão
adquiridos para fabricar o produto
Arquivos Produção Arquivos Vendas Arquivos Compras
Compartilhamento de Dados
7
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.
Dados de diferentes aplicações não estão integrados;
Dados estão projetados para atender uma aplicação
específica.
SISTEMA PRODUÇÃO
SISTEMA VENDAS
SISTEMA DE COMPRAS
Arquivos Produção Arquivos Vendas Arquivos Compras
Produto Produto Produto
8
Problemas da Falta de Integração de Dados
Redundância ControladaControlada e Não ControladaNão Controlada de Dados
Redundância de DadosRedundância de Dados - o mesmo objeto da realidade
é armazenado mais de uma vez no banco de dados.
Ex: Produtos
Redundância ControladaControlada - 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.
9
Problemas da Falta de Integração de Dados
Redundância Não ControladaNã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 :Redigitação de Dados -Redigitação de Dados - o mesmo dado é digitado várias vezes no sistema. Este trabalho repetitivo pode levar a erros;
Inconsistência dos Dados - 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.
Dificuldade de extração de informações - Dificuldade de extração de informações - os dados projetados para atender uma aplicação específica po- dem gerar dificuldade para o cruzamento dos dados
10
Solução
COMPARTILHAMENTO DE DADOSCOMPARTILHAMENTO DE DADOS
A solução para evitar a redundância NÃO CONTROLADA de informações.
SISTEMA VENDAS
BANCO DE DADOS
Produtos
SISTEMA PRODUÇÃO
SISTEMA DE COMPRAS
Assim, cada dado é armazenado uma ÚNICA VEZ, sendo acessada pelos vários sistemas que dele necessitam.
11
Compartilhamento de Dados
SISTEMA PRODUÇÃO
SISTEMA VENDAS
SISTEMA DE COMPRAS
Arquivos Produção Arquivos Vendas Arquivos Compras
Produto Produto Produto
SISTEMA VENDAS
BANCO DE DADOS
Produto
SISTEMA PRODUÇÃO
SISTEMA DE COMPRAS
12
BANCO DE DADOS
É o nome dado ao conjunto de arquivos integrados que atendem a um conjunto de sistemas
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.” (Kort, Henry F.)
Banco de Dados
13
Banco de Dados
O que muda com o surgimento dos Bancos de Dados, ou seja, com o Compartilhamento dos Dados?
Acesso por múltiplos programas - Acesso por múltiplos programas - pode haver mais de uma equipe de desenvolvimento envolvida no desenvolvimento de uma aplicação
Os programas devem garantir a Restrição de Integridade,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.
O BD pode ser acessado concorrentemente por múlti-O BD pode ser acessado concorrentemente por múlti- plos usuários - plos usuários - os programas devem implementar o controle de acesso concorrente
14
Banco de Dados
Restrições de Acesso - 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ê
Dados são de importância vital e não podem ser perdi-Dados são de importância vital e não podem ser perdi- dos - dos - mecanismos simples como cópias de “backup” não são suficientes. Caso haja uma falha, o banco de dados deve ser recuperado rapidamente. Os programas devem implementar mecanismos de tolerância a falhas
Estruturas de dados mais complexas -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.
15
Sistema de Gerência de Banco de Dados
SGBD SGBD Software que incorpora as funções de definição, recuperação e alteração de dados em um banco de dados
Aplicação
SGBD
Banco de Dados
Software que serve para armazenar e acessar dados em um banco de dados
16
Sistema de Gerência de Banco de Dados: Vantagens
Independência de dados - 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
Abstração de dados - 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..)
Controle de segurança -Controle de segurança - que usuário pode fazer o que sobre qual dado
Tolerância a falhas -Tolerância a falhas - recuperação em caso de falha imperceptível ao usuário
Controle a acesso concorrente -Controle a acesso concorrente - muitos usuários acessando o banco ao mesmo tempo
17
Quatro Gerações de Gerenciamento de Dados
1960 1970 1980 1990 2000
Sistemas de Gerenciamento de Arquivos(ISAM, VSAM)
SGBD’s Hierárquicos (IMS) e em Rede (CODASYL)
SGBD’s RELACIONAISRELACIONAIS (DB2,SQL Server, Oracle)
SGBD’s OO (Versant, Objectivity)
18
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.
19
Ainda não entendi o que afinal de contas eu vou aprender nesta disciplina!!!
20
Parte 2Parte 2
Abordagem Abordagem
Entidade-RelacionamentoEntidade-Relacionamento
21
Objetivos
Compreender os conceitos de ENTIDADE e algumas de suas características: RELACIONAMENTO, ATRIBUTO, CARDINALIDADE
22
Abordagem Entidade-Relacionamento
A primeira etapa do projeto de um banco de dados é a construção de um modelo conceitual, a chamada Modelagem ConceitualModelagem Conceitual.
A Modelagem ConceitualModelagem Conceitual tem por objetivo obter uma descrição abstrata, independente de implementação em computador, dos dados que serão armazenados no banco de dados.
MODELOCONCEITUAL
MODELOCONCEITUAL
MODELO LÓGICO
MODELO LÓGICO
MODELO FÍSICO
MODELO FÍSICO
23
Abordagem Entidade-Relacionamento
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
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.
24
Abordagem Entidade-Relacionamento
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 ainda não existe um Banco de Dados totalmente Orientado a ObjetoOrientado a Objeto..
Para solucionar tal problema, a UML utiliza um procedimento denominado “Mapeamento Objeto-Mapeamento Objeto-RelacionalRelacional”, de forma a permitir que as estruturas definidas no modelo Orientado a Objeto possam ser implementadas em um Banco de Dados Relacional.
25
Modelo Entidade-Relacionamento
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.
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
Estes objetos ele classificou em dois grupos:
ENTIDADE e RELACIONAMENTO
26
Faz Contém
O fato acima pode acontecer em qualquer realidade. Ele deve, portanto, ser retratado através de elementos básicos que compõem o Modelo ER.
PEDIDO
CLIENTE PRODUTO
Abordagem Entidade-Relacionamento
27
Modelo Entidade-Relacionamento (M.E.R.)
Os componentes básicos do Modelo ER são:
ENTIDADESENTIDADES
RELACIONAMENTOSRELACIONAMENTOS
ATRIBUTOSATRIBUTOS
28
Modelo ER: ENTIDADE
ENTIDADE ENTIDADE
“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.
São as “coisas” que existem no negócio sobre as quais temos interesse em manter armazenadas no banco de dados.
É uma “coisa” ou um “objeto” no mundo real que pode ser identificada de forma única em relação aos outros objetos.
29
Modelo ER: ENTIDADE
ENTIDADE ENTIDADE
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.
É representada através de um retângulo com o nome da entidade em seu interior.
CLIENTECLIENTE PRODUTOPRODUTO FUNCIONÁRIOFUNCIONÁRIO
NOTA FISCAL
NOTA FISCAL
ORDEM DE PRODUÇÃO
ORDEM DE PRODUÇÃO
30
Modelo ER: ENTIDADE
Exemplo:
CLIENTE
O retângulo CLIENTE representa o conjunto de todas as pessoas sobre as quais se deseja manter informações no BD..
Este objeto particular (um dos clientes) é chamado de OCORRÊNCIA de uma entidade, neste caso CLIENTE.
31
Modelo ER: ENTIDADE
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ÁRIOFUNCIONÁRIO
Matrícula Nome Data Admissão
4456 João Carlos da Silva 29/04/91
6689 Sílvia de Oliveira 26/02/92
1203 Carla Martinez 14/04/92
32
Modelo ER: ENTIDADE
Exemplo:
Quais são as “coisas” que vocês conseguem identificar nos LABORATÓRIOS de INFORMÁTICA da UNINOVE ?
Máquinas Bancadas Pessoas Quadro-negro Canetas Ar-condicionado
33
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
34
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 que
quem utiliza, ou seja, as PESSOAS são
de interesse do sistema
35
Modelo ER: PROPRIEDADES
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.
Estas propriedades são :
Participar de um Relacionamento
Ter um ATRIBUTO
36
Modelo ER : ATRIBUTO
ATRIBUTOATRIBUTO
Dado que é associado a cada ocorrência de uma
entidade ou de um relacionamento (características específicas)
37
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:
PROJETO
tipo
código
nome
Modelo ER: ATRIBUTO
ENTIDADE
ATRIBUTOS
38
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ículaNome
Data AdmissãoData Nascimento
Valor Salário
Modelo ER: ATRIBUTO
39
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 DataAdmissão
4456 João Carlos da Silva 29/04/91
6689 Sílvia de Oliveira 26/02/92
1203 Carla Martinez 14/04/92
7702 Pedro Guilherme Souza 01/01/92
Modelo ER: ATRIBUTO
40
CLIENTE
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
Modelo ER: ATRIBUTO
Telefone
41
Cada entidade deve possuir um identificador!!!
IDENTIFICADORIDENTIFICADOR é 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
PESSOAcódigonomeendereço
DISCIPLINACód. DepartamentoCód. DisciplinaNome da disciplina
Identificador simples
Identificador composto
Modelo ER: ATRIBUTO IDENTIFICADOR
42
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
PESSOAcódigonomeendereço
Modelo ER: ATRIBUTO IDENTIFICADOR
Não é necessário utilizar Código e nome para identificar a entidade. Código é suficiente paradistinguir as ocorrências de PESSOA
43
Modelo ER: RELACIONAMENTO
RELACIONAMENTO RELACIONAMENTO
Conjunto de associações entre entidades através de algo comum.
DEPARTAMENTODEPARTAMENTO PESSOAPESSOALOTAÇÃO
• 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 LOTAÇÃO);
44
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
45
Modelo ER: RELACIONAMENTO
RELACIONAMENTO RELACIONAMENTO
Assim como ocorre com as entidades, temos as ocorrências de relacionamentos.
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.
46
p1,d1
p1 p2p4 p5
p3 p7 p8
d1 d2d3
p2,d1
p4,d2 p5,d3
Diagrama de ocorrências
PESSOAPESSOA
DEPARTAMENTODEPARTAMENTO
LOTAÇÃO
Modelo ER: RELACIONAMENTO 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
Entidade
Relacionamento
Entidade
47
No exemplo, ATUAÇÃO possui um atributo (Função), ou seja, o papel que um engenheiro deve
desempenhar dentro de um projeto.
ENGENHEIRO
código
nome
PROJETO
código
título
ATUAÇÃO(0,n) (0,n)
Função
Assim como Entidade, Relacionamentos também podem possuir atributos
Função ENGENHEIRO Função PROJETO
Modelo ER: RELACIONAMENTO
48
Modelo ER: CARDINALIDADE
CARDINALIDADECARDINALIDADE (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
49
Modelo ER: LEITURA da CARDINALIDADE
HOMEMHOMEM MULHERMULHERCASADO ??
PERGUNTAPERGUNTA:Um homem pode estar casado com quantas mulheres?
HOMEMHOMEM MULHERMULHERCASADO (0,1)
RESPOSTARESPOSTA::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”;
50
Modelo ER: LEITURA da CARDINALIDADE
HOMEMHOMEM MULHERMULHERCASADO??
PERGUNTAPERGUNTA:Uma mulher pode estar casada com quantos homens?
HOMEMHOMEM MULHERMULHERCASADO(0,1)
RESPOSTARESPOSTA::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”;
51
Modelo ER: LEITURA da CARDINALIDADE
HOMEMHOMEM MULHERMULHERCASADO(0,1)
HOMEMHOMEM MULHERMULHERCASADO(0,1)
HOMEMHOMEM MULHERMULHERCASADO(0,1) (0,1)
52
Modelo ER: Cardinalidade MÍNIMA
Cardinalidade MínimaCardinalidade Mínima é o número mínimo de ocorrências de uma entidade associadas a uma ocorrência de outra entidade num relacionamento
Consideram-se apenas duas cardinalidades:
Obrigatória (“1”) indica que o relacionamento deve obrigatoriamente associar uma ocorrência de uma entidade a uma ocorrência de outra entidade
Opcional (“0”) indica que o relacionamento existe independente de haver ou não uma ocorrência de uma entidade ligada à outra
53
Modelo ER: Cardinalidade MÍNIMA
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”)
EMPREGADOEMPREGADO
DEPARTAMENTODEPARTAMENTO
ALOCAÇÃO
(0,n)
(1,1)
54
Modelo ER: Cardinalidade MÁXIMA
Cardinalidade MáximaCardinalidade Máxima é o número máximo de ocorrências de uma entidade associadas a uma ocorrência de outra entidade num relacionamento
Consideram-se apenas duas cardinalidades:
“n” indica que uma ocorrência de uma determinada entidade pode estar associada a muitas ocorrências da entidade relacionada a ela
“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
55
Modelo ER: Cardinalidade MÁXIMA
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
EMPREGADOEMPREGADO DEPARTAMENTODEPARTAMENTOLOTAÇÃO(0,n) (1,1)
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
56
Modelo ER: TIPO DE RELACIONAMENTO
TIPO DE RELACIONAMENTOTIPO DE RELACIONAMENTO
1:1 1:N N:N
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:
TIPO DE RELACIONAMENTOTIPO DE RELACIONAMENTOTIPO DE RELACIONAMENTOTIPO DE RELACIONAMENTO
57
Modelo ER: TIPO DE RELACIONAMENTO
HOMEMHOMEM MULHERMULHERCASADO(0,1)
HOMEMA •B •C •D •
MULHER
• X
• Y
• Z
• W
(0,1)
Relacionamento de 1:1Relacionamento de 1:1 Cada elemento de uma entidade relaciona-se com um e somente um elemento de outra entidade
58
Modelo ER: TIPO DE RELACIONAMENTO
Cada divisão é gerenciada por UM e apenas UM gerente
Cada gerente administra UMA e apenas UMA divisão
Exemplo Relacionamento de 1:1
DIVISÃODIVISÃO GERÊNCIAGERÊNCIAGERENCIADA
(0,1) (0,1)
59
Modelo ER: TIPO DE RELACIONAMENTO
MÃE
A •B •
C •
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 sua interpretação
Relacionamento de 1:NRelacionamento de 1:N Cada elemento da entidade A relaciona-se com muitos elementos da entidade B, mas cada elemento da entidade B só pode estar relacionado a um elemento da entidade A
60
Modelo ER: TIPO DE RELACIONAMENTO
MÃEMÃE FILHOFILHOPOSSUI (1,n)
MÃEMÃE FILHOFILHOPOSSUI(1,1)
MÃEMÃE FILHOFILHOPOSSUI(1,1) (1,n)
A cardinalidade determinante é sempre a máxima obtida da interpretação dos fatos
Exemplo Relacionamento de 1:N
61
Modelo ER: TIPO DE RELACIONAMENTO
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ÃEMÃE FILHOFILHOPOSSUI(1,1) (1,n)
EMPREGADOEMPREGADO DEPENDENTEDEPENDENTE(1,1) (0,n)
POSSUI
62
Modelo ER: TIPO DE RELACIONAMENTO
ESTUDANTE
E1 •E2 •E3 •E4 •E5 •
DISCIPLINA
• D1
• D2
• D3
• D4
ESTUDANTE
E1 •E2 •E3 •E4 •E5 •
DISCIPLINA
• D1
• D2
• D3
• D4
Relacionamento de N:NRelacionamento 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
63
Modelo ER: TIPO DE RELACIONAMENTO
Exemplo Relacionamento de N:N
DISCIPLINADISCIPLINACURSAALUNOALUNO(0,n) (0,n)
64
Modelo ER: TIPO DE RELACIONAMENTO
ALUNO
E1 •E2 •E3 •E4 •E5 •
DISCIPLINA
• D1
• D2
• D3
• D4
CURSACURSA
1•2•3•4•5•6•7•8•
65
Modelo ER: TIPO DE RELACIONAMENTO
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
FORNECEDORFORNECEDOR PRODUTOPRODUTOFORNECE(0,n) (0,n)
Exemplo Relacionamento de N:N
Vl_Unit
66
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
Número seqüência
EMPREGADO
códigonome
DEPENDENTE
nome
(1,1) (0,n)
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
Modelo ER: IDENTIFICANDO ENTIDADES
67
Modelo ER: GRAU DE RELACIONAMENTO
GRAU DE RELACIONAMENTOGRAU DE RELACIONAMENTO
É o número de entidades ligadas num mesmo relacionamento. São eles:
Grau 1 ou Auto-relacionamento Grau 2 ou Binário Grau 3 ou Ternário N-ário (acima de 3 entidades)
68
Modelo ER: GRAU DE RELACIONAMENTO
Grau 1 ou Auto-relacionamentoGrau 1 ou Auto-relacionamento Quando existe 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
PESSOAPESSOA
CASAMENTOmarido esposa
p1 p2p4 p5
p3 p7 p8
p2,p3 p4,p5
Uma ocorrência de pessoa exerce o papel de marido e a outra ocorrência exerce o papel de esposa
maridomarido
esposa esposa
69
Modelo ER: GRAU DE RELACIONAMENTO
Grau 2 ou BinárioGrau 2 ou Binário é quando existem duas entidades envolvidas num mesmo relacionamento.
1:1 Um para Um
HOMEMHOMEM MULHERMULHERCASADO1 1
1:N Um para Muitos
ALUNOALUNO CURSOCURSOINSCRIÇÃOn 1
N:N Muitos para muitos
MÉDICOMÉDICO PACIENTEPACIENTEATENDEn n
70
CIDADECIDADE DISTRIBUIDORDISTRIBUIDOR
DISTRIBUIÇÃO
PRODUTOPRODUTO
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 - um distribuidor
Modelo ER: GRAU DE RELACIONAMENTO
n 1
n
Grau 3 ou TernárioGrau 3 ou Ternário é quando existem três entidades envolvidas num mesmo relacionamento.
71
CIDADECIDADE DISTRIBUIDORDISTRIBUIDOR
DISTRIBUIÇÃO n 1
PRODUTOPRODUTO
n
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.
Modelo ER: GRAU DE RELACIONAMENTO
Neste caso analisaremos PARES de entidades
A cardinalidade “1”refere-se a um par cidade produto
72
CIDADECIDADEDISTRIBUIDORDISTRIBUIDOR
DISTRIBUIÇÃO n 1
PRODUTOPRODUTO
n
Modelo ER: GRAU DE RELACIONAMENTO
(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
73
DEPARTAMENTODEPARTAMENTO
DISCIPLINADISCIPLINARESPONSÁVEL(1,1) (0,n)
ALUNOALUNO
CURSOCURSO
DISC-CURSO
INSCRIÇÃO(0,n) (1,1)
(0,n)
(0,n)
PRÉ_REQUIS(0,n) (0,n)
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:
74
Modelo ER: EXERCÍCIOS 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)
b)
c) d)
DEPARTAMENTODEPARTAMENTO DISCIPLINADISCIPLINARESPONSÁVEL
(1,1) (0,n)
DISCIPLINADISCIPLINA
PRÉ_REQUIS
(0,n) (0,n)
(0,n) (1,1)
DISCIPLINADISCIPLINA
CURSOCURSO
DISC-CURSO
(0,n)
(0,n)
INSCRIÇÃOALUNOALUNO CURSOCURSO
75
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)
b) ________
ALUNOALUNO CURSOCURSOINSCRIÇÃO(0,n) (1,1)
____MÉDICOMÉDICO PACIENTEPACIENTEATENDE
____
PROJETOPROJETOENGENHEIROENGENHEIRO ALOCAÇÃO
76
Modelo ER: EXERCÍCIOSc)
d)
e)
f)
PRODUTOPRODUTO
COMPOSIÇÃO
____compõe
____é composto
PEÇAPEÇAFORNECEDORFORNECEDORFORNECE
____ ____
POSSUI____ ___
MEDICAMENTOMEDICAMENTOPRESCRIÇÃO____ ___
MÉDICOMÉDICO
EMPREGADOEMPREGADO DEPENDENTEDEPENDENTE
77
Modelo ER: EXERCÍCIOS
4) Identifique as entidades, os relacionamentos e a cardinalidade entre os relacionamentos, como no exercício 1.
EMPREGADO DEPARTAMENTO
DEPENDENTEPROJETO
controla
trabalha
gerencia
trabalha
possui
78
É 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
Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO
79
Através deste conceito é possível atribuir propriedades particulares a um subconjunto das ocorrências (especializadas) de uma entidade genérica.
FILIAL CLIENTE
PESSOA FÍSICA
PESSOAJURÍDICA
CPF Sexo CNPJ Tipo de organização
nome
código
(0,n)(1,1)
Cliente é dividida em dois subconjutnos, as entidadesPESSOA FÍSICA eJURÍDICA, cada umacom propriedadesespecíficas
Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO
80
• 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
Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO
81
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 CLIENTE
PESSOA FÍSICA
PESSOAJURÍDICA
CPF SEXO CNPJ Tipo de organização
nome
código
(0,n)(1,1)
Pessoa Física tem como atributos nome, código, CPF e sexo. É identificada pelo código e está obrigatoriamente relacionada a exatamente uma filial.
Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO
82
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 especializações
CLIENTE
PESSOA FÍSICA
PESSOAJURÍDICA
CPF Sexo CNPJ Tipo de organização
nome
códigot
Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO
83
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
FUNCIONÁRIO
MOTORISTA SECRETÁRIA
Tipo de funcionário
p
Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO
84
Uma entidade pode ser especializada em qualquer número de entidades, inclusive em uma única.
FUNCIONÁRIO
MOTORISTA
Tipo de funcionário p
VEÍCULO
VEÍCULO TERRESTRE
VEÍCULOAQUÁTICO
AUTOMÓVELVEÍCULOANFÍBIO
BARCO
Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO
85
Só pode haver UMA ENTIDADE GENÉRICA em cada hierarquia de generalização/Especialização
Modelo ER: GENERALIZAÇÃO/ESPECIALIZAÇÃO
86
MÉDICO PACIENTECONSULTAn n
Como ficaria o modelo se quiséssemos saber QUE MEDICAMENTOS EXISTEM e QUAIS FORAM
PRESCRITOS em cada consulta?
Modelo ER: ENTIDADE ASSOCIATIVA
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 ?
87
MÉDICO PACIENTECONSULTAn n
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 receberam a prescrição.
MEDICAMENTO
Modelo ER: ENTIDADE ASSOCIATIVA
PRESCRIÇÃO
n
n
1a OPÇÃO
88
MÉDICO PACIENTECONSULTAn n
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 prescreveu tais medicamentos.
MEDICAMENTO
Modelo ER: ENTIDADE ASSOCIATIVA
PRESCRIÇÃO
n
n
2a OPÇÃO
89
MÉDICO PACIENTECONSULTAn n
MEDICAMENTO
Modelo ER: ENTIDADE ASSOCIATIVA
PRESCRIÇÃO
n
n
SOLUÇÃO
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.
90
MÉDICO PACIENTE
CONSULTA
n n
MEDICAMENTO
PRESCRIÇÃO
n
n
(1,1)(1,1)
Modelo ER: ENTIDADE ASSOCIATIVA
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.
91
Parte 3Parte 3
Modelo Relacional/LógicoModelo Relacional/Lógico
92
Modelo Relacional/Lógico – TABELASModelo 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).
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 é 01.
NomeCódigo
011101120271010803570097
JoãoAntônioCarlosEduardoLuísVera
Data Admissão
12/11/200012/12/200105/06/200103/03/200020/10/200115/02/2002
Código Depto
010110101021
linha
coluna
93
Modelo Relacional/Lógico – TABELASModelo Relacional/Lógico – TABELAS
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).
NomeCódigo
011101120271010803570097
JoãoAntônioCarlosEduardoLuísVera
Data Admissão
12/11/200012/12/200105/06/200103/03/200020/10/200115/02/2002
Código Depto
010110101021
Valor do campo
coluna (atributo)
linha(tupla)
94
Modelo Relacional/Lógico – CHAVESModelo 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
NomeCodEmpE5E3E2E1
SouzaSantosSilvaSoares
CategFuncional
C5C5C2----
D1D2D1D1
CodDepto
EmpregadoEmpregado
95
HorastrabCodEmpE1E1E2E6E6
86 32
180 40
120
0102010102
CodProj
Chave Primária
Simples
EmpxProjEmpxProj
NomeCodEmpE5E3E2E1
SouzaSantosSilvaSoares
CategFuncional
C5C5C2----
D1D2D1D1
CodDepto
EmpregadoEmpregado
Chave Primária
Composta
Modelo Relacional/Lógico – CHAVESModelo Relacional/Lógico – CHAVES
96
Modelo Relacional/Lógico – CHAVESModelo 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
NomeCodEmpE5E3E2E1
SouzaSantosSilvaSoares
CategFuncional
C5C5C2----
D1D2D1D1
CodDepto
EmpregadoEmpregado Chave estrangeira
97
Nome DeptoCodDepto
011021
ContabilidadeVendasFaturamento
Verba
9.500,0015.000,0012.800,00
NomeCodigo
011101120271010803570097
JoãoAntônioCarlosEduardoLuísVera
Data Admissão
12/11/200012/12/200105/06/200103/03/200020/10/200115/02/2002
CodDepto
010110101021
Qual o nome do departamento do Funcionário João?
Modelo Relacional/Lógico – CHAVESModelo Relacional/Lógico – CHAVES
Tabela: DepartamentoTabela: Departamento
Tabela: EmpregadoTabela: Empregado
98
CodDepto
011021
ContabilidadeVendasFaturamento
Verba
9.500,0015.000,0012.800,00
Tabela: DepartamentoTabela: Departamento
Tabela: EmpregadoTabela: Empregado
Nome
011101120271010803570097
JoãoAntônioCarlosEduardoLuísVera
Data Admissão
12/11/200012/12/200105/06/200103/03/200020/10/200115/02/2002
Código Depto
010110101021
Código
Modelo Relac./Lógico – EXPRESSÃO RELACIONAMENTOModelo Relac./Lógico – EXPRESSÃO RELACIONAMENTO
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
Nome Depto
99
Modelo Relacional/Lógico – REGRAS DE CONVERSÃOModelo 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:
100
Modelo Relacional/Lógico – REGRAS DE CONVERSÃOModelo 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.
DEPARTAMENTODEPARTAMENTO EMPREGADOEMPREGADOLOTAÇÃO1 N
101
Modelo Relacional/Lógico – REGRAS DE CONVERSÃOModelo 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.
PROJETOPROJETO EMPREGADOEMPREGADOALOCADON N
HorasTrab
102
Modelo Relacional/Lógico – REGRAS DE CONVERSÃOModelo 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.
PEDIDOPEDIDO NOTA FISCALNOTA FISCALGERA1 1
DataEmissão
103
Modelo Relacional/Lógico – REGRAS DE CONVERSÃOModelo 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.
104
Modelo Relac./Lógico – RESTRIÇÕES DE CHAVESModelo 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);
105
Modelo Relac./Lógico – DOMÍNIOS E VALORES VAZIOSModelo 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, numérico,..) que os campos da respectiva coluna podem assumir.
Também deve ser especificado se os campos da coluna podem estar vazios (“null” em inglês) ou não.
Este conjunto de valores é chamado de DOMÍNIO DA COLUNA ou do CAMPO
As colunas nas quais não são admitidos valores vazios são chamadas de colunas Obrigatórias e as
outras são chamadas de Opcionais.
106
Parte 4Parte 4
Álgebra RelacionalÁlgebra Relacional
107
Álgebra Relacional – OPERAÇÕESÁ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
108
Álgebra Relacional – OPERAÇÕESÁlgebra Relacional – OPERAÇÕES
UNIÃO COMPATÍVELQuando 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.
109
Álgebra Relacional – SIMBOLOGIASÁlgebra Relacional – SIMBOLOGIAS
UNIÃO – A B
INTERSEÇÃO – A B
DIFERENÇA – A - B
SELEÇÃO – (Condição seleção)
PROJEÇÃO – (Lista de Campos)
PROD.CARTESIANO – A B
JUNÇÃO – A x <condição de junção> B
110
SELEÇÃO
Ex: < condição de seleção > (nome da relação)
PROJEÇÃO
Ex: < 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)
Álgebra Relacional – SIMBOLOGIASÁlgebra Relacional – SIMBOLOGIAS
111
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”.
Álgebra Relacional – UNIÃOÁlgebra Relacional – UNIÃO
112
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.
Álgebra Relacional – INTERSECÇÃOÁlgebra Relacional – INTERSECÇÃO
113
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”.
Álgebra Relacional – DIFERENÇAÁlgebra Relacional – DIFERENÇA
114
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”.
Álgebra Relacional – PRODUTO CARTESIANOÁlgebra Relacional – PRODUTO CARTESIANO
115
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.
Álgebra Relacional – PROJEÇÃOÁlgebra Relacional – PROJEÇÃO
116
Parte 5Parte 5
SQLSQL
Structured Query LanguageStructured Query Language
117
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:
( Linguagem Estrutura de Consulta )
Onde Ai = atributo i, ti = tabela i e C = conjunto de condições
SQL – Structured Query LanguageSQL – Structured Query Language
118
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 Tabela1
SQL – Structured Query LanguageSQL – Structured Query Language
119
SELEÇÃO
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
T
R
R=SELEÇÃO T; (REG_VDA .NEQ. ‘SP’ AND TOTAL .GT. 500)
NRO_PED DATA REG_VDA TOTAL
11 13/12/87 RJ 722
14 28/04/87 MG 2455
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.
SQL – Structured Query LanguageSQL – Structured Query Language
120
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
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
AG_NOME REGIAO
Itaim Sul
Sto. Amaro Sul
Boa Vista Centro
Direita Centro
Diamantina Norte
b) SELECT AG_NOME, REGIAO FROM Agencia
SQL – Structured Query LanguageSQL – Structured Query Language
121
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”
d) SELECT AG_NOME, REGIAO FROM Agencia WHERE REGIAO <> “Centro”
e) SELECT REGIAO FROM Agencia
AG_NOME REGIAO
Boa Vista Centro
Direita Centro
AG_NOME REGIAO
Itaim Sul
Sto. Amaro Sul
Diamantina Norte
REGIAO
Sul
Sul
Centro
Centro
Norte
f) SELECT DISTINCT REGIAO FROM Agencia
REGIAO
Sul
Centro
Norte
SELEÇÃO - Exemplos
SQL – Structured Query LanguageSQL – Structured Query Language
122
JOIN - FusãoA 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.
A: PEDIDOS
C: Fusão de A e B segundo FORNEC
B: FORNECEDORESPEDIDO FORNEC DATA
720 214 110295
721 273 220595
916 214 120396
930 273 240496
FORNEC NOME
214 ALFA AS
273 BETA AS
283 GAMA AS
PEDIDO FORNEC DATA NOME
720 214 110295 ALFA AS
721 273 220595 BETA AS
916 214 120396 ALFA AS
930 273 240496 BETA AS
SQL – Structured Query LanguageSQL – Structured Query Language
123
JOIN - ExemplosTAB. AGENCIA TAB. CLIENTE
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. Diereita, 344 Centro
15-001 Diamantina R. Diamantina Norte
NUM_CLI NOM_CLI AG_NUM END_CLI
1002 Jair Bastos 12-005 R. ....
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 ...
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
SQL – Structured Query LanguageSQL – Structured Query Language
124
TAB. AGENCIA TAB. CLIENTEAG_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. Diereita, 344 Centro
15-001 Diamantina R. Diamantina Norte
NUM_CLI NOM_CLI AG_NUM END_CLI
1002 Jair Bastos 12-005 R. ....
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 ...
c) SELECT NUM_CLI, NOM_CLI, AG_NOME FROM Agencia, Cliente WHERE Agencia.AG_NUM=Cliente.AG_NUM AND REGIAO=“Sul”
NUM_CLI NOM_CLI AG_NOME
1004 Nelson Alves Itaim
1005 Ana Pádua Itaim
1006 Hugo Torres Sto. Amaro
1007 Tiago Melo Itaim
d) SELECT NOM_CLI, NUM_CLI, AG_NOME FROM Agencia, Cliente WHERE Agencia.AG_NUM=Cliente.AG_NUM AND NOM_CLI LIKE “N%”
NOM_CLI NUM_CLI AG_NOME
Nair Mendes 1003 Diamantina
Nelson Alves 1004 Itaim
JOIN - Exemplos
SQL – Structured Query LanguageSQL – Structured Query Language
125
Parte 6Parte 6
Normalização de TabelasNormalização de Tabelas
126
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ão-normalizados 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.
Normalização de TabelasNormalização de Tabelas
127
Consequências da Não-Normalização
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?
900,00900,009,00100GIZ21
RUA CHUÍ
N. 240CARLOS86418/05302
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
2180,00900,0090,0010CLIPS16RUA A, N.76
CARLOS
86428/04238
2180,00480,002,00240LÁPIS63RUA A, N.76
CARLOS
86428/04238
2180,00800,008,00100GIZ21RUA A, N.76
CARLOS
86428/04238
VAL-PED-TOT
VAL-PRC-TOT
VAL-PRC-UNT
QTDNOM-PRD
NUM-PRD
DES-END-CLI
NOM-CLI
NUM-CLI
DATNUM
PEDIDO
Normalização de TabelasNormalização de Tabelas
128
900,00900,009,00100GIZ21
RUA CHUÍ
N. 240CARLOS86418/05302
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
2180,00900,0090,0010CLIPS16RUA A, N.76
CARLOS86428/04238
2180,00480,002,00240LÁPIS63RUA A, N.76
CARLOS86428/04238
2180,00800,008,00100GIZ21RUA A, N.76
CARLOS86428/04238
VAL_
TOT_
PED
VAL_
TOT_
ITEM
VAL_
UNIT_
PRD
QTDNOME_PRD
NUM_
PRD
DES_
END_
CLI
NOME_
CLI
NUM_
CLI
DATA
NUM
PEDIDO
NORMALIZAÇÃO
Normalização de TabelasNormalização de Tabelas
129
*
*
*
2180,00
*
*
*
900,00
*
*
*
864
*
*
*
864
*
*
*
28/04
*
*
*
18/05
*
*
*
238
*
*
*
302
VAL_TOT_PEDNUM_CLI
(FK)
DATANUM
(PK)
*
*
*
RUA CHUÍ, N.240
*
*
*
CARLOS
*
*
*
864
DES_END_CLINOMENUM
(PK)
PEDIDOCLIENTE
*
*
*
800,00
480,00
900,00
*
900,00
*
*
*
100
240
10
*
100
*
*
*
21
63
16
*
21
*
*
*
238
238
238
*
302
VAL_TOT_ITEMQTDNUM_
PRD
(FK)
NUM_
PED
(FK)
ITEMPRD
*
98,00
*
*
9,00
*
*
2,60
*
CLIPS
*
*
GIZ
*
*
LÁPIS
*
16
*
*
21
*
*
63
VAL_UNIT_PRDNOMENUM
(PK)
PRD
Normalização de TabelasNormalização de Tabelas
*
*
*
800,00
480,00
900,00
*
900,00
*
*
*
100
240
10
*
100
*
*
*
21
63
16
*
21
*
*
*
238
238
238
*
302
VAL_TOT_ITEMQTDNUM_
PRD
(FK)
NUM_
PED
(FK)
ITEMPRD
130
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.
Normalização de TabelasNormalização de Tabelas
131
1ª FN – Exemplo 1
ELIMINAR DADO ESTRUTURADO (Atributo Composto)
90000
8888
*
*
011 2345678
0194 424166
*
*
JOÃO
CRISTINA
*
*
0100
0200
*
*
VAL_SALNUM_TEL_DDDNOMENUM (PK)
FUN
2345678
424166
*
*
NUM_TEL
9000
8888
*
*
011
0194
*
*
JOÃO
CRISTINA
*
*
0100
0200
*
*
VAL_SALDDDNOMENUM(PK)
FUN
1ª FN
Normalização de TabelasNormalização de Tabelas
132
1ª FN – Exemplo 2
ELIMINAR DADO REPETITIVO (Atributo Multivalorado)
90000
80000
*
*
JOSÉ,MARIA,CAIO
BEATRIZ
*
*
JOÃO
JOSÉ
*
*
0100
0300
*
*
VAL_SALNOME_DEPENDNOMENUM(PK)
FUN
90000
90000
90000
90000
JOSÉ
MARIA
CAIO
BEATRIZ
JOÃO
JOÃO
JOÃO
JOSÉ
0100
0100
0100
0300
VAL_SALNOME_DEPEND(PK)NOMENUM(PK)
FUN1ª FN
Normalização de TabelasNormalização de Tabelas
133
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 C2 depende funcionalmente de uma coluna C1 (ou que a coluna C1 determina a coluna C2) quando, em todas as linhas da tabela, para cada valor de C1 que aparece na tabela, aparece o mesmo valor de C2.
Normalização de TabelasNormalização de Tabelas
134
Dependência Funcional
O conceito fica mais fácil de entender se considerarmos um exemplo:
Normalização de TabelasNormalização de Tabelas
........ CÓDIGO ........ SALÁRIO ................
E1 10
E3 10
E1 10
E2 5
E3 10
E2 5
E1 10
135
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 “E1” 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 como na definição acima.
Normalização de TabelasNormalização de Tabelas
136
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.
Normalização de TabelasNormalização de Tabelas
137
2ª FN – Exemplo 1
90000
90000
90000
*
VAL_SAL
PESQUISA
PESQUISA
PESQUISA
NUM_DEPTO
CAROLINE
CAROLINE
CAROLINE
JOSÉ
MARIA
CAIO
JOÃO
JOÃO
JOÃO
*
0100
0100
0100
*
NOME_DIRNOME_DEPNOMENUM
FUN
PESQUISA
*
NOME_DEPTO
CAROLINE
*
90000
*
JOÃO
*
0100
*
NOME_DIRVAL_SALNOMENUM(PK)
FUN
JOSÉ
MARIA
CAIO
*
0100
0100
0100
*
NOME_DEP(PK)NUM_FUN(PK)
DPD +
2ª FN
Normalização de TabelasNormalização de Tabelas
138
2ª FN – Exemplo 2
3
3
2
*
*
*
QT_DIAR
STD
LUXO
SUITE
*
*
*
TIP_APT
100,00
140,00
170,00
*
*
*
VL_DIAR
300,00
420,00
340,00
*
*
*
104
105
82
*
*
*
01/07/89
01/07/89
08/07/89
*
*
*
JOSÉ
JOSÉ
ELZA
*
*
*
VL_TOTNUM_APTDT_ENTRNOMECLI
RSV
2ª FN
•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.
Normalização de TabelasNormalização de Tabelas
139
2ª FN – Exemplo 2
3
3
*
*
*
QT_DIAR
300,00
420,00
*
*
*
104
105
*
*
*
01/07/89
01/07/89
*
*
*
JOSÉ
JOSÉ
*
*
*
VL_TOTNUM_APTDT_ENTRNOMECLI
RSV
STD
LUXO
SUÍTE
TIP_APT
100,00
140,00
170,00
*
*
*
104
105
82
*
*
*
VL_DIARNUM(PK)
APT
+
Normalização de TabelasNormalização de Tabelas
140
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.
Normalização de TabelasNormalização de Tabelas
141
3ª FN – Exemplo 1
CAROLINE
JORGE
JORGE
FLAVIA
PESQUISA
VENDAS
VENDAS
FINANÇAS
0100
0200
0300
0400
NOME_DIRNOME_DEPTONUM(PK)FUN
PESQUISA
VENDAS
VENDAS
FINANÇAS
NOME_DEPTO(FK)
0100
0200
0300
0400
NUM(PK)
FUN
NUM NOME_DEPTONOME_DEPTO NOME_DIRNOME_DEPTO NUM
CAROLINE
JORGE
FLAVIA
NOME_DIR
PESQUISA
VENDAS
FINANÇAS
NOME_DEPTO(PK)
DEP
/
+
3ª FN
Normalização de TabelasNormalização de Tabelas
142
3ª FN – Exemplo 2
3,57
4,59
2,05
4,99
6,00
*
VAL_SAL_HOR
101
101
202
303
202
*
NUM_PRJ
BIBLIOTECA
BIBLIOTECA
RH
PD
RH
*
NOME_PRJ
23/06/90
23/06/90
20/08/91
20/12/92
20/08/91
*
1234567
2224567
2312200
2312200
2223300
*
0100
0200
0300
0400
0500
*
DT_FIMNUM_TELNUM
(PK)
FUN
1234567
2224567
2312200
2311000
2223300
NUM_TEL
3,57
4,59
2,05
4,99
6,00
VAL_SAL_HOR
101
101
202
303
202
NUM_PRJ
(FK)
0100
0200
030
0040
0050
NUM
(PK)
FUN
BIBLIOTECA
RH
PD
NOME
23/06/90
20/08/91
20/12/92
DT_FIM
101
202
303
NUM
(PK)
PRJ
+
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
Normalização de TabelasNormalização de Tabelas
143
Regras1ª 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.
Normalização de TabelasNormalização de Tabelas
144
Normalização de TabelasNormalização de Tabelas
145
Implicações
Normalização de TabelasNormalização de Tabelas