Baixe o app para aproveitar ainda mais
Prévia do material em texto
IMPLEMENTAÇÃO DE BANCO DE DADOS AULA 01: MODELO RELACIONAL Nesta aula, você irá: 1.Conhecer as principais características de um Sistema de Banco de Dados. 2.Definir modelo de dados. 3.Conceituar Relação. 4.Identificar os componentes do modelo relacional. 5.Conceituar integridade referencial. Introdução Nossas vidas são gerenciadas por enormes bases de dados. Quando vamos, por exemplo, realizarmos um saque no caixa eletrônico de um banco, o sistema do equipamento tem que acessar as informações de agencia, numero da conta, senha e saldo disponível. Estas informações, na realidade dados, necessitam de gerenciamento eficiente e confiável que permita sua rápida recuperação e atualização. É exatamente este o grande objetivo dos Sistemas de Banco de dados. IMPLEMENTAÇÃO DE BANCO DE DADOS 1 DESENVOLVIMENTO DE SISTEMAS INFORMATIZADOS O desenvolvimento de Sistemas informatizados de uso comercial ao longo do tempo: SISTEMAS TRADICIONAIS Nos Sistemas Tradicionais os dados são armazenados em arquivos que estão fisicamente armazenados, separados uns dos outros. O acesso é feito pelos programas de aplicação, utilizando o nome externo dos arquivos e definindo todo o registro, independente da utilização dos campos. para SISTEMAS DE BANCO DE DADOS Nos Sistemas de Banco de Dados os dados são definidos para o Sistema Gerenciador de Banco de Dados (SGBD), através da DDL (linguagem de definição de dados). Fisicamente estão armazenados em um único local, e o acesso só se realiza através do SGBD. Nos programas de aplicação, é necessário apenas definir os campos a serem utilizados pelo programa. IMPLEMENTAÇÃO DE BANCO DE DADOS 2 Sistemas tradicionais A abordagem de bancos de dados ao gerenciamento de dados surgiu devido a problemas associados com a abordagem tradicional do gerenciamento de dados como: redundância, dificuldades de acesso, integridade e problemas de segurança. Um banco de dados é uma coleção de dados organizados de tal forma que possam ser acessados e utilizados por muitas aplicações diferentes. Ou seja, em vez de armazenar dados em arquivos separados para utilização, os dados são armazenados e organizados em um só local pe rm i t i ndo compar t i l hamen to e i n te r - relacionamento por múltiplos usuários. A fim de usar a abordagem de banco de dados para o gerenciamento de dados, foi necessário um software adicional – um sistema de gerenciamento de banco de dados (SGBD). O SGBD consiste em um grupo de programas que pode ser usado como uma interface entre o banco de dados e um usuário ou um banco de dados e um programa aplicativo. Com o passar do tempo, essas aplicações e arquivos independentes podem proliferar a ponto IMPLEMENTAÇÃO DE BANCO DE DADOS 3 de os recursos de informações da empresa ficarem fora de controle. Isso muitas vezes resulta em: REDUNDÂNCIA DE DADOS Trata-se da duplicação dos mesmos dados em dois ou mais arquivos. O problema com a redundância é que as mudanças ao serem feitas no arquivo de uma apl icação não são automaticamente realizadas nos arquivos das outras aplicações, gerando a falta de integridade dos dados. DEPENDÊNCIA ENTRE PROGRAMAS E DADOS Os aplicativos tradicionais de banco de dados de arquivos são caracterizados pela dependência entre programas e dados, isto é, programas e dados desenvolvidos e organizados para uma aplicação são incompatíveis com os programas e dados organizados diferentemente para um outro aplicativo. CUSTO EXCESSIVO EM SOFTWARE Resul tam da cr iação, documentação e acompanhamento de muitos arquivos e aplicações diferentes IMPLEMENTAÇÃO DE BANCO DE DADOS 4 Conforme já explicado, abaixo, a linguagem de programação, dentro do contexto do processo de desenvolvimento, deve ser encarada como um dos componentes de desenvolvimento e devem ser compatíveis com as metodologias e técnicas nas fases de análise e projeto de sistemas, para que o desenvolvimento flua com eficiência e o sistema resultante seja efetivo em seus objetivos. Ou seja, deve haver uma relação fácil entre as técnicas de análise e projeto, as técnicas de programação e as respectivas linguagens de programação. IMPLEMENTAÇÃO DE BANCO DE DADOS 5 Na pratica, historicamente as linguagens de programação são as primeiras a surgirem, pois como a encarnação de um sistema só existe quando escrita em linguagem, as gerações de linguagens é que motivam a definição das técnicaa de análise de projeto. Isso pode ser observado na figura. Ambiente de desenvolvimento de sistemas IMPLEMENTAÇÃO DE BANCO DE DADOS 6 IMPLEMENTAÇÃO DE BANCO DE DADOS 7 IMPLEMENTAÇÃO DE BANCO DE DADOS 8 IMPLEMENTAÇÃO DE BANCO DE DADOS 9 MOodelo de Dados é basicamente um conjunto de conceitos utilizados para descrever um banco de dados. Não existe uma única forma de representação deste modelo, porém qualquer forma que permita a correta compreensão das IMPLEMENTAÇÃO DE BANCO DE DADOS 10 estruturas de dados compreendidas no banco de dados pode ser considerada adequada. Os tipos de modelo são: ALTO NÍVEL ou MODELO DE DADOS CONCEITUAL Fornece uma visão mais próxima do modo como os usuários visualizam os dados realmente O modelo entidade-relacionamento e o funcional são exemplos deste tipo de abordagem. BAIXO NÍVEL ou MODELO DE DADOS FÍSICOS Fornece uma visão mais detalhada do modo como os dados estão realmente armazenados no computador . O modelo relacional, o modelo de rede e o hierárquico e o orientado a objetos são exemplos deste tipo de representação Esquemas e instâncias Em qualquer modelo de dados utilizado, é importante distinguir a “descrição” do banco de dados do “banco de dados” por si próprio. A descrição de um banco de dados é chamada de “esquema de um banco de dados” e é especificada durante o projeto do banco de dados. Geralmente, poucas mudanças ocorrem no esquema do banco de dados. IMPLEMENTAÇÃO DE BANCO DE DADOS 11 Os dados armazenados em um banco de dados em um determinado instante do tempo formam um conjunto chamado de “instância do banco de dados”. A instância altera toda vez que uma alteração no banco de dados é feita. O SGBD é responsável por garantir que toda instância do banco de dados satisfaça ao esquema do banco de dados, respeitando sua estrutura e suas restrições. MODELOS DE DADOS Visando facilitar o desenvolvimento de sistemas e realçar as vantagens da utilização de Sistemas de Banco de Dados foi criada a Arquitetura Três Esquemas, cuja principal meta é separar as aplicações do usuáriodo banco de dados físico. Os esquemas podem ser definidos como: \ IMPLEMENTAÇÃO DE BANCO DE DADOS 12 MODELO RELACIONAL IMPLEMENTAÇÃO DE BANCO DE DADOS 13 MODELO DE OBJETO RELACIONAL MODELOS RELACIONAIS IMPLEMENTAÇÃO DE BANCO DE DADOS 14 relações Dr. Codd TUPLAS IMPLEMENTAÇÃO DE BANCO DE DADOS 15 CAMPOS CHAVE PRIMÁRIA CHAVE ESTRANGEIRA IMPLEMENTAÇÃO DE BANCO DE DADOS 16 Nesta aula, você: •Aprendeu a respeito do desenvolvimento dos Sistemas de Banco de Dados. •Compreendeu as principais características dos Sistemas de Banco de Dados. •Definiu modelos de dados. •Analisou o modelo Relacional. REGISTRO DE PARTICIPAÇÃO 1. No Modelo Relacional, tupla corresponde a: 1) Tabela. 2) Linha. 3) Coluna. 4) Relação. 5) Atributo. 2. A especificação do relacionamento entre duas tabelas é feita pela chave: 1) Estrangeira. 2) Candidata. 3) Relacional. 4) Primária. 5) Composta. IMPLEMENTAÇÃO DE BANCO DE DADOS 17 3. Na terminologia do Modelo Relacional, cada linha da tabela é chamada de I, a tabela é denominada II e o nome da coluna é denominado III. As lacunas I, II e III são preenchidas de forma correta, respectivamente, por 1) registro, arquivo e campo. 2) tupla, relação e atributo. 3) esquema, instância e domínio. 4) tupla , relação e domínio. 5) tupla, instância e atributo. 4. Considere: I. Conjunto de um ou mais atributos que, quando tomados coletivamente, permitem identificar exclusivamente uma entidade. II. Restrição que especifica que o atributo é uma chave candidata (tem um valor não nulo para cada linha de uma tabela). Correspondem, respectivamente, a I e II: 1) Integridade de entidade e chave estrangeira. 2) Chave primaria e chave estrangeira. 3) Chave primaria e integridade de entidade. 4) Chave estrangeira e chave primaria. 5) Chave estrangeira e integridade de entidade. IMPLEMENTAÇÃO DE BANCO DE DADOS 18 CCT0199_EX_A1_201102276103 Disciplina: CCT0199 - IMPL. BANCO DE DADOS Período Acad.: 2014.1 - EAD (G) / EX 1. É responsável por garantir a Integridade Referencial: ( ) Chave de Atributo ( X ) Chave Estrangeira ( ) Chave Primária ( ) Chave Secundária ( ) Chave Candidata 2. Relacione as colunas abaixo: (1) Tabela bidimensional, composta por linhas e colunas de dados (2) Coluna (3) Linha (4) Conjunto de valores de um atributo (5) Nomes das relações seguidas pelos nomes dos atributos, com chaves primária e estrangeiras identificadas ( ) Esquema ( ) Atributo ( ) Tupla ( ) Relação ( ) Domínio IMPLEMENTAÇÃO DE BANCO DE DADOS 19 A sequência correta está representada na opção: ( ) 5,2,3,4,1 ( ) 5,2,1,3,4 ( ) 5,3,2,1,4 ( ) 4,2,3,1,5 ( X ) 5,2,3,1,4 3. Considere a tabela aluno mostrada abaixo: Qual o atributo poderia ser chave candidata da tabela acima? ( ) nome ( ) cidade ( ) Não existe atributo que poderia ser chave candidata. ( ) endereco ( X ) cpf IMPLEMENTAÇÃO DE BANCO DE DADOS 20 AULA 02 ÁLGEBRA RELACIONAL Nesta aula, você irá: 1.Conceituar álgebra relacional. 2.Conhecer as operações da álgebra relacional. 3.Utilizar operadores de conjunto. 4.Escrever expressões utilizando operadores de tabelas. 5.Analisar expressões de álgebra relacional. CONCEITOS ENVOLVIDOS a) Relação: representada por uma tabela de duas dimensões (linhas e colunas); b) Tupla: corresponde a uma linha da relação; c) Atributo: corresponde às colunas da relação; d) Chave primária: conjunto de atributos que identificam univocamente cada tupla da relação; e) Chave extrangeira: atributo de uma relação que é chave primária de outra relação. IMPLEMENTAÇÃO DE BANCO DE DADOS 21 Álgebra relacional A álgebra relacional é uma coleção de operações canônicas que são utilizadas para manipular as relações. Essas operações são utilizadas para selecionar tuplas de relações individuais e para combinar tuplas relacionadas de relações diferentes, para especificar uma consulta em um determinado banco de dados. O resultado de cada operação é uma nova relação, que também pode ser manipulada pela álgebra relacional. As principais operações da álgebra relacional são: ✴Seleção ✴Projeção ✴Produto Cartesiano ✴Junção ✴Operações de Conjunto : (União, Intersecção, Diferença) ✴Divisão IMPLEMENTAÇÃO DE BANCO DE DADOS 22 OPERAÇÃO DE SELEÇÃO A operação de SELEÇÃO, representada pela letra grega sigma (σ), é aplicada sobre apenas uma relação visando selecionar um subconjunto de tuplas (com todos os seus atributos). A forma geral de uma operação de Seleção é: Onde: - <condição de seleção> é a condição que a linha deve atender para ser selecionada e - <nome da relação> é o nome da relação sobre a qual será aplicada a operação de Seleção A relação resultante da operação tem os mesmos atributos da relação especificada em <nome da relação>. EXEMPLO: Dada a relação dos empregados da empresa selecionar aqueles que trabalham no IMPLEMENTAÇÃO DE BANCO DE DADOS 23 departamento 10 e que ganhem salario > 1500. A expressão que atende ao pedido é: IMPLEMENTAÇÃO DE BANCO DE DADOS 24 OPERAÇÃO DE PROJEÇÃO A operação de PROJEÇÃO, representada pela letra grega (∏), é aplicada sobre apenas uma relação de visando projetar os atributos de uma relação de acordo com uma lista de atributos oferecida. Os atributos são exibidos na mesma ordem que aparecem na lista. A operação Projeção seleciona um conjunto determinado de colunas de uma relação. A forma geral da operação de PROJEÇÃO é: Onde: -<lista de atributos> representa a lista de atributos que o usuário deseja selecionar e - <nome da relação> representa a relação sobre a qual a operação projeção será aplicada. IMPLEMENTAÇÃO DE BANCO DE DADOS 25 EXEMPLO Dada a relação de empregados da empresa, projetar as colunas ID, NOME E SALARIO: A expressão que atende ao pedido é: IMPLEMENTAÇÃO DE BANCO DE DADOS26 SEQUENCIALIDADE DE OPERAÇÕES A Projeção e a Seleção podem ser combinadas, de forma que apenas algumas linhas e algumas colunas retornem na operação. A forma geral de uma operação sequencializada é: π<lista de atributos> (σ <condição de seleção> (<nome da relação>)) EXEMPLO: Dada a relação de empregados da empresa, projetar as colunas ID, NOME E SALARIO P A R A O S E M P R E G A D O S D O DEPARTAMENTO 10: IMPLEMENTAÇÃO DE BANCO DE DADOS 27 A expressão que atende ao pedido é: π Id,Nome, Salario (σ Id_Depto = 10 (Empregado)) Realizada em Dois Passos Passo 1 - Seleção IMPLEMENTAÇÃO DE BANCO DE DADOS 28 Passos 2 - Projeção Essa ordem de execução: primeiro a Seleção e depois a Projeção, funciona em todos os casos. Observe que se tentarmos realizar primeiro, a Projeção, segundo, a seguinte expressão: (σ Id_Depto = 10 (π Id, Nome, Salario (Empregado)) Iriamos obter como resultado intermediário a seguinte tabela: IMPLEMENTAÇÃO DE BANCO DE DADOS 29 Onde não teríamos a coluna Id_depto para realizarmos a Seleção. Então, podemos afirmar que nunca podemos inverter? Na verdade, podemos inverter a ordem quando a Projeção contempla todas as colunas utilizadas na seleção. Vejamos um exemplo: Dada a relação dos empregados da empresa projetar as colunas Id, Nome , Salário e Departamento para os empregados que IMPLEMENTAÇÃO DE BANCO DE DADOS 30 trabalham no departamento 10 e que ganhem salário >= 1500. Observando tabela, notamos que, dos empregados das cinco colunas da tabela, desejamos projetar quatro e apenas os empregados de ID 101 t raba lham no departamento 10 e possuem salário maior ou igual a 1500. As expressões algébrica que realizam a operação são: IMPLEMENTAÇÃO DE BANCO DE DADOS 31 (σ Id_Depto = 10 E Salario >= 1500 (π Id, Nome, Salario, Id_Depto (Empregado)) Notamos que estamos realizando primeiro a Projeção, gerando a seguinte tabela: Observe que estão disponíveis na tabela intermediária as colunas salário e Id_depto que nos permite fazer a Seleção das tuplas de ID 101 e 104 as únicas que atendem a condição de Salário >=1500 e Id_depto = 10 Analisando a Expressão: π Id, Nome, Salário, Id_Depto (σ Id_Depto = 10 E Salario >= 1500 (Empregado)) Notamos que estamos realizando primeiro a Seleção gerando a seguinte tabela: IMPLEMENTAÇÃO DE BANCO DE DADOS 32 A seguir basta Projetarmos as colunas ID, NOME, SALÁRIO E ID_DEPTO Essas duas expressões produzem o mesmo resultado, na tabela abaixo e são equivalentes. OPERAÇÃO PRODUTO CARTESIANO O produto cartesiano é uma operação binária que combina todas as tuplas de duas tabelas. IMPLEMENTAÇÃO DE BANCO DE DADOS 33 A forma geral do Produto Cartesiano é: A X B Onde: X representa a operação de produto cartesiano A e B representam as relações sobre as quais a operação será aplicada. Vejamos um exemplo da operação de Produto Cartesiano: Observemos as tabelas Região e Departamento. A tabela de Departamento possui 3 coluna e 4 linhas e a tabela Região possui duas colunas e 3 linhas. Se desejássemos realizar o produto cartesiano entre elas qual seria expressão algébrica e qual resultado obteríamos? IMPLEMENTAÇÃO DE BANCO DE DADOS 34 A operação ira justapor as colunas e combinar todas as linhas. OPERAÇÃO JUNÇÃO A operação junção atua de forma similar á operação produto cartesiano, porém, a tabela resultante conterá apenas as combinações das tuplas que se relacionam de acordo com uma determinada condição de junção. IMPLEMENTAÇÃO DE BANCO DE DADOS 35 A forma geral da operação junção entre duas tabelas A e B é a seguinte: A ⋈ <condição de junção> B Onde: <condição de junção> é uma expressão relacional, normalmente utilizando o operador =, que determina qual coluna da tabela A deve ser comparada com qual coluna da tabela B. Observação: Normalmente, as colunas envolvidas na condição de junção são a Chave Primária de uma tabela e a Chave Estrangeira na outra. Vejamos um exemplo da operação de Junção: IMPLEMENTAÇÃO DE BANCO DE DADOS 36 Observemos as tabelas Região e Departamento. A tabela de Departamento possui 3 coluna e 4 linhas e a tabela Região possui duas colunas e 3 linhas. Se desejássemos realizar a Junção entre elas qual seria expressão algébrica e qual resultado obteríamos? Departamento ⋈ Id_Regiao = Id Região IMPLEMENTAÇÃO DE BANCO DE DADOS 37 OPERAÇÕES DE CONJUNTO Levando em consideração que as relações podem ser tratadas como conjuntos, podemos então aplicar um conjunto de operações matemáticas sobre as mesmas. Essas operações são: união (∪) , intersecção (∩) e diferença (-). Esse conjunto de operações não é unário, elas se aplicam a duas relações que obedecem à "compatibilidade de união". Operações ✴União ✴ Interseção ✴Diferença Redundância de Tuplas Compatibilidade quanto a união IMPLEMENTAÇÃO DE BANCO DE DADOS 38 1. UNIÃO O resultado desta operação entre duas relações consiste no conjunto de todas as linhas das duas relações A operação de União gera resultados sem redundância de tuplas A forma geral da operação junção entre duas tabelas A e B é a seguinte: A U B A Operação de União é comutativa ou seja A U B produz o mesmo resultado de B U A Vejamos um exemplo da operação de União: IMPLEMENTAÇÃO DE BANCO DE DADOS 39 Observemos a tabela Empregado e a tabela Acionista. ( figura1) Desejamos saber as pessoas que são OU Empregadas OU Acionistas da Empresa. Queremos, então, todos aqueles que aparecem ou no conjunto de Empregado ou no Conjunto de Acionista ou nos Dois. Realizaríamos, portanto, uma União. Empregado U Acionista \ IMPLEMENTAÇÃO DE BANCO DE DADOS 40 Analisando a Tabela Resultante do comando ( figura2) podemos notar que: ✴No resultado aparecem todas as linhas de Empregado e todas a linhas de Acionista. ✴A linha redundante ( 102 Pedro Leitão) aparece apenas uma; ✴Os nomes das colunas correspondem aos nomes das colunas da tabela que aparece a esquerda na expressão no caso a tabela Empregado. 2. INTERSEÇÃO O resultado desta operação entre duas relações consiste no conjunto de todas as linhas que existem em comum nas duas relações A operaçãode Interseção gera resultados sem redundância de tuplas A forma geral da operação Interseção entre duas tabelas A e B é a seguinte: A ∩ B A Operação de Comutativa é comutativa ou seja A ∩ B produz o mesmo resultado de B ∩ A IMPLEMENTAÇÃO DE BANCO DE DADOS 41 Vejamos um exemplo da operação de Interseção: Observemos a tabela Empregado e a tabela Acionista (figura 3). Desejamos saber as pessoas que são Empregadas E Acionistas da Empresa. Queremos, portanto, todos aqueles que aparecem conjunto de Empregado E no Conjunto de Acionista, ou seja, os que aparecem nos Dois conjuntos. Realizaríamos, portanto, uma Interseção. IMPLEMENTAÇÃO DE BANCO DE DADOS 42 A expressão que realiza a Interseção é: Acionista ∩ Empregado Analisando a Tabela Resultante do comando ( Figura 4) podemos notar que: ✴No resultado aparece apenas a linha 102 Pedro Leitão que é comum a duas tabelas. ✴Como esta linha é redundante aparece apenas uma vez ✴Os nomes das colunas correspondem aos nomes das colunas da tabela que aparece a esquerda na expressão. ✴Repare que em relação ao resultado que vimos no exemplo de União, agora o nome das colunas correspondem as colunas de Acionista pois esta tabela aparece a Esquerda na expressão. IMPLEMENTAÇÃO DE BANCO DE DADOS 43 3. DIFERENÇA O resultado desta operação entre duas relações consiste no conjunto de todas as linhas que existem em uma tabela e não existe na outra A operação de Diferença gera resultados sem redundância de tuplas A forma geral da operação Diferença entre duas tabelas A e B é a seguinte: A - B A Operação de Diferença não é comutativa ou seja A - B produz um resultado diferente de B - A IMPLEMENTAÇÃO DE BANCO DE DADOS 44 Vejamos um exemplo da operação de Diferença: Observemos a tabela Empregado e a tabela Acionista (Figura 5). Desejamos saber as pessoas que são Empregadas E NÃO são Acionistas da Empresa. Queremos, portanto, todos aqueles que aparecem conjunto de Empregado E NÃO a p a r e c e m n o C o n j u n t o d e A c i o n i s t a . Realizaríamos, portanto, uma Diferença. IMPLEMENTAÇÃO DE BANCO DE DADOS 45 ou 4. TABELA TEMPORÁRIA Em consultas complexas pode ser interessante criar tabelas temporárias a partir de uma operação algébrica visando seu uso posterior vejamos um exemplo: IMPLEMENTAÇÃO DE BANCO DE DADOS 46 Considere a tabela departamento (Figura 8 a) desejamos criar uma tabela temporária denominada T1 com o ID e NOME dos departamentos da Regiao 1, poderíamos obtê-la então atribuindo a saída da expressão relacional, utilizando o operador = , a uma tabela. A expressão seria então: Observe o resultado na Tabela T1 (Figura 8 b). IMPLEMENTAÇÃO DE BANCO DE DADOS 47 5. OPERAÇÕES DE CONJUNTOS A PARTIR DE CONSULTAS Todos os exemplos que vimos até agora em Operações de Conjunto atuaram sobre conjuntos de linhas que correspondiam a tabelas existentes. Vejamos um exemplo diferente onde os conjuntos serão gerados a partir da operação de projeção e seleção Observemos a tabela Empregado (Figura 9). Desejamos saber o ID e o nome dos empregados que são do departamento 10 ou que ganham salário maior ou igual a 1500,00. Apesar de podermos resolver esta situação apenas com projeção e seleção, conforme já vimos anteriormente, em alguns casos pode ser mais interessante operar com conjuntos. Como faríamos então? IMPLEMENTAÇÃO DE BANCO DE DADOS 48 Inicialmente projetaríamos o ID e NOME dos empregados do departamento 10 a atribuiríamos a uma tabela temporária usando a expressão: A tabela resultante T1 ( Figura 10) teria então três linhas: A seguir projetaríamos o ID e NOME dos empregados com salario maior ou igual a 1500,00 e atribuiríamos a uma tabela temporária usando a expressão: A tabela resultante T2 (Figura 11) teria então três linhas: IMPLEMENTAÇÃO DE BANCO DE DADOS 49 Finalmente faríamos a União das tabelas temporárias usando a expressão: T1 U T2 Analisando a Tabela Resultante (Figura 12) do comando podemos notar que: ✴ No resultado aparecem todas as linhas de T1e todas a linhas de T2. ✴ As linha redundantes ( 101 Carlos Antunes e 104 Marco Aurélio ) aparecem apenas uma vez ✴Os nomes das colunas correspondem aos nomes das colunas da tabela que aparece a esquerda na expressão no caso a tabela T1. O mesmo tipo de raciocínio poderia ser aplicado as operações de Interseção e Diferença. IMPLEMENTAÇÃO DE BANCO DE DADOS 50 DIVISÃO É uma operação adicional que produz como resultado a projeção de todos os elementos da primeira relação que se relacionam com todos os elementos da segunda relação. É utilizada para consultas do tipo todos os eleitores que participaram de todas as votações, os sócios que pagaram todas as mensalidades e outras do mesmo tipo. A representação da operação de divisão entre as relações A e B é A / B Vejamos um exemplo Considere as tabelas Funcionário, Projeto e Funcionario_Projeto A tabela Funcionário_Projeto faz a associação entre os Projetos e os Empregados. Vamos supor que desejamos saber o ID dos Funcionários que Trabalham em todos os Projetos. Qual seria a expressão que realizaria a operação desejada e qual resultado obteríamos? IMPLEMENTAÇÃO DE BANCO DE DADOS 51 Inicialmente, teríamos que projetar o ID dos projetos a partir da expressão, T1 = π Id_Proj(Projeto) IMPLEMENTAÇÃO DE BANCO DE DADOS 52 gerando a tabela T1 ( figura abaixo). A s e g u i r , f a r í a m o s a d i v i s ã o d e Funcionário_Projeto por T1 com a expressão Funcionario_Projeto / T1 Analisando a Tabela Resultante do comando, podemos notar que: ✴no resultado, aparece apenas a coluna ID_FUNC, que é a coluna que existe na tabela funcionário_projeto, que não existe em T1; ✴a divisão é realizada, comparando-se todas as linhas de funcionário_projeto com as linhas de T1 e retornando o valor de ID_FUNC, que se relaciona com TODOS os valores de ID_PROJ em T1; ✴como temos 3 valores de ID_PROJ em T1 (11,12,13), ao realizarmos a divisão irá retornar o valor de ID_FUNC que se associe aos três valores; IMPLEMENTAÇÃO DE BANCO DE DADOS 53 ✴analisando Funcionário_Projeto, notamos que o Funcionário 101 está associado aos três projetos, portanto ele faz parte do conjunto resposta; ✴de forma contrária, o funcionário 102 estáassociado APENAS aos projetos 12 e 13, não retornando na divisão. Nesta aula, você: •Aprendeu a respeito da álgebra relacional. •Compreendeu as principais operações da álgebra relacional. •Construiu expressões de álgebra relacional. IMPLEMENTAÇÃO DE BANCO DE DADOS 54 REGISTRO DE PARTICIPAÇÃO 1. A operação de álgebra relacional que seleciona as colunas de uma tabela é a: 1) Seleção. 2) Projeção. 3) Junção. 4) União. 5) Interseção. 2. Quando queremos produzir, como resultado de uma consulta, um conjuntos de dados que é junção de dois conjuntos de dados de mesmo tipos, usamos a: 1) Junção. 2) Intersecção. 3) Seleção. 4) Produto Cartesiano. 5) União. 3. É um exemplo de operação unária: 1) Junção. 2) Seleção. 3) União. 4) Diferença. 5) Interseção. IMPLEMENTAÇÃO DE BANCO DE DADOS 55 4. Quando queremos produzir uma tabela a partir outras duas, combinando apenas as tuplas que se relacionam, devemos fazer uma: 1) Projeção. 2) Junção. 3) Seleção. 4) Interseção. 5) Diferença. 5. A operação que nos permite recuperar informações do tipo: os jogadores que participaram de todos os torneios, é a: 1) Junção. 2) Seleção. 3) União. 4) Diferença. 5) Divisão. IMPLEMENTAÇÃO DE BANCO DE DADOS 56 CCT0199_EX_A2_201102276103 Disciplina: CCT0199 - IMPL. BANCO DE DADOS Período Acad.: 2014.1 - EAD (G) / EX 1. Considere a relação: FILME (nome, diretor, tempoduracao, genero, ano) A consulta em álgebra relacional que retorna o nome e o ano de todos os filmes de ação com tempo de duração entre 120 e 180 minutos. ( ) ∏ nome, ano (σ(genero = "ação" AND tempoduracao >= 120 OR tempoduracao <= 180) (FILME) ) ( X ) ∏ nome, ano (σ(genero = "ação" AND tempoduracao >= 120 AND tempoduracao <= 180) (FILME) ) ( ) σ nome, ano (∏(genero = "ação" AND tempoduracao >= 120 AND tempoduracao <= 180) (FILME) ) ( ) ∏ nome, ano (σ(genero = "ação" AND tempoduracao = 120) (FILME) ) ( ) ∏ nome, ano (σ(genero = "ação" OR tempoduracao >= 120 AND tempoduracao <= 180) (FILME) ) IMPLEMENTAÇÃO DE BANCO DE DADOS 57 2. A partir da tabela abaixo, identifique a expressão, em álgebra relacional, que permite listar a matrícula dos funcionários do setor de Projetos: ( ) π setor = "Projetos" (σ matricula (Funcionarios_ste)) ( X ) π matricula (σ setor = "Projetos" (Funcionarios_ste)) ( ) σ setor = "Projetos" (Funcionarios_ste) ( ) π Funcionarios_ste (σ matricula, setor = "Projetos") ( ) σ matricula (π setor = "Projetos" (Funcionarios_ste)) IMPLEMENTAÇÃO DE BANCO DE DADOS 58 3. De acordo com a tabela a seguir, como fica a instrução de seleção para recuperarmos os funcionários possuem ID maior do que 102, trabalham no Departamento 10 e ganham mais de R$ 2.000,00 de Salário? ( ) s ID > 102 OU id_Depto = 10 E Salario > 2000 (Empregado) ( ) s ID = 102 E id_Depto = 10 E Salario > 2000 (Empregado) ( ) s ID > 102 OU id_Depto = 10 OU Salario > 2000 (Empregado) ( X ) s ID > 102 E id_Depto = 10 E Salario > 2000 (Empregado) ( ) s ID = 102 OU id_Depto = OU E Salario > 2000 (Empregado) 1 IMPLEMENTAÇÃO DE BANCO DE DADOS 59 AULA 03 SQL PARTE 1 – CREATE TABLE E DML Nesta aula, você irá: 1.Conhecer a linguagem SQL; 2.Aprender a instalar e configurar um SGBD; 3.Aprender a criar tabelas; 4.Manipular dados. Instalação e configuração do SGBD Oracle 10G Express Visando tornar nossas aulas de SQL as mais práticas possíveis, utilizaremos o Oracle 10G Express. Para isso, é importante que você faça o download do SGBD e realize sua instalação no computador que irá utilizar ˘˘para seu estudo. A parte inicial de nossa aula será, então, dedicada a essa tarefa. Mãos a obra. A LINGUAQEM SQL COMO PADRÃO Agora que já instalamos e criamos o usuário AULABD, vamos começar nosso estudo da Linguagem SQL. SQL quer dizer Structured Query Language e é o padrão mundial de acesso às bases de dados relacionais. Partes da SQL IMPLEMENTAÇÃO DE BANCO DE DADOS 60 ✴DDL Create, Alter, Drop, Rename, Truncate Permitem a criação e definição de objetos como tabelas, views e outros objetos no banco de dados. ✴DRL Select É o comando mais comum do SQL. U t i l i z a d o a m p l a m e n t e p a r a recuperação dos dados de uma base. ✴DML Insert, Delete, Update Comandos de manipulação dos dados. Usados nas aplicações que mantêm a base de informações com inserções, atualizações e deleções de dados. ✴DCL Grant, Revoke São utilizados para atribuir ou remover direitos de acesso a objetos do banco de dados e suas estruturas. ✴Controle de Transações Commit, Rollback, Savepoint Faz parte do padrão SQL a efetivação total, parcial ou o abandono de uma transação através da utilização destes comandos. IMPLEMENTAÇÃO DE BANCO DE DADOS 61 Criação de Tabelas Iniciaremos nosso Estudo pelo Comando de Criação de Tabelas A tabela é a forma básica de armazenamento de informações em um sistema gerenciador de banco de dados relacional e, por isso, deve conter um conjunto de elementos, alguns opcionais, na sua composição e que são: NOME IMPLEMENTAÇÃO DE BANCO DE DADOS 62 COLUNAS DICA A recomendação de encurtar os nomes de colunas se faz necessária, mas não obrigatória, para facilitar o trabalho do analisador de comandos SQL do RDBMS (parser). Quanto maiores os nomes das colunas, mais demorada é a etapa de aná l i se do comando , po rém, devemos ter o cuidado para não IMPLEMENTAÇÃO DE BANCO DE DADOS 63 tirarmos o significado claro da coluna na tabela. TIPOS DE DADOS Cada coluna de uma tabela, tem que ter um tipo de dado definido e único, obedecendo a lista de tipos definidos pelo RDBMS. No Oracle existe uma diversidade bastante grande de tipos e alguns são descritos na tabela abaixo: IMPLEMENTAÇÃO DE BANCO DE DADOS 64 RESTRIÇÕES (CONSTRAINS) Criando a tabela DEPARTAMENTO Antes de conhecermos melhor os comandos desta aula, vamos analisar uma tabela: IMPLEMENTAÇÃO DE BANCO DE DADOS 65 Tabela Coluna Tipo Tamanho Observação Departa mento ID numérico 5 C h a v e Primária Departa mento NOME caracter 40 obrigatóri o A análise da tabela nos mostra o seguinte: - a tabela possui duas colunas ( ID e Nome); - a coluna ID é um número inteiro de 5 dígitos, logo seu tipo deve ser number(5); - a coluna ID é achave primária, logo deve receber a constraint Primary Key; - a coluna Nome é alfanumérica de 40 caracteres, logo seu tipo deve ser varchar2(40); - a coluna Nome é de preenchimento obrigatório, logo deve receber a constraint Not Null . Dessa forma, o comando para criar a tabela acima poderia ser parecido com: CRIAÇÃO DA TABELA DEPARTAMENTO CREATE TABLE DEPARTAMENTO ( ID N U M B E R ( 7 ) P R I M A R Y K E Y, N O M E VARCHAR2(40) NOT NULL) IMPLEMENTAÇÃO DE BANCO DE DADOS 66 Para facilitar a visualização dos comandos, podemos identá- los , já que espaços, tabulações e ENTER não são considerados em um comando SQL: CREATE TABLE DEPARTAMENTO ( ID NUMBER(7) PRIMARY KEY, NOME VARCHAR2(40) NOT NULL) Observe a restrição Primary Key que indica que a coluna codigo_cargo é a Chave Primária da tabela e Not Null, indicando que a coluna Nome é de preenchimento obrigatório. Vamos, então, criar a tabela Departamento no Oracle. Primeiro, vamos fazer login com o Usuário AULABD: Na tela inicial, clique em Sql e na Seguinte em Comandos Sql IMPLEMENTAÇÃO DE BANCO DE DADOS 67 Estamos, então, na tela de execução de comandos SQL; digite o comando de Criação da Tabela e Clique em Executar. IMPLEMENTAÇÃO DE BANCO DE DADOS 68 Após e execução, irá aparecer uma mensagem, informando que o comando foi executado e a tabela criada. P o d e m o s v e r a e s t r u t u r a d a t a b e l a Departamento através do comando Describe. (o comando pode ser escrito de forma abreviada como DESC) Observe que a coluna ID está com o valor 1, em Chave Primária, identificando que ela possui esta restrição. Note, ainda, que tanto a coluna NOME, como ID, estão sem marcação em anulável, NOME IMPLEMENTAÇÃO DE BANCO DE DADOS 69 porque tem a restrição NOT NULL e ID por ser chave primária. ACRESCENTANDO COLUNAS EM TABELAS Podemos acrescentar colunas em tabela já criada com comando Alter Table. Sintaxe: alter table <nome tabela> add <nome_coluna> <tipo da coluna> <constrain> EXEMPLO ALTER TABLE DEPARTAMENTO ADD descricao VARCHAR (30) NOT NULL; Vamos conferir como ficou a estrutura da tabela. Dê o comando DESCRIBE DEPARTAMENTO. IMPLEMENTAÇÃO DE BANCO DE DADOS 70 Observe que a tabela possui agora 3 colunas : ID,NOME e DESCRIÇÃO. ALTERANDO TABELAS O comando ALTER TABLE permite, além de criar novas colunas, podermos alterar constraints já existentes em colunas de nossas tabelas. Por exemplo, para tornarmos o campo DESCRIÇÂO, da tabela Departamento, não obrigatório, podemos utilizar a cláusula MODIFY, conforme abaixo: ALTER TABLE DEPARTAMENTO MODIFY DESCRICAO NULL Vamos executar o comando: para isso, digite-o no Oracle e clique em executar. Observe a mensagem, informando que a tabela foi alterada Vamos conferir como ficou a estrutura da tabela. Dê o comando DESCRIBE DEPARTAMENTO. Observe que a tabela possui agora 3 colunas : ID,NOME e DESCRIÇÃO. IMPLEMENTAÇÃO DE BANCO DE DADOS 71 ELIMINANDO COLUNAS DE TABELAS Com o Oracle é possível eliminar colunas de tabelas, inclusive aquelas referenciadas por constraints e índices, até mesmo chaves primárias, únicas e estrangeiras. É verdade que cuidados em nível de aplicação devem ser tomados por parte dos desenvolvedores e DBAs, p o r é m , o p r o d u t o i m p l e m e n t a e s t a funcionalidade. Ao eliminarmos uma coluna, suas restrições, caso existam, também são removidas do dicionário de dados. IMPLEMENTAÇÃO DE BANCO DE DADOS 72 Sintaxe alter table <nome_tabela> drop column <nome_coluna>; Onde: <nome_tabela> é o nome da tabela a qual será eliminada a coluna. <nome coluna> é o nome da coluna que será eliminada. Por exemplo, se desejarmos eliminar a coluna descrição da tabela Departamento, daria o seguinte comando: ALTER TABLE DEPARTAMENTO DROP COLUMN DESCRICAO Vamos executar o comando, para isso, digite-o no Oracle e clique em executar. Observe a mensagem informando que a tabela foi alterada. IMPLEMENTAÇÃO DE BANCO DE DADOS 73 Vamos conferir como ficou a estrutura da tabela. Dê o comando DESCRIBE DEPARTAMENTO. Observe que a tabela possui agora apenas 2 colunas : ID e NOME IMPLEMENTAÇÃO DE BANCO DE DADOS 74 MAIS CONSTRAINTS Até agora utilizamos apenas as constraints NOT NULL e PRIMARY KEY, vejamos agora as Constraints UNIQUE e FOREIGN KEY. CAMPOS ÚNICOS Esta constraint determina que uma coluna não pode ter valor repetido; não significando, entretanto, que deva possuir valor, ou seja, que tenho preenchimento obrigatório. Isso significa que podemos ter mais uma linha nula em uma coluna única. Para estabelecer essa restrição, estabelecemos UNIQUE como restrição da coluna, por exemplo: CPF char(11) UNIQUE, R E F O R Ç A N D O A I N T E G R I D A D E REFERENCIAL COM CHAVE ESTRANGEIRA Os relacionamentos entre tabelas são criados, gerando-se chaves estrangeiras (foreign key – FK) nas tabelas FILHO que referenciam colunas chaves nas tabelas PAI. Para estabelecer essa restrição, acrescentamos REFERENCES definição da coluna como exemplo: IMPLEMENTAÇÃO DE BANCO DE DADOS 75 I D _ D E P T O n u m b e r ( 7 ) R e f e r e n c e s Departamento(ID), Onde: Id_depto é o nome da coluna. Number(7) o tipo da coluna. References identifica a restrição de chave estrangeira. Departamento é o nome da tabela para onde aponta a chave estrangeira. (ID) é coluna da tabela departamento apontada pela chave estrangeira. Vejamos um exemplo de criação de Tabela utilizando estas restrições. Observe o comando, de sua análise podemos observar que: • A coluna ID é sua chave primaria; • As colunas ULT_NOME e PRIM_NOME são de preenchimento obrigatório; • A coluna CPF é única; IMPLEMENTAÇÃO DE BANCO DE DADOS 76 • A coluna ID_DEPTO é uma chave estrangeira para a tabela departamento; • A coluna ID_GERENTE é uma chave estrangeira para a própria tabela empregado, que se originou de uma autorelacionamento na modelagem conceitual. CREATE TABLE EMPREGADO (ID NUMBER(7) PRIMARY KEY, ULT_NOME VARCHAR(20) NOT NULL, PRIM_NOME VARCHAR(20) NOT NULL, CARGO VARCHAR(30), SALARIO NUMBER(7,2), DT_ADMISSAO DATE, CPF CHAR(11) UNIQUE, ID_DEPTO NUMBER(7) REFERENCES DEPARTAMENTO(ID), ID_GERENTE NUMBER(7) REFERENCES EMPREGADO(ID)) Vamos conferir como ficou a estrutura da tabela. De o comando DESCRIBE EMPREGADO. IMPLEMENTAÇÃO DE BANCO DE DADOS77 CONSTRAINT DE COLUNAS E TABELAS As constraints podem ser definidas junto com a coluna. ou separadamente (no final do comando create table ou com o comando alter table). As constraints not null só podem ser definidas junto com a definição da coluna. As constraints de tabela são util izadas principalmente para criar constraints compostas. onde duas ou mais colunas fazem parte da constraint. como por exemplo, chaves primárias compostas. Vejamos um exemplo: IMPLEMENTAÇÃO DE BANCO DE DADOS 78 A tabela Turmas possui uma chave primary composta pelas colunas CODIGO_TURMA e CODIGO_CURSO. O comando para sua criação é: CREATE TABLE TURMAS (CODIGO_TURMA NUMBER(6), CODIGO_CURSO NUMBER(3), CODIGO_FUNCIONARIO NUMBER(6), DATA_INICIO DATE, DATA_FIM DATE , SALA NUMBER(2), P R I M A R Y K E Y ( C O D I G O _ T U R M A , CODIGO_CURSO) ); Execute o comando no ORACLE. Observe a mensagem de tabela criada. Vamos conferir como ficou a estrutura da tabela. Dê o comando DESCRIBE TURMAS. Observe, particularmente, a indicação de que a tabela possui uma chave primária composta p e l a s c o l u n a s C O D I G O _ T U R M A e CODIGO_CURSO. IMPLEMENTAÇÃO DE BANCO DE DADOS 79 DETALHE INCLUINDO UMA FOREIGN KEY NUMA TABELA EXISTENTE Também podemos incluir a constraint de Foreign Key após a criação da tabela, para tal, basta especificar a adição da constraint no comando ALTER TABLE. IMPLEMENTAÇÃO DE BANCO DE DADOS 80 Para exemplificar, vamos primeiro criar a tabela CLIENTE, utilizando o comando abaixo: CREATE TABLE CLIENTE ( ID NUMBER(7) PRIMARY KEY, NOME VARCHAR2(40) NOT NULL, VENDEDOR NUMBER(7)) A tabela foi criada, mas a coluna Vendedor deveria ser uma chave estrangeira para a tabela Empregado na coluna ID, podemos dar o seguinte comando de ALTER TABLE: ALTER TABLE CLIENTE ADD FOREIGN KEY (VENDEDOR) REFERENCES EMPREGADO(ID) Onde: CLIENTE – é a tabela a ser alterada ADD FOREIGN KEY – é a restrição a ser acrescida (VENDEDOR) é a coluna que recebera a constraint REFERENCES EMPREGADO(ID) indica a tabela e coluna referenciadas pela chave estrangeira. IMPLEMENTAÇÃO DE BANCO DE DADOS 81 RENOMEANDO TABELAS É possível trocar o nome de uma tabela através do comando RENAME. Sua sintaxe é bem simples, conforme mostrada abaixo: Renomeando a Tabela TURMAS. RENAME TURMAS TO NOVATURMA. Execute o comando no ORACLE. Observe a mensagem de INSTRUÇÃO PROCESSADA. IMPLEMENTAÇÃO DE BANCO DE DADOS 82 ELIMINANDO TABELAS Para eliminarmos uma tabela já existente, basta executarmos o comando DROP TABLE. Veja o exemplo: Eliminando a tabela NOVATURMA DROP TABLE NOVATURMA Execute o comando no ORACLE IMPLEMENTAÇÃO DE BANCO DE DADOS 83 Observe a mensagem de TABELA ELIMINADA. Vamos conferir como ficou a estrutura da tabela NOVATURMA. Dê o comando DESCRIBE NOVATURMA. Observe a mensagem dizendo que o objeto não pode ser encontrado. Isso aconteceu, porque a tabela foi eliminada do banco de dados. Se você aplicar este comando em uma tabela que possua filhos, como por exemplo, a tabela Departamento irá gerar um erro ( veja figura). O erro ocorre porque essa tabela é referenciada por uma foreign key de outra tabela, que ficaria inconsistente caso esta fosse removida; o banco de dados, muito acertadamente, não permite este comando DROP TABLE. Caso seja realmente necessário eliminar essa tabela, posso solicitar que todas as constraints a ela vinculadas sejam eliminadas também, para isso acrescentamos CASCADE CONSTRAINTS ao comando. IMPLEMENTAÇÃO DE BANCO DE DADOS 84 MANIPULANDO DADOS Agora que já aprendemos a criar, alterar e eliminar tabelas iremos estudar os comandos de manipulação de dados que permitam que façamos a inclusão, alteremos ou eliminemos linhas das tabelas. Esses comandos são: INSERT% DELETE% UPDATE COMANDO INSERT - INSERINDO LINHAS O comando INSERT insere linhas em uma tabela. A forma mais simples do comando IMPLEMENTAÇÃO DE BANCO DE DADOS 85 INSERT insere somente uma linha , dados os valores conforme a sintaxe abaixo: insert into % <nome_tabela> (coluna1, coluna2, ..., colunan) values (valor1, valor2, ..., valorn) INSERT SEM REFERENCIAR COLUNAS INSERT INTO DEPARTAMENTO VALUES ( 10, ‘Financeiro’) IMPLEMENTAÇÃO DE BANCO DE DADOS 86 INSERT REFERECIANDO COLUNAS INSERT INTO DEPARTAMENTO (ID,NOME) VALUES ( 20, ‘Vendas’) IMPLEMENTAÇÃO DE BANCO DE DADOS 87 CONFERINDO INSERÇÃO SELECT * FROM DEPARTAMENTO Comando Update Para alterarmos dados já existentes em nossas tabelas utilizaremos o comando UPDATE. Veja a sintaxe do comando abaixo: UPDATE [schema. ] nome_tabela SET coluna1 = expressão I subquery [, colunan = ... ] WHERE condição IMPLEMENTAÇÃO DE BANCO DE DADOS 88 ALTEROU O ID DE 110 PARA 1110 ATUALIZANDO LINHAS Vejamos um exemplo que ira alterar o ID de todos os Departamentos somando 100 a eles. UPDATE DEPARTAMENTO SET ID = ID + 100 Execute o comando no ORACLE Observe a mensagem de que as duas linhas da tabela foram atualizadas. IMPLEMENTAÇÃO DE BANCO DE DADOS 89 Conferindo Inserção IMPLEMENTAÇÃO DE BANCO DE DADOS 90 Update Com Condição UPDATE DEPARTAMENTO SET ID = ID + 1000 WHERE ID = 110 IMPLEMENTAÇÃO DE BANCO DE DADOS 91 Conferindo Update SELECT * FROM DEPARTAMENTO Podemos atualizar apenas algumas linhas, para isso, iremos estabelecer uma condição na cláusula where que a linha a ser atualizada deve atender. Por exemplo, se desejamos somar 1000 apenas ao ID do departamento 110, devemos dar o seguinte comando: UPDATE DEPARTAMENTO SET ID = ID + 1000 WHERE ID = 110 IMPLEMENTAÇÃO DE BANCO DE DADOS 92 Execute o comando no ORACLE. Observe a mensagem de que uma linha foi atualizada. Comando Delete Para excluirmos linhas em uma tabela utilizamos o comando DELETE. Veja a sintaxe do comando abaixo: DELETE [FROM] [schema.]nome_tabela WHERE condição IMPLEMENTAÇÃO DE BANCO DE DADOS93 IMPLEMENTAÇÃO DE BANCO DE DADOS 94 Registro de Participação 1. O comando que exclui linhas de uma tabela é: 1) Update 2) Delete 3) Insert 4) Alter 5) Drop 2. O comando que atualiza linhas é o comando: 1) Update 2) Delete 3) Insert 4) Alter 5) Drop 3. O comando que insere linhas é o comando: 1) Update 2) Delete 3) Insert 4) Alter 5) Drop 4. O comando que me permite incluir uma nova coluna em um tabela é o comando: 1) Update 2) Delete 3) Insert IMPLEMENTAÇÃO DE BANCO DE DADOS 95 4) Alter 5) Drop CCT0199_EX_A3_201102276103 Disciplina: CCT0199 - IMPL. BANCO DE DADOS Período Acad.: 2014.1 - EAD (G) / EX 1. Considere o script: CREATE TABLE PECA (CODIGO NUMERIC(5) NOT NULL, DESCRICAO VARCHAR(20) NOT NULL, ESTOQUE NUMERIC(5) NOT NULL, PRIMARY KEY(CODIGO)); Após a criação desta tabela, o comando que inclui a tupla (1234, "lápis de cor", 500) está representado em: ( X ) INSERT INTO PECA (1234, "lápis de cor", 500); ( ) INSERT PECAS (1234, "lápis de cor", 500); ( ) INSERT INTO PECAS (1234, "lápis de cor", 500); ( ) INCLUDE INTO PECA (1234, "lápis de cor", 500); ( ) INSERT INTO PECAS ("1234", "lápis de cor", 500); IMPLEMENTAÇÃO DE BANCO DE DADOS 96 2. Partindo da tabela a seguir, considerando que a mesma já foi criada de acordo com essa ordem de atributos, como ficaria o comando SQL para inserir um registro na tabela? ( ) INSERT INTO X_PROD VALUES (20, TV Led 40 polegadas, 400, 3000); ( ) INSERT INTO X_PROD VALUES ( TV Led 40 polegadas, 20, 400, 3000); ( X ) INSERT INTO X_PROD VALUES ( 20, 'TV Led 40 polegadas', 400, 3000); ( ) INSERT INTO X_PROD VALUES ('20', 'TV Led 40 polegadas', '400', '3000'); ( ) INSERT INTO X_PROD VALUES ('TV Led 40 polegadas', 20, 400, 3000); IMPLEMENTAÇÃO DE BANCO DE DADOS 97 3. A linguagem SQL pode ter vários enfoques, EXCETO: ( ) Linguagem de administração de banco de dados ( ) Linguagem para acesso às bases de dados ( ) Linguagem de consulta em ambiente cliente/ servidor ( ) Linguagem interativa de consulta (query AdHoc) ( X ) Linguagem para a programação de sistemas de informação IMPLEMENTAÇÃO DE BANCO DE DADOS 98 AULA 04 SQL PARTE 2 - SELECT UMA TABELA Nesta aula, você irá: 1.Conhecer o comando SELECT. 2.Executar um script no Oracle. 3.Recuperar dados de uma tabela. MODELO DE DADOS Visando tornar as aulas o mais prática possível daremos todos os exemplos utilizando o banco de dados de um empresa que é composto de 3 tabelas: Empregado. Cliente. Departamento. A descrição das tabelas é a seguinte: IMPLEMENTAÇÃO DE BANCO DE DADOS 99 Modelo de Dados IMPLEMENTAÇÃO DE BANCO DE DADOS 100 MODELO DE DADOS Para criar o banco de dados que utilizaremos em nossas aulas iremos executar o scr ipt AULAIMPBD disponível na biblioteca da disciplina. É importante que você executado o script antes de iniciar aula, pois desta forma poderá acompanhar os exemplos e executar os exercícios de fixação. Pretendemos, desta forma, tornar a aula o mais interativa possível, recomendamos fortemente, portanto, que ao realizarem esta aula estejam conectados ao ORACLE e executem os comandos para ver o resultado Como precisamos criar o banco e executar o script iremos agora aprender a fazer esta atividade. Existem pelos menos duas formas diferentes de executar um script no Oracle 10G Express, pela interface gráfica (via browser) ou pela linha de comando. Como a interface gráfica, dependendo da configuração do Windows pode dar algum IMPLEMENTAÇÃO DE BANCO DE DADOS 101 problema optamos, nesta disciplina, por utilizar a interface de linha de comando. Para acionar esta interface entre no menu de programas e selecione Executar Linha de Comandos SQL. Será aberta uma interface estilo prompt de comando do DOS. Nesta interface você deve digitar conn aulabd/estacio e dar enter. Vamos entender o comando: IMPLEMENTAÇÃO DE BANCO DE DADOS 102 conn : é a abreviatura de connect. aulabd: o nome do usuário que criamos na aula 2 para utilizarmos, se você criou o usuário com outro nome deve substutir aulabd pelo usuário que criou. / : separa o nome do usuário da senha. Estacio: a senha do usuário aulabd, se você utilizou outra senha deve digitá-la aqui no lugar da senha estacio. Se a conexão for bem sucedida aparecerá a mensagem conectado. A seguir você deve digitar no prompt de comando @ e arrastar o script do local onde ele esta no computador para a janela de linha de comandos. @ no prompt de comando do ORACLE é um comando para executar o script. Após arrastar e soltar o script irá aparecer o caminho para o diretório onde o script esta armazenado. Feito isto basta dar enter para o script ser executado. Ao invés de arrastar e soltar você pode também digitar o caminho no prompt, só que isso normalmente da mais trabalho. IMPLEMENTAÇÃO DE BANCO DE DADOS 103 Após o enter o script é executado e ao seu termino aparece a mensagem tabelas criadas e populadas. Se você conseguiu fazer todos os passos e a mensagem apareceu, parabéns, você criou o banco de dados que será utilizada nas aulas. IMPLEMENTAÇÃO DE BANCO DE DADOS 104 ARQUIVO set feedback off; DROP TABLE CLIENTE CASCADE CONSTRAINT; DROP TABLE EMPREGADO CASCADE CONSTRAINT; DROP TABLE DEPARTAMENTO CASCADE CONSTRAINT; CREATE TABLE DEPARTAMENTO ( ID NUMBER(7) PRIMARY KEY, NOME VARCHAR2(40) NOT NULL); INSERT INTO DEPARTAMENTO VALUES ( 10, 'Financeiro'); INSERT INTO DEPARTAMENTO VALUES ( 20, 'Vendas'); INSERT INTO DEPARTAMENTO VALUES ( 30, 'Administrativo'); COMMIT; IMPLEMENTAÇÃO DE BANCO DE DADOS 105 CREATE TABLE EMPREGADO ( ID NUMBER(7) PRIMARY KEY, ULT_NOME VARCHAR2(20) NOT NULL, PRIM_NOME VARCHAR2(20) NOT NULL, CARGO VARCHAR2(30), SALARIO NUMBER(7,2), DT_ADMISSAO DATE, CPF CHAR(11) UNIQUE, ID_DEPTO NUMBER(7) REFERENCES DEPARTAMENTO(ID), ID_GERENTE NUMBER(7) REFERENCES EMPREGADO(ID)); INSERT INTO EMPREGADO VALUES( 1, 'Velasquez', 'Carmen','Presidente', 25000, '08/04/2008','12345678901', 30,null); INSERT INTO EMPREGADO VALUES( 2, 'Neves', 'Lauro', 'Diretor de Vendas',19500, '07/03/2009','23456789012',20, 1); INSERT INTO EMPREGADO VALUES( 3, 'Nogueira', 'Mário','Diretor Financeiro', 18000, '07/04/2010','34567890123',10,1); IMPLEMENTAÇÃO DE BANCO DE DADOS106 INSERT INTO EMPREGADO VALUES( 4, 'Queiroz', 'Mark','Gerente de Vendas',8000, '07/11/2010','12345432123',20,2); INSERT INTO EMPREGADO VALUES( 5, 'Rodrigues', 'Alberto', 'Vendedor', 4000, '10/1/2008', '87965432123', 20, 4); INSERT INTO EMPREGADO VALUES( 6, 'Ugarte', 'Marlene', 'Vendedor', 3500,'23/11/2009', '87654345678', 20,4); COMMIT; CREATE TABLE CLIENTE ( ID NUMBER(7) PRIMARY KEY, NOME VARCHAR2(40) NOT NULL, VENDEDOR NUMBER(7) REFERENCES EMPREGADO(ID)); INSERT INTO CLIENTE VALUES ( 110, 'Ponto Quente',5); INSERT INTO CLIENTE VALUES ( 120, 'Casa Supimpa',6); INSERT INTO CLIENTE VALUES ( 130, 'Coisas e Tralhas',5); IMPLEMENTAÇÃO DE BANCO DE DADOS 107 INSERT INTO CLIENTE VALUES ( 140, 'Casa Desconto',null); COMMIT; prompt tabelas criadas e populadas; CONSULTANDO DADOS DE UMA TABELA SELECT é o comando SQL para recuperação dos dados de uma ou mais tabelas. Pode referenciar todas ou parte das colunas de uma ou mais tabelas, além de poder restringir as linhas que serão recuperadas. Como é orientado a conjuntos, um único comando pode retornar nenhuma ou várias linhas de uma combinação de tabelas desde que satisfaçam restrições impostas. Os componentes básicos do comando são: A Cláusula SELECT. L i s t a a s c o l u n a s q u e s e r ã o recuperadas. Se ut i l izarmos o art i f íc io do * (asterisco) na cláusula SELECT, estaremos definindo que todas as colunas serão recuperadas. IMPLEMENTAÇÃO DE BANCO DE DADOS 108 A Cláusula FROM. Define a tabela que será recuperada. RETORNANDO UMA TABELA INTEIRA Acesse o Oracle e digite o seguinte comando: SELECT * FROM EMPREGADO. No comando acima, estaremos selecionando todas as colunas e todas as linhas da tabela EMPREGADO. Teremos uma resposta, semelhante a figura. IMPLEMENTAÇÃO DE BANCO DE DADOS 109 Observe que não há nenhuma ordem ou seleção de linhas ou colunas. RETORNANDO COLUNAS ESPECIFICAS Na álgebra relacional vimos que existe a operação de projeção que nos permite retornar apenas algumas colunas da tabela, mas todas as linhas, o mesmo pode ser obtido em SQL, para isso basta listarmos as colunas que queremos na claúsula SELECT, separando-as por virgulas. Vejamos um exemplo: Acesse o Oracle e digite o seguinte comando: IMPLEMENTAÇÃO DE BANCO DE DADOS 110 SELECT ID, PRIM_NOME, ULT_NOME FROM EMPREGADO. No comando acima, estaremos selecionando apenas três colunas e todas as linhas da tabela EMPREGADO. Neste segundo caso não são exibidas as co lunas CARGO,SALARIO, DT_ADMISSAO, CPF,ID_DEPTO e ID_GERENTE No primeiro comando analisado, um asterisco substitui a lista de colunas desejadas, indicando a dados que t o d a s a s c o l u n a s d e v e m s e r informadas. IMPLEMENTAÇÃO DE BANCO DE DADOS 111 INCREMENTANDO A CONSULTA Para efetuar consultas mais complexas e derivar dados a partir das informações contidas nas tabelas podemos construir expressões na clausula SELECT. As expressões podem ser aritméticas ou a l f a n u m é r i c a s , f a z e n d o p o r e x e m p l o concatenações. Uma expressão aritmética pode conter os seguintes operadores: *, /, +, - Para concatenarmos duas colunas utilizaremos o operador ⎮⎮ Em uma expressão podemos especificar não apenas uma coluna, mas um dado derivado de uma ou mais colunas, por exemplo: A figura mostra a tabela empregado, como ficaria então o comando que listaria o nome completo do empregado e o seu salário anual (consideramos o salário anual o salário mensal multiplicado por doze) IMPLEMENTAÇÃO DE BANCO DE DADOS 112 O comando seria: SELECT PRIM_NOME || ' ' || ULT_NOME, SALARIO * 12 FROM EMPREGADO Teremos uma resposta, semelhante a figura. IMPLEMENTAÇÃO DE BANCO DE DADOS 113 Observe o seguinte: Que entre o PRIM_NOME e o ULT_NOME foi realizada a concatenação com um espaço em branco ( ‘ ‘ ) se isso não fosse feito os nomes apareceriam colados, já que não existe espaço em branco nem depois de PRIM_NOME nem antes de ULT_NOME. O conteúdo das colunas PRIM_NOME E ULT_NOME foi concatenado e transformado em uma única coluna ( primeira coluna da tabela de resultado). O valor do salario de cada empregado foi multiplicado por doze e o resultado da operação exibido como uma coluna (segunda coluna). O nome das colunas na tabela de retorno é a própria expressão que foi escrita no comando SQL. Podemos ainda dar nomes alternativos às colunas resposta de um comando SELECT através de um ALIAS. No exemplo anterior notamos que a primeira coluna resposta do comando é PRIM_NOME|| ‘ ‘ ||ULT_NOME e a segunda coluna resposta é SALARIO*12. IMPLEMENTAÇÃO DE BANCO DE DADOS 114 Estes nomes podem não ser significativos. Podemos, então, manter o conteúdo da coluna, trocando apenas o seu nome na resposta. Para isso utilizamos um ALIAS. Devemos definir o ALIAS após a coluna especificada, com um espaço em branco. É importante sabermos que o ALIAS é sempre escrito com UPPER CASE e não pode conter espaços em branco. Caso seja necessário definirmos o alias com maiúsculas e minúsculas ou ainda com espaços em branco, devemos colocá-lo entre (" ") aspas. Vejamos o exemplo colocando o ALIAS NOME COMPLETO para concatenação e SALARIO ANUAL para a multiplicação. O comando seria : SELECT PRIM_NOME || ' ' || ULT_NOME AS "NOME COMPLETO " , SALARIO * 12 " SALARIO ANUAL" FROM EMPREGADO Teremos uma resposta, semelhante a figura. IMPLEMENTAÇÃO DE BANCO DE DADOS 115 Observe que: 1 - A clausula AS é opcional foi utilizada em NOME COMPLETO mas não em SALARIO ANUAL 2 - O ALIAS em entre “ “ ( aspas duplas) e não ‘ ‘ ( aspas simples) , esta última, no ORACLE, é utilizada para delimitar string ( veja o espaço em branco entre PRIM_NOME e ULT_NOME. IMPLEMENTAÇÃO DE BANCO DE DADOS 116 EXERCICIOS DE FIXAÇÃO 1 Faremos agora alguns exercícios de fixação A mecânica do exercício será a seguinte: • Será apresentado para você a solicitação da tarefa e uma imagem com os dados que devem retornar. • Escreva no ORACLE o comando que você acha que resolve o pedido e veja a se o retorno corresponde aos dados esperados • Apenas a realização do exercício compare o seu comando com o comando que consta no Garbarito. Prontos? Mão a obra. Modelo de Dados IMPLEMENTAÇÃO DE BANCO DE DADOS 117 Exercício 1 Mostrar Id, sobrenome, cargo e salario dos empregados ordenados pelo salario em ordem crescente ( ordenar utilizando o nome da coluna) SOLUÇÃO IMPLEMENTAÇÃO DE BANCO DE DADOS118 Exercício 2 Mostrar Id, sobrenome, cargo e salario dos empregados ordenados pelo cargo em ordem decrescente ( ordenar utilizando o nome da coluna) SOLUÇÃO IMPLEMENTAÇÃO DE BANCO DE DADOS 119 Exercício 3 Mostrar Id, sobrenome, cargo e salario dos empregados ordenados pelo cargo em ordem decrescente dentro do cargo por salario em ordem crescente ( ordenar utilizando o nome da coluna) SOLUÇÃO IMPLEMENTAÇÃO DE BANCO DE DADOS 120 Exercício 4 Mostrar Id, sobrenome, cargo e salario dos empregados ordenados pelo cargo em ordem decrescente dentro do cargo por salario em ordem crescente ( ordenar utilizando a posição da coluna) SOLUÇÃO IMPLEMENTAÇÃO DE BANCO DE DADOS 121 Exercício 5 Mostrar Id, sobrenome, cargo e salario dos empregados ordenados pelo cargo em ordem decrescente dentro do cargo por salario em ordem crescente ( ordenar utilizando a posição da coluna) para os empregados com id maior que 3 SOLUÇÃO IMPLEMENTAÇÃO DE BANCO DE DADOS 122 Exercício 6 Mostrar uma única vez os cargos dos empregados como o cabeçalho Cargos Diferentes SOLUÇÃO GERANDO RESTRIÇÕES ÀS CONSULTAS Ao selecionar os dados para visualização ou outra necessidade podemos, além de ordená- los, restringir o seu espectro de visualização utilizando a cláusula WHERE. A cláusula Where estabelece uma condição que a linha deverá obedecer para que faça parte do conjunto resposta da consulta. No caso apenas IMPLEMENTAÇÃO DE BANCO DE DADOS 123 retornam as linhas cujo deste da condição de como resposta verdadeiro. Veja o exemplo abaixo onde desejamos que retornem apenas a linhas dos empregados com ID > 3: SELECT * FROM EMPREGADO WHERE ID > 3 Observe na figura abaixo o retorno do comando. Repare que das seis linhas que a tabela possui, empregado com ID de 1 até 6 somente retornam as três que possuem ID maior que 3 ( 4,5 e 6). IMPLEMENTAÇÃO DE BANCO DE DADOS 124 Quando vamos trabalhar com Caracteres devemos ter alguns cuidados, o primeiro é que a palavra deverá vir entre aspas simples. Observe o seguinte exemplo: Desejamos mostrar nome e cargo do empregado com sobrenome UGARTE SELECT prim_nome,cargo FROM c_empr WHERE ult_nome = ' UGARTE' Repare que não foi selecionada linha alguma porque as COLUNAS tipo CHAR no ORACLE são "case sensitives" de forma que letras maiúsculas e minúsculas são diferentes. IMPLEMENTAÇÃO DE BANCO DE DADOS 125 dados não encontrados Para perceber a diferença execute o comando: SELECT prim_nome,cargo FROM c_empr WHERE ult_nome = ' Ugarte' A figura mostra o resultado do comando. Note que no comando Ugarte este com a primeira letra maiúscula e as outras minúsculas que é o padrão adotado em nosso banco de dados, por isso a consulta retornou uma linha enquanto na anterior como o nome estava todo em maiúsculas nenhuma linha retornou. IMPLEMENTAÇÃO DE BANCO DE DADOS 126 Ao trabalharmos com datas devemos coloca-las entre aspas simples, no formato dd/mm/aa, ou dd/mm/aaaa onde dd é o dia em dois dígitos, mm o mês em dois digitos e aa o ano em dois dígitos ou aaaa o ano em quatro digitos. Exemplo: Mostrar sobrenome e senha dos empregados admitidos em 7/4/2010 SELECT ULT_NOME, DT_ADMISSAO FROM EMPREGADO WHERE DT_ADMISSAO = '7/4/2010'; Repare que por p a d r ã o o ORACLE exibe o a n o e m d o i s d í g i t o s m a s p e r m i t e a c o n s u l t a c o m quatro ou dois. Teste também os s e g u i n t e s comandos: IMPLEMENTAÇÃO DE BANCO DE DADOS 127 SELECT ULT_NOME, DT_ADMISSAO FROM EMPREGADO WHERE DT_ADMISSAO = '7/ABR/2010' ou SELECT ULT_NOME, DT_ADMISSAO FROM EMPREGADO WHERE DT_ADMISSAO = '7/ABRIL/2010' O ORACLE também reconhece estes formatos para a Data, ou seja, o mês abreviado para três letras ou por extenso. OPERADORES LÓGICOS E RELACIONAIS Um conjunto de operadores são disponibilizados de forma que possamos montar a lógica dos nossos comandos SQL. Alguns destes operadores podem ser utilizados em quase todos os comandos SQL, não restringindo-se ao comando SELECT. Operadores Relacionais: = igual <> ou != diferente < menor que > maior que >= maior ou igual a <= menor ou igual a IMPLEMENTAÇÃO DE BANCO DE DADOS 128 OPERADORES LÓGICOS AND OR NOT Consultando Dados com várias condições Você pode especificar critérios complexos combinando várias condições de pesquisa. Os operadores AND e OR podem ser utilizados para formar expressões lógicas compostas. O operador AND retorna TRUE se ambas as condições forem verdadeiras, enquanto o operador OR retorna TRUE se uma das condições for verdadeira. Você pode ainda utilizar parêntesis para estabelecer a ordem de execução dos testes. Vejamos um exemplo vamos supor que desejamos os empregados que tenham sido contratados após primeiro de janeiro de 2009 e que tenham salario maior que 1000. O comando e o resultado estão na figura. IMPLEMENTAÇÃO DE BANCO DE DADOS 129 Repare que a utilização do AND obriga que as duas condições sejam verdadeira para que a linha retorne, se tivéssemos utilizado OR bastaria uma ser verdadeira para que a linha retorne. Aproveite e teste o comando com OR e veja a diferença nas linhas que retornam. SELECT * FROM EMPREGADO WHERE DT_ADMISSAO > ‘1/1/2009’ OR SALARIO > 1000 IMPLEMENTAÇÃO DE BANCO DE DADOS 130 Antes de continuar faça os exercícios de fixação, em seguida confira suas respostas com o gabarito. EXERCICIOS DE FIXAÇÃO 2 Faremos agora alguns exercícios de fixação. A mecânica do exercício será a seguinte: ✴ Será apresentado para você a solicitação da tarefa e uma imagem com os dados que devem retornar. ✴ Escreva no ORACLE o comando que você acha que resolve o pedido e veja a se o retorno corresponde aos dados esperados ✴ Apenas ao final da realização do exercício compare o seu comando com o comando que consta no Gabarito. Prontos? IMPLEMENTAÇÃO DE BANCO DE DADOS 131 Exercício 1 Mostrar primeiro nome o sobrenome dos empregados lotados no departamento cuja identificação é 20 SOLUÇÃO SELECT PRIM_NOME, ULT_NOME FROM EMPREGADO WHERE ID_DEPTO = 20 IMPLEMENTAÇÃO DE BANCO DE DADOS
Compartilhar