Baixe o app para aproveitar ainda mais
Prévia do material em texto
PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS Professor: Renato Augusto dos Santos Cortes PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 1 Índice Apresentação ................................................................................................................................ 7 Aula 1: Modelo relacional ............................................................................................................. 9 Introdução ............................................................................................................................. 9 Conteúdo .............................................................................................................................. 10 Conceitos do modelo relacional .................................................................................. 10 Definição matemática de relação ................................................................................. 10 Conceitos do modelo relacional .................................................................................. 11 Projeto (design) de bancos de dados .......................................................................... 12 Exemplo de diagrama de entidades e relacionamentos (modelo conceitual) .... 12 Exemplo de relações ou tabelas (modelo físico) ....................................................... 13 Conceitos .......................................................................................................................... 13 Regras de integridade do modelo relacional ............................................................. 15 Atividade Proposta .......................................................................................................... 16 Referências........................................................................................................................... 17 Exercícios de fixação ......................................................................................................... 17 Notas ........................................................................................................................................... 20 Chaves de resposta ..................................................................................................................... 20 Aula 1 ..................................................................................................................................... 20 Exercícios de fixação ....................................................................................................... 20 Aula 2: Álgebra relacional ........................................................................................................... 22 Introdução ........................................................................................................................... 22 Conteúdo .............................................................................................................................. 23 Introdução......................................................................................................................... 23 Álgebra relacional ............................................................................................................ 23 Operadores da álgebra relacional ................................................................................ 24 Classificações da álgebra relacional quanto à sua origem ...................................... 24 Classificações da álgebra relacional quanto ao número de relações ................... 24 Classificações da álgebra relacional quanto à área matemática............................ 25 Operações primitivas da álgebra relacional ............................................................... 25 Operação de projeção .................................................................................................... 25 Operação de seleção ...................................................................................................... 27 Operação de projeção com seleção ............................................................................ 28 Produto Cartesiano ......................................................................................................... 29 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 2 Produto cartesiano com projeção e seleção ............................................................. 30 União .................................................................................................................................. 31 Modelo de dados - exemplo .......................................................................................... 31 Interseção .......................................................................................................................... 33 Diferença ........................................................................................................................... 34 Junção ............................................................................................................................... 36 Junção natural ................................................................................................................. 37 Atividade proposta .......................................................................................................... 37 Referências........................................................................................................................... 40 Exercícios de fixação ......................................................................................................... 40 Chaves de resposta ..................................................................................................................... 43 Aula 2 ..................................................................................................................................... 43 Exercícios de fixação ....................................................................................................... 43 Aula 3: Linguagem SQL – Parte 1 ................................................................................................ 45 Introdução ........................................................................................................................... 45 Conteúdo .............................................................................................................................. 46 Introdução à linguagem SQL e suas divisões sintáticas .......................................... 46 Principais características da SQL .................................................................................. 46 Grupos de comandos SQL ............................................................................................. 47 Tipos de domínios básicos ............................................................................................ 48 Linguagem de Definição de Dados (DDL) – criação de uma tabela ...................... 49 Linguagem de Definição de Dados (DDL) – SQL Constraints ................................. 51 Linguagem de Definição de Dados (DDL) – alteração de uma tabela .................. 52 Linguagem de Definição de Dados (DDL) – remoção de uma tabela .................. 52 Linguagem de Definição de Dados (DDL) – truncando uma tabela ..................... 53 Linguagem de Definição de Dados (DDL) – criação e remoção de índices ........ 53 Atividade proposta .......................................................................................................... 54 Referências........................................................................................................................... 54 Exercícios de fixação ......................................................................................................... 55 Chaves de resposta .....................................................................................................................58 Aula 3 ..................................................................................................................................... 58 Exercícios de fixação ....................................................................................................... 58 Aula 4: Linguagem SQL – Parte 2 ................................................................................................ 59 Introdução ........................................................................................................................... 59 Conteúdo .............................................................................................................................. 60 Introdução......................................................................................................................... 60 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 3 Inclusão de tuplas em uma tabela ............................................................................... 61 Atualização dos dados de uma tabela ......................................................................... 62 Remoção de dados de uma tabela ............................................................................... 63 Conceito de transação.................................................................................................... 63 Propriedades ACID .......................................................................................................... 64 Controle de transações .................................................................................................. 64 Comando SELECT ............................................................................................................ 65 SELECT - operadores ...................................................................................................... 65 SELECT – consulta simples ............................................................................................ 66 DISTINCT ........................................................................................................................... 67 WHERE ............................................................................................................................... 68 Operações no comando SELECT ................................................................................. 70 Operadores lógicos ......................................................................................................... 71 Operador LIKE .................................................................................................................. 71 Operador BETWEEN ........................................................................................................ 73 Operador IN ...................................................................................................................... 73 Operador NULL/NOT NULL ........................................................................................... 74 ALIAS .................................................................................................................................. 74 Concatenação de campos ............................................................................................. 75 Ordenação do resultado................................................................................................. 76 Atividade proposta .......................................................................................................... 78 Referências........................................................................................................................... 78 Exercícios de fixação ......................................................................................................... 78 Chaves de resposta ..................................................................................................................... 82 Aula 4 ..................................................................................................................................... 82 Exercícios de fixação ....................................................................................................... 82 Aula 5: Linguagem SQL – Parte 3 ................................................................................................ 84 Introdução ........................................................................................................................... 84 Conteúdo .............................................................................................................................. 85 Tabelas de exemplo ........................................................................................................ 85 Select – funções de agregação ..................................................................................... 85 Agrupamentos .................................................................................................................. 86 Cláusula having ................................................................................................................ 86 Junção e junção interna (INNER JOIN)....................................................................... 86 Usando joing using ......................................................................................................... 88 Junção natural (natural join) ......................................................................................... 88 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 4 Usando ALIAS ................................................................................................................... 89 Várias tabelas no JOIN ................................................................................................... 89 Autojunção ....................................................................................................................... 89 Tipos de junção e operações ........................................................................................ 90 Subconsulta ...................................................................................................................... 94 Operador EXISTS/NOT EXISTS ....................................................................................... 95 Operador ALL ................................................................................................................... 95 Operador ANY .................................................................................................................. 96 Criação de visões (VIEWS) ............................................................................................. 96 Linguagem de controle de dados (DCL) ..................................................................... 99 Comando GRANT ............................................................................................................ 99 Atividade proposta .......................................................................................................... 99 Referências......................................................................................................................... 100 Exercícios de fixação ....................................................................................................... 100 Notas ......................................................................................................................................... 105 Chaves de resposta ................................................................................................................... 105 Aula 5 ................................................................................................................................... 105 Exercícios de fixação ..................................................................................................... 105 Aula 6: Indexação ......................................................................................................................108 Introdução ......................................................................................................................... 108 Conteúdo ............................................................................................................................ 109 Índices – conceitos básicos ......................................................................................... 109 Tipos básicos de índices ............................................................................................... 110 Índices sobre arquivos sequenciais ............................................................................ 110 Atualização de índice: exclusão .................................................................................. 115 Atualização de índice: inserção .................................................................................. 115 Arquivos de índice de árvore B+ ................................................................................. 116 Estrutura de nós da árvore B+ ..................................................................................... 117 Definição de um índice em SQL ................................................................................. 120 Atividade proposta ........................................................................................................ 121 Referências......................................................................................................................... 122 Exercícios de fixação ....................................................................................................... 122 Notas ......................................................................................................................................... 124 Chaves de resposta ................................................................................................................... 124 Aula 6 ................................................................................................................................... 124 Exercícios de fixação ..................................................................................................... 124 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 5 Aula 7: Transações .................................................................................................................... 126 Introdução ......................................................................................................................... 126 Conteúdo ............................................................................................................................ 127 Introdução a transações ............................................................................................... 127 Conceito de transação.................................................................................................. 127 Operações de leitura/escrita ........................................................................................ 128 Execução das operações .............................................................................................. 128 Exemplo de acesso aos dados .................................................................................... 129 Estados de uma transação ........................................................................................... 130 Transição de estados de uma transação .................................................................. 131 Propriedades de uma transação ................................................................................. 132 Atomicidade .................................................................................................................... 132 Consistência ................................................................................................................... 133 Isolamento ...................................................................................................................... 134 Durabilidade ou persistência ....................................................................................... 135 Gerência básica de transações .................................................................................... 135 Transações em SQL ....................................................................................................... 136 Consistência dos dados ................................................................................................ 138 Controle de concorrência ............................................................................................ 139 Problema da perda de atualização ............................................................................. 139 Problema da atualização temporária (leitura suja) ................................................. 140 Problema da agregação incorreta .............................................................................. 140 Problema da leitura não-repetitiva ............................................................................ 140 Atividade Proposta ........................................................................................................ 141 Referências......................................................................................................................... 142 Exercícios de fixação ....................................................................................................... 143 Chaves de resposta ................................................................................................................... 145 Aula 7 ................................................................................................................................... 145 Exercícios de fixação ..................................................................................................... 145 Aula 8: Otimização e proces. de consultas ................................................................................ 146 Introdução ......................................................................................................................... 146 Conteúdo ............................................................................................................................ 147 Conceito de performance em Banco de Dados ...................................................... 147 Arquitetura funcional de um SGBD ............................................................................ 149 Componentes envolvidos na execução de uma consulta .................................... 150 Fluxo de execução de uma consulta SQL ................................................................. 151 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 6 Componentes envolvidos ............................................................................................ 151 Fluxo dentro do otimizador CBO ................................................................................ 153 Gerador de planos ......................................................................................................... 153 Gerador de registros ..................................................................................................... 154 Executor de SQL ............................................................................................................. 154 Plano de execução (exemplo SGBD Oracle) ............................................................. 154 Gerando planos de execução no SGBD Oracle ....................................................... 154 Colunas do plano de execução................................................................................... 155 Métodos de acesso ........................................................................................................ 156 Índices ..............................................................................................................................157 Ações para melhoria de comandos SQL ................................................................... 158 Atividade proposta ........................................................................................................ 159 Referências......................................................................................................................... 159 Exercícios de fixação ....................................................................................................... 160 Notas ......................................................................................................................................... 162 Chaves de resposta ................................................................................................................... 163 Aula 8 ................................................................................................................................... 163 Exercícios de fixação ..................................................................................................... 163 Conteudista ............................................................................................................................... 164 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 7 Projeto e otimização de banco de dados - Apostila Apresentação As instituições hoje têm nos seus dados o mais precioso ativo. Quem tem o domínio sobre suas informações tem grande vantagem competitiva. O grande desafio reside em lidar com os grandes volumes de dados gerados pelo mundo moderno. Armazenar, recuperar e manipular os dados das organizações é missão crítica. Os Sistemas Gerenciadores de Banco de Dados Relacionais (SGBDR) são o ponto central nesse contexto. Os pesquisadores de sistemas de armazenamento de bancos de dados têm desenvolvido novas tecnologias para permitir maior eficiência no gerenciamento, armazenamento e na recuperação de dados. No entanto, o desenvolvimento tecnológico por si só não traz resultados duradouros sem um bom projeto de banco de dados, desde o modelo conceitual, passando pelo modelo lógico, até o modelo físico. É imprescindível que os desenvolvedores de sistemas tenham conhecimento aprofundado do funcionamento dos SGBDR e da linguagem SQL para produzir modelos de dados eficientes e consultas performáticas. Neste contexto é de fundamental importância a compreensão da sintaxe SQL, do controle de transação e das técnicas de otimização de consultas, permitindo o uso eficiente dos recursos do SGBDR. Sendo assim, essa disciplina tem como objetivos: 1. Esclarecer os conceitos fundamentais do Modelo Relacional; PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 8 2. Empregar comandos utilizando a Linguagem SQL; 3. Aplicar índices; 4. Registrar transações; 5. Desenvolver consultas a bancos de dados. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 9 Aula 1: Modelo relacional Introdução Nesta aula, serão apresentados os conceitos do Modelo Relacional, a estrutura relacional de dados e as regras de integridade relacional. Objetivo: 1. Apresentar os conceitos do Modelo Relacional; 2. Descrever as regras de integridade do Modelo Relacional. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 10 Conteúdo Conceitos do modelo relacional O Modelo Relacional é um modelo de dados proposto pelo pesquisador Edgar F. Codd em 1970 e tem seus fundamentos na teoria de conjuntos. Está baseado no princípio de que os dados são guardados em tabelas relacionais, também chamadas de relações. Uma tabela ou relação consiste em uma entidade bidimensional, constituída de linhas (ou tuplas) e colunas (ou atributos). Cada linha da tabela representa uma coleção de valores de dados relacionados, que podem ser interpretados como fatos que descrevem uma entidade ou um relacionamento. O nome da tabela e os nomes das colunas são usados para ajudar na interpretação do significado dos valores em cada linha da tabela. Um banco de dados relacional é um banco de dados que modela os dados de tal forma que eles sejam percebidos pelo usuário como tabelas. O termo está relacionado aos dados organizados segundo o Modelo Relacional, ou a um Sistema Gerenciador de Banco de Dados Relacional (SGBDR) – do inglês Relational Database Management System (RDBMS) – um software que implementa a abstração. Definição matemática de relação Considere conjuntos, não necessariamente disjuntos D1, D2, ..., Dn de valores atômicos. R é uma relação sobre estes conjuntos (domínios de R), se e somente se R é um conjunto de n-uplas (tuplas) ordenadas <d1, d2, ... , dn>, tal que para i=1,2,...,n di pertence a Di. Exemplo: A = {1, 2, 3, 4, 5} B = {2, 3, 4, 6, 7} PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 11 R = B é o dobro de A R = {(2,1), (4,2), (6,3)} Conceitos do modelo relacional Vejamos agora os conceitos do modelo relacional: Relação = Tabela bi-dimensional, composta de linhas e colunas de dados Relação recursiva = Relaciona o objeto a si mesmo Atributo = Coluna Grau de uma relação = Número de atributos Tupla = Cada linha da relação Domínio de um atributo = Conjunto ao qual pertence os valores de um atributo Exemplo: O atributo SEXO de uma tabela ALUNO só pode conter os valores M (masculino) e F (feminino). Valor nulo de um atributo de uma tupla = Atributo inaplicável ou com valor desconhecido Atenção Esquema de Banco de Dados Relacional = Nomes das relações seguidos pelos nomes dos atributos, com os atributos chaves sublinhados e com as chaves estrangeiras identificadas (duplo sublinhado). PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 12 Projeto (design) de bancos de dados Temos aqui um esquema de um projeto (design) de um banco de dados. Veja como é o seu funcionamento: Exemplo de Esquema (Modelo Lógico) FUNCIONÁRIO (MAT, NOME, ENDERECO, CPF, DT_ADM, CODDEPTO, CODCARGO) CARGO (CODCARGO, DESC_CARGO) DEPARTAMENTO (CODDEPTO, NOME_DEPTO) Exemplo de diagrama de entidades e relacionamentos (modelo conceitual) Observe o exemplo de um diagrama de entidades e relacionamentos (modelo conceitual) e suas características: PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 13 Exemplo de relações ou tabelas (modelo físico) Vejamos agora um exemplo de um modelo físico em forma de tabelas: Conceitos Entenda o conceito de cada item de uma relação: Atributo identificador O atributo ou conjunto de atributos que será utilizado para identificar instâncias de uma entidade faz parte do modelo conceitual. Chave Designa o conceito de item de busca, isto é, um atributo ou conjunto de atributos que será utilizado nas consultas à base de dados. É um conceito lógico da aplicação. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 14 Índice É um recurso físico que visa otimizar a recuperação de uma informação via método de acesso. O banco de dados usa o índice de maneira semelhante ao índice remissivo de um livro, verifica um determinado assunto no índice e depois localiza a sua posição em uma determinada página. Obs.: Uma chave pode ser utilizada como índice, mas um índice não é, necessariamente, uma chave. Chave candidata Deve ser única, ou seja, nenhuma tupla de uma mesma relação pode ter o mesmo valor para o atributo escolhido como chave candidata. Deve ser irredutível, nenhum subconjunto da chave candidata pode ter sozinho a propriedade de ser único.Pode ser: Simples: quando é composta por apenas um atributo; Composta: quanto possui mais de um atributo para formar a chave. Chave primária É um caso especial da chave candidata. É a escolhida entre as candidatas para identificar unicamente uma tupla. Chave estrangeira É quando um atributo de uma relação é chave primária em outra. Constitui um conceito de vital importância no modelo relacional: é o elo lógico entre as tabelas (relacionamentos). Através das operações com as chaves estrangeiras que se garante a Integridade Referencial do banco de dados que você verá a seguir. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 15 Regras de integridade do modelo relacional Conheça agora as regras de integridade do modelo relacional: Regras de integridade Regras que devem ser obedecidas em todos os estados válidos da base de dados (podem envolver uma ou mais linhas de uma ou mais tabelas. São elas: a) Domínio b) Chave c) Entidade d) Referencial Integridade de domínio Especificam que dentro de cada tupla o valor de cada atributo deve ser um valor atômico. Os tipos de dados associados aos domínios incluem: • Inteiros (inteiro curto, inteiro e inteiro longo); • Números reais (ponto flutuante e flutuante de precisão dupla); • Caracteres booleanos; • Cadeias de caracteres (data, hora, timestamp) etc. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 16 Integridade de chave A chave é um atributo ou conjunto de atributos cujo valor ou combinação de valores deve ser único em qualquer instância da relação. Integridade de entidade O valor da chave não pode ser vazio (nulo). A chave primária serve como representante na base de dados de uma entidade – se a chave primária for vazia, a linha não corresponde a nenhuma entidade. Integridade referencial As chaves estrangeiras tem que ser respeitadas, ou seja, se existe um determinado valor para o atributo na tabela onde ele é chave estrangeira esse valor deve existir na tabela onde ele é chave primária. Integridade semântica Restrições de integridade semântica ou regras do negócio também podem ser especificadas no Banco de Dados. Essas restrições dependem da semântica do negócio ou da aplicação. Exemplos: Um funcionário só pode participar de 3 projetos ao mesmo tempo. O menor salário da empresa deve ser maior ou igual ao salário mínimo vigente. Todo funcionário deve estar lotado em um departamento. Atividade Proposta (AOCP/BRDE – 2011 - ANALISTA DE SISTEMAS) Na terminologia formal do modelo relacional, encontramos os seguintes termos: tupla, atributo, relação e domínio. Defina-os individualmente. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 17 Chave de resposta: Existem alguns termos utilizados na terminologia do modelo relacional formal, entre eles destacam-se os abaixos relacionados e suas respectivas definições: • O termo Tupla refere-se a uma linha de uma relação; • O termo Atributo refere-se a um cabeçalho de coluna de uma relação; • O termo Relação refere-se a uma tabela; • O termo Domínio (D) refere-se a um conjunto de valores atômicos. Considerando que, por atômicos, entendemos que cada valor no domínio é indivisível no que diz respeito ao modelo relacional. Referências ELMASRI, R.; NAVATHE, S., Sistemas de banco de dados. 4. ed. Pearson Education do Brasil, 2005. SILBERSCHATZ, Abraham; KORTH, Henry F; SUDARSHAN, S. A. 5. ed. Sistema de banco de dados. Rio de Janeiro: Campus, 2006. Exercícios de fixação Questão 1 Em relação ao modelo relacional, marque a alternativa correta. a) A chave estrangeira não precisa ser única, mas não pode ser nula. b) O domínio de um atributo corresponde ao seu tipo de dado. c) O valor de um atributo de uma tupla é nulo quando o seu valor é desconhecido. d) A integridade referencial é garantida através da chave primária. e) Uma chave pode ser um índice, mas um índice é obrigatoriamente uma chave. Questão 2 Em relação aos conceitos apresentados, marque a alternativa correta. a) Uma relação ou tabela é composta apenas por linhas ou tuplas de dados. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 18 b) Uma relação recursiva representa um loop no Banco de Dados. c) Um atributo é o mesmo que uma linha da tabela. d) O grau de uma relação corresponde ao número de atributos dessa relação. e) Um “Esquema” representa o índice de uma coluna da tabela. Questão 3 Em relação as chaves, marque a alternativa correta. a) A chave candidata não precisa ser única. b) A chave candidata é um caso especial da chave primária. c) A chave estrangeira ocorre quando um atributo é chave estrangeira em outra relação. d) Um índice é recurso lógico para otimizar a recuperação de uma informação. e) Uma chave estrangeira é o elo lógico entre as tabelas (relacionamentos). Questão 4 Em relação ao Modelo Relacional, marque a alternativa incorreta. a) O Modelo Relacional foi proposto pelo pesquisador Edgar F. Codd na década de 1990. b) O Modelo Relacional tem seus fundamentos na teoria de conjuntos. c) O nome da tabela e os nomes das colunas são usados para ajudar na interpretação do significado dos valores em cada linha da tabela. d) Uma linha é também chamada de tupla. e) Uma coluna é também chamada de atributo. Questão 5 São etapas do projeto de Banco de Dados, exceto: a) Coleta e análise de requisitos de dados b) Análise de requisitos funcionais c) Projeto físico d) Projeto conceitual PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 19 e) Projeto lógico Questão 6 São Regras de Integridade, exceto. a) Domínio b) Chave c) Entidade d) Referencial e) Índice Questão 7 Os seguintes tipos de dados podem estar associados a um domínio, exceto: a) Inteiros b) Símbolos c) Números reais d) Caracteres booleanos e) Cadeias de caracteres Questão 8 Em relação a regra de integridade da entidade e referencial, podemos afirmar: a) O valor da chave primária pode ser vazio. b) A chave primária não serve como representante de uma entidade. c) O valor da chave estrangeira deve existir na tabela onde o atributo relacionado é chave primária. d) A chave estrangeira não pode ser nula. e) A chave estrangeira não pode conter valores duplicados. Questão 9 A afirmação “O salário do servidor público não pode ultrapassar o salário do presidente da república” está associada ao seguinte termo: a) Restrição de chave primária b) Integridade referencial PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 20 c) Definição de um índice d) Restrição de integridade semântica e) Associação de tipos Notas Atributo identificador: Conjunto de atributos que será utilizado para identificar instâncias de uma entidade. Chave Estrangeira (Foreing Key): Mecanismo que permite a implementação de relacionamentos no modelo relacional. Chave Primária (Primary Key): Um campo ou conjunto de campos escolhidos pelo projetista do banco de dados como de significado principal para a identificação exclusiva de um registro dentro de uma tabela de dados. Chave: Designa o conceito de item de busca. Domínio (Domain): Conjunto de valores que um determinado atributo (ou campo) pode assumir. Índice: Recurso físico que visa otimizar a recuperação de uma informação. Integridade Referencial: Utilizada para garantir a integridade dos dados entre tabelas relacionadas. Regras de integridade: Regras que estabelecem quando uma base de dados está correta.Chaves de resposta Aula 1 Exercícios de fixação Questão 1 - C Justificativa: Um valor nulo significa ausência de valor ou valor desconhecido. Questão 2 - D Justificativa: O número de atributos da relação corresponde ao seu grau. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 21 Questão 3 - E Justificativa: A chave estrangeira permite a ligação entre duas tabelas. Questão 4 - A Justificativa: O Modelo Relacional foi proposto por F. Codd na década de 1970. Questão 5 - B Justificativa: A análise de requisitos funcionais não é uma etapa do projeto de banco de dados. Questão 6 - E Justificativa: Não existe regra de integridade chamada Índice. Questão 7 - B Justificativa: Símbolos não correspondem a um tipo de dados associado a um domínio. Questão 8 - C Justificativa: Se existe um determinado valor para o atributo na tabela onde ele é chave estrangeira, esse valor deve existir na tabela onde ele é chave primária. Questão 9 - D Justificativa: Restrições de integridade semântica ou regras do negócio. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 22 Aula 2: Álgebra relacional Introdução Nesta aula, será apresentado o conceito de álgebra relacional. Serão estudadas as operações de seleção e projeção, operações de conjunto, produto cartesiano e junção. Objetivo: 1. Conhecer o conceito de álgebra relacional, operações de seleção e projeção; 2. Realizar operações de conjunto, produto cartesiano e junção. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 23 Conteúdo Introdução A álgebra relacional pode ser definida como uma linguagem de consulta formal, uma coleção de operações de alto nível sobre relações ou conjuntos. O pesquisador E. F. Codd propôs tal álgebra como a base das linguagens de consulta de banco de dados em seu modelo relacional de, dados visto na Aula 1. A principal aplicação da álgebra relacional é sustentar a fundamentação teórica de banco de dados relacional, particularmente linguagem de consulta para tais bancos de dados, entre os maiores o SQL. Na álgebra relacional alguns operadores são primitivos e os outros são derivados em termos dos primitivos. Linguagens Formais de Consulta ao Modelo Relacional Álgebra Relacional Linguagem procedural. Cálculo Relacional Relaciona Linguagem não procedural ou linguagem declarativa. Álgebra relacional Provê um fundamento formal para operações do modelo relacional. Utilizada como base para implementar e otimizar as consultas nos sistemas gerenciadores de banco de dados relacional (SGBDRs). PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 24 Operadores da álgebra relacional Classificações da álgebra relacional quanto à sua origem Cinco operadores são ditos primitivos ou fundamentais, ou seja, através deles qualquer expressão de consulta de dados é possível. São eles: seleção, projeção, produto cartesiano, união e diferença. Derivam dos operadores fundamentais. São definidos para facilitar a especificação de certos procedimentos. São eles: interseção, junção (normal e natural) e divisão. Operadores que não se enquadram nos itens anteriores. São eles: renomeação e alteração. Classificações da álgebra relacional quanto ao número de relações Operam em uma única tabela. São eles: seleção, projeção, renomeação e alteração. Operam em duas tabelas. São eles: união, interseção, diferença, produto cartesiano, junção e divisão. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 25 Classificações da álgebra relacional quanto à área matemática Operadores adicionais, definidos pela álgebra relacional para manipulação de dados. São eles: seleção, projeção, junção, divisão, renomeação e alteração. Operadores usuais da teoria de conjuntos da matemática. São eles: união, interseção, diferença e produto cartesiano. Funções matemáticas de agregação para uma coleção de valores de dados. Entre as mais comuns estão a soma, média, máximo, mínimo e contador. Operações primitivas da álgebra relacional Modelo de Dados - Exemplo Operação de projeção Representada pela letra grega PI, é uma operação que produz um subconjunto com os atributos passados como argumento e com todas as linhas do conjunto inicial. É classificada como uma operação unária por operar sobre apenas um conjunto de entrada. π nome_coluna1, nome_coluna2, ... , nome_colunaN (nome_tabela) PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 26 Argumentos: nome_coluna1, nome_coluna2, ... , nome_colunaN Conjunto de Entrada: nome_tabela (relação) Exemplo 1: Mostrar o nome de todos os funcionários. π NOME (FUNCIONÁRIO) Exemplo 2: Mostrar o nome e a data de admissão de todos os funcionários. π NOME, DT_ADM (FUNCIONÁRIO) PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 27 Operação de seleção Representada pela letra grega sigma, é uma operação que produz um subconjunto com os mesmos atributos do conjunto inicial, porém com as linhas que satisfazem a uma determinada condição (chamada de predicado). A seleção pode ser entendida como uma operação que filtra as linhas de uma relação (tabela), e é uma operação unária, pois opera sobre um único conjunto de dados. σ predicado (nome_tabela) Argumentos: predicado (filtro) Conjunto de Entrada: nome_tabela Exemplo 1: Selecionar os funcionários que são analistas (código do cargo ‘AN’) σ CODCARGO=’AN’ (FUNCIONÁRIO) Exemplo 2: Selecionar os funcionários que são analistas (código do cargo ‘AN’) e que pertencem ao departamento de Tecnologia da Informação (código do departamento ‘TI’) σ CODCARGO=’AN’ ^ CODDEPTO=’TI’ (FUNCIONÁRIO) PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 28 Exemplo 3: Selecionar os funcionários que são analistas (código do cargo ‘AN’) ou que pertencem ao departamento de Recursos Humanos (código do departamento ‘RH’) CODCARGO=’AN’ v CODDEPTO=’RH’ (FUNCIONÁRIO) Operação de projeção com seleção Uso combinado das operações de seleção para filtrar linhas e projeção para delimitar colunas em um subconjunto resultante. π nome_coluna(s) (σ predicado (nome_tabela)) Exemplo 1: Selecionar a matrícula e o nome dos funcionários admitidos após o ano de 2001. π MAT, NOME (σ DT_ADM ≥ ’01/01/2001’ (FUNCIONÁRIO)) PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 29 Exemplo 2: Selecionar nome e data de admissão dos funcionários admitidos após o ano de 2001 e que não são analistas (código do cargo ‘AN’). π NOME, DT_ADM (σ DT_ADM ≥ ’01/01/2001’ ^ CODCARGO ≠ ’AN’ (FUNCIONÁRIO)) Produto Cartesiano O produto cartesiano de duas relações ou tabelas tem como resultado uma terceira relação contendo todas as combinações possíveis entre os elementos das relações originais. A relação ou tabela resultante terá um número de colunas que é igual à soma das quantidades de colunas das duas relações iniciais, e um número de linhas igual ao produto do número de suas linhas. Dessa forma, o produto cartesiano PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 30 de uma relação 1 que possua 5 colunas e 3 linhas com uma relação 2 com 2 colunas e 5 linhas, a relação resultante terá 5+2 = 7 colunas e 3*5 = 15 linhas. Exemplo 1: Produto cartesiano de funcionários e cargos.Resultado: Produto cartesiano com projeção e seleção Uso combinado das operações de seleção, projeção com produto cartesiano para produzir um subconjunto resultantes com linhas e colunas filtradas e os dados de mais de uma tabela. Exemplo 1: Selecionar o nome do funcionário e a descrição do seu cargo (atributo DESC_CARGO) para funcionários admitidos à partir de 2003. π NOME, DESC_CARGO (σ DT_ADM ≥ ’01/01/2003’ ^ FUNCIONÁRIO.CODCARGO=CARGO.CODCARGO(FUNCIONÁRIO X CARGO)) PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 31 União O resultado é uma relação que contém todas as linhas da primeira relação seguidas de todas as linhas da segunda relação. A relação resultante possui a mesma quantidade de colunas que as relações originais, e tem um número de linhas que é no máximo igual à soma das linhas das relações fornecidas como operandos, já que as linhas que são comuns a ambas as relações aparecem uma única vez no resultado. As relações devem possuir o mesmo número de atributos. Modelo de dados - exemplo Exemplo 1: Relacionar o nome dos clientes que têm conta ou empréstimo. π NOME (σ CLIENTE.COD_CLI=CONTA.COD_CLI (CLIENTE X CONTA)) PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 32 π NOME (σ CLIENTE.COD_CLI=EMPRESTIMO.COD_CLI (CLIENTE X EMPRÉSTIMO)) R1 = π NOME (σ CLIENTE.COD_CLI=CONTA.COD_CLI (CLIENTE X CONTA)) R2 = π NOME (σ CLIENTE.COD_CLI=EMPRESTIMO.COD_CLI (CLIENTE X EMPRESTIMO)) R3 = R1 U R2 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 33 Interseção Produz como resultado uma relação que contém, sem repetições, todos os elementos que são comuns às duas tabelas fornecidas como operandos. As relações devem possuir o mesmo número de atributos. Exemplo 1: Relacionar o nome dos clientes que têm conta e empréstimo. π NOME (σ CLIENTE.COD_CLI=CONTA.COD_CLI (CLIENTE X CONTA)) π NOME (σ CLIENTE.COD_CLI=EMPRESTIMO.COD_CLI (CLIENTE X EMPRESTIMO)) R1 = π NOME (σ CLIENTE.COD_CLI=CONTA.COD_CLI (CLIENTE X CONTA)) R2 = π NOME (σ CLIENTE.COD_CLI=EMPRESTIMO.COD_CLI (CLIENTE X EMPRESTIMO)) R3 = R1 R2 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 34 Diferença O resultado é uma relação que possui todas as linhas que existem na primeira relação e não existem na segunda. As relações devem possuir o mesmo número de atributos. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 35 Exemplo 1: Relacionar o nome dos clientes que têm conta e não têm empréstimo. π NOME (σ CLIENTE.COD_CLI=CONTA.COD_CLI (CLIENTE X CONTA)) π NOME (σ CLIENTE.COD_CLI=EMPRESTIMO.COD_CLI (CLIENTE X EMPRÉSTIMO)) Resultado: R1 = π NOME (σ CLIENTE.COD_CLI=CONTA.COD_CLI (CLIENTE X CONTA)) R2 = π NOME (σ CLIENTE.COD_CLI=EMPRESTIMO.COD_CLI (CLIENTE X EMPRESTIMO)) R3 = R1 ∩ R2 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 36 Junção O resultado da operação junção é uma relação com todas as combinações das tuplas na relação1 (R1) e relação2 (R2) que satisfazem a condição de junção. Exemplo 1: Relacionar todas as informações dos clientes que têm empréstimos. CLIENTE |X| CLIENTE.COD_CLI=EMPRÉSTIMO.COD_CLI EMPRÉSTIMO Resultado: PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 37 Junção natural Quando os atributos usados na condição de junção têm o mesmo nome nas duas relações não é necessário especificá-los. Dá-se o nome de junção natural. Exemplo 1: Relacionar o nome e CPF dos clientes do Rio de Janeiro que têm empréstimos. π NOME, CPF (σ CLIENTE.CIDADE=’RIO DE JANEIRO’(CLIENTE |X| EMPRESTIMO)) Obs.: A coluna COD_CLI tem o mesmo nome na tabela EMPRÉSTIMO e na tabela CLIENTE. Atividade proposta Atividade 1 Dadas às relações, escreva as sentenças da Álgebra Relacional que respondem às questões propostas. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 38 a) Selecionar o código do cliente e o código da agência dos empréstimos maiores que de 300,00. b) Selecionar os códigos dos clientes que têm conta na agência 04, mas não têm empréstimo na mesma agência. As relações são: AGÊNCIA Código Nome Cidade 01 Centro Rio de Janeiro 02 Arariboia Niterói 03 Centro Rio de Janeiro 04 Centro São Gonçalo CLIENTE Código Nome Cidade 01 Luiz Niterói 02 João Niterói 03 Ana Rio de Janeiro 04 Maria Rio de Janeiro 05 Pedro Rio de Janeiro 06 João Rio de Janeiro 07 Mário Rio de Janeiro 08 Renato São Gonçalo 09 Felipe São Gonçalo 10 Kátia São Gonçalo EMPRÉSTIMO Número Cód_cliente Cód_agência Quantia 01 01 01 100,00 02 01 04 300,00 03 06 01 100,00 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 39 04 07 04 300,00 05 05 04 200,00 CONTA Número Cód_cliente Cód_agência Saldo 01 01 01 100,00 02 07 01 400,00 03 02 01 200,00 04 03 02 300,00 05 08 02 200,00 06 04 02 300,00 07 04 03 400,00 08 03 03 300,00 09 07 04 100,00 10 02 04 100,00 11 09 04 200,00 Chave de resposta: a) π cod_cli,cod_ag (squantia > 300,00 (EMPRÉSTIMO)) b) π cod_cli (σcod_ag = 04 (CONTA) - πcod_cli (σcod_ag = 04 (EMPRESTIMO) Material complementar Para saber mais sobre álgebra relacional, leia os textos disponíveis em nossa biblioteca virtual. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 40 Referências SILBERSCHATZ, Abraham; KORTH, Henry F; SUDARSHAN, S. A. Sistema de banco de dados. 5. ed. Rio de Janeiro: Campus, 2006. ELMASRI, R.; NAVATHE, S. Sistemas de banco de dados. 4. ed. Pearson Education do Brasil, 2005. Exercícios de fixação Questão 1 Em relação a álgebra relacional, marque a alternativa correta. a) Corresponde a uma coleção de operações de baixo nível sobre relações. b) É a base das linguagens de consulta a banco de dados. c) Só existem operadores primitivos. d) Não pode ser utilizada para otimizar consultas em SGBDR. e) É linguagem declarativa, não procedural. Questão 2 Em relação aos operadores da álgebra relacional, marque a alternativa correta. a) Podem ser classificados quanto a origem, número de relações e área matemática. b) São operadores derivados: interseção, junção e projeção. c) São exemplos de operadores renomeação, alteração e seleção. d) Os operadores primitivos são definidos para facilitar certos procedimentos. e) Os operadores união, interseção, diferença e seleção são binários. Questão 3 Sobre os operadores da álgebra relacional, marque a alternativa incorreta. a) São exemplos de operadores usuais da teoria de conjuntos da matemática: união, interseção, diferença e produto cartesiano. b) São funções matemáticas de agregação: soma, média, máximo e mínimo. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 41 c) O símbolo σ representa uma operação de seleção. d) O símbolo ← representa uma operação de renomeação. e) O símbolo π representa uma operação de projeção. Questão 4 Seja a tabela ALUNO (MAT, NOME, END, SEXO, DT_NASC). A sentença da álgebra relacional que responde à proposição “Recuperar a matrícula e o nome de todos os alunos do sexo masculino (atributo SEXO = ‘M’) é: a) π MAT, NOME (π SEXO=’M’ (ALUNO)). b) σ SEXO=’M’ (ALUNO). c) π MAT, NOME (σ SEXO=’M’ (ALUNO)). d) σ MAT, NOME (π SEXO=’M’ (ALUNO)). e) π SEXO=’M’ (ALUNO). Questão 5 Seja a tabela ALUNO (MAT, NOME, END, SEXO, DT_NASC). A sentença da álgebra relacional que responde à proposição “Recuperaro nome de todos os alunos nascidos no ano 2000) é: a) σ NOME (π DT_NASC ≥ ’01/01/2000’ v DT_NASC ≤ ’31/12/2000’ (ALUNO)). b) π NOME (σ DT_NASC > ’01/01/2000’ ^ DT_NASC < ’31/12/2000’ (ALUNO)). c) π NOME (σ DT_NASC = ’2000’ (ALUNO)). d) π NOME (σ DT_NASC ≥ ’01/01/2000’ ^ DT_NASC ≤ ’31/12/2000’ (ALUNO)). e) π NOME (σ DT_NASC ≥ ’01/01/2000’ (ALUNO)). Questão 6 A relação resultante do produto cartesiano de uma relação 1 que possua 7 colunas e 4 linhas com uma relação 2 com 3 colunas e 6 linhas terá: a) 11 colunas e 18 linhas. b) 10 colunas e 24 linhas. c) 13 colunas e 7 linhas. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 42 d) 24 colunas e 10 linhas. e) 7 colunas e 13 linhas. Questão 7 A expressão π NOME (σ FUNCIONARIO.CODCARGO=CARGO.CODCARGO(FUNCIONARIO X CARGO)) representa: a) Um Produto Cartesiano com Projeção e Seleção. b) Uma Junção. c) Somente um Produto Cartesiano. d) Uma Junção Natural. e) Somente uma Seleção e Projeção. Questão 8 Qual operação da Álgebra Relacional produz como resultado uma relação que contém, sem repetições, todos os elementos que são comuns às duas tabelas fornecidas como operandos? a) União b) Diferença c) Interseção d) Junção Natural e) Projeção Questão 9 Em uma operação de Diferença é necessário que: a) As relações satisfaçam a condição de junção. b) As relações possuam o mesmo número de linhas. c) Seja possível realizar uma operação de Junção Natural. d) Apenas uma relação esteja envolvida. e) As relações possuam o mesmo número de atributos. Questão 10 PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 43 A expressão CLIENTE |X| CLIENTE.COD_CLI=EMPRÉSTIMO.COD_CLI EMPRÉSTIMO Pode ser substituída pela expressão: a) σ CLIENTE.COD_CLI= EMPRÉSTIMO.COD (CLIENTE X EMPRÉSTIMO). b) CLIENTE U EMPRÉSTIMO. c) CLIENTE ∩ EMPRÉSTIMO. d) CLIENTE |X| EMPRÉSTIMO. e) π NOME (CLIENTE |X| EMPRÉSTIMO). Chaves de resposta Aula 2 Exercícios de fixação Questão 1 - B Justificativa: Conforme proposto por E.F. Codd. Questão 2 - A Justificativa: Os operadores da álgebra relacional podem ser classificados quanto a origem, número de relações e área matemática. Questão 3 - D Justificativa: O símbolo ← representa uma operação de atribuição e não de renomeação. Questão 4 - C Justificativa: A combinação das operações de projeção e seleção tem a sintaxe: π nome_coluna(s) (σ predicado (nome_tabela)). Questão 5 - D PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 44 Justificativa: A combinação das operações de projeção e seleção tem a sintaxe: π nome_coluna(s) (σ predicado (nome_tabela)). Questão 6 - B Justificativa: Terá 7+3= 10 colunas e 4*6= 24 linhas. Questão 7 - A Justificativa: Produto Cartesiano combinado com as operações de Projeção e Seleção. Questão 8 - C Justificativa: A interseção produz como resultado uma relação que contém, sem repetições, todos os elementos que são comuns às duas tabelas fornecidas como operandos. Questão 9 - E Justificativa: A interseção produz como resultado uma relação que contém, sem repetições, todos os elementos que são comuns às duas tabelas fornecidas como operandos. Questão 10 - D Justificativa: Uma operação de junção onde os atributos da condição de junção tenham o mesmo nome pode ser substituída por uma junção natural. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 45 Aula 3: Linguagem SQL – Parte 1 Introdução Nesta aula, faremos uma introdução à linguagem SQL e suas divisões sintáticas. Inicialmente, trataremos da Linguagem de Definição de Dados (DDL); em seguida, serão apresentados os tipos de dados mais utilizados e as sentenças para criação, alteração e exclusão de tabelas. Objetivo: 1. Introduzir a linguagem SQL e suas divisões sintáticas; 2. Conhecer a Linguagem de Definição de Dados (DDL). PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 46 Conteúdo Introdução à linguagem SQL e suas divisões sintáticas SQL é um acrônimo para Structured Query Language ou Linguagem de Consulta Estruturada. Consiste em uma linguagem de pesquisa declarativa padrão para acesso a banco de dados relacionais. A base da linguagem está relacionada à álgebra relacional, estudada na aula anterior. Dr. E. F. Codd Historicamente, a SQL foi desenvolvida pela IBM, nos anos 70, dentro do Projeto R, como parte da pesquisa do Dr. E. F. Codd, que visava demonstrar a viabilidade da implementação do modelo relacional. Originalmente, a linguagem se chamava SEQUEL, acrônimo para “Structured English Query Language” (Linguagem de Consulta Estruturada), e foi desenvolvida para acesso ao System R, sistema de banco de dados construído pela IBM no seu Centro de Pesquisas de Almaden, em San Jose, Califórnia. Logo, a linguagem se tornaria um padrão para acesso a banco de dados relacionais. Em 1986, o ANSI (American National Standarts Institute) publicou um padrão SQL, denominado SQL-86. Seguiram-se outras publicações posteriores: SQL-89 (padrão estendido), SQL-92 (SQL2), SQL:1999 (SQL3), SQL:2003, SQL:2006, SQL:2008 e SQL:2011. Principais características da SQL Manipulação e controle de bancos de dados relacionais O acesso ao banco de dados pode ser feito das seguintes formas: • Através de ambiente interativo de consultas; • Embutida em linguagens hospedeiras (por exemplo, linguagens C e Java). PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 47 Recursos: • Alto poder de consulta; • Gerenciamento de índices; • Construção de visões; • Execução de instruções em blocos. Grupos de comandos SQL A SQL pode ser dividida em subconjuntos de comandos conforme as operações que se deseja efetuar em um banco de dados. DDL Data Definition Language Linguagem de Definição de Dados Comandos para definir, alterar e remover tabelas, visões e índices. Exemplos: CREATE TABLE, CREATE INDEX, CREATE VIEW, ALTER TABLE, ALTER INDEX, DROP INDEX, DROP VIEW. DML Data Manipulation Language Linguagem de Manipulação de Dados Comandos para inserir, remover, atualizar e consultar os dados armazenados nas tabelas. Exemplos: INSERT, DELETE e UPDATE. DCL Data Control Language Linguagem de Controle de Dados PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 48 Comandos para trabalhos em ambiente multiusuário, que permitem estabelecer níveis de segurança e manipular transações. Exemplos: GRANT e REVOKE. DTL Data Transaction Language Linguagem de Transação de Dados Comandos para interagir com áreas de controle de transação. Exemplos: COMMIT e ROLLBACK. DQL Data Query Language Linguagem de Consulta de Dados Comando para especificar uma consulta (“query”) como uma descrição do resultado desejado. Exemplo: SELECT. Tipos de domínios básicos CHAR(n) ou CHARACTER(n) Cadeia de caracteres de tamanho fixo, com o tamanho n definido pelo usuário. VARCHAR(n) Cadeia de caracteres de tamanho variável, com o tamanho máximo n definido pelo usuário. INT ou INTEGER Número inteiro, geralmente de 32768 a 32767. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 49 SMALLINT Número inteiro pequeno, geralmente de 0 a 65535. NUMERIC(t, d) Número de ponto decimal fixo, que consiste de t dígitos totais e d dígitos à direita do ponto decimal, com t e d definidos pelo usuário. REALNúmero de ponto flutuante. DOUBLE PRECISION Número de ponto flutuante de dupla precisão. FLOAT(n) Número de ponto flutuante com a precisão n definida pelo usuário. DATE Formato de data, contendo dia, mês e ano. TIME Formato de horário, contendo horas, minutos e segundos. Linguagem de Definição de Dados (DDL) – criação de uma tabela Sintaxe PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 50 CREATE TABLE nome_tabela (nome_coluna tipo [NOT NULL] [SET DEFAULT valor], ...., PRIMARY KEY (nome_colunas), [UNIQUE (nome_coluna)], [FOREIGN KEY (nome_coluna) REFERENCES nome_tabela (nome_coluna)] [CHECK condição]) Exemplo 1: Criação da tabela disciplina CREATE TABLE DISCIPLINA (CodD CHAR(5), NomeD VARCHAR(20) NOT NULL, CargaD INTEGER NOT NULL, AreaD VARCHAR(20), PreReqD CHAR(5), UNIQUE (NomeD), PRIMARY KEY (CodD) CHECK CargaD > 0 AND CargaD <= 6 ) Exemplo 2: Criação da tabela grade CREATE TABLE GRADE (CodC CHAR(5), CodD CHAR(5), CodP CHAR(5), Sala INTEGER, PRIMARY KEY (CodC, CodD, CodP), FOREIGN KEY (CodC) REFERENCES CURSO (CodC) , PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 51 FOREIGN KEY (CodD) REFERENCES DISCIPLINA (CodD), FOREIGN KEY (CodP) REFERENCES PROFESSOR (CodP) Linguagem de Definição de Dados (DDL) – SQL Constraints NOT NULL Indica que uma coluna não pode armazenar valores nulos. UNIQUE Garante que uma linha de uma coluna contém valores únicos. PRIMARY KEY Garante que uma coluna (ou combinação de duas ou mais colunas) tem uma identidade única, que permite encontrar uma determinada linha da tabela. FOREIGN KEY Garante a integridade referencial dos dados em uma tabela para coincidir com os valores da tabela referenciada. CHECK Garante que o valor em uma coluna atende a uma condição específica. DEFAULT Especifica um valor padrão quando não há valor para a coluna. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 52 Linguagem de Definição de Dados (DDL) – alteração de uma tabela Sintaxe ALTER TABLE nome_tabela [ADD nome_coluna tipo [NOT NULL]] [DROP COLUMN nome_coluna [CASCADE/RESTRICT]] [MODIFY nome_coluna] Exemplo 1: Adição da coluna MensC à tabela curso ALTER TABLE CURSO ADD MensC NUMERIC(6,2); Exemplo 2: Modificação do tamanho da coluna NomeD da tabela disciplina para varchar(50) ALTER TABLE DISCIPLINA MODIFY NomeD VARCHAR(50); Linguagem de Definição de Dados (DDL) – remoção de uma tabela Remove a tabela e seus dados. Sintaxe DROP TABLE nome_tabela CASCADE/RESTRICT; Exemplo: Remoção da tabela grade DROP TABLE GRADE; PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 53 Linguagem de Definição de Dados (DDL) – truncando uma tabela Remove os dados, mas mantém a estrutura da tabela. Sintaxe TRUNCATE TABLE nome_tabela; Exemplo: Truncando a tabela grade TRUNCATE TABLE GRADE; Linguagem de Definição de Dados (DDL) – criação e remoção de índices Os índices são estruturas de dados que contêm os valores de uma ou mais colunas e são utilizadas para obter mais rapidamente os dados das tabelas. O tema será objeto de uma aula a seguir. Sintaxe para criação: CREATE INDEX nome_índice ON nome_tabela ( nome_coluna [ASC|DESC],...); Exemplo: Criação de índice sobre a coluna CidadeP da tabela professor CREATE INDEX Xcidadeprof ON PROFESSOR (CidadeP ASC); Atenção A cláusula ASC é padrão e não precisa ser especificada. Significa que os valores no índice estarão em ordem ascendente. A cláusula DESC indica que os valores no índice estarão em ordem descendente. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 54 Atividade proposta Escreva os comandos em SQL para responder às seguintes proposições: a) Criar as tabelas Func (cod_func number(2), nome varchar(50), dta_nasc date, salário number(9,2), nro_depto number(1)) e Depto (nro_depto number(1), nome varchar(50)) ; b) Acrescentar a coluna DTA_ADM à tabela FUNC; c) Criar um índice em ordem descendente para o atributo DTA_NASC da tabela Func. A) CREATE TABLE DEPTO (NRO_DEPTO NUMBER(1), NOME VARCHAR2(50), PRIMARY KEY (NRO_DEPTO)); CREATE TABLE FUNC (COD_FUNC NUMBER(2), NOME VARCHAR2(50), DTA_NASC DATE, SALARIO NUMBER(9,2), NRO_DEPTO NUMBER(1), PRIMARY KEY (COD_FUNC), FOREIGN KEY (NRO_DEPTO) REFERENCES DEPTO (NRO_DEPTO)); B) ALTER TABLE FUNC ADD DTA_ADM DATE; C) CREATE INDEX IDX_DTA_NASC ON FUNC(DTA_NASC DESC); Referências ELMASRI, R.; NAVATHE, S. Sistemas de Banco de Dados. 4ª ed. Pearson Education do Brasil, 2005. SILBERSCHATZ, Abraham; KORTH, Henry F; SUDARSHAN, S. A. Sistema de banco de dados. 5ª ed. Rio de Janeiro: Campus, 2006. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 55 Exercícios de fixação Questão 1 Leia as afirmativas a respeito da linguagem SQL e, em seguida, marque a alternativa incorreta. a) Consiste em uma linguagem de pesquisa. b) Utilizada para acesso a banco de dados relacionais. c) A base da linguagem está relacionada à álgebra relacional. d) Padronizada pelo instituto ANSI. e) É linguagem não declarativa, procedural. Questão 2 A respeito dos recursos da linguagem SQL, marque verdadeiro (V) ou falso (F). ( ) Tem alto poder de consulta. ( ) Permite o gerenciamento de índices. ( ) Não pode estar em linguagem hospedeira. ( ) Permite a construção de visões. ( ) Não permite a execução em blocos. a) V-V-F-V-F b) F-F-F-V-V c) V-F-F-V-F d) V-V-F-V-V e) F-V-F-V-F Questão 3 São subconjuntos de comandos da linguagem SQL, exceto: a) DDL b) DML c) DCL d) DSL e) DQL PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 56 Questão 4 Os seguintes tipos de domínios básicos são possíveis na SQL, exceto: a) Char(n) b) Varchar(n) c) Double Real d) Int e) Float Questão 5 Veja o esquema da tabela ALUNO (MAT CHAR(5) Chave Primária, NOME VARCHAR(50) não nulo, CODCURSO CHAR(2)). Marque a alternativa que corresponde à sentença para criação da referida tabela. a) CREATE TABLE ALUNO(MAT VARCHAR(5) primary key, NOME CHAR(50) NULL, CODCURSO CHAR(2)) b) CREATE TABLE ALUNO(MAT CHAR(5) primary key, NOME VARCHAR NOT NULO, CODCURSO VARCHAR)) c) CREATE TABLE ALUNO(MAT CHAR(5) primary key, NOME VARCHAR(50) NOT NULL, CODCURSO CHAR(2)) d) CREATE TABLE ALUNO(MAT CHAR(5) foreign key, NOME CHAR(50) NOT NULL, CODCURSO CHAR(2)) e) CREATE TABLE ALUNO(MAT VARCHAR primary key, NOME CHAR NOT NULL, CODCURSO INT) Questão 6 Marque verdadeiro (V) ou falso (F) nas afirmativas a respeito das restrições em SQL. ( ) UNIQUE – Garante que uma linha de uma coluna contém valores únicos. ( ) PRIMARY KEY – Garante a integridade referencial dos dados em uma tabela. ( ) CHECK – Garante que o valor em uma coluna atende a uma condição específica. ( ) DEFAULT – Especifica um valor padrão quando não há valor para a coluna. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 57 ( ) NULL – Indica que uma coluna não pode armazenar valores nulos. a) F-V-V-V-F b) V-V-V-V-F c) V-F-V-V-F d) V-V-V-V-V e) F-V-V-V-F Questão 7 A sentença ALTER TABLE CURSO DROP COLUMN MensC realiza a seguinte operação: a) Exclui a tabela CURSO. b) Adiciona a coluna MensC à tabela CURSO. c) Altera a coluna MensC da tabela CURSO. d) Exclui a coluna MensCda tabela CURSO. e) Trunca a tabela CURSO. Questão 8 A sentença CREATE INDEX IDX_SAL ON ALUNO (SALARIO DESC) realiza a seguinte operação: a) Cria o índice IDX_SAL na coluna SALARIO da tabela ALUNO, em ordem descendente. b) Cria o índice IDX_SAL na coluna ALUNO da tabela SALARIO, em ordem descendente. c) Cria o índice IDX_SAL na coluna SALARIO da tabela ALUNO, em ordem crescente. d) Cria o índice IDX_SAL na tabela ALUNO, sem ordenação específica. e) Cria o índice SALARIO na coluna IDX_SAL da tabela ALUNO, em ordem descendente. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 58 Chaves de resposta Aula 3 Exercícios de fixação Questão 1 - E Justificativa: A linguagem SQL é uma linguagem declarativa e não procedural. Questão 2 - A Justificativa: A linguagem SQL pode estar em linguagem hospedeira e permite a execução em blocos. Questão 3 - D Justificativa: DSL não é um subconjunto de comandos da SQL. Questão 4 - C Justificativa: Double Real não é um tipo de domínio da SQL. Questão 5 - C Justificativa: A sintaxe correta é CREATE TABLE ALUNO(MAT CHAR(5) primary key, NOME VARCHAR(50) NOT NULL, CODCURSO CHAR(2)); Questão 6 - C Justificativa: Quem garante a integridade referencial é a FOREIGN KEY. NOT NULL – Indica que uma coluna não pode armazenar valores nulos. Questão 7 - D Justificativa: ALTER TABLE CURSO DROP COLUMN col1 é a sentença correta para excluir a coluna de uma tabela. Questão 8 - A Justificativa: A afirmativa correta é “Cria o índice IDX_SAL na coluna SALARIO da tabela ALUNO, em ordem descendente”. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 59 Aula 4: Linguagem SQL – Parte 2 Introdução Nesta aula, trataremos da Linguagem de Manipulação de Dados (DML). Serão estudadas as sentenças de inserção, atualização e exclusão de dados nas tabelas. Objetivo: 1. Estudou a Linguagem de Manipulação de Dados (DML), sentenças de inserção, atualização e exclusão de dados nas tabelas; 2. Realizar consultas simples. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 60 Conteúdo Introdução A linguagem de manipulação de dados é usada para modificar registros em um banco de dados. As seguintes tabelas exemplo serão utilizadas. Curso CodC NomeC DuracaoC MensC C1 Análise Sist. 4 400 C2 Eng. Mecatrônica 5 600 C3 Ciência Comp. 4 450 C4 Eng. Elétrica 4 600 C5 Turismo 3 350 Disciplina CodD NomeD CargaD AreaD PreReqD D1 TLP1 2 Computação D2 D2 Cálculo 1 4 Matemática null D3 Inglês 2 Humanas null D4 Ed. Física 3 Saúde null D5 G. Analítica 5 Matemática D2 D6 Projeto Final 6 null D1 Professor CodP NomeP CidadeP TituloP P1 Joaquim Rib. Preto Mestre P2 Paulo Batatais Espec. P3 André Rib. Preto Doutor P4 Gil S. Carlos Doutor P5 Juliana S. Carlos Pós Doc PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 61 Grade CodC CodD CodP Sala C1 D6 P1 305 C2 D2 P2 305 C3 D2 P2 305 C4 D1 P5 201 C4 D3 P3 204 C5 D4 P3 204 C5 D4 P4 207 A linguagem de manipulação de dados é usada para modificar registros em um banco de dados. As seguintes tabelas exemplo serão utilizadas. Inclusão de tuplas em uma tabela Inserir dados em uma tabela significa preencher uma linha de determinada tabela com dados correspondentes aos tipos determinados naquela tabela. Essa inserção de dados deve seguir as regras de integridade da tabela, assim como respeitar as regras de chave primária e chaves estrangeiras estabelecidas na tabela. Sintaxe INSERT INTO nome_tabela (coluna1, coluna2, .... , colunaN) VALUES (valor1, valor2, ... , valorN) PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 62 Exemplo: Inserir uma linha na tabela Professor INSERT INTO Professor (CodP, NomeP, CidadeP, TituloP) VALUES (‘P1', 'Joaquim', 'Rib Preto', 'Mestre') Os valores valor1 , valor2 etc. seguem a ordem dos campos da tabela, sendo utilizado valor vazio (' ') ou a sentença NULL para campos que não necessitem de preenchimento. Dados de tipo numérico podem ser escritos sem a necessidade de aspas simples. Dados do tipo caractere (como char e varchar) devem ser escritos entre aspas simples. Atualização dos dados de uma tabela Alterar dados em uma tabela significa atualizar um dado de uma determinada tabela por outro dado do mesmo tipo. O comando Update pode ser realizado sem o WHERE. Neste caso todas as linhas da tabela serão atualizadas com o valor determinado no comando. Para os casos onde se necessite atualizar apenas linhas que cumpram determinada condição, essa condição é estabelecida com a inclusão do comando WHERE. Sintaxe UPDATE nome_tabela SET nome_coluna = valor, ..... [WHERE (condição de localização) Exemplo: Alterar o valor da mensalidade do curso de Ciência da Computação para 650,00. UPDATE Curso SET MensC = 650 WHERE NomeC = ‘Ciência Comp’ PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 63 Remoção de dados de uma tabela Apagar dados em uma tabela significa eliminar uma ou mais linhas de uma determinada tabela. Para isso utilizamos a instrução DELETE. O comando Delete pode ser realizado sem o WHERE. Neste caso todas as linhas da tabela determinada serão excluídas. Utilizamos WHERE quando desejamos eliminar os registros que obedeçam a certa condição. Sintaxe DELETE FROM nome_tabela WHERE (condição de localização) Exemplo: Remover da tabela Professor todos os professores que têm título de Doutor. DELETE FROM Professor WHERE TituloP = ‘Doutor’ Conceito de transação Uma TRANSAÇÃO é uma unidade de execução programada que acessa e provavelmente atualiza vários itens de dados. Uma transação deve acessar uma base de dados consistente. Durante a execução da transação é possível que a base de dados passe por um estado de inconsistência. Quando a transação é confirmada (COMMIT), a base de dados deverá estar consistente. O gerenciamento de transações lida com 2 situações principais: • Falhas de vários tipos: hardware, energia, travamento de aplicações etc. • Execução concorrente de múltiplas transações. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 64 Propriedades ACID Atomicidade Ou todas ou nenhuma das operações da transação serão devidamente refletidas na base de dados. Consistência Após a execução de uma transação, a base de dados deverá retornar a um estado consistente dos dados. Isolamento Apesar de múltiplas transações poderem ser executadas concorrentemente, cada uma delas deverá estar isolada dos efeitos da execução das outras. Resultados intermediários de uma transação deverão ficar ocultos para as outras. Durabilidade Depois que uma transação termina com sucesso, as alterações que ela realizou sobre o banco de dados persistirão mesmo que haja falhas do sistema. Controle de transações Todas as transações realizadas com o banco deverão ser confirmadas pelo usuário para que sejam realmente efetivadas, ou então descartadas, caso o usuário deseje. • Para confirmar uma transação - ou um bloco de transações - utiliza-se o comando COMMIT. • Para descartar uma transação - ou um bloco de transações - utiliza-se o comando ROLLBACK. PROJETO E OTIMIZAÇÃO DE BANCO DE DADOS 65 O bloco de transações é definido a partir da última ocorrência de COMMIT, ou a partir do início da seção. Comando
Compartilhar