Slides Implementação de Banco de Dados

Prévia do material em texto

Implementação de BDs
Slides da Disciplina
Prof. Henrique Mota
mota.henrique@gmail.com
Créditos: prof. Romulo Alencar
Implementação de BDs 2
Referencial Bibliográfico
q Database Management Systems
Ramakrishnan e Gehrke
q Fundamentals of Database Systems
Elmasri e Navathe
q Database System Concepts
Silberschatz, Korth e Sudarshan
q Database Systems – The Complete Book
Garcia-Molina, Ullman e Widom
q The Art of SQL
Faroult e Robson
q Slides de aula do Prof. Dr-Ing Angelo Brayner
Implementação de BDs 3
Conteúdo
q Parte I – Modelo Relacional
➥ Conceitos Básicos
➥ Restrições de Integridade
➥ Álgebra Relacional
q Parte II – SQL
➥ Conceitos Básicos
➥ DDL
➥ DML
➥ Visões
➥ Auto-Numeração
➥ DCL
q Parte III – Indexação
➥ Conceitos Básicos
➥ Classificação de Índices
➥ Estruturas de Índices Ordenados
➥ Criação de Índices em SQL
➥ Efeitos Adversos
q Parte IV – Processamento de 
Consultas
➥ Conceitos Básicos
➥ Representação de Consultas
➥ Implementação dos Operadores 
Relacionais
➥ Otimização de Consultas
➥ Execução de Consultas 
q Parte V – Processamento de 
Transações
➥ Conceitos Básicos
➥ DTL
➥ Problemas de Concorrência
➥ Protocolos de Controle de 
Concorrência
➥ Níveis de Isolamento
Prof. Rômulo Alencar Implementação de BDs 4
MODELO RELACIONAL
Parte I
Implementação de BDs 5
Modelo Relacional
- Conceitos Básicos -
q Proposto por Edgar Codd
➥Funcionário da IBM
➥Em 1970
q Baseado em
➥Teoria dos conjuntos
➥Lógica de predicados
q Forte base matemática
Implementação de BDs 6
Modelo Relacional
- Conceitos Básicos -
qModelo de dados mais popular do mercado
➥Simplicidade de representação
➥Alto desempenho
➥Garantia de consistência
q Exemplos
➥Oracle
➥SQL Server
➥DB2
➥PosgreSQL
➥MySQL
➥Sybase
Implementação de BDs 7
Modelo Relacional
- Conceitos Básicos -
q Dados são representados como Relações
➥Relação
➫Sub-conjunto dos produtos cartesianos entre os 
conjuntos domínio de seus atributos
q Na prática, é utilizado o conceito de Tabela
➥Tabela
➫Dados são organizados em linhas e colunas
➱Cada linha representa um indivíduo
➱Cada coluna representa um atributo dos indivíduos
Implementação de BDs 8
Modelo Relacional
- Conceitos Básicos -
q Exemplo de Tabela
➥Tabela de Funcionários
matricula nome cpf endereco salario
1 João 111.111.111-11 Rua ABC, 123 1500,00
2 José 222.222.222-22 Av. XYZ, 456 2500,00
3 Maria 333.333.333-33 Rua DEF, 222 2000,00
4 Pedro 444.444.444-44 Av. JKL, 555 3000,00
5 Paulo 555.555.555-55 Rua GHI, 789 1500,00
Implementação de BDs 9
Modelo Relacional
- Conceitos Básicos -
q Operadores
➥Álgebra Relacional
➥Cálculo Relacional
➥Permitem que os dados das relações sejam manipulados
➥São os meios “oficiais” de manipulação de dados no 
Modelo Relacional
Implementação de BDs 10
Modelo Relacional
- Conceitos Básicos -
q Linguagens de consulta
➥Permitem manipulação facilitada dos dados de um BD 
relacional
➥Na prática, são o padrão para manipulação de dados 
relacionais
➫SQL � Padrão do mercado
➥Geralmente declarativas
➫Você diz o que quer, mas não como quer que seja 
executado
➥Comumente convertidas para álgebra relacional durante a 
execução
Implementação de BDs 11
Modelo Relacional
- Restrições de Integridade -
q Conjunto de regras que, se aplicadas, garantem
consistência ao BD
qMais populares
➥Restrição de Domínio
➥Restrição de Chave
➥Restrição de Integridade Referencial
Implementação de BDs 12
Modelo Relacional
- Restrições de Integridade -
q Restrição de Domínio
➥Um atributo possui um conjunto domínio relacionado
➫A matrícula de um aluno, por exemplo, possui como 
conjunto domínio o conjunto dos números inteiros
➥A Restrição de Domínio diz que, necessariamente, o valor 
de um atributo faz parte do conjunto domínio do mesmo
➫Desta forma, torna-se impossível atribuir um valor 
diferente de um número inteiro a uma matrícula
➱Garantia de integridade!
Implementação de BDs 13
Modelo Relacional
- Restrições de Integridade -
q Restrição de Domínio
➥Na prática, a Restrição de Domínio é implementada 
através de
➫Indicação de tipos de dados
➫Regras de checagem (verificação)
➱Checks
➫Permissão (ou não) de valores nulos
Implementação de BDs 14
Modelo Relacional
- Restrições de Integridade -
q Restrição de Chave
➥Existe um sub-conjunto diferente de vazio do conjunto total de 
atributos que identifica cada entidade da relação
➫Valores não se repetem!
➥A Restrição de Chave garante que um indivíduo do mundo real 
não apareça mais de uma vez na mesma tabela
➫Garantia de integridade!
➥Uma tabela pode ter várias chaves, mas apenas uma delas 
será a chave primária
➫As outras serão chaves candidatas / alternativas
➥Cada chave pode ser
➫Simples → Um atributo
➫Composta → Mais de um atributo
➱Também conhecida como super-chave
Implementação de BDs 15
Modelo Relacional
- Restrições de Integridade -
q Restrição de Chave
➥Segundo o Modelo Relacional, toda relação precisa ter ao 
menos uma chave
➫Teoria dos Conjuntos
➱Um conjunto não pode possuir elementos repetidos
➧ Para garantir isso, uma chave deve existir!
➥Na prática, porém, podemos ter tabelas com linhas repetidas
➫É importante que toda tabela possua uma chave!
➫Caso não seja possível encontrar um conjunto de atributos 
para compor a chave, pode-se incluir um novo atributo com 
essa finalidade
➱Chave externa
➧ Também conhecida como surrogate key
➧ Geralmente um código de auto-numeração
Implementação de BDs 16
Modelo Relacional
- Restrições de Integridade -
q Restrição de Integridade Referencial
➥Uma chave estrangeira é um conjunto de campos de 
uma tabela que é chave primária em outra tabela
➫Utilizada para representar relacionamentos
➫Chave estrangeira funciona como um ponteiro para a 
outra tabela
➥A Restrição de Integridade Referencial diz que os 
possíveis valores que chave estrangeira pode assumir em 
sua tabela estão limitados aos valores da chave primária 
relacionada a ela na outra tabela
➫Garantia de integridade!
Implementação de BDs 17
Modelo Relacional
- Restrições de Integridade -
q Restrição de Integridade Referencial
➥Exemplo
➥Como a coluna “lotacao” é chave estrangeira de 
Departamentos em Funcionários, os valores dela estão 
limitados aos valores da chave primária de 
Departamentos: a coluna “codigo”
matrícula nome lotacao
1 João 2
2 José 1
3 Maria 2
4 Pedro 3
Funcionários
codigo nome
1 Gerência
2 Financeiro
3 Vendas
Departamentos
Implementação de BDs 18
Álgebra Relacional
- Conceitos Básicos -
q Operador oficial do Modelo Relacional
➥Padrão “de direito”
➥Desenvolvida pelo próprio Edgar Codd
q Funciona como uma linguagem de consulta para bancos 
de dados relacionais
q Coleção de operações sobre relações
➥Cada operador recebe relações como operandos 
(entrada)
➥Fornece uma relação como resultado (saída)
Relação(ões) � Consulta � Relação
Entrada Saída
q Linguagem procedural
Implementação de BDs 19
Álgebra Relacional
- Conceitos Básicos -
q A Álgebra Relacional possui um conjunto bastante 
amplo de operadores
q Para nosso estudo, vamos admitir os seguintes:
➥Seleção
➥Projeção
➥União
➥Intersecção
➥Diferença
➥Produto Cartesiano
➥Junção
Operadores de conjuntos
Implementação de BDs 20
Álgebra Relacional
- Exemplo Base -
q Admita a seguinte relação nos exemplos a seguir
➥Relação “Funcionarios”
matricula nome salario lotacao
1 Rômulo 1000 1
2 Alex 2000 2
3 João 2500 2
Implementação de BDs 21
Álgebra Relacional
- Seleção -
q Operador unário
➥1 relação como operando
q Símbolo
➥ !
q Finalidade
➥Filtragem horizontal
➫Filtragem de tuplas
q Sintaxe
➥!"R
➫": condição lógica
➫R: relaçãoImplementação de BDs 22
Álgebra Relacional
- Seleção -
q Exemplo
➥Quais os funcionários que ganham mais de R$1.000?
➥Resposta em Álgebra Relacional!salario>1000Funcionarios
➥Resultado
➥Importante
➫O resultado de uma operação da Álgebra Relacional sempre
é uma relação!
matricula nome salario lotacao
2 Alex 2000 2
3 João 2500 2
Implementação de BDs 23
Álgebra Relacional
- Projeção -
q Operador unário
➥1 relação como operando
q Símbolo
➥ #
q Finalidade
➥Filtragem vertical
➫Filtragem de atributos
q Sintaxe
➥#AR
➫A: lista de atributos
➫R: relação
Implementação de BDs 24
Álgebra Relacional
- Projeção -
q Exemplo
➥Quais os nomes e salários dos funcionários?
➥Resposta em Álgebra Relacional
➫#nome,salarioFuncionarios
➥Resultado
➥Importante
➫O resultado de uma operação da Álgebra Relacional sempre
é uma relação!
nome salario
Rômulo 1000
Alex 2000
João 2500
Implementação de BDs 25
Álgebra Relacional
- Combinando Operadores -
q Sabendo que o resultado de uma operação sempre é
uma relação, podemos combinar operadores!
q Exemplo
➥Quais os nomes e salários dos funcionários que trabalham
na lotação 2?
➥Resposta em Álgebra Relacional#nome,salario(!lotacao=2Funcionarios)
➥Resultado nome salario
Alex 2000
João 2500
Implementação de BDs 26
Álgebra Relacional
- União -
q Assim como na teoria dos conjuntos, é possível utilizar a 
operação de união entre relações
➥A união entre dois conjuntos é um conjunto com todos os 
elementos dos conjuntos originais
➥A união entre duas relações é uma relação com todas as 
tuplas das relações originais
q Operador binário
➥2 relações como operandos
q Símbolo
➥�
q Finalidade
➥Unir as tuplas das duas relações
Implementação de BDs 27
Álgebra Relacional
- União -
q Restrições
➥As relações envolvidas precisam ser compatíveis
➫Elas devem, obrigatoriamente, ter a mesma quantidade
de colunas
➫As colunas de mesma posição em cada tabela devem
ter tipos de dados compatíveis entre si
q Sintaxe
➥R1 � R2
➫R1 e R2: relações
Implementação de BDs 28
Álgebra Relacional
- Exemplo Base -
q Admita as seguintes relações no exemplo a seguir
➥Relação “Funcionarios”
➥Relação “Clientes”
matricula nome endereco salario
1 Rômulo Rua ABC 1000
2 Alex Rua DEF 2000
codigo nome endereco
1 Ana Rua GHI
2 Maria Av TUV
3 Alex Rua DEF
Implementação de BDs 29
Álgebra Relacional
- União -
q Exemplo
➥Deseja-se criar uma mala direta para todos os 
funcionários e clientes da empresa. Como conseguir os 
nomes e endereços de todos os funcionários e clientes 
juntos?
➥Solução: usar união!
➥Resposta em Álgebra Relacional
(#nome,enderecoFuncionarios) � (#nome,enderecoClientes)
➥Resultado nome endereco
Rômulo Rua ABC
Alex Rua DEF
Ana Rua GHI
Maria Av TUV
Implementação de BDs 30
Álgebra Relacional
- Intersecção -
q Operação vinda diretamente da teoria dos conjuntos
➥As tuplas da primeira relação que estiverem também na 
segunda vão para o resultado
q Operador binário
➥2 relações como operandos
q Símbolo
➥∩
q Sintaxe
➥R1 ∩ R2
➫R1 e R2: relações
➱Assim como na união, as relações precisam ser compatíveis
Implementação de BDs 31
Álgebra Relacional
- Intersecção -
q Exemplo
➥Quais os nomes e endereços dos funcionários que 
também são clientes?
➥Solução: usar intersecção!
➥Resposta em Álgebra Relacional
(#nome,enderecoFuncionarios) ∩ (#nome,enderecoClientes)
➥Resultado
nome endereco
Alex Rua DEF
Implementação de BDs 32
Álgebra Relacional
- Diferença -
q Operação vinda diretamente da teoria dos conjuntos
➥As tuplas da primeira relação que não estiverem na 
segunda vão para o resultado
q Operador binário
➥2 relações como operandos
q Símbolo
➥ -
q Sintaxe
➥R1 - R2
➫R1 e R2: relações
➱Assim como na união, as relações precisam ser compatíveis
Implementação de BDs 33
Álgebra Relacional
- Diferença -
q Exemplo
➥Quais os nomes e endereços dos funcionários que não 
são clientes?
➥Solução: usar diferença!
➥Resposta em Álgebra Relacional
(#nome,enderecoFuncionarios) - (#nome,enderecoClientes)
➥Resultado
nome endereco
Rômulo Rua ABC
Implementação de BDs 34
Álgebra Relacional
- Exemplo Base -
q Admita as seguintes relações nos exemplos a seguir
➥Relação “Funcionarios”
➥Relação “Departamentos”
matricula nome salario lotacao
1 Rômulo 1000 1
2 Alex 2000 2
3 João 2500 2
codigo nome_dep
1 Gerência
2 Financeiro
3 Vendas
Implementação de BDs 35
Álgebra Relacional
- Produto Cartesiano -
q Operação vinda diretamente da teoria dos conjuntos
➥Cada tupla de uma relação é “combinada” com todas as 
tuplas da outra relação, formando uma tupla no resultado
contendo todos atributos das duas relações
q Operador binário
➥2 relações como operandos
q Símbolo
➥�
q Sintaxe
➥R1� R2
➫R1 e R2: relações
Implementação de BDs 36
Álgebra Relacional
- Produto Cartesiano -
q Exemplo 1
➥Produto cartesiano entre Funcionarios e Departamentos
Funcionarios � Departamentos
Implementação de BDs 37
Álgebra Relacional
- Produto Cartesiano -
q Exemplo:
➥Resultado
matricula nome salario lotacao codigo nome_dep
1 Rômulo 1000 1 1 Gerência
1 Rômulo 1000 1 2 Financeiro
1 Rômulo 1000 1 3 Vendas
2 Alex 2000 2 1 Gerência
2 Alex 2000 2 2 Financeiro
2 Alex 2000 2 3 Vendas
3 João 2500 2 1 Gerência
3 João 2500 2 2 Financeiro
3 João 2500 2 3 Vendas
Implementação de BDs 38
Álgebra Relacional
- Produto Cartesiano -
q Exemplo 2#nome(!salario>1500Funcionarios) � #nome_depDepartamentos
q Resultado nome nome_dep
Alex Gerência
Alex Financeiro
Alex Vendas
João Gerência
João Financeiro
João Vendas
Implementação de BDs 39
Álgebra Relacional
- Junção -
q Geralmente o produto cartesiano traz resultados
“estranhos”
➥Nos exemplos anteriores, não faz sentido “combinar”
departamentos diferentes da lotação do funcionário
q Um produto cartesiano pode ser combinado com a 
operação de seleção (filtragem de tuplas) para produzir
resultados mais refinados
➥Chamamos essa operação de junção
Implementação de BDs 40
Álgebra Relacional
- Junção -
q Operador derivado
➥Produto cartesiano seguido de uma seleção
q Operador binário
➥2 relações como operandos
q Símbolo
➥ �
q Sintaxe
➥R1 �" R2 � !"(R1� R2)
➫R1 e R2: relações
➫": condição lógica
Implementação de BDs 41
Álgebra Relacional
- Junção -
q Exemplo:
➥Quais os nomes de todos funcionários e os nomes dos 
departamentos onde eles estão lotados?
➥Resposta em Álgebra Relacional#nome,nome_dep(Funcionarios �lotacao=codigo Departamentos)
➥Resultado
nome nome_dep
Rômulo Gerência
Alex Financeiro
João Financeiro
Implementação de BDs 42
Álgebra Relacional
- Junção -
qTipos específicos de junção
➥Junção Theta
➫É a junção tradicional, comum, com a informação do critério "
➥Equijunção (equijoin)
➫Junção cujo critério " é composto apenas por comparações de 
igualdade
➥Junção Natural
➫Junção cujo critério é automaticamente composto pela 
igualdade entre os atributos das duas relações que possuem o 
mesmo nome
➱Neste caso, o critério " não deve ser informado
➫No resultado, apenas uma ocorrência de cada coluna de 
mesmo nome é mostrada
Implementação de BDs 43
Álgebra Relacional
- Junção -
qTipos específicos de junção
➥Semi-Junção
➫Junção em que apenas os atributos de uma das 
relações são exibidos no resultado
➫Operadores � (atributos da primeira relação no 
resultado) e � (atributos da segunda relação no 
resultado)
➥Junção Externa
➫Junção em que tuplas das relações podem ser 
inseridas no resultado, mesmo que não satisfaçam o 
critério "
➱Será abordada como parte do conteúdo de SQL
➫Operadores ⟕ (left join), ⟖ (right join) e ⟗ (full join)
Prof.Rômulo Alencar Implementação de BDs 44
SQL
Parte II
Implementação de BDs 45
SQL
- Conceitos Básicos -
q Structured Query Language
q Linguagem de consulta para SGBDs relacionais
➥Padrão “de facto”
➥Linguagem declarativa
➥Baseada na álgebra relacional e no cálculo relacional
Implementação de BDs 46
SQL
- Conceitos Básicos -
q Histórico:
➥Desenvolvida pela IBM
➫Início dos anos 70
➥Para o uso com o SGBD System R
➥Inicialmente chamada SEQUEL
➥Ao final dos anos 70, IBM e ORACLE utilizavam SQL 
como linguagem de consulta
Implementação de BDs 47
SQL
- Conceitos Básicos -
q Composta por:
➥DML (Data Manipulation Language)
➫Consultas e atualizações è Dados
➥DDL (Data Definition Language)
➫Alterações no esquema do BD è Estrutura
➥DCL (Data Control Language)
➫Controle de usuários e permissões è Acesso
➥DTL (Data Transaction Language)
➫Controle de transações è Multiprocessamento
Implementação de BDs 48
SQL
- DDL -
q Permite manipular a estrutura do BD
q Comandos básicos:
➥CREATE
➫Criação de objetos do BD
➥DROP
➫Exclusão de objetos do BD
➥ALTER
➫Alteração de objetos do BD
Implementação de BDs 49
SQL
- DDL -
q Aplicados a tabelas:
➥CREATE TABLE
➫Criação de tabelas
➥DROP TABLE
➫Exclusão de tabelas
➥ALTER TABLE
➫Alteração de tabelas
Implementação de BDs 50
SQL
- CREATE TABLE -
q Comando de criação de tabelas
q Sintaxe:
CREATE TABLE nome_tabela (
coluna1 tipo_de_dados [NULL|NOT NULL] [DEFAULT valor1],
coluna2 tipo_de_dados [NULL|NOT NULL] [DEFAULT valor2],
coluna3 tipo_de_dados [NULL|NOT NULL] [DEFAULT valor3],
…
restrição_de_integridade1,
restrição_de_integridade2,
…
)
Implementação de BDs 51
SQL
- CREATE TABLE -
q Tipos de dados definidos pelo padrão ANSI SQL:2011
➥Tipos numéricos exatos
➫SMALLINT
➫INTEGER (INT)
➫BIGINT
➫DECIMAL
➫NUMERIC
➥Tipos numéricos aproximados
➫FLOAT
➫REAL
➫DOUBLE PRECISION (DOUBLE)
Implementação de BDs 52
SQL
- CREATE TABLE -
q Tipos de dados definidos pelo padrão ANSI SQL:2011
➥Tipos de cadeias de caracteres
➫CHARACTER (CHAR)
➫CHARACTER VARYING (VARCHAR)
➫CHARACTER LARGE OBJECT (CLOB)
➥Tipos de cadeias binárias
➫BINARY
➫BINARY VARYING (VARBINARY)
➫BINARY LARGE OBJECT (BLOB)
➥Tipo booleano
➫BOOLEAN
Implementação de BDs 53
SQL
- CREATE TABLE -
q Tipos de dados definidos pelo padrão ANSI SQL:2011
➥Tipos de data/hora
➫DATE
➫TIME
➫TIMESTAMP
➫INTERVAL
q Cada SGBD do mercado possui sua própria lista de 
tipos de dados suportados, que pode ou não seguir o 
padrão ANSI
➥Consulte a documentação do seu SGBD!
Implementação de BDs 54
SQL
- CREATE TABLE -
q Valor nulo
➥Quando a coluna não admitir valores nulos
➫Deve-se usar NOT NULL na definição
➥Quando a coluna admitir valores nulos
➫Pode-se usar NULL
➫Ou simplesmente não indicar nada
➥Importante
➫Chaves primárias não podem admitir valores nulos
q Valor DEFAULT
➥É o valor atribuído automaticamente à coluna quando um 
valor específico não é informado
Implementação de BDs 55
SQL
- CREATE TABLE -
q Sintaxe das restrições de integridade:
➥Chave primária
CONSTRAINT nome_restrição PRIMARY KEY(colunas)
➥Chave candidata / alternativa
CONSTRAINT nome_restrição UNIQUE(colunas)
➥Checagem
CONSTRAINT nome_restrição CHECK(expressão)
➥Chave estrangeira
CONSTRAINT nome_restrição FOREIGN KEY(colunas)
REFERENCES tabela(colunas)
[ON UPDATE ação_referencial]
[ON DELETE ação_referencial]
Implementação de BDs 56
SQL
- CREATE TABLE -
q Restrição de Chave Estrangeira
➥Ações Referenciais
➫CASCADE
➱Propaga a alteração ou remoção da chave primária para a 
chave estrangeira
➫RESTRICT
➱A alteração ou remoção da chave primária não é possível se 
existirem valores correspondentes na chave estrangeira
➫NO ACTION
➱Não executa qualquer ação na chave estrangeira quando a 
chave primária é alterada ou removida
Implementação de BDs 57
SQL
- CREATE TABLE -
q Restrição de Chave Estrangeira
➥Ações Referenciais
➫SET DEFAULT
➱Altera o valor da chave estrangeira para o valor DEFAULT da 
coluna quando a chave primária é alterada ou removida
➫SET NULL
➱Altera o valor da chave estrangeira para o valor NULL quando 
a chave primária é alterada ou removida
Implementação de BDs 58
SQL
- CREATE TABLE -
q Exemplo
➥Criação da tabela de departamentos:
CREATE TABLE Departamentos (
codigo INT NOT NULL,
nome VARCHAR(50) NOT NULL,
CONSTRAINT pk_departamentos
PRIMARY KEY(codigo)
)
Implementação de BDs 59
SQL
- CREATE TABLE -
q Exemplo
➥ Criação da tabela de funcionários:
CREATE TABLE Funcionarios (
matricula INT NOT NULL,
nome VARCHAR(50) NOT NULL,
cpf CHAR(11) NOT NULL,
salario DECIMAL(10, 2) NOT NULL,
lotacao INT,
CONSTRAINT pk_funcionarios
PRIMARY KEY(matricula),
CONSTRAINT un_funcionarios_cpf UNIQUE(cpf),
CONSTRAINT ck_funcionarios_salario CHECK(salario >= 0),
CONSTRAINT fk_funcionarios_departamentos
FOREIGN KEY(lotacao)
REFERENCES Departamentos(codigo)
)
Implementação de BDs 60
SQL
- CREATE TABLE -
q Restrições de integridade podem ser informadas com sintaxe
reduzida
➥Nomes das restrições podem ser omitidos
➫O SGBD atribuirá automaticamente os nomes, de acordo com seu
padrão adotado
➫Neste caso, a cláusula CONSTRAINT não deve ser utilizada
➥Quando a chave primária ou candidata possuir apenas uma coluna, 
sua indicação pode ficar na própria definição da coluna
➫Apenas quando possuir uma só coluna!
➥Quando a chave estrangeira possuir apenas uma coluna, sua
indicação pode ficar na própria definição da coluna
➫Apenas a cláusula REFERENCES precisa ser informada
➥ A coluna referenciada pela chave estrangeira pode ser omitida
➫Será, por padrão, a chave primária da tabela referenciada
Implementação de BDs 61
SQL
- CREATE TABLE -
q Exemplo (1)
➥Criação da tabela de funcionários:
CREATE TABLE Funcionarios (
matricula INT NOT NULL,
nome VARCHAR(50) NOT NULL,
cpf CHAR(11) NOT NULL,
salario DECIMAL(10, 2) NOT NULL,
lotacao INT,
PRIMARY KEY (matricula),
UNIQUE (cpf),
CHECK (salario >= 0),
FOREIGN KEY (lotacao) REFERENCES Departamentos
)
Implementação de BDs 62
SQL
- CREATE TABLE -
q Exemplo (2)
➥Criação da tabela de funcionários:
CREATE TABLE Funcionarios (
matricula INT PRIMARY KEY NOT NULL,
nome VARCHAR(50) NOT NULL,
cpf CHAR(11) UNIQUE NOT NULL,
salario DECIMAL(10, 2) NOT NULL,
lotacao INT REFERENCES Departamentos,
CHECK (salario >= 0)
)
Implementação de BDs 63
SQL
- DROP TABLE -
q Comando de exclusão de tabelas
q Sintaxe:
DROP TABLE nome_tabela
Implementação de BDs 64
SQL
- DROP TABLE -
q Exemplo
➥Exclusão da tabela de funcionários:
DROP TABLE Funcionarios
➥Todos os registros da tabela de funcionários serão 
excluídos, junto com sua estrutura
Implementação de BDs 65
SQL
- DROP TABLE -
q Importante!
➥As restrições de integridade devem ser sempre 
respeitadas!
➥Caso tentássemos excluir a tabela de departamentos com 
o comando DROP a seguir
DROP TABLE Departamentos
➫Antes da exclusão da tabela de funcionários, um erro 
iria ocorrer
➱A restrição de chave estrangeira seria desrespeitada!
Implementação de BDs 66
SQL
- ALTER TABLE -
q Comando de alteração de tabelas
q Sintaxe:
ALTER TABLE nome_tabela
ADD [CONSTRAINT] nome_objeto nova_definição
ALTER TABLE nome_tabela
DROP|ALTER COLUMN|CONSTRAINT
nome_objeto [nova_definição]
Implementação de BDs 67
SQL
- ALTER TABLE -
q Exemplo
➥Inclusão da coluna “rg” na tabela de funcionários:
ALTER TABLE Funcionarios
ADD rg VARCHAR(10)
➥Alteração do tamanho da coluna “rg” na tabela de 
funcionários:
ALTER TABLE Funcionarios
ALTER COLUMN rg TYPE VARCHAR(50)
➥Exclusão da coluna “rg” na tabela de funcionários:
ALTER TABLE Funcionarios
DROP COLUMNrg
Implementação de BDs 68
SQL
- ALTER TABLE -
q Exemplo
➥ Inclusão da restrição de chave candidata na coluna “rg” da tabela de 
funcionários:
ALTER TABLE Funcionarios
ADD CONSTRAINT un_funcionarios_rg UNIQUE(rg)
ou
ALTER TABLE Funcionarios ADD UNIQUE(rg)
➥ Alteração na restrição de checagem do salário da tabela de funcionários:
ALTER TABLE Funcionarios
ALTER CONSTRAINT ck_funcionarios_salario
CHECK(salario >= 500)
➥ Exclusão da restrição de chave estrangeira na lotação na tabela de 
funcionários:
ALTER TABLE Funcionarios
DROP CONSTRAINT fk_funcionarios_departamentos
Implementação de BDs 69
SQL
- DML -
q Permite consultas e atualizações aos dados 
armazenados
q Comandos básicos:
➥INSERT
➫Inserções
➥DELETE
➫Exclusões
➥UPDATE
➫Alterações
➥SELECT
➫Consultas
Implementação de BDs 70
SQL
- INSERT -
q Comando de inserção
q Sintaxe 1:
INSERT INTO tabela [(lista_de_colunas)]
VALUES (lista_de_valores)
q Sintaxe 2:
INSERT INTO tabela [(lista_de_colunas)]
SELECT …
Implementação de BDs 71
SQL
- INSERT -
q Exemplo:
➥Adicione o funcionário “João da Silva”, de matrícula 123 e 
salário R$2.500,00 à tabela de funcionários
➥Consulta em SQL:
INSERT INTO Funcionarios(matricula, nome, 
salario)
VALUES (123, ‘João da Silva’, 2500)
Implementação de BDs 72
SQL
- DELETE -
q Comando de remoção
q Sintaxe:
DELETE FROM tabela
[WHERE condição]
Implementação de BDs 73
SQL
- DELETE -
q Exemplo:
➥Apague da tabela de funcionários todos os funcionários de 
salário inferior a R$1.000,00
➥Consulta em SQL:
DELETE FROM Funcionarios
WHERE salario < 1000
Implementação de BDs 74
SQL
- DELETE -
q Importante!
➥As exclusões realizadas pelo comando DELETE são
registradas no arquivo de LOG do BD
➫Uma recuperação das linhas apagadas pode ser
efetuada depois da exclusão, dependendo do modelo
de recuperação do SGBD
➥Existe um comando similar, chamado TRUNCATE
➫Exclui todas as linhas da tabela
➫Mas não registra as exclusões no arquivo de LOG
➱Mais rápido, mas extremamente perigoso!
Implementação de BDs 75
SQL
- UPDATE -
q Comando de alteração
q Sintaxe:
UPDATE tabela
SET coluna1 = valor1 [, coluna2 = valor2…]
[WHERE condição]
Implementação de BDs 76
SQL
- UPDATE -
q Exemplo:
➥Os funcionários com salários menores que R$2.000,00 
receberão um aumento de 25%
➥Consulta em SQL:
UPDATE Funcionarios
SET salario = salario * 1.25
WHERE salario < 2000
Implementação de BDs 77
SQL
- SELECT -
q Comando básico de consulta
q Sintaxe:
SELECT lista_de_colunas | *
FROM lista_de_tabelas
[WHERE condição]
[GROUP BY expressão_de_agrupamento
[HAVING condição]]
[ORDER BY expressão_de_ordenamento]
Implementação de BDs 78
SQL
- SELECT -
q Exemplo:
➥Tabela “Funcionarios”
id nome salario
1 Rômulo 1000
2 Alex 2000
3 João 2500
4 José 1500
Implementação de BDs 79
SQL
- SELECT -
q Exemplo:
➥Consulta que retorne os nomes e salários de todos os
funcionários que tenham salários a partir de R$ 2.000,00.
➥Consulta em SQL:
SELECT nome, salario
FROM Funcionarios
WHERE salario >= 2000
Implementação de BDs 80
SQL
- SELECT -
q Exemplo:
➥Resultado:
➥Importante!
➫O resultado de um SELECT é sempre uma tabela!
nome salario
Alex 2000
João 2500
Implementação de BDs 81
SQL
- SELECT -
q Toda consulta em SQL é convertida para álgebra 
relacional no momento de sua execução
SELECT nome, salario
FROM Funcionarios
WHERE salario >= 2000
ê
πnome,salario(σsalario>=2000(Funcionarios))
Implementação de BDs 82
SQL
- SELECT -
q Caso todas as colunas da tabela devam aparecer no 
resultado, pode-se utilizar um asterisco (*) no lugar da 
lista de colunas
q A consulta a seguir representa uma leitura total da 
tabela Funcionarios, sem qualquer filtro nas linhas nem
nas colunas:
SELECT *
FROM Funcionarios
Implementação de BDs 83
SQL
- SELECT -
q Nomes de colunas podem ser qualificados
➥A operação de qualificação consiste em identificar de 
forma explícita de qual tabela é a coluna
➥A qualificação utiliza o operador ponto (.)
➫Similar ao acesso a atributos de objetos na maioria das 
linguagens de programação
➥Exemplo:
SELECT Funcionarios.nome, Funcionarios.salario
FROM Funcionarios
WHERE Funcionarios.salario >= 2000
Implementação de BDs 84
SQL
- SELECT -
q Alguns operadores definidos no padrão ANSI SQL:2011 
para uso em expressões
➥Operadores unários: + -
➥Operadores aritméticos: + - * /
➥Operadores relacionais: = <> > < >= <=
➥Operadores lógicos: NOT AND OR
➥Operador de concatenação de strings: ||
q Obs: É muito comum que cada SGBD implemente seu 
próprio conjunto de operadores, que pode ou não estar 
em conformidade com o padrão ANSI
➥SQL Server usa o operador “+” para concatenação
Implementação de BDs 85
SQL
- SELECT -
q Algumas funções comuns definidas no padrão ANSI 
SQL:2011
➥Resto da divisão: MOD(x, y)
➥Valor absoluto: ABS(x)
➥Potência: POWER(x, y)
➥Raiz quadrada: SQRT(x)
➥Exponencial: EXP(x)
➥Logaritmo natural: LN(x)
➥Teto: CEIL(x) ou CEILING(x)
➥Piso: FLOOR(x)
Implementação de BDs 86
SQL
- SELECT -
q Expressões e constantes podem aparecer como colunas 
do resultado, na cláusula SELECT de uma consulta
➥Exemplo:
SELECT 'F', 'Sr(a) ' || nome
FROM Funcionarios
WHERE FLOOR(salario) >= 2000
➥Resultado: Coluna 1 Coluna 2
F Sr(a) Alex
F Sr(a) João
Os nomes das colunas do resultado
serão dependentes do SGBD
Implementação de BDs 87
SQL
- ORDER BY -
q Cláusula para ordenar os resultados de um SELECT
q A cláusula ORDER BY é opcional
➥Quando existir deve ser a última do SELECT
➫Sempre!
q O critério de ordenação deve ser uma lista de colunas
ou expressões envolvendo colunas
➥Quando a ordenação for ascendente, deve-se utilizar a 
palavra ASC após cada coluna, ou não informar nada
➥Quando a ordenação for descendente, deve-se utilizar a 
palavra DESC após cada coluna
Implementação de BDs 88
SQL
- ORDER BY -
q Exemplo:
➥Consulta que retorne os nomes e salários de todos os
funcionários ordenados pelo nome do funcionário
➥Consulta em SQL:
SELECT nome, salario
FROM Funcionarios
ORDER BY nome
➥Resultado: nome salario
Alex 2000
João 2500
José 1500
Rômulo 1000
Implementação de BDs 89
SQL
- ORDER BY -
q Exemplo:
➥Consulta que retorne os nomes dos funcionários
ordenados pelos salários de forma decrescente
➥Consulta em SQL:
SELECT nome
FROM Funcionarios
ORDER BY salario DESC
➥Resultado: nome
João
Alex
José
Rômulo
As colunas da cláusula ORDER BY
não precisam aparecer nos resultados!
Implementação de BDs 90
SQL
- Produto Cartesiano -
q Operação vinda diretamente da teoria dos conjuntos
➥Cada linha de uma tabela é combinada com todas as 
linhas da outra tabela, formando uma linha no resultado 
contendo todas as colunas das duas tabelas
q Sintaxe
➥Lista de tabelas separadas por vírgula na cláusula FROM 
do comando SELECT
Implementação de BDs 91
SQL
- Produto Cartesiano -
q Exemplo:
➥Tabela “Funcionarios”
➥Tabela “Departamentos”
matricula nome salario lotacao
1 Rômulo 1000 1
2 Alex 2000 2
3 João 2500 2
codigo nome_dep
1 Gerência
2 Financeiro
3 Vendas
Implementação de BDs 92
SQL
- Produto Cartesiano -
q Exemplo:
SELECT *
FROM Funcionarios, Departamentos
Implementação de BDs 93
SQL
- Produto Cartesiano -
q Exemplo:
➥Resultado
matricula nome salario lotacao codigo nome_dep
1 Rômulo 1000 1 1 Gerência
1 Rômulo 1000 1 2 Financeiro
1 Rômulo 1000 1 3 Vendas
2 Alex 2000 2 1 Gerência
2 Alex 2000 2 2 Financeiro
2 Alex 2000 2 3 Vendas
3 João 2500 2 1 Gerência
3 João 2500 2 2 Financeiro
3 João 2500 2 3 Vendas
Implementação de BDs 94SQL
- Produto Cartesiano -
q Exemplo:
SELECT nome, nome_dep
FROM Funcionarios, Departamentos
WHERE salario > 1500
Implementação de BDs 95
SQL
- Produto Cartesiano -
q Exemplo:
➥Resultado
nome nome_dep
Alex Gerência
Alex Financeiro
Alex Vendas
João Gerência
João Financeiro
João Vendas
Implementação de BDs 96
SQL
- Junção -
q Geralmente o produto cartesiano traz resultados
“estranhos”
➥Nos exemplos anteriores, não faz sentido “combinar”
departamentos diferentes da lotação do funcionário
q Um produto cartesiano pode ser combinado com a 
cláusula WHERE (filtragem de linhas) para produzir
resultados mais refinados
q Quando um produto cartesiano (na cláusula FROM) está
seguido de uma condição de ligação entre tabelas (na
cláusula WHERE)
➥Chamamos essa operação de JUNÇÃO
Implementação de BDs 97
SQL
- Junção -
q Exemplo:
SELECT nome, nome_dep
FROM Funcionarios, Departamentos
WHERE lotacao = codigo
Implementação de BDs 98
SQL
- Junção -
q Exemplo:
➥Resultado
nome nome_dep
Rômulo Gerência
Alex Financeiro
João Financeiro
Implementação de BDs 99
SQL
- Junção -
q Existe uma sintaxe específica para junção:
SELECT lista_de_colunas
FROM tabela1
[INNER] JOIN tabela2
ON condição
Implementação de BDs 100
SQL
- Junção -
q Exemplo:
SELECT nome, nome_dep
FROM Funcionarios, Departamentos
WHERE lotacao = codigo
�
SELECT nome, nome_dep
FROM Funcionarios
JOIN Departamentos
ON lotacao = codigo
(1)
(2)
Implementação de BDs 101
SQL
- Junção -
q As duas sintaxes são equivalentes
➥Retornam exatamente os mesmos resultados
q Em teoria, a sintaxe (2) é mais rápida
➥Quando um SGBD recebe uma consulta na sintaxe (1), ele
a converte para uma representação similar à sintaxe (2) 
durante a fase de otimização
q Na prática, a diferença no tempo de otimização é
desprezível
➥Escolher uma sintaxe em detrimento da outra transforma-
se em uma questão de gosto pessoal
Implementação de BDs 102
SQL
- Junção Externa -
q É um tipo de junção em que as linhas de uma tabela podem 
ser inseridas no resultado ainda que não haja 
correspondência delas com linhas da outra tabela
q Três tipos
➥ Left Outer Join
➫As linhas da tabela à esquerda sempre serão incluídas no 
resultado
➥Right Outer Join
➫As linhas da tabela à direita sempre serão incluídas no resultado
➥ Full Outer Join
➫As linhas das duas tabelas sempre serão incluídas no resultado
q A não correspondência com a outra tabela será sempre 
completada com valores nulos
Implementação de BDs 103
SQL
- Junção Externa -
q Sintaxe:
SELECT lista_de_colunas
FROM tabela1
LEFT | RIGHT | FULL [OUTER] JOIN tabela2
ON condição
Implementação de BDs 104
SQL
- Junção Externa -
q Exemplo:
➥Tabela “Funcionarios”
➥Tabela “Departamentos”
matricula nome salario lotacao
1 Rômulo 1000 1
2 Alex 2000 2
3 João 2500 NULO
codigo nome_dep
1 Gerência
2 Financeiro
3 Vendas
Implementação de BDs 105
SQL
- Junção Externa -
q Exemplo:
➥Consulta
SELECT nome, nome_dep
FROM Funcionarios
LEFT OUTER JOIN Departamentos
ON lotacao = codigo
➥Resultado nome nome_dep
Rômulo Gerência
Alex Financeiro
João NULO
Implementação de BDs 106
SQL
- Junção Externa -
q Exemplo:
➥Consulta
SELECT nome, nome_dep
FROM Funcionarios
RIGHT OUTER JOIN Departamentos
ON lotacao = codigo
➥Resultado nome nome_dep
Rômulo Gerência
Alex Financeiro
NULO Vendas
Implementação de BDs 107
SQL
- Junção Externa -
q Exemplo:
➥Consulta
SELECT nome, nome_dep
FROM Funcionarios
FULL OUTER JOIN Departamentos
ON lotacao = codigo
➥Resultado nome nome_dep
Rômulo Gerência
Alex Financeiro
João NULO
NULO Vendas
Implementação de BDs 108
SQL
- Apelidos de Tabelas -
q É possível “apelidar” as tabelas envolvidas nas suas 
consultas
➥Ou seja, dar novos nomes às tabelas, durante a execução 
da sua consulta
q Bastante útil para
➥Alterar os nomes para facilitar desenvolvimento da 
consulta
➥Referenciar colunas que originalmente possuem o mesmo 
nome (ambíguas)
➥Permitir auto-junção
Implementação de BDs 109
SQL
- Apelidos de Tabelas -
q Sintaxe:
tabela AS apelido
ou
tabela apelido
Implementação de BDs 110
SQL
- Apelidos de Tabelas -
q Exemplo:
➥Retorne o nome do funcionário e o nome do departamento
onde ele está lotado
➥Consulta em SQL:
SELECT f.nome, d.nome
FROM Funcionarios f,
Departamentos d
WHERE codigo = lotacao
Implementação de BDs 111
SQL
- Apelidos de Tabelas -
q Exemplo:
➥Resultado:
➥Importante!
➫Quais os nomes das colunas do resultado?
➱Como as colunas são ambíguas, não há como saber
➱Será dependente do SGBD
➱Muito provavelmente seria algo próximo de f.nome e d.nome
f.nome d.nome
Rômulo Gerência
Alex Financeiro
João Financeiro
Implementação de BDs 112
SQL
- Apelidos de Colunas -
q É possível “apelidar” as colunas dos resultados das suas
consultas
➥Ou seja, dar novos nomes a colunas do seu resultado
q Bastante útil para
➥Alterar os nomes de acordo com a necessidade de uso
➥Desambiguar colunas que originalmente possuem o 
mesmo nome
➥Nomear colunas com valores obtidos por expressões ou 
constantes
q A sintaxe é exatamente igual à dos apelidos de tabelas
Implementação de BDs 113
SQL
- Apelidos de Colunas -
q Exemplo:
➥Retorne o nome do funcionário e o nome do departamento
onde ele está lotado
➥Consulta em SQL:
SELECT f.nome AS nome_fun,
d.nome AS nome_dep
FROM Funcionarios f,
Departamentos d
WHERE codigo = lotacao
Implementação de BDs 114
SQL
- Apelidos de Colunas -
q Exemplo:
➥Resultado:
➥Importante!
➫No resultado da consulta, os nomes das colunas 
passam a ser nome_fun e nome_dep
➱Garantido!
nome_fun nome_dep
Rômulo Gerência
Alex Financeiro
João Financeiro
Implementação de BDs 115
SQL
- DISTINCT -
q No Modelo Relacional, assim como na Teoria dos 
Conjuntos na qual ele é fundamentado, não há repetição
de elementos
➥Mas os SGBDs comerciais permitem linhas repetidas
q O comando DISTINCT, parte da cláusula SELECT, 
serve para omitir linhas repetidas do resultado das 
consultas
Implementação de BDs 116
SQL
- DISTINCT -
q Exemplo:
➥Retorne o nome dos departamentos que possuem 
funcionários lotados
➥Se executarmos a consulta em SQL:
SELECT d.nome as nome_dep
FROM Funcionarios f,
Departamentos d
WHERE codigo = lotacao
Implementação de BDs 117
SQL
- DISTINCT -
q Exemplo:
➥Obteremos o resultado:
➥O departamento “Financeiro” está repetido!
➫Como resolver?
➱Utilizando DISTINCT!
nome_dep
Gerência
Financeiro
Financeiro
Implementação de BDs 118
SQL
- DISTINCT -
q Exemplo:
➥Se alterarmos a consulta em SQL para:
SELECT DISTINCT d.nome as nome_dep
FROM Funcionarios f, Departamentos d
WHERE codigo = lotacao
➥Obteremos o resultado:
➥Sem repetição de linhas!
nome_dep
Gerência
Financeiro
Implementação de BDs 119
SQL
- União -
q Assim como na teoria dos conjuntos, é possível utilizar a 
operação de união entre tabelas
➥A união entre dois conjuntos é um conjunto com todos os
elementos dos conjuntos originais
➥A união entre duas tabelas é uma relação com todas as 
linhas das tabelas originais
q Restrições para o uso da união
➥As tabelas envolvidas devem obrigatoriamente ter a 
mesma quantidade de colunas
➥As colunas de mesma posição em cada tabela devem ter
tipos de dados compatíveis entre si
Implementação de BDs 120
SQL
- União -
q Sintaxe:
SELECT …
UNION [ALL]
SELECT …
➥É executada uma união entre o resultado do primeiro
SELECT e o resultado do segundo SELECT
Implementação de BDs 121
SQL
- União -
q Tabela “Funcionarios”
q Tabela “Clientes”
matricula nome_func end_func salario1 Rômulo Rua ABC 1000
2 Alex Rua DEF 2000
3 João Av XYZ 2500
4 Pedro Rua JKL 1500
codigo nome_cliente end_cliente
1 Ana Rua GHI
2 Maria Av TUV
3 Pedro Rua JKL
Implementação de BDs 122
SQL
- União -
q Problema: deseja-se criar uma mala direta para todos os 
funcionários e clientes da empresa. Como conseguir os 
nomes e endereços de todos os funcionários e clientes 
juntos?
➥Solução: usar união!
Implementação de BDs 123
SQL
- União -
q Solução:
SELECT nome_func, end_func
FROM Funcionarios
UNION
SELECT nome_cliente, end_cliente
FROM Clientes
Implementação de BDs 124
SQL
- União -
q Resultado:
➥Importante!
➫Os nomes das colunas no resultado serão os nomes
das colunas do primeiro SELECT
➱Apelidos para as colunas podem ser utilizados para que os
nomes das colunas fiquem mais adequados
nome_func end_func
Rômulo Rua ABC
Alex Rua DEF
João Av XYZ
Ana Rua GHI
Maria Av TUV
Pedro Rua JKL
Implementação de BDs 125
SQL
- União -
q Solução com apelidos:
SELECT nome_func AS nome,
end_func AS endereco
FROM Funcionarios
UNION
SELECT nome_cliente, end_cliente
FROM Clientes
Implementação de BDs 126
SQL
- União -
q Resultado:
nome endereco
Rômulo Rua ABC
Alex Rua DEF
João Av XYZ
Ana Rua GHI
Maria Av TUV
Pedro Rua JKL
Implementação de BDs 127
SQL
- União -
q Curiosidade
➥A operação de união remove tuplas repetidas do resultado
➫Caso não tenha percebido, “Pedro” é funcionário e 
cliente ao mesmo tempo
➱Ele foi incluído apenas uma vez no resultado do UNION!
➥Caso seja importante manter as tuplas repetidas, deve ser 
utilizado o comando UNION ALL
Implementação de BDs 128
SQL
- União -
q Solução com UNION ALL:
SELECT nome_func as nome,
end_func as endereco
FROM Funcionarios
UNION ALL
SELECT nome_cliente, end_cliente
FROM Clientes
Implementação de BDs 129
SQL
- União -
q Resultado:
q Em algumas situações, será importante preservar as 
duplicatas; em outras, será importante que elas sejam 
omitidas
➥Use UNION ou UNION ALL adequadamente!
nome endereco
Rômulo Rua ABC
Alex Rua DEF
João Av XYZ
Pedro Rua JKL
Ana Rua GHI
Maria Av TUV
Pedro Rua JKL
Implementação de BDs 130
SQL
- Valores Nulos -
q Na teoria do Modelo Relacional, valores nulos não 
existem!
➥Todo e qualquer atributo deve conter um valor
qMas os SGBDs relacionais permitem que atributos 
recebam valores nulos (desde que permitidos na 
especificação das colunas)
Implementação de BDs 131
SQL
- Valores Nulos -
q Interpretação errônea sobre o valor nulo:
➥Valor não existe
q Significado correto de um valor nulo
➥Valor existe, mas não se sabe no momento
q Uma coisa é existir, mas não se saber; outra coisa é não 
existir!
Implementação de BDs 132
SQL
- Valores Nulos -
q Tabela “Funcionarios”
matricula nome endereco salario data_nascimento
1 Rômulo Rua ABC 1000,00 04/10/1970
2 Alex Rua DEF 1500,00 06/09/1980
3 João Av XYZ 3000,00 NULO
4 Ana Rua GHI 2000,00 01/01/2000
5 Pedro Av TUV 2500,00 NULO
Implementação de BDs 133
SQL
- Valores Nulos -
q Exemplo:
➥Quais os nomes dos funcionários que não possuem data 
de nascimento informada?
SELECT nome
FROM Funcionarios
WHERE data_nascimento IS NULL
Implementação de BDs 134
SQL
- Valores Nulos -
q Resultado:
nome
João
Pedro
Implementação de BDs 135
SQL
- Valores Nulos -
q Exemplo:
➥Quais os nomes dos funcionários que possuem data de 
nascimento informada?
SELECT nome
FROM Funcionarios
WHERE data_nascimento IS NOT NULL
Implementação de BDs 136
SQL
- Valores Nulos -
q Resultado:
nome
Rômulo
Alex
Ana
Implementação de BDs 137
SQL
- Valores Nulos -
q Importante
➥Toda e qualquer comparação a nulos que não seja feita
com o operador IS (ou IS NOT) retornará valor indefinido
➫Na prática, pode-se considerar falso, pois, se é 
indefinido, não é verdadeiro e não irá para o resultado
➥A consulta
SELECT nome
FROM Funcionarios
WHERE data_nascimento = NULL
➥Retornará uma tabela vazia!
Implementação de BDs 138
SQL
- BETWEEN -
q O operador ternário BETWEEN compara se um valor 
está compreendido dentro de um intervalo fechado
informado
q Sintaxe:
valor BETWEEN inicio AND fim
Implementação de BDs 139
SQL
- BETWEEN -
q Exemplo:
➥Qual o nome dos funcionários que ganham salário entre 
R$1.500,00 e R$2.500,00?
q Solução:
SELECT nome
FROM Funcionarios
WHERE salario BETWEEN 1500 AND 2500
Implementação de BDs 140
SQL
- BETWEEN -
q A consulta do exemplo anterior é equivalente a
SELECT nome
FROM Funcionarios
WHERE salario >= 1500 AND salario <= 2500
q Os pontos extremos fazem parte do intervalo!
➥Trata-se de um intervalo fechado
Implementação de BDs 141
SQL
- BETWEEN -
q Se o problema fosse:
➥Qual o nome dos funcionários que ganham salário maior
que R$1.500,00 e menor que R$2.500,00?
q Solução:
SELECT nome
FROM Funcionarios
WHERE salario > 1500 AND salario < 2500
q BETWEEN não poderia ser utilizado!
➥Neste caso, os pontos extremos não devem fazer parte do 
intervalo
Implementação de BDs 142
SQL
- LIKE -
q O operador LIKE compara um texto a um padrão de 
caracteres
➥Padrões podem incluir caracteres comuns ou wildcards 
(curingas)
➥O texto pode ser qualquer expressão que retorne uma
string
q Sintaxe:
texto [NOT] LIKE padrao [ESCAPE caractere_de_escape]
Implementação de BDs 143
SQL
- LIKE -
q Lista de wildcards do SQL padrão:
➥Cuidado!
➫SGBDs diferentes podem trazer diferentes caracteres
como wildcards
Wildcard Descrição
% Qualquer texto de zero ou mais caracteres
_ (underscore) Um único caractere
[ ] Um único caractere dentro de um intervalo ou de 
uma lista de caracteres
[^ ] Um único caractere fora de um intervalo ou de uma 
lista de caracteres
Implementação de BDs 144
SQL
- LIKE -
q Exemplo:
➥Quais as matrículas e nomes dos funcionários que
possuem “Silva” no final do nome?
q Solução:
SELECT matricula, nome
FROM Funcionarios
WHERE nome LIKE '%Silva'
Implementação de BDs 145
SQL
- LIKE -
q Exemplo:
➥Quais as matrículas e nomes dos funcionários que
possuem um “A” ou um “Z” como primeira letra do nome e 
qualquer letra de “C” a “G” como terceira letra?
q Solução:
SELECT matricula, nome
FROM Funcionarios
WHERE nome LIKE '[AZ]_[C-G]%'
Implementação de BDs 146
SQL
- LIKE -
q Exemplo:
➥Quais as matrículas e nomes dos funcionários que
possuem um “%” dentro de seu nome?
➫Provavelmente vindo de uma digitação incorreta!
q Solução:
SELECT matricula, nome
FROM Funcionarios
WHERE nome LIKE '%?%%' ESCAPE '?'
➥O caractere de escape faz com que o próximo caractere
seja considerado um caractere comum!
Implementação de BDs 147
SQL
- Funções de Agregação -
q Operação de Agregação
➥Reduzir um conjunto de dados a um valor que represente 
o conjunto inteiro
➫De acordo com o efeito que se deseja
➥Por exemplo:
➫Notas: 10, 9.5, 8, 7.5, 9
➫Qual a média das notas?
➱Média = (10 + 9.5 + 8 + 7.5 + 9) / 5 = 8.8
➫A média das notas é uma agregação!
➱Conjunto original de 5 valores foi reduzido a apenas um
Implementação de BDs 148
SQL
- Funções de Agregação -
q O padrão ANSI SQL:2011 define 15 funções de 
agregação, mas raramente os SGBDs comerciais 
implementam todas elas
➥Podem, inclusive, implementar funções proprietárias fora 
do padrão
q Funções de agregação mais comuns
➥SUM: soma
➥COUNT: contagem
➥AVG: média
➥MAX: máximo
➥MIN: mínimo
Implementação de BDs 149
SQL
- Funções de Agregação -
q Exemplo:
➥Qual o valor da folha de pagamento da empresa (soma 
dos salários de todos os funcionários)?
SELECT SUM(salario)
FROM Funcionarios
➥Qual a média salarial dos funcionários?
SELECT AVG(salario)
FROMFuncionarios
Implementação de BDs 150
SQL
- Funções de Agregação -
q Exemplo:
➥Qual o maior de todos os salários?
SELECT MAX(salario)
FROM Funcionarios
➥Qual o menor de todos os salários?
SELECT MIN(salario)
FROM Funcionarios
Implementação de BDs 151
SQL
- Funções de Agregação -
q Exemplo:
➥Qual a quantidade total de funcionários?
SELECT COUNT(*)
FROM Funcionarios
➱ “COUNT(*)” significa contagem de linhas
➥Qual a quantidade de funcionários que ganham mais de 
R$2.000,00?
SELECT COUNT(*)
FROM Funcionarios
WHERE salario > 2000
Implementação de BDs 152
SQL
- Funções de Agregação -
q Exemplo:
➥Qual a quantidade total de salários?
SELECT COUNT(salario)
FROM Funcionarios
➱ Neste caso, os salários nulos não são contados!
➥Qual a quantidade total de salários diferentes?
SELECT COUNT(DISTINCT salario)
FROM Funcionarios
➱ Neste caso, apenas os salários distintos (e não nulos) 
serão contados!
Implementação de BDs 153
SQL
- Funções de Agregação -
q É comum o uso de apelidos
➥Nomes mais adequados e independentes do SGBD
q Exemplo:
➥Qual a folha de pagamento total e média salarial dos 
funcionários?
SELECT SUM(salario) AS folha_pgto,
AVG(salario) AS media_salarial
FROM Funcionarios
Implementação de BDs 154
SQL
- Agrupamento -
q A operação de agrupamento cria grupos de linhas
➥Uma vez que os grupos estão criados, operações
específicas podem ser aplicadas a eles
q Exemplo:
➥Qual a média salarial dos funcionários por lotação?
SELECT lotacao, AVG(salario)
FROM Funcionarios
GROUP BY lotacao
➥Qual o maior e o menor salário de cada lotacao?
SELECT lotacao, MAX(salario), MIN(salario)
FROM Funcionarios
GROUP BY lotacao
Implementação de BDs 155
SQL
- Agrupamento -
q Exemplo:
➥Quais as lotações com média salarial dos funcionários
maior que R$2.000,00?
SELECT lotacao
FROM Funcionarios
GROUP BY lotacao
HAVING AVG(salario) > 2000
➥A cláusula HAVING efetua filtragens dentro de cada grupo
criado
➫Filtragens no HAVING podem conter funções de 
agregação
Implementação de BDs 156
SQL
- Agrupamento -
q Exemplo:
➥Quais os nomes dos departamentos com média salarial
dos funcionários maior que R$2.000,00?
SELECT D.nome
FROM Funcionarios F
JOIN Departamentos D ON F.lotacao = D.codigo
GROUP BY D.nome
HAVING AVG(F.salario) > 2000
➥Agrupamento pode ser utilizado em conjunto com as 
outras operações do SELECT (como a junção)
Implementação de BDs 157
SQL
- Sub-consultas -
q Algumas consultas precisam buscar valores já presentes
no BD para utilizá-los em suas condições de 
comparação
➥Assim, podemos inserir consultas SQL dentro de outras
consultas SQL
➫Sub-consultas
Implementação de BDs 158
SQL
- Sub-consultas -
q Operadores utilizados com sub-consultas
➥IN: testa se o valor a ser comparado está dentro dos 
resultados da sub-consulta (relação de pertinência)
➥{=|<>|>|<|>=|<=} ANY: testa se a operação desejada é
verdadeira para qualquer dos resultados da sub-consulta
➥{=|<>|>|<|>=|<=} ALL: testa se a operação desejada é
verdadeira para todos os resultados da sub-consulta
➥EXISTS: testa se a sub-consulta possui resultados
➫Os operadores possuem suas formas negativas
➱NOT IN
➱NOT EXISTS
Implementação de BDs 159
SQL
- Sub-consultas -
q Exemplos
➥Qual o nome do funcionário de maior salário?
SELECT nome
FROM Funcionarios
WHERE salario IN (SELECT max(salario) FROM Funcionarios)
➥Qual o nome do funcionário de segundo maior salário?
SELECT nome
FROM Funcionarios
WHERE salario IN
(SELECT max(salario)
FROM Funcionarios
WHERE salario < (SELECT max(salario) FROM Funcionarios))
Implementação de BDs 160
SQL
- Sub-consultas -
q O operador IN (e sua forma negativa NOT IN) pode ser
utilizado também para avaliar a pertinência de um valor a um 
conjunto arbitrário informado
➥O conjunto deve ser delimitado por parênteses, com elementos 
separados por vírgula
q Exemplo:
➥Quais os nomes dos funcionários que trabalham nos
departamentos de código 1, 5, 8 e 15?
SELECT nome
FROM Funcionarios
WHERE lotacao IN (1, 5, 8, 15)
Implementação de BDs 161
SQL
- Sub-consultas -
q Se a sub-consulta tiver comparações de valores com 
dados da consulta externa
➥Ela será uma sub-consulta correlacionada ou correlata
q Problema: o tempo de processamento pode ser alto
➥Uma sub-consulta não-correlacionada pode ser executada
uma única vez e ter seus valores avaliados pela consulta
externa tantas vezes quanto forem necessárias
➥Uma sub-consulta correlacionada deverá ser executada
para cada avaliação necessária na consulta externa
➫Ou seja, será executada várias vezes durante a 
execução da consulta externa
➫Sub-consultas correlacionadas devem ser usadas com 
parcimônia
Implementação de BDs 162
SQL
- Sub-consultas -
q Exemplo
➥Tabela “Funcionarios”
➥Tabela “Projetos” Tabela “Func_Proj”
matricula nome salario lotacao
1 Rômulo 1000 1
2 Alex 2000 2
3 João 2500 2
codigo nome_pro
1 Projeto X
2 Projeto Y
matricula codigo
1 1
1 2
2 2
Implementação de BDs 163
SQL
- Sub-consultas -
q Exemplos
➥Quais o nomes dos funcionários que estão trabalhando
em algum projeto?
SELECT nome
FROM Funcionarios F
WHERE EXISTS (SELECT matricula FROM Func_Proj
WHERE matricula = F.matricula)
➥A consulta acima poderia ser reescrita como
SELECT nome
FROM Funcionarios
WHERE matricula IN (SELECT matricula FROM Func_Proj)
Implementação de BDs 164
SQL
- Sub-consultas -
q Exemplos
➥Outra forma de resolver a consulta de funcionário de 
maior salário:
SELECT nome
FROM Funcionarios F
WHERE salario > ALL (SELECT salario FROM Funcionarios
WHERE matricula <> F.matricula)
q Quando precisar utilizar sub-consultas correlacionadas, 
avalie com bastante atenção seu desempenho
Implementação de BDs 165
SQL
- Visões -
q Os SGBD relacionais suportam um tipo de objeto
chamado visão
➥Tabelas virtuais
➫Baseadas em consultas SQL
➥É comparável a promover uma consulta SQL ao nível de 
tabela
q Sintaxe
CREATE VIEW nome_visão [(apelidos_de_colunas)] AS
SELECT …
Implementação de BDs 166
SQL
- Visões -
q Exemplo
➥Suponha que a seguinte consulta SQL seja
demasiadamente utilizada
SELECT nome, nome_dep
FROM Funcionarios
JOIN Departamentos ON lotacao=codigo
➥Podemos criar uma visão chamada Func_Dep_View, com 
o comando
CREATE VIEW Func_Dep_View AS
SELECT nome, nome_dep
FROM Funcionarios
JOIN Departamentos ON lotacao=codigo
Implementação de BDs 167
SQL
- Visões -
q Exemplo
➥Assim, sempre que o resultado da consulta anterior for 
necessário, bastará executar a consulta
SELECT * FROM Func_Dep_View
q Visões funcionam como tabelas (virtuais)!
➥Podem participar de junções, agrupamentos, sub-
consultas, etc
SELECT nome_dep, COUNT(*) as quantidade
FROM Func_Dep_View
GROUP BY nome_dep
Implementação de BDs 168
SQL
- Visões -
q Visões podem até mesmo receber atualizações, desde 
que alguns critérios sejam respeitados
➥A consulta só pode possuir uma tabela base
➥Todas as colunas necessárias a atualizações devem estar 
presentes na visão
➥A consulta não pode possuir DISTINCT, GROUP BY, 
funções de agregação, colunas derivadas ou quaisquer 
recursos que não permitam referenciar as colunas 
originais da tabela
➥Regras específicas podem ser impostas por cada SGBD
Implementação de BDs 169
SQL
- Visões -
q A seguinte visão é atualizável:
CREATE VIEW Func_Salario_Maior_1000 AS
SELECT matricula, nome, salario, lotacao
FROM Funcionarios
WHERE salario > 1000
q O que acontecerá quando o seguinte INSERT for 
executado?
INSERT INTO Func_Salario_Maior_1000
VALUES (9999, 'Maria', 900, 2)
Implementação de BDs 170
SQL
- Visões -
q Vantagens
➥O usuário final verá apenas o resultado davisão, não
precisando saber os detalhes de como estão construídas
as tabelas ou de como foi especificada a consulta
➫Encapsulamento
➫Desacoplamento
➫Melhor manutenibilidade
➥Visões podem receber permissões de acesso
personalizadas
➫Desta forma, a administração da política de segurança
do banco de dados pode ser facilitada
➱Ver DCL mais à frente
Implementação de BDs 171
SQL
- Auto-Numeração -
q O padrão ANSI SQL:2011 permite a criação de auto-
numerações (numerações automáticas) de duas formas 
distintas:
➥Colunas IDENTITY
➥Objetos SEQUENCE
q Cuidado!
➥ É comum que os SGBDs comerciais implementem auto-numerações 
com sintaxes fora do padrão, com o uso de comandos proprietários
➫MySQL permite colunas com a propriedade AUTO_INCREMENT
➫ProstgreSQL possui tipo de dados SERIAL
➫SQL Server utiliza sintaxe fora do padrão para sua implementação 
da propriedade IDENTITY
Implementação de BDs 172
SQL
- IDENTITY -
q Propriedade de colunas que indica que seus valores 
serão gerados automaticamente através de auto-
numeração
q Sintaxe:
nome_coluna tipo_dados
GENERATED [ALWAYS | BY DEFAULT]
AS IDENTITY [([START WITH valor_inicial]
[INCREMENT BY valor_incremento]
[MINVALUE valor_minimo | NO MINVALUE]
[MAXVALUE valor_minimo | NO MAXVALUE]
[CYCLE | NO CYCLE]
[CACHE quantidade | NO CACHE])]
Implementação de BDs 173
SQL
- IDENTITY -
q Exemplos
➥Coluna “matricula” do tipo bigint, sempre gerada por auto-numeração, 
começando de 1, sem limites, e incremento de 1
matricula bigint GENERATED ALWAYS
AS IDENTITY (START WITH 1 INCREMENT BY 1)
➥Coluna “lotacao” do tipo int, gerada por auto-numeração apenas 
quando um valor não for informado, começando de 30, no intervalo de 
0 a 100, incremento de 10 e com ciclo (quando passar do último, 
voltar ao primeiro)
lotacao int GENERATED BY DEFAULT
AS IDENTITY (START WITH 30 INCREMENT BY 10
MINVALUE 0 MAXVALUE 100 CYCLE)
Implementação de BDs 174
SQL
- SEQUENCE -
q Objeto que permite a manipulação de uma sequência de 
números inteiros
➥Bastante útil para criar auto-numerações independentes, 
utilizadas sob-demanda
q Sintaxe:
CREATE SEQUENCE nome_sequence
[AS tipo_dados]
[START WITH valor_inicial]
[INCREMENT BY valor_incremento]
[MINVALUE valor_minimo | NO MINVALUE]
[MAXVALUE valor_minimo | NO MAXVALUE]
[CYCLE | NO CYCLE]
[CACHE quantidade | NO CACHE]
Implementação de BDs 175
SQL
- SEQUENCE -
q Exemplo
➥Criar uma sequência de valores do tipo int de 100 a 200, 
incrementada de 1 em 1, com valor inicial 150 e com ciclo
CREATE SEQUENCE seq_100_200
AS int
START WITH 150
INCREMENT BY 1
MINVALUE 100
MAXVALUE 200
CYCLE
Implementação de BDs 176
SQL
- SEQUENCE -
q Uma vez criado, o valor atual da SEQUENCE pode ser 
manipulado com o uso do operador NEXT VALUE FOR
➥A cada uso de NEXT VALUE FOR, o valor da SEQUENCE 
é incrementado
q Exemplos
SELECT NEXT VALUE FOR seq_100_200
INSERT INTO Funcionarios(matricula, nome)
VALUES (NEXT VALUE FOR seq_100_200, 'José’)
Valor retornado: 150
Valor retornado: 151
Implementação de BDs 177
SQL
- DCL -
q Permite administrar permissões de acesso em BDs 
relacionais
q Comandos básicos:
➥GRANT
➫Concede permissões a objetos
➥REVOKE
➫Revoga permissões anteriormente concedidas a 
objetos
Implementação de BDs 178
SQL
- GRANT -
q Comando para conceder permissões de acesso
q Sintaxe:
GRANT lista_de_permissões | ALL [PRIVILEGES]
ON objeto
TO lista_de_usuários
[WITH GRANT OPTION]
Implementação de BDs 179
SQL
- GRANT -
q As permissões possíveis dependem do tipo de objeto
➥Para tabelas, permissões comuns são
➫SELECT
➫INSERT
➫UPDATE
➫DELETE
➫REFERENCES
➥Permissões podem ser aplicadas a colunas específicas
➫permissão [(lista_de_colunas)]
q A opção “WITH GRANT OPTION” garante que o usuário 
possa conceder a outros usuários as permissões que 
recebeu
Implementação de BDs 180
SQL
- GRANT -
q Exemplos
➥Concedendo permissão apenas de inserção e remoção 
sobre a tabela “Departamentos” para a usuária “Maria”:
GRANT INSERT, DELETE
ON Departamentos
TO Maria
➥Concedendo permissão de leitura apenas para as colunas 
“matricula” e “nome” da tabela “Funcionarios” para o 
usuário “Jose”:
GRANT SELECT (matricula, nome)
ON Funcionarios
TO Jose
Implementação de BDs 181
SQL
- GRANT -
q Exemplos
➥Concedendo todas as permissões de acesso sobre a 
tabela “Funcionarios” para o usuário “Joao”, dando a ele a 
opção de repassar as permissões para outros usuários:
GRANT ALL
ON Funcionarios
TO Joao
WITH GRANT OPTION
Implementação de BDs 182
SQL
- REVOKE -
q Comando para revogar permissões de acesso
q Sintaxe:
REVOKE [GRANT OPTION FOR]
lista_de_permissões | ALL [PRIVILEGES]
ON objeto
FROM lista_de_usuários
[CASCADE]
Implementação de BDs 183
SQL
- REVOKE -
q A opção “GRANT OPTION FOR” remove não a 
permissão, mas a possibilidade de repasse da 
permissão a outros usuários
➥Anula a opção “WITH GRANT OPTION” do GRANT
q A cláusula “CASCADE” garante que a revogação das 
permissões seja propagada a todos os usuários que
receberam essas permissões e partir do usuário em
questão
➥Desde que ele tenha recebido um GRANT com a opção
“WITH GRANT OPTION”
Implementação de BDs 184
SQL
- REVOKE -
q Exemplos
➥Revogando qualquer privilégio de leitura à tabela
“Departamentos” à usuária “Maria”:
REVOKE SELECT
ON Departamentos
FROM Maria
➥Revogando toda e qualquer permissão concedida ao
usuário “Jose” sobre a tabela “Funcionarios”:
REVOKE ALL
ON Funcionarios
FROM Jose
Prof. Rômulo Alencar Implementação de BDs 185
INDEXAÇÃO
Parte III
Implementação de BDs 186
Indexação
- Conceitos Básicos -
q Índices
➥Índices são estruturas adicionais que fornecem caminhos 
secundários de acesso aos dados de uma tabela
➫Caminhos potencialmente mais eficientes do que uma 
varredura sequencial na tabela
➫São especialmente úteis quando o resultado da busca 
é uma pequena fração do total de linhas da tabela
➥Eles permitem acesso com base em chaves de busca
➫Chaves de busca são compostas por colunas da tabela
➥Uma entrada de índice corresponde a um par composto 
pela chave de busca e um ponteiro para o dado 
correspondente
➫<Key, Pointer>
Implementação de BDs 187
Indexação
- Conceitos Básicos -
q Admita a consulta:
SELECT *
FROM Funcionarios
WHERE matricula = 1000
q O caminho de acesso primário para obter a(s) linha(s) 
desejada(s) seria executar uma varredura na tabela 
(table scan)
➥Posicionar o ponteiro de leitura na primeira linha
➥Se o valor da coluna matricula for o desejado (1000)
➫Inserir a linha no resultado
➥Posicionar o ponteiro de leitura na próxima linha
➥Enquanto houver linhas, repetir o processo
Implementação de BDs 188
Indexação
- Conceitos Básicos -
q Admita a consulta:
SELECT *
FROM Funcionarios
WHERE matricula = 1000
q Caso existisse um índice definido sobre a coluna 
matricula (chave de busca), ele seria um caminho de 
acesso alternativo
➥Buscar o valor de chave de busca desejado (1000) nas 
entradas do índice
➥Para cada entrada encontrada, utilizar seu ponteiro para 
obter a(s) linha(s) apontada(s)
➥Inserir cada linha obtida no resultado
Implementação de BDs 189
Indexação
- Conceitos Básicos -
q Cabe ao SGBD avaliar os custos de execução dos 
diferentes caminhos de acesso disponíveis e executar 
aquele que se mostrar o mais eficiente
q Índices geralmente melhoram o desempenho das 
consultas e são largamente utilizados
➥Deve-se, entretanto, avaliar os planos de execução das 
consultas para garantir que o índice está realmente sendo 
utilizado pelo SGBD, pois sua manutenção irá degradar o 
desempenho das atualizações
➫Um índice pode melhorar o desempenho das consultas
➫Um índice sempre piorao desempenho das 
atualizações
Implementação de BDs 190
Indexação
- Classificação de Índices -
q Classificação de índices
Índice ordenado
Índice hash
Índice Primário
Índice Secundário
Denso
Esparso
�
Implementação de BDs 191
Indexação
- Classificação de Índices -
q Classificação de índices
➥Índice ordenado
➫Garante que as linhas obtidas através dele estão 
ordenadas por sua chave de busca
➫Geralmente implementado com o uso de estruturas de 
árvore
Índice ordenado
Índice hash
Índice Primário
Índice Secundário
Denso
Esparso
�
Implementação de BDs 192
Indexação
- Classificação de Índices -
q Classificação de índices
➥Índice Primário
➫Ordem das chaves de busca determina ordem física 
das linhas da tabela
➫Apenas um índice primário pode existir por tabela
Índice ordenado
Índice hash
Índice Primário
Índice Secundário
Denso
Esparso
�
Implementação de BDs 193
Indexação
- Classificação de Índices -
q Classificação de índices
➥Índice Primário
➫Índices densos possuem uma entrada para cada linha 
da tabela
➫Índices esparsos possuem entradas apenas para a 
primeira linha de cada página da tabela
Índice ordenado
Índice hash
Índice Primário
Índice Secundário
Denso
Esparso
�
Implementação de BDs 194
Indexação
- Classificação de Índices -
q Índice Primário (Clustered Index)
➥Ordem das chaves de busca determina ordem física das 
linhas da tabela
Índice primário denso definido sobre a coluna matrícula
Fonte: Silberschatz/Korth/Sudarshan
Implementação de BDs 195
Indexação
- Classificação de Índices -
q Índice Primário (Clustered Index)
➥Ordem das chaves de busca determina ordem física das 
linhas da tabela
Índice primário esparso definido sobre a coluna matrícula
Fonte: Silberschatz/Korth/Sudarshan
Implementação de BDs 196
Indexação
- Classificação de Índices -
q Classificação de índices
➥Índice Secundário
➫Ordem das chaves de busca não determina ordem 
física das linhas da tabela
➫Uma tabela pode possuir vários índices secundários
➫Todo índice secundário é denso
Índice ordenado
Índice hash
Índice Primário
Índice Secundário
Denso
Esparso
�
Implementação de BDs 197
Indexação
- Classificação de Índices -
q Índice Secundário
➥Ordem das chaves de busca não determina ordem física 
das linhas da tabela
Índice secundário definido sobre a coluna salário
Fonte: Silberschatz/Korth/Sudarshan
Implementação de BDs 198
Indexação
- Classificação de Índices -
q Índice Secundário
➥Ordem das chaves de busca não determina ordem física 
das linhas da tabela
nível de indireção
Índice secundário definido sobre a coluna salário
Fonte: Silberschatz/Korth/Sudarshan
Implementação de BDs 199
Indexação
- Classificação de Índices -
q Níveis de Indireção
➥Índices primários e secundários podem ser definidos 
sobre colunas chaves ou não-chaves
➫Com ou sem repetição de valores
➥Caso a chave de busca admita repetição, para que a 
estrutura de índice não armazene entradas com chaves 
repetidas, níveis de indireção podem ser utilizados
Implementação de BDs 200
Indexação
- Classificação de Índices -
q Classificação de índices
➥Índice hash
➫É organizado internamente como um arquivo hash
➫Entradas de índice são armazenadas em buckets após 
a aplicação de uma função hash
Índice ordenado
Índice hash
Índice Primário
Índice Secundário
Denso
Esparso
�
Implementação de BDs 201
Indexação
- Classificação de Índices -
q Índice Hash
função hash utilizada:
soma dos dígitos MOD 8
Índice hash definido sobre a coluna matrícula Fonte: Silberschatz/Korth/Sudarshan
Implementação de BDs 202
Indexação
- Classificação de Índices -
q Índice Hash
Índice hash definido sobre a coluna matrícula
overflow de bucket!
função hash utilizada:
soma dos dígitos MOD 8
Fonte: Silberschatz/Korth/Sudarshan
Implementação de BDs 203
Indexação
- Classificação de Índices -
q Índice Hash
➥Estimativa de custo: 2 acessos a disco
➫Desde que não haja overflow de buckets
➫Funções hash que gerem uma distribuição uniforme 
das chaves nos buckets são preferíveis, pois evitam o 
overflow de buckets
Implementação de BDs 204
Indexação
- Classificação de Índices -
q Índices Ordenados x Índices Hash
➥Índices hash são excelentes para consultas do tipo exact
match (condição com igualdade)
➫Um acesso a disco para o ler o bucket, outro para ler a 
página que contém a linha buscada
➱O desempenho do índice hash é bastante impactado pela 
ocorrência de overflow de buckets
➥Índices ordenados são excelentes para consultas sobre 
intervalos de valores (range queries)
➫Uma vez encontrada a primeira entrada referente ao 
intervalo, as outras são lidas sequencialmente na 
estrutura de índice
➱Caso o índice seja primário, a leitura sequencial pode ocorrer 
diretamente no arquivo de dados ⟵ Por quê?
Implementação de BDs 205
Indexação
- Estruturas de Índices Ordenados -
q Árvore-B+
➥Estrutura de dados mais largamente utilizada para 
implementação de índices ordenados
➥Mantém uma alta eficiência mesmo com muitas inserções 
e remoções de dados
➥Árvore balanceada
➫Daí o “B”
➥Extensão da Árvore-B
➫Daí o “B+”
Implementação de BDs 206
Indexação
- Estruturas de Índices Ordenados -
q Árvore-B+
➥Regras de Formação
➫Por ser uma árvore balanceada, todos os caminhos da 
raiz até uma folha devem ter o mesmo tamanho
➫Uma Árvore-B+, assim como toda árvore, possui uma 
ordem (denotada por n)
➱O valor de n é calculado de acordo com os tamanhos de
➧ Página que conterá cada nó da árvore
➧ Chave de busca
➧ Ponteiro
➫Os nós que não sejam raiz e folhas são chamados de 
nós internos ou intermediários
Implementação de BDs 207
Indexação
- Estruturas de Índices Ordenados -
q Árvore-B+
➥Regras de Formação
➫Cada nó conterá n ponteiros (P1...Pn) e n-1 chaves de 
busca (K1...Kn-1):
➫Todos os nós deverão possuir preenchimento mínimo 
de ponteiros (denotado por m) de
➱m = 2, caso o nó seja raiz
➱m = ⎡n/2⎤, para todos os outros
➫Todos os nós deverão ter m-1 chaves de busca 
preenchidas
486 Chapter 11 Indexing and Hashing
through the data. Although this degradation can be remedied by reorganization
of the file, frequent reorganizations are undesirable.
TheB+-tree index structure is themostwidelyusedof several index structures
that maintain their efficiency despite insertion and deletion of data. A B+-tree
index takes the form of a balanced tree in which every path from the root of the
tree to a leaf of the tree is of the same length. Each nonleaf node in the tree has
between ⌈n/2⌉ and n children, where n is fixed for a particular tree.
We shall see that the B+-tree structure imposes performance overhead on in-
sertion and deletion, and adds space overhead. The overhead is acceptable even
for frequently modified files, since the cost of file reorganization is avoided. Fur-
thermore, since nodes may be as much as half empty (if they have the minimum
number of children), there is some wasted space. This space overhead, too, is
acceptable given the performance benefits of the B+-tree structure.
11.3.1 Structure of a B+-Tree
A B+-tree index is a multilevel index, but it has a structure that differs from that
of the multilevel index-sequential file. Figure 11.7 shows a typical node of a B+-
tree. It contains up to n − 1 search-key values K1, K2, . . . , Kn− 1, and n pointers
P1, P2, . . . , Pn. The search-key valueswithin a node are kept in sorted order; thus,
if i < j , then Ki < K j .
We consider first the structure of the leaf nodes. For i = 1, 2, . . . , n−1, pointer
Pi points to a file recordwith search-key value Ki . Pointer Pn has a special purposethat we shall discuss shortly.
Figure 11.8 shows one leaf node of a B+-tree for the instructor file, in which
we have chosen n to be 4, and the search key is name.
Now that we have seen the structure of a leaf node, let us consider how
search-key values are assigned to particular nodes. Each leaf can hold up to n − 1
values. We allow leaf nodes to contain as few as ⌈(n − 1)/2⌉ values. With n = 4
in our example B+-tree, each leaf must contain at least 2 values, and at most 3
values.
The ranges of values in each leaf do not overlap, except if there are duplicate
search-key values, in which case a value may be present in more than one leaf.
Specifically, if Li and L j are leaf nodes and i < j , then every search-key value
in Li is less than or equal to every search-key value in L j . If the B+-tree index is
used as a dense index (as is usually the case) every search-key value must appear
in some leaf node.
Now we can explain the use of the pointer Pn. Since there is a linear order on
the leaves based on the search-key values that they contain, we use Pn to chain
P1 K1 P2 Pn-1 Kn-1 Pn…
Figure 11.7 Typical node of a B+-tree.
Estrutura de nó de uma Árvore-B+ Fonte: Silberschatz/Korth/Sudarshan
Implementação de BDs 208
Indexação
- Estruturas de Índices Ordenados -
q Árvore-B+
➥Regras de Formação
➫Nos nós raiz e intermediários, cada ponteiro Pi
apontará para uma sub-árvore que conterá valores de 
chave de busca X:
➱X < Ki, se i = 1
➱Ki-1 ≤ X < Ki, se 1 < i < m
➱X ≥ Ki-1, se i = m
➫Nos nós folhas, cada ponteiro Pi apontará para:
➱Página de dados com as linhas de valor Ki, caso i < m
➱Próxima folha, caso i = n
Implementação de BDs 209
Indexação
- Estruturas de Índices Ordenados -
q Árvore-B+
Fonte: Silberschatz/Korth/Sudarshan
Armazenamento em Árvore-B+ de um índice secundário 
definido sobre a coluna nome
Implementação de BDs 210
Indexação
- Criação de Índices em SQL -
q Sintaxe genérica
➥CREATE [UNIQUE] INDEX nome ON
tabela(chave_de_busca)
➫A cláusula UNIQUE garante que não haverá repetição 
na chave de busca, inclusive na tabela
q Exemplos
➥CREATE INDEX in_funcionarios_nome ON 
Funcionarios(nome)
➥CREATE UNIQUE INDEX in_funcionarios_matricula ON 
Funcionarios(matricula)
Implementação de BDs 211
Indexação
- Criação de Índices em SQL -
q Os comandos DDL para criação de índices possuem
sintaxes levemente diferentes em cada SGBD
q Exemplos
➥Criação de índice primário no SQL Server
➫CREATE CLUSTERED INDEX in_depto_codigo ON 
Departamentos(codigo)
➥Criação de índice hash no PostgreSQL
➫CREATE INDEX in_func_matricula ON Funcionarios
USING hash (matricula)
q Estude a documentação do seu SGBD!
Implementação de BDs 212
Indexação
- Efeitos Adversos -
q Cuidado com a criação indiscriminada de índices!
➥Eles prejudicam o desempenho das atualizações!
Fonte: Faroult/Robson
Impacto de índices durante inserções no Oracle
Implementação de BDs 213
Indexação
- Efeitos Adversos -
q Cuidado com a criação indiscriminada de índices!
➥Eles ocupam espaço em disco!
Caso real de espaço ocupado por índices de uma tabela
Fonte: Faroult/Robson
Implementação de BDs 214
Indexação
- Resumo -
q Índices possuem o objetivo primário de reduzir o custo de 
acesso aos dados de uma tabela
➥Mas esse objetivo só é alcançado caso o otimizador de consultas 
indique a utilização do índice
➥Caso contrário, ele estará apenas contribuindo para prejudicar o 
desempenho das atualizações
q Em geral, índices só são vantajosos quando a consulta 
retorna uma fração pequena do total de dados
q Índices podem ser utilizados também com a finalidade de 
forçar a organização física dos dados
➥ Índices primários
➥ Podem trazer ganhos no processamento da consulta
q Cuidado com os efeitos adversos!
➥Use com parcimônia!
Prof. Rômulo Alencar Implementação de BDs 215
PROCESSAMENTO DE CONSULTAS
Parte IV
Implementação de BDs 216
Processamento de Consultas 
- Conceitos Básicos -
q Processamento de consultas consiste no conjunto de 
atividades envolvidas na extração de dados de um banco de 
dados
q As fases do processamento de uma consulta são:
➥ Parsing e tradução
➫Análise léxica, sintática e semântica da consulta
➫Tradução da consulta em SQL para a notação interna do SGBD
➥Otimização
➫Obtenção da “melhor” forma para executar a consulta, entre as 
várias formas existentes
➥ Execução
➫Execução do plano escolhido para a consulta
➫Submissão de resultados ao usuário
Implementação de BDs 217
Processamento de Consultas
- Conceitos Básicos -
q Fases do processamento de consultas
360 Chapter 13
Generator Estimator
Plan CostPlan
Query Plan Evaluator
Query Optimizer
Query Parser
Manager
Catalog
Evaluation plan
Parsed query
Query
Figure 13.1 Query Parsing, Optimization, and Execution
We will consider a number of example queries using the following schema:
Sailors(sid: integer, sname: string, rating: integer, age: real)
Reserves(sid: integer, bid: integer, day: dates, rname: string)
As in Chapter 12, we will assume that each tuple of Reserves is 40 bytes long, that
a page can hold 100 Reserves tuples, and that we have 1,000 pages of such tuples.
Similarly, we will assume that each tuple of Sailors is 50 bytes long, that a page can
hold 80 Sailors tuples, and that we have 500 pages of such tuples.
13.1 OVERVIEW OF RELATIONAL QUERY OPTIMIZATION
The goal of a query optimizer is to find a good evaluation plan for a given query. The
space of plans considered by a typical relational query optimizer can be understood
by recognizing that a query is essentially treated as a σ − π − × algebra expression,
with the remaining operations (if any, in a given query) carried out on the result of
the σ−π−× expression. Optimizing such a relational algebra expression involves two
basic steps:
Enumerating alternative plans for evaluating the expression; typically, an opti-
mizer considers a subset of all possible plans because the number of possible plans
is very large.
Estimating the cost of each enumerated plan, and choosing the plan with the least
estimated cost.
Fonte: Ramakrishnan/Gehrke
Implementação de BDs 218
Processamento de Consultas 
- Conceitos Básicos -
q Consultas escritas em linguagem SQL devem ser 
mapeadas para outras formas de representação
➥SQL é uma linguagem declarativa
q A forma de representação escolhida deve permitir a 
reescrita da consulta para a produção de versões 
equivalentes otimizadas
q A forma adotada pelos SGBDs é a Álgebra Relacional
➥A consulta SQL é convertida em uma expressão da 
Álgebra Relacional
➥A expressão da Álgebra Relacional é representada como 
uma árvore
➫Árvore de Consulta
Implementação de BDs 219
Processamento de Consultas 
- Representação de Consultas -
q Árvore de Consulta
➥Descreve um fluxo de dados com base na execução dos 
operadores da Álgebra Relacional
➫Nós folha representam as tabelas sobre as quais a 
consulta é executada
➫Nós não-folha representam os operadores da consulta
➫A raiz representa o resultado final da consulta
➫Os dados fluem das folhas até a raiz
➥Representa uma estratégia lógica de execução para a 
construção do resultado da consulta
Implementação de BDs 220
Processamento de Consultas 
- Representação de Consultas -
q Plano de Execução
➥Cada operador da Álgebra Relacional pode ser 
implementado com o uso de vários algoritmos diferentes
➥A Árvore de Consulta é uma estrutura lógica
➫Não possui a informação de como o operador será 
executado
➥Quando algoritmos específicos são definidos para os 
operadores da Árvore de Consulta, ela se torna um Plano 
de Execução
➫Representa uma estratégia física de execução para a 
construção do resultado da consulta
➥Planos de execução são otimizados pelo SGBD, com