Banco de Dados II - Curso técnico em informática

Curso Técnico em Informática

Banco de Dados II

Robson Braga de Andrade

Presidente da Confederação Nacional da Indústria

Rafael Lucchesi

Diretor do Departamento Nacional do SENAI

Regina Maria de Fátima Torres

Diretora de Operações do Departamento Nacional do SENAI

Alcantaro Corrêa

Presidente da Federação da Indústria do Estado de Santa Catarina

Sérgio Roberto Arruda

Diretor Regional do SENAI/SC

Antônio José Carradore

Diretor de Educação e Tecnologia do SENAI/SC

Marco Antônio Dociatti

Confederação Nacional da Indústria

Serviço Nacional de Aprendizagem Industrial

Curso Técnico em Informática

Banco de Dados II

Silvio Luis de Sousa

Florianópolis/SC

2011

É proibida a reprodução total ou parcial deste material por qualquer meio ou sistema sem o prévio consentimento do editor.

Autor

Silvio Luis de Sousa

Fotografias

Banco de Imagens SENAI/SC http://www.sxc.hu/

http://office.microsoft.com/en-us/ images/ http://www.morguefile.com/

http://www.bancodemidia.cni.org.br/

Ficha catalográfica elaborada por Luciana Effting CRB14/937 - Biblioteca do SENAI/SC Florianópolis

S725b

Sousa, Silvio Luis de

Banco de dados II / Silvio Luis de Sousa. – Florianópolis : SENAI/SC, 2011. 59 p. : il. color ; 28 cm.

Inclui bibliografias.

1. Banco de dados. 2. Banco de dados relacionais. 3. Sistemas de recuperação da informação – Segurança. 4. SQL (Linguagem de

programação de computadores). I. SENAI. Departamento Regional de Santa Catarina. II. Título.

CDU 004.65

SENAI/SC — Serviço Nacional de Aprendizagem Industrial

Rodovia Admar Gonzaga, 2.765 – Itacorubi – Florianópolis/SC CEP: 88034-001

Fone: (48) 0800 48 12 12 www.sc.senai.br

Prefácio

Você faz parte da maior instituição de educação profissional do estado. Uma rede de Educação e Tecnologia, formada por 35 unidades conecta-das e estrategicamente instalaconecta-das em toconecta-das as regiões de Santa Catarina. No SENAI, o conhecimento a mais é realidade. A proximidade com as necessidades da indústria, a infraestrutura de primeira linha e as aulas teóricas, e realmente práticas, são a essência de um modelo de Educação por Competências que possibilita ao aluno adquirir conhecimentos, de-senvolver habilidade e garantir seu espaço no mercado de trabalho. Com acesso livre a uma eficiente estrutura laboratorial, com o que existe de mais moderno no mundo da tecnologia, você está construindo o seu futuro profissional em uma instituição que, desde 1954, se preocupa em oferecer um modelo de educação atual e de qualidade.

Estruturado com o objetivo de atualizar constantemente os métodos de ensino-aprendizagem da instituição, o Programa Educação em Movi-mento promove a discussão, a revisão e o aprimoraMovi-mento dos processos

de educação do SENAI. Buscando manter o alinhamento com as neces-sidades do mercado, ampliar as possibilidades do processo educacional, oferecer recursos didáticos de excelência e consolidar o modelo de Edu-cação por Competências, em todos os seus cursos.

É nesse contexto que este livro foi produzido e chega às suas mãos. Todos os materiais didáticos do SENAI Santa Catarina são produções colaborativas dos professores mais qualificados e experientes, e contam com ambiente virtual, mini-aulas e apresentações, muitas com anima-ções, tornando a aula mais interativa e atraente.

Mais de 1,6 milhões de alunos já escolheram o SENAI. Você faz parte deste universo. Seja bem-vindo e aproveite por completo a Indústria do Conhecimento.

Sumário

Conteúdo Formativo 9

Apresentação 11

12 Unidade de estudo 1

Índice: Herói ou

Vilão?

Seção 1 - Conhecendo sobre

índice

Seção 2 - Trabalhando e

re-conhecendo tipos de índice

24 Unidade de estudo 2

Avançando em SQL

Seção 1 - Abordagem inicial Seção 2 - Cálculo em SQL Seção 3 - Funções de

agre-gação sobre conjuntos

Seção 4 - Mais recursos SQL

13 16

38 Unidade de estudo 3

Views

Seção 1 - Abordagem inicial Seção 2 - Manipulando views

42 Unidade de estudo 4

Trigger

Seção 1 - Abordagem inicial Seção 2 - Manipulando

Trigge

48 Unidade de estudo 5

Segurança em

Ban-co de Dados

Seção 1 - Abordagem inicial Seção 2 - Abrangência da

se-gurança em banco de dados

Seção 3 - Integridade de

dados

Seção 4 - Controle de acesso

a banco de dados 25 25 26 28 43 44 39 39

Finalizando 55

Referências 57

49 49 49 52

Conteúdo Formativo

Carga horária da dedicação

Carga horária: 60 horas

Competências

Criar e gerenciar sistemas de banco de dados para solução de problemas

Conhecimentos

▪ Índices, ▪ Integridade de dados, ▪ Junções (join), ▪ União (union), ▪ Visões (views), ▪ Triggers (gatilhos), ▪ Redundância.

Habilidades

▪ Aplicar álgebra de conjuntos.

▪ Interpretar e analisar modelos de dados.

▪ Utilizar a linguagem SQL para manipulação de dados. ▪ Identificar defeitos e falhas em banco de dados.

▪ Aplicar normas de segurança de dados (integridade de dados, backup e restore).

Atitudes

▪ Organização e zelo na utilização de equipamentos. ▪ Foco no conteúdo trabalhado.

▪ Acesso a sítios relacionados ao tema trabalhado. ▪ Organização e limpeza dos ambientes coletivos.

▪ Dedicação e empenho nas atividades curriculares e extracurriculares. ▪ Capacidade de abstração.

▪ Trabalho em equipe.

Apresentação

Que tal iniciar os estudos da unidade curricular Banco de Dados II aprendendo a desenvolver competências voltadas ao desenvolvimento de atividades relacionadas à sua profissão?

Bem, você deve estar se perguntando: “Que competências são essas?”. São as competências de criar e gerenciar sistemas de banco de dados para a solução de problemas computacionais.

Durante seus estudos, este material poderá contribuir para o desenvolvi-mento de habilidades voltadas para a elaboração de projetos de banco de dados, utilizando técnicas eficientes, o que contribui, substancialmente, para a elaboração e o desenvolvimento de projetos de software.

Esta unidade curricular contempla, ainda, a utilização correta dos recur-sos de informática e de ferramentas garantindo a qualidade para solução de problemas. Esses são os requisitos de empresas que atuam em diver-sos segmentos do mercado de trabalho.

Que você tenha, a partir de agora, um estudo e um ótimo aproveitamen-to deste material!

Silvio Luis de Sousa

Graduado em Processamento de Dados e Sistemas de Infor-mação. Pós-graduado em For-mação Docente para o Ensino Superior. Sócio proprietário da LM&SL Treinamento, Desenvol-vimento e Consultoria em Infor-mática, com atuação em Joinvil-le e região. Atua no SENAI em Joinville como instrutor de cur-sos de qualificação e técnicos, com experiência profissional em treinamento e desenvolvimento de projeto de software.

Unidade de

estudo 1

Seções de estudo

Seção 1 – Conhecendo sobre índice Seção 2 – Trabalhando e reconhecendo

Índice: Herói ou Vilão?

Seção 1

Conhecendo sobre índice

Muito se fala sobre índice em banco de dados. Alguns defendem, de ma-neira muito entusiasta, seu uso como estrutura indispensável e obrigató-ria quando da cobrigató-riação das tabelas do seu banco de dados, outros, apesar de não serem contrários à ideia, sugerem cautela na sua utilização e, para encerrar, existe uma corrente que nem quer ouvir falar sobre índice, devido a experiências malsucedidas. Diante disso, você está convidado a conhecer sobre índice, para que possa formar a sua opinião.

Para iniciar a compreensão sobre índice em sua principal utilidade, con-fira um exemplo como referência, adaptado de uma página da internet. Suponha uma tabela de clientes, tendo como campo chave primária o

campo Código do cliente, com a seguinte estrutura física:

Figura 1: Representação da tabela Clientes populada Fonte: Aquino (2010)

Imagine que nessa estrutura não exista nenhum campo (simples) cria-do como índice e que você queira fazer uma consulta dos dados do cliente a partir do seu nome, por exemplo, do cliente Silvio Luis:

SELECT Codigo, Nome, Rua, Numero FROM clientes WHERE Nome = “Silvio Luis”

Campo simples: campo que não seja chave na ta-bela, seja ela primária,

estran-geira, única ou full-text, ou seja,

trata-se de um campo de dados simples ou não chave.

Índices: recurso utilizado para encontrar registros ra-pidamente em tabelas de banco de dados.

Instrução SQL:

Figura 2: Consulta da tabela Clientes pelo campo simples Nome

Agora, supondo que a tabela clientes seja modificada com, por exemplo, a criação de um índice para o campo Nome e, após, você queira

nova-mente fazer a consulta dos dados do cliente Silvio Luis. Confira: Instrução SQL

SELECT Codigo, Nome, Rua, Numero FROM clientes USE INDEX (index_ Nome) WHERE Nome = “Silvio Luis”

Figura 3: Consulta da tabela Clientes pelo campo índice Nome

A sintaxe SQL de criação e a de consultas que envolvem índices será explica-da em detalhes na próxima seção desta uniexplica-dade – Trabalhando e reconhe-cendo tipos de índice.

Fácil de analisar o que ocorreu nas duas situações, não é? Na primeira simulação, foi realizada a consulta pelo campo Nome, que na ocasião tratava-se de um campo simples. Como resultado você observou que foi realizada uma varredura na tabela o que chamamos de Table Scan até

encontrar o registro desejado. Isso é bom ou ruim? Bem, em tabelas com poucos registros não é muito relevante, mas, ao contrário, trata-se de queda de performance em consultas a banco de dados.

Na segunda simulação também foi realizada a consulta pelo campo Nome, porém, nesta ocasião, como índice da tabela, e não mais como um campo simples. No resultado você observou a identificação do valor do campo de maneira mais rápida em relação à primeira simulação, sem a tal varredura, o que nos isenta da tal perda de performance na consulta

ao banco de dados, logicamente, dependendo da quantidade de registros na tabela.

Assim, ficou clara a ideia principal do índice em um banco de dados, que se trata de uma estrutura im-portante no que diz respeito à oti-mização das consultas realizadas. Um índice é uma estrutura as-sociada a uma tabela que torna a pesquisa mais rápida. Deve--se levar em conta o emprego de índices em qualquer coluna frequentemente utilizada na pesquisa de uma determinada tabela, pois, com esta opção, a duração das pesquisas será con-sideravelmente diminuída (MA-CHADO, 2008, p. 381).

Os índices podem ser criados para um único campo, como no exemplo anterior, ou para múlti-plos campos para, por exemplo, auxiliar na distinção dos registros quando um determinado campo possui o mesmo valor. Também podem ser atribuídos aos campos chaves primárias e chaves estran-geiras nas tabelas, aí temos que

alguns SGBD determinam, automa-ticamente, como índices as chaves primárias (PK) e chaves estrangei-ras (FK) de suas tabelas e outros não, portanto é importante verifi-car isso quando da criação da sua estrutura de tabelas.

Agora que você conhece um pou-co mais sobre índice em banpou-co de dados, é importante também saber que a escolha pela criação de índices, nas respectivas tabelas, deve ser executada com critério e sem exageros, pois, como todo re-curso de otimização existente, os índices ocupam espaços em disco e seu uso exagerado pode dimi-nuir o rendimento nas transações de inclusão, exclusão e alteração dos registros nas tabelas do banco de dados.

Quanto à criação de índices, Ma-chado (2008, p. 381) alerta: “[...] tecnicamente não há limi-te ao número de índices, mas quanto maior o número deles mais difícil a sua manutenção”. A determinação para utilização de índices deve obedecer a alguns critérios. Veja:

▪

faça uma análise individual por tabela para verificar, com consci-ência, se existem ou não campos que devem ser índices. Tabelas que armazenam poucos registros ou que apenas servem como auxílio a outras tabelas normalmente não requerem campos indexados;

▪

analise as cláusulas where e join que irá utilizar. Os campos envolvi-dos são candidatos a índices;

▪

cuidado com o número exces-sivo de campos indexados em tabelas simples;

▪

cuidado também ao criar os índices. Evite criar como índices os campos que já estejam definidos como tal, como o caso dos cam-pos chave primária (PK) e camcam-pos chave estrangeira (FK), pois isso caracteriza sobreposição de índice.

Esteja atento: uma tabela deve pos-suir um campo como índice, pois, do contrário, os registros serão ar-mazenados aleatoriamente e, no caso de qualquer acesso a esta, o processo torna-se bastante lento.

Cada SGBD possui característi-cas própria apesar de alguns se-rem bastante similares em muitas funcionalidades. Cada banco de dados apresenta sua uma forma particular com seus tipos de ín-dices suportados e como utilizá-los respectivamente, fica aqui a suges-tão de que consulte a documenta-ção deste quando da manipuladocumenta-ção de índices.

SGBD: Sistema Gerencia-dor de Banco de Dados.

Seção 2

Trabalhando e

reconhecendo tipos de índice

Antes da abordagem deste assunto, vale salientar que a criação de índices pode ser executada para cada banco de dados disponível. Reservadas as funcionalidades destes, essa tarefa pode ser executada pelos programas disponibilizados ou também de forma manual via instrução SQL. A sin-taxe para manipulação dos índices pode variar de um banco de dados para outro. No nosso caso, mostraremos exemplos para criação de banco de dados a partir de instrução SQL com base no banco de dados MySQL versão 5.2 embarcada no utilitário phpMyMAdmin 2.11.7. Crie um banco de dados para os trabalhos.

Criação da tabela clientes:

CREATE TABLE clientes ( codigo INT(4) NOT NULL AUTO_INCREMENT, nome VARCHAR(50) NOT NULL , rua VARCHAR(40) NOT NULL , numero INT(4) NOT NULL , PRIMARY KEY (codigo) ) ENGINE = InnoDB

Confira a seguir a representação da estrutura da tabela clientes resultante da instrução anterior.

Figura 4: Estrutura de índices tabela clientes pelo campo índice Nome

Como você sabe, em um banco de dados é possível contar com um ou mais campos cujos seus valores determinam uma unicidade para os registros de uma tabela fazendo com que estes não se repitam – são as chamadas chaves primárias. Estas são identificadas e tratadas automa-ticamente como índices únicos do tipo PRIMARY, permitindo, assim,

um acesso rápido aos dados declarados com esse índice, nesse caso, ao código do cliente.

SELECT Codigo, Nome, Rua, Numero FROM clientes WHERE Codigo =’3’ Resultado:

Figura 5: Resultado da consulta do cliente pelo código

Observe na figura o tempo decorrido da consulta. É claro que, em nosso exemplo, contamos com a tabela populada com poucos registros, mas é importante saber que todas as consultas são executadas a partir do cam-po código, que, cam-por sua vez, é um camcam-po índice.

Agora veja uma consulta pelo nome do cliente Silvio Luis. Neste caso é interessante que se crie um índice para o campo Nome, pois será neces-sário utilizar a cláusula where, e, tendo este campo como índice, a

consul-ta tende a ser mais eficiente e rápida.

Criando o índice para o campo Nome da tabela clientes: CREATE INDEX index_nome ON clientes (nome(50))

Resultado:

Figura 6: Resultado da criação do índice contendo o campo Nome

Note que a partir de agora a tabela clientes contém mais um índice, além do campo código (chave primária). Foi criado um novo índice contendo o campo Nome para acelerar as consultas dos clientes por meio do seu

Nome. Esse índice é tido como um índice simples, também conhecido como índice normal. Esse índice não contém nenhum tipo de restrição como, por exemplo, o índice primário. Ele é criado quando há uma ne-cessidade sistemática de se fazer uma consulta a um determinado campo da tabela.

Você pode criar quantos índices normais forem necessários, mas fique atento para a real necessidade da sua criação, pois, do contrário, você pode provocar uma queda de performance no banco.

Para consultar o cliente Silvio Luis a partir do índice criado, temos: SELECT Codigo, Nome, Rua, Numero FROM clientes USE INDEX (index_ nome) WHERE Nome = ‘Silvio Luis’

Resultado:

Figura 7: Resultado da consulta do cliente pelo índice Nome

Observe, também nesse exemplo, o tempo decorrido da consulta, ressal-tando que, diante da consulta do exemplo anterior, ou seja, consulta pelo código (índice primário), esta ocorreu em um tempo maior. Isso porque executar uma consulta a partir de um campo numérico normalmente é mais rápido do que uma consulta por um campo caracter, como o caso

do campo Nome.

Lembre-se de que, na medida em que a tabela contém um número sig-nificativo de registros, mais evidente ficará a eficácia da consulta a partir de campos índices.

Caso essa mesma consulta seja realizada a partir de um banco de dados com muitos registros, a partir de um campo da tabela e não tendo criado esse campo como índice, a tendência é que a consulta seja mais demo-rada, pois, para encontrar o registro desejado, será executada uma var-redura (Table Scan) de todos os registros até encontrar aquele solicitado,

conforme mencionado.

Agora você já sabe como criar e utilizar índices simples, porém você pode se deparar com uma situação em que exista a necessidade de criar e utilizar índices compostos. Para não ter dúvidas, acompanhe as informa-ções a seguir, e lembre-se: sempre que precisar, pergunte ao professor. Suponha que você necessite obter os dados do cliente Fábio Karnopp que mora na Rua B. Note que nesse caso será preciso resgatar informa-ções de dois campos, e não mais de um campo apenas. Tenho a certeza de que sua reação seria elaborar a seguinte instrução SQL:

SELECT Codigo,Nome,Rua,Numero FROM clientes WHERE Nome=’Fabio Karnopp’ AND Rua =’Rua B’

Com certeza o resultado seria sa-tisfatório. Você teria uma tabela com todos os dados do cliente solicitado. Tudo certo não fosse um único porém: se considerar-mos o que foi trabalhado até ago-ra, a esta altura só temos o campo Nome criado como índice, e isso não é o suficiente, precisamos de um índice com 2 campos – Nome e Rua. O que podemos fazer? As alternativas são: customizar o ín-dice do Nome e incluir o campo Rua ou excluí-lo e criar um novo índice com os campos Nomes e Rua.

Levando-se em conta que o ín-dice existente possui o nome de index_nome, caracterizando sua utilização para consultas pelo nome do cliente, e também o fato de que criando um novo índice composto com Nome e Rua não o impediria de realizar tais consul-tas, e que, como estamos conhe-cendo sobre índices, com certeza vale a pena excluir o índice in-dex_nome e criar um novo índice, só que composto e com um novo nome. Vamos trabalhar um pouco mais com índices?

▪

Excluindo o campo índice in-dex_nome:

ALTER TABLE clientes DROP IN-DEX index_nome

Ao executar essa instrução, uma confirmação dessa operação será solicitada:

Figura 8: Confirmação da exclusão do índice index_nome

Como o alerta sugere, caso queira cancelar a operação, clique no botão Cancelar, e caso queira confirmar a exclusão, clique no botão OK.Ao clicar em OK, o índice é excluído, e você pode seguir para a próxima etapa. Criando o índice composto contendo os campos Nome e Rua:

Criando o índice composto contendo os campos

Nome e Rua:

ALTER TABLE clientes ADD INDEX index_Nome_Rua (nome ,rua )

Note que novamente utilizamos o comando ALTER TABLE, pois a tabela clientes está sendo modificada com a implementação de um novo índice, e, como tal, deve utilizar também o comando ADD para identifi-car o índice a ser implementado e os campos que compõem esse índice, no caso nome e rua.

Como resultado você terá:

Figura 9: Resultado da criação do índice contendo os campos Nome e Rua

Observe que agora tem um índice chamado index_Nome_Rua com-posto por mais de um campo (nome e rua), por isso o nome do índice criado, mas isso não é o fato marcante desse tipo de índice. Durante esse processo você entenderá a sua característica principal.

Como estamos nos baseando no MySQL, em se tratando de índi-ces compostos, este, ao realizar uma consulta, faz uso de um úni-co índice. Se tiver de optar por mais de um índice, opta pelo mais restritivo, ou seja, aquele que torna a menor quantidade de re-gistros.

Analisando nosso exemplo, te-mos que, quando executarte-mos a consulta, o índice será utilizado da seguinte forma:

Figura 10: Leitura de um índice com-posto

A leitura de um índice composto é realizada da esquerda para a direi-ta até chegar naquele, que retorna a menor quantidade de registros. Criado o índice composto, é então possível realizar a consulta dos dados do cliente Fábio Karnopp, que mora na Rua B, a partir da se-guinte instrução SQL:

SELECT Codigo, Nome, Rua, Numero FROM clientes WHERE Nome = ‘Fábio Karnopp’ AND Rua = ‘Rua B’

Figura 11: Resultado da consulta dos dados do cliente, pelo índice Nome, Rua

A partir desse índice você pode também realizar a seguinte consulta: SELECT Codigo, Nome, Rua, Numero FROM clientes WHERE Nome = ‘Silvio Luis’

Isso porque o campo Nome faz parte do índice e trata-se do campo do índice que retorna a menor quantidade de registros.

Já na instrução SQL a seguir, o índice criado não é utilizado, pois o cam-po Nome não está presente na condição da sentença. Apesar disso você obtém o resultado dos dados do cliente que residem na Rua R, normal-mente, mas sem eficiência e rapidez de uma consulta utilizando o índice.

SELECT Codigo, Nome, Rua, Numero from clientes where Rua = ‘Rua B’

Suponha agora que deseje que, em sua tabela de clientes, não sejam sal-vos clientes com nomes duplicados. Apesar de isso não ser tecnicamente interessante, tome como exemplo para que se entenda a explicação da criação e utilização de índices únicos.

Lembre-se de que o campo Nome não faz parte de chave primária, isso significa que até o presente momento você pode cadastrar clientes com nomes iguais à vontade, mas não é isso que a proposta exemplo sugere, então a primeira providência é a de criar um índice único com o campo Nome:

ALTER TABLE clientes ADD UNIQUE unico_nome (nome)

Como resultado da criação desse índice você terá:

Observe que foi criado um índice único para o campo Nome, sendo que esse campo também faz parte de outro índice, o index_Nome_ Rua. Isso é possível porque os tipos de índices são diferentes e são cria-dos para propósitos diferentes.

Agora, teste a utilização desse ín-dice inserindo um registro na ta-bela com o nome de um cliente já cadastrado, como, por exemplo, Fábio Karnopp.

Instrução SQL para inclusão do cliente:

INSERT INTO clientes (codigo ,nome ,rua ,numero )VALUES (NULL ,’Fábio Karnopp’, ‘Rua C’, ‘111’)

Resultado:

Figura 13: Resultado da utilização da chave única unico_nome

Note que a mensagem chama a sua atenção informando que você está tentando inserir um registro duplicado para o cliente Fábio Karnopp, cujo código é 2.

Nesse exemplo foi criado um índice único contendo um único campo, então temos um índice único simples.

Agora, suponha que você concluiu que pessoas possam ter nomes iguais, os chamados homônimos – apenas para exemplificar, meu pai, o Sr. José Manoel de Sousa, tem aqui na cidade de Joinville quatro homônimos, ou seja, pessoas com o mesmo nome, porém com número de CPF, RG e endereço diferentes.

Você deve estar se perguntando, mas o que isso tem a ver com nosso exemplo? Tudo, não acha? Como fará para diferenciar um cliente de outro? Claro que nosso exemplo é um tanto limitado, mas, o que acha de incrementarmos nosso índice com o campo Rua? Certo, ainda existe a possibilidade de termos clientes homônimos morando na mesma rua, então o que acha de incrementarmos com o campo Rua e Número? Continue preparando o seu banco de dados para que aceite clientes com nomes iguais, porém residindo em endereços diferentes. Para isso, você deve adicionar mais um campo ao índice único já existente. Mas como fazer isso, já que o índice está com um nome que sugere seu conteúdo com o campo Nome e como incrementar esse índice com o campo Rua? Da seguinte maneira:

ALTER TABLE clientes DROP INDEX unico_nome , ADD UNIQUE unico_ nome_rua (nome , rua )

Ao executar essa instrução, uma confirmação dessa operação será soli-citada:

Figura 14: Confirmação de alteração do índice unico_nome

Para incrementar o índice existente único_nome com o campo Rua, mas deve-se eliminar o índice existente (DROP INDEX único_nome) e em seguida proceder a adição do novo índice com os respectivos campos (ADD UNIQUE único_nome_rua (nome,rua).

Ao confirmar a operação, o novo índice será criado, porém uma situação lhe será alertada:

Figura 15: Resultado da criação do índice único contendo os campos Nome e Rua

Como o alerta esclarece, existem índices idênticos e, apesar de serem de tipos diferentes, possuem os mesmos campos, por isso o banco de dados sugere que elimine um deles para que o espaço no banco de dados não seja utilizado com recursos em duplicidade.

Mesmo tendo essa duplicidade, os dois índices continuam servindo aos seus propósitos, porém não se aconselha a mantê-los em duplicidade.

Você deverá optar pela eliminação de um dos índices: ou o índice com-posto tipo INDEX, que permite a consulta dos clientes e residência

sem a preocupação de registros duplicados, ou o índice também com-posto do tipo UNIQUE, que permite a realização da consulta a

exem-plo do seu concorrente, mas com o adicional de não permitir registros duplicados com as informações dos campos desse índice.

A diferença entre esses índices encontra-se apenas na permissão ou não de inserir registros duplicados para os campos do índice, ou seja, é pos-sível consultar os dados dos clientes a partir do nome e da rua sem mais problemas. Assim, a melhor opção é excluir o índice do tipo INDEX. Se estiver em dúvidas de como fazer a exclusão, reveja o exemplo onde o índice index_nome foi excluído anteriormente, e lembre-se também de consultar o professor sempre que achar necessário. Feito isso, insira um registro para o cliente, Fábio Karnopp em uma rua diferente daquela do Fábio Karnopp já cadastrado (Rua A e/ou Rua C).

Perceba que o seu banco de dados permite essa operação porque, apesar de já existir um cliente com esse nome, a rua é diferente daquela já ca-dastrada para esse cliente, ou seja,agora, para que o cliente seja impedido

de ser cadastrado, tanto o valor do campo nome do cliente como o da rua devem ser iguais aqueles já registrados para o cliente.

Que tal testar isso agora? Insira um registro para o cliente Fábio Karnopp na Rua B. Provavelmen-te você receberá como retorno uma mensagem de que o cliente já se encontra cadastrado.

Você conheceu até aqui os índices do tipo primário, simples, com-postos e únicos. Agora conhece-rá o índice do tipo Full-text, ou texto completo. Trata-se de um índice para realizar consultas a campos de texto de uma tabela. No MySQL esses tipos de índice são utilizados em tabelas do tipo MyISAM e para os campos do tipo CHAR, VARCHAR e TEXT. Para trabalhar esse conteúdo, se-guiremos com o exemplo da tabe-la de clientes e, por ser tratar de um tipo especial de índice, criare-mos uma nova tabela para estudá--lo de forma individualizada. Para começar, você pode criar a estrutura da tabela no mesmo banco de dados onde se encontra a tabela clientes, para uma melhor organização. Dê a essa tabela o nome de artigos, como se você fosse armazenar informações so-bre artigos em um site para web,

por exemplo.

CREATE TABLE artigos (

id int(10) unsigned NOT NULL auto_increment, titulo var-char(200) default NULL, texto text, PRIMARY KEY (id), FULL-TEXT KEY titulo (titulo,texto) ) ENGINE=MyISAM

Resultado:

Figura 16: Estrutura da tabela e de índices de artigos

Note que esse índice também é composto e, neste caso, pelos campos título e texto, será preciso popular a tabela para em seguida aprender a executar consultas FULL-TEXT.

Para popular a tabela artigos, utilize o código a seguir.

INSERT INTO artigos (id, titulo, texto) VALUES (NULL, ‘Banco de Dados II’, ‘Uma visão prática’),(NULL, ‘Como trabalhar com índices’, ‘Conheça tudo sobre índices’),(NULL, ‘Otimizando suas consultas’, ‘Otimize suas consultas utilizando....’),(NULL, ‘Dicas para criar índices’, ‘1. Analise as cláusulas where e join 2. ...’),(NULL, ‘Criando índices full-text’, ‘Como trabalhar ...’)

Como resultado da tabela artigos populada, tem-se:

Figura 17: Tabela artigos populada

Agora a consulta FULL-TEXT pode iniciar, então vamos direto ao as-sunto. Vamos supor que você queira capturar o texto referente ao id 3: “Otimize suas consultas utilizando...”

SELECT * FROM artigos WHERE MATCH (titulo, texto) AGAINST (‘Otimize’)

Entendendo a instrução SQL: observe que a função MATCH (título, texto) é utilizada para repassar parâmetros como título e texto. Isso faz com que a busca de uma string seja realizada a partir dos campos do

Tal valor do argumento é procu-rado nos campos do índice título e texto e, quando encontrado, de-volve o resultado. Para se certifi-car disso, escreva e teste a seguin-te instrução SQL:

SELECT * FROM artigos WHERE MATCH (titulo, texto) AGAINST (‘Otimizando’)

Para finalizar, escreva a próxima instrução SQL e teste também para ver o resultado:

SELECT * FROM artigos WHERE MATCH (titulo, texto) AGAINST (‘Trabalhar’)

O resultado traz os registros cor-respondentes aos id 2 e 5, que são

os registros que contêm a palavra

Trabalhar. Perceba que nos dois

registros as palavras encontram--se salvas em minúsculo, porém a pesquisa foi realizada com caixa--alta e caixa-baixa, o que quer dizer que esse tipo de busca não faz diferenciação entre letras mai-úsculas e minmai-úsculas, ou seja, não é case sensitive.

Saiba mais

Na próxima unidade, avançare-mos em SQL, por isso é impor-tante que você não fique com dúvidas sobre o que já estudou até aqui. Para mais informa-ções e/ou detalhes sobre o uso de índices, lembre-se de con-sultar o manual e/ou tutorial do SGBD que você está traba-lhando (neste caso, o MySQL).

Unidade de

estudo 2

Seções de estudo

Seção 1 – Abordagem inicial

Seção 2 – Cálculo em SQL

Seção 3 – Funções de agregação sobre

conjuntos

Avançando em SQL

Seção 1

Abordagem inicial

Você já conhece o conceito, a es-trutura e a importância da SQL para o ambiente de banco de da-dos relacional. Também conhece as transações que podem ser exe-cutadas a partir dessa linguagem e sabe que serve de base para os SGBDs encontrados no mercado. Sabe que, apesar da universalida-de, a linguagem possui adaptações dos seus recursos de acordo com o SGBD utilizado, e isso sugere que, ao trabalhar com a lingua-gem, é preciso ficar atento à do-cumentação do banco de dados para que ele possa ser utilizado com eficiência.

Criar banco de dados e tabelas ou excluí-las não deve ser mais novi-dade para você, assim como in-cluir, alterar e pesquisar registros em uma tabela.

Além desses conhecimentos es-pecíficos em SQL, você já deve também dominar a elaboração de projetos de banco de dados a partir dos conhecimentos de mo-delagem de dados, não é mesmo? Então, você já deve ter percebido o quanto é complexa a estrutura de um banco de dados bem mo-delada. São tabelas criadas com seus respectivos campos, que por sua vez são de um determinado tipo e tamanho, podendo estes ser chave primária ou estrangeira, ou simplesmente campos simples.

Ainda sobre projeto de banco de dados, você deve ter percebi-do também que, de acorpercebi-do com a necessidade, tais tabelas devem estar relacionadas segundo regras preestabelecidas, a fim de o banco de dados garantir que seu usuá-rio obtenha o máximo de infor-mações que garantam boas ações para o seu negócio, de forma efi-ciente e rápida.

Diante de toda essa complexide que o projeto complexide banco complexide da-dos nos remete, existem situações que exige um conhecimento mais aprofundado em SQL para garan-tir bons resultados a pargaran-tir da es-trutura projetada, situações como realizar cálculos com informações selecionadas, utilizar funções de agregação sobre conjuntos, agru-par informações selecionadas, acessar dados de várias tabelas e utilizar consultas avanças são im-portantes.

É isso que esta unidade se pro-põe: repassar alguns dos conhe-cimentos avançados em SQL necessários, para que você possa implementar com eficiência seu projeto de banco de dados.

Nota

Serão abordados nesta unida-de recursos da SQL baseados no SGBD MySQL, que é uma linguagem universal, e tudo o que é apresentado existe na maioria dos bancos de dados.

Seção 2

Cálculo em SQL

Apenas para relembrar, campo calculado é um campo que não pertence fisicamente a uma tabela (não é um campo de dado), mas armazena o resultado do cálculo proveniente de alguns campos (campos de dados) destas, objeti-vando a exibição de seu conteúdo quando da apresentação do resul-tado de uma consulta.

Situação: Mostrar o novo valor

dos produtos de um minimerca-do, identificados com tarja “ver-de”, com desconto de 10%. Or-denar pelo nome do produto.

Figura 18: Diagrama de entidade e relacionamentos produtos

Após criar e popular a tabela

produtos, tem-se:

Figura 19: Tabela produtos populada

Calcular o desconto de 10% para os produtos com tarja verde:

Função AVG ( )

Situação: Qual será a média

dos preços dos produtos?

SELECT AVG (valor_venda) AS Média_Valores_Produtos from produtos

Resultado:

Figura 22: Resultado da média dos valo-res dos produtos

A função executa a média aritmé-tica simples de um conjunto de valores, no caso do exemplo, dos preços dos produtos.

Função Count ( )

Situação 1: Quantos registros

existem na tabela produtos?

SELECT count (*) AS Total_Re-gistros FROM produtos

A informação Total_Registros

na instrução captura a

quanti-dade total de registros

existen-tes na tabela produtos.

Resul-tado:

Figura 23: Resultado quantidade de registros na tabela produtos Situação 2: Quantos produtos

possuem o valor de venda supe-rior a R$ 5,00?

SELECT Nome, (valor_venda – ( (valor_venda * 1.10) – valor_ venda )) AS Valor FROM pro-dutos WHERE tarja = ‘Verde’ ORDER BY nome

Note que temos ORDER BY nome, indicando que se deseja que o resultado da consulta seja orde-nado pelo nome do produto em ordem ascendente. Sempre que a ordenação é omitida, assume-se a ordem ASC (ascendente). Caso se prefira a ordenação descendente, insere-se a informação ORDER BY nome DESC.

Resultado:

Figura 20: Resultado da consulta

Seção 3

Funções de agregação

sobre conjuntos

É um recurso bastante parecido com o trabalhado na seção an-terior. As funções de agregação existem para gerar novas colunas.

Para aplicar as funções de agrega-ção, utilizaremos a tabela produ-tos, já trabalhada anteriormente. Confira as funções a seguir.

Funções Max ( ) e

Min ( )

Situação: Mostrar o menor e o

maior valor dos produtos da tabe-la produtos.

SELECT MIN ( valor_venda ) AS Valor_Mínimo, MAX( va-lor_venda ) AS Valor_Máximo FROM produtos

Resultado:

Figura 21: Resultado menor e maior do valor dos produtos

Figura 25: Diagrama de entidade e relacionamentos orcamento-pecas

Após criar e popular a tabela orcamento_pecas, tem-se:

Figura 26: Tabela orçamento_pecas populada

Situação: Mostrar a somatória da quantidade de peças orçadas para a

peça cujo código de identificação é 8901.

SELECT SUM(qtdepeca ) AS Somatória FROM orcamento_peca WHERE peca_idpeca =8901

Resultado:

Figura 27: Resultado da somatória da quantidade orçada para a peça 8901

SELECT count ( * ) AS Total_Re-gistros FROM produtos WHERE valor_venda > 5.00

Neste caso, a resposta é a quanti-dade total de registros com valor de venda superior a R$ 5,00 na tabela produtos. Perceba também a presença do operador relacional “>” (maior). Esses operadores podem ser utilizados em instru-ções SQL.

Resultado:

Figura 24: Resultado da quantidade de registros com valor do produto acima de R$5,00

Função SUM()

Para exemplificar o uso da função SUM ( ), crie e popule a tabela or-camento_pecas, conforme diagra-ma e tabela a seguir.

Seção 4

Mais recursos SQL

Nesta seção você conhecerá outros recursos da SQL, como a utilização da cláusula DISTINCT, agrupamento de informações selecionadas, re-cuperação dos dados de várias tabelas e utilização de consultas avança-das. Confira!

Cláusula DISTINCT

É comum ter registros em tabela contendo valores repetidos, como:

Figura 28: Tabela de produtos populada acrescida do registro Feijão preto

Note que foi inserido registro “Feijão preto” e sua unidade de medida é kg, como a do Arroz branco, repetindo assim o valor “kg” na tabela. Esse é apenas um exemplo de ocorrência entre várias que podem ser encontradas (e normalmente são encontradas) em tabelas de banco de dados.

Situação: Suponha que você deseja fazer uma consulta das unidades

de medidas, mas não gostaria que estas se repetissem, ou seja, não de-seja que o registro kg de-seja repetido, como normalmente aconteceria ao executar uma instrução básica SQL. Para isso, implemente a seguinte instrução SQL:

SELECT DISTINCT Unidademedida from produtos

Resultado:

Figura 29: Resultado da consulta das unidades de medida

Esse é um exemplo que repre-senta a aplicação da cláusula DIS-TINCT que, segundo Machado (2008, p. 349)

“[...] foi criada para não permitir que certas redundâncias, ob-viamente necessárias, causem problemas. A cláusula DISTINCT elimina repetições de valores em relação a uma coluna.”

Cláusulas GROUP BY e

HAVING

O exemplo para essa cláusula ba-seia-se na tabela orcamento_peca, utilizada na explicação da função SUM ( ).

Situação: Digamos que você

pre-cise emitir a listagem do núme-ro de peças que cada orçamento possui, agrupados pelo id do

or-çamento.

Se você verificar a Tabela orca-mento_pecas populada, pode conferir que para o orçamento cujo id é 12 existem duas peças,

a de código 12 e a de código 8901, já para o orçamento cujo id é 13

existe uma peça a de código 12 e, por fim, para o orçamento cujo id é 15 existem duas peças, a

de código 12 e a de código 8901. Conseguiu visualizar? Se realizar

uma consulta simples nesta tabela, o resultado será o mesmo visuali-zado na da Tabela 10, mas não é assim que desejamos o resultado, mas sim o dos orçamentos agru-pados com o total de peças que possui.

SELECT orcamento_idorca-mento, count( * ) AS Total_Pe-ças FROM orcamento_peca GROUP BY orcamento_idorca-mento

Resultado:

Figura 30: Resultado da consulta do número de peças por orçamento

Sentiu o poder desta cláusula? Nesse caso é mostrado o resulta-do para toresulta-dos os orçamentos da tabela. Se quiser, pode filtrar os orçamentos que deseja visualizar de forma agrupada, como, por exemplo:

SELECT orcamento_idorcamen-to, count ( * ) AS Total_Peças FROM orcamento_peca WHERE orcamento_idmento > 12 GROUP BY orca-mento_idorcamento

Como você viu, estamos reali-zando a mesma consulta, só que desejamos visualizar o resultado para os orçamentos cujo id é su-perior a 12.

Resultado:

Figura 31: Resultado da consulta orça-mentos com id superior a 12

O exemplo anterior mostra a uti-lização da cláusula GROUP BY com a cláusula WHERE.

Segundo Machado (2008, p. 350), “Geralmente, a cláusula GROUP BY é utilizada em conjunto com as operações COUNT e AVG.” Agora, imagine uma consulta para os orçamentos com o total de pe-ças superior a 1:

SELECT orcamento_idorcamen-to, count( * ) AS Total_Peças FROM orcamento_peca GROUP BY orcamento_idorca-mento

HAVING COUNT( * ) >1

Resultado:

Figura 32: Resultado da consulta do total de peças superior a 1

Note que agora foi utilizada a cláusula HAVING para fazer o fil-tro (restrição) da sentença, deter-minando que só sejam mostrados os orçamentos cujos totais sejam superior a 1. É semelhante à cláu-sula WHERE em um SELECT. A cláusula HAVING trabalha em parceria com GROUP BY. Perceba que existe uma interação da cláusula GROUP BY com ou-tras cláusulas SQL, possibilitando

Ainda sobre essa integração, tem--se que GROUP BY pode ser aplicada com qualquer outra cláu-sula trabalhada em nosso estudo.

Acessar dados de duas

tabelas

Ao desenvolver a modelagem de dados, está-se elaborando o pro-jeto de banco de dados para uma solução que servirá a um sistema de informação para que possa inserir, atualizar, excluir e princi-palmente acessar as informações para que o usuário deste possa gerir seu negócio. Por menor que seja seu projeto de banco de da-dos, sempre haverá tabelas de di-ferentes natureza e características, porém algumas se relacionam pela necessidade de se obter informa-ções.

Mas como acessar as informações de duas tabelas? É o que você vai saber a partir de agora.

A linguagem SQL nos fornece também recursos para o acesso simultâneo a tabelas que encon-tram-se relacionadas, visando extrair informações encontradas nelas. Esse recurso é identificado como junção ou JOIN entre tabe-las.

Para ilustrar nosso estudo, vamos utilizar as tabelas clientes e carros, que se referem respectivamente a informações dos clientes de uma oficina mecânica e também aos carros pertencentes a esses clien-tes.

Representando essas tabelas populadas, você verifica:

Figura 33: Tabela clientes oficina mecânica populada

Figura 34: Tabela carros oficina mecânica populada

Observe o relacionamento entre as tabelas a partir do código do cliente inserido na tabela carros, identificando a quem o veículo pertence. Po-rém, observe também que se trata do código do cliente, e não seu nome. Isso faz parte das regras de relacionamento entre tabelas, como você já estudou.

INNER JOIN

Agora suponha que necessite fazer uma consulta em que tenha que vi-sualizar os carros, identificados por suas placas, com os seus respectivos donos (clientes), sendo, neste caso, necessário visualizar o nome e o có-digo. A SQL disponibiliza o recurso de junção para resolver essa e outras situações, veja:

SELECT clientes.nome,carros.cliente_idcliente,carros.placa FROM clien-tes INNER JOIN carros

ON clientes.idcliente = carros.cliente_idcliente

Entendendo a instrução:

Note que estamos selecionando os campos que serão visualizados na consulta fazendo referência à tabela onde ele se encontra seguido do seu nome, clientes.nome, carros.cliente, carros.placa, isso porque a consulta envolve duas tabelas – clientes e carros.

Observe na sequência FROM clientes INNER JOIN carros que esta-mos estabelecendo a junção entre as tabelas, do tipo INNER JOIN que, por sua vez, força com que os registros resultantes da consulta sejam aqueles que satisfazem a condição do JOIN.

Finalizando, temos ON clientes.idcliente=carros.cliente_idcliente, na qual estamos determinando a condição do JOIN para que só inclua, na consulta, os registros que existam tanto na tabela clientes quanto na tabela carros, ou seja, aquele registro que não se encontra em ambas as tabelas não aparecerá no resultado dessa consulta. Com isso sabemos, de antemão, que o registro do cliente Silvio Luis não aparecerá no resultado dessa consulta.

Resultado:

Figura 35: esultado da consulta cliente/carros INNER JOIN

Devido à junção entre as tabelas cliente e carros, agora é possível ver, além dos códigos, os nomes dos clientes, podendo assim identificar com maior clareza os proprietários dos veículos.

CROSS JOIN

O CROSS JOIN é outro tipo de JOIN, em que cada registro de uma das tabelas é combinada com todos os registros da outra tabela relacionada. Veja como utilizar, ainda tomando como exemplo as tabelas relaciona-das clientes e carros:

SELECT nome, carro.cliente_idcliente, carro.placa FROM clientes CROSS JOIN carros

Resultado:

Figura 36: Resultado da consulta clientes/carros CROSS JOIN

Note que cada registro de cliente da tabela clientes relaciona-se com todos os registros encontrados na tabela carros, formando assim um relacionamento cruzado relativamente grande e também sem muita uti-lidade na maioria dos casos. Esse tipo de JOIN também é conhecido como produto cartesiano em álgebra relacional.

OUTER JOIN

Neste outro tipo de JOIN você encontra três tipos de qualificação:

▪

LEFT OUTER JOIN

Suponha que queira saber quais são os clientes que têm veículos cadas-trados em carros e também queira saber os clientes que ainda não têm veículos cadastros em carros.

SELECT clientes.nome,carros.cliente_idcliente,carros.placa from clien-tes LEFT OUTER JOIN carros ON clienclien-tes.idcliente = carros.cliente_ idcliente

Resultado:

Figura 37: Resultado da consulta clientes/carros LEFT OUTER JOIN

Nessa consulta são considerados todos os registros encontrados na ta-bela de clientes, ou seja, a tata-bela à esquerda (LEFT) na condição ON cliente.idcliente = carro.cliente_idcliente, segue fazendo a varredura em carros dos clientes que também existem em ambas as tabelas.

Note também que o cliente Silvio Luis, como não tem nenhum carro cadastrado, apresenta o resultado NULL nas colunas cliente_idcliente e placa.

Conforme Machado (2008, p. 357) “[...] não devemos utilizar NULL na condição de seleção, pois teremos de utilizar os resultados mais imprevi-síveis e imagináveis posimprevi-síveis.”

▪

RIGHT OUTER JOIN

Agora, para saber quais são os proprietários dos veículos (clientes) ca-dastrados na tabela carros, utilize a seguinte sintaxe:

SELECT clientes.nome,carros.cliente_idcliente,carros.placa from clien-tes RIGHT OUTER JOIN carros ON clienclien-tes.idcliente = carros.cliente_ idcliente

Resultado:

Tabela 1: Resultado da consulta clientes/carros RIGHT OUTER JOIN

nome cliente idcliente placa

Luciene Márcia 1 ABC 000

Fábio Karnopp 2 DEF 111

Carlos Carvalho 3 GHI 222

▪

FULL OUTER JOIN

Nesse caso obtém-se como resul-tado os registros que não satisfa-zem a condição tanto da primeira como da segunda tabela envolvi-das no relacionamento.

Vale ainda salientar que você pode utilizar várias cláusulas misturada com operadores na junção de ta-belas, como nos explica Machado (2008, p. 357): “Podemos utilizar as cláusulas LIKE, NOT LIKE, IN, NOT IN, NULL, NOT NULL e misturá-las com os ope-radores AND, OR e NOT, dentro de uma cláusula WHERE na jun-ção de tabelas”.

Exemplo: suponha que deseja ob-ter os clientes que têm veículos cadastrados na oficina com a cor cinza:

SELECT clientes.nome,carros. cliente_idcliente,carros.placa FROM clientes INNER JOIN ros ON clientes.idcliente = car-ros.cliente_idcliente WHERE carros,cor = cinza

Resultado:

Figura 38: Resultado da consulta clientes/carros FULL OUTER JOIN

Utilizar apelidos em JOINS

As instruções JOIN permitem a simplificação em sua escrita, por exem-plo, verifique a instrução a seguir:

SELECT clientes.nome,carros.cliente_idcliente,carros.placa from clien-tes INNER JOIN carros

ON clientes.idcliente = carros.cliente_idcliente

Note que na frente dos campos das tabelas você identifica o nome da tabela para depois informar o nome do campo (clientes.nome). Isso é necessário porque existem campos de tabelas diferentes.

SELECT cl.nome,ca.cliente_idcliente,ca.placa FROM cliente cl INNER JOIN carro ca

ON cl.idcliente = ca.cliente_idcliente

Agora perceba que os nomes das tabelas não são escritos por inteiro antes dos nomes dos campos (cl.nome, ca.cliente). Para simplificar, uti-liza-se somente as iniciais, e isso só é possível porque em FROM você informa que cliente poderia ser entendido como cl (FROM cliente cl). Esses nomes de tabelas simplificados são chamados de ALIASES ou APELIDO, e isso facilita bastante quando temos de manusear consultas em tabelas com muitos campos, por exemplo.

Acessar dados de várias tabelas

Figura 39: Diagrama de entidades e relacionamentos bairros/clientes/carros

A representação das tabelas clien-tes e carros populadas você já conhece, falta agora conhecer a representação da tabela bairros populada. Veja a seguir.

Figura 40: Tabela bairros oficina mecâ-nica populada

Suponha agora que em uma con-sulta seja necessário visualizar os carros, identificados por suas pla-cas, com os seus respectivos pro-prietários (clientes) e também o nome do bairro em que residem. Como você pode imaginar, para realizar essa pesquisa você terá de trabalhar com três tabelas “bair-ros, clientes e carros”. Note que elas já estão relacionadas, então, é só utilizar os recursos para extrair as informações desejadas. Então:

SELECT carros.cliente_idclien-te AS código, cliencarros.cliente_idclien-tes.nome, bairros.nome AS bairro, car-ros.placa AS placa_veiculo FROM clientes INNER JOIN carros ON clientes.idclien-te = carros.clienclientes.idclien-te_idclienclientes.idclien-te INNER JOIN bairros ON clien-tes.bairro_idbairro = bairros. idbairro

Note que houve uma “mexida” na disposição das colunas (campos) das tabelas, mas apenas por uma questão de estética, nada por questões téc-nicas. Onde antes mostrava o código do cliente, proveniente da tabela carros e com o nome de coluna alterado de cliente_idcliente mudou para código, a seguir o campo nome do cliente, proveniente da tabela clientes, na sequência o campo nome do bairro, proveniente da tabela bairros e com o nome de coluna alterado de nome para bairro e, por fim, o cam-po placa, proveniente da tabela carros com o nome de coluna também alterado de placa para placa_veículo.

Resultado:

Figura 41: Resultado da consulta clientes/carros/bairros

Verifique a tabela referente aos dados populados de clientes e lá você identificará os códigos dos bairros para os clientes. Foi com base nesses códigos que foram extraídos seus respectivos nomes. Perceba que na-quela tabela a cliente Luciene Márcia está associada ao bairro 1 - Costa e Silva; o cliente Fábio Karnopp está associado ao bairro 2 - Vila Nova; e o cliente Carlos Carvalho está associado ao bairro 3 - Bom Retiro.

Sabe por que só apareceram esses três nomes no resultado dessa consulta? Porque somente esses clientes é que têm cadastro na tabela de carros.

Como todo conhecimento é aprendizado, e em se tratando de banco de dados quanto mais, melhor, vamos seguir utilizando a estrutura do projeto de banco de dados da oficina mecânica, incrementando-o com mais duas tabelas.

Representação da nova estrutura:

Figura 42: Diagrama de entidades e relacionamentos bairros/clientes/carros/orça-mentos/atendentes

A representação das tabelas clientes, carros e bairros populadas você já conhece. Veja agora a representação das tabelas atendentes e orçamen-tos, populadas.

Figura 43: Tabela atendentes oficina mecânica populada

Figura 44: Tabela orçamentos oficina mecânica populada

Com relação à tabela orçamentos, cabe explicar que os dados do campo data são armazenados no formato ano-mês-dia e referem-se à data, pro-priamente dita. Já o campo execução refere-se ao tempo de execução do serviço orçado, e o campo situacao, refere-se à situação do orçamento (confirmado ou aguardando confirmação).

Essas consultas estão ficando cada vez mais interessantes, não é mesmo? Continuando, então, suponha que queira fazer uma consulta dos clien-tes cujos veículos estão cadastrados e que já possuem orçamento com previsão de execução de serviço menor que 10 dias, e que tenham sido atendidos pelo atendente de código 1 (João), em que o resultado deverá ser mostrado por ordem crescente do nome do cliente:

SELECT carros.cliente_idcliente AS código, clientes.nome AS clientes, carros.placa AS placa_veículo, orcamentos.execucao AS execução, atendentes.nome AS atendente

FROM clientes

INNER JOIN carros ON clientes.idcliente = carros.cliente_idcliente INNER JOIN orcamentos ON carros.placa = orcamentos.carros_placa INNER JOIN atendentes ON orcamentos.atendente_idatendente = idatendente

WHERE orcamentos.execucao < 10

AND orcamentos.atendente_idatendente=1 ORDER BY clientes.nome

Da mesma forma que no exemplo anterior, perceba que a sequência dos campos, bem como o nome de algumas colunas, foram alterados. Note também que agora manipulamos quatro tabelas – clientes, carros, orça-mentos e atendentes –, utilizamos a cláusula WHERE para os filtros da consulta e a cláusula ORDER BY para mostrar o resultado por ordem crescente do nome do cliente.

Resultado:

Se você parar para observar o resultado da consulta, verá que os códigos aparecem em ordem decrescente. Parece estar errado, não é mesmo? Porém, a letra F vem antes da letra L e você solici-tou que o resultado fosse ordena-do pelo nome ordena-do cliente, então o resultado da pesquisa está correto. Note que as quatro tabelas men-cionadas encontram-se presentes no resultado dessa consulta, por-que foi solicitado a partir da ins-trução SQL que você implemen-tou anteriormente.

Os conhecimentos a serem passa-dos por esta unidade encerram-se aqui, mas lembre-se que é impor-tante praticar, pôr a mão na massa, pois só assim você vai esclarecer muitas dúvidas que certamente persistem. A prática é tudo em um processo técnico de aprendi-zagem. Na próxima unidade você conhecerá mais um recurso im-portante para a sua formação em banco de dados. Bom estudo!

Unidade de

estudo 3

Seções de estudo

Seção 1 – Abordagem inicial Seção 2 – Manipulando views

Views

Seção 1

Abordagem inicial

Do começo do curso até aqui você já conheceu vários recursos de banco de dados implemen-tados a partir da SQL. Agora, você está convidado a conhecer as views, ou visões. São recursos

disponibilizados por alguns SGB-Ds e implementados a partir de instruções SQL que permitem a criação de visões personalizadas das informações de suas tabelas. Machado (2008, p. 373) fala o se-guinte sobre a utilização de views :

“[...] são utilizadas para se ter uma particular visão de uma tabela, para que não seja ne-cessária a utilização do conjunto como todo”.

Imagine que, ao desenvolver o seu projeto de banco de dados, você já previu que para a tabela produ-tos precisará de uma consulta dos produtos com unidade de medida em quilogramas, isso sugere que você crie uma visão, ou view, para

essa consulta. Dessa forma, toda vez que necessitar dessa medida basta acionar a view criada.

A exemplo das tabelas, as views

também devem ser criadas e, após sua criação, também apresentam uma estrutura bem definida.

DICA

Quando for utilizar uma

View, fique atento para

algu-mas restrições. Neste aspec-to, Machado (2008, p. 374) nos dá a seguinte orienta-ção: “Não utilize SELECT INTO, ORDER BY, COMPUT, COMPUTE BY OU UNION.” Na próxima seção você acompa-nhará as views na prática. Siga em

frente!

Seção 2

Manipulando views

Para este estudo será utilizado o banco de dados de um minimer-cado e manteremos a tabela pro-dutos que já tem uma estrutura pronta e, inclusive, já foi utiliza-da nos exemplos anteriores, está lembrado dela?

Acompanhe então os passos para criar uma view para a consulta dos

produtos com a unidade de medi-da quilogramas.

Criando a view

Em primeiro lugar, acesse o ban-co de dados onde deseja criar a

view, e no seu editor SQL escreva

a seguinte instrução:

CREATE VIEW prodkg (codigo_ prodkg,nome,descricao,unidad emedida)AS

SELECT codigo,nome,descricao, unidademedida from produtos WHERE unidademedida=’Kg’

Analisando a instrução, note que inicia com o comando para a cria-ção da view, seguida pelo nome

que deseja atribuir a sua view

(pro-dkg) e, após, dentro dos parên-teses, o nome dos campos que deseja que a view contenha, sendo

que esses campos receberão os valores dos campos da tabela que está utilizando como parâmetro para a construção da view. Nesse

exemplo estamos criando a view

prodkg, com os campos codigo_ prodkg, nome, descricao e uni-dademedida, que receberão os

dados dos campos código, nome, descrição e unidade medida da ta-bela produtos.

Complementando a análise da instrução, essa view só conterá os

registros dos produtos cuja uni-dade de medida for o quilograma (kg).

Veja como fica a estrutura da view

Figura 46: da criação da view prodkg

Percebeu como o arquivo da view prodkg é parecido com uma tabela?

Isso acontece porque é criada uma estrutura física e a view é alimentada

com os registros que a instrução SQL solicita. Depois de criada, a view

está pronta para ser utilizada a qualquer momento. Agora, vamos para a parte prática.

Situação: suponha que queira mostrar todos os registros, com todos os

campos da view prodkg.

SELECT * FROM prodkg

Resultado:

Figura 47: Resultado consulta simples à view prodkg

Para consultar a view, você pode fazer uso de tudo o que aprendeu sobre

consultas SQL até o momento. Se você quiser fazer um filtro, por exem-plo, consultar os produtos cujo código seja 1 ou cuja descrição seja Tipo parboilizado, e assim por diante, nesse aspecto não existem restrições.

A view criada permanece em seu

banco de dados até que seja exclu-ída. Uma vantagem do uso de views

é a de que você pode personalizar suas consultas com estruturas en-xutas possibilitando uma melhor performance quando do acesso às informações dessas views.

Você pode ainda inserir registros em uma view, assim como faz com

tabelas:

INSERT INTO prodkg VALUES (5,’Arroz integral’,’Tipo 1’,’Kg’)

Analisando a sintaxe apresentada para inserção de produto na view,

você pode se perguntar “por que o código do produto é 5 se na view

só temos produtos de código 1 e 2?” É que essa view está associa

àquela tabela de produtos que já temos no nosso banco de dados, e já existem produtos cadastrados com os códigos 3 e 4. Apesar de não aparecerem na view, seus

có-digos não podem ser utilizados, portanto, neste caso, o próximo código de produto a inserir na view

Resultado:

Figura 48: Resultado da inserção de um registro na view prodkg

É possível também modificar um registro de uma view como se faz em

uma tabela:

UPDATE prodkg SET descricao = ‘Tipo 2’ WHERE codigo_prodkg = 5

Resultado:

Figura 49: Resultado da edição de um registro na view prodkg

Já para eliminar um registro de uma view:

DELETE from prodkg where codigo_prodkg = 5

Resultado:

Para finalizar, como eliminar

inteiramente uma

view?

DROP VIEW prodkg

Atenção! Cuidado ao utilizar esse comando. Nenhuma confirmação é solicitada e, ao realizar essa ope-ração, a view é excluída imediata-mente do banco de dados.

DICA

Analise bem as views que deseja criar. Não saia por aí criando views de qualquer maneira, de preferência crie

views para aquelas consultas

que envolvem uma ou mais tabelas que você considera diferenciadas, ou seja, para aquelas consultas que serão utilizadas com frequência e também que agregam re-sultados importantes para o usuário final.

Você está convidado a

conhe-cer, na próxima unidade, um

recurso muito útil na gestão de

informações de banco de

da-dos. Até lá!

Unidade de

estudo 4

Seções de estudo

Seção 1 – Abordagem inicial Seção 2 – Manipulando Trigger

Trigger

Seção 1

Abordagem inicial

Você já ouviu falar em Triggers, ou gatilho? É um recurso utilizan-do quanutilizan-do, ao inserir registros nas tabelas do seu banco de dados, você deseja enviar mensagens de alerta avisando, por exemplo, que a inclusão foi efetuada com sucesso, que ocorreu um erro na inclusão, alteração ou exclusão. Em programação estruturada, é comum utilizar funções nesse tipo de situação, mas esse recurso tam-bém pode ser utilizado a partir do banco de dados, por meio da Trigger. Digamos que você tenha

implementado integridade refe-rencial em suas tabelas e que de-seja fazer validações dos campos das tabelas para que, por exemplo, campo data e hora sejam preen-chidos corretamente, evitando assim inconsistência de dados. Isso pode ser trabalhado em pro-gramação estrutura com uso de função ou igualmente em banco de dados com Trigger.

Ainda no contexto de que você te-nha implementado integridade re-ferencial em suas tabelas e queira excluir informações de um cliente na tabela clientes, e este cliente também se encontra registrado na tabela carros, e você gostaria de que, ao confirmar a exclusão, os registros desse cliente fossem excluídos em cascata, primeiro da tabela carros para depois ser excluído da tabela clientes. Isso é possível por meio de uma função em programação estruturada ou fazendo uso de Trigger.

Está com dúvida se deve usar pro-gramação estruturada ou Trigger? Não desanime! Ainda bem que existem, à sua disposição, muitos meios de viabilizar sua solução e, com um conhecimento mais aprofundado, você saberá discer-nir qual a melhor implementação para as situações que necessitem de tratamentos.

Outras aplicabilidades de Trigger:

[...]

1. Alertar o usuário se houver alguma exceção (por exem-plo, emitindo um aviso se a qualidade disponível de alguma peça cair abaixo do nível de perigo).

2. Depuração (por exemplo, monitoração de referências e/ou mudança de estado de variáveis designadas). 3. Auditoria (por exemplo,

acompanhamento de que re-alizou quais atualizações so-bre quais eventos do banco de dados).

4. Medição de desempenho (por exemplo, temporização ou rastreamento de eventos específicos do banco de da-dos)[...] (DATE, 2003, p. 240).

Arquitetura de um

Trigger

É importante você compreender

turas com maior eficiência. As ex-plicações sobre arquitetura de um Trigger foram baseadas em Ecle-siastes (2010):

Sintaxe de criação:

1. CREATE [DEFINER = {

user | CURRENT_USER }]

2. TRIGGER trigger_name

tri-gger_time trigger_event ON tbl_name

3. FOR EACH ROW trigger_

stmt

A sintaxe apresentada sugere a criação de um Trigger em uma ta-bela no banco de dados, lembran-do que, ao optar pelo uso desse recurso, é necessário verificar se o SGBD que está sendo utilizando suporta esse recurso , por exem-plo, se for criar Trigger em MyS-QL, será preciso utilizar a tabela com o tipo InnoDB.

Sobre essa sintaxe o autor destaca que o Trigger é nomeado objeto de banco de dados que está asso-ciado com uma tabela e é ativado quando um evento em particular ocorre para essa tabela, e ainda diz que não se pode associar um Trigger a uma TEMPORARY TABLE ou uma view.

Perceba a preocupação do autor em relacionar um Trigger com uma tabela de banco de dados e ainda nos esclarecer que este só é acionado a partir do momento em que um evento (ação) ocorre para