depósito - Blog das Exatas

Notas de Aula Banco de Dados
André Luís Duarte
Sistema de Informação
FEPI – 2014
Banco de Dados
Sumário
Capítulo 1 – Projeto Lógico de Banco de Dados .......................................................... 5
1.1 – Introdução – Sistema: Uma Visão Geral ............................................................ 5
1.2 – Modelagem Lógica de Sistemas .......................................................................... 7
1.3 – Banco de Dados – Conceitos ............................................................................... 8
1.4 – Sistemas de Banco de Dados e Modelos de Dados ............................................. 9
1.5 – Modelagem e seus objetivos ................................................................................ 9
Capítulo 2 – Modelo Conceitual .................................................................................. 11
Modelo Entidade-Relacionamento e Seus Conceitos ................................................. 11
2.1 – Abstração e Modelagem de Dados .................................................................... 11
2.2 – O Método Entidade-Relacionamento ................................................................ 12
2.3 Entidades e Conjuntos de Entidades ..................................................................... 13
2.4 – Atributos ............................................................................................................ 14
2.4.A – Atributos Simples e Compostos................................................................. 15
2.4.B – Atributos Multivalorados ........................................................................... 15
2.4.C – Atributos Opcionais ou Mandatário .......................................................... 16
2.4.D – Atributos Determinantes ou Identificadores .............................................. 16
2.5 – Relacionamentos e Classes de Relacionamentos............................................... 16
2.6 – Auto-Relacionamento ........................................................................................ 19
2.7 – Atributos de Relacionamentos ........................................................................... 21
2.8 – Relacionamentos Parciais e Totais .................................................................... 21
2.9 – Relacionamentos Múltiplos ............................................................................... 21
2.10 – Agregações ...................................................................................................... 22
2.11 – Generalização / Especialização ....................................................................... 25
2.12 – Chaves ............................................................................................................. 26
2.13 – Conjunto de Entidades Forte e Fraco .............................................................. 26
2.14 – Restrições de Mapeamento .............................................................................. 27
2.15 – Reduzindo o Diagrama E/R a tabelas .............................................................. 28
2.15.1 – Representação de Conjuntos de Entidades Fortes ................................... 28
2.15.2 – Representação de Conjuntos de Entidades Fracos .................................. 29
2.15.3 – Representação de Conjuntos de Relacionamentos ................................... 30
Capítulo 3 – Modelo Relacional .................................................................................. 32
3.1 – Introdução .......................................................................................................... 32
3.2 – Estrutura de Banco de Dados Relacionais ......................................................... 32
3.2.1 – Estrutura básica ......................................................................................... 32
3.2.2 – Esquema de Banco de Dados ..................................................................... 33
3.2.3 – Linguagens de Consulta ............................................................................. 36
3.3 – Álgebra Relacional ............................................................................................ 37
3.3.1 – Operações Fundamentais ........................................................................... 39
3.3.2 Definição Formal da Álgebra Relacional ..................................................... 47
3.3.3 – Operações Adicionais ................................................................................. 47
3.4 – Modificação no Banco de Dados com Álgebra Relacional ............................... 54
3.4.1 – Remoção ..................................................................................................... 54
3.4.2 – Inserção ...................................................................................................... 55
3.4.3 – Atualização ................................................................................................. 56
3.5 – Visões de Usuário .............................................................................................. 57
3.5.1 – Definição de Visão ..................................................................................... 58
3.5.2 – Atualizações com Visões e Valores Vazios ................................................. 59
André Luís Duarte
2
[email protected]
Capítulo 4 – Linguagem Relacional Comercial ......................................................... 62
4.1 – Introdução .......................................................................................................... 62
4.2 – SQL.................................................................................................................... 62
4.2.1 – Histórico ..................................................................................................... 62
4.2.2 – Estrutura básica ......................................................................................... 63
4.2.3 – Operações de conjuntos e tuplas duplicadas ............................................. 64
4.2.4 – Operações com conjuntos........................................................................... 64
4.2.5 – Predicados e junções .................................................................................. 66
4.2.6 – Pertinência a conjuntos .............................................................................. 68
4.2.7 – Variáveis tupla............................................................................................ 69
4.2.8 – Comparação de conjuntos .......................................................................... 70
4.2.9 – Teste de relações vazias ............................................................................. 71
4.2.10 – Ordenação da exibição de tuplas ............................................................. 73
4.2.11 – Funções agregadas ................................................................................... 73
4.2.12 – A potência de SQL .................................................................................... 75
4.2.13 – Modificação do Banco de Dados.............................................................. 75
4.2.13.1 – Remoção (delete) ................................................................................... 76
4.2.13.2 – Inserção (insert)..................................................................................... 77
4.2.13.2 – Atualização (update) .............................................................................. 78
4.2.14 – Valores Vazios .......................................................................................... 79
4.2.15 – Visões ........................................................................................................ 80
4.2.16 – Definição de Dados .................................................................................. 81
Capítulo 5 – Restrições de Integridade ....................................................................... 83
5.1 – Introdução .......................................................................................................... 83
5.2 – Restrições de domínio ....................................................................................... 83
5.2.1 – Tipos de Domínio ........................................................................................... 83
5.2.2 – Tipos de domínios em SQL ......................................................................... 84
5.2.3 – Valores vazios ............................................................................................. 84
5.3 – Integridade Referencial...................................................................................... 85
5.3.1 – Conceitos básicos ....................................................................................... 85
5.3.2 – Integridade Referencial em SQL ................................................................ 86
5.4 – Asserções ........................................................................................................... 87
5.5 – Gatilhos (TRIGGERS) ...................................................................................... 88
Capítulo 6 – Normalização (ou normatização) .......................................................... 90
6.1 – Introdução .......................................................................................................... 90
6.2 – Conceitos ........................................................................................................... 91
6.2.1 Revisão dos conceitos relevantes .................................................................. 93
6.2.2 Dependência Funcional (DF) ........................................................................ 93
6.2.2.1 Trivialidade ................................................................................................ 95
6.2.2.2 Transitividade ............................................................................................. 95
6.2.2.3 Dependência funcional irredutível à esquerda .......................................... 95
6.2.2.4 Anomalias de atualização........................................................................... 96
6.2.2 Decomposição sem perdas ............................................................................ 98
6.2.3 Junção (Join) ................................................................................................. 99
6.2.4 Projeções ....................................................................................................... 99
6.3 Importância da Normalização ............................................................................... 99
6.4 Normalização estudo de caso ............................................................................... 99
6.4.1 1ª Forma normal (1FN) ............................................................................... 101
6.4.1.1 Normalização e atributos compostos ....................................................... 102
6.4.2 2ª Forma Normal (2FN) .............................................................................. 103
3
André Luís Duarte
Banco de Dados
6.4.3 3ª Forma Normal (3FN) .............................................................................. 105
Observações sobre as anomalias ......................................................................... 107
5.4.3.4 Forma Normal de Boyce/Codd (BCNF) ................................................... 108
6.5 Normalização e integridade ................................................................................ 108
6.6 Desnormalização ................................................................................................ 108
Bibliografia .................................................................................................................. 111
André Luís Duarte
4
[email protected]
Capítulo 1 – Projeto Lógico de Banco de Dados
1.1 – Introdução – Sistema: Uma Visão Geral
Um Banco de Dados (BD) consiste em uma coleção de dados que possuem algum
relacionamento entre si. O conjunto de software e “hardware” utilizados a fim de prover
o acesso seguro e manipulação dos dados do BD, é conhecidos como Sistema
Gerenciador de Banco de Dados (SGBD). O conjunto formado pelo Banco de Dados e o
Sistema Gerenciador de Banco de Dados, é comumente chamado de Sistema de Banco
de Dados (SBD).
O objetivo principal da utilização de um sistema de banco de dados é prover um
ambiente seguro e eficiente na manipulação das informações dos sistemas clientes,
podendo ser tanto o armazenamento quanto à recuperação destas informações.
Um banco de dados possui as seguintes características:
 é uma coleção lógica e coerente de dados que possuem um significado
intrínseco;
 é projetado, construído e populado com dados que tenham um propósito
específico;
 representa algum aspecto (parte) do mundo real;
TABELAS
LÓGICAS
INFORMAÇÕES PARA
O USUÁRIO
BANCO DE DADOS
(Arquivo Físico)
Figura1.1 – Representação física de um banco de dados
Uma característica importante da abordagem Banco de Dados é que os SGBD mantêm
não somente os dados, mas também a forma como os mesmos se encontram
armazenados (estrutura de cada arquivo, tipo e o formato de armazenamento de cada
dado, restrições, etc) em um local chamado catálogo do SGBD. Estas informações são
5
André Luís Duarte
Banco de Dados
conhecidas como “meta dados” e juntamente com os dados em si provêm uma descrição
completa do banco de dados.
No processamento tradicional de arquivos isto não ocorre, já que a estrutura de
armazenamento e manipulação deve estar descrita no próprio código do programa de
manipulação. Com isto, uma alteração na estrutura de armazenamento ou no próprio
dado, vai obrigar a alteração de todo o código responsável pela manipulação deste dado.
Programas de Aplicação / Consulta
Software para processar manipulação
Sistema de Banco
de Dados
SGBD
Software de Acesso aos Dados
Meta Dados
Dados
Figura 1.2 - Um ambiente de Sistema de Banco de Dados
A adoção ou não de um sistema de banco de dados deve levar em conta algumas
questões. Podemos citar algumas vantagens e desvantagens na utilização de um SBD:
 Vantagens:
-
Controle de redundância: em um sistema tradicional de arquivos, o
armazenamento e processamento de informações redundantes geram uma
perda na qualidade, no tempo e na facilidade de atualizações destes sistemas;
-
Compartilhamento de dados: um SGBD pode fornecer acesso
concorrente aos dados do sistema, permitindo que várias aplicações acessem
aos dados simultaneamente;
-
Restrição a acessos não autorizados: um SGBD pode possuir sub-
sistemas que controlem o acesso não autorizado aos dados armazenados;
André Luís Duarte
6
[email protected]
-
Tolerância a falhas: um Sistema de Banco de Dados pode fornecer
recursos rápidos e seguros para tratar possíveis falhas tanto de software
quanto de hardware, garantindo assim a integridade das informações;
 Desvantagens:
-
Alto investimento inicial: muitas vezes o custo gerado na adoção de um
Sistema de Banco de Dados pode não ser atrativo a fim de substituir um
sistema de arquivos tradicional;
-
Sobrecarga no gerenciamento: normalmente ocorre uma sobrecarga na
provisão de controle de segurança, controle de concorrência, recuperação e
integração de funções;
-
Necessidade de treinamento e aperfeiçoamento de profissionais: este
item é essencial, pois de nada adianta adotarmos um sistema complexo e
moderno e se não temos um administrador capacitado para sua manipulação;
O desenvolvimento correto do projeto de um sistema de banco de dados que atenda as
exigências de segurança e eficiência, é talvez, a parte principal em projetos de empresas
ligadas ao desenvolvimento de sistemas, pois geram grandes custos em casos de revisão
e correção.
Para que o desenvolvimento possa ocorrer de forma segura, é necessário que se adote e
utilize metodologias e técnicas que minimizem custos e gastos gerados por falhas no
projeto.
1.2 – Modelagem Lógica de Sistemas
A modelagem lógica de sistemas consiste no encadeamento lógico dos componentes do
sistema com a finalidade de prover ao analista, ferramentas para análise e entendimento
do funcionamento real do sistema modelado.
Esta modelagem é um importante meio de comunicação entre a equipe de analistas e o
cliente para que toda a estrutura lógica do sistema seja “mapeada” de forma correta,
possibilitando assim a identificação e correção de erros e mal entendidos em fases
anteriores ao desenvolvimento.
7
André Luís Duarte
Banco de Dados
CAA
CBB
ABC
AAA
BBB
CCC
ABC
BAA
CBC
CCC
AAA
BBB
Figura 1.3 – Necessidade da modelagem na comunicação
A modelagem lógica abrange todos os elementos existentes em um SGBD (softwares,
redes, discos, estruturas, dados, meta dados, etc).
1.3 – Banco de Dados – Conceitos
 Banco de Dados: é o arquivo físico, em dispositivos periféricos, onde estão
armazenados os dados de um ou mais sistemas a fim de serem manipulados pelo sistema
cliente.
 Sistema Gerenciador de Banco de Dados: é o conjunto de software e hardware
utilizados no gerenciamento (armazenamento e recuperação) dos dados no banco de
dados.
 Dado: é o valor do campo quando armazenado no banco de dados. (Ex. o valor do
campo “Nome do Cliente” para quem se está fazendo a entrada dos dados).
 Conteúdo do Campo: é o valor do campo armazenado no banco de dados. (Ex. o
valor do campo “Nome do Cliente”, sem ser, momentaneamente utilizado, armazenado
no banco de dados).
 Informação: é o valor que este campo representa para as atividades da empresa. (Ex.
qual o nome dos clientes localizados em Itajubá. A resposta a esta consulta é a
informação).
André Luís Duarte
8
[email protected]
 Modelo de banco de dados: representa a estrutura física no qual o armazenamento
dos dados foi projetado. Pode ser modelo relacional, modelo hierárquico e modelo em
rede.
1.4 – Sistemas de Banco de Dados e Modelos de Dados
Foi discutido anteriormente no que um sistema de banco de dados deve ser o mais
seguro e confiável possível e que este deve ser projetado de forma a evitar erros e
correções que gerem um custo adicional muito grande ao projeto.
Para efetuar este planejamento, devemos primeiramente identificar e definir como as
entidades deste sistema serão constituídas. Esta imagem gráfica de toda base de
informações necessárias para a construção de um determinado sistema, pode ser
denominada por “Modelos de Dados” e pode possuir vários níveis de abstração.
Os modelos de dados podem ser basicamente de dois tipos:
 Alto nível: ou modelo de dado conceitual, possibilita uma visão mais próxima do
mundo real. É o modo como o usuário visualiza os dados realmente.
 Baixo nível: ou modelo de dado físico, fornece uma visão mais detalhada de
como os dados estão realmente armazenados.
1.5 – Modelagem e seus objetivos
A organização atual de algumas corporações de negócios possibilita, em uma análise
mais abrangente, a identificação de alguns elementos chaves para o bom andamento dos
negócios. Isso permite que regras de negócio sejam elaboradas e isto sirva como pedras
fundamentais para orientar as atividades de toda a organização.
Estas definições estratégicas fazem parte da política de negócio adotado pelas empresas,
bem como pelas características próprias do ramo de atividade de cada uma delas.
Alguns exemplos destas definições podem ser denominados como “regras de negócios”
e são listadas abaixo:
 Levantamento das personagens;
 Eventos e procedimentos envolvidos com o dia a dia da organização;
 Características particulares a cada um destes elementos
 Relacionamento entre eles;
9
André Luís Duarte
Banco de Dados
 Objetivos finais da organização;
 Prioridades da empresa; etc
Essa tarefa de levantamento e estruturação dos dados segundo as regras de negócio de
determinada empresa é denominada de “Modelagem de Dados”. Essa modelagem
ocorre em níveis diferentes de abstração contemplando desde a visão geral dos negócios
até os detalhes de uma parte específica do conjunto, gerando assim uma visão com
vários subsistemas.
Podemos definir então que o objetivo da modelagem de dados é definir o contexto dos
dados em que os diversos sistemas funcionam. Por causa disto, o conteúdo produzido
pela modelagem de dados deve ser o mais fiel possível ao sistema real, e suas
especificações não devem implicar em nenhuma implementação física em particular
evitando assim viciar a análise pela restrição do ponto de vista do analista.
André Luís Duarte
10
[email protected]
Capítulo 2 – Modelo Conceitual
Modelo Entidade-Relacionamento e Seus Conceitos
2.1 – Abstração e Modelagem de Dados
Como discutido no capítulo anterior, chamamos de modelagem de dados a tarefa de
levantamento e estruturação dos dados segundo as regras de negócio de determinada
empresa. Como cada entidade pode utilizar estas informações de diferentes maneiras, o
conjunto gerado nesta análise deve possibilitar vários níveis de visões, a fim de obter
um mapa completo do modelo de dados.
A partir deste ponto podemos então definir “Modelos de Dados” como sendo uma
abstração de dados que é utilizada para fornecer a representação conceitual utilizando
conceitos lógicos como objetos, suas propriedades e seus relacionamentos. A estrutura
detalhada e a organização de cada arquivo são descritas no catálogo do banco de dados.
A Figura2.1 mostra um exemplo de projeto lógico de banco de dados onde as
informações vão sofrendo análises até gerar um modelo conceitual que represente com
fidelidade toda estrutura do minimundo estudado
Figura2.1 – Exemplo de projeto lógico de banco de dados
11
André Luís Duarte
Banco de Dados
2.2 – O Método Entidade-Relacionamento
Em 1976 o Prof. Peter Chen propôs um metodologia chamada “EntidadeRelacionamento” a fim de poder representar na forma de diagramas o modelo de dados,
auxiliando o analista a examinar o esquema da empresa do ponto de vista das regras de
negócios da empresa e não do ponto de vista do programador de aplicação. O Método
Entidade-Relacionamento (E/R) ou Modelo de Entidade / Relacionamento (MER), foi
proposto a fim de acrescentar um estágio intermediário ao projeto lógico de banco de
dados.
Posteriormente, a comunidade de estudos na área de modelagem acrescentou novos
conceitos e técnicas ampliando assim a metodologia proposta originalmente.
As principais características da modelagem E/R são:
Representação gráfica da modelagem através de um diagrama chamado “Diagrama
de E/R”
Preocupação com a semântica dos relacionamentos
Ideal para comunicação com usuários leigos
Independência de sistema gerenciador de banco de dados
Permite a construção de modelos mais estáveis
A fim de trabalharmos com o método E/R de forma mais eficiente e segura, devemos
possuir uma compreensão de alguns conceitos básicos que serão vistos com mais
detalhes nos tópicos seguintes.
São eles:
 Entidade: conjunto de qualquer coisa que possua características próprias
 Atributos: características de uma Entidade
 Relacionamentos: vinculo ou associação entre Entidades
A partir destas definições mais simplificadas podemos analisar e identificar estas
estruturas no exemplo do diagrama E/R a seguir:
André Luís Duarte
12
[email protected]
Figura2.2 – Exemplo de um diagrama E/R
2.3 Entidades e Conjuntos de Entidades
Entidade ou conjunto de entidades são quaisquer “coisas” do mundo real sobre as quais
se deseja guardar algum tipo de informação. São exemplos típicos:
 Pessoas – física ou jurídica (funcionário, empresa, fornecedor, cliente, etc)
 Objetos – materiais ou abstratos (produto, veículo, disciplina, projeto, etc)
 Eventos – (pedido, viagem, empréstimo, vendas, etc)
Devemos tomar cuidado quando identificamos as entidades pois existem elementos que
não podem ser considerados como entidades:
 Entidade com apenas um elemento;
 Operações e saídas do sistema;
 Pessoas que manipulam o sistema (usuários do sistema);
 Cargos específicos;
Geralmente as entidades são representadas por um retângulo contendo o nome da
entidade (um substantivo singular) posicionado ao centro.
Figura2.3 – Representação gráfica de uma entidade segundo o método E/R
Uma entidade geralmente possui várias manifestações dela mesma. Por exemplo, a
entidade funcionário representa todos os funcionários da empresa e não somente um
13
André Luís Duarte
Banco de Dados
funcionário (analista, programador, gerente de qualidade, etc). A estas manifestações
chamamos de ocorrências ou como iremos nos referir “instâncias” da entidade.
2.4 – Atributos
Dada uma entidade que seja de nosso interesse, chamamos de atributos o conjunto de
informações mais relevantes e que possam descrevê-la com precisão.
É muito importante que durante a modelagem, identifiquemos todos os atributos
requeridos para o funcionamento adequado dos processos de negócio, pois estes
permanecem constantes para todos os seus relacionamentos e os atributos são
independentes das outras entidades.
São exemplos de atributos de entidades (instâncias): nome, número de chamada, sexo,
cargo, carga horária, etc.
A representação original para os atributos no diagrama E/R é uma elipse ou um círculo
contendo o nome do atributo e este é ligado a sua respectiva entidade através de uma
linha contínua. Veja figura a seguir:
Figura2.4 – Exemplos de notações para atributos
A representação acima na maioria das vezes é pouco prática e uma das maneiras mais
utilizadas é a representada a seguir:
Figura2.5 – Notação mais usual para atributos
André Luís Duarte
14
[email protected]
Os atributos podem assumir valores de acordo com a sua natureza. O conjunto destes
valores é chamado de domínio do atributo.
Os atributos podem ser caracterizados por cardinalidade de atributo e pode ser mínima e
máxima. Esta é representada no diagrama pelos valores entre parênteses separados por
vírgula. Quando a cardinalidade não é especificada no atributo, assume-se que esta é
(1,1). A figura a seguir mostra um exemplo onde a entidade professor deve possuir um
ou mais títulos (no mínimo um título e no máximo n títulos).
Figura2.6 – No exemplo a presença de pelo manos um título é obrigatória
2.4.A – Atributos Simples e Compostos
O atributo denominado “simples” ou “atômico” é o atributo de determinada entidade
que não pode ser subdividido em partes.
O atributo denominado “composto” é o atributo que pode ser subdividido em outras
sub-partes e cada uma destas possui um significado próprio e são independentes entre si.
Figura2.7 – Representação de atributo composto
2.4.B – Atributos Multivalorados
São atributos que podem assumir mais de um valor para cada entidade (instância) e é
representado no diagrama E/R com um “asterisco” (*) ou pela Figura2.8. Estes
atributos devem possui cardinalidade máxima de atributo maior do que 1.
Figura2.8 – Diagrama que representa atributos multivalorados
15
André Luís Duarte
Banco de Dados
Figura2.9 – Representações para atributos multivalorados
Os atributos que não podem assumir mais de um valor para cada entidade (instância)
são chamados de atributo simplesmente valorado.
2.4.C – Atributos Opcionais ou Mandatário
São os atributos que possuem cardinalidade mínima de atributo igual a zero, por
exemplo, (0,n).
2.4.D – Atributos Determinantes ou Identificadores
É o atributo que permite identificar uma instância da entidade de forma única. Muitas
vezes, este identificador pode ser formado pela composição de dois ou mais atributos da
entidade.
O identificador ou atributo determinante deve estar representado no diagrama E / R
sublinhado, como mostrado na Figura2.9.
2.5 – Relacionamentos e Classes de Relacionamentos
Além de conhecermos as entidades, é muito importante conhecermos como estas se
relacionam entre si.
A definição mais formal para relacionamento em banco de dados diz que: “um tipo
relacionamento R entre n entidades (E1, E2, E3, ..., Em) é o conjunto de associações
entre entidades deste tipo”. Isto significa que cada instância de relacionamento r1 em R
é uma associação de entidades, onde a associação inclui exatamente uma entidade de
cada tipo participante no relacionamento.
No exemplo da Figura2.10 abaixo podemos verificar o relacionamento entre dois tipos
de entidades (empregado e departamento) e o relacionamento entre elas (trabalha para).
André Luís Duarte
16
[email protected]
Para cada relacionamento, participam apenas uma entidade de cada tipo, porém, uma
entidade pode participar de mais de um relacionamento.
Figura2.10 – Exemplo de um relacionamento
No exemplo anterior podemos notar que a ocorrência da entidade empregado, sempre
estará associada a ocorrência da entidade departamento. Podemos então batizar esta
associação (relacionamento) de “trabalha para” (ou pertence) visto que um funcionário
sempre vai trabalhar para (pertencer a) um departamento da empresa.
O relacionamento é representado no diagrama E / R como um losango contendo o nome
do relacionamento.
Figura2.11 – Relacionamento entre entidades
Um relacionamento possui uma característica importante chamada de cardinalidade que
é considerada uma “restrição imposta pelo estado das entidades no seu mini-mundo” .
A cardinalidade em um atributo consiste na especificação do sentido da associação entre
as entidades envolvidas.
No exemplo anterior podemos verificar que para cada ocorrência da entidade
funcionário deve existir uma e somente uma ocorrência da entidade departamento. Por
outro lado para cada ocorrência da entidade departamento, deve existir uma ou mais
ocorrências da entidade funcionário, haja visto que um departamento pode possuir
17
André Luís Duarte
Banco de Dados
vários funcionários. Por isto dizemos que neste caso a cardinalidade do relacionamento
trabalha para é de um para muitos ou 1:N (um para ene) e é representado da seguinte
forma no diagrama E / R.
Figura2.12 – Cardinalidade de um relacionamento
Os relacionamentos de cardinalidade 1:N são clássicos, pois representam a maioria das
situações encontradas no desenvolvimento de sistemas comerciais. Contudo existem
teoricamente relacionamentos que possuem outras cardinalidades e que são
considerados como variantes do relacionamento de cardinalidade 1:N.
Podemos citar então o relacionamento de cardinalidade 1:1 (um para um) e seu exemplo
é mostrado na figura a seguir.
Figura2.13 – Exemplo de relacionamento de cardinalidade 1:1
Apesar de existirem muitas situações que sugerem relacionamento de cardinalidade
(1:1), devemos sempre desconfiar de tais relacionamentos e analisar se as duas
entidades envolvidas não são na realidade uma só. Uma forma de fazermos esta análise,
é verificarmos se o principal identificador da primeira entidade é diferente do principal
identificador da segunda, caso não o sejam, provavelmente estamos interpretando dois
aspectos diferentes da mesma entidade.
Um exemplo de relacionamento (N:N) pode ser observado na Figura2.15.
Figura2.14 – Exemplo de relacionamento de cardinalidade (N:N)
André Luís Duarte
18
[email protected]
2.6 – Auto Relacionamento
Ao trabalharmos situações e componentes do dia-a-dia de empresas, poderemos
encontrar casos em que instâncias da mesma entidade se relacionam entre si por
apresentarem uma estrutura de natureza hierárquica. São exemplos clássicos a
modelagem de estruturas de produtos e a representações de hierarquias de pessoal. Para
ilustrarmos este tipo de relacionamento, vamos propor o estudo de dois casos.
 Suponha a existência de uma empresa estruturada em departamentos que por sua vez
são compostos por conjunto de funcionários. Alguns funcionários são supervisores de
outros funcionários e este modelo é representado na seguinte tabela.
Tabela2.1 – Tabela de descrição dos departamentos
Podemos observar na Tabela2.1 que existem ocorrências da entidade funcionário que se
relacionam com outras ocorrências dessa mesma entidade. Isso caracteriza um tipo de
relacionamento chamado de reflexivo na entidade funcionário e pode ser representado
no modelo E / R como mostrado na Figura2.16.
Figura2.15 – Auto relacionamento (relacionamento reflexivo) entre funcionários
 A tabela a seguir apresenta uma parte dos produtos comercializados por uma
empresa no setor de montagem, manutenção e venda de equipamentos de informática.
19
André Luís Duarte
Banco de Dados
Podemos notar que na lista existem produtos básicos (placa de vídeo, unidade de disco
flexível, unidade de cd-rom, etc) que não podem ser decompostos em partes menores
(pelo menos baseado no objetivo de negócio da empresa). Contudo existem outros
produtos que chamamos de compostos, pois são formados pela combinação de produtos
mais simples (o computador e o Kit multimídia). Verificamos também que um produto
composto pode participar na formação de outro produto composto.
Tabela2.2 – Lista parcial de produtos comercializados por uma empresa
Com isso temos a ocorrência da entidade produto que se relaciona com outras
ocorrências da própria entidade. Notamos que em um relacionamento entre dois
produtos, um representa o papel de composto e o outro o papel de componente. Um
produto pode ser componente de zero ou mais produtos e um produto pode ser
composto de zero ou mais componentes. A representação do auto relacionamento
anterior é mostrada na Figura2.17.
Figura2.16 – Auto relacionamento entre produtos
André Luís Duarte
20
[email protected]
Como podemos observar existem auto relacionamentos ou relacionamento recursivo de
qualquer cardinalidade, no caso, (N:N).
2.7 – Atributos de Relacionamentos
É quando um determinado relacionamento possui atributos, também conhecido como
relacionamento valorado. Por exemplo: Jussara trabalha 25 horas no projeto Aprender.
 Jussara: instância da entidade funcionário
 Aprender: instância da entidade projeto
 Trabalha em: relacionamento entre a entidade funcionário e projeto
Figura2.17 – Exemplo de atributo de relacionamento
2.8 – Relacionamentos Parciais e Totais
Outra restrição importante nos relacionamentos é chamada de participação e pode ser
total ou parcial. Relacionamento parcial é quando nem todos os elementos de uma ou
mais entidades participam do relacionamento. Em oposição relacionamento total é
quando todos os elementos de uma ou mais entidades participam do relacionamento.
Podemos ver claramente estes exemplos por exemplo no relacionamento entre
funcionário  gerencia  departamento. A entidade funcionário possui participação
parcial no relacionamento pois nem todo funcionário gerencia um departamento
(existem funcionários que só trabalham no departamento). Já a entidade departamento
possui participação total no relacionamento pois todos os departamentos são
gerenciados por algum funcionário. Já no relacionamento funcionário  trabalha em 
departamento, a participação é total para ambas as entidades.
2.9 – Relacionamentos Múltiplos
Até este momento nosso estudo levou em conta relacionamento entre duas entidades.
Este tipo de relacionamento é chamado de relacionamento binário. O relacionamento
21
André Luís Duarte
Banco de Dados
múltiplo é uma extensão do relacionamento binário para um número qualquer de
entidades.
Figura2.18 – Exemplo de relacionamento ternário ou múltiplo
Outro exemplo clássico é o relacionamento entre cliente, vendedor e produto.
Geralmente podemos analisar este tipo de relacionamentos agrupando as entidades de
maneira conveniente.
2.10 – Agregações
O modelo E / R proposto por Peter Chen na década de 70 sofreu algumas adaptações e
extensões a fim de torná-lo mais completo e abrangente. Uma destas extensões é
denominada agregação e foi proposta a fim de representar algumas situações de
relacionamentos N:N que não poderiam ser representadas utilizando o modelo da forma
como foi proposto.
A agregação é uma abstração por meio da qual relacionamentos são tratados como
entidades de nível superior. É representada por um retângulo envolvendo as entidades e
relacionamentos participantes.
Podemos então construir objetos compostos a partir de objetos componentes. Podemos
olhar para agregação como temos olhado até agora para os outros componentes, ou seja,
uma entidade é uma agregação de atributos, um relacionamento é uma agregação de
entidades e atributos, um atributo composto é uma agregação de atributos mais simples,
etc.
André Luís Duarte
22
[email protected]
Vamos propor o seguinte cenário a fim de contextualizar nosso exemplo:
A turma computação foi requisitada para desenvolver projetos computacionais dentro
do campus universitário. Para isto, a turma deve formar grupos de analistas a fim de que
possam acompanhar os projetos. Um analista pode participar de mais de um projeto ao
mesmo tempo. Baseados neste caso têm-se o seguinte diagrama E/R:
n
Analista
n
Alocado
Projeto
Figura2.19 – Exemplo de agregação situação 1
Vamos agora supor que o Analista João que trabalha no Projeto de Levantamento de
Regras de Negócio (P_LRN) requisitou um computador notebook no LPD e um veículo
de transporte para se locomover e a analista Márcia requisitou o mesmo equipamento
notebook para o projeto de modelagem entidade relacionamento (P_MER). Vamos
impor uma consideração, um analista só poderá requisitar um recurso se for para
utilização em um projeto. Não deve haver problemas com o compartilhamento do
notebook, pois a utilização no projeto P_MER depende da finalização do projeto
P_LRN.
Como o recurso foi requisitado por um analista para utilização em um projeto, é de se
esperar que estejam disponíveis informações a respeito do analista e projeto para o qual
o recurso será utilizado. Por isto não podemos relacionar o recurso unicamente ao
analista nem somente ao projeto, o que nos obriga a visualizarmos o relacionamento
entre a entidade ANALISTA e PROJETO inclusive estas, como uma única entidade.
Graficamente teremos o seguinte.
23
André Luís Duarte
Banco de Dados
n
n
Analista
Projeto
Alocado
n
Requisita
n
Recurso
Figura2.20 – Exemplo completo de agregação
Outro exemplo seguindo a mesma ideia é na modelagem de um funcionário que trabalha
em um departamento e utiliza uma máquina.
n
n
Funcionário
Projeto
Trabalha
n
Utiliza
n
Máquina
Figura2.21 – Outro exemplo de agregação
Este tipo de abstração estabelece um relacionamento “é-parte-de” entre os componentes
e a classe.
Poderemos encontrar também a seguinte representação para agregação:
n
Funcionário
n
Trabalha
Projeto
n
Utiliza
n
Máquina
Figura2.22 – Outra representação para agregação
André Luís Duarte
24
[email protected]
2.11 – Generalização / Especialização
É um tipo de abstração que define um relacionamento de subconjunto entre os
elementos de duas ou mais classes. Temos então que: todas as abstrações definidas para
a classe genérica, são herdadas por todas as classes que são subconjuntos.
Este tipo de estrutura é utilizada no diagrama E/R para enfatizar as semelhanças entre
tipos de entidades de nível superior e ocultar suas diferenças. É representada no
diagrama E/R por um triângulo com o rótulo “é um(a)”.
A generalização / especialização pode ser classificada em:
 Generalização total (t): quando cada elemento da classe genérica é mapeada
para pelo menos um elemento das classes especializadas.
Nome
CPF
Pessoa
é
uma
Homem
t
Mulher
Próstata
Gestações
Figura2.21 – Exemplo de generalização total
 Generalização parcial (p): quando existe algum elemento da classe genérica
que não é mapeado para nenhum elemento das subclasses.
Cor
Dimensões
Veículo
é
um
Carro
Motor
p
Bicicleta
Manopla
Figura2.22 – Exemplo de generalização parcial
25
André Luís Duarte
Banco de Dados
2.12 – Chaves
Conceitualmente vimos que entidades e relacionamentos individuais são distintos.
Porém, numa visão mais prática e real, essa diferença deve ser expressa em termos dos
atributos das entidades e dos relacionamentos envolvidos. Para conseguir esta distinção,
devemos lançar mão do conceito de “chave primária” (superchave) e “chave
candidata”.
Uma chave primária é um atributo ou conjunto de atributos que representam uma
instância da entidade de forma única.
Uma chave candidata possui a mesma definição de chave primária, porém esta não deve
possuir nenhum subconjunto próprio que seja uma chave candidata. Podemos ver alguns
exemplos de chaves candidatas na Tabela2.3 a seguir.
Atributo
CPF
RG
Nome1
Endereço1
Nome + Endereço
Nome + CPF2
Chave candidata
Sim
Sim
Não
Não
Sim
Não
Chave primária
Sim
Sim
Não
Não
Sim
Não
1 – Depende do
tamanho do
banco de dados a
ser desenvolvido.
2 – CPF é uma
chave candidata
individualmente.
Tabela2.3 – Exemplos de chave candidatas
2.13 – Conjunto de Entidades Forte e Fraco
Durante a modelagem poderemos encontrar entidades com atributos suficientes para
formar chaves candidatas e então escolhermos dentre estas a mais apropriada para ser a
chave primária da entidade. A entidade que possui uma chave primária é dita entidade
ou conjunto de entidades forte.
Porém, existirão situações onde o conjunto de entidades não vai possuir atributos que
possam formar chaves candidatas (nem individualmente nem em grupos). Sem chaves
candidatas não poderemos definir uma chave primária para a entidade. A este conjunto
de entidades (que não possuem chave primária) damos o nome de entidade ou conjunto
de entidades fraco.
André Luís Duarte
26
c
o
n
°
N
s
ta
a
ld
a
[email protected]
o
ã
ç
a
n
ti
a
r
n
t ta a
° a u
N D Q
o
S
n
n
Conta
Transação
Log
Figura2.23 – Exemplo de conjunto de entidade forte e fraco
2.14 – Restrições de Mapeamento
Ao analisar um sistema, o analista deve estar atento para as restrições impostas na
elaboração dos modelos em um banco de dados. Vamos estudar inicialmente duas
destas restrições.
A primeira é chamada de restrição de cardinalidade do mapeamento, estas expressam o
número de entidades às quais outras entidades podem se associar. Elas podem ser,
cardinalidade mínima ou cardinalidade máxima. No caso de cardinalidade máxima, elas
podem assumir as combinações um-para-um, um-para-muitos, muitos-para-um e
muitos-para-muitos. No caso da cardinalidade mínima deve-se analisar o contexto, pois
obviamente, elas dependerão do mundo real que está sendo modelado.
A dependência de existência forma outra importante classe de restrições. Quando a
existência de uma entidade E1 depende da existência de outra entidade E2, dizemos que
E1 é dependente de E2. A entidade E2 é chamada de entidade dominante e E1 é
chamada de entidade subordinada.
ua
a l nt i d
or
ad
C
e
ód u n
i
tá
ig
ri o
o
Entidades que possuem chave primária, sempre serão entidades dominantes (forte),
o
at r
a
o
Vendedor
D
(0,n) n
Efetua
Q
al
ip
T
1(1,1)
V
N
C
P
F
om
e
entidades subordinadas (fracas).
1(0,1)
Venda
V
enquanto as entidades que não possuírem chave primária própria serão geralmente
(1,n) n
Possui
Itens
Figura2.24 – Conjunto de entidades fraco não possuem chaves candidatas
27
André Luís Duarte
Banco de Dados
2.15 – Reduzindo o Diagrama E/R a tabelas
2.15.1 – Representação de Conjuntos de Entidades Fortes
Seja um conjunto de entidades forte E com atributos descritivos a1, a2, ..., an.
Representamos esta entidade por uma tabela chamada E com n colunas distintas, cada
uma correspondendo a um dos atributos de E. Cada linha nesta tabela corresponde a
uma instância do conjunto de entidades E.
Como primeiro exemplo iremos trabalhar o conjunto de entidades “Conta” presente no
diagrama mostrado na Figura2.25. De acordo com o diagrama E/R, a entidade conta
possui dois atributos Número Conta e Saldo.
Dizemos que D1 representa o conjunto de todos os números de conta e D2 representa o
conjunto de todos os saldos. As linhas da Tabela Conta devem ser constituídas de uma
da
N
ta
N
o
C me
P
R F
u
C a
id
ad
e
V 2  D2 .
n
S
úm
er
al
o
do
co
nt
a
dupla (V1, V2), onde V1 é um número de conta tal que V 1  D1 e V2 é um saldo tal que
n
Cliente
Possui
Conta
1
N
ú
Log
m
e
ro
tr
a
nD
Q s aata
ua çã
n o
tia
n
Transação
Figura2.25 – Diagrama E/R para cliente-conta-transação
Chamamos de produto cartesiano D1 D2 o conjunto de todas as combinações
possíveis das linhas de D1 e D2. A Tabela Conta geralmente irá conter um subconjunto
do
conjunto
resultante
do
produto
cartesiano.
Matematicamente
TabelaConta  D1 D2 .
Generalizando, se tivermos uma tabela de n colunas, representamos o produto
cartesiano de D1, D2, ...,Dn por:
André Luís Duarte
28
[email protected]
D1 x D2 x .. x .Dn - 1 x Dn
630
1000
259
2000
183
1300
...
...
(630,1000)(630,2000)(630,1300)
(259,1000)(259,2000)(259,1300)
(183,1000)(183,2000)(183,1300)
(...,...)
número-conta
259
630
401
700
199
467
115
183
118
225
210
Figura2.26 – Produto cartesiano D1xD2
saldo
1000
2000
1500
1500
500
900
1200
1300
2000
2500
2200
Tabela2.4 – Tabela Conta
Da mesma forma a TabelaCliente será reduzida para a seguinte forma.
Nome-cliente
Rafael
Jucimara
Lucas
Davi
Samuel
Lisley
Talita
cpf-cliente
rua
cidade-cliente
12345678901
Albino
Itajubá
36582945871
Souza
Divinópolis
25478965210 Cardoso
Maria da Fé
65001452036 Rodrigues
Itajubá
80145800478
Seabra
Itajubá
15844452102 Jurandir
Maria da Fé
35810023695
Pereira
Itajubá
Tabela2.5 – Tabela Cliente
Como podemos observar, a entidade cliente possui quatro atributos, Nome, CPF, Rua,
Cidade. A tabela correspondente irá possuir quatro colunas sendo uma para cada
atributo.
2.15.2 – Representação de Conjuntos de Entidades Fracos
Seja A um conjunto de entidades fraco com atributos a1, a2, ..., ar. Dizemos que B é o
conjunto de entidades forte B do qual A depende. Dizemos que a chave primária de B
consiste dos atributos b1, b2, ..., bs. Representamos o conjunto de entidades A por uma
tabela chamada A que possuirá uma coluna que irá representar cada atributo do conjunto
{a1,a2,...,ar}U{b1,b2,...,bs}.
Vamos analisar o conjunto de entidades Transação da figura 2.25. Este conjunto possui
três atributos, Número transação, Data, Quantia.
29
André Luís Duarte
Banco de Dados
Como podemos notar, nenhum atributo pode ser usado individualmente ou em conjunto
para formar uma chave primária. Por isto este conjunto é dito conjunto de entidades
fraco.
A chave primária do conjunto de entidades Conta, do qual Transação é dependente, é
Número conta. Com base nestes dados, a tabela Transação será representada por uma
tabela contendo quatro colunas, Número conta, Número transação, Data e Quantia.
número-conta
259
630
401
700
199
259
115
199
259
número-transação
5
11
22
69
103
6
53
104
7
data
11/05/2003
17/05/2003
23/05/2003
28/05/2003
03/06/2003
07/06/2003
07/06/2003
13/06/2003
17/06/2003
quantia
+50
+70
-300
-500
+900
-44
+120
-200
-79
Tabela2.6 – Representação da entidade Transação
2.15.3 – Representação de Conjuntos de Relacionamentos
Seja R um conjunto de relacionamentos envolvendo os conjuntos de entidades E1, E2,
..., En. Digamos que atributo(R) consista em n atributos. Representamos este conjunto
de relacionamentos por uma tabela chamada R com n colunas distintas, cada uma
correspondendo a um atributo de atributo(R).
Vamos analisar o relacionamento “<possui>” entre “[conta]” e “[usuário]”.
Antes, porém, é importante notarmos que este relacionamento irá se tornar uma tabela, e
nomearmos uma tabela de “possui” ficaria um tanto estranho e não será tão intuitivo
para a finalidade aqui estudada como é na representação E/R. Por isso, iremos chamar
este relacionamento de contacliente. O conjunto de relacionamento contacliente envolve
os seguintes conjuntos de entidade:
 Cliente, com chave primária cpf
 Conta, com chave primária número-conta
Uma vez que o conjunto de relacionamentos possui o atributo data, a tabela contacliente
irá possuir três colunas, cpf, número-conta, data.
André Luís Duarte
30
[email protected]
cpf
12345678901
36582945871
25478965210
65001452036
80145800478
15844452102
35810023695
número-conta
259
630
401
700
199
259
115
data
17/02/2003
17/02/2003
23/02/2003
01/03/2003
07/03/2003
07/03/2003
15/04/2003
Tabela2.7 – Representação do relacionamento contacliente (possui)
os
iv
ld
o
er
o
Sa
1
n
Cliente
N
úm
Agência
C
N
om
e
PF
R
ua
C
id
ad
e
co
nt
a
At
C
N
mostrado na Figura2.27 a seguir.
id
om
ad
e
e
Como exemplo final, consideremos o conjunto de relacionamentos do diagrama
n
CCA
Conta
Figura2.27 – Relacionamento ternário conta-cliente-agência
Este relacionamento envolve três conjuntos de entidades:
 Cliente, com chave primária cpf
 Conta, com chave primária número-conta
 Agência, com chave primária nome-agência
Analogamente de acordo com o estudo realizado até este momento, teremos a tabela que
irá representar o conjunto de relacionamentos CCA.
cpf
12345678901
36582945871
25478965210
65001452036
80145800478
15844452102
35810023695
número-conta
259
630
401
700
199
259
115
nome-agência
Av BPS
Rua Rodrigues
Av 1° de junho
Rua Nova
Av Major
Rua Carneiro
Alameda A
Tabela2.8 – Representação do relacionamento CCA
31
André Luís Duarte
Banco de Dados
Capítulo 3 – Modelo Relacional
3.1 – Introdução
Historicamente o modelo relacional é um modelo novo. Os primeiros sistemas de Banco
de Dados foram baseados nos modelos hierárquicos ou em rede.
O Modelo Relacional estabeleceu-se como o modelo de dados principal para sistemas
de processamento de dados comerciais, mas, além disso, também foi utilizado como
ferramenta CASE para projetos em diversos ambientes.
3.2 – Estrutura de Banco de Dados Relacionais
Um BDR consiste em uma coleção de tabelas, cada qual designada por um nome único.
Cada tabela possui uma estrutura similar àquela apresentada na redução do ME/R em
tabelas. Uma linha numa tabela representa um relacionamento entre um conjunto de
valores. Uma vez que uma tabela é um conjunto destes relacionamentos, existe uma
correspondência entre o conceito de tabela e o conceito matemático de relação.
3.2.1 – Estrutura básica
Considere a tabela representada a seguir.
nome-agência número-conta nome-cliente saldo
Centro
101
Lucas
500
Morro Chic
215
André
700
Boa Vista
102
Jussara
400
Varginha
305
Duarte
350
Boa Vista
201
Davi
900
BPS
222
Samuel
700
Avenida
217
Mateus
750
Centro
105
Mateus
850
Tabela 3.1 – Relação depósito
A Tabela 3.1 possui quatro atributos: nome-agência, número-conta, nome-cliente,
saldo. Para cada atributo existe um conjunto de valores possíveis que chamamos de
domínio daquele atributo. Para o atributo nome-agência o domínio é o conjunto de
todos os nomes de agências.
Seja então D1 o conjunto dos nomes de agência, D2 o conjunto de números de contas,
D3 o conjunto de nomes de clientes e D4 o conjunto de todos os saldos. Podemos então
André Luís Duarte
32
[email protected]
verificar que qualquer linha de depósito deverá conter valores quádruplos (v1, v2, v3,
v4) onde v1 é um nome de agência, v2 é um número de conta, v3 é um nome de cliente e
v4 é um saldo de conta, ou seja, v1 está no domínio de D1, v2 está no domínio de D2,
etc. Geralmente depósito conterá apenas um subconjunto de todas as linhas possíveis,
ou seja, depósito é um subconjunto de D1 D2  D3  D4 .
Geralmente
uma
tabela
de
n
colunas
precisa
ser
um
subconjunto
de
D1 D2  D3  D4  ...  Dn  1 Dn .
Devido ao fato de as tabelas serem essencialmente relações, encontraremos na literatura
conceitual os termos matemáticos relação e tupla no lugar de tabela e linha, por isso,
assumiremos esta nomenclatura.
Na relação depósito da Tabela3.1, existem oito tuplas. Digamos que a variável tupla t
refira-se à primeira tupla da relação. Usamos a notação
para representar o valor de t no atributo nome agência. Assim:
t[número-conta]=101
é o valor de t no atributo número da conta, nome do cliente e saldo seguem a mesma
idéia.
De outro modo podemos escrever t[1] para representar o valor da tupla t no primeiro
atributo (nome-agência), t[2] para representar o número da conta e assim por diante.
Uma vez que a relação é um conjunto de tuplas, usaremos a notação t  r para
representar que a tupla t está na relação r.
Por enquanto, vamos considerar que todas as relações r possuem domínio atômico, ou
seja, os elementos do domínio são elementos indivisíveis.
3.2.2 – Esquema de Banco de Dados
Geralmente um esquema relacional é uma lista de atributos e de seus domínios
correspondentes1. Quando necessitarmos explicitar nosso domínio, o faremos logo após
1
Não iremos definir precisamente o domínio de cada atributo pois necessitamos do conceito de
integridade que será visto mais adiante.
33
André Luís Duarte
Banco de Dados
cada atributo da relação. Por exemplo: (nome-agência:cadeia número-conta: cadeia,
etc).
Devemos sempre atribuir um nome a um esquema relacional. Nomes começando com
letras maiúsculas serão usados para esquemas de relações e nomes começando com
letras minúsculas para relações. Baseado na notação proposta, Esquema-depósito
representará o esquema relacional para a relação depósito. Assim:
Esquema-depósito=(nome-agência, número-conta, nome-cliente, saldo)
Note que depósito é uma relação num esquema Depósito, ou seja,
depósito(Esquema-depósito)
Como outro exemplo, vamos considerar a relação cliente. O esquema para esta relação
é:
Esquema-cliente(nome-cliente, rua-cliente, cidade-cliente)
Podemos dizer que analogamente um esquema relacional corresponde à noção de
definição de tipo em uma linguagem de programação, também uma variável
corresponde a uma instância de uma relação, já que ambas em um determinado instante
possuem um valor.
nome-cliente
rua
cidade-cliente
Junior
Rodrigues
Itajubá
André
Seabra
Maria da Fé
Jussara
Rodrigues
Itajubá
Lislei
Seabra
Maria da Fé
Samuel
Cardoso
Pouso Alegre
Duarte
Arlindo
Brasópolis
Davi
Zaroni
Paraisópolis
Talita
Major
Brasópolis
Lucas
Coronel
Piranguinho
Mariana
Nova
Sta Rita
Tiago
Corrêa
Cristina
Mateus
Lamounier
Brasópolis
Tabela3.2 – Relação cliente
O uso de atributos comuns em esquemas relacionais é uma forma de relacionar tuplas de
relações distintas. Suponha que desejamos achar as cidades onde os clientes da agência
Morro Chic moram. Vamos assumir a relação mostrada na Tabela3.1 e propor uma
outra relação mostrada na Tabela3.2.
André Luís Duarte
34
[email protected]
Primeiro observamos a relação depósito para encontrar todos os clientes da agência
Morro Chic. Para cada cliente, examinamos a relação cliente para encontrar a cidade na
qual reside.
Podemos pensar que um esquema relacional representando todas as informações seria
melhor. Para um usuário a idéia de se utilizar um só esquema relacional é interessante,
mas vamos analisar este cenário.
Suponhamos, então, o seguinte esquema relacional.
Esquema-conta-info=(nome-agência,número-conta,nome-cliente,saldo,ruacliente,cidade-cliente)
A primeira coisa que podemos observar é que se um cliente possui diversas contas,
precisamos listar seu endereço uma vez para cada conta. Esta repetição é dispendiosa e
pode ser evitada pelo uso de duas relações. Outro problema é se um cliente possui mais
de uma conta, mas não forneceu seu endereço. Não podemos construir uma tupla sobre
Esquema-conta-info, uma vez que os valores para rua e cidade do cliente não são
conhecidos. Para poder representar tuplas incompletas, precisamos utilizar valores
nulos. No exemplo anterior utilizando duas relações, uma sobre Esquema-depósito e
outra sobre Esquema-cliente, podemos representar um cliente cujos dados do endereço
são desconhecidos sem utilizar valores nulos, ou seja:
nome-agência número-conta nome-cliente saldo rua-cliente cidade-cliente
Centro
101
Lucas
500
Coronel
Piranguinho
Morro Chic
215
André
700
NULO
NULO
Boa Vista
102
Jussara
400
NULO
NULO
Varginha
305
Duarte
350
Arlindo
Brasópolis
Tabela3.3 – Esquema-conta-info
nome-agência número-conta nome-cliente saldo
Centro
101
Lucas
500
Morro Chic
215
André
700
Boa Vista
102
Jussara
400
Varginha
305
Duarte
350
Tabela3.4 – Esquema-conta
nome-cliente rua-cliente cidade-cliente
Lucas
Coronel
Piranguinho
Lislei
Seabra
Maria da Fé
Tabela3.5 – Esquema-informação
35
André Luís Duarte
Banco de Dados
Nem sempre poderemos eliminar valores vazios. Veremos que este tipo de informação
causa dificuldades no acesso e atualização do banco de dados. Sempre que possível,
devemos eliminar valores nulos do banco.
Por enquanto assumiremos que os esquemas de relação não possuem valores nulos.
Posteriormente estudaremos critérios que nos ajudam a decidir quando um conjunto de
esquema é melhor do que o outro em termos de repetição de valores e da existência de
valores nulos.
3.2.3 – Linguagens de Consulta
Uma linguagem de consulta é uma linguagem na qual o usuário requisita informações
de um banco de dados. São tipicamente de mais alto nível em relação às linguagens de
programação. Podem ser classificadas como procedural e não procedural.
Examinaremos duas linguagens puristas: a álgebra relacional é procedural enquanto que
o cálculo relacional de tupla e o calculo relacional de domínio são não procedural.
Para nosso estudo consideremos o seguinte diagrama E/R mostrado na seguinte figura.
Saldo
Conta n°
Depósito
Agência
Cliente
Cidade
Cidade
Ativos
Rua
Nome
Nome
Empréstimo
Empréstimo n°
Quantia
Figura3.1 – Diagrama E/R para um empreendimento bancário
Consideremos também os seguintes esquemas de relacionamentos e suas relações.
André Luís Duarte
36
[email protected]
Esquema-agência=(nomeagência,ativos,cidadeagência)
Esquema-cliente=(nomecliete,ruacliente,cidadecliente)
Esquema-depósito=(nomeagência,númeroconta,nomecliente,saldo)
Esquema-empréstimo=(nomeagência,numeroempréstimo,nomecliente,quantia)
nome-agência número-empréstimo nome-cliente quantia
Centro
17
Junior
1000
BPS
23
André
2000
Boa Vista
15
Jussara
1500
Centro
14
Ana
1500
Morro Chic
93
Lislei
500
Varginha
11
Duarte
900
Canudos
29
Davi
1200
Sul
16
Talita
1300
Centro
18
Lucas
2000
Boa Vista
25
Mariana
2500
Avenida
10
Tiago
2200
Tabela3.6 – Relação empréstimo
nome-agência
Centro
BPS
Boa Vista
Morro Chic
Varginha
Canudos
Sul
Avenida
ativos cidade-agência
9000000
Cristina
2100000
Piranguinho
1700000
São José
400000
São José
8000000
São José
300000
Jacareí
3700000
Maria da Fé
7100000
Cristina
Tabela3.7 – Relação agência
3.3 – Álgebra Relacional
A álgebra relacional é uma linguagem de consulta procedural. Ela consiste de um
conjunto de operações que usam uma ou duas relações como entrada e produzem uma
nova relação como resultado.
37
André Luís Duarte
Banco de Dados
As operações fundamentais da álgebra relacional são selecionar, projetar, produto
cartesiano, renomear, união e diferença de conjuntos. Além das operações fundamentais
existem interseção de conjuntos, junção natural, divisão e atribuição.
As operações selecionar, projetar e renomear são chamadas de unárias, pois operam
sobre uma única relação. As outras três são operações binárias por operarem com pares
de relações.
O seguinte panorama ilustra de forma intuitiva a álgebra relacional.
AX
AY
A
X
BX
Y
BY
B
C
CX
CY
PROJETAR
SELECIONAR
PRODUTO CARTESIANO
UNIÃO
a1
b1
INTERSEÇÃO
b1
c1
a1
b1
c1
a2
b2
b2
c2
a2
b2
c2
a3
b3
b3
c3
a3
b3
c3
DIFERENÇA
ax
x
ay
y
a
az
bx
cy
DIVISÃO
JUNÇÃO NATURAL
Figura3.2 – Panorama da álgebra relacional
André Luís Duarte
38
[email protected]
3.3.1 – Operações Fundamentais
3.3.1.A – Operação Selecionar
A operação selecionar seleciona tuplas que satisfazem um dado predicado.
Utiliza-se a letra grega  (sigma) para representar seleção. O predicado aparece como
subscrito de

. A relação argumento é dada entre parênteses.
Exemplo 1
Selecionar as tuplas da relação empréstimo onde agência é centro.
 nomeagência"Centro" empréstimo
nome-agência número-empréstimo nome-cliente quantia
Centro
15
Jussara
1500
Centro
25
Mateus
2500
Centro
18
Lucas
2000
Tabela3.8 – Relação resultante da consulta
 nomeagência"Centro" empréstimo
Exemplo 2
Encontrar todas as tuplas nas quais a quantia emprestada é maior do que $1200,00.
 quantia1200empréstimo
Podemos utilizar os operadores , , , , ,  , além de combinar predicados com os
conectivos  (e) e  (ou).
Exemplo 3
Encontrar as tuplas pertencentes a empréstimos maiores que $1200,00 feitos pela
agência do centro.
 nomeagência"Centro" quantia1200empréstimo
O predicado de seleção pode ser usado para comparar dois atributos. Considere o
esquema relacional Esquema-usuário=(nome-cliente,nome-gerente), indicando que um
cliente possui um gerente pessoal.
nome-cliente nome-gerente
André
Lucas
Jussara
Antônio
Lucas
Lucas
Tabela3.9 – Relação Usuário
A Ttabela3.9 mostra a relação usuário(Esquema-usuário).
39
André Luís Duarte
Banco de Dados
Exemplo 4
Encontrar todos os clientes que têm o mesmo nome do seu gerente pessoal.
 nomeclientenome gerente(usuário)
A relação resultante da consulta acima é dada pela seguinte tabela.
nome-cliente nome-gerente
Lucas
Lucas
Tabela3.10 – Relação resultante da consulta
 nomeclientenome gerente(usuário)
3.3.1.B – Operação Projetar
No exemplo anterior, obtivemos a relação (nome-cliente, nome-gerente) na qual t[nomecliente]=t[nome-gerente] para todas as tuplas t. Esta listagem é redundante pois
poderíamos ter somente uma listagem. A operação projetar permite a geração desta
listagem pois é uma operação unária que da como resultado sua relação argumento com
certas colunas deixadas de fora. Como uma relação é um conjunto, quaisquer linhas
duplicadas são deixadas de fora.
A projeção é representada pela letra grega  (pi maiúscula). Listamos os atributos que
queremos que apareçam nos resultados como subscrito de  . O argumento de relação
segue a letra  entre parênteses.
Exemplo 5
Suponha que desejamos uma relação mostrando os clientes e as agências das quais eles
tomaram empréstimo não importando a quantia ou o número do empréstimo.
 nome agência, nomeusuário(empréstimo)
Exemplo 6
Dê o nome dos clientes que possuem o mesmo nome dos seus gerentes pessoais.
 nomeusuário nomeusuário nome gerenteusuário
Note que ao invés de dar o nome da relação como argumento da operação de projeção,
damos uma expressão de consulta que nos retorna uma relação.
André Luís Duarte
40
[email protected]
3.3.1.C – Operação Produto Cartesiano
Até agora, obtivemos informações de apenas uma relação de cada vez. A operação
produto cartesiano nos permite combinar informações a partir de diversas relações. A
operação produto cartesiano é representada pela  (cruz) e trata-se de uma operação
binária. Usaremos a notação r1 r 2 para representar o produto cartesiano entre as
relações r1 e r2. Lembremos que uma relação é definida como sendo um subconjunto
de um produto cartesiano de um conjunto de domínios.
Definindo nomes de atributos para a relação resultante
Um problema que surge ao trabalhar com a operação produto cartesiano é como ficam
os nomes dos atributos da relação resultante.
Suponha que desejamos encontrar os usuários atendidos pelo gerente Lucas, bem como
as cidades nas quais estes usuários vivem. Precisamos da informação na relação usuário
e na relação cliente. A Tabela3.11 mostra a relação. O esquema relação para r é:
(usuário.nome-cliente,usuário.nome-gerente,cliente.nome-cliente,
cliente.rua-cliente,cliente.cidade-cliente)
usuário.
nome-cliente
André
André
André
André
André
André
André
Jussara
Jussara
Jussara
Jussara
Jussara
Jussara
Jussara
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
nome-gerente
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Antônio
Antônio
Antônio
Antônio
Antônio
Antônio
Antônio
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
cliente.
nome-cliente
Lucas
André
Jussara
Lislei
Samuel
Duarte
Davi
Lucas
André
Jussara
Lislei
Samuel
Duarte
Davi
Lucas
André
Jussara
Lislei
Samuel
Duarte
Davi
Tabela3.11 – Relação resultante
rua-cliente
Rodrigues
Seabra
Rodrigues
Seabra
Cardoso
Arlindo
Zaroni
Rodrigues
Seabra
Rodrigues
Seabra
Cardoso
Arlindo
Zaroni
Rodrigues
Seabra
Rodrigues
Seabra
Cardoso
Arlindo
Zaroni
cidade-cliente
Itajubá
Maria da Fé
Itajubá
Maria da Fé
São José
Brasópolis
Paraisópolis
Itajubá
Maria da Fé
Itajubá
Maria da Fé
São José
Brasópolis
Paraisópolis
Itajubá
Maria da Fé
Itajubá
Maria da Fé
São José
Brasópolis
Paraisópolis
r  usuário  cliente
41
André Luís Duarte
Banco de Dados
Simplesmente ligamos todos os atributos de ambas as relações e ligamos o nome da
relação da qual veio o atributo original. Para os atributos que aparecem em apenas um
dos dois esquemas, utilizamos somente o nome do atributo e para os que se repetem
usamos a dupla nome-relação.nome-atributo, afim de evitar ambiguidades. O esquema
para a relação será:
(usuário.nome-cliente,nome-gerente,cliente.nome-cliente,rua-cliente,cidade-cliente)
Agora que conhecemos o esquema relacional para r  usuário  cliente , que tuplas
aparecem em r? Uma tupla de r será constituída a partir de cada par de tuplas possíveis;
uma da relação usuário e uma da relação cliente.
Assuma que tenhamos n1 tuplas em usuário e n2 tuplas em cliente. Existem então
n1 n2 maneiras possíveis de combinar as tuplas de ambas as relações. Em particular,
note que, para algumas tuplas t de r pode ocorrer:
tusuário.nome  cliente   tcliente .nome  cliente 
Geralmente se temos relações r1R1 e r 2R2 , então r1 r 2 é uma relação cujo
esquema é a concatenação de R1 e R2. A relação R contém todas as tuplas t para qual
existe uma tupla t1 em r1 e t2 em r2 onde tR1  t1R1 e tR2  t 2R2 .
Retomando a consulta “dê todos os usuários do gerente Lucas e a cidade em que eles
vivem”.
Consideremos
a
relação
r  usuário  cliente .
Se
escrevemos
 nome gerente"Lucas" (usuário  cliente ) , então teremos a Tabela 3.12 mostrada a
seguir.
Ou seja, temos uma relação pertencente à “Lucas”. Note que a coluna usuário.nomecliente contém apenas clientes de “Lucas”.
Uma vez que a operação produto cartesiano associa qualquer tupla de cliente a qualquer
tupla de usuário, sabemos que algumas tuplas em usuário cliente possuem a
informação
desejada.
Isto
irá
ocorrer
nos
casos
onde
usuário.nome-
cliente=cliente.nome-cliente. Logo teremos o seguinte.
André Luís Duarte
42
[email protected]
 usuário.nomeclientecliente.nomecliente  nome gerente"Lucas" usuário  cliente 
Com esta expressão teremos as tuplas da relação usuário cliente que:
 Pertencem a “Lucas”
 Têm a rua e cidade do cliente de “Lucas”
usuário.
nome-gerente
nome-cliente
André
Lucas
André
Lucas
André
Lucas
André
Lucas
André
Lucas
André
Lucas
André
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
Lucas
cliente.
nome-cliente
Lucas
André
Jussara
Lislei
Samuel
Duarte
Davi
Lucas
André
Jussara
Lislei
Samuel
Duarte
Davi
Tabela3.12 – Relação resultante
rua-cliente
Rodrigues
Seabra
Rodrigues
Seabra
Cardoso
Arlindo
Zaroni
Rodrigues
Seabra
Rodrigues
Seabra
Cardoso
Arlindo
Zaroni
cidade-cliente
Itajubá
Maria da Fé
Itajubá
Maria da Fé
São José
Brasópolis
Paraisópolis
Itajubá
Maria da Fé
Itajubá
Maria da Fé
São José
Brasópolis
Paraisópolis
 nome gerente"Lucas" (usuário  cliente )
Finalizando, como queremos o nome dos clientes e a cidade em que eles vivem,
fazemos uma projeção.
 usuário.nomecliente,cidadecliente 
 usuário.nomeclientecliente.nomecliente  nome gerente"Lucas" usuário  cliente 
O resultado desta expressão é a resposta correta para a nossa consulta.
3.3.1.D – Operação Renomear
Na consulta apresentada anteriormente, introduzimos a convenção de nomear atributo
por nome-relação.nome-atributo a fim de eliminarmos possíveis ambiguidades ao
acessar as tuplas. Outra forma de ambiguidade que pode ocorrer é quando a mesma
relação aparece mais de uma vez na consulta.
Suponha que desejamos os nomes de todos os clientes que moram na mesma rua e na
mesma cidade que André. Podemos obter rua e cidade de André através da consulta
43
André Luís Duarte
Banco de Dados
 rua, cidade cliente  nome cliente" André" cliente 
Entretanto, para encontrarmos outros clientes com esta rua e cidade, devemos nos referir
à relação cliente uma segunda vez.
pcliente   rua,cidadecliente  nomecliente" André" cliente 
Onde p é um predicado de seleção que requer que os valores de cidade-cliente e rua
sejam iguais.
Para obtermos a consulta desejada, necessitamos nos referir a rua e cidade na mesma
tabela. Já sabemos que não podemos ter a mesma nomenclatura pois estaríamos criando
uma ambiguidade não desejada. Para resolver este problema, utilizamos a operação
Renomear, representada pela letra  (Rô minúscula) na expressão.
 x r 
Esta expressão retornará a relação r com o nome x. agora podemos renomear uma
referência à relação duas vezes sem ambiguidades. Usando o nome cliente2 como um
segundo nome para a relação cliente, temos a expressão.
 cliente.nome cliente  cliente2.rua cliente.rua cliente2.cidade cliente cliente.cidade cliente 
cliente   rua,cidade cliente  nome cliente" André" cliente2 cliente 

3.3.1.E – Operação União
Como na teoria dos conjuntos, a operação união é representada pelo símbolo  . Para
analisarmos esta operação, vamos supor que o banco deseja saber o nome de todos os
clientes que possuem uma relação (conta, empréstimo ou ambos) com a agência Boa
Vista.
Para fazermos esta consulta (produzirmos esta listagem) precisamos das informações da
relação empréstimo e da relação depósito. Podemos encontrar todos os clientes com um
empréstimo na agência Boa Vista com a seguinte expressão:
André Luís Duarte
44
[email protected]
 nomecliente  nomeagência"BoaVista" empréstimo

Podemos encontrar todos os clientes que possuem uma conta na agência Boa Vista com
a expressão:
 nomecliente  nomeagência"BoaVista" depósito 

Para solucionarmos o problema proposto aqui, devemos fazer a união entre os dois
conjuntos, ou seja, todos os clientes que aparecem em uma ou em ambas as relações.
Assim, a expressão que nos dá o resultado desejado é:
 nomecliente  nomeagência"BoaVista" empréstimo
  nomecliente  nomeagência"BoaVista" depósito 


A relação resultante para esta expressão é:
nome-cliente
Jussara
Mariana
Davi
Tabela3.13 – Relação resultante da consulta
 nome cliente  nome agência" BoaVista" empréstimo    nome cliente  nome agência" BoaVista" depósito 

Note que existem três tuplas no resultado apesar de existirem dois solicitadores de
empréstimo e dois depositantes. Isto se deve porque como as relações são conjuntos, os
valores duplicados são eliminados.
É importante observar que precisamos nos assegurar de que as uniões sejam feitas entre
relações compatíveis. Não faz sentido promovermos a união entre relações com número
de atributos diferentes, ou relações que não possuem domínios em comum. Logo, para
que uma operação união r  s seja válida, necessitamos de que duas condições sejam
cumpridas:
 As relações envolvidas devem ter o mesmo grau, ou seja, devem possuir o
mesmo número de atributos;
 Os domínios do i-ésimo atributo de r e de s devem ser os mesmos;
45
André Luís Duarte
Banco de Dados
3.3.1.F – Operação Diferença de Conjuntos
A operação diferença de conjuntos nos permite encontrar tuplas que estejam em uma
relação e não estejam em outra relação. É representada pelo símbolo matemático de
subtração –. A expressão r  s resulta em uma relação contendo as tuplas que estão em
r mas não estão em s.
Exemplo 7
Encontrar todos os clientes da agência Boa Vista que tenham uma conta, mas não um
empréstimo.
 nomecliente  nomeagência"BoaVista" depósito 

  nomecliente  nomeagência"BoaVista" empréstimo

A relação resultante para esta consulta aparece na tabela a seguir.
nome-cliente
Davi
Tabela3.13 – Relação resultante da consulta
nomecliente  nome agência"BoaVista" depósito    nomecliente  nome agência"BoaVista" empréstimo

Exemplo 8
Dê a conta com o maior saldo.
Intuitivamente poderíamos pensar em escrever uma consulta comparando na mesma
relação o atributo saldo procurando pelo maior valor. Se escrevêssemos a consulta
 depósito.saldod .saldodepósito   d depósito 
teríamos como resultado todos os
valores exceto os menores valores.
Exercício de fixação
Crie a relação para a expressão anterior e comprove que ela exibe somente os
menores valores.
André Luís Duarte
46
[email protected]
Este resultado não nos ajuda muito, porém, se fizermos a comparação inversa, ou seja,
 depósito.saldod .saldodepósito   d depósito 
teremos todos os valores exceto
os maiores.
Exercício de fixação
Crie a relação para a expressão anterior e comprove que ela exibe somente os maiores
valores.
De uma forma mais completa,
 depósito.saldo depósito.saldod .saldodepósito   d depósito  .
Se utilizarmos a operação diferença, poderemos obter da relação depósito as tuplas que
não constam na relação anterior, assim, a expressão
 saldo depósito 
  depósito.saldo  depósito.saldod .saldo depósito   d depósito 
possui os maiores valores para os saldos existentes na relação depósito.
3.3.2 Definição Formal da Álgebra Relacional
Uma expressão geral da álgebra relacional é constituída a partir de sub expressões
menores. Sejam E1 e E2 expressões da álgebra relacional. Então, as seguintes são
expressões da álgebra relacional:

E1  E 2

E1  E 2

E1 E 2

 p E1 , onde p é um predicado sobre atributos que ocorrem em E1

 s E1 , onde s é uma lista de alguns atributos que ocorrem em E1

 x E1 , onde x é o novo nome para a relação E1
3.3.3 – Operações Adicionais
As operações fundamentais são suficientes para representar qualquer consulta da
álgebra relacional. As operações que serão estudadas neste tópico, não acrescentam
47
André Luís Duarte
Banco de Dados
nenhuma potência à álgebra relacional, mas simplificam algumas consultas comuns que
são longas demais para serem expressas.
3.3.3.A – Operação Intersecção de Conjuntos
Observe que qualquer expressão que usa a intersecção na álgebra relacional pode ser
reescrita substituindo-se a operação intersecção por um par de diferenças entre
conjuntos, assim, r  s  r  r  s  .
Assim, a operação intersecção representado pelo símbolo
,
não é uma operação
fundamental nem acrescenta potência à consulta.
Exemplo 9
Suponha que desejamos encontrar todos os clientes com um empréstimo e uma conta na
agência Boa Vista. Usando a operação intersecção podemos escrever
 nomecliente  nomeagência"BoaVista" depósito 
  nomecliente  nomeagência"BoaVista" empréstimo
Exercício de fixação
Crie a relação para a expressão anterior e comprove que o resultado é uma relação com
Esquema (nome-cliente) e a tupla Jussara.
3.2.3.B – Operação Junção Natural
Frequentemente desejamos simplificar uma consulta que requer um produto cartesiano.
Uma consulta que envolve um produto cartesiano inclui, normalmente, uma operação de
seleção no resultado desse produto.
A operação junção natural é uma operação binária que permite combinar certas seleções
e um produto cartesiano em uma operação e é representada pelo símbolo
.
Exemplo 10
Dê os clientes que têm um empréstimo no banco e as cidades em que vivem.
André Luís Duarte
48
[email protected]
Uma solução que poderíamos desenvolver para o problema anterior é a expressão
utilizando somente as operações fundamentais apresentada a seguir:
 empréstimo.nomeclient,cidadecliente 
 empréstimo.nomeclientecliente.nomecliente empréstimo  cliente 

Ou seja, tomamos o produto cartesiano entre as relações empréstimos e cliente, depois
selecionamos aquelas tuplas que pertencem a um único nome-cliente.
A operação junção natural forma um produto cartesiano dos seus dois argumentos, faz
uma seleção forçando uma igualdade sobre os atributos comuns em ambos os esquemas
relacionais e remove as tuplas duplicadas.
Assim a consulta anterior pode ser reescrita utilizando a operação junção por:
nome-cliente cidade-cliente
Junior
Itajubá
André
Maria da Fé
Jussara
Itajubá
Lislei
Maria da Fé
Duarte
Brasópolis
Davi
Paraisópolis
Talita
Pouso Alegre
Lucas
Piranguinho
Mariana
Santa Rita
Tiago
Cristina
Tabela3.14 – Relação resultante da expressão com junção
Uma vez que os esquemas para empréstimo e cliente têm o atributo nome-cliente em
comum, a operação junção combina cada par de tuplas em uma única tupla na união dos
dois esquemas. Depois de executar a projeção, obtemos a relação mostrada
anteriormente.
Podemos agora definir formalmente a junção natural.
49
André Luís Duarte
Banco de Dados
Considere dois esquemas de relações R e S, que são listas de atributos. Se
considerarmos os esquemas como conjuntos ao invés de listas, poderemos escreveras
expressões R  S 2 como sendo os atributos que são comuns a R e S, e também, R  S
como sendo os atributos que estão em R, S ou em ambos os esquemas.
Considere agora duas relações r(R) e s(S)*1.
A ligação natural de r e s, representada por
, é uma relação sobre o
esquema R  S *2.
É a projeção em R  S de uma seleção sobre r  s *3, onde o predicado requer que
r. A  s. A *4. Para cada atributo A em R  S *5.
Formalmente:
=
 R  S  r . A1 s. A1 r . A2  s. A2 ... r . An  s. An r  s 
onde
R  S  A1, A2,..., An
Vamos propor uma ilustração a fim de facilitar o entendimento da teoria apresentada.
Para isto, vamos supor os esquemas Pessoa={número, nome, idade,casado} e
Ócio={dia, mês, nome, idade}. Vamos considerar também R Pessoa e SÓcio.
*1
r
número nome Idade casado
1
Bido
21
S
2
Muca
30
N
3
Luca
17
N
Tabela3.15 – relação pessoa
*2
2

s
dia
03
14
21
Mês
02
03
04
nome
Bido
Jú
Luca
idade
21
18
17
Tabela3.16 – relação ócio
R  S = {número, nome, idade, casado, dia, mês}
Note que nos referimos à intersecção e união de atributos em esquemas e não tuplas em relações.
André Luís Duarte
50
[email protected]
*3
rs
número pessoa.nome pessoa.idade casado
1
Bido
21
S
1
Bido
21
S
1
Bido
21
S
2
Muca
30
N
2
Muca
30
N
2
Muca
30
N
3
Luca
17
N
3
Luca
17
N
3
Luca
17
N
dia
03
14
21
03
14
21
03
14
21
mês
02
03
04
02
03
04
02
03
04
ócio.nome ócio.idade
Bido
21
Jú
18
*4
Luca
17
Bido
21
Jú
18
Luca
17
Bido
21
Jú
18
*4
Luca
17
Tabela3.17 – relação rxs
*5
R  S  nome, idade
número
1
3
nome
Bido
Luca
idade
21
17
casado
S
N
Tabela3.18 – relação
dia
03
21
mês
02
04
,
Exemplo 10
Dê os ativos e os nomes de todas as agências que possuem depositantes, isto é, clientes
com uma conta, vivendo em Brasópolis.
Note que não há necessidade de colocarmos a junção entre parênteses pelo fato da
operação junção natural ser associativa.
nome-agência
Varginha
Avenida
Centro
ativo
8M
7.1M
90M
Tabela3.19 – relação resultante da junção do Exemplo 10
Exemplo 11
Dê todos os clientes que possuem tanto uma conta como um empréstimo na agência Boa
Vista.
51
André Luís Duarte
Banco de Dados
A consulta que foi proposta no Exemplo 9 poderia ser resolvida com a expressão acima.
Este exemplo ilustra o fato de que na álgebra relacional é possível escrever diversas
expressões equivalentes e bastante diferente uma da outra.
Sejam r(R) e s(S) relações com nenhum atributo em comum, isto é, R  S  . Então,
.
3.3.3.C – Operação Divisão
A operação divisão é representada pelo símbolo matemático de divisão
 , serve para
consultas onde se pode incluir a frase “para todos”.
Exemplo 12
Suponha que desejamos encontrar todos os clientes que têm uma conta em todas as
agências em Cristina.
Podemos encontrar todos os pares nome-cliente e nome-agência para os clientes que
possuem alguma conta numa agência escrevendo
r 2   nomecliente,nomeagência depósito 
nome-cliente
Lucas
André
Jussara
Duarte
Davi
Samuel
Mateus
Mateus
nome-agência
Centro
Morro Chic
Boa Vista
Varginha
Boa Vista
BPS
Avenida
Centro
Tabela3.21 – relação resultante da consulta r 2   nomecliente,nomeagência depósito 
Podemos localizar todas as agências em Cristina utilizando a expressão
r1   nome agência cidade"Cristina" agência 
André Luís Duarte
52
[email protected]
nome-agência
Avenida
Centro
Tabela3.20 – relação resultante da consulta r1   nome agência cidade"Cristina" agência 
Agora precisamos encontrar clientes que aparecem em r2 com cada nome de agência
em r1, ou seja, para toda tupla existente em r1 achar uma tupla correspondente a cada
valor em r2. Neste caso, podemos escrever:
 nomecliente,nomeagência depósito 
  nome agência  cidade"Cristina" agência 
O resultado para esta expressão é uma relação que possui o esquema (nome-cliente) e
contém a tupla (Mateus).
Formalmente, podemos escrever:
Sejam r(R) e s(S) relações e seja S  R . A relação r  s é uma relação no esquema RS. Uma tupla t está em r  s se, para cada tupla ts em s existe uma tupla tr em r
satisfazendo as regras:
tr S   ts S 
tr R  S   t R  S 
Exercício de Fixação
Comprove baseado na definição formal anterior que o resultado para o Exemplo 12 é
realmente uma relação que possui o esquema (nome-cliente) e contém a tupla (Mateus).
Finalmente podemos definir a operação divisão em termos de operações básicas da
seguinte forma:
r  s   RS r    RS  R S r   s   r 
Exercício de Fixação
Comprove baseado na expressão anterior que o resultado para o Exemplo 12 é
verdadeiro.
53
André Luís Duarte
Banco de Dados
3.3.3.D – Operação Atribuição
Durante os estudos realizados, pudemos observar que algumas expressões podem se
tornar extremamente complexas, bem mais do os exemplos vistos. Por isso é
interessante escrever uma expressão da álgebra relacional em partes. Para fazer isto,
utilizamos a operação atribuição, que é representada pela  (seta à esquerda), para
uma variável temporária do tipo relação.
A operação atribuição funciona de maneira similar à atribuição em linguagem de
programação, sendo assim, podemos reescrever a operação divisão utilizando a seguinte
forma:
temp   R  S r 
temp   R  S temp  s   r 
O resultado da expressão à direita da seta é atribuído para a variável de tipo relação à
esquerda. Este resultado não é exibido para o usuário mas pode ser utilizado em
expressões subsequentes.
Com a operação atribuição as expressões da álgebra relacional podem ser escritas como
um programa sequencial consistindo em uma série de atribuições seguidas por uma
expressão cujo valor é exibido como resultado da consulta. A atribuição deve sempre,
em caso de consultas, ser feita para uma variável temporária, pois, as atribuições para
relações permanentes consistem em uma modificação no banco de dados.
3.4 – Modificação no Banco de Dados com Álgebra Relacional
As modificações no banco de dados na álgebra relacional são expressões usando o
operador de atribuição. As atribuições são feitas para relações presentes no banco de
dados atual.
3.4.1 – Remoção
Uma requisição de remoção é expressa da mesma forma que uma consulta, contudo, em
vez de exibir o resultado, removem-se as tuplas da seleção. Podemos remover apenas
tuplas inteiras, não sendo possível a remoção de parte das tuplas. A remoção é expressa
André Luís Duarte
54
[email protected]
da álgebra relacional como r  r  E
onde r é uma relação e E é uma expressão
(consulta) da álgebra relacional.
Exemplo 13
Remova todas as contas de André.
depósito  depósito   nomecliente" André" depósito 
Exemplo 14
Remova todos os empréstimos com número entre 1300 e 1500.
depósito  depósito   númeroempréstimo1300númeroempréstimo1500depósito 
Exemplo 15
Remova todas as contas da agência localizada em Itajubá.
r1   cidadeagência"Itajubá" depósito  agência 
r 2   nomeagência,númeroconta,nomecliente,saldo r1
depósito  depósito  r 2
Observe que utilizamos relações temporárias a fim de simplificar nossa expressão.
3.4.2 – Inserção
Utilizando a operação de inserção podemos inserir dados em uma relação especificando
os valores ou escrevendo uma consulta cujo resultado é o conjunto de tuplas a serem
inseridas.
Obviamente, os valores dos atributos para tuplas inseridas precisam ser elementos do
domínio do atributo. Na álgebra relacional expressamos uma inserção por
r  r  E , onde r é uma relação e E é uma expressão da álgebra relacional. A
expressão de uma tupla simples é expressa fazendo com que a expressão E seja uma
relação constante contendo uma única tupla.
Exemplo 14
55
André Luís Duarte
Banco de Dados
Suponha que desejamos inserir o fato de que André tem $1200 na conta 9732 na
agência Boa Vista.
depósito  depósito  " BoaVista " ,9732, " André",1200
De modo geral, na maioria dos casos, iremos inserir tuplas que são o resultado de uma
consulta.
Exemplo 15
Suponha que desejamos abrir uma poupança de $200 para todos os clientes com
empréstimo na agência “Boa Vista”. Digamos que o número do empréstimo sirva como
número da conta para novas poupanças. Teremos:
r1   nome agência " BoaVista " empréstimo
r 2   nome agência, número empréstimo, nome cliente r1
depósito  depósito  r 2  200
Como podemos observar, especificamos diversas tuplas ao invés de uma só. Cada tupla
tem nome-agência (”BoaVista”), um número-empréstimo (serve como número da
conta), o nome-cliente de empréstimo (o qual está sendo dado uma conta) e o saldo
inicial ($200).
3.4.3 – Atualização
Em muitas situações (a grande maioria) desejaremos alterar um valor de uma tupla sem
mudar todos os valores da tupla. Se fizermos esta mudança usando remoção e inserção,
podemos não ser capaz de reter os valores que não queremos alterar. Em vez disto,
usaremos a operação alteração representada pela letra grega
possui a forma

(delta minúsculo) e que
 A  Er  , onde r é o nome de uma relação com atributo A que recebe
o valor da expressão E. A expressão E é qualquer expressão aritmética, ou não,
envolvendo constantes e atributos no esquema da relação r.
Exemplo 16
Suponha que o pagamento de juros esteja sendo feito e o saldo de todas as contas seja
acrescido em 5%.
André Luís Duarte
56
[email protected]
 saldo  saldo 1,05depósito 
A “instrução” acima é aplicada uma vez para cada tupla em depósito.
Exemplo 17
Suponha que contas com saldos superiores a $10.000 recebam 6% enquanto todas as
outras recebam 5%.
 saldo  saldo 1,06 saldo10.000 depósito 
 saldo  saldo 1,05 saldo10.000 depósito 
Note que a ordem na qual aplicamos as operações de atualização é muito importante. Se
mudarmos a ordem no exemplo anterior, uma conta cujo saldo esteja pouco abaixo de
$10.000 receberia juros de 11,3%.
3.5 – Visões de Usuário
Até agora operamos no nível do modelo conceitual, pois as coleções de relações as
quais estamos manipulando são as relações efetivamente armazenadas no banco de
dados.
Sabemos que não é de interesse de empresas que todos os seus funcionários e/ou
usuários vejam todo o modelo conceitual do banco de dados. Considere um caixa que
precisa saber o número de empréstimo de um cliente, mas não precise saber a quantia do
empréstimo. Esta é uma consideração de segurança que requer que alguns dados sejam
“escondidos” de certos usuários. O caixa em questão deve ver somente a relação
 nome agência, empréstimo, nome cliente empréstimo
Operando com visões, podemos criar coleção de relações personalizadas que combinem
melhor com a intuição do usuário do que todo o modelo conceitual.
Exemplo 18
Suponha que um funcionário do departamento de propaganda gostaria de ver uma
relação com os clientes de cada agência. Esta relação lista as pessoas que possuem uma
conta ou um empréstimo naquela agência. A relação seria resultado da expressão
57
André Luís Duarte
Banco de Dados
 nomeagência,nomecliente depósito 
  nomeagência,nomecliente empréstimo
Qualquer relação que não faça parte do modelo conceitual, mas que é visível ao usuário
como uma “relação virtual” é chamada de visão do usuário.
Levando em consideração que as relações armazenadas no banco de dados podem ser
modificadas, geralmente não é possível (nem recomendável) armazenar uma relação
correspondente a uma visão. Logo, uma visão deve ser recomposta para cada vez que
uma expressão se refira a ela.
Quando uma visão é definida, o banco de dados necessita armazenar a definição
propriamente dita da visão. Portanto, esta definição não é uma expressão da álgebra
relaciona, ao contrário, uma definição de visão produz o armazenamento de uma
expressão que deve substituir referências à visão em consultas que a empregam.
3.5.1 – Definição de Visão
Para definir uma visão, precisamos dar a ela um nome e estabelecer a consulta que
computa a visão. A forma da instrução é create view u as <expressão de consulta>
onde <expressão de consulta> é qualquer expressão de consulta válida da álgebra
relacional. O nome da visão é representado por u.
Considere a visão consistindo em agências e seus clientes. Assuma que desejamos que
esta visão se chame todos-clientes. Definimos esta visão como:
create view todos-clientes as
 nome agência, nome cliente depósito 
  nome agência, nome cliente empréstimo 
Uma vez que seja definida uma visão, o nome pode ser usado para referenciar a relação
virtual que a visão gera. Podemos achar todos os clientes da agência Boa Vista
escrevendo
 nomecliente  nome agência"BoaVista" todos  clientes  .
André Luís Duarte
58
[email protected]
3.5.2 – Atualizações com Visões e Valores Vazios
Pudemos ver que as visões são muito úteis para auxiliar nas consultas no banco de
dados. Porém, um problema significativo ocorre quando utilizamos visões para efetuar
inserções, atualizações e remoções no banco de dados. A dificuldade é que para efetuar
uma modificação, a estrutura da visão deve ser traduzida para uma modificação da
estrutura das relações presentes no modelo conceitual do banco de dados.
Exemplo 18
Considere o caixa que precisa ver todos os dados de empréstimo na relação empréstimo,
exceto quantia-empréstimo. Digamos que info-empréstimo seja o nome da visão dada ao
caixa. Esta será definida como:
create view info-empréstimo as
 nome agência, númeroempréstimo, nomecliente empréstimo
Uma vez que definimos que um nome de visão pode aparecer em qualquer lugar onde é
permitido o nome de uma relação, o caixa poderá escrever
info  empréstimo  info  empréstimo  " BoaVista ",3," João"
Esta inserção precisa ser representada por uma inserção na relação empréstimo. No
entanto, para inserir uma tupla em empréstimo, precisamos ter algum valor para
quantia. Existem duas abordagens razoáveis para orientar esta inserção:
 Rejeitar a inserção e dar uma mensagem de erro ao usuário;
 Inserir uma tupla {(“BoaVista”,3,”João”,vazio)} na relação empréstimo;
O símbolo vazio significa que este é desconhecido ou que ele não existe. Todas as
comparações envolvendo vazio são falsas por definição.
create viewcidade-agência as
nome agência,cidade agênciaempréstimo  cliente 
59
André Luís Duarte
Banco de Dados
Esta visão lista as cidades nas quais vivem clientes com um empréstimo. Considere a
seguinte inserção por meio dessa visão.
cidade  agência  cidade  agência  " Avenida" , " StaRita "
O único método possível para inserirmos tuplas na relação empréstimo e cliente é
inserirmos {(“Avenida”,vazio,vazio,vazio)} na relação empréstimo e na relação cliente
inserirmos {(vazio,vazio,”StaRita”)}.
Então obtemos as relações
nome-agência
Centro
BPS
Boa Vista
Centro
Morro Chic
Varginha
Canudos
Sul
Centro
Boa Vista
Avenida
Avenida
número-empréstimo
17
23
15
14
93
11
29
16
18
25
10
nulo
nome-cliente quantia
Junior
1000
André
2000
Jussara
1500
Ana
1500
Lislei
500
Duarte
900
Davi
1200
Talita
1300
Lucas
2000
Mariana
2500
Tiago
2200
nulo
nulo
Nome-cliente
rua
cidade-cliente
Junior
Rodrigues
Itajubá
André
Seabra
Maria da Fé
Jussara
Rodrigues
Itajubá
Lislei
Seabra
Maria da Fé
Samuel
Cardoso
Pouso Alegre
Duarte
Arlindo
Brasópolis
Davi
Zaroni
Paraisópolis
Talita
Major
Brasópolis
Lucas
Coronel
Piranguinho
Mariana
Nova
Sta Rita
Tiago
Corrêa
Cristina
Mateus
Lamounier
Brasópolis
Sta Rita
nulo
nulo
Tabela3.22 – Tuplas inseridas em empréstimo e cliente
André Luís Duarte
60
[email protected]
O resultado é insatisfatório. Se fizermos uma consulta com a expressão
 nomeagência,cidadecliente cidade  agência  , o resultado não incluirá a tupla inserida
{(“Avenida”,”StaRita”)}, pois todas as comparações envolvendo vazio são definidas
como falsas.
61
André Luís Duarte
Banco de Dados
Capítulo 4 – Linguagem Relacional Comercial
4.1 – Introdução
Vimos no capítulo 3 um exemplo de linguagem formal que fornece uma notação
concisa para representar consultas, entretanto, sistemas comerciais de banco de dados
requerem uma linguagem mais “amigável” ao usuário.
As linguagens relacionais comerciais se baseiam em linguagens formais (álgebra
relacional, cálculo relacional de tuplas e cálculo relacional de domínios) ou na
combinação destas na maneira de implementar a manipulação de banco de dados.
Podemos citar como exemplos, a linguagem SQL, QBE e Quel, para manipulação de
banco de dados. Embora seja comum referenciá-las como “linguagens de consulta”, elas
possuem muitas outras capacidades. Por exemplo, elas incluem recursos para definição
da estrutura de dados, modificação dos dados e recursos para especificar restrições de
segurança.
4.2 – SQL
4.2.1 – Histórico
A linguagem SQL original, foi desenvolvida no laboratório de pesquisas da IBM em
San Jose. Originalmente denominada “Sequel”, foi implementada como parte do projeto
System R no início dos anos 70. A linguagem evoluiu desde então e passou a ser
chamada SQL.
Em 1986, o American National Standard Institute (ANSI) publicou um primeiro padrão
para a linguagem SQL. Rapidamente a linguagem estabeleceu-se claramente como uma
linguagem padrão para banco de dados relacional.
A linguagem SQL3 possui diversas partes.
 Data Definition Language (DDL) – fornece comandos para definição de
esquemas de relação, remoção de relação, criação de índices e modificação de
esquemas de relação.
3
Dependendo do SGBD utilizado, a SQL poderá possuir diferenças sutis em sua capacidade de
manipulação.
André Luís Duarte
62
[email protected]
 Interactive Data Manipulation Language (DML) – inclui uma linguagem de
consulta baseada na álgebra relacional e no cálculo relacional de tupla.
Compreende também comandos para inserir, remover e modificar tuplas em um
banco de dados.
 Embeded Data Manipulation Language – a forma embutida da SQL é projetada
para utilização em linguagens de programação como Pascal, Fortran, C, etc.
 Views Definitions – a SQL DDL inclui também comandos para definição de
visualizações.
 Authorization – a SQL DDL inclui comandos para especificação de autorização
de acesso a relações e visões.
 Integrity – a linguagem SQL original incluía comandos para especificação de
instruções complexas de integridade. As versões da SQL fornecem apenas uma
forma limitada de verificação de integridade.
 Transaction control – a SQL inclui comandos para especificação do início e fim
de transações. Diversas implementações permitem o trancamento explicito de
dados para controle de concorrência.
4.2.2 – Estrutura básica
A estrutura básica de uma expressão SQL consiste em três cláusulas: select, from e
where.
 A cláusula “select” corresponde à operação projeção da álgebra relacional. É
usada para listar os atributos desejados no resultado de um a consulta.
 A cláusula “from” corresponde á operação produto cartesiano da álgebra
relacional. Ela lista as relações a serem examinadas na avaliação da expressão.
 A cláusula “where” corresponde ao predicado de seleção da álgebra relacional.
Consiste em um predicado envolvendo atributos de relação que aparecem na
cláusula “from”.
Uma consulta típica SQL assume a forma:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
Listagem 4.1 – Forma geral de uma consulta SQL.
63
André Luís Duarte
Banco de Dados
Cada “Ai” representa um atributo e cada “ri” é uma relação. “P” é um predicado. Esta
expressão é equivalente à expressão da álgebra relacional
 A1, A2,...,An  p r1 r 2  ...  rm
Figura 4.1 – Expressão equivalente à uma consulta SQL.
Caso a cláusula where seja omitida, o predicado “P” é verdadeiro. A lista
“A1,A2,...An” pode ser substituída pelo “*” indicando todos os atributos de todas
as relações presentes na cláusula from. O resultado de uma consulta SQL é, obviamente,
uma relação.
Exemplo 1
Dê os nomes de todas as agências na relação depósito.
select nome_agência
from depósito
Listagem 4.2 – Exemplo de seleção simples.
4.2.3 – Operações de conjuntos e tuplas duplicadas
Linguagens de consultas formais consideram relações como sendo conjuntos, logo, não
há ocorrência de tuplas duplicadas na relação. Na prática, em sistemas comerciais, esta
eliminação é dispendiosa e na maioria das vezes desnecessária. Todavia, a SQL permite
duplicações em relações. Nos casos em que desejamos forçar a eliminação de
duplicações, inserimos a palavra reservada “distinct” depois da cláusula select. Logo a
consulta do exemplo anterior seria:
select distinct nome_agência from depósito
Listagem 4.3 – Eliminação de duplicações em um select.
4.2.4 – Operações com conjuntos
A SQL original inclui as operações union, intersect e minus que operam sobre relações.
Os exemplos aqui são os contidos no capítulo 3.
a) Localize todos os clientes que possuem uma conta na agência “Morro Chic”.
select distinct nome_cliente
from depósito
where nome_agência = “Morro Chic”
Listagem 4.4 – Uso da cláusula “where” na relação depósito.
b) Podemos encontrar todos os clientes com um empréstimo na agência “Morro Chic”.
André Luís Duarte
64
[email protected]
select distinct nome_cliente
from empréstimo
where nome_agência = “Morro Chic”
Listagem 4.4 – Uso da cláusula “where” na relação empréstimo.
c) Para achar todos os clientes com um empréstimo, uma conta, ou ambos na agência
“Morro Chic”.
(select distinct nome_cliente
from depósito
where nome_agência = “Morro Chic”)
union4
(select distinct nome_cliente
from empréstimo
where nome_agência = “Morro Chic”)
Listagem 4.5 – Uso da operação union.
d) Da mesma forma, para achar todos os clientes que possuem um empréstimo e uma
conta na agência “Morro Chic” escrevemos:
(select distinct nome_cliente
from depósito
where nome_agência = “Morro Chic”)
intersect
(select distinct nome_cliente
from empréstimo
where nome_agência = “Morro Chic”)
Listagem 4.6 – Uso da operação intersect.
e) Para encontrar todos os clientes da agência “Morro Chic” que possuem uma conta
mas não possuem um empréstimo escrevemos:
(select distinct nome_cliente
from depósito
where nome_agência = “Morro Chic”)
minus
(select distinct nome_cliente
4
Como padrão, a operação union elimina tuplas duplicadas. Para reter a duplicação precisamos utilizar
union all no lugar de union.
65
André Luís Duarte
Banco de Dados
from empréstimo
where nome_agência = “Morro Chic”)
Listagem 4.7 – Uso da operação minus.
Apesar da operação union fazer parte da SQL padrão ANSI, diversos outros padrões não
dão suporte a ela. As operações intersect e minus faziam parte da SQL original (Sequel),
mas não foram incluídas no padrão ANSI.
4.2.5 – Predicados e junções
A SQL original não possui uma representação da operação junção natural. Porém, é
relativamente simples escrever uma expressão em SQL para a junção natural. Logo, se
temos a expressão
 nome _ cliente,cidade_ cliente empréstimo  cliente 
Podemos construir a expressão equivalente SQL:
select distinct cliente.nome_cliente, cidade_cliente
from empréstimo, cliente
where empréstimo.nome_cliente = cliente.nome_cliente
Listagem 4.8 – Representação da junção natural em SQL.
Exemplo 2
Dê o nome, a cidade de todos os clientes com empréstimos na agência “Boa Vista”.
Para efetuarmos essa consulta, precisamos enunciar duas restrições na cláusula where
utilizando o conector lógico and.
select distinct cliente.nome_cliente, cidade_cliente
from empréstimo, cliente
where empréstimo.nome_cliente = cliente.nome_cliente and
nome_agência = “Boa Vista”
Listagem 4.9 – Utilização do conector lógico and.
Em SQL, podemos utilizar os conectivos lógicos and, or e not, expressões aritméticas
envolvendo os operadores +, -, *, / em constantes ou valores de tuplas. Algumas
implementações de SQL incluem funções aritméticas especiais para tipos como data,
tempo, dinheiro, cadeia de caractere, etc.
André Luís Duarte
66
[email protected]
A SQL inclui um operador de comparação “between” para simplificar algumas
construções da cláusula where que especificam que um valor esteja entre outros dois.
Exemplo 3
Encontre os números de contas cujos saldos sejam maiores do que $900,00 e menores
do que $10.000,00 inclusive. Teremos para isto a expressão
select número_conta
from depósito
where saldo between 900 and 10000
Listagem 4.10 – Utilização do operador between.
Da mesma forma podemos usar o operador de comparação not between.
O SQL possui também um operador de comparação de cadeia de caracteres. Os padrões
para comparação são descritos utilizando dois tipos de caracteres juntamente com o
operador de comparação “like”:
 O caractere “%” equivale a qualquer subcadeia;
 O caractere “_” equivale a qualquer caractere;
 Os padrões são “case sensitive”.
Exemplo 4
Dê os nomes dos clientes cujas ruas incluem a subcadeia “en”. Teremos a expressão:
select nome_cliente
from cliente
where rua like “%en%”
Listagem 4.11 – Utilização do operador de comparação like.
poderíamos ter as seguintes expressões e suas equivalências:
Expressão
“Boa%”
%en%
_en%
“_ _ _”
“_ _ _%”
Equivalência
Qualquer cadeia começada por “Boa”.
Qualquer cadeia contendo a subcadeia “en” (Centro, Avenida).
Qualquer cadeia contendo a subcadeia “en” iniciando na segunda posição.
Qualquer cadeia contendo exatamente três caracteres.
Qualquer cadeia contendo pelo menos três caracteres.
Tabela 4.1 – Expressões contendo operadores de comparação de cadeias de caracteres.
67
André Luís Duarte
Banco de Dados
Para que os padrões possam ser utilizados como qualquer caractere, devemos
especificar um caractere denominado “caractere de escape” através da palavra chave
“escape”. Ilustramos definindo o caractere “\” (barra invertida) como sendo o caractere
de escape nos seguintes padrões:
Expressão
like “ab\%cd%” escape “\”
like “ab\\%cd%” escape “\”
Equivalência
Todas as cadeias começada por “ab%cd”.
Todas as cadeias começada por “ab\cd”.
Tabela 4.2 – Expressões contendo um caractere de escape.
A SQL permite a busca por não equivalência utilizando o operador de comparação not
like.
4.2.6 – Pertinência a conjuntos
O conectivo “in” testa a pertinência a um conjunto onde o conjunto é uma coleção de
valores produzidos por uma cláusula select. O conectivo “not in” testa a não pertinência
de um conjunto. Considere a consulta d) anterior, podemos reescreve-la da seguinte
forma
select distinct nome_cliente
from empréstimo
where nome_agência = “Morro Chic” and
nome_cliente in (
select nome_cliente
from depósito
where nome_agência = “Morro Chic”)
Listagem 4.12 – Utilização do conectivo in.
Podemos também testar a pertinência a uma relação arbitrária. A SQL usa a notação
<v1, v2, ..., vn> para representar uma tupla de n elementos contendo os valores
v1, v2, ..., vn. Podemos reescrever a consulta anterior como
select distinct nome_cliente
from empréstimo
where nome_agência = “Morro Chic” and
<nome_agência, nome_cliente> in (
select nome_agência, nome_cliente
from depósito)
Listagem 4.13 – Utilização de uma tupla de n elementos.
André Luís Duarte
68
[email protected]
Note que podemos escrever a mesma consulta de diversas formas em SQL, poderíamos
reescrever a consulta e) da seguinte forma
select distinct nome_cliente
from depósito
where nome_agência = “Morro Chic” and
nome_cliente not in (
select nome_cliente
from empréstimo
where nome_agência = “Morro Chic”)
Listagem 4.14 – Utilização do conectivo not in.
4.2.7 – Variáveis tupla
Uma variável tupla precisa estar associada a uma relação particular. As variáveis tuplas
são definidas na cláusula from. Assim podemos reescrever a consulta do exemplo 2
como
select distinct c.nome_cliente, c.cidade_cliente
from empréstimo e, cliente c
where e.nome_cliente = c.nome_cliente and
e.nome_agência = “Boa Vista”
Listagem 4.15 – Utilização de variáveis tupla.
A variável tupla é definida na cláusula from colocando-a logo após o nome da relação à
qual ela está associada separada por um espaço. A variável tupla além de possibilitar
uma simplificação em consultas extensas onde apareçam várias vezes o nome da
relação, elas são essenciais na comparação de duas tuplas de uma mesma relação5.
Exemplo 5
Ache todos os clientes que possuem uma conta em alguma agência na qual “Lucas”
tenha uma conta.
select distinct t.nome_cliente
from depósito s, depósito t
where s.nome_cliente= “Lucas” and
s.nome_agência = t.nome_agência
Listagem 4.15 – Utilização de variáveis tuplas para a mesma relação.
5
Isto era feito utilizando a operação renomear na álgebra relacional.
69
André Luís Duarte
Banco de Dados
4.2.8 – Comparação de conjuntos
Utilizamos o conectivo in quando desejamos testar a igualdade entre os valores de
atributos. Porém, vamos supor que desejássemos os nomes de todas as agências que
possuem ativos maiores do que alguma agência localizada em Itajubá. Podemos obter o
resultado através da expressão
select distinct t.nome-agência
from agência t, agência s
where t.ativos > some (
select ativos
from agência
where cidade_agência = “Itajubá”)
Listagem 4.16 – Utilização do conectivo some.
A consulta mais interna gera todos os valores de ativos para as agências em Itajubá, a
comparação >some, na cláusula where do select externo é verdadeira se o valor da tupla
ativos for maior do que algum (pelo menos um) elemento do conjunto de todos os
ativos. Podemos utilizar também <some, >=some, <=some e <>some. Observe que
=some é idêntico a in.
Por outro lado, e se nossa consulta fosse encontrar os nomes de todas as agências que
possuem ativos maiores do que todas as agências em Itajubá. A construção “> all”
corresponde à frase “maior do que todos”. Logo podemos escrever a consulta como
select nome_agência
from agência
where ativos >all (
select ativos
from agência
where cidade_agência = “Itajubá”)
Listagem 4.17 – Utilização da construção >all.
Também podemos usar construções <all, >=all, <=all, =all e <>all. As construções
in, >some, >all e assim por diante nos permite testar um único valor contra elementos
de um conjunto inteiro. Como o select gera um conjunto de tuplas, podemos às vezes,
quere comparar conjuntos para determinar se um conjunto contém todos os elementos
de um outro conjunto. Para isto usamos as construções contains e not contains.
André Luís Duarte
70
[email protected]
Exemplo 6
Dê todos os clientes que possuem uma conta em todas as agências localizadas em
“Maria da Fé”. Para cada cliente, precisamos ver se o conjunto de todas as agências na
qual um cliente possui uma conta contém o conjunto de todas as agências em “Maria da
Fé”.
select distinct e.nome_cliente
1
from depósito s
where (
select t.nome_agência
2
from depósito t
where s.nome_cliente = t.nome_cliente)
contains (
select nome_agência
3
from agência
where cidade_agência = “Maria da Fé”)
Listagem 4.18 – Utilização da construção cotains.
A subconsulta (3) fornece todas as agências de “Maria da Fé”. A subconsulta (2)
fornece todas as agências nas quais s.nome_cliente têm uma conta. Assim a consulta
externa (1) pega cada cliente e testa se o conjunto de todas as agências em que cada
cliente possui uma conta contém o conjunto de todas as agências em “Maria da Fé”.
4.2.9 – Teste de relações vazias
O SQL inclui um recurso para testar se uma consulta possui alguma tupla em seus
resultados. A construção “exists” retorna true se o argumento da consulta é não vazia.
Podemos reescrever a consulta d) da seguinte forma.
select nome_cliente
from cliente c
where exists(
select *
from depósito d
where d.nome_cliente = c.nome_cliente
and nome_agência = “Morro Chic”)
and exists(
select *
from empréstimo e
71
André Luís Duarte
Banco de Dados
where e.nome_cliente = c.nome_cliente and
nome_agência = “Morro Chic”
Listagem 4.19 – Utilização da construção exists.
A não existência de uma tupla em uma subconsulta pode ser testada usando-se a
construção not exists. Suponha que desejamos todos os clientes da agência “Morro
Chic” que têm uma conta mas não um empréstimo nesta agência.
select nome_cliente
from cliente c
where exists(
select *
from depósito d
where d.nome_cliente = c.nome_cliente and
nome_agência = “Morro Chic”)
and not exists(
select *
from empréstimo e
where e.nome_cliente = c.nome_cliente and
nome_agência = “Morro Chic”)
Listagem 4.20 – Utilização da construção not exists.
Finalizando, podemos utilizar a construção minus para reescrever o código do Exemplo
6 como segue.
select distinct s.nome_cliente
from depósito s
where not exists((
select nome_agência
from agência
where cidade_agência
= “Maria da Fé”)
minus(
select t.nome_agência
from depósito t
where s.nome_cliente = t.nome_cliente))
Listagem 4.21 – Utilização da construção minus
André Luís Duarte
72
[email protected]
Exercício de fixação
Analise os resultados das expressões para o código da Listagem 4.21 e mostre que
retornam realmente o resultado obtido na consulta original.
4.2.10 – Ordenação da exibição de tuplas
O SQL fornece algum controle sobre a ordem na qual as tuplas serão exibidas. A
cláusula “order by” ocasiona o aparecimento de tuplas no resultado de uma consulta em
uma ordem determinada. Para listar em ordem alfabética todos os clientes, por exemplo,
escrevemos:
select distinct nome_cliente
from empréstimo
where nome_agência = “Centro”
order by nome_cliente
Listagem 4.22 – Utilização da cláusula order by.
Por padrão o SQL classifica as tuplas em ordem crescente, porém, podemos utilizar
desc para especificar a ordem decrescente como classificação de tuplas.
select *
from empréstimo
order by quantia desc, número_empréstimo asc
Listagem 4.23 – Utilização da construção desc.
4.2.11 – Funções agregadas
A SQL oferece a possibilidade de computar funções em grupos de tuplas usando a
cláusula group by. Os atributos utilizados na cláusula group by são usados para
formarem grupos de tuplas. O SQL também inclui função para computar a média (avg),
mínimo (min) máximo (max), total (sum) e contar (count). Estas operações são
chamadas funções de agregação porque operam em um grupo de tuplas e geram um
único valor.
Exemplos
f) Suponha que desejamos encontrar o saldo médio de conta em cada agência.
select nome_agência, avg(saldo)
from depósito
group by nome_agência
Listagem 4.24 – Função avg.
73
André Luís Duarte
Banco de Dados
g) Poderíamos também querer saber o número de depositantes de cada agência.
select nome_agência, count(distinct nome_cliente)
from depósito
group by nome_agência
Listagem 4.25 – Função count.
h) Um caso interessante seria listar as agências nas quais a média dos saldos é maior do
que $1200. Note que a condição aqui é aplicada ao grupo em vez de a tupla simples.
Para isto, utilizamos a cláusula “having”. Os predicados na clausula having são
aplicados depois da formação dos grupos para que funções de agregação possam ser
usadas, assim, teremos o código
select nome_agência, avg(saldo)
from depósito
group by nome_agência
having avg(saldo) > 1200
Listagem 4.26 – Utilização da cláusula having.
i) Dê as agências com a maior média de saldos. Em SQL as funções de agregação não
podem ser compostas. Isto significa que a construção max(avg(...)) não será permitida.
A estratégia é encontrar as filias cuja média de saldo é maior ou igual a todas as médias
de saldo.
select nome_agência
from depósito
group by nome_agência
having avg(saldo) >= all(
select avg(saldo)
from depósito
group by nome_agência)
Listagem 4.27 – Exemplo da utilização de função de agregação avg.
Às vezes, desejamos tratar toda a relação como um único grupo, pra isto, não incluímos
a cláusula group by. A função de agregação count é usada para contar o número de
tuplas numa relação. A notação para isto em SQL é count(*).
André Luís Duarte
74
[email protected]
Se uma cláusula where e uma cláusula having aparecem numa mesma consulta, o
predicado na cláusula where é aplicado primeiro. As tuplas que satisfazem o predicado
where são então colocadas em grupo por uma cláusula group by e então a cláusula
having é utilizadas para cada grupo.
Para ilustrar a utilização da cláusula where e having na mesma consulta, considere a
necessidade de se obter a média dos saldos de todos os depositantes que vivem em
“Maria da Fé” e têm pelo menos três contas.
select avg(saldo)
from depósito, cliente
where depósito.nome_cliente = cliente.nome_cliente and
cidade_cliente = “Maria da Fé”
group by depósito.nome_cliente
having count(distinct número_conta) >= 3
Listagem 4.28 – Utilização da construção where e having.
4.2.12 – A potência de SQL
A SQL é tão poderosa em expressividade quanto a álgebra relacional. A SQL inclui as
operações da álgebra relacional fundamental. A SQL permite resultados intermediários
para ser guardados em relações temporárias.assim, podemos codificar qualquer
expressão da álgebra relacional na SQL. Assim a SQL é rigorosamente mais poderosa
do que a álgebra relacional.
Muitas versões da SQL permitem que consultas em SQL sejam feitas a partir de um
programa escrito em uma linguagem de uso genérico como Pascal, Fortran, PL/1, C, etc.
A SQL não é tão poderosa quanto uma linguagem de programação, isto é, existem
consultas e construções que podem ser expressas em uma linguagem e não podem ser
implementadas em SQL.
4.2.13 – Modificação do Banco de Dados
Até o momento, nos restringimos em consultar informações do banco de dados.
Mostramos agora como adicionar, remover ou alterar as informações utilizando a SQL.
75
André Luís Duarte
Banco de Dados
4.2.13.1 – Remoção (delete)
Uma requisição de remoção é expressa da mesma forma que uma consulta. Podemos
remover apenas tuplas inteiras; não é possível remover valores em apenas alguns
atributos. Em SQL uma remoção é expressa como
delete r
where P
Listagem 4.29 – Expressão geral da remoção.
Onde r representa uma relação e P representa um predicado. As tuplas t de r para qual
P(t) é verdadeiro são removidas da relação r.
O comando delete opera em apenas uma relação. Se desejamos remover tuplas em n
relações, devemos utilizar um comando delete para cada relação. O predicado da
cláusula where pode ser tão complexo quanto uma cláusula where em um select.
Podemos também possuir um construção de delete sem a cláusula where. Com isso a
requisição
delete empréstimo
remove todas as tuplas da relação empréstimo6.
Outros exemplos de requisições de remoção em SQL.
delete depósito
where nome_cliente = “Smith”
delete empréstimo
where número_empréstimo between 1300 and 1500
delete depósito
where nome_agência in(
select nome_agência
from agência
where cidade_agência = “Itajubá”)
Listagem 4.30 – Exemplos de requisição de remoção de tuplas
6
Sistemas bem projetados requerem uma confirmação do usuário antes de executar comandos que
possam remover ou alterar informações armazenadas em qualquer parte do sistema.
André Luís Duarte
76
[email protected]
Devemos tomar cuidado quando o comando delete possuir uma cláusula select embutida
que faça referência à relação na qual as tuplas serão removidas, podemos encontrar
prováveis anomalias.
delete depósito
where saldo < (
select avg(saldo)
from depósito)
Listagem 4.31 – Possível anomalia em expressões aninhadas.
Exercícios de fixação
Descreva o que irá ocorrer quando as expressões da Listagem 4.30 forem executadas.
Descreva a anomalia que ocorre na execução da expressão contida na Listagem 4.31.
A SQL padrão não permite que a anomalia descrita anteriormente ocorra, pois na
realidade as tuplas que satisfaçam às condições são, primeiramente, marcadas e depois
que o processamento da requisição termina é que as tuplas serão removidas.
4.2.13.2 – Inserção (insert)
Para inserirmos um dado na relação, ou especificarmos uma tupla para ser inserida ou
escrevermos uma consulta cujo resultado será inserido na relação utilizamos o comando
insert. Para isto, os valores dos atributos para tuplas inseridas precisam ser membros do
domínio do atributo. Da mesma forma, as tuplas inseridas precisam ser do mesmo tipo.
A instrução mais simples é uma requisição para inserir uma tupla.
insert into depósito
values(“Boa Vista”, 9732, “Leonardo”, 1200)
Listagem 4.32 – Expressão mais simples para uma inserção.
No exemplo anterior, os valores inseridos estão dispostos na ordem em que os atributos
aparecem no esquema relacional. Porém podemos não lembrar a ordem dos atributos,
por isso a SQL permite que os atributos sejam especificados como parte da instrução
SQL.
77
André Luís Duarte
Banco de Dados
insert into depósito(número_conta, nome_cliente,
saldo, nome_agência)
values(9732, “Leonardo”, 1200, “Boa Vista”)
Listagem 4.33 – Utilização do nome dos atributos na requisição de inserção.
Podemos também desejar inserir tuplas baseadas no resultado de uma consulta.
Podemos então ter a expressão
insert into depósito
select nome_agência, número_empréstimo, nome_cliente, 200
from depósito
where nome_agência = “Boa Vista”
Listagem 4.34 – Inserção do resultado de uma consulta.
A SQL padrão proíbe ao select embutido referenciar as relações nas quais tuplas estão
sendo inseridas para que não ocorram inserções do tipo mostrados na listagem 4.35.
insert into depósito
select *
from depósito
Listagem 4.35 – Construções proibidas no SQL padrão.
Exercício de Fixação
Descreva o que irá ocorrer após a execução das requisições contidas nas Listagens 4.34
e 4.35.
4.2.13.2 – Atualização (update)
Em muitas situações, desejaremos alterar o valor de alguns atributos de algumas tuplas,
sem mudar toda a tupla. Isto só pode ser feito utilizando a instrução update. Como no
caso das instruções anteriores, podemos escolher as tuplas que serão atualizadas
utilizando uma consulta válida.
update depósito
set saldo = saldo * 1.06
Listagem 4.36 – Atualização simples
André Luís Duarte
78
[email protected]
A instrução anterior é aplicada uma vez para cada tupla na relação depósito.
update depósito
set saldo = saldo * 1.06
where saldo > 10000
update depósito
set saldo = saldo * 1.05
where saldo <= 10000
Listagem 4.36 – Utilização do comando update com where.
Como é o caso do delete e do delete, qualquer select embutido dentro de um update não
deve referenciar a relação que está sendo atualizada. Suponha a atualização a seguir
update depósito
set saldo = saldo * 1.05
where saldo > select avg(saldo)
from depósito
Listagem 4.37 – Exemplo de anomalia com update.
Exercício de Fixação
A instrução da Listagem 4.36 prevê um abono de 6% nos saldos acima de $10.000 e de
5% nos saldos abaixo de $10.000. O que poderá ocorrer se a ordem em que a
atualização é executada for invertida? A instrução na Listagem 4.37 deveria pagar 5%
de juros sobre contas cujos saldos sejam maiores do que a média de saldos. Porque esta
rotina na faz o que é desejado?
4.2.14 – Valores Vazios
Nossa discussão até agora sempre considerou que as tuplas estavam “completas”, ou
seja, não possuíam valores vazios. Em SQL, podemos atribuir valores a apenas alguns
atributos do esquema7, os demais receberão valores nulos, que no SQL padrão, são
representados por null. Considere o comando
insert into depósito
values (“Centro”, null, “Luciana”, 1200)
Listagem 4.38 – Exemplo da inserção de um valor nulo.
7
Veremos mais adiante como não permitir que isto ocorra em casos onde desejamos que o atributo seja
preenchido.
79
André Luís Duarte
Banco de Dados
Neste caso sabemos que Luciana possui uma conta na Agência Centro e possui saldo de
$1.200,00, mas não sabemos qual é o número desta conta. Logo, considere uma consulta
para verificar a tupla cujo número da conta é 1700.
Como o número da conta de Luciana não é conhecido, não podemos determinar se ele é
igual a 1700.
Todas as comparações envolvendo null, por definição resultam em false, inclusive o
teste de igualdade (número_conta = null). para saber se um determinado atributo possui
o valor null, escrevemos
select *
from empréstimo
where quantia is null
Listagem 4.39 – Testando a presença de valores nulos nas relações.
O predicado is not null testa a ausência de um valor vazio.
Todas as operações de agregação, ignoram tuplas com valores vazios, exceto o count,
nos atributos de seus argumentos. Isto gera bastante problemas ao utilizarmos funções
de agregação, por isto devemos projetar de forma adequada nosso sistema.
Exercício de Fixação
Cite um exemplo de problema que você identifica quando ocorre a utilização de funções
de agregação e a presença de valores vazios em uma relação.
4.2.15 – Visões
Para criarmos uma visão em SQL devemos utilizar o comando create view da seguinte
forma create view v as <expressão de consulta> onde v é o nome da visão e
<expressão de consulta> é qualquer expressão de consulta válida.
Exemplo 7
Crie a visão consistindo em nomes de agências e nomes de clientes. Assuma que
desejamos esta visão sendo chamada todos_clientes.
create view todos_clientes as
(select nome_agência, nome_cliente
from depósito)
André Luís Duarte
80
[email protected]
union
(select nome_agência, nome_cliente
from empréstimo)
Listagem 4.40 – Exemplo de criação de visão.
As anomalias da atualização de uma visão discutida no capítulo 3, também ocorrem em
SQL. Como o SQL permite que o nome de visão apareça em qualquer lugar onde possa
aparecer o nome de relação, utilizar visão em comandos insert, delete e update é
conceitualmente possível, mas veremos na prática que é bom evitar este tipo de
construção. Como resultado, muitos sistemas baseados em SQL impõem uma restrição
para modificações com visões, a saber;
“Uma modificação é permitida por uma visão apenas se a visão em questão está
definida em termos de uma única relação do banco de dados relacional”.
4.2.16 – Definição de Dados
Até o momento, assumimos relações que existiam previamente. Logicamente estas
relações (ou seus esquemas relacionais) precisa ser especificada para o sistema por meio
de uma linguagem de definição de dados (DDL).
A SQL DDL permite a especificação não apenas de um conjunto de relações, mas
também de informações sobre cada relação, incluindo:
 O esquema de cada relação;
 O domínio dos valores associados a cada atributo;
 O conjunto de índices a ser mantido para cada relação;
 Informações sobre segurança e autorização de acesso para cada relação;
 Restrições de integridade;
 A estrutura física de armazenamento de cada relação no disco.
Vamos neste ponto nos ater somente a definição de esquema e inserir os outros
conceitos na medida em que forem desenvolvidos.
Uma relação em SQL é definida utilizando o comando create table:
create table r (A1 D1, A2 D2, ..., An Dn)
81
André Luís Duarte
Banco de Dados
onde r é o nome da relação, cada Ai é o nome do atributo no esquema relacional r e Di é
o tipo de valores de dados do domínio de Ai. Podemos inserir algumas restrições de
integridade com o comando create table que ainda serão abordados.
Uma vez criada a relação, esta se encontra vazia. Devemos utilizar o comando insert a
fim de popular nossa relação com os dados. A forma mais geral para o comando insert
é:
insert into r values(v1, v2, ..., v3)
onde r é o nome da relação que está sendo populada, vi são os valores para cada campo
dentro do esquema de r. Existem algumas variações dependendo do SGBD utilizado,
estas sempre que possível serão mencionadas durante as aulas e colocadas no material
de apoio (arquivos PPT).
Para remover uma relação de um banco de dados SQL usamos o comando drop table no
seguinte formato:
drop table r
onde r é o nome da tabela a ser apagada do banco de dados. Alguns sistema SQL
possuem o comando delete r, onde r é o nome da relação que será deletada. Neste
ultimo caso, todas as tuplas da relação são apagadas, porém o esquema da relação
continua, podendo ser populado novamente.
O comando alter table geralmente é utilizado para adicionar atributos em um esquema
existente. Todas as tuplas na relação recebem valor null. A forma mais geral para o
comando é:
alter table r add A D
onde r é a relação existente, A e D são, consecutivamente, o nome do atributo e seu
domínio. O comando alter table aparece em muitos sistemas SQL, porém não faz parte
do SQL padrão.
André Luís Duarte
82
[email protected]
Capítulo 5 – Restrições de Integridade
5.1 – Introdução
As restrições de integridade fornecem meios para assegurar que mudanças feitas no
banco de dados por usuários autorizados não resultem na perda da consistência dos
dados. Assim, as restrições de integridade resguardam o banco de dados contra danos
acidentais.
Conhecemos algumas destas restrições em capítulos anteriores. São elas:

Declaração de chave: O conjunto de inserções e atualizações válidas é
restringido àquelas que não criam duas entidades com o mesmo valor de chave
candidata;

Forma de um relacionamento: Um relacionamento um para um ou um para
muitos restringe o conjunto de relacionamentos válidos entre entidades de uma
coleção de conjunto de entidades;
Geralmente uma restrição de integridade pode ser um predicado arbitrário que reaplica
ao banco de dados. No entanto os predicados arbitrários podem ser custosos para ser
testado. Para isto apresentamos formas mais simples e que possuem um custo bem
menor para ser testada.
5.2 – Restrições de domínio
Vimos nos capítulos anteriores que, para todo atributo de uma relação, precisamos ter
um domínio relacionado. Restrições de domínio é a forma mais elementar de restrições
de integridade. Elas podem ser testadas facilmente pelo sistema cada vez que um novo
item de dado é inserido no banco de dados.
5.2.1 – Tipos de Domínio
É possível que diversos atributos tenham o mesmo domínio. Por exemplo, nome_cliente
e nome_funcionário podem ter o mesmo domínio. Os domínios de saldo e
nome_agência, certamente precisam ser diferentes. É talvez menos claro se
nome_agência e nome_cliente devem ter o mesmo domínio. No nível de
implementação, tanto nome de agência quanto nome de cliente são cadeias de
83
André Luís Duarte
Banco de Dados
caracteres. Contudo, não podemos considerar como válida (normal) a consulta “dê todos
os clientes que possuem o mesmo nome de sua agência” como sendo uma consulta com
sentido. Assim se analisarmos o banco de dados no nível conceitual em vez de no nível
físico, nome_cliente e nome_agência certamente devem ter domínios distintos.
A própria definição de restrições de domínio permite testar os valores inseridos no
banco de dados e também testar consultas para assegurar que as comparações façam
sentido.
5.2.2 – Tipos de domínios em SQL
A SQL padrão suporta um conjunto restrito de tipos de domínio:

Cadeia com comprimento de caracteres fixo, com comprimento especificado
pelo usuário;

Número em ponto fixo, com precisão especificada pelo usuário;

Inteiro (um conjunto finto dos inteiros que é dependente da máquina);

Número de ponto flutuante, com precisão especificada pelo usuário;

Ponto flutuante e números de ponto flutuante com dupla precisão, com precisão
dependente da máquina;
Diversas implementações da SQL incluem um tipo data. Este tipo é usado para
representar datas e permitir processamento baseados em datas. Por exemplo, se x e y são
do tipo data, então x - y retorna o número de dias da data x até a data y.
5.2.3 – Valores vazios
Vimos que a inserção de tuplas incompletas pode introduzir valores vazios no banco de
dados. Podem ser problemáticos permitir a inserção de valores vazios, principalmente
em atributos chave do nosso sistema. Em casos como este desejamos proibir valores
vazios, restringindo o domínio de nome_cliente para excluir valores vazios.
A SQL padrão permite a declaração de domínio de um atributo inclua a especificação
not null, proibindo assim a presença de valores nulos neste atributo. Qualquer alteração
no banco de dados que provocar a inserção de valores vazios em um domínio not null
gera um diagnóstico de erro.
André Luís Duarte
84
[email protected]
Existem porém diversos casos que desejaremos permitir a inserção de tuplas com certos
atributos vazios, porém, é importante que no atributos que formam a chave de um
esquema relacional seja proibido valores nulos.
5.3 – Integridade Referencial
Frequentemente desejamos assegurar que um valor que aparece em uma relação para
um dado conjunto de atributos apareça também em um certo conjunto de atributos em
outra relação. Isto é chamado de integridade referencial.
5.3.1 – Conceitos básicos
Considere um par de relações r(R) e s(S), e a junção natural r |x| s. Pode acontecer que
exista uma tupla tr em r que não se junta a nenhuma tupla em s. Isto é, não existe ts em s
tal que tr R  S   ts R  S  . Tais tuplas são chamadas de tuplas soltas. Este tipo de
tupla pode ou não ser aceito.
Suponha que exista uma tupla em t1 na relação depósito com t1[nome_angencia] =
“Colonial”, mas não exista nenhuma tupla na relação agência para a agencia Colonial.
Isso seria uma situação indesejável e necessitaríamos de uma restrição de integridade
para evitar este tipo de tuplas soltas.
Analise o inverso e veja se neste caso gostaríamos de ter restrição de integridade.
Podemos fazer as seguintes considerações baseado nos exemplos mostrados
anteriormente:

nome_agência é chave primária no Esquema_agência;

nome_agência aparece em Esquema_depósito;

As tuplas de depósito se referem à chave primária de agência;

nome_agência em Esquema_depósito é uma chave estrangeira;

nome_agência é chave primária de um esquema que não é Esquema_depósito;

nome_agência em Esquema_agência não é uma chave estrangeira;

nome_agência não é a chave primária de nenhum outro esquema relacional;
85
André Luís Duarte
Banco de Dados
No exemplo do “Colonial”, a tupla t1 em depósito tem um valor na chave estrangeira
nome_agência que não aparece em agência. No exemplo que segue, a tupla t2 em
agência tem um valor em nome_agência que não aparece em depósito, mas
nome_agência não é uma chave estrangeira.
Note que integridade referencial surge de forma freqüente em nossos bancos de dados.
Por exemplo, podemos citar duas fontes deste tipo de restrição que são:

Toda relação surgindo de conjuntos de relacionamentos, tem restrições de
integridade referencial;

Conjunto de entidades fraco precisava incluir a chave primária do conjunto de
entidades do qual ele dependesse;
5.3.2 – Integridade Referencial em SQL
Um recurso de aperfeiçoamento de integridade foi aprovado como uma adição ao
padrão. Esse recurso permite a especificação de chaves primárias, candidatas e
estrangeiras, como parte da instrução create table.

A cláusula primary key da instrução create table inclui uma lista de atributos
que compreende a chave primária;

A cláusula unique key da instrução create table inclui uma lista de atributos que
compreende a chave candidata;

A cláusula foreign key da instrução create table inclui uma lista de atributos que
compreende a chave estrangeira e o nome da relação referida pela chave
estrangeira;
Na definição de cliente e agência, usamos a especificação not null para primary key.
Isto é um requerimento do próprio SQL. Qualquer atributo que seja membro de uma
chave candidata, deve ser declarado not null.
Exemplo 1
Vamos neste exemplo reformular as relações a fim de inserirmos a integridade vista até
este momento.
create table cliente (
nome_cliente
char(20) not null,
rua
char(30),
André Luís Duarte
86
[email protected]
cidade_cliente char(30),
primary key(nome_cliente))
create table agencia (
nome_agencia
char(20) not null,
ativos
inteiro,
cidade_agencia char(30),
primary key(nome_agencia))
create table deposito (
nome_agencia
char(20) not null,
numero_conta
char(10),
nome_cliente
char(20) not null,
saldo
inteiro,
primary key(numero_conta, nome_cliente)
foreign key(nome_agencia) references agencia,
foreign key(nome_cliente) references cliente)
5.4 – Asserções
Uma asserção é um predicado expressando uma condição que desejamos que o banco de
dados sempre satisfaça (vimos algumas formas especiais de asserções). Existem muitas
restrições que não podem ser expressas usando somente estas formas especiais de
restrições. Podemos citar:

A soma de todas as quantias de empréstimos para cada agência precisa ser
menor do que a soma de todos os saldos das contas na agência;

Todo cliente de empréstimo deve manter uma conta com o saldo mínimo de
$1000,00;
Quando uma asserção é feita, o sistema testa sua validade. Se a asserção é válida, então
qualquer futura modificação no banco de dados é permitida apenas se não violar esta
asserção. O problema é que asserções muito complexas podem sobrecarregar de forma
significativa o sistema gerenciador de banco de dados. Esta sobrecarga tem levado os
sistemas SQL a omitirem o suporte a asserções.
87
André Luís Duarte
Banco de Dados
Uma asserção que diz respeito a uma única relação toma a forma:
assert <nome_asserção> on <nome_relação> :
<predicado>
Por exemplo, se desejássemos definir uma restrição de integridade que não permita
saldos negativos, escrevemos:
assert restricao_saldo on deposito: saldo >= 0
O comando assert nos permite definir uma restrição de integridade que não poderia ser
expressa como restrição de domínio.
assert restricao_gerente on cliente:
nome_cliente <> nome_funcionario
Ou seja, nenhum funcionário do banco pode ser seu próprio gerente de conta.
As asserções podem ser restritas apenas para modificações no banco de dados, como no
caso de desejarmos evitar a criação de uma conta, a menos que o nome do cliente
apareça na relação cliente.
assert restricao_referencia on insertion to deposito:
exists (select * from cliente
where cliente.nome_cliente = deposito.nome_cliente)
Devido ao custo de processamento, asserções deram lugar a restrições de integridade
mais fáceis de serem testadas como restrição de domínio, chave e integridade
referencial.
5.5 – Gatilhos (TRIGGERS)
Um gatilho é um comando executado automaticamente pelo sistema como um efeito
colateral de uma modificação no banco de dados.
Para projetarmos um gatilho, precisamos:

Especificar as condições sob as quais o gatilho deve ser acionado;

Especificar as ações a serem tomadas quando o gatilho é executado;
André Luís Duarte
88
[email protected]
Exemplo 2
Suponha que ao invés de não permitir saldos negativos, o banco trate o saque a
descoberto ajustando o saldo para zero e criando um empréstimo no valor da quantia
saldo negativo. A este empréstimo é dado o numero da conta estourada. A condição
para o exemplo anterior, é uma atualização na relação depósito que resulte em um valor
de saldo negativo. Seja t a tupla com valor negativo em saldo. As ações a serem
tomadas são as seguintes:

Inserir uma nova tupla na relação empréstimo
s[nome_agencia] = t[nome_agencia]
s[numero_emprestimo] = t[numero_conta]
s[quantia] = -t[saldo]8
s[nome_cliente] = t[nome_cliente]

Ajusta t[saldo] para 0 (zero)
A SQL padrão não inclui gatilhos, porém, diversos sistemas implementam seus próprios
recursos não padrões de gatilhos. O gatilho para solucionar nosso problema anterior
poderia ser escrito na versão original do SQL da seguinte forma:
define trigger saldo_negativo
on update of depósito D (
if new D.saldo < 0
then (
insert into emprestimo values (
D.nome_agencia, D.numero_conta,
D.nome_cliente,
-new D.saldo)
update deposito S
set S.saldo = 0
where S.numero_conta = D.numero_conta))
8
Negamos o t[saldo] para obter a quantia do empréstimo, um número que deve ser positivo.
89
André Luís Duarte
Banco de Dados
Capítulo 6 – Normalização (ou normatização)
6.1 – Introdução
Dado um corpo de dados, como decidimos a estrutura lógica adequada a representar no
banco de dados, como decidimos a estrutura lógica adequada para estes dados? De
forma mais precisa, como decidimos que relações são necessárias e quais devem ser
seus atributos? Trata-se do problema de design do banco de dados (ou de forma mais
correta, do problema de design lógico do banco de dados).
Vamos considerar o seguinte banco de dados de fornecedores e peças.
FCOD
S1
S2
S3
S4
S5
FNOME
Smith
Jones
Blake
Clark
Adams
PCOD
PNOME
P1
Porca
P2
Trinco
P3
Parafuso
P4
Parafuso
P5
Arruela
P6
Engrenagem
Fornecedores
FSTATUS FCIDADE
20
Londres
10
Paris
30
Paris
20
Londres
30
Atenas
PCOR
Vermelho
Verde
Azul
Vermelho
Azul
Vermelho
PPESO
12
17
17
14
12
19
Peças
PCIDADE
Londres
Paris
Roma
Londres
Paris
Londres
FP
FCOD PNUM QTD
S1
P1
300
S1
P2
200
S1
P3
400
S1
P4
200
S1
P5
100
S1
P6
100
S2
P1
300
S2
P2
400
S3
P2
200
S4
P2
200
S4
P4
300
S4
P5
400
Tabela 6.1 – Banco de dados fornecedores e peças
A estrutura lógica deste banco de dados nos dá a sensação de exatidão do mesmo; é
obvio que as três relações são necessárias, que por exemplo, cor deve pertencer a
relação peças e não fornecedores, da mesma forma quantidade pertence a relação FP, e
assim por diante. Mas o que é que nos diz que isto deve ser desta forma? Podemos ter
alguma noção sobre este ponto, se verificarmos o que acontece caso o design seja
alterado. Suponhamos que o atributo STATUS saia da relação fornecedor e vá para a
relação FP. Teremos a Tabela 6.2 mostrada a seguir.
André Luís Duarte
90
[email protected]
Fica claro, ao analisarmos a Tabela 6.2, que a relação FP’ envolve inúmeras
redundâncias; o fato de que o fornecedor tenha um certo status tantas vezes quantas
hajam expedições deste fornecedor.
FCOD
S1
S1
S1
S1
S1
S1
S2
S2
S3
S4
S4
S4
PNUM
P1
P2
P3
P4
P5
P6
P1
P2
P2
P2
P4
P5
QTD
300
200
400
200
100
100
300
400
200
200
300
400
STATUS
20
20
20
20
20
20
10
10
30
20
20
20
Tabela 6.2 – Relação FP’ resultante da inserção do atributo STATUS na relação FP
A redundância leva a vários problemas. Por exemplo, após uma atualização, o
fornecedor S1 pode ser mostrado como tendo um status de 20 em uma tupla e um status
de 30 em outra.
A teoria da normalização (normatização) é basicamente uma formalização de idéias
simples de forma a ser aplicada no design do banco de dados.
O ponto fundamental a ser percebido é que uma relação, mesmo que normalizada, ainda
possui algumas propriedades indesejáveis; a teoria da normalização permite-nos
reconhecer estes casos, e mostra como as relações podem ser convertidas para uma
forma mais desejável.
6.2 – Conceitos
A teoria da normalização se constrói em torno do conceito de formas normalizadas. Dizse que uma relação está numa forma normalizada se satisfizer um conjunto específico
de limitações. Por exemplo, diz–se que uma relação está na primeira forma normal
(abreviatura 1FN) se satisfizer a limitação de que contenha apenas valores atômicos.
As vezes, há boas razões para desconsiderar os princípios de normalização (sob muita
análise). A única necessidade premente é que as relações sejam, no mínimo, na primeira
91
André Luís Duarte
Banco de Dados
forma normalizada. O design do banco de dados é uma tarefa extremamente complexa
(pelo menos em ambiente de banco de dados grande). A teoria da normalização é útil ao
processo, mas não é uma solução universal; “cada caso é um caso”.
Durante a fase de desenvolvimento, notamos muitas vezes que as técnicas de
normalização têm sido tratadas com pouca atenção (quando muito são utilizadas). Com
isto, podemos encontrar anomalias na tabela como tabelas sem chave primária,
redundância de informações e outras anomalias na atualização.
A normalização esta impregnada em banco de dados relacionais, ignora-la durante a
modelagem invalida qualquer garantia de que nossa base de dados é coerente e segura.
A normalização tem dois objetivos principais:
1. Garantir a integridade dos dados, evitando que informações inconsistentes sejam
inseridas;
2. Organizar e dividir as tabelas da forma mais eficiente possível, diminuindo a
redundância e permitindo a evolução do banco de dados com o mínimo de efeitos
colaterais;
Para conseguir estes objetivos devemos utilizar regras implementadas a fim de analisar
cada relação do nosso projeto. Estas regras são conhecidas como formas normais e são
seis as mais conhecidas:

1FN (1ª Forma Normal)

2FN (2ª Forma Normal)

3FN (3ª Forma Normal)

BCNF (Forma Normal de Boyce e Codd)

4FN (4ª Forma Normal)

5FN (5ª Forma Normal)
Uma forma normal engloba todas as anteriores, ou seja, para que uma tabela esteja na
2FN, ela obrigatoriamente deve estar na 1FN e assim por diante.
André Luís Duarte
92
[email protected]
Universe of relations (normalized and unnormalized)
1NF relations (normalized relations)
2NF relations
3NF relations
BCNF relations
4NF relations
PJ/NF (5NF) relations
Figura 6.1 – Esquema para formas de normalização
6.2.1 Revisão dos conceitos relevantes
Variáveis de relação
O termo “variáveis de relações”, de maneira simplificada, é análogo às relações
trabalhadas nos tópicos anteriores;
Chaves
Chave candidata  é um atributo ou conjunto de atributos únicos para cada registro.
Cada relação pode ter uma ou várias chaves deste tipo;
Chave primária  é (são) o(s) atributo(s) escolhido(s) dentro do conjunto das chaves
candidatas. Cada relação deve possuir uma chave deste tipo;
Chave alternativa  é (são) o(s) atributo(s) pertencente(s) ao conjunto da chave
candidata que não foram escolhidos como chave primária;
Chave estrangeira  é (são) o(s) atributo(s) que faz “a ligação” com uma chave
candidata de outra tabela;
6.2.2 Dependência Funcional (DF)
Começamos a estudar a teoria da normalização inserindo a definição de dependência
funcional (ou dependente funcional). Nós já temos alguma ideia do que venha a ser,
pois já trabalhamos de forma intuitiva com estas noções. Iremos agora formalizar estas
ideias.
93
André Luís Duarte
Banco de Dados
Dada uma relação R, o atributo y de R é funcionalmente dependente do atributo x de R,
simbolicamente R.x  R.y (R.x determina funcionalmente R.y), se, e apenas se, sempre
que duas tuplas de R equivalerem a seus valores x, elas devem necessariamente
equivaler a seus valores y.
Sempre que um atributo x identifica um atributo y, dizemos que entre eles há uma
dependência funcional. Temos, portanto, que x é o determinante e que y é o dependente.
No banco de dados fornecedor e peças, por exemplo, os atributos nome, status e cidade
da relação fornecedor são todos funcionalmente dependentes do atributo FCOD da
relação fornecedor, porque, dado um valor particular para fornecedor (F) F.FCOD,
existe precisamente um valor correspondente para F.FNOME, F.STATUS e F.CIDADE.
Simbolicamente representaríamos da seguinte forma:
F.FCOD  F.FNOME
F.FCOD  F.STATUS
F.FCOD  F.CIDADE
ou de forma mais sucinta,
F.FCOD  F.(NOME, STATUS, CIDADE)
Exemplo1
Dada uma determinada cidade (sem considerar cidades homônimas) sabemos o seu
estado e com o estado temos o país. Este fato pode ser representado da seguinte forma:
cidade  estado
estado  pais
Em outras palavras, estado é funcionalmente dependente de cidade e país é
funcionalmente dependente de estado. Ou ainda, cidade determina estado e estado
determina país.
Podemos inserir aqui também o conceito de dependência funcional completa. Isto
ocorre quando o atributo y da relação R é completamente funcionalmente dependente do
atributo x da relação R se for funcionalmente dependente de x e não funcionalmente
André Luís Duarte
94
[email protected]
dependente de qualquer subconjunto peculiar de x. Por exemplo, na relação fornecedor,
é certamente verdadeiro que o atributo cidade seja funcionalmente dependente do
atributo (composto) {F.COD, F.STATUS}, ou seja: F.(COD,STATUS)  F.CIDADE
Contudo, não é uma dependência funcional total, porque, naturalmente, também temos a
DF: F.COD  F.CIDADE. Notamos que CIDADE também é funcionalmente
dependente de FCOD apenas. Podemos então concluir que: se y é funcionalmente
dependente de x, mas não de forma total, então x deve ser composto.
6.2.2.1 Trivialidade
A dependência funcional trivial indica que um determinante com mais de um atributo
pode determinar seus próprios membros isolados. Por exemplo:

banco, agencia  banco DF trivial, pois, banco faz parte do determinante;

banco, agencia  agencia DF trivial, pois banco faz parte do determinante;
Quando um determinante identifica outro atributo qualquer, temos uma dependência
funcional não trivial (esta é a que nos interessa no processo de normalização):

banco, agencia  cidade
DF não trivial, pois cidade não faz parte do
determinante;
6.2.2.2 Transitividade
Se um atributo x determina y e se o atributo y determina z, podemos dizer que x
determina z de forma transitiva. Outra leitura é: existe uma dependência funcional
transitiva de x para z.
Exemplo2
cidade  estado
estado  país
cidade  país (cidade determina país de forma transitiva)
6.2.2.3 Dependência funcional irredutível à esquerda
Dizemos que o lado esquerdo de uma dependência funcional é irredutível quando o
determinante está em sua forma mínima. Temos a forma mínima quando não é possível
reduzir a quantidade de atributos determinantes sem perder a dependência funcional.
95
André Luís Duarte
Banco de Dados
Exemplo3
cidade, estado  país
Esta dependência funcional não está na forma irredutível à esquerda, pois podemos ter
somente o estado como determinante, ou seja:
estado  país 9
6.2.2.4 Diagrama de Dependência Funcional (Diagrama FD)
É conveniente representar as dependências funcionais de nossas relações através de um
diagrama de dependência funcional ou Diagrama de FDs. Um exemplo de como
construir estes diagramas é mostrado na figura a seguir:
PNOME
FNOME
FCOD
FSTATUS
PCOD
FCIDADE
PCOR
PPESO
PCIDADE
FCOD
QTD
PCOD
Figura 6.2 – Diagrama FD para o banco de dados de fornecedores e peças
6.2.2.4 Anomalias de atualização
São os problemas causados por inserções, atualizações ou deleções em uma base de
dados não normalizada. Para vermos este tipo de problema, suponha a relação mostrada
na Tabela 6.3.
Vamos propor algumas considerações sobre a relação em questão.

A tabela mistura dados do fornecedor, dados do fornecimento e dados de
localização do fornecedor;
9
Nem sempre estar na forma irredutível à esquerda significa possuir um determinante com apenas uma
coluna (atributo).
André Luís Duarte
96
[email protected]

Não existem tabelas auxiliares – todas as informações estão armazenadas nessa
tabela;

Nenhum campo pode ser nulo;

A chave primária é composta pelos campos CodFornecedor e CodPeça;
CodFornecedor
Nome
Status
Cidade
CodPeça
F1
F1
F1
F1
F1
F1
F2
F2
F3
F4
F4
F4
Fornecedor 1
Fornecedor 1
Fornecedor 1
Fornecedor 1
Fornecedor 1
Fornecedor 1
Fornecedor 2
Fornecedor 2
Fornecedor 3
Fornecedor 4
Fornecedor 4
Fornecedor 4
20
20
220
20
20
20
10
10
10
20
20
20
Londres
Londres
Londres
Londres
Londres
Londres
Paris
Paris
Paris
Londres
Londres
Londres
P1
P2
P3
P4
P5
P6
P1
P2
P2
P2
P4
P5
Preço
R$
10
20
15
25
12
5
10
20
20
20
25
12
Qtde
300
200
400
200
100
100
300
400
200
200
300
400
Valor
R$
3000
4000
6000
5000
1200
500
3000
8000
4000
4000
7500
4800
Tabela 6.3 – Relação Fornecedor peça
Baseando-se nestas considerações, podemos identificar algumas anomalias para as
operações DML executadas sobre esta relação.
Anomalias no comando INSERT

Não podemos inserir a localização de um fornecedor até que este forneça pelo
menos uma peça;

De fato, a relação não mostra que o fornecedor F5 está localizado em Atenas
(ele existe na vida real, mas não pode ser representado);

Sempre que cadastrarmos o fornecimento de uma peça, temos que repetir os
dados do fornecedor (Nome, Status e Cidade);
Anomalias no comando UPDATE

O valor do campo cidade aparece repetido várias vezes na relação;

Esta redundância aumenta a probabilidade de erro durante uma atualização, por
exemplo, se o fornecedor F1 se mudar de Londres para Amsterdã, teremos que
pesquisar todos os registros correspondentes e atualiza-los tomando o cuidado de
não deixar os dados inconsistentes;

O mesmo problema se aplica aos campos nome, status e preço;

O campo valor representa outra anomalia: ele é calculado pela multiplicação de
preço e quantidade se alterarmos o preço unitário ou a quantidade, teremos que
atualizar também o campo valor;
97
André Luís Duarte
Banco de Dados
Anomalias no comando DELETE

Se removermos todos os registros de um determinado fornecimento, apagaremos
não somente a ligação com as peças, mas também a informação da localização
do fornecedor (os problemas do INSERT e DELETE são na realidade duas faces
da mesma moeda);
6.2.2 Decomposição sem perdas
O processo de normalização envolve a quebra ou decomposição de uma tabela em
partes menores. Essa decomposição tem que ser reversível de modo que nenhuma
informação seja perdida no processo. O fato de uma decomposição ser ou não sem
perdas está intimamente ligado ao conceito de dependência funcional visto
anteriormente. Tendo como base a Tabela 6.4 e 6.5, analise as seguintes considerações:
Funcionário
André
Luís
Sexo
M
M
Salário
5.000,00
4.500,00
Tabela 6.4 – Relação funcionário original
a)
Funcionário
André
Luís
Sexo
M
M
Funcionário
André
Luís
Salário
5.000,00
4.500,00
b)
Funcionário
André
Luís
Sexo
M
M
Sexo
M
M
Salário
5.000,00
4.500,00
Tabela 6.5 – Relações formadas após a decomposição
Na decomposição da letra a é possível restaurar a tabela original, pois com o nome do
funcionário obtemos no sexo e o salário correspondentes (decomposição sem perdas).
Na letra b não é possível obter a tabela original, pois para o funcionário “André”, do
sexo “M”, encontramos dois salários na segunda tabela.
André Luís Duarte
98
[email protected]
6.2.3 Junção (Join)
Junção é o processo inverso da decomposição sem perdas. Na Tabela 6.5 letra a, temos
um atributo comum (Funcionário), podemos obter a tabela original fazendo a junção
das duas tabelas decompostas.
6.2.4 Projeções
Significa considerar apenas alguns campos de uma tabela. Nos capítulos anteriores este
assunto foi amplamente abordado.
6.3 Importância da Normalização
Os benefícios mais importantes obtidos com a normalização de uma base de dados são:

Tabela flexíveis de fácil manutenção: uma base sem as anomalias de
atualização e com um estrutura de armazenamento eficaz é mais simples de
atualizar e evoluir (embora em alguns casos o processo de consulta se torna mais
complexo, pois a normalização geralmente aumenta o número de tabelas do
banco).

Eliminação de redundância: sem redundância, as tabelas ficam menores, mais
consistentes e menos sujeitas a discrepâncias. Sempre que possível, evite o que o
mercado chama de “redundância controlada”, como o nome já diz, há de se
criar um controle (rotinas), o que aumenta a complexidade do sistema.
6.4 Normalização estudo de caso
Para efeito didático, vamos trabalhar com o seguinte exemplo: em uma empresa, os
produtos recebidos de um fornecedor são registrados em um formulário próprio, que
pode ser visualizado na Figura 6.3. Vamos informatizar esse processo criando uma base
de dados para armazenar as informações deste formulário.
A primeira versão para a base de dados poderia ser definida utilizando a Tabela 6.5
mostrada a seguir. Note que esta estrutura ainda não pode ser carregada em um banco de
dados relacional, pois possui atributos (CódItem, CódProd, Qtde, Preço e TotalItem)
compostos e não se apresenta no formato de uma tabela bidimensional10.
10
A palavra bidimensional talvez não seja muito bem utilizada aqui, pois, se a analisarmos esta
propriedade como sendo largura e altura, nossa tabela possui estas características. O fato aqui é que uma
linha e uma coluna seja bem definida e possua somente uma dimensão (possua somente um valor), o que
99
André Luís Duarte
Banco de Dados
Figura 6.3 – Formulário de fornecimento de mercadoria
não ocorre no exemplo já que uma linha começa com um valor para a primeira coluna e ao chegar na 7ª
coluna a linha se divide em três outras linhas passando a possuir três valores independentes.
André Luís Duarte
100
[email protected]
N°Nota
Data
CForn
Nome
Fone
End
1
15/6/2003
22
Empresa
Excalibur
33355255
Itamaracá,
15
2
20/7/2003
17
Cia Silva
33344787
Cardoso,
145
3
22/8/2003
22
Empresa
Excalibur
33355255
Itamaracá,
15
Citem
1
2
3
1
2
3
1
2
CProd
CA
BB
TC
TC
CM
RM
CA
BB
Prod
Chapa de aço
Bobina
Tab corridas
Tab corridas
Compensado
Ripas de mad
Chapa de aço
Bobina
Qtde
35
20
50
50
30
300
50
20
Preço
15
15
20
20
15
2
15
15
TItem
525
300
1000
1000
450
600
750
300
Tabela 6.5 – Relação retirada a partir dos formulários
6.4.1 1ª Forma normal (1FN)
O primeiro passo para normalizar a tabela anterior é aplicar as regras da primeira forma
normal. Podemos dizer que uma entidade está na primeira forma normal quando cada
atributo contém somente um valor, em somente um lugar. Essa exigência também é
conhecida como atomicidade de dados. As regras gerais para obtenção da 1FN são:

Não podemos ter atributos multivalorados. Neste caso, colocamos cada valor do
atributo em uma linha diferente e repetimos os dados de todas as outras colunas;

Não podemos ter atributos repetidos, como telefone1, telefone2, etc. A solução é
semelhante ao item anterior;

Todos os registros têm que ser diferentes;

A entidade não pode ter mais de duas dimensões;

Cada atributo deve ter somente um tipo de dado. Uma violação comum desta
regra, por exemplo, é a criação de um campo para armazenar o CPF e o CNPJ,
alternadamente. Esse cenário deve ser evitado, pois cria complicações para a
evolução da regra de negócio;
N°Nota
Data
CForn
Nome
Empresa
Excalibur
Empresa
Excalibur
Empresa
Excalibur
1
15/6/2003
22
1
15/6/2003
22
1
15/6/2003
22
2
20/7/2003
17
Cia Silva
33344787
2
20/7/2003
17
Cia Silva
33344787
2
20/7/2003
17
Cia Silva
33344787
3
22/8/2003
22
3
22/8/2003
22
Empresa
Excalibur
Empresa
Excalibur
Fone
33355255
33355255
33355255
33355255
33355255
End
Itamaracá,
15
Itamaracá,
15
Itamaracá,
15
Cardoso,
145
Cardoso,
145
Cardoso,
145
Itamaracá,
15
Itamaracá,
15
CItem
CProd
Prod
Chapa de
aço
Qtde
Preço
TItem
1
CA
35
15
525
2
BB
Bobina
20
15
300
3
TC
Tab corrida
50
20
1000
1
TC
Tab corrida
50
20
1000
2
CM
Compensado
30
15
450
3
RM
300
2
600
1
CA
50
15
750
2
BB
20
15
300
Ripas de
mad
Chapa de
aço
Bobina
Tabela 6.6 – Relação após a aplicação da 1FN
101
André Luís Duarte
Banco de Dados
Para trabalhar com a segunda forma normal, precisamos definir uma chave primária. Na
tabela recém criada, a chave escolhida é formada pelos campos número da nota e código
do item (destacados na Tabela 6.6), pois através deles, podemos determinar todos os
outros campos. Por exemplo:

Com o número da nota determinamos os atributos Data, CodForn, Nome,
Telefone e Endereço. NroNota  {Data, CodForn, Nome, Telefone, Endereço};

Com o número da nota e o código do item determinamos os demais atributos:
{NroNota, CodItem}  {CodProd, Produto, Quantidade, Preço, TotalItem};
Note que no exemplo inicial do banco de dados de fornecedores e peça, as relações já
estão em 1FN.
6.4.1.1 Normalização e atributos compostos
O objetivo principal da 1FN é transformar estruturas não padronizadas em tabelas, para
que possam ser armazenadas em um banco de dados relacional. De fato, podemos dizer
que toda tabela já esta na 1 FN senão não seria uma tabela.
O caso mais comum de violação da 1FN são estruturas que possuem atributos
multivalorados, repetidos ou aninhados. Na 1FN, esses atributos devem ser
“planificados”, fazendo com que cada célula armazene somente um valor. Existem duas
formas de enquadrar os atributos multivalorados na 1FN:
1. Expandir o atributo multivalorado em vários registros, repetindo o valor dos
demais campos (solução que mais se aproxima a definição de Codd, o pai da
normalização);
2. Criar uma tabela auxiliar para conter o atributo multivalorado (solução adotada
por alguns desenvolvedores pela praticidade). No entanto, em alguns casos, essa
abordagem pode induzir ao erro;
Por exemplo, observe o aninhamento de três entidades na relação representada na
Tabela 6.7 (vários gostos dentro de dependentes e vários dependentes dentro de pai).
Utilizando a primeira abordagem, chegaremos facilmente à estrutura mostrada na
Tabela 6.8. Agora imagine a dificuldade em criar direto tabelas auxiliares. Teremos que
verificar quem é a chave, se a tabela auxiliar terá outra chave, se precisará de uma tabela
André Luís Duarte
102
[email protected]
auxiliar para auxiliá-la, etc. Em suma, será necessário utilizar os conceitos de chave
primária, dependência funcional, dependência funcional irredutível à esquerda e
decomposição sem perdas, que são regras utilizadas na 2FN. Certamente neste caso, os
projetistas estão sujeitos a inserir erros com mais facilidade no projeto.
Pai
Código
Nome
Telefone
Nome
Samuel
Lucas
1
André
3455456
Jussara
Davi
2
Duarte
4344554
Mariana
Dependente
Gosto
Futebol
Futebol
Luta
Flores
Colorir
Dança
Games
Xadrez
Brincos
Colares
Chocolate
Brinquedos
Idade
10
Idade
8
31
6
9
37
7
Tabela 6.7 – Informações aninhadas
Código
1
1
1
1
1
1
2
2
2
2
2
2
Nome
André
André
André
André
André
André
Duarte
Duarte
Duarte
Duarte
Duarte
Duarte
Telefone
3455456
3455456
3455456
3455456
3455456
3455456
4344554
4344554
4344554
4344554
4344554
4344554
Nome
Samuel
Lucas
Lucas
Jussara
Jussara
Jussara
Davi
Davi
Mariana
Mariana
Mariana
Mariana
Gosto
Futebol
Futebol
Luta
Flores
Colorir
Dança
Games
Xadrez
Brincos
Colares
Chocolate
Brinquedos
Idade
10
8
8
6
6
6
9
9
7
7
7
7
Idade
31
31
31
31
31
31
37
37
37
37
37
37
Tabela 6.8 – Utilizando a primeira abordagem para aplicar a 1FN
6.4.2 2ª Forma Normal (2FN)
Essa forma normal visa à diminuição da redundância e o desagrupamento de
informações. Com a 2FN, uma tabela passa a representar uma quantidade menor de
entidades (o ideal é que cada entidade seja armazenada em apenas uma tabela), observe
que a nossa tabela agrupa as entidades nota fiscal, item de nota, fornecedor e produto.
103
André Luís Duarte
Banco de Dados
A definição da segunda forma normal é que uma tabela está em 2FN se estiver em 1FN
e todo atributo não-chave for determinado por todos os campos da chave primária. Em
outras palavras, é necessário eliminar as dependências funcionais parciais.
A tabela do nosso exemplo, viola a 2FN, pois os campos Data, Codforn, Nome,
Telefone e Endereço não são determinados pela chave primária completa (o campo
CodItem não é necessário para identificar essas informações):
NroNota  {Data, CodForn, Nome, Telefone, endereço}
Como regra geral, a 2FN deve ser aplicada através dos passos:
1. Eleger a chave primária da tabela;
2. Verificar as dependências funcionais parciais;
3. Mover os campos não enquadrados na 2FN para uma nova tabela, fazendo a
decomposição sem perdas;
4. Na tabela criada, repetir os passos anteriores até eliminar a DF parcial;
Aplicando a 2FN em nossa relação, teremos o resultado apresentado na Tabela 6.7 e
Tabela 6.8.
NroNota
1
2
3
Data
CodForn
15/6/2003
22
20/7/2003
17
22/8/2003
22
Nome
Empresa Excalibur
Cia Silva
Empresa Excalibur
Telefone
33355255
33344787
33355255
Endereço
Itamaracá, 15
Cardoso, 145
Itamaracá, 15
Tabela 6.9 – 1ª relação obtida com a aplicação das regras da 2FN
NroNota
1
1
1
2
2
2
3
3
CodItem
1
2
3
1
2
3
1
2
CodProd
CA
BB
TC
TC
CM
RM
CA
BB
Produto
Chapa de aço
Bobina
Tábuas corrida
Tábuas corrida
Compensado
Ripas de madeira
Chapa de aço
Bobina
Qtde
35
20
50
50
30
300
50
20
Preço
15
15
20
20
15
2
15
15
TotalItem
525
300
1000
1000
450
600
750
300
Tabela 6.10 – 2ª relação obtida com a 2FN
Observe que todos os campos nas duas tabelas são agora determinados por suas chaves
primárias completas, garantindo assim a 2FN11. Note também que o resultado da
aplicação desta forma normal são duas tabelas mais simples, que representam as
entidades com mais proximidade.
11
Se a chave primária possui apenas um campo ou é composta por todos os campos, a tabela já está
automaticamente na 2FN.
André Luís Duarte
104
[email protected]
6.4.3 3ª Forma Normal (3FN)
A 3FN dá continuidade ao objetivo da 2FN que é o de reduzir as redundâncias,
desagrupando as tabelas de forma que cada uma represente apenas uma entidade. A
Tabela 6.7 agrupa informações sobre as entidades Nota e Fornecedor e a Tabela 6.8
agrupa informações sobre as entidades Item, Nota e Produto.
As técnicas utilizadas pela 3FN é a identificação e eliminação da transitividade.
Dizemos que uma tabela está na 3FN se também está na 2FN e todo atributo não chave
for determinado de forma não transitiva pela chave primária. Em outras palavras,
dizemos que todo atributo não chave deve ser mutuamente independente e determinado
somente pela chave primária.
Dois ou mais atributos são mutuamente independentes se nenhum dos atributos em
questão for funcionalmente dependente de um dos outros. Observe que tal atributo pode
ser atualizado de forma independente de todos os outros. Um atributo não chave é
qualquer atributo que não participe da chave primária nem das chaves candidatas da
relação em questão.
Vamos analisar a tabela criada na 2FN. Observe que os campos Nome, Telefone e
Endereço podem ser determinados tanto pela chave primária quanto pelo campo
CodForn:
NroNota  {Data, Nome, CodForn, Telefone, Endereço}
CodForn  {Nome, Telefone, Endereço}
Assim, esses campos possuem dependência funcional transitiva com a chave primária:
{NroNota}  CodForn  {Nome, Telefone, Endereço}
Na segunda tabela também temos transitividade:
{NroNota, CodItem}  CodProd  {Produto, Preço}
Outro tipo de violação da 3FN são os campos calculados, que também possuem
transitividade. Na 3FN, todos os campos calculados são removidos da base de dados.
Veja a representação:
105
André Luís Duarte
Banco de Dados
{NroNota, CodItem} Preço, Qtde  {TotalItem}
Para adequar as relações à 3FN, seguimos um roteiro semelhante ao usado na 2FN:
1. Mover os campos com transitividade para uma nova relação;
2. Criar uma chave primária na tabela nova com o(s) campo(s) da tabela original que
determinava(m) diretamente os campos movidos;
3. Na nova tabela, repetir os passos anteriores até eliminar a transitividade;
Observe nas tabelas a seguir o formato final da nossa base normalizada após a aplicação
da 3FN.
CodForn
22
17
Nome
Empresa Excalibur
Cia Silva
Telefone
33355255
33344787
Endereço
Itamaracá, 15
Cardoso, 145
Tabela 6.11 – Relação Fornecedor
CodProd
CA
BB
TC
CM
RM
Produto
Chapa de Aço
Bobina
Tábuas Corridas
Compensado
Ripas de Madeira
Preço
15,00
15,00
20,00
15,00
2,00
Tabela 6.12 – Relação Produto
NroNota
1
2
CodForn
22
17
Tabela 6.13 – Relação Nota
NroNota
1
1
1
2
2
2
CodItem
1
2
3
1
2
3
CodProd
CA
BB
TC
TC
CM
RM
Qtde
35
20
50
50
30
300
Tabela 6.14 – Relação Item Nota
Neste ponto temos a organização ideal para esta base de dados pelos seguintes motivos:
1. A decomposição foi feita sem perdas, através de junções podemos recuperar a
Tabela 6.6;
2. As quatro entidades (Nota, Item nota, Fornecedor e Produto) possuem tabelas
exclusivas, eliminado assim, o agrupamento de informações e redundância;
André Luís Duarte
106
[email protected]
3. As tabelas foram separadas de tal forma que as anomalias de atualização não
ocorrerão;
4. As tabelas são fáceis de evoluir e manter. Por exemplo, se quisermos incluir
dados de um produto que ainda não tenha sido fornecido, podemos inserir sua
descrição na tabela Produtos. Observe que isso não era possível até a aplicação
da 3FN;
5. Do ponto de vista relacional, os dados estão armazenados e distribuídos de
forma eficiente;
No exemplo fornecedores e peças no inicio do capítulo, podemos notar ver que os
atributos PNOME, PCOLOR, PPESO E PCIDADE são independentes entre si, pois é
possível alterar qualquer um deles sem ter que alterar os demais ao mesmo tempo.
Também, cada um destes atributos são totalmente dependentes de PCOD, a chave
primária (as dependências devem ser totais pois PCOD não é composto).
Observações sobre as anomalias
Vamos supor, no exemplo de fornecedores e peças, que a informação relativa aos
fornecedores em vez de estar fragmentada em duas relações, fornecedores e peças, seja
reunida em uma relação:
TESTE (FCOD, STATUS, CIDADE, PCOD, QTD)
Podemos observar que a relação resultante seria uma versão ampliada para a relação
“FP”. Os atributos tem seus significados usuais, a não ser que, neste exemplo,
introduzamos uma limitação adicional, a
saber que STATUS é funcionalmente
dependente de CIDADE. O significado desta limitação é que um status de fornecedor é
determinado pela localização deste fornecedor (por exemplo, todos os fornecedores de
Londres devem ter status 20). Para simplificar, vamos ignorar o atributo NOME. A
chave primária da relação TESTE é formada pela combinação dos atributos FCOD e
PCOD. O diagrama FD é mostrado na Figura 6.4.
STATUS
FCOD
QTD
PCOD
CIDADE
Figura 6.4 – Diagrama FD para a relação TESTE
107
André Luís Duarte
Banco de Dados
Observe que o diagrama FD da relação TESTE é mais complexo do que o diagrama FD
para a relação na Figura 6.2. Informalmente, um diagrama 3FN tem setas apenas a partir
da chave primária (Figura 6.2). Em relações que não sejam 3FN, existirão setas
adicionais, e são estas setas adicionais que causam as anomalias citadas anteriormente.
De fato, a relação TESTE viola as regras da 3FN, ou seja, os atributos não chave não
são mutuamente independentes, porque STATUS depende funcionalmente de CIDADE
(uma seta adicional) e ambos não dependem totalmente da chave primária, ambas
dependem apenas de FCOD (mais duas setas adicionais).
5.4.3.4 Forma Normal de Boyce/Codd (BCNF)
< Trabalho em grupo >
6.5 Normalização e integridade
As formas normais visam à consistência da base de dados sob o aspecto da eliminação
de redundâncias, mas não garantem que as informações certas serão inseridas no lugar
certo (um ponto crucial para que o banco se mantenha íntegro ao longo do tempo). Para
que as tabelas obtidas, com a aplicação das formas normais, façam sentido, é preciso
utilizar as regras de integridades abordadas nos capítulos anteriores.
Analisando nosso exemplo, podemos listar:

Não podemos inserir um produto na tabela Item nota que não tenha sido
cadastrado na tabela Produto;

Não podemos apagar um registro na tabela Nota sem apagar o conjunto de
registros correspondentes da tabela Item nota;

Não podemos alterar o código de um fornecedor sem alterar todos os registros
da tabela Nota que o referenciam;
6.6 Desnormalização
A normalização não se preocupa com a performance de obtenção dos dados e sim com a
melhor forma de organizá-los. Como a normalização geralmente aumenta o número de
tabelas, há uma tendência de queda de performance nas consultas (logicamente pela
necessidade de mais joins).
André Luís Duarte
108
[email protected]
A desnormalização é o processo inverso, onde o administrador de banco de dados abre
mão de algumas regras das formas normais com o objetivo de otimizar consultas. É
importante notar que isso não é o mesmo que uma base de dados não normalizada: a
desnormalização acontece sempre depois da aplicação das formas normais.
A desnormalização inadvertida é na maioria dos casos desaconselhável. Esta só deve ser
aplicada quando o administrador tiver certeza (estudos, testes, provas físicas, etc) de que
esta trará benefícios reais no sistema em questão. Alguns profissionais fazem suposições
sobre a perda de performance e já projetam o sistema desnormalizando, sem fazer
nenhum benchmark. Os custos com o ganho de performance podem não aparecer ou não
pagam os custos das anomalias de atualização geradas e que necessitaram serem
tratadas. Vale notar que nem sempre joins degradam performance em SGBD,
normalmente este são dotados de recursos que visam otimizar as consultas.
Como exemplo, imagine que nossa aplicação acesse massivamente um relatório
contendo o campo calculado TotalItem (eliminado na 3FN). Para evitar o cálculo do
total a todo o momento, podemos abrir mão da 3FN e criar o campo fisicamente na
tabela de itens de nota, arcando com a responsabilidade de mantê-lo devidamente
atualizado12.
Algumas dúvidas comuns podem surgir a respeito da normalização. Podemos citar duas
delas:
a) O que dizer da normalização para fins de manutenção de dados históricos? No
exemplo trabalhado, se o preço do produto for alterado, todos os dados de
fornecimento são alterados. Repetir o campo preço na tabela Item nota, para
manter o histórico do preço é uma boa prática?
R. Este caso não se trata de desnormalização. O que temos são dois atributos com
sutil diferença: Preço atual e Preço histórico.
b) Devemos sempre normalizar até o fim? Por exemplo, geralmente os campos
Cidade, Bairro, Uf e CEP apresentam repetição, é correto criarmos tabelas
auxiliares para cada um?
12
O Delphi possui componentes elaborados para suprir várias necessidades e solucionar muitos
problemas de desenvolvedores de aplicações que acessam banco de dados pequenos ou corporativos.
109
André Luís Duarte
Banco de Dados
R. Depende, se a aplicação realizar pesquisas nestes campos, sim – imagine se
quiséssemos saber a quantidade de pessoas em um bairro e este estiver cadastrado
de forma diferente em mais de um registro (por exemplo, Sto. Antônio em um
registro e Santo Antônio em outro). Hoje em dia vemos vários sistema como o dos
correios, que a partir do CEP trazem todas as demais informações do endereço,
deixando claro que várias tabelas foram utilizadas.
Podemos notar que a normalização é uma etapa (ferramenta) importante no processo de
desenvolvimento de um sistema de banco de dados. A normalização individualmente
não soluciona todos os problemas encontrados no desenvolvimento, já que nem todas as
anomalias de atualização e as redundâncias podem ser eliminadas através de formas
normais. A normalização nunca deve substituir uma boa análise do negócio da aplicação
e a utilização de todas as técnicas de desenvolvimento de sistemas.
André Luís Duarte
110
[email protected]
Bibliografia

ELMASRI, Ramez; NAVATHE, Shamkant B. Sistemas de banco de dados. 4a ed.
São Paulo: Pearson, 2006. 724 p. ISBN 85-88639-17-3.

SUEHRING, Steve. MySQL a bíblia. Rio de Janeiro: Elsevier, 2002. 674 p. ISBN
85-352-1084-9.63

SILBERSCHATZ, Abraham; KORTH, Henry F.; SUDARSHAN, S.. Sistema de
banco de dados. 3. ed. São Paulo: Pearson, 2008. 778 p. ISBN 978-85-346-1073-5.

HEUSER, C. Projeto de Banco de Dados. Porto Alegre: Sagra Luzzato, 1998, Série
de Livros Didáticos, número 4.

KORTH, H. F.; Sudarshan, S; Silberschatz, A. Sistema de Banco de Dados. 5a ed.
Editora Campus, 2006.

KORTH, H.F.; Silberschatz, A. Sistema de Banco de Dados. 3a ed. São Paulo:
Makron Books, 1999.

MACHADO, F.N.R.; Abreu, M. Projeto de Banco de Dados: uma Visão Prática.
São Paulo: editora Érica, 1995.

SILVA, I, J, Banco de Dados: do Modelo Conceitual à Implementação Física, Ed:
Alta Books, 2005.
111
André Luís Duarte