Modelagem de Banco de Dados Relacional

Prévia do material em texto

UNIDADE II
Modelos 
de Bancos 
de Dados 
Relacionais
Everton Gomede
Modelagem e Projeto de Banco de
Dados Relacional
Imagine como transformar a especi�cação de um problema do mundo real em um
projeto de banco de dados. Isso pode ser feito utilizando o modelo Entidade-
Relacionamento (E-R) que vamos estudar.
Ao �nal desta aula, você será capaz de:
ter uma visão geral do processo de design;
entender o modelo de relacionamento entre entidades;
aprender sobre restrições em bancos de dados.
Visão Geral do Processo de Design
Aula 01
Introdução
2
A tarefa de criar um aplicativo de banco de dados é complexa, envolvendo design do
esquema do banco de dados, design dos programas que acessam e atualizam os dados
e o design de um esquema de segurança para controlar o acesso aos dados. As
necessidades dos usuários desempenham um papel central no processo de design. O
design de um ambiente de aplicativo de banco de dados completo que atenda às
necessidades de uma organização que está sendo modelada requer atenção a um
amplo conjunto de questões. Esses aspectos do uso esperado do banco de dados
in�uenciam as opções de design nos níveis físico, lógico e de visualização (ELMASRI;
NAVATHE, 2016).
Fases de Design
Para pequenas aplicações, pode ser viável, para um designer de banco de dados que
entende os requisitos desta aplicação, decidir diretamente sobre as relações a serem
criadas, seus atributos e restrições nas relações. No entanto, esse estilo de design
tona o processo difícil para aplicativos do mundo real, já que estes geralmente são
altamente complexos. Muitas vezes, ninguém entende as necessidades completas de
dados de um aplicativo. O designer de banco de dados deve interagir com os usuários
do aplicativo para entender tais necessidades da aplicação, representá-las de uma
forma que possa ser entendida pelos usuários e, em seguida, traduzi-la para os
requisitos do modelo de dados. Um modelo de dados de alto nível atende ao designer
de banco de dados com uma estrutura conceitual na qual se pode especi�car, de
maneira sistemática, os dados e requisitos dos usuários do banco de dados, além de
uma estrutura de banco de dados que atenda aos requisitos.
A fase inicial do design do banco de dados tem como objetivo caracterizar as
necessidades dos usuários do banco de dados em potencial. O designer de banco
de dados precisa interagir com especialistas de domínio e usuários para realizar
esta tarefa. O resultado desta fase é uma especi�cação dos requisitos do
usuário. Geralmente, esta especi�cação pode ser feita textualmente ou via
técnicas mais apropriadas (SILBERSCHATZ et al., 2019).
Em seguida, o designer escolhe um modelo de dados e, aplicando os conceitos do
modelo de dados escolhido, traduz esses requisitos em um esquema conceitual
para o banco de dados. O esquema desenvolvido nesta fase de projeto
conceitual fornece uma visão geral detalhada do problema que está sendo
resolvido. O modelo entidade-relacionamento, que estudaremos no restante
desta unidade é tipicamente usado para representar o design conceitual.
Declarado em termos do modelo entidade-relacionamento, o esquema
especi�ca as entidades que são representadas no banco de dados, o atributo
3
das entidades, as relações entre as entidades e as restrições nas entidades e
relacionamentos. Normalmente, a fase de projeto conceitual resulta na criação
de um diagrama entidade-relacionamento que fornece um grá�co de
representação do esquema. O designer revisa o esquema para con�rmar que
todos os requisitos de dados estão, de fato, satisfeitos e não estão em con�ito
um com os outros. Ele também pode examinar o design para remover quaisquer
recursos redundantes. Seu foco, neste momento, está em descrever os dados e
seus relacionamentos, em vez de especi�car detalhes de armazenamento
(ELMASRI; NAVATHE, 2016).
Um esquema conceitual totalmente desenvolvido também indica as exigências
funcionais da instituição. Em uma especi�cação de requisitos funcionais, os
usuários descrevem os tipos de operações (ou transações) que serão executadas
em os dados. Exemplos de operações incluem modi�cação ou atualização de
dados, pesquisa para e recuperar dados especí�cos e excluir dados. Nesta fase
de design conceitual, o projetista pode revisar o esquema para garantir que ele
atenda aos requisitos.
O processo de passar de um modelo de dados abstrato para a implementação
do banco de dados prossegue em duas fases �nais de projeto.
Na fase de design lógico, o projetista mapeia o modelo conceitual de alto nível
para o modelo de dados de implementação do sistema de banco de dados que
será usado. O modelo de dados de implementação é tipicamente o modelo
relacional modelo de dados e esta etapa, normalmente, consiste em mapear o
esquema de�nido usando o modelo de entidade-relacionamento em um
esquema de relação.
Finalmente, o designer usa o esquema de banco de dados especí�co do sistema
resultante na fase subsequente chamada de projeto físico, na qual as
características físicas do banco de dados são especi�cadas. Esses recursos
incluem a forma de arquivo ou organização e escolha de estruturas de índices
(SILBERSCHATZ et al., 2019).
O esquema físico de um banco de dados pode ser alterado com relativa facilidade
após a aplicação construída. No entanto, as alterações no esquema lógico são
geralmente mais difíceis de realizar, pois elas podem afetar várias consultas e
atualizações por meio do código do aplicativo. Portanto, é importante realizar o
4
banco de dados na fase de design com cuidado, antes de criar o restante do aplicativo
de banco de dados (ELMASRI; NAVATHE, 2016).
Alternativas de design
Uma parte importante do processo de design do banco de dados é decidir como
representar os vários tipos de “coisas” como pessoas, lugares, produtos e transações.
Usamos o termo entidade para nos referir a qualquer item distintamente
identi�cável. Em um banco de dados universitário, exemplos de entidades incluiriam
professores, alunos, departamentos, cursos e ofertas de cursos. As várias entidades
estão relacionadas uns com os outros em uma variedade de maneiras, todas as quais
precisam ser capturadas no banco de dados desenhar. Por exemplo, um aluno
frequenta um curso, enquanto um professor ensina um curso; ensinar e frequentar
ATENÇÃO
O modelo físico de um banco de dados se refere à tradução do modelo lógico em
uma linguagem de de�nição de dados. Nesse modelo, o tipo de dados, tamanhos,
restrições e demais elementos são considerados.
Fonte: 14.13.1 On-line... (s.d.).
5
são exemplos de relacionamentos entre entidades. Ao projetar um esquema de
banco de dados, devemos evitar duas grandes armadilhas:
1. redundância: um design ruim pode repetir informações. Por exemplo, se armazenar o
identi�cador do curso e o título de um curso com cada oferta de curso, o título seria
armazenado de forma redundante (ou seja, várias vezes, desnecessariamente) com cada
oferta de curso. Bastaria armazenar apenas o identi�cador do curso com cada oferta de
curso e associar o título ao identi�cador do curso apenas uma vez, em uma entidade
curso. Redundância também pode ocorrer em um esquema relacional. Na Universidade,
o exemplo que usamos até agora, temos uma relação com as informações da seção e
uma relação separada com a informação do curso. Suponha que em vez disso temos uma
relação única onde repetimos todas as informações do curso (curso id, título, nome do
departamento, créditos) uma vez para cada seção (oferta) do curso. Claramente, as
informações sobre os cursos seriam armazenadas de forma redundante. O maior
problema com essa representação redundante de informações é que as cópias de uma
informação podem se tornar inconsistentes se as informações forem atualizadas sem
tomar precauções para atualizar todas as cópias da informação. Por exemplo, ofertas
diferentes de um curso podem ter mesmo identi�cador de curso, mas podem ter títulos
diferentes. Dessa forma, não �caria claro o curso a ser selecionado. Idealmente, ainformação deve aparecer exatamente em um lugar;
2. incompletude: um mau design pode fazer certos aspectos da empresa difícil ou
impossível de modelar. Por exemplo, suponha que, como no caso anterior, tínhamos
apenas entidades correspondentes à oferta de cursos, sem uma entidade
correspondente aos cursos. Equivalente, em termos de relações, suponha que temos
uma relação única em que repetimos todas as informações do curso mação uma vez
para cada seção que o curso é oferecido. Seria, então, impossível representar
informações sobre um novo curso, a menos que é oferecido. Podemos tentar nos
contentar com o projeto problemático e os valores nulos para as informações da seção.
Tal solução não é apenas desinteressante, mas pode ser impedida por restrições de
chave primária.
Evitar projetos ruins não é su�ciente. Como um exemplo simples, considere um
cliente que compra um produto. A venda deste produto é uma relação entre o cliente
e o produto? Alternativamente, a venda em si é uma entidade que está relacionada
tanto ao cliente quanto para o produto? Esta escolha, embora simples, pode fazer
uma importante diferença em que aspectos da empresa podem ser bem modelados.
Considerando que precisa fazer escolhas como essa para um grande número de
entidades e relacionamentos em uma empresa do mundo real, não é difícil ver que o
design do banco de dados pode ser um problema desa�ador (ELMASRI; NAVATHE,
2016).
6
INFOGRÁFICO INTERATIVO
Para consultar o Infográ�co Interativo, 
acesse a versão digital deste material
SAIBA MAIS
Projetos de bancos de dados é uma área de grande relevância para a construção
de aplicações de software. Geralmente, são a base ou a estrutura de dados dessas
aplicações.
Fonte: Takai, Italiano e Ferreira (2005).
7
O Modelo de Relacionamento Entre
Entidades
O modelo de dados entidade-relacionamento (E-R) foi desenvolvido para facilitar o
projeto de um banco de dados, permitindo a especi�cação de um esquema
corporativo que representa a estrutura lógica de um banco de dados. O modelo E-R é
muito útil no mapeamento dos signi�cados e interações de empresas do mundo real
em um esquema conceitual. Devido a essa utilidade, muitas ferramentas de design de
banco de dados utilizam conceitos do modelo E-R. O modelo de dados E-R emprega
três conceitos básicos: conjuntos de entidades, conjuntos de relacionamentos e
atributos. O modelo E-R também possui uma representação diagramática associada,
o diagrama E-R, que estudaremos mais adiante neste capítulo. Em nossos estudos,
vamos utilizar uma ferramenta conhecida como MySQL Workbench.
SAIBA MAIS
O MySQL Workbench é uma excelente ferramenta para administração e projetos
de bancos de dados baseados no MySQL.
Para saber mais, acesse: http://bit.ly/2JuQtyM.
8
http://bit.ly/2JuQtyM
Conjuntos de Entidades
Uma entidade é uma “coisa” ou “objeto” no mundo real que se distingue de todos os
outros objetos. Por exemplo, cada pessoa em uma universidade é uma entidade. Uma
entidade tem um conjunto de propriedades que podem ser exclusivos identi�car a
mesma. Por exemplo, uma pessoa pode ter uma propriedade CPF cujo valor identi�ca
exclusivamente essa pessoa. Assim, o valor 110.120.130-90 para o CPF da pessoa
identi�ca de forma única uma pessoa na universidade. Da mesma forma, cursos
podem ser pensados como entidades, e ID do curso identi�ca exclusivamente uma
entidade de curso em uma universidade. Uma entidade pode ser concreta, como uma
pessoa ou um livro, ou pode ser abstrata, como um curso, uma oferta de curso ou uma
reserva de voo (ELMASRI; NAVATHE, 2016).
No processo de modelagem usamos o termo entidade sem referir-se a um conjunto
particular de entidades individuais. Uma entidade é representada por um conjunto
de atributos. Atributos são descritivos propriedades possuídas por cada membro de
um conjunto de entidades. A designação de um atributo para um conjunto de
entidades expressa que o banco de dados armazena informações semelhantes sobre
cada entidade no conjunto de entidades; no entanto, cada entidade pode ter seu
próprio valor para cada atributo. Possíveis atributos do conjunto de entidades
professor são ID, nome, nome do departamento e salário. Na vida real, haveria outros
atributos, como rua, número, número do apartamento, estado, código postal e país.
Possíveis atributos do conjunto de entidades do curso são id do curso, título, nome do
departamento e créditos. Cada entidade tem um valor para cada um dos seus
atributos. Por exemplo, uma determinada entidade professor pode ter o valor 100
para ID, o valor “JOAO” para NOME, o valor “COMPUTACAO” para o nome do
DEPARTAMENTO e o valor "5000" para o SALARIO.
O atributo ID é usado para identi�car professores com exclusividade, pois pode
existir mais de um com o mesmo nome. No Brasil, muitas empresas acham
conveniente usar o número do CPF social de uma pessoa como atributo cujo valor
identi�ca exclusivamente a mesma. Em geral, a empresa teria que criar e atribuir um
identi�cador único para cada professor. Um banco de dados inclui uma coleção de
conjuntos de entidades, cada um contendo qualquer número de entidades do mesmo
tipo. A Figura 1 mostra parte de uma universidade banco de dados que consiste em
dois conjuntos de entidades: professor e aluno. Para manter a �gura simples, apenas
alguns dos atributos dos dois conjuntos de entidades são mostrados. Um banco de
dados para uma universidade pode incluir vários outros conjuntos de entidades. Por
exemplo, além de acompanhar os professores e os alunos, a universidade também
possui informações sobre os cursos, que são representados pelo curso conjunto da
9
entidade com identi�cação do curso de atributos, título, nome do departamento e
créditos. Em um ambiente real, uma universidade banco de dados pode manter
dezenas de conjuntos de entidades.
Relacionamento entre entidades
Um relacionamento é uma associação entre várias entidades. Considere os dois
conjuntos de entidades professor e aluno da Figura 1. Nós de�nimos o
relacionamento “orienta” para denotar a associação entre professores e estudantes.
A Figura 2 mostra essa associação. Como outro exemplo, considere os dois conjuntos
de entidades aluno e seção. Podemos de�nir que o conjunto de relacionamento leva a
denotar a associação entre um aluno e as seções do curso em que o aluno está
matriculado. Um relacionamento posição em um esquema E-R representa uma
associação entre as entidades nomeadas em uma empresa do mundo real que está
sendo modelada. Como os conjuntos de entidades que participam de um
relacionamento são geralmente distintos, os papéis estão implícitos e geralmente
não precisam ser especi�cados. No entanto, eles são úteis quando o signi�cado de
um relacionamento precisa de esclarecimento. Tal é o caso quando a entidade de�ne
que um conjunto de relacionamento não é distinto; ou seja, o mesmo conjunto de
entidades participa de um relacionamento de�nido mais de uma vez, em diferentes
funções. Neste tipo de conjunto de relacionamento, às vezes chamado de conjunto de
Figura 1 - O conjunto de professores de uma universidade e o conjunto de alunos
Fonte: Adaptada de Silberschatz et al. (2019).
10
relacionamento recursivo, nomes de funções explícitos são necessários para
especi�car como uma entidade participa de uma instância de um relacionamento
(ELMASRI; NAVATHE, 2016).
Um relacionamento também pode ter atributos chamados atributos descritivos.
Considere um relacionamento com conjuntos de entidades professor e aluno.
Poderíamos associar a data do atributo a essa relação para especi�car a data em que
o professor tornou-se o orientador de um aluno. A Figura 3 mostra o orientador do
conjunto de relacionamentos com uma data de atributo descritiva.
Figura 2 - O relacionamento entre o conjunto de professores de uma universidade e o
conjunto de alunos 
Fonte: Adaptada de Silberschatz et al. (2019).
11
Note que João orienta dois alunos (Pedro e Paulo) com duas datas diferentes de
início. Uma instância de relacionamento em um determinado conjunto de
relacionamento deveser identi�cada de suas entidades participantes, sem utilizar os
atributos descritivos. Para entender esse ponto, suponha que queremos modelar
todas as datas em que um professor se tornou um orientador de um determinado
aluno. A data do atributo de valor único pode armazenar apenas uma data. Não
podemos representar várias datas por múltiplas relações entre o mesmo professor e
um aluno, uma vez que o relacionamento não seria exclusivamente identi�cável
usando apenas as entidades participantes. A maneira correta de lidar com esse caso é
criar uma data de atributo de vários valores, que pode armazenar todas as datas. É
possível ter mais de um conjunto de relacionamentos envolvendo o mesmo conjunto
de entidades. Além disso, suponha que cada aluno deve ter outro professor que atua
como orientador de departamento (Graduação ou Pós-Graduação). Então, os
conjuntos de entidades de professor e aluno podem participar de outro conjunto de
relacionamento, conselheiro de departamento.
Figura 3 - O relacionamento entre o conjunto de professores de uma universidade e o
conjunto de alunos com a data de orientação 
Fonte: Adaptada de Silberschatz et al. (2019).
12
Atributos
Para cada atributo, há um conjunto de valores permitidos, chamado domínio ou valor
conjunto, desse atributo. O domínio do ID do curso pode ser os valores entre 0 e 100.
Da mesma forma, o domínio do atributo semestre pode ser {outono, inverno,
primavera, verão}. Formalmente, um atributo de um conjunto de entidades é uma
função que mapeia a entidade de�nida em um domínio. Como um conjunto de
entidades pode ter vários atributos, cada entidade pode ser descrita por um conjunto
de pares (atributo, valor de dados), um par para cada atributo do conjunto de
entidades. Por exemplo, uma entidade professor pode ser descrita pelo conjunto {(ID,
1), (nome, João), (nome do departamento, Computação), (salário, 5000)}, signi�cando
que a entidade descreve uma pessoa chamada João cujo ID de professor é 1, que é
um membro do departamento de Computação com salário de R$ 5.000. Nós
podemos ver uma integração do esquema abstrato com o real sendo modelado. Os
valores de atributos que descrevem uma entidade constituem uma porção
ATENÇÃO
No modelo E-R, o R refere-se ao relacionamento. Isso é muito importante para
de�nir como um modelo de banco de dados deve trabalhar para alcançar os
objetivos propostos.
Fonte: Faria (2018).
13
signi�cativa dos dados armazenados no banco de dados. Um atributo, como usado no
modelo E-R, pode ser caracterizado pelos seguintes tipos de atributos (ELMASRI;
NAVATHE, 2016):
atributos simples e compostos. Em nossos exemplos até agora, os atributos
foram simples; isto é, eles não foram divididos em subpartes. Atributos
compostos, por outro lado, podem ser divididos em subpartes (isto é, outros
atributos). Por exemplo, um nome de atributo pode ser estruturado como um
atributo composto que consiste em primeiro nome e sobrenome. Atributos
compostos nos ajudam a agrupar atributos relacionados, tornando a
modelagem mais limpa. Note também que um atributo composto pode aparecer
como uma hierarquia. Na entidade Endereço, o atributo composto Rua pode ser
dividido no número da rua, nome da rua e número do apartamento (VANIER et
al., 2019). A Figura 4 mostra esses exemplos de atributos compostos para o
conjunto de entidades professor;
atributos de valor único e valor múltiplo. Os atributos em nossos exemplos têm
um valor único para uma entidade particular. Por exemplo, o atributo de ID do
aluno para uma entidade estudantil especí�ca, refere-se a apenas um ID de
estudante. Tais atributos são ditos de valor único. Pode haver casos em que um
atributo tem um conjunto de valores para uma entidade especí�ca. Suponha
que adicionemos à entidade professor um atributo de número de telefone. Um
professor pode ter zero, um ou vários telefones, e professores diferentes
podem ter diferentes números de telefones. Esse tipo de atributo é considerado
de valor múltiplo. Como outro exemplo, nós poderíamos adicionar à entidade
professor um atributo nome do dependente listando todos os dependentes.
Este atributo seria multivalorado, uma vez que qualquer professor pode ter
zero, um ou mais dependentes. Para denotar que um atributo é de valor
múltiplo, o colocamos entre chaves, por exemplo {número de telefone} ou {nome
dependente}. Quando apropriado, os limites superior e inferior podem ser
colocados no número de valores em um atributo multivalorado. Por exemplo,
uma universidade pode limitar os números de telefones gravados para um único
professor para dois. Colocação limites, neste caso, expressa que o atributo de
número de telefone do professor conjunto de entidades pode ter entre zero e
dois valores (VANIER et al., 2019);
atributo derivado. O valor para este tipo de atributo pode ser derivado dos
14
valores de outros atributos ou entidades relacionadas. Por exemplo, digamos
que o conjunto de entidades professor tem um atributo que os alunos
recomendaram, que representa como muitos alunos que um professor orienta.
Podemos derivar o valor para este atributo contando o número de entidades
estudantis associadas a esse professor. Como outro exemplo, suponha que o
conjunto de entidades professor tenha um atributo idade que indica a idade do
professor. Se o conjunto de entidades professor também tiver atributo data de
nascimento, podemos calcular a idade a partir da data de nascimento. Assim, a
idade é um atributo derivado. Neste caso, a data de nascimento pode ser
referida como um atributo base ou um atributo armazenado. O valor de um
atributo derivado é não armazenado, mas é calculado quando necessário.
Um atributo recebe um valor nulo quando uma entidade não possui um valor para
ele. O valor nulo pode indicar "não aplicável" - isto é, que o valor não existe para a
entidade. Por exemplo, um professor pode não ter nome do meio. Nulo também pode
designar que um valor de atributo é desconhecido (HOFFER et al., 2019). Um valor
desconhecido pode ser ausente (o valor existe, mas não temos essa informação) ou
não é conhecido (não sabemos se o valor realmente existe ou não). Por exemplo, se o
valor do nome de um professor for nulo, assumimos que o valor está faltando, já que
todo professor deve ter um nome. Um valor nulo para o atributo de número de
apartamento pode signi�car que o endereço não inclui um número de apartamento
(não aplicável), que existe um número de apartamento, mas nós não sei o que é (falta),
ou que não sabemos se é ou não o número do apartamento faz parte do endereço do
professor (desconhecido).
15
Restrições
Um esquema E-R pode de�nir certas restrições às quais o conteúdo de um banco de
dados deve estar em conformidade. Nesta seção, examinamos as restrições de
cardinalidades e restrições de participação.
Figura 4 - Exemplo de atributos compostos 
Fonte: Adaptada de Silberschatz et al. (2019).
16
Restrições de Cardinalidades
Mapeamento das cardinalidades expressam o número de entidades a qual outra
entidade pode ser associada mediante um conjunto de relacionamento. As
cardinalidades de mapeamento são mais úteis na descrição de conjuntos de
relacionamentos binários, embora possam contribuir para a descrição dos conjuntos
de relacionamento que envolvem mais de dois conjuntos de entidades (ELMASRI;
NAVATHE, 2016). Nesta seção, vamos nos concentrar apenas no conjunto de
relacionamento binário. Para um relacionamento binário R entre os conjuntos de
entidades A e B, o mapeamento cardinalidade deve ser um dos seguintes:
um a um. Uma entidade em A está associada com no máximo uma entidade em
B, e uma entidade em B está associada com no máximo uma entidade em A. (Ver
Figura 5a);
ATENÇÃO
Restrições devem ser consideradas em projetos de banco de dados para manter a
consistência dos dados ao longo do tempo.
Fonte: Salamon (s.d).
17
um para muitos. Uma entidade em A está associada a qualquer número (zero ou
mais) de entidades em B. Uma entidade em B, no entanto, pode ser associada
com no máximouma entidade em A. (Veja a Figura 5b);
muitos para um. Uma entidade em A está associada com no máximo uma
entidade em B. A entidade em B, no entanto, pode ser associada a qualquer
número (zero ou mais) de entidades em A. (Veja a Figura 5c);
muitos para muitos. Uma entidade em A está associada a qualquer número
(zero ou mais) de entidades em B, e uma entidade em B está associada a
qualquer número (zero ou mais) de entidades em A. (Veja a Figura 5d).
A cardinalidade de mapeamento apropriada para um conjunto de relacionamento
especí�co, obviamente depende da situação do mundo real em que o conjunto de
relacionamentos é modelado. Como exemplo, considere o conjunto de
relacionamentos do orientador. Se, em uma universidade, um estudante pode ser
aconselhado por apenas um professor, e um professor pode orientar vários alunos,
então o relacionamento de�nido de professor para aluno é um para muitos (HOFFER
et al., 2019). Se um aluno pode ser orientador por vários professores, o conjunto de
relacionamento é muitos para muitos.
Figura 5 - Exemplo de atributos compostos
Fonte: Adaptada de Silberschatz et al. (2019).
18
Restrições de participação
A participação de um conjunto de entidades E em um conjunto de relacionamento R é
considerada total se a entidade em E participa em pelo menos um relacionamento em
R. Se apenas algumas entidades em E participar de relacionamentos em R, a
participação do conjunto de entidades E no relacionamento R é dito parcial. Na
Figura 5a, a participação de B no conjunto de relacionamentos é total, enquanto a
participação de A no conjunto de relacionamentos é parcial. Na Figura 5b, a
participação de A e B no conjunto de relacionamentos é total. Por exemplo,
esperamos que cada entidade estudantil esteja relacionada a pelo menos um
professor por meio do relacionamento de orientação. Portanto, a participação do
estudante na orientação do conjunto de relacionamento é total. Em contraste, um
professor não precisa orientar estudantes. Portanto, é possível que apenas algumas
das entidades professor estejam relacionadas para a entidade estudante de�nida
mediante o relacionamento de orientação, e a participação do professor no conjunto
de relacionamento de orientação é, portanto, parcial.
Chaves
Devemos ter uma maneira de especi�car como as entidades dentro de um
determinado conjunto de entidades são distintas (HOFFER et al., 2019).
Conceitualmente, entidades individuais são distintas; de uma perspectiva de banco
de dados, no entanto, as diferenças entre eles devem ser expressas em termos de
atributos. Portanto, os valores dos atributos de uma entidade devem ser tais que eles
podem identi�car exclusivamente a entidade. A noção de uma chave para um
esquema E-R aplica-se diretamente para conjuntos de entidades. Ou seja, uma chave
para uma entidade é um conjunto de atributos que bastam para distinguir as
entidades umas das outras (ELMASRI; NAVATHE, 2016). Os conceitos de super
chave, chave candidata e chave primária são aplicáveis aos conjuntos de entidades.
As chaves também ajudam a identi�car relações de maneira única e, portanto,
distinguem relações entre si. A chave primária de um conjunto de entidades nos
permite distinguir entre as várias entidades do conjunto. Precisamos de um
mecanismo semelhante para distinguir entre os vários relacionamentos de um
conjunto de relacionamento.
19
As chaves em banco de dados desempenham um papel importante nos mais variados
cenários. Vamos estudar algumas delas com algum exemplo de como utilizá-las.
Chave. Uma ou mais colunas em uma tabela de banco de dados que é usada para
classi�car e/ou identi�car linhas em uma tabela. Por exemplo, se você estivesse
classi�cando as pessoas pelo salário de campo, então o campo de salário é a
chave.
Chave primária. Uma chave primária é um ou mais campos que identi�cam
exclusivamente uma linha em uma tabela. A chave primária não pode ser nula
(em branco) e a chave primária é indexada.
Chave estrangeira. Uma chave estrangeira é uma relação entre colunas em duas
tabelas de banco de dados (uma das quais é indexada) projetada para garantir a
consistência de dados. Por exemplo, cada registro em uma tabela CLIENTE
ATENÇÃO
O conceito de chave primária é muito importante para a modelagem de bancos
de dados. Sempre que possível, utilize uma chave natural no lugar de uma chave
arti�cial.
Fonte: Macário e Baldo (2005).
20
contém o ID do gerente de contas desse cliente. Na tabela GERENTE, o ID
normalmente seria a chave primária (indexada, exclusiva, não nula). O campo
GERENTE_FK na tabela CLIENTE é a chave estrangeira; somente valores para
GERENTE.ID serão permitidos no campo CLIENTE.GERENTE_FK.
Chave Primária Composta. Uma chave primária composta por uma ou mais
colunas, por exemplo, uma tabela chamada PESSOA contém os campos
PRIMEIRO_NOME e ULTIMO_NOME para o primeiro e último nomes,
respectivamente. A chave primária pode ser formada usando os campos
(embora não seja muito recomendável).
Chave natural. Uma chave primária composta que é composta por atributos
(campos) que já existem no mundo real. Por exemplo, o número do CPF no
Brasil.
Chave substituta. Uma chave primária que é gerada internamente  (geralmente
valor inteiro autoincremental) que não existe no mundo real, isto é, ID = 1 para
o Cliente A e ID = 2 para o Cliente B serve para identi�car exclusivamente o
registro, mas não tem o próprio cliente e é um atributo que eles nunca precisam
saber.
Chave candidata. Uma chave candidata é uma coluna ou um grupo de colunas
que podem identi�car exclusivamente uma linha na tabela sem se referir a
nenhuma outra fonte. Em uma tabela com várias chaves candidatas, uma é
selecionada para ser a chave primária, por exemplo, você pode ter uma tabela
EMPREGADO com uma chave candidata usando NOME e outra usando
DATA_NASCIMENTO.
Chave composta. Uma chave composta consiste em dois ou mais campos que
descrevem exclusivamente uma linha em uma tabela. A diferença entre
composto e candidato é que todos os campos da chave composta são chaves
estrangeiras; na chave candidata, um ou mais dos campos podem ser chaves
estrangeiras (mas não é obrigatório). Você pode ter uma tabela EMPREGADO
com uma chave candidata usando NUMERO_PASSAPORTE e outra usando CPF.
Na exclusão, ambos podem identi�car uma linha de maneira exclusiva. Qualquer
um pode ser usado como uma chave primária (mas não ambos, já que uma
tabela pode ter apenas uma chave primária).
21
QUESTÃO OBJETIVA
Projetos de bancos de dados podem ser desa�adores. Ao
projetar um esquema de banco de dados, devemos evitar
duas grandes armadilhas. Neste caso, qual seria uma delas?
Redundância.
Retrabalho.
Refactor.
Reindexação.
Redimensionamento.
22
QUESTÃO OBJETIVA
Atributos que podem ser divididos em subpartes (isto é,
outros atributos), por exemplo, um nome que consiste em
primeiro nome e sobrenome pode ser chamado de:
atributo simples.
atributo incorreto.
atributo valorado.
atributo composto.
atributo externo.
23
Fechamento
O maior benefício de um bom modelo de banco de dados para um projeto é manter a
consistência dos dados ao longo do tempo. Como vimos, isso pode ser feito via o
modelo E-R.
Nesta aula, você teve a oportunidade de:
ter uma visão geral do processo de design;
entender o modelo de relacionamento entre entidades;
aprender sobre restrições em bancos de dados.
24
Linguagens de De�nição de Dados
Quando temos a necessidade de projetar um banco de dados, alguns fundamentos
precisam ser conhecidos. Primeiramente, devemos entender qual a técnica de
modelagem a ser utilizada. Segundo, precisamos entender os tipos de dados que
podem ser mapeados do mundo real para o modelo. E, por �m, entender a qual
linguagem de de�nição de dados pode ser aplicada.
Ao �nal desta aula, você será capaz de:
entender a modelagem de dados utilizando o modelo entidade-relacionamento
(E-R);
aprender os tipos de entidade, conjuntos de entidades, atributos e chaves;
aprender sobre os tipos de relacionamento, conjuntosde relacionamento,
funções e restrições estruturais;
entender sobre a Data De�nition Language.
Aula 02
Introdução
25
Modelagem de Dados Utilizando o
Modelo Entidade-Relacionamento (E-
R)
A modelagem conceitual é uma fase muito importante na criação de um aplicativo de
banco de dados bem-sucedido. Geralmente, o termo aplicativo de banco de dados se
refere a um banco de dados especí�co e programas associados que implementam as
consultas e atualizações do banco de dados. Por exemplo, um aplicativo de banco de
dados UNIVERSIDADE que gerencia os professores e alunos para orientações de
programas de mestrados pode ser modelado via E-R. Esses programas fornecem
interfaces grá�cas de usuário fáceis de usar, utilizando formulários e menus.
Portanto, uma parte importante do aplicativo de banco de dados exigirá o design, a
implementação e o teste desses programas de aplicativos. Tradicionalmente, o design
e o teste de programas aplicativos foram considerados como parte da engenharia de
software, e não do design do banco de dados. Em muitas ferramentas de design de
software, as metodologias de design de banco de dados e as metodologias de
engenharia de software estão interligadas, uma vez que essas atividades estão
fortemente relacionadas.
26
Neste estudo, seguimos a abordagem tradicional de concentração nas estruturas e
restrições durante o design conceitual do banco de dados. O design de programas de
aplicação é tipicamente coberto em cursos de engenharia de software. Apresentamos
os conceitos de modelagem do modelo Entidade-Relacionamento (ER), que é um
popular modelo de dados conceitual de alto nível. Esse modelo e suas variações são
frequentemente usados para o design conceitual de aplicativos de banco de dados, e
muitas ferramentas de design empregam seus conceitos. Descreveremos os conceitos
básicos de estruturação de dados e restrições do modelo de ER e discutiremos seu
uso no projeto de esquemas conceituais para aplicações de banco de dados. Também
apresentaremos a notação diagramática associada ao modelo ER, conhecida como
diagrama ER.
Processo de design de banco de dados
SAIBA MAIS
O desenvolvimento de software tem uma intersecção com os projetos de bancos
de dados. O banco de dados acomoda as estruturas de dados enquanto o
software fornece as funcionalidades.
Saiba mais, acessando: http://bit.ly/2XKtML8.
27
http://bit.ly/2XKtML8
A Figura 1 mostra uma visão geral e simpli�cada do processo de design do banco de
dados. A primeira etapa mostrada é a de�nição e análise de requisitos. Durante essa
etapa, os projetistas de bancos de dados entrevistam usuários em potencial para
entender e modelar seus requisitos de dados. O resultado dessa etapa é um conjunto
de requisitos de usuários redigidos de forma concisa. Esses requisitos devem ser
especi�cados da maneira mais detalhada e completa possível. Paralelamente à
especi�cação dos requisitos de dados, é útil especi�car os requisitos funcionais
conhecidos da aplicação. Eles consistem em operações de�nidas pelo usuário (ou
transações) que serão aplicadas ao banco de dados, incluindo recuperações e
atualizações. No design de software, é comum usar diagramas de �uxo de dados,
diagramas de sequência, cenários e outras técnicas para especi�car requisitos
funcionais. Não vamos discutir nenhuma dessas técnicas aqui; eles são geralmente
descritos em detalhes em textos de engenharia de software.
Depois que os requisitos foram de�nidos e analisados, a próxima etapa é criar um
esquema conceitual para o banco de dados, usando um modelo de dados conceitual
de alto nível. Essa etapa é chamada de design conceitual. O esquema conceitual é uma
descrição concisa dos requisitos de dados dos usuários e inclui descrições detalhadas
dos tipos de entidade, relacionamentos e restrições; estes são expressos usando os
Figura 1 - Projeto de banco de dados e de aplicativo de banco de dados
Fonte: Adaptada de Silberschatz et al. (2019).
28
conceitos fornecidos pelo modelo de dados de alto nível. Como esses conceitos não
incluem detalhes de implementação, eles geralmente são mais fáceis de entender e
podem ser usados para se comunicar com usuários não técnicos. O esquema
conceitual de alto nível também pode ser usado como referência para garantir que os
requisitos de dados de todos os usuários sejam atendidos e que os requisitos não
entrem em con�ito. Essa abordagem permite que os designers de banco de dados se
concentrem na especi�cação das propriedades dos dados, sem se preocupar com
detalhes de armazenamento e implementação. Isso facilita a criação de um bom
design de banco de dados conceitual.
Durante ou após o design do esquema conceitual, as operações básicas do modelo de
dados podem ser usadas para especi�car as consultas e operações de alto nível do
usuário identi�cadas durante a análise funcional. Isso também serve para con�rmar
que o esquema conceitual atende a todos os requisitos funcionais identi�cados.
Modi�cações no esquema conceitual podem ser introduzidas se alguns requisitos
funcionais não puderem ser especi�cados usando o esquema inicial.
SAIBA MAIS
Um bom modelo de dados deve representar bem o minimundo. Além disso, deve
ter os tipos de dados precisos (como inteiros, datas, monetários etc.). Isso
melhora muito o projeto de modelo de banco de dados.
29
O próximo passo no design do banco de dados é a implementação real do banco de
dados, usando um SGBD comercial. A maioria dos SGBDs comerciais atuais usa um
modelo de dados de implementação - como o modelo de banco de dados relacional
ou objeto-relacional - para que o esquema conceitual seja transformado do modelo
de dados de alto nível para o modelo de dados de implementação. Essa etapa é
chamada de design lógico ou mapeamento de modelo de dados. Seu resultado é um
esquema de banco de dados no modelo de dados de implementação do SGBD. O
mapeamento de modelo de dados é geralmente automatizado ou semiautomatizado
nas ferramentas de design do banco de dados.
A última etapa é a fase de design físico, durante a qual as estruturas de
armazenamento interno, organizações de arquivos, índices, caminhos de acesso e
parâmetros de design físico para os arquivos de banco de dados são especi�cados.
Paralelamente a essas atividades, os programas aplicativos são projetados e
implementados como transações de bancos de dados correspondentes às
especi�cações de transações de alto nível.
Usando Modelos de Dados Conceituais de Alto Nível
para Design de Banco de Dados
Nesta seção, descreveremos um aplicativo de banco de dados de amostra,
denominado UNIVERSIDADE, que serve para ilustrar os conceitos básicos do modelo
ER e seu uso no design do esquema. Listamos os requisitos de dados para o banco de
dados aqui e, em seguida, criamos seu esquema conceitual passo a passo à medida
que introduzimos os conceitos de modelagem do modelo ER. O banco de dados da
UNIVERSIDADE gerenciar os professores, departamentos e alunos de uma
universidade. Suponha que após a fase de de�nição e análise de requisitos, os
projetistas do banco de dados forneçam a seguinte descrição do minimundo - a parte
da universidade que será representada no banco de dados.
A universidade é organizada em departamentos. Cada departamento tem um
nome exclusivo, um número exclusivo e um professor especí�co que gerencia o
departamento. Acompanhamos a data de início em que o professor começou a
gerenciar o departamento.
Um departamento pode ter vários locais.
Um departamento pode gerenciar vários professores, cada um com um nome
exclusivo e um número exclusivo.
30
A universidade tem o gerenciamento dos alunos com um número de matrícula
exclusivo e com o e-mail também exclusivo.
Cada aluno é orientado por um único professor e cada professor pode orientar
muitos alunos.
A orientação é acompanhada da data de início e, quando �nalizada, pela data de
encerramento.
A Figura 2 mostra como o esquema para esse aplicativo de banco de dados pode ser
exibido por meio da notação grá�ca conhecida como diagrama ER. Este valor será
explicado gradualmenteà medida que os conceitos do modelo ER forem
apresentados. Descrevemos o processo passo a passo de derivar este esquema dos
requisitos declarados - e explicar a notação diagramática do ER - à medida que
introduzimos os conceitos do modelo ER.
Figura 2 - Modelo de entidade-relacionamento do aplicativo
Fonte: Elaborada pelo autor.
31
Tipos de Entidade, Conjuntos de
Entidades, Atributos e Chaves
O modelo ER descreve os dados como entidades, relacionamentos e atributos. Na
Seção 2.1, introduziremos os conceitos de entidades e seus atributos. Discutiremos
os tipos de entidade e atributos-chave na Seção 2.2.
Entidades e Atributos
O objeto básico que o modelo ER representa é uma entidade, que é uma coisa no
mundo real com uma existência independente. Uma entidade pode ser um objeto
com uma existência física (por exemplo, uma determinada pessoa, carro, casa ou
professor) ou pode ser um objeto com uma existência conceitual (por exemplo, uma
universidade, um emprego ou um curso universitário). Cada entidade possui
atributos - as propriedades particulares que descrevem. Por exemplo, uma entidade
PROFESSOR pode ser descrita pelo nome, idade, endereço, salário e departamento.
Uma entidade particular, ou registro, terá um valor para cada um dos seus atributos.
Os valores de atributos que descrevem cada entidade tornam-se uma parte
importante dos dados armazenados no banco de dados.
32
Vários tipos de atributos ocorrem no modelo ER: simples versus composto, valor
único versus valor múltiplo e armazenado versus derivado. Os atributos compostos
podem ser divididos em subpartes menores, que representam atributos mais básicos
com signi�cados independentes. Atributos compostos são úteis para modelar
situações em que um usuário às vezes se refere ao atributo composto como uma
unidade, mas em outros momentos se refere especi�camente a seus componentes.
Se o atributo composto é referenciado apenas como um todo, não é preciso
subdividi-lo em atributos de componente. Por exemplo, se não houver necessidade
de se referir aos componentes individuais de um endereço (CEP, rua e assim por
diante), o endereço completo poderá ser designado como um atributo simples. Na
entidade ENDERECO optamos por um atributo composto para representar a rua
(NOME_RUA e NUMERO_RUA).
Adicionalmente, temos os atributos de valor único versus multivalorado. A maioria
dos atributos possui um valor único para uma entidade especí�ca; esses atributos são
chamados de valor único. Por exemplo, UF é um atributo de valor único de um
SAIBA MAIS
Sempre que possível evite atributos com valores nulos. Quando existir essa
necessidade, tente aplicar uma das formas normais. Valores nulos em banco de
dados trazem muitos problemas como tuplas espúrias e eventuais consultas com
agregações.
33
ENDERECO. Em alguns casos, um atributo pode ter um conjunto de valores para a
mesma entidade - por exemplo, um atributo Cores para um carro ou um atributo
Títulos para uma pessoa. Tais atributos são chamados de valores múltiplos. Um
atributo multivalorado pode ter limites inferiores e superiores para restringir o
número de valores permitidos para cada entidade individual. Por exemplo, o atributo
Cores de um carro pode ser restrito a ter entre um e três valores, se assumirmos que
um carro pode ter três cores no máximo. Em nosso modelo, os atributos são de
valores únicos.
Temos também os atributos armazenados versus derivados. Em alguns casos, dois (ou
mais) valores de atributos estão relacionados - por exemplo, os atributos IDADE e
DATA_NASCIMENTO de uma pessoa. Para uma entidade de pessoa especí�ca, o
valor de Idade pode ser determinado a partir da data atual (de hoje) e do valor da
data de nascimento dessa pessoa. O atributo IDADE é, portanto, chamado de um
atributo derivado e é dito derivável do atributo DATA_NASCIMENTO, que é
chamado de atributo armazenado. Alguns valores de atributos podem ser derivados
de entidades relacionadas; por exemplo, um atributo QUANTIDADE_PROFESSORES
ATENÇÃO
Grande parte dos atributos em bancos de dados são multivalorados. De�nir
limites superiores e inferiores para eles, via restrições, são uma boa prática.
34
de uma entidade DEPARTAMENTO pode ser derivado contando o número de
professores relacionados a (trabalhando para) este departamento.
Em alguns casos, uma entidade especí�ca pode não ter um valor aplicável para um
atributo. Por exemplo, o atributo DATA_ENCERRAMENTO de uma orientação se
aplica somente a orientações que estão concluídas. Para tais situações, um valor
especial chamado NULL é criado. O primeiro signi�cado do tipo NULL pode ser “não
é aplicável”, enquanto o segundo pode ser “desconhecido”. A categoria desconhecida
de NULL pode ser classi�cada em dois casos. O primeiro caso surge quando se sabe
que o valor do atributo existe, mas está ausente. O segundo caso surge quando não
se sabe se o valor do atributo existe.
Por �m, temos os atributos complexos. Observe que, em geral, os atributos
compostos e multivalorados podem ser aninhados arbitrariamente. Podemos
representar o aninhamento arbitrário agrupando componentes de um atributo
composto entre parênteses () e separando os componentes com vírgulas e exibindo
atributos de valores múltiplos entre chaves {}. Tais atributos são chamados de
atributos complexos. Por exemplo, se um professor tem mais de um endereço e cada
endereço tem vários telefones, um atributo ENDERECO_TELEFONE para uma
pessoa pode ser especi�cado como atributo complexo. Em nosso modelo não
utilizamos esse tipo de atributo.
Tipos de Entidades, Conjuntos de Entidades, Chaves
e Conjuntos de Valores
Um banco de dados geralmente contém grupos de entidades semelhantes. Por
exemplo, uma universidade que emprega centenas de professores pode querer
armazenar informações semelhantes sobre cada um deles. Essas entidades de
professores compartilham os mesmos atributos, mas cada entidade possui seus
próprios valores para cada atributo. Um tipo de entidade de�ne uma coleção (ou
conjunto) de entidades que possuem os mesmos atributos. Cada tipo de entidade no
banco de dados é descrito pelos seus nomes e atributos. A Figura 3 mostra dois tipos
de entidades: PROFESSOR e ALUNO, e uma lista de alguns dos atributos de cada um.
Algumas entidades individuais de cada tipo também são ilustradas, juntamente com
os valores de seus atributos. A coleção de todas as entidades de um determinado tipo
de entidade na base de dados em qualquer momento é chamada de conjunto de
entidades; o conjunto de entidades é geralmente referido usando o mesmo nome que
o tipo de entidade. Por exemplo, PROFESSOR refere-se a um tipo de entidade, bem
como ao conjunto atual de todas as entidades do professor no banco de dados.
35
Uma restrição importante nas entidades de um tipo de entidade é a restrição de
chave ou exclusividade nos atributos. Um tipo de entidade geralmente possui um ou
mais atributos cujos valores são distintos para cada entidade individual no conjunto
de entidades. Esse atributo é chamado de atributo-chave e seus valores podem ser
usados para identi�car cada entidade de maneira exclusiva. Às vezes, vários atributos
juntos formam uma chave, o que signi�ca que a combinação dos valores dos atributos
deve ser distinta para cada entidade. Se um conjunto de atributos possui essa
propriedade, a maneira correta de representá-lo no modelo E-R que descrevemos
aqui é de�nir um atributo composto e designá-lo como um atributo-chave do tipo de
entidade. Observe que essa chave composta deve ser mínima; ou seja, todos os
atributos do componente devem ser incluídos no atributo composto para ter a
propriedade unicidade. Atributos supér�uos não devem ser incluídos em uma chave.
Especi�car que um atributo como chave de um tipo de entidade signi�ca que a
propriedade de exclusividade anterior deve ser mantida para cada conjunto de
entidades do tipo de entidade. Portanto, é uma restrição que proíbe que duas
entidades tenham o mesmo valor para o atributo-chave ao mesmo tempo. Não é a
propriedade de um determinado conjunto de entidades;em vez disso, é uma
restrição em qualquer conjunto de entidades do tipo de entidade em qualquer
momento. Esta restrição chave (e outras restrições que discutiremos mais adiante) é
derivada das restrições do minimundo que o banco de dados representa.
Figura 3 - Atributos das entidades PROFESSOR e ALUNO
Fonte: Elaborada pelo autor.
36
Cada atributo simples de um tipo de entidade é associado a um conjunto de valores
(ou domínio de valores), que especi�ca o conjunto de valores que podem ser
atribuídos a esse atributo para cada entidade individual. Por exemplo, se a faixa de
idades permitidas para os alunos estiver entre 18 e 70, podemos especi�car o
conjunto de valores do atributo DATA_NASCIMENTO de ALUNO para ser o conjunto
de números inteiros resultantes entre 18 e 70. Da mesma forma, podemos
especi�car o conjunto de valores para o atributo NOME ser o conjunto de caracteres
alfabéticos separados por caracteres em branco e assim por diante. Conjuntos de
valores não são exibidos em diagramas E-R e são normalmente especi�cados usando
os tipos de dados básicos disponíveis na maioria das linguagens de programação,
como integer, string, boolean, �oat, tipo enumerado e assim por diante. Tipos de dados
adicionais para representar tipos comuns de banco de dados, como data, hora e
outros conceitos, também são empregados.
SAIBA MAIS
A utilização de restrições em atributos de bancos de dados é uma boa maneira de
manter a consistência do banco de dados.
Saiba mais, acessando: http://bit.ly/30DBAjN.
37
http://bit.ly/30DBAjN
Tipos de Relacionamento, Conjuntos
de Relacionamento, Funções e
Restrições Estruturais
Na Figura 2, existem vários relacionamentos implícitos entre os tipos de entidade. De
fato, sempre que um atributo de um tipo de entidade se refere a outro tipo de
entidade, existe algum relacionamento. Por exemplo, o atributo CURSO_FK de
ALUNO refere-se a um curso que o aluno participa; o atributo DEPARTAMENTO_FK
do PROFESSOR se refere ao departamento que gerencia o professor; e assim por
diante. No modelo E-R, essas referências não devem ser representadas como
atributos, mas como relações, que são discutidas nesta seção. No design inicial dos
tipos de entidade, os relacionamentos são tipicamente capturados na forma de
atributos. À medida que o design é re�nado, esses atributos são convertidos em
relacionamentos entre tipos de entidade.
38
Tipos de Relacionamento, Conjuntos e Instâncias
Um tipo de relacionamento R entre n tipos de entidades E1, E2, ..., En de�ne um
conjunto de associações (ou um conjunto de relacionamentos) entre entidades
desses tipos de entidade. Quanto ao caso de tipos de entidade e conjuntos de
entidades, um tipo de relacionamento e seu conjunto de relacionamentos
correspondente são costumeiramente referenciados pelo mesmo nome, R.
Informalmente, cada instância de relacionamento ri em R é uma associação de
entidades, onde a associação inclui exatamente uma entidade de cada tipo de
entidade participante. Cada instância de relacionamento ri representa o fato de que
as entidades participantes de ri estão relacionadas de alguma forma na situação do
minimundo correspondente. Por exemplo, considere um tipo de relacionamento
TRABALHA_PARA entre os dois tipos de entidade PROFESSOR e DEPARTAMENTO,
que associa cada professor ao departamento para o qual trabalha. Cada instância de
SAIBA MAIS
Um relacionamento de tabela é representado por uma linha de relacionamento
desenhada entre as tabelas na janela Relacionamentos. Um relacionamento que
não aplica a integridade referencial aparece como uma linha �na entre os campos
comuns que suportam o relacionamento.
Saiba mais, acessando: http://bit.ly/2SaGNfP.
39
http://bit.ly/2SaGNfP
relacionamento no conjunto de relacionamento TRABALHA_PARA associa uma
entidade PROFESSOR e uma entidade DEPARTAMENTO. A Figura 4 ilustra este
exemplo, onde cada instância de relacionamento ri é mostrada conectada às
entidades PROFESSOR e DEPARTAMENTO que participam de ri. No minimundo
representado pela Figura 4, os professores p1, p3 e p6 trabalham para o
departamento d1; professores p2 e p4 trabalham para o departamento d2; e os
professores p5 e p7 trabalham para o departamento d3.
4. Data De�nition Language
A seguir, podemos ver o script DDL gerado pelo nosso modelo. Esse script contém
alguns elementos especí�cos do banco de dados utilizados no estudo chamado
MySQL. Vamos ver o script linha a linha e entender o que está sendo feito em cada
situação.
DROP DATABASE UNIVERSIDADE;
Figura 4 - Exemplo de relacionamentos 
Fonte: Adaptada de Silberschatz et al. (2019).
40
CREATE DATABASE UNIVERSIDADE;
USE UNIVERSIDADE;
CREATE TABLE ENDERECO (
       ID INT NOT NULL PRIMARY KEY,
   NOME_RUA VARCHAR(50) NOT NULL,
   NUMERO_RUA VARCHAR(10) NOT NULL,
   CEP VARCHAR(8) NOT NULL,
   BAIRRO VARCHAR(50) NOT NULL,
   CIDADE VARCHAR(50) NOT NULL,
   UF CHAR(2) NOT NULL
) ENGINE=INNODB;
CREATE TABLE DEPARTAMENTO (
ID INT NOT NULL PRIMARY KEY,
   NOME VARCHAR(50) NOT NULL
) ENGINE=INNODB;
CREATE TABLE PROFESSOR (
ID INT NOT NULL PRIMARY KEY,
   NOME VARCHAR(50) NOT NULL,
   DEPARTAMENTO_FK INT NOT NULL,
ENDERECO_FK INT NOT NULL,
   FOREIGN KEY (DEPARTAMENTO_FK)
       REFERENCES DEPARTAMENTO(ID)
41
       ON DELETE CASCADE,
   FOREIGN KEY (ENDERECO_FK)
       REFERENCES ENDERECO(ID)
       ON DELETE CASCADE
) ENGINE=INNODB;
CREATE TABLE CURSO (
ID INT NOT NULL PRIMARY KEY,
   NOME VARCHAR(50) NOT NULL
) ENGINE=INNODB;
CREATE TABLE ALUNO (
ID INT NOT NULL PRIMARY KEY,
   NOME VARCHAR(50) NOT NULL,
   DATA_NASCIMENTO DATE NOT NULL,
   CURSO_FK INT,
   FOREIGN KEY (CURSO_FK)
       REFERENCES CURSO(ID)
       ON DELETE CASCADE
) ENGINE=INNODB;
CREATE TABLE ORIENTACAO (
PROFESSOR_FK INT,
ALUNO_FK INT,
   DATA_INICIO DATE,
42
   DATA_ENCERRAMENTO DATE,
   FOREIGN KEY (PROFESSOR_FK)
       REFERENCES PROFESSOR(ID)
       ON DELETE CASCADE,
   FOREIGN KEY (ALUNO_FK)
       REFERENCES ALUNO(ID)
       ON DELETE CASCADE
) ENGINE=INNODB;
A linguagem de consulta estruturada (SQL) é a linguagem de banco de dados que
podemos realizar determinadas operações no banco de dados existente e também
podemos usar essa linguagem para criar um banco de dados. O SQL usa certos
comandos como Criar, Descartar, Inserir etc., para executar as tarefas necessárias.
Esses comandos SQL são, principalmente, categorizados em quatro categorias,
conforme discutidas a seguir.
Figura 5 - Classi�cação dos comandos SQL
Fonte: Elaborada pelo autor.
43
A primeira categoria, chamada de DDL (Linguagem de De�nição de Dados) consiste
nos comandos SQL que podem ser usados para de�nir o esquema do banco de dados.
Ele simplesmente lida com descrições do esquema do banco de dados e é usado para
criar e modi�car a estrutura de objetos de banco de dados no banco de dados.
Exemplos de comandos DDL:
CREATE - é usado para criar o banco de dados ou seus objetos (como tabela,
índice, função, visualizações, procedimento de armazenamento e gatilhos).
DROP - é usado para excluir objetos do banco de dados.
ALTER - é usado para alterar a estrutura do banco de dados.
TRUNCATE - é usado para remover todos os registros de uma tabela, inclusive
todos os espaços alocados para os registros são removidos.
COMMENT - é usado para adicionar comentários ao dicionário de dados.
RENAME - é usado para renomear um objeto existente no banco de dados.
Os comandos DDL são muito úteis no início do projeto do banco, quando as
de�nições estão sendo feitas. Já na DML (Data Manipulation Language), os comandos
SQL que lidam com a manipulação de dados presentes no banco de dados pertencem
a DML ou Data Manipulation Language e isso inclui a maioria das instruções SQL.
Alguns exemplos de DML:
SELECT - é usado para recuperar dados do banco de dados.
INSERT - é usado para inserir dados em uma tabela.
UPDATE - é usado para atualizar os dados existentes em uma tabela.
DELETE - é usado para excluir registros de uma tabela de banco de dados.
Adicionalmente, a DCL (DataControl Language) inclui comandos como GRANT e
REVOKE, que lidam principalmente com os direitos, permissões e outros controles do
sistema de banco de dados. Alguns exemplos de comandos DCL:
GRANT - dá privilégios de acesso do usuário ao banco de dados.
REVOKE - retira os privilégios de acesso do usuário usando o comando GRANT.
44
A TCL (Transaction Control Language) lida com a transação dentro do banco de dados.
Alguns exemplos de comandos do TCL:
COMMIT - con�rma uma transação.
ROLLBACK - reverte uma transação no caso de ocorrer algum erro.
SAVEPOINT - de�ne um ponto de salvamento dentro de uma transação.
SET TRANSACTION - especi�ca características para a trans
SAIBA MAIS
A W3Schools apresenta uma classi�cação da SQL para o MySQL. Você pode
aprender mais no link a seguir.
Acesse: http://bit.ly/32lyrXi.
45
http://bit.ly/32lyrXi
QUESTÃO OBJETIVA
Uma fase muito importante na criação de um aplicativo de
banco de dados bem-sucedido é aquela que mapeia as
entidades do mundo real para um modelo. Esse modelo
tem o objetivo de relacionar as entidades uma com as
outras para entender como elas funcionam no mundo real.
Neste contexto, qual o nome dessa modelagem?
Modelagem conceitual.
Modelagem lógica.
Modelagem física.
Modelagem estrutural.
Modelagem comercial.
46
QUESTÃO OBJETIVA
Depois que os requisitos foram de�nidos e analisados, a
próxima etapa é criar um esquema para o banco de dados,
usando um modelo de dados de alto nível. Esse esquema é
uma descrição concisa dos requisitos de dados dos
usuários e inclui descrições detalhadas dos tipos de
entidade, relacionamentos e restrições. Qual o nome dessa
etapa?
Design alternativo.
Design arquitetural.
Design de modelo.
Design lógico.
Design conceitual.
47
Fechamento
O maior benefício ao se projetar um banco de dados é o entendimento de
fundamentos que possam ser aplicados em vários cenários. Primeiramente,
entendemos qual a técnica de modelagem a ser utilizada. Segundo, compreendemos
os tipos de dados que podem ser mapeados do mundo real para o modelo. E, por �m,
aprendemos a qual linguagem de de�nição de dados pode ser aplicada.
Nesta aula, você teve a oportunidade de:
aprender a modelagem de dados utilizando o modelo Entidade-Relacionamento
(E-R);
entender os tipos de entidade, conjuntos de entidades, atributos e chaves;
aprender sobre os tipos de relacionamento, conjuntos de relacionamento,
funções e restrições estruturais;
entender sobre a Data De�nition Language.
48
Linguagens de Manipulação e
Consulta de Dados
Recuperação de dados é uma parte fundamental de um bom projeto de banco de
dados. Dessa forma, utilizar boas técnicas de consulta pode ser um grande diferencial
no momento do projeto e da operação do banco de dados.
Ao �nal desta aula, você será capaz de:
entender a visão geral da linguagem SQL Query;
aprender sobre a estrutura básica de consultas SQL;
aprender sobre as consultas em uma única relação;
entender as consultas sobre Múltiplas Relações.
Aula 03
Introdução
49
Visão Geral da Linguagem SQL Query
Há um número de linguagens de consulta de banco de dados em uso, seja
comercialmente ou experimentalmente. Vamos estudar a linguagem de consulta
amplamente utilizada: Structured Query Language (SQL). Embora nos referimos à
linguagem SQL como uma “linguagem de consulta”, ela pode fazer muito mais do que
apenas consultar um banco de dados. Pode ser utilizada para de�nir a estrutura de
dados, modi�car dados no banco de dados e especi�car restrições de segurança. Não
é nossa intenção fornecer um guia completo para o SQL. Pelo contrário, nós vamos
apresentar construções e conceitos fundamentais da SQL. Implementações
individuais de SQL podem diferir em detalhes, ou podem suportar apenas um
subconjunto da linguagem completa (SILBERSCHATZ et al., 2019).
SAIBA MAIS
A linguagem de programação SQL foi desenvolvida pela primeira vez na década
de 1970 pelos pesquisadores da IBM Raymond Boyce e Donald Chamberlin. A
linguagem de programação, conhecida então como SEQUEL, foi criada após a
publicação do artigo de Edgar Frank Todd, "Um modelo relacional de dados para
grandes bancos de dados compartilhados", em 1970.
Saiba mais, acessando: http://bit.ly/2Jxu8kr.
50
http://bit.ly/2Jxu8kr
A IBM desenvolveu a versão original da SQL, originalmente chamada de Sequel,
como parte do Projeto System R no início dos anos 70. A linguagem Sequel evoluiu
desde então, e seu nome foi alterado para SQL (Structured Query Language). Muitos
produtos agora suportam a linguagem SQL. A SQL estabeleceu-se claramente como
padrão de linguagem de banco de dados relacional. Em 1986, o Instituto Nacional de
Padrões Americanos (ANSI) e a Organização Internacional de Normalização (ISO)
publicou um padrão SQL, chamado SQL-86. O ANSI publicou um padrão estendido
para SQL, SQL-89, em 1989. A próxima versão a partir da norma foi o padrão SQL-92,
seguido de SQL: 1999, SQL: 2003, SQL: 2006, e mais recentemente SQL: 2008. A
linguagem SQL possui várias partes (VANIER et al., 2019):
linguagem de de�nição de dados (DDL). A DDL fornece comandos para de�nir
esquemas de relação, excluindo relações e modi�cando esquemas de relação;
linguagem de manipulação de dados (DML). A DML fornece a capacidade de
consultar informações do banco de dados e inserir tuplas, excluir tuplas e
modi�car as tuplas no banco de dados;
integridade. A DDL inclui comandos para especi�car a integridade das
restrições que os dados armazenados no banco de dados devem satisfazer.
Atualizações que violam restrições de integridade não são permitidas;
controle de transações. A SQL inclui comandos para especi�car o início e �nal
de transações;
SQL incorporado e SQL dinâmico. SQL incorporado e dinâmico de�ne como as
instruções SQL podem ser incorporadas em linguagens de programação, como
C, C ++ e Java;
autorização. A DDL inclui comandos para especi�car direitos de acesso às
relações e pontos de vista.
Neste estudo, apresentamos uma pesquisa de DML básica e os recursos de DDL do
SQL. Os recursos descritos aqui fazem parte do padrão SQL desde o SQL-92.
Posteriormente, forneceremos uma cobertura mais detalhada da linguagem de
consulta SQL, incluindo (a) várias expressões de junção; (b) visões; (c) transações; d)
integridade das restrições; e) sistema de tipos; e (f) autorização. Embora a maioria
das implementações SQL suporte os recursos padrão, você deve estar ciente de que
existem diferenças entre a implementação. Por exemplo, um banco de dados como o
MySQL pode implementar de uma forma e o Oracle de outra (VANIER et al., 2019).
51
Linguagem de Manipulação de dados
A Linguagem de Manipulação de Dados (DML) é usada para recuperar, inserir e
modi�car informações do banco de dados. Esses comandos são usados por todos os
usuários do banco de dados durante a operação de rotina do banco de dados.
INSERT. O comando INSERT no SQL é usado para adicionar registros a uma
tabela existente. Por exemplo, imagine que nosso departamento de RH precisa
adicionar um novo professor ao banco de dados. Você poderia usar um
comando semelhante a este:
INSERT INTO PROFESSOR values('João','Silva',12345,5000);
Observe que há quatro valores especi�cados para o registro. Eles correspondem aos
atributos da tabela na ordem em que foram de�nidos.
SELECT. O comando SELECT é o comando mais usado no SQL. Ele permite que
os usuários do banco de dados recuperem as informações especí�cas que
desejam de um banco de dados operacional. Dê uma olhada em alguns
exemplos, novamente usando a tabela professor do banco de dados da
Figura 1 - Visão geral do SQL 
Fonte: Weerapat Wattanapichayakul / 123RF.
52
universidade. O comando mostrado, a seguir, recupera todas as informações
contidas na tabela PROFESSOR. Observe que o asterisco é usado como curinga
no SQL. Isso signi�ca literalmente "Selecione tudo na tabela professor".
SELECT * FROM PROFESSOR;
Como alternativa, os usuários podem querer limitar os atributos recuperados do
banco de dados. Por exemplo, o departamentode Recursos Humanos pode exigir
uma lista dos sobrenomes de todos os funcionários da empresa. O seguinte comando
SQL recuperaria apenas essa informação:
SELECT NOME FROM PROFESSOR;
A cláusula WHERE pode ser usada para limitar os registros recuperados àqueles que
atendem aos critérios especi�cados. O reitor pode estar interessado em revisar os
registros de pessoal de todos os professores altamente remunerados. O comando, a
seguir, recupera todos os dados contidos em PROFESSOR para registros que
possuem um valor de salário maior que $ 5.000:
SELECT * FROM PROFESSOR WHERE SALARIO > $5000;
53
UPDATE. O comando UPDATE pode ser usado para modi�car as informações
contidas em uma tabela, em massa ou individualmente. Suponha que a empresa
conceda a todos os funcionários um aumento de 3% no custo de vida do salário
anualmente. O seguinte comando SQL pode ser usado para aplicar isso
rapidamente a todos os funcionários armazenados no banco de dados:
UPDATE PROFESSOR SET SALARIO = SALARIO * 1.03;
Quando o novo funcionário, João Silva, demonstra um desempenho acima e além do
dever, a administração deseja reconhecer suas realizações estelares com um
aumento de $ 5.000. A cláusula WHERE poderia ser usada para destacar João para
este aumento:
UPDATE PROFESSOR SET SALARIO = SALARIO + 5000 WHERE ID = 12345;
SAIBA MAIS
O comando SELECT é um dos mais utilizados da SQL e dessa forma vale a pena
entender melhor o seu funcionamento. Existem várias cláusulas que podem ser
utilizadas neste comando.
Saiba mais, acessando: http://bit.ly/2JuoNu66.
54
http://bit.ly/2JuoNu6
DELETE. Finalmente, vamos dar uma olhada no comando DELETE. Você verá que
a sintaxe deste comando é semelhante a dos outros comandos DML.
Infelizmente, nosso último relatório de ganhos corporativos não atendeu às
expectativas e o João foi demitido. O comando DELETE com uma cláusula
WHERE pode ser usado para remover seu registro da tabela PROFESSOR:
DELETE FROM PROFESSOR WHERE ID = 12345;
Agora que você tem um contexto de como manipular os dados via DML, vamos
analisar esses comandos com mais detalhes a seguir.
SAIBA MAIS
O UPDATE possui algumas cláusulas que são comuns ao SELECT. Deve-se tomar
cuidado para não atualizar linhas que não sejam as desejadas.
Saiba mais, acessando: http://bit.ly/2YLvzRk.
55
http://bit.ly/2YLvzRk
De�nição de Dados SQL
O padrão SQL suporta uma variedade de tipos integrados. Esses tipos podem ser
utilizados para melhorar a modelagem do minimundo e, melhorando assim, a forma
como o banco de dados resolve o problema, incluindo:
char(n): uma cadeia de caracteres de comprimento �xo com comprimento
especi�cado pelo usuário n;
varchar(n): uma cadeia de caracteres de tamanho variável com o máximo
especi�cado pelo usuário comprimento n. A forma completa, com caráter
variável, é equivalente;
int: um inteiro (um subconjunto �nito dos inteiros que é dependente da
máquina). A forma completa, inteiro, é equivalente;
SAIBA MAIS
O DELETE possui algumas cláusulas que são comuns ao SELECT. Deve-se tomar
cuidado para não remover as linhas que não sejam as desejadas.
Saiba mais, acessando: http://bit.ly/2Si9g3q.
56
http://bit.ly/2Si9g3q
smallint: um inteiro pequeno (um subconjunto dependente da máquina do tipo
inteiro);
numeric(p, d): um número de ponto �xo com precisão especi�cada pelo usuário.
O número ber consiste em p dígitos (mais um sinal), e d dos p dígitos estão à
direita de o ponto decimal. Assim, numérico (3,1) permite que 44,5 sejam
armazenados exatamente, mas nem 444,5 ou 0,32 podem ser armazenados
exatamente em um campo desse tipo;
double: ponto �utuante e ponto �utuante de precisão dupla números com
precisão dependente da máquina;
�oat(n): um número de ponto �utuante, com precisão de pelo menos n dígitos.
SAIBA MAIS
Os tipos que podem ser utilizados para as colunas fazem com que os modelos de
dados possam ser melhor estruturados. Saiba quais são os tipos suportados pelo
banco que você está utilizando acessando: http://bit.ly/30u1uGj.
57
http://bit.ly/30u1uGj
Estrutura Básica de Consultas SQL
A estrutura básica de uma consulta SQL consiste em três cláusulas: select, from e
where. A consulta toma como entrada as relações listadas na cláusula from, opera
neles, conforme especi�cado nas cláusulas where e select, e produz uma relação como
resultado. Introduzimos a sintaxe SQL por meio de exemplos e descreveremos a
estrutura geral de consultas SQL mais tarde.
Consultas em uma Única Relação
Vamos considerar uma consulta simples usando nosso exemplo da universidade:
“Encontre os nomes de todos os professores”. Nomes de professores são
encontrados na relação professor, então colocamos essa relação na cláusula from. O
Figura 2 - Tipos de dados 
Fonte: Piotr Trojanowski / 123RF.
58
nome do professor aparece no nome atributo, então colocamos isso na cláusula
select.
SELECT `PROFESSOR`.`NOME`,
FROM `UNIVERSIDADE`.`PROFESSOR`;
O resultado é uma relação que consiste em um único atributo com o nome do
cabeçalho. E se a relação de professor é mostrada na Figura 1, então a relação que
resulta da consulta anterior é mostrada na Figura 2. Agora, considere outra consulta,
"encontre os nomes dos departamentos de todos os professores" que pode ser
escrito como:
SELECT
   `DEPARTAMENTO`.`NOME`
FROM `UNIVERSIDADE`.`PROFESSOR`
   INNER JOIN `UNIVERSIDADE`.`DEPARTAMENTO`
         ON `PROFESSOR`.`DEPARTAMENTO_FK` = `DEPARTAMENTO`.`ID`;
Como mais de um professor pode pertencer a um departamento, um nome de
departamento pode aparecer mais de uma vez na relação de professor. O resultado
da consulta é uma relação contendo os nomes dos departamentos, mostrada na
Figura 3. Na de�nição formal e matemática do modelo relacional, uma relação é um
conjunto. Assim, as tuplas duplicadas nunca apareceriam nas relações. Na prática,
duplicar a eliminação é demorada. Portanto, o SQL permite duplicatas em relações,
bem como nos resultados de expressões SQL. Assim, a lista resultante da consulta
SQL exibe o nome de cada departamento uma vez para cada tupla em que aparece o
nome do professor na relação (SILBERSCHATZ et al., 2019). Nos casos em que
queremos forçar a eliminação de duplicatas, inserimos a palavra-chave distinta
depois de selecionar. Podemos reescrever a consulta anterior como:
SELECT DISTINCT
       `DEPARTAMENTO`.`NOME`
FROM `UNIVERSIDADE`.`PROFESSOR`
   INNER JOIN `UNIVERSIDADE`.`DEPARTAMENTO`
59
         ON `PROFESSOR`.`DEPARTAMENTO_FK` = `DEPARTAMENTO`.`ID`;
Como a retenção duplicada é o padrão, não usaremos todos em nossos exemplos.
Para garantir a eliminação de duplicatas nos resultados de nossas consultas de
exemplo, devemos usar distinto sempre que necessário. A cláusula select também
pode conter expressões aritméticas envolvendo os operadores +, -, ∗ e / operando em
constantes ou atributos de tuplas. Por exemplo, a consulta:
SELECT
        `PROFESSOR`.`NOME`,
   `DEPARTAMENTO`.`NOME`,
       (`PROFESSOR`.`SALARIO_MENSAL`),
   (`PROFESSOR`.`SALARIO_MENSAL`)*1.1
FROM `UNIVERSIDADE`.`PROFESSOR`
   INNER JOIN `UNIVERSIDADE`.`DEPARTAMENTO`
        ON `PROFESSOR`.`DEPARTAMENTO_FK` = `DEPARTAMENTO`.`ID`;
Essa consulta retorna uma relação que é o mesmo que a relação de professor, exceto
o atributo salário que é multiplicado por 1.1. Isso mostra o que resultaria se
déssemos um aumento de 10% para cada professor; observe, entretanto, que isso
não resulta em nenhuma alteração na relação professor. A SQL também fornece tipos
de dados especiais, como várias formas do tipo de data, e permite que várias funções
aritméticas operem nesses tipos. A cláusula where nos permite selecionar apenas as
linhas na relação de resultados da cláusula from que satisfaz um predicado
especi�cado. Considere a consulta “encontre o nomes de todos os professores do
departamento de Ciência da Computação que têm salário maior que $ 70.000”. Esta
consulta pode ser escrita em SQL como:
SELECT
        `PROFESSOR`.`NOME`,
   `DEPARTAMENTO`.`NOME`,
        `PROFESSOR`.`SALARIO_MENSAL`
60
FROM`UNIVERSIDADE`.`PROFESSOR`
   INNER JOIN `UNIVERSIDADE`.`DEPARTAMENTO`
  ON `PROFESSOR`.`DEPARTAMENTO_FK` = `DEPARTAMENTO`.`ID`
WHERE `DEPARTAMENTO`.`NOME` = 'Computação'
   AND `PROFESSOR`.`SALARIO_MENSAL` > 7000;
Se a relação de professor é mostrada na Figura 1, então a relação que resulta da
consulta anterior é mostrada na Figura 4. O SQL permite o uso de conectivos lógicos
e/ou não no local onde há cláusula. Os operandos dos conectivos lógicos podem ser
expressões envolvendo os operadores de comparação <, <=,>,> =, = e <>. O SQL nos
permite usar os operadores de comparação para comparar strings e expressões
aritméticas, bem como tipos especiais, como tipos de data. Vamos explorar outras
características dos predicados da cláusula where mais adiante neste estudo.
Consultas sobre Múltiplas Relações
As consultas geralmente precisam acessar a informação de múltiplas relações. Um
exemplo: suponha que queremos responder à consulta “recuperar os nomes de todos
os professores, juntamente com seus nomes de departamento". Olhando para o
esquema da universidade, percebemos que podemos obter o nome do departamento
por meio da relação PROFESSOR. Para responder à consulta, cada tupla na relação
de professor deve ser correspondida com a tupla em relação ao departamento cujo
valor do nome do departamento corresponde ao valor do ID do departamento da
tupla de professor, chamada de DEPARTAMENTO_FK. No SQL, para responder à
consulta anterior, listamos as relações que precisam ser acessadas na cláusula from e
especi�cando a condição correspondente na cláusula where. A consulta anterior
pode ser escrita em SQL, como:
SELECT
        `PROFESSOR`.`NOME`,
   `DEPARTAMENTO`.`NOME`
FROM `UNIVERSIDADE`.`PROFESSOR`, `UNIVERSIDADE`.`DEPARTAMENTO`
WHERE `PROFESSOR`.`DEPARTAMENTO_FK` = `DEPARTAMENTO`.`ID`;
61
Restringindo tuplas com a cláusula Where
Uma cláusula WHERE ausente indica nenhuma condição na seleção da tupla;
portanto, todas as tuplas da relação especi�cada na cláusula FROM quali�cam e são
selecionadas para o resultado da consulta (SILBERSCHATZ et al., 2019). Se mais de
uma relação for especi�cada na cláusula FROM e não houver cláusula WHERE, o
produto cartesiano - todas as combinações de tupla possíveis - dessas relações serão
selecionadas. Por exemplo, a consulta a seguir seleciona todos os professores de
todos os departamentos associando cada professor a um departamento.
SELECT * FROM `UNIVERSIDADE`.`PROFESSOR`,
`UNIVERSIDADE`.`DEPARTAMENTO`
É extremamente importante especi�car todas as condições de seleção e associação
na cláusula WHERE; se qualquer dessas condições for negligenciada, podem resultar
relações incorretas e muito grandes. Se especi�carmos todos os atributos de
PROFESSOR e DEPARTAMENTO, obteremos o produto cartesiano real (exceto para
eliminação duplicada, se houver). Para recuperar todos os valores de atributos das
tuplas selecionadas, não precisamos listar os nomes de atributos explicitamente em
SQL; nós apenas especi�camos um asterisco (*), que signi�ca todos os atributos. Por
exemplo, a consulta anterior recupera todos os valores de atributo de qualquer
PROFESSOR que trabalhe em qualquer DEPARTAMENTO.
Tabelas como conjuntos no SQL
Como mencionamos anteriormente, o SQL geralmente trata uma tabela não como
um conjunto, mas como um multiconjunto; tuplas duplicadas podem aparecer mais
de uma vez em uma tabela e no resultado de uma consulta. O SQL não elimina
automaticamente as tuplas duplicadas nos resultados das consultas, pelos seguintes
motivos:
a eliminação duplicada é uma operação cara. Uma maneira de implementá-la é
classi�car as tuplas primeiro e depois eliminar as duplicatas;
o usuário pode querer ver tuplas duplicadas no resultado de uma consulta;
quando uma função agregada é aplicada a tuplas, na maioria dos casos, não
queremos eliminar duplicatas.
Uma tabela SQL com uma chave é restrita a ser um conjunto, uma vez que o valor da
chave deve ser distinto em cada tupla. Se quisermos eliminar as tuplas duplicadas do
resultado de uma consulta SQL, usamos a palavra-chave DISTINCT na cláusula
62
SELECT, signi�cando que apenas as tuplas distintas devem permanecer no resultado.
Em geral, uma consulta com SELECT DISTINCT elimina duplicatas, enquanto uma
consulta com SELECT ALL não. Especi�car SELECT sem ALL nem DISTINCT - como em
nossos exemplos anteriores - é equivalente a SELECT ALL. Por exemplo, na consulta
que recupera o salário de todos os funcionários, se vários funcionários tiverem o
mesmo salário, esse valor salarial aparecerá tantas vezes no resultado da consulta. Se
estivermos interessados apenas em valores salariais distintos, queremos que cada
valor apareça apenas uma vez, independentemente de quantos funcionários
recebem esse salário, usando a palavra-chave DISTINCT, conseguimos isso. O SQL
incorporou diretamente algumas das operações do conjunto da teoria dos conjuntos
matemáticos, que também fazem parte da álgebra relacional. Existem operações de
união (UNION), diferença de conjunto (EXCEPT) e interseção (INTERSECT). As
relações resultantes dessas operações de conjunto são conjuntos de tuplas; isto é, as
tuplas duplicadas são eliminadas do resultado. Essas operações de conjunto aplicam-
se apenas a relações compatíveis com o sindicato, portanto, devemos nos certi�car
de que as duas relações nas quais aplicamos a operação possuem os mesmos
atributos e que os atributos apareçam na mesma ordem em ambas as relações
(SILBERSCHATZ et al., 2019). O próximo exemplo ilustra o uso de UNION.
SELECT * FROM `UNIVERSIDADE`.`CURSO`
UNION ALL
SELECT * FROM `UNIVERSIDADE`.`DEPARTAMENTO`;
A primeira consulta SELECT recupera os cursos da universidade e o segundo recupera
os departamentos da mesma. Observe que apesar de serem entidades diferentes do
ponto de vista lógico, elas são idênticas do ponto de vista físico, ou sejam possuem as
mesmas colunas com a mesma tipagem. O SQL também possui operações multiset
correspondentes, que são seguidas pela palavra-chave ALL (UNION ALL, EXCEPT ALL,
INTERSECT ALL). Seus resultados são multisets (as duplicações não são eliminadas).
Basicamente, cada tupla - seja ela duplicada ou não - é considerada como uma tupla
diferente ao aplicar essas operações.
63
Correspondência de Padrões de Subconjunto e Operadores Aritméticos
Nesta seção, discutiremos vários outros recursos do SQL. O primeiro recurso
permite condições de comparação em apenas partes de uma cadeia de caracteres,
usando o operador de comparação LIKE. Isso pode ser usado para correspondência
de padrões de sequência de caracteres. Strings parciais são especi�cadas usando dois
caracteres reservados % que substitui um número arbitrário de zero ou mais
caracteres, e o sublinhado (_) substitui um único caractere. Por exemplo, considere a
seguinte consulta:
SELECT * FROM `UNIVERSIDADE`.`CURSO`
WHERE `CURSO`.`NOME` LIKE '%II%';
Ela recupera todos os cursos que tenham a sequência de caracteres “II” em qualquer
posição da coluna NOME. O operador LIKE pode ser aplicado no início, no �nal ou em
ambas partes da coluna. Existe ainda a possibilidade de aplicar em mais de um local,
por exemplo, a consulta a seguir.
SELECT * FROM `UNIVERSIDADE`.`CURSO`
WHERE `CURSO`.`NOME` LIKE '%Banco%II%';
Figura 3 - Tabelas em bancos de dados 
Fonte: Semisatch / 123RF.
64
Para recuperar todos os professores que tenham a sequência “ria” iniciando na
segunda posição da coluna NOME, podemos utilizar uma variação do operador LIKE,
como na consulta a seguir.
SELECT `ALUNO`.`ID`,
  `ALUNO`.`NOME`,
  `ALUNO`.`DATA_NASCIMENTO`,
  `ALUNO`.`CURSO_FK`
FROM `UNIVERSIDADE`.`ALUNO`
WHERE `ALUNO`.`NOME` LIKE '__ria';
SAIBA MAIS
A cláusula LIKE geralmente consome muito processamento e precisa de um
índice adequado para ela.
Saiba mais, acessando: http://bit.ly/2xGRSfc.
65
http://bit.ly/2xGRSfc
Se um sublinhado ou % for necessário como um caractere literal na string, o caractere
deve ser precedido por um caractere de escape, que é especi�cado após a string,
usando a palavra-chave ESCAPE. Porexemplo, "AB \ _CD \% EF" ESCAPE "\"
representa a string literal "AB_CD% EF" porque \ é especi�cado como o caractere de
escape. Qualquer caractere não usado na string pode ser escolhido como o caractere
de escape. Além disso, precisamos de uma regra para especi�car apóstrofos ou aspas
simples (‘’), se eles forem incluídos em uma sequência, porque eles são usados para
iniciar e �nalizar cadeias de caracteres. Se um apóstrofo (') for necessário, ele será
representado como dois apóstrofos consecutivos (”) para que não seja interpretado
como �nalizando a sequência. Observe que a comparação de substring implica que os
valores de atributo não são valores atômicos (indivisíveis), conforme assumimos no
modelo relacional formal.
Outro recurso que permite o uso de aritmética em consultas são os operadores
aritméticos padrão para adição (+), subtração (-), multiplicação (*) e divisão (/) que
podem ser aplicados a valores numéricos ou atributos com domínios numéricos. Por
exemplo, suponha que queremos ver o efeito de dar a todos os professores um
aumento de 10%. Podemos emitir a consulta a seguir para ver quais seriam seus
salários. Este exemplo também mostra como podemos renomear um atributo no
resultado da consulta usando AS na cláusula SELECT.
Ordenação dos resultados da consulta
O SQL permite que o usuário ordene as tuplas no resultado de uma consulta pelos
valores de um ou mais dos atributos que aparecem no resultado da consulta, usando
a cláusula ORDER BY. Isso é ilustrado pela consulta a seguir. A ordem padrão está em
ordem crescente de valores. Podemos especi�car a palavra-chave DESC se
quisermos ver o resultado em uma ordem descendente de valores. A palavra-chave
ASC pode ser usada para especi�car a ordem ascendente explicitamente.
SELECT `PROFESSOR`.`ID`,
  `PROFESSOR`.`NOME`,
  `PROFESSOR`.`SALARIO_MENSAL`,
  `PROFESSOR`.`DEPARTAMENTO_FK`,
  `PROFESSOR`.`ENDERECO_FK`
FROM `UNIVERSIDADE`.`PROFESSOR`
66
ORDER BY `PROFESSOR`.`NOME`;
Podemos utilizar outras colunas na ordenação. Isso pode gerar resultados
interessantes e ser muito útil na prática. Apesar de ser um recurso útil, deve-se tomar
cuidado ao utilizá-lo, pois exige um processamento maior para ordenação.
Figura 4 - Ordenação de dados em SQL 
Fonte: Helder Almeida / 123RF.
67
QUESTÃO OBJETIVA
Há um número de linguagens de consulta de banco de
dados em uso, seja comercialmente ou
experimentalmente. Embora nos referimos à linguagem
SQL como uma “linguagem de consulta”, ela pode fazer
muito mais do que apenas consultar um banco de dados.
Neste caso, qual o signi�cado do acrônimo SQL?
Structured Question Language.
Structured Query Language.
Structured Quotation Language.
Structured Query Loop.
Structured Query Localization.
68
QUESTÃO OBJETIVA
O SQL permite que o usuário ordene as tuplas no
resultado de uma consulta pelos valores de um ou mais dos
atributos que aparecem no resultado da consulta. A ordem
padrão está em ordem crescente de valores. Podemos
especi�car a palavra-chave DESC se quisermos ver o
resultado em uma ordem descendente de valores. Neste
caso, qual o nome da cláusula que realiza a ordenação de
uma consulta?
ORDER BY.
DESC BY.
CLASSIFY BY.
ORDER TO.
ORDER FROM.
69
Fechamento
Uma das grandes questões relacionadas aos bancos de dados é a forma como esse
recupera os dados. Recuperação de dados é uma parte fundamental de um bom
projeto de banco de dados. Dessa forma, vimos que utilizar as boas técnicas de
consulta pode ser um grande diferencial no momento do projeto e da operação do
banco de dados.
Nesta aula, você teve a oportunidade de:
entender a visão geral da linguagem SQL Query;
aprender sobre a estrutura básica de consultas SQL;
aprender sobre as consultas em uma única relação;
entender as consultas sobre Múltiplas Relações.
70
Otimização de Consulta de Dados
O entendimento correto de como as consultas são processadas pelos bancos de
dados pode auxiliar no projeto de bancos de dados. Além disso, pode auxiliar na
resolução de problemas com um diagnóstico correto e, consequentemente, uma
solução correta.
Ao �nal desta aula, você será capaz de:
entender os elementos do processamento de consultas;
aprender como são as medidas do custo da consulta;
entender como funciona a otimização de consultas.
Aula 04
Introdução
71
Processamento de Consultas
O processamento de consulta refere-se ao conjunto de atividades envolvidas na
extração de dados em um banco de dados. As atividades incluem tradução de
consultas em banco de dados de uma linguagem de alto nível em expressões que
podem ser usadas no nível físico do sistema de arquivos, uma variedade de
transformações de otimização de consulta e avaliação real de consultas
(SILBERSCHATZ et al., 2019).
Visão Global
As etapas envolvidas no processamento de uma consulta aparecem na Figura 1. Os
passos básicos são:
1. Análise e tradução
2. Otimização
3. Avaliação
Antes que o processamento da consulta possa começar, o sistema deve traduzir a
consulta em uma forma utilizável. Uma linguagem como SQL é adequada para uso
humano, mas é inadequada para a representação interna do sistema de uma consulta.
Uma representação interna mais útil é aquela baseada na álgebra relacional
estendida. Assim, a primeira ação que o sistema deve executar no processamento de
consultas é traduzir uma determinada consulta em sua forma interna. Este processo
de tradução é semelhante ao trabalho executado pelo analisador de um compilador.
Ao gerar a forma interna da consulta, o analisador veri�ca a sintaxe da consulta do
usuário, veri�ca se a relação que aparece na consulta são nomes das relações no
banco de dados. O sistema constrói uma representação em árvore da consulta, que é
então traduzida em uma expressão de álgebra relacional (SILBERSCHATZ et al.,
2019).
72
SAIBA MAIS
O modelo relacional estendido pode ser utilizado para melhorar o modelo de
banco de dados. Além disso, esse modelo é implementado por ferramentas de
modelagem.
Saiba mais, acessando: http://bit.ly/2NRtlPu.
73
http://bit.ly/2NRtlPu
Dada uma consulta, há geralmente uma variedade de métodos para calcular o
responda. Por exemplo, vimos que, em SQL, uma consulta poderia ser expressa em
várias maneiras diferentes. Cada consulta SQL pode ser convertida em uma relação
expressão de álgebra de várias maneiras. Além disso, a álgebra relacional representa
uma consulta especí�ca apenas parcialmente como avaliar uma consulta; há
geralmente várias maneiras de avaliar as expressões de álgebra relacional. Como
uma ilustração, considere a consulta:
SELECT
`PROFESSOR`.`NOME`,
`PROFESSOR`.`SALARIO_MENSAL`
FROM `UNIVERSIDADE`.`PROFESSOR`
WHERE `PROFESSOR`.`SALARIO_MENSAL` > 7000;
Essa consulta pode ser traduzida em qualquer um dos seguintes exemplos de álgebra
relacional:
salário <7000 (salário (professor))
salário (salário <7000 (professor))
Além disso, podemos executar cada operação de álgebra relacional por um dos vários
algoritmos diferentes. Por exemplo, para implementar a seleção anterior, podemos
pesquisar cada tupla no professor para encontrar as tuplas com salário inferior a
7000. O índice B+ está disponível no atributo salário, podemos usar o índice para
localizar as tuplas. Para especi�car completamente como avaliar uma consulta,
precisamos não apenas fornecer expressão de álgebra relacional, mas também anotá-
lo com instruções especi�cando como avaliar cada operação. As anotações podem
indicar o algoritmo a ser usado para uma operação especí�ca ou o índice ou índices
especí�cos a serem usados. Uma operação de álgebra anotada com instruções sobre
como avaliá-lo é chamado de avaliação primitiva. Uma sequência de operações
Figura 1 - Como as consultas são analisadas, otimizadas e avaliadas nos sistemas
gerenciadores de bancos de dados
Fonte: Adaptada de Silberschatz et al. (2019).
74
primitivas que podem ser usadas para avaliar uma consulta é um plano de execução
de consulta ou plano de avaliação de consulta.
A Figura 2 ilustra um plano de execução para nossaconsulta exemplo, na qual um
índice especí�co (indicado na �gura como “índice 1”) é especi�cado para a operação
de seleção. O mecanismo de execução de consultas usa um plano de avaliação de
consultas, executa esse plano e retorna as respostas para a consulta. Os diferentes
planos de avaliação para uma determinada consulta podem ter custos diferentes.
Não espere que os usuários escrevam suas consultas de uma maneira que sugira os
mais e�cientes planos de avaliação. Em vez disso, é responsabilidade do sistema
construir uma consulta plano de avaliação que minimiza o custo da avaliação da
consulta; esta tarefa é chamada otimização de consulta. Depois que o plano de
consulta é escolhido, a consulta é avaliada com esse plano e o resultado da consulta é
a saída (SILBERSCHATZ et al., 2019).
A sequência de etapas já descrita para o processamento de uma consulta é
representativa; nem todos os bancos de dados seguem exatamente essas etapas. Por
exemplo, em vez de usar a representação da álgebra relacional, vários bancos de
dados usam uma análise anotada representação em árvore baseada na estrutura da
consulta SQL fornecida. No entanto, os conceitos que descrevemos aqui formam a
base do processamento de consultas em bancos de dados. Para otimizar uma
Figura 2 - Exemplo de plano de execução da consulta anterior 
Fonte: Adaptada de Silberschatz et al. (2019).
75
consulta, um otimizador de consulta deve saber o custo de cada Operação. Embora o
custo exato seja difícil de calcular, pois depende de muitos parâmetros como a
memória real disponível para a operação, é possível obter uma estimativa
aproximada do custo de execução para cada operação (ELMASRI; NAVATHE, 2016).
Figura 3 - Um exemplo de plano de execução gerado pela ferramenta MySQl
Workbench 
Fonte: Elaborada pelo autor.
76
Medidas do custo da consulta
Existem vários planos de execução possíveis para uma consulta, e é importante ser
capaz de comparar as alternativas em termos de custo (estimado) e escolher o
melhor plano. Para fazer isso, devemos estimar o custo de operações individuais, e
combine-os para obter o custo de um plano de execução da consulta. O custo da
avaliação de consultas pode ser medido em termos de um número de recursos
adicionais, incluindo acessos ao disco, tempo de CPU para executar uma consulta e,
em um sistema de banco de dados distribuído ou paralelo, o custo da comunicação.
Em grandes sistemas de banco de dados, o custo para acessar dados do disco é
geralmente o mais custo importante, já que os acessos ao disco são lentos
comparados às operações na memória. Além disso, as velocidades da CPU
melhoraram muito mais rapidamente do que as velocidades do disco. Assim, é
provável que o tempo gasto na atividade do disco continue a dominar o tempo total
para executar uma consulta. O tempo de CPU usado para uma tarefa é mais difícil
SAIBA MAIS
O recurso Visual Explain gera e exibe uma representação visual da instrução
MySQL EXPLAIN usando informações estendidas. Isso é muito útil para melhorar
o resultado das consultas que criamos.
Saiba mais, acessando: http://bit.ly/2GnoxLF.
77
http://bit.ly/2GnoxLF
estimativa, uma vez que depende de detalhes de baixo nível do código de execução.
Apesar dos otimizadores de consultas reais levarem em consideração os custos de
CPU, para simpli�car no livro nós ignoramos os custos de CPU e usamos apenas os
custos de acesso ao disco para medir o custo de um plano de avaliação de consulta.
Usamos o número de transferências de blocos do disco e o número de tentativas de
disco para estimar o custo de um plano de avaliação de consulta. Se o subsistema de
disco levar média de t T segundos para transferir um bloco de dados e tem um acesso
de bloco médio tempo (tempo de busca do disco mais latência rotacional) de t S
segundos, então uma operação que transfere b blocos e executa S buscas levaria b ∗ t
T + S ∗ t S segundos. Os valores de t T e t S devem ser calibrados para o sistema de
disco usado, mas os valores típicos para discos high-end hoje seria t S = 4
milissegundos e t T = 0,1 milissegundos, assumindo um tamanho de bloco de 4
kilobytes e uma taxa de transferência de 40 megabytes por segundo
(SILBERSCHATZ et al., 2019).
Podemos re�nar ainda mais nossas estimativas de custo, distinguindo leituras de
blocos escreve, desde gravações de bloco são tipicamente cerca de duas vezes mais
caras do que lê (isso ocorre porque os sistemas de disco leem os setores de volta
ATENÇÃO
Entender os detalhes do plano de execução de uma consulta pode fazer muita
diferença em sistemas com uma alta demanda de usuários.
78
depois de serem gravados para veri�car se a gravação foi bem-sucedida). Para
simpli�car, ignoramos esse detalhe e deixamos para você calcular as estimativas de
custo mais precisas para várias operações. As estimativas de custo que fornecemos
não incluem o custo de escrever o resultado �nal de uma operação de volta ao disco.
Estes são levados em consideração separadamente, quando necessário. Os custos de
todos os algoritmos que consideramos dependem do tamanho do buffer na memória
principal. No melhor dos casos, todos os dados podem ser lidos nos buffers e o disco
não precisa ser acessado novamente. No pior dos casos, assumimos que o buffer pode
conter apenas alguns blocos de dados - aproximadamente um bloco por relação. Ao
apresentar estimativas de custos, geralmente assumimos o pior caso
(SILBERSCHATZ et al., 2019).
Além disso, embora assumamos que os dados devem ser lidos a partir do disco
inicialmente, é possível que um bloco acessado já esteja presente no buffer de
memória. Novamente, por simplicidade, ignoramos esse efeito; como resultado, o
custo real de acesso ao disco durante a execução de um plano pode ser inferior ao
custo estimado. O tempo de resposta para um plano de avaliação de consulta (ou
seja, a hora do relógio na parede necessário para executar o plano), supondo que
nenhuma outra atividade esteja ocorrendo no computador, seria responsável por
todos esses custos e poderia ser usado como medida do custo do plano. Infelizmente,
o tempo de resposta de um plano é muito difícil a estimativa sem realmente executar
o plano, pelas seguintes razões:
1. o tempo de resposta depende do conteúdo do buffer quando a consulta começa a
execução; esta informação não está disponível quando a consulta é difícil de explicar,
mesmo se estivesse disponível;
2. em um sistema com vários discos, o tempo de resposta depende de como os acessos são
distribuídos entre discos, o que é difícil de estimar sem detalhes conhecimento de
layout de dados no disco.
Curiosamente, um plano pode obter um melhor tempo de resposta ao custo de
recurso extra consumo. Por exemplo, se um sistema tiver vários discos, um plano A
que requer extra disco lê, mas realiza as leituras em paralelo em vários discos pode
terminar mais rápido do que outro plano B que tem menos leituras de disco, mas de
apenas um disco. No entanto, se muitas instâncias de uma consulta usando o plano A
forem executadas simultaneamente, o tempo de resposta total pode, na verdade, ser
maior do que se as mesmas instâncias forem executadas usando o plano B, já que o
plano A gera mais carga nos discos. Como resultado, em vez de tentar minimizar o
tempo de resposta, os otimizadores tentam minimizar o consumo total de recursos
de um plano de consulta. Nosso modelo é estimar o tempo total de acesso ao disco
79
(incluindo busca e transferência de dados) como um exemplo de tal modelo de custo
de consulta baseado no consumo de recursos (SILBERSCHATZ et al., 2019).
Operação de Seleção
No processamento de consultas, a veri�cação de arquivos é o operador de nível mais
baixo para acessar dados. Scans de arquivos são algoritmos de busca que localizam e
recuperam registros que preenchem uma seleção condição. Em sistemas relacionais,
uma varredura de arquivos permite que uma relação inteira seja lida os casos em que
a relação é armazenada em um único arquivo dedicado.
Seleções usando Varreduras e Índices de Arquivo
SAIBA MAIS
O editor de estatísticasdo Query Stats usa dados do esquema de desempenho
para reunir as principais estatísticas coletadas para consulta executada, como
temporização, tabelas temporárias, índices, junções e mais.
Saiba mais, acessando: http://bit.ly/2NRFKmu.
80
http://bit.ly/2NRFKmu
Considere uma operação de seleção em uma relação cujas tuplas são armazenadas
juntas em um arquivo. A maneira mais direta de executar uma seleção é a seguinte:
A1 (pesquisa linear). Em uma pesquisa linear, o sistema veri�ca cada bloco de
arquivo e testa todos os registros para ver se eles satisfazem a condição de
seleção. Uma inicial seek é necessário para acessar o primeiro bloco do arquivo.
Caso os blocos do arquivo não sejam armazenados de forma contígua, buscas
extras podem ser necessárias, mas ignoramos isso como efeito para a
simplicidade.
Embora possa ser mais lento que outros algoritmos para implementar o seu
algoritmo de busca linear pode ser aplicado a qualquer arquivo, independentemente
da ordenação do arquivo, ou a disponibilidade de índices, ou a natureza da seleção
Operação. Os outros algoritmos que vamos estudar não são aplicáveis em todos
casos, mas quando aplicável, eles geralmente são mais rápidos que a pesquisa linear.
Os otimizadores da vida real geralmente assumem que a raiz da árvore está presente
no buffer na memória, uma vez que é acessado com frequência. Alguns otimizadores
até assumem que todos, exceto o nível da folha da árvore, estão presentes na
memória, pois são acessados com relativa frequência, e geralmente menos de 1% dos
nós de uma árvore B+ são nós sem folha. As fórmulas de custo podem ser
modi�cadas apropriadamente. Estruturas de índice são chamadas de caminhos de
acesso, pois fornecem um caminho por meio dos quais os dados podem ser
localizados e acessados. Lembre-se de que um índice primário (também chamado de
índice de clustering) é um índice que permite que os registros de um arquivo sejam
lidos em uma ordem que corresponda para a ordem física no arquivo. Um índice que
não é um índice primário é chamado de índice secundário. Os algoritmos de pesquisa
que usam um índice são chamados de varreduras de índice (VANIER et al., 2019). Nós
usamos o predicado de seleção para nos guiar na escolha do índice a ser usado no
processamento da consulta. Algoritmos de pesquisa que usam um índice são
(SILBERSCHATZ et al., 2019):
A2 (índice primário, igualdade na chave). Para uma comparação de  igualdade
em uma chave atributo com um índice primário, podemos usar o índice para
recuperar um único registro que satisfaz a condição de igualdade
correspondente;
A3 (índice primário, igualdade em não chave). Nós podemos recuperar vários
registros usando um índice primário quando a condição de seleção especí�ca
uma igualdade comparação em um atributo não chave, A. A única diferença em
81
relação ao anterior, nesse caso, vários registros podem precisar ser buscados.
No entanto, os registros devem ser armazenados consecutivamente no arquivo,
uma vez que ele é classi�cado na pesquisa chave;
A4 (índice secundário, igualdade). Seleções especi�cando uma condição de
igualdade pode usar um índice secundário. Esta estratégia pode recuperar um
único registro se a condição de igualdade está em uma chave; registros
múltiplos podem ser recuperados se o índice campo não é uma chave.
No primeiro caso, apenas um registro é recuperado. O custo do tempo, neste caso, é
o mesmo que para um índice primário. No segundo caso, cada registro pode ser
residente em um bloco diferente, pode resultar em uma operação de E/S por registro
recuperado, com cada operação de E/S requerendo uma busca e uma transferência
em bloco. O pior custo do tempo neste caso é (h i + n) ∗ (t S + t T), onde n é o número
de registros buscados, se cada registro está em um bloco de disco diferente e as
buscas de bloco são ordenadas aleatoriamente. O No pior dos casos, o custo poderia
tornar-se ainda pior do que o da pesquisa linear se um grande número de registros é
recuperado.
Se o buffer de memória é grande, o bloco que contém o registro pode já estar no
buffer. É possível construir uma estimativa da média custo esperado da seleção
levando em consideração a probabilidade de o bloco contendo o registro já estando
no buffer. Para buffers grandes, a estimativa será muito menor do que a estimativa do
pior caso. Em certos algoritmos, incluindo A2, o uso de uma organização de arquivos
B+ salve um acesso, pois os registros são armazenados no nível de folha da árvore.
Quando os registros são armazenados em uma organização de arquivos B+ ou outras
organizações de arquivos que possam exigir a realocação de registros, os índices
geralmente não armazenam ponteiros nos registros. Em vez disso, os índices
secundários armazenam os valores dos atributos usados como chave de busca em
uma organização de arquivos. Acessar um registro por meio de um índice secundário
é então mais caro: primeiro, o índice secundário é pesquisado para encontrar o valor
da chave de pesquisa do índice primário. Em seguida, o índice principal é procurado
para localizar os registros. As fórmulas de custo descritos para os índices secundários
têm que ser modi�cados apropriadamente se tais índices são usados (ELMASRI;
NAVATHE, 2016).
82
Otimização de Consultas
A otimização de consulta é o processo de selecionar a avaliação de consulta mais
e�ciente para planejar entre as muitas estratégias geralmente possíveis para o
processamento de uma dada consulta, especialmente se a consulta é complexa. Não
esperamos que os usuários escrevam suas consultas para que possam ser
processadas de forma e�ciente. Pelo contrário, esperamos que o sistema construa
um plano de avaliação de consulta que minimize o custo da avaliação da consulta. É
aqui que a otimização de consultas entra em ação. Um aspecto da otimização ocorre
no nível da álgebra relacional, onde o sistema tenta encontrar uma expressão que
seja equivalente à expressão dada, mas mais e�ciente de executar. Outro aspecto é
selecionar uma estratégia detalhada para o processamento da consulta, como a
escolha do algoritmo a ser usado para executar uma operação, escolhendo os índices
especí�cos para usar, e assim por diante. A diferença de custo (em termos de tempo
de avaliação) entre uma boa estratégia e uma estratégia ruim é muitas vezes
substancial e pode ter várias ordens de grandeza. Portanto, vale a pena que o sistema
gaste uma quantidade substancial de tempo na seleção de uma boa estratégia para
processar uma consulta, mesmo que a consulta seja executada apenas uma vez
(SILBERSCHATZ et al., 2019).
Figura 4 - Um exemplo de estatísticas gerado pela ferramenta MySQl Workbench
Fonte: Elaborada pelo autor.
83
Visão global
Considere a seguinte expressão de álgebra relacional, para a consulta “Procure os
nomes de todos os professores do departamento de Música, juntamente com o título
do curso de todos os cursos que os professores ensinam”: nome, título (nome do
departamento = “Música” (professor 1 (ensina 1 ID do curso, título (curso)))). Observe
que a projeção de curso em (id do curso, título) é necessária, pois as ações do curso
um nome de departamento de atributo com professor; se não removermos este
atributo usando o projeção, a expressão anterior usando junções naturais retornaria
apenas cursos do departamento de Música, mesmo que alguns professores do
departamento de Música cursos em outros departamentos. A expressão anterior
constrói uma relação intermediária grande, professor 1 ensina 1 id do curso, título
(curso). No entanto, estamos interessados apenas em algumas poucas tuplas dessa
relação (aquelas pertencentes a professores no departamento de Música) e apenas
dois dos dez atributos dessa relação. Desde que estamos preocupados apenas com
aquelas tuplas na relação de professor que pertencem ao departamento de Música,
nós não precisamos considerar aquelas tuplas que não possuem nome de dept =
“Música”. Por reduzir o número de tuplas da relação professor que precisamos
acessar, nós reduzimos o tamanho do resultado intermediário (SILBERSCHATZet al.,
2019). Nossa consulta é agora representada pela expressão de álgebra relacional:
nome, título ((𝞿 nome do departamento = “Música” (professor)) 1 (ensina 1 ID do
curso, título (curso)))
Que é equivalente à nossa expressão de álgebra original, mas que gera relações
intermediárias menores.
nome, título ((𝞿 nome do departamento = “Música” (professor)) 1 (ensina 1 ID do
curso, título (curso)))
Que é equivalente à nossa expressão de álgebra original, mas que gera relações
intermediárias menores. A Figura 2 mostra a inicial e transformada expressões. Um
plano de avaliação de�ne exatamente qual algoritmo deve ser usado para cada
operação e como a execução das operações deve ser coordenada. Como vimos, vários
algoritmos diferentes podem ser usados para cada operação relacional, dando
origem a planos de avaliação alternativos. Na �gura, hash junção foi escolhida para
uma das operações de junção, enquanto a outra usa mesclagem junte-se, após
classi�car as relações no atributo de junção, que é ID. Onde bordas são marcadas
como pipelined, a saída do produtor é canalizada diretamente para o consumidor, sem
ser gravado em disco. Dada uma expressão de álgebra relacional, é o trabalho do
otimizador de consulta chegar a um plano de avaliação de consulta que calcula o
84
mesmo resultado que o dado expressão, e é a maneira menos onerosa de gerar o
resultado (ou, pelo menos, não é muito mais dispendioso do que a forma menos
onerosa).
Para encontrar o plano de avaliação de consulta mais barato, o otimizador precisa
gerar planos alternativos que produzem o mesmo resultado que a expressão dada, e
escolha o menos dispendioso. A geração de planos de avaliação de consulta envolve
três passos: (1) gerar expressões que são logicamente equivalentes à expressão dada,
(2) anotar as expressões resultantes de formas alternativas para gerar planos
alternativos de avaliação de consultas; e (3) estimar o custo de cada avaliação
planejar e escolher aquele cujo custo estimado é o menor (SILBERSCHATZ et al.,
2019).
As etapas (1), (2) e (3) são intercaladas no otimizador de consulta – algumas são
geradas e anotadas para gerar planos de avaliação, depois expressões são geradas e
anotadas, e assim por diante. Como os planos de avaliação são gerados, seus custos
são estimados usando informações estatísticas sobre as relações, tais como
tamanhos de relação e profundidades de índice. Para implementar a primeira etapa, o
otimizador de consulta deve gerar expressões equivalentes a uma determinada
expressão.
Transformação de expressões relacionais
Uma consulta pode ser expressa de várias maneiras diferentes, com diferentes
custos de execução. Nesta seção, ao invés de tomar a expressão relacional como
dada, nós consideramos expressões equivalentes e alternativas. Duas expressões de
álgebra relacional são consideradas equivalentes se, em todas as instâncias do banco
de dados, as duas expressões geram o mesmo conjunto de tuplas. (Lembre-se que
uma instância de banco de dados legal é aquela que satisfaz todas as restrições de
integridade especi�cadas no esquema do banco de dados). Observe que a ordem das
tuplas é irrelevante; as duas expressões podem gerar as tuplas em ordens diferentes,
mas seriam consideradas equivalentes, desde que o conjunto de tuplas seja o mesmo.
No SQL, as entradas e saídas são multisets de tuplas e a versão multiset da álgebra
relacional é usada para avaliar Consultas SQL. Duas expressões na versão
multiconjunto da álgebra relacional são equivalentes se em cada banco de dados
legal as duas expressões geram o mesmo multiset de tuplas (SILBERSCHATZ et al.,
2019).
85
Tópicos Avançados em Otimização de Consultas
Há uma série de oportunidades para otimizar as consultas, além das que tenho visto
até agora. Vamos examinar algumas a seguir.
Otimização Top-K
Muitas consultas buscam resultados classi�cados em alguns atributos e exigem
apenas o K superior resultados para alguns K. Às vezes, o K ligado é especi�cado
explicitamente. Por exemplo, alguns bancos de dados suportam uma cláusula K de
limite que resulta apenas nos resultados de K superiores sendo retornado pela
consulta. Outras bases de dados suportam formas alternativas competindo com
limites semelhantes. Em outros casos, a consulta pode não especi�car tal limite, mas
o otimizador pode permitir que uma sugestão seja especi�cada, indicando que
apenas os resultados do K superior da consulta provavelmente serão recuperados,
SAIBA MAIS
Visualize as estatísticas de desempenho do servidor em um painel grá�co. Para
exibir o painel no MySQL Workbench, abra uma guia de consulta e clique em
Painel na área Desempenho da barra lateral do Navegador com a guia
Gerenciamento selecionada.
Saiba mais, acessando: http://bit.ly/2Y0x80E.
86
http://bit.ly/2Y0x80E
mesmo que a consulta gere mais resultados. Quando K é pequeno, um plano de
otimização de consulta que gera o conjunto inteiro de resultados, em seguida,
classi�ca e gera o top-K, é muito ine�ciente, uma vez que se desfaz a maioria dos
resultados intermediários que ele calcula. Várias técnicas foram propostas para
otimizar tais consultas top-K. Uma abordagem é usar planos com pipeline que pode
gerar os resultados na ordem de classi�cação. Outra abordagem é estimar o que é o
valor mais alto nos atributos classi�cados que aparecerão na saída top-K, e introduzir
predicados de seleção que eliminem valores maiores. Se tuplas extras além do topo-K
são gerados, são descartados, e se muito poucas tuplas são geradas, então a condição
de seleção é alterada e a consulta é executada novamente (SILBERSCHATZ et al.,
2019).
Otimizando as Junções
Quando as consultas são geradas por meio de visualizações, às vezes mais relações
são unidas do que o necessário para o cálculo da consulta. Por exemplo, uma visão v
pode incluir a junção de professor e departamento, mas um uso da visão v pode usar
somente atributos do professor. O atributo de junção nome do professor é um nome
estrangeiro departamento de referência-chave. Assumindo que o nome foi declarado
como não null, a junção com o departamento pode ser descartada, sem impacto na
consulta. Para, sob a suposição anterior, a junção com o departamento não elimina as
tuplas do professor, nem resulta em cópias extras de qualquer tupla de professor.
Eliminar uma relação de uma junção como a anterior é um exemplo de minimização
de junção. Na verdade, a minimização de junção pode ser executada em outras
situações também (ELMASRI; NAVATHE, 2016).
Otimização de Atualizações
As consultas de atualização geralmente envolvem subconsultas no conjunto e
cláusulas where, que também deve ser levado em consideração na otimização da
atualização. Atualizações que envolvem uma seleção na coluna atualizada (por
exemplo, dê um aumento salarial de 10% a todos professores cujo salário é ≥ $
100.000) devem ser manuseados com cuidado. Se a atualização é feita enquanto a
seleção está sendo avaliada por uma varredura de índice, uma tupla atualizada pode
ser reinserida no índice antes da varredura ser visto novamente pela varredura; a
mesma tupla de professor pode ser atualizada incorretamente várias vezes, in�nito
número de vezes, neste caso). Um problema semelhante também surge com
atualizações envolvendo subconsultas cujo resultado é afetado pela atualização. O
problema de uma atualização que afeta a execução de uma consulta associada a
atualização é conhecida como o problema de Halloween (assim chamado porque foi
reconhecido pela primeira vez em um dia de Halloween, na IBM). O problema pode
ser evitado executando-se as consultas e de�nindo a atualização primeiro, criando
87
uma lista de tuplas afetadas e atualizando as tuplas e índices como último passo. No
entanto, dividindo o plano de execução de tal maneira aumenta o custo de execução.
Os planos de atualização podem ser otimizados ao veri�car se o problema de
Halloween pode ocorrer, e se isso não ocorrer, as atualizações podem ser executadas
enquanto a consulta está sendo processada, reduzindo as despesas gerais de
atualização (VANIERet al., 2019).
Por exemplo, o problema de Halloween não pode ocorrer se a atualização não afetar
os atributos de índice. Mesmo que isso aconteça, se as atualizações diminuírem o
valor, enquanto o índice é digitalizado em ordem crescente, as tuplas atualizadas não
serão encontradas novamente durante a digitalização. Nesses casos, o índice pode
ser atualizado mesmo quando a consulta é executada, reduzindo o custo total.
Atualizar consultas que resultam em um grande número de atualizações também
podem ser otimizadas coletando as atualizações como um lote e, em seguida,
aplicando o lote de atualizações separadamente para cada índice afetado. Ao aplicar
o lote de atualizações a um índice, o lote é primeiro classi�cado na ordem de índice
para esse índice; essa classi�cação pode reduzir signi�cativamente a quantidade de
E/S aleatória necessária para atualizar os índices. Essas otimizações de atualizações
são implementadas na maioria dos sistemas de banco de dados (SILBERSCHATZ et
al., 2019).
Otimização multitarefa e varreduras compartilhadas
Quando um lote de consultas é enviado junto, um otimizador de consulta pode
explorar comumente subexpressões comuns entre as diferentes consultas, avaliando
uma vez e reutilizá-los, quando necessário. Consultas complexas podem, de fato, ter
subexpressões repetidas em diferentes partes da consulta, que podem ser
similarmente explorada, para reduzir o custo de avaliação da consulta. Essa
otimização é conhecida como otimização de consulta. A eliminação comum de
subexpressão otimiza as subexpressões compartilhadas por diferentes expressões
em um programa, computando e armazenando o resultado, e reutilizando-o onde
quer que a subexpressão ocorra. A eliminação de subexpressão comum é uma
otimização padrão aplicada em expressões aritméticas por programação de
compiladores de linguagem. Explorando subexpressões comuns entre planos de
avaliação escolhida para cada lote de consultas é tão útil quanto na consulta de banco
de dados e é implementado por alguns bancos de dados. No entanto, a otimização
multiconsulta pode melhorar ainda mais em alguns casos: uma consulta geralmente
tem mais de uma avaliação, plano de avaliação e um conjunto criteriosamente
escolhido de planos de avaliação de consulta para as consultas que podem
proporcionar uma maior partilha e menor custo do que aquele proporcionado pelo
plano de avaliação de menor custo para cada consulta. Mais detalhes sobre
88
multiquery otimização pode ser encontrada nas referências citadas nas notas
bibliográ�cas. O compartilhamento de varreduras de relação entre consultas é outra
forma limitada de otimização de multiquery que é implementada em alguns bancos
de dados. A varredura compartilhada otimização funciona da seguinte maneira: em
vez de ler a relação repetidamente no disco, uma vez para cada consulta que precisa
varrer uma relação, os dados são lidos no disco e na pipeline para cada uma das
consultas (SILBERSCHATZ et al., 2019). A otimização de varredura compartilhada é
particularmente útil quando várias consultas executam uma varredura em uma única
relação grande (tipicamente uma “tabela de fatos”).
Otimização de consulta paramétrica
Planejar o armazenamento em cache é usado como uma heurística em muitos bancos
de dados. Lembre-se de que, com o cache de planos, se uma consulta for chamada
com algumas constantes, o plano escolhido pelo otimizador é armazenado em cache
e reutilizado se a consulta é submetida novamente, mesmo se as constantes na
consulta forem diferentes. Por exemplo, suponha que uma consulta tenha um nome
de departamento como um parâmetro e recupere todos os cursos do departamento.
Com o planejamento de cache, um plano escolhido quando a consulta é executada
pela primeira vez, digamos, para o departamento de Música, é reutilizado se a
consulta for executada para qualquer outro departamento. Essa reutilização de
planos pelo cache de planos é razoável se o plano de consulta ideal for não
signi�cativamente afetado pelo valor exato das constantes na consulta. Contudo, se
o plano for afetado pelo valor das constantes, a otimização de consultas paramétricas
é uma alternativa (SILBERSCHATZ et al., 2019).
Na otimização de consultas paramétricas, uma consulta é otimizada sem valores
especí�cos para seus parâmetros, por exemplo, o nome do departamento no
exemplo. O otimizador então gera vários planos, cada um ideal para um diferente
valor do parâmetro. Um plano seria enviado pelo otimizador somente se for
otimizado por algum valor possível dos parâmetros. O conjunto de planos
alternativos produzidos pelo otimizador é armazenado. Quando uma consulta é
enviada com valores especí�cos para seus parâmetros, em vez de executar uma
otimização completa, o plano mais barato do conjunto de planos alternativos
calculados anteriormente é usado. Encontrar o plano mais barato geralmente leva
muito menos tempo que a reotimização (SILBERSCHATZ et al., 2019).
89
SAIBA MAIS
Os relatórios baseados no esquema de desempenho fornecem
informações sobre as operações do servidor MySQL mediante
os relatórios úteis de alto nível. O MySQL Workbench usa as
visões SYS no Esquema de Desempenho para gerar mais de 20
relatórios para ajudar a analisar o desempenho de seus bancos
de dados MySQL. Os relatórios ajudam a analisar pontos de
acesso IO, descobrir instruções SQL de alto custo e analisar
estatísticas de espera e métricas do mecanismo InnoDB. Para
obter informações adicionais sobre o esquema SYS, consulte o
MySQL sys Schema.
Saiba mais, acessando: http://bit.ly/2G92o3k.
90
http://bit.ly/2G92o3k
QUESTÃO OBJETIVA
Existe um tipo de pesquisa em que o sistema veri�ca cada
bloco de arquivo e testa todos os registros para ver se eles
satisfazem a condição de seleção. Neste caso, qual é o
nome dessa pesquisa?
Pesquisa sequencial.
Pesquisa exponencial.
Pesquisa operacional.
Pesquisa linear.
Pesquisa em rede.
91
Fechamento
O benefício do entendimento correto de como as consultas são processadas pelos
bancos de dados pode auxiliar no projeto de bancos de dados. Além disso, pode
QUESTÃO OBJETIVA
Para uma comparação de igualdade em uma chave atributo
com um índice primário, podemos usar o índice para
recuperar um único registro que satisfaz a condição de
igualdade correspondente. Esse tipo de busca é chamada
de:
índice primário, igualdade na chave.
índice secundário, igualdade na chave.
índice primário, desigualdade na chave.
índice primário, igualdade na linha.
índice primário, igualdade na restrição.
92
auxiliar nas resoluções de problemas com um diagnóstico correto e,
consequentemente, uma solução correta.
Nesta aula, você teve a oportunidade de:
entender os elementos do processamento de consultas;
aprender como são as medidas do custo da consulta;
entender como funciona a otimização de consultas.
93
Procedimentos e Funções
Pense no seguinte cenário: existe a necessidade de criar um algoritmo que retorne se
um departamento tem muitos professores de uma maneira mais simples para o
usuário. Dessa forma, somente com uma consulta SQL seria mais complexo para
criação e manutenção. Para isso, vamos estudar algumas técnicas de programação
para bancos de dados.
Ao �nal desta aula, você será capaz de:
entender a programação de bancos de dados com suas técnicas e problemas;
utilizar o SQL incorporado, SQL dinâmico e SQLJ;
criar procedimentos armazenados no banco de dados.
Aula 05
Introdução
94
Introdução ao SQL - Técnicas de
Programação
Neste estudo, discutimos alguns dos métodos que foram desenvolvidos para acessar
bancos de dados via programas. A maioria dos acessos ao banco de dados na prática
das aplicações é realizada mediante programas de software que implementam banco
de dados de aplicações. Este software é geralmente desenvolvido em uma
programação de propósito geral da linguagem como Java, C/C++/C#, COBOL ou
alguma outra linguagem de programação. Além disso, muitas linguagens de script,
como PHP e JavaScript, também estão sendo usadas para programação de acesso ao
banco dedados em aplicativos da Web (ELMASRI; NAVATHE, 2016).
SAIBA MAIS
Existem centenas de linguagens de programação. É interessante sempre estar
atento(a) a qual está sendo utilizado atualmente e, principalmente, qual é mais
adequada para o trabalho a ser realizado.
Saiba mais, acessando: http://bit.ly/2XIl7ZH.
95
http://bit.ly/2XIl7ZH
É importante observar que a programação de banco de dados é um tópico muito
amplo. Existem livros inteiros dedicados a cada técnica de programação de banco de
dados e como isso é realizado em um sistema especí�co, como o MySQL, por
exemplo. Novas técnicas são desenvolvidas o tempo todo, e mudanças nas técnicas
existentes são incorporadas em versões mais novas do sistema e linguagens. Uma
di�culdade adicional em apresentar este tópico é que, embora existam Padrões SQL,
esses em si estão continuamente evoluindo, e cada fornecedor de SGBD pode ter
algumas variações do padrão (ELMASRI; NAVATHE, 2016).
Por causa disso, nós decidimos dar uma introdução a algumas das principais técnicas
de comparar essas técnicas, ao invés de estudar um método ou sistema particular em
detalhe. Os exemplos apresentados servem para ilustrar as principais diferenças que
um programador enfrentaria ao usar cada um desses programas de banco de dados.
Vamos tentar usar os padrões SQL em nossos exemplos do que descrever um sistema
especí�co.
Programação de Bancos de Dados: Técnicas e
problemas
Figura 1 - Programação de Bancos de Dados
Fonte: Gmast3r / 123RF.
96
Agora voltamos nossa atenção para as técnicas que foram desenvolvidas para
acessar bancos de dados de programas e, em particular, à questão de como acessar
dados via SQL. A maioria dos sistemas de banco de dados possui uma interface
interativa em que esses comandos SQL podem ser digitados diretamente para
execução pelo sistema de banco de dados. Por exemplo, em um sistema de
computador no qual o MySQL está instalado, os comandos podem ser via a interface
interativa. O usuário pode digitar comandos SQL ou consultas diretamente sobre
várias linhas, terminadas por um ponto e vírgula e a tecla Enter (ou seja, "; <cr>").
Alternativamente, um arquivo de comandos (script) pode ser criado e executado por
meio da interface interativa digitando @ <�lename>. O sistema executará os
comandos escritos no arquivo e exibirá os resultados, se houver. A interface
interativa é bastante conveniente para a criação de esquemas e restrições ou para
consultas ad hoc ocasionais. Entretanto, na prática, na maioria dos bancos de dados
SAIBA MAIS
Existem alguns clientes para a programação em banco de dados utilizando o
MySQL. Uma delas é o MySQL Workbench. Além de permitir a programação, ela
possui uma gama de funcionalidades que permitem um bom gerenciamento do
MySQL.
Saiba mais, acessando: http://bit.ly/2JuQtyM.
97
http://bit.ly/2JuQtyM
as ações são executadas mediante programas que foram cuidadosamente projetados
e testados. Esses programas são geralmente conhecidos como programas aplicativos
ou aplicativos de banco de dados e são usadas como transações pelos usuários �nais.
Outro uso comum de programação de banco de dados é acessar um banco de dados
por meio de um programa de aplicação que implementa uma interface Web, por
exemplo, ao fazer reservas de passagens aéreas ou compras on-line. De fato, a grande
maioria dos aplicativos de comércio eletrônico da Web inclui alguns comandos de
acesso ao banco de dados. Nesta seção, primeiro damos uma visão geral das
principais abordagens para programação. Em seguida, discutimos alguns dos
problemas que ocorrem quando tentamos acessar um banco de dados de uma
linguagem de programação de uso geral, e a típica sequência de comandos para
interagir com um banco de dados de um programa de software (SILBERSCHATZ et al.,
2019).
Abordagens para a Programação de Banco de Dados
Existem várias técnicas para incluir as interações do banco de dados em aplicativos.
As principais abordagens para a programação de banco de dados são as seguintes
(SILBERSCHATZ et al., 2019):
Figura 2 - Programação de Banco de Dados
Fonte: Titipong Jaiharn / 123RF.
98
1. incorporar comandos de banco de dados em uma linguagem de programação. Nesta
abordagem, as instruções do banco de dados são incorporadas à linguagem hospedeira
de programação, mas eles são identi�cados por um pre�xo especial. Um pré-compilador
ou o pré-processador veri�ca o código do programa de origem para identi�car o estado
do banco de dados e extraí-los para processamento pelo SGBD. Eles são substituídos em
um programa por chamadas de função para o código gerado pelo SGBD. Essa técnica é
geralmente referida como SQL incorporado;
2. usando uma biblioteca de funções de banco de dados. Uma biblioteca de funções é
disponibilizada compatível com a linguagem de programação do host para chamadas de
banco de dados. Por exemplo, poderia ser funções para se conectar a um banco de
dados, executar uma consulta, executar uma atualização e assim por diante. A consulta
real do banco de dados e comandos de atualização e quaisquer outras informações
necessárias são incluídas como parâmetros nas chamadas de função. Essa abordagem
fornece o que é conhecido como programação de aplicativo interface (API) para acessar
um banco de dados de programas aplicativos;
3. criando uma nova linguagem. Uma linguagem de programação de banco de dados é
projetado a partir do zero para ser compatível com o modelo de banco de dados e
consulta de língua. Estruturas de programação adicionais, como loops e instruções
condicionais, são adicionadas ao idioma do banco de dados para convertê-lo em um
arquivo completo. Um exemplo dessa abordagem é o da Oracle PL/SQL.
Na prática, as duas primeiras abordagens são mais comuns, pois muitas aplicações
são já escritas em linguagens de programação de uso geral, mas requer alguns dados
para o acesso básico. A terceira abordagem é mais apropriada para aplicativos que
têm interação intensiva com o banco de dados. Um dos principais problemas com as
duas primeiras abordagens é a incompatibilidade de impedância, o que não ocorre na
terceira abordagem (HOFFER et al., 2019).
Incompatibilidade de Impedância
Incompatibilidade de impedância é o termo usado para se referir aos problemas que
ocorrem das diferenças entre o modelo de banco de dados e o modelo de linguagem
de programação. Por exemplo, o modelo relacional prático tem três construções
principais: colunas (atributos) e seus tipos de dados, linhas (também chamadas de
tuplas ou registros) e tabelas (conjuntos ou multisets de registros). O primeiro
problema que pode ocorrer é que os tipos de dados da linguagem de programação
diferem dos tipos de dados de atributo que são disponíveis no modelo de dados.
Portanto, é necessário ter uma ligação para cada host linguagem de programação que
especi�ca para cada tipo de atributo de linguagem de gramática. Uma ligação
diferente é necessária para cada linguagem de programação porque diferentes
99
linguagens têm diferentes tipos de dados. Por exemplo, os dados tipos disponíveis em
C/C ++ e Java são diferentes, e ambos diferem dos dados SQL tipos, que são os tipos
de dados padrão para bancos de dados relacionais (SILBERSCHATZ et al., 2019).
Outro problema ocorre porque os resultados da maioria das consultas são conjuntos
ou multisets de tuplas (linhas) e cada tupla é formada por uma sequência de valores
de atributos. No programa, muitas vezes é necessário acessar os valores de dados
individuais dentro de cada tupla para impressão ou processamento. Portanto, uma
ligação é necessária para mapear o resultado da consulta estrutura de dados, que é
uma tabela, para uma estrutura de dados apropriada na programação língua. Um
mecanismo é necessário para fazer um loop sobre as tuplas em um resultado de
consulta em ordem para acessar uma única tupla por vez e para extrair valores
individuais da tupla. Os valores de atributos extraídos são normalmente copiados
para variáveis de programa apropriadas para processamento posterior pelo
SAIBA MAIS
A incompatibilidadede impedância é um problema comum quando utilizamos
diferentes linguagens de programação para o acesso a um banco de dados
relacional. Esse problema continua sendo estudado conforme as novas
linguagens são criadas ou modi�cadas.
Saiba mais, acessando: http://bit.ly/2xMH1QK.
100
http://bit.ly/2xMH1QK
programa. Uma variável de cursor ou iterador é normalmente usada para loop sobre
as tuplas em um resultado de consulta. Valores individuais dentro de cada tupla são
então extraídos em variáveis de programa distintas do tipo apropriado
(SILBERSCHATZ et al., 2019).
A incompatibilidade de impedância é um problema menor quando uma programação
especial de banco de dados linguagem é projetada que usa o mesmo modelo de dados
e tipos de dados que o banco de dados modelo. Um exemplo de tal linguagem é o
PL/SQL da Oracle. O padrão SQL também tem uma proposta para tal linguagem de
programação de banco de dados, conhecida como SQL/PSM. Para bancos de dados
de objeto, o modelo de dados do objeto é bastante semelhante aos dados modelo da
linguagem de programação Java, portanto, a incompatibilidade de impedância é
muito reduzido quando o Java é usado como a linguagem de host para acessar um
banco de dados de objetos. Várias linguagens de programação de banco de dados
foram implementadas como protótipos de pesquisa.
Sequência Típica de Interação na programação de banco de dados
Quando um programador ou engenheiro de software escreve um programa que
requer acesso a um banco de dados, é bastante comum que o programa seja
executado em um computador enquanto o banco de dados é instalado em outro.
Figura 3 - Interação 
Fonte: Alexander Atkishkin / 123RF.
101
Lembre-se que a principal arquitetura para acesso ao banco de dados é o modelo
cliente/servidor, em que um cliente programa lida com a lógica de uma aplicação de
software, mas inclui algumas chamadas para ou mais servidores de banco de dados
para acessar ou atualizar os dados. Ao escrever tal programa, uma sequência comum
de interação é a seguinte (VANIER et al., 2019):
1. quando o programa cliente requer acesso a um banco de dados especí�co, o gram deve
primeiro estabelecer ou abrir uma conexão com o servidor de banco de dados.
Normalmente, isso envolve a especi�cação do endereço da Internet (URL) da máquina
onde o servidor de banco de dados está localizado, além de fornecer um nome de conta
de login e senha para acesso ao banco de dados;
2. uma vez que a conexão é estabelecida, o programa pode interagir com os dados base
enviando consultas, atualizações e outros comandos do banco de dados. Em geral, a
maioria dos tipos de instruções SQL pode ser incluída em um aplicativo programa;
3. quando o programa não precisa mais acessar um banco de dados especí�co, ele termina
ou fecha a conexão com o banco de dados. Um programa pode acessar vários bancos de
dados, se necessário. Em algumas programações de banco de dados abordagens, apenas
uma conexão pode estar ativa por vez, enquanto em outras abordagens múltiplas
conexões podem ser estabelecidas simultaneamente.
SQL incorporado, SQL dinâmico e SQLJ
Nesta seção, damos uma visão geral da técnica de como instruções SQL podem ser
incorporadas em uma linguagem de programação de uso geral. Nós nos
concentramos em duas linguagens: C e Java. Nesta abordagem incorporada, a
linguagem de programação é chamada de idioma hospedeiro. A maioria das
instruções SQL, incluindo dados ou restrições de�nições, consultas, atualizações ou
de�nições de visualização - podem ser incorporadas em uma linguagem (ELMASRI;
NAVATHE, 2016).
Recuperando tuplas únicas com SQL incorporado
Para ilustrar os conceitos do SQL incorporado, usaremos C como a linguagem
hospedeira. Ao usar C como linguagem host, uma instrução SQL incorporada
distingue-se das declarações de linguagem de programação pre�xando-as com as
palavras EXEC SQL para que um pré-processador (ou pré-compilador) possa separar
Instruções SQL do código da linguagem do host. As instruções SQL dentro de um
programa são terminadas por um END-EXEC correspondente ou por um ponto e
vírgula (;). Regras semelhantes se aplicam para incorporar SQL em outras linguagens
de programação. Dentro de um comando SQL incorporado, podemos nos referir ao
programa C especialmente declarado variáveis. Estas são chamadas de variáveis
102
compartilhadas porque são usadas em ambos os grama e as instruções SQL
embutidas. Variáveis compartilhadas são pre�xadas por dois pontos (:) quando eles
aparecem em uma instrução SQL. Isso distingue nomes de variáveis de programa a
partir dos nomes das construções do esquema do banco de dados, como atributos
(nomes de colunas) e relações (nomes de tabelas). Também permite que variáveis de
programa tenham o mesmo nome como nomes de atributos, pois eles são
distinguíveis pelo pre�xo (:) na instrução SQL (SILBERSCHATZ et al., 2019).
Suponha que queremos escrever programas em C para processar o banco de dados
da universidade. Precisamos declarar variáveis de programa para corresponder aos
tipos de banco de dados atributos que o programa irá processar. O programador
pode escolher os nomes das variáveis do programa; eles podem ou não ter nomes
idênticos aos seus atributos de banco de dados correspondentes. Vamos usar as
variáveis do programa C declaradas para todos os nossos exemplos e mostrar os
segmentos de programa C sem variáveis declarações. Variáveis compartilhadas são
declaradas dentro de uma seção de declaração no programa. Algumas das ligações
comuns de C tipos para tipos SQL são os seguintes. Os tipos SQL INTEGER,
SMALLINT, REAL e DOUBLE são mapeados para os tipos C long, short, �oat e double,
respectivamente. Sequências de comprimento �xo e comprimento variável (CHAR [i],
VARCHAR [i]) em SQL podem ser mapeados para matrizes de caracteres (char [i + 1],
varchar [i + 1]) em C que são um caractere maior que o tipo SQL porque as strings em
C são terminadas por um caractere NULL (\ 0), que não faz parte da própria cadeia de
caracteres. Embora varchar não seja um tipo de dados C padrão, é permitido quando
C é usado para banco de dados SQL gramming. Observe que os únicos comandos SQL
incorporados que dizem ao pré-compilador para anotar os nomes das variáveis C
entre BEGIN DECLARE e END DECLARE, porque eles podem ser incluídos no estado
SQL incorporado mentos, desde que sejam precedidos por dois pontos (:). As linhas 2
a 5 são regulares declarações de programa (SILBERSCHATZ et al., 2019).
As variáveis do programa C declaradas nas linhas 2 a 5 correspondem de acordo com
os atributos das tabelas PROFESSOR e DEPARTAMENTO do Banco de dados
UNIVERSIDADE que foi declarado pelo SQL DDL. As variáveis declaradas na linha 6 -
SQLCODE e SQLSTATE - são usadas para comunicação de erros de identi�cação e
condições de exceção entre o sistema de banco de dados eu programa. A linha 0
mostra um loop de variável de programa que não será usado em nenhuma instrução
SQL, por isso é declarado fora da seção SQL declare.
Conectando-se ao banco de dados, o comando SQL, para estabelecer uma conexão
para um banco de dados, tem o seguinte formato:
CONNECT TO  <nome do servidor> AS <nome da conexão>
103
SET AUTHORIZATION  <nome da conta de usuário e senha>;
Em geral, como um usuário ou programa pode acessar vários servidores de banco de
dados, vários podem ser estabelecidos, mas apenas uma conexão pode estar ativa em
qualquer ponto Tempo. O programador ou usuário pode usar o <nome da conexão>
para mudar a conexão atualmente ativa para uma diferente usando o seguinte
comando:
SET CONNECTION <nome da conexão>;
Uma vez que uma conexão não é mais necessária, ela pode ser terminada pelos
seguintes comandos:
DISCONNECT  <nome da conexão>;
Nos exemplos deste capítulo, assumimos que a conexão apropriada já foi
estabelecida para o banco de dados da UNIVERSIDADE, e que é atualmente conexão
ativa. Comunicando-se entre o programa e o SGBD usando SQLCODE e SQLSTATE.
As duas variáveis especiais de comunicação que são usadas pelo SGBD para
comunicar condições de exceção ou erro ao programaé SQLCODE e SQLSTATE. A
variável SQLCODE mostrada na Figura 1 é uma variável inteira. Depois que cada
comando do banco de dados é executado, o SGBD retorna um valor no SQLCODE.
104
Um valor de 0 indica que a instrução foi executada com sucesso pelo SGBD. E se
SQLCODE> 0 (ou, mais especi�camente, se SQLCODE = 100), isso indica que não
mais dados (registros) estão disponíveis em um resultado de consulta. Se SQLCODE
<0, isso indica que algum erro ocorreu. Em alguns sistemas - por exemplo, no MySQL
- SQLCODE é um campo em uma estrutura de registro chamada SQLCA (área de
comunicação SQL), então é referenciado como SQLCA.SQLCODE. Neste caso, a
de�nição de SQLCA deve ser incluído no programa C, incluindo a seguinte linha:
EXEC SQL include SQLCA;
Em versões posteriores do padrão SQL, uma variável de comunicação chamada
SQLSTATE foi adicionado, que é uma string de cinco caracteres. Um valor de "00000"
no SQLSTATE indica caracteres sem erro ou exceção; outros valores indicam vários
erros ou exceções.
SAIBA MAIS
O SQLCODE é implementado nos bancos de dados para o retorno dos códigos de
SQL. Existem vários códigos e entender do banco que você está utilizando pode
melhorar as respostas ao usuário.
Saiba mais, acessando: http://bit.ly/30vsdSR.
105
http://bit.ly/30vsdSR
Por exemplo, "02000" indica "sem mais dados" ao usar o SQLSTATE. Atualmente,
tanto SQLSTATE e SQLCODE estão disponíveis no padrão SQL. Muitos dos erros e
códigos de exceção retornados no SQLSTATE devem ser padronizados para todo o
SQL fornecedores e plataformas, enquanto os códigos retornados no SQLCODE não
são padronizados, mas são de�nidos pelo fornecedor DBMS. Por isso, geralmente é
melhor usar SQLSTATE porque isso faz o tratamento de erros nos programas
aplicativos independente de um determinado SGBD. Como exercício, o(a) leitor(a)
deve reescrever o dados posteriormente neste capítulo usando SQLSTATE em vez de
SQLCODE.
0) int loop;
1) EXEC SQL BEGIN DECLARE SECTION;
2) varchar nome[50];
3) char sexo[2];
4) �oat salario;
5) int id;
6) int SQLCODE; char SQLSTATE[6];
7) EXEC SQL END DECLARE SECTION;
Nosso primeiro exemplo para ilustrar a programação SQL incorporada é um
segmento de programa repetitivo (loop) que insere um número de identi�cação de
um professor e imprime algumas informações do registro PROFESSOR
correspondente no banco de dados. O código do programa C é mostrado como
segmento de programa E1. O programa lê (entradas) um valor de ID e, em seguida,
recupera a tupla PROFESSOR com esse ID do banco de dados por meio do comando
SQL. A cláusula INTO (linha 5) especi�ca as variáveis do programa em quais valores
de atributos do registro do banco de dados são recuperados. Variáveis de programa C
na cláusula INTO são pre�xados com dois-pontos (:), como discutimos
anteriormente. O INTO cláusula pode ser usada desta maneira somente quando o
resultado da consulta é um único registro; se multi registros são recuperados, um
erro será gerado (SILBERSCHATZ et al., 2019).
A linha 7 em E1 ilustra a comunicação entre o banco de dados e o programa por meio
da variável especial SQLCODE. Se o valor retornado pelo SGBD em SQLCODE é 0, a
instrução anterior foi executada sem erros ou exceção condições. A linha 7 veri�ca
106
isso e assume que, se ocorreu um erro, foi porque não existiu nenhuma tupla
PROFESSOR com o dado ID; portanto, envia uma mensagem para esse efeito (linha
8). Em E1, um único registro é selecionado pela consulta SQL incorporada (porque ID
é uma chave atributo de PROFESSOR). Quando um único registro é recuperado, o
programador pode atribuir seus valores de atributo diretamente a variáveis de
programa C na cláusula INTO, como em linha 5. Em geral, uma consulta SQL pode
recuperar várias tuplas. Nesse caso, normalmente, o gram percorre as tuplas
recuperadas e processa-as uma de cada vez. O conceito de um cursor é usado para
permitir o processamento da tupla por vez de um resultado da consulta pelo
programa da língua an�triã. Nós descrevemos os cursores a seguir.
Recuperando várias tuplas com SQL incorporado usando Cursores
Podemos pensar em um cursor como um ponteiro que aponta para uma única tupla
(linha) do resultado de uma consulta que recupera várias tuplas. O cursor é declarado
quando o SQL comando de consulta é declarado no programa. Mais tarde no
programa, o comando OPEN CURSOR busca o resultado da consulta do banco de
dados e de�ne o cursor para uma posição antes da primeira linha no resultado da
consulta. Isso se torna a linha atual para o cursor. Subsequentemente, os comandos
FETCH são emitidos no programa; cada FETCH move o cursor para a próxima linha
no resultado da consulta, tornando-a aluga linha e copia seus valores de atributo para
o programa C (linguagem hospedeira) especi�cados no comando FETCH por uma
cláusula INTO. A variável do cursor é basicamente um iterador que itera (faz um loop)
sobre as tuplas no resultado da consulta – uma tupla de cada vez.
0) loop = 1;
1) while(loop) {
2)     prompt("Indique o número do professor: ", id);
3)     EXEC SQL
4)     select ID, NOME, SEXO, SALARIO_MENSAL
5)     into :id, :nome, :sexo, :salario
6)     from PROFESSOR where ID = :id;
7)     if(SQLCODE == 0) printf(id, nome, sexo, salario)
8)     else printf("ID não localizado");
107
9)     prompt("Deseja realizar outra pesquisa (1 para sim, 0 para não): ", loop);
10) }
Para determinar quando todas as tuplas no resultado da consulta foram processadas,
a variável de comunicação SQLCODE (ou, alternativamente, SQLSTATE) é veri�cada.
Se um comando FETCH é emitido, o que resulta em mover o cursor além da última
tupla no resultado da consulta, um valor positivo (SQLCODE> 0) é retornado no
SQLCODE, indicando que nenhum dado (tuple) foi encontrado. O programador usa
isso para terminar um loop sobre as tuplas no resultado da consulta. Em geral, vários
cursores podem ser abertos ao mesmo tempo. Um comando CLOSE CURSOR é
emitido para indicar que concluímos o processamento do resultado da consulta
associada a esse cursor. Um exemplo do uso de cursores para processar um resultado
de consulta com vários registros é mostrado no código anterior, onde um cursor
chamado PROFESSOR_CURSOR é declarado na linha 4. O cursor
PROFESSOR_CURSOR está associado à consulta SQL declarada nas linhas 5 a 6, mas
a consulta não é executada até que o comando OPEN PROFESSOR_CURSOR (linha
8) seja processado. A abertura do comando <cursor name> executa a consulta e
busca seu resultado como uma tabela o espaço de trabalho do programa, onde o
programa pode percorrer as linhas individuais (tuplas) pelos comandos subsequentes
FETCH <nome do cursor> (linha 9). Nós assumimos que as variáveis apropriadas do
programa C foram declaradas como no código anterior. O programa segmento no E2
lê (entradas) um nome de departamento (linha 0), recupera a correspondência
número de departamento do banco de dados (linhas 1 a 3) e, em seguida, recupera o
ees que trabalham nesse departamento por meio do cursor PROFESSOR_CURSOR
declarado. Um loop (linhas 10 a 18) itera sobre cada registro no resultado da
consulta, um de cada vez, e imprime o nome do empregado. O programa então lê
(entradas) uma quantia de aumento para aquele empregado (linha 12) e atualiza o
salário do empregado no banco de dados pelo valor do aumento que foi fornecido
(linhas 14 a 16).
Este exemplo também ilustra como o programador pode atualizar os registros do
banco de dados. Quando um cursor é de�nido para linhas que devem ser modi�cadas
(atualizadas), devemos adicionar a cláusula FOR UPDATE OF na declaração do
cursor e listar os nomes de quaisquer atributos que serão atualizados pelo programa.
Isso é ilustrado na linha 7 do código segmento E2. Se as linhas precisarem ser
excluídas, as palavras-chave FOR UPDATE devem ser adicionadas sem especi�car
nenhum atributo. Na versão incorporada de UPDATE (ou DELETE) mand, a condição
WHERE CURRENT OF <nome do cursor> especi�ca que a tupla de aluguel
referenciada pelo cursor é aquela a ser atualizada (ou excluída), como na linha 16 de
E2. Observeque declarar um cursor e associá-lo a uma consulta (linhas de 4 a 7 E2)
108
não executa a consulta; a consulta é executada somente quando o cursor OPEN <
nome> comando (linha 8) é executado. Observe também que não há necessidade de
incluir a cláusula FOR UPDATE OF na linha 7 de E2, se os resultados da consulta
forem usados apenas para �ns de recuperação (sem atualização ou exclusão).
Opções gerais para uma declaração de cursor. Várias opções podem ser especi�cadas
ao declarar um cursor. A forma geral de uma declaração de cursor é a seguinte:
DECLARE <nome do cursor> [INSENSITIVE] [SCROLL] CURSOR
[WITH HOLD] PARA <especi�cação da consulta>
[ORDER BY <especi�cação de pedido>]
[FOR READ | FOR UPDATE [OF <lista de atributos>]];
Já discutimos, brevemente, as opções listadas na última linha. O padrão é que a
consulta é para �ns de recuperação (para ler apenas). Se algumas das tuplas na
consulta resultado forem atualizadas, precisamos especi�car FOR UPDATE OF <lista
de atributos> e listar os atributos que podem ser atualizados. Se algumas tuplas
precisarem ser deletadas, precisamos especi�car ify FOR UPDATE sem nenhum
atributo listado (SILBERSCHATZ et al., 2019).
Quando a palavra-chave opcional SCROLL é especi�cada em uma declaração de
cursor, é possível para posicionar o cursor de outras maneiras que não o acesso
puramente sequencial. Uma busca e orientação podem ser adicionadas ao comando
FETCH, cujo valor pode ser um de NEXT, PRIOR, FIRST, LAST, ABSOLUTE e
RELATIVE i. Nos dois últimos comandos, deve avaliar para um valor inteiro que
especi�ca uma posição de tupla absoluta dentro do resultado da consulta (para
ABSOLUTE i) ou uma posição da tupla relativa ao cursor atual posição (para
RELATIVO i). A orientação de busca padrão que usamos em nosso exame ples é
NEXT. A orientação de busca permite ao programador mover o cursor em torno das
tuplas no resultado da consulta com maior �exibilidade, fornecendo acesso por
posição ou acesso em ordem inversa. Quando SCROLL é especi�cado no sor, a forma
geral de um comando FETCH é a seguinte, com as partes em quadrado parênteses
sendo opcional:
FETCH [[<buscar orientação>] FROM] <nome do cursor> INTO <meta de busca
lista>;
A cláusula ORDER BY ordena as tuplas para que o comando FETCH as busque na
ordem especi�cada. É especi�cado de maneira semelhante à cláusula
109
correspondente para consultas SQL. As duas últimas opções ao declarar um cursor
(INSENSITIVE e WITH HOLD) referem-se às características de transação do banco
de dados.
Especi�cando consultas em tempo de execução usando SQL dinâmico
Nos exemplos anteriores, as consultas SQL incorporadas foram escritas como parte
do código-fonte do programa host. Portanto, sempre que quisermos escrever uma
consulta diferente, deve modi�car o código do programa e passar por todas as etapas
envolvidas (compilação, depuração, teste e assim por diante). Em alguns casos, é
conveniente escrever um programa que pode executar diferentes consultas SQL ou
atualizações (ou outras operações) dinamicamente em tempo de execução. Por
exemplo, podemos querer escrever um programa que aceite uma consulta SQL
digitado do monitor, executa-o e exibe seu resultado, como a tela interativa
interfaces disponíveis para a maioria dos SGBDs relacionais. Outro exemplo é
quando um usuário interface amigável gera consultas SQL dinamicamente para o
usuário com base em clique e clique em operações em um esquema grá�co. Nesta
seção, damos uma breve visão geral do SQL dinâmico, que é uma técnica para
escrever este tipo de programa de banco de dados, dando um exemplo simples para
ilustrar como o SQL dinâmico pode funcionar (ELMASRI; NAVATHE, 2016).
Figura 4 - SQL dinâmico
Fonte: Botond Peter / 123RF.
110
O segmento de programa E3 na Figura 4 lê uma string que é inserida pelo usuário
(aquela string deve ser um comando SQL update) na variável de programa string sql
update string na linha 3. Ele então prepara isso como um comando SQL na linha 4
associando-o com o comando sql da variável. A linha 5 executa o comando. Observe
que, neste caso, nenhuma veri�cação de sintaxe ou outros tipos de veri�cações no
comando são possíveis em tempo de compilação, uma vez que o comando SQL não
está disponível até o tempo de execução. Este contrasta com nossos exemplos
anteriores de SQL embutido, onde a consulta poderia ser veri�cada em tempo de
compilação porque seu texto estava no código-fonte do programa.
0) prompt("Informe o número do departamento: ", id);
1) EXEC SQL
2)     select ID into :dnumber
3)    from DEPARTAMENTO where ID = :id;
4) EXEC SQL DECLARE PROFESSOR_CURSOR FOR
5)    select ID, NOME, SEXO, SALARIO_MENSAL
6)    from PROFESSOR where DEPARTAMENTO_FK = :dnumber
7)    FOR UPDATE OF SALARIO_MENSAL;
8) EXEC SQL OPEN PROFESSOR_CURSOR;
9) EXEC SQL FETCH from PROFESSOR_CURSOR into :id, :nome, : sexo, :salario;
10) while(SQLCODE == 0) {
11)    printf("Professor: ", NOME);
12)    prompt("Digite o aumento: ", aumento);
13)    EXEC SQL
14)   update PROFESSOR
15)   set SALARIO_MENSAL = SALARIO_MENSAL + :aumento
16)   where CURRENT OF PROFESSOR_CURSOR;
111
17)    EXEC SQL FETCH from PROFESSOR_CURSOR into :id, :nome, : sexo, :salario;
18) }
19) EXEC SQL CLOSE PROFESSOR_CURSOR;
Embora a inclusão de um comando de atualização dinâmica seja relativamente
simples SQL dinâmico, uma consulta dinâmica é muito mais complicada. Isso ocorre
porque geralmente nós não sabemos os tipos ou o número de atributos a serem
recuperados pelo SQL consulta quando estamos escrevendo o programa. Uma
estrutura de dados complexa é, às vezes, necessária para permitir diferentes
números e tipos de atributos no resultado da consulta se nenhuma informação prévia
é conhecida sobre a consulta dinâmica (VANIER et al., 2019).
Procedimentos armazenados no banco de dados e
SQL/PSM
Esta seção apresenta dois tópicos adicionais relacionados à programação do banco
de dados. Discutiremos o conceito de procedimentos armazenados, que são
programas módulos armazenados pelo SGBD no servidor de banco de dados. Essas
extensões são conhecidas como SQL/PSM (SQL/Persistent Stored Modules) e podem
ser usadas para escrever procedimentos armazenados. SQL/PSM também serve
como um exemplo de uma linguagem de programação de banco de dados que
estende um modelo e linguagem de banco de dados - ou seja, SQL - com algumas
construções, como instruções condicionais e loops.
112
Procedimentos armazenados e funções do banco de dados
Em nossa apresentação de técnicas de programação de banco de dados, até agora,
suposição implícita de que o programa de aplicativo de banco de dados estava sendo
executado em um cliente, ou mais provavelmente no computador do servidor de
aplicativos na camada intermediária de uma arquitetura cliente-servidor de três
camadas. Em ambos os casos, a máquina onde o programa está sendo executado é
diferente da máquina na qual o servidor de banco de dados - e a parte principal do
pacote de software SGBD - está localizado. Embora isso seja adequado para muitas
aplicações, às vezes é útil criar módulos básicos do programa - procedimentos ou
funções - que são armazenados e executados no servidor de banco de dados. Estes
são historicamente conhecidos como procedimentos armazenados, embora possam
ser funções ou procedimentos. O termo usado no padrão SQL para procedimentos
armazenados é módulos armazenados persistentes porque programas são
armazenados persistentemente pelo SGBD, similarmente aos dados persistentes
SAIBA MAIS
O SQL/Persistent Stored Modules possui muitas funcionalidades para auxiliar no
desenvolvimento de software para banco de dados. As mais conhecidas são as
funções e os procedimentos armazenados.
Saiba mais, acessando: http://bit.ly/2YPmTcM.
113
http://bit.ly/2YPmTcM
pelo SGBD. Procedimentos armazenados são úteis nas seguintes circunstâncias
(SILBERSCHATZ et al., 2019):
se um programa de banco de dados é necessário por vários aplicativos, ele pode
ser armazenado no servidor e invocado por qualquer um dos programas
aplicativos. Isso reduza duplicação de esforços e melhora a modularidade do
software;
executar um programa no servidor pode reduzir a transferência de dados e a
comunicação custo de aquisição entre o cliente e servidor em determinadas
situações;
esses procedimentos podem melhorar o poder de modelagem fornecido pelas
visões permitindo que tipos mais complexos de dados derivados sejam
disponibilizados aos usuários do banco de dados. Além disso, eles podem ser
usados para veri�car restrições que estão além do poder de especi�cação de
a�rmações e disparadores.
Em geral, muitos SGBDs comerciais permitem que procedimentos e funções
armazenadas sejam escritas em uma linguagem de programação de propósito geral.
Alternativamente, um procedimento armazenado pode ser feito de comandos SQL
simples, como recuperações e atualizações. A forma geral de declaração de
procedimentos armazenados é a seguinte:
CREATE PROCEDURE <nome do procedimento> (<parâmetros>)
<declarações locais>
<corpo do procedimento>;
Os parâmetros e declarações locais são opcionais e são especi�cados somente se
necessário. Para declarar uma função, um tipo de retorno é necessário, portanto, o
formulário de declaração é:
CREATE FUNCTION <nome da função> (<parâmetros>)
RETORNOS <tipo de retorno>
<declarações locais>
<corpo da função>;
114
Se o procedimento (ou função) estiver escrito em uma linguagem de programação, é
típico especi�car a linguagem, bem como um nome de arquivo onde o programa
código é armazenado. Por exemplo, o seguinte formato pode ser usado:
CREATE PROCEDURE <nome do procedimento> (<parâmetros>)
LANGUAGE  <nome da linguagem de programação>
EXTERNAL NAME <nome do caminho do arquivo>;
Em geral, cada parâmetro deve ter um tipo de parâmetro que seja um dos dados SQL
tipos. Cada parâmetro também deve ter um modo de parâmetro, que é um de IN,
OUT, ou INOUT. Estes correspondem aos parâmetros cujos valores são apenas de
entrada, saída (retornado) apenas, ou ambos, entrada e saída, respectivamente.
Como os procedimentos e funções são armazenados persistentemente pelo SGBD,
deve ser possível chamá-los das várias interfaces SQL e programação técnicas. A
instrução CALL no padrão SQL pode ser usada para invocar um procedimento - a
partir de uma interface interativa ou de um SQL ou SQLJ incorporado. O formato da
declaração é o seguinte:
CALL <procedimento ou nome da função> (<lista de argumentos>);
Se esta instrução é chamada de JDBC, ela deve ser atribuída a um objeto de instrução
de digite CallableStatement.
115
SQL/PSM: estendendo o SQL para especi�car persistente Módulos Armazenados
O SQL/PSM é a parte do padrão SQL que especi�ca como escrever persistentes
módulos armazenados. Inclui as instruções para criar funções e procedimentos que
nós descrevemos na seção anterior. Inclui também programação adicional structs
para melhorar o poder do SQL com o propósito de escrever o código (ou corpo) de
procedimentos e funções armazenados. Nesta seção, discutimos as construções
SQL/PSM para condicional (rami�cação) instruções e para instruções em loop. Estes
darão um sabor do tipo de estruturas que o SQL/PSM incorporou; então damos um
exemplo para ilustrar como essas construções podem ser usadas. A instrução de
rami�cação condicional no SQL/PSM possui o seguinte formato:
IF <condição> THEN <lista de instruções>
ELSEIF <condição> THEN <lista de instruções>
...
ATENÇÃO
O SQLJ permite que os programadores de aplicativos incorporem instruções SQL
no código Java de uma maneira compatível com a �loso�a de design do Java.
Fonte: 2 Introduction (s.d.).
116
ELSEIF <condição> THEN <lista de instruções>
ELSE <lista de instruções>
END IF;
0) EXEC SQL BEGIN DECLARE SECTION;
1) varchar sqlupdatestring[256];
2) EXEC SQL END DECLARE SECTION;
...
3) prompt("Informe o comando update: ", sqlupdatestring);
4) EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring;
5) EXEC SQL EXECUTE sqlcommand;
Considere o exemplo na Figura 4, que ilustra como o ramo condicional estrutura pode
ser usada em uma função SQL/PSM. A função retorna um valor de string (linha 1)
descrevendo o tamanho de um departamento dentro de uma empresa com base no
número de empregados. Existe um parâmetro inteiro IN, deptno, que dá um
departamento número. Uma variável local QTDE_PROFESSORES é declarada na
linha 2. A consulta nas linhas 3 e 4 retorna o número de funcionários no
departamento e o ramo condicional em as linhas 5 a 8 retornam um dos valores
{"Muito Grande", "Grande", "Médio", "Pequeno"} com base no número de empregados.
O SQL/PSM possui várias construções para loop. Há padrão enquanto repete as
estruturas de looping, que têm as seguintes formas:
0) CREATE FUNCTION Dept_size(IN id INTEGER)
1) RETURNS VARCHAR[20]
2) DECLARE qtde_professores INTEGER;
3) SELECT COUNT(*) INT qtde_professores
4) FROM PROFESSOR WHERE DEPARTAMENTO_FK = id;
5) IF qtde_professores > 100 THEN RETURN "Muito Grande"
117
6)    ELSEIF qtde_professores > 25 THEN RETURN "Grande"
7)    ELSEIF qtde_professores > 10 THEN RETURN "Médio"
8)    ELSE RETURN "Pequeno"
9) END IF;
WHILE <condição> DO
<lista de instruções>
END WHILE;
REPEAT
<lista de instruções>
UNTIL <condição>
END REPEAT;
Há também uma estrutura de loop baseada em cursor. A lista de instruções em tal
loop é executada uma vez para cada tupla no resultado da consulta. Isso tem o
seguinte formato:
FOR <nome do loop> AS <nome do cursor> CURSOR FOR <query> DO
<lista de instruções>
END FOR;
Loops podem ter nomes e há uma instrução LEAVE <loop name> para quebrar um
loop quando uma condição é satisfeita. O SQL/PSM tem muitos outros recursos, mas
eles são lado o escopo de nossa apresentação (SILBERSCHATZ et al., 2019).
118
QUESTÃO OBJETIVA
As instruções do banco de dados que são incorporadas à
linguagem hospedeira de programação que possui uma
forma especí�ca de codi�cação é uma das técnicas
utilizadas para a criação de aplicativo de bancos de dados.
Como é o nome dessa técnica?
Incorporar comandos de banco de dados em uma linguagem
de programação.
Utilizar um framework de mapeamento de consultas.
Utilizar uma ferramenta de script.
Incorporar arquivos xml de banco de dados em uma
linguagem de programação.
Incorporar comandos de banco de dados em uma stored
procedure.
119
QUESTÃO OBJETIVA
Uma biblioteca de funções é disponibilizada compatível
com a linguagem de programação hospedeira para
chamadas de banco de dados. Por exemplo, poderia ter
funções para se conectar a um banco de dados, executar
uma consulta, executar uma atualização e assim por diante.
A consulta real do banco de dados e comandos de
atualização e quaisquer outras informações necessárias
são incluídas como parâmetros nas chamadas de função.
Essa abordagem fornece o que é conhecido como
programação de aplicativo interface (API) para acessar um
banco de dados de programas aplicativos. Como é o nome
dessa técnica?
Usando uma lista de funções de banco de dados.
Usando um arquivo xml de banco de dados.
Usando uma API de conexão de rede.
Usando uma biblioteca de funções de banco de dados.
Usando uma ferramenta de funções de banco de dados.
120
Fechamento
O maior benefício da programação em banco de dados é a de facilitar a criação de
procedimentos que resolvam problemas que, somente por meio de uma consulta
SQL, seria mais complexo para criação e manutenção. Para isso, estudamos algumas
técnicas de programação para bancos de dados.
Nesta aula, você teve a oportunidade de:
entender a programação de bancos de dados com suas técnicas e problemas;
aprender como utilizar o SQL incorporado, SQL dinâmico e SQLJ;
aprender como criar procedimentos armazenados no banco de dados.
121
ATIVIDADE COMPLEMENTAR
Leia o artigo “Armazenamento Otimizado de Dados RDF
em um SGBD Relacional” publicado no último SBDD
(XXXIII BRAZILIAN SYMPOSIUM ON DATABASES, SBBD,
2018). Veja como os autores abordam a questão de
projetos de banco de dados. Note a conclusão sobre os
elementos de otimização identi�cados pelos autores.
Disponível em:http://bit.ly/2Y0RjLW.
122
http://bit.ly/2Y0RjLW
TEORIA E PRÁTICA
Vamos trabalhar com a otimização de consultas. Para isso,
o primeiro passo é entender como essa consulta é
processada. Vimos como mostrar o plano de execução de
uma consulta para entender como os elementos dela
podem afetar os custos de processamento. Para isso,
analise a consulta a seguir e exiba o seu plano de execução
utilizando a ferramenta do MySQL Workbench.
Consulta:
SELECT
`PROFESSOR`.`NOME`,
   `DEPARTAMENTO`.`NOME`,
(`PROFESSOR`.`SALARIO_MENSAL`),
   (`PROFESSOR`.`SALARIO_MENSAL`)*1.1
FROM `UNIVERSIDADE`.`PROFESSOR`
   INNER JOIN `UNIVERSIDADE`.`DEPARTAMENTO`
  ON `PROFESSOR`.`DEPARTAMENTO_FK` =
`DEPARTAMENTO`.`ID`
ORDER BY `PROFESSOR`.`SALARIO_MENSAL` DESC;
Resposta:
123
124
ESTUDO DE CASO
Vamos utilizar o script para a criação do banco de dados a
seguir para recuperação de dados. Para isso, vamos
recorrer a um requisito de recuperação da seguinte forma:
recupere o nome de todos os professores, com o
nome dos departamentos e simulando 50% de
aumento para cada um deles;
esse resultado deve ser ordenado do maior salário
para o menor.
DROP DATABASE UNIVERSIDADE;
CREATE DATABASE UNIVERSIDADE;
USE UNIVERSIDADE;
CREATE TABLE ENDERECO (
ID INT NOT NULL PRIMARY KEY,
NOME_RUA VARCHAR(50) NOT NULL,
NUMERO_RUA VARCHAR(10) NOT NULL,
CEP VARCHAR(8) NOT NULL,
BAIRRO VARCHAR(50) NOT NULL,
CIDADE VARCHAR(50) NOT NULL,
UF CHAR(2) NOT NULL
) ENGINE=INNODB;
125
CREATE TABLE DEPARTAMENTO (
ID INT NOT NULL PRIMARY KEY,
NOME VARCHAR(50) NOT NULL
) ENGINE=INNODB;
CREATE TABLE PROFESSOR (
ID INT NOT NULL PRIMARY KEY,
NOME VARCHAR(50) NOT NULL,
SALARIO_MENSAL DECIMAL(10,2) NOT NULL,
DEPARTAMENTO_FK INT NOT NULL,
ENDERECO_FK INT NOT NULL,
FOREIGN KEY (DEPARTAMENTO_FK)
    REFERENCES DEPARTAMENTO(ID)
    ON DELETE CASCADE,
FOREIGN KEY (ENDERECO_FK)
    REFERENCES ENDERECO(ID)
    ON DELETE CASCADE
) ENGINE=INNODB;
CREATE TABLE CURSO (
ID INT NOT NULL PRIMARY KEY,
NOME VARCHAR(50) NOT NULL
) ENGINE=INNODB;
CREATE TABLE ALUNO (
126
ID INT NOT NULL PRIMARY KEY,
NOME VARCHAR(50) NOT NULL,
DATA_NASCIMENTO DATE NOT NULL,
CURSO_FK INT,
FOREIGN KEY (CURSO_FK)
    REFERENCES CURSO(ID)
    ON DELETE CASCADE
) ENGINE=INNODB;
CREATE TABLE ORIENTACAO (
PROFESSOR_FK INT,
ALUNO_FK INT,
DATA_INICIO DATE,
DATA_ENCERRAMENTO DATE,
FOREIGN KEY (PROFESSOR_FK)
    REFERENCES PROFESSOR(ID)
    ON DELETE CASCADE,
FOREIGN KEY (ALUNO_FK)
    REFERENCES ALUNO(ID)
    ON DELETE CASCADE
) ENGINE=INNODB;
Resposta:
SELECT
127
`PROFESSOR`.`NOME`,
   `DEPARTAMENTO`.`NOME`,
(`PROFESSOR`.`SALARIO_MENSAL`),
   (`PROFESSOR`.`SALARIO_MENSAL`)*1.1
FROM `UNIVERSIDADE`.`PROFESSOR`
   INNER JOIN `UNIVERSIDADE`.`DEPARTAMENTO`
  ON `PROFESSOR`.`DEPARTAMENTO_FK` =
`DEPARTAMENTO`.`ID`
ORDER BY `PROFESSOR`.`SALARIO_MENSAL` DESC;
128
Mapa Conceitual
MODELO
E-R DDL DML
Tipos Programação Recuperação
Procedimento Função
pode ser feito por
suportado por que permite que permite
feito por
129