Embed Size (px)
Citation preview
1
Parte 1 – Conceitos Introdutórios
Dados – Matéria bruta de que é feita a informação. Valores fisicamente registrados no banco de dados.
Informação – Dado que significa alguma coisa para alguém e é utilizado em algum contexto.
Usuários/Profissionais e Atividades Envolvidas na Área de Banco de Dados
- Considera-se haver quatro grandes classes de usuários de banco de dados:
Usuário final: Precisa ter acesso à base de dados para consultar, modificar e gerar relatórios.
Interage com o sistema por meio de programas de aplicação ou através de interfaces integrantes do
próprio sistema.
A maioria dos sistemas fornece pelo menos uma aplicação embutida, ou seja, um processador de
linguagem de consulta interativo, com o qual o usuário é capaz de emitir instruções de alto nível para o
SGBD. Outros possuem ainda interfaces adicionais através das quais os usuários operam escolhendo
itens de menus ou preenchendo formulários.
Projetista da base de dados:
Decide o conteúdo do banco de dados: Identifica os dados a serem armazenados e escolhe estruturas
apropriadas para representar e armazenar tais dados, ou seja, faz o projeto conceitual do banco de
dados.
Programador de aplicações:
1. Responsável pelo desenvolvimento dos programas que utilizam o banco de dados,
caracteristicamente escritos em linguagens de terceira geração (Cobol, PL/I, C, Pascal, Java, etc).
2. Tais programas operam sobre os dados de todas as formas usuais: recuperação, inserção, deleção e
atualização de dados são executadas pelo envio de solicitações apropriadas ao SGBD.
Administrador de banco de dados (DBA):
É a pessoa, ou grupo de pessoas, responsável pelo controle do sistema, tendo as seguintes
responsabilidades:
o Servir de elo com os usuários: Garantir a disponibilidade dos dados de que eles necessitam.
o Definir os controles de segurança e de integridade: Acesso autorizado, quem pode acessar o que,
encriptação de dados, auditoria da base de dados. Local físico de armazenamento, duplicação,
etc.
o Definir estratégias de recuperação: Na eventualidade de danos a partes do banco de dados,
causados seja por erro humano, por falha no hardware ou no sistema operacional, é importante
recuperar os dados o mais rapidamente possível e com o mínimo de conseqüências para o
restante do sistema.
o Monitorar o desempenho e atender as necessidades de modificações.
Modelo Relacional e Outros
- Um sistema relacional é aquele no qual:
1) Os dados são percebidos pelos usuários como tabelas;
2) Os operadores à disposição dos usuários são operadores que geram novas tabelas a partir das antigas.
2
- O modelo relacional resultou de um estudo teórico realizado por Codd (pesquisador da IBM), tendo por base a
teoria dos conjuntos e a álgebra relacional. O estudo foi apresentado em 1970, mas só nos anos 80 foi
implementado.
- Revelou-se ser o mais flexível e adequado ao solucionar os vários problemas que se colocam ao nível da
concepção e implementação das bases de dados.
- A maioria dos sistemas de bancos de dados desenvolvidos nos últimos anos são relacionais, ao contrário dos
sistemas mais antigos.
- A abordagem relacional representa a tendência dominante no mercado de hoje e o modelo relacional é
considerado como a evolução mais importante em toda a história do ramo de banco de dados.
Os Outros
- Pode-se dividir os sistemas de acordo com as estruturas de dados e os operadores que apresentam ao usuário
em cinco categorias: hierárquico, rede, lista invertida, relacional e orientado a objetos.
- O modelo orientado a objetos foi o último a surgir (meados dos anos 80), sendo utilizado em aplicações tais
como Sistemas de Informações Geográficas (SIG) e sistemas CAD/CAM.
- A seguir temos uma pequena lista com exemplos de sistemas comerciais das quatro categorias.
Modelo Sistema Fabricante
Relacional
DB2 IBM
SQL/DS IBM
INGRES Relational Technology Inc.
ORACLE ORACLE Corp.
Rdb/VMS DEC
Lista Invertida
MODEL 204 CCA
ADABAS Software AG
DATACOM/DB Applied Data Research
Hierárquico IMS IBM
System 2000 Intel
Rede
IDMS Cullinet
DMS 1100 Sperry
TOTAL Cincorn Systems
N O M E R U A C ID A D E N Ú M E R O
C O N T AJ o ã o R ia c h u e lo S o ro c a b a 9 0 0
A n a G a l O só r io I tú 5 5 6
S a n d ra A c á c ia s S o ro c a b a 8 0 0
M a rc o s A p a re c id a S ã o R o q u e 6 4 7
N Ú M E R O
C O N T A
S A L D O
9 0 0 5 5 ,0 0
5 5 6 1 0 0 0 0 0 ,0 0
8 0 0 4 2 6 7 ,0 0
6 4 7 3 5 8 ,0 0
3
Trabalho de Pesquisa
1) Procure na Internet, em sites de ofertas de emprego, quais as denominações mais comuns para os profissionais
de banco de dados e identifique quais as habilidades mais requisitadas para tais profissionais. Faça um resumo de
uma página sobre o que encontrou.
2) Novamente, tomando por base ofertas de emprego para profissionais de banco de dados, enumere quais são os
SGBDs mencionados com maior frequência. Para cada SGBD, especifique o número de vezes (ofertas de emprego
diferentes) em que foi mencionado. Verifique, no mínimo, 20 ofertas distintas.
3) Faça o cálculo da média salarial dos profissionais de banco de dados, com base nas ofertas que encontrou.
Algumas questões de concursos públicos
1) (CESGRANRIO - 2010 - Petrobrás - Técnico em Informática) Existem muitas atividades envolvidas em um
Sistema Gerenciador de Bancos de Dados, o que torna necessária a contratação de profissionais especializados para
manter o bom funcionamento do sistema. Entre esses profissionais, aquele que tem a responsabilidade de
identificar os dados que irão compor a base de dados e escolher estruturas apropriadas para representar e armazenar
esses dados é o
a) Analista de Suporte Sênior.
b) Programador de Aplicações SQL.
c) Administrador da Base de Dados.
d) Técnico de Suporte a Usuários.
e) Projetista da Base de Dados.
2) (CESGRANRIO - 2010 - Petrobrás - Técnico em Informática) A linguagem, na qual um usuário requisita
informações do Banco de Dados e que é de mais alto nível que uma linguagem de programação comum, constitui
uma linguagem de
a) Consulta. b) Conexão. c) Máquina. d) Manipulação. e) Requisição.
3) (FCC - 2010 - AL-SP - Agente Técnico Legislativo Especializado - Admin e Arquitetura de Dados) A
identificação dos dados que devem ser armazenados no banco de dados, escolhendo a estrutura correta para
representar e armazenar dados, e, a avaliação das necessidades de cada grupo de usuários para definir as visões que
serão necessárias, integrando-as, fazendo com que o banco de dados seja capaz de atender a todas as necessidades
dos usuários, são de responsabilidade do
a) projetista de Banco de Dados.
b) DBA.
c) DBM.
d) ADB.
e) usuário final.
4) (FMP-RS - 2011 - TCE-RS - Auditor Público Externo – Administração) Um administrador de banco de dados
(Data Base Administrator – DBA) é responsável por realizar uma série de funções sobre um determinado banco
de dados. Entre as funções listadas abaixo, assinale a que NÃO É de competência específica de um DBA.
a) Conceder autorização para acesso a dados a usuários.
b) Zelar pela integridade do banco de dados e definir procedimentos para criação e recuperação de backups.
c) Auxiliar equipes de desenvolvimento e de testes a maximizar o desempenho e uso do banco de dados.
d) Planejar, documentar, gerenciar e integrar recursos de informações corporativas de forma a manter as regras de
negócio coerentes.
e) Projetar o armazenamento dos dados de forma a atender às necessidades de acesso.
4
Parte 2 – Modelagem: MER
Ciclo de vida de um banco de dados relacional
1. Formulação e análise de requisitos:
Relacionamentos naturais entre os dados (independentes de processo).
Plataforma: hardware/software: SO, SGBD
Requisitos de desempenho e integridade.
Resultado: Documento de especificação de requisitos.
2. Projeto lógico do banco de dados:
Modelo ER (projeto conceitual)
Integração dos múltiplos diagramas ER da empresa.
Transformação dos diagramas ER para tabelas relacionais.
Normalização das tabelas relacionais.
Resultado: Esquema global transformado para definições de tabelas.
5
3. Projeto físico do banco de dados e distribuição:
Definição das estruturas físicas de armazenamento de dados: tipo e tamanho dos campos, nomenclaturas, etc.
Seleção de índices e métodos de acesso.
Fragmentação dos dados, replicação: Minimizar tempo de resposta, minimizar custos, maximizar
disponibilidade.
4. Implementação do banco de dados, monitoração e manutenção.
Modelo Entidade-Relacionamento
- O modelo Entidade-Relacionamento foi proposto originalmente por Peter Pin Shan-Chen (MIT) em 1976, no
trabalho intitulado "The Entity-Relationship Model - Toward a Unified View of Data".
- Tem sido usado rotineiramente na análise e modelagem de sistemas.
- O modelo entidade-relacionamento (E-R) é baseado na percepção do mundo real como um conjunto de objetos
básicos chamados entidades e nos relacionamento entre as mesmas.
- Para a introdução dos conceitos associados ao modelo entidade-relacionamento e ao projeto de uma base de
dados, vamos utilizar um exemplo: A base de dados COMPANHIA.
A Base de Dados Companhia
- Base que armazena dados sobre funcionários, departamentos e projetos. Descrição:
Companhia organizada em departamentos. Cada departamento tem:
o Um nome;
o Um número que identifica o departamento;
o Número de funcionários;
o Uma localização;
o Um funcionário que gerencia o departamento:
Armazena-se a data de início em que o funcionário começou a gerenciar o departamento.
Um departamento é responsável pelo controle de diversos projetos. Cada projeto tem:
o Um nome;
o Um número que identifica o projeto;
o Uma localização.
Sobre o funcionário armazena-se:
o Nome;
o Número do seguro social;
o CPF;
o Endereço;
o Telefones;
o Salário;
o Sexo;
o Data de nascimento;
o Idade.
Todo funcionário é associado a um departamento, mas pode trabalhar em diversos projetos, não
necessariamente controlados pelo mesmo departamento (todo projeto é controlado por algum departamento,
mas há departamentos que não controlam nenhum projeto). Todo funcionário precisa atuar em pelo menos um
projeto. Armazena-se:
o Número de horas que o funcionário trabalha em cada projeto.
6
o Quem é o supervisor direto de cada funcionário. Todo funcionário tem um supervisor.
Os dependentes de cada funcionário são armazenados para garantir os benefícios do seguro. Para cada
dependente são armazenados:
o Nome;
o Sexo;
o Data de nascimento;
o Idade;
o Relacionamento com o empregado.
Símbolos Utilizados
Retângulos – Representam entidades.
Elipses – Representam atributos.
Losângulos – Representam relacionamentos.
Linhas – Ligam atributos a entidades e entidades a relacionamentos.
Conceitos do Modelo Entidade-Relacionamento
Entidades e Atributos
- Entidade: - É uma classe de objetos do mundo real que possuem uma existência independente e que possuem
propriedades em comum sobre as quais se deseja armazenar dados.
- Tais objetos podem ter existência física: Pessoa, Carro, Livro.
- Podem ainda ter apenas existência conceitual: Curso Universitário, Projeto.
7
- Instância de entidade: - É uma ocorrência de uma entidade. O equivalente lógico de uma instância é um
registro de uma tabela. Exemplo: A entidade Funcionário possui várias instâncias: o funcionário Pedro da Costa,
com CPF 123456789, que mora no endereço R. A, 23; o funcionário Paulo da Silva, com CPF 987654321, que
mora no endereço R. B, 32 e assim por diante.
- Atributos: - Propriedades particulares de uma entidade (ou relacionamento).
Exemplo:
Nome=João Nunes Silva da Silva
f1 Endereço = R. Goiás, 711
São Paulo - SP, CEP: 13011-000
Idade = 55
Fone residencial = 11-3713-3345
Salário = 2300,00
- Classificação dos Atributos
Os atributos são classificados em:
Simples OU Compostos
Monovalorados OU Multivalorados
Obrigatórios OU Opcionais
Derivados OU Não-Derivados
Chaves OU Não-Chaves
As definições são como segue:
Atributo composto: Pode ser dividido em subpartes com significados independentes.
Exemplos: Um endereço pode ser subdividido em Logradouro (rua e número), Cidade, Estado e CEP.
Um telefone pode ser subdividido em DDI, DDD e Número propriamente dito.
É útil decompor um atributo em suas partes quando há a necessidade de se referenciar o mesmo como um
todo, mas algumas vezes, ter acesso a apenas alguns de seus componentes.
Atributo simples: Em contraposição aos atributos compostos, um atributo simples é aquele cujo valor não
se pode subdividir em partes. Exemplo: O atributo salário.
Representação:
Atributo multivalorado: É aquele que pode assumir múltiplos valores para uma dada instância de uma
entidade.
Exemplos: É comum que uma pessoa tenha vários números de telefone ou vários endereços comerciais.
Simples
Composto
Simples Simples Simples
8
Atributo monovalorado: Em contraposição aos atributos multivalorados, um atributo monovalorado
assume um único valor para cada instância de uma entidade.
Exemplos: Nome, CPF, data de nascimento são todos exemplos de atributos que, para uma dada pessoa,
assumem um único valor.
Representação:
Atributo opcional: Quando uma entidade não possui valor para determinado atributo ou quando seu valor
é desconhecido, um valor especial chamado null é usado neste caso.
Exemplos: Um valor null é a representação de:
Um valor não aplicável: Funcionário com endereço sem número de apartamento porque
reside em casa.
Um valor desconhecido no momento: Funcionário ainda não adquiriu um telefone
residencial.
Valor indefinido: Funcionário encarregado de várias funções, sem cargo definido.
Atributo obrigatório: Atributo cujo valor sempre existe e é conhecido para todas as instâncias de uma
entidade.
Exemplos: Todo funcionário possui nome, data de nascimento, CPF, dentre outros.
Representação:
Nesta representação:
- Um atributo opcional tem valor mínimo 0.
- Um atributo obrigatório tem valor mínimo 1.
Observação: Também se pode utilizar esta representação para distinguir atributos monovalorados de
atributos multivalorados.
Representação:
Nesta representação:
- Um atributo monovalorado tem valor máximo 1.
- Um atributo multivalorado tem valor máximo N.
Mono Multi
Entidade
(0,1)
Opcional
Entidade
(1,1)
Obrigatório
Valor
mínimo
Valor
máximo
Entidade
(1,1)
Mono
Entidade
(1,N)
Multi
9
Atributo derivado: Aquele cujos valores que assume podem ser obtidos a partir de outro atributo ou a
partir de relacionamentos entre entidades, através de um processo algébrico. Normalmente não são
armazenados em bancos de dados.
Exemplos: O atributo idade pode ser obtido a partir da data de nascimento do funcionário, fazendo-se uma
conta de subtração entre datas.
O atributo número de funcionários da entidade Departamento pode ser obtido através de um processo de
contagem do número de instâncias do relacionamento Trabalha entre Funcionário e Departamento.
Atributo não-derivado: Aquele cujos valores que assume não podem ser obtidos a partir de outro atributo
ou a partir de relacionamentos entre entidades.
Exemplo: A data de nascimento do funcionário não pode ser obtida a partir de sua idade.
Representação:
Atributo Chave (Identificador): Atributo ou conjunto de atributos que identificam de modo único cada
instância de uma entidade. Não pode haver duas instâncias de uma entidade com o mesmo conteúdo para o
atributo identificador. Uma entidade pode ter várias chaves candidatas (também chamadas chaves
alternativas).
Exemplos: CPF, RG, CNH para pessoas. DDI para países.
Atributo Não-Chave: Atributo cujo valor pode se repetir entre instâncias de uma mesma entidade.
Exemplos: Na maioria dos contextos o nome de uma pessoa não é um atributo chave, pois existem
homônimos.
Representação:
- Chave-Primária: Deve ser escolhida dentre os atributos chave, devendo satisfazer aos seguintes critérios:
Unicidade: É a principal característica de uma chave. Seu valor é único para cada instância de uma
entidade, permitindo que, através deste valor, se identifique cada uma destas instâncias.
Ser atributo obrigatório: Uma chave primária não pode assumir valores nulos. Assim, embora o número
da CNH seja único, nem todos a possuem, de forma que este não é um bom candidato à chave-primária.
Ser mínima: Poderia se pensar em tomar a dupla de atributos (cpf, nome) como sendo a chave-primária da
entidade Funcionário. No entanto, esta chave não seria mínima, pois o atributo cpf isoladamente já é um
atributo chave.
Ser estável ao longo do tempo: Embora todo funcionário deva ter um número de conta bancária a si
associado, necessária para que seja efetuado o seu pagamento mensal e, ainda que este atributo possa ser
único e obrigatório, ele pode sofrer alterações ao longo do tempo. Por isso, esta não seria uma boa escolha
para a chave-primária da entidade Funcionário.
Não-Derivado Derivado
Não-Chave Chave-Primária
10
Relacionamentos
- Relacionamento: Classe de associações entre duas ou mais entidades.
Exemplo: Há um relacionamento “Trabalha-Para” entre a entidade Funcionário e a entidade Departamento.
- Instância de relacionamento: Ocorrência particular de um relacionamento, envolvendo instâncias específicas de
cada uma das entidades envolvidas.
Exemplo: O Funcionário João Nunes Silva da Silva “trabalha para” o Departamento de Desenvolvimento de
Produtos.
Atributos de Relacionamentos
- Os relacionamentos também podem ter atributos, da mesma forma que as entidades.
Exemplo: Necessidade de se registrar o número de horas que um funcionário dedica a um determinado projeto.
Grau de um relacionamento
- Relacionamentos classificam-se quanto ao número de entidades envolvidas em:
Binários: Envolvem duas entidades.
Funcionário
Cpf
Departamento
Número
TrabalhaPara
TrabalhaEm Projeto
Número
Funcionário
Cpf NumHoras
TrabalhaEm
Funcionário
Projeto
TrabalhaPara
11
Binários recursivos: Envolvem a associação entre diferentes instâncias de uma única entidade, as quais
participam do relacionamento assumindo diferentes papeis.
Ternários: Envolvem três entidades.
Mapeamento de Restrições
- O mundo real pode impor certas restrições que são refletidas nos relacionamentos.
- Estas restrições podem ser decorrentes de:
Regras de negócio do empreendimento para o qual se modela o banco de dados. Exemplo: Uma regra
segundo a qual um empregado trabalha apenas para um departamento.
Senso comum, isto é, aquilo que é de conhecimento de todos, sem precisar que seja enunciado. Exemplo:
Toda pessoa tem uma única mãe natural.
- Dois tipos principais de restrições de relacionamentos são a razão de cardinalidade de um relacionamento e a
dependência de existência (restrição de participação).
Razão de Cardinalidade
- A cardinalidade expressa o número de instâncias de uma entidade às quais uma instância de outra entidade pode
estar associada por meio de um relacionamento e é, obviamente, dependente das situações reais que estão sendo
modeladas pelo relacionamento.
- Para os relacionamentos binários entre entidades A e B, bem como para relacionamentos binários recursivos, a
razão de cardinalidade pode ser:
Um para um (1:1): Uma instância da entidade A está associada a no máximo uma instância da entidade B,
e uma instância da entidade B está associada a no máximo uma instância da entidade A.
Supervisiona Funcionário
Fornecedor
Projeto
Peça
Fornece
12
Exemplos: Funcionário – Gerencia - Departamento
Pessoa – Casada – Pessoa
Um para muitos (1:N): Uma instância da entidade A está associada a várias instâncias da entidade B, e
uma instância da entidade B está associada a, no máximo, uma instância da entidade A.
Exemplo: Funcionário – Trabalha Para – Departamento
Funcionário
Departamento
Gerencia 1 1
Casada Pessoa
1
1
esposo
esposa
Funcionário
Departamento
TrabalhaPara N 1
Gerencia
Casamento
João
Nune
s
Silva Maria
Jussara
Leôncio
Marcia
Joel
Ana
Ivo
Pedro
Lúcia
Depto Pessoal
Depto Compras
Depto Marketing
Depto Pesquisa
José
Marta
Juliana
Leonel
Maira
Igor
13
Pessoa – É Mãe Natural de – Pessoa
Muitos para muitos (N:M): Uma instância da entidade A está associada a várias instâncias da entidade B,
e uma instância da entidade B está associada a várias instâncias da entidade A.
Exemplo: Funcionário – AtuaEm - Projeto
Progenitora Pessoa
1
N
mãe
filho
Funcionário
Projeto
AtuaEm N N
Trabalha Para
João
Nune
s
Silva Maria
Jussara
Leôncio
Marcia
Joel
Ana
Ivo
Pedro
Lúcia
Depto Pessoal
Depto Compras
Depto Marketing
Depto Pesquisa
Laura
Larissa
a Mauro
Meire
Murilo
Bia
Ana
Progenitora
14
Peça – Compõe – Peça
Restrição de Participação
- Esta restrição especifica se a existência de uma instância de uma entidade depende de ela estar relacionada com
uma instância de outra entidade através de um relacionamento.
- Existem dois tipos de restrição de participação:
Total: Também chamada de dependência existencial. Ocorre quando a existência de uma instância de uma
dada entidade depende da participação da mesma em um relacionamento.
Exemplo:
- Se uma companhia estabelece a regra de que todo Departamento deve ter um funcionário que atua como seu
gerente, então uma instância da entidade Departamento só pode existir se participar de um relacionamento
Gerencia com a entidade Funcionario. Por outro lado, nem todo funcionário é gerente de algum departamento.
- A participação de Departamento em Gerencia é total, ao passo que a participação de Funcionario é parcial.
- A entidade Funcionario é chamada de entidade dominante ou forte e a entidade Departamento de entidade
dependente ou subordinada.
Parcial: Ocorre quando a existência das instâncias de uma dada entidade é independente de sua participação
em qualquer relacionamento.
Compõe Peça
N
N
contida
contém
João
Nune
s
Silva Maria
Jussara
Leôncio
Marcia
Joel
Ana
Ivo
Pedro
Lúcia
Proj ABC
Proj XYZ
Proj XXX
Proj DEF
Atuação em Projeto
15
Exemplo:
- Não é esperado que todo funcionário gerencie um departamento, assim a participação de Funcionário no
relacionamento Gerencia é parcial.
- Não necessariamente todas as instâncias da entidade Funcionário estarão relacionadas a instâncias da entidade
Departamento via Gerencia.
- Uma possível notação para representar se a participação de uma entidade em um relacionamento é parcial ou
total é através do uso de:
Linha simples ligando a entidade ao relacionamento para participação parcial.
Linha dupla ligando a entidade ao relacionamento para participação total.
Exemplo:
Outras Notações Possíveis
Notação de Peter-Chen
- A notação de cardinalidade mínima e máxima (notação de Peter-Chen) também é muito utilizada.
- As cardinalidades mínima e máxima são expressas entre parênteses da seguinte forma:
(cardinalidade mínima, cardinalidade máxima)
- Os valores que a cardinalidade mínima pode assumir são:
0: Representa Participação Parcial da entidade que está no lado oposto no relacionamento.
1: Representa Participação Total da entidade que está no lado oposto no relacionamento.
- Os valores que a cardinalidade máxima pode assumir são 1 ou N, tendo o mesmo significado já visto
anteriormente.
Exemplos:
1) Um funcionário trabalha para apenas um departamento e todo funcionário tem que estar atrelado a um
departamento. Expressamos isso como (1, 1), isto é, no mínimo 1 e, no máximo, 1.
Todo departamento tem funcionários trabalhando nele. Um departamento pode ter vários funcionários
trabalhando nele. Expressamos isto como (1, N), isto é, no mínimo 1 e, no máximo, N.
2) Todo departamento tem um funcionário que atua como gerente dele. Um departamento pode ter um único
gerente. Expressamos isso como (1, 1).
Um funcionário pode ou não ser gerente de um departamento. Um funcionário só pode gerenciar um único
departamento. Expressamos isso como (0, 1), isto é, no mínimo, 0 e, no máximo, 1.
Funcionário
Departamento
Gerencia 1 1
(1, 1) Funcionário
Departamento
TrabalhaPara (1, N)
16
Notação de James Martin
- As principais diferenças com relação às notações anteriores são:
Os relacionamentos são representados apenas por uma linha.
Somente permite a representação de relacionamentos binários.
A notação de cardinalidade máxima e mínima é gráfica.
O símbolo mais próximo ao retângulo representa a cardinalidade máxima.
O símbolo mais distante representa a cardinalidade mínima.
Representações:
associados a:
Exemplos:
Um funcionário tem que trabalhar para um e apenas um departamento.
Um departamento tem um ou mais funcionários trabalhando nele.
Um passageiro pode ocupar, em uma viagem de avião, apenas uma poltrona.
Uma poltrona pode estar ocupada por um ou nenhum passageiro.
(0, 1) Funcionário
Departamento
Gerencia (1, 1)
Muitos
Um
A ocorrência do relacionamento é opcional.
A ocorrência do relacionamento é obrigatória.
Passageiro
Poltrona
Funcionário
Departamento
Cardinalidade Mínima
Cardinalidade Máxima
17
Entidades Fracas
- Algumas entidades podem não ter quaisquer atributos-chave. Isto implica que podem haver instâncias cuja
combinação dos valores dos atributos são idênticas.
- A identificação destas instâncias ocorre por estarem associadas a instâncias de uma outra entidade, em
combinação com algum ou alguns de seus atributos.
- Esta outra entidade é dita ser proprietária da identificação e o relacionamento que relaciona uma entidade fraca
com a proprietária da identificação é chamado relacionamento de identificação.
- Uma entidade-fraca sempre tem uma restrição de participação total (dependência existencial) com relação ao seu
relacionamento de identificação.
Exemplo: Entidade Dependente, relacionada a Funcionario. Trata-se de um relacionamento 1:N.
Os atributos de Dependente são Nome, DataNasc, Sexo e Relação com o funcionário (esposa, marido,
filho, etc).
Dependentes de funcionários diferentes podem ter os mesmos valores para os atributos, e ainda assim
tratam-se de entidades distintas.
Os dependentes serão identificados como entidades distintas após a determinação da entidade
funcionário com a qual cada um está relacionado.
- Uma entidade fraca possui uma chave parcial, que é um atributo ou conjunto de atributos que pode
univocamente identificar instâncias da entidade fraca relacionadas à mesma instância da entidade proprietária.
Exemplo: Considerando que os dependentes de um mesmo funcionário terão nomes diferentes, então o atributo
Nome de Dependente será a chave parcial.
18
Projeto da Base de Dados Companhia Utilizando o Modelo Entidade-Relacionamento
- Podemos especificar os seguintes tipos de relacionamentos extraídos da descrição sobre a companhia:
Gerencia (1:1) entre Funcionário e Departamento.
A participação de Funcionário é parcial. A participação de Departamento é total.
O atributo DataIni é associado a esse relacionamento.
Trabalha-Para (1:N) entre Departamento e Funcionário.
Ambos têm participação total.
Controla (1:N) entre Departamento e Projeto.
A participação de Projeto é total e de Departamento é parcial.
Supervisiona (1:N) entre Funcionário (no papel de supervisor) e Funcionário (no papel de
supervisionado).
A participação de Funcionário no papel de supervisor é parcial, pois nem todo funcionário é supervisor,
mas a participação de Funcionário no papel de supervisionado é total, pois todo funcionário é
supervisionado.
Trabalha-Em (N:N) entre Funcionário e Projeto.
Ambos têm participação total.
O atributo Horas é associado a este relacionamento.
Dependente-de (1:N) entre Funcionário e Dependente.
É um relacionamento de identificação para a entidade-fraca Dependente.
A participação de Funcionário é parcial e de Dependente é total.
Telefone
19
Mais sobre relacionamentos ternários
- Muitas vezes é difícil decidir se um relacionamento deve ser representado como sendo ternário ou se não pode ser
representado por vários relacionamentos binários entre as entidades participantes. O projetista da base de dados
deve se guiar pelo significado da situação particular que estiver representando para decidir qual alternativa adotar.
Exemplo1:
País
N
Vende
N N
Companhia Produto
- Para um dado par (companhia, produto) há vários países para os quais o produto é vendido por aquela companhia.
- Para um dado par (país, produto) há várias companhias exportando aquele produto para aquele país.
- Para um dado par (companhia, país) há vários produtos exportados por aquela companhia para aquele país.
Companhia Produto País
A abóbora Alemanha
A abóbora Bélgica
B abóbora Alemanha
A pepino Alemanha
C pepino Bélgica
- Deve-se notar que, no nosso exemplo hipotético, A não exporta pepino para a Bélgica.
- Não conseguiríamos representar este fato através do seguinte diagrama ER, a partir do qual a leitura que se faz é
de que uma companhia manufatura vários produtos e exporta todos para um número de diferentes países.
N Exporta N País
Companhia
N Produz N Produto
Uma companhia
produz muitos
produtos e exporta
todos os produtos
que produz para um
número de
diferentes países.
20
Companhia País Companhia Produto
A Alemanha A abóbora
A Bélgica A pepino
C Bélgica C pepino
B Alemanha B abóbora
- Também não conseguiríamos representar este fato através do DER abaixo:
N Exporta N
Companhia País
N M
Produz N Produto N Compra
Companhia País Companhia Produto País Produto
A Alemanha A abóbora Alemanha abóbora
A Bélgica A Pepino Alemanha pepino
C Bélgica C Pepino Bélgica abóbora
B Alemanha B abóbora Bélgica pepino
Exemplo 2: Relacionamento ternário 1:N:N
Gerente
1
Gerencia
N N
Engenheiro Projeto
Para um dado par (Engenheiro, Projeto): Há apenas 1 gerente. Isto é, cada engenheiro trabalhando em um projeto
particular tem exatamente um gerente.
Para um dado par (Gerente, Projeto): Há vários engenheiros. Isto é, cada gerente de um projeto pode gerenciar
muitos engenheiros.
Para um dado par (Gerente, Engenheiro): Há vários projetos. Isto é, cada gerente de um engenheiro pode gerenciar
aquele engenheiro em vários projetos.
21
Exemplo 3: Relacionamento ternário 1:1:N
Projeto
1
DesignadoA
1 N
Local Funcionário
Para um dado par (Funcionário, Projeto): Há apenas um local de trabalho. Isto é, cada funcionário designado para
um projeto trabalha em apenas um local naquele projeto (mas pode estar em diferentes locais para diferentes
projetos).
Para um dado par (Funcionário, Local): Há apenas um projeto. Isto é, em um particular local um funcionário
trabalha apenas em um projeto.
Para um dado par (Projeto, Local): Há vários funcionários. Isto é, em um particular local podem haver muitos
funcionários designados para um projeto.
Atenção: Este DER não implica que um funcionário participe de um só projeto.
Exemplo 4: Relacionamento ternário 1:1:1
Professor
1
Usa
1 1
Livro Curso
Um professor usa um livro para um dado curso.
Nenhum professor usa o mesmo livro para diferentes cursos.
Mas diferentes professores podem usar o mesmo livro em diferentes cursos.
Extensões do Modelo E-R
- Apesar de ser possível modelar a maioria dos bancos de dados apenas com os conceitos básicos do E-R, alguns
aspectos de um banco de dados podem ser expressos de modo mais conveniente por meio de algumas extensões.
São estas: a Especialização/Generalização e as Entidades Associativas.
22
Especialização/Generalização
- Uma entidade pode conter subgrupos de instâncias que são, de alguma forma, diferentes de outras instâncias do
conjunto.
- O processo de projetar subgrupos de uma entidade é chamado especialização e é representado graficamente por
um triângulo.
Exemplo:
- Estas entidades de nível inferior podem possuir atributos, ou mesmo participar de relacionamentos que não
podem ser aplicados a todas as instâncias da entidade de nível superior.
- O uso do mecanismo de especialização evita que se tenha entidades com muitos atributos opcionais.
Exemplo:
(0,1)
Funcionário
Nome
Código
TipoFuncionário
CREA
DataExpiracaoCarteiraHabilitacao
NumeroCarteiraHabilitacao CRM
(0,1) (0,1)
(0,1)
Funcionário
Motorista Médico Engenheiro
Nome
Código
DataExpiracaoCarteiraHabilitacao
NumeroCarteiraHabilitacao
CREA CRM
Esta situação fica melhor modelada assim:
G
E
N
E
R
A
L
I
Z
A
Ç
Ã
O
Cliente
PessoaFísica PessoaJurídica
CPFouCNPJ
Sexo
Nome
RazaoSocial DataNasc
DataInaugura
E
S
P
E
C
I
A
L
I
Z
A
Ç
Ã
O
23
- A generalização é simplesmente o inverso da especialização.
- Existem dois tipos de generalização/especialização: Total (representada pela letra T) e Parcial (representada pela
letra P):
• Total: Toda ocorrência da entidade genérica corresponde a uma ocorrência de uma das entidades especializadas.
• Parcial: Nem toda ocorrência da entidade genérica corresponde a uma entidade especializada.
Exemplos:
- No primeiro exemplo, todo Cliente é ou uma Pessoa Física ou uma Pessoa Jurídica. Não há outras opções além
destas.
- No segundo exemplo, uma Pessoa pode ser um Professor, um Aluno ou ainda um Funcionário da instituição de
ensino. Os funcionários não estão representados como subentidades porque, neste caso, não haveria outros
atributos (que fossem particulares de funcionários), além daqueles atributos comuns a todas as pessoas.
- Não há limite para o número de níveis hierárquicos no processo de especialização/generalização. Temos um
exemplo a seguir com três níveis.
Exemplo:
p
Pessoa
Professor Aluno
t
Cliente
PessoaFísica PessoaJurídica
ProfGraduacao ProfPos
Pessoa
Professor Aluno
AlunoGraduacao AlunoPos
24
Herança de Atributos e Relacionamentos
- Os atributos das entidades de nível superior são herdados pelas entidades de nível inferior.
- As entidades de nível inferior (ou subentidades) também herdam a participação em relacionamentos dos quais
participam as entidades de nível superior (ou superentidades).
Entidade Associativa
- Um relacionamento é uma associação entre entidades. O Modelo ER original não comporta a possibilidade de se
associar uma entidade com um relacionamento ou então de associar dois relacionamentos entre si.
- No entanto, na prática, eventualmente surge esta necessidade. Vamos ilustrar isto com um exemplo.
Exemplo: - Suponhamos que, em uma dada instituição de ensino, alunos que estejam tendo dificuldades em alguma disciplina
tenham direito ao acompanhamento de um tutor nesta disciplina.
- A função do tutor é acompanhar o desempenho do aluno na disciplina e auxiliá-lo nas suas dificuldades com a
disciplina.
- Representamos isso como segue:
Refletindo o aspecto temporal
- A modelagem do banco de dados deve refletir o fato de que existem:
Atributos cujos valores sofrem modificações ao longo do tempo.
Exemplo:
CategoriaFunc
salário
CategoriaFunc
Recebe Salário 1 N
data valor
O banco de dados
contém apenas o
salário atual.
O banco de dados
contém o histórico dos
salários.
Aluno
Tutor
Disciplina
Tutoria
Matriculado Matriculado N N
N
1
25
Relacionamentos sofrem modificações ao longo do tempo.
Exemplo:
Exercício sobre Classificação de Atributos Atividade envolvendo o cenário: Instituição de Ensino “Aprendendo a Aprender”.
Considere a seguinte situação:
A instituição de Ensino Técnico “Aprendendo a Aprender”, localizada na zona Norte de São Paulo, foi fundada em
1970.
Conta com aproximadamente 800 alunos, 10 professores e os cursos de Tecnologia da Informação e Magistério,
que são oferecidos nos períodos manhã, tarde e noite.
Conta também com 8 funcionários que dão suporte administrativo e financeiro à instituição.
Inicialmente, a instituição controlava as suas informações manualmente por meio de fichas que continham o
histórico dos alunos, os dados cadastrais de alunos, professores e funcionários, informações sobre os cursos, entre
outras informações.
Sabemos que os alunos são matriculados em um curso, que os cursos são compostos por disciplinas e que as
disciplinas são ensinadas por professores.
A seguir são apresentados alguns exemplos de relatórios com as informações que eram preenchidas:
Aluno:
Nome Curso Semestre Data de Nascimento RG Endereço completo
Antonio Ubaldo
da Silva
Processamento de
Dados
1 15/08/1984 3789789 R. das Magnólias, 329 - Bela
Vista – CEP:02460-044
São Paulo – SP
Ana Regina
Domingues
Magistério 4 07/02/1986 1234556 R. Madrigal, 875 - Pinheiros
– CEP:42568-144
São Paulo – SP
Curso:
Nome Duração (horas) Disciplinas Portaria de aprovação
Processamento de Dados 2000 Cálculo numérico
Português,
Fundamentos de computação
...
578/70
Magistério 1800 Português,
Literatura,
Pedagogia
...
579/70
Funcionário
Mesa
Alocação
1
1
Alocação
Funcionário
Mesa
N
N
data
O banco de dados
contém apenas a
alocação atual.
O banco de dados
contém o histórico das
alocações.
26
Disciplina:
Nome Carga Horária Semestre Professor Turma Curso
Cálculo
Numérico
80 2 Murilo Maciel 1
2
Processamento de Dados
Pedagogia 80 4 Maria Luiza Passos 1
2
Magistério
Professor:
Nome Data de Nascimento RG Endereço completo
Murilo Maciel 14/10/1970 17172545 R. das Acácias, 41 - Ibirapuera – CEP:12345-544
São Paulo – SP
Maria Luiza
Passos
27/04/1972 3216548 R. Arquimedes, 85 – Cerqueira Cesar – CEP:74125-632
São Paulo – SP
1. Para cada tabela classifique os atributos quanto a se são:
simples ou compostos, multivalorados ou monovalorados, opcionais ou não, derivados ou não, chaves candidatas
ou não.
2. Para cada tabela, identifique um campo candidato à chave primária. Caso não haja nenhum, sugira o acréscimo
de uma nova coluna à tabela, indicando o nome da coluna e o tipo de dado.
Exercícios sobre Modelo Entidade Relacionamento – Parte 1
1. Represente o modelo Entidade-Relacionamento correspondente aos seguintes enunciados. Coloque pelo menos
três atributos em cada entidade, identificando um como chave-primária. Identifique as cardinalidades e restrições
de participação:
a) Atletas participam de competições. Em uma competição participam vários atletas.
b) Em uma rede de hotelaria, os hotéis possuem quartos. Cada quarto pertence a apensa um hotel.
c) Um país possui estados. Um estado pertence a apenas um país. Atenção: O Vaticano é um país que não
possui estados.
d) Todo soldado, que possui as características: Nome, Registro Militar (RM), data de nascimento, possui
armas. Toda arma, que possui as características de série, registro e calibre, é de um soldado. Uma arma é
limpa por vários soldados. Um soldado limpa várias armas.
2. Represente os diagramas do exercício anterior usando a notação de Peter-Chen (cardinalidade máxima e
mínima).
Exercícios sobre Modelo Entidade Relacionamento – Parte 2
1) Minerais
Solicita-se o projeto de um banco de dados para armazenar informações sobre os países do mundo e suas
reservas de minerais.
Para cada mineral deve-se registrar o nome do mineral e o seu preço corrente em dólares, por grama.
Para cada país, a informação a ser registrada inclui o nome do país, seu produto interno bruto (PIB) e, para cada
mineral encontrado no país, a produção anual (em toneladas) e reserva estimada (em toneladas).
Um país pode ter reservas de muitos minerais e cada mineral pode ser encontrado em muitos países.
2) Colégio Um professor ministra várias disciplinas.
27
Uma determinada disciplina só é ministrada por um único professor. Por exemplo, apenas um professor é
responsável por ministrar Literatura.
Cada disciplina é identificada por um código e possui um nome e uma carga horária.
Um aluno pode estar matriculado em várias disciplinas.
Sobre o aluno registra-se o nome, endereço (Rua, Número, Cidade, Estado e CEP) e telefone. Todo aluno
tem um código que o identifica de forma única dentro do colégio.
Sobre o professor registra-se o nome, endereço (Rua, Número, Cidade, Estado e CEP), telefone e titulação
máxima. Todo professor também possui um código que o identifica de forma única dentro do colégio.
Para cada disciplina em que o aluno está matriculado são armazenadas, ao final do período letivo a sua
média final e a sua frequência na disciplina.
3) Clínica Em uma clínica trabalham vários médicos.
Um médico pode ter várias especialidades.
Uma especialidade é identificada por um código e possui um nome que a descreve. Além disso, registra-se
se se trata de uma especialidade reconhecida ou não pelo Conselho de Medicina.
Sobre os médicos registra-se o seu Nome, CRM, CPF, RG, Endereço (Rua, Número, Cidade, Estado e CEP)
e Telefone.
Diariamente pacientes são atendidos na clínica por diferentes médicos.
Para cada consulta armazena-se a data e o valor da consulta.
Para todo paciente atendido na clínica é feito um cadastro aonde consta o seu nome e endereço. Além disso,
cada novo paciente ganha um código de identificação dentro da clínica.
4) Componentes eletrônicos
Uma companhia que produz componentes eletrônicos deseja manter uma base de dados a respeito dos
dispositivos que manufatura e dos componentes de que cada dispositivo necessita para ser manufaturado.
Para cada componente, deve-se armazenar um número de identificação (único), um nome, descrição, o
nome e o endereço do fornecedor.
Cada componente possui apenas um fornecedor, mas um dado fornecedor pode fornecer muitos
componentes diferentes.
Para cada dispositivo, deseja-se registrar um número de identificação único e o seu nome, juntamente com o
preço corrente do dispositivo e a quantidade em estoque.
Deseja-se registrar também a quantidade de cada componente necessária para a manufatura de cada
dispositivo.
5) Aluguel de carros
Uma firma de aluguel de carros deseja manter um banco de dados para registrar detalhes a respeito da sua
frota de carros e vans e sobre os alugueis que são feitos.
Para cada carro ou van os detalhes a serem registrados incluem: o número de registro (único), a marca, o
modelo, número de portas, cor, potência do motor e a data de registro do veículo.
Para cada van uma informação adicional é a sua carga máxima, em toneladas.
Para cada carro, por sua vez, deseja-se armazenar adicionalmente o volume do porta-malas.
A cada vez que um veículo é alugado, o nome, endereço, número de telefone e número da carteira de
motorista do cliente devem ser registrados, juntamente com a identificação do veículo que está sendo alugado.
A data e o horário do início e do final do período de aluguel também são registrados, bem como o número
de quilômetros rodados pelo cliente e o custo total do aluguel.
6) Administradora de condomínios
Construir um diagrama E-R para uma administradora de condomínios, considerando que:
Um condomínio é formado por diversas unidades habitacionais.
Cada unidade habitacional pertence a um proprietário, que pode possuir diversas unidades.
28
Cada unidade pode ser alugada.
Toda pessoa (proprietário ou locatário) possui um código, um nome e um endereço. Para o locatário, deseja-se
armazenar o CPF do fiador.
Toda unidade possui um código que a identifica no condomínio e um endereço. Também deseja-se armazenar
qual é a área construída.
Um condomínio é identificado por um código e endereço.
Entre os proprietários de um condomínio, um é síndico.
7) Companhia aérea Suponha que você tenha sido contratado pela companhia aérea Tomara-Que-Chegue-Lá para projetar um
banco de dados para administrar sua operação.
Atualmente a companhia possui dois aviões B747, cujos números de série são N1001 e N1002, e um avião
MD11, cujo número de série é N2001.
O N1001 foi comprado em 1990, o N1002 em 1995, e o N2001 em 1985.
A capacidade de cada B747 é de 200 passageiros e do MD11 é de 150 passageiros.
Na empresa trabalham três pilotos: Comandante Ventolateral, Comandante Furacão e Comandante
Kamikaze, nascidos, respectivamente, em 1960, 1965 e 1970.
O Comandante Ventolateral pode voar B747s; o Comandante Furacão pode voar B747s e MD11s, e o
Comandante Kamikaze pode voar MD11s.
Três vôos estão programados diariamente: Vôo F001 com partida às 8:00 h e chegada às 10:00 h (usa
N1001); vôo F002 com partida às 9:00 h e chegada às 15:00 h (usa N2001) e o vôo F003 com partida às 14:00 h e
chegada às 17:00 h (usa N1001).
As partidas são canceladas caso não haja nenhum passageiro a transportar.
Os horários de partida e de chegada são os programados.
O Comandante Ventolateral pilotou o vôo F001 em 15/01/03 e em 20/01/03.
O Comandante Furacão comandou o vôo F002 em 20/01/03 e o vôo F003 em 25/01/03.
Cada vôo envolve um piloto e um avião.
Vôos diferentes podem envolver o mesmo piloto e o mesmo avião.
O passageiro com código de cliente C001, que vive em São Paulo, viajou no vôo F001, em 15/01/03 e no
vôo F003 em 25/01/03.
O passageiro com código de cliente C002, que mora em Fortaleza, viajou no vôo F002, em 20/01/03.
O passageiro com código de cliente C003, que vive em Belo Horizonte, viajou no vôo F003 em 25/01/03.
O horário de partida do vôo F001 em 15/01/03 foi às 8:05 h e sua chegada se deu às 10:05 h.
8) Representantes de venda – Versão 1 Um banco de dados deve conter informações relativas a representantes de vendas, áreas de vendas e
produtos.
Cada representante é responsável pelas vendas em uma ou mais áreas; cada área tem um ou mais
representantes responsáveis.
De modo semelhante, cada representante é responsável pelas vendas de um ou mais produtos, e cada
produto tem um ou mais representantes responsáveis.
Cada produto é vendido em diversas áreas, porém dois representantes nunca vendem o mesmo produto na
mesma área.
Projete um DER para este banco de dados.
9) Representantes de venda – Versão 2 Um banco de dados deve conter informações relativas a representantes de vendas, áreas de vendas e
produtos.
Cada representante é responsável pelas vendas em uma ou mais áreas; cada área tem um ou mais
representantes responsáveis.
29
De modo semelhante, cada representante é responsável pelas vendas de um ou mais produtos, e cada
produto tem um ou mais representantes responsáveis.
Finalmente, cada produto é vendido em uma ou mais áreas, e cada área tem um ou mais produtos vendidos
nela.
Ainda mais, se o representante R é responsável pela área A, e o produto P é vendido na área A, e o
representante R é responsável pelo produto P, então R vende P em A.
Projete um DER para este banco de dados.
10) Medicamentos prescritos
Médicos (identificados por seu CRM) que trabalham em uma clínica diariamente atendem pacientes
(identificados por seu CPF) e, para cada consulta, deseja-se armazenar a data e o valor da mesma. Isto é
representado como segue:
Deseja-se agora armazenar os medicamentos prescritos em uma consulta. Medicamentos são identificados
pelo seu nome. Complete o diagrama acima.
11) Museu Considere o contexto de um museu.
Cada obra no museu possui um código, um título e um ano. Obras ou são pinturas ou são esculturas. No
primeiro caso, são dados importantes o estilo (por exemplo, impressionista). No caso de esculturas, são importantes
o peso e os materiais de que é feita (por exemplo, argila, madeira, etc).
Uma obra pode estar exposta em um único salão, em uma determinada posição neste salão. Um salão, que
geralmente abriga várias obras, é identificado por um número e está em um andar do museu. Certos dados a
respeito dos autores de cada obra também são relevantes: código, nome e nacionalidade.
Uma obra é produzida por apenas um autor, porém, pode existir mais de uma obra de um mesmo autor no
museu. No museu trabalham restauradores, cada um possuindo um ID, CPF, nome e especialidade.
Um restaurador pode estar realizando a manutenção de várias obras. Uma obra, caso esteja em manutenção,
está nas mãos de apenas um restaurador. Para cada manutenção deve-se registrar a data de início e a data prevista
de término do trabalho, uma descrição do serviço a ser feito e um custo previsto para realizar a manutenção.
Uma manutenção pode estar utilizando uma ou mais matérias-primas. Uma matéria-prima possui um
código, um nome e uma quantidade em estoque. Uma matéria-prima pode estar sendo utilizada em várias
manutenções, em uma certa quantidade.
Exercícios sobre Modelo Entidade Relacionamento – Relacionamentos Ternários
1.
Considere as situações ilustradas pelas seguintes tabelas e diga se estão de acordo com o DER a seguir. Justifique
suas respostas.
Dados os gerentes:
João Nunes Silva
Paulo
Ana Cláudia
Elaine Dias
dados os projetos:
“Desenvolvimento do produto A”
“Implantação da plataforma B”
“Aperfeiçoamento do método C”
dados os engenheiros:
Leila
Karina
Luciano
Maurício
Médico Paciente Consulta
Valor Data
30
a) b) Projeto Gerente Engenheiro
“Desenvolvimento do produto A” João Pedro Leila
“Implantação da plataforma B” João Pedro Luciano
“Aperfeiçoamento do método C” Ana Cláudia Maurício
“Desenvolvimento do produto A” Ana Cláudia Leila
“Implantação da plataforma B” João Pedro Karina
“Implantação da plataforma B” João Pedro Leila
2.
Considere as situações ilustradas pelas seguintes tabelas e diga se estão de acordo com o DER a seguir. Justifique
suas respostas.
a) b) Projeto Local Funcionário Projeto Local Funcionário
“Desenvolvimento do produto A” Lab X Leila “Desenvolvimento do produto A” Lab X Leila “Implantação da plataforma B” Sala Y Luciano “Implantação da plataforma B” Sala Y Luciano “Aperfeiçoamento do método C” Lab X Maurício “Aperfeiçoamento do método C” Lab X Maurício “Desenvolvimento do produto A” Sala Y Leila “Desenvolvimento do produto A” Lab X Luciano “Implantação da plataforma B” Lab X Maurício “Aperfeiçoamento do método C” Sala Y Luciano “Implantação da plataforma B” Sala Y Maurício “Implantação da plataforma B” Sala Y Maurício
Projeto Gerente Engenheiro
“Desenvolvimento do produto A” Elaine Karina
“Implantação da plataforma B” Elaine Maurício
“Aperfeiçoamento do método C” Ana Cláudia Luciano
“Desenvolvimento do produto A” Ana Cláudia Maurício
“Implantação da plataforma B” Elaine Leila
“Implantação da plataforma B” Elaine Karina
Dados os locais de trabalho:
Laboratório X
Sala Y
Complexo Z
dados os projetos:
“Desenvolvimento do produto A”
“Implantação da plataforma B”
“Aperfeiçoamento do método C”
dados os funcionários:
Leila
Karina
Luciano
Maurício
Gerente
Engenheiro Projeto
Gerencia
1
N N
Projeto
Local Funcionário
Designado
A
1
1 N
31
3.
Considere as situações ilustradas pelas seguintes tabelas e diga se estão de acordo com o DER a seguir. Justifique
suas respostas.
a) b) Livro Curso Professor
“A Moreninha” Literatura I Marta “Crime do Padre Amaro” Literatura I Laércio
“O Guarani” Lit. Portug. Laércio “Sítio do Pica-Pau Amarelo” Literatura II Miriam
“Crime do Padre Amaro” Literatura II Marta “Sítio do Pica-Pau Amarelo” Lit. Portug. Marta
Algumas questões de Concursos Públicos
1) (FCC - 2008 - METRÔ-SP - Analista Treinee - Análise de Sistemas) A cardinalidade de um relacionamento
ternário, em um modelo de dados, refere-se à quantidade de ocorrências de
a) todas as três entidades somadas em conjunto.
b) cada entidade, independentemente uma das outras.
c) uma entidade em relação apenas com uma outra entidade.
d) uma entidade em relação às outras duas entidades.
e) de duas entidades somadas em relação à terceira entidade.
2) (CESPE - 2011 - Correios - Analista de Correios - Analista de Sistemas – Produção) Com relação a banco de
dados, julgue os itens a seguir.
Os registros armazenados em banco de dados podem conter, ou não, campos com valores nulos.
Certo Errado
3) (Prova: FUMARC - 2011 - BDMG - Analista de Sistemas)
Em relação aos conceitos do modelo Entidade- Relacionamento, observe o diagrama ER abaixo e analise as
seguintes afirmativas:
Livro Curso Professor “Crime do Padre Amaro” Literatura I Laércio
“A Moreninha” Literatura II Miriam “O Guarani” Lit. Portug. Miriam
“Sítio do Pica-Pau Amarelo” Literatura I Marta “Crime do Padre Amaro” Literatura II Laércio
“Sítio do Pica-Pau Amarelo” Lit. Portug. Laércio
Dados os professores:
Laércio
Marta
Miriam
dados os cursos:
Literatura I
Literatura II
Literatura Portuguesa
dados os livros:
“A Moreninha”
“O Guarani”
“Sítio do Pica-Pau Amarelo”
“Crime do Padre Amaro”
Professor
Livro Curso
Usa
1
1 1
32
I. O atributo Num_agencia do tipo de entidade AGENCIA é conhecido como chave primária.
II. De acordo com as restrições de participação definidas, uma entidade de BANCO obrigatoriamente deve estar
relacionada a pelo menos uma entidade de AGENCIA.
III. AGENCIA é um tipo de entidade fraca e POSSUI é um relacionamento identificador, cuja razão de
cardinalidade é 1:N.
Marque a alternativa CORRETA:
a) apenas as afirmativas I e II são verdadeiras.
b) apenas as afirmativas I e III são verdadeiras.
c) apenas as afirmativas II e III são verdadeiras.
d) todas as afirmativas são verdadeiras.
4) (Prova: FGV - 2009 - MEC - Analista de Sistemas – Especialista) O Modelo de dados é representado através de
um modelo entidade-relacionamento - modelo ER, enquanto que o – diagrama entidade-relacionamento (DER)
constitui a representação gráfica do modelo ER. No que diz respeito à simbologia utilizada, para
globalização/especialização e entidade associativa são empregados, respectivamente, os seguintes símbolos:
a) e
b) e
c) e
d) e
e) e
5) (FUNIVERSA - 2010 - MPE-GO - Técnico de Informática) Quando se constrói um banco de dados, define-se o
modelo de entidade e relacionamento (MER), que é a representação abstrata das estruturas de dados do banco e
seus relacionamentos. Cada entidade pode se relacionar com uma ou mais entidades diferentes, resultando em
mapeamentos, por exemplo: 1:1, 1:N, N:1 ou N:M. Esses mapeamentos, com base no número de entidades às quais
outra entidade pode ser associada, denominam-se
a) cardinalidade.
b) hierarquia.
33
c) relacionamento.
d) diagrama.
e) agregação.
6) (VUNESP - 2009 - CETESB - Analista de TI - Administração de Dados)
Considere o seguinte trecho de um diagrama entidade-relacionamento, relativo a um banco de dados relacional.
No relacionamento R, a participação de E2 é chamada de
a) fraca.
b) total.
c) parcial.
d) adaptada.
e) derivada.
7) (VUNESP - 2009 - CETESB - Analista de Tecnologia da Informação - Banco de Dados)
Considere o seguinte trecho de um diagrama entidade-relacionamento relativo a um banco de dados relacional:
E1 e E2 representam, respectivamente, conjuntos de entidades
a) constantes e variáveis. b) fortes e fracas. c) macros e micros.
d) paralelas e ortogonais. e) temporárias e permanentes.
34
Parte 3 - Modelo de Dados Relacional
- O modelo de dados relacional foi introduzido por E. F. Codd, da IBM, em 1970, através de um artigo
publicado com o título “A Relational Model of Data for Large Shared Data Bases” (Communications of the
ACM, 13, no 6, 1970, 377-387).
- A fundamentação matemática para o modelo proposto é a teoria dos conjuntos.
Conceitos do Modelo Relacional
- O modelo de dados relacional representa os dados como uma coleção de relações.
- Uma tabela bidimensional é a estrutura de dados mais adequada para se representar o conceito abstrato de
relação.
- Quando uma relação é vista como uma tabela de valores, cada linha representa uma coleção de valores
relacionados que podem ser interpretados como um fato que descreve uma instância de entidade ou uma
instância de relacionamento.
Exemplo:
Estudante: Nome Código Telefone Endereço TelComercial CPF
Maria 1782 233-5678 R. X, 123 null 125811728-27
João 2345 432-8900 Av. H., 45 345-2356 225832729-57
José 5667 345-7867 Al. R, 56 null 322311728-29
Alfredo 2156 3456-9087 R. D, 67 321-7890 765817928-45
- O nome da tabela e os nomes das colunas são usados para ajudar a interpretar o significado dos valores em
cada linha da tabela.
- Assim, no exemplo anterior a tabela se chama estudante porque cada linha representa uma particular
entidade estudante. Os nomes das colunas: Nome, Código, Telefone, Endereço, TelComercial, CPF
especificam como devem ser interpretados os valores de cada linha, conforme a coluna em que se
encontram.
- Na terminologia do modelo relacional:
o Cada linha é chamada de tupla.
o Cada coluna é chamada de atributo.
o A tabela é chamada de relação.
- O número de atributos de uma relação é chamado grau da relação.
- Assim, uma relação de grau 1 é dita unária, uma de grau 2 é dita binária, de grau 3, ternária, de grau n n-
ária.
- O tipo de dado que especifica o tipo dos valores que podem ocorrer para um atributo da relação é chamado
de domínio.
35
Exemplo:
Conjunto de
todos os
possíveis
nomes de pessoas
Conjunto
de todos
os
possíveis
códigos
de aluno
Conjunto
de todos os
possíveis
números de
telefone
Conjunto
de todos os
possíveis
endereços
Conjunto
de todos os
possíveis
números de
telefone
Conjunto de todos
os possíveis
números de CPF
Domínios
Nome Código Telefone Endereço TelComercial CPF
Maria 1782 233-5678 R. X, 123 null 125811728-27
João Nunes Silva 2345 432-8900 Av. H., 45 345-2356 225832729-57
José 5667 345-7867 Al. R, 56 null 322311728-29
Alfredo 2156 3456-9087 R. D, 67 321-7890 765817928-45
Atributos
Grau = 6
- Uma relação-esquema é denotada por R(A1, A2, A3, ..., An) aonde A1, A2, A3, ..., An são os nomes dos
atributos e R o nome da relação. Uma relação esquema é utilizada para descrever uma relação.
Exemplo:
Estudante(Nome, Código, Endereço, Telefone, TelComercial, CPF)
Atributos Chave de uma Relação
- Uma relação é definida como um conjunto de tuplas.
- Por definição, todos os elementos de um conjunto são distintos. Assim, todas as tuplas de uma relação
também são distintas.
- Isto significa que nenhuma tupla pode ter a mesma combinação de valores para todos os seus atributos.
- Uma super-chave de uma relação é um subconjunto de atributos de uma relação com a propriedade de
que não existam duas tuplas que tenham a mesma combinação de valores para esses atributos.
- Toda relação tem ao menos uma super-chave que é o conjunto de todos os seus atributos.
- Uma chave de uma relação R é uma super-chave de R com a propriedade adicional de não se poder
remover qualquer atributo da mesma, sem que ela deixe de ser super-chave de R.
- Assim, uma chave é uma super-chave mínima.
Exemplo: - Considerando a relação-esquema:
Estudante(Nome, Código, Endereço, Telefone, TelComercial, CPF)
- O conjunto de atributos {CPF} é uma super-chave de Estudante, porque sabe-se que nenhum estudante irá
ter o mesmo valor para CPF de outro estudante.
- O conjunto de atributos {CPF} é também uma chave, pois não se pode remover nenhum atributo do
conjunto.
Tuplas
36
- Qualquer conjunto de atributos que inclua CPF, como por exemplo {Nome, Endereço, CPF} também será
uma super-chave.
- No entanto, {Nome, Endereço, CPF} não é uma chave porque removendo Nome ou Endereço, o conjunto
resultante ainda será uma super-chave.
- O valor de um atributo-chave pode ser usado para identificar unicamente uma tupla em uma relação. Assim,
o valor 225832729-57 identifica unicamente a tupla correspondente à ‘João Nunes Silva’ na relação
Estudante.
- Por outro lado, o atributo Nome na relação Estudante não pode ser tomado como uma chave, pois nada
garante que não existam homônimos.
- Em geral, uma relação-esquema pode ter mais de uma chave. Nestes casos, cada chave é chamada de
chave-candidata. Por exemplo, na relação-esquema Estudante tanto CPF como Código (número de
identificação do Estudante na Escola) seriam duas chaves-candidatas.
- Qualquer uma das chaves-candidatas pode ser escolhida como chave-primária da relação.
- Embora a escolha da chave-primária seja arbitrária, é sempre melhor escolher uma chave-primária com o
menor número de atributos.
Propriedades de Tabelas 1. Não existem tuplas duplicadas:
- Um corolário importante deste fato é que há sempre uma chave-primária: como tuplas são únicas, pelo
menos a combinação de todos os atributos da relação tem a propriedade de unicidade.
2. Não há ordem entre os atributos: - O rearranjo de colunas não tem nenhum efeito sobre a significação dos dados em uma tabela relacional.
Nome Código Telefone Endereço TelComercial CPF
Maria 1782 233-5678 R. X, 123 null 125811728-27
João 2345 432-8900 Av. H., 45 345-2356 225832729-57
José 5667 345-7867 Al. R, 56 null 322311728-29
Alfredo 2156 3456-9087 R. D, 67 321-7890 765817928-45
é idêntica a :
Código Nome CPF TelComercial Telefone Endereço
1782 Maria 125811728-27 Null 233-5678 R. X, 123
2345 João 225832729-57 345-2356 432-8900 Av. H., 45
5667 José 322311728-29 Null 345-7867 Al. R, 56
2156 Alfredo 765817928-45 321-7890 3456-9087 R. D, 67
3. Não há ordem entre tuplas: - Um rearranjo da posição das tuplas em uma tabela relacional não altera a significação dos dados.
- Trata-se de uma conseqüência do fato de que uma relação é um conjunto matemático e conjuntos não são
ordenados.
Nome Código Telefone Endereço TelComercial CPF
Maria 1782 233-5678 R. X, 123 null 125811728-27
João 2345 432-8900 Av. H., 45 345-2356 225832729-57
José 5667 345-7867 Al. R, 56 null 322311728-29
Alfredo 2156 3456-9087 R. D, 67 321-7890 765817928-45
37
é idêntica a:
Nome Código Telefone Endereço TelComercial CPF
João 2345 432-8900 Av. H., 45 345-2356 225832729-57
Maria 1782 233-5678 R. X, 123 null 125811728-27
Alfredo 2156 3456-9087 R. D, 67 321-7890 765817928-45
José 5667 345-7867 Al. R, 56 null 322311728-29
4. Todos os valores dos atributos são atômicos: - Cada célula (posição linha-coluna) de uma tabela relacional pode conter apenas um valor de dado.
Nome Código Telefone Endereço TelComercial CPF
Maria 1782 233-5678 R. X, 123 null 125811728-27
342-7634
467-9056
João 2345 432-8900 Av. H., 45 345-2356 225832729-57
José 5667 345-7867 Al. R, 56 null 322311728-29
Alfredo 2156 3456-9087 R. D, 67 321-7890 765817928-45
- Implica em dizer que todas as relações no modelo relacional têm que estar na sua primeira foma normal.
Mapeamento do Modelo Entidade-Relacionamento para o Modelo de Dados Relacional
- O passo seguinte à modelagem dos dados usando o Modelo Entidade-Relacionamento é o mapeamento do
Diagrama Entidade-Relacionamento para um modelo de dados de implementação. No caso, estaremos
tratando do Modelo de Dados Relacional.
- As recomendações de mapeamento baseiam-se na análise da cardinalidade dos relacionamentos. Com base
nesta análise, algumas alternativas de mapeamento podem ser adotadas:
1. Entidades relacionadas podem ser fundidas em uma única tabela.
2. Tabelas podem ser criadas para o relacionamento.
3. Chaves estrangeiras podem ser criadas em tabelas a fim de representar adequadamente o relacionamento.
- Vamos descrever todos os passos necessários para mapear o Diagrama Entidade-Relacionamento de
Companhia para o Modelo de Dados Relacional.
PASSO 1: - Identificar no DER todas as entidades regulares (não fracas e não subtipos).
- Para cada entidade regular E criar uma relação-esquema R que inclua todos os atributos simples de E e
decidir aqui se serão armazenados ou não, separadamente, os componentes simples dos atributos
compostos. Não considerar aqui os atributos derivados, nem os atributos multivalorados, que serão tratados
em um passo subsequente.
- Escolher um dos atributos chave de E como sendo a chave-primária de R.
- As chaves-primárias das relações devem aparecer grifadas.
- No banco de dados Companhia identificamos as entidades regulares: Funcionário,
Departamento e Projeto.
- As relações-esquema correspondentes seriam:
Funcionario(CPF, NSS, Nome, DataNasc, Endereço, Sexo, Salario)
Departamento(DNome, DNumero, DLocalizacao)
Projeto(PNome, PNumero, PLocalizacao)
38
- Foram escolhidas as chaves-primárias CPF para Funcionário, DNumero para Departamento e PNumero
para Projeto.
- Os atributos decorrentes de relacionamentos serão adicionados em passos subsequentes.
PASSO 2: - Identificar no DER todas as entidades fracas.
- Para cada entidade fraca do DER criar uma relação-esquema R e incluir nela todos os atributos simples e
os componentes simples dos atributos compostos (tomar decisão se vai armazenar separadamente os
componentes) da entidade. Não considerar aqui os atributos derivados, nem os atributos multivalorados,
que serão tratados em um passo subsequente.
- Incluir na relação R a chave-primária da relação que corresponde à entidade proprietária da
identificação.
- A chave-primária de R fica sendo formada pela combinação da chave-primária da entidade proprietária da
identificação com a chave parcial da entidade fraca. Trata-se assim de uma chave-primária composta.
- No banco de dados Companhia identificamos a entidade fraca Dependente. A
relação-esquema correspondente fica:
Dependente(CPFFunc, NomeDependente, Sexo, DataNasc, Relacao)
- CPFFunc é chamado, na relação Dependente, de chave-estrangeira, visto que tem
o mesmo domínio da chave-primária CPF da relação Funcionário.
Atenção: Neste segundo passo, surge pela primeira vez, o conceito de chave-estrangeira.
Quando se inclui na relação R um atributo que referencia a chave-primária da relação que corresponde à
entidade proprietária da identificação, tem-se aí uma chave estrangeira.
Uma chave estrangeira é uma coluna ou uma combinação de colunas, cujos valores aparecem necessariamente
na chave primária de uma outra (ou da mesma) tabela.
A chave estrangeira é o mecanismo que permite a implementação de relacionamentos em um banco de dados
relacional.
A presença de uma chave estrangeira sempre denuncia a existência de um relacionamento entre a tabela onde
ela é inserida e a tabela cuja chave-primária ela referencia.
A integridade referencial é um conceito intimamente associado ao de chave-estrangeira. Trata-se de uma
restrição que define que os valores dos campos que aparecem em uma chave estrangeira devem constar
necessariamente na coluna que é a chave primária da tabela referenciada.
PASSO 3: - Identificar no DER todos os relacionamentos binários 1:1.
- Para cada relacionamento binário 1:1 do DER, escolher uma das relações que participam do
relacionamento (aquela que possua participação total no relacionamento) e incluir nela como chave-
estrangeira, a chave primária da outra relação participante.
- Incluir nesta relação, além disso, todos os atributos simples e os componentes simples de atributos
compostos que estão associados ao relacionamento.
- No banco de dados Companhia identificamos o relacionamento 1:1 Gerencia entre
as entidades Funcionario e Departamento.
- Como a participação de Departamento no relacionamento é total (pois todo
departamento tem um gerente), inclui-se na relação-esquema Departamento como
39
chave-estrangeira, a chave-primária da relação-esquema Funcionario. Na relação
Departamento este atributo recebe o nome de CPFGer.
- Além disso, inclui-se também na relação Departamento o atributo simples
DataInicio, renomeando-o para DataInicGer.
Departamento(DNome, DNumero, DLocalizacao, CPFGer, DataInicGer)
- Uma outra alternativa para o mapeamento de um relacionamento 1:1 seria unir as duas entidades e o
relacionamento em uma única relação. Isto é particularmente apropriado quando ambas as entidades
possuem participação total e quando as entidades não participam em quaisquer outros relacionamentos.
Vejamos exemplo a seguir:
Conferencia(Sigla, Nome, DataIniOrg, CodComissao, EMailComissao)
- Por outro lado, quando ambas as entidades possuem participação parcial e participam em outros
relacionamentos, deve-se criar uma relação à parte para representar o relacionamento. Nesta relação-
esquema devem ser inclusas as chaves primárias de cada uma das relações que representam as entidades
participantes, bem como quaisquer atributos simples e componentes simples dos atributos compostos
associados ao relacionamento. A chave primária desta nova relação poderá ser a chave de qualquer uma
das relações que representam as entidades participantes. Vejamos um exemplo:
Homem(CPF, Nome)
Mulher(CPF, Nome)
Casamento(CPFH, CPFM, Data)
PASSO 4: - Identificar no DER todos os relacionamentos binários 1:N.
- Para cada relacionamento 1:N, identificar a entidade que participa do lado N no relacionamento.
- Se a participação desta entidade no relacionamento for total, incluir na relação-esquema que representa
esta entidade, como chave-estrangeira, a chave-primária da entidade que participa do lado 1 no
relacionamento.
- Incluir também nesta relação quaisquer atributos simples e os componentes simples de atributos compostos
que estejam associados ao relacionamento.
- No banco de dados Companhia, temos o relacionamento Trabalha-Para entre
Funcionário e Departamento.
- Neste relacionamento, cada funcionário está relacionado a apenas um
Departamento e a participação de Funcionario no relacionamento é total. Sendo
assim, incluímos na relação-esquema Funcionario como chave-estrangeira, a
chave-primária de Departamento, com o nome NDepto.
Conferência ComissaoOrganizadora Organização 1 1
Sigla Nome Codigo E-Mail DataInic
Homem Mulher Casamento 1 1
CPF Nome CPF Nome Data
40
- O relacionamento Trabalha-Para não possui nenhum atributo a si associado.
Funcionario(CPF, NSS, Nome, DataNasc, Endereco, Sexo, Salario, NDpto)
- No banco de dados Companhia, temos ainda o relacionamento Supervisiona do qual
participa a entidade Funcionário ora no papel de supervisor, ora no papel de
supervisionado.
- Inclui-se, então, na relação-esquema Funcionario como chave estrangeira, a
chave-primária de Funcionario, com o nome de CPFSuper.
Funcionario(CPF, NSS, Nome, DataNasc, Endereco, Sexo, Salario, NDpto, CPFSuper)
- Temos ainda o relacionamento Controla entre Departamento e Projeto. Neste
relacionamento, cada Projeto está relacionado a apenas um Departamento. Desta
forma, incluímos na relação-esquema Projeto como chave-estrangeira, a chave-
primária de Departamento, com o nome de NDpto.
- O relacionamento Controla não possui nenhum atributo a si associado.
Projeto(PNome, PNumero, PLocalizacao, NDpto)
- Por outro lado, se a entidade que participa do lado N do relacionamento tiver participação parcial, então o
relacionamento deve ser representado por uma relação-esquema à parte, contendo as chaves primárias das
relações que representam ambas as entidades participantes, bem como quaisquer atributos simples e os
componentes simples de atributos compostos que estejam associados ao relacionamento. A chave primária desta
nova relação será a chave primária da relação que representa a entidade que participa do lado N do
relacionamento. Vejamos exemplo a seguir:
Cliente(CPF, Nome)
Automovel(Chassi, Modelo, Ano)
Posse(Chassi,CPF, DataCompra)
PASSO 5: - Identificar no DER todos os relacionamentos binários N:N.
- Para cada relacionamento binário N:N, criar uma relação-esquema para representar o relacionamento.
- Incluir como chaves estrangeiras nesta relação as chaves primárias das duas entidades participantes do
relacionamento. A combinação destas formará a chave-primária da relação que representa o
relacionamento.
- Incluir na relação também quaisquer atributos simples e os componentes simples dos atributos
compostos que estejam associados ao relacionamento.
- No banco de dados Companhia identificamos o relacionamento Trabalha-Em entre
Funcionario e Projeto.
- Criamos então, a relação TrabalhaEm, aonde são incluídas a chave primária de
Funcionario, com o nome CPFFunc e a chave primária de Projeto com o nome
ProjNum.
- Também incluímos o atributo Horas, pertencente ao relacionamento TrabalhaEm.
TrabalhaEm(CPFFunc, ProjNum, Horas)
Automovel Cliente Posse N 1
Chassi Modelo CPF Nome DataCompra
Ano
41
- Note-se que a chave-primária da relação TrabalhaEm é a combinação das chaves-
estrangeiras CPFFunc e ProjNum.
PASSO 6:
- Identificar no DER os atributos multivalorados com as respectivas entidades (ou relacionamentos) a que
pertencem.
- Para cada atributo multivalorado, criar uma nova relação-esquema, a qual deve incluir a chave primária K
da relação que representa a entidade (ou relacionamento) que tem A como atributo. Incluir também o
próprio atributo, se simples, ou os componentes simples do atributo, se composto.
- A chave primária desta relação será a combinação de K e A.
- Suponhamos que, no banco de dados Companhia, Funcionario possua o atributo
Telefone, considerado como multivalorado.
- Criamos então, a relação-esquema Telefone, aonde são incluídas a chave
primária de Funcionario, e o atributo Fone.
Telefone(CPF, Fone)
PASSO 7:
- Identificar no DER as entidades que sejam subtipos.
- Neste caso, há três alternativas:
1. Criar apenas uma relação-esquema para o supertipo, com a sua chave-primária e todos os atributos
que são comuns a todos os subtipos, bem como todos os atributos específicos de cada um dos subtipos.
2. Criar uma relação-esquema para o supertipo, com a sua chave-primária e todos os atributos que são
comuns a todos os subtipos.
Criar uma relação-esquema para cada um dos subtipos, contendo a mesma chave primária do
supertipo, juntamente com quaisquer atributos específicos de cada um dos subtipos.
3. Criar relações-esquema separadas para cada um dos subtipos, todas com a mesma chave primária e
contendo os atributos que lhe são específicos, bem como os atributos que são comuns a todos.
- No banco de dados Companhia não há nenhum exemplo de ocorrência de subtipos.
Assim, para ilustrar, consideremos o caso a seguir:
- Na alternativa 1, as entidades seriam mapeadas para:
Servidor(CPF, Nome, Tipo, Funcao, Titulacao, Categoria)
- Na alternativa 2, as entidades seriam mapeadas para:
Servidor(CPF, Nome)
Funcionario(CPF, Funcao), CPF FK referência Servidor
Professor(CPF, Titulacao, Categoria) , CPF FK referência Servidor
Servidor
Funcionário Professor
CPF
Nome
Função
Titulação
Categoria
42
- Na alternativa 3, as entidades seriam mapeadas para:
Funcionario(CPF, Nome, Funcao)
Professor(CPF, Nome, Titulacao, Categoria)
Deve-se notar que a alternativa 3 não se aplica a especializações parciais.
PASSO 8:
- Identificar no DER todos os relacionamentos ternários.
- Para cada relacionamento ternário, criar uma nova relação para representá-lo.
- Incluir como chaves-estrangeiras nesta relação, as chaves-primárias das relações que representam as
entidades participantes.
- Incluir, também, quaisquer atributos simples e componentes simples de atributos compostos que
pertençam ao relacionamento.
- A chave-primária da relação depende da razão de cardinalidade do relacionamento:
Se N:N:N - A chave-primária consiste na combinação das chaves-primárias das relações que
representam as três entidades participantes.
Se 1:1:1 – A chave-primária pode ser tomada dentre qualquer combinação de duas das três
chaves-primárias das relações que representam as entidades participantes.
Se 1:N:N – A chave-primária será dada pela combinação das chaves-primárias das relações que
representam as entidades que participam dos dois lados “muitos”.
Se 1:1:N – A chave-primária pode ser tomada como sendo a combinação da chave da relação que
representa a entidade que participa do lado “muitos” com qualquer uma das chaves das relações
que representam as entidades que participam do lado 1.
- As situações descritas acima estão ilustradas a seguir:
Relacionamento N:N:N
E1
E2 E3
R
N
N N
CP1
CP2 CP3
Atrib
Este relacionamento deve ser mapeado
para a relação R, onde a chave-primária
é a combinação das 3 chaves
estrangeiras CP1, CP2 e CP3:
E1(CP1, ...)
E2(CP2, ...)
E3(CP3, ...)
R(CP1, CP2, CP3, Atrib)
43
Relacionamento 1:N:N
Relacionamento 1:1:N
Relacionamento 1:1:1
E1
E2 E3
R
1
N N
CP1
CP2 CP3
Atrib
Este relacionamento deve ser mapeado
para a relação R, onde a chave-primária
é a combinação das 2 chaves
estrangeiras CP2 e CP3:
E1(CP1, ...)
E2(CP2, ...)
E3(CP3, ...)
R(CP1, CP2, CP3, Atrib)
E1
E2 E3
R
N
1 1
CP1
CP2 CP3
Atrib
Este relacionamento deve ser mapeado
para a relação R, onde a chave-primária
pode ser formada pela combinação das
chaves estrangeiras CP1 e CP2 ou CP1
e CP3:
E1(CP1, ...)
E2(CP2, ...)
E3(CP3, ...)
R(CP1, CP2, CP3, Atrib) ou
R(CP1, CP2, CP3, Atrib)
E1
E2 E3
R
1
1 1
CP1
CP2 CP3
Atrib
Este relacionamento deve ser mapeado
para a relação R, onde a chave-primária
pode ser formada pela combinação das
chaves estrangeiras CP1 e CP2 ou CP1
e CP3 ou CP2 e CP3:
E1(CP1, ...)
E2(CP2, ...)
E3(CP3, ...)
R(CP1, CP2, CP3, Atrib) ou
R(CP1, CP2, CP3, Atrib) ou
R(CP1, CP2, CP3, Atrib)
44
PASSO 9:
- Identificar no DER todos os entidades associativas.
- Fazer o mapeamento do relacionamento entre a entidade associativa e uma outra entidade qualquer,
seguindo as mesmas recomendações já vistas anteriormente para os relacionamentos em função de sua
cardinalidade.
No banco de dados Companhia não há nenhum exemplo de ocorrência de entidades
associativas. Assim, para ilustrar, consideremos o caso a seguir em que há
sempre um tutor acompanhando o desempenho de cada aluno em cada disciplina que
ele cursa.
Aluno(RA, Nome)
Disciplina(Codigo, NomeDisc)
Tutor(Codigo, NomeTutor)
Matricula(RA, CodDisc, Media, Freq, CodTutor), CodTutor FK referência
Tutor(Codigo)
Relações-Esquema da Base de Dados Companhia
Funcionario(CPF, NSS, Nome, DataNasc, Endereco, Sexo, Salario, NDpto, CPFSuper)
Departamento(DNome, DNumero, DLocalizacao, CPFGer, DataInicGer)
Projeto(PNome, PNumero, PLocalizacao, NDpto)
Dependente(CPFFunc, NomeDependente, Sexo, DataNasc, Relacao)
TrabalhaEm(CPFFunc, ProjNum, Horas)
- Note-se que o atributo DNumero em Departamento e NDpto em Funcionario referem-se ao mesmo conceito
do mundo real, que é o número dado a um departamento.
- Isto significa que é possível dar nomes distintos a atributos que representam um mesmo conceito do mundo
real.
N N
N
1
Aluno
Tutor
Disciplina
Tutoria
Matriculado Matriculado RA
Nome Nome
Codigo
Codigo
Nome
Media Freq
45
- Da mesma forma, é possível que atributos que representam conceitos diferentes tenham o mesmo nome,
desde que pertençam a relações diferentes. Por exemplo, poderíamos ter usado Nome, ao invés de PNome e
DNome nas relações-esquema Projeto e Departamento, respectivamente.
Exercícios sobre Modelo Relacional
1. Fazer o mapeamento para o Modelo Relacional dos DER desenvolvidos no grupo de exercícios intitulado
Exercícios sobre Modelo Entidade Relacionamento – Parte 2.
2. Faça o mapeamento do DER abaixo para o modelo relacional.
Algumas Questões de Concursos Públicos
1) (FUNIVERSA - 2010 - MPE-GO - Técnico de Informática) Um banco de dados relacional é baseado em tabelas
nas quais é possível armazenar, manipular e recuperar dados. Para se recuperarem informações de uma linha de
uma tabela, bem como para inter-relacionar informações entre tabelas diferentes em um banco de dados relacional,
utilizam-se as chaves. Assinale a alternativa que apresenta o nome de três tipos de chaves utilizadas em um banco
de dados relacional.
a) primária, secundária e terciária.
b) pública, privada e simétrica.
c) simétrica, assimétrica e distribuída.
d) primária, alternativa e estrangeira.
e) global, local e universal.
Uma possível convenção para dar nomes a tabelas e campos em bancos de dados:
Tabela => TB_<NOME_TABELA>
Chaves Primarias => <NOME_ABREVIADO>_PK
Chaves Unicas => <NOME_ABREVIADO>_UK
Chaves Estrangeiras => <NOME_CAMPO_TABELA_PAI>_FK
NomeArtistico
Música Artista
CD
Criação
Compõe
Gravação
(1,n) (0,n)
(0,n) (0,n)
(1,n)
(0,n)
Titulo
Codigo
Duração
DataNasc
Faixa
Data
Titulo
46
2) (CESGRANRIO - 2011 - TRANSPETRO - Analista de Sistemas Júnior) Considere o diagrama entidade-
relacionamento a seguir, que expressa um modelo conceitual de dados. Nesse modelo, pode-se ler que projetos
produzem peças, que pessoas trabalham em projetos e que pessoas coordenam outras pessoas. As participações e
cardinalidades correspondentes a cada relacionamento estão indicadas no próprio diagrama.
Suponha que, durante o mapeamento do modelo conceitual acima para o modelo relacional, o projetista de banco
de dados responsável pela tarefa decidiu mapear cada entidade para uma relação distinta. Ele também decidiu criar
o mínimo necessário de relações nesse mapeamento. Sendo assim, as quantidades de chaves primárias e de chaves
estrangeiras criadas nesse mapeamento são, respectivamente,
a) 3 e 4
b) 5 e 5
c) 4 e 4
d) 3 e 5
e) 5 e 4
3) (FCC - 2010 - TRE-AM - Analista Judiciário - Tecnologia da Informação) Na modelagem de dados, o conjunto
de valores possíveis de um atributo de dados é chamado de
a) cardinalidade.
b) tupla.
c) domínio.
d) entidade.
e) instância.
4) (FUMARC - 2011 - BDMG - Analista de Sistemas) De acordo com o diagrama do esquema do banco de dados
relacional abaixo, responda a questão.
De acordo com o diagrama do esquema de banco de dados relacional, analise as seguintes afirmativas.
I. Na relação DEPOSITANTE, o atributo id_cliente é chave estrangeira e faz parte da chave primária juntamente
com o atributo num_conta.
II. Na relação EMPRESTIMO, o atributo num_conta é chave primária.
III. Na relação AGENCIA, o atributo id_agencia é chave primária.
Marque a alternativa CORRETA:
47
a) apenas as afirmativas I e II são verdadeiras.
b) apenas as afirmativas I e III são verdadeiras.
c) apenas as afirmativas II e III são verdadeiras.
d) todas as afirmativas são verdadeiras.
5) (FCC - 2011 - NOSSA CAIXA DESENVOLVIMENTO - Analista de Sistemas)
Cada analista trabalhando em um projeto tem um e somente um gerente, mas cada gerente de um projeto pode
gerenciar muitos analistas e cada gerente de um analista pode gerenciar esse analista em mais de um projeto.
A passagem do modelo de dados conceitual para o modelo lógico relacional normalizado derivará em
a) duas tabelas representando: Gerente-Gerencia e Analista-Projeto.
b) duas tabelas representando: Gerente-Analista e Analista-Projeto.
c) três tabelas representando: Gerente-Analista, Gerente-Gerencia e Analista-Projeto.
d) três tabelas representando: Gerente-Projeto, Analista-Projeto e Gerente-Analista.
e) quatro tabelas representando: Gerente, Gerencia, Analista e Projeto.
6) (FCC - 2009 - TJ-PA - Analista Judiciário - Análise de Sistemas – Desenvolvimento) Considere o
relacionamento Funcionário é gerente de Projeto (N:M). Sem a necessidade de manutenção de dados históricos, a
condição de que projetos iniciam em datas diferentes e que gerentes nem sempre começam a gerenciar o projeto a
partir de sua data de início, a indicação da data a partir da qual funcionários assumem a função de gerente deverá
ser
a) um atributo não chave de Funcionário.
b) um atributo não chave de Projeto.
c) a chave primária de Funcionário.
d) a chave primária de Projeto e uma estrangeira do relacionamento.
e) um atributo do relacionamento.
48
Parte 4 - Normalização
- Motivação: Ainda há muitos sistemas que funcionam à moda antiga, isto é, são sistemas de processamento de
arquivos. Foram desenvolvidos ao longo dos últimos 40 anos e não utilizam bancos de dados relacionais. São
conhecidos como sistemas legados. Podemos nos deparar com o desafio de ter que adaptá-los à abordagem de
banco de dados relacionais.
- Assim, a motivação do estudo dos processos de normalização é a engenharia reversa de arquivos, ou seja, obter
um modelo lógico relacional a partir de um banco de dados não relacional.
- A normalização é a base teórica para este processo. Trata-se de uma técnica que objetiva eliminar redundância
de dados de arquivos.
- A redundância de dados é responsável por várias anomalias, as chamadas anomalias de inclusão, de exclusão e de
deleção. A normalização dos dados elimina tais anomalias.
Documento Exemplo:
Relatório de Alocação de Projeto Código do Projeto: LSC001
Tipo: Novo Desenvolvimento Descrição: Sistema de Estoque
Código do Empregado Nome Categoria Funcional Salário Data de Início do Projeto
Tempo Alocado ao Projeto
2146 João A1 4 01-11-1991 24 3145 Sílvio A2 6 02-10-1991 24 6126 José B1 9 03-10-1992 18 1214 Carlos A2 6 01-11-1991 18 8191 Mário A1 4 01-11-1992 12
Código do Projeto: PAG02
Tipo: Manutenção Descrição: Sistema de RH
Código do Empregado Nome Categoria Funcional Salário Data de Início do Projeto
Tempo Alocado ao Projeto
8191 Mário A1 4 01-05-1993 12 4112 João A2 6 04-01-1991 24 6126 José B1 9 01-11-1992 12
Representação na forma de tabela não normalizada:
CodProj Tipo Descr Emp
CodEmp Nome Cat Sal DataIni TempAl
LSC001 Novo Desenv.
Sistema de Estoque
2146 João A1 4 01-11-1991 24
3145 Sílvio A2 6 02-10-1991 24
6126 José B1 9 03-10-1992 18
1214 Carlos A2 6 01-11-1991 18
8191 Mário A1 4 01-11-1992 12
PAG02 Manutenção Sistema de RH
8191 Mário A1 4 01-05-1993 12
4112 João A2 6 04-01-1991 24
6126 José B1 9 01-11-1992 12
Esquema não normalizado: Proj(CodProj, Tipo, Descr,
(CodEmp, Nome, Cat, Sal, DataIni, TempAl) )
49
Primeira Forma Normal
- Transformação de um esquema NN em um esquema na 1FN:
Constrói-se uma tabela para cada tabela aninhada (Decomposição em tabelas).
Cria-se uma tabela referente a própria tabela que está sendo normalizada e uma tabela para cada tabela
aninhada.
No exemplo “Alocação de Projeto” o esquema resultante seria o seguinte:
Proj(CodProj, Tipo, Descr)
ProjEmp(CodProj, CodEmp, Nome, Cat, Sal, DataIni, TempAl)
Na decomposição de tabelas, a passagem à 1FN segue os seguintes passos:
i. É criada uma tabela na 1FN referente à tabela NN que contenha apenas as colunas que não pertençam à
tabelas aninhadas.
A chave primária desta tabela é idêntica à da tabela não normalizada.
ii. Para cada tabela aninhada, é criada uma tabela na 1FN composta pelas seguintes colunas:
Chave primária de cada uma das tabelas na qual a tabela em questão está aninhada.
As colunas da própria tabela aninhada.
iii. São definidas as chaves primárias das tabelas na 1FN correspondentes às tabelas aninhadas.
Para melhor entender como se definem as chaves, vemos a seguir o conteúdo das tabelas na 1FN do
exemplo “Alocação de Projeto”:
Proj:
CodProj Tipo Descr
LSC001 Novo Desenv. Sistema de Estoque
PAG02 Manutenção Sistema de RH
ProjEmp: CodProj CodEmp Nome Cat Sal DataIni TempAl
LSC001 2146 João A1 4 01-11-1991 24
LSC001 3145 Sílvio A2 6 02-10-1991 24 LSC001 6126 José B1 9 03-10-1992 18
LSC001 1214 Carlos A2 6 01-11-1991 18 LSC001 8191 Mário A1 4 01-11-1992 12
PAG02 8191 Mário A1 4 01-05-1993 12 PAG02 4112 João A2 6 04-01-1991 24
PAG02 6126 José B1 9 01-11-1992 12
- Note-se que a chave primária da tabela ProjEmp precisa ser composta pelas colunas CodProj e CodEmp, porque o
mesmo empregado pode trabalhar em múltiplos projetos. Assim, é preciso utilizar o código do projeto para se fazer
distinção entre as múltiplas linhas em que consta o mesmo empregado.
Agora, com as chaves primárias definidas, temos:
Proj(CodProj, Tipo, Descr)
ProjEmp(CodProj, CodEmp, Nome, Cat, Sal, DataIni, TempAl)
Primeira Forma Normal – 1FN Uma tabela está na primeira forma normal quando ela não contém
tabelas aninhadas
50
Atenção: A chave primária de uma tabela na 1FN nem sempre é a concatenação das chaves primárias das colunas
chaves primárias na forma NN.
Exemplo:
A tabela não normalizada cujo esquema é exibido a seguir:
Arq-Candidatos(Cod_Curso, Nome_Curso, Numero_Vagas_Curso,
(Cod_Cand, Nome_Cand, Nota_Cand))
representa um arquivo que armazena informações sobre um concurso vestibular. O arquivo contém um registro
para cada curso, com código, nome e número de vagas do curso. Além disso, para cada curso há uma lista dos
candidatos aprovados no mesmo. Supõe-se que cada candidato tenha sido aprovado em um curso somente.
A passagem à 1FN gera as tabelas abaixo:
Cursos(Cod_Curso, Nome_Curso, Numero_Vagas_Curso)
Candidatos(Cod_Curso, Cod_Cand, Nome_Cand, Nota_Cand)
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 sempre o mesmo valor de C2.
Exemplo: ... Código ... Salário ...
E1 1000,00
E3 1500,00
E1 1000,00
E2 800,00
E3 1500,00
E2 800,00
E1 1000,00
Observa-se que a coluna Salário depende funcionalmente da coluna Código, ou a coluna Código determina a
coluna Salário.
Para denotar esta dependência funcional usamos:
Código Salário
Exemplo: A B C D
Y 5 1 20
Z 2 6 15
Y 6 7 20
Y 5 1 20
Z 2 6 15
Z 4 2 15
X 10 5 18
X 12 3 18
X 10 5 18
Y 6 7 20
Z 4 2 15
X 12 3 18
Z 2 6 15
Dependências Funcionais: (A,B) C; A D
Segunda Forma Normal
Segunda Forma Normal – 2FN Uma tabela está na segunda forma normal quando, além de estar na
1FN, não contém dependências parciais.
51
- A passagem para a 2FN visa eliminar um certo tipo de redundância de dados.
- Para exemplificar, na tabela ProjEmp os dados referentes a empregados (Nome, Cat e Sal) aparecem várias
vezes repetidos para os empregados que trabalham em mais de um projeto.
- Uma tabela que não se encontre na 2FN possui dependências funcionais parciais, ou seja, contém colunas não
chave que dependem apenas de uma parte da chave primária.
- Obviamente que se uma tabela está na 1FN e possui apenas uma coluna como chave primária (chave primária
simples), então ela não conterá dependências parciais. Sendo assim, ela também já está na 2FN. É o caso da
tabela Proj.
- A tabela ProjEmp possui chave primária composta. Abaixo, ilustra-se suas dependências funcionais:
ProjEmp( CodProj, CodEmp, Nome, Cat, Sal, DataIni, TempAl)
- Para passar a tabela ProjEmp para a 2FN (ou seja, eliminar as dependências parciais) é necessário dividir a
mesma em duas tabelas com o seguinte esquema:
ProjEmp(CodProj, CodEmp, DataIni, TempAl)
Emp(CodEmp, Nome, Cat, Sal)
- Assim, o modelo relacional correspondente ao arquivo em questão, na 2FN fica:
Proj(CodProj, Tipo, Descr)
ProjEmp(CodProj, CodEmp, DataIni, TempAl)
Emp(CodEmp, Nome, Cat, Sal)
- O conteúdo das tabelas é exibido a seguir:
Proj: CodProj Tipo Descr
LSC001 Novo Desenv. Sistema de Estoque
PAG02 Manutenção Sistema de RH
ProjEmp: CodProj CodEmp DataIni TempAl
LSC001 2146 01-11-1991 24
LSC001 3145 02-10-1991 24
LSC001 6126 03-10-1992 18
LSC001 1214 01-11-1991 18
LSC001 8191 01-11-1992 12
Dependência Parcial Uma dependência funcional parcial ocorre quando uma coluna
depende apenas de parte de uma chave primária composta.
52
PAG02 8191 01-05-1993 12
PAG02 4112 04-01-1991 24
PAG02 6126 01-11-1992 12
Emp: CodEmp Nome Cat Sal
2146 João A1 4
3145 Sílvio A2 6
6126 José B1 9
1214 Carlos A2 6
8191 Mário A1 4
4112 João A2 6
- Passos a serem seguidos no processo de passagem da 1FN para a 2FN:
1. Copiar para a 2FN as tabelas que tenham chave primária simples ou que não tenham colunas além da chave.
Ex: Tabela Proj.
2. Para cada tabela com chave primária composta e com pelo menos uma coluna não chave (Ex: Tabela
ProjEmp):
Criar na 2FN uma tabela com as chaves primárias da tabela na 1FN (trata-se da tabela ProjEmp na 2FN);
Para cada coluna não chave questionar: A coluna depende de toda a chave ou de apenas parte dela?
SIM: Depende de toda a chave (Caso das colunas DataIni e TempAl):
Criar a coluna correspondente na tabela com a chave completa na 2FN (Colunas DataIni e TempAl
da tabela ProjEmp na 2FN).
NÃO: Depende apenas de parte da chave (Caso das colunas Nome, Sal e Cat):
Criar, caso não exista ainda, uma tabela na 2FN que tenha como chave primária a parte da chave que
é determinante da coluna em questão (A tabela Emp na 2FN).
Criar a coluna dependente dentro da tabela na 2FN (As colunas Nome, Sal e Cat da tabela Emp na
2FN).
Terceira Forma Normal
- A passagem para a 3FN visa eliminar um outro tipo de redundância de dados.
- Para exemplificar, vamos supor que na tabela Emp o salário de um empregado (coluna Sal) seja determinado
pela sua categoria funcional (coluna Cat).
- Sendo assim, a informação de que um dado salário é pago para uma determinada categoria vai se repetir na
tabela tantas vezes quantos forem os empregados que possuírem aquela categoria.
- A passagem para a 3FN consiste em dividir tabelas de forma a eliminar as dependências transitivas.
- A seguir ilustra-se as dependências funcionais da tabela Emp:
Terceira Forma Normal – 2FN
Uma tabela está na terceira forma normal quando, além de estar na
2FN, não contém dependências transitivas.
Dependência Transitiva
Uma dependência funcional transitiva ocorre quando uma coluna,
além de depender da chave primária da tabela, depende de outra
coluna ou conjunto de colunas da tabela.
53
Emp( CodEmp, Nome, Cat, Sal)
- Para passar a tabela Emp para a 3FN (ou seja, eliminar as dependências transitivas) é necessário dividir a mesma
em duas tabelas com o seguinte esquema:
Emp(CodEmp, Nome, Cat)
Cat(Cat, Sal)
- Assim, o modelo relacional correspondente ao arquivo em questão, na 3FN fica:
Proj(CodProj, Tipo, Descr)
ProjEmp(CodProj, CodEmp, DataIni, TempAl)
Emp(CodEmp, Nome, Cat)
Cat(Cat, Sal)
- O conteúdo das tabelas é exibido a seguir: Proj:
CodProj Tipo Descr
LSC001 Novo Desenv. Sistema de Estoque
PAG02 Manutenção Sistema de RH
ProjEmp: CodProj CodEmp DataIni TempAl
LSC001 2146 01-11-1991 24
LSC001 3145 02-10-1991 24
LSC001 6126 03-10-1992 18
LSC001 1214 01-11-1991 18
LSC001 8191 01-11-1992 12
PAG02 8191 01-05-1993 12
PAG02 4112 04-01-1991 24
PAG02 6126 01-11-1992 12
Emp: CodEmp Nome Cat
2146 João A1
3145 Sílvio A2
6126 José B1
1214 Carlos A2
8191 Mário A1
4112 João A2
Cat: Cat Sal
A1 4
A2 6
B1 9
Passos a serem seguidos no processo de passagem da 1FN para a 2FN:
1. Copiar para a 3FN as tabelas que tenham menos que duas colunas não chave, pois neste caso não há como se
ter dependências transitivas.
2. Para cada tabela com duas ou mais colunas não chave:
Criar na 3FN uma tabela com a chave primária da tabela em questão;
Para cada coluna não chave questionar: A coluna depende de alguma outra coluna não chave?
NÃO: Depende apenas da chave:
Copiar a coluna para a tabela na 3FN (Colunas Nome e Cat da tabela Emp).
SIM: Depende de outra coluna (Caso da coluna Sal):
Criar, caso não exista ainda, uma tabela na 3FN que tenha como chave primária a coluna da qual há a
dependência indireta (A tabela Cat na 3FN).
Copiar a coluna dependente para a tabela criada (A coluna Sal).
54
A coluna determinante deve permanecer também na tabela original.
Exercícios sobre Normalização
1) Normalize os dados abaixo e represente o diagrama entidade-relacionamento correspondente:
a) codProfessor, nomeProfessor, dataAdmissao, codCategoria, descrCategoria, valorAulaCategoria, numeroAulas,
nomeDependente (ocorre n vezes), dtNascDependente (ocorre n vezes), totalDependentes, dtNascProfessor,
codDepartamento, nomeDepartamento, codDisciplina (ocorre n vezes), nomeDisciplina (ocorre n vezes)
b) codAluno, codDisciplina (ocorre n vezes), nomeDisciplina (ocorre n vezes), cargaHorariaDisciplina (ocorre n
vezes), nomeAluno, codLocalNascAluno, nomeLocalNascAluno
2) Identifique nas relações abaixo as dependências funcionais e se há violação de alguma forma normal (1, 2 ou 3).
Se houver violação, normalize-as. Os campos entre parênteses indicam campos multivalorados:
a) Salario(RgFunc, NomeFunc, (DataSalario, ValorSalario))
b) Gerencia(CodDepartamento, RgGerente, DescrDepartamento)
3) Aplique as formas normais cabíveis, nos itens abaixo. Você deve transformar as relações abaixo em conjuntos
de relações que estejam na 2FN, 3FN e, justificar sua normalização de acordo com as dependências funcionais:
a) Funcionario(NumeroFuncionario, NomeFuncionario, NumeroDepto, NomeDepto, NumeroSupervisor,
NomeSupervisor, (NumeroProjeto, NomeProjeto, DataInicioProjeto, NumHorasTrabalhadasProjeto))
b) Paciente (NumPaciente, NomePaciente, NumQuarto, DescrQuarto, NumComodosQuarto, (CodMedico,
NomeMedico, FoneMedico))
4) A Vídeo Center of America Ltda. é uma cadeia de locadoras de DVDs. Ela precisa manter dados sobre os
DVDs, seus clientes e locações. Cada DVD para locação tem um número de série único. Os títulos de filme e
números de cliente também são identificadores únicos. Suponha que cada filme tenha exatamente uma “estrela”.
Observe a diferença no ano em que cada filme foi originalmente filmado em oposição à data em que o DVD – o
disco real – foi fabricado. Alguns dos atributos e dependências funcionais neste ambiente são os seguintes:
Atributos: Número do DVD, Data de Fabricação, Título do Filme, Estrela, Ano de Filmagem, Duração (em
minutos), Número do Cliente, Nome do Cliente, Endereço do Cliente, Data da Locação, Data da Devolução, Valor
Pago.
Dependências Funcionais:
Número do DVD Título do Filme
Número do DVD Estrela
Número do DVD Data de Fabricação
Título do Filme Estrela
Título do Filme Duração
Título do Filme Ano de Filmagem
Número do Cliente Nome do Cliente
Número do Cliente Endereço do Cliente
Número do DVD, Número do Cliente, Data de Locação Data de Devolução, Valor Pago
Para cada uma das tabelas a seguir, escreva primeiro a forma normal da tabela (como 1FN, 2FN ou 3FN). A seguir,
para aquelas tabelas que estejam atualmente na 1FN ou 2FN, reconstrua-as como tabelas bem estruturadas na 3FN.
Os atributos que são as chaves primárias estão sublinhados. Não suponha qualquer outra dependência funcional
além das mostradas.
a) Título do Filme, Estrela, Duração, Ano de Filmagem.
55
b) Número do DVD, Número do Cliente, Data de Locação, Nome do Cliente, Data da Devolução, Valor Pago.
c) Número do DVD, Data da Fabricação, Título do Filme, Estrela.
d) Título do Filme, Número do Cliente, Estrela, Duração, Nome do Cliente, Endereço do Cliente.
e) Número do DVD, Número do Ciente, Data de Locação, Data da Devolução, Valor Pago.
5) Apresente um conjunto de dependências funcionais para o esquema de relação R(A,B,C,D) com AB sendo
chave primaria tal que R está em 1FN, mas não está em 2FN.
6) Apresente um conjunto de dependências funcionais para o esquema de relação R(A,B,C,D) com AB sendo
chave primaria tal que R está em 2FN, mas não está em 3FN.
56
Parte 5 - SQL (Structured Query Language)
- SQL (“Structured Query Language”): Evolução do sistema protótipo Sistema R, desenvolvido inicialmente por
Chamberlin no IBM Research Laboratory (1974-1977), em San Diego (Califórnia).
- Linguagem de interface concebida para o sistema: SEQUEL (“Structured English Query Language”).
Posteriormente, passou-se a chamar SEQUEL II. Finalmente, o nome foi abreviado para SQL.
- 1986: O “American National Standards Institute” (ANSI) e a “International Organization for Standardization”
(ISO) padronizaram um dialeto do SQL como interface oficial para sistemas relacionais.
- 1989: Revisão com adição de algumas extensões.
- 1992: Revisão mais recente. Por esta razão, a linguagem é atualmente conhecida informalmente como SQL/92.
- Linguagens como a SQL são denominadas não procedurais: especifica-se o que se quer e não o como.
- Os comandos existentes na linguagem SQL utilizados na interação com bancos de dados são subdivididos em
cinco grupos:
Linguagem de Definição de Dados (DDL - Data Definition Language)
- A DDL possui comandos para especificar a estrutura de uma tabela, alterar esta estrutura ou excluí-la que são,
respectivamente, os comandos create table, alter table e drop table.
- Também possui comandos para gerenciar índices e visões (views): create index, drop index, create view e drop
view.
Linguagem de Manipulação de Dados (DML - Data Manipulation Language)
- A DML possui um conjunto de comandos que permitem a manipulação dos dados de um banco de dados.
- A manipulação de dados envolve:
Inserção de novos dados no banco de dados;
Exclusão de dados do banco de dados;
Alteração de dados armazenados no banco de dados.
Linguagem de Consulta aos Dados (DQL - Data Query Language)
- A DQL é a parte da SQL mais utilizada.
- Contém apenas o comando SELECT, com suas várias cláusulas e opções. Este comando permite que se
especifique uma consulta ("query") como uma descrição do resultado desejado.
Linguagem de Controle de Dados (DCL - Data Control Language)
- Esta parte da linguagem está relacionada ao controle do acesso aos dados, afetando, portanto, diretamente a
segurança dos mesmos.
- Duas palavras chave da DCL são:
Grant: Autoriza ao usuário executar ou setar operações.
Revoke: Remove ou restringe a capacidade de um usuário de executar operações.
Exemplo:
GRANT SELECT, INSERT, UPDATE ON Fornecedor
TO [ORGANIZACAO\alexandre.araujo]
57
Linguagem de Transação de Dados (DTL - Data Transaction Language)
- Esta parte da linguagem está relacionada ao controle de transações, possuindo os comandos:
START TRANSACTION: Pode ser usado para marcar o começo de uma transação de banco de dados que
pode ser completada ou não.
COMMIT: Envia todos os dados das mudanças permanentemente.
ROLLBACK: Faz com que as mudanças nos dados existentes desde o último COMMIT ou ROLLBACK
sejam descartadas.
Tabela - Comandos da Linguagem SQL
Tipo de Comando Exemplos de Comandos
Definição de Dados (DDL) create table/alter table/drop table
create index/ drop index
Manipulação de Dados (DML) insert
update
delete
Consulta aos Dados (DQL) select
Transação (DTL) commit
rollback
savepoint
Controle (DCL) grant
revoke
- Numerosos produtos suportam a linguagem SQL. Embora as versões de SQL implementadas em cada
produto difiram em diversos detalhes de linguagem, as diferenças são, em sua maioria, secundárias.
Definição de Dados (DDL)
- Comandos usados para definir (CREATE), alterar (ALTER) e deletar (DROP) estruturas de dado e de acesso:
Tabelas e Índices.
Criando Tabelas – Create Table
- Objetivo: Criar a estrutura de uma tabela definido as colunas (campos) e as chaves primárias e estrangeiras
existentes.
Sintaxe:
CREATE TABLE <nome-tabela> (<campo1> <tipo-do-dado [(tamanho)]> [NOT NULL] [UNIQUE]
[NOT NULL WITH DEFAULT]
[,<campo2> <tipo-do-dado [(tamanho)]> [NOT NULL] [UNIQUE]
[NOT NULL WITH DEFAULT]] [,...]
[, definição-de-chave-primária ] [, definição-de-chave-estrangeira1][, definição-de-chave-estrangeira2 ] [,...] );
sendo a sintaxe para definição-de-chave-primária:
PRIMARY KEY (campo1 [, campo2] [,...])
e a sintaxe para definição-de-chave-estrangeira é:
58
FOREIGN KEY (campo1 [, campo2] [,...])
REFERENCES <nome-tabela-estrangeira> (campo_estrangeiro1 [, campo_estrangeiro2] [,...])
[ ON DELETE [RESTRICT] [CASCADE] [SET NULL] ]
onde:
a) nome-tabela - Representa o nome da tabela que será criada.
b) campo1, campo2, etc - Representa os nomes das colunas da tabela. A definição das colunas de uma tabela é feita
relacionando-as uma após a outra.
c) tipo-do-dado [(tamanho)] - Define o tipo e tamanho dos campos definidos para a tabela.
d) NOT NULL – A presença desta cláusula exige o preenchimento do campo, ou seja, torna obrigatório, no momento
da inclusão, que o mesmo possua um conteúdo.
e) NOT NULL WITH DEFAULT - Preenche o campo com valores pré-definidos, de acordo com o tipo do campo,
caso não seja especificado o seu conteúdo no momento da inclusão do registro. Os valores pré-definidos são:
e.1) Campos numéricos - Valor zero.
e.2 ) Campos alfanuméricos - Caracter branco.
e.3) Campo formato Date - Data corrente.
e.4) Campo formato Time - Horário no momento da operação.
f) UNIQUE - Especifica que não pode haver dois registros na tabela especificada que tenham o mesmo valor para o
campo em um mesmo instante.
g) PRIMARY KEY (campo1 [, campo2] [,...]) - Define o(s) campo(s) que compõe(m) a chave primária da tabela.
Caso a chave primária seja composta por mais de um campo, os nomes dos campos devem ser listados separados
por vírgulas entre os parênteses.
h) FOREIGN KEY (campo1 [, campo2] [,...]) REFERENCES nome-tabela-estrangeira (campo_estrangeiro1 [,
campo_estrangeiro2] [,...]) - Define os campos que são chaves estrangeiras na tabela, ou seja, os campos que são
chaves primárias de outras tabelas. Na cláusula REFERENCES deve ser especificada a tabela na qual o(s)
campo(s) compõe(m) a chave primária, seguida do(s) nome(s) do(s) campo(s) correspondente(s) na referida tabela
entre parênteses.
i) ON DELETE - Esta cláusula especifica os procedimentos que devem ser feitos pelo SGBD quando houver uma
exclusão de um registro na tabela pai quando existe um registro correspondente nas tabelas filhas. As opções
disponíveis são:
i.1) RESTRICT - Opção default. Esta opção não permite a exclusão na tabela pai de um registro cuja chave
primária exista em alguma tabela filha.
i.2) CASCADE - Esta opção realiza a exclusão em todas as tabelas filhas que possua o valor da chave que
será excluída na tabela pai.
i.3) SET NULL - Esta opção atribui o valor NULO nas colunas das tabelas filhas que contenha o valor da
chave que será excluída na tabela pai.
- Os tipos de dados mais comuns são:
1) Numéricos:
- Smallint - Armazena valores numéricos, em dois bytes, compreendidos no intervalo -32768 a +32767.
- Integer - Armazena valores numéricos, em quatro bytes, compreendidos no intervalo -2147483648 a
+2147483647
- Decimal(n,m) - Armazena valores numéricos com no máximo 15 dígitos. Nesta opção deve ser definida a
quantidade de dígitos inteiros (n) e casas decimais (m) existentes no campo.
2) Alfanuméricos:
- Character (n) - Armazena um campo alfanumérico de n caracteres, onde n deve estar entre 0 e 255.
59
3) Campo Date – Armazena data.
4) Campo Time - Armazena horário.
Tipos de Dados: SQL Server
Abaixo segue uma relação dos tipos de dados básicos do SQL Server, sendo que os tipos que estiverem marcados
com * somente funcionam a partir do SQL Server 2000
TINYINT: Valores numéricos inteiros variando de 0 até 256
SMALLINT: Valores numéricos inteiros variando de –32.768 até 32.767
INT: Valores numéricos inteiros variando de -2.147.483.648 até 2.147.483.647
* BIGINT: Valores numéricos inteiros variando de –92.23.372.036.854.775.808 até 9.223.372.036.854.775.807
BIT: Somente pode assumir os valores 0 ou 1. Utilizado para armazenar valores lógicos.
DECIMAL(I,D) e NUMERIC(I,D): Armazenam valores numéricos inteiros com casas decimais utilizando
precisão. I deve ser substituído pela quantidade de dígitos total do número e D deve ser substituído pela quantidade
de dígitos da parte decimal (após a vírgula). DECIMAL e NUMERIC possuem a mesma funcionalidade, porém
DECIMAL faz parte do padrão ANSI e NUMERIC é mantido por compatibilidade. Por exemplo, DECIMAL(8,2)
armazena valores numéricos decimais variando de – 999999,99 até 999999,99
Lembrando sempre que o SQL Server internamente armazena o separador decimal como ponto (.) e o separador de
milhar como vírgula (,). Essas configurações INDEPENDEM de como o Windows está configurado no painel de
controle e para DECIMAL E NUMERIC, somente o separador decimal (.) é armazenado
SMALLMONEY: Valores numéricos decimais variando de -214.748,3648 até 214.748,3647
MONEY: Valores numéricos decimais variando de -922.337.203.685.477,5808 até 922.337.203.685.477,5807
REAL: Valores numéricos aproximados com precisão de ponto flutuante, indo de -3.40E + 38 até 3.40E + 38
FLOAT: Valores numéricos aproximados com precisão de ponto flutuante, indo de -1.79E + 308 até 1.79E + 308
SMALLDATETIME: Armazena hora e data variando de 1 de janeiro de 1900 até 6 de junho de 2079. A precisão
de hora é armazenada até os segundos.
DATETIME: Armazena hora e data variando de 1 de janeiro de 1753 até 31 de Dezembro de 9999. A precisão de
hora é armazenada até os centésimos de segundos.
CHAR(N): Armazena N caracteres fixos (até 8.000) no formato não Unicode. Se a quantidade de caracteres
armazenada no campo for menor que o tamanho total especificado em N, o resto do campo é preenchido com
espaços em branco.
VARCHAR(N): Armazena N caracteres (até 8.000) no formato não Unicode. Se a quantidade de caracteres
armazenada no campo for menor que o tamanho total especificado em N, o resto do campo não é preenchido.
TEXT: Armazena caracteres (até 2.147.483.647) no formato não Unicode. Se a quantidade de caracteres
armazenada no campo for menor que 2.147.483.647, o resto do campo não é preenchido. Procure não utilizar este
tipo de dado diretamente, pois existem funções específicas para trabalhar com este tipo de dado.
60
NCHAR(N): Armazena N caracteres fixos (até 4.000) no formato Unicode. Se a quantidade de caracteres
armazenada no campo for menor que o tamanho total especificado em N, o resto do campo é preenchido com
espaços em branco.
NVARCHAR(N): Armazena N caracteres (até 4.000) no formato Unicode. Se a quantidade de caracteres
armazenada no campo for menor que o tamanho total especificado em N, o resto do campo não é preenchido.
NTEXT: Armazena caracteres (até 1.073.741.823) no formato Unicode. Se a quantidade de caracteres armazenada
no campo for menor que 1.073.741.823, o resto do campo não é preenchido. Procure não utilizar este tipo de dado
diretamente, pois existem funções específicas para trabalhar com este tipo de dado.
Exemplo:
- Criar as tabelas Funcionario, Departamento, Projeto, TrabalhaEm e Dependente do banco de dados Companhia.
Tabela Funcionario:
create table Funcionario(Nome text(60), CPF long, DataNasc date, Endereco text(80), Sexo char, Salario single,
CPFSuper long, NDep byte, primary key (CPF));
Tabela Departamento:
create table Departamento(DNome text(20), DNumero byte, DLocalizacao text(20), CPFGer long, DataIniGer
date, primary key (DNumero), foreign key (CPFGer) references Funcionario (CPF));
Tabela Projeto:
create table Projeto(PNome text(20), PNumero byte, PLocalizacao text(20), DNum byte, primary key (PNumero),
foreign key (DNum) references Departamento(DNumero));
Tabela TrabalhaEm:
create table TrabalhaEm(CPFFunc long, ProjNum byte, Horas byte, primary key (CPFFunc, ProjNum), foreign
key (CPFFunc) references Funcionario(CPF), foreign key (ProjNum) references Projeto(PNumero));
Tabela Dependente:
create table Dependente(CPFFunc long, NomeDependente text(60) not null, Sexo char, DataNasc date, Relacao
text(20), primary key (CPFFunc, NomeDependente), foreign key (CPFFunc) references Funcionario(CPF));
Alterando Tabelas – Alter Table
- Objetivo: Alterar a estrutura de uma tabela acrescentando, alterando e excluindo campos ou alterando nomes e
formatos dos campos, ou ainda alterando a integridade referencial definida para a tabela.
Sintaxe:
ALTER TABLE <nome-tabela>
DROP <nome-campo>
ADD <nome-campo> <tipo-do-dado> [NOT NULL] [UNIQUE]
[NOT NULL WITH DEFAULT]
RENAME <nome-campo> <novo-nome-campo>
RENAME TABLE <novo-nome-tabela>
MODIFY <nome-campo> <novo-nome-campo> <tipo-do-dado> [NULL] [UNIQUE]
61
[NOT NULL]
[NOT NULL WITH DEFAULT]
ADD PRIMARY KEY (<nome-campo> [,<nome-campo>] [,...])
DROP PRIMARY KEY (<nome-campo> [,<nome-campo>] [,...])
ADD FOREIGN KEY (<nome-campo> [,<nome-campo>] [,...]) REFERENCES
<nome-tabela-estrangeira> (<nome-campo-estrangeiro> [,<nome-campo-estrangeiro>] [,...])
[ON DELETE [RESTRICT] [CASCADE] [SET NULL]]
DROP FOREIGN KEY (nome-campo-chave-estrangeira)
onde:
a) nome-tabela - Representa o nome da tabela que será alterada.
b) nome-campo - Representa o nome do campo que será alterado, deletado ou incluído.
c) tipo-do-dado - Define o tipo e tamanho do campo a ser incluído ou modificado.
d) DROP <nome-campo> - Efetua a exclusão do campo especificado da estrutura da tabela.
e) ADD <nome-campo> <tipo-do-dado> - Efetua a inclusão do campo especificado na estrutura da tabela. As
definições NOT NULL e NOT NULL WITH DEFAULT são semelhantes às do comando CREATE TABLE.
f) RENAME <nome- campo > <novo-nome- campo > - Efetua a troca do nome do campo especificado.
g) RENAME TABLE <novo-nome-tabela> - Efetua a troca do nome da tabela especificada.
h) MODIFY <nome-campo> <novo-nome-campo> <tipo-do-dado> - Efetua a alteração no nome e tipo do campo
especificado.
Opções: - Além das existentes na opção ADD (NOT NULL e NOT NULL WITH DEFAULT), temos a opção
NULL que altera a característica do campo passando a permitir o preenchimento com o valor Nulo.
i) ADD PRIMARY KEY - Esta opção é utilizada quando se quer acrescentar um novo campo como chave
primária da tabela.
j) DROP PRIMARY KEY - Esta opção é utilizada quando se quer retirar um campo como chave primária da
tabela.
l) ADD FOREIGN KEY - Esta opção é utilizada quando um campo deve ser especificado como sendo uma chave
estrangeira.
l) DROP FOREIGN KEY - Esta opção é utilizada quando se quer retirar uma chave estrangeira da estrutura da
tabela.
Exemplo: - Considerando que a tabela Funcionario tenha sido criada com o comando sql abaixo:
create table Funcionario(Nome text(60), CPF long, DataNasc date, Endereco text(80), Sexo char, Salario single,
CPFSuper long, NDep byte, primary key (CPF));
aonde faltou especificar que NDep é uma chave estrangeira que faz referência ao campo DNumero da tabela
Departamento, acrescentar esta informação, na tabela Funcionario:
alter table funcionario add foreign key (NDep) references Departamento (DNumero);
62
Também acrescentar que CPFSuper é uma chave estrangeira que faz referência ao CPF da própria tabela
Funcionario
alter table funcionario add foreign key (CPFSuper) references Funcionario (CPF)
Excluindo Tabelas – Drop Table
- Objetivo: Excluir a estrutura e os dados existentes em uma tabela. Após a execução deste comando estarão
deletados todos os dados, a estrutura e os índices de acessos que estejam a ela associados.
Sintaxe:
DROP TABLE <nome-tabela>
onde:
a) nome-tabela - Representa o nome da tabela que será deletada.
Criando Índices – Create Index
- Objetivo: Criar uma estrutura de índice de acesso para uma determinada coluna em uma tabela. Um índice de
acesso permite um acesso mais rápido aos dados em uma operação de recuperação. Os índices podem ser criados a
partir de um ou mais campos de uma tabela. Devem ser criados para os campos que são frequentemente
pesquisados.
- A criação de índices para chaves estrangeiras aumenta a velocidade na execução das junções. A criação de
índices facilita também a realização dos comandos ORDER BY e GROUP BY.
- Logicamente, criar índices pode também trazer algumas desvantagens, como por exemplo: o tempo para a
construção do índice, o espaço em disco utilizado para armazenar os índices e o maior tempo levado nas operações
de modificação no banco de dados, pois todas as mudanças têm que ser realizadas nos dados e nos índices.
- As diretrizes abaixo descrevem que tipo de dado deve ou não ser indexado:
1. Campos que devem ser indexados:
Chave primária.
Campos freqüentemente utilizados em operações de junção (chaves estrangeiras).
Campos freqüentemente pesquisados em faixas de valores.
Campos freqüentemente recuperados de forma classificada.
2. Campos que não devem ser indexados:
Campos que raramente são referenciados em uma consulta.
Campos que contém poucos valores únicos.
Campos definidos com os tipos de dados text, image ou bit
Quando o desempenho das atualizações é mais importante que o desempenho das consultas.
Sintaxe:
CREATE [UNIQUE] INDEX <nome-índice>
ON <nome-tabela> (<nome-campo> [ASC ] [,<nome-campo> [ASC ] ] [,...])
[DESC] [DESC]
63
onde:
a) nome-índice - Representa o nome da estrutura de índice que será criada.
b) nome-tabela - Representa o nome da tabela que contem a coluna na qual será criado o índice de acesso.
c) nome-campo - Representa o nome do campo a ser indexado.
d) Opção ASC/DESC - Representa a criação do índice com ordenação ascendente (crescente) (ASC) ou
decrescente (DESC). ASC é o default.
e) Opção UNIQUE - Especifica que não pode haver dois registros na tabela base especificada que tenham o mesmo
valor para o campo indexado ou combinação de campos em um mesmo instante.
- Os campos especificados para compor o índice não precisam ser contíguos e nem ser todos do mesmo tipo de
dado.
Exemplo: - Indexar o campo Nome da tabela Funcionario em ordem ascendente.
create index NomeFunc on Funcionario (Nome);
- O índice criado será automaticamente atualizado para refletir atualizações na tabela.
Deletando Índices – Drop Index
- Objetivo: Deletar uma estrutura de índice de acesso para uma determinada coluna em uma tabela.
Sintaxe:
DROP INDEX <nome-índice>
onde:
a) nome-índice - Representa o nome da estrutura de índice que será deletada.
- O índice especificado é destruído.
Manipulação de Dados (DML)
Inserindo Dados - Insert
- Objetivo: Incluir um novo registro em uma tabela do Banco de Dados.
Sintaxe:
INSERT INTO <nome-tabela> [(<nome-campo> [,<nome- campo>] [,...])]
VALUES (<literal1 [, literal2] [,...] >)
onde:
a) nome-tabela - Representa o nome da tabela onde será incluído o novo registro.
b) nome-campo - Representa o nome da(s) coluna(s) que terão conteúdo após a operação de inclusão.
c) literal – Representa os valores a serem incluídos para cada um dos campos listados, segundo a ordem em que são
listados.
64
Obs.: Este comando pode ser executado de duas maneiras:
1) Quando todos os campos da tabela terão conteúdo para o novo registro - Neste caso não é necessário especificar
os nomes dos campos, entretanto a relação dos valores a serem incluídos deverá obedecer a mesma seqüência da
definição da tabela.
2) Quando apenas parte dos campos da tabela terão conteúdo - Neste caso devem ser especificados todos os nomes
dos campos que terão conteúdo e os valores relacionados deverão obedecer esta seqüência. Os campos para os
quais não houver conteúdo especificado serão preenchidos com o valor NULL.
Exemplo: - Inserir um registro na tabela Funcionário com os seguintes valores para os campos (Nome, CPF,
DataNasc, Endereco, Sexo, Salario, CPFSuper, NDep):
João Nunes Silva, 123456789, 08-DEZ-57, R. A., 1, M, 3000,00, null, null
insert into Funcionario values('João Nunes Silva', 123456789, '08-DEZ-57', 'R. A, 1', 'M', 3000, null, null);
- Atenção para o fato de que dados do tipo caracter ou data devem ser delimitados por aspas simples.
- Os registros a serem inseridos em uma tabela também podem ser especificados como o resultado de uma
consulta.
Sintaxe:
INSERT INTO <nome-tabela> [(<nome-campo> [,<nome- campo>] [,...])]
subconsulta;
- Neste segundo formato uma subconsulta é avaliada e uma cópia do resultado é inserida na tabela: a i-ésima
coluna do resultado desta consulta corresponde ao i-ésimo campo na lista de campos.
Exemplo:
- Criar uma tabela chamada Temporaria e inserir nela o primeiro nome, o sexo, o CPF e o endereço de todos os
funcionários.
CREATE TABLE Temporaria
(Nome text(60) , SexoFunc text(1), CPFFunc long, End text(80),
PRIMARY KEY ( CPFFunc) );
INSERT INTO Temporaria(Nome, SexoFunc, CPFFunc, End)
SELECT Nome, Sexo, CPF, Endereco
FROM Funcionario;
Atualizando Dados - Update
- Objetivo: Atualizar os dados de um ou um grupo de registros em uma tabela do Banco de Dados.
Sintaxe:
UPDATE <nome-tabela>
SET <nome-campo> = <novo conteúdo para o campo>
[,<nome-campo> = <novo conteúdo para o campo>]
[WHERE <condição>]
onde:
a) nome-tabela - Representa o nome da tabela cujo conteúdo será alterado.
65
b) nome-campo - Representa o nome da(s) coluna(s) que terão seus conteúdos alterados com o(s) novo(s) valor(es)
especificado(s).
c) condição - Representa a condição para a seleção dos registros que serão atualizados. Esta seleção poderá resultar
em um ou vários registros. Neste caso a alteração irá ocorrer em todos os registros selecionados.
Exemplo: - Atualizando um único registro:
- Alterar o endereço e o salário do funcionário cujo CPF é 123456789:
UPDATE Funcionario
SET Endereco = 'R. X., 65 ',
Salario = 3200
WHERE CPF = 123456789 ;
Exemplo: - Atualizando múltiplos registros:
- Conceder um aumento de 6% a todos os funcionários do sexo feminino:
UPDATE Funcionario
SET Salario = Salario * 1.06
WHERE Sexo = 'F' ;
- Na omissão da cláusula WHERE a atualização é feita para todos os registros da tabela.
Exemplo: - Atualizando múltiplos registros:
- Conceder um aumento de 2% a todos os funcionários:
UPDATE Funcionario
SET Salario = Salario * 1.02;
- A condição da cláusula WHERE pode ser uma subconsulta.
Exemplo: - Usando subconsultas para atualizar:
- Conceder um aumento de 3% nos salários de todos os funcionários que sejam gerentes de algum
departamento:
UPDATE Funcionario
SET Salario = Salario * 1.03
WHERE CPF IN
(SELECT DISTINCT CPFGer
FROM Departamento);
Deletando Dados - Delete
- Objetivo: Deletar um ou um grupo de registros de uma tabela do Banco de Dados.
Sintaxe:
DELETE FROM <nome-tabela>
[WHERE <condição>];
onde:
a) nome-tabela - Representa o nome da tabela cujos registros serão deletados.
b) condição - Representa a condição para a deleção dos registros. Este seleção poderá resultar em um ou vários
registros. Neste caso a operação irá ocorrer em todos os registros selecionados.
66
Ex: - Deletando um único registro:
- Deletar da tabela Temporaria o registro referente ao funcionário cujo CPF é 999887777:
DELETE
FROM Temporaria
WHERE CPFFunc = 999887777 ;
Ex: - Deletando múltiplos registros:
- Deletar da tabela Temporaria os registros referentes a todos os funcionários de sexo Feminino.
DELETE
FROM Temporaria
WHERE Sexo = 'F';
- A condição da cláusula WHERE pode ser uma subconsulta.
Exemplo: - Usando subconsultas para deletar:
- Deletar da tabela Temporaria todos os funcionarios que possuem algum dependente.
DELETE
FROM Temporaria
WHERE CPFFunc IN
(SELECT DISTINCT CPFFunc
FROM Dependente);
- Cuidado: A omissão da cláusula WHERE faz com que todos os registros da tabela sejam deletados!!!!!
Extração de Dados – Comando Select
Sintaxe do comando SELECT:
SELECT campo [,campo]...
FROM tabela [,tabela]...
[ WHERE condição]
[ GROUP BY campo [, campo ]...]
[ HAVING condição]
[ ORDER BY campo [, campo ]...];
- O SELECT é o mais complexo dentre os comandos SQL, possibilitando a realização de um grande número de
tarefas, como:
Consultas simples;
Consultas com subconsultas;
Ligações;
Consultas com resultados ordenados.
- Forma mais comum do comando SELECT: SELECT... FROM...WHERE...
Exemplo: SELECT Nome, Salario
FROM Funcionario
WHERE Salario > 2500 ;
Tabela resultante:
Nome Salario
João Nunes Silva 3000,00
Francisco Teles Natti 4000,00
Jenifer Souza Lopes 4300,00
Ramesh Kaj Narayan 3800,00
Thiago Carvalho Pinto 5500,00
67
Correspondente na A.Relacional: Nome, Salario( Salario > 2500 (Funcionario))
- Nome de campo qualificado consiste no nome da tabela ao qual pertence seguido do nome do campo, separados
por um ponto: sempre se pode usar campos qualificados.
Exemplo: SELECT Funcionario.Nome, Funcionario.Salario
FROM Funcionario
WHERE Salario > 2500 ;
Omitindo a cláusula WHERE
- Na omissão da cláusula WHERE a condição é considerada verdadeira: o valor dos campos selecionados pela
cláusula SELECT é listado para todas as tuplas da tabela selecionada.
Exemplo: SELECT Salario
FROM Funcionario;
Tabela resultante:
Salario 3000,00
4000,00
2500,00
4300,00
3800,00
2500,00
2500,00
5500,00
Usando a palavra-chave DISTINCT
- O SQL não elimina linhas duplicadas do resultado de um SELECT, a menos que se indique explicitamente.
Exemplo: SELECT DISTINCT Salario
FROM Funcionario;
Tabela resultante:
Salario
3000,00
4000,00
2500,00
4300,00
3800,00
5500,00
Correspondente na Álgebra Relacional: Salario (Funcionario)
68
Extraindo valores calculados
- A cláusula SELECT pode incluir também expressões envolvendo operadores escalares.
Exemplo: SELECT Nome, ‘Salario em dólares:’, Salario/3 AS Salario em Dólares
FROM Funcionario;
Tabela resultante:
Nome Salário em Dólares
João Nunes Silva Saldo em Dólares: 1000,00
Francisco Teles Natti Saldo em Dólares: 1333,33
Alícia Jamil Zelaya Saldo em Dólares: 833,33
Jenifer Souza Lopes Saldo em Dólares: 1433,33
Ramesh Kaj Narayan Saldo em Dólares: 1266,67
Joyce Abreu Elias Saldo em Dólares: 833,33
Ahmad Veri Jabbar Saldo em Dólares: 833,33
Thiago Carvalho Pinto Saldo em Dólares: 1833,33
Selecionando todos os campos
- O uso de um asterisco após a cláusula SELECT implica na seleção de todos os campos da tabela especificada na
cláusula FROM.
Exemplo: SELECT *
FROM Funcionario; é equivalente à:
SELECT Nome, CPF, DataNasc, Endereço, Sexo, Salario, CPFSuper, NDep
FROM Funcionario;
Correspondente na Álgebra Relacional:
Nome, CPF, DataNasc, Endereço, Sexo, Salario, CPFSuper, NDep (Funcionario)
Condição da cláusula WHERE envolvendo operadores relacionais e lógicos
- A condição que se segue à cláusula WHERE pode incluir os operadores de comparação: =, <>, >, >=, <, <= e
operadores booleanos: AND, OR e NOT
Exemplo: - Obter o primeiro nome de todos os funcionários que trabalham no departamento 5 e ganham mais que R$3000,00. SELECT Funcionario.Nome
FROM Funcionario
WHERE Funcionario.NDep = 5 AND Funcionario.Salario > 3000,00;
Tabela resultante:
Nome Francisco Teles Natti
Ramesh Kaj Narayan
Extraindo dados ordenados
- A tabela resultante de uma consulta não é apresentada, em geral, em nenhuma ordem particular.
69
- A cláusula ORDER BY é usada para se obter a tabela resultante ordenada, devendo-se especificar os nomes dos
campos em termos dos quais o ordenamento deve ser feito e a ordem desejada.
- A ordem pode ser ASC (Ascendente), que é o default, ou DESC (Descendente).
Exemplo: - Obter o nome, CPF e Salário dos funcionários do sexo masculino, em ordem
alfabética descendente de seus primeiros nomes. SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Sexo = “M”
ORDER BY Funcionario.Nome DESC;
Tabela resultante:
Nome CPF Salario
Thiago Carvalho Pinto 888665555 5500
Ramesh Kaj Narayan 666884444 3800
João Nunes Silva 123456789 3000
Francisco Teles Natti 333445555 4000
Ahmad Veri Jabbar 987987987 2500
Consultas envolvendo ligações entre tabelas
- Consultas podem envolver a extração de dados de mais de uma tabela.
- O resultado de uma tal consulta é a ligação de duas ou mais tabelas sob a condição de existência de algum tipo de
relação entre valores de um determinado campo comum às tabelas envolvidas.
- Quando o operador de comparação usado na condição de ligação é o operador de igualdade e são eliminados os
campos repetidos, diz-se que o resultado da operação é uma ligação natural.
Exemplo: - Obter informações sobre departamentos e o nome dos gerentes de cada departamento.
SELECT Departamento.*, Funcionario.Nome
FROM Departamento, Funcionario
WHERE Departamento.CPFGer = Funcionario.CPF;
Tabela resultante:
DNome DNumero DLocalizacao CPFGer DataIniGer Nome
Pesquisa 5 Prédio A 333445555 22/05/78 Francisco Teles Natti
Administrativo 4 Prédio B 987654321 01/01/85 Jenifer Souza Lopes
Gerencial 1 Prédio C 888665555 19/06/71 Thiago Carvalho Pinto
Correspondente na A.Relacional:
Departamento.DNome, Departamento.DNumero, Departamento.DLocalizacao, Departamento.CPFGer,
Departamento.DataIniGer, Funcionario.Nome
(Departamento* Funcionario) Departamento.CPFGer = Funcionario.CPF
- O operador de comparação usado na condição de ligação pode ser qualquer um dos operadores, além do de
igualdade: <>, >, >=, <. <=.
- A cláusula WHERE em uma ligação pode incluir outras condições além da condição de ligação.
Exemplo: - Obter informações sobre departamentos e o primeiro nome dos gerentes de cada
departamento, considerando apenas os gerentes que ganham mais do que R$4000,00
70
SELECT Departamento.*, Funcionario.Nome
FROM Departamento, Funcionario
WHERE Departamento.CPFGer = Funcionario.CPF AND Funcionario.Salario > 4000,00;
Tabela resultante:
DNome DNumero DLocalizacao CPFGer DataIniGer Nome DNome
Gerencial 1 Prédio C 888665555 19/06/71 Thiago Carvalho Pinto Gerencial
Administrativo 4 Prédio B 987654321 01/01/85 Jenifer Souza Lopes Administrativo
- Não há nenhum limite intrínseco para o número de tabelas que podem participar de uma consulta envolvendo
ligações.
Exemplo: - Obter informações sobre departamentos e o primeiro nome dos gerentes de cada
departamento, para aqueles gerentes que possuem dependentes.
SELECT Departamento.*, Funcionario.Nome
FROM Departamento, Funcionario, Dependente
WHERE Departamento.CPFGer = Funcionario.CPF AND
Funcionario.CPF = Dependente.CPFFunc;
Tabela resultante:
DNome DNumero DLocalizacao CPFGer DataIniGer Nome
Pesquisa 5 Prédio A 333445555 22/05/78 Francisco Teles Natti
Pesquisa 5 Prédio A 333445555 22/05/78 Francisco Teles Natti
Pesquisa 5 Prédio A 333445555 22/05/78 Francisco Teles Natti
Administrativo 4 Prédio B 987654321 01/01/85 Jenifer Souza Lopes
- Para eliminar linhas repetidas, utilizar a cláusula DISTINCT:
Exemplo:
SELECT DISTINCT Departamento.*, Funcionario.Nome
FROM Departamento, Funcionario, Dependente
WHERE Departamento.CPFGer = Funcionario.CPF AND
Funcionario.CPF = Dependente.CPFFunc;
Tabela resultante:
DNome DNumero DLocalizacao CPFGer DataIniGer Nome
Pesquisa 5 Prédio A 333445555 22/05/78 Francisco Teles Natti
Administrativo 4 Prédio B 987654321 01/01/85 Jenifer Souza Lopes Correspondente na A.Relacional:
Departamento.DNome, Departamento.DNumero, Departamento.DLocalizacao, Departamento.CPFGer,
Departamento.DataIniGer, Funcionario.Nome (Departamento * (Funcionario * Dependente)) Funcionario.CPF = Dependente.CPFGer
Departamento.CPFGer = Funcionario.CPF
Consultas envolvendo ligações de uma tabela consigo própria
- Tendo-se duas cópias de uma mesma tabela, é possível fazer comparações entre valores de campos de diferentes
tuplas da mesma tabela.
- Para fazer distinção entre duas referências a uma mesma tabela, introduz-se variáveis com nomes arbitrários,
sendo que cada uma delas varre uma cópia da tabela, estando a cada instante representando alguma linha da cópia
correspondente.
Exemplo: - Obter o nome dos funcionários que têm salário superior a algum outro funcionário, bem como o nome dos funcionários cujo salário é inferior e os salários correspondentes.
71
SELECT Primeiro.Nome, Primeiro.Salario, Segundo.Nome, Segundo.Salario
FROM Funcionario AS Primeiro, Funcionario AS Segundo
WHERE Primeiro.Salario > Segundo.Salario
ORDER BY Primeiro.Salario, Segundo.Nome
Tabela resultante: Primeiro.Nome Primeiro.Salario Segundo.Nome Segundo.Salario
João Nunes Silva 3000 Ahmad Veri Jabbar 2500 João Nunes Silva 3000 Alícia Jamil Zelaya 2500 João Nunes Silva 3000 Joyce Abreu Elias 2500 Ramesh Kaj Narayan 3800 Ahmad Veri Jabbar 2500 Ramesh Kaj Narayan 3800 Alícia Jamil Zelaya 2500 Ramesh Kaj Narayan 3800 João Nunes Silva 3000 Ramesh Kaj Narayan 3800 Joyce Abreu Elias 2500 Francisco Teles Natti 4000 Ahmad Veri Jabbar 2500 Francisco Teles Natti 4000 Alícia Jamil Zelaya 2500 Francisco Teles Natti 4000 João Nunes Silva 3000 Francisco Teles Natti 4000 Joyce Abreu Elias 2500 Francisco Teles Natti 4000 Ramesh Kaj Narayan 3800 Jenifer Souza Lopes 4300 Ahmad Veri Jabbar 2500 Jenifer Souza Lopes 4300 Alícia Jamil Zelaya 2500 Jenifer Souza Lopes 4300 Francisco Teles Natti 4000 Jenifer Souza Lopes 4300 João Nunes Silva 3000 Jenifer Souza Lopes 4300 Joyce Abreu Elias 2500 Jenifer Souza Lopes 4300 Ramesh Kaj Narayan 3800 Thiago Carvalho Pinto 5500 Ahmad Veri Jabbar 2500 Thiago Carvalho Pinto 5500 Alícia Jamil Zelaya 2500 Thiago Carvalho Pinto 5500 Francisco Teles Natti 4000 Thiago Carvalho Pinto 5500 Jenifer Souza Lopes 4300 Thiago Carvalho Pinto 5500 João Nunes Silva 3000 Thiago Carvalho Pinto 5500 Joyce Abreu Elias 2500 Thiago Carvalho Pinto 5500 Ramesh Kaj Narayan 3800
Correspondente na A.Relacional:
( Nome ( Primeiro ( Funcionario) ) ( Nome ( Segundo ( Funcionario ) ) )
Primeiro.Salario > Segundo.Salario
Usando funções de agregação
- A SQL fornece as seguintes funções de agregação:
COUNT: Conta o número de valores presentes em colunas/tabelas;
SUM: Soma os valores de uma coluna. Válido apenas sobre valores numéricos;
AVG: Calcula a média dos valores de uma coluna. Válido apenas para valores numéricos;
MAX: Retorna o maior valor que ocorre em uma coluna;
MIN: Retorna o menor valor de uma coluna;
- Quaisquer valores nulos que ocorram na coluna argumento são ignorados na aplicação das funções, exceto no
caso do COUNT(*), onde nulls são considerados como quaisquer outros valores.
- Se o argumento da função for um conjunto vazio, COUNT retorna um valor igual a 0, enquanto as outras funções
retornam null.
Exemplo: - Obter o número total de linhas na tabela Funcionario.
SELECT COUNT(*) AS TotalFuncionarios
FROM Funcionario;
72
Exemplo: - Obter o número total de funcionários com salários superiores a R$2500,00.
SELECT COUNT( *) AS BomSalarios
FROM Funcionario
WHERE Funcionario.Salario > 2500,00;
Exemplo: - Obter a soma dos salarios de todos os funcionários.
SELECT SUM(Salario) AS Total
FROM Funcionario;
Tabela resultante:
Total 28100
Usando a cláusula GROUP BY
- A cláusula GROUP BY rearranja logicamente a tabela especificada na cláusula FROM em grupos, tal que dentro
de cada um dos grupos todas as linhas tenham o mesmo valor no campo especificado em GROUP BY.
Exemplo: - Obter uma tabela com o CPF e o número de horas de trabalho de cada funcionário, levando em consideração todos os projetos em que o funcionário trabalha.
SELECT CPFFunc, SUM(Horas) AS Total
FROM TrabalhaEm
GROUP BY CPFFunc;
Tabela resultante:
CPFFunc Total
123456789 40
333445555 40
453453453 40
666884444 40
888665555
987654321 35
987987987 40
999887777 40
- Uma tabela pode ser agrupada por qualquer combinação de seus campos.
Usando a cláusula HAVING
- A cláusula HAVING é usada para eliminar grupos, da mesma forma que WHERE é usada para eliminar linhas
de uma tabela.
- A cláusula HAVING deve ser especificada após a especificação de uma cláusula GROUP BY.
Exemplo: - Obter o CPF de todos os funcionários que trabalham em mais de um projeto.
SELECT CPFFunc
FROM TrabalhaEm
GROUP BY CPFFunc
HAVING COUNT(*) > 1;
73
Tabela resultante:
CPFFunc
123456789
333445555
453453453
987654321
987987987
999887777
Usando LIKE e NOT LIKE
- O uso do LIKE na condição de uma cláusula WHERE tem a forma:
campo LIKE cadeia
onde campo deve designar um campo do tipo cadeia de caracteres.
- Para um dado registro a condição é avaliada como verdadeira se o valor do campo estiver em conformação com o
padrão especificado em cadeia.
- Os caracteres em cadeia são interpretados como:
No SQL ANSI: ‘_’ representa qualquer caracter único. Em Access usa-se o ‘?’;
No SQL ANSI: ‘%’ representa qualquer seqüência de n caracteres. Em Access usa-se o ‘*’.
Exemplo: - Obter nome, CPF e salário dos funcionários cujo nome se inicia com a letra J.
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Nome LIKE ‘J%’;
Tabela resultante:
Nome CPF Salario
Jenifer Souza Lopes 987654321 4300
João Nunes Silva 123456789 3000
Joyce Abreu Elias 453453453 2500
Exemplo: - Obter nome, CPF e salário dos funcionários que têm a letra i no seu nome.
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Nome LIKE '*i*';
Exemplo: - Obter nome, CPF e salário dos funcionários que NÃO têm a letra i no seu nome.
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Nome NOT LIKE '*i*';
Exemplo: - Obter nome, CPF e salário dos funcionários com, no mínimo, 5 letras no nome e cuja segunda letra é h.
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Nome LIKE ‘?h???*”;
74
Tabela resultante:
Nome CPF Salario
Ahmad Veri Jabbar 987987987 2500
Thiago Carvalho Pinto 888665555 5500
Extração de dados envolvendo valores NULL
- Quando um nulo é comparado a algum outro valor na avaliação de uma condição, qualquer que seja o operador
de comparação envolvido, o resultado é sempre um falso.
- Para testar a presença ou ausência de nulos dispõe-se de uma condição especial que tem a forma:
nome-campo IS [NOT] NULL
Exemplo: - Obter o nome de todos os funcionárioss sobre os quais se desconhece o CPF do
funcionário supervisor. SELECT Nome
FROM Funcionario
WHERE CPFSuper IS NULL;
Tabela resultante:
Nome
Thiago Carvalho Pinto
Subconsultas
- Uma subconsulta é uma expressão SELECT-FROM-WHERE-GROUP BY-HAVING que é aninhada dentro
de uma outra expressão.
- Subconsultas são usadas tipicamente para representar o conjunto de valores a serem procurados via uma condição
IN.
Subconsultas usando IN e NOT IN
- O sistema avalia a consulta total avaliando primeiro a subconsulta aninhada.
Exemplo: - Obter nome, CPF e salário dos funcionários que tenham dependentes.
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Funcionario.CPF IN
( SELECT CPFFunc
FROM Dependente);
Tabela resultante:
Nome CPF Salario
João Nunes Silva 123456789 3000
Francisco Teles Natti 333445555 4000
Jenifer Souza Lopes 987654321 4300
Exemplo: - Obter nome, CPF e salário dos funcionários que não tenham dependentes.
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Funcionario.CPF NOT IN
( SELECT CPFFunc
FROM Dependente);
75
Tabela resultante:
Nome CPF Salario
Ahmad Veri Jabbar 987987987 2500
Thiago Carvalho Pinto 888665555 5500
Alícia Jamil Zelaya 999887777 2500
Ramesh Kaj Narayan 666884444 3800
Joyce Abreu Elias 453453453 2500
Subconsultas com múltiplos níveis de aninhamento
- Subconsultas podem ser aninhadas em qualquer nível de profundidade.
Exemplo: - Obter o nome de todos os funcionários do sexo masculino que são gerentes de algum departamento que tenha algum projeto que seja executado no Prédio A.
SELECT Nome
FROM Funcionario
WHERE Sexo = 'M'
AND CPF IN
( SELECT CPFGer
FROM Departamento
WHERE DNumero IN
( SELECT DNum
FROM Projeto
WHERE Plocalizacao = 'Prédio A'));
Tabela resultante:
Nome
Francisco Teles Natti
Subconsultas com outros operadores de comparação além do IN
- Se é sabido que uma subconsulta particular deve retornar exatamente um valor, um operador de comparação
escalar simples, tal como: =, >, >=, <, <= pode ser usado no lugar do IN.
Exemplo: - Obter nome, CPF e salário de todos os funcionários cujo salário seja superior ao do funcionário cujo CPF é 333445555.
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Salario >
(SELECT Salario
FROM Funcionario
WHERE CPF = 333445555);
Tabela resultante:
Nome CPF Salario
Thiago Carvalho Pinto 888665555 5500
Jenifer Souza Lopes 987654321 4300
Subconsultas usando SOME e ANY
Exemplo: - Obter nome, CPF e salário de todos os funcionários do sexo feminino cujo salário seja superior ao de algum funcionário do sexo masculino.
76
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Sexo = ‘F’ and
Salario > SOME
(SELECT Salario
FROM Funcionario
WHERE Sexo = ‘M’);
Tabela resultante:
Nome CPF Salario
Jenifer Souza Lopes 987654321 4300
- A palavra-chave ANY é sinônimo de SOME.
- São permitidas quaisquer comparações: < SOME, <= SOME, > SOME, >= SOME, = SOME, <> SOME.
- = SOME é equivalente a IN.
Subconsultas usando ALL
Exemplo: - Obter nome, CPF e salário dos funcionários que possuem salário menor do que todos os funcionários que são gerentes de algum departamento.
SELECT Nome, CPF, Salario
FROM Funcionario
WHERE Salario < ALL
( SELECT Salario
FROM Funcionario
WHERE CPF IN
(SELECT CPFGer
FROM Departamento));
- São permitidas quaisquer comparações: < ALL, <= ALL, > ALL, >= ALL, = ALL, <> ALL.
Usando o EXISTS
- A expressão “EXISTS (SELECT... FROM... WHERE...)” é avaliada como verdadeira se o resultado da
avaliação do “SELECT... FROM... WHERE...” não for vazio, ou seja, se existir um registro na tabela
especificada na cláusula FROM que satisfaça a condição WHERE da subconsulta.
Exemplo: - Obter nome e CPF de todos os funcionários que possuem mais do que um dependente.
SELECT Nome, CPF
FROM Funcionario
WHERE EXISTS
(SELECT Dependente.CPFFunc
FROM Dependente
WHERE Dependente.CPFFunc = Funcionario.CPF
GROUP BY Dependente.CPFFunc
HAVING COUNT(*) > 1)
77
Tabela resultante:
Nome CPF
João Nunes Silva 123456789
Francisco Teles Natti 333445555
Usando o NOT EXISTS
Exemplo: - Obter nome e CPF de todos os funcionários que não possuem mais do que um
dependente.
SELECT Nome, CPF
FROM Funcionario
WHERE NOT EXISTS
(SELECT Dependente.CPFFunc
FROM Dependente
WHERE Dependente.CPFFunc = Funcionario.CPF
GROUP BY Dependente.CPFFunc
HAVING COUNT(*) > 1)
Tabela resultante:
Nome CPF
Ahmad Veri Jabbar 987987987
Thiago Carvalho Pinto 888665555
Alícia Jamil Zelaya 999887777
Jenifer Souza Lopes 987654321
Ramesh Kaj Narayan 666884444
Joyce Abreu Elias 453453453
Consultas envolvendo UNION
- UNION é o operador união tradicional da teoria de conjuntos.
- Linhas duplicadas redundantes são sempre eliminadas do resultado de uma UNION.
Exemplo: - Obter nome, salário e número do departamento daqueles funcionários que trabalhem para o departamento de número 4 ou que tenham salário superior a 4000.
SELECT Nome, Salario, NDep
FROM Funcionario
WHERE NDep = 4
UNION
SELECT Nome, Salario, NDep
FROM Funcionario
WHERE Salario > 4000;
Tabela resultante:
Nome Salario NDep
Ahmad Veri Jabbar 2500 4
Alícia Jamil Zelaya 2500 4
Jenifer Souza Lopes 4300 4
Thiago Carvalho Pinto 5500 1
78
Exercícios sobre SQL
Exercícios 1 – Comando CREATE TABLE e INSERT
1) Criar as tabelas Mineral, Pais e PaisMineral, conforme especificação a seguir no banco de dados
ProducaoMinerais.
Digitar: Use ProducaoMinerais
2) Em seguida, inserir os seguintes dados:
Pais
Nome pib
Brasil 756.2
Argentina 377.5
Chile 223.7
Peru 232.4
Bolívia 223.7
México 613.6
Estados Unidos 14300
Mineral
nome valor
Ouro 43600
Prata 500
Bronze 250.35
Paladio 24200
Platina 86500
Cobre 12.5
Estanho 31.53
PaisMineral
nomePais nomeMineral reserva producao
Estados Unidos Ouro 1000000 240
Peru Ouro 500000 170
Argentina Ouro 30000 40
Brasil Ouro 20000 37
México Prata 2800000 2748
Peru Prata 2000000 2200
Argentina Prata 450000 300
Estados Unidos Platina 1200 4.39
Estados Unidos Paladio 2300 12.26
Chile Cobre 2800000 5360.8
Peru Cobre 750000 980.6
Brasil Estanho 42300 15
Argentina Estanho 5200 5
Pais (nome, pib)
Mineral(nome, valor)
PaisMineral (nomePais, nomeMineral, reserva, producao)
CE(Pais) CE(Mineral)
79
3) Para visualizar o conteúdo das tabelas digite:
select * from Pais
e faça o mesmo para as outras duas tabelas.
4) Tente deletar uma linha da tabela Pais, digitando, por exemplo:
delete from Pais where nome = ‘Brasil’
e anote o que acontece. Explique.
5) Explique o conceito de integridade referencial.
Exercícios 2 – ALTER TABLE, INSERT, DELETE, UPDATE
Para as questões a seguir, escreva os comandos SQL solicitados e responda às perguntas:
1. a) Crie uma tabela cliente com o comando a seguir:
create table cliente(id int identity, nome varchar(40))
b) Pesquise:
O que significa a palavra identity após a definição de tipo int do campo id? Para que serve?
Trata-se de um termo específico do MS SQL-Server ou é utilizado também em outros SGBDs?
2. a) Altere a tabela cliente, adicionando uma nova coluna chamada cpf , que deve ser varchar(13) not null default '':
b) Explique qual é a função da restrição default ''.
3. Altere a tabela cliente, adicionando uma nova coluna chamada endereco, que deve ser do tipo varchar(50).
4. Altere a tabela cliente excluindo o campo nome.
5. a) Altere a tabela cliente adicionando de volta o campo nome, agora como varchar(30).
b) Ao invés de excluir o campo nome e, em seguida, adicioná-lo de novo à tabela com uma outra definição de tipo,
que outra alternativa de comando você poderia usar para efetuar esta alteração?
6. Altere a tabela cliente adicionando o campo numFilhos, do tipo int.
7. a) Insira na tabela cliente um registro com nome=’Marta’, CPF=’12345678911’ e numFilhos = -1.
b) Qual é o valor que o campo id assume para a cliente Marta?
8. a) Altere a tabela, impondo uma restrição no campo numFilhos, de tal forma que ele só possa aceitar valores
maiores ou iguais a zero. Para isso você terá que utilizar a palavra reservada CHECK.
b) Você consegue executar este comando? Escreva o que acontece e explique o por quê.
9. a) Tente novamente, agora incluindo a cláusula WITH NOCHECK. Ou seja, altere a tabela, impondo uma restrição
sobre o campo numFilhos, de tal forma que ele só possa aceitar valores maiores ou iguais a zero, com o seguinte
comando:
alter table cliente WITH NOCHECK add constraint restr1 check(numFilhos >= 0)
b) Explique qual é a função da cláusula WITH NOCHECK.
80
10. a) Insira na tabela cliente um registro com nome=’Pedro’, CPF=’22335578911’ e numFilhos = -1.
b) O que acontece ao tentar executar este comando de inserção? Explique o por quê.
11. Insira na tabela cliente um registro com nome=’Pedro’, CPF=’22335578911’ e numFilhos = 0.
12. Insira 10 registros válidos na tabela, com números de filhos entre 0 e 10.
13. Delete da tabela o registro correspondente ao cliente cujo id seja igual a 5.
14. a) Delete da tabela todos os registros correspondentes a pessoas com numFilhos igual a 10. Mesmo que não exista
nenhum cliente nesta situação, escreva qual seria o comando necessário para efetuar esta operação.
b) No seu caso, quantas linhas da tabela foram afetadas por este comando?
15. Suponha que, por uma grande coincidência, todos os clientes tenham tido um filho no último ano. Escreva o
comando necessário para fazer a atualização do número de filhos de todos os clientes a uma só vez.
16. Explique e exemplifique:
a) O que é integridade referencial?
b) O que é integridade de domínio?
Exercícios 3 – Comando SELECT
Caso ainda não tenham sido criadas, criar as tabelas Mineral, Pais e PaisMineral, conforme especificação a seguir
no banco de dados ProducaoMinerais.
Utilize os seguintes comandos:
create database ProducaoMinerais
use ProducaoMinerais
create table Mineral(nome varchar(15) primary key, preco real)
create table Pais(nome varchar(30) primary key, PIB real)
create table PaisMineral(nomePais varchar(30) not null, nomeMineral varchar(15) not null,
reserva real, producao real, primary key(nomePais, nomeMineral),
foreign key(nomePais) references Pais(nome), foreign key(nomeMineral) references Mineral(nome))
Em seguida, copiar e executar as seguintes instruções de inserção:
insert into Pais values('Brasil', 756.2)
insert into Pais values('Argentina', 377.5)
insert into Pais values('Chile', 223.7)
insert into Pais values('Peru', 232.4)
insert into Pais values('Bolívia', 223.7)
insert into Pais values('México', 613.6)
Pais (nome, pib)
Mineral(nome, valor)
PaisMineral (nomePais, nomeMineral, reserva, producao)
CE(Pais) CE(Mineral)
81
insert into Pais values('Estados Unidos', 14300)
insert into Mineral values('Ouro', 43600)
insert into Mineral values('Prata', 500)
insert into Mineral values('Bronze', 250.35)
insert into Mineral values('Paladio', 24200)
insert into Mineral values('Platina', 86500)
insert into Mineral values('Cobre', 12.5)
insert into Mineral values('Estanho', 31.53)
insert into PaisMineral values('Estados Unidos', 'Ouro', 1000000, 240)
insert into PaisMineral values('Peru', 'Ouro', 500000, 170)
insert into PaisMineral values('Argentina', 'Ouro', 30000, 40)
insert into PaisMineral values('Brasil', 'Ouro', 20000, 37)
insert into PaisMineral values('México', 'Prata', 2800000, 2748)
insert into PaisMineral values('Peru', 'Prata', 2000000, 2200)
insert into PaisMineral values('Argentina', 'Prata', 450000, 300)
insert into PaisMineral values('Estados Unidos', 'Platina', 1200, 4.39)
insert into PaisMineral values('Estados Unidos', 'Paladio', 2300, 12.26)
insert into PaisMineral values('Chile', 'Cobre', 2800000, 5360.8)
insert into PaisMineral values('Peru', 'Cobre', 750000, 980.6)
insert into PaisMineral values('Brasil', 'Estanho', 42300, 15)
insert into PaisMineral values('Argentina', 'Estanho', 5200, 5)
1) Forma mais comum do comando select (select-from-where). Selecionar os nomes de todos os países que
tenham um pib maior que 300.
2) Omitindo a cláusula where no comando select (select-from). Selecionar os pibs de todos os países.
3) Palavra-chave distinct (select-from). Obter apenas os pibs distintos entre si.
4)Extraindo valores calculados. O PIB está expresso em unidades de bilhões de dólares. Obtenha o nome do país
e o pib expresso em bilhões de reais, sabendo que a taxa de conversão de dólar para real é: 1 dolar = 1,8 reais
5) Selecionando todos os campos de uma tabela. Obter todos os dados da tabela PaisMineral.
6)Condição composta na cláusula where. Obter todos os campos da tabela PaisMineral apenas para os casos em
que a reserva é maior que 400000 e a producao é maior que 200.
7) Extraindo dados ordenados. Obter todos os dados da tabela PaisMineral:
a) Ordenados em ordem crescente do nome do mineral.
b) Ordenados em ordem decrescente do nome do país.
8) Consultas envolvendo mais que uma tabela (inner join). Obter uma listagem dos nomes dos países e dos
minerais que produzem, com o valor de cada mineral.
/* Em uma consulta do tipo inner join somente registros das duas tabelas que possuírem o mesmo valor para um
determinado campo (FK-PK) são retornados. */
82
9) Consultas envolvendo mais que uma tabela (right join). Obter uma listagem dos nomes dos países e dos
minerais que produzem, com o valor de cada mineral. Listar inclusive aqueles minerais que não são produzidos por
nenhum país.
10) Outras condições, além da condição de ligação na cláusula where. Obter uma listagem dos nomes dos
países e dos minerais que produzem, com o valor de cada mineral, apenas para aqueles minerais cujo quilo vale
mais do que 100 dólares.
11) Ligação de uma tabela consigo própria. Obter o nome e o valor dos minerais que têm valor superior ao de
algum outro mineral, bem como o nome e o valor do mineral cujo valor é inferior. Ordenar, em primeiro lugar,
pelos minerais de valor superior em ordem decrescente e, em segundo lugar, pelos minerais de valor inferior, em
ordem decrescente.
12) Ligação de três tabelas. Obter os nomes dos países e dos minerais que eles produzem, bem como os pibs dos
países e os valores dos minerais.
13) Ligação de três tabelas. Obter os nomes dos países e dos minerais que eles produzem, bem como os pibs dos
países e os valores dos minerais. Listar também tanto aqueles minerais para os quais não consta nenhum país que
os produza e aqueles países para os quais não consta nenhum mineral que eles produzam.
14) Função count. Contar o número de Países que produzem Ouro.
15) Função avg. Obter a média do pib dos países cadastrados.
16) Função max. Obter o maior pib dentre os países cadastrados.
17) Função min. Obter o menor valor dentre os minerais cadastrados.
18) Função soma. Obter a soma dos valores de todos os minerais cadastrados.
19) Cláusula GROUP BY. Obter o nome de todos os países que produzem minerais e a quantidade de minerais
que eles produzem.
Obter o nome de todos os minerais produzidos por algum país e a reserva total destes minerais considerando todos
os países que os produzem.
20) Cláusula GROUP BY com HAVING. Obter o nome de todos os países que produzem minerais e a
quantidade de minerais que eles produzem apenas para aqueles países que produzem mais de um mineral.
21) Usando o LIKE e o NOT LIKE. Obter o nome dos minerais que se iniciam com a letra P.
Obter o nome dos países que não possuem a letra a no seu nome.
Obter o nome dos países que possuem exatamente 5 letras no seu nome.
22) Extração de dados envolvendo valores NULL. Após executar a seguinte inserção:
insert into Mineral(Nome) values('Manganês')
obtenha uma listagem dos minerais cujo valor é NULL.
23) Subconsultas usando IN e NOT IN. Obter os nomes dos minerais e seus valores apenas para aqueles que
constam serem produzidos por algum país:
83
Obter os nomes dos minerais e seus valores apenas para aqueles que não constam serem produzidos por algum
país:
24) Subconsultas com outros operadores de comparação além do IN. Obter os nomes dos minerais e seus
valores apenas para aqueles que possuem um valor superior ao do Ouro:
25) Subconsultas usando SOME ou ANY. Obter os nomes dos minerais apenas para aqueles que possuem uma
reserva maior ou igual ao de algum dos minerais produzidos no Chile (e depois Peru):
26) Subconsultas usando ALL. Obter os nomes dos minerais apenas para aqueles que possuem uma reserva
superior ao de todos os minerais produzidos nos Estados Unidos:
27) Subconsultas com múltiplos níveis de aninhamento. Obter os nomes dos países e seus pibs apenas para
aqueles que possuem um pib maior do que algum dentre os países que produzem Ouro:
28) Subconsultas usando EXISTS e NOT EXISTS. Obter os nomes dos minerais e seus valores apenas para
aqueles que constam serem produzidos por algum país:
Obter os nomes dos minerais e seus valores apenas para aqueles que não constam serem produzidos por algum
país:
29) Subconsultas com UNION. Obter uma listagem dos minerais que são produzidos pelos Estados Unidos ou
pelo Brasil:
Algumas Questões de Concursos Públicos
1) (FCC - 2010 - TRT - 22ª Região (PI) - Técnico Judiciário - Tecnologia da Informação)
No Modelo Relacional de Dados, o mecanismo fundamental para manter a consistência dos dados e evitar registros
órfãos é denominado
a) Integridade Relacional.
b) Normalização.
c) Integridade Referencial.
d) Dependência Funcional.
e) Integridade de Chave.
2) (FGV - 2009 - MEC - Administrador de Banco de Dados) As restrições de integridade resguardam o Banco de
Dados contra danos acidentais, assegurando que mudanças feitas por usuários autorizados não resultem na perda de
consistência de dados.
A restrição de integridade, na qual um valor que aparece em uma relação para um determinado conjunto de
atributos aparece também em outro conjunto de atributos em outra relação (tabela), é conhecida por:
a) Integridade de Duplicação.
b) Integridade de Domínio.
c) Integridade Referencial.
d) Integridade de Chave.
e) Integridade de Vazio.
3)(FCC - 2010 - MPE-RN - Analista de Tecnologia da Informação - Banco de Dados)
NÃO se trata de uma operação executada com a linguagem de manipulação de dados:
a) Especificação do esquema do banco de dados.
b) Recuperação da informação armazenada.
c) Inserção de novas informações.
84
d) Exclusão de informações.
e) Modificação de dados armazenados.
4) (FGV - 2008 - Senado Federal - Analista de Suporte de Sistemas)
No banco de dados do Senado Federal, que contém as tabelas FUNCIONARIOS e ANALISTAS, ao se cadastrar
um Analista é feita a verificação se a matrícula do funcionário que foi digitada já existe na tabela
FUNCIONARIOS. Se não existir, o cadastro do Analista não será aceito. Entretanto, existe um recurso que garante
a execução das seguintes tarefas:
I. Quando a matrícula do funcionário for alterada na tabela FUNCIONARIOS, pode-se configurar para o banco de
dados atualizar, automaticamente, todos as tuplas de matrículas dos funcionários na tabela ANALISTAS, de tal
forma que não fiquem registros órfãos, isto é, registros de ANALISTAS com uma matrícula do funcionário para o
qual não existe mais um correspondente na tabela FUNCIONARIOS. Esse procedimento é conhecido como
"Propagar atualização dos campos relacionados".
II. Quando uma matrícula do funcionário for excluída da tabela FUNCIONARIOS, pode-se configurar para que o
banco de dados exclua, automaticamente, na tabela ANALISTAS, toda tupla de Analista para o funcionário que
está sendo excluído. Esse procedimento é conhecido como "Propagar exclusão dos registros relacionados".
O recurso acima mencionado é denominado Integridade:
a) referencial.
b) globalizada.
c) consistente.
d) particionada.
e) segmentada.
5) (FCC - 2009 - TJ-PA - Analista Judiciário - Análise de Sistemas – Desenvolvimento) A proposição "um banco
de dados não pode conter valores de chaves estrangeiras sem correspondentes" remete ao conceito de
a) restrição de atributo.
b) restrição de tipo.
c) unicidade.
d) irredutibilidade.
e) integridade referencial.
6) (Prova: CESGRANRIO - 2010 - Petrobrás - Analista de Sistemas Júnior - Infra-Estrutura) Seja o seguinte
esquema de banco de dados.
Cliente(IdCliente: Integer, Nome: Varchar(120));
Pedido(IdPedido: Integer, Dia: Integer, Mes: Integer, Ano: Integer);
Cliente_Pedido(IdCliente: Integer referencia Cliente(IdCliente), IdPedido: Integer referencia Pedido(IdPedido));
Item(IdProduto: Integer referencia Produto(IdProduto), IdPedido: Integer, Quantidade: Integer);
Produto(IdProduto: Integer, Nome: Varchar(100));
Considere que os atributos sublinhados correspondam à chave primária da respectiva relação e os atributos que são
seguidos da palavra "referencia" sejam chaves estrangeiras. Considere ainda que existam as seguintes tuplas no
banco de dados:
Cliente_Pedido(30, 2);
Cliente_Pedido(15, 3);
Produto(2, "Caneta");
Produto(4, "Caderno");
Qual opção apresenta somente tuplas válidas para o esquema apresentado?
85
a) Pedido (30, 2, 2, 2010);
Item (30, 4, 2);
b) Pedido (2, 1, 3, 2010);
Cliente (4, "Marcelo de Almeida");
c) Cliente (30, "Simone Cunha");
Item (2, 2, 15);
d) Cliente (2, "Pedro Santos");
Cliente (3, "Marta Ramos");
e) Item (2, 3, 5);
Item (3, 4, 10);
7) (UFPR - 2010 - UFPR - Analista de Tecnologia da Informação) No modelo relacional de administração de
dados, uma relação pode ter um conjunto de atributos que contém valores com mesmo domínio de um conjunto de
atributos que forma a chave primária de uma outra relação. Esse conceito refere-se à:
a) integridade de chave.
b) integridade de atributo.
c) integridade de entidades.
d) integridade referencial.
e) integridade de tuplas
