Baixe o app para aproveitar ainda mais
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 - qOperadores ➥Á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 - qOperador 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 - qOperador 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 - qOperador 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 qOperador 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 - qOperação vinda diretamente da teoria dos conjuntos ➥As tuplas da primeira relação que estiverem também na segunda vão para o resultado qOperador 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 - qOperação vinda diretamente da teoria dos conjuntos ➥As tuplas da primeira relação que não estiverem na segunda vão para o resultado qOperador 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 - qOperaçã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 qOperador 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 - qGeralmente 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 - qOperador derivado ➥Produto cartesiano seguido de uma seleção qOperador 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 ⟗ (fulljoin) 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 FuncionariosDROP COLUMN rg 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: || qObs: É 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! qO 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 - qOperaçã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çãode BDs 94 SQL - 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 - qGeralmente 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 qQuando 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 qO 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_funcsalario 1 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 - qO 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 qOs 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 - qO 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 - qOperaçã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 - qO 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 - qOperadores 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) qQuando precisar utilizar sub-consultas correlacionadas, avalie com bastante atenção seu desempenho Implementação de BDs 165 SQL - Visões - qOs 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 qO 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 - qObjeto 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 qO 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 piora odesempenho 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 purpose thatwe 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 - qOs 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
Compartilhar