Roteiro 9 - SQL Básico: chave estrangeira, operadores de comparação e operadores booleanos

(1)

e

cnologia SENAC PELOTAS

Cre denciado pela Portaria nº. 3.07 1, de 01 de out ubro de 2004.

Roteiro 9 - SQL Básico: chave estrangeira, operadores de

comparação e operadores booleanos

Objetivos:

• Criar restrições para atributos, chaves primárias e estrangeiras; • Explorar consultas SQL com uso de operadores de comparação; • Explorar consultas SQL com uso de operadores booleanos;

Neste roteiro são explorados comandos para a realização de consultas com uso de operadores de comparação e operadores booleanos importantes para a realização de consultas mais complexas onde os dados devem ser filtrados de acordo com critérios específicos do programador.

Ferramentas necessárias

Navegador, Xampp,Notepad++, console Mysql, Bibliografias de livros da faculdade.

Criando Restrições

Em SQL, restrições podem ser definidas para as tabelas que forem criadas. Existem restrições de chave e integridade referencial, restrições sobre domínios de atributo e NULLs e restrições sobre tuplas individuais (registros) dentro de uma relação.

Restrições de atributo: Como o nome indica, são restrições que podem ser aplicadas aos atributos de uma tabela. Existem

algumas restrições que podem ser aplicadas como valores null (NULL), valores padrão e valores (DEFAULT) de checagem (CHECK), que são detalhados a seguir na tabela abaixo:

Restrição Descrição Exemplo de uso

NOT NULL Se um valor NULL não for permitido isto pode ser especificado através do parâmetro NOT NULL na definição de um campo. Geralmente esta propriedade é definida para campos de chave primária, mas pode ser especificado para quaisquer outros atributos cujos valores não podem ser NULL.

CREATE TABLE X(

NOME VARCHAR(30) NOT NULL );

DEFAULT Determina um valor padrão para um atributo. O valor padrão será incluído em qualquer registro se um valor explícito não for declarado.

CREATE TABLE Y(

TPAGTO INT DEFAULT 100 );

CHECK Utilizado para limitar valores de atributo ou domínio. Pode ser utilizado para expressar que apenas valores dentro de uma faixa x podem ser utilizados em um campo.

CREATE TABLE Z(

TPAGTO INT CHECK(TPAGTO > 0 AND TPAGTO <3)

(2)

Restrições de chave e integridade referencial: são restrições que podem ser impostas para os atributos para garantir a

integridade da informação que será armazenada. Existem as restrições de chave primária (primary key) e de chave segundária (foreign key), as duas propriedades são detalhadas na tabela a seguir:

PRIMARY KEY

Permite especificar atributos de chave primária em uma tabela. Se o atributo chave for único a restrição pode ser incluída diretamente no atributo, caso contrário deve ser incluído ao final da declaração dos campos ou após através da alteração da tabela.

CREATE TABLE X(

ID_FUNC INT PRIMARY KEY ); CREATE TABLE X( ID_FUNC INT, PRIMARY KEY(ID_FUNC); ); PRIMARY KEY(CAMPO1,CAMPO2,CAMPO3); FOREIGN KEY

Especifica a criação de uma chave estrangeira. Da mesma forma que a chave primária, esta propriedade pode ser inserida na definição da tabela ou

posteriormente através do comando ALTER TABLE.

CREATE TABLE Y(

IDCIDADE INT NOT NULL,

FOREIGN KEY(IDCIDADE) REFERENCES CIDADE(IDCIDADE)

);

Ações de violação de integridade: uma violação ocorre quando alguma informação inserida ou atualizada em um atributo

viola as regras de chave. Quando acontece uma violação de integridade, a ação padrão (default) do SGBD é a de recusar a operação. Porém é possível adicionar ações de comportamento para que sejam executadas, descritas na tabela a seguir:

ON DELETE Define opções para quando são deletados

registros envolvendo chaves estrangeiras.

CREATE TABLE X( ...

FOREIGN KEY (IDCIDADE) REFERENCES CIDADE(IDCIDADE) ON DELETE [AÇÃO]

);

ON UPDATE

Define opções para quando são

atualizados registros envolvendo chaves estrangeiras.

FOREIGN KEY (IDCIDADE) REFERENCES CIDADE(IDCIDADE) ON UPDATE [AÇÃO]

);

SET NULL

Opção para ON DELETE OU ON UPDATE que define que ao ser excluído ou alterado um registro que é referenciado por outras tabelas como chave estrangeira, estas são definidas como NULL.

FOREIGN KEY (IDCIDADE) REFERENCES CIDADE(IDCIDADE) ON DELETE SET NULL

);

CASCADE

Opção para ON DELETE OU ON UPDATE que define que ao ser alterado um registro que é referenciado por outras tabelas como chave estrangeira, estas são atualizadas juntamente com o novo valor definido.

FOREIGN KEY (IDCIDADE) REFERENCES CIDADE(IDCIDADE) ON UPDATE CASCADE

(3)

e

Nomeando Restrições

Uma restrição pode ser nomeada em sua definição com uso da palavra-chave CONSTRAINT. Nomes de restrição precisam ser exclusivos e são importantes por identificarem diferentes restrições no caso de precisarem ser excluídas ou trocadas por outras ao longo da definição do banco de dados. Nomear restrições em bancos de dados é uma tarefa opcional, porém as principais ferramentas CASE de implementação normalmente utilizam nomes em restrições.

Exemplo de criação de restrição com uso de CONSTRAINT

Operadores de comparação

A cláusula WHERE introduz um potencial maior para as buscas de informações. Permite que seja possível escolher quais linhas são restauradas de uma declaração SELECT. É utilizado para restaurar linhas que combinem uma condição, como ter um valor de coluna que combine exatamente com uma string, um número maior ou menor que um valor, ou uma string que é o prefixo de outra. A seguir são demonstrados os operadores de comparação sendo utilizados em conjunto com a cláusula WHERE.

Operadores de Comparação

Os operadores de comparação a serem utilizados em uma consulta SQL são os mesmos utilizados na maioria das linguagens de programação, e permitem que uma consulta possa ser executada de forma muito mais poderosa para a seleção de informações em tabelas. Os operadores de comparação são descritos a seguir:

Comando Função Exemplo

SELECT campos FROM tabela WHERE argumentos

= Insere um operador de comparação “igual a” entre dois campos.

SELECT * FROM dados WHERE nome = “joão”

<

Insere um operador de comparação “menor que” entre dois campos ou expressões.

SELECT * FROM funcionario WHERE salario < 1000

>

Insere um operador de comparação “maior que ”entre dois campos ou expressões.

SELECT * FROM cidade WHERE populacao > 20000

<=

Insere um operador de comparação “maior que” entre dois campos ou expressões.

SELECT * FROM cidade WHERE populacao >= 20000

>=

Insere um operador de comparação “menor que ”entre dois campos ou expressões.

SELECT * FROM aluno WHERE nota >= 7

<> Insere um operador de comparação “diferente de”

SELECT * FROM pessoa WHERE sexo <> “masculino”

CONSTRAINT PK_FUNCIONARIO

PRIMARY KEY(IDFUNCIONARIO), CONSTRAINT FK_CIDADE_FUNCIONARIO

FOREIGN KEY(IDCIDADE) REFERENCES CIDADE(IDCIDADE) ON DELETE SET NULL ON UPDADTE CASCADE;

(4)

Operadores Booleanos

Os operadores de condição são úteis para combinar duas ou mais condições utilizando os operadores booleanos AND, OR e NOT. Permite unir critérios de pesquisa diferentes na mesma consulta, podemos utilizar a conjunção AND e separar critérios diferentes, por exemplo.

Operadores AND, OR e NOT

A seguir são detalhados os operadores de combinação que podem ser utilizados em conjunto nas consultas com uso do SELECT em conjunto com a cláusula WHERE.

Comando

Função

Exemplo

SELECT campos FROM tabela WHERE argumentos

AND Restringe os resultados para as linhas que possuem ambas as condições.

SELECT * FROM dados WHERE nome = “joão” and salario > 3000

OR Restringe os resultados para as linha que atendam pelo menos uma das condições.

SELECT * FROM funcionario WHERE salario < 1000 or nome >= ‘G’

NOT Nega uma declaração Booleana. SELECT * FROM funcionario WHERE NOT (salario >1000 and salario < 5000)

Tarefas

Utilizando o script de criação disponível em http://187.7.106.14/andre/ads/proj_bd/2012-2/roteiros/roteiro9-chaves-restricoes/roteiro9-definicao-dados.sql, crie o banco de dados em seu servidor Mysql para uso nas tarefas do roteiro.

1) Crie as chaves estrangeiras utilizando uma CONSTRAINT nomeada com o prefixo FK_TABELAORIGEM_TABELADESTINO para as seguintes tabelas

a. Movimento navio; b. Navio;

c. Porto;

d. Porto_visitado;

2) Altere o script de criação e Insira uma restrição de atributo para o campo TIPO_NAVIO.CAPACIDADE_PESO para que não aceite valores inferiores a 20000. Insira um novo registro de tipo de navio e teste o resultado da restrição feita.

3) Insira no script de todas as tabelas criadas para a chave primária a ação de violação de integridade ON UPDATE com

CASCADE e ON DELETE para SET NULL;

4) Crie uma consulta para selecionar todos os portos Localizados em Salvador;

5) Crie uma consulta para selecionar todos os tipos de navios com capacidade maior do que 30000 e casco unico; 6) Crie uma consulta para selecionar todos os navios onde o proprietário seja Sergen & Terres OU o tipo seja

Graneleiro;

(5)

e

10) Crie uma consulta que liste todos os nomes dos navios e a data de inicio onde a data de inicio seja superior a 13 de setembro de 2011;

Referências

• Elmasri, Navathe – Sistemas de banco de dados 6ª Edição;

• “Introdução a Banco de dados”, Osvaldo Kataro Tokai, disponível em http://pt.scribd.com/doc/50780287/BD; • “Apache Friends Home page” - http://www.apachefriends.org/pt_br/xampp-windows.html#2287