Baixe o app para aproveitar ainda mais
Prévia do material em texto
Recife, 2010 Banco de Dados UNIVERSIDADE FEDERAL RURAL DE PERNAMBUCO (UFRPE) COORDENAÇÃO GERAL DE EDUCAÇÃO A DISTÂNCIA (EAD/UFRPE) Sandra de Albuquerque Siebra Volume 4 Universidade Federal Rural de Pernambuco Reitor: Prof. Valmar Corrêa de Andrade Vice-Reitor: Prof. Reginaldo Barros Pró-Reitor de Administração: Prof. Francisco Fernando Ramos Carvalho Pró-Reitor de Extensão: Prof. Paulo Donizeti Siepierski Pró-Reitor de Pesquisa e Pós-Graduação: Prof. Fernando José Freire Pró-Reitor de Planejamento: Prof. Rinaldo Luiz Caraciolo Ferreira Pró-Reitora de Ensino de Graduação: Profª. Maria José de Sena Coordenação Geral de Ensino a Distância: Profª Marizete Silva Santos Produção Gráfica e Editorial Capa e Editoração: Rafael Lira, Italo Amorim e Arlinda Torres Revisão Ortográfica: Elias Vieira Ilustrações: Mário França Coordenação de Produção: Marizete Silva Santos Sumário Apresentação ................................................................................................................. 4 Conhecendo o Volume 4 ................................................................................................ 5 Capítulo 10 – Álgebra e Cálculo Relacional ..................................................................... 7 Álgebra Relacional ...........................................................................................................7 Álgebra Relacional: Operadores de Tabelas ..................................................................11 Capítulo 11 – Criando e Mantendo um Banco de Dados ............................................... 26 A SQL .............................................................................................................................26 Capítulo 12 – Consultas em Banco de Dados Relacionais .............................................. 39 Inserindo Dados em Tabelas ..........................................................................................39 Considerações Finais .................................................................................................... 75 Conheça a Autora ........................................................................................................ 77 4 Apresentação Caro(a) cursista, Seja bem-vindo(a) ao quarto módulo do curso Banco de Dados! Neste quarto módulo, vamos aprender a manipular um SGBD de forma a criar, manter e consultar o banco de dados que você antes aprendeu a modelar. Para fazer isso, estudaremos principalmente a linguagem SQL, presente em todos os banco de dados relacionais e de grande utilidade no acesso a eles. Adicionalmente, estudaremos um pouco de álgebra relacional e cálculo relacional que são formas de representar matematicamente as operações que podem ser aplicadas a um Banco de Dados. Bons estudos! Sandra de Albuquerque Siebra Autora 5 Banco de Dados Conhecendo o Volume 4 Neste quarto volume, você irá encontrar o Módulo 4 da disciplina de Banco de Dados. Para facilitar seus estudos, veja a organização deste quarto módulo. Módulo 4 – Criação, Manutenção e Consulta a Banco de Dados Carga horária do Módulo 4: 15 h/aula Objetivo do Módulo 4: » Introduzir os principais conceitos referentes a álgebra relacional e o cáclulo relacional como formas de manipulação e consulta a BD. » Examinar os principais comandos em SQL para criação e manutenção de banco de dados. » Examinar os principais comandos em SQL para consultas simples e aninhadas a banco de dados. Conteúdo Programático do Módulo 4: » Álgebra Relacional. » Cálculo Relacional de Tupla e Cálculo Relacional de Domínio. » SQL – Conceitos básicos, comandos para criação e atualização do banco de dados. » SQL - Consultas básicas e Consultas Aninhadas. 6 Banco de Dados Capítulo 10 O que vamos estudar neste capítulo? Neste capítulo, vamos estudar os seguintes temas: » Álgebra Relacional. » Cálculo Relacional de Tupla. » Cálculo Relacional de Domínio. Metas Após o estudo deste capítulo, esperamos que você: » Compreenda o que é álgebra relacional e cálculo relacional. » Consiga diferenciar o cálculo relacional de tupla e de domínio. » Consiga fazer uso de alguns comandos da álgebra relacional. 7 Banco de Dados Capítulo 10 – Álgebra e Cálculo Relacional Vamos conversar sobre o assunto? Até aqui, já estudamos como modelar e otimizar nosso modelo para um banco de dados relacional. Um modelo relacional representa o banco de dados como um conjunto de relações. Uma relação pode ser pensada como uma tabela de valores, onde cada linha na tabela representa uma coleção de valores de dados relacionados. Para acessar e manipular esses valores, há duas categorias de linguagens que podem ser utilizadas: as linguagens formais (a álgebra relacional e o cálculo relacional) e as linguagens comerciais, que são baseadas nas linguagens formais (tal como a SQL – Structured Query Language). As linguagens formais são justamente as que daremos uma olhada nesse capítulo e uma das linguagens comerciais mais utilizadas (a SQL) será assunto dos dois capítulos finais desse volume. Neste capítulo, vamos estudar as linguagens formais para consulta e manipulação dos dados em um banco de dados: a álgebra relacional e o cálculo relacional. Álgebra Relacional A Álgebra Relacional é uma linguagem de consulta formal, porém procedimental (procedural), ou seja, o usuário dá as instruções ao sistema para que o mesmo realize uma sequência de operações na base de dados para calcular o resultado desejado. Ela consiste de um conjunto de operações que têm como entrada uma ou duas relações e produzem, como resultado, uma nova relação. Os operadores da álgebra relacional podem ser divididos em dois grupos: » Operadores de Conjuntos: são operadores típicos definidos pela álgebra para conjunto, tais como união, interseção, diferença e produto cartesiano. As operações com esses operadores se aplicam a duas relações que obedeçam à “compatibilidade de união”, ou seja, ambas as relações devem apresentar atributos que pertençam, respectivamente, aos mesmos domínios. » Operadores de Tabelas: são operadores definidos, especialmente, para a manipulação de tuplas em bases de dados relacionais tais como: Select, Project e Join, entre outras. As operações com esses operadores se aplicam a quaisquer relações. Esses operadores serão detalhados nas seções a seguir. Observação As linguagens disponíveis para acesso a BDs relacionais, inclusive o SQL, não utilizam os mesmos operadores ou nomes definidos pela álgebra relacional. Entretanto todos, ou quase todos, os operadores da álgebra relacional podem ser escritos usando estas linguagens. 8 Banco de Dados Álgebra Relacional: Operadores de Conjuntos São operadores binários e para utilizá-los, é preciso se assegurar de que as duas relações envolvidas nas operações tenham o mesmo tipo de tuplas, ou seja, pertençam ao mesmo domínio. Essa condição é chamada união compatível. Em outras palavras, duas relações R1 (A1, A2,..., An) e R2 (B1, B2, ..., Bn) são união compatível, se elas tiverem o mesmo grau n, e dom(Ai) = dom(Bi) para 1 ≤ i ≤ n. Isso significa que as duas relações têm o mesmo número de atributos e que cada par de atributos correspondentes pertence ao mesmo domínio. Pode-se definir as operações de união, interseção, diferença e produto cartesiano sobre duas relações que sejam união compatível R1 e R2. Resumidamente: » União (R1 ∪ R2) - todas as tuplas de R1 e todas as tuplas de R2, sendo que tuplas duplicadas são eliminadas. » Interseção (R1 ∩ R2) - todas as tuplas comuns a R1 e R2. » Diferença (R1 – R2) - todas as tuplas de R1 que não estão em R2. » Produto Cartesiano (R1 x R2) - combinação das tuplas de R1 com as de R2. Vamos detalhar e exemplificar, a seguir, cada uma dessas operações. Mas, antes, algumas observações sobre essas operações: » As operações de união e interseção são operações comutativas. Ou seja: R1 ∪ R2 = R2 ∪ R1 e R1 ∩ R2 = R2 ∩ R1. » Já a operação de diferença nãoé comutativa: R - S ≠ S - R. » As operações de união e interseção são binárias, mas podem ser aplicadas a qualquer número de relações e ambas são operações associativas. Assim: R ∪ (S ∪ T) = (R ∪ S) ∪ T e R ∩ (S ∩ T) = (R ∩ S) ∩ T. Considere como base para os exemplos das operações as relações Professor e Aluno representadas pelas Tabelas 1 e 2. Tabela 1 - Relação Professor Tabela 2 - Relação Aluno CPF Nome CPF Nome 1001 Ana Maria 1002 João 1002 João 1116 Mércia 1003 Pedro 1900 Ronaldo União: A ∪ B União é a operação entre duas relações “união compatível” (mesmo número de atributos e com domínio compatível) que gera uma relação resultante, contendo todas as tuplas (linhas) das duas relações originais, com eliminação das tuplas duplicadas. Ela é denotada pelo símbolo ∪. Por exemplo, a união das relações Professor e Aluno, representadas pelas Tabelas 1 e 2 é a relação representada pela Tabela 3. 9 Banco de Dados Tabela 3 – Relação Resultante de Professor ∪ Aluno CPF Nome 1001 Ana Maria 1002 João 1003 Pedro 1116 Mércia 1900 Ronaldo Interseção: A ∩ B Interseção é a operação entre duas relações “união compatível” que gera uma relação resultante, contendo todas as tuplas (linhas) presentes em ambas as relações originais. Ela é denotada pelo símbolo ∩. Por exemplo, a interseção das relações Professor e Aluno, representadas pelas Tabelas 1 e 2 é a relação representada pela Tabela 4. Tabela 4 - Relação Resultante de Professor ∩ Aluno CPF Nome 1002 João Diferença: A – B Diferença é a operação entre duas relações “união compatível” que gera uma relação resultante, contendo todas as linhas que estão na primeira relação, e que não aparecem na segunda. Ela é denotada pelo símbolo –. Por exemplo, a diferença das relações Professor e Aluno, representadas pelas Tabelas 1 e 2 (Professor – Aluno) é a relação representada pela Tabela 5, que contém as tuplas das relação Professor que não estão na relação Aluno. Tabela 5 - Relação Resultante de Professor – Aluno CPF Nome 1001 Ana Maria 1003 Pedro Para mostrar que a operação de diferença não é comutativa, vamos agora fazer Aluno – Professor, ou seja, as tuplas que estão na relação Aluno, mas que não estão na relação Professor. Veja pela relação resultante representada na Tabela 6 que os resultados da diferença, mudando a ordem das relações, são diferentes. 10 Banco de Dados Tabela 6 - Relação Resultante de Aluno – Professor CPF Nome 1116 Mércia 1900 Ronaldo Produto Cartesiano: A x B O produto cartesiano combina duas relações, gerando uma terceira cujas linhas representam todas as possíveis combinações das linhas (tuplas) das relações originais. Um esquema dessa combinação pode ser vista na Figura 1. Figura 1 - Esquema de combinações do Produto Cartesiano O produto cartesiano é uma operação binária e é representado pelo símbolo X. O formato geral da operação é: relação_1 X relação_2. Vamos dar um exemplo. Qual seria o resultado do produto cartesiano Cidade x Estado (vide Tabelas 7 e 8)? Tabela 7 - Relação Cidade Tabela 8 - Relação Estado Código Nome UF Região 1 Recife PB Nordeste 2 Manaus AM Norte 3 João Pessoa O resultado seria a relação resultante apresentada na Tabela 9. Veja que esta relação resultante apresenta a combinação de todas as tuplas da relação Cidade, com todas as tuplas da relação Estado. 11 Banco de Dados Tabela 9 - Relação Resultante Código Nome UF Região 1 Recife PB Nordeste 2 Natal PB Nordeste 3 João Pessoa PB Nordeste 1 Recife AM Norte 2 Natal AM Norte 3 João Pessoa AM Norte Renomear para diferenciar: O Produto Cartesiano entre duas relações R1 e R2 (R1 x R2) apresenta problemas quando: * As relações têm mesmo nome; * Se se quer fazer o produto de uma relação com ela mesma ou * O produto cartesiano está envolvido com a relação resultante de expressões (que vamos ver o que são daqui a pouco). Isso porque um mesmo nome de atributo pode aparecer tanto em R1 quanto em R2. Por isso, é preciso estabelecer um modo de diferenciar esses atributos na relação resultante. Para isso, pode- se anexar ao atributo o nome da relação a qual ele pertença: nome_relacao.nome_atributo. Por exemplo, se fizéssemos o produto cartesiano entre as relações Professor e Aluno (vide Tabelas 1 e 2), como as duas tabelas possuem atributos de mesmo nome, a relação resultante ficaria com as seguintes colunas: (Professor.CPF, Professor.Nome, Aluno.CPF, Aluno.Nome) Álgebra Relacional: Operadores de Tabelas Os operadores de tabela manipulam tuplas em bases de dados relacionais e podem ser aplicados a quaisquer relações. A seguir, descreveremos cada uma das operações. Seleção O operador de seleção é usado para selecionar tuplas que satisfaçam uma determinada condição. Essas tuplas selecionadas geram uma relação resultante. O esquema da relação resultante é o mesmo da relação original. O operador de seleção é representado pela letra grega sigma (δ) e seu formato geral é: δ condição(tabela ou relação). O operador da seleção é unário, ou seja, seleciona tuplas de somente uma relação. Vamos dar alguns exemplos a partir da relação Cidade (vide Tabela 10). 12 Banco de Dados Tabela 10 - Relação Cidade Código Nome É_capital UF 1 Recife S PE 2 Natal S RN 3 João Pessoa S PB 4 Patos N PB δ UF = ‘PB’(cidade) daria como resultado a relação da Tabela 11, que atenderia a algo como: selecione, da relação cidade, as tuplas cuja UF seja igual ao valor ‘PB’. Tabela 11 - Relação Resultante Código Nome É_capital UF 3 João Pessoa S PB 4 Patos N PB Para especificar as condições da seleção, podemos utilizar: valores constantes, nome de atributos (colunas), operadores relacionais (=, <, >, <=, >=, ≠) ou operadores lógicos (and, or, not). Por exemplo, δ UF=’PB’ and E_Capital=’N’(CIDADE) daria como resultado a relação da Tabela 12, que atenderia a algo como: selecione da relação cidade, as tuplas cuja UF seja igual ao valor ‘PB’ e o campo E_Capital tenha o valor ‘N’. Tabela 12 - Relação Resultante Código Nome É_capital UF 4 Patos N PB Vamos a outro exemplo: suponha o esquema de relação Empregado (CPF, Nome, DataNasc, Endereço, Sexo, NumDep, Salario). Agora, suponha que desejamos selecionar os empregados que trabalham no departamento 10 e ganham mais de 1500 ou aqueles que trabalham no departamento 3 e ganham mais que 4000. Como ficaria? δ (NumDep = 10 and salario > 1500) or (NumDep=3 and salario > 4000) (Empregado) A operação de seleção é comutativa, ou seja, uma sequência de seleção pode ser aplicada em qualquer ordem: δ <cond1> (δ <cond2> (Relação)) = δ <cond2> (δ <cond1> (Relação)) Sempre é possível combinar uma propagação de operações de seleção dentro de uma única operação de seleção, fazendo uso de uma condição conjuntiva (AND): δ <cond1> (δ <cond2> (Relação)) = δ <cond1> and <cond2> (Relação) δ <cond1> (δ <cond2> ( ... (δ <condN> (Relação)) ...)) = δ <cond1> AND <cond2> AND ... AND <condN> (Relação) 13 Banco de Dados Projeção A operação de projeção é unária e opera sobre uma única relação, gerando outra relação resultante que conterá todas as linhas da relação original, mas apenas as colunas (atributos) que se deseja projetar (e que foram especificadas na operação). Ou seja, retorna parte da relação, deixando de fora os atributos que não foram solicitados. Na relação resultante, as tuplas (linhas) duplicadas são eliminadas. O formato geral da operação de projeção é: π A1, A2, …, An (Relação) onde A1, A2, ..., An são nomes de atributos da relação. Vamos dar um exemplo. Tomando como base a relação Cidade (vide Tabela 10), vamos supor que queremos selecionar (projetar) apenas os atributos Nome e UF. Daí precisaríamos da seguinte operação: π Nome, UF (Cidade). A relação resultante dessa projeção seria a apresentada na Tabela 13. Observe que a relação resultante tem todas as tuplas da relação original, mas só apresenta os atributos especificados na operação de projeção. Além disso, observe que estes atributos aparecem na mesma ordem emque foram especificados. Tabela 13 - Relação Resultante da Projeção Nome UF Recife PE Natal RN João Pessoa PB Patos PB Na verdade, se observarmos bem, o operador de projeção também serve para selecionar. Porém, enquanto o operador de SELEÇÃO seleciona tuplas de uma relação, o operador de PROJEÇÃO seleciona colunas de uma Relação. O número de tuplas na relação resultante sempre será igual ou menor que a quantidade de tuplas na relação original. Isto porque tuplas duplicadas são eliminadas. Por exemplo, observe a seguinte operação de projeção π Nome, UF (Cidade) aplicada à relação Cidade (vide Tabela 10). Veja que “PB” aparece apenas uma vez na relação resultante (vide Tabela 14). Mesmo que ela apareça duas vezes na relação original (Tabela 10). Tabela 14 - Relação Resultante da Projeção UF PE RN PB A operação de projeção não é comutativa. Apenas no caso específico de <lista2> conter os mesmos atributos de <lista1> pode-se observar comutatividade. π <lista_atributos1> (π <lista_atributos2> (Relação)) ≠ π <lista_atributos 2> (π<lista_atributos1> (Relação)) 14 Banco de Dados Combinando Operações Em geral, existe a necessidade de se aplicar várias operações da álgebra relacional uma após a outra. Pode-se escrever essas operações em apenas uma única expressão da álgebra relacional, combinando as operações, usando tanto operadores de conjunto, quanto de tabela. Vamos dar alguns exemplos a seguir. » Tomando como base as relações Professor(CPF, Nome) e Aluno (CPF, Nome) - vide Tabelas 1 e 2 – se desejássemos encontrar o nome de todos os professores que também são alunos, poderíamos usar a expressão: Π nome(Professor) ∩ Π nome(Aluno) » A partir da relação Empregado (CPF, Nome, Sexo, Salario, Num_Dep), vide Tabela 15, selecione o nome e o salário de todos os empregados que trabalhem no departamento de número 4. Para isso, poderíamos usar a expressão: Π nome, salario (δ Num_Dep=4(Empregado)) Isso daria origem a tabela 16. Tabela 15 - Relação Empregado CPF Nome Sexo Salario Num_Dep 1234 Ana Gomes F 1500 2 2345 Pedro Nunes M 1000 4 3765 Maria Lima F 2000 2 4987 Igor Matos M 3500 5 9876 Laís Ramos F 3000 4 Tabela 16 - Relação Resultante Nome Salario Pedro Nunes 1000 Laís Ramos 3000 Tabela 17 - Relação Gerente CPF Nome Sexo Salario Num_Dep 5678 João Teixeira M 1800 2 2345 Pedro Nunes M 1000 4 » A partir da relação Empregado (Tabela 15) e da relação Gerente (Tabela 17), encontre o nome de todos os empregados que não são gerentes. Para isso, poderíamos usar a expressão: Π nome (Empregado) - Π nome (Gerente) 15 Banco de Dados Outra forma de combinar operações é aplicar uma única operação por vez e criar relações intermediárias. Neste caso, é preciso dar nomes às relações intermediárias. Por exemplo, vamos tomar o exemplo anteriormente visto de tomar a relação Empregado como base (Tabela 15) e dela recuperar o nome e o salário de todos os empregados que trabalham no departamento 4. Já apresentamos como fazer isso com uma única expressão. Agora, alternativamente, seria possível explicitar a sequência de operações, dando um nome para cada relação intermediária: Empregados_Dep4 ← ß δ Num_Dep=4(Empregado) Π nome, salario (Empregados_Dep4) Junção O operador de junção combina as linhas de duas tabelas (relações) segundo uma ou mais condições. A condição de junção deve ser baseada em uma ou mais colunas (atributos) de cada uma das tabelas cujos valores compartilhem um domínio comum. As linhas das tabelas serão combinadas sempre que a condição de junção for verdadeira. (geralmente a condição é uma igualdade entre atributos equivalentes). O join é representado pelo operador binário |x| e o formato geral de utilização é: Relação1 |x| <condição de junção> Relação2. Por exemplo, Cidade |x| cidade.UF = Estado.UF Estado. Vai combinar os valores das relações Cidade (Tabela 18) e Estado (Tabela 19), de acordo com a igualdade do atributo comum às duas relações: a UF. Dessa forma, seria gerada a relação representada na Tabela 20. Veja que a primeira tupla da relação Cidade (vide Tabela 18) não faz parte da relação resultante, porque ela não tem equivalente na relação Estado (já que está sendo feita a igualdade com base no atributo UF). Ou seja, tuplas cujos atributos de junção são nulos não aparecem na relação resultante. Tabela 18 - Relação Cidade Código Nome Sexo 1 Recife PE 2 Manaus AM 3 João Pessoa PB Tabela 19 - Relação Estado UF Região PB Nordeste AM Norte Tabela 20 - Relação Resultante Código Nome Cidade.UF Estado.UF Região 2 Manaus AM AM Norte 3 João Pessoa PB PB Nordeste 16 Banco de Dados Quando a condição de uma junção é a igualdade, a junção é chamada de equijoin. O equijoin gera duas colunas idênticas na relação resultante (vide as colunas UF na Tabela 20). Uma Junção Natural é um equijoin onde uma das colunas idênticas é eliminada. Como assim? O operador de junção natural combina as linhas de duas tabelas que têm atributos comuns (mesmo nome), resultando numa tabela que contém apenas as linhas onde todos os atributos comuns apresentam o mesmo valor. Na relação resultante, uma das colunas idênticas é eliminada, evitando a duplicidade. A junção natural equivale a uma seleção precedida de um produto cartesiano. Assim: Cidade |x| cidade.UF = Estado.UF Estado é equivalente a δ cidade.UF = Estado.UF (Cidade x Estado) Se duas relações envolvidas em uma junção natural não têm atributos em comum, então a junção natural produz um resultado igual ao produzido pelo produto cartesiano. » EQUIJOIN: junção onde somente operadores de comparação “=“ são utilizados. Gera colunas idênticas. » JUNÇÃO NATURAL: requer que os dois atributos de junção tenham o mesmo nome em ambas as relações. Nesse tipo de join, outras condições podem ser utilizadas além da igualdade. A relação resultante não gera nenhuma duplicidade. Cálculo Relacional O Cálculo Relacional (CR) é uma linguagem de consulta formal. Utilizando-se de uma expressão declarativa, pode-se especificar uma consulta. Uma expressão de cálculo permite a descrição da consulta desejada sem especificar os procedimentos para obtenção dessas informações, ou seja, é não-procedural. Contudo, tal consulta deve ser capaz de descrever formalmente a informação desejada, com exatidão. No Cálculo Relacional, existem variáveis, constantes, operadores lógicos, de comparação e quantificadores. As expressões de Cálculo são chamadas de fórmulas. Uma tupla de respostas é essencialmente uma atribuição de constantes às variáveis que levam a fórmula a um estado verdadeiro. Existem dois tipos de cálculo relacional: Cálculo Relacional de Tuplas (CRT) e Cálculo Relacional de Domínio (CRD), ambos subconjuntos simples de lógica de primeira ordem. No CRT, as variáveis são definidas sobre (isto é, associam) tuplas. Já em CRD, variáveis são definidas sobre o domínio dos elementos (ou seja, sobre os valores dos campos). Como o cálculo relacional é pouco utilizado, vamos apenas apresentar algumas definições e exemplos, a título informativo, de cada um dos tipos de cálculo. Adicionalmente, é importante saber que todas as expressões de consulta descritas no Cálculo Relacional possuem equivalentes em Álgebra Relacional, que é mais utilizada e possui mais ferramentas para dar suporte a construção de suas expressões. Cálculo Relacional de Tupla O Cálculo Relacional de Tupla (CRT) é baseado na especificação de um número de variáveis de tuplas. Cada variável de tupla pode assumir como seu valor qualquer tupla da relação especificada. Uma consulta em CRT é especificada da seguinte forma: 17 Banco de Dados {variável tupla | predicado} ou { t | P(t) } que significa o conjunto de todas as tuplas t, tal que o predicado P seja verdadeiro para t. E temos que t é uma variável de tuplas. P é uma expressão condicional e t.A ou t[A] denota o valor do atributo A da tupla t. O resultado de tal consulta é o conjunto de todas as variáveis tuplas para as quais o predicado é indicadocomo verdadeiro. Uma expressão genérica do cálculo relacional de tuplas tem a forma: {t1.A1, t2.A2, ..., tn.An | predicado(t1, t2, ..., tn, tn+1, tn+2, ..., tn+m)} Onde: t1, t2, ..., tn, tn+1, tn+2, ..., tn+m são variáveis de tuplas, cada Ai é um atributo da relação na qual ti se encontra e o predicado é uma fórmula do cálculo relacional de tuplas. Uma fórmula é definida, de forma recursiva, por uma ou mais fórmulas atômicas. Essas fórmulas podem ser conectadas por operadores lógicos (AND, OR ou NOT), como segue: » Se F1 e F2 são fórmulas atômicas, então (F1 AND F2), (F1 OR F2), NOT (F1) e NOT (F2) também o são, tendo seus valores verdade derivados a partir de F1 e F2. Relembrando... (F1 AND F2) será TRUE apenas se ambos, F1 e F2, forem TRUE; (F1 OR F2) será TRUE quando uma das duas fórmulas F1 e F2, for TRUE; NOT(F1) será TRUE quando F1 for FALSE; NOT(F2) será TRUE quando F2 for FALSE. » Se F1 é uma fórmula atômica, então (Ǝ t)(F1) também o é, e seu valor verdade apenas será TRUE se a fórmula F for avaliada como verdadeira para, pelo menos, uma tupla atribuída para ocorrências livres de t (que é uma variável de tupla) em F. » Se F1 é uma fórmula atômica, então (∀ t)(F1) também o é, e seu valor verdade apenas será TRUE se a fórmula F for avaliada como verdadeira para todas as tuplas atribuídas para ocorrências livres de t em F. Adicionalmente, temos: » Uma fórmula atômica ti.A op tj.B, onde op é um dos operadores de comparação no conjunto {=, >, <, ≠, >=, <=}, ti e tj são variáveis de tuplas, A é um atributo da relação na qual ti se encontra, B é um atributo da relação na qual tj se encontra. » Uma fórmula atômica ti.A op c ou c op tj.B, onde op é um dos operadores de comparação no conjunto {=, >, <, ≠, >=, <=}, ti e tj são variáveis de tuplas, A é um atributo da relação na qual ti se encontra, B é um atributo da relação na qual tj se encontra e c é um valor constante. Nos dois casos acima, se as variáveis de tupla forem designadas de forma que os valores dos atributos especificados satisfaçam o predicado, a fórmula assumirá valor verdade TRUE. Cada uma das fórmulas atômicas anteriormente especificadas tem seu valor verdade avaliado como TRUE ou FALSE para uma combinação específica de tuplas. 18 Banco de Dados Todas as variáveis tuplas abordadas são consideradas variáveis livres (elas aparecem em uma expressão de cálculo relacional à esquerda da barra |), uma vez que estas não aparecem quantificadas. Porém, quando quantificadores (universal (∀) ou existencial (Ǝ)) aparecem nas fórmulas, as variáveis que os sucedem são denominadas variáveis limite. Tabela 21 - Relação Empregado CPF Nome Sexo Salario Cod_Depto 1234 Ana Gomes F 1500 2 2345 Pedro Nunes M 1000 4 3765 Maria Lima F 2000 2 4987 Igor Matos M 3500 5 9876 Laís Ramos F 3000 4 Tabela 22 - Relação Departamento Cod Descricao 2 Vendas 4 Suporte 5 Gerência Vamos dar alguns exemplos para ilustrar. Tomando como base a relação Empregado (vide Tabela 21) e a relação Departamento (vide Tabela 22), suponha as seguintes consultas e como elas ficariam representadas em cálculo relacional de tupla. » Obtenha todos os empregados cujo salário seja maior que 3000 reais: { t | EMPREGADO(t) AND t.SALARIO > 3000 }. Analisando a expressão, podemos lê-la da seguinte forma: considere uma tupla t, ela deve ser uma tupla da relação empregado, cujo atributo salário dessa tupla deve ser maior que 3000. EMPREGADO(t) é o mesmo que dizer que t ∈ EMPREGADO. A consulta acima resulta em uma relação que contém todas as tuplas t da relação EMPREGADO que satisfazem a condição (no caso, salário > 3000). No CRT, especificamos primeiro os atributos desejados. Se for usado apenas o t, sem especificação de atributos, todos os atributos da tupla são recuperados. Logo, na consulta acima, seriam recuperados os atributos CPF, Nome, Sexo, Salario e Cod_Depto (vide Tabela 21). Agora, suponha que gostaríamos de recuperar apenas os atributos CPF e Nome das tuplas que atendessem a condição. Como faríamos? { t.CPF, t.Nome | EMPREGADO(t) AND t.SALARIO > 3000 }. Observe que os atributos desejados são especificados do lado esquerdo da barra (|). » Vamos a outro exemplo. Obtenha o nome e o salário dos empregados que trabalham para o departamento de Suporte. 19 Banco de Dados {t.NOME, t.SALARIO | EMPREGADO(t) AND (Ǝ d) (DEPARTAMENTO (d) AND d.DESCRICAO = ‘Suporte’ AND d.COD = t.COD_DEPTO)} Analisando a expressão, podemos lê-la da seguinte forma: obtenha o nome e o salário de todas as tuplas da relação empregado e exista um departamento d, cuja descrição seja ‘Suporte’ e o código desse departamento de nome ‘Suporte’ seja igual ao código do departamento da tupla, sendo avaliada na relação Empregado. Vamos exemplificar agora o quanto o CRT pode ser representado facilmente por uma expressão da álgebra relacional, levando em conta a relação Empregado (Tabela 21). » Recupere o CPF e o nome de todos os empregados. Em CRT: { t.CPF, t.Nome | EMPREGADO(t) } Em Álgebra Relacional: π CPF, Nome (Empregado) » Recupere todos os empregados do sexo masculino Em CRT: { t | EMPREGADO(t) AND t.SEXO = ‘M’ } Em Álgebra Relacional: δ Sexo = ‘M’ (Empregado) Cálculo Relacional de Domínio (CRD) Trata-se de uma segunda forma de cálculo relacional, equivalente ao CRT. Essa forma usa variáveis de domínio que tomam valores do domínio de um atributo, em vez de valores da tupla inteira. Uma expressão, neste cálculo, tem a forma: { <x1, x2, ..., xn > | Predicado (x1, x2, ..., xn) } onde x1, x2, ..., xn representam variáveis de domínio e Predicado representa uma fórmula composta de átomos, como no cálculo relacional de tupla. A diferença básica entre CRT e CRD é que, neste último, as variáveis estendem-se sobre valores únicos de domínios de atributos. Para formar uma relação de grau n para um resultado de consulta, faz-se necessário criar n variáveis de domínio, uma para cada atributo. Como em CRT, as fórmulas são avaliadas em valores verdade para um conjunto específico de valores. A seguir, para fins de comparação e para ilustrar o CRD, seguem em CRD os mesmos exemplos de consultas já escritos em CRT. Tabela 23 - Relação Empregado a b c d e CPF Nome Sexo Salario Cod_Depto 1234 Ana Gomes F 1500 2 2345 Pedro Nunes M 1000 4 3765 Maria Lima F 2000 2 4987 Igor Matos M 3500 5 9876 Laís Ramos F 3000 4 20 Banco de Dados Tabela 24 - Relação Departamento m n Cod Descricao 2 Vendas 4 Suporte 5 Gerência Tomando como base as tabelas 23 e 24 que representam, respectivamente, as relações Empregado e Departamento (note que cada coluna dessas relações recebeu uma letra para referenciar o domíno do atributo representado por cada coluna), podemos realizar as seguintes consultas: » Obtenha todos os empregados cujo salário seja maior que 3000 reais: { t | EMPREGADO(t) AND t.SALARIO > 3000 }. { abcde1 | (Ǝ d2) EMPREGADO(abcde) AND d > 3000 } » Se, na consulta anterior, quiséssemos recuperar apenas o CPF e o nome dos empregados, teríamos: { ab | (Ǝ d) EMPREGADO(abcde) AND d > 3000 } » Obtenha o nome e o salário dos empregados que trabalham para o departamento de Suporte. { bd | (Ǝ e) (Ǝ m)(Ǝ n) (EMPREGADO(abcde) AND DEPARTAMENTO(mn) AND n = ‘Suporte’ AND m = e) } Considerações Finais A álgebra relacional é uma forma de cálculo sobre conjuntos ou relações. Uma aplicação prática da álgebra relacional é na execução de consultas a bancos de dados relacionais. A álgebra relacional recebia pouca atenção até a publicação do modelo relacional de dados de E.F Codd, em 1970. Codd propôs tal álgebra como uma base para linguagens de consulta em banco de dados. As operações da álgebra relacional podem ser resumidas de forma ilustrada na Figura 2. Comentário 1 Observe que as letras à esquerda da barra (|) representam o domíno dos atributos desejados (vide Tabela 23), como conseqüência, eles referenciam as colunas desejadas. Comentário 2 Somente é necessário quantificar as variáveis que participamde uma condição. Ou seja, só usamos o operador existencial Ǝ na variável de domínio d, porque apenas essa variável é usada na condição expressa no predicado (d > 3000) 21 Banco de Dados Figura 2 - Resumo das Operações da Álgebra Relacional O Cálculo Relacional é uma linguagem formal, não-procedural, para consulta a relações. A álgebra relacional tem poder de expressão essencialmente equivalente ao do cálculo relacional, esse resultado é conhecido como teorema de Codd. Em geral, a álgebra relacional é bem mais utilizada do que o cálculo relacional. Conheça Mais Para obter mais informações sobre o assunto estudado nesse capítulo, você pode consultar qualquer um dos livros listados a seguir. Todos eles possuem capítulos dedicados a Álgebra Relacional e o Cálculo Relacional (de Tupla e de Domínio): KORTH, Henry F; SILBERSCHATZ, Abraham; SUDARSHAN, S. Sistema de banco de dados. Traduzido por Daniel Vieira. Rio de Janeiro: Elsevier; Campus, 2006. ELMASRI, Ramez; NAVATHE, Shamkant B. Sistemas de banco de dados. 4a. ed. São Paulo: Pearson Education do Brasil, 2005. DATE, C. J. Introdução a sistemas de bancos de dados. Rio de Janeiro: Campus, 2000. ALVES, W.P. Fundamentos de Bancos de Dados. Editora Érica, 2004. 22 Banco de Dados Você Sabia? O Cálculo relacional, dependendo do autor, pode ter sua notação ligeiramente modificada. A notação que fizemos uso nesse capítulo é a notação de Navathe (ELMASRI e NAVATHE, 2005). Porém, existe também a notação de Korth (KORTH, SILBERSCHATZ e SUDARSHAN, 2006). Para ilustrar as diferenças, vamos repetir a mesma consulta anteriormente realizada: » Obtenha o nome e o salário dos empregados que trabalham para o departamento de Suporte. Na notação de Navathe, que já utilizamos, ficaria: {bd | (Ǝ e) (Ǝ m) (Ǝ n) (EMPREGADO(abcde) AND DEPARTAMENTO(mn) AND n = ‘Suporte’ AND m = e)} Já na notação de Korth, a mesma sentença ficaria da seguinte forma: {<b, d> | <b, d> � empregado ∧ departamento(mn) ∧ n = “Suporte” ∧ m = e)} Veja que o formato geral da expressão muda um pouco, mas o que muda mesmo é a simbologia dos operadores lógicos: AND (∧), OR (∨), NOT (¬), a forma de expressar as variáveis de tupla (observe o começo das duas expressões e veja a diferença) e ao invés de aspas simples em constantes, usa-se aspas duplas. Aprenda Praticando Como a álgebra relacional é mais utilizada, vamos avaliar alguns exercícios resolvidos para que, logo depois, você possa resolver os seus exercícios sozinho. 1) Tome como base os esquemas das tabelas 25 e 26 e mostre como ficariam as consultas, a seguir, em álgebra relacional. Tabela 25 - Relação Devedor Nome Num_Emprestimo Sexo Ana Gomes 01 F Pedro Nunes 03 M Maria Lima 05 F Tabela 26 - Relação Empréstimo Agencia Num_Emprestimo Valor 3456 01 1500 2123 03 5000 2123 05 2500 Em geral, na realização de consultas as operações mais utilizadas são a projeção (representada pelo símbolo π) que seleciona quais colunas (atributos) se deseja na relação resultante e a seleção (representada pelo símbolo δ) que seleciona quais tuplas da relação atendem a uma determinada condição. Se a projeção não for utilizada, todos os atributos da relação original farão parte da relação resultante. Quando necessitamos de dados que estão 23 Banco de Dados em duas relações diferentes, devemos utilizar a operação de junção |x|. » Apresentar os dados de todos os devedores do sexo feminino. Cada vez que desejamos selecionar tuplas da relação de acordo com uma determinada condição, usamos a seleção. δ sexo = ‘F’ (devedor) » Apresentar o nome e o número do empréstimo de todos os devedores do sexo masculino. Como é especificado o que deve ser mostrado – nome e número empréstimo – é necessário usar a projeção antes da seleção) π nome,num_emprestimo (δ sexo = ‘M’ (devedor)) » Mostrar o nome das pessoas que possuem empréstimo acima de três mil reais na agência 2123. Como o nome da pessoa está na relação devedor e o valor do empréstimo na relação empréstimo, é necessário fazer a junção dessas duas relações, para ser possível selecionar o que foi pedido. A junção é feita equiparando os atributos que as relações têm em comum. π nome (δ valor > 3000 and agencia = 2123 (Devedor |x| devedor.num_emprestimo = empréstimo.num_emprestimo Emprestimo)) » Apresentar as pessoas cujo número do empréstimo é igual a 5 e o número da agência é igual a 2123 ou que o número do empréstimo seja igual a 3 e o valor do empréstimo maior do que 1000 reais. É um caso de seleção onde é necessário usar operadores lógicos para montar as condições. δ (num_emprestimo = 5 and agencia = 2123) or (num_emprestimo = 3 and valor > 1000) (Emprestimo)) Atividades e Orientações de Estudo Agora vamos exercitar o que foi estudado neste capítulo. Assim sendo, faça as atividades sugeridas a seguir. Lembre que exercitar vai lhe ajudar a fixar melhor o conteúdo estudado. Mãos à obra! Atividades Práticas Responda as questões a seguir em um documento de texto (doc)3 e poste as respostas no ambiente virtual, no local indicado. Esse trabalho deve ser feito em DUPLA. Especifique, usando a álgebra relacional, as consultas a seguir, tomando como base as relações cujos esquemas estão representados a seguir. EMPREGADO CPF (PK) Nome DtNasc Endereco Sexo Salario Cod_Dep (FK) Dica 3 No Word, a simbologia usada na álgebra relacional pode ser inserida no documento através do seguinte caminho: Inserir -> Símbolo, fazendo uso da fonte Symbol (daí você escolhe na tabela que é apresentada o símbolo apropriado) 24 Banco de Dados DEPARTAMENTO Cod_Dep (PK) Descricao CPF_Gerente (FK) Dt_Inicio_Gerencia PROJETO Cod_Proj (PK) Nome_Proj Cod_Dep (FK) ALOCACAO CPF (PK) Cod_Proj (PK) Horas DEPENDENTE CPF (PK) Nome_Depen Sexo Parentesco a) Recuperar os nomes de empregados do departamento 6 que trabalham mais que 20 horas no projeto chamado ‘Star Project’. b) Listar os nomes dos empregados que tenham um dependente com o mesmo nome deles. c) Recuperar os códigos e os nomes dos projetos do departamento de nome “Pesquisa”. d) Listar o nome do projeto, do departamento ao qual ele pertence e o nome dos empregados deles. e) Recuperar os nomes dos empregados que trabalham em todos os projetos. f) Recuperar os nomes dos empregados que não trabalham em quaisquer projetos. h) Recuperar o nome e o sexo de todos os dependentes do empregado de CPF de número 12345. i) Recuperar o nome e a quantidade de horas trabalhadas por cada empregado em cada projeto do qual faz parte. Vamos Revisar? Você estudou, neste capítulo, formas de acessar e manipular os dados armazenados em um banco de dados, fazendo uso de linguagens formais tais como a álgebra relacional e o cálculo relacional (tanto de tupla, quanto de domíno). A maneira de raciocionar, fazendo uso dessas linguagens, irá facilitar a compreensão do uso da linguagem comercial a ser estudada nos capítulos seguintes: a SQL. Até lá! 25 Banco de Dados Capítulo 11 O que vamos estudar neste capítulo? Neste capítulo, vamos estudar os seguintes temas: » A Linguagem SQL. » Subdivisões da SQL. » Como criar um Banco de Dados usando SQL. Metas Após o estudo deste capítulo, esperamos que você: » Conheça a linguagem SQL. » Conheça as subdivisões da linguagem. » Consiga criar e manter a estrutura de um banco de dados usando SQL. » Consiga criar índices para um banco de dados. 26 Banco de Dados Capítulo 11 – Criando e Mantendo um Banco de Dados Vamos conversar sobre o assunto? “No capítulo anterior, vimos linguagens formais para consulta a banco de dados relacionais. Porém, na área comercial, essas linguagens não são muito utilizadas, ao invés delas, são usadas linguagens comerciais para criação, manutenção e consulta a banco de dados. Entre essas linguagens, a SQL (Structured Query Language) é a mais utilizada. Por isso mesmo, a interface SQL é implementada em todos os sistemas de bancos de dados relacionais existentes. É justamente sobre a SQL que estudaremosnesse capítulo e no capítulo seguinte.” Neste capítulo, começaremos estudando a linguagem comercial SQL, com o objetivo de ter um conhecimento geral sobre a linguagem, além de criar e manter esquemas de bancos de dados relacionais. Adicionalmente, também apresentaremos como criar índices para esses esquemas. Vamos lá? A SQL SQL ou Structured Query Language (Linguagem de Consulta Estruturada) é uma linguagem de consulta declarativa, não-procedural, fundamentada na álgebra e no cálculo relacional de tupla. Apesar de ser chamada linguagem de consulta (Query), ela não é apenas de consulta, ela inclui comandos para definição, manutenção e consulta em bancos de dados relacionais. Além disso, ela define mecanismos para criação de visões, especificações de segurança, autorizações, definições de restrições e controle de transações. Adicionalmente, ela possui regras para embutir os comandos SQL em linguagens de programação genéricas como Java, PHP, C# ou C/C++. A SQL foi desenvolvida pelo laboratório da IBM, nos anos 70, como parte do sistema System R (o primeiro SGBD relacional). Ela foi, inicialmente, chamada de SEQUEL (Structured English Query Language), mas teve seu nome alterado para SQL por razões Jurídicas. Em 1986, em um esforço conjunto da ANSI (American Nacional Standars Institute) e da ISO (International Standards Organization) criou-se a primeira versão padrão da SQL, a SQL-86 (SQL1), substituída posteriormente pela SQL-92 (SQL2) e depois pela SQL-99 (SQL3). O atual projeto da SQL é o padrão 200n. A SQL padrão é suportada por todos os SGBDs relacionais comerciais. Porém, mesmo padronizada, existem variações, ou seja, cada fornecedor pode incluir comandos próprios na SQL utilizada pelo seu SGBD. Em outras palavras, cada implementação do SQL de cada fornecedor possui os comandos do SQL padrão (também chamado SQL ANSI) e, também, algumas adaptações para resolver certas particularidades. Para conhecer o conjunto completo de comandos SQL de um determinado fornecedor (ex: Oracle), recomendamos a leitura do manual do fabricante. A vantagem de fazer uso apenas do SQL padrão é não ter problemas com migração de SGBD para SGBD. Por exemplo, se você fazia uso de SQL Server e, depois, migrou para o uso do Oracle, se fez uso apenas do SQL padrão, 27 Banco de Dados não haverá problemas ou necessidade de adaptações. O SQL usado nesta disciplina será o baseado no Padrão ANSI e nenhuma característica específica de SGBD será abordada. Dessa forma, para praticar os comandos de SQL aqui ensinados, você poderá fazer uso de qualquer SGBD comercial. Subdivisão da SQL A SQL é composta por grupos de instruções que são utilizadas no processo de administração e controle de bancos de dados. Esses grupos serão descritos a seguir (vide Figura 3). » DDL (Data Definition Language - Linguagem de Definição de Dados) - as instruções do tipo DDL permitem efetuar a criação das estruturas (esquemas) de tabelas (relações) onde os dados serão armazenados, índices e os bancos de dados como um todo. Permitem também efetuar alterações nas estruturas criadas, bem como remover estruturas existentes. Neste grupo, estão as instruções: CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX e DROP INDEX. O resultado da compilação dos parâmetros/comandos DDL geram os dicionários de dados (arquivo de metadados). Adicionalmente, a DDL inclui comandos para definição de visões e para especificação de direitos de acesso às relações/visões. Figura 3 - Subdivisões da SQL » DML (Data Manipulation Language - Linguagem de Manipulação de Dados) - as instruções do tipo DML permitem efetuar a manipulação dos dados que estejam armazenados nas tabelas de um determinado banco de dados. Desta forma, é possível cadastrar, alterar e excluir registros (tuplas) existentes. Neste grupo, encontram-se as instruções: INSERT, SELECT, UPDATE e DELETE. De todos os comandos existentes, o comando SELECT é o mais importante e utilizado, pois é com ele que se obtém a extração de informações a partir do banco de dados. » DCL (Data Control Language - Linguagem de Controle de Dados) – as instruções do tipo DCL permitem controlar o acesso e os privilégios dos usuários às relações e visões, protegendo os dados de manipulações não autorizadas; » TML (Transactions Manipulation Language - Linguagem de Manipulação de Transações) – as intruções do tipo TML especificam as transações através de comandos de iniciação e finalização das mesmas, garantindo o compartilhamento e a integridade dos dados. Nesta disciplina apenas abordaremos a DDL e a DML. 28 Banco de Dados Tipos de Dados Antes de entrar nos comandos propiamente ditos da SQL, vale a pena comentar sobre tipos de dados. Para definir os atributos das tabelas, precisamos definir os domínios de cada um deles. Isso é feito através da especificação do tipo do dado. Nesse ponto, é importante ressaltar que cada SGBD tem um conjunto próprio de tipos de dados. Mas, podemos dizer que, genericamente, vamos encontrar na maioria dos SGBDs tipos como: » Char(X): Para dados caracteres, onde X é o tamanho máximo permitido de caracteres e esse tamanho é fixo. Ou seja, se for especificado, por exemplo, um tamanho de 50 caracteres, sempre será ocupado na memória 50 posições, independente da palavra sendo armazenada. » Varchar(X): Idem o anterior, mas o tamanho armazenado é variável. Se ocupará memória apenas para o que for digitado, tendo o X apenas como referência para tamanho máximo. » Integer: Para dados numéricos inteiros positivos ou negativos » Decimal(X,Y): Pada dados numéricos decimais, onde X é o tamanho máximo permitido da parte inteira e Y é o tamanho máximo da parte fracionária » Date: Para datas. Seu formato depende do SGBD relacional. E cada SGBD pode ter um tipo diferenciado para armazenamento de datas. » Logical: Para os valores lógicos TRUE ou FALSE. Comandos SQL para Definição de Dados (DDL) A DDL serve para expressar a especificação do esquema do BD. O resultado da compilação dos parâmetros DDLs é um conjunto de tabelas que são armazenadas em um arquivo especial chamado dicionário de dados4. Os comandos SQL para definição de dados são: CREATE, DROP e ALTER. Vamos dar uma olhada em cada um desses comandos, a seguir e, para exemplificar o uso deles, vamos tomar a modelagem da Figura 4 como base. Nela temos especificado o MER para uma Editora. Figura 4 - Modelo base para exemplos O MR para este diagrama é o seguinte (baseado nas regras de conversão do MER para o MR, anteriormente vistas): Comentário 4 Relembrando: o Dicionário de Dados é um arquivo de metadados (dados a respeito de dados) no SGBD. Ou seja, ele contém a semântica dos dados do BD (o que eles significam). 29 Banco de Dados AUTOR (CodAutor (PK), Nome, Nascimento) LIVRO (TitLivro (PK), CodAutor (FK), CodEditora (FK), Valor,Publicacao, Volume, Idioma) EDITORA (CodEditora (PK), Razao, Endereco, Cidade) DDL - Criando Tabelas O comando CREATE TABLE especifica uma nova tabela (relação), dando o seu nome e especificando as colunas (atributos), cada uma com seu nome, tipo e restrições iniciais. A forma geral do comando é: create table nome_tabela. Por exemplo: create table Empregado. Porém, a sintaxe completa do comando é bem mais detalhada: CREATE TABLE Nome_Tabela ( Nome_Atributo1 Tipo [(Tamanho)] [NOT NULL] [DEFAULT valor] [...], [,Nome_Atributo2 Tipo [(Tamanho)] [NOT NULL] [DEFAULT valor] [...], [PRIMARY KEY (Primária1[, Primária2 [, ...]])] [UNIQUE (Candidata1[, Candidata2[, ...]])] [FOREIGN KEY (Estrangeira1[, Estrangeira2 [, ...]]) REFERENCES TabelaExterna [(AtributoExterno1 [, AtributoExterno2 [, ...]])] [CHECK (condição)] ) Onde: ( ) Indica parte da sintaxe do comando e [ ] Indica opcionalidade do comando. Vamos explicar agora cada parte do comando completo. Nome_Atributo: nome do atributo que está sendo definido. Tipo: domínio do atributo, ou seja, o tipo do dado do atributo. Tamanho: alguns tipos de dados necessitamde especificação do tamanho do dado. Por exemplo, o tipo CHAR. NOT NULL: expressa que o atributo não pode receber valores nulos. DEFAULT valor: indica um valor a ser atribuído ao atributo, caso não seja determinado um valor durante a inserção. PRIMARY KEY (Primária1, Primária2, ...): serve para especificar a(s) chave(s) primária(s) da tabela. UNIQUE: indica que o atributo tem valor único na tabela. Qualquer tentativa de se introduzir uma linha na tabela, contendo um valor igual ao do atributo, será rejeitada. Serve para indicar chaves secundárias (chaves candidatas). Em Candidata1, Candidata2 devem ser especificados os atributos que terão esse valor único na tabela. FOREIGN KEY (Estrangeira1[, Estrangeira2 [, ...]]) REFERENCES TabelaExterna [(AtributoExterno1 [, AtributoExterno2 [, ...]]): serve para especificar os atributos que são chaves estrangeiras na relação, já relacionando-os às tabelas onde eles são chave primária (Integridade Referencial). Em Estrangeira1, Estrangeira2, ... especificam-se os atributos que são chave estrangeira. Em TabelaExterna se especifica o nome da tabela onde o atributo é chave primária e, por fim, o nome desse atributo nessa TabelaExterna (porque os atributos na relação e na tabela externa original podem ter nomes diferentes). Se os atributos da relação e da tabela externa tiverem o mesmo nome, esses AtributoExterno1, 30 Banco de Dados AtributoExterno2, ... não precisam ser especificados. CHECK (condição): aqui são especificadas condições que devem ser checadas na inserção de dados na tabela (validações). Vamos agora dar exemplos de uso dessa sintaxe, tomando o nosso modelo base (Figura 4). Primeiro, vamos criar as tabelas Autor e Editora que são tabelas simples (sem chaves estrangeiras ou checagens a serem feitas): CREATE TABLE AUTOR( CodAutor INTEGER NOT NULL, Nome CHAR(50)5 NOT NULL, Nascimento DATE NOT NULL, PRIMARY KEY (CodAutor), UNIQUE (Nome) ); CREATE TABLE EDITORA( CodEditora INTEGER NOT NULL, Razao CHAR(50), Endereco CHAR(50), Cidade CHAR(30), PRIMARY KEY(CodEditora )); Agora, podemos partir para a definição da tabela Livro que faz uso das duas tabelas anteriormente definidas: CREATE TABLE LIVRO( Titulo CHAR(50) NOT NULL, CodAutor INTEGER NOT NULL, CodEditora INTEGER NOT NULL, Valor DECIMAL(3.2)6, Publicacao DATE, Volume INTEGER, Idioma CHAR (15) DEFAULT = ‘Português’ 7, PRIMARY KEY (Titulo, CodAutor8), FOREIGN KEY (CodAutor) REFERENCES AUTOR9, FOREIGN KEY (CodEditora) REFERENCES EDITORA, CHECK Valor > 10.010); Só para ilustrar melhor o uso da cláusula CHECK, vamos dar outro exemplo de criação de tabela, fora do exemplo do modelo base (Figura 4). Suponha que desejamos criar a tabela estudante que contenha os atributos matricula, nome e nivel. CREATE TABLE estudante ( matricula char(10) NOT NULL, nome char(15) NOT NULL, nivel char(15) NOT NULL, Comentário 5 Como tipo de dados estamos deduzindo os tipos possíveis baseados na explicação da seção anterior sobre tipos de dados. Comentário 6 Valor será do tipo DECIMAL, ou seja, um valor de ponto flutuante, tendo 3 casas na parte inteira e duas casas decimais. Comentário 7 Aqui está sendo especificado o valor default para o atributo idioma. Se esse campo não for informado, o valor “Português” será assumido. Comentário 8 Veja que está sendo especificada uma chave primária composta, uma vez que dois atributos fazem parte da especificação. Comentário 9 Veja que estamos espeficifando que CodEditora é chave estrangeira na tabela sendo definida e é um atributo pertencente à tabela Autor (sendo chave primária nessa outra tabela – tabela externa) 31 Banco de Dados PRIMARY KEY (matricula), CHECK (nivel IN (“Bacharelado”, ”Mestrado”, ”Doutorado”)))11 O SQL-89 obrigava os atributos da chave primária a serem declarados como NOT NULL e UNIQUE. SQL-92 e posteriores já assumem essas condições, assim, sua declaração é redundante. Uma cláusula FOREIGN KEY pode incluir regras de remoção / atualização: FOREIGN KEY (coluna) REFERENCES tabela [ON DELETE {RESTRICT | CASCADE | SET NULL | SET DEFAULT}] [ON UPDATE {RESTRICT | CASCADE | SET NULL | SET DEFAULT}] Suponha que T2 tem uma chave estrangeira para T1, ou seja, tem um atributo que é chave primária em T1. Vejamos as cláusulas ON DELETE e ON UPDATE ON DELETE RESTRICT: (default) significa que uma tentativa de se remover uma linha de T1 falhará, se alguma linha em T2 combinar com a chave da tupla de T1 que está sendo deletada. CASCADE: a remoção de uma linha de T1 implica em remoção de todas as linhas de T2 que combinam com a chave da tupla de T1 sendo deletada. SET NULL: remoção de T1 implica em colocar NULL em todos os atributos de T2 que sejam chave estrangeira e estejam relacionados com a tupla sendo deletada em T1. SET DEFAULT: remoção de linha em T1 implica em colocar valores DEFAULT nos atributos da chave estrangeira de cada linha de T2 que combina. ON UPDATE RESTRICT: (default) a atualização de um atributo de T1 falha, se existem linhas em T2 combinando com a tupla sendo modificada. CASCADE: a atualização de atributo em T1 implica que linhas que combinam em T2 também serão atualizadas. SET NULL: a atualização de T1 implica que valores da chave estrangeira em T2, nas linhas que combinam com a tupla de T1, sendo atualizada, são postos para NULL. SET DEFAULT: a atualização de T1 implica que valores da chave estrangeira de T2, nas linhas que combinam, terão valores default aplicados. Vamos dar um exemplo de uso dessas cláusulas: CREATE TABLE empregado ( matricula char(10) NOT NULL, nome char(15) NOT NULL, cod_depto INT NOT NULL DEFAULT 112, PRIMARY KEY(matricula)13, FOREIGN KEY(supervisor) REFERENCES Empregado(matricula)14 ON DELETE SET NULL15 ON UPDATE CASCADE16, FOREIGN KEY (cod_depto) REFERENCES Departamento(codigo) ON DELETE SET DEFAULT17 Comentário 10 Aqui é especificado que os livros que forem criados devem ter seu valor maior que 10. Essa é uma validação que será feita a cada inserção / alteração de dados na tabela. Comentário 11 Veja que aqui estamos especificando os valores possíveis para o atributo nível. Comentário 12 O valor default para o departamento é 1. Comentário 13 A chave primária é a matrícula. Comentário 14 Veja que aqui o atributo da tabela sendo definida tem nome diferente do atributo na sua tabela externa de origem. Por isso, o nome do atributo na tabela externa precisa ser especificado. Comentário 15 Aqui é especificado que, se a tupla que contém a matrícula sendo utilizada nesta tabela for deletada, o atributo SUPERVISOR deverá receber o valor NULL. 32 Banco de Dados ON UPDATE CASCADE); DDL - Alterando Tabelas O comando ALTER TABLE permite inserir/eliminar/modificar colunas nas tabelas já existentes, modificando a estrutura das mesmas. A sintaxe básica desse comando é: ALTER TABLE Tabela { ADD (NomeNovoAtributo NovoTipo [BEFORE Nome_Atributo] [, ...] ) | DROP (Nome_Atributo [, ...] ) | MODIFY ( Nome_Atributo NovoTipo [ NOT NULL ] [DEFAULT, ... ] ) } Onde: | Indica escolha de várias opções e { } Indica obrigatoriedade de escolha de uma opção entre as várias. Agora, vamos explicar cada parte do comando. Adicionando um novo atributo (nova coluna) na Tabela ADD (NomeNovoAtributo NovoTipo [BEFORE Nome_Atributo] [, ...] ) | Usando o ADD, é possível adicionar um novo atributo na Tabela. Dessa forma, o novo atributo deve ser especificado (nome e tipo). É possível ainda dizer antes de qual atributo se deseja que esse novo atributo seja inserido (BEFORE nome_atributo). Por exemplo, se desejássemos adicionar o campo E-MAIL na tabela Autor, do nosso exemplo base (Figura 4), usaríamos: ALTER TABLE AUTOR ADD EMAIL CHAR(40); Observação Os novos atributos terão valores nulos em todas as linhas. Por isso, não se pode usar NOT NULL juntamente com ADD (na definição do novo atributo), quando a tabela já contiverregistros (lembre, com o uso de ADD a nova coluna é carregada com NULL’s). Deletando um atributo (uma coluna) da Tabela DROP (Nome_Atributo [, ...] ) | Para usar a cláusula DROP, é necessário apenas especificar o nome do atributo que se deseja remover da tabela. Porém, atenção! A cláusula DROP não remove atributos da chave primária. Por exemplo, se desejássemos eliminar o campo E-MAIL (anteriormente adicionado) da tabela Autor, usaríamos: ALTER TABLE AUTOR DROP EMAIL; A cláusula DROP pode ser usada com algumas configurações adicionais: DROP Nome_Atributo [CASCADE | RESTRICT] onde: CASCADE: removeria o atributo de todos os lugares onde ele estivesse sendo usado (outras tabelas como chave estrangeira e em visões). RESTRICT: não permitiria a remoção do atributo, se este estivesse sendo usado em uma visão ou como chave estrangeira em outra tabela. Comentário 16 Aqui é especifricado que, se a matrícula for atualizada na tabela de origem, todas as tuplas da tabela, onde o atributo é chave estrangeira, devem ser atualizadas também. Comentário 17 Aqui é especificado que, se a tupla que contém o código do departamento sendo utilizado nesta tabela for deletada, o atributo cod_depto deverá receber o valor default especificado para este atributo. No caso, o número 1. 33 Banco de Dados Ex: ALTER TABLE AUTOR DROP EMAIL RESTRICT; Modificando um atributo (uma coluna) da Tabela MODIFY18 ( Nome_Atributo NovoTipo [ NOT NULL ] [, ... ] ) Esta cláusula serve para modificar as informações de um atributo como, por exemplo, seu tamanho, sua nulidade, etc. Quando se altera o tipo de dados de uma coluna, os dados são convertidos para o novo tipo. Por exemplo, se desejássemos modificar o campo E-MAIL na tabela Autor, diminuindo seu tamanho de 40 para 30, usaríamos: ALTER TABLE AUTOR MODIFY EMAIL CHAR(25); O detalhe é que, se se diminuir o tamanho de um atributo do tipo CHAR, os dados existentes serão truncados, havendo, assim, perda de informação. DDL – Criando e Removendo Índices Índices são estruturas que permitem agilizar a busca e ordenação de dados em tabelas. Para criar um índice em uma tabela existente, usamos o comando CREATE INDEX. A sintaxe completa desse comando é: CREATE [UNIQUE] INDEX Nome_Indice ON Nome_Tabela (Nome_Atributo1 [, Nome_Atributo2…]) Neste comando, devemos especificar se o índice deve ser único (UNIQUE), ou seja, não deve permitir repetições (restrição de chaves) ou se será apenas um índice usado para acelerar a busca entre as tuplas da tabela. Depois, devemos especificar o nome do índice (Nome_Indice), a qual tabela ele vai pertencer (Nome_Tabela) e qual(ais) atributo(s) fará (ão) parte do índice. Por exemplo, se desejássemos criar um índice para o campo código do autor da tabela Autor, usaríamos: CREATE UNIQUE INDEX CodigoIDX19 ON Autor (CodAutor); Agora, se desejássemos criar um índice para pesquisar pelo código do autor e pelo código da editora ao mesmo tempo, usaríamos: CREATE INDEX AutorEditoraIDX ON Livro (CodAutor,CodEditora); O default é indexar em ordem ascendente, se quisermos uma ordem descendente devemos adicionar palavra DESC depois do nome do atributo (no final do comando). Por exemplo, suponha que se deseja pesquisar os autores pelo seu nascimento. Mas das datas maiores (mais rescentes) para as menores (mais antigas). Assim, ficaríamos com: CREATE INDEX NascIDX20 ON Autor (Nascimento) DESC; Observação Uma consulta que envolva atributos indexados é realizada com um tempo de execução melhor do que com atributos não-indexados. Agora, cuidado, você também não pode indexar TODOS os atributos de uma tabela. Você deverá usar o bom-senso para escolher quais aqueles que serão indexados de acordo com o problema sendo modelado e a freqüência de uso do atributo em consultas. Alguns SGBDs (por exemplo, o Oracle) criam, automaticamente, índices para as chaves primárias das tabelas, fazendo uso da cláusula UNIQUE. Comentário 18 Em alguns SGBDs ao invés de MODIFY é usada a cláusula ALTER. Comentário 19 Foi usado o sufixo IDX para indicar que é um índice para o código do autor. Comentário 20 Foi usado o sufixo IDX para indicar que é um índice para o nome do autor. 34 Banco de Dados Para eliminarmos um índice, usamos o comando DROP INDEX, cuja sintaxe é: DROP INDEX Nome-Índice21 Por exemplo, suponha que desejemos deletar o índice criado para a data de nascimento do autor, ficaríamos com: DROP INDEX NascIDX DDL - Excluindo Tabelas Para excluir uma tabela existente do SGBD, é utilizado o comando DROP. Agora, é necessário muito cuidado com este comando, pois ao deletar a tabela (esquema da mesma), automaticamente, TODOS os dados da tabela também serão excluídos. A sintaxe desse comando é: DROP TABLE Nome_Tabela Exemplo: /* Excluir a tabela livro */ DROP TABLE LIVRO; Conheça Mais Em geral, os livros de banco de dados trazem um ou mais capítulo sobre SQL. Entre esses livros temos: SILBERSCHATZ, Abraham; KORTH, Henry F; SUDARSHAN, S. Sistema de banco de dados. Traduzido por Daniel Vieira. Rio de Janeiro: Elsevier; Campus, 2006. ELMASRI, Ramez; NAVATHE, Shamkant B. Sistemas de banco de dados. 4a. ed. São Paulo: Pearson Education do Brasil, 2005. DATE, C. J. Introdução a sistemas de bancos de dados. Rio de Janeiro: Campus, 2000. ALVES, W.P. Fundamentos de Bancos de Dados. Editora Érica, 2004. Além destes, há livros específicos sobre SQL, independente de SGBD, tais como: BEIGHLEY, Lynn. Use a Cabeça SQL. Starlin Alta Consult, 1ª Edição, 2008. KLINE, Daniel; KLINE, Kelvin E. Sql - O Guia Essencial - Manual de Referência Profissional. Alta Books, 2010. SHELDON, Robert; OPPEL, Andy. SQL – Um Guia para Iniciantes. Editora Ciência Moderna, 3ª Edição, 2009. DAMAS, Luís. Sql - Structured Query Language. Editora LTC, 6ª edição, 2007. Comentário 21 Deve ser usado o nome que foi dado ao índice na criação do mesmo. Comentário 22 Para criar tabelas é usado o comando CREATE TABLE. Comentário 23 Integer indica um valor numérico inteiro e como o código é a chave, ele deve ser NOT NULL. Comentário 24 A descrição deve ser um atributo do tipo caractere. Usando o bom-senso, você define o tamanho do campo. Optei pela descrição ser também not null. Comentário 25 O preço é um valor decimal . Optei por ele poder assumir o valor NULL (suponha que. no momento do cadastro. você ainda não saiba por quanrto vai vender o produto. 35 Banco de Dados Você Sabia? Além da SQL outra linguage comercial para manipulação de SGBDs é a QBE (Query-by-Example). A versão experimental da linguagem foi descrita formalmente (publicada) por Moshe Zloof em 1977. A versão comercial foi descrita em 1978 pela IBM e usada mais tarde na Query Management Facility (QMF). A QBE tem por base o cálculo relacional de domínio e possui sintaxe bidimensional: as consultas parecem tabelas. Nesta linguagem as consultas são expressas “por exemplo”. Em vez de determinar um procedimento para obtenção da resposta desejada, o usuário dá um exemplo do que é desejado. A partir daí, o sistema generaliza o exemplo para o processamento da resposta da consulta. Comentário 26 Você não pode deixar para especificar a quantidade de itens depois. Por isso, NOT NULL. Aprenda Praticando Utilize SQL para fazer o que se pede, a partir do modelo relacional a seguir. » Produto (cod_prod (PK), descricao, preco) » Item_Venda (cod_venda (PK), cod_prod(PK), qntde) » Venda (cod_venda (PK), nome_cliente) 1) Crie as tabelas acima, usando o comando, sabendo que os códigos devem ser valores numéricos, preco deve ser um valor de ponto flutuante e qntde (quantidade comprada do produto) deve ser um valor inteiro, obrigatoriamente, maior que zero (afinal, ninguém compra zero produto!). Devemos começar a criação pelas tabelas mais simples (sem chave estrangeira). Dessa forma, vamos criar primeiro a tabela PRODUTO e, depois, a tabela VENDA. CREATE TABLE22 PRODUTO( cod_prod integer23 NOT NULL,descricao CHAR(45) NOT NULL24, preco DECIMAL25(5,2), PRIMARY KEY (cod_prod) ) Seguindo o mesmo raciocínio da tabela anterior, criaremos, agora, a tabela VENDA. CREATE TABLE VENDA( cod_venda integer NOT NULL, nome_cliente CHAR(40) NOT NULL, PRIMARY KEY (cod_venda) ) CREATE TABLE ITEM_VENDA( cod_prod INTEGER NOT NULL, cod_venda INTEGER NOT NULL, qntde INTEGER NOT NULL26, PRIMARY KEY (cod_prod, cod_venda)27, FOREIGN KEY (cod_prod) REFERENCES Produto28, FOREIGN KEY (cod_venda) REFERENCES Venda, Comentário 27 Veja que está sendo especificada uma chave primária composta, uma vez que dois atributos fazem parte da especificação. Comentário 28 Veja que estamos espeficifando que cod_prod é chave estrangeira na tabela e é um atributo pertencente à tabela Produto. 36 Banco de Dados CHECK qntde > 0)29; 2) Altere a Tabela VENDA para incluir o atributo Dt_Venda e, depois, para mudar o tamanho do atributo nome para 50 caracteres. ALTER TABLE VENDA { ALTER TABLE VENDA { ADD Dt_Venda DATE NOT NULL30 MODIFY Nome_Cliente CHAR(50)31 } } 3) Criar um Índice para o atributo nome_cliente da tabela VENDA CREATE INDEX clienteIDX32 ON VENDA (nome_cliente); Atividades e Orientações de Estudo Agora é a sua vez de fazer as atividades! Lembre-se: praticar é muito importante para fixar o conteúdo estudado! Atividades Práticas: Resolva as atividades a seguir em um documento texto e poste o mesmo no ambiente virtual, no local indicado. Essa atividade é para ser realizada em DUPLA (escolha seu companheiro de trabalho!) e fará parte da avaliação somativa de vocês. I) A partir do modelo relacional especificado a seguir, escreva os comandos SQL que realizem as operações solicitadas. Professor (CPF_Prof (PK), Nome_Prof, Titulacao) Disciplina (Cod_Disc (PK), CPF_Prof (FK), Nome_Disc, carga_horaria) Aluno (Matricula (PK), Nome) Turma (Cod_Disc (PK), Matricula (PK), sala) 1) Faça a criação das tabelas do modelo relacional especificado acima. Algumas observações são: o atributo TITULACAO deve ser caractere de tamanho 30, deve permitir nulos e os valors permitidos são “graduado, especialista, mestre ou doutor”. A carga horária da disciplina deve ser maior que zero. 2) Altere a tabela PROFESSOR para incluir o atributo tempo de serviço, do tipo inteiro e que deve ser not null. E modifique o atributo titulação para que passe a não permitir valores nulos. 3) Altere a tabela TURMA para deletar o atributo sala. 4) Altere a tabela ALUNO para incluir o curso que ele está prestando. Esse atributo deve ser caractere de tamanho 40 e não deve permitir nulos. O valor default desse atributo deve ser “Informática”. 5) Crie índices para os atributos Nome_Prof (tabela professor) e Nome_Disc (tabela Disciplina). Comentário 29 Aqui é especificado que a quantidade de itens deve ser maior que zero, conforme foi especificado no enunciado. Comentário 30 Adicionamos o atributo à tabela VENDA. Comentário 31 Aumentamos o tamanho para 50. Comentário 32 Como estamos fazendo neste capítulo, você pode adotar um sufixo para indicar o nome do índice, como no caso IDX. 37 Banco de Dados Vamos Revisar? Nos capítulos anteriores a esse, você estudou como fazer a modelagem conceitual do seu banco de dados e, depois, como projetá-lo segundo o modelo relacional (MR). A partir do MR gerado, para poder criar fisicamente o banco de dados, você necessitará fazer uso dos comandos estudados neste capítulo. Esses comandos fazem parte da DDL (Data Definition Language) da SQL e incluem, entre outros, comandos para criar, alterar e deletar tabelas, além de comandos para criação e deleção de índices de tabela. Os comandos aqui estudados poderão ser utilizados em qualquer SGBD, pois fazem parte do SQL ANSI. Uma vez que seu banco de dados estiver criado, ele poderá ser manipulado e consultado, usando a DML (Data Manipulation Language) da SQL, este será o assunto do próximo capítulo. 38 Banco de Dados Capítulo 12 O que vamos estudar neste capítulo? Neste capítulo, vamos estudar os seguintes temas: » Inserção de dados em Tabelas. » Deleção de dados de Tabelas. » Consultas em Tabelas. Metas Após o estudo deste capítulo, esperamos que você saiba utilizar a DML (Data Manipulation Language) do SQL. Dessa forma, você deverá saber: » Inserir dados em Tabelas. » Deletar dados de Tabelas. » Realizar consultas simples, agrupadas e aninhadas em Tabelas. 39 Banco de Dados Capítulo 12 – Consultas em Banco de Dados Relacionais Vamos conversar sobre o assunto? No capítulo anterior, você aprendeu como criar fisicamente o seu banco de dados através dos comandos da DDL (Data Definition Language) da SQL. Ou seja, você aprendeu como fazer a criação de tabelas, índices para determinados atributos das tabelas e fazer a manutenção de tudo que foi criado em termos de esquema (definição da tabela). Agora, que as tabelas já estão criadas, resta saber como inserir dados nas mesmas, como atualizar ou deletar esses dados inseridos, além de como fazer para buscar informações em uma ou mais tabelas através de consultas simples ou aninhadas. É justamente isto que você irá estudar neste capítulo. Neste capítulo, estudaremos a DML (Data Manipulation Language) da SQL que engloba justamente os comandos da SQL para inserção, deleção, atualização e consulta de dados em tabelas de banco de dados relacionais. Vamos lá? Inserindo Dados em Tabelas A partir do momento em que uma tabela está criada, ela já pode receber a entrada de dados. Para isto usamos o comando INSERT INTO. Este comando adiciona uma ou mais linhas na tabela. A sintaxe desse comando é: è Para inserir uma única tupla (linha): INSERT INTO nome_tabela [(atrib1,atrib2,...)] VALUES (valor1, valor2,...) Onde: nome_tabela deve ser o nome da tabela onde se deseja inserir dados. Atrib1, atrib2, ... são os nomes dos atributos que receberão os valores na inserção. Se for omitida essa lista de nomes de atributos, serão selecionadas todas as colunas da tabela, pela sua ordem de criação33. Se for especificada uma lista de nomes de atributos, os valores para os dados deverão ser especificados para inserção na ordem em que aparecem na lista. Valor1, valor2, ... são os valores que serão atribuídos aos atributos. Esses valores devem ser especificados seguindo a ordem dos atributos (ou da lista de atributos especificada no comando ou a ordem de criação dos atributos na tabela). Na especificação dos valores também deve-se atentar que: 1) Valores de atributos do tipo caracter (CHAR ou VARCHAR) e do tipo DATE devem estar entre apóstrofos. 2) A entrada de dados, baseada em caracteres, deve ser efetuada, de preferência com caracteres em maiúsculo e sem acentuação, pois, se algum acento for utilizado, pode criar problemas no momento de uma pesquisa com uma palavra idêntica que não possua acento. 3) Os atributos especificados como NOT NULL devem sempre receber algum valor senão um erro será gerado e o comando não será executado, Comentário 33 Importante atentar para isto porque você deverá especificar os valores a serem inseridos também pela ordem de criação dos atributos. Senão, corre o risco de inserir dados nos campos errados. 40 Banco de Dados pois esses atributos nunca poderão ficar vazios. è Para inserir mais de uma tupla (linha): INSERT INTO nome_tabela [(atrib1,atrib2,...)] <comando SELECT>34 Vamos exemplificar o uso desses comandos. Para isso, tomaremos como base o modelo relacional usado nos exemplos do capítulo anterior, mas com alguns atributos a menos, veja: AUTOR (CodAutor (PK), Nome, Nascimento) LIVRO (TitLivro (PK), CodAutor (FK), CodEditora (FK), Valor, Ano_Publicacao) EDITORA (CodEditora (PK), Razao, Endereco, Cidade) Vamos aos exemplos. Suponha que você deseje inserir um registro na tabela Autor. Como ficaria? INSERT INTO Autor ( CodAutor, Nome, Nascimento ) VALUES (112, ‘C. J. Date’, ‘03/12/1941’35); Lembrando que a ordem dos valores deve ser a mesmaordem dos atributos para que sejam inseridos nos lugares corretos. Agora, vamos inserir um registro na tabela Editora. INSERT INTO Editora( CodEditora, Razao, Endereco, Cidade ) VALUES (1, ‘Editora Campus’, ‘R. Sete de Setembro,111’, ‘Rio de Janeiro’); Depois de preenchida as tabelas base (que não dependem de nenhuma outra), vamos colocar um registro na tabela Livro, que depende de valores cadastrados nas duas tabelas anteriores INSERT INTO Livro36 VALUES (‘Introdução a Sistemas de Banco de Dados’, 11237, 138, NULL39, ‘2000’); Chamamos a atenção para o fato que, na Tabela Livro, o código do autor e o código da editora são chaves estrangeiras e, para que tudo dê certo, os valores utilizados, aqui, no insert, devem existir anteriormente nas tabelas de origem das chaves estrangeiras, no caso, nas tabelas Autor e Editora. Para finalizar os exemplos, vamos fazer a criação de uma nova tabela no nosso modelo, chamada AUTOR_JOVEM com os mesmos campos da tabela AUTOR. Depois, vamos inserir nesta nova tabela os autores da tabela AUTOR com nascimento posterior a 01/01/1980. Como ficariam os comandos SQL para realizar essas ações? Comecemos pela criação da nova tabela. CREATE TABLE AUTOR_JOVEM ( CodAutor INTEGER NOT NULL, Nome CHAR(50) NOT NULL, Nascimento DATE NOT NULL, PRIMARY KEY (CodAutor), UNIQUE (Nome, Nascimento) ); Agora vamos preencher essa tabela com os autores com nascimento posterior a 01/01/1980. INSERT INTO AUTOR_JOVEM Comentário 34 O comando SELECT será explicado posteriormente. Por hora, o importante é saber que podemos inserir, em uma tabela, várias tuplas, resultado de uma consulta usando SELECT. Comentário 35 Observe que, como mencionado, valores do tipo caracter e valores do tipo DATE devem vir entre apóstrofos. Comentário 36 Lembre que, quando não especificamos a ordem dos atributos, é tomada a ordem de criação dos atributos na tabela. Assim, os valores dos atributos deveriam vir nessa mesma ordem de criação que está especificada no esquema da tabela Livro, do modelo relacional exemplo. Comentário 37 112 é o código do autor anteriormente cadastrado. Comentário 38 O Valor 1 é o código da editora anteriormente cadastrada. Comentário 39 Como o atributo VALOR pode receber valores nulos (ele não é NOT NULL) pela definição feita na tabela, no capítulo anterior, podemos preenchê-lo com o valor NULL. 41 Banco de Dados pois esses atributos nunca poderão ficar vazios. è Para inserir mais de uma tupla (linha): INSERT INTO nome_tabela [(atrib1,atrib2,...)] <comando SELECT>34 Vamos exemplificar o uso desses comandos. Para isso, tomaremos como base o modelo relacional usado nos exemplos do capítulo anterior, mas com alguns atributos a menos, veja: AUTOR (CodAutor (PK), Nome, Nascimento) LIVRO (TitLivro (PK), CodAutor (FK), CodEditora (FK), Valor, Ano_Publicacao) EDITORA (CodEditora (PK), Razao, Endereco, Cidade) Vamos aos exemplos. Suponha que você deseje inserir um registro na tabela Autor. Como ficaria? INSERT INTO Autor ( CodAutor, Nome, Nascimento ) VALUES (112, ‘C. J. Date’, ‘03/12/1941’35); Lembrando que a ordem dos valores deve ser a mesma ordem dos atributos para que sejam inseridos nos lugares corretos. Agora, vamos inserir um registro na tabela Editora. INSERT INTO Editora( CodEditora, Razao, Endereco, Cidade ) VALUES (1, ‘Editora Campus’, ‘R. Sete de Setembro,111’, ‘Rio de Janeiro’); Depois de preenchida as tabelas base (que não dependem de nenhuma outra), vamos colocar um registro na tabela Livro, que depende de valores cadastrados nas duas tabelas anteriores INSERT INTO Livro36 VALUES (‘Introdução a Sistemas de Banco de Dados’, 11237, 138, NULL39, ‘2000’); Chamamos a atenção para o fato que, na Tabela Livro, o código do autor e o código da editora são chaves estrangeiras e, para que tudo dê certo, os valores utilizados, aqui, no insert, devem existir anteriormente nas tabelas de origem das chaves estrangeiras, no caso, nas tabelas Autor e Editora. Para finalizar os exemplos, vamos fazer a criação de uma nova tabela no nosso modelo, chamada AUTOR_JOVEM com os mesmos campos da tabela AUTOR. Depois, vamos inserir nesta nova tabela os autores da tabela AUTOR com nascimento posterior a 01/01/1980. Como ficariam os comandos SQL para realizar essas ações? Comecemos pela criação da nova tabela. CREATE TABLE AUTOR_JOVEM ( CodAutor INTEGER NOT NULL, Nome CHAR(50) NOT NULL, Nascimento DATE NOT NULL, PRIMARY KEY (CodAutor), UNIQUE (Nome, Nascimento) ); Agora vamos preencher essa tabela com os autores com nascimento posterior a 01/01/1980. INSERT INTO AUTOR_JOVEM Comentário 34 O comando SELECT será explicado posteriormente. Por hora, o importante é saber que podemos inserir, em uma tabela, várias tuplas, resultado de uma consulta usando SELECT. Comentário 35 Observe que, como mencionado, valores do tipo caracter e valores do tipo DATE devem vir entre apóstrofos. Comentário 36 Lembre que, quando não especificamos a ordem dos atributos, é tomada a ordem de criação dos atributos na tabela. Assim, os valores dos atributos deveriam vir nessa mesma ordem de criação que está especificada no esquema da tabela Livro, do modelo relacional exemplo. Comentário 37 112 é o código do autor anteriormente cadastrado. Comentário 38 O Valor 1 é o código da editora anteriormente cadastrada. Comentário 39 Como o atributo VALOR pode receber valores nulos (ele não é NOT NULL) pela definição feita na tabela, no capítulo anterior, podemos preenchê-lo com o valor NULL. SELECT * FROM AUTOR WHERE Nascimento40 > ‘01/01/1980’; Atualizando Dados em Tabelas Para modificar o valor de atributos de uma ou mais tuplas (linhas), dependendo dos critérios de seleção de quem será modificado, o comando UPDATE deve ser utilizado. A sintaxe desse comando é: UPDATE nome_tabela SET lista_atributos com atribuições de valores [WHERE condição de seleção das tuplas a serem modificadas] Onde: nome_tabela - é a indicação da tabela em que se deseja efetuar a atualização dos registros; lista_atributos com atribuições de valores – É a indicação de quais atributos deverão ser atualizados e por qual valor. Esse trecho deve ter o seguinte formato: nome_ atributo1 [, nome_atributo2, ....] = {valor ou expressão } A cláusula WHERE especifica quais dados da coluna serão alterados. Quando a cláusula WHERE (que é opcional) é omitida, o UPDATE deve ser aplicado a todas as tuplas da relação. Ou seja, todas as tuplas da relação serão modificadas. Por exemplo: se desejássemos reajustar o valor de todos os livros em 10%, usaríamos o seguinte comando: UPDATE LIVRO SET Valor = Valor * 1.141 Como no comando acima não foi especificada uma cláusula WHERE, todos os livros cadastrados na tabela LIVRO seriam atualizados. Agora, vamos supor que desejássemos alterar o endereço e a cidade da editora com CodEditora = 10. UPDATE EDITORA SET endereco = ‘Av. N.S. de Fátima, 456’, cidade = ‘João Pessoa42’ WHERE CodEditora = 1; Aqui não seriam atualizadas todas as editoras da tabela EDITORA, mas apenas a editora de código 10. A cláusula WHERE aceita como condição um comando SELECT. Daremos mais detalhes do que pode vir em uma cláusula WHERE mais à frente. Aguarde... Exluindo Dados de Tabelas Para excluir linhas (que satisfaçam uma determinada condição) de uma ou mais tabelas, usa-se o comando DELETE FROM, cuja sintaxe é: DELETE FROM Nome_Tabela [WHERE Condição43] Se omitirmos a cláusula WHERE, então o DELETE será aplicado a todas as tuplas da relação, ou seja, TODOS os registros da tabela serão deletados (cuidado com esse comando!). Porém, a tabela permanece no BD como uma tabela vazia. Por exemplo, o comando: DELETE FROM LIVRO; Deletaria todos os registros da tabela livro, deixando a mesma vazia. Vale ressaltar que a tabela (seu esquema) permanece. Logo, esse comando não é equivalente ao DROP TABLE (que apagaria o esquema da tabela do banco de dados e, por consequência, todos
Compartilhar